Returns an SQL WHERE comparison string using =, i=, !=, >, <, >=, <= or BETWEEN. Strings may contain wildcards (*) at the start or at the end. A list or tuple of values can be given when using =, != or BETWEEN.
(field, value, comparison="=", escape=lambda v: _escape(v), table="")
| 1032 | return _format(field) |
| 1033 | |
| 1034 | def cmp(field, value, comparison="=", escape=lambda v: _escape(v), table=""): |
| 1035 | """ Returns an SQL WHERE comparison string using =, i=, !=, >, <, >=, <= or BETWEEN. |
| 1036 | Strings may contain wildcards (*) at the start or at the end. |
| 1037 | A list or tuple of values can be given when using =, != or BETWEEN. |
| 1038 | """ |
| 1039 | # Use absolute field names if table name is given: |
| 1040 | if table: |
| 1041 | field = abs(table, field) |
| 1042 | # cmp("name", "Mar*") => "name like 'Mar%'". |
| 1043 | if isinstance(value, basestring) and (value.startswith(("*","%")) or value.endswith(("*","%"))): |
| 1044 | if comparison in ("=", "i=", "==", LIKE): |
| 1045 | return "%s like %s" % (field, escape(value.replace("*","%"))) |
| 1046 | if comparison in ("!=", "<>"): |
| 1047 | return "%s not like %s" % (field, escape(value.replace("*","%"))) |
| 1048 | # cmp("name", "markov") => "name" like 'markov'" (case-insensitive). |
| 1049 | if isinstance(value, basestring): |
| 1050 | if comparison == "i=": |
| 1051 | return "%s like %s" % (field, escape(value)) |
| 1052 | # cmp("type", ("cat", "dog"), "!=") => "type not in ('cat','dog')". |
| 1053 | # cmp("amount", (10, 100), ":") => "amount between 10 and 100". |
| 1054 | if isinstance(value, (list, tuple)): |
| 1055 | if find(lambda v: isinstance(v, basestring) and (v.startswith("*") or v.endswith("*")), value): |
| 1056 | return "(%s)" % any(*[(field, v) for v in value]).sql(escape=escape) |
| 1057 | if comparison in ("=", "==", IN): |
| 1058 | return "%s in (%s)" % (field, ",".join(escape(v) for v in value)) |
| 1059 | if comparison in ("!=", "<>"): |
| 1060 | return "%s not in (%s)" % (field, ",".join(escape(v) for v in value)) |
| 1061 | if comparison in (":", BETWEEN): |
| 1062 | return "%s between %s and %s" % (field, escape(value[0]), escape(value[1])) |
| 1063 | # cmp("type", None, "!=") => "type is not null". |
| 1064 | if isinstance(value, type(None)): |
| 1065 | if comparison in ("=", "=="): |
| 1066 | return "%s is null" % field |
| 1067 | if comparison in ("!=", "<>"): |
| 1068 | return "%s is not null" % field |
| 1069 | # Using a subquery: |
| 1070 | if isinstance(value, Query): |
| 1071 | if comparison in ("=", "==", IN): |
| 1072 | return "%s in %s" % (field, escape(value)) |
| 1073 | if comparison in ("!=", "<>"): |
| 1074 | return "%s not in %s" % (field, escape(value)) |
| 1075 | return "%s%s%s" % (field, comparison, escape(value)) |
| 1076 | |
| 1077 | # Functions for date fields: cmp(year("date"), 1999, ">"). |
| 1078 | def year(date): |
no test coverage detected