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())
