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')"))
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 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_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)