turbogears.paginate

1 2 import types 3 from math import ceil 4 import logging 5 6 import cherrypy 7 try: 8 import sqlobject 9 except ImportError: 10 sqlobject = None 11 12 try: 13 import sqlalchemy 14 try: 15 from sqlalchemy.exc import InvalidRequestError 16 sqlalchemy_before_0_5 = False 17 except ImportError: # SQLAlchemy < 0.5 18 from sqlalchemy.exceptions import InvalidRequestError 19 sqlalchemy_before_0_5 = True 20 except ImportError: 21 sqlalchemy = None 22 23 import turbogears 24 from turbogears.controllers import redirect 25 from turbogears.decorator import weak_signature_decorator 26 from turbogears.view import variable_providers 27 from formencode.variabledecode import variable_encode 28 from turbogears.util import add_tg_args 29 30 log = logging.getLogger('turbogears.paginate') 31 32 33 # lists of databases that lack support for OFFSET 34 # this will need to be updated periodically as modules change 35 _so_no_offset = 'mssql maxdb sybase'.split() 36 _sa_no_offset = 'mssql maxdb access'.split() 37 38 # this is a global that is set the first time paginate() is called 39 _simulate_offset = None 40 41 # these are helper classes for getting data that has no table column

42 -class attrwrapper:

43 """Helper class for accessing object attributes."""

44 - def __init__(self, name):

45 self.name = name

46 - def __call__(self, obj):

47 for name in self.name.split('.'): 48 obj = getattr(obj, name) 49 return obj

50

51 -class itemwrapper:

52 """Helper class for dictionary access."""

53 - def __init__(self, name):

54 self.name = name

55 - def __call__(self, obj):

56 return obj[self.name]

57 58

59 -def paginate(var_name, default_order='', limit=10, 60 max_limit=0, max_pages=5, max_sort=1000, dynamic_limit=None):

61 """The famous TurboGears paginate decorator. 62 63 @param var_name: the variable name that the paginate decorator will try 64 to control. This key must be present in the dictionary returned from your 65 controller in order for the paginate decorator to be able to handle it. 66 @type var_name: string 67 68 @param default_order: The column name(s) that will be used to order 69 pagination results. Due to the way pagination is implemented specifying a 70 default_order will override any result ordering performed in the controller. 71 @type default_order: string or a list of strings. Any string starting with 72 "-" (minus sign) indicates a reverse order for that field/column. 73 74 @param limit: The hard-coded limit that the paginate decorator will impose 75 on the number of "var_name" to display at the same time. This value can be 76 overridden by the use of the dynamic_limit keyword argument. 77 @type limit: integer 78 79 @param max_limit: The maximum number to which the imposed limit can be 80 increased using the "var_name"_tgp_limit keyword argument in the URL. 81 If this is set to 0, no dynamic change at all will be allowed; 82 if it is set to None, any change will be allowed. 83 @type max_limit: int 84 85 @param max_pages: Used to generate the tg.paginate.pages variable. If the 86 page count is larger than max_pages, tg.paginate.pages will only contain 87 the page numbers surrounding the current page at a distance of max_pages/2. 88 A zero value means that all pages will be shown, no matter how much. 89 @type max_pages: integer 90 91 @param max_sort: The maximum number of records that will be sorted in 92 memory if the data cannot be sorted using SQL. If set to 0, sorting in 93 memory will never be performed; if set to None, no limit will be imposed. 94 @type max_sort: integer 95 96 @param dynamic_limit: If specified, this parameter must be the name 97 of a key present in the dictionary returned by your decorated 98 controller. The value found for this key will be used as the limit 99 for our pagination and will override the other settings, the hard-coded 100 one declared in the decorator itself AND the URL parameter one. 101 This enables the programmer to store a limit settings inside the 102 application preferences and then let the user manage it. 103 @type dynamic_limit: string 104 105 """ 106 107 def entangle(func): 108 109 get = turbogears.config.get 110 111 def decorated(func, *args, **kw): 112 113 def kwpop(name, default=None): 114 return kw.pop(var_name + '_tgp_' + name, default)

