330 |
330 |
331 WARNING: a CubicWebSQLGenerator instance is not thread safe, but generate is |
331 WARNING: a CubicWebSQLGenerator instance is not thread safe, but generate is |
332 protected by a lock |
332 protected by a lock |
333 """ |
333 """ |
334 |
334 |
335 def __init__(self, schema, dbms_helper, attrmap=None): |
335 def __init__(self, schema, dbhelper, attrmap=None): |
336 self.schema = schema |
336 self.schema = schema |
337 self.dbms_helper = dbms_helper |
337 self.dbhelper = dbhelper |
338 self.dbencoding = dbms_helper.dbencoding |
338 self.dbencoding = dbhelper.dbencoding |
339 self.keyword_map = {'NOW' : self.dbms_helper.sql_current_timestamp, |
339 self.keyword_map = {'NOW' : self.dbhelper.sql_current_timestamp, |
340 'TODAY': self.dbms_helper.sql_current_date, |
340 'TODAY': self.dbhelper.sql_current_date, |
341 } |
341 } |
342 if not self.dbms_helper.union_parentheses_support: |
342 if not self.dbhelper.union_parentheses_support: |
343 self.union_sql = self.noparen_union_sql |
343 self.union_sql = self.noparen_union_sql |
344 if self.dbms_helper.fti_need_distinct: |
344 if self.dbhelper.fti_need_distinct: |
345 self.__union_sql = self.union_sql |
345 self.__union_sql = self.union_sql |
346 self.union_sql = self.has_text_need_distinct_union_sql |
346 self.union_sql = self.has_text_need_distinct_union_sql |
347 self._lock = threading.Lock() |
347 self._lock = threading.Lock() |
348 if attrmap is None: |
348 if attrmap is None: |
349 attrmap = {} |
349 attrmap = {} |
389 sqls = ('(%s)' % self.select_sql(select, needalias) |
389 sqls = ('(%s)' % self.select_sql(select, needalias) |
390 for select in union.children) |
390 for select in union.children) |
391 return '\nUNION ALL\n'.join(sqls) |
391 return '\nUNION ALL\n'.join(sqls) |
392 |
392 |
393 def noparen_union_sql(self, union, needalias=False): |
393 def noparen_union_sql(self, union, needalias=False): |
394 # needed for sqlite backend which doesn't like parentheses around |
394 # needed for sqlite backend which doesn't like parentheses around union |
395 # union query. This may cause bug in some condition (sort in one of |
395 # query. This may cause bug in some condition (sort in one of the |
396 # the subquery) but will work in most case |
396 # subquery) but will work in most case |
|
397 # |
397 # see http://www.sqlite.org/cvstrac/tktview?tn=3074 |
398 # see http://www.sqlite.org/cvstrac/tktview?tn=3074 |
398 sqls = (self.select_sql(select, needalias) |
399 sqls = (self.select_sql(select, needalias) |
399 for i, select in enumerate(union.children)) |
400 for i, select in enumerate(union.children)) |
400 return '\nUNION ALL\n'.join(sqls) |
401 return '\nUNION ALL\n'.join(sqls) |
401 |
402 |
502 return sql |
503 return sql |
503 |
504 |
504 def _subqueries_sql(self, select, state): |
505 def _subqueries_sql(self, select, state): |
505 for i, subquery in enumerate(select.with_): |
506 for i, subquery in enumerate(select.with_): |
506 sql = self.union_sql(subquery.query, needalias=True) |
507 sql = self.union_sql(subquery.query, needalias=True) |
507 tablealias = '_T%s' % i |
508 tablealias = '_T%s' % i # XXX nested subqueries |
508 sql = '(%s) AS %s' % (sql, tablealias) |
509 sql = '(%s) AS %s' % (sql, tablealias) |
509 state.subtables[tablealias] = (0, sql) |
510 state.subtables[tablealias] = (0, sql) |
510 for vref in subquery.aliases: |
511 for vref in subquery.aliases: |
511 alias = vref.variable |
512 alias = vref.variable |
512 alias._q_sqltable = tablealias |
513 alias._q_sqltable = tablealias |
526 assert len(self._state.actual_tables) == 1, self._state.actual_tables |
527 assert len(self._state.actual_tables) == 1, self._state.actual_tables |
527 tables = self._state.actual_tables[-1] |
528 tables = self._state.actual_tables[-1] |
528 if tables: |
529 if tables: |
529 # sort for test predictability |
530 # sort for test predictability |
530 sql.insert(1, 'FROM %s' % ', '.join(sorted(tables))) |
531 sql.insert(1, 'FROM %s' % ', '.join(sorted(tables))) |
531 elif self._state.restrictions and self.dbms_helper.needs_from_clause: |
532 elif self._state.restrictions and self.dbhelper.needs_from_clause: |
532 sql.insert(1, 'FROM (SELECT 1) AS _T') |
533 sql.insert(1, 'FROM (SELECT 1) AS _T') |
533 sqls.append('\n'.join(sql)) |
534 sqls.append('\n'.join(sql)) |
534 if select.need_intersect: |
535 if select.need_intersect: |
535 #if distinct or not self.dbms_helper.intersect_all_support: |
536 #if distinct or not self.dbhelper.intersect_all_support: |
536 return '\nINTERSECT\n'.join(sqls) |
537 return '\nINTERSECT\n'.join(sqls) |
537 #else: |
538 #else: |
538 # return '\nINTERSECT ALL\n'.join(sqls) |
539 # return '\nINTERSECT ALL\n'.join(sqls) |
539 elif distinct: |
540 elif distinct: |
540 return '\nUNION\n'.join(sqls) |
541 return '\nUNION\n'.join(sqls) |
941 if isinstance(rel.parent, Not): |
942 if isinstance(rel.parent, Not): |
942 self._state.done.add(rel.parent) |
943 self._state.done.add(rel.parent) |
943 not_ = True |
944 not_ = True |
944 else: |
945 else: |
945 not_ = False |
946 not_ = False |
946 return self.dbms_helper.fti_restriction_sql(alias, const.eval(self._args), |
947 return self.dbhelper.fti_restriction_sql(alias, const.eval(self._args), |
947 jointo, not_) + restriction |
948 jointo, not_) + restriction |
948 |
949 |
949 def visit_comparison(self, cmp): |
950 def visit_comparison(self, cmp): |
950 """generate SQL for a comparison""" |
951 """generate SQL for a comparison""" |
951 if len(cmp.children) == 2: |
952 if len(cmp.children) == 2: |
954 else: |
955 else: |
955 lhs = None |
956 lhs = None |
956 rhs = cmp.children[0] |
957 rhs = cmp.children[0] |
957 operator = cmp.operator |
958 operator = cmp.operator |
958 if operator in ('IS', 'LIKE', 'ILIKE'): |
959 if operator in ('IS', 'LIKE', 'ILIKE'): |
959 if operator == 'ILIKE' and not self.dbms_helper.ilike_support: |
960 if operator == 'ILIKE' and not self.dbhelper.ilike_support: |
960 operator = ' LIKE ' |
961 operator = ' LIKE ' |
961 else: |
962 else: |
962 operator = ' %s ' % operator |
963 operator = ' %s ' % operator |
963 elif (operator == '=' and isinstance(rhs, Constant) |
964 elif (operator == '=' and isinstance(rhs, Constant) |
964 and rhs.eval(self._args) is None): |
965 and rhs.eval(self._args) is None): |
1001 rel = constant.relation() |
1002 rel = constant.relation() |
1002 if rel is not None: |
1003 if rel is not None: |
1003 rel._q_needcast = value |
1004 rel._q_needcast = value |
1004 return self.keyword_map[value]() |
1005 return self.keyword_map[value]() |
1005 if constant.type == 'Boolean': |
1006 if constant.type == 'Boolean': |
1006 value = self.dbms_helper.boolean_value(value) |
1007 value = self.dbhelper.boolean_value(value) |
1007 if constant.type == 'Substitute': |
1008 if constant.type == 'Substitute': |
1008 _id = constant.value |
1009 _id = constant.value |
1009 if isinstance(_id, unicode): |
1010 if isinstance(_id, unicode): |
1010 _id = _id.encode() |
1011 _id = _id.encode() |
1011 else: |
1012 else: |
1063 etypes = ','.join("'%s'" % et for et in pts) |
1064 etypes = ','.join("'%s'" % et for et in pts) |
1064 restr = '%s.type IN (%s)' % (vtablename, etypes) |
1065 restr = '%s.type IN (%s)' % (vtablename, etypes) |
1065 self._state.add_restriction(restr) |
1066 self._state.add_restriction(restr) |
1066 elif principal.r_type == 'has_text': |
1067 elif principal.r_type == 'has_text': |
1067 sql = '%s.%s' % (self._fti_table(principal), |
1068 sql = '%s.%s' % (self._fti_table(principal), |
1068 self.dbms_helper.fti_uid_attr) |
1069 self.dbhelper.fti_uid_attr) |
1069 elif principal in variable.stinfo['rhsrelations']: |
1070 elif principal in variable.stinfo['rhsrelations']: |
1070 if self.schema.rschema(principal.r_type).inlined: |
1071 if self.schema.rschema(principal.r_type).inlined: |
1071 sql = self._linked_var_sql(variable) |
1072 sql = self._linked_var_sql(variable) |
1072 else: |
1073 else: |
1073 sql = '%s.eid_to' % self._relation_table(principal) |
1074 sql = '%s.eid_to' % self._relation_table(principal) |
1265 try: |
1266 try: |
1266 return relation._q_sqltable |
1267 return relation._q_sqltable |
1267 except AttributeError: |
1268 except AttributeError: |
1268 pass |
1269 pass |
1269 self._state.done.add(relation) |
1270 self._state.done.add(relation) |
1270 alias = self.alias_and_add_table(self.dbms_helper.fti_table) |
1271 alias = self.alias_and_add_table(self.dbhelper.fti_table) |
1271 relation._q_sqltable = alias |
1272 relation._q_sqltable = alias |
1272 return alias |
1273 return alias |
1273 |
1274 |
1274 def _varmap_table_scope(self, select, table): |
1275 def _varmap_table_scope(self, select, table): |
1275 """since a varmap table may be used for multiple variable, its scope is |
1276 """since a varmap table may be used for multiple variable, its scope is |