Mercurial > p > roundup > code
changeset 5869:16e1255b16cf
Implement limit and offset for filter
These map to the corresponding SQL parameters for the SQL backends.
| author | Ralf Schlatterbeck <rsc@runtux.com> |
|---|---|
| date | Mon, 26 Aug 2019 20:23:53 +0200 |
| parents | d2fac1069028 |
| children | 5ae426616576 |
| files | CHANGES.txt doc/design.txt roundup/backends/back_sqlite.py roundup/backends/rdbms_common.py roundup/hyperdb.py test/db_test_base.py |
| diffstat | 6 files changed, 69 insertions(+), 14 deletions(-) [+] |
line wrap: on
line diff
--- a/CHANGES.txt Mon Aug 26 18:19:09 2019 +0200 +++ b/CHANGES.txt Mon Aug 26 20:23:53 2019 +0200 @@ -85,7 +85,9 @@ - issue2551043: Add X-Roundup-issue-id email header. Add a new header to make it easier to filter notification emails without having to parse the subject line. (John Rouillard) -- The database filter function now can also do an exact string search. +- The database filter method now can also do an exact string search. +- The database filter method now has limit and offset parameters that + map to the corresponging parameters of SQL. Fixed:
--- a/doc/design.txt Mon Aug 26 18:19:09 2019 +0200 +++ b/doc/design.txt Mon Aug 26 20:23:53 2019 +0200 @@ -516,7 +516,7 @@ """ def filter(self, search_matches, filterspec, sort, group, - retired, exact_match_spec): + retired, exact_match_spec, limit, offset): """Return a list of the ids of the active nodes in this class that match the 'filter' spec, sorted by the group spec and then the sort spec. The arguments sort, group, retired, and @@ -544,6 +544,14 @@ items are returned. The default is False, i.e. only live items are returned by default. + The "limit" and "offset" parameters define a limit on the + number of results returned and an offset before returning + any results, respectively. These can be used when displaying + a number of items in a pagination application or similar. A + common use-case is returning the first item of a sorted + search by specifying limit=1 (i.e. the maximum or minimum + depending on sort order). + The filter must match all properties specificed. If the property value to match is a list:
--- a/roundup/backends/back_sqlite.py Mon Aug 26 18:19:09 2019 +0200 +++ b/roundup/backends/back_sqlite.py Mon Aug 26 20:23:53 2019 +0200 @@ -454,14 +454,11 @@ return [[row[col] for col in cols] for row in l] class sqliteClass: - def filter(self, search_matches, filterspec, sort=(None,None), - group=(None,None), retired=False, exact_match_spec={}): + def filter(self, *args, **kw): """ If there's NO matches to a fetch, sqlite returns NULL instead of nothing """ - return [f for f in rdbms_common.Class.filter(self, search_matches, - filterspec, sort=sort, group=group, retired=retired, - exact_match_spec=exact_match_spec) if f] + return [f for f in rdbms_common.Class.filter(self, *args, **kw) if f] class Class(sqliteClass, rdbms_common.Class): pass
--- a/roundup/backends/rdbms_common.py Mon Aug 26 18:19:09 2019 +0200 +++ b/roundup/backends/rdbms_common.py Mon Aug 26 20:23:53 2019 +0200 @@ -2403,7 +2403,8 @@ return where, v, True # True to indicate original def _filter_sql (self, search_matches, filterspec, srt=[], grp=[], retr=0, - retired=False, exact_match_spec={}): + retired=False, exact_match_spec={}, limit=None, + offset=None): """ Compute the proptree and the SQL/ARGS for a filter. For argument description see filter below. We return a 3-tuple, the proptree, the sql and the sql-args @@ -2717,15 +2718,24 @@ else: order = '' + if limit is not None: + limit = ' LIMIT %s' % limit + else: + limit = '' + if offset is not None: + offset = ' OFFSET %s' % offset + else: + offset = '' cols = ','.join(cols) loj = ' '.join(loj) - sql = 'select %s from %s %s %s%s'%(cols, frum, loj, where, order) + sql = 'select %s from %s %s %s%s%s%s'%( + cols, frum, loj, where, order, limit, offset) args = tuple(args) __traceback_info__ = (sql, args) return proptree, sql, args def filter(self, search_matches, filterspec, sort=[], group=[], - retired=False, exact_match_spec={}): + retired=False, exact_match_spec={}, limit=None, offset=None): """Return a list of the ids of the active nodes in this class that match the 'filter' spec, sorted by the group spec and then the sort spec @@ -2750,7 +2760,8 @@ sq = self._filter_sql (search_matches, filterspec, sort, group, retired=retired, - exact_match_spec=exact_match_spec) + exact_match_spec=exact_match_spec, + limit=limit, offset=offset) # nothing to match? if sq is None: return [] @@ -2774,7 +2785,8 @@ return l def filter_iter(self, search_matches, filterspec, sort=[], group=[], - retired=False, exact_match_spec={}): + retired=False, exact_match_spec={}, limit=None, + offset=None): """Iterator similar to filter above with same args. Limitation: We don't sort on multilinks. This uses an optimisation: We put all nodes that are in the @@ -2785,7 +2797,8 @@ """ sq = self._filter_sql(search_matches, filterspec, sort, group, retr=1, retired=retired, - exact_match_spec=exact_match_spec) + exact_match_spec=exact_match_spec, + limit=limit, offset=offset) # nothing to match? if sq is None: return
--- a/roundup/hyperdb.py Mon Aug 26 18:19:09 2019 +0200 +++ b/roundup/hyperdb.py Mon Aug 26 20:23:53 2019 +0200 @@ -1399,7 +1399,7 @@ return sortattr def filter(self, search_matches, filterspec, sort=[], group=[], - retired=False, exact_match_spec={}): + retired=False, exact_match_spec={}, limit=None, offset=None): """Return a list of the ids of the active nodes in this class that match the 'filter' spec, sorted by the group spec and then the sort spec. @@ -1426,6 +1426,13 @@ are returned. The default is False, i.e. only live items are returned by default. + The "limit" and "offset" parameters define a limit on the number + of results returned and an offset before returning any results, + respectively. These can be used when displaying a number of + items in a pagination application or similar. A common use-case + is returning the first item of a sorted search by specifying + limit=1 (i.e. the maximum or minimum depending on sort order). + The filter must match all properties specificed. If the property value to match is a list: @@ -1453,8 +1460,17 @@ sortattr = self._sortattr(sort = sort, group = group) proptree = self._proptree(exact_match_spec, filterspec, sortattr) proptree.search(search_matches, retired=retired) + if offset is not None or limit is not None: + items = proptree.sort() + if limit and offset: + return items[offset:offset+limit] + elif offset is not None: + return items[offset:] + else: + return items[:limit] return proptree.sort() + # non-optimized filter_iter, a backend may chose to implement a # better version that provides a real iterator that pre-fills the # cache for each id returned. Note that the filter_iter doesn't
--- a/test/db_test_base.py Mon Aug 26 18:19:09 2019 +0200 +++ b/test/db_test_base.py Mon Aug 26 20:23:53 2019 +0200 @@ -2055,6 +2055,25 @@ ae(f(None, {'supervisor.supervisor': '3', 'supervisor': '4'}, ('+','username')), ['6', '7']) + def testFilteringTransitiveLinkUserLimit(self): + ae, filter, filter_iter = self.filteringSetupTransitiveSearch('user') + for f in filter, filter_iter: + ae(f(None, {'supervisor.username': 'ceo'}, ('+','username'), + limit=1), ['4']) + ae(f(None, {'supervisor.supervisor.username': 'ceo'}, + ('+','username'), limit=4), ['6', '7', '8', '9']) + ae(f(None, {'supervisor.supervisor': '3'}, ('+','username'), + limit=2, offset=2), ['8', '9']) + ae(f(None, {'supervisor.supervisor.id': '3'}, ('+','username'), + limit=3, offset=1), ['7', '8', '9']) + ae(f(None, {'supervisor.username': 'grouplead2'}, ('+','username'), + limit=2, offset=2), ['10']) + ae(f(None, {'supervisor.username': 'grouplead2', + 'supervisor.supervisor.username': 'ceo'}, ('+','username'), + limit=4, offset=3), []) + ae(f(None, {'supervisor.supervisor': '3', 'supervisor': '4'}, + ('+','username'), limit=1, offset=5), []) + def testFilteringTransitiveLinkSort(self): ae, filter, filter_iter = self.filteringSetupTransitiveSearch() ae, ufilter, ufilter_iter = self.iterSetup('user')