115 116 page = kwpop('no') 117 if page is None: 118 page = 1 119 elif page == 'last': 120 page = None 121 else: 122 try: 123 page = int(page) 124 if page < 1: 125 raise ValueError 126 except (TypeError, ValueError): 127 page = 1 128 if get('paginate.redirect_on_out_of_range'): 129 cherrypy.request.params[var_name + '_tgp_no'] = page 130 redirect(cherrypy.request.path_info, cherrypy.request.params) 131 132 try: 133 limit_ = int(kwpop('limit')) 134 if max_limit is not None: 135 if max_limit <= 0: 136 raise ValueError 137 limit_ = min(limit_, max_limit) 138 except (TypeError, ValueError): 139 limit_ = limit 140 order = kwpop('order') 141 ordering = kwpop('ordering') 142 143 log.debug("paginate params: page=%s, limit=%s, order=%s", 144 page, limit_, order) 145 146 # get the output from the decorated function 147 output = func(*args, **kw) 148 if not isinstance(output, dict): 149 return output 150 151 try: 152 var_data = output[var_name] 153 except KeyError: 154 raise KeyError("paginate: var_name" 155 " (%s) not found in output dict" % var_name) 156 if not hasattr(var_data, '__getitem__') and callable(var_data): 157 # e.g. SQLAlchemy query class 158 var_data = var_data() 159 if not hasattr(var_data, '__getitem__'): 160 raise TypeError('Paginate variable is not a sequence') 161 162 if dynamic_limit: 163 try: 164 dyn_limit = output[dynamic_limit] 165 except KeyError: 166 raise KeyError("paginate: dynamic_limit" 167 " (%s) not found in output dict" % dynamic_limit) 168 limit_ = dyn_limit 169 170 if ordering: 171 ordering = str(ordering).split(',') 172 else: 173 ordering = default_order or [] 174 if isinstance(ordering, basestring): 175 ordering = [ordering] 176 177 if order: 178 order = str(order) 179 log.debug('paginate: ordering was %s, sort is %s', 180 ordering, order) 181 sort_ordering(ordering, order) 182 log.debug('paginate: ordering is %s', ordering) 183 184 try: 185 row_count = len(var_data) 186 except TypeError: 187 try: # SQL query 188 row_count = var_data.count() or 0 189 except AttributeError: # other iterator 190 var_data = list(var_data) 191 row_count = len(var_data) 192 193 if ordering: 194 var_data = sort_data(var_data, ordering, 195 max_sort is None or 0 < row_count <= max_sort) 196 197 # If limit is zero then return all our rows 198 if not limit_: 199 limit_ = row_count or 1 200 201 page_count = int(ceil(float(row_count)/limit_)) 202 203 if page is None: 204 page = max(page_count, 1) 205 if get('paginate.redirect_on_last_page'): 206 cherrypy.request.params[var_name + '_tgp_no'] = page 207 redirect(cherrypy.request.path_info, cherrypy.request.params) 208 elif page > page_count: 209 page = max(page_count, 1) 210 if get('paginate.redirect_on_out_of_range'): 211 cherrypy.request.params[var_name + '_tgp_no'] = page 212 redirect(cherrypy.request.path_info, cherrypy.request.params) 213 214 offset = (page-1) * limit_ 215 216 pages_to_show = _select_pages_to_show(page, page_count, max_pages) 217 218 # remove pagination parameters from request 219 input_values = variable_encode(cherrypy.request.params.copy()) 220 input_values.pop('self', None) 221 for input_key in input_values.keys(): 222 if input_key.startswith(var_name + '_tgp_'): 223 del input_values[input_key] 224 225 paginate_instance = Paginate( 226 current_page=page, 227 limit=limit_, 228 pages=pages_to_show, 229 page_count=page_count, 230 input_values=input_values, 231 order=order, 232 ordering=ordering, 233 row_count=row_count, 234 var_name=var_name) 235 236 cherrypy.request.paginate = paginate_instance 237 if not hasattr(cherrypy.request, 'paginates'): 238 cherrypy.request.paginates = dict() 239 cherrypy.request.paginates[var_name] = paginate_instance 240 241 # we replace the var with the sliced one 242 endpoint = offset + limit_ 243 log.debug("paginate: slicing data between %d and %d", 244 offset, endpoint) 245 246 global _simulate_offset 247 if _simulate_offset is None: 248 _simulate_offset = get('paginate.simulate_offset', None) 249 if _simulate_offset is None: 250 _simulate_offset = False 251 so_db = get('sqlobject.dburi', 'NOMATCH:').split(':', 1)[0] 252 sa_db = get('sqlalchemy.dburi', 'NOMATCH:').split(':', 1)[0] 253 if so_db in _so_no_offset or sa_db in _sa_no_offset: 254 _simulate_offset = True 255 log.warning("paginate: simulating OFFSET," 256 " paginate may be slow" 257 " (disable with paginate.simulate_offset=False)") 258 259 if _simulate_offset: 260 var_data = iter(var_data[:endpoint]) 261 # skip over the number of records specified by offset 262 for i in xrange(offset): 263 var_data.next() 264 # return the records that remain 265 output[var_name] = list(var_data) 266 else: 267 try: 268 output[var_name] = var_data[offset:endpoint] 269 except TypeError: 270 for i in xrange(offset): 271 var_data.next() 272 output[var_name] = [var_data.next() 273 for i in xrange(offset, endpoint)] 274 275 return output 276 277 if not get('tg.strict_parameters', False): 278 # add hint that paginate parameters shall be left intact 279 add_tg_args(func, (var_name + '_tgp_' + arg 280 for arg in ('no', 'limit', 'order', 'ordering'))) 281 return decorated 282 283 return weak_signature_decorator(entangle) 284 285

