· 8 years ago · May 29, 2018, 07:02 PM
1# -*- coding: utf-8 -*-
2
3import base64
4import cgi
5import copy
6import csv
7import datetime
8import decimal
9import os
10import shutil
11import sys
12import types
13
14from ._compat import PY2, StringIO, pjoin, exists, hashlib_md5, \
15 integer_types, basestring, iteritems, xrange, implements_iterator, \
16 implements_bool, copyreg, reduce
17from ._globals import DEFAULT, IDENTITY, AND, OR
18from ._gae import Key
19from .exceptions import NotFoundException, NotAuthorizedException
20from .helpers.regex import REGEX_TABLE_DOT_FIELD, REGEX_ALPHANUMERIC, \
21 REGEX_PYTHON_KEYWORDS, REGEX_STORE_PATTERN, REGEX_UPLOAD_PATTERN, \
22 REGEX_CLEANUP_FN, REGEX_VALID_TB_FLD
23from .helpers.classes import Reference, MethodAdder, SQLCallableList, SQLALL, \
24 Serializable, BasicStorage
25from .helpers.methods import list_represent, bar_decode_integer, \
26 bar_decode_string, bar_encode, archive_record, cleanup, \
27 use_common_filters, pluralize
28from .helpers.serializers import serializers
29
30long = integer_types[-1]
31
32DEFAULTLENGTH = {'string': 512, 'password': 512, 'upload': 512, 'text': 2**15,
33 'blob': 2**31}
34
35
36class Row(BasicStorage):
37
38 """
39 A dictionary that lets you do d['a'] as well as d.a
40 this is only used to store a `Row`
41 """
42
43 def __getitem__(self, k):
44 key = str(k)
45 _extra = super(Row, self).get('_extra', None)
46 if _extra is not None:
47 v = _extra.get(key, DEFAULT)
48 if v != DEFAULT:
49 return v
50
51 try:
52 return BasicStorage.__getattribute__(self, key)
53 except AttributeError:
54 pass
55
56 m = REGEX_TABLE_DOT_FIELD.match(key)
57 if m:
58 try:
59 e = super(Row, self).__getitem__(m.group(1))
60 return e[m.group(2)]
61 except (KeyError, TypeError):
62 pass
63 key = m.group(2)
64 try:
65 return super(Row, self).__getitem__(key)
66 except KeyError:
67 pass
68 try:
69 e = super(Row, self).get('__get_lazy_reference__')
70 if e is not None and callable(e):
71 self[key] = e(key)
72 return self[key]
73 except Exception as e:
74 raise e
75
76 raise KeyError
77
78 __str__ = __repr__ = lambda self: '<Row %s>' % self.as_dict(custom_types=[LazySet])
79
80 __int__ = lambda self: self.get('id')
81
82 __long__ = lambda self: long(self.get('id'))
83
84 __call__ = __getitem__
85
86 def __getattr__(self, k):
87 try:
88 return self.__getitem__(k)
89 except KeyError:
90 raise AttributeError
91
92 def __copy__(self):
93 return Row(self)
94
95 def __eq__(self, other):
96 try:
97 return self.as_dict() == other.as_dict()
98 except AttributeError:
99 return False
100
101 def get(self, key, default=None):
102 try:
103 return self.__getitem__(key)
104 except(KeyError, AttributeError, TypeError):
105 return default
106
107 def as_dict(self, datetime_to_str=False, custom_types=None):
108 SERIALIZABLE_TYPES = [str, int, float, bool, list, dict]
109 if PY2:
110 SERIALIZABLE_TYPES += [unicode, long]
111 if isinstance(custom_types, (list, tuple, set)):
112 SERIALIZABLE_TYPES += custom_types
113 elif custom_types:
114 SERIALIZABLE_TYPES.append(custom_types)
115 d = dict(self)
116 for k in list(d.keys()):
117 v = d[k]
118 if d[k] is None:
119 continue
120 elif isinstance(v, Row):
121 d[k] = v.as_dict()
122 elif isinstance(v, Reference):
123 d[k] = long(v)
124 elif isinstance(v, decimal.Decimal):
125 d[k] = float(v)
126 elif isinstance(v, (datetime.date, datetime.datetime, datetime.time)):
127 if datetime_to_str:
128 d[k] = v.isoformat().replace('T', ' ')[:19]
129 elif not isinstance(v, tuple(SERIALIZABLE_TYPES)):
130 del d[k]
131 return d
132
133 def as_xml(self, row_name="row", colnames=None, indent=' '):
134 def f(row, field, indent=' '):
135 if isinstance(row, Row):
136 spc = indent+' \n'
137 items = [f(row[x], x, indent+' ') for x in row]
138 return '%s<%s>\n%s\n%s</%s>' % (
139 indent,
140 field,
141 spc.join(item for item in items if item),
142 indent,
143 field)
144 elif not callable(row):
145 if REGEX_ALPHANUMERIC.match(field):
146 return '%s<%s>%s</%s>' % (indent, field, row, field)
147 else:
148 return '%s<extra name="%s">%s</extra>' % \
149 (indent, field, row)
150 else:
151 return None
152 return f(self, row_name, indent=indent)
153
154 def as_json(self, mode="object", default=None, colnames=None,
155 serialize=True, **kwargs):
156 """
157 serializes the row to a JSON object
158 kwargs are passed to .as_dict method
159 only "object" mode supported
160
161 `serialize = False` used by Rows.as_json
162
163 TODO: return array mode with query column order
164
165 mode and colnames are not implemented
166 """
167
168 item = self.as_dict(**kwargs)
169 if serialize:
170 return serializers.json(item)
171 else:
172 return item
173
174
175def pickle_row(s):
176 return Row, (dict(s), )
177
178copyreg.pickle(Row, pickle_row)
179
180
181class Table(Serializable, BasicStorage):
182
183 """
184 Represents a database table
185
186 Example::
187 You can create a table as::
188 db = DAL(...)
189 db.define_table('users', Field('name'))
190
191 And then::
192
193 db.users.insert(name='me') # print db.users._insert(...) to see SQL
194 db.users.drop()
195
196 """
197
198 def __init__(
199 self,
200 db,
201 tablename,
202 *fields,
203 **args):
204 """
205 Initializes the table and performs checking on the provided fields.
206
207 Each table will have automatically an 'id'.
208
209 If a field is of type Table, the fields (excluding 'id') from that table
210 will be used instead.
211
212 Raises:
213 SyntaxError: when a supplied field is of incorrect type.
214 """
215 # import DAL here to avoid circular imports
216 from .base import DAL
217 super(Table, self).__init__()
218 self._actual = False # set to True by define_table()
219 self._db = db
220 self._tablename = tablename
221 if (not isinstance(tablename, str) or hasattr(DAL, tablename)
222 or not REGEX_VALID_TB_FLD.match(tablename)
223 or REGEX_PYTHON_KEYWORDS.match(tablename)
224 ):
225 raise SyntaxError('Field: invalid table name: %s, '
226 'use rname for "funny" names' % tablename)
227 self._ot = None
228 self._rname = args.get('rname')
229 self._sequence_name = (args.get('sequence_name') or
230 db and db._adapter.sequence_name(self._rname
231 or tablename))
232 self._trigger_name = (args.get('trigger_name') or
233 db and db._adapter.trigger_name(tablename))
234 self._common_filter = args.get('common_filter')
235 self._format = args.get('format')
236 self._singular = args.get(
237 'singular', tablename.replace('_', ' ').capitalize())
238 self._plural = args.get(
239 'plural', pluralize(self._singular.lower()).capitalize())
240 # horrible but for backard compatibility of appamdin:
241 if 'primarykey' in args and args['primarykey'] is not None:
242 self._primarykey = args.get('primarykey')
243
244 self._before_insert = []
245 self._before_update = [Set.delete_uploaded_files]
246 self._before_delete = [Set.delete_uploaded_files]
247 self._after_insert = []
248 self._after_update = []
249 self._after_delete = []
250
251 self._virtual_fields = []
252 self._virtual_methods = []
253
254 self.add_method = MethodAdder(self)
255
256 fieldnames, newfields = set(), []
257 _primarykey = getattr(self, '_primarykey', None)
258 if _primarykey is not None:
259 if not isinstance(_primarykey, list):
260 raise SyntaxError(
261 "primarykey must be a list of fields from table '%s'"
262 % tablename)
263 if len(_primarykey) == 1:
264 self._id = [f for f in fields if isinstance(f, Field)
265 and f.name == _primarykey[0]][0]
266 elif not [f for f in fields if (isinstance(f, Field) and
267 f.type == 'id') or (isinstance(f, dict) and
268 f.get("type", None) == "id")]:
269 field = Field('id', 'id')
270 newfields.append(field)
271 fieldnames.add('id')
272 self._id = field
273
274 virtual_fields = []
275
276 def include_new(field):
277 newfields.append(field)
278 fieldnames.add(field.name)
279 if field.type == 'id':
280 self._id = field
281 for field in fields:
282 if isinstance(field, (FieldVirtual, FieldMethod)):
283 virtual_fields.append(field)
284 elif isinstance(field, Field) and field.name not in fieldnames:
285 if field.db is not None:
286 field = copy.copy(field)
287 include_new(field)
288 elif isinstance(field, Table):
289 table = field
290 for field in table:
291 if field.name not in fieldnames and field.type != 'id':
292 t2 = not table._actual and self._tablename
293 include_new(field.clone(point_self_references_to=t2))
294 elif isinstance(field, dict) and field['fieldname'] not in fieldnames:
295 include_new(Field(**field))
296 elif not isinstance(field, (Field, Table)):
297 raise SyntaxError(
298 'define_table argument is not a Field or Table: %s' %
299 field
300 )
301 fields = newfields
302 tablename = tablename
303 self._fields = SQLCallableList()
304 self.virtualfields = []
305 fields = list(fields)
306
307 if db and db._adapter.uploads_in_blob is True:
308 uploadfields = [f.name for f in fields if f.type == 'blob']
309 for field in fields:
310 fn = field.uploadfield
311 if isinstance(field, Field) and field.type == 'upload'\
312 and fn is True and not field.uploadfs:
313 fn = field.uploadfield = '%s_blob' % field.name
314 if isinstance(fn, str) and fn not in uploadfields and not field.uploadfs:
315 fields.append(Field(fn, 'blob', default='',
316 writable=False, readable=False))
317
318 fieldnames_set = set()
319 reserved = dir(Table) + ['fields']
320 if (db and db.check_reserved):
321 check_reserved = db.check_reserved_keyword
322 else:
323 def check_reserved(field_name):
324 if field_name in reserved:
325 raise SyntaxError("field name %s not allowed" % field_name)
326 for field in fields:
327 field_name = field.name
328 check_reserved(field_name)
329 if db and db._ignore_field_case:
330 fname_item = field_name.lower()
331 else:
332 fname_item = field_name
333 if fname_item in fieldnames_set:
334 raise SyntaxError("duplicate field %s in table %s" % (field_name, tablename))
335 else:
336 fieldnames_set.add(fname_item)
337
338 self.fields.append(field_name)
339 self[field_name] = field
340 if field.type == 'id':
341 self['id'] = field
342 field.tablename = field._tablename = tablename
343 field.table = field._table = self
344 field.db = field._db = db
345 self.ALL = SQLALL(self)
346
347 if _primarykey is not None:
348 for k in _primarykey:
349 if k not in self.fields:
350 raise SyntaxError("primarykey must be a list of fields from table '%s " % tablename)
351 else:
352 self[k].notnull = True
353 for field in virtual_fields:
354 self[field.name] = field
355
356 @property
357 def fields(self):
358 return self._fields
359
360 def update(self, *args, **kwargs):
361 raise RuntimeError("Syntax Not Supported")
362
363 def _enable_record_versioning(self,
364 archive_db=None,
365 archive_name='%(tablename)s_archive',
366 is_active='is_active',
367 current_record='current_record',
368 current_record_label=None):
369 db = self._db
370 archive_db = archive_db or db
371 archive_name = archive_name % dict(tablename=self._tablename)
372 if archive_name in archive_db.tables():
373 return # do not try define the archive if already exists
374 fieldnames = self.fields()
375 same_db = archive_db is db
376 field_type = self if same_db else 'bigint'
377 clones = []
378 for field in self:
379 nfk = same_db or not field.type.startswith('reference')
380 clones.append(
381 field.clone(unique=False, type=field.type if nfk else 'bigint')
382 )
383 archive_db.define_table(
384 archive_name,
385 Field(current_record, field_type, label=current_record_label),
386 *clones, **dict(format=self._format))
387
388 self._before_update.append(
389 lambda qset, fs, db=archive_db, an=archive_name, cn=current_record:
390 archive_record(qset, fs, db[an], cn))
391 if is_active and is_active in fieldnames:
392 self._before_delete.append(
393 lambda qset: qset.update(is_active=False))
394 newquery = lambda query, t=self, name=self._tablename: \
395 reduce(AND, [db[tn].is_active == True
396 for tn in db._adapter.tables(query)
397 if tn == name or getattr(db[tn], '_ot', None) == name])
398 query = self._common_filter
399 if query:
400 self._common_filter = lambda q: reduce(AND, [query(q), newquery(q)])
401 else:
402 self._common_filter = newquery
403
404 def _validate(self, **vars):
405 errors = Row()
406 for key, value in iteritems(vars):
407 value, error = self[key].validate(value)
408 if error:
409 errors[key] = error
410 return errors
411
412 def _create_references(self):
413 db = self._db
414 pr = db._pending_references
415 self._referenced_by_list = []
416 self._referenced_by = []
417 self._references = []
418 for field in self:
419 # fieldname = field.name #FIXME not used ?
420 field_type = field.type
421 if isinstance(field_type, str) and (
422 field_type.startswith('reference ') or
423 field_type.startswith('list:reference ')):
424
425 is_list = field_type[:15] == 'list:reference '
426 if is_list:
427 ref = field_type[15:].strip()
428 else:
429 ref = field_type[10:].strip()
430
431 if not ref:
432 SyntaxError('Table: reference to nothing: %s' % ref)
433 if '.' in ref:
434 rtablename, throw_it, rfieldname = ref.partition('.')
435 else:
436 rtablename, rfieldname = ref, None
437 if not rtablename in db:
438 pr[rtablename] = pr.get(rtablename, []) + [field]
439 continue
440 rtable = db[rtablename]
441 if rfieldname:
442 if not hasattr(rtable, '_primarykey'):
443 raise SyntaxError(
444 'keyed tables can only reference other keyed tables (for now)')
445 if rfieldname not in rtable.fields:
446 raise SyntaxError(
447 "invalid field '%s' for referenced table '%s'"
448 " in table '%s'" % (rfieldname, rtablename, self._tablename)
449 )
450 rfield = rtable[rfieldname]
451 else:
452 rfield = rtable._id
453 if is_list:
454 rtable._referenced_by_list.append(field)
455 else:
456 rtable._referenced_by.append(field)
457 field.referent = rfield
458 self._references.append(field)
459 else:
460 field.referent = None
461 if self._tablename in pr:
462 referees = pr.pop(self._tablename)
463 for referee in referees:
464 if referee.type.startswith('list:reference '):
465 self._referenced_by_list.append(referee)
466 else:
467 self._referenced_by.append(referee)
468
469 def _filter_fields(self, record, id=False):
470 return dict([(k, v) for (k, v) in iteritems(record) if k
471 in self.fields and (self[k].type != 'id' or id)])
472
473 def _build_query(self, key):
474 """ for keyed table only """
475 query = None
476 for k, v in iteritems(key):
477 if k in self._primarykey:
478 if query:
479 query = query & (self[k] == v)
480 else:
481 query = (self[k] == v)
482 else:
483 raise SyntaxError('Field %s is not part of the primary key of %s' % (k, self._tablename))
484 return query
485
486 def __getitem__(self, key):
487 if not key:
488 return None
489 elif isinstance(key, dict):
490 """ for keyed table """
491 query = self._build_query(key)
492 return self._db(query).select(
493 limitby=(0, 1),
494 orderby_on_limitby=False
495 ).first()
496 else:
497 try:
498 isgoogle = 'google' in self._db._drivers_available and isinstance(key, Key)
499 except:
500 isgoogle = False
501 if str(key).isdigit() or isgoogle:
502 return self._db(self._id == key).select(
503 limitby=(0, 1),
504 orderby_on_limitby=False
505 ).first()
506 else:
507 try:
508 return getattr(self, key)
509 except:
510 raise KeyError(key)
511
512 def __call__(self, key=DEFAULT, **kwargs):
513 for_update = kwargs.get('_for_update', False)
514 if '_for_update' in kwargs:
515 del kwargs['_for_update']
516
517 orderby = kwargs.get('_orderby', None)
518 if '_orderby' in kwargs:
519 del kwargs['_orderby']
520
521 if key is not DEFAULT:
522 if isinstance(key, Query):
523 record = self._db(key).select(
524 limitby=(0, 1),
525 for_update=for_update,
526 orderby=orderby,
527 orderby_on_limitby=False).first()
528 elif not str(key).isdigit():
529 record = None
530 else:
531 record = self._db(self._id == key).select(
532 limitby=(0, 1),
533 for_update=for_update,
534 orderby=orderby,
535 orderby_on_limitby=False).first()
536 if record:
537 for k, v in iteritems(kwargs):
538 if record[k] != v:
539 return None
540 return record
541 elif kwargs:
542 query = reduce(lambda a, b: a & b, [self[k] == v for k, v in iteritems(kwargs)])
543 return self._db(query).select(limitby=(0, 1),
544 for_update=for_update,
545 orderby=orderby,
546 orderby_on_limitby=False).first()
547 else:
548 return None
549
550 def __setitem__(self, key, value):
551 if isinstance(key, dict) and isinstance(value, dict):
552 """ option for keyed table """
553 if set(key.keys()) == set(self._primarykey):
554 value = self._filter_fields(value)
555 kv = {}
556 kv.update(value)
557 kv.update(key)
558 if not self.insert(**kv):
559 query = self._build_query(key)
560 self._db(query).update(**self._filter_fields(value))
561 else:
562 raise SyntaxError('key must have all fields from primary key: %s' % self._primarykey)
563 elif str(key).isdigit():
564 if key == 0:
565 self.insert(**self._filter_fields(value))
566 elif self._db(self._id == key)\
567 .update(**self._filter_fields(value)) is None:
568 raise SyntaxError('No such record: %s' % key)
569 else:
570 if isinstance(key, dict):
571 raise SyntaxError(
572 'value must be a dictionary: %s' % value)
573 self.__dict__[str(key)] = value
574 if isinstance(value, (FieldVirtual, FieldMethod)):
575 if value.name == 'unknown':
576 value.name = str(key)
577 if isinstance(value, FieldVirtual):
578 self._virtual_fields.append(value)
579 else:
580 self._virtual_methods.append(value)
581
582 def __setattr__(self, key, value):
583 if key[:1] != '_' and key in self:
584 raise SyntaxError('Object exists and cannot be redefined: %s' % key)
585 self[key] = value
586
587 def __delitem__(self, key):
588 if isinstance(key, dict):
589 query = self._build_query(key)
590 if not self._db(query).delete():
591 raise SyntaxError('No such record: %s' % key)
592 elif not str(key).isdigit() or \
593 not self._db(self._id == key).delete():
594 raise SyntaxError('No such record: %s' % key)
595
596 def __iter__(self):
597 for fieldname in self.fields:
598 yield self[fieldname]
599
600 def __repr__(self):
601 return '<Table %s (%s)>' % (self._tablename, ', '.join(self.fields()))
602
603 def __str__(self):
604 if self._ot is not None:
605 ot = self._ot
606 if 'Oracle' in str(type(self._db._adapter)):
607 return '%s %s' % (ot, self._tablename)
608 return '%s AS %s' % (ot, self._tablename)
609
610 return self._tablename
611
612 @property
613 def sqlsafe(self):
614 rname = self._rname
615 if rname:
616 return rname
617 return self._db._adapter.sqlsafe_table(self._tablename)
618
619 @property
620 def sqlsafe_alias(self):
621 rname = self._rname
622 ot = self._ot
623 if rname and not ot:
624 return rname
625 return self._db._adapter.sqlsafe_table(self._tablename, self._ot)
626
627 def _drop(self, mode=''):
628 return self._db._adapter._drop(self, mode)
629
630 def drop(self, mode=''):
631 return self._db._adapter.drop(self, mode)
632
633 def _listify(self, fields, update=False):
634 new_fields = {} # format: new_fields[name] = (field, value)
635
636 # store all fields passed as input in new_fields
637 for name in fields:
638 if name not in self.fields:
639 if name != 'id':
640 raise SyntaxError(
641 'Field %s does not belong to the table' % name)
642 else:
643 field = self[name]
644 value = fields[name]
645 if field.filter_in:
646 value = field.filter_in(value)
647 new_fields[name] = (field, value)
648
649 # check all fields that should be in the table but are not passed
650 to_compute = []
651 for ofield in self:
652 name = ofield.name
653 if name not in new_fields:
654 # if field is supposed to be computed, compute it!
655 if ofield.compute: # save those to compute for later
656 to_compute.append((name, ofield))
657 # if field is required, check its default value
658 elif not update and ofield.default is not None:
659 value = ofield.default
660 fields[name] = value
661 new_fields[name] = (ofield, value)
662 # if this is an update, user the update field instead
663 elif update and ofield.update is not None:
664 value = ofield.update
665 fields[name] = value
666 new_fields[name] = (ofield, value)
667 # if the field is still not there but it should, error
668 elif not update and ofield.required:
669 raise RuntimeError('Table: missing required field: %s' % name)
670 # now deal with fields that are supposed to be computed
671 if to_compute:
672 row = Row(fields)
673 for name, ofield in to_compute:
674 # try compute it
675 try:
676 row[name] = new_value = ofield.compute(row)
677 new_fields[name] = (ofield, new_value)
678 except (KeyError, AttributeError):
679 # error silently unless field is required!
680 if ofield.required:
681 raise SyntaxError('unable to compute field: %s' % name)
682 elif ofield.default is not None:
683 row[name] = new_value = ofield.default
684 new_fields[name] = (ofield, new_value)
685 return list(new_fields.values())
686
687 def _attempt_upload(self, fields):
688 for field in self:
689 if field.type == 'upload' and field.name in fields:
690 value = fields[field.name]
691 if not (value is None or isinstance(value, str)):
692 if hasattr(value, 'file') and hasattr(value, 'filename'):
693 new_name = field.store(value.file, filename=value.filename)
694 elif isinstance(value, dict):
695 if 'data' in value and 'filename' in value:
696 stream = StringIO(value['data'])
697 new_name = field.store(stream, filename=value['filename'])
698 else:
699 new_name = None
700 elif hasattr(value, 'read') and hasattr(value, 'name'):
701 new_name = field.store(value, filename=value.name)
702 else:
703 raise RuntimeError("Unable to handle upload")
704 fields[field.name] = new_name
705
706 def _defaults(self, fields):
707 """If there are no fields/values specified, return table defaults"""
708 fields = copy.copy(fields)
709 for field in self:
710 if (field.name not in fields and
711 field.type != "id" and
712 field.compute is None and
713 field.default is not None):
714 fields[field.name] = field.default
715 return fields
716
717 def _insert(self, **fields):
718 fields = self._defaults(fields)
719 return self._db._adapter._insert(self, self._listify(fields))
720
721 def insert(self, **fields):
722 fields = self._defaults(fields)
723 self._attempt_upload(fields)
724 if any(f(fields) for f in self._before_insert):
725 return 0
726 ret = self._db._adapter.insert(self, self._listify(fields))
727 if ret and self._after_insert:
728 fields = Row(fields)
729 [f(fields, ret) for f in self._after_insert]
730 return ret
731
732 def _validate_fields(self, fields, defattr='default'):
733 response = Row()
734 response.id, response.errors = None, Row()
735 new_fields = copy.copy(fields)
736 for fieldname in self.fields:
737 default = getattr(self[fieldname], defattr)
738 if callable(default):
739 default = default()
740 raw_value = fields.get(fieldname, default)
741 value, error = self[fieldname].validate(raw_value)
742 if error:
743 response.errors[fieldname] = "%s" % error
744 elif value is not None:
745 new_fields[fieldname] = value
746 return response, new_fields
747
748 def validate_and_insert(self, **fields):
749 response, new_fields = self._validate_fields(fields)
750 if not response.errors:
751 response.id = self.insert(**new_fields)
752 return response
753
754 def validate_and_update(self, _key=DEFAULT, **fields):
755 response, new_fields = self._validate_fields(fields, 'update')
756 #: select record(s) for update
757 if _key is DEFAULT:
758 record = self(**fields)
759 elif isinstance(_key, dict):
760 record = self(**_key)
761 else:
762 record = self(_key)
763 #: do the update
764 if not response.errors and record:
765 if '_id' in self:
766 myset = self._db(self._id == record[self._id.name])
767 else:
768 query = None
769 for key, value in iteritems(_key):
770 if query is None:
771 query = getattr(self, key) == value
772 else:
773 query = query & (getattr(self, key) == value)
774 myset = self._db(query)
775 response.id = myset.update(**new_fields)
776 return response
777
778 def update_or_insert(self, _key=DEFAULT, **values):
779 if _key is DEFAULT:
780 record = self(**values)
781 elif isinstance(_key, dict):
782 record = self(**_key)
783 else:
784 record = self(_key)
785 if record:
786 record.update_record(**values)
787 newid = None
788 else:
789 newid = self.insert(**values)
790 return newid
791
792 def validate_and_update_or_insert(self, _key=DEFAULT, **fields):
793 if _key is DEFAULT or _key == '':
794 primary_keys = {}
795 for key, value in iteritems(fields):
796 if key in self._primarykey:
797 primary_keys[key] = value
798 if primary_keys != {}:
799 record = self(**primary_keys)
800 _key = primary_keys
801 else:
802 required_keys = {}
803 for key, value in iteritems(fields):
804 if getattr(self, key).required:
805 required_keys[key] = value
806 record = self(**required_keys)
807 _key = required_keys
808 elif isinstance(_key, dict):
809 record = self(**_key)
810 else:
811 record = self(_key)
812
813 if record:
814 response = self.validate_and_update(_key, **fields)
815 if hasattr(self, '_primarykey'):
816 primary_keys = {}
817 for key in self._primarykey:
818 primary_keys[key] = getattr(record, key)
819 response.id = primary_keys
820 else:
821 response = self.validate_and_insert(**fields)
822 return response
823
824 def bulk_insert(self, items):
825 """
826 here items is a list of dictionaries
827 """
828 listify_items = [self._listify(item) for item in items]
829 if any(f(item) for item in items for f in self._before_insert):return 0
830 ret = self._db._adapter.bulk_insert(self, listify_items)
831 ret and [[f(item, ret[k]) for k, item in enumerate(items)] for f in self._after_insert]
832 return ret
833
834 def _truncate(self, mode=None):
835 return self._db._adapter._truncate(self, mode)
836
837 def truncate(self, mode=None):
838 return self._db._adapter.truncate(self, mode)
839
840 def import_from_csv_file(self,
841 csvfile,
842 id_map=None,
843 null='<NULL>',
844 unique='uuid',
845 id_offset=None, # id_offset used only when id_map is None
846 *args, **kwargs
847 ):
848 """
849 Import records from csv file.
850 Column headers must have same names as table fields.
851 Field 'id' is ignored.
852 If column names read 'table.file' the 'table.' prefix is ignored.
853
854 - 'unique' argument is a field which must be unique (typically a
855 uuid field)
856 - 'restore' argument is default False; if set True will remove old values
857 in table first.
858 - 'id_map' if set to None will not map ids
859
860 The import will keep the id numbers in the restored table.
861 This assumes that there is an field of type id that is integer and in
862 incrementing order.
863 Will keep the id numbers in restored table.
864 """
865
866 delimiter = kwargs.get('delimiter', ',')
867 quotechar = kwargs.get('quotechar', '"')
868 quoting = kwargs.get('quoting', csv.QUOTE_MINIMAL)
869 restore = kwargs.get('restore', False)
870 if restore:
871 self._db[self].truncate()
872
873 reader = csv.reader(csvfile, delimiter=delimiter,
874 quotechar=quotechar, quoting=quoting)
875 colnames = None
876 if isinstance(id_map, dict):
877 if self._tablename not in id_map:
878 id_map[self._tablename] = {}
879 id_map_self = id_map[self._tablename]
880
881 def fix(field, value, id_map, id_offset):
882 list_reference_s = 'list:reference'
883 if value == null:
884 value = None
885 elif field.type == 'blob':
886 value = base64.b64decode(value)
887 elif field.type == 'double' or field.type == 'float':
888 if not value.strip():
889 value = None
890 else:
891 value = float(value)
892 elif field.type in ('integer', 'bigint'):
893 if not value.strip():
894 value = None
895 else:
896 value = long(value)
897 elif field.type.startswith('list:string'):
898 value = bar_decode_string(value)
899 elif field.type.startswith(list_reference_s):
900 ref_table = field.type[len(list_reference_s):].strip()
901 if id_map is not None:
902 value = [id_map[ref_table][long(v)] \
903 for v in bar_decode_string(value)]
904 else:
905 value = [v for v in bar_decode_string(value)]
906 elif field.type.startswith('list:'):
907 value = bar_decode_integer(value)
908 elif id_map and field.type.startswith('reference'):
909 try:
910 value = id_map[field.type[9:].strip()][long(value)]
911 except KeyError:
912 pass
913 elif id_offset and field.type.startswith('reference'):
914 try:
915 value = id_offset[field.type[9:].strip()]+long(value)
916 except KeyError:
917 pass
918 return (field.name, value)
919
920 def is_id(colname):
921 if colname in self:
922 return self[colname].type == 'id'
923 else:
924 return False
925
926 first = True
927 unique_idx = None
928 for lineno, line in enumerate(reader):
929 if not line:
930 break
931 if not colnames:
932 # assume this is the first line of the input, contains colnames
933 colnames = [x.split('.', 1)[-1] for x in line][:len(line)]
934 cols, cid = [], None
935 for i, colname in enumerate(colnames):
936 if is_id(colname):
937 cid = i
938 elif colname in self.fields:
939 cols.append((i, self[colname]))
940 if colname == unique:
941 unique_idx = i
942 else:
943 # every other line contains instead data
944 items = []
945 for i, field in cols:
946 try:
947 items.append(fix(field, line[i], id_map, id_offset))
948 except ValueError:
949 raise RuntimeError("Unable to parse line:%s field:%s value:'%s'"
950 % (lineno+1, field, line[i]))
951
952 if not (id_map or cid is None or id_offset is None or unique_idx):
953 csv_id = long(line[cid])
954 curr_id = self.insert(**dict(items))
955 if first:
956 first = False
957 # First curr_id is bigger than csv_id,
958 # then we are not restoring but
959 # extending db table with csv db table
960 id_offset[self._tablename] = (curr_id-csv_id) \
961 if curr_id > csv_id else 0
962 # create new id until we get the same as old_id+offset
963 while curr_id < csv_id+id_offset[self._tablename]:
964 self._db(self._db[self][colnames[cid]] == curr_id).delete()
965 curr_id = self.insert(**dict(items))
966 # Validation. Check for duplicate of 'unique' &,
967 # if present, update instead of insert.
968 elif not unique_idx:
969 new_id = self.insert(**dict(items))
970 else:
971 unique_value = line[unique_idx]
972 query = self._db[self][unique] == unique_value
973 record = self._db(query).select().first()
974 if record:
975 record.update_record(**dict(items))
976 new_id = record[self._id.name]
977 else:
978 new_id = self.insert(**dict(items))
979 if id_map and cid is not None:
980 id_map_self[long(line[cid])] = new_id
981
982 def as_dict(self, flat=False, sanitize=True):
983 table_as_dict = dict(
984 tablename=str(self),
985 fields=[],
986 sequence_name=self._sequence_name,
987 trigger_name=self._trigger_name,
988 common_filter=self._common_filter,
989 format=self._format,
990 singular=self._singular,
991 plural=self._plural)
992
993 for field in self:
994 if (field.readable or field.writable) or (not sanitize):
995 table_as_dict["fields"].append(field.as_dict(
996 flat=flat, sanitize=sanitize))
997 return table_as_dict
998
999 def with_alias(self, alias):
1000 return self._db._adapter.alias(self, alias)
1001
1002 def on(self, query):
1003 return Expression(self._db, self._db._adapter.ON, self, query)
1004
1005
1006class Expression(object):
1007
1008 def __init__(self,
1009 db,
1010 op,
1011 first=None,
1012 second=None,
1013 type=None,
1014 **optional_args
1015 ):
1016
1017 self.db = db
1018 self.op = op
1019 self.first = first
1020 self.second = second
1021 self._table = getattr(first, '_table', None)
1022 ### self._tablename = first._tablename ## CHECK
1023 if not type and first and hasattr(first, 'type'):
1024 self.type = first.type
1025 else:
1026 self.type = type
1027 self.optional_args = optional_args
1028
1029 def sum(self):
1030 db = self.db
1031 return Expression(db, db._adapter.AGGREGATE, self, 'SUM', self.type)
1032
1033 def max(self):
1034 db = self.db
1035 return Expression(db, db._adapter.AGGREGATE, self, 'MAX', self.type)
1036
1037 def min(self):
1038 db = self.db
1039 return Expression(db, db._adapter.AGGREGATE, self, 'MIN', self.type)
1040
1041 def len(self):
1042 db = self.db
1043 return Expression(db, db._adapter.LENGTH, self, None, 'integer')
1044
1045 def avg(self):
1046 db = self.db
1047 return Expression(db, db._adapter.AGGREGATE, self, 'AVG', self.type)
1048
1049 def abs(self):
1050 db = self.db
1051 return Expression(db, db._adapter.AGGREGATE, self, 'ABS', self.type)
1052
1053 def lower(self):
1054 db = self.db
1055 return Expression(db, db._adapter.LOWER, self, None, self.type)
1056
1057 def upper(self):
1058 db = self.db
1059 return Expression(db, db._adapter.UPPER, self, None, self.type)
1060
1061 def replace(self, a, b):
1062 db = self.db
1063 return Expression(db, db._adapter.REPLACE, self, (a, b), self.type)
1064
1065 def year(self):
1066 db = self.db
1067 return Expression(db, db._adapter.EXTRACT, self, 'year', 'integer')
1068
1069 def month(self):
1070 db = self.db
1071 return Expression(db, db._adapter.EXTRACT, self, 'month', 'integer')
1072
1073 def day(self):
1074 db = self.db
1075 return Expression(db, db._adapter.EXTRACT, self, 'day', 'integer')
1076
1077 def hour(self):
1078 db = self.db
1079 return Expression(db, db._adapter.EXTRACT, self, 'hour', 'integer')
1080
1081 def minutes(self):
1082 db = self.db
1083 return Expression(db, db._adapter.EXTRACT, self, 'minute', 'integer')
1084
1085 def coalesce(self, *others):
1086 db = self.db
1087 return Expression(db, db._adapter.COALESCE, self, others, self.type)
1088
1089 def coalesce_zero(self):
1090 db = self.db
1091 return Expression(db, db._adapter.COALESCE_ZERO, self, None, self.type)
1092
1093 def seconds(self):
1094 db = self.db
1095 return Expression(db, db._adapter.EXTRACT, self, 'second', 'integer')
1096
1097 def epoch(self):
1098 db = self.db
1099 return Expression(db, db._adapter.EPOCH, self, None, 'integer')
1100
1101 def __getitem__(self, i):
1102 if isinstance(i, slice):
1103 start = i.start or 0
1104 stop = i.stop
1105
1106 db = self.db
1107 if start < 0:
1108 pos0 = '(%s - %d)' % (self.len(), abs(start) - 1)
1109 else:
1110 pos0 = start + 1
1111
1112 maxint = sys.maxint if PY2 else sys.maxsize
1113 if stop is None or stop == maxint:
1114 length = self.len()
1115 elif stop < 0:
1116 length = '(%s - %d - %s)' % (self.len(), abs(stop) - 1, pos0)
1117 else:
1118 length = '(%s - %s)' % (stop + 1, pos0)
1119
1120 return Expression(db, db._adapter.SUBSTRING,
1121 self, (pos0, length), self.type)
1122 else:
1123 return self[i:i + 1]
1124
1125 def __str__(self):
1126 return str(self.db._adapter.expand(self, self.type))
1127
1128 def __or__(self, other): # for use in sortby
1129 db = self.db
1130 return Expression(db, db._adapter.COMMA, self, other, self.type)
1131
1132 def __invert__(self):
1133 db = self.db
1134 if hasattr(self, '_op') and self.op == db._adapter.INVERT:
1135 return self.first
1136 return Expression(db, db._adapter.INVERT, self, type=self.type)
1137
1138 def __add__(self, other):
1139 db = self.db
1140 return Expression(db, db._adapter.ADD, self, other, self.type)
1141
1142 def __sub__(self, other):
1143 db = self.db
1144 if self.type in ('integer', 'bigint'):
1145 result_type = 'integer'
1146 elif self.type in ['date', 'time', 'datetime', 'double', 'float']:
1147 result_type = 'double'
1148 elif self.type.startswith('decimal('):
1149 result_type = self.type
1150 else:
1151 raise SyntaxError("subtraction operation not supported for type")
1152 return Expression(db, db._adapter.SUB, self, other, result_type)
1153
1154 def __mul__(self, other):
1155 db = self.db
1156 return Expression(db, db._adapter.MUL, self, other, self.type)
1157
1158 def __div__(self, other):
1159 db = self.db
1160 return Expression(db, db._adapter.DIV, self, other, self.type)
1161
1162 def __truediv__(self, other):
1163 return self.__div__(other)
1164
1165 def __mod__(self, other):
1166 db = self.db
1167 return Expression(db, db._adapter.MOD, self, other, self.type)
1168
1169 def __eq__(self, value):
1170 db = self.db
1171 return Query(db, db._adapter.EQ, self, value)
1172
1173 def __ne__(self, value):
1174 db = self.db
1175 return Query(db, db._adapter.NE, self, value)
1176
1177 def __lt__(self, value):
1178 db = self.db
1179 return Query(db, db._adapter.LT, self, value)
1180
1181 def __le__(self, value):
1182 db = self.db
1183 return Query(db, db._adapter.LE, self, value)
1184
1185 def __gt__(self, value):
1186 db = self.db
1187 return Query(db, db._adapter.GT, self, value)
1188
1189 def __ge__(self, value):
1190 db = self.db
1191 return Query(db, db._adapter.GE, self, value)
1192
1193 def like(self, value, case_sensitive=True, escape=None):
1194 db = self.db
1195 op = case_sensitive and db._adapter.LIKE or db._adapter.ILIKE
1196 return Query(db, op, self, value, escape=escape)
1197
1198 def ilike(self, value, escape=None):
1199 return self.like(value, case_sensitive=False, escape=escape)
1200
1201 def regexp(self, value):
1202 db = self.db
1203 return Query(db, db._adapter.REGEXP, self, value)
1204
1205 def belongs(self, *value, **kwattr):
1206 """
1207 Accepts the following inputs::
1208
1209 field.belongs(1, 2)
1210 field.belongs((1, 2))
1211 field.belongs(query)
1212
1213 Does NOT accept:
1214
1215 field.belongs(1)
1216
1217 If the set you want back includes `None` values, you can do::
1218
1219 field.belongs((1, None), null=True)
1220
1221 """
1222 db = self.db
1223 if len(value) == 1:
1224 value = value[0]
1225 if isinstance(value, Query):
1226 value = db(value)._select(value.first._table._id)
1227 elif not isinstance(value, basestring):
1228 value = set(value)
1229 if kwattr.get('null') and None in value:
1230 value.remove(None)
1231 return (self == None) | Query(db, db._adapter.BELONGS, self, value)
1232 return Query(db, db._adapter.BELONGS, self, value)
1233
1234 def startswith(self, value):
1235 db = self.db
1236 if self.type not in ('string', 'text', 'json', 'upload'):
1237 raise SyntaxError("startswith used with incompatible field type")
1238 return Query(db, db._adapter.STARTSWITH, self, value)
1239
1240 def endswith(self, value):
1241 db = self.db
1242 if self.type not in ('string', 'text', 'json', 'upload'):
1243 raise SyntaxError("endswith used with incompatible field type")
1244 return Query(db, db._adapter.ENDSWITH, self, value)
1245
1246 def contains(self, value, all=False, case_sensitive=False):
1247 """
1248 For GAE contains() is always case sensitive
1249 """
1250 db = self.db
1251 if isinstance(value, (list, tuple)):
1252 subqueries = [self.contains(str(v), case_sensitive=case_sensitive)
1253 for v in value if str(v)]
1254 if not subqueries:
1255 return self.contains('')
1256 else:
1257 return reduce(all and AND or OR, subqueries)
1258 if self.type not in ('string', 'text', 'json', 'upload') and not self.type.startswith('list:'):
1259 raise SyntaxError("contains used with incompatible field type")
1260 return Query(db, db._adapter.CONTAINS, self, value, case_sensitive=case_sensitive)
1261
1262 def with_alias(self, alias):
1263 db = self.db
1264 return Expression(db, db._adapter.AS, self, alias, self.type)
1265
1266 # GIS expressions
1267
1268 def st_asgeojson(self, precision=15, options=0, version=1):
1269 return Expression(self.db, self.db._adapter.ST_ASGEOJSON, self,
1270 dict(precision=precision, options=options,
1271 version=version), 'string')
1272
1273 def st_astext(self):
1274 db = self.db
1275 return Expression(db, db._adapter.ST_ASTEXT, self, type='string')
1276
1277 def st_x(self):
1278 db = self.db
1279 return Expression(db, db._adapter.ST_X, self, type='string')
1280
1281 def st_y(self):
1282 db = self.db
1283 return Expression(db, db._adapter.ST_Y, self, type='string')
1284
1285 def st_distance(self, other):
1286 db = self.db
1287 return Expression(db, db._adapter.ST_DISTANCE, self, other, 'double')
1288
1289 def st_simplify(self, value):
1290 db = self.db
1291 return Expression(db, db._adapter.ST_SIMPLIFY, self, value, self.type)
1292
1293 def st_simplifypreservetopology(self, value):
1294 db = self.db
1295 return Expression(db, db._adapter.ST_SIMPLIFYPRESERVETOPOLOGY, self, value, self.type)
1296
1297 # GIS queries
1298
1299 def st_contains(self, value):
1300 db = self.db
1301 return Query(db, db._adapter.ST_CONTAINS, self, value)
1302
1303 def st_equals(self, value):
1304 db = self.db
1305 return Query(db, db._adapter.ST_EQUALS, self, value)
1306
1307 def st_intersects(self, value):
1308 db = self.db
1309 return Query(db, db._adapter.ST_INTERSECTS, self, value)
1310
1311 def st_overlaps(self, value):
1312 db = self.db
1313 return Query(db, db._adapter.ST_OVERLAPS, self, value)
1314
1315 def st_touches(self, value):
1316 db = self.db
1317 return Query(db, db._adapter.ST_TOUCHES, self, value)
1318
1319 def st_within(self, value):
1320 db = self.db
1321 return Query(db, db._adapter.ST_WITHIN, self, value)
1322
1323 def st_dwithin(self, value, distance):
1324 db = self.db
1325 return Query(db, db._adapter.ST_DWITHIN, self, (value, distance))
1326
1327
1328class FieldVirtual(object):
1329 def __init__(self, name, f=None, ftype='string', label=None, table_name=None):
1330 # for backward compatibility
1331 (self.name, self.f) = (name, f) if f else ('unknown', name)
1332 self.type = ftype
1333 self.label = label or self.name.capitalize().replace('_', ' ')
1334 self.represent = lambda v, r=None: v
1335 self.formatter = IDENTITY
1336 self.comment = None
1337 self.readable = True
1338 self.writable = False
1339 self.requires = None
1340 self.widget = None
1341 self.tablename = table_name
1342 self.filter_out = None
1343
1344 def __str__(self):
1345 return '%s.%s' % (self.tablename, self.name)
1346
1347
1348class FieldMethod(object):
1349 def __init__(self, name, f=None, handler=None):
1350 # for backward compatibility
1351 (self.name, self.f) = (name, f) if f else ('unknown', name)
1352 self.handler = handler
1353
1354
1355@implements_bool
1356class Field(Expression, Serializable):
1357
1358 Virtual = FieldVirtual
1359 Method = FieldMethod
1360 Lazy = FieldMethod # for backward compatibility
1361
1362 """
1363 Represents a database field
1364
1365 Example:
1366 Usage::
1367
1368 a = Field(name, 'string', length=32, default=None, required=False,
1369 requires=IS_NOT_EMPTY(), ondelete='CASCADE',
1370 notnull=False, unique=False,
1371 uploadfield=True, widget=None, label=None, comment=None,
1372 uploadfield=True, # True means store on disk,
1373 # 'a_field_name' means store in this field in db
1374 # False means file content will be discarded.
1375 writable=True, readable=True, update=None, authorize=None,
1376 autodelete=False, represent=None, uploadfolder=None,
1377 uploadseparate=False # upload to separate directories by uuid_keys
1378 # first 2 character and tablename.fieldname
1379 # False - old behavior
1380 # True - put uploaded file in
1381 # <uploaddir>/<tablename>.<fieldname>/uuid_key[:2]
1382 # directory)
1383 uploadfs=None # a pyfilesystem where to store upload
1384 )
1385
1386 to be used as argument of `DAL.define_table`
1387
1388 """
1389
1390 def __init__(self,
1391 fieldname,
1392 type='string',
1393 length=None,
1394 default=DEFAULT,
1395 required=False,
1396 requires=DEFAULT,
1397 ondelete='CASCADE',
1398 notnull=False,
1399 unique=False,
1400 uploadfield=True,
1401 widget=None,
1402 label=None,
1403 comment=None,
1404 writable=True,
1405 readable=True,
1406 update=None,
1407 authorize=None,
1408 autodelete=False,
1409 represent=None,
1410 uploadfolder=None,
1411 uploadseparate=False,
1412 uploadfs=None,
1413 compute=None,
1414 custom_store=None,
1415 custom_retrieve=None,
1416 custom_retrieve_file_properties=None,
1417 custom_delete=None,
1418 filter_in=None,
1419 filter_out=None,
1420 custom_qualifier=None,
1421 map_none=None,
1422 rname=None
1423 ):
1424 self._db = self.db = None # both for backward compatibility
1425 self.op = None
1426 self.first = None
1427 self.second = None
1428 if PY2 and isinstance(fieldname, unicode):
1429 try:
1430 fieldname = str(fieldname)
1431 except UnicodeEncodeError:
1432 raise SyntaxError('Field: invalid unicode field name')
1433 self.name = fieldname = cleanup(fieldname)
1434 if (not isinstance(fieldname, str) or hasattr(Table, fieldname) or
1435 not REGEX_VALID_TB_FLD.match(fieldname) or
1436 REGEX_PYTHON_KEYWORDS.match(fieldname)):
1437 raise SyntaxError('Field: invalid field name: %s, '
1438 'use rname for "funny" names' % fieldname)
1439
1440 if not isinstance(type, (Table, Field)):
1441 self.type = type
1442 else:
1443 self.type = 'reference %s' % type
1444
1445 self.length = length if length is not None else DEFAULTLENGTH.get(self.type, 512)
1446 self.default = default if default != DEFAULT else (update or None)
1447 self.required = required # is this field required
1448 self.ondelete = ondelete.upper() # this is for reference fields only
1449 self.notnull = notnull
1450 self.unique = unique
1451 self.uploadfield = uploadfield
1452 self.uploadfolder = uploadfolder
1453 self.uploadseparate = uploadseparate
1454 self.uploadfs = uploadfs
1455 self.widget = widget
1456 self.comment = comment
1457 self.writable = writable
1458 self.readable = readable
1459 self.update = update
1460 self.authorize = authorize
1461 self.autodelete = autodelete
1462 self.represent = list_represent if represent is None and \
1463 type in ('list:integer', 'list:string') else represent
1464 self.compute = compute
1465 self.isattachment = True
1466 self.custom_store = custom_store
1467 self.custom_retrieve = custom_retrieve
1468 self.custom_retrieve_file_properties = custom_retrieve_file_properties
1469 self.custom_delete = custom_delete
1470 self.filter_in = filter_in
1471 self.filter_out = filter_out
1472 self.custom_qualifier = custom_qualifier
1473 self.label = (label if label is not None else
1474 fieldname.replace('_', ' ').title())
1475 self.requires = requires if requires is not None else []
1476 self.map_none = map_none
1477 self._rname = rname
1478
1479 def set_attributes(self, *args, **attributes):
1480 self.__dict__.update(*args, **attributes)
1481
1482 def clone(self, point_self_references_to=False, **args):
1483 field = copy.copy(self)
1484 if point_self_references_to and field.type == 'reference %s'+field._tablename:
1485 field.type = 'reference %s' % point_self_references_to
1486 field.__dict__.update(args)
1487 return field
1488
1489 def store(self, file, filename=None, path=None):
1490 if self.custom_store:
1491 return self.custom_store(file, filename, path)
1492 if isinstance(file, cgi.FieldStorage):
1493 filename = filename or file.filename
1494 file = file.file
1495 elif not filename:
1496 filename = file.name
1497 filename = os.path.basename(filename.replace('/', os.sep).replace('\\', os.sep))
1498 m = REGEX_STORE_PATTERN.search(filename)
1499 extension = m and m.group('e') or 'txt'
1500 uuid_key = self._db.uuid().replace('-', '')[-16:]
1501 encoded_filename = base64.b16encode(filename).lower()
1502 newfilename = '%s.%s.%s.%s' % (self._tablename, self.name, uuid_key, encoded_filename)
1503 newfilename = newfilename[:(self.length - 1 - len(extension))] + '.' + extension
1504 self_uploadfield = self.uploadfield
1505 if isinstance(self_uploadfield, Field):
1506 blob_uploadfield_name = self_uploadfield.uploadfield
1507 keys = {self_uploadfield.name: newfilename,
1508 blob_uploadfield_name: file.read()}
1509 self_uploadfield.table.insert(**keys)
1510 elif self_uploadfield is True:
1511 if path:
1512 pass
1513 elif self.uploadfolder:
1514 path = self.uploadfolder
1515 elif self.db._adapter.folder:
1516 path = pjoin(self.db._adapter.folder, '..', 'uploads')
1517 else:
1518 raise RuntimeError(
1519 "you must specify a Field(..., uploadfolder=...)")
1520 if self.uploadseparate:
1521 if self.uploadfs:
1522 raise RuntimeError("not supported")
1523 path = pjoin(path, "%s.%s" % (
1524 self._tablename, self.name), uuid_key[:2]
1525 )
1526 if not exists(path):
1527 os.makedirs(path)
1528 pathfilename = pjoin(path, newfilename)
1529 if self.uploadfs:
1530 dest_file = self.uploadfs.open(newfilename, 'wb')
1531 else:
1532 dest_file = open(pathfilename, 'wb')
1533 try:
1534 shutil.copyfileobj(file, dest_file)
1535 except IOError:
1536 raise IOError(
1537 'Unable to store file "%s" because invalid permissions, '
1538 'readonly file system, or filename too long' % pathfilename)
1539 dest_file.close()
1540 return newfilename
1541
1542 def retrieve(self, name, path=None, nameonly=False):
1543 """
1544 If `nameonly==True` return (filename, fullfilename) instead of
1545 (filename, stream)
1546 """
1547 self_uploadfield = self.uploadfield
1548 if self.custom_retrieve:
1549 return self.custom_retrieve(name, path)
1550 if self.authorize or isinstance(self_uploadfield, str):
1551 row = self.db(self == name).select().first()
1552 if not row:
1553 raise NotFoundException
1554 if self.authorize and not self.authorize(row):
1555 raise NotAuthorizedException
1556 file_properties = self.retrieve_file_properties(name, path)
1557 filename = file_properties['filename']
1558 if isinstance(self_uploadfield, str): # ## if file is in DB
1559 stream = StringIO(row[self_uploadfield] or '')
1560 elif isinstance(self_uploadfield, Field):
1561 blob_uploadfield_name = self_uploadfield.uploadfield
1562 query = self_uploadfield == name
1563 data = self_uploadfield.table(query)[blob_uploadfield_name]
1564 stream = StringIO(data)
1565 elif self.uploadfs:
1566 # ## if file is on pyfilesystem
1567 stream = self.uploadfs.open(name, 'rb')
1568 else:
1569 # ## if file is on regular filesystem
1570 # this is intentially a sting with filename and not a stream
1571 # this propagates and allows stream_file_or_304_or_206 to be called
1572 fullname = pjoin(file_properties['path'], name)
1573 if nameonly:
1574 return (filename, fullname)
1575 stream = open(fullname, 'rb')
1576 return (filename, stream)
1577
1578 def retrieve_file_properties(self, name, path=None):
1579 m = REGEX_UPLOAD_PATTERN.match(name)
1580 if not m or not self.isattachment:
1581 raise TypeError('Can\'t retrieve %s file properties' % name)
1582 self_uploadfield = self.uploadfield
1583 if self.custom_retrieve_file_properties:
1584 return self.custom_retrieve_file_properties(name, path)
1585 if m.group('name'):
1586 try:
1587 filename = base64.b16decode(m.group('name'), True)
1588 filename = REGEX_CLEANUP_FN.sub('_', filename)
1589 except (TypeError, AttributeError):
1590 filename = name
1591 else:
1592 filename = name
1593 # ## if file is in DB
1594 if isinstance(self_uploadfield, (str, Field)):
1595 return dict(path=None, filename=filename)
1596 # ## if file is on filesystem
1597 if not path:
1598 if self.uploadfolder:
1599 path = self.uploadfolder
1600 else:
1601 path = pjoin(self.db._adapter.folder, '..', 'uploads')
1602 if self.uploadseparate:
1603 t = m.group('table')
1604 f = m.group('field')
1605 u = m.group('uuidkey')
1606 path = pjoin(path, "%s.%s" % (t, f), u[:2])
1607 return dict(path=path, filename=filename)
1608
1609 def formatter(self, value):
1610 requires = self.requires
1611 if value is None:
1612 return self.map_none
1613 if not requires:
1614 return value
1615 if not isinstance(requires, (list, tuple)):
1616 requires = [requires]
1617 elif isinstance(requires, tuple):
1618 requires = list(requires)
1619 else:
1620 requires = copy.copy(requires)
1621 requires.reverse()
1622 for item in requires:
1623 if hasattr(item, 'formatter'):
1624 value = item.formatter(value)
1625 return value
1626
1627 def validate(self, value):
1628 if not self.requires or self.requires == DEFAULT:
1629 return ((value if value != self.map_none else None), None)
1630 requires = self.requires
1631 if not isinstance(requires, (list, tuple)):
1632 requires = [requires]
1633 for validator in requires:
1634 (value, error) = validator(value)
1635 if error:
1636 return (value, error)
1637 return ((value if value != self.map_none else None), None)
1638
1639 def count(self, distinct=None):
1640 return Expression(self.db, self.db._adapter.COUNT, self, distinct, 'integer')
1641
1642 def as_dict(self, flat=False, sanitize=True):
1643 attrs = (
1644 'name', 'authorize', 'represent', 'ondelete',
1645 'custom_store', 'autodelete', 'custom_retrieve',
1646 'filter_out', 'uploadseparate', 'widget', 'uploadfs',
1647 'update', 'custom_delete', 'uploadfield', 'uploadfolder',
1648 'custom_qualifier', 'unique', 'writable', 'compute',
1649 'map_none', 'default', 'type', 'required', 'readable',
1650 'requires', 'comment', 'label', 'length', 'notnull',
1651 'custom_retrieve_file_properties', 'filter_in')
1652 serializable = (int, long, basestring, float, tuple,
1653 bool, type(None))
1654
1655 def flatten(obj):
1656 if isinstance(obj, dict):
1657 return dict((flatten(k), flatten(v)) for k, v in obj.items())
1658 elif isinstance(obj, (tuple, list, set)):
1659 return [flatten(v) for v in obj]
1660 elif isinstance(obj, serializable):
1661 return obj
1662 elif isinstance(obj, (datetime.datetime,
1663 datetime.date, datetime.time)):
1664 return str(obj)
1665 else:
1666 return None
1667
1668 d = dict()
1669 if not (sanitize and not (self.readable or self.writable)):
1670 for attr in attrs:
1671 if flat:
1672 d.update({attr: flatten(getattr(self, attr))})
1673 else:
1674 d.update({attr: getattr(self, attr)})
1675 d["fieldname"] = d.pop("name")
1676 return d
1677
1678 def __bool__(self):
1679 return True
1680
1681 def __str__(self):
1682 try:
1683 return '%s.%s' % (self.tablename, self.name)
1684 except:
1685 return '<no table>.%s' % self.name
1686
1687 @property
1688 def sqlsafe(self):
1689 if self._table:
1690 return self._table.sqlsafe + '.' + \
1691 (self._rname or self._db._adapter.sqlsafe_field(self.name))
1692 return '<no table>.%s' % self.name
1693
1694 @property
1695 def sqlsafe_name(self):
1696 return self._rname or self._db._adapter.sqlsafe_field(self.name)
1697
1698
1699class Query(Serializable):
1700
1701 """
1702 Necessary to define a set.
1703 It can be stored or can be passed to `DAL.__call__()` to obtain a `Set`
1704
1705 Example:
1706 Use as::
1707
1708 query = db.users.name=='Max'
1709 set = db(query)
1710 records = set.select()
1711
1712 """
1713
1714 def __init__(self,
1715 db,
1716 op,
1717 first=None,
1718 second=None,
1719 ignore_common_filters=False,
1720 **optional_args
1721 ):
1722 self.db = self._db = db
1723 self.op = op
1724 self.first = first
1725 self.second = second
1726 self.ignore_common_filters = ignore_common_filters
1727 self.optional_args = optional_args
1728
1729 def __repr__(self):
1730 from .adapters.base import BaseAdapter
1731 return '<Query %s>' % BaseAdapter.expand(self.db._adapter, self)
1732
1733 def __str__(self):
1734 return str(self.db._adapter.expand(self))
1735
1736 def __and__(self, other):
1737 return Query(self.db, self.db._adapter.AND, self, other)
1738
1739 __rand__ = __and__
1740
1741 def __or__(self, other):
1742 return Query(self.db, self.db._adapter.OR, self, other)
1743
1744 __ror__ = __or__
1745
1746 def __invert__(self):
1747 if self.op==self.db._adapter.NOT:
1748 return self.first
1749 return Query(self.db, self.db._adapter.NOT, self)
1750
1751 def __eq__(self, other):
1752 return repr(self) == repr(other)
1753
1754 def __ne__(self, other):
1755 return not (self == other)
1756
1757 def case(self, t=1, f=0):
1758 return self.db._adapter.CASE(self, t, f)
1759
1760 def as_dict(self, flat=False, sanitize=True):
1761 """Experimental stuff
1762
1763 This allows to return a plain dictionary with the basic
1764 query representation. Can be used with json/xml services
1765 for client-side db I/O
1766
1767 Example:
1768 Usage::
1769
1770 q = db.auth_user.id != 0
1771 q.as_dict(flat=True)
1772 {
1773 "op": "NE",
1774 "first":{
1775 "tablename": "auth_user",
1776 "fieldname": "id"
1777 },
1778 "second":0
1779 }
1780 """
1781
1782 SERIALIZABLE_TYPES = (tuple, dict, set, list, int, long, float,
1783 basestring, type(None), bool)
1784
1785 def loop(d):
1786 newd = dict()
1787 for k, v in d.items():
1788 if k in ("first", "second"):
1789 if isinstance(v, self.__class__):
1790 newd[k] = loop(v.__dict__)
1791 elif isinstance(v, Field):
1792 newd[k] = {"tablename": v._tablename,
1793 "fieldname": v.name}
1794 elif isinstance(v, Expression):
1795 newd[k] = loop(v.__dict__)
1796 elif isinstance(v, SERIALIZABLE_TYPES):
1797 newd[k] = v
1798 elif isinstance(v, (datetime.date,
1799 datetime.time,
1800 datetime.datetime)):
1801 newd[k] = unicode(v) if PY2 else str(v)
1802 elif k == "op":
1803 if callable(v):
1804 newd[k] = v.__name__
1805 elif isinstance(v, basestring):
1806 newd[k] = v
1807 else:
1808 pass # not callable or string
1809 elif isinstance(v, SERIALIZABLE_TYPES):
1810 if isinstance(v, dict):
1811 newd[k] = loop(v)
1812 else:
1813 newd[k] = v
1814 return newd
1815
1816 if flat:
1817 return loop(self.__dict__)
1818 else:
1819 return self.__dict__
1820
1821
1822class Set(Serializable):
1823
1824 """
1825 Represents a set of records in the database.
1826 Records are identified by the `query=Query(...)` object.
1827 Normally the Set is generated by `DAL.__call__(Query(...))`
1828
1829 Given a set, for example::
1830
1831 myset = db(db.users.name=='Max')
1832
1833 you can::
1834
1835 myset.update(db.users.name='Massimo')
1836 myset.delete() # all elements in the set
1837 myset.select(orderby=db.users.id, groupby=db.users.name, limitby=(0, 10))
1838
1839 and take subsets:
1840
1841 subset = myset(db.users.id<5)
1842
1843 """
1844
1845 def __init__(self, db, query, ignore_common_filters=None):
1846 self.db = db
1847 self._db = db # for backward compatibility
1848 self.dquery = None
1849
1850 # if query is a dict, parse it
1851 if isinstance(query, dict):
1852 query = self.parse(query)
1853
1854 if ignore_common_filters is not None and use_common_filters(query) == ignore_common_filters:
1855 query = copy.copy(query)
1856 query.ignore_common_filters = ignore_common_filters
1857 self.query = query
1858
1859 def __repr__(self):
1860 from .adapters.base import BaseAdapter
1861 return '<Set %s>' % BaseAdapter.expand(self.db._adapter, self.query)
1862
1863 def __call__(self, query, ignore_common_filters=False):
1864 return self.where(query, ignore_common_filters)
1865
1866 def where(self, query, ignore_common_filters=False):
1867 if query is None:
1868 return self
1869 elif isinstance(query, Table):
1870 query = self.db._adapter.id_query(query)
1871 elif isinstance(query, str):
1872 query = Expression(self.db, query)
1873 elif isinstance(query, Field):
1874 query = query != None
1875 if self.query:
1876 return Set(self.db, self.query & query,
1877 ignore_common_filters=ignore_common_filters)
1878 else:
1879 return Set(self.db, query,
1880 ignore_common_filters=ignore_common_filters)
1881
1882 def _count(self, distinct=None):
1883 return self.db._adapter._count(self.query, distinct)
1884
1885 def _select(self, *fields, **attributes):
1886 adapter = self.db._adapter
1887 tablenames = adapter.tables(self.query,
1888 attributes.get('join', None),
1889 attributes.get('left', None),
1890 attributes.get('orderby', None),
1891 attributes.get('groupby', None))
1892 fields = adapter.expand_all(fields, tablenames)
1893 return adapter._select(self.query, fields, attributes)
1894
1895 def _delete(self):
1896 db = self.db
1897 tablename = db._adapter.get_table(self.query)
1898 return db._adapter._delete(tablename, self.query)
1899
1900 def _update(self, **update_fields):
1901 db = self.db
1902 tablename = db._adapter.get_table(self.query)
1903 fields = db[tablename]._listify(update_fields, update=True)
1904 return db._adapter._update(tablename, self.query, fields)
1905
1906 def as_dict(self, flat=False, sanitize=True):
1907 if flat:
1908 uid = dbname = uri = None
1909 codec = self.db._db_codec
1910 if not sanitize:
1911 uri, dbname, uid = (self.db._dbname, str(self.db),
1912 self.db._db_uid)
1913 d = {"query": self.query.as_dict(flat=flat)}
1914 d["db"] = {"uid": uid, "codec": codec,
1915 "name": dbname, "uri": uri}
1916 return d
1917 else:
1918 return self.__dict__
1919
1920 def parse(self, dquery):
1921 """Experimental: Turn a dictionary into a Query object"""
1922 self.dquery = dquery
1923 return self.build(self.dquery)
1924
1925 def build(self, d):
1926 """Experimental: see .parse()"""
1927 op, first, second = (d["op"], d["first"],
1928 d.get("second", None))
1929 left = right = built = None
1930
1931 if op in ("AND", "OR"):
1932 if not (type(first), type(second)) == (dict, dict):
1933 raise SyntaxError("Invalid AND/OR query")
1934 if op == "AND":
1935 built = self.build(first) & self.build(second)
1936 else: built = self.build(first) | self.build(second)
1937
1938 elif op == "NOT":
1939 if first is None:
1940 raise SyntaxError("Invalid NOT query")
1941 built = ~self.build(first)
1942 else:
1943 # normal operation (GT, EQ, LT, ...)
1944 for k, v in {"left": first, "right": second}.items():
1945 if isinstance(v, dict) and v.get("op"):
1946 v = self.build(v)
1947 if isinstance(v, dict) and ("tablename" in v):
1948 v = self.db[v["tablename"]][v["fieldname"]]
1949 if k == "left":
1950 left = v
1951 else:
1952 right = v
1953
1954 if hasattr(self.db._adapter, op):
1955 opm = getattr(self.db._adapter, op)
1956
1957 if op == "EQ":
1958 built = left == right
1959 elif op == "NE":
1960 built = left != right
1961 elif op == "GT":
1962 built = left > right
1963 elif op == "GE":
1964 built = left >= right
1965 elif op == "LT":
1966 built = left < right
1967 elif op == "LE":
1968 built = left <= right
1969 elif op in ("JOIN", "LEFT_JOIN", "RANDOM", "ALLOW_NULL"):
1970 built = Expression(self.db, opm)
1971 elif op in ("LOWER", "UPPER", "EPOCH", "PRIMARY_KEY",
1972 "COALESCE_ZERO", "RAW", "INVERT"):
1973 built = Expression(self.db, opm, left)
1974 elif op in ("COUNT", "EXTRACT", "AGGREGATE", "SUBSTRING",
1975 "REGEXP", "LIKE", "ILIKE", "STARTSWITH",
1976 "ENDSWITH", "ADD", "SUB", "MUL", "DIV",
1977 "MOD", "AS", "ON", "COMMA", "NOT_NULL",
1978 "COALESCE", "CONTAINS", "BELONGS"):
1979 built = Expression(self.db, opm, left, right)
1980 # expression as string
1981 elif not (left or right):
1982 built = Expression(self.db, op)
1983 else:
1984 raise SyntaxError("Operator not supported: %s" % op)
1985
1986 return built
1987
1988 def isempty(self):
1989 return not self.select(limitby=(0, 1), orderby_on_limitby=False)
1990
1991 def count(self, distinct=None, cache=None):
1992 db = self.db
1993 if cache:
1994 sql = self._count(distinct=distinct)
1995 if isinstance(cache, dict):
1996 cache_model = cache['model']
1997 time_expire = cache['expiration']
1998 key = cache.get('key')
1999 if not key:
2000 key = db._uri + '/' + sql
2001 key = hashlib_md5(key).hexdigest()
2002 else:
2003 cache_model, time_expire = cache
2004 key = db._uri + '/' + sql
2005 key = hashlib_md5(key).hexdigest()
2006 return cache_model(
2007 key,
2008 (lambda self=self, distinct=distinct: db._adapter.count(self.query, distinct)),
2009 time_expire)
2010 return db._adapter.count(self.query, distinct)
2011
2012 def select(self, *fields, **attributes):
2013 adapter = self.db._adapter
2014 tablenames = adapter.tables(self.query,
2015 attributes.get('join', None),
2016 attributes.get('left', None),
2017 attributes.get('orderby', None),
2018 attributes.get('groupby', None))
2019 fields = adapter.expand_all(fields, tablenames)
2020 return adapter.select(self.query, fields, attributes)
2021
2022 def iterselect(self, *fields, **attributes):
2023 adapter = self.db._adapter
2024 tablenames = adapter.tables(self.query,
2025 attributes.get('join', None),
2026 attributes.get('left', None),
2027 attributes.get('orderby', None),
2028 attributes.get('groupby', None))
2029 fields = adapter.expand_all(fields, tablenames)
2030 return adapter.iterselect(self.query, fields, attributes)
2031
2032 def nested_select(self, *fields, **attributes):
2033 return Expression(self.db, self._select(*fields, **attributes))
2034
2035 def delete(self):
2036 db = self.db
2037 tablename = db._adapter.get_table(self.query)
2038 table = db[tablename]
2039 if any(f(self) for f in table._before_delete): return 0
2040 ret = db._adapter.delete(tablename, self.query)
2041 ret and [f(self) for f in table._after_delete]
2042 return ret
2043
2044 def update(self, **update_fields):
2045 db = self.db
2046 tablename = db._adapter.get_table(self.query)
2047 table = db[tablename]
2048 table._attempt_upload(update_fields)
2049 if any(f(self, update_fields) for f in table._before_update):
2050 return 0
2051 fields = table._listify(update_fields, update=True)
2052 if not fields:
2053 raise SyntaxError("No fields to update")
2054 ret = db._adapter.update("%s" % table._tablename, self.query, fields)
2055 ret and [f(self, update_fields) for f in table._after_update]
2056 return ret
2057
2058 def update_naive(self, **update_fields):
2059 """
2060 Same as update but does not call table._before_update and _after_update
2061 """
2062 tablename = self.db._adapter.get_table(self.query)
2063 table = self.db[tablename]
2064 fields = table._listify(update_fields, update=True)
2065 if not fields:
2066 raise SyntaxError("No fields to update")
2067
2068 ret = self.db._adapter.update("%s" % table, self.query, fields)
2069 return ret
2070
2071 def validate_and_update(self, **update_fields):
2072 tablename = self.db._adapter.get_table(self.query)
2073 response = Row()
2074 response.errors = Row()
2075 new_fields = copy.copy(update_fields)
2076 for key, value in iteritems(update_fields):
2077 value, error = self.db[tablename][key].validate(value)
2078 if error:
2079 response.errors[key] = '%s' % error
2080 else:
2081 new_fields[key] = value
2082 table = self.db[tablename]
2083 if response.errors:
2084 response.updated = None
2085 else:
2086 if not any(f(self, new_fields) for f in table._before_update):
2087 table._attempt_upload(new_fields)
2088 fields = table._listify(new_fields, update=True)
2089 if not fields:
2090 raise SyntaxError("No fields to update")
2091 ret = self.db._adapter.update(tablename, self.query, fields)
2092 ret and [f(self, new_fields) for f in table._after_update]
2093 else:
2094 ret = 0
2095 response.updated = ret
2096 return response
2097
2098 def delete_uploaded_files(self, upload_fields=None):
2099 table = self.db[self.db._adapter.tables(self.query)[0]]
2100 # ## mind uploadfield==True means file is not in DB
2101 if upload_fields:
2102 fields = list(upload_fields)
2103 # Explicitly add compute upload fields (ex: thumbnail)
2104 fields += [f for f in table.fields if table[f].compute is not None]
2105 else:
2106 fields = table.fields
2107 fields = [f for f in fields if table[f].type == 'upload' and
2108 table[f].uploadfield == True and
2109 table[f].autodelete]
2110 if not fields:
2111 return False
2112 for record in self.select(*[table[f] for f in fields]):
2113 for fieldname in fields:
2114 field = table[fieldname]
2115 oldname = record.get(fieldname, None)
2116 if not oldname:
2117 continue
2118 if (upload_fields and fieldname in upload_fields and
2119 oldname == upload_fields[fieldname]):
2120 continue
2121 if field.custom_delete:
2122 field.custom_delete(oldname)
2123 else:
2124 uploadfolder = field.uploadfolder
2125 if not uploadfolder:
2126 uploadfolder = pjoin(self.db._adapter.folder, '..', 'uploads')
2127 if field.uploadseparate:
2128 items = oldname.split('.')
2129 uploadfolder = pjoin(uploadfolder, "%s.%s" % (items[0], items[1]), items[2][:2])
2130 oldpath = pjoin(uploadfolder, oldname)
2131 if exists(oldpath):
2132 os.unlink(oldpath)
2133 return False
2134
2135
2136class LazyReferenceGetter(object):
2137 def __init__(self, table, id):
2138 self.db, self.tablename, self.id = table._db, table._tablename, id
2139
2140 def __call__(self, other_tablename):
2141 if self.db._lazy_tables is False:
2142 raise AttributeError()
2143 table = self.db[self.tablename]
2144 other_table = self.db[other_tablename]
2145 for rfield in table._referenced_by:
2146 if rfield.table == other_table:
2147 return LazySet(rfield, self.id)
2148 raise AttributeError()
2149
2150
2151class LazySet(object):
2152 def __init__(self, field, id):
2153 self.db, self.tablename, self.fieldname, self.id = \
2154 field.db, field._tablename, field.name, id
2155
2156 def _getset(self):
2157 query = self.db[self.tablename][self.fieldname] == self.id
2158 return Set(self.db, query)
2159
2160 def __repr__(self):
2161 return repr(self._getset())
2162
2163 def __call__(self, query, ignore_common_filters=False):
2164 return self.where(query, ignore_common_filters)
2165
2166 def where(self, query, ignore_common_filters=False):
2167 return self._getset()(query, ignore_common_filters)
2168
2169 def _count(self, distinct=None):
2170 return self._getset()._count(distinct)
2171
2172 def _select(self, *fields, **attributes):
2173 return self._getset()._select(*fields, **attributes)
2174
2175 def _delete(self):
2176 return self._getset()._delete()
2177
2178 def _update(self, **update_fields):
2179 return self._getset()._update(**update_fields)
2180
2181 def isempty(self):
2182 return self._getset().isempty()
2183
2184 def count(self, distinct=None, cache=None):
2185 return self._getset().count(distinct, cache)
2186
2187 def select(self, *fields, **attributes):
2188 return self._getset().select(*fields, **attributes)
2189
2190 def nested_select(self, *fields, **attributes):
2191 return self._getset().nested_select(*fields, **attributes)
2192
2193 def delete(self):
2194 return self._getset().delete()
2195
2196 def update(self, **update_fields):
2197 return self._getset().update(**update_fields)
2198
2199 def update_naive(self, **update_fields):
2200 return self._getset().update_naive(**update_fields)
2201
2202 def validate_and_update(self, **update_fields):
2203 return self._getset().validate_and_update(**update_fields)
2204
2205 def delete_uploaded_files(self, upload_fields=None):
2206 return self._getset().delete_uploaded_files(upload_fields)
2207
2208
2209class VirtualCommand(object):
2210 def __init__(self, method, row):
2211 self.method=method
2212 self.row=row
2213
2214 def __call__(self, *args, **kwargs):
2215 return self.method(self.row, *args, **kwargs)
2216
2217
2218@implements_bool
2219class BasicRows(object):
2220 """
2221 Abstract class for Rows and IterRows
2222 """
2223 def __bool__(self):
2224 return True if self.first() is not None else False
2225
2226 def __str__(self):
2227 """
2228 Serializes the table into a csv file
2229 """
2230
2231 s = StringIO()
2232 self.export_to_csv_file(s)
2233 return s.getvalue()
2234
2235 def as_trees(self, parent_name='parent_id', children_name='children',
2236 render=False):
2237 """
2238 returns the data as list of trees.
2239
2240 :param parent_name: the name of the field holding the reference to the
2241 parent (default parent_id).
2242 :param children_name: the name where the children of each row will be
2243 stored as a list (default children).
2244 :param render: whether we will render the fields using their represent
2245 (default False) can be a list of fields to render or
2246 True to render all.
2247 """
2248 roots = []
2249 drows = {}
2250 rows = list(self.render(fields=None if render is True else render)) \
2251 if render else self
2252 for row in rows:
2253 drows[row.id] = row
2254 row[children_name] = []
2255 for row in rows:
2256 parent = row[parent_name]
2257 if parent is None:
2258 roots.append(row)
2259 else:
2260 drows[parent][children_name].append(row)
2261 return roots
2262
2263 def as_list(self,
2264 compact=True,
2265 storage_to_dict=True,
2266 datetime_to_str=False,
2267 custom_types=None):
2268 """
2269 Returns the data as a list or dictionary.
2270
2271 Args:
2272 storage_to_dict: when True returns a dict, otherwise a list
2273 datetime_to_str: convert datetime fields as strings
2274 """
2275 (oc, self.compact) = (self.compact, compact)
2276 if storage_to_dict:
2277 items = [item.as_dict(datetime_to_str, custom_types)
2278 for item in self]
2279 else:
2280 items = [item for item in self]
2281 self.compact = oc
2282 return items
2283
2284 def as_dict(self,
2285 key='id',
2286 compact=True,
2287 storage_to_dict=True,
2288 datetime_to_str=False,
2289 custom_types=None):
2290 """
2291 Returns the data as a dictionary of dictionaries (storage_to_dict=True)
2292 or records (False)
2293
2294 Args:
2295 key: the name of the field to be used as dict key, normally the id
2296 compact: ? (default True)
2297 storage_to_dict: when True returns a dict, otherwise a list(default True)
2298 datetime_to_str: convert datetime fields as strings (default False)
2299 """
2300
2301 # test for multiple rows
2302 multi = False
2303 f = self.first()
2304 if f and isinstance(key, basestring):
2305 multi = any([isinstance(v, f.__class__) for v in f.values()])
2306 if ("." not in key) and multi:
2307 # No key provided, default to int indices
2308 def new_key():
2309 i = 0
2310 while True:
2311 yield i
2312 i += 1
2313 key_generator = new_key()
2314 key = lambda r: key_generator.next()
2315
2316 rows = self.as_list(compact, storage_to_dict, datetime_to_str,
2317 custom_types)
2318 if isinstance(key, str) and key.count('.') == 1:
2319 (table, field) = key.split('.')
2320 return dict([(r[table][field], r) for r in rows])
2321 elif isinstance(key, str):
2322 return dict([(r[key], r) for r in rows])
2323 else:
2324 return dict([(key(r), r) for r in rows])
2325
2326 def xml(self, strict=False, row_name='row', rows_name='rows'):
2327 """
2328 Serializes the table using sqlhtml.SQLTABLE (if present)
2329 """
2330 if not strict and not self.db.has_representer('rows_xml'):
2331 strict = True
2332
2333 if strict:
2334 return '<%s>\n%s\n</%s>' % (
2335 rows_name,
2336 '\n'.join(
2337 row.as_xml(
2338 row_name=row_name,
2339 colnames=self.colnames
2340 ) for row in self),
2341 rows_name
2342 )
2343
2344 rv = self.db.represent('rows_xml', self)
2345 if hasattr(rv, 'xml') and callable(getattr(rv, 'xml')):
2346 return rv.xml()
2347 return rv
2348
2349 def as_xml(self, row_name='row', rows_name='rows'):
2350 return self.xml(strict=True, row_name=row_name, rows_name=rows_name)
2351
2352 def as_json(self, mode='object', default=None):
2353 """
2354 Serializes the rows to a JSON list or object with objects
2355 mode='object' is not implemented (should return a nested
2356 object structure)
2357 """
2358 items = [record.as_json(
2359 mode=mode, default=default, serialize=False,
2360 colnames=self.colnames
2361 ) for record in self]
2362
2363 return serializers.json(items)
2364
2365 def export_to_csv_file(self, ofile, null='<NULL>', *args, **kwargs):
2366 """
2367 Exports data to csv, the first line contains the column names
2368
2369 Args:
2370 ofile: where the csv must be exported to
2371 null: how null values must be represented (default '<NULL>')
2372 delimiter: delimiter to separate values (default ',')
2373 quotechar: character to use to quote string values (default '"')
2374 quoting: quote system, use csv.QUOTE_*** (default csv.QUOTE_MINIMAL)
2375 represent: use the fields .represent value (default False)
2376 colnames: list of column names to use (default self.colnames)
2377
2378 This will only work when exporting rows objects!!!!
2379 DO NOT use this with db.export_to_csv()
2380 """
2381 delimiter = kwargs.get('delimiter', ',')
2382 quotechar = kwargs.get('quotechar', '"')
2383 quoting = kwargs.get('quoting', csv.QUOTE_MINIMAL)
2384 represent = kwargs.get('represent', False)
2385 writer = csv.writer(ofile, delimiter=delimiter,
2386 quotechar=quotechar, quoting=quoting)
2387
2388 def unquote_colnames(colnames):
2389 unq_colnames = []
2390 for col in colnames:
2391 m = self.db._adapter.REGEX_TABLE_DOT_FIELD.match(col)
2392 if not m:
2393 unq_colnames.append(col)
2394 else:
2395 unq_colnames.append('.'.join(m.groups()))
2396 return unq_colnames
2397
2398 colnames = kwargs.get('colnames', self.colnames)
2399 write_colnames = kwargs.get('write_colnames', True)
2400 # a proper csv starting with the column names
2401 if write_colnames:
2402 writer.writerow(unquote_colnames(colnames))
2403
2404 def none_exception(value):
2405 """
2406 Returns a cleaned up value that can be used for csv export:
2407
2408 - unicode text is encoded as such
2409 - None values are replaced with the given representation (default <NULL>)
2410 """
2411 if value is None:
2412 return null
2413 elif PY2 and isinstance(value, unicode):
2414 return value.encode('utf8')
2415 elif isinstance(value, Reference):
2416 return long(value)
2417 elif hasattr(value, 'isoformat'):
2418 return value.isoformat()[:19].replace('T', ' ')
2419 elif isinstance(value, (list, tuple)): # for type='list:..'
2420 return bar_encode(value)
2421 return value
2422
2423 repr_cache = {}
2424 for record in self:
2425 row = []
2426 for col in colnames:
2427 m = self.db._adapter.REGEX_TABLE_DOT_FIELD.match(col)
2428 if not m:
2429 row.append(record._extra[col])
2430 else:
2431 (t, f) = m.groups()
2432 field = self.db[t][f]
2433 if isinstance(record.get(t, None), (Row, dict)):
2434 value = record[t][f]
2435 else:
2436 value = record[f]
2437 if field.type == 'blob' and value is not None:
2438 value = base64.b64encode(value)
2439 elif represent and field.represent:
2440 if field.type.startswith('reference'):
2441 if field not in repr_cache:
2442 repr_cache[field] = {}
2443 if value not in repr_cache[field]:
2444 repr_cache[field][value] = field.represent(
2445 value, record
2446 )
2447 value = repr_cache[field][value]
2448 else:
2449 value = field.represent(value, record)
2450 row.append(none_exception(value))
2451 writer.writerow(row)
2452
2453 # for consistent naming yet backwards compatible
2454 as_csv = __str__
2455 json = as_json
2456
2457
2458class Rows(BasicRows):
2459 """
2460 A wrapper for the return value of a select. It basically represents a table.
2461 It has an iterator and each row is represented as a `Row` dictionary.
2462 """
2463
2464 # ## TODO: this class still needs some work to care for ID/OID
2465
2466 def __init__(self, db=None, records=[], colnames=[], compact=True,
2467 rawrows=None):
2468 self.db = db
2469 self.records = records
2470 self.colnames = colnames
2471 self.compact = compact
2472 self.response = rawrows
2473
2474 def __repr__(self):
2475 return '<Rows (%s)>' % len(self.records)
2476
2477 def setvirtualfields(self, **keyed_virtualfields):
2478 """
2479 For reference::
2480
2481 db.define_table('x', Field('number', 'integer'))
2482 if db(db.x).isempty(): [db.x.insert(number=i) for i in range(10)]
2483
2484 from gluon.dal import lazy_virtualfield
2485
2486 class MyVirtualFields(object):
2487 # normal virtual field (backward compatible, discouraged)
2488 def normal_shift(self): return self.x.number+1
2489 # lazy virtual field (because of @staticmethod)
2490 @lazy_virtualfield
2491 def lazy_shift(instance, row, delta=4): return row.x.number+delta
2492 db.x.virtualfields.append(MyVirtualFields())
2493
2494 for row in db(db.x).select():
2495 print row.number, row.normal_shift, row.lazy_shift(delta=7)
2496
2497 """
2498 if not keyed_virtualfields:
2499 return self
2500 for row in self.records:
2501 for (tablename, virtualfields) in iteritems(keyed_virtualfields):
2502 attributes = dir(virtualfields)
2503 if tablename not in row:
2504 box = row[tablename] = Row()
2505 else:
2506 box = row[tablename]
2507 updated = False
2508 for attribute in attributes:
2509 if attribute[0] != '_':
2510 method = getattr(virtualfields, attribute)
2511 if hasattr(method, '__lazy__'):
2512 box[attribute] = VirtualCommand(method, row)
2513 elif type(method) == types.MethodType:
2514 if not updated:
2515 virtualfields.__dict__.update(row)
2516 updated = True
2517 box[attribute] = method()
2518 return self
2519
2520 def __and__(self, other):
2521 if self.colnames != other.colnames:
2522 raise Exception('Cannot & incompatible Rows objects')
2523 records = self.records+other.records
2524 return Rows(self.db, records, self.colnames,
2525 compact=self.compact or other.compact)
2526
2527 def __or__(self, other):
2528 if self.colnames != other.colnames:
2529 raise Exception('Cannot | incompatible Rows objects')
2530 records = [record for record in other.records
2531 if record not in self.records]
2532 records = self.records + records
2533 return Rows(self.db, records, self.colnames,
2534 compact=self.compact or other.compact)
2535
2536 def __len__(self):
2537 return len(self.records)
2538
2539 def __getslice__(self, a, b):
2540 return Rows(self.db, self.records[a:b], self.colnames,
2541 compact=self.compact)
2542
2543 def __getitem__(self, i):
2544 row = self.records[i]
2545 keys = list(row.keys())
2546 if self.compact and len(keys) == 1 and keys[0] != '_extra':
2547 return row[keys[0]]
2548 return row
2549
2550 def __iter__(self):
2551 """
2552 Iterator over records
2553 """
2554
2555 for i in xrange(len(self)):
2556 yield self[i]
2557
2558 def __eq__(self, other):
2559 if isinstance(other, Rows):
2560 return (self.records == other.records)
2561 else:
2562 return False
2563
2564 def column(self, column=None):
2565 return [r[str(column) if column else self.colnames[0]] for r in self]
2566
2567 def first(self):
2568 if not self.records:
2569 return None
2570 return self[0]
2571
2572 def last(self):
2573 if not self.records:
2574 return None
2575 return self[-1]
2576
2577 def find(self, f, limitby=None):
2578 """
2579 Returns a new Rows object, a subset of the original object,
2580 filtered by the function `f`
2581 """
2582 if not self:
2583 return Rows(self.db, [], self.colnames, compact=self.compact)
2584 records = []
2585 if limitby:
2586 a, b = limitby
2587 else:
2588 a, b = 0, len(self)
2589 k = 0
2590 for i, row in enumerate(self):
2591 if f(row):
2592 if a <= k:
2593 records.append(self.records[i])
2594 k += 1
2595 if k == b:
2596 break
2597 return Rows(self.db, records, self.colnames, compact=self.compact)
2598
2599 def exclude(self, f):
2600 """
2601 Removes elements from the calling Rows object, filtered by the function
2602 `f`, and returns a new Rows object containing the removed elements
2603 """
2604 if not self.records:
2605 return Rows(self.db, [], self.colnames, compact=self.compact)
2606 removed = []
2607 i = 0
2608 while i < len(self):
2609 row = self[i]
2610 if f(row):
2611 removed.append(self.records[i])
2612 del self.records[i]
2613 else:
2614 i += 1
2615 return Rows(self.db, removed, self.colnames, compact=self.compact)
2616
2617 def sort(self, f, reverse=False):
2618 """
2619 Returns a list of sorted elements (not sorted in place)
2620 """
2621 rows = Rows(self.db, [], self.colnames, compact=self.compact)
2622 # When compact=True, iterating over self modifies each record,
2623 # so when sorting self, it is necessary to return a sorted
2624 # version of self.records rather than the sorted self directly.
2625 rows.records = [r for (r, s) in sorted(zip(self.records, self),
2626 key=lambda r: f(r[1]),
2627 reverse=reverse)]
2628 return rows
2629
2630 def group_by_value(self, *fields, **args):
2631 """
2632 Regroups the rows, by one of the fields
2633 """
2634 one_result = False
2635 if 'one_result' in args:
2636 one_result = args['one_result']
2637
2638 def build_fields_struct(row, fields, num, groups):
2639 """
2640 helper function:
2641 """
2642 if num > len(fields)-1:
2643 if one_result:
2644 return row
2645 else:
2646 return [row]
2647
2648 key = fields[num]
2649 value = row[key]
2650
2651 if value not in groups:
2652 groups[value] = build_fields_struct(row, fields, num+1, {})
2653 else:
2654 struct = build_fields_struct(row, fields, num+1, groups[value])
2655
2656 # still have more grouping to do
2657 if type(struct) == type(dict()):
2658 groups[value].update()
2659 # no more grouping, first only is off
2660 elif type(struct) == type(list()):
2661 groups[value] += struct
2662 # no more grouping, first only on
2663 else:
2664 groups[value] = struct
2665
2666 return groups
2667
2668 if len(fields) == 0:
2669 return self
2670
2671 # if select returned no results
2672 if not self.records:
2673 return {}
2674
2675 grouped_row_group = dict()
2676
2677 # build the struct
2678 for row in self:
2679 build_fields_struct(row, fields, 0, grouped_row_group)
2680
2681 return grouped_row_group
2682
2683 def render(self, i=None, fields=None):
2684 """
2685 Takes an index and returns a copy of the indexed row with values
2686 transformed via the "represent" attributes of the associated fields.
2687
2688 Args:
2689 i: index. If not specified, a generator is returned for iteration
2690 over all the rows.
2691 fields: a list of fields to transform (if None, all fields with
2692 "represent" attributes will be transformed)
2693 """
2694 if i is None:
2695 return (self.render(i, fields=fields) for i in range(len(self)))
2696 if not self.db.has_representer('rows_render'):
2697 raise RuntimeError("Rows.render() needs a `rows_render` \
2698 representer in DAL instance")
2699 row = copy.deepcopy(self.records[i])
2700 keys = list(row.keys())
2701 tables = [f.tablename for f in fields] if fields \
2702 else [k for k in keys if k != '_extra']
2703 for table in tables:
2704 repr_fields = [f.name for f in fields if f.tablename == table] \
2705 if fields else [k for k in row[table].keys()
2706 if (hasattr(self.db[table], k) and
2707 isinstance(self.db[table][k], Field)
2708 and self.db[table][k].represent)]
2709 for field in repr_fields:
2710 row[table][field] = self.db.represent(
2711 'rows_render', self.db[table][field], row[table][field],
2712 row[table])
2713
2714 if self.compact and len(keys) == 1 and keys[0] != '_extra':
2715 return row[keys[0]]
2716 return row
2717
2718
2719@implements_iterator
2720class IterRows(BasicRows):
2721 def __init__(self, db, sql, fields, colnames, blob_decode, cacheable):
2722 self.db = db
2723 self.fields = fields
2724 self.colnames = colnames
2725 self.blob_decode = blob_decode
2726 self.cacheable = cacheable
2727 (self.fields_virtual, self.fields_lazy, self.tmps) = \
2728 self.db._adapter._parse_expand_colnames(colnames)
2729 self.db._adapter.execute(sql)
2730 self.db._adapter.current_cursor_in_use = True
2731 self.cursor = self.db._adapter.cursor
2732 self._head = None
2733 self.last_item = None
2734 self.last_item_id = None
2735 self.compact = True
2736 self.sql = sql
2737
2738 def __next__(self):
2739 db_row = self.cursor.fetchone()
2740 if db_row is None:
2741 raise StopIteration
2742 row = self.db._adapter._parse(db_row, self.tmps, self.fields,
2743 self.colnames, self.blob_decode,
2744 self.cacheable, self.fields_virtual,
2745 self.fields_lazy)
2746 if self.compact:
2747 # The following is to translate
2748 # <Row {'t0': {'id': 1L, 'name': 'web2py'}}>
2749 # in
2750 # <Row {'id': 1L, 'name': 'web2py'}>
2751 # normally accomplished by Rows.__get_item__
2752 keys = list(row.keys())
2753 if len(keys) == 1 and keys[0] != '_extra':
2754 row = row[keys[0]]
2755 return row
2756
2757 def __iter__(self):
2758 if self._head:
2759 yield self._head
2760 try:
2761 row = next(self)
2762 while row is not None:
2763 yield row
2764 row = next(self)
2765 except StopIteration:
2766 # Iterator is over, adjust the cursor logic
2767 if self.db._adapter.current_cursor_in_use == True:
2768 # nothing to do, current_cursor_in_use is still True
2769 self.db._adapter.current_cursor_in_use = False
2770 else:
2771 # A sub query has opened a new cursor. Close the one in use, pop the former one from stack
2772 self.db._adapter.cursor.close()
2773 self.db._adapter.cursor = self.db._adapter.cursors_in_use.pop()
2774 raise StopIteration
2775 return
2776
2777 def first(self):
2778 if self._head is None:
2779 try:
2780 self._head = next(self)
2781 except StopIteration:
2782 # TODO should I raise something?
2783 return None
2784 return self._head
2785
2786 def __getitem__(self, key):
2787 if not isinstance(key, (int, long)):
2788 raise TypeError
2789
2790 if key == self.last_item_id:
2791 return self.last_item
2792
2793 n_to_drop = key
2794 if self.last_item_id is not None:
2795 if self.last_item_id < key:
2796 n_to_drop -= (self.last_item_id + 1)
2797 else:
2798 raise IndexError
2799
2800 # fetch and drop the first key - 1 elements
2801 for i in xrange(n_to_drop):
2802 self.cursor._fetchone()
2803 row = next(self)
2804 if row is None:
2805 raise IndexError
2806 else:
2807 self.last_item_id = key
2808 self.last_item = row
2809 return row
2810
2811# # rowcount it doesn't seem to be reliable on all drivers
2812# def __len__(self):
2813# return self.db._adapter.cursor.rowcount