def test_create_table_with_columns(self): with self.subTest("with nullable"): a = Column("a", "INT", True) b = Column("b", "VARCHAR(100)", False) q = Query.create_table(self.new_table).columns(a, b) self.assertEqual('CREATE TABLE "abc" ("a" INT NULL,"b" VARCHAR(100) NOT NULL)', str(q)) with self.subTest("with defaults"): a = Column("a", "INT", default=ValueWrapper(42)) b = Column("b", "VARCHAR(100)", default=ValueWrapper("foo")) q = Query.create_table(self.new_table).columns(a, b) self.assertEqual('CREATE TABLE "abc" ("a" INT DEFAULT 42,"b" VARCHAR(100) DEFAULT \'foo\')', str(q)) with self.subTest("with period for"): a = Column("id", "INT") b = Column("valid_from", "DATETIME") c = Column("valid_to", "DATETIME") q = Query.create_table(self.new_table).columns(a, b, c).period_for('valid_period', b, c) self.assertEqual( 'CREATE TABLE "abc" (' '"id" INT,' '"valid_from" DATETIME,' '"valid_to" DATETIME,' 'PERIOD FOR "valid_period" ("valid_from","valid_to"))', str(q), ) with self.subTest("without temporary keyword"): q = Query.create_table(self.new_table).columns(self.foo, self.bar) self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with temporary keyword"): q = Query.create_table(self.new_table).temporary().columns(self.foo, self.bar) self.assertEqual('CREATE TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with primary key"): q = Query.create_table(self.new_table).columns(self.foo, self.bar).primary_key(self.foo, self.bar) self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),PRIMARY KEY ("a","b"))', str(q)) with self.subTest("with unique keys"): q = ( Query.create_table(self.new_table) .columns(self.foo, self.bar) .unique(self.foo, self.bar) .unique(self.foo) ) self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),UNIQUE ("a","b"),UNIQUE ("a"))', str(q)) with self.subTest("with system versioning"): q = Query.create_table(self.new_table).columns(self.foo, self.bar).with_system_versioning() self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100)) WITH SYSTEM VERSIONING', str(q))
def _get_json_criterion(items: List): if len(items) == 2: left = items.pop(0) right = items.pop(0) return BasicCriterion(JSONOperators.GET_TEXT_VALUE, ValueWrapper(left), ValueWrapper(right)) left = items.pop(0) return BasicCriterion(JSONOperators.GET_JSON_VALUE, ValueWrapper(left), _get_json_criterion(items))
def mysql_json_contained_by(field: Term, value_str: str) -> Criterion: values = json.loads(value_str) contained_by = None for value in values: if contained_by is None: contained_by = JSONContains(field, ValueWrapper(json.dumps([value]))) else: contained_by |= JSONContains(field, ValueWrapper(json.dumps( [value]))) # type: ignore return contained_by
def on_duplicate_key_update(self, field: Union[Field, str], value: Any) -> "MySQLQueryBuilder": if self._ignore_duplicates: raise QueryException("Can not have two conflict handlers") field = Field(field) if not isinstance(field, Field) else field self._duplicate_updates.append((field, ValueWrapper(value)))
async def add_replica(self, replica_id, master_id): rf = ReplicaFiles(alias='rfm') q = self.insert().columns( 'replica_id', 'file_id', 'location', 'status', ).from_(rf.table).select( replica_id, rf.table.file_id, ValueWrapper(''), Status.indexing, ).where(rf.table.replica_id == master_id) await self.exec(q)
def do_update(self, update_field, update_value): if self._on_conflict_do_nothing: raise QueryException('Can not have two conflict handlers') if isinstance(update_field, str): field = self._conflict_field_str(update_field) elif isinstance(update_field, Field): field = update_field self._on_conflict_updates.append((field, ValueWrapper(update_value)))
def on_duplicate_key_update(self, field: Union[Field, str], value: Any) -> "MySQLQueryBuilder": with copy_if_immutable(self) as this: if this._ignore_duplicates: raise QueryException("Can not have two conflict handlers") field = Field(field) if not isinstance(field, Field) else field this._duplicate_updates.append((field, ValueWrapper(value))) return this
def _make_query(self) -> None: self.query = copy(self.model._meta.basequery) self.resolve_filters( model=self.model, q_objects=self.q_objects, annotations=self.annotations, custom_filters=self.custom_filters, ) self.query._limit = 1 self.query._select_other(ValueWrapper(1))
def get_args_for_dialect(self, dialect=None): term, delimiter, index = self.args return { Dialects.MYSQL: (term, delimiter, index), Dialects.POSTGRESQL: (term, delimiter, index), Dialects.REDSHIFT: (term, delimiter, index), Dialects.VERTICA: (term, delimiter, index), Dialects.ORACLE: (term, ValueWrapper('[^{}]+'.format(delimiter.value)), 1, index) }.get(dialect, None)
def _create_json_criterion(items: List, field_term: Term, operator_: Callable, value: str): if len(items) == 1: term = items.pop(0) return operator_( BasicCriterion(JSONOperators.GET_TEXT_VALUE, field_term, ValueWrapper(term)), value) return operator_( BasicCriterion(JSONOperators.GET_JSON_VALUE, field_term, _get_json_criterion(items)), value)
def do_update(self, update_field, update_value): if self._on_conflict_do_nothing: raise QueryException('Can not have two conflict handlers') #assert len(update_fields) == len(update_values), 'number of fields does noth match with number of values' #for i, f in enumerate(update_fields): #field = None if isinstance(update_field, str): field = self._conflict_field_str(update_field) elif isinstance(update_field, Field): field = update_field self._on_conflict_updates.append((field, ValueWrapper(update_value)))
def do_update(self, update_field: Union[str, Field], update_value: Any) -> "PostgreQueryBuilder": if self._on_conflict_do_nothing: raise QueryException("Can not have two conflict handlers") if isinstance(update_field, str): field = self._conflict_field_str(update_field) elif isinstance(update_field, Field): field = update_field else: raise QueryException("Unsupported update_field") self._on_conflict_do_updates.append((field, ValueWrapper(update_value)))
def do_update(self, update_field: Union[str, Field], update_value: Any) -> "PostgreQueryBuilder": with copy_if_immutable(self) as this: if this._on_conflict_do_nothing: raise QueryException("Can not have two conflict handlers") if isinstance(update_field, str): field = this._conflict_field_str(update_field) elif isinstance(update_field, Field): field = update_field else: raise QueryException("Unsupported update_field") this._on_conflict_do_updates.append( (field, ValueWrapper(update_value))) return this
def on_duplicate_key_update(self, field, value): field = Field(field) if not isinstance(field, Field) else field self._duplicate_updates.append((field, ValueWrapper(value)))
def test_create_table_with_columns(self): with self.subTest("with nullable"): a = Column("a", "INT", True) b = Column("b", "VARCHAR(100)", False) q = Query.create_table(self.new_table).columns(a, b) self.assertEqual( 'CREATE TABLE "abc" ("a" INT NULL,"b" VARCHAR(100) NOT NULL)', str(q)) with self.subTest("with defaults"): a = Column("a", "INT", default=ValueWrapper(42)) b = Column("b", "VARCHAR(100)", default=ValueWrapper("foo")) q = Query.create_table(self.new_table).columns(a, b) self.assertEqual( 'CREATE TABLE "abc" ("a" INT DEFAULT 42,"b" VARCHAR(100) DEFAULT \'foo\')', str(q)) with self.subTest("with unwrapped defaults"): a = Column("a", "INT", default=42) b = Column("b", "VARCHAR(100)", default="foo") q = Query.create_table(self.new_table).columns(a, b) self.assertEqual( 'CREATE TABLE "abc" ("a" INT DEFAULT 42,"b" VARCHAR(100) DEFAULT \'foo\')', str(q)) with self.subTest("with period for"): a = Column("id", "INT") b = Column("valid_from", "DATETIME") c = Column("valid_to", "DATETIME") q = Query.create_table(self.new_table).columns(a, b, c).period_for( 'valid_period', b, c) self.assertEqual( 'CREATE TABLE "abc" (' '"id" INT,' '"valid_from" DATETIME,' '"valid_to" DATETIME,' 'PERIOD FOR "valid_period" ("valid_from","valid_to"))', str(q), ) with self.subTest("without temporary keyword"): q = Query.create_table(self.new_table).columns(self.foo, self.bar) self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with temporary keyword"): q = Query.create_table(self.new_table).temporary().columns( self.foo, self.bar) self.assertEqual( 'CREATE TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with primary key"): q = Query.create_table(self.new_table).columns( self.foo, self.bar).primary_key(self.foo, self.bar) self.assertEqual( 'CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),PRIMARY KEY ("a","b"))', str(q)) with self.subTest("with simple foreign key"): cref, dref = Columns(("c", "INT"), ("d", "VARCHAR(100)")) q = (Query.create_table(self.new_table).columns( self.foo, self.bar).foreign_key([self.foo, self.bar], self.existing_table, [cref, dref])) self.assertEqual( 'CREATE TABLE "abc" (' '"a" INT,' '"b" VARCHAR(100),' 'FOREIGN KEY ("a","b") REFERENCES "efg" ("c","d"))', str(q), ) with self.subTest("with foreign key reference options"): cref, dref = Columns(("c", "INT"), ("d", "VARCHAR(100)")) q = (Query.create_table(self.new_table).columns( self.foo, self.bar).foreign_key( [self.foo, self.bar], self.existing_table, [cref, dref], on_delete=ReferenceOption.cascade, on_update=ReferenceOption.restrict, )) self.assertEqual( 'CREATE TABLE "abc" (' '"a" INT,' '"b" VARCHAR(100),' 'FOREIGN KEY ("a","b") REFERENCES "efg" ("c","d") ON DELETE CASCADE ON UPDATE RESTRICT)', str(q), ) with self.subTest("with unique keys"): q = (Query.create_table(self.new_table).columns( self.foo, self.bar).unique(self.foo, self.bar).unique(self.foo)) self.assertEqual( 'CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),UNIQUE ("a","b"),UNIQUE ("a"))', str(q)) with self.subTest("with system versioning"): q = Query.create_table(self.new_table).columns( self.foo, self.bar).with_system_versioning() self.assertEqual( 'CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100)) WITH SYSTEM VERSIONING', str(q)) with self.subTest("with unlogged keyword"): q = Query.create_table(self.new_table).unlogged().columns( self.foo, self.bar) self.assertEqual( 'CREATE UNLOGGED TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with if not exists keyword"): q = Query.create_table(self.new_table).if_not_exists().columns( self.foo, self.bar) self.assertEqual( 'CREATE TABLE IF NOT EXISTS "abc" ("a" INT,"b" VARCHAR(100))', str(q))
_q_insert_realm_role = """ INSERT INTO realm_user_role( realm, user_, role, certificate, certified_by, certified_on ) SELECT ({}), ({}), ({}), ({}), ({}), ({}) """.format( Parameter("$1"), q_user_internal_id(organization_id=Parameter("$2"), user_id=Parameter("$3")), ValueWrapper("OWNER"), Parameter("$4"), q_device_internal_id(organization_id=Parameter("$2"), device_id=Parameter("$5")), Parameter("$6"), ) _q_insert_realm_encryption_revision = """ INSERT INTO vlob_encryption_revision ( realm, encryption_revision ) SELECT $1, 1 """
def test__constant_is_aggregate_none(self): v = ValueWrapper(100) self.assertIsNone(v.is_aggregate)
def test_negate_wrapped_float(self): q = -ValueWrapper(1.0) self.assertEqual("-1.0", q.get_sql())
def postgres_json_contained_by(field: Term, value: str) -> Criterion: return BasicCriterion(JSONOperators.CONTAINED_BY, field, ValueWrapper(value))
def test__constant_arithmetic_is_neither_aggr_or_not(self): v = ValueWrapper(100) + ValueWrapper(100) self.assertIsNone(v.is_aggregate)
def is_in(field: Term, value: Any) -> Criterion: if value: return field.isin(value) return BasicCriterion(Equality.eq, ValueWrapper(1), ValueWrapper(0))
def test_negate_wrapped_int(self): q = -ValueWrapper(1) self.assertEqual("-1", q.get_sql())
def lit(value): return Column(ValueWrapper(value))
def on_duplicate_key_update(self, field, value): if self._ignore_duplicates: raise QueryException("Can not have two conflict handlers") field = Field(field) if not isinstance(field, Field) else field self._duplicate_updates.append((field, ValueWrapper(value)))
def not_in(field: Term, value: Any) -> Criterion: if value: return field.notin(value) | field.isnull() return BasicCriterion(Equality.eq, ValueWrapper(1), ValueWrapper(1))
def is_in(field: Term, value: Any) -> Criterion: if value: return field.isin(value) # SQL has no False, so we return 1=0 return BasicCriterion(Equality.eq, ValueWrapper(1), ValueWrapper(0))
def test_select_no_with_alias_from(self): q = Query.select(ValueWrapper(1, "test")) self.assertEqual('SELECT 1 "test"', str(q))
def test_uuid_string_generation(self): id = uuid.uuid4() self.assertEqual("'{}'".format(id), ValueWrapper(id).get_sql())
def set(self, field, value): field = Field(field) if not isinstance(field, Field) else field self._updates.append((field, ValueWrapper(value)))
def test_inline_string_concatentation(self): self.assertEqual("'it''s'", ValueWrapper("it's").get_sql())