286 -def _paginate_var_provider(d):

287 """Auxiliary function for providing the paginate variable.""" 288 paginate = getattr(cherrypy.request, 'paginate', None) 289 if paginate: 290 d.update(dict(paginate=paginate)) 291 paginates = getattr(cherrypy.request, 'paginates', None) 292 if paginates: 293 d.update(dict(paginates=paginates))

294 variable_providers.append(_paginate_var_provider) 295 296

297 -class Paginate:

298 """Class for paginate variable provider.""" 299

300 - def __init__(self, current_page, pages, page_count, input_values, 301 limit, order, ordering, row_count, var_name):

302 303 self.var_name = var_name 304 self.pages = pages 305 self.limit = limit 306 self.page_count = page_count 307 self.current_page = current_page 308 self.input_values = input_values 309 self.order = order 310 self.ordering = ordering 311 self.row_count = row_count 312 self.first_item = page_count and ((current_page - 1) * limit + 1) or 0 313 self.last_item = min(current_page * limit, row_count) 314 315 self.reversed = ordering and ordering[0][0] == '-' 316 317 # If ordering is empty, don't add it. 318 input_values = {var_name + '_tgp_limit': limit} 319 if ordering: 320 input_values[var_name + '_tgp_ordering'] = ','.join(ordering) 321 self.input_values.update(input_values) 322 323 if current_page < page_count: 324 self.input_values.update({ 325 var_name + '_tgp_no': current_page + 1, 326 var_name + '_tgp_limit': limit 327 }) 328 self.href_next = turbogears.url( 329 cherrypy.request.path_info, self.input_values) 330 self.input_values.update({ 331 var_name + '_tgp_no': 'last', 332 var_name + '_tgp_limit': limit 333 }) 334 self.href_last = turbogears.url( 335 cherrypy.request.path_info, self.input_values) 336 else: 337 self.href_next = None 338 self.href_last = None 339 340 if current_page > 1: 341 self.input_values.update({ 342 var_name + '_tgp_no': current_page - 1, 343 var_name + '_tgp_limit': limit 344 }) 345 self.href_prev = turbogears.url( 346 cherrypy.request.path_info, self.input_values) 347 self.input_values.update({ 348 var_name + '_tgp_no': 1, 349 var_name + '_tgp_limit': limit 350 }) 351 self.href_first = turbogears.url( 352 cherrypy.request.path_info, self.input_values) 353 else: 354 self.href_prev = None 355 self.href_first = None

