def test_clear_log(self):
"""
Makes sure queries are cleared
"""
logger_one = Logger('one')
logger_one.start_logging()
query = Query().from_table(Account)
# run a query and update the logger's query list
query.select()
logger_one.update_log()
# the index should be at 1
self.assertEqual(1, logger_one.query_index)
# increment the connection query count
query.select()
# clear the log
logger_one.clear_log()
# make sure no queries
self.assertEqual(0, len(logger_one.queries))
# query index should match that of the connection log
self.assertEqual(2, logger_one.query_index)
def test_clear_log(self):
"""
Makes sure queries are cleared
"""
logger_one = Logger('one')
logger_one.start_logging()
query = Query().from_table(Account)
# run a query and update the logger's query list
query.select()
logger_one.update_log()
# the index should be at 1
self.assertEqual(1, logger_one.query_index)
# increment the connection query count
query.select()
# clear the log
logger_one.clear_log()
# make sure no queries
self.assertEqual(0, len(logger_one.queries))
# query index should match that of the connection log
self.assertEqual(2, logger_one.query_index)
def test_start_logging(self):
"""
Verifies that the query index gets updated
"""
logger = Logger()
query = Query().from_table(Account)
query.select()
query.select()
logger.start_logging()
self.assertEqual(2, logger.query_index)
def test_start_logging(self):
"""
Verifies that the query index gets updated
"""
logger = Logger()
query = Query().from_table(Account)
query.select()
query.select()
logger.start_logging()
self.assertEqual(2, logger.query_index)
def test_count(self):
"""
Verifies that the correct number of queries is returned
"""
logger = Logger()
logger.start_logging()
query = Query().from_table(Account)
query.select()
query.select()
self.assertEqual(2, logger.count())
query.select()
self.assertEqual(3, logger.count())
def test_count(self):
"""
Verifies that the correct number of queries is returned
"""
logger = Logger()
logger.start_logging()
query = Query().from_table(Account)
query.select()
query.select()
self.assertEqual(2, logger.count())
query.select()
self.assertEqual(3, logger.count())
def test_joined_model_foreign_reverse(self):
query = Query().from_table(Order).join(right_table=Account, fields=["*"], prefix_fields=True)
rows = query.select(True)
self.assertGreater(len(rows), 0, "No records")
logger = Logger()
logger.start_logging()
for row in rows:
self.assertIsInstance(row, Order, "Record is not model instance")
self.assertIs(hasattr(row, "account"), True, "Row does not have nested model")
self.assertIsInstance(row.account, Account, "Nested record is not model instance")
self.assertEqual(logger.count(), 0, "Queries were executed when none should")
def test_joined_model_foreign_reverse(self):
query = Query().from_table(Order).join(right_table=Account,
fields=['*'],
prefix_fields=True)
rows = query.select(True)
self.assertGreater(len(rows), 0, 'No records')
logger = Logger()
logger.start_logging()
for row in rows:
self.assertIsInstance(row, Order, 'Record is not model instance')
self.assertIs(hasattr(row, 'account'), True,
'Row does not have nested model')
self.assertIsInstance(row.account, Account,
'Nested record is not model instance')
self.assertEqual(logger.count(), 0,
'Queries were executed when none should')
def test_update_null_numbers(self):
"""
Verifies that null values can be bulk updated
"""
order1 = G(Order, revenue=10)
order2 = G(Order, revenue=20)
order3 = G(Order, revenue=30)
query = Query().from_table(table=Order, fields=['id', 'revenue'])
logger = Logger()
logger.start_logging()
rows = [[order1.id, None], [order2.id, None], [order3.id, 3000]]
query.update(rows)
orders = list(Order.objects.order_by('id'))
self.assertIsNone(orders[0].revenue)
self.assertIsNone(orders[1].revenue)
self.assertEqual(3000, orders[2].revenue)
def test_update_all_nulls(self):
"""
Verifies that the sql is modified when all values for a field are null. For whatever reason,
postgres doesn't handle this the same when all values are null
"""
order1 = G(Order, revenue=10, margin=5)
order2 = G(Order, revenue=20, margin=10)
order3 = G(Order, revenue=30, margin=15)
query = Query().from_table(table=Order, fields=['id', 'revenue', 'margin'])
logger = Logger()
logger.start_logging()
rows = [[order1.id, None, 50], [order2.id, None, 100], [order3.id, None, 150]]
query.update(rows)
orders = list(Order.objects.order_by('id'))
self.assertIsNone(orders[0].revenue)
self.assertIsNone(orders[1].revenue)
self.assertIsNone(orders[2].revenue)
def test_stop_logging(self):
"""
Verifies that the logger stops caring about queries
"""
logger = Logger()
logger.start_logging()
query = Query().from_table(Account)
query.select()
query.select()
self.assertEqual(2, logger.count())
logger.stop_logging()
query.select()
query.select()
self.assertEqual(2, logger.count())
logger.start_logging()
query.select()
self.assertEqual(3, logger.count())
def test_joined_model_foreign(self):
query = Query().from_table(
Account
).join(
right_table=Order,
fields=[
'*'
],
prefix_fields=True
)
rows = query.select(True)
self.assertGreater(len(rows), 0, 'No records')
logger = Logger()
logger.start_logging()
for row in rows:
self.assertIsInstance(row, Account, 'Record is not model instance')
self.assertIs(hasattr(row, 'order'), True, 'Row does not have nested model')
self.assertIsInstance(row.order, Order, 'Nested record is not model instance')
self.assertEqual(logger.count(), 0, 'Queries were executed when none should')
def test_stop_logging(self):
"""
Verifies that the logger stops caring about queries
"""
logger = Logger()
logger.start_logging()
query = Query().from_table(Account)
query.select()
query.select()
self.assertEqual(2, logger.count())
logger.stop_logging()
query.select()
query.select()
self.assertEqual(2, logger.count())
logger.start_logging()
query.select()
self.assertEqual(3, logger.count())
def test_logger(self):
logger_one = Logger('one')
logger_two = Logger('two')
logger_one.start_logging()
query = Query().from_table(Account)
query.select()
self.assertEqual(logger_one.count(), 1, 'Incorrect number of queries')
query.select()
logger_two.start_logging()
query.select()
logger_one.stop_logging()
query.select()
self.assertEqual(logger_one.count(), 3, 'Incorrect number of queries')
self.assertEqual(logger_two.count(), 2, 'Incorrect number of queries')
query.select()
logger_one.start_logging()
query.select()
self.assertEqual(logger_one.count(), 4, 'Incorrect number of queries')
self.assertEqual(logger_two.count(), 4, 'Incorrect number of queries')
query.select()
logger_two.clear_log()
query.select()
self.assertEqual(logger_one.count(), 6, 'Incorrect number of queries')
self.assertEqual(logger_two.count(), 1, 'Incorrect number of queries')
def test_logger(self):
logger_one = Logger('one')
logger_two = Logger('two')
logger_one.start_logging()
query = Query().from_table(Account)
query.select()
self.assertEqual(logger_one.count(), 1, 'Incorrect number of queries')
query.select()
logger_two.start_logging()
query.select()
logger_one.stop_logging()
query.select()
self.assertEqual(logger_one.count(), 3, 'Incorrect number of queries')
self.assertEqual(logger_two.count(), 2, 'Incorrect number of queries')
query.select()
logger_one.start_logging()
query.select()
self.assertEqual(logger_one.count(), 4, 'Incorrect number of queries')
self.assertEqual(logger_two.count(), 4, 'Incorrect number of queries')
query.select()
logger_two.clear_log()
query.select()
self.assertEqual(logger_one.count(), 6, 'Incorrect number of queries')
self.assertEqual(logger_two.count(), 1, 'Incorrect number of queries')
def test_clear_log(self):
"""
Makes sure queries are cleared
"""
logger_one = Logger('one')
logger_one.start_logging()
query = Query().from_table(Account)
# run a query and update the logger's query list
query.select()
logger_one.update_log()
# there should be one query
self.assertEqual(logger_one.count(), 1)
# increment the connection query count
query.select()
# clear the log
logger_one.clear_log()
# make sure no queries
self.assertEqual(0, len(logger_one.queries))
def test_clear_log(self):
"""
Makes sure queries are cleared
"""
logger_one = Logger('one')
logger_one.start_logging()
query = Query().from_table(Account)
# run a query and update the logger's query list
query.select()
logger_one.update_log()
# there should be one query
self.assertEqual(logger_one.count(), 1)
# increment the connection query count
query.select()
# clear the log
logger_one.clear_log()
# make sure no queries
self.assertEqual(0, len(logger_one.queries))
class InsertTest(QueryTestCase):
def setUp(self):
self.logger = Logger()
self.logger.start_logging()
def tearDown(self):
super(InsertTest, self).tearDown()
LogManager.loggers = {}
def test_insert_single_row(self):
G(User, id=1)
query = Query().from_table(
table=Account,
fields=[
'user_id',
'first_name',
'last_name'
]
)
rows = [
[1, 'Test', 'User']
]
sql, sql_params = query.get_insert_sql(rows)
self.assertEqual(
sql,
(
'INSERT INTO tests_account (user_id, first_name, last_name) VALUES (%s, %s, %s)'
)
)
self.assertEqual(sql_params[0], 1)
self.assertEqual(sql_params[1], 'Test')
self.assertEqual(sql_params[2], 'User')
query.insert(rows)
sql = self.logger.get_log()[2]['sql']
self.assertEqual(sql, "INSERT INTO tests_account (user_id, first_name, last_name) VALUES (1, 'Test', 'User')")
def test_insert_multiple_rows(self):
G(User, id=1)
G(User, id=2)
query = Query().from_table(
table=Account,
fields=[
'user_id',
'first_name',
'last_name'
]
)
rows = [
[1, 'Test', 'User'],
[2, 'Test2', 'User2'],
]
sql, sql_params = query.get_insert_sql(rows)
self.assertEqual(
sql,
'INSERT INTO tests_account (user_id, first_name, last_name) VALUES (%s, %s, %s), (%s, %s, %s)'
)
self.assertEqual(sql_params[0], 1)
self.assertEqual(sql_params[1], 'Test')
self.assertEqual(sql_params[2], 'User')
self.assertEqual(sql_params[3], 2)
self.assertEqual(sql_params[4], 'Test2')
self.assertEqual(sql_params[5], 'User2')
query.insert(rows)
sql = self.logger.get_log()[4]['sql']
self.assertEqual(
sql,
("INSERT INTO tests_account (user_id, first_name, last_name) "
"VALUES (1, 'Test', 'User'), (2, 'Test2', 'User2')")
)
class TestUpdate(QueryTestCase):
def setUp(self):
self.logger = Logger()
self.logger.start_logging()
def test_upsert_json_field(self):
"""
Only runs for django 1.9 because the jsonfield project uses an incorrect db prep value
"""
if VERSION[0] != 1 or VERSION[1] < 9:
return
items = [
Uniques(field1='1.1',
field2='1.2',
field3='1.3',
field6='1.6',
field7='1.7',
field8={'one': 'two'}),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5', 'field8'])
def test_upsert(self):
"""
Verifies that records get upserted correctly. Skipping this test now until travis-ci supports 9.5 addon.
"""
items = [
Uniques(field1='1.1',
field2='1.2',
field3='1.3',
field6='1.6',
field7='1.7'),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5'])
model = Uniques.objects.get()
self.assertEqual(model.field1, '1.1')
self.assertEqual(model.field2, '1.2')
self.assertEqual(model.field3, '1.3')
self.assertEqual(model.field4, 'default_value')
self.assertEqual(model.field5, None)
self.assertEqual(model.field6, '1.6')
self.assertEqual(model.field7, '1.7')
self.assertEqual(model.field8, {})
items = [
Uniques(field1='1.1',
field2='1.2 edited',
field3='1.3 edited',
field4='not default',
field5='new value',
field6='1.6',
field7='1.7'),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5'])
# Only fields 3, 4, 5 should be updated
model = Uniques.objects.get()
self.assertEqual(model.field1, '1.1')
self.assertEqual(model.field2, '1.2')
self.assertEqual(model.field3, '1.3 edited')
self.assertEqual(model.field4, 'not default')
self.assertEqual(model.field5, 'new value')
self.assertEqual(model.field6, '1.6')
self.assertEqual(model.field7, '1.7')
# Include a new record and an existing record
items = [
Uniques(field1='1.1',
field2='1.2',
field3='1.3',
field6='1.6',
field7='1.7'),
Uniques(field1='2.1',
field2='2.2',
field3='2.3',
field4='not default',
field5='not null',
field6='2.6',
field7='2.7'),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5'])
# Both records should exist and have different data
models = list(Uniques.objects.order_by('id'))
self.assertEqual(models[0].field1, '1.1')
self.assertEqual(models[0].field2, '1.2')
self.assertEqual(models[0].field3, '1.3')
self.assertEqual(models[0].field4, 'default_value')
self.assertEqual(models[0].field5, None)
self.assertEqual(models[0].field6, '1.6')
self.assertEqual(models[0].field7, '1.7')
self.assertEqual(models[1].field1, '2.1')
self.assertEqual(models[1].field2, '2.2')
self.assertEqual(models[1].field3, '2.3')
self.assertEqual(models[1].field4, 'not default')
self.assertEqual(models[1].field5, 'not null')
self.assertEqual(models[1].field6, '2.6')
self.assertEqual(models[1].field7, '2.7')
class TestUpdate(QueryTestCase):
def setUp(self):
self.logger = Logger()
self.logger.start_logging()
def test_update_single_row(self):
query = Query().from_table(
table=Account,
fields=[
'id',
'user_id',
'first_name',
'last_name'
]
)
rows = [
[1, 1, 'Test\'s', '"User"']
]
sql, sql_params = query.get_update_sql(rows)
self.assertEqual(
sql,
(
'UPDATE querybuilder_tests_account '
'SET user_id = new_values.user_id, '
'first_name = new_values.first_name, '
'last_name = new_values.last_name '
'FROM (VALUES (%s, %s::integer, %s::varchar(64), %s::varchar(64))) '
'AS new_values (id, user_id, first_name, last_name) '
'WHERE querybuilder_tests_account.id = new_values.id'
)
)
self.assertEqual(sql_params[0], 1)
self.assertEqual(sql_params[1], 1)
self.assertEqual(sql_params[2], 'Test\'s')
self.assertEqual(sql_params[3], '"User"')
query.update(rows)
sql = self.logger.get_log()[0]['sql']
self.assertEqual(
sql,
(
"UPDATE querybuilder_tests_account "
"SET user_id = new_values.user_id, "
"first_name = new_values.first_name, "
"last_name = new_values.last_name "
"FROM (VALUES (1, 1::integer, 'Test''s'::varchar(64), '\"User\"'::varchar(64))) "
"AS new_values (id, user_id, first_name, last_name) "
"WHERE querybuilder_tests_account.id = new_values.id"
)
)
def test_update_json_field(self):
MetricRecord.objects.create(id=10, data={'default1': 'd1'})
MetricRecord.objects.create(id=11, data={'default2': 'd2'})
query = Query().from_table(
table=MetricRecord,
fields=[
'id',
'data',
]
)
# Manually prep the values for db query
rows = [
[10, json.dumps({'first': '111'})],
[11, json.dumps({'second': '222'})],
]
query.update(rows)
records = list(MetricRecord.objects.order_by('id'))
self.assertEqual(records[0].data, {'first': '111'})
self.assertEqual(records[1].data, {'second': '222'})
def test_update_multiple_rows(self):
query = Query().from_table(
table=Account,
fields=[
'id',
'user_id',
'first_name',
'last_name'
]
)
rows = [
[1, 1, 'Test', 'User'],
[2, 2, 'Test2', 'User2']
]
sql, sql_params = query.get_update_sql(rows)
self.assertEqual(
sql,
(
'UPDATE querybuilder_tests_account '
'SET user_id = new_values.user_id, '
'first_name = new_values.first_name, '
'last_name = new_values.last_name '
'FROM (VALUES (%s, %s::integer, %s::varchar(64), %s::varchar(64)), (%s, %s, %s, %s)) '
'AS new_values (id, user_id, first_name, last_name) '
'WHERE querybuilder_tests_account.id = new_values.id'
)
)
self.assertEqual(sql_params[0], 1)
self.assertEqual(sql_params[1], 1)
self.assertEqual(sql_params[2], 'Test')
self.assertEqual(sql_params[3], 'User')
self.assertEqual(sql_params[4], 2)
self.assertEqual(sql_params[5], 2)
self.assertEqual(sql_params[6], 'Test2')
self.assertEqual(sql_params[7], 'User2')
query.update(rows)
sql = self.logger.get_log()[0]['sql']
self.assertEqual(
sql,
(
"UPDATE querybuilder_tests_account "
"SET user_id = new_values.user_id, "
"first_name = new_values.first_name, "
"last_name = new_values.last_name "
"FROM (VALUES (1, 1::integer, 'Test'::varchar(64), 'User'::varchar(64)), "
"(2, 2, 'Test2', 'User2')) "
"AS new_values (id, user_id, first_name, last_name) "
"WHERE querybuilder_tests_account.id = new_values.id"
)
)
def test_update_null_numbers(self):
"""
Verifies that null values can be bulk updated
"""
order1 = G(Order, revenue=10)
order2 = G(Order, revenue=20)
order3 = G(Order, revenue=30)
query = Query().from_table(table=Order, fields=['id', 'revenue'])
logger = Logger()
logger.start_logging()
rows = [[order1.id, None], [order2.id, None], [order3.id, 3000]]
query.update(rows)
orders = list(Order.objects.order_by('id'))
self.assertIsNone(orders[0].revenue)
self.assertIsNone(orders[1].revenue)
self.assertEqual(3000, orders[2].revenue)
def test_update_all_nulls(self):
"""
Verifies that the sql is modified when all values for a field are null. For whatever reason,
postgres doesn't handle this the same when all values are null
"""
order1 = G(Order, revenue=10, margin=5)
order2 = G(Order, revenue=20, margin=10)
order3 = G(Order, revenue=30, margin=15)
query = Query().from_table(table=Order, fields=['id', 'revenue', 'margin'])
logger = Logger()
logger.start_logging()
rows = [[order1.id, None, 50], [order2.id, None, 100], [order3.id, None, 150]]
query.update(rows)
orders = list(Order.objects.order_by('id'))
self.assertIsNone(orders[0].revenue)
self.assertIsNone(orders[1].revenue)
self.assertIsNone(orders[2].revenue)
class TestUpsert(QueryTestCase):
def setUp(self):
self.logger = Logger()
self.logger.start_logging()
def test_upsert_json_field(self):
"""
Only runs for django 1.9 because the jsonfield project uses an incorrect db prep value
"""
if VERSION[0] != 1 or VERSION[1] < 9:
return
items = [
Uniques(field1='1.1',
field2='1.2',
field3='1.3',
field6='1.6',
field7='1.7',
field8={'one': 'two'}),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5', 'field8'])
def test_upsert(self):
"""
Verifies that records get upserted correctly. Skipping this test now until travis-ci supports 9.5 addon.
"""
items = [
Uniques(field1='1.1',
field2='1.2',
field3='1.3',
field6='1.6',
field7='1.7'),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5'])
model = Uniques.objects.get()
self.assertEqual(model.field1, '1.1')
self.assertEqual(model.field2, '1.2')
self.assertEqual(model.field3, '1.3')
self.assertEqual(model.field4, 'default_value')
self.assertEqual(model.field5, None)
self.assertEqual(model.field6, '1.6')
self.assertEqual(model.field7, '1.7')
self.assertEqual(model.field8, {})
items = [
Uniques(field1='1.1',
field2='1.2 edited',
field3='1.3 edited',
field4='not default',
field5='new value',
field6='1.6',
field7='1.7'),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5'])
# Only fields 3, 4, 5 should be updated
model = Uniques.objects.get()
self.assertEqual(model.field1, '1.1')
self.assertEqual(model.field2, '1.2')
self.assertEqual(model.field3, '1.3 edited')
self.assertEqual(model.field4, 'not default')
self.assertEqual(model.field5, 'new value')
self.assertEqual(model.field6, '1.6')
self.assertEqual(model.field7, '1.7')
# Include a new record and an existing record
items = [
Uniques(field1='1.1',
field2='1.2',
field3='1.3',
field6='1.6',
field7='1.7'),
Uniques(field1='2.1',
field2='2.2',
field3='2.3',
field4='not default',
field5='not null',
field6='2.6',
field7='2.7'),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5'])
# Both records should exist and have different data
models = list(Uniques.objects.order_by('id'))
self.assertEqual(models[0].field1, '1.1')
self.assertEqual(models[0].field2, '1.2')
self.assertEqual(models[0].field3, '1.3')
self.assertEqual(models[0].field4, 'default_value')
self.assertEqual(models[0].field5, None)
self.assertEqual(models[0].field6, '1.6')
self.assertEqual(models[0].field7, '1.7')
self.assertEqual(models[1].field1, '2.1')
self.assertEqual(models[1].field2, '2.2')
self.assertEqual(models[1].field3, '2.3')
self.assertEqual(models[1].field4, 'not default')
self.assertEqual(models[1].field5, 'not null')
self.assertEqual(models[1].field6, '2.6')
self.assertEqual(models[1].field7, '2.7')
def test_upsert_pk(self):
"""
Makes sure upserting is possible when the only uniqueness constraint is the pk.
"""
user1 = G(User, email='user1')
user1.email = 'user1change'
user2 = User(email='user2')
user3 = User(email='user3')
self.assertEqual(User.objects.count(), 1)
Query().from_table(User).upsert(
[user1, user2, user3],
unique_fields=['id'],
update_fields=['email'],
)
self.assertEqual(User.objects.count(), 3)
users = list(User.objects.order_by('id'))
self.assertEqual(users[0].email, 'user1change')
self.assertEqual(users[1].email, 'user2')
self.assertEqual(users[2].email, 'user3')
def test_upsert_pk_return_dicts(self):
"""
Makes sure upserting is possible when the only uniqueness constraint is the pk. Should return dicts.
"""
user1 = G(User, email='user1')
user1.email = 'user1change'
user2 = User(email='user2')
user3 = User(email='user3')
self.assertEqual(User.objects.count(), 1)
rows = Query().from_table(User).upsert(
[user1, user2, user3],
unique_fields=['id'],
update_fields=['email'],
return_rows=True,
)
self.assertEqual(User.objects.count(), 3)
self.assertEqual(len(rows), 3)
# Check ids
for row in rows:
self.assertIsNotNone(row['id'])
# Check emails
email_set = {row['email'] for row in rows}
self.assertEqual(email_set, {'user1change', 'user2', 'user3'})
# Check fields from db
users = list(User.objects.order_by('id'))
self.assertEqual(users[0].email, 'user1change')
self.assertEqual(users[1].email, 'user2')
self.assertEqual(users[2].email, 'user3')
def test_upsert_pk_return_models(self):
"""
Makes sure upserting is possible when the only uniqueness constraint is the pk. Should return models.
"""
user1 = G(User, email='user1')
user1.email = 'user1change'
user2 = User(email='user2')
user3 = User(email='user3')
self.assertEqual(User.objects.count(), 1)
records = Query().from_table(User).upsert(
[user1, user2, user3],
unique_fields=['id'],
update_fields=['email'],
return_models=True,
)
self.assertEqual(len(records), 3)
# Check ids
for record in records:
self.assertIsNotNone(record.id)
# Check emails
email_set = {record.email for record in records}
self.assertEqual(email_set, {'user1change', 'user2', 'user3'})
# Check fields from db
users = list(User.objects.order_by('id'))
self.assertEqual(users[0].email, 'user1change')
self.assertEqual(users[1].email, 'user2')
self.assertEqual(users[2].email, 'user3')
def test_upsert_custom_db_column(self):
"""
Makes sure upserting a model containing a field with a custom db_column name works.
"""
model = Uniques(field1='1', custom_field_name='test')
Query().from_table(Uniques).upsert([model],
unique_fields=['field1'],
update_fields=[])
saved_model = Uniques.objects.get()
self.assertEqual(saved_model.custom_field_name, 'test')
saved_model.custom_field_name = 'edited'
Query().from_table(Uniques).upsert([saved_model],
unique_fields=['field1'],
update_fields=['custom_field_name'])
updated_model = Uniques.objects.get()
self.assertEqual(updated_model.custom_field_name, 'edited')
rows = Query().from_table(Uniques).select()
self.assertEqual(rows[0]['actual_db_column_name'], 'edited')
class TestUpdate(QueryTestCase):
def setUp(self):
self.logger = Logger()
self.logger.start_logging()
def test_update_single_row(self):
query = Query().from_table(
table=Account,
fields=[
'id',
'user_id',
'first_name',
'last_name'
]
)
rows = [
[1, 1, 'Test\'s', '"User"']
]
sql, sql_params = query.get_update_sql(rows)
self.assertEqual(
sql,
(
'UPDATE tests_account '
'SET user_id = new_values.user_id, '
'first_name = new_values.first_name, '
'last_name = new_values.last_name '
'FROM (VALUES (%s, %s, %s, %s)) '
'AS new_values (id, user_id, first_name, last_name) '
'WHERE tests_account.id = new_values.id'
)
)
self.assertEqual(sql_params[0], 1)
self.assertEqual(sql_params[1], 1)
self.assertEqual(sql_params[2], 'Test\'s')
self.assertEqual(sql_params[3], '"User"')
query.update(rows)
sql = self.logger.get_log()[0]['sql']
self.assertEqual(
sql,
(
"UPDATE tests_account "
"SET user_id = new_values.user_id, "
"first_name = new_values.first_name, "
"last_name = new_values.last_name "
"FROM (VALUES (1, 1, 'Test''s', '\"User\"')) "
"AS new_values (id, user_id, first_name, last_name) "
"WHERE tests_account.id = new_values.id"
)
)
def test_update_multiple_rows(self):
query = Query().from_table(
table=Account,
fields=[
'id',
'user_id',
'first_name',
'last_name'
]
)
rows = [
[1, 1, 'Test', 'User'],
[2, 2, 'Test2', 'User2']
]
sql, sql_params = query.get_update_sql(rows)
self.assertEqual(
sql,
(
'UPDATE tests_account '
'SET user_id = new_values.user_id, '
'first_name = new_values.first_name, '
'last_name = new_values.last_name '
'FROM (VALUES (%s, %s, %s, %s), (%s, %s, %s, %s)) '
'AS new_values (id, user_id, first_name, last_name) '
'WHERE tests_account.id = new_values.id'
)
)
self.assertEqual(sql_params[0], 1)
self.assertEqual(sql_params[1], 1)
self.assertEqual(sql_params[2], 'Test')
self.assertEqual(sql_params[3], 'User')
self.assertEqual(sql_params[4], 2)
self.assertEqual(sql_params[5], 2)
self.assertEqual(sql_params[6], 'Test2')
self.assertEqual(sql_params[7], 'User2')
query.update(rows)
sql = self.logger.get_log()[0]['sql']
self.assertEqual(
sql,
(
"UPDATE tests_account "
"SET user_id = new_values.user_id, "
"first_name = new_values.first_name, "
"last_name = new_values.last_name "
"FROM (VALUES (1, 1, 'Test', 'User'), (2, 2, 'Test2', 'User2')) "
"AS new_values (id, user_id, first_name, last_name) "
"WHERE tests_account.id = new_values.id"
)
)
def test_update_null_numbers(self):
"""
Verifies that null values can be bulk updated
"""
order1 = G(Order, revenue=10)
order2 = G(Order, revenue=20)
order3 = G(Order, revenue=30)
query = Query().from_table(table=Order, fields=['id', 'revenue'])
logger = Logger()
logger.start_logging()
rows = [[order1.id, None], [order2.id, None], [order3.id, 3000]]
query.update(rows)
orders = list(Order.objects.order_by('id'))
self.assertIsNone(orders[0].revenue)
self.assertIsNone(orders[1].revenue)
self.assertEqual(3000, orders[2].revenue)
def test_update_all_nulls(self):
"""
Verifies that the sql is modified when all values for a field are null. For whatever reason,
postgres doesn't handle this the same when all values are null
"""
order1 = G(Order, revenue=10, margin=5)
order2 = G(Order, revenue=20, margin=10)
order3 = G(Order, revenue=30, margin=15)
query = Query().from_table(table=Order, fields=['id', 'revenue', 'margin'])
logger = Logger()
logger.start_logging()
rows = [[order1.id, None, 50], [order2.id, None, 100], [order3.id, None, 150]]
query.update(rows)
orders = list(Order.objects.order_by('id'))
self.assertIsNone(orders[0].revenue)
self.assertIsNone(orders[1].revenue)
self.assertIsNone(orders[2].revenue)
class TestUpsert(QueryTestCase):
def setUp(self):
self.logger = Logger()
self.logger.start_logging()
def test_upsert_json_field(self):
"""
Only runs for django 1.9 because the jsonfield project uses an incorrect db prep value
"""
if VERSION[0] != 1 or VERSION[1] < 9:
return
items = [
Uniques(field1='1.1', field2='1.2', field3='1.3', field6='1.6', field7='1.7', field8={
'one': 'two'
}),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5', 'field8']
)
def test_upsert(self):
"""
Verifies that records get upserted correctly. Skipping this test now until travis-ci supports 9.5 addon.
"""
items = [
Uniques(field1='1.1', field2='1.2', field3='1.3', field6='1.6', field7='1.7'),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5']
)
model = Uniques.objects.get()
self.assertEqual(model.field1, '1.1')
self.assertEqual(model.field2, '1.2')
self.assertEqual(model.field3, '1.3')
self.assertEqual(model.field4, 'default_value')
self.assertEqual(model.field5, None)
self.assertEqual(model.field6, '1.6')
self.assertEqual(model.field7, '1.7')
self.assertEqual(model.field8, {})
items = [
Uniques(
field1='1.1',
field2='1.2 edited',
field3='1.3 edited',
field4='not default',
field5='new value',
field6='1.6',
field7='1.7'
),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5']
)
# Only fields 3, 4, 5 should be updated
model = Uniques.objects.get()
self.assertEqual(model.field1, '1.1')
self.assertEqual(model.field2, '1.2')
self.assertEqual(model.field3, '1.3 edited')
self.assertEqual(model.field4, 'not default')
self.assertEqual(model.field5, 'new value')
self.assertEqual(model.field6, '1.6')
self.assertEqual(model.field7, '1.7')
# Include a new record and an existing record
items = [
Uniques(field1='1.1', field2='1.2', field3='1.3', field6='1.6', field7='1.7'),
Uniques(
field1='2.1',
field2='2.2',
field3='2.3',
field4='not default',
field5='not null',
field6='2.6',
field7='2.7'
),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5']
)
# Both records should exist and have different data
models = list(Uniques.objects.order_by('id'))
self.assertEqual(models[0].field1, '1.1')
self.assertEqual(models[0].field2, '1.2')
self.assertEqual(models[0].field3, '1.3')
self.assertEqual(models[0].field4, 'default_value')
self.assertEqual(models[0].field5, None)
self.assertEqual(models[0].field6, '1.6')
self.assertEqual(models[0].field7, '1.7')
self.assertEqual(models[1].field1, '2.1')
self.assertEqual(models[1].field2, '2.2')
self.assertEqual(models[1].field3, '2.3')
self.assertEqual(models[1].field4, 'not default')
self.assertEqual(models[1].field5, 'not null')
self.assertEqual(models[1].field6, '2.6')
self.assertEqual(models[1].field7, '2.7')
def test_upsert_pk(self):
"""
Makes sure upserting is possible when the only uniqueness constraint is the pk.
"""
user1 = G(User, email='user1')
user1.email = 'user1change'
user2 = User(email='user2')
user3 = User(email='user3')
self.assertEqual(User.objects.count(), 1)
Query().from_table(User).upsert(
[user1, user2, user3],
unique_fields=['id'],
update_fields=['email'],
)
self.assertEqual(User.objects.count(), 3)
users = list(User.objects.order_by('id'))
self.assertEqual(users[0].email, 'user1change')
self.assertEqual(users[1].email, 'user2')
self.assertEqual(users[2].email, 'user3')
def test_upsert_pk_return_dicts(self):
"""
Makes sure upserting is possible when the only uniqueness constraint is the pk. Should return dicts.
"""
user1 = G(User, email='user1')
user1.email = 'user1change'
user2 = User(email='user2')
user3 = User(email='user3')
self.assertEqual(User.objects.count(), 1)
rows = Query().from_table(User).upsert(
[user1, user2, user3],
unique_fields=['id'],
update_fields=['email'],
return_rows=True,
)
self.assertEqual(User.objects.count(), 3)
self.assertEqual(len(rows), 3)
# Check ids
for row in rows:
self.assertIsNotNone(row['id'])
# Check emails
email_set = {
row['email'] for row in rows
}
self.assertEqual(email_set, {'user1change', 'user2', 'user3'})
# Check fields from db
users = list(User.objects.order_by('id'))
self.assertEqual(users[0].email, 'user1change')
self.assertEqual(users[1].email, 'user2')
self.assertEqual(users[2].email, 'user3')
def test_upsert_pk_return_models(self):
"""
Makes sure upserting is possible when the only uniqueness constraint is the pk. Should return models.
"""
user1 = G(User, email='user1')
user1.email = 'user1change'
user2 = User(email='user2')
user3 = User(email='user3')
self.assertEqual(User.objects.count(), 1)
records = Query().from_table(User).upsert(
[user1, user2, user3],
unique_fields=['id'],
update_fields=['email'],
return_models=True,
)
self.assertEqual(len(records), 3)
# Check ids
for record in records:
self.assertIsNotNone(record.id)
# Check emails
email_set = {
record.email for record in records
}
self.assertEqual(email_set, {'user1change', 'user2', 'user3'})
# Check fields from db
users = list(User.objects.order_by('id'))
self.assertEqual(users[0].email, 'user1change')
self.assertEqual(users[1].email, 'user2')
self.assertEqual(users[2].email, 'user3')
def test_upsert_custom_db_column(self):
"""
Makes sure upserting a model containing a field with a custom db_column name works.
"""
model = Uniques(field1='1', custom_field_name='test')
Query().from_table(Uniques).upsert(
[model],
unique_fields=['field1'],
update_fields=[]
)
saved_model = Uniques.objects.get()
self.assertEqual(saved_model.custom_field_name, 'test')
saved_model.custom_field_name = 'edited'
Query().from_table(Uniques).upsert(
[saved_model],
unique_fields=['field1'],
update_fields=['custom_field_name']
)
updated_model = Uniques.objects.get()
self.assertEqual(updated_model.custom_field_name, 'edited')
rows = Query().from_table(Uniques).select()
self.assertEqual(rows[0]['actual_db_column_name'], 'edited')
class TestUpdate(QueryTestCase):
def setUp(self):
self.logger = Logger()
self.logger.start_logging()
def test_upsert_json_field(self):
"""
Only runs for django 1.9 because the jsonfield project uses an incorrect db prep value
"""
if VERSION[0] != 1 or VERSION[1] < 9:
return
items = [
Uniques(field1='1.1', field2='1.2', field3='1.3', field6='1.6', field7='1.7', field8={
'one': 'two'
}),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5', 'field8']
)
def test_upsert(self):
"""
Verifies that records get upserted correctly. Skipping this test now until travis-ci supports 9.5 addon.
"""
items = [
Uniques(field1='1.1', field2='1.2', field3='1.3', field6='1.6', field7='1.7'),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5']
)
model = Uniques.objects.get()
self.assertEqual(model.field1, '1.1')
self.assertEqual(model.field2, '1.2')
self.assertEqual(model.field3, '1.3')
self.assertEqual(model.field4, 'default_value')
self.assertEqual(model.field5, None)
self.assertEqual(model.field6, '1.6')
self.assertEqual(model.field7, '1.7')
self.assertEqual(model.field8, {})
items = [
Uniques(
field1='1.1',
field2='1.2 edited',
field3='1.3 edited',
field4='not default',
field5='new value',
field6='1.6',
field7='1.7'
),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5']
)
# Only fields 3, 4, 5 should be updated
model = Uniques.objects.get()
self.assertEqual(model.field1, '1.1')
self.assertEqual(model.field2, '1.2')
self.assertEqual(model.field3, '1.3 edited')
self.assertEqual(model.field4, 'not default')
self.assertEqual(model.field5, 'new value')
self.assertEqual(model.field6, '1.6')
self.assertEqual(model.field7, '1.7')
# Include a new record and an existing record
items = [
Uniques(field1='1.1', field2='1.2', field3='1.3', field6='1.6', field7='1.7'),
Uniques(
field1='2.1',
field2='2.2',
field3='2.3',
field4='not default',
field5='not null',
field6='2.6',
field7='2.7'
),
]
Query().from_table(Uniques).upsert(
items,
unique_fields=['field1'],
update_fields=['field3', 'field4', 'field5']
)
# Both records should exist and have different data
models = list(Uniques.objects.order_by('id'))
self.assertEqual(models[0].field1, '1.1')
self.assertEqual(models[0].field2, '1.2')
self.assertEqual(models[0].field3, '1.3')
self.assertEqual(models[0].field4, 'default_value')
self.assertEqual(models[0].field5, None)
self.assertEqual(models[0].field6, '1.6')
self.assertEqual(models[0].field7, '1.7')
self.assertEqual(models[1].field1, '2.1')
self.assertEqual(models[1].field2, '2.2')
self.assertEqual(models[1].field3, '2.3')
self.assertEqual(models[1].field4, 'not default')
self.assertEqual(models[1].field5, 'not null')
self.assertEqual(models[1].field6, '2.6')
self.assertEqual(models[1].field7, '2.7')
class InsertTest(QueryTestCase):
def setUp(self):
self.logger = Logger()
self.logger.start_logging()
def tearDown(self):
super(InsertTest, self).tearDown()
LogManager.loggers = {}
def test_insert_single_row(self):
G(User, id=1)
query = Query().from_table(
table=Account, fields=['user_id', 'first_name', 'last_name'])
rows = [[1, 'Test', 'User']]
sql, sql_params = query.get_insert_sql(rows)
self.assertEqual(sql, (
'INSERT INTO querybuilder_tests_account (user_id, first_name, last_name) VALUES (%s, %s, %s)'
))
self.assertEqual(sql_params[0], 1)
self.assertEqual(sql_params[1], 'Test')
self.assertEqual(sql_params[2], 'User')
query.insert(rows)
sql = self.logger.get_log()[2]['sql']
self.assertEqual(
sql,
"INSERT INTO querybuilder_tests_account (user_id, first_name, last_name) VALUES (1, 'Test', 'User')"
)
def test_insert_multiple_rows(self):
G(User, id=1)
G(User, id=2)
query = Query().from_table(
table=Account, fields=['user_id', 'first_name', 'last_name'])
rows = [
[1, 'Test', 'User'],
[2, 'Test2', 'User2'],
]
sql, sql_params = query.get_insert_sql(rows)
self.assertEqual(
sql,
'INSERT INTO querybuilder_tests_account (user_id, first_name, last_name) VALUES (%s, %s, %s), (%s, %s, %s)'
)
self.assertEqual(sql_params[0], 1)
self.assertEqual(sql_params[1], 'Test')
self.assertEqual(sql_params[2], 'User')
self.assertEqual(sql_params[3], 2)
self.assertEqual(sql_params[4], 'Test2')
self.assertEqual(sql_params[5], 'User2')
query.insert(rows)
sql = self.logger.get_log()[4]['sql']
self.assertEqual(sql, (
"INSERT INTO querybuilder_tests_account (user_id, first_name, last_name) "
"VALUES (1, 'Test', 'User'), (2, 'Test2', 'User2')"))