def test_or(self): for or_ in [ Or((self.table.c1, self.table.c2)), self.table.c1 | self.table.c2 ]: self.assertEqual(str(or_), '("c1" OR "c2")') self.assertEqual(or_.params, ())
def convert_domain_child_mptt(self, domain, tables): cursor = Transaction().cursor table, _ = tables[None] name, operator, ids = domain red_sql = reduce_ids(table.id, ids) Target = self.get_target() left = getattr(Target, self.left).sql_column(table) right = getattr(Target, self.right).sql_column(table) cursor.execute(*table.select(left, right, where=red_sql)) where = Or() for l, r in cursor.fetchall(): where.append((left >= l) & (right <= r)) expression = table.id.in_(table.select(table.id, where=where)) if operator == 'not child_of': return ~expression return expression
def test_operator_operators(self): and_ = And((Literal(True), self.table.c1)) and2 = and_ & And((Literal(True), self.table.c2)) self.assertEqual(str(and2), '((%s AND "c1") AND %s AND "c2")') self.assertEqual(and2.params, (True, True)) and3 = and_ & Literal(True) self.assertEqual(str(and3), '((%s AND "c1") AND %s)') self.assertEqual(and3.params, (True, True)) or_ = Or((Literal(True), self.table.c1)) or2 = or_ | Or((Literal(True), self.table.c2)) self.assertEqual(str(or2), '((%s OR "c1") OR %s OR "c2")') self.assertEqual(or2.params, (True, True)) or3 = or_ | Literal(True) self.assertEqual(str(or3), '((%s OR "c1") OR %s)') self.assertEqual(or3.params, (True, True))
def convert_domain_path(self, domain, tables): cursor = Transaction().connection.cursor() table, _ = tables[None] name, operator, ids = domain red_sql = reduce_ids(table.id, (i for i in ids if i is not None)) Target = self.get_target() path_column = getattr(Target, self.path).sql_column(table) cursor.execute(*table.select(path_column, where=red_sql)) if operator.endswith('child_of'): where = Or() for path, in cursor: where.append(path_column.like(path + '%')) else: ids = [int(x) for path, in cursor for x in path.split('/')[:-1]] where = reduce_ids(table.id, ids) if not where: where = Literal(False) if operator.startswith('not'): return ~where return where
def reduce_ids(field, ids): ''' Return a small SQL expression for the list of ids and the sql column ''' ids = list(ids) if not ids: return Literal(False) assert all(x.is_integer() for x in ids if isinstance(x, float)), \ 'ids must be integer' ids = list(map(int, ids)) ids.sort() prev = ids.pop(0) continue_list = [prev, prev] discontinue_list = array('l') sql = Or() for i in ids: if i == prev: continue if i != prev + 1: if continue_list[-1] - continue_list[0] < 5: discontinue_list.extend([ continue_list[0] + x for x in range(continue_list[-1] - continue_list[0] + 1) ]) else: sql.append((field >= continue_list[0]) & (field <= continue_list[-1])) continue_list = [] continue_list.append(i) prev = i if continue_list[-1] - continue_list[0] < 5: discontinue_list.extend([ continue_list[0] + x for x in range(continue_list[-1] - continue_list[0] + 1) ]) else: sql.append((field >= continue_list[0]) & (field <= continue_list[-1])) if discontinue_list: sql.append(field.in_(discontinue_list)) return sql
def convert_domain_mptt(self, domain, tables): cursor = Transaction().connection.cursor() table, _ = tables[None] name, operator, ids = domain red_sql = reduce_ids(table.id, (i for i in ids if i is not None)) Target = self.get_target() left = getattr(Target, self.left).sql_column(table) right = getattr(Target, self.right).sql_column(table) cursor.execute(*table.select(left, right, where=red_sql)) where = Or() for l, r in cursor.fetchall(): if operator.endswith('child_of'): where.append((left >= l) & (right <= r)) else: where.append((left <= l) & (right >= r)) if not where: where = Literal(False) if operator.startswith('not'): return ~where return where
def convert_domain_mptt(self, domain, tables): cursor = Transaction().cursor table, _ = tables[None] name, operator, ids = domain red_sql = reduce_ids(table.id, ids) Target = self.get_target() left = getattr(Target, self.left).sql_column(table) right = getattr(Target, self.right).sql_column(table) cursor.execute(*table.select(left, right, where=red_sql)) where = Or() for l, r in cursor.fetchall(): if operator.endswith('child_of'): where.append((left >= l) & (right <= r)) else: where.append((left <= l) & (right >= r)) if not where: where = Literal(False) if operator.startswith('not'): return ~where return where
def reduce_ids(field, ids): ''' Return a small SQL expression for the list of ids and the sql column ''' ids = list(ids) if not ids: return Literal(False) assert all(x.is_integer() for x in ids if isinstance(x, float)), \ 'ids must be integer' ids = map(int, ids) ids.sort() prev = ids.pop(0) continue_list = [prev, prev] discontinue_list = array('l') sql = Or() for i in ids: if i == prev: continue if i != prev + 1: if continue_list[-1] - continue_list[0] < 5: discontinue_list.extend([continue_list[0] + x for x in range(continue_list[-1] - continue_list[0] + 1)]) else: sql.append((field >= continue_list[0]) & (field <= continue_list[-1])) continue_list = [] continue_list.append(i) prev = i if continue_list[-1] - continue_list[0] < 5: discontinue_list.extend([continue_list[0] + x for x in range(continue_list[-1] - continue_list[0] + 1)]) else: sql.append((field >= continue_list[0]) & (field <= continue_list[-1])) if discontinue_list: sql.append(field.in_(discontinue_list)) return sql
def __or__(self, other): from sql.operators import Or return Or((self, other))