356

357 - def get_href(self, page, order=None, reverse_order=None):

358 # Note that reverse_order is not used. It should be cleaned up here 359 # and in the template. I'm not removing it now because I don't want 360 # to break the API. 361 order = order or None 362 input_values = self.input_values.copy() 363 input_values[self.var_name + '_tgp_no'] = page 364 if order: 365 input_values[ self.var_name + '_tgp_order'] = order 366 return turbogears.url('', input_values)

367 368

369 -def _select_pages_to_show(current_page, page_count, max_pages=None):

370 """Auxiliary function for getting the range of pages to show.""" 371 if max_pages is not None and max_pages > 0: 372 start = current_page - (max_pages // 2) - (max_pages % 2) + 1 373 end = start + max_pages - 1 374 if start < 1: 375 start, end = 1, min(page_count, max_pages) 376 elif end > page_count: 377 start, end = max(1, page_count - max_pages + 1), page_count 378 else: 379 start, end = 1, page_count 380 return xrange(start, end + 1)

381 382 383 # Auxiliary functions for dealing with columns and SQL 384

385 -def sort_ordering(ordering, sort_name):

386 """Rearrange ordering based on sort_name.""" 387 try: 388 index = ordering.index(sort_name) 389 except ValueError: 390 try: 391 index = ordering.index('-' + sort_name) 392 except ValueError: 393 ordering.insert(0, sort_name) 394 else: 395 del ordering[index] 396 ordering.insert(0, (index and '-' or '') + sort_name) 397 else: 398 del ordering[index] 399 ordering.insert(0, (not index and '-' or '') + sort_name)

400

401 -def sqlalchemy_get_column(colname, var_data, join_props=None):

402 """Return a column from SQLAlchemy var_data based on colname.""" 403 if sqlalchemy_before_0_5: 404 mapper = var_data.mapper 405 else: 406 mapper = var_data._mapper_zero() 407 propnames = colname.split('.') 408 colname = propnames.pop() 409 props = [] 410 for propname in propnames: 411 try: 412 prop = mapper.get_property(propname) 413 except InvalidRequestError: 414 prop = None 415 if not prop: 416 break 417 if join_props is not None: 418 props.append(prop) 419 mapper = prop.mapper 420 col = getattr(mapper.c, colname, None) 421 if col is not None and props: 422 join_props.append(props) 423 return col

424

425 -def sqlobject_get_column(colname, var_data, join_props=None):

426 """Return a column from SQLObject var_data based on colname.""" 427 return getattr(var_data.sourceClass.q, colname, None)

428

429 -def sql_get_column(colname, var_data):

430 """Return a column from var_data based on colname.""" 431 if sqlalchemy: 432 try: 433 return sqlalchemy_get_column(colname, var_data) 434 except AttributeError: 435 pass 436 if sqlobject: 437 try: 438 return sqlobject_get_column(colname, var_data) 439 except AttributeError: 440 pass 441 raise TypeError('Cannot find columns of paginate variable')

442

443 -def sqlalchemy_order_col(col, descending=False):

444 """Return an SQLAlchemy ordered column for col.""" 445 if descending: 446 return sqlalchemy.sql.desc(col) 447 else: 448 return sqlalchemy.sql.asc(col)

449

450 -def sqlobject_order_col(col, descending=False):

451 """Return an SQLObject ordered column for col.""" 452 if descending: 453 return sqlobject.DESC(col) 454 else: 455 return col

456

457 -def sql_order_col(col, ascending=True):

458 """Return an ordered column for col.""" 459 if sqlalchemy and isinstance(col, sqlalchemy.sql.ColumnElement): 460 return sqlalchemy_order_col(col, ascending) 461 elif sqlobject and isinstance(col, types.InstanceType): 462 # Sadly, there is no better way to check for SQLObject col type 463 return sqlobject_order_col(col, ascending) 464 raise TypeError("Expected Column, but got %s" % type(col))

465

466 -def sqlalchemy_join_props(var_data, join_props):

467 """Return a query where all tables for properties are joined.""" 468 if join_props: 469 for props in join_props: 470 attrs = [] 471 for prop in props: 472 try: 473 attrs.append(prop.class_attribute) 474 except AttributeError: # SQLAlchemy < 0.6 475 try: 476 attrs.append(getattr(prop.parent.class_, prop.key)) 477 except AttributeError: 478 pass 479 if attrs: 480 if sqlalchemy_before_0_5: # old version, gets single argument 481 var_data = var_data.outerjoin(attrs) 482 else: 483 var_data = var_data.outerjoin(*attrs) 484 return var_data

485

486 -def sort_data(data, ordering, in_memory=True):

487 """Sort data based on ordering. 488 489 Tries to sort the data using SQL whenever possible, 490 otherwise sorts the data as list in memory unless in_memory is false. 491 492 """ 493 try: 494 order_by = data.order_by # SQLAlchemy, gets variable argument list 495 if sqlalchemy_before_0_5: 496 old_order_by = order_by # old version, gets single argument 497 order_by = lambda *cols: old_order_by(cols) 498 get_column, order_col = sqlalchemy_get_column, sqlalchemy_order_col 499 except AttributeError: 500 try: 501 orderBy = data.orderBy # SQLObject, gets single argument 502 order_by = lambda *cols: orderBy(cols) 503 get_column, order_col = sqlobject_get_column, sqlobject_order_col 504 except AttributeError: 505 order_by = None 506 order_cols = [] 507 key_cols = [] 508 num_ascending = num_descending = 0 509 join_props = [] 510 for order in ordering: 511 if order[0] == '-': 512 order = order[1:] 513 descending = True 514 else: 515 descending = False 516 if order_by: 517 col = get_column(order, data, join_props) 518 if col is not None: 519 order_cols.append(order_col(col, descending)) 520 continue 521 if not order_cols: 522 key_cols.append((order, descending)) 523 if descending: 524 num_descending += 1 525 else: 526 num_ascending += 1 527 if order_by and order_cols: 528 data = order_by(*order_cols) 529 if join_props: 530 data = sqlalchemy_join_props(data, join_props) 531 if key_cols: 532 if in_memory: 533 data = list(data) 534 if not data: 535 return data 536 wrapper = isinstance(data[0], dict) and itemwrapper or attrwrapper 537 keys = [(wrapper(col[0]), col[1]) for col in key_cols] 538 if num_ascending == 0 or num_descending == 0: 539 reverse = num_ascending == 0 540 keys = [key[0] for key in keys] 541 if len(key_cols) == 1: 542 key = keys[0] 543 else: 544 key = lambda row: [key(row) for key in keys] 545 else: 546 reverse = num_descending > num_ascending 547 def reverse_key(key, descending): 548 if reverse == descending: 549 return key 550 else: 551 keys = map(key, data) 552 try: 553 keys = list(set(keys)) 554 except TypeError: # unhashable 555 keys.sort() 556 return lambda row: -keys.index(key(row)) 557 else: 558 keys.sort() 559 keys = dict((k, -n) for n, k in enumerate(keys)) 560 return lambda row: keys[key(row)]

561 keys = [reverse_key(*key) for key in keys] 562 key = lambda row: [key(row) for key in keys] 563 log.debug("paginate: sorting in memory") 564 data.sort(key=key, reverse=reverse) 565 else: 566 log.debug("paginate: sorting in memory not allowed") 567 return data 568

Source Code for Module turbogears.paginate