16 class WhereNode(tree.Node): |
16 class WhereNode(tree.Node): |
17 """ |
17 """ |
18 Used to represent the SQL where-clause. |
18 Used to represent the SQL where-clause. |
19 |
19 |
20 The class is tied to the Query class that created it (in order to create |
20 The class is tied to the Query class that created it (in order to create |
21 the corret SQL). |
21 the correct SQL). |
22 |
22 |
23 The children in this tree are usually either Q-like objects or lists of |
23 The children in this tree are usually either Q-like objects or lists of |
24 [table_alias, field_name, field_class, lookup_type, value]. However, a |
24 [table_alias, field_name, db_type, lookup_type, value_annotation, |
25 child could also be any class with as_sql() and relabel_aliases() methods. |
25 params]. However, a child could also be any class with as_sql() and |
|
26 relabel_aliases() methods. |
26 """ |
27 """ |
27 default = AND |
28 default = AND |
28 |
29 |
29 def as_sql(self, node=None, qn=None): |
30 def add(self, data, connector): |
|
31 """ |
|
32 Add a node to the where-tree. If the data is a list or tuple, it is |
|
33 expected to be of the form (alias, col_name, field_obj, lookup_type, |
|
34 value), which is then slightly munged before being stored (to avoid |
|
35 storing any reference to field objects). Otherwise, the 'data' is |
|
36 stored unchanged and can be anything with an 'as_sql()' method. |
|
37 """ |
|
38 # Because of circular imports, we need to import this here. |
|
39 from django.db.models.base import ObjectDoesNotExist |
|
40 |
|
41 if not isinstance(data, (list, tuple)): |
|
42 super(WhereNode, self).add(data, connector) |
|
43 return |
|
44 |
|
45 alias, col, field, lookup_type, value = data |
|
46 try: |
|
47 if field: |
|
48 params = field.get_db_prep_lookup(lookup_type, value) |
|
49 db_type = field.db_type() |
|
50 else: |
|
51 # This is possible when we add a comparison to NULL sometimes |
|
52 # (we don't really need to waste time looking up the associated |
|
53 # field object). |
|
54 params = Field().get_db_prep_lookup(lookup_type, value) |
|
55 db_type = None |
|
56 except ObjectDoesNotExist: |
|
57 # This can happen when trying to insert a reference to a null pk. |
|
58 # We break out of the normal path and indicate there's nothing to |
|
59 # match. |
|
60 super(WhereNode, self).add(NothingNode(), connector) |
|
61 return |
|
62 if isinstance(value, datetime.datetime): |
|
63 annotation = datetime.datetime |
|
64 else: |
|
65 annotation = bool(value) |
|
66 super(WhereNode, self).add((alias, col, db_type, lookup_type, |
|
67 annotation, params), connector) |
|
68 |
|
69 def as_sql(self, qn=None): |
30 """ |
70 """ |
31 Returns the SQL version of the where clause and the value to be |
71 Returns the SQL version of the where clause and the value to be |
32 substituted in. Returns None, None if this node is empty. |
72 substituted in. Returns None, None if this node is empty. |
33 |
73 |
34 If 'node' is provided, that is the root of the SQL generation |
74 If 'node' is provided, that is the root of the SQL generation |
35 (generally not needed except by the internal implementation for |
75 (generally not needed except by the internal implementation for |
36 recursion). |
76 recursion). |
37 """ |
77 """ |
38 if node is None: |
|
39 node = self |
|
40 if not qn: |
78 if not qn: |
41 qn = connection.ops.quote_name |
79 qn = connection.ops.quote_name |
42 if not node.children: |
80 if not self.children: |
43 return None, [] |
81 return None, [] |
44 result = [] |
82 result = [] |
45 result_params = [] |
83 result_params = [] |
46 empty = True |
84 empty = True |
47 for child in node.children: |
85 for child in self.children: |
48 try: |
86 try: |
49 if hasattr(child, 'as_sql'): |
87 if hasattr(child, 'as_sql'): |
50 sql, params = child.as_sql(qn=qn) |
88 sql, params = child.as_sql(qn=qn) |
51 format = '(%s)' |
|
52 elif isinstance(child, tree.Node): |
|
53 sql, params = self.as_sql(child, qn) |
|
54 if child.negated: |
|
55 format = 'NOT (%s)' |
|
56 elif len(child.children) == 1: |
|
57 format = '%s' |
|
58 else: |
|
59 format = '(%s)' |
|
60 else: |
89 else: |
|
90 # A leaf node in the tree. |
61 sql, params = self.make_atom(child, qn) |
91 sql, params = self.make_atom(child, qn) |
62 format = '%s' |
|
63 except EmptyResultSet: |
92 except EmptyResultSet: |
64 if node.connector == AND and not node.negated: |
93 if self.connector == AND and not self.negated: |
65 # We can bail out early in this particular case (only). |
94 # We can bail out early in this particular case (only). |
66 raise |
95 raise |
67 elif node.negated: |
96 elif self.negated: |
68 empty = False |
97 empty = False |
69 continue |
98 continue |
70 except FullResultSet: |
99 except FullResultSet: |
71 if self.connector == OR: |
100 if self.connector == OR: |
72 if node.negated: |
101 if self.negated: |
73 empty = True |
102 empty = True |
74 break |
103 break |
75 # We match everything. No need for any constraints. |
104 # We match everything. No need for any constraints. |
76 return '', [] |
105 return '', [] |
77 if node.negated: |
106 if self.negated: |
78 empty = True |
107 empty = True |
79 continue |
108 continue |
80 empty = False |
109 empty = False |
81 if sql: |
110 if sql: |
82 result.append(format % sql) |
111 result.append(sql) |
83 result_params.extend(params) |
112 result_params.extend(params) |
84 if empty: |
113 if empty: |
85 raise EmptyResultSet |
114 raise EmptyResultSet |
86 conn = ' %s ' % node.connector |
115 |
87 return conn.join(result), result_params |
116 conn = ' %s ' % self.connector |
|
117 sql_string = conn.join(result) |
|
118 if sql_string: |
|
119 if self.negated: |
|
120 sql_string = 'NOT (%s)' % sql_string |
|
121 elif len(self.children) != 1: |
|
122 sql_string = '(%s)' % sql_string |
|
123 return sql_string, result_params |
88 |
124 |
89 def make_atom(self, child, qn): |
125 def make_atom(self, child, qn): |
90 """ |
126 """ |
91 Turn a tuple (table_alias, field_name, field_class, lookup_type, value) |
127 Turn a tuple (table_alias, column_name, db_type, lookup_type, |
92 into valid SQL. |
128 value_annot, params) into valid SQL. |
93 |
129 |
94 Returns the string for the SQL fragment and the parameters to use for |
130 Returns the string for the SQL fragment and the parameters to use for |
95 it. |
131 it. |
96 """ |
132 """ |
97 table_alias, name, field, lookup_type, value = child |
133 table_alias, name, db_type, lookup_type, value_annot, params = child |
98 if table_alias: |
134 if table_alias: |
99 lhs = '%s.%s' % (qn(table_alias), qn(name)) |
135 lhs = '%s.%s' % (qn(table_alias), qn(name)) |
100 else: |
136 else: |
101 lhs = qn(name) |
137 lhs = qn(name) |
102 db_type = field and field.db_type() or None |
|
103 field_sql = connection.ops.field_cast_sql(db_type) % lhs |
138 field_sql = connection.ops.field_cast_sql(db_type) % lhs |
104 |
139 |
105 if isinstance(value, datetime.datetime): |
140 if value_annot is datetime.datetime: |
106 cast_sql = connection.ops.datetime_cast_sql() |
141 cast_sql = connection.ops.datetime_cast_sql() |
107 else: |
142 else: |
108 cast_sql = '%s' |
143 cast_sql = '%s' |
109 |
144 |
110 if field: |
|
111 params = field.get_db_prep_lookup(lookup_type, value) |
|
112 else: |
|
113 params = Field().get_db_prep_lookup(lookup_type, value) |
|
114 if isinstance(params, QueryWrapper): |
145 if isinstance(params, QueryWrapper): |
115 extra, params = params.data |
146 extra, params = params.data |
116 else: |
147 else: |
117 extra = '' |
148 extra = '' |
118 |
149 |
121 extra) |
152 extra) |
122 return (format % (field_sql, |
153 return (format % (field_sql, |
123 connection.operators[lookup_type] % cast_sql), params) |
154 connection.operators[lookup_type] % cast_sql), params) |
124 |
155 |
125 if lookup_type == 'in': |
156 if lookup_type == 'in': |
126 if not value: |
157 if not value_annot: |
127 raise EmptyResultSet |
158 raise EmptyResultSet |
128 if extra: |
159 if extra: |
129 return ('%s IN %s' % (field_sql, extra), params) |
160 return ('%s IN %s' % (field_sql, extra), params) |
130 return ('%s IN (%s)' % (field_sql, ', '.join(['%s'] * len(value))), |
161 return ('%s IN (%s)' % (field_sql, ', '.join(['%s'] * len(params))), |
131 params) |
162 params) |
132 elif lookup_type in ('range', 'year'): |
163 elif lookup_type in ('range', 'year'): |
133 return ('%s BETWEEN %%s and %%s' % field_sql, params) |
164 return ('%s BETWEEN %%s and %%s' % field_sql, params) |
134 elif lookup_type in ('month', 'day'): |
165 elif lookup_type in ('month', 'day'): |
135 return ('%s = %%s' % connection.ops.date_extract_sql(lookup_type, |
166 return ('%s = %%s' % connection.ops.date_extract_sql(lookup_type, |
136 field_sql), params) |
167 field_sql), params) |
137 elif lookup_type == 'isnull': |
168 elif lookup_type == 'isnull': |
138 return ('%s IS %sNULL' % (field_sql, (not value and 'NOT ' or '')), |
169 return ('%s IS %sNULL' % (field_sql, |
139 params) |
170 (not value_annot and 'NOT ' or '')), ()) |
140 elif lookup_type == 'search': |
171 elif lookup_type == 'search': |
141 return (connection.ops.fulltext_search_sql(field_sql), params) |
172 return (connection.ops.fulltext_search_sql(field_sql), params) |
142 elif lookup_type in ('regex', 'iregex'): |
173 elif lookup_type in ('regex', 'iregex'): |
143 return connection.ops.regex_lookup(lookup_type) % (field_sql, cast_sql), params |
174 return connection.ops.regex_lookup(lookup_type) % (field_sql, cast_sql), params |
144 |
175 |