1
2
3 """ S3 Query Construction
4
5 @copyright: 2009-2019 (c) Sahana Software Foundation
6 @license: MIT
7
8 Permission is hereby granted, free of charge, to any person
9 obtaining a copy of this software and associated documentation
10 files (the "Software"), to deal in the Software without
11 restriction, including without limitation the rights to use,
12 copy, modify, merge, publish, distribute, sublicense, and/or sell
13 copies of the Software, and to permit persons to whom the
14 Software is furnished to do so, subject to the following
15 conditions:
16
17 The above copyright notice and this permission notice shall be
18 included in all copies or substantial portions of the Software.
19
20 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
21 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
22 OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
23 NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
24 HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
25 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
26 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
27 OTHER DEALINGS IN THE SOFTWARE.
28 """
29
30 __all__ = ("FS",
31 "S3FieldSelector",
32 "S3Joins",
33 "S3ResourceField",
34 "S3ResourceQuery",
35 "S3URLQuery",
36 "S3URLQueryParser",
37 )
38
39 import datetime
40 import re
41 import sys
42
43 from gluon import current, IS_EMPTY_OR, IS_IN_SET
44 from gluon.storage import Storage
45
46 from s3dal import Field, Row
47 from s3fields import S3RepresentLazy
48 from s3utils import s3_get_foreign_key, s3_str, s3_unicode, S3TypeConverter
49
50 ogetattr = object.__getattribute__
51
52 TEXTTYPES = ("string", "text")
56 """ Helper class to construct a resource query """
57
58 LOWER = "lower"
59 UPPER = "upper"
60
61 OPERATORS = [LOWER, UPPER]
62
64 """ Constructor """
65
66 if not isinstance(name, basestring) or not name:
67 raise SyntaxError("name required")
68 self.name = str(name)
69 self.type = type
70
71 self.op = None
72
73
76
77
80
81
84
85
88
89
92
93
96
97
98 - def like(self, value):
100
101
104
105
108
109
112
113
116
117
120
121
123 self.op = self.LOWER
124 return self
125
126
128 self.op = self.UPPER
129 return self
130
131
132 - def expr(self, val):
133
134 if self.op and val is not None:
135 if self.op == self.LOWER and \
136 hasattr(val, "lower") and callable(val.lower) and \
137 (not isinstance(val, Field) or val.type in TEXTTYPES):
138 return val.lower()
139 elif self.op == self.UPPER and \
140 hasattr(val, "upper") and callable(val.upper) and \
141 (not isinstance(val, Field) or val.type in TEXTTYPES):
142 return val.upper()
143 return val
144
145
147
148 try:
149 rfield = S3ResourceField(resource, self.name)
150 except (SyntaxError, AttributeError):
151 colname = None
152 else:
153 colname = rfield.colname
154 if colname:
155 if self.op is not None:
156 return "%s.%s()" % (colname, self.op)
157 else:
158 return colname
159 else:
160 return "(%s?)" % self.name
161
162
163 @classmethod
165 """
166 Extract a value from a Row
167
168 @param resource: the resource
169 @param row: the Row
170 @param field: the field
171
172 @return: field if field is not a Field/S3FieldSelector instance,
173 the value from the row otherwise
174 """
175
176 error = lambda fn: KeyError("Field not found: %s" % fn)
177
178 t = type(field)
179
180 if isinstance(field, Field):
181 colname = str(field)
182 tname, fname = colname.split(".", 1)
183
184 elif t is S3FieldSelector:
185 rfield = S3ResourceField(resource, field.name)
186 colname = rfield.colname
187 if not colname:
188
189 raise error(field.name)
190 fname = rfield.fname
191 tname = rfield.tname
192
193 elif t is S3ResourceField:
194 colname = field.colname
195 if not colname:
196
197 return None
198 fname = field.fname
199 tname = field.tname
200
201 else:
202 return field
203
204 if type(row) is Row:
205 try:
206 if tname in row.__dict__:
207 value = ogetattr(ogetattr(row, tname), fname)
208 else:
209 value = ogetattr(row, fname)
210 except AttributeError:
211 try:
212 value = row[colname]
213 except (KeyError, AttributeError):
214 raise error(colname)
215 elif fname in row:
216 value = row[fname]
217 elif colname in row:
218 value = row[colname]
219 elif tname is not None and \
220 tname in row and fname in row[tname]:
221 value = row[tname][fname]
222 else:
223 raise error(colname)
224
225 if callable(value):
226
227 try:
228 value = value()
229 except:
230 t, m = sys.exc_info()[:2]
231 current.log.error("%s.%s: %s" % (tname, fname, str(m) or t.__name__))
232 value = None
233
234 if hasattr(field, "expr"):
235 return field.expr(value)
236 return value
237
238
240 """
241 Resolve this field against a resource
242
243 @param resource: the resource
244 """
245 return S3ResourceField(resource, self.name)
246
247
248
249
250 FS = S3FieldSelector
254 """ Helper class to parse field selectors """
255
256
257 @classmethod
258 - def resolve(cls, resource, selector, tail=None):
259 """
260 Resolve a selector (=field path) against a resource
261
262 @param resource: the S3Resource to resolve against
263 @param selector: the field selector string
264 @param tail: tokens to append to the selector
265
266 The general syntax for a selector is:
267
268 selector = {[alias].}{[key]$}[field|selector]
269
270 (Parts in {} are optional, | indicates alternatives)
271
272 * Alias can be:
273
274 ~ refers to the resource addressed by the
275 preceding parts of the selector (=last
276 resource)
277 component alias of a component of the last resource
278 linktable alias of a link table of the last resource
279 table name of a table that has a foreign key for
280 the last resource (auto-detect the key)
281 key:table same as above, but specifying the foreign key
282
283 * Key can be:
284
285 key the name of a foreign key in the last resource
286 context a context expression
287
288 * Field can be:
289
290 fieldname the name of a field or virtual field of the
291 last resource
292 context a context expression
293
294 A "context expression" is a name enclosed in parentheses:
295
296 (context)
297
298 During parsing, context expressions get replaced by the
299 string which has been configured for this name for the
300 last resource with:
301
302 s3db.configure(tablename, context = dict(name = "string"))
303
304 With context expressions, the same selector can be used
305 for different resources, each time resolving into the
306 specific field path. However, the field addressed must
307 be of the same type in all resources to form valid
308 queries.
309
310 If a context name can not be resolved, resolve() will
311 still succeed - but the S3FieldPath returned will have
312 colname=None and ftype="context" (=unresolvable context).
313 """
314
315 if not selector:
316 raise SyntaxError("Invalid selector: %s" % selector)
317 tokens = re.split(r"(\.|\$)", selector)
318 if tail:
319 tokens.extend(tail)
320 parser = cls(resource, None, tokens)
321 parser.original = selector
322 return parser
323
324
325 - def __init__(self, resource, table, tokens):
326 """
327 Constructor - not to be called directly, use resolve() instead
328
329 @param resource: the S3Resource
330 @param table: the table
331 @param tokens: the tokens as list
332 """
333
334 s3db = current.s3db
335
336 if table is None:
337 table = resource.table
338
339
340 self.original = None
341 tname = self.tname = table._tablename
342 self.fname = None
343 self.field = None
344 self.method = None
345 self.ftype = None
346 self.virtual = False
347 self.colname = None
348
349 self.joins = {}
350
351 self.distinct = False
352 self.multiple = True
353
354 head = tokens.pop(0)
355 tail = None
356
357 if head and head[0] == "(" and head[-1] == ")":
358
359
360 head = head.strip("()")
361 self.fname = head
362 self.ftype = "context"
363
364 if not resource:
365 resource = s3db.resource(table, components=[])
366 context = resource.get_config("context")
367 if context and head in context:
368 tail = self.resolve(resource, context[head], tail=tokens)
369 else:
370
371 pass
372
373 elif tokens:
374
375
376 op = tokens.pop(0)
377 if tokens:
378
379 if op == ".":
380
381
382 if not resource:
383 resource = s3db.resource(table, components=[])
384 ktable, join, m, d = self._resolve_alias(resource, head)
385 self.multiple = m
386 self.distinct = d
387 else:
388
389
390 ktable, join = self._resolve_key(table, head)
391 self.distinct = True
392
393 if join is not None:
394 self.joins[ktable._tablename] = join
395 tail = S3FieldPath(None, ktable, tokens)
396
397 else:
398 raise SyntaxError("trailing operator")
399
400 if tail is None:
401
402
403 if self.ftype != "context":
404
405 field, method = self._resolve_field(table, head)
406 if not field:
407 self.virtual = True
408 self.field = None
409 self.method = method
410 self.ftype = "virtual"
411 else:
412 self.virtual = False
413 self.field = field
414 self.method = None
415 self.ftype = str(field.type)
416 self.fname = head
417 self.colname = "%s.%s" % (tname, head)
418 else:
419
420 self.tname = tail.tname
421 self.fname = tail.fname
422 self.field = tail.field
423 self.method = tail.method
424 self.ftype = tail.ftype
425 self.virtual = tail.virtual
426 self.colname = tail.colname
427
428 self.distinct |= tail.distinct
429 self.multiple |= tail.multiple
430
431 self.joins.update(tail.joins)
432
433
434 @staticmethod
436 """
437 Resolve a field name against the table, recognizes "id" as
438 table._id.name, and "uid" as current.xml.UID.
439
440 @param table: the Table
441 @param fieldname: the field name
442
443 @return: tuple (Field, Field.Method)
444 """
445
446 method = None
447
448 if fieldname == "uid":
449 fieldname = current.xml.UID
450
451 if fieldname == "id":
452 field = table._id
453 elif fieldname in table.fields:
454 field = ogetattr(table, fieldname)
455 else:
456
457 field = None
458 try:
459 method = ogetattr(table, fieldname)
460 except AttributeError:
461
462 pass
463
464 return field, method
465
466
467 @staticmethod
469 """
470 Resolve a foreign key into the referenced table and the
471 join and left join between the current table and the
472 referenced table
473
474 @param table: the current Table
475 @param fieldname: the fieldname of the foreign key
476
477 @return: tuple of (referenced table, join, left join)
478 @raise: AttributeError is either the field or
479 the referended table are not found
480 @raise: SyntaxError if the field is not a foreign key
481 """
482
483 if fieldname in table.fields:
484 f = table[fieldname]
485 else:
486 raise AttributeError("key not found: %s" % fieldname)
487
488 ktablename, pkey = s3_get_foreign_key(f, m2m=False)[:2]
489
490 if not ktablename:
491 raise SyntaxError("%s is not a foreign key" % f)
492
493 ktable = current.s3db.table(ktablename,
494 AttributeError("undefined table %s" % ktablename),
495 db_only=True)
496
497 pkey = ktable[pkey] if pkey else ktable._id
498 join = [ktable.on(f == pkey)]
499
500 return ktable, join
501
502
503 @staticmethod
505 """
506 Resolve a table alias into the linked table (component, linktable
507 or free join), and the joins and left joins between the current
508 resource and the linked table.
509
510 @param resource: the current S3Resource
511 @param alias: the alias
512
513 @return: tuple of (linked table, joins, left joins, multiple,
514 distinct), the two latter being flags to indicate
515 possible ambiguous query results (needed by the query
516 builder)
517 @raise: AttributeError if one of the key fields or tables
518 can not be found
519 @raise: SyntaxError if the alias can not be resolved (e.g.
520 because on of the keys isn't a foreign key, points
521 to the wrong table or is ambiguous)
522 """
523
524
525 if alias in ("~", resource.alias):
526 return resource.table, None, False, False
527
528 multiple = True
529
530 linked = resource.linked
531 if linked and linked.alias == alias:
532
533
534 linktable = resource.table
535
536 ktable = linked.table
537 join = [ktable.on(ktable[linked.fkey] == linktable[linked.rkey])]
538
539 return ktable, join, multiple, True
540
541 component = resource.components.get(alias)
542 if component:
543
544 ktable = component.table
545 join = component._join()
546 multiple = component.multiple
547 else:
548 s3db = current.s3db
549 tablename = resource.tablename
550 calias = s3db.get_alias(tablename, alias)
551 if calias:
552
553 component = resource.components.get(calias)
554 link = component.link
555 ktable = link.table
556 join = link._join()
557 elif "_" in alias:
558
559 pkey = fkey = None
560
561
562 fkey, kname = (alias.split(":") + [None])[:2]
563 if not kname:
564 fkey, kname = kname, fkey
565 ktable = s3db.table(kname,
566 AttributeError("table not found: %s" % kname),
567 db_only=True,
568 )
569
570 if fkey is None:
571
572 for fname in ktable.fields:
573 tn, key = s3_get_foreign_key(ktable[fname], m2m=False)[:2]
574 if not tn:
575 continue
576 if tn == tablename:
577 if fkey is not None:
578 raise SyntaxError("ambiguous foreign key in %s" %
579 alias)
580 else:
581 fkey = fname
582 if key:
583 pkey = key
584 if fkey is None:
585 raise SyntaxError("no foreign key for %s in %s" %
586 (tablename, kname))
587
588 else:
589
590 if fkey not in ktable.fields:
591 raise AttributeError("no field %s in %s" % (fkey, kname))
592
593 tn, pkey = s3_get_foreign_key(ktable[fkey], m2m=False)[:2]
594 if tn and tn != tablename:
595 raise SyntaxError("%s.%s is not a foreign key for %s" %
596 (kname, fkey, tablename))
597 elif not tn:
598 raise SyntaxError("%s.%s is not a foreign key" %
599 (kname, fkey))
600
601
602 table = resource.table
603 if pkey is None:
604 pkey = table._id.name
605
606
607 query = (table[pkey] == ktable[fkey])
608 DELETED = current.xml.DELETED
609 if DELETED in ktable.fields:
610 query &= ktable[DELETED] != True
611 join = [ktable.on(query)]
612
613 else:
614 raise SyntaxError("Invalid tablename: %s" % alias)
615
616 return ktable, join, multiple, True
617
620 """ Helper class to resolve a field selector against a resource """
621
622
623 - def __init__(self, resource, selector, label=None):
624 """
625 Constructor
626
627 @param resource: the resource
628 @param selector: the field selector (string)
629 """
630
631 self.resource = resource
632 self.selector = selector
633
634 lf = S3FieldPath.resolve(resource, selector)
635
636 self.tname = lf.tname
637 self.fname = lf.fname
638 self.colname = lf.colname
639
640 self._joins = lf.joins
641
642 self.distinct = lf.distinct
643 self.multiple = lf.multiple
644
645 self._join = None
646
647 self.field = lf.field
648
649 self.virtual = False
650 self.represent = s3_unicode
651 self.requires = None
652
653 if self.field is not None:
654 field = self.field
655 self.ftype = str(field.type)
656 if resource.linked is not None and self.ftype == "id":
657
658
659 self.represent = lambda i, resource=resource: \
660 resource.component_id(None, i)
661 else:
662 self.represent = field.represent
663 self.requires = field.requires
664 elif self.colname:
665 self.virtual = True
666 self.ftype = "virtual"
667
668
669 method = lf.method
670 if hasattr(method, "handler"):
671 handler = method.handler
672 if hasattr(handler, "represent"):
673 self.represent = handler.represent
674 if hasattr(handler, "search_field"):
675 self.search_field = handler.search_field
676 else:
677 self.ftype = "context"
678
679
680 if label is None:
681 fname = self.fname
682 if fname in ["L1", "L2", "L3", "L3", "L4", "L5"]:
683 try:
684 label = current.gis.get_location_hierarchy(fname)
685 except:
686 label = None
687 elif fname == "L0":
688 label = current.messages.COUNTRY
689 if label is None:
690 f = self.field
691 if f:
692 label = f.label
693 elif fname:
694 label = " ".join([s.strip().capitalize()
695 for s in fname.split("_") if s])
696 else:
697 label = None
698
699 self.label = label
700 self.show = True
701
702
703 self._is_numeric = None
704 self._is_lookup = None
705 self._is_string = None
706 self._is_datetime = None
707 self._is_reference = None
708 self._is_list = None
709
710
712 """ String representation of this instance """
713
714 return "<S3ResourceField " \
715 "selector='%s' " \
716 "label='%s' " \
717 "table='%s' " \
718 "field='%s' " \
719 "type='%s'>" % \
720 (self.selector, self.label, self.tname, self.fname, self.ftype)
721
722
723 @property
725 """
726 Implicit join (Query) for this field, for backwards-compatibility
727 """
728
729 if self._join is not None:
730 return self._join
731
732 join = self._join = {}
733 for tablename, joins in self._joins.items():
734 query = None
735 for expression in joins:
736 if query is None:
737 query = expression.second
738 else:
739 query &= expression.second
740 if query:
741 join[tablename] = query
742 return join
743
744
745 @property
747 """
748 The left joins for this field, for backwards-compability
749 """
750
751 return self._joins
752
753
755 """
756 Extract the value for this field from a row
757
758 @param row: the Row
759 @param represent: render a text representation for the value
760 @param lazy: return a lazy representation handle if available
761 """
762
763 tname = self.tname
764 fname = self.fname
765 colname = self.colname
766 error = "Field not found in Row: %s" % colname
767
768 if type(row) is Row:
769 try:
770 if tname in row.__dict__:
771 value = ogetattr(ogetattr(row, tname), fname)
772 else:
773 value = ogetattr(row, fname)
774 except AttributeError:
775 try:
776 value = row[colname]
777 except (KeyError, AttributeError):
778 raise KeyError(error)
779 elif fname in row:
780 value = row[fname]
781 elif colname in row:
782 value = row[colname]
783 elif tname is not None and \
784 tname in row and fname in row[tname]:
785 value = row[tname][fname]
786 else:
787 raise KeyError(error)
788
789 if callable(value):
790
791 try:
792 value = value()
793 except:
794 current.log.error(sys.exc_info()[1])
795 value = None
796
797 if represent:
798 renderer = self.represent
799 if callable(renderer):
800 if lazy and hasattr(renderer, "bulk"):
801 return S3RepresentLazy(value, renderer)
802 else:
803 return renderer(value)
804 else:
805 return s3_unicode(value)
806 else:
807 return value
808
809
810 @property
842
843
844 @property
846 """
847 Check whether the field type is numeric (lazy property)
848
849 @return: True if field type is integer or double, else False
850 """
851
852 is_numeric = self._is_numeric
853 if is_numeric is None:
854
855 ftype = self.ftype
856
857 if ftype == "integer" and self.is_lookup:
858 is_numeric = False
859 else:
860 is_numeric = ftype in ("integer", "double")
861 self._is_numeric = is_numeric
862 return is_numeric
863
864
865 @property
867 """
868 Check whether the field type is a string type (lazy property)
869
870 @return: True if field type is string or text, else False
871 """
872
873 is_string = self._is_string
874 if is_string is None:
875 is_string = self.ftype in ("string", "text")
876 self._is_string = is_string
877 return is_string
878
879
880 @property
882 """
883 Check whether the field type is date/time (lazy property)
884
885 @return: True if field type is datetime, date or time, else False
886 """
887
888 is_datetime = self._is_datetime
889 if is_datetime is None:
890 is_datetime = self.ftype in ("datetime", "date", "time")
891 self._is_datetime = is_datetime
892 return is_datetime
893
894
895 @property
897 """
898 Check whether the field type is a reference (lazy property)
899
900 @return: True if field type is a reference, else False
901 """
902
903 is_reference = self._is_reference
904 if is_reference is None:
905 is_reference = self.ftype[:9] == "reference"
906 self._is_reference = is_reference
907 return is_reference
908
909
910 @property
912 """
913 Check whether the field type is a list (lazy property)
914
915 @return: True if field type is a list, else False
916 """
917
918 is_list = self._is_list
919 if is_list is None:
920 is_list = self.ftype[:5] == "list:"
921 self._is_list = is_list
922 return is_list
923
926 """ A collection of joins """
927
928 - def __init__(self, tablename, joins=None):
929 """
930 Constructor
931
932 @param tablename: the name of the master table
933 @param joins: list of joins
934 """
935
936 self.tablename = tablename
937 self.joins = {}
938 self.tables = set()
939
940 self.add(joins)
941
942
944 """
945 Iterate over the names of all joined tables in the collection
946 """
947
948 return self.joins.__iter__()
949
950
952 """
953 Get the list of joins for a table
954
955 @param tablename: the tablename
956 """
957
958 return self.joins.__getitem__(tablename)
959
960
962 """
963 Update the joins for a table
964
965 @param tablename: the tablename
966 @param joins: the list of joins for this table
967 """
968
969 master = self.tablename
970 joins_dict = self.joins
971
972 tables = current.db._adapter.tables
973
974 joins_dict[tablename] = joins
975 if len(joins) > 1:
976 for join in joins:
977 try:
978 tname = join.first._tablename
979 except AttributeError:
980 tname = str(join.first)
981 if tname not in joins_dict and \
982 master in tables(join.second):
983 joins_dict[tname] = [join]
984 self.tables.add(tablename)
985 return
986
987
989 """
990 Return the number of tables in the join, for boolean
991 test of this instance ("if joins:")
992 """
993
994 return len(self.tables)
995
996
998 """
999 Get a list of names of all joined tables
1000 """
1001
1002 return self.joins.keys()
1003
1004
1006 """
1007 Get a list of tuples (tablename, [joins]) for all joined tables
1008 """
1009
1010 return self.joins.items()
1011
1012
1014 """
1015 Get a list of joins for all joined tables
1016
1017 @return: a nested list like [[join, join, ...], ...]
1018 """
1019
1020 return self.joins.values()
1021
1022
1023 - def add(self, joins):
1024 """
1025 Add joins to this collection
1026
1027 @param joins: a join or a list/tuple of joins
1028
1029 @return: the list of names of all tables for which joins have
1030 been added to the collection
1031 """
1032
1033 tablenames = set()
1034 if joins:
1035 if not isinstance(joins, (list, tuple)):
1036 joins = [joins]
1037 for join in joins:
1038 tablename = join.first._tablename
1039 self[tablename] = [join]
1040 tablenames.add(tablename)
1041 return list(tablenames)
1042
1043
1045 """
1046 Extend this collection with the joins from another collection
1047
1048 @param other: the other collection (S3Joins), or a dict like
1049 {tablename: [join, join]}
1050 @return: the list of names of all tables for which joins have
1051 been added to the collection
1052 """
1053
1054 if type(other) is S3Joins:
1055 add = self.tables.add
1056 else:
1057 add = None
1058 joins = self.joins if type(other) is S3Joins else self
1059 for tablename in other:
1060 if tablename not in self.joins:
1061 joins[tablename] = other[tablename]
1062 if add:
1063 add(tablename)
1064 return other.keys()
1065
1066
1068 """
1069 String representation of this collection
1070 """
1071
1072 return "<S3Joins %s>" % str([str(j) for j in self.as_list()])
1073
1074
1075 - def as_list(self, tablenames=None, aqueries=None, prefer=None):
1076 """
1077 Return joins from this collection as list
1078
1079 @param tablenames: the names of the tables for which joins
1080 shall be returned, defaults to all tables
1081 in the collection. Dependencies will be
1082 included automatically (if available)
1083 @param aqueries: dict of accessible-queries {tablename: query}
1084 to include in the joins; if there is no entry
1085 for a particular table, then it will be looked
1086 up from current.auth and added to the dict.
1087 To prevent differential authorization of a
1088 particular joined table, set {<tablename>: None}
1089 in the dict
1090 @param prefer: If any table or any of its dependencies would be
1091 joined by this S3Joins collection, then skip this
1092 table here (and enforce it to be joined by the
1093 preferred collection), to prevent duplication of
1094 left joins as inner joins:
1095 join = inner_joins.as_list(prefer=left_joins)
1096 left = left_joins.as_list()
1097
1098 @return: a list of joins, ordered by their interdependency, which
1099 can be used as join/left parameter of Set.select()
1100 """
1101
1102 accessible_query = current.auth.s3_accessible_query
1103
1104 if tablenames is None:
1105 tablenames = self.tables
1106 else:
1107 tablenames = set(tablenames)
1108
1109 skip = set()
1110 if prefer:
1111 preferred_joins = prefer.as_list(tablenames=tablenames)
1112 for join in preferred_joins:
1113 try:
1114 tname = join.first._tablename
1115 except AttributeError:
1116 tname = str(join.first)
1117 skip.add(tname)
1118 tablenames -= skip
1119
1120 joins = self.joins
1121
1122
1123 required_tables = set()
1124 get_tables = current.db._adapter.tables
1125 for tablename in tablenames:
1126 if tablename not in joins or \
1127 tablename == self.tablename or \
1128 tablename in skip:
1129 continue
1130
1131 join_list = joins[tablename]
1132 preferred = False
1133 dependencies = set()
1134 for join in join_list:
1135 join_tables = set(get_tables(join.second))
1136 if join_tables:
1137 if any((tname in skip for tname in join_tables)):
1138 preferred = True
1139 dependencies |= join_tables
1140 if preferred:
1141 skip.add(tablename)
1142 skip |= dependencies
1143 prefer.extend({tablename: join_list})
1144 else:
1145 required_tables.add(tablename)
1146 required_tables |= dependencies
1147
1148
1149 joins_dict = {}
1150 for tablename in required_tables:
1151 if tablename not in joins or tablename == self.tablename:
1152 continue
1153 for join in joins[tablename]:
1154 j = join
1155 table = j.first
1156 tname = table._tablename
1157 if aqueries is not None and tname in tablenames:
1158 if tname not in aqueries:
1159 aquery = accessible_query("read", table)
1160 aqueries[tname] = aquery
1161 else:
1162 aquery = aqueries[tname]
1163 if aquery is not None:
1164 j = join.first.on(join.second & aquery)
1165 joins_dict[tname] = j
1166
1167
1168 try:
1169 return self.sort(joins_dict.values())
1170 except RuntimeError:
1171 return joins_dict.values()
1172
1173
1174 @classmethod
1175 - def sort(cls, joins):
1176 """
1177 Sort a list of left-joins by their interdependency
1178
1179 @param joins: the list of joins
1180 """
1181
1182 if len(joins) <= 1:
1183 return joins
1184 r = list(joins)
1185
1186 tables = current.db._adapter.tables
1187
1188 append = r.append
1189 head = None
1190 while r:
1191 head = join = r.pop(0)
1192 tablenames = tables(join.second)
1193 for j in r:
1194 try:
1195 tn = j.first._tablename
1196 except AttributeError:
1197 tn = str(j.first)
1198 if tn in tablenames:
1199 head = None
1200 break
1201 if head is not None:
1202 break
1203 else:
1204 append(join)
1205 if head is not None:
1206 return [head] + cls.sort(r)
1207 else:
1208 raise RuntimeError("circular join dependency")
1209
1212 """
1213 Helper class representing a resource query
1214 - unlike DAL Query objects, these can be converted to/from URL filters
1215 """
1216
1217
1218 NOT = "not"
1219 AND = "and"
1220 OR = "or"
1221 LT = "lt"
1222 LE = "le"
1223 EQ = "eq"
1224 NE = "ne"
1225 GE = "ge"
1226 GT = "gt"
1227 LIKE = "like"
1228 BELONGS = "belongs"
1229 CONTAINS = "contains"
1230 ANYOF = "anyof"
1231 TYPEOF = "typeof"
1232 INTERSECTS = "intersects"
1233
1234 COMPARISON = [LT, LE, EQ, NE, GE, GT,
1235 LIKE, BELONGS, CONTAINS, ANYOF, TYPEOF, INTERSECTS]
1236
1237 OPERATORS = [NOT, AND, OR] + COMPARISON
1238
1239
1240 - def __init__(self, op, left=None, right=None):
1241 """ Constructor """
1242
1243 if op not in self.OPERATORS:
1244 raise SyntaxError("Invalid operator: %s" % op)
1245
1246 self.op = op
1247
1248 self.left = left
1249 self.right = right
1250
1251
1256
1257
1262
1263
1265 """ NOT """
1266
1267 if self.op == self.NOT:
1268 return self.left
1269 else:
1270 return S3ResourceQuery(self.NOT, self)
1271
1272
1273 - def _joins(self, resource, left=False):
1274
1275 op = self.op
1276 l = self.left
1277 r = self.right
1278
1279 if op in (self.AND, self.OR):
1280 if isinstance(l, S3ResourceQuery):
1281 ljoins, ld = l._joins(resource, left=left)
1282 else:
1283 ljoins, ld = {}, False
1284 if isinstance(r, S3ResourceQuery):
1285 rjoins, rd = r._joins(resource, left=left)
1286 else:
1287 rjoins, rd = {}, False
1288
1289 ljoins = dict(ljoins)
1290 ljoins.update(rjoins)
1291
1292 return (ljoins, ld or rd)
1293
1294 elif op == self.NOT:
1295 if isinstance(l, S3ResourceQuery):
1296 return l._joins(resource, left=left)
1297 else:
1298 return {}, False
1299
1300 joins, distinct = {}, False
1301
1302 if isinstance(l, S3FieldSelector):
1303 try:
1304 rfield = l.resolve(resource)
1305 except (SyntaxError, AttributeError):
1306 pass
1307 else:
1308 distinct = rfield.distinct
1309 if distinct and left or not distinct and not left:
1310 joins = rfield._joins
1311
1312 return (joins, distinct)
1313
1314
1316 """ Get all field selectors involved with this query """
1317
1318 op = self.op
1319 l = self.left
1320 r = self.right
1321
1322 if op in (self.AND, self.OR):
1323 lf = l.fields()
1324 rf = r.fields()
1325 return lf + rf
1326 elif op == self.NOT:
1327 return l.fields()
1328 elif isinstance(l, S3FieldSelector):
1329 return [l.name]
1330 else:
1331 return []
1332
1333
1334 - def split(self, resource):
1335 """
1336 Split this query into a real query and a virtual one (AND)
1337
1338 @param resource: the S3Resource
1339 @return: tuple (DAL-translatable sub-query, virtual filter),
1340 both S3ResourceQuery instances
1341 """
1342
1343 op = self.op
1344 l = self.left
1345 r = self.right
1346
1347 if op == self.AND:
1348 lq, lf = l.split(resource) \
1349 if isinstance(l, S3ResourceQuery) else (l, None)
1350 rq, rf = r.split(resource) \
1351 if isinstance(r, S3ResourceQuery) else (r, None)
1352 q = lq
1353 if rq is not None:
1354 if q is not None:
1355 q &= rq
1356 else:
1357 q = rq
1358 f = lf
1359 if rf is not None:
1360 if f is not None:
1361 f &= rf
1362 else:
1363 f = rf
1364 return q, f
1365 elif op == self.OR:
1366 lq, lf = l.split(resource) \
1367 if isinstance(l, S3ResourceQuery) else (l, None)
1368 rq, rf = r.split(resource) \
1369 if isinstance(r, S3ResourceQuery) else (r, None)
1370 if lf is not None or rf is not None:
1371 return None, self
1372 else:
1373 q = lq
1374 if rq is not None:
1375 if q is not None:
1376 q |= rq
1377 else:
1378 q = rq
1379 return q, None
1380 elif op == self.NOT:
1381 if isinstance(l, S3ResourceQuery):
1382 if l.op == self.OR:
1383 i = (~(l.left)) & (~(l.right))
1384 return i.split(resource)
1385 else:
1386 q, f = l.split(resource)
1387 if q is not None and f is not None:
1388 return None, self
1389 elif q is not None:
1390 return ~q, None
1391 elif f is not None:
1392 return None, ~f
1393 else:
1394 return ~l, None
1395
1396 l = self.left
1397 try:
1398 if isinstance(l, S3FieldSelector):
1399 lfield = l.resolve(resource)
1400 else:
1401 lfield = S3ResourceField(resource, l)
1402 except (SyntaxError, AttributeError):
1403 lfield = None
1404 if not lfield or lfield.field is None:
1405 return None, self
1406 else:
1407 return self, None
1408
1409
1419
1420
1421 - def query(self, resource):
1422 """
1423 Convert this S3ResourceQuery into a DAL query, ignoring virtual
1424 fields (the necessary joins for this query can be constructed
1425 with the joins() method)
1426
1427 @param resource: the resource to resolve the query against
1428 """
1429
1430 op = self.op
1431 l = self.left
1432 r = self.right
1433
1434
1435 if op == self.AND:
1436 l = l.query(resource) if isinstance(l, S3ResourceQuery) else l
1437 r = r.query(resource) if isinstance(r, S3ResourceQuery) else r
1438 if l is None or r is None:
1439 return None
1440 elif l is False or r is False:
1441 return l if r is False else r if l is False else False
1442 else:
1443 return l & r
1444 elif op == self.OR:
1445 l = l.query(resource) if isinstance(l, S3ResourceQuery) else l
1446 r = r.query(resource) if isinstance(r, S3ResourceQuery) else r
1447 if l is None or r is None:
1448 return None
1449 elif l is False or r is False:
1450 return l if r is False else r if l is False else False
1451 else:
1452 return l | r
1453 elif op == self.NOT:
1454 l = l.query(resource) if isinstance(l, S3ResourceQuery) else l
1455 if l is None:
1456 return None
1457 elif l is False:
1458 return False
1459 else:
1460 return ~l
1461
1462
1463 if isinstance(l, S3FieldSelector):
1464 try:
1465 rfield = S3ResourceField(resource, l.name)
1466 except (SyntaxError, AttributeError):
1467 return None
1468 if rfield.virtual:
1469 return None
1470 elif not rfield.field:
1471 return False
1472 lfield = l.expr(rfield.field)
1473 elif isinstance(l, Field):
1474 lfield = l
1475 else:
1476 return None
1477 if isinstance(r, S3FieldSelector):
1478 try:
1479 rfield = S3ResourceField(resource, r.name)
1480 except (SyntaxError, AttributeError):
1481 return None
1482 rfield = rfield.field
1483 if rfield.virtual:
1484 return None
1485 elif not rfield.field:
1486 return False
1487 rfield = r.expr(rfield.field)
1488 else:
1489 rfield = r
1490
1491
1492 invert = False
1493 query_bare = self._query_bare
1494 ftype = str(lfield.type)
1495 if isinstance(rfield, (list, tuple)) and ftype[:4] != "list":
1496 if op == self.EQ:
1497 op = self.BELONGS
1498 elif op == self.NE:
1499 op = self.BELONGS
1500 invert = True
1501 elif op not in (self.BELONGS, self.TYPEOF):
1502 query = None
1503 for v in rfield:
1504 q = query_bare(op, lfield, v)
1505 if q is not None:
1506 if query is None:
1507 query = q
1508 else:
1509 query |= q
1510 return query
1511
1512
1513 if ftype in ("date", "datetime") and isinstance(rfield, basestring):
1514 to_type = datetime.date if ftype == "date" else datetime.datetime
1515 rfield = S3TypeConverter.convert(to_type, rfield)
1516
1517
1518 if op == self.EQ and rfield is not None and \
1519 (ftype == "id" or ftype[:9] == "reference"):
1520 try:
1521 rfield = long(rfield)
1522 except (ValueError, TypeError):
1523
1524
1525 rfield = 0
1526
1527 query = query_bare(op, lfield, rfield)
1528 if invert and query is not None:
1529 query = ~query
1530 return query
1531
1532
1534 """
1535 Translate a filter expression into a DAL query
1536
1537 @param op: the operator
1538 @param l: the left operand
1539 @param r: the right operand
1540 """
1541
1542 if op == self.CONTAINS:
1543 q = l.contains(r, all=True)
1544 elif op == self.ANYOF:
1545
1546 q = l.contains(r, all=False)
1547 elif op == self.BELONGS:
1548 q = self._query_belongs(l, r)
1549 elif op == self.TYPEOF:
1550 q = self._query_typeof(l, r)
1551 elif op == self.LIKE:
1552 if current.deployment_settings.get_database_airegex():
1553 q = S3AIRegex.like(l, r)
1554 else:
1555 q = l.like(s3_unicode(r))
1556 elif op == self.INTERSECTS:
1557 q = self._query_intersects(l, r)
1558 elif op == self.LT:
1559 q = l < r
1560 elif op == self.LE:
1561 q = l <= r
1562 elif op == self.EQ:
1563 q = l == r
1564 elif op == self.NE:
1565 q = l != r
1566 elif op == self.GE:
1567 q = l >= r
1568 elif op == self.GT:
1569 q = l > r
1570 else:
1571 q = None
1572 return q
1573
1574
1576 """
1577 Translate TYPEOF into DAL expression
1578
1579 @param l: the left operand
1580 @param r: the right operand
1581 """
1582
1583 hierarchy, field, nodeset, none = self._resolve_hierarchy(l, r)
1584 if not hierarchy:
1585
1586 return self._query_belongs(l, r)
1587 if not field:
1588
1589 return None
1590
1591
1592 list_type = str(field.type)[:5] == "list:"
1593 if nodeset:
1594 if list_type:
1595 q = (field.contains(list(nodeset)))
1596 elif len(nodeset) > 1:
1597 q = (field.belongs(nodeset))
1598 else:
1599 q = (field == tuple(nodeset)[0])
1600 else:
1601 q = None
1602
1603 if none:
1604
1605 if not list_type:
1606 if q is None:
1607 q = (field == None)
1608 else:
1609 q |= (field == None)
1610 if q is None:
1611
1612 q = field.belongs(set())
1613
1614 return q
1615
1616
1617 @classmethod
1619 """
1620 Resolve the hierarchical lookup in a typeof-query
1621
1622 @param l: the left operand
1623 @param r: the right operand
1624 """
1625
1626 from s3hierarchy import S3Hierarchy
1627
1628 tablename = l.tablename
1629
1630
1631 hierarchy = S3Hierarchy(tablename)
1632 if hierarchy.config is None:
1633
1634 ktablename, key = s3_get_foreign_key(l)[:2]
1635 if ktablename:
1636 hierarchy = S3Hierarchy(ktablename)
1637 else:
1638 key = None
1639
1640 list_type = str(l.type)[:5] == "list:"
1641 if hierarchy.config is None and not list_type:
1642
1643 return False, None, None, None
1644
1645 field, keys = l, r
1646
1647 if not key:
1648
1649 s3db = current.s3db
1650
1651 table = s3db[tablename]
1652 if l.name != table._id.name:
1653
1654
1655
1656 fs = S3FieldSelector(l.name)
1657 if list_type:
1658 expr = fs.contains(r)
1659 else:
1660 expr = cls._query_belongs(l, r, field = fs)
1661
1662
1663 resource = s3db.resource(tablename)
1664 if expr is not None:
1665 subquery = expr.query(resource)
1666 else:
1667 subquery = None
1668 if not subquery:
1669
1670 return True, None, None, None
1671
1672
1673 DELETED = current.xml.DELETED
1674 if DELETED in table.fields:
1675 subquery &= table[DELETED] != True
1676 rows = current.db(subquery).select(table._id)
1677
1678
1679 field = table[hierarchy.pkey.name]
1680 keys = set([row[table._id.name] for row in rows])
1681
1682 nodeset, none = None, False
1683 if keys:
1684
1685 none = False
1686 if not isinstance(keys, (list, tuple, set)):
1687 keys = set([keys])
1688 nodes = set()
1689 for node in keys:
1690 if node is None:
1691 none = True
1692 else:
1693 try:
1694 node_id = long(node)
1695 except ValueError:
1696 continue
1697 nodes.add(node_id)
1698 if hierarchy.config is not None:
1699 nodeset = hierarchy.findall(nodes, inclusive=True)
1700 else:
1701 nodeset = nodes
1702
1703 elif keys is None:
1704 none = True
1705
1706 return True, field, nodeset, none
1707
1708
1709 @staticmethod
1711 """
1712 Resolve BELONGS into a DAL expression (or S3ResourceQuery if
1713 field is an S3FieldSelector)
1714
1715 @param l: the left operand
1716 @param r: the right operand
1717 @param field: alternative left operand
1718 """
1719
1720 if field is None:
1721 field = l
1722
1723 expr = None
1724 none = False
1725
1726 if not isinstance(r, (list, tuple, set)):
1727 items = [r]
1728 else:
1729 items = r
1730 if None in items:
1731 none = True
1732 items = [item for item in items if item is not None]
1733
1734 wildcard = False
1735
1736 if str(l.type) in ("string", "text"):
1737 for item in items:
1738 if isinstance(item, basestring):
1739 if "*" in item and "%" not in item:
1740 s = item.replace("*", "%")
1741 else:
1742 s = item
1743 else:
1744 s = s3_str(item)
1745
1746 if "%" in s:
1747 wildcard = True
1748 _expr = (field.like(s))
1749 else:
1750 _expr = (field == s)
1751
1752 if expr is None:
1753 expr = _expr
1754 else:
1755 expr |= _expr
1756
1757 if not wildcard:
1758 if len(items) == 1:
1759
1760 expr = (field == tuple(items)[0])
1761 elif items:
1762 expr = (field.belongs(items))
1763
1764 if none:
1765
1766 if expr is None:
1767 expr = (field == None)
1768 else:
1769 expr |= (field == None)
1770 elif expr is None:
1771 expr = field.belongs(set())
1772
1773 return expr
1774
1775
1777 """
1778 Resolve INTERSECTS into a DAL expression;
1779 will be ignored for non-spatial DBs
1780
1781 @param l: the left operand (Field)
1782 @param r: the right operand
1783 """
1784
1785 if current.deployment_settings.get_gis_spatialdb():
1786
1787 expr = None
1788
1789 if str(l.type)[:3] == "geo":
1790
1791 if isinstance(r, basestring):
1792
1793
1794
1795 from shapely.wkt import loads as wkt_loads
1796 try:
1797 wkt_loads(r)
1798 except Exception:
1799
1800
1801
1802 current.log.error("INTERSECTS: %s" % sys.exc_info()[1])
1803 else:
1804 expr = l.st_intersects(r)
1805
1806 elif hasattr(r, type) and str(r.type)[:3] == "geo":
1807
1808 expr = l.st_intersects(r)
1809
1810 if expr is None:
1811
1812 return l.belongs(set())
1813
1814 else:
1815
1816 expr = False
1817
1818 return expr
1819
1820
1821 - def __call__(self, resource, row, virtual=True):
1822 """
1823 Probe whether the row matches the query
1824
1825 @param resource: the resource to resolve the query against
1826 @param row: the DB row
1827 @param virtual: execute only virtual queries
1828 """
1829
1830 if self.op == self.AND:
1831 l = self.left(resource, row, virtual=False)
1832 r = self.right(resource, row, virtual=False)
1833 if l is None:
1834 return r
1835 if r is None:
1836 return l
1837 return l and r
1838 elif self.op == self.OR:
1839 l = self.left(resource, row, virtual=False)
1840 r = self.right(resource, row, virtual=False)
1841 if l is None:
1842 return r
1843 if r is None:
1844 return l
1845 return l or r
1846 elif self.op == self.NOT:
1847 l = self.left(resource, row)
1848 if l is None:
1849 return None
1850 else:
1851 return not l
1852
1853 real = False
1854 left = self.left
1855 if isinstance(left, S3FieldSelector):
1856 try:
1857 lfield = left.resolve(resource)
1858 except (AttributeError, KeyError, SyntaxError):
1859 return None
1860 if lfield.field is not None:
1861 real = True
1862 elif not lfield.virtual:
1863
1864 return None
1865 else:
1866 lfield = left
1867 if isinstance(left, Field):
1868 real = True
1869 right = self.right
1870 if isinstance(right, S3FieldSelector):
1871 try:
1872 rfield = right.resolve(resource)
1873 except (AttributeError, KeyError, SyntaxError):
1874 return None
1875 if rfield.virtual:
1876 real = False
1877 elif rfield.field is None:
1878
1879 return None
1880 else:
1881 rfield = right
1882 if virtual and real:
1883 return None
1884
1885 extract = lambda f: S3FieldSelector.extract(resource, row, f)
1886 try:
1887 l = extract(lfield)
1888 r = extract(rfield)
1889 except (KeyError, SyntaxError):
1890 current.log.error(sys.exc_info()[1])
1891 return None
1892
1893 if isinstance(left, S3FieldSelector):
1894 l = left.expr(l)
1895 if isinstance(right, S3FieldSelector):
1896 r = right.expr(r)
1897
1898 op = self.op
1899 invert = False
1900 probe = self._probe
1901 if isinstance(rfield, (list, tuple)) and \
1902 not isinstance(lfield, (list, tuple)):
1903 if op == self.EQ:
1904 op = self.BELONGS
1905 elif op == self.NE:
1906 op = self.BELONGS
1907 invert = True
1908 elif op != self.BELONGS:
1909 for v in r:
1910 try:
1911 r = probe(op, l, v)
1912 except (TypeError, ValueError):
1913 r = False
1914 if r:
1915 return True
1916 return False
1917 try:
1918 r = probe(op, l, r)
1919 except (TypeError, ValueError):
1920 return False
1921 if invert and r is not None:
1922 return not r
1923 else:
1924 return r
1925
1926
1928 """
1929 Probe whether the value pair matches the query
1930
1931 @param l: the left value
1932 @param r: the right value
1933 """
1934
1935 result = False
1936 convert = S3TypeConverter.convert
1937
1938
1939 if op == self.TYPEOF:
1940 if isinstance(l, (list, tuple, set)):
1941 op = self.ANYOF
1942 elif isinstance(r, (list, tuple, set)):
1943 op = self.BELONGS
1944 else:
1945 op = self.EQ
1946
1947 if op == self.CONTAINS:
1948 r = convert(l, r)
1949 result = self._probe_contains(l, r)
1950
1951 elif op == self.ANYOF:
1952 if not isinstance(r, (list, tuple, set)):
1953 r = [r]
1954 for v in r:
1955 if isinstance(l, (list, tuple, set, basestring)):
1956 if self._probe_contains(l, v):
1957 return True
1958 elif l == v:
1959 return True
1960 return False
1961
1962 elif op == self.BELONGS:
1963 if not isinstance(r, (list, tuple, set)):
1964 r = [r]
1965 r = convert(l, r)
1966 result = self._probe_contains(r, l)
1967
1968 elif op == self.LIKE:
1969
1970 pattern = re.escape(str(r)).replace("\\%", ".*").replace(".*.*", "\\%")
1971 return re.match(pattern, str(l)) is not None
1972
1973 else:
1974 r = convert(l, r)
1975 if op == self.LT:
1976 result = l < r
1977 elif op == self.LE:
1978 result = l <= r
1979 elif op == self.EQ:
1980 result = l == r
1981 elif op == self.NE:
1982 result = l != r
1983 elif op == self.GE:
1984 result = l >= r
1985 elif op == self.GT:
1986 result = l > r
1987
1988 return result
1989
1990
1991 @staticmethod
1993 """
1994 Probe whether a contains b
1995 """
1996
1997 if a is None:
1998 return False
1999
2000 if isinstance(a, basestring):
2001 return s3_str(b) in s3_str(a)
2002
2003 if isinstance(a, (list, tuple, set)):
2004 if isinstance(b, (list, tuple, set)):
2005 convert = S3TypeConverter.convert
2006 found = True
2007 for b_item in b:
2008 if b_item not in a:
2009 found = False
2010 for a_item in a:
2011 try:
2012 if convert(a_item, b_item) == a_item:
2013 found = True
2014 break
2015 except (TypeError, ValueError):
2016 continue
2017 if not found:
2018 break
2019 return found
2020 else:
2021 return b in a
2022 else:
2023 return s3_str(b) in s3_str(a)
2024
2025
2027 """
2028 Represent this query as a human-readable string.
2029
2030 @param resource: the resource to resolve the query against
2031 """
2032
2033 op = self.op
2034 l = self.left
2035 r = self.right
2036 if op == self.AND:
2037 l = l.represent(resource) \
2038 if isinstance(l, S3ResourceQuery) else str(l)
2039 r = r.represent(resource) \
2040 if isinstance(r, S3ResourceQuery) else str(r)
2041 return "(%s and %s)" % (l, r)
2042 elif op == self.OR:
2043 l = l.represent(resource) \
2044 if isinstance(l, S3ResourceQuery) else str(l)
2045 r = r.represent(resource) \
2046 if isinstance(r, S3ResourceQuery) else str(r)
2047 return "(%s or %s)" % (l, r)
2048 elif op == self.NOT:
2049 l = l.represent(resource) \
2050 if isinstance(l, S3ResourceQuery) else str(l)
2051 return "(not %s)" % l
2052 else:
2053 if isinstance(l, S3FieldSelector):
2054 l = l.represent(resource)
2055 elif isinstance(l, basestring):
2056 l = '"%s"' % l
2057 if isinstance(r, S3FieldSelector):
2058 r = r.represent(resource)
2059 elif isinstance(r, basestring):
2060 r = '"%s"' % r
2061 if op == self.CONTAINS:
2062 return "(%s in %s)" % (r, l)
2063 elif op == self.BELONGS:
2064 return "(%s in %s)" % (l, r)
2065 elif op == self.ANYOF:
2066 return "(%s contains any of %s)" % (l, r)
2067 elif op == self.TYPEOF:
2068 return "(%s is a type of %s)" % (l, r)
2069 elif op == self.LIKE:
2070 return "(%s like %s)" % (l, r)
2071 elif op == self.LT:
2072 return "(%s < %s)" % (l, r)
2073 elif op == self.LE:
2074 return "(%s <= %s)" % (l, r)
2075 elif op == self.EQ:
2076 return "(%s == %s)" % (l, r)
2077 elif op == self.NE:
2078 return "(%s != %s)" % (l, r)
2079 elif op == self.GE:
2080 return "(%s >= %s)" % (l, r)
2081 elif op == self.GT:
2082 return "(%s > %s)" % (l, r)
2083 else:
2084 return "(%s ?%s? %s)" % (l, op, r)
2085
2086
2088 """
2089 Serialize this query as URL query
2090
2091 @return: a Storage of URL variables
2092 """
2093
2094 op = self.op
2095 l = self.left
2096 r = self.right
2097
2098 url_query = Storage()
2099 def _serialize(n, o, v, invert):
2100 try:
2101 quote = lambda s: s if "," not in s else '"%s"' % s
2102 if isinstance(v, list):
2103 v = ",".join([quote(S3TypeConverter.convert(str, val))
2104 for val in v])
2105 else:
2106 v = quote(S3TypeConverter.convert(str, v))
2107 except:
2108 return
2109 if "." not in n:
2110 if resource is not None:
2111 n = "~.%s" % n
2112 else:
2113 return url_query
2114 if o == self.LIKE:
2115 v = v.replace("%", "*")
2116 if o == self.EQ:
2117 operator = ""
2118 else:
2119 operator = "__%s" % o
2120 if invert:
2121 operator = "%s!" % operator
2122 key = "%s%s" % (n, operator)
2123 if key in url_query:
2124 url_query[key] = "%s,%s" % (url_query[key], v)
2125 else:
2126 url_query[key] = v
2127 return url_query
2128 if op == self.AND:
2129 lu = l.serialize_url(resource=resource)
2130 url_query.update(lu)
2131 ru = r.serialize_url(resource=resource)
2132 url_query.update(ru)
2133 elif op == self.OR:
2134 sub = self._or()
2135 if sub is None:
2136
2137 return url_query
2138 n, o, v, invert = sub
2139 _serialize(n, o, v, invert)
2140 elif op == self.NOT:
2141 lu = l.serialize_url(resource=resource)
2142 for k in lu:
2143 url_query["%s!" % k] = lu[k]
2144 elif isinstance(l, S3FieldSelector):
2145 _serialize(l.name, op, r, False)
2146 return url_query
2147
2148
2150 """
2151 Helper method to URL-serialize an OR-subtree in a query in
2152 alternative field selector syntax if they all use the same
2153 operator and value (this is needed to URL-serialize an
2154 S3SearchSimpleWidget query).
2155 """
2156
2157 op = self.op
2158 l = self.left
2159 r = self.right
2160
2161 if op == self.AND:
2162 return None
2163 elif op == self.NOT:
2164 lname, lop, lval, linv = l._or()
2165 return (lname, lop, lval, not linv)
2166 elif op == self.OR:
2167 lvars = l._or()
2168 rvars = r._or()
2169 if lvars is None or rvars is None:
2170 return None
2171 lname, lop, lval, linv = lvars
2172 rname, rop, rval, rinv = rvars
2173 if lop != rop or linv != rinv:
2174 return None
2175 if lname == rname:
2176 return (lname, lop, [lval, rval], linv)
2177 elif lval == rval:
2178 return ("%s|%s" % (lname, rname), lop, lval, linv)
2179 else:
2180 return None
2181 else:
2182 return (l.name, op, r, False)
2183
2186 """ URL Query Parser """
2187
2188 FILTEROP = re.compile(r"__(?!link\.)([_a-z\!]+)$")
2189
2190
2191 @classmethod
2192 - def parse(cls, resource, get_vars):
2193 """
2194 Construct a Storage of S3ResourceQuery from a Storage of get_vars
2195
2196 @param resource: the S3Resource
2197 @param get_vars: the get_vars
2198 @return: Storage of S3ResourceQuery like {alias: query}, where
2199 alias is the alias of the component the query concerns
2200 """
2201
2202 query = Storage()
2203
2204 if resource is None or not get_vars:
2205 return query
2206
2207 subquery = cls._subquery
2208 allof = lambda l, r: l if r is None else r if l is None else r & l
2209
2210 for key, value in get_vars.iteritems():
2211
2212 if not key:
2213 continue
2214
2215 elif key == "$filter":
2216
2217 parser = S3URLQueryParser()
2218 if parser.parser is None:
2219
2220 continue
2221
2222
2223 expressions = value if type(value) is list else [value]
2224
2225
2226 default_alias = resource.alias
2227
2228
2229 for expression in expressions:
2230 parsed = parser.parse(expression)
2231 for alias in parsed:
2232 q = parsed[alias]
2233 qalias = alias if alias is not None else default_alias
2234 if qalias not in query:
2235 query[qalias] = [q]
2236 else:
2237 query[qalias].append(q)
2238
2239
2240 continue
2241
2242 elif key[0] == "_" or \
2243 not("." in key or key[0] == "(" and ")" in key):
2244
2245 continue
2246
2247
2248 selectors, op, invert = cls.parse_expression(key)
2249
2250 if type(value) is list:
2251
2252 q = reduce(allof,
2253 [subquery(selectors, op, invert, v) for v in value],
2254 None)
2255 else:
2256 q = subquery(selectors, op, invert, value)
2257
2258 if q is None:
2259 continue
2260
2261
2262 if len(selectors) > 1:
2263 aliases = [s.split(".", 1)[0] for s in selectors]
2264 if len(set(aliases)) == 1:
2265 alias = aliases[0]
2266 else:
2267 alias = resource.alias
2268
2269 else:
2270 alias = selectors[0].split(".", 1)[0]
2271 if alias == "~":
2272 alias = resource.alias
2273 if alias not in query:
2274 query[alias] = [q]
2275 else:
2276 query[alias].append(q)
2277
2278 return query
2279
2280
2281 @staticmethod
2283 """
2284 Parse a URL query into get_vars
2285
2286 @param query: the URL query string
2287 @return: the get_vars (Storage)
2288 """
2289
2290 if not url:
2291 return Storage()
2292 elif "?" in url:
2293 query = url.split("?", 1)[1]
2294 elif "=" in url:
2295 query = url
2296 else:
2297 return Storage()
2298
2299 import urlparse
2300 dget = urlparse.parse_qsl(query, keep_blank_values=1)
2301
2302 get_vars = Storage()
2303 for (key, value) in dget:
2304 if key in get_vars:
2305 if type(get_vars[key]) is list:
2306 get_vars[key].append(value)
2307 else:
2308 get_vars[key] = [get_vars[key], value]
2309 else:
2310 get_vars[key] = value
2311 return get_vars
2312
2313
2314 @classmethod
2316 """
2317 Parse a URL filter key
2318
2319 @param key: the filter key
2320
2321 @return: tuple (selector, operator, invert)
2322 """
2323
2324 if key[-1] == "!":
2325 invert = True
2326 else:
2327 invert = False
2328
2329 fs = key.rstrip("!")
2330 op = None
2331
2332
2333 m = cls.FILTEROP.search(fs)
2334 if m:
2335 op = m.group(0).strip("_")
2336 fs = fs[:m.span(0)[0]]
2337 else:
2338 fs = fs.rstrip("_")
2339 if not op:
2340 op = "eq"
2341
2342 return fs, op, invert
2343
2344
2345 @classmethod
2347 """
2348 Parse a URL filter key, separating multiple field selectors
2349 if the key specifies alternatives
2350
2351 @param key: the filter key
2352
2353 @return: tuple ([field selectors], operator, invert)
2354 """
2355
2356 fs, op, invert = cls.parse_key(key)
2357
2358 if "|" in fs:
2359 selectors = [s for s in fs.split("|") if s]
2360 else:
2361 selectors = [fs]
2362
2363 return selectors, op, invert
2364
2365
2366 @staticmethod
2368 """
2369 Parse a URL query value
2370
2371 @param value: the value
2372 @return: the parsed value
2373 """
2374
2375 uquote = lambda w: w.replace('\\"', '\\"\\') \
2376 .strip('"') \
2377 .replace('\\"\\', '"')
2378 NONE = ("NONE", "None")
2379 if type(value) is not list:
2380 value = [value]
2381 vlist = []
2382 for item in value:
2383 w = ""
2384 quote = False
2385 ignore_quote = False
2386 for c in s3_unicode(item):
2387 if c == '"' and not ignore_quote:
2388 w += c
2389 quote = not quote
2390 elif c == "," and not quote:
2391 if w in NONE:
2392 w = None
2393 else:
2394 w = uquote(w).encode("utf-8")
2395 vlist.append(w)
2396 w = ""
2397 else:
2398 w += c
2399 if c == "\\":
2400 ignore_quote = True
2401 else:
2402 ignore_quote = False
2403 if w in NONE:
2404 w = None
2405 else:
2406 w = uquote(w).encode("utf-8")
2407 vlist.append(w)
2408 if len(vlist) == 1:
2409 return vlist[0]
2410 return vlist
2411
2412
2413 @classmethod
2414 - def _subquery(cls, selectors, op, invert, value):
2415 """
2416 Construct a sub-query from URL selectors, operator and value
2417
2418 @param selectors: the selector(s)
2419 @param op: the operator
2420 @param invert: invert the query
2421 @param value: the value
2422 """
2423
2424 v = cls.parse_value(value)
2425
2426
2427 like = lambda s: s3_unicode(s).lower() \
2428 .replace("%", "\\%") \
2429 .replace("_", "\\_") \
2430 .replace("?", "_") \
2431 .replace("*", "%") \
2432 .encode("utf-8")
2433
2434 q = None
2435
2436
2437 escaped = False
2438
2439 for fs in selectors:
2440
2441 if op == S3ResourceQuery.LIKE:
2442 f = S3FieldSelector(fs).lower()
2443 if not escaped:
2444 if isinstance(v, basestring):
2445 v = like(v)
2446 elif isinstance(v, list):
2447 v = [like(s) for s in v if s is not None]
2448 escaped = True
2449 else:
2450 f = S3FieldSelector(fs)
2451
2452 rquery = None
2453 try:
2454 rquery = S3ResourceQuery(op, f, v)
2455 except SyntaxError:
2456 current.log.error("Invalid URL query operator: %s (sub-query ignored)" % op)
2457 q = None
2458 break
2459
2460
2461 if invert:
2462 rquery = ~rquery
2463
2464
2465 if q is None:
2466 q = rquery
2467 elif invert:
2468 q &= rquery
2469 else:
2470 q |= rquery
2471
2472 return q
2473
2476 """
2477 Helper class to construct quasi-accent-insensitive text search
2478 queries based on SQL regular expressions (REGEXP).
2479
2480 Important: This will return complete nonsense if the REGEXP
2481 implementation of the DBMS is not multibyte-safe,
2482 so it must be suppressed for those cases (see also
2483 modules/s3config.py)!
2484 """
2485
2486
2487 GROUPS = (
2488 u"aăâåãáàẩắằầảẳẵẫấạặậǻ",
2489 u"äæ",
2490 u"cçćĉ",
2491 u"dđð",
2492 u"eêèềẻểẽễéếẹệë",
2493 u"gǵĝ",
2494 u"hĥ",
2495 u"iìỉĩíịîïİ",
2496 u"jĵ",
2497 u"kḱ",
2498 u"lĺ",
2499 u"mḿ",
2500 u"nñńǹ",
2501 u"oôơòồờỏổởõỗỡóốớọộợ",
2502 u"öøǿ",
2503 u"pṕ",
2504 u"rŕ",
2505 u"sśŝ",
2506 u"tẗ",
2507 u"uưùừủửũữúứụựứüǘûǜ",
2508 u"wẃŵẁ",
2509 u"yỳỷỹýỵÿŷ",
2510 u"zźẑ",
2511 )
2512
2513 ESCAPE = ".*$^[](){}\\+?"
2514
2515
2516 @classmethod
2517 - def like(cls, l, r):
2518 """
2519 Query constructor
2520
2521 @param l: the left operand
2522 @param r: the right operand (string)
2523 """
2524
2525 string = cls.translate(r)
2526 if string:
2527 return l.lower().regexp("^%s$" % string)
2528 else:
2529 return l.like(r)
2530
2531
2532 @classmethod
2534 """
2535 Helper method to translate the search string into a regular
2536 expression
2537
2538 @param string: the search string
2539 """
2540
2541 if not string:
2542 return None
2543
2544 match = False
2545 output = []
2546 append = output.append
2547
2548 GROUPS = cls.GROUPS
2549 ESCAPE = cls.ESCAPE
2550
2551 escaped = False
2552 for character in s3_unicode(string).lower():
2553
2554 result = None
2555
2556
2557 if not escaped:
2558 if character == "\\":
2559 escaped = True
2560 continue
2561 elif character == "%":
2562 result = ".*"
2563 elif character == "_":
2564 result = "."
2565
2566 if result is None:
2567 if character in ESCAPE:
2568 result = "\\%s" % character
2569 else:
2570 result = character
2571 for group in GROUPS:
2572 if character in group:
2573 match = True
2574 result = "[%s%s]{1}" % (group, group.upper())
2575 break
2576
2577
2578 if escaped and character not in ("%", "_"):
2579 result = "\\%s" % result
2580
2581 escaped = False
2582 append(result)
2583
2584 return "".join(output) if match else None
2585
2586
2587
2588
2589 combine = lambda x, y: x & y if x is not None else y
2593 """ New-style URL Filter Parser """
2594
2596 """ Constructor """
2597
2598 self.parser = None
2599 self.ParseResults = None
2600 self.ParseException = None
2601
2602 self._parser()
2603
2604
2606 """ Import PyParsing and define the syntax for filter expressions """
2607
2608
2609 try:
2610 import pyparsing as pp
2611 except ImportError:
2612 current.log.error("Advanced filter syntax requires pyparsing, $filter ignored")
2613 return False
2614
2615
2616 context = lambda s, l, t: t[0].replace("[", "(").replace("]", ")")
2617 selector = pp.Word(pp.alphas + "[]~", pp.alphanums + "_.$:[]")
2618 selector.setParseAction(context)
2619
2620 keyword = lambda x, y: x | pp.Keyword(y) if x else pp.Keyword(y)
2621
2622
2623 function = reduce(keyword, S3FieldSelector.OPERATORS)
2624 expression = function + \
2625 pp.Literal("(").suppress() + \
2626 selector + \
2627 pp.Literal(")").suppress()
2628
2629
2630 comparison = reduce(keyword, S3ResourceQuery.COMPARISON)
2631
2632
2633 number = pp.Regex(r"[+-]?\d+(:?\.\d*)?(:?[eE][+-]?\d+)?")
2634 value = number | \
2635 pp.Keyword("NONE") | \
2636 pp.quotedString | \
2637 pp.Word(pp.alphanums + pp.printables)
2638 qe = pp.Group(pp.Group(expression | selector) +
2639 comparison +
2640 pp.originalTextFor(pp.delimitedList(value, combine=True)))
2641
2642 parser = pp.operatorPrecedence(qe, [("not", 1, pp.opAssoc.RIGHT, ),
2643 ("and", 2, pp.opAssoc.LEFT, ),
2644 ("or", 2, pp.opAssoc.LEFT, ),
2645 ])
2646
2647 self.parser = parser
2648 self.ParseResults = pp.ParseResults
2649 self.ParseException = pp.ParseException
2650
2651 return True
2652
2653
2654 - def parse(self, expression):
2655 """
2656 Parse a string expression and convert it into a dict
2657 of filters (S3ResourceQueries).
2658
2659 @parameter expression: the filter expression as string
2660 @return: a dict of {component_alias: filter_query}
2661 """
2662
2663 query = {}
2664
2665 parser = self.parser
2666 if not expression or parser is None:
2667 return query
2668
2669 try:
2670 parsed = parser.parseString(expression)
2671 except self.ParseException:
2672 current.log.error("Invalid URL Filter Expression: '%s'" %
2673 expression)
2674 else:
2675 if parsed:
2676 query = self.convert_expression(parsed[0])
2677 return query
2678
2679
2681 """
2682 Convert a parsed filter expression into a dict of
2683 filters (S3ResourceQueries)
2684
2685 @param expression: the parsed filter expression (ParseResults)
2686 @returns: a dict of {component_alias: filter_query}
2687 """
2688
2689 ParseResults = self.ParseResults
2690 convert = self.convert_expression
2691
2692 if isinstance(expression, ParseResults):
2693 first, op, second = ([None, None, None] + list(expression))[-3:]
2694
2695 if isinstance(first, ParseResults):
2696 first = convert(first)
2697 if isinstance(second, ParseResults):
2698 second = convert(second)
2699
2700 if op == "not":
2701 return self._not(second)
2702 elif op == "and":
2703 return self._and(first, second)
2704 elif op == "or":
2705 return self._or(first, second)
2706 elif op in S3ResourceQuery.COMPARISON:
2707 return self._query(op, first, second)
2708 elif op in S3FieldSelector.OPERATORS and second:
2709 selector = S3FieldSelector(second)
2710 selector.op = op
2711 return selector
2712 elif op is None and second:
2713 return S3FieldSelector(second)
2714 else:
2715 return None
2716
2717
2718 - def _and(self, first, second):
2719 """
2720 Conjunction of two query {component_alias: filter_query} (AND)
2721
2722 @param first: the first dict
2723 @param second: the second dict
2724 @return: the combined dict
2725 """
2726
2727 if not first:
2728 return second
2729 if not second:
2730 return first
2731
2732 result = dict(first)
2733
2734 for alias, subquery in second.items():
2735 if alias not in result:
2736 result[alias] = subquery
2737 else:
2738 result[alias] &= subquery
2739 return result
2740
2741
2742 - def _or(self, first, second):
2743 """
2744 Disjunction of two query dicts {component_alias: filter_query} (OR)
2745
2746 @param first: the first query dict
2747 @param second: the second query dict
2748 @return: the combined dict
2749 """
2750
2751 if not first:
2752 return second
2753 if not second:
2754 return first
2755
2756 if len(first) > 1:
2757 first = {None: reduce(combine, first.values())}
2758 if len(second) > 1:
2759 second = {None: reduce(combine, second.values())}
2760
2761 falias = first.keys()[0]
2762 salias = second.keys()[0]
2763
2764 alias = falias if falias == salias else None
2765 return {alias: first[falias] | second[salias]}
2766
2767
2768 - def _not(self, query):
2769 """
2770 Negation of a query dict
2771
2772 @param query: the query dict {component_alias: filter_query}
2773 """
2774
2775 if query is None:
2776 return None
2777
2778 if len(query) == 1:
2779
2780 alias, sub = query.items()[0]
2781
2782 if sub.op == S3ResourceQuery.OR and alias is None:
2783
2784 lalias = self._alias(sub.left.left)
2785 ralias = self._alias(sub.right.left)
2786
2787 if lalias == ralias:
2788 return {alias: ~sub}
2789 else:
2790
2791 return {lalias: ~sub.left, ralias: ~sub.right}
2792 else:
2793 if sub.op == S3ResourceQuery.NOT:
2794 return {alias: sub.left}
2795 else:
2796 return {alias: ~sub}
2797 else:
2798 return {None: ~reduce(combine, query.values())}
2799
2800
2801 - def _query(self, op, first, second):
2802 """
2803 Create an S3ResourceQuery
2804
2805 @param op: the operator
2806 @param first: the first operand (=S3FieldSelector)
2807 @param second: the second operand (=value)
2808 """
2809
2810 if not isinstance(first, S3FieldSelector):
2811 return {}
2812
2813 selector = first
2814
2815 alias = self._alias(selector)
2816
2817 value = S3URLQuery.parse_value(second.strip())
2818 if op == S3ResourceQuery.LIKE:
2819 selector.lower()
2820 if isinstance(value, basestring):
2821 value = value.replace("*", "%").lower()
2822 elif isinstance(value, list):
2823 value = [x.replace("*", "%").lower() for x in value if x is not None]
2824
2825 return {alias: S3ResourceQuery(op, selector, value)}
2826
2827
2828 @staticmethod
2830 """
2831 Get the component alias from an S3FieldSelector (DRY Helper)
2832
2833 @param selector: the S3FieldSelector
2834 @return: the alias as string or None for the master resource
2835 """
2836
2837 alias = None
2838 if selector and isinstance(selector, S3FieldSelector):
2839 prefix = selector.name.split("$", 1)[0]
2840 if "." in prefix:
2841 alias = prefix.split(".", 1)[0]
2842 if alias in ("~", ""):
2843 alias = None
2844 return alias
2845
2846
2847