def handle_loan_paid(version, event):
"""Handles a loan.paid event (typically sent from utils/paid_utils.py); if
the lender does not have the recheck permission and has an unknown/good
trust status, and they don't have the recheck permission we grant them the
recheck permission.
"""
with LazyItgs(logger_iden=LOGGER_IDEN) as itgs:
lender_id = event['lender']['id']
lender_username = event['lender']['username']
itgs.logger.print(
Level.TRACE,
'Detected /u/{} (id={}) had one of the loans he gave out paid back',
lender_username, lender_id)
trusts = Table('trusts')
itgs.read_cursor.execute(
Query.from_(trusts).select(1).where(
trusts.user_id == Parameter('%s')).where(
trusts.status == Parameter('%s')).get_sql(),
(lender_id, 'bad'))
row = itgs.read_cursor.fetchone()
if row is not None:
itgs.logger.print(
Level.TRACE,
'Ignoring lender /u/{} (id={}); he has bad trust status',
lender_username, lender_id)
return
passwd_auths = Table('password_authentications')
itgs.read_cursor.execute(
Query.from_(passwd_auths).select(passwd_auths.id).where(
passwd_auths.user_id == Parameter('%s')).where(
passwd_auths.human == Parameter('%s')).where(
passwd_auths.deleted == Parameter('%s')).get_sql(),
(lender_id, True, False))
row = itgs.read_cursor.fetchone()
if row is None:
itgs.logger.print(
Level.TRACE,
'Ignoring lender /u/{} (id={}); he has not signed up')
return
(passwd_auth_id, ) = row
passwd_auth_perms = Table('password_auth_permissions')
perms = Table('permissions')
itgs.read_cursor.execute(
Query.from_(passwd_auth_perms).join(perms).on(
perms.id == passwd_auth_perms.permission_id).select(1).where(
passwd_auth_perms.password_authentication_id == Parameter(
'%s')).where(perms.name == Parameter('%s')).get_sql(),
(passwd_auth_id, RECHECK_PERMISSION))
if itgs.read_cursor.fetchone():
itgs.logger.print(
Level.TRACE,
'Ignoring lender /u/{} (id={}); already has recheck permission',
lender_username, lender_id)
return
loans = Table('loans')
itgs.read_cursor.execute(
Query.from_(loans).select(Count(
Star())).where(loans.lender_id == Parameter('%s')).where(
loans.repaid_at.notnull()).where(
loans.deleted_at.isnull()).get_sql(), (lender_id, ))
(num_loans_compl_as_lender, ) = itgs.read_cursor.fetchone()
if num_loans_compl_as_lender < MINIMUM_COMPLETED_LOANS:
itgs.logger.print(
Level.TRACE,
'Ignoring lender /u/{} (has {} completed, threshold is {})',
lender_username, num_loans_compl_as_lender,
MINIMUM_COMPLETED_LOANS)
return
itgs.logger.print(
Level.TRACE, 'Going to grant recheck permission to lender '
'/u/{} (has {} completed, threshold is {})', lender_username,
num_loans_compl_as_lender, MINIMUM_COMPLETED_LOANS)
itgs.read_cursor.execute(
Query.from_(perms).select(
perms.id).where(perms.name == Parameter('%s')).get_sql(),
(RECHECK_PERMISSION, ))
row = itgs.read_cursor.fetchone()
if row is None:
itgs.logger.print(
Level.INFO,
'Detected there is no recheck permission in the database, '
'automatically adding with name=%s', RECHECK_PERMISSION)
itgs.write_cursor.execute(
Query.into(perms).columns(
perms.name, perms.description).insert(
Parameter('%s'),
Parameter('%s')).returning(perms.id).get_sql(),
(RECHECK_PERMISSION,
'Ability to have the LoansBot revisit a comment'))
row = itgs.write_cursor.fetchone()
(recheck_perm_id, ) = row
itgs.write_cursor.execute(
Query.into(passwd_auth_perms).columns(
passwd_auth_perms.password_authentication_id,
passwd_auth_perms.permission_id).insert(
Parameter('%s'), Parameter('%s')).get_sql(),
(passwd_auth_id, recheck_perm_id))
itgs.write_conn.commit()
itgs.logger.print(
Level.INFO,
'Granted /u/{} access to recheck permission - signed up and has '
'{} loans completed as lender (threshold is {})', lender_username,
num_loans_compl_as_lender, MINIMUM_COMPLETED_LOANS)
(subject, body) = get_letter_response(itgs,
'user_granted_recheck_pm',
username=lender_username)
utils.reddit_proxy.send_request(itgs, 'recheck_permission', version,
'compose', {
'recipient': lender_username,
'subject': subject,
'body': body
})
def test_select__column__single__field(self):
t = Table('abc')
q = Query.from_(t).select(t.foo)
self.assertEqual('SELECT "foo" FROM "abc"', str(q))
def handle_comment(self, itgs, comment, rpiden, rpversion):
token_vals = PARSER.parse(comment['body'])
lender_username = comment['author']
borrower_username = token_vals[0]
amt = token_vals[1]
comment_permalink = 'https://www.reddit.com/comments/{}/redditloans/{}'.format(
comment['link_fullname'][3:], comment['fullname'][3:])
loans = Table('loans')
lenders = Table('lenders')
borrowers = Table('borrowers')
effected_loans_pre = []
effected_loans_post = []
remaining = amt
while remaining.minor > 0:
itgs.write_cursor.execute(
loan_format_helper.create_loans_query().where(
lenders.username == Parameter('%s')).where(
borrowers.username == Parameter('%s')).where(
loans.repaid_at.isnull()).orderby(
loans.created_at,
order=Order.asc).limit(1).get_sql(),
(lender_username.lower(), borrower_username.lower()))
row = itgs.write_cursor.fetchone()
if row is None:
break
loan_pre = loan_format_helper.fetch_loan(row)
old_minor = remaining.minor
(_, _, remaining) = utils.paid_utils.apply_repayment(
itgs, loan_pre.id, remaining)
itgs.write_cursor.execute(
loan_format_helper.create_loans_query().where(
loans.id == Parameter('%s')).get_sql(), (loan_pre.id, ))
row = itgs.write_cursor.fetchone()
if row is None:
itgs.logger.print(
Level.WARN,
'Somehow, while handling the paid summon by /u/{} at {}, '
+
'the loan was deleted while applying repayment. We stopped '
+
'propagating the loan early. If nobody was deleting loans this '
+ 'is definitely developer error.', lender_username,
comment_permalink)
effected_loans_pre.append(loan_pre)
break
loan_post = loan_format_helper.fetch_loan(row)
if old_minor <= remaining.minor:
# Sanity check to prevent loops
break
effected_loans_pre.append(loan_pre)
effected_loans_post.append(loan_post)
itgs.logger.print(
Level.INFO, '/u/{} was repaid by /u/{} by {} over {} loan{} at {}',
lender_username, borrower_username, amt, len(effected_loans_pre),
's' if len(effected_loans_pre) != 1 else '', comment_permalink)
formatted_response = get_response(
itgs,
'paid',
lender_username=lender_username,
borrower_username=borrower_username,
loans_before=loan_format_helper.format_loan_table(
effected_loans_pre),
loans_after=loan_format_helper.format_loan_table(
effected_loans_post),
num_loans_affected=len(effected_loans_pre),
amount=str(amt),
remaining=str(remaining))
utils.reddit_proxy.send_request(itgs, rpiden, rpversion,
'post_comment', {
'parent': comment['fullname'],
'text': formatted_response
})
def handle_mod_added(version, event):
with LazyItgs(logger_iden=LOGGER_IDEN) as itgs:
itgs.logger.print(Level.DEBUG,
'Detected that /u/{} is now a moderator',
event['username'])
password_authentications = Table('password_authentications')
itgs.read_cursor.execute(
Query.from_(password_authentications).select(
password_authentications.id).where(
password_authentications.user_id == Parameter('%s')).where(
password_authentications.human.eq(True)).where(
password_authentications.deleted.eq(
False)).get_sql(), (event['user_id'], ))
row = itgs.read_cursor.fetchone()
if row is None:
itgs.logger.print(
Level.DEBUG,
'Detected that /u/{} has not yet claimed his account',
event['username'])
(subject,
body) = get_letter_response(itgs,
ACCOUNT_NOT_CLAIMED_LETTER_NAME,
username=event['username'])
utils.reddit_proxy.send_request(itgs, 'mod_onboarding', version,
'compose', {
'recipient': event['username'],
'subject': subject,
'body': body
})
utils.mod_onboarding_utils.store_letter_message(
itgs,
event['user_id'],
ACCOUNT_NOT_CLAIMED_LETTER_NAME,
commit=True)
itgs.logger.print(
Level.INFO,
'Sent a message to /u/{} to claim his account to gain mod '
'permissions on the website (since he is now a mod on the '
'subreddit)', event['username'])
return
(passwd_auth_id, ) = row
utils.mod_onboarding_utils.grant_mod_permissions(itgs,
event['user_id'],
passwd_auth_id,
commit=True)
itgs.logger.print(
Level.DEBUG,
'Granted all permissions to /u/{}, sending greeting...',
event['username'])
(subject, body) = get_letter_response(itgs,
GREETING_LETTER_NAME,
username=event['username'])
utils.reddit_proxy.send_request(itgs, 'mod_onboarding', version,
'compose', {
'recipient': event['username'],
'subject': subject,
'body': body
})
utils.mod_onboarding_utils.store_letter_message(itgs,
event['user_id'],
GREETING_LETTER_NAME,
commit=True)
itgs.logger.print(
Level.INFO,
'Granted all permissions to the new mod /u/{} & sent a greeting',
event['username'])
def test_select__table_schema(self):
q = Query.from_(Table('abc', 'schema1')).select('*')
self.assertEqual('SELECT * FROM "schema1"."abc"', str(q))
class InsertIntoOnDuplicateTests(unittest.TestCase):
table_abc = Table('abc')
def test_insert_one_column(self):
query = MySQLQuery\
.into(self.table_abc).insert(1)\
.on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo)
self.assertEqual('INSERT INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `foo`=`foo`', str(query))
def test_insert_one_column_using_values(self):
query = MySQLQuery\
.into(self.table_abc).insert(1)\
.on_duplicate_key_update(self.table_abc.foo, Values(self.table_abc.foo))
self.assertEqual('INSERT INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `foo`=VALUES(`foo`)', str(query))
def test_insert_one_column_single_element_array(self):
query = MySQLQuery\
.into(self.table_abc).insert((1,))\
.on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo)
self.assertEqual('INSERT INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `foo`=`foo`', str(query))
def test_insert_one_column_multi_element_array(self):
query = MySQLQuery\
.into(self.table_abc).insert((1,), (2,))\
.on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo)
self.assertEqual('INSERT INTO `abc` VALUES (1),(2) ON DUPLICATE KEY UPDATE `foo`=`foo`', str(query))
def test_insert_multiple_columns_on_duplicate_update_one_with_same_value(self):
query = MySQLQuery\
.into(self.table_abc).insert(1, 'a')\
.on_duplicate_key_update(self.table_abc.bar, Values(self.table_abc.bar))
self.assertEqual('INSERT INTO `abc` VALUES (1,\'a\') ON DUPLICATE KEY UPDATE `bar`=VALUES(`bar`)', str(query))
def test_insert_multiple_columns_on_duplicate_update_one_with_different_value(self):
query = MySQLQuery\
.into(self.table_abc).insert(1, 'a') \
.on_duplicate_key_update(self.table_abc.bar, 'b')
self.assertEqual('INSERT INTO `abc` VALUES (1,\'a\') ON DUPLICATE KEY UPDATE `bar`=\'b\'', str(query))
def test_insert_multiple_columns_on_duplicate_update_one_with_expression(self):
query = MySQLQuery\
.into(self.table_abc).insert(1, 2) \
.on_duplicate_key_update(self.table_abc.bar, 4+F('bar')) # todo sql expression? not python
self.assertEqual('INSERT INTO `abc` VALUES (1,2) ON DUPLICATE KEY UPDATE `bar`=4+`bar`', str(query))
def test_insert_multiple_columns_on_duplicate_update_one_with_expression_using_original_field_value(self):
query = MySQLQuery\
.into(self.table_abc).insert(1, 'a')\
.on_duplicate_key_update(self.table_abc.bar, fn.Concat(self.table_abc.bar, 'update'))
self.assertEqual(
'INSERT INTO `abc` VALUES (1,\'a\') ON DUPLICATE KEY UPDATE `bar`=CONCAT(`bar`,\'update\')',
str(query)
)
def test_insert_multiple_columns_on_duplicate_update_one_with_expression_using_values(self):
query = MySQLQuery\
.into(self.table_abc).insert(1, 'a')\
.on_duplicate_key_update(self.table_abc.bar, fn.Concat(Values(self.table_abc.bar), 'update'))
self.assertEqual(
'INSERT INTO `abc` VALUES (1,\'a\') ON DUPLICATE KEY UPDATE `bar`=CONCAT(VALUES(`bar`),\'update\')',
str(query)
)
def test_insert_multiple_columns_on_duplicate_update_multiple(self):
query = MySQLQuery \
.into(self.table_abc).insert(1, 'a', 'b') \
.on_duplicate_key_update(self.table_abc.bar, 'b') \
.on_duplicate_key_update(self.table_abc.baz, 'c')
self.assertEqual(
'INSERT INTO `abc` VALUES (1,\'a\',\'b\') ON DUPLICATE KEY UPDATE `bar`=\'b\',`baz`=\'c\'',
str(query)
)
def test_insert_multi_rows_chained_mixed_on_duplicate_update_multiple(self):
query = MySQLQuery.into(self.table_abc)\
.insert((1, 'a', True), (2, 'b', False))\
.insert(3, 'c', True)\
.on_duplicate_key_update(self.table_abc.foo, self.table_abc.foo)\
.on_duplicate_key_update(self.table_abc.bar, Values(self.table_abc.bar))
self.assertEqual(
'INSERT INTO `abc` VALUES (1,\'a\',true),(2,\'b\',false),(3,\'c\',true) '
'ON DUPLICATE KEY UPDATE `foo`=`foo`,`bar`=VALUES(`bar`)',
str(query)
)
def test_insert_selected_columns_on_duplicate_update_one(self):
query = MySQLQuery.into(self.table_abc)\
.columns(self.table_abc.foo, self.table_abc.bar, self.table_abc.baz)\
.insert(1, 'a', True)\
.on_duplicate_key_update(self.table_abc.baz, False)
self.assertEqual(
'INSERT INTO `abc` (`foo`,`bar`,`baz`) VALUES (1,\'a\',true) ON DUPLICATE KEY UPDATE `baz`=false',
str(query)
)
def test_insert_selected_columns_on_duplicate_update_multiple(self):
query = MySQLQuery.into(self.table_abc)\
.columns(self.table_abc.foo, self.table_abc.bar, self.table_abc.baz)\
.insert(1, 'a', True)\
.on_duplicate_key_update(self.table_abc.baz, False)\
.on_duplicate_key_update(self.table_abc.bar, Values(self.table_abc.bar))
self.assertEqual(
'INSERT INTO `abc` (`foo`,`bar`,`baz`) VALUES (1,\'a\',true) '
'ON DUPLICATE KEY UPDATE `baz`=false,`bar`=VALUES(`bar`)',
str(query)
)
def test_insert_none_skipped(self):
query = MySQLQuery.into(self.table_abc).insert().on_duplicate_key_update(self.table_abc.baz, False)
self.assertEqual('', str(query))
def test_insert_ignore(self):
query = MySQLQuery.into(self.table_abc).insert(1).ignore().on_duplicate_key_update(self.table_abc.baz, False)
self.assertEqual('INSERT IGNORE INTO `abc` VALUES (1) ON DUPLICATE KEY UPDATE `baz`=false', str(query))
def _from(column: Column) -> QueryBuilder:
topic = get_topic_by_id(column.parameter.topicId)
topic_col_name = build_collection_name(topic.name)
return Query.from_(Table(topic_col_name))
class AliasTests(unittest.TestCase):
t = Table("abc")
def test_table_field(self):
q = Query.from_(self.t).select(self.t.foo.as_("bar"))
self.assertEqual('SELECT "foo" "bar" FROM "abc"', str(q))
def test_table_field__multi(self):
q = Query.from_(self.t).select(self.t.foo.as_("bar"), self.t.fiz.as_("buz"))
self.assertEqual('SELECT "foo" "bar","fiz" "buz" FROM "abc"', str(q))
def test_arithmetic_function(self):
q = Query.from_(self.t).select((self.t.foo + self.t.bar).as_("biz"))
self.assertEqual('SELECT "foo"+"bar" "biz" FROM "abc"', str(q))
def test_functions_using_as(self):
q = Query.from_(self.t).select(fn.Count("*").as_("foo"))
self.assertEqual('SELECT COUNT(*) "foo" FROM "abc"', str(q))
def test_functions_using_constructor_param(self):
q = Query.from_(self.t).select(fn.Count("*", alias="foo"))
self.assertEqual('SELECT COUNT(*) "foo" FROM "abc"', str(q))
def test_ignored_in_where(self):
q = Query.from_(self.t).select(self.t.foo).where(self.t.foo.as_("bar") == 1)
self.assertEqual('SELECT "foo" FROM "abc" WHERE "foo"=1', str(q))
def test_ignored_in_groupby(self):
q = Query.from_(self.t).select(self.t.foo).groupby(self.t.foo.as_("bar"))
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_ignored_in_orderby(self):
q = Query.from_(self.t).select(self.t.foo).orderby(self.t.foo.as_("bar"))
self.assertEqual('SELECT "foo" FROM "abc" ORDER BY "foo"', str(q))
def test_ignored_in_criterion(self):
c = self.t.foo.as_("bar") == 1
self.assertEqual('"foo"=1', str(c))
def test_ignored_in_criterion_comparison(self):
c = self.t.foo.as_("bar") == self.t.fiz.as_("buz")
self.assertEqual('"foo"="fiz"', str(c))
def test_ignored_in_field_inside_case(self):
q = Query.from_(self.t).select(
Case().when(self.t.foo == 1, "a").else_(self.t.bar.as_('"buz"'))
)
self.assertEqual(
'SELECT CASE WHEN "foo"=1 THEN \'a\' ELSE "bar" END FROM "abc"', str(q)
)
def test_case_using_as(self):
q = Query.from_(self.t).select(
Case().when(self.t.foo == 1, "a").else_("b").as_("bar")
)
self.assertEqual(
'SELECT CASE WHEN "foo"=1 THEN \'a\' ELSE \'b\' END "bar" FROM "abc"',
str(q),
)
def test_case_using_constructor_param(self):
q = Query.from_(self.t).select(
Case(alias="bar").when(self.t.foo == 1, "a").else_("b")
)
self.assertEqual(
'SELECT CASE WHEN "foo"=1 THEN \'a\' ELSE \'b\' END "bar" FROM "abc"',
str(q),
)
def test_select__multiple_tables(self):
table_abc, table_efg = Table("abc", alias="q0"), Table("efg", alias="q1")
q = (
Query.from_(table_abc)
.select(table_abc.foo)
.from_(table_efg)
.select(table_efg.bar)
)
self.assertEqual(
'SELECT "q0"."foo","q1"."bar" FROM "abc" "q0","efg" "q1"', str(q)
)
def test_use_aliases_in_groupby_and_orderby(self):
table_abc = Table("abc", alias="q0")
my_foo = table_abc.foo.as_("my_foo")
q = (
Query.from_(table_abc)
.select(my_foo, table_abc.bar)
.groupby(my_foo)
.orderby(my_foo)
)
self.assertEqual(
'SELECT "q0"."foo" "my_foo","q0"."bar" '
'FROM "abc" "q0" '
'GROUP BY "my_foo" '
'ORDER BY "my_foo"',
str(q),
)
def test_table_with_schema_and_alias(self):
table = Table("abc", schema="schema", alias="alias")
self.assertEqual('"schema"."abc" "alias"', str(table))
def test_null_value_with_alias(self):
q = Query.select(NullValue().as_("abcdef"))
self.assertEqual('SELECT NULL "abcdef"', str(q))
def test_table_with_schema_and_alias(self):
table = Table("abc", schema="schema", alias="alias")
self.assertEqual('"schema"."abc" "alias"', str(table))
def test_select__table_schema(self):
q = Query.from_(Table("abc", "schema1")).select("*")
self.assertEqual('SELECT * FROM "schema1"."abc"', str(q))
def test_select__table_schema_with_multiple_levels_as_list(self):
q = Query.from_(Table("abc", ["schema1", "schema2"])).select("*")
self.assertEqual('SELECT * FROM "schema1"."schema2"."abc"', str(q))
class GroupByTests(unittest.TestCase):
t = Table("abc")
maxDiff = None
def test_groupby__single(self):
q = Query.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_groupby__multi(self):
q = (
Query.from_(self.t)
.groupby(self.t.foo, self.t.bar)
.select(self.t.foo, self.t.bar)
)
self.assertEqual('SELECT "foo","bar" FROM "abc" GROUP BY "foo","bar"', str(q))
def test_groupby__count_star(self):
q = Query.from_(self.t).groupby(self.t.foo).select(self.t.foo, fn.Count("*"))
self.assertEqual('SELECT "foo",COUNT(*) FROM "abc" GROUP BY "foo"', str(q))
def test_groupby__count_field(self):
q = (
Query.from_(self.t)
.groupby(self.t.foo)
.select(self.t.foo, fn.Count(self.t.bar))
)
self.assertEqual('SELECT "foo",COUNT("bar") FROM "abc" GROUP BY "foo"', str(q))
def test_groupby__count_distinct(self):
q = (
Query.from_(self.t)
.groupby(self.t.foo)
.select(self.t.foo, fn.Count("*").distinct())
)
self.assertEqual(
'SELECT "foo",COUNT(DISTINCT *) FROM "abc" GROUP BY "foo"', str(q)
)
def test_groupby__sum_distinct(self):
q = (
Query.from_(self.t)
.groupby(self.t.foo)
.select(self.t.foo, fn.Sum(self.t.bar).distinct())
)
self.assertEqual(
'SELECT "foo",SUM(DISTINCT "bar") FROM "abc" GROUP BY "foo"', str(q)
)
def test_groupby__str(self):
q = Query.from_("abc").groupby("foo").select("foo", fn.Count("*").distinct())
self.assertEqual(
'SELECT "foo",COUNT(DISTINCT *) FROM "abc" GROUP BY "foo"', str(q)
)
def test_groupby__int(self):
q = Query.from_("abc").groupby(1).select("foo", fn.Count("*").distinct())
self.assertEqual('SELECT "foo",COUNT(DISTINCT *) FROM "abc" GROUP BY 1', str(q))
def test_groupby__alias(self):
bar = self.t.bar.as_("bar01")
q = Query.from_(self.t).select(fn.Sum(self.t.foo), bar).groupby(bar)
self.assertEqual(
'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar01"', str(q)
)
def test_groupby__no_alias(self):
bar = self.t.bar.as_("bar01")
q = Query.from_(self.t).select(fn.Sum(self.t.foo), bar).groupby(bar)
self.assertEqual(
'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar"',
q.get_sql(groupby_alias=False),
)
def test_groupby__no_alias_platforms(self):
bar = self.t.bar.as_("bar01")
for query_cls in [MSSQLQuery, OracleQuery]:
q = query_cls.from_(self.t).select(fn.Sum(self.t.foo), bar).groupby(bar)
self.assertEqual(
'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar"', str(q)
)
def test_groupby__alias_platforms(self):
bar = self.t.bar.as_("bar01")
for query_cls in [
MySQLQuery,
VerticaQuery,
PostgreSQLQuery,
RedshiftQuery,
ClickHouseQuery,
SQLLiteQuery,
]:
q = query_cls.from_(self.t).select(fn.Sum(self.t.foo), bar).groupby(bar)
quote_char = (
query_cls._builder().QUOTE_CHAR
if isinstance(query_cls._builder().QUOTE_CHAR, str)
else '"'
)
self.assertEqual(
"SELECT SUM({quote_char}foo{quote_char}),{quote_char}bar{quote_char} "
"{quote_char}bar01{quote_char} "
"FROM {quote_char}abc{quote_char} "
"GROUP BY {quote_char}bar01{quote_char}".format(quote_char=quote_char),
str(q),
)
def test_groupby__alias_with_join(self):
table1 = Table("table1", alias="t1")
bar = table1.bar.as_("bar01")
q = (
Query.from_(self.t)
.join(table1)
.on(self.t.id == table1.t_ref)
.select(fn.Sum(self.t.foo), bar)
.groupby(bar)
)
self.assertEqual(
'SELECT SUM("abc"."foo"),"t1"."bar" "bar01" FROM "abc" '
'JOIN "table1" "t1" ON "abc"."id"="t1"."t_ref" '
'GROUP BY "bar01"',
str(q),
)
def test_groupby_with_case_uses_the_alias(self):
q = (
Query.from_(self.t)
.select(
fn.Sum(self.t.foo).as_("bar"),
Case()
.when(self.t.fname == "Tom", "It was Tom")
.else_("It was someone else.")
.as_("who_was_it"),
)
.groupby(
Case()
.when(self.t.fname == "Tom", "It was Tom")
.else_("It was someone else.")
.as_("who_was_it")
)
)
self.assertEqual(
'SELECT SUM("foo") "bar",'
"CASE WHEN \"fname\"='Tom' THEN 'It was Tom' "
"ELSE 'It was someone else.' END \"who_was_it\" "
'FROM "abc" '
'GROUP BY "who_was_it"',
str(q),
)
def test_mysql_query_uses_backtick_quote_chars(self):
q = MySQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual("SELECT `foo` FROM `abc` GROUP BY `foo`", str(q))
def test_vertica_query_uses_double_quote_chars(self):
q = VerticaQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_mssql_query_uses_double_quote_chars(self):
q = MSSQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_oracle_query_uses_double_quote_chars(self):
q = OracleQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_postgres_query_uses_double_quote_chars(self):
q = PostgreSQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_redshift_query_uses_double_quote_chars(self):
q = RedshiftQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_group_by__single_with_totals(self):
q = Query.from_(self.t).groupby(self.t.foo).select(self.t.foo).with_totals()
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo" WITH TOTALS', str(q))
def test_groupby__multi_with_totals(self):
q = (
Query.from_(self.t)
.groupby(self.t.foo, self.t.bar)
.select(self.t.foo, self.t.bar)
.with_totals()
)
self.assertEqual(
'SELECT "foo","bar" FROM "abc" GROUP BY "foo","bar" WITH TOTALS', str(q)
)
class WhereTests(unittest.TestCase):
t = Table("abc")
def test_where_field_equals(self):
q1 = Query.from_(self.t).select("*").where(self.t.foo == self.t.bar)
q2 = Query.from_(self.t).select("*").where(self.t.foo.eq(self.t.bar))
self.assertEqual('SELECT * FROM "abc" WHERE "foo"="bar"', str(q1))
self.assertEqual('SELECT * FROM "abc" WHERE "foo"="bar"', str(q2))
q = self.t.select("*").where(self.t.foo == self.t.bar)
self.assertEqual(q, q1)
def test_where_field_equals_where(self):
q = (
Query.from_(self.t)
.select("*")
.where(self.t.foo == 1)
.where(self.t.bar == self.t.baz)
)
self.assertEqual('SELECT * FROM "abc" WHERE "foo"=1 AND "bar"="baz"', str(q))
def test_where_field_equals_where_not(self):
q = (
Query.from_(self.t)
.select("*")
.where((self.t.foo == 1).negate())
.where(self.t.bar == self.t.baz)
)
self.assertEqual(
'SELECT * FROM "abc" WHERE NOT "foo"=1 AND "bar"="baz"', str(q)
)
def test_where_field_equals_where_two_not(self):
q = (
Query.from_(self.t)
.select("*")
.where((self.t.foo == 1).negate())
.where((self.t.bar == self.t.baz).negate())
)
self.assertEqual(
'SELECT * FROM "abc" WHERE NOT "foo"=1 AND NOT "bar"="baz"', str(q)
)
def test_where_single_quote(self):
q1 = Query.from_(self.t).select("*").where(self.t.foo == "bar'foo")
self.assertEqual("SELECT * FROM \"abc\" WHERE \"foo\"='bar''foo'", str(q1))
def test_where_field_equals_and(self):
q = (
Query.from_(self.t)
.select("*")
.where((self.t.foo == 1) & (self.t.bar == self.t.baz))
)
self.assertEqual('SELECT * FROM "abc" WHERE "foo"=1 AND "bar"="baz"', str(q))
def test_where_field_equals_or(self):
q = (
Query.from_(self.t)
.select("*")
.where((self.t.foo == 1) | (self.t.bar == self.t.baz))
)
self.assertEqual('SELECT * FROM "abc" WHERE "foo"=1 OR "bar"="baz"', str(q))
def test_where_nested_conditions(self):
q = (
Query.from_(self.t)
.select("*")
.where((self.t.foo == 1) | (self.t.bar == self.t.baz))
.where(self.t.baz == 0)
)
self.assertEqual(
'SELECT * FROM "abc" WHERE ("foo"=1 OR "bar"="baz") AND "baz"=0', str(q)
)
def test_where_field_starts_with(self):
q = Query.from_(self.t).select(self.t.star).where(self.t.foo.like("ab%"))
self.assertEqual('SELECT * FROM "abc" WHERE "foo" LIKE \'ab%\'', str(q))
def test_where_field_contains(self):
q = Query.from_(self.t).select(self.t.star).where(self.t.foo.like("%fg%"))
self.assertEqual('SELECT * FROM "abc" WHERE "foo" LIKE \'%fg%\'', str(q))
def test_where_field_ends_with(self):
q = Query.from_(self.t).select(self.t.star).where(self.t.foo.like("%yz"))
self.assertEqual('SELECT * FROM "abc" WHERE "foo" LIKE \'%yz\'', str(q))
def test_where_field_is_n_chars_long(self):
q = Query.from_(self.t).select(self.t.star).where(self.t.foo.like("___"))
self.assertEqual('SELECT * FROM "abc" WHERE "foo" LIKE \'___\'', str(q))
def test_where_field_does_not_start_with(self):
q = Query.from_(self.t).select(self.t.star).where(self.t.foo.not_like("ab%"))
self.assertEqual('SELECT * FROM "abc" WHERE "foo" NOT LIKE \'ab%\'', str(q))
def test_where_field_does_not_contain(self):
q = Query.from_(self.t).select(self.t.star).where(self.t.foo.not_like("%fg%"))
self.assertEqual('SELECT * FROM "abc" WHERE "foo" NOT LIKE \'%fg%\'', str(q))
def test_where_field_does_not_end_with(self):
q = Query.from_(self.t).select(self.t.star).where(self.t.foo.not_like("%yz"))
self.assertEqual('SELECT * FROM "abc" WHERE "foo" NOT LIKE \'%yz\'', str(q))
def test_where_field_is_not_n_chars_long(self):
q = Query.from_(self.t).select(self.t.star).where(self.t.foo.not_like("___"))
self.assertEqual('SELECT * FROM "abc" WHERE "foo" NOT LIKE \'___\'', str(q))
def test_where_field_matches_regex(self):
q = Query.from_(self.t).select(self.t.star).where(self.t.foo.regex(r"^b"))
self.assertEqual('SELECT * FROM "abc" WHERE "foo" REGEX \'^b\'', str(q))
def test_ignore_empty_criterion(self):
q1 = Query.from_(self.t).select("*").where(EmptyCriterion())
self.assertEqual('SELECT * FROM "abc"', str(q1))
def test_select_with_force_index_and_where(self):
q = (
Query.from_("abc")
.select("foo")
.where(self.t.foo == self.t.bar)
.force_index("egg")
)
self.assertEqual(
'SELECT "foo" FROM "abc" FORCE INDEX ("egg") WHERE "foo"="bar"', str(q)
)
def test_omit_where__table_schema(self):
q = Query.from_(Table("abc", "schema1")).delete()
self.assertEqual('DELETE FROM "schema1"."abc"', str(q))
class InsertIntoTests(unittest.TestCase):
table_abc = Table('abc')
def test_insert_one_column(self):
query = Query.into(self.table_abc).insert(1)
self.assertEqual('INSERT INTO "abc" VALUES (1)', str(query))
def test_insert_one_column_single_element_array(self):
query = Query.into(self.table_abc).insert((1,))
self.assertEqual('INSERT INTO "abc" VALUES (1)', str(query))
def test_insert_one_column_multi_element_array(self):
query = Query.into(self.table_abc).insert((1,), (2,))
self.assertEqual('INSERT INTO "abc" VALUES (1),(2)', str(query))
def test_insert_single_row_with_array_value(self):
query = Query.into(self.table_abc).insert(1, ['a', 'b', 'c'])
self.assertEqual('INSERT INTO "abc" VALUES (1,[\'a\',\'b\',\'c\'])', str(query))
def test_insert_multiple_rows_with_array_value(self):
query = Query.into(self.table_abc).insert((1, ['a', 'b', 'c']),
(2, ['c', 'd', 'e']), )
self.assertEqual('INSERT INTO "abc" '
'VALUES (1,[\'a\',\'b\',\'c\']),(2,[\'c\',\'d\',\'e\'])', str(query))
def test_insert_all_columns(self):
query = Query.into(self.table_abc).insert(1, 'a', True)
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true)', str(query))
def test_insert_all_columns_single_element(self):
query = Query.into(self.table_abc).insert((1, 'a', True))
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true)', str(query))
def test_insert_all_columns_multi_rows(self):
query = Query.into(self.table_abc).insert((1, 'a', True), (2, 'b', False))
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true),(2,\'b\',false)', str(query))
def test_insert_all_columns_multi_rows_chained(self):
query = Query.into(self.table_abc).insert(1, 'a', True).insert(2, 'b', False)
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true),(2,\'b\',false)', str(query))
def test_insert_all_columns_multi_rows_chained_mixed(self):
query = Query.into(self.table_abc).insert(
(1, 'a', True), (2, 'b', False)
).insert(3, 'c', True)
self.assertEqual('INSERT INTO "abc" VALUES '
'(1,\'a\',true),(2,\'b\',false),'
'(3,\'c\',true)', str(query))
def test_insert_all_columns_multi_rows_chained_multiple_rows(self):
query = Query.into(self.table_abc) \
.insert((1, 'a', True),
(2, 'b', False)) \
.insert((3, 'c', True),
(4, 'd', False))
self.assertEqual('INSERT INTO "abc" VALUES '
'(1,\'a\',true),(2,\'b\',false),'
'(3,\'c\',true),(4,\'d\',false)', str(query))
def test_insert_selected_columns(self):
query = Query.into(self.table_abc).columns(
self.table_abc.foo, self.table_abc.bar, self.table_abc.buz
).insert(1, 'a', True)
self.assertEqual('INSERT INTO "abc" ("foo","bar","buz") VALUES (1,\'a\',true)', str(query))
def test_insert_none_skipped(self):
query = Query.into(self.table_abc).insert()
self.assertEqual('', str(query))
def test_insert_ignore(self):
query = Query.into(self.table_abc).insert(1).ignore()
self.assertEqual('INSERT IGNORE INTO "abc" VALUES (1)', str(query))
def test_insert_null(self):
query = Query.into(self.table_abc).insert(None)
self.assertEqual('INSERT INTO "abc" VALUES (NULL)', str(query))
def handle_loan_paid(version, event):
"""Called shortly after a loan is paid.
"""
with LazyItgs(logger_iden=LOGGER_IDEN) as itgs:
itgs.logger.print(
Level.TRACE,
'Detected /u/{} had a payment toward one of the loans he gave out...',
event['lender']['username'])
trusts = Table('trusts')
itgs.read_cursor.execute(
Query.from_(trusts).select(1).where(
trusts.user_id == Parameter('%s')).get_sql(),
(event['lender']['id'], ))
if itgs.read_cursor.fetchone():
itgs.logger.print(
Level.TRACE, '/u/{} already has a trust entry - nothing to do',
event['lender']['username'])
return
loans = Table('loans')
itgs.read_cursor.execute(
Query.from_(loans).select(Count(
Star())).where(loans.lender_id == Parameter('%s')).where(
loans.repaid_at.notnull()).where(
loans.deleted_at.isnull()).get_sql(),
(event['lender']['id'], ))
(loans_compl_as_lender, ) = itgs.read_cursor.fetchone()
if loans_compl_as_lender < THRESHOLD_LOANS:
itgs.logger.print(
Level.DEBUG,
'/u/{} now has {} loans completed as lender, which is below threshold of {}',
event['lender']['username'], loans_compl_as_lender,
THRESHOLD_LOANS)
return
itgs.logger.print(
Level.DEBUG,
'/u/{} reached threshold of {} loans completed as lender, '
'which is above the threshold of {}, queuing trust entry...',
event['lender']['username'], loans_compl_as_lender,
THRESHOLD_LOANS)
itgs.write_cursor.execute(
Query.into(trusts).columns(
trusts.user_id, trusts.status,
trusts.reason).insert(*(Parameter('%s')
for _ in range(3))).get_sql(),
(event['lender']['id'], 'unknown', 'Vetting required'))
delayed_queue.store_event(
itgs,
delayed_queue.QUEUE_TYPES['trust'],
datetime.now(), {'username': event['lender']['username'].lower()},
commit=True)
itgs.logger.print(
Level.INFO,
'Gave /u/{} an explicit unknown status and added to trust queue',
event['lender']['username'])
(subject,
body) = get_letter_response(itgs,
'queue_trust_pm',
username=event['lender']['username'])
utils.reddit_proxy.send_request(itgs, 'lender_queue_trusts', version,
'compose', {
'recipient': '/r/borrow',
'subject': subject,
'body': body
})
itgs.logger.print(
Level.TRACE,
'Successfully alerted modmail of the new entry in trust queue')
def test_insert_returning_from_other_table(self):
table_cba = Table('cba')
with self.assertRaises(QueryException):
PostgreSQLQuery.into(self.table_abc).insert(1).returning(table_cba.id)
def __init__(self, table_reference, user_type):
super().__init__()
self.table_reference = Table(table_reference)
self.user_type = user_type
async def _prefetch_m2m_relation(
self,
instance_list: "Iterable[Model]",
field: str,
related_query: Tuple[Optional[str], "QuerySet"],
) -> "Iterable[Model]":
to_attr, related_query = related_query
instance_id_set: set = {
self._field_to_db(instance._meta.pk, instance.pk, instance)
for instance in instance_list
}
field_object: ManyToManyFieldInstance = self.model._meta.fields_map[ # type: ignore
field]
through_table = Table(field_object.through)
subquery = (self.db.query_class.from_(through_table).select(
through_table[field_object.backward_key].as_(
"_backward_relation_key"),
through_table[field_object.forward_key].as_(
"_forward_relation_key"),
).where(
through_table[field_object.backward_key].isin(instance_id_set)))
related_query_table = related_query.model._meta.basetable
related_pk_field = related_query.model._meta.db_pk_column
related_query.resolve_ordering(related_query.model,
related_query_table, [], {})
query = (related_query.query.join(subquery).on(
subquery._forward_relation_key ==
related_query_table[related_pk_field]).select(
subquery._backward_relation_key.as_("_backward_relation_key"),
*[
related_query_table[field].as_(field)
for field in related_query.fields
],
))
if related_query._q_objects:
joined_tables: List[Table] = []
modifier = QueryModifier()
for node in related_query._q_objects:
modifier &= node.resolve(
model=related_query.model,
annotations=related_query._annotations,
custom_filters=related_query._custom_filters,
table=related_query_table,
)
where_criterion, joins, having_criterion = modifier.get_query_modifiers(
)
for join in joins:
if join[0] not in joined_tables:
query = query.join(join[0],
how=JoinType.left_outer).on(join[1])
joined_tables.append(join[0])
if where_criterion:
query = query.where(where_criterion)
if having_criterion:
query = query.having(having_criterion)
_, raw_results = await self.db.execute_query(query.get_sql())
# TODO: we should only resolve the PK's once
relations = [(
self.model._meta.pk.to_python_value(e["_backward_relation_key"]),
field_object.related_model._meta.pk.to_python_value(
e[related_pk_field]),
) for e in raw_results]
related_object_list = [
related_query.model._init_from_db(**e) for e in raw_results
]
await self.__class__(model=related_query.model,
db=self.db,
prefetch_map=related_query._prefetch_map
)._execute_prefetch_queries(related_object_list)
related_object_map = {e.pk: e for e in related_object_list}
relation_map: Dict[str, list] = {}
for object_id, related_object_id in relations:
if object_id not in relation_map:
relation_map[object_id] = []
relation_map[object_id].append(
related_object_map[related_object_id])
for instance in instance_list:
relation_container = getattr(instance, field)
relation_container._set_result_for_query(
relation_map.get(instance.pk, []), to_attr)
return instance_list
def test_tabled_eq_fields_equally_hashed(self):
client_name1 = Field(name="name", table=Table("clients"))
client_name2 = Field(name="name", table=Table("clients"))
self.assertTrue(hash(client_name1) == hash(client_name2))
def __init__(self, table_reference):
self.table_reference = Table(table_reference)
self.table_name = table_reference
def test_tabled_ne_fields_differently_hashed(self):
customer_name = Field(name="name", table=Table("customers"))
client_name = Field(name="name", table=Table("clients"))
self.assertTrue(hash(customer_name) != hash(client_name))
class WhereTests(unittest.TestCase):
t = Table('abc')
def test_where_field_equals(self):
q1 = Query.from_(self.t).select('*').where(self.t.foo == self.t.bar)
q2 = Query.from_(self.t).select('*').where(self.t.foo.eq(self.t.bar))
self.assertEqual('SELECT * FROM "abc" WHERE "foo"="bar"', str(q1))
self.assertEqual('SELECT * FROM "abc" WHERE "foo"="bar"', str(q2))
def test_where_field_equals_where(self):
q = Query.from_(self.t).select('*').where(self.t.foo == 1).where(
self.t.bar == self.t.baz)
self.assertEqual('SELECT * FROM "abc" WHERE "foo"=1 AND "bar"="baz"',
str(q))
def test_where_field_equals_and(self):
q = Query.from_(self.t).select('*').where((self.t.foo == 1)
& (self.t.bar == self.t.baz))
self.assertEqual('SELECT * FROM "abc" WHERE "foo"=1 AND "bar"="baz"',
str(q))
def test_where_field_equals_or(self):
q = Query.from_(self.t).select('*').where((self.t.foo == 1)
| (self.t.bar == self.t.baz))
self.assertEqual('SELECT * FROM "abc" WHERE "foo"=1 OR "bar"="baz"',
str(q))
def test_where_nested_conditions(self):
q = Query.from_(
self.t).select('*').where((self.t.foo == 1)
| (self.t.bar == self.t.baz)).where(
self.t.baz == 0)
self.assertEqual(
'SELECT * FROM "abc" WHERE ("foo"=1 OR "bar"="baz") AND "baz"=0',
str(q))
def test_where_field_starts_with(self):
q = Query.from_(self.t).select(self.t.star).where(
self.t.foo.like('ab%'))
self.assertEqual("SELECT * FROM \"abc\" WHERE \"foo\" LIKE 'ab%'",
str(q))
def test_where_field_contains(self):
q = Query.from_(self.t).select(self.t.star).where(
self.t.foo.like('%fg%'))
self.assertEqual("SELECT * FROM \"abc\" WHERE \"foo\" LIKE '%fg%'",
str(q))
def test_where_field_ends_with(self):
q = Query.from_(self.t).select(self.t.star).where(
self.t.foo.like('%yz'))
self.assertEqual("SELECT * FROM \"abc\" WHERE \"foo\" LIKE '%yz'",
str(q))
def test_where_field_is_n_chars_long(self):
q = Query.from_(self.t).select(self.t.star).where(
self.t.foo.like('___'))
self.assertEqual("SELECT * FROM \"abc\" WHERE \"foo\" LIKE '___'",
str(q))
def test_where_field_matches_regex(self):
q = Query.from_(self.t).select(self.t.star).where(
self.t.foo.regex(r'^b'))
self.assertEqual("SELECT * FROM \"abc\" WHERE \"foo\" REGEX '^b'",
str(q))
class JSONBOperatorsTests(unittest.TestCase):
# reference https://www.postgresql.org/docs/9.5/functions-json.html
table_abc = Table("abc")
def test_json_contains_for_json(self):
q = PostgreSQLQuery.select(JSON({"a": 1, "b": 2}).contains({"a": 1}))
# gotta split this one up to avoid the indeterminate order
sql = str(q)
start, end = 9, -13
self.assertEqual("SELECT '{}'@>'{\"a\":1}'", sql[:start] + sql[end:])
members_set = set(sql[start:end].split(","))
self.assertSetEqual({'"a":1', '"b":2'}, members_set)
def test_json_contains_for_field(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.contains({"dates":
"2018-07-10 - 2018-07-17"})))
self.assertEqual(
"SELECT * "
'FROM "abc" '
'WHERE "json"@>\'{"dates":"2018-07-10 - 2018-07-17"}\'',
str(q),
)
def test_json_contained_by_using_str_arg(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.contained_by(
OrderedDict([
("dates", "2018-07-10 - 2018-07-17"),
("imported", "8"),
]))))
self.assertEqual(
'SELECT * FROM "abc" '
'WHERE "json"<@\'{"dates":"2018-07-10 - 2018-07-17","imported":"8"}\'',
str(q),
)
def test_json_contained_by_using_list_arg(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.contained_by(["One", "Two", "Three"])))
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"<@\'["One","Two","Three"]\'',
str(q))
def test_json_contained_by_with_complex_criterion(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.contained_by(["One", "Two", "Three"])
& (self.table_abc.id == 26)))
self.assertEqual(
'SELECT * FROM "abc" WHERE "json"<@\'["One","Two","Three"]\' AND "id"=26',
str(q),
)
def test_json_has_key(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.has_key("dates")))
self.assertEqual('SELECT * FROM "abc" WHERE "json"?\'dates\'', str(q))
def test_json_has_keys(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.has_keys(["dates", "imported"])))
self.assertEqual(
"SELECT * FROM \"abc\" WHERE \"json\"?&ARRAY['dates','imported']",
str(q))
def test_json_has_any_keys(self):
q = (PostgreSQLQuery.from_(self.table_abc).select("*").where(
self.table_abc.json.has_any_keys(["dates", "imported"])))
self.assertEqual(
"SELECT * FROM \"abc\" WHERE \"json\"?|ARRAY['dates','imported']",
str(q))
class AliasTests(unittest.TestCase):
t = Table('abc')
def test_table_field(self):
q = Query.from_(self.t).select(self.t.foo.as_('bar'))
self.assertEqual('SELECT "foo" "bar" FROM "abc"', str(q))
def test_table_field__multi(self):
q = Query.from_(self.t).select(self.t.foo.as_('bar'),
self.t.fiz.as_('buz'))
self.assertEqual('SELECT "foo" "bar","fiz" "buz" FROM "abc"', str(q))
def test_arithmetic_function(self):
q = Query.from_(self.t).select((self.t.foo + self.t.bar).as_('biz'))
self.assertEqual('SELECT "foo"+"bar" "biz" FROM "abc"', str(q))
def test_functions_using_as(self):
q = Query.from_(self.t).select(fn.Count('*').as_('foo'))
self.assertEqual('SELECT COUNT(*) "foo" FROM "abc"', str(q))
def test_functions_using_constructor_param(self):
q = Query.from_(self.t).select(fn.Count('*', alias='foo'))
self.assertEqual('SELECT COUNT(*) "foo" FROM "abc"', str(q))
def test_ignored_in_where(self):
q = Query.from_(self.t).select(
self.t.foo).where(self.t.foo.as_('bar') == 1)
self.assertEqual('SELECT "foo" FROM "abc" WHERE "foo"=1', str(q))
def test_ignored_in_groupby(self):
q = Query.from_(self.t).select(self.t.foo).groupby(
self.t.foo.as_('bar'))
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_ignored_in_orderby(self):
q = Query.from_(self.t).select(self.t.foo).orderby(
self.t.foo.as_('bar'))
self.assertEqual('SELECT "foo" FROM "abc" ORDER BY "foo"', str(q))
def test_ignored_in_criterion(self):
c = self.t.foo.as_('bar') == 1
self.assertEqual('"foo"=1', str(c))
def test_ignored_in_criterion_comparison(self):
c = self.t.foo.as_('bar') == self.t.fiz.as_('buz')
self.assertEqual('"foo"="fiz"', str(c))
def test_ignored_in_field_inside_case(self):
q = Query.from_(self.t).select(Case().when(self.t.foo == 1, 'a').else_(
self.t.bar.as_('"buz"')))
self.assertEqual(
"SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE \"bar\" END FROM \"abc\"",
str(q))
def test_case_using_as(self):
q = Query.from_(self.t).select(Case().when(self.t.foo == 1,
'a').else_('b').as_('bar'))
self.assertEqual(
"SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE 'b' END \"bar\" FROM \"abc\"",
str(q))
def test_case_using_constructor_param(self):
q = Query.from_(self.t).select(
Case(alias='bar').when(self.t.foo == 1, 'a').else_('b'))
self.assertEqual(
"SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE 'b' END \"bar\" FROM \"abc\"",
str(q))
def test_select_aliases(self):
test_query = Query.from_(self.t).select(
self.t.foo.as_('fiz1'),
self.t.bar.as_('buz1')).groupby(self.t.foo.as_('fiz2'),
self.t.bar.as_('buz2'))
self.assertListEqual(['fiz1', 'buz1'], test_query.select_aliases())
def test_select_aliases_mixed_with_fields(self):
test_query = Query.from_(self.t).select(self.t.foo.as_('fiz1'),
self.t.bar).groupby(
self.t.foo,
self.t.bar.as_('buz2'))
self.assertListEqual(['fiz1', 'bar'], test_query.select_aliases())
def test_select_aliases_mixed_with_complex_fields(self):
test_query = Query.from_(self.t).select(
self.t.foo.as_('foobar'),
fn.Count(self.t.fiz + self.t.buz)).groupby(self.t.foo)
self.assertListEqual(['foobar', 'COUNT("fiz"+"buz")'],
test_query.select_aliases())
def test_groupby_aliases(self):
test_query = Query.from_(self.t).select(
self.t.foo.as_('fiz1'),
self.t.bar.as_('buz1')).groupby(self.t.foo.as_('fiz2'),
self.t.bar.as_('buz2'))
self.assertListEqual(['fiz2', 'buz2'], test_query.groupby_aliases())
def test_groupby_aliases_mixed_with_fields(self):
test_query = Query.from_(self.t).select(self.t.foo.as_('fiz1'),
self.t.bar).groupby(
self.t.foo,
self.t.bar.as_('buz2'))
self.assertListEqual(['foo', 'buz2'], test_query.groupby_aliases())
class RollupTests(unittest.TestCase):
table = Table('abc')
def test_mysql_one_groupby(self):
q = Query.from_(self.table).select(self.table.foo,
fn.Sum(self.table.bar)).rollup(
self.table.foo, vendor='mysql')
self.assertEqual(
'SELECT "foo",SUM("bar") FROM "abc" GROUP BY "foo" WITH ROLLUP',
str(q))
def test_mysql_rollup_two_groupbys(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
fn.Sum(self.table.bar)).rollup(
self.table.foo,
self.table.fiz,
vendor='mysql')
self.assertEqual(
'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY "foo","fiz" WITH ROLLUP',
str(q))
def test_no_rollup_before_groupby(self):
with self.assertRaises(RollupException):
Query.from_(self.table).select(
self.table.foo, fn.Sum(self.table.bar)).rollup(vendor='mysql')
def test_no_rollup_after_rollup(self):
with self.assertRaises(AttributeError):
Query.from_(self.table).select(
self.table.foo, self.table.fiz, fn.Sum(self.table.bar)).rollup(
self.table.foo, vendor='mysql').rollup(self.table.fiz,
vendor='mysql')
def test_verticaoracle_func_one_groupby(self):
q = Query.from_(self.table).select(self.table.foo,
fn.Sum(self.table.bar)).groupby(
Rollup(self.table.foo))
self.assertEqual(
'SELECT "foo",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo")',
str(q))
def test_verticaoracle_func_two_groupbys(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
fn.Sum(self.table.bar)).groupby(
Rollup(
self.table.foo,
self.table.fiz,
))
self.assertEqual(
'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo","fiz")',
str(q))
def test_verticaoracle_func_partial(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
self.table.buz,
fn.Sum(self.table.bar)).groupby(
Rollup(
self.table.foo,
self.table.fiz,
),
self.table.buz,
)
self.assertEqual(
'SELECT "foo","fiz","buz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo","fiz"),"buz"',
str(q))
def test_verticaoracle_from_groupbys(self):
q = Query.from_(self.table).select(self.table.foo,
fn.Sum(self.table.bar)).rollup(
self.table.foo)
self.assertEqual(
'SELECT "foo",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo")',
str(q))
def test_verticaoracle_from_two_groupbys(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
fn.Sum(self.table.bar)).rollup(
self.table.foo,
self.table.fiz,
)
self.assertEqual(
'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo","fiz")',
str(q))
def test_verticaoracle_from_parameters(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
fn.Sum(self.table.bar)).groupby(
self.table.foo, ).rollup(
self.table.fiz, )
self.assertEqual(
'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY "foo",ROLLUP("fiz")',
str(q))
def test_verticaoracle_multiple_rollups(self):
q = Query.from_(self.table).select(self.table.foo, self.table.fiz,
fn.Sum(self.table.bar)).rollup(
self.table.foo, ).rollup(
self.table.fiz, )
self.assertEqual(
'SELECT "foo","fiz",SUM("bar") FROM "abc" GROUP BY ROLLUP("foo"),ROLLUP("fiz")',
str(q))
def test_verticaoracle_rollups_with_parity(self):
q = Query.from_(self.table).select(self.table.buz, ).rollup(
[self.table.foo, self.table.bar],
self.table.fiz,
)
self.assertEqual(
'SELECT "buz" FROM "abc" GROUP BY ROLLUP(("foo","bar"),"fiz")',
str(q))
class SelectTests(unittest.TestCase):
t = Table('abc')
def test_empty_query(self):
q = Query.from_('abc')
self.assertEqual('', str(q))
def test_select__star(self):
q = Query.from_('abc').select('*')
self.assertEqual('SELECT * FROM "abc"', str(q))
def test_select__table_schema(self):
q = Query.from_(Table('abc', 'schema1')).select('*')
self.assertEqual('SELECT * FROM "schema1"."abc"', str(q))
def test_select__star__replacement(self):
q = Query.from_('abc').select('foo').select('*')
self.assertEqual('SELECT * FROM "abc"', str(q))
def test_select__distinct__single(self):
q = Query.from_('abc').select('foo').distinct()
self.assertEqual('SELECT distinct "foo" FROM "abc"', str(q))
def test_select__distinct__multi(self):
q = Query.from_('abc').select('foo', 'bar').distinct()
self.assertEqual('SELECT distinct "foo","bar" FROM "abc"', str(q))
def test_select__column__single__str(self):
q = Query.from_('abc').select('foo')
self.assertEqual('SELECT "foo" FROM "abc"', str(q))
def test_select__column__single__field(self):
t = Table('abc')
q = Query.from_(t).select(t.foo)
self.assertEqual('SELECT "foo" FROM "abc"', str(q))
def test_select__columns__multi__str(self):
q1 = Query.from_('abc').select('foo', 'bar')
q2 = Query.from_('abc').select('foo').select('bar')
self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q1))
self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q2))
def test_select__columns__multi__field(self):
q1 = Query.from_(self.t).select(self.t.foo, self.t.bar)
q2 = Query.from_(self.t).select(self.t.foo).select(self.t.bar)
self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q1))
self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q2))
def test_select__fields_after_star(self):
q = Query.from_('abc').select('*').select('foo')
self.assertEqual('SELECT * FROM "abc"', str(q))
def test_select__no_table(self):
q = Query.select(1, 2, 3)
self.assertEqual('SELECT 1,2,3', str(q))
def test_select_then_add_table(self):
q = Query.select(1).select(2, 3).from_('abc').select('foo')
self.assertEqual('SELECT 1,2,3,"foo" FROM "abc"', str(q))
def test_static_from_function_removed_after_called(self):
with self.assertRaises(AttributeError):
Query.from_('abc').from_('efg')
def test_instance_from_function_removed_after_called(self):
with self.assertRaises(AttributeError):
Query.select(1).from_('abc').from_('efg')
def test_select_with_limit(self):
q1 = Query.from_('abc').select('foo')[:10]
self.assertEqual('SELECT "foo" FROM "abc" LIMIT 10', str(q1))
def test_select_with_limit__func(self):
q1 = Query.from_('abc').select('foo').limit(10)
self.assertEqual('SELECT "foo" FROM "abc" LIMIT 10', str(q1))
def test_select_with_offset(self):
q1 = Query.from_('abc').select('foo')[10:]
self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10', str(q1))
def test_select_with_offset__func(self):
q1 = Query.from_('abc').select('foo').offset(10)
self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10', str(q1))
def test_select_with_limit_and_offset(self):
q1 = Query.from_('abc').select('foo')[10:10]
self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10 LIMIT 10', str(q1))
class PostgresInsertIntoReturningTests(unittest.TestCase):
table_abc = Table('abc')
def test_insert_returning_one_field(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).returning(self.table_abc.id)
self.assertEqual('INSERT INTO "abc" VALUES (1) RETURNING id', str(query))
def test_insert_returning_one_field_str(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).returning('id')
self.assertEqual('INSERT INTO "abc" VALUES (1) RETURNING id', str(query))
def test_insert_returning_all_fields(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).returning(self.table_abc.star)
self.assertEqual('INSERT INTO "abc" VALUES (1) RETURNING *', str(query))
def test_insert_returning_all_fields_and_arithmetics(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).returning(
self.table_abc.star,
self.table_abc.f1 + self.table_abc.f2
)
self.assertEqual('INSERT INTO "abc" VALUES (1) RETURNING *,f1+f2', str(query))
def test_insert_all_columns_multi_rows_chained_returning_star(self):
query = PostgreSQLQuery.into(
self.table_abc
).insert(1, 'a', True).insert(2, 'b', False).returning(self.table_abc.star)
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true),(2,\'b\',false) RETURNING *', str(query))
def test_insert_all_columns_multi_rows_chained_returning_star_and_id(self):
query = PostgreSQLQuery.into(
self.table_abc
).insert(1, 'a', True).insert(2, 'b', False).returning(
self.table_abc.name,
self.table_abc.star,
self.table_abc.id,
)
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true),(2,\'b\',false) RETURNING *', str(query))
def test_insert_all_columns_multi_rows_chained_returning_star_str(self):
query = PostgreSQLQuery.into(
self.table_abc
).insert(1, 'a', True).insert(2, 'b', False).returning('*')
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true),(2,\'b\',false) RETURNING *', str(query))
def test_insert_all_columns_single_element_arrays(self):
query = PostgreSQLQuery.into(self.table_abc).insert((1, 'a', True)).returning(self.table_abc.star)
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true) RETURNING *', str(query))
def test_insert_returning_null(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).returning(None)
self.assertEqual('INSERT INTO "abc" VALUES (1) RETURNING NULL', str(query))
def test_insert_returning_tuple(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).returning((1, 2, 3))
self.assertEqual('INSERT INTO "abc" VALUES (1) RETURNING (1,2,3)', str(query))
def test_insert_returning_arithmetics(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).returning(self.table_abc.f1 + self.table_abc.f2)
self.assertEqual('INSERT INTO "abc" VALUES (1) RETURNING f1+f2', str(query))
def test_insert_returning_aggregate(self):
with self.assertRaises(QueryException):
PostgreSQLQuery.into(self.table_abc).insert(1).returning(Avg(self.table_abc.views))
def test_insert_returning_from_other_table(self):
table_cba = Table('cba')
with self.assertRaises(QueryException):
PostgreSQLQuery.into(self.table_abc).insert(1).returning(table_cba.id)
class PostgresInsertIntoOnConflictTests(unittest.TestCase):
table_abc = Table("abc")
def test_insert_on_conflict_do_nothing_field(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).on_conflict(
self.table_abc.id).do_nothing()
self.assertEqual(
'INSERT INTO "abc" VALUES (1) ON CONFLICT ("id") DO NOTHING',
str(query))
def test_insert_on_conflict_do_nothing_multiple_fields(self):
query = (PostgreSQLQuery.into(self.table_abc).insert(1).on_conflict(
self.table_abc.id, self.table_abc.sub_id).do_nothing())
self.assertEqual(
'INSERT INTO "abc" VALUES (1) ON CONFLICT ("id", "sub_id") DO NOTHING',
str(query))
def test_insert_on_conflict_do_nothing_field_str(self):
query = PostgreSQLQuery.into(
self.table_abc).insert(1).on_conflict("id").do_nothing()
self.assertEqual(
'INSERT INTO "abc" VALUES (1) ON CONFLICT ("id") DO NOTHING',
str(query))
def test_insert_on_conflict_do_nothing_multiple_fields_str(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).on_conflict(
"id", "sub_id").do_nothing()
self.assertEqual(
'INSERT INTO "abc" VALUES (1) ON CONFLICT ("id", "sub_id") DO NOTHING',
str(query))
def test_insert_on_conflict_do_nothing_mixed_fields(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).on_conflict(
"id", self.table_abc.sub_id).do_nothing()
self.assertEqual(
'INSERT INTO "abc" VALUES (1) ON CONFLICT ("id", "sub_id") DO NOTHING',
str(query))
def test_insert_on_conflict_do_update_field(self):
query = (PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict(self.table_abc.id).do_update(
self.table_abc.name, "m"))
self.assertEqual(
"INSERT INTO \"abc\" VALUES (1,'m') ON CONFLICT (\"id\") DO UPDATE SET \"name\"='m'",
str(query),
)
def test_insert_on_conflict_do_update_multiple_fields(self):
query = (PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict(self.table_abc.id,
self.table_abc.sub_id).do_update(
self.table_abc.name, "m"))
self.assertEqual(
"INSERT INTO \"abc\" VALUES (1,'m') ON CONFLICT (\"id\", \"sub_id\") DO UPDATE SET \"name\"='m'",
str(query),
)
def test_insert_on_conflict_do_update_field_str(self):
query = PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict("id").do_update("name", "m")
self.assertEqual(
"INSERT INTO \"abc\" VALUES (1,'m') ON CONFLICT (\"id\") DO UPDATE SET \"name\"='m'",
str(query),
)
def test_insert_on_conflict_do_update_multiple_fields_str(self):
query = PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict("id", "sub_id").do_update("name", "m")
self.assertEqual(
"INSERT INTO \"abc\" VALUES (1,'m') ON CONFLICT (\"id\", \"sub_id\") DO UPDATE SET \"name\"='m'",
str(query),
)
def test_insert_on_conflict_do_update_multiple_mixed_fields(self):
query = (PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict("id",
self.table_abc.sub_id).do_update("name", "m"))
self.assertEqual(
"INSERT INTO \"abc\" VALUES (1,'m') ON CONFLICT (\"id\", \"sub_id\") DO UPDATE SET \"name\"='m'",
str(query),
)
def test_insert_on_conflict_no_handler(self):
with self.assertRaises(QueryException):
query = str(
PostgreSQLQuery.into(self.table_abc).insert(1).on_conflict(
self.table_abc.id))
def test_insert_on_conflict_two_handlers_do_nothing(self):
with self.assertRaises(QueryException):
query = (PostgreSQLQuery.into(self.table_abc).insert(
1).on_conflict("id").do_nothing().do_update(
self.table_abc.name, "m"))
def test_insert_on_conflict_two_handlers_do_update(self):
with self.assertRaises(QueryException):
query = (PostgreSQLQuery.into(
self.table_abc).insert(1).on_conflict(
self.table_abc.id).do_update(self.table_abc.name,
"m").do_nothing())
def test_non_insert_on_conflict_do_nothing(self):
with self.assertRaises(QueryException):
query = PostgreSQLQuery.update(self.table_abc).set(
"foo", "bar").on_conflict("id").do_nothing()
def test_non_insert_on_conflict_do_update(self):
with self.assertRaises(QueryException):
query = (PostgreSQLQuery.update(self.table_abc).set(
"foo", "bar").on_conflict("id").do_update(["name"], ["m"]))
def test_insert_on_fieldless_conflict_do_nothing(self):
query = PostgreSQLQuery.into(
self.table_abc).insert(1).on_conflict(None).do_nothing()
self.assertEqual('INSERT INTO "abc" VALUES (1) ON CONFLICT DO NOTHING',
str(query))
def test_insert_on_fieldless_conflict_do_update_field(self):
with self.assertRaises(QueryException):
query = str(
PostgreSQLQuery.into(self.table_abc).insert(
1,
"m").on_conflict(None).do_update(self.table_abc.name, "m"))
def test_on_conflict_from_subquery(self):
table_bcd = Table('bcd')
query = (PostgreSQLQuery.into(self.table_abc).insert(
self.table_abc.fname, self.table_abc.lname).select(
table_bcd.fname, table_bcd.lname).from_(table_bcd).on_conflict(
self.table_abc.id, self.table_abc.sub_id).do_update(
self.table_abc.fname, 1).do_update(
self.table_abc.lname, table_bcd.lname).do_update(
self.table_abc.cname,
Case().when(self.table_abc.cname.eq('cname'),
'new_name').else_(
self.table_abc.cname),
))
self.assertEqual(
'INSERT INTO "abc" VALUES ("fname","lname") '
'ON CONFLICT ("id", "sub_id") '
'DO UPDATE SET "fname"=1,"lname"="bcd"."lname",'
'"cname"=CASE WHEN "abc"."cname"=\'cname\' THEN \'new_name\' ELSE "abc"."cname" END',
str(query),
)
def test_on_conflict_where_empty_conflict_fields_do_nothing(self):
with self.assertRaises(QueryException):
(PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict().where(self.table_abc.abc.eq(0)).where(
self.table_abc.cde.eq(0)).do_nothing())
def test_on_conflict_where_conflict_fields_do_nothing(self):
qs = (PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict('id').where(self.table_abc.abc.eq(0)).where(
self.table_abc.cde.eq(0)).do_nothing())
self.assertEqual(
'''INSERT INTO "abc" VALUES (1,'m') ON CONFLICT ("id") WHERE "abc"=0 AND "cde"=0 DO NOTHING''',
str(qs))
def test_on_conflict_where_empty_conflict_fields_do_update(self):
with self.assertRaises(QueryException):
(PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict().where(self.table_abc.abc.eq(0)).where(
self.table_abc.cde.eq(0)).do_update('field', 'val'))
def test_on_conflict_where_conflict_fields_do_update(self):
qs = (PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict('id').where(self.table_abc.abc.eq(0)).where(
self.table_abc.cde.eq(0)).do_update('field', 'val'))
self.assertEqual(
'''INSERT INTO "abc" VALUES (1,'m') ON CONFLICT ("id") WHERE "abc"=0 AND "cde"=0 '''
'''DO UPDATE SET "field"='val\'''',
str(qs),
)
def test_where_and_on_conflict_where(self):
table_bcd = Table('bcd')
qs = (PostgreSQLQuery.into(
self.table_abc).select(table_bcd.abc).from_(table_bcd).where(
table_bcd.abc.eq('1')).on_conflict('id').where(
self.table_abc.abc.eq(0)).where(
self.table_abc.cde.eq(0)).do_update('field', 'val'))
self.assertEqual(
'INSERT INTO "abc" SELECT "abc" FROM "bcd" WHERE "abc"=\'1\' '
'ON CONFLICT ("id") WHERE "abc"=0 AND "cde"=0 DO UPDATE SET "field"=\'val\'',
str(qs),
)
def test_on_conflict_do_nothing_where(self):
with self.assertRaises(QueryException):
(PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict().do_nothing().where(
self.table_abc.abc.eq(1)))
def test_empty_on_conflict_do_update_where(self):
with self.assertRaises(QueryException):
(PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict().do_update('abc', 1).where(
self.table_abc.abc.eq(1)))
def test_on_conflict_do_update_where(self):
qs = (PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict("id").do_update('abc', 1).where(
self.table_abc.abc.eq(1)))
self.assertEqual(
'INSERT INTO "abc" VALUES (1,\'m\') ON CONFLICT ("id") DO UPDATE SET "abc"=1 WHERE "abc"."abc"=1',
str(qs))
def test_on_conflict_do_update_with_excluded_where(self):
qs = (PostgreSQLQuery.into(self.table_abc).insert(
1, "m").on_conflict("id").do_update('abc').where(
self.table_abc.abc.eq(1)))
self.assertEqual(
'INSERT INTO "abc" VALUES (1,\'m\') ON CONFLICT ("id") DO UPDATE SET "abc"=EXCLUDED."abc" WHERE '
'"abc"."abc"=1',
str(qs),
)
def test_on_conflict_where_complex(self):
table_bcd = Table('bcd')
qs = (PostgreSQLQuery.into(
self.table_abc).select(table_bcd.abc).from_(table_bcd).where(
table_bcd.abc.eq('1')).on_conflict('id').where(
self.table_abc.abc.eq(0)).where(
self.table_abc.cde.eq(0)).do_update(
'field',
'val').where(self.table_abc.id.eq(2)).where(
self.table_abc.sub_id.eq(3)))
self.assertEqual(
'INSERT INTO "abc" SELECT "abc" FROM "bcd" WHERE "abc"=\'1\' '
'ON CONFLICT ("id") WHERE "abc"=0 AND "cde"=0 '
'DO UPDATE SET "field"=\'val\' WHERE "abc"."id"=2 AND "abc"."sub_id"=3',
str(qs),
)
def setUpClass(cls):
super(PseudoColumnsTest, cls).setUpClass()
cls.table1 = Table('table1')
