class TestMySQLTableSelectGroupBy(TestCase):
"""
https://dev.mysql.com/doc/refman/5.5/en/group-by-modifiers.html
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/select_group_by')
def tearDown(self):
self.m.ddl(self.f.drop_schema('select_group_by'))
self.m = None
self.f = None
def test_01_single_column(self):
inputs = 'single_column'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
self.assertEqual(long(expected_rows[0]), actual_rows[0][0])
self.assertEqual(Decimal(expected_rows[1]), actual_rows[0][1])
def test_02_multiple_columns(self):
inputs = 'multiple_columns'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(long(expected_rows[i * 3 + 0]),
actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 3 + 1], actual_rows[i][0][1])
self.assertEqual(Decimal(expected_rows[i * 3 + 2]),
actual_rows[i][0][2])
def test_03_roll_up(self):
inputs = 'roll_up'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
if expected_rows[i * 3 + 0] == 'None':
self.assertEqual(None, actual_rows[i][0][0])
else:
self.assertEqual(long(expected_rows[i * 3 + 0]),
actual_rows[i][0][0])
if expected_rows[i * 3 + 1] == 'None':
self.assertEqual(None, actual_rows[i][0][1])
else:
self.assertEqual(expected_rows[i * 3 + 1],
actual_rows[i][0][1])
self.assertEqual(Decimal(expected_rows[i * 3 + 2]),
actual_rows[i][0][2])
class TestMySQLTableConstraint(TestCase):
"""
https://dev.mysql.com/doc/refman/5.7/en/constraints.html
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/constraint')
def tearDown(self):
for stmt in self.f.drop_schema('constraint'):
self.m.ddl(stmt)
self.m = None
self.f = None
def test_01_valid_data(self):
inputs = 'valid_data'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
if expected_rows[i * 2 + 0] == 'None':
self.assertEqual('', actual_rows[i][0][0])
else:
self.assertEqual(expected_rows[i * 2 + 0],
actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 2 + 1], actual_rows[i][0][1])
def test_02_invalid_data(self):
inputs = 'invalid_data'
self.m.ddl(self.f.load_schema(inputs))
with self.assertRaises(OperationalError) as context:
self.m.dml(self.f.populate(inputs))
self.assertIn('doesn\'t have a default value', str(context.exception))
def test_03_valid_fk(self):
inputs = 'valid_fk'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
self.assertEqual(expected_rows[0], actual_rows[0][0])
self.assertEqual(long(expected_rows[1]), actual_rows[0][1])
self.assertEqual(Decimal(expected_rows[2]), actual_rows[0][2])
def test_04_invalid_fk(self):
inputs = 'invalid_fk'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
with self.assertRaises(OperationalError) as context:
self.m.dml(self.f.populate(inputs))
self.assertIn('cannot be null', str(context.exception))
class TestMySQLTableSelectOrderBy(TestCase):
"""
https://www.w3schools.com/sql/sql_orderby.asp
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/select_order_by')
def tearDown(self):
for stmt in self.f.drop_schema('select_order_by'):
self.m.ddl(stmt)
self.m = None
self.f = None
def test_01_reverse_order(self):
inputs = 'reverse_order'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = self.f.expected(inputs)
self.assertEqual(expected[0], actual[0][0][0])
self.assertEqual(expected[1], actual[1][0][0])
self.assertEqual(expected[2], actual[2][0][0])
self.assertEqual(expected[3], actual[3][0][0])
self.assertEqual(expected[4], actual[4][0][0])
def test_02_multiple_columns_asc(self):
inputs = 'multiple_columns_asc'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(long(expected_rows[i * 3 + 0]),
actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 3 + 1], actual_rows[i][0][1])
self.assertEqual(expected_rows[i * 3 + 2], actual_rows[i][0][2])
def test_03_multiple_columns_asc_desc(self):
inputs = 'multiple_columns_asc_desc'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(long(expected_rows[i * 3 + 0]),
actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 3 + 1], actual_rows[i][0][1])
self.assertEqual(expected_rows[i * 3 + 2], actual_rows[i][0][2])
class TestMySQLTableDelete(TestCase):
"""
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/delete')
def tearDown(self):
self.m.ddl(self.f.drop_schema('delete'))
self.m = None
self.f = None
def test_01_single_row(self):
inputs = 'single_row'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_02_multiple_rows(self):
inputs = 'multiple_rows'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_03_all_rows(self):
inputs = 'all_rows'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_04_truncate(self):
inputs = 'truncate'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
class TestMySQLTableUpdate(TestCase):
"""
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/update')
def tearDown(self):
self.m.ddl(self.f.drop_schema('update'))
self.m = None
self.f = None
def test_01_single_row(self):
inputs = 'single_row'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_02_multiple_rows(self):
inputs = 'multiple_rows'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(long(expected_rows[i]), actual_rows[i][0][0])
def test_03_all_rows(self):
inputs = 'all_rows'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(expected_rows[i], actual_rows[i][0][0])
class TestMySQLRelationshipModel(TestCase):
"""
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/relationship')
def tearDown(self):
for stmt in self.f.drop_schema('datatype'):
self.m.ddl(stmt)
self.m = None
self.f = None
def test_01_foreign_key(self):
inputs = 'foreign_key'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(expected_rows[i * 4 + 0], actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 4 + 1], actual_rows[i][0][1])
self.assertEqual(expected_rows[i * 4 + 2], actual_rows[i][0][2])
self.assertEqual(expected_rows[i * 4 + 3], actual_rows[i][0][3])
def test_02_many_to_many(self):
inputs = 'many_to_many'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(expected_rows[i * 3 + 0], actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 3 + 1], actual_rows[i][0][1])
self.assertEqual(expected_rows[i * 3 + 2], actual_rows[i][0][2])
def test_03_one_to_many(self):
inputs = 'one_to_many'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(expected_rows[i * 2 + 0], actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 2 + 1], actual_rows[i][0][1])
def test_04_one_to_one(self):
inputs = 'one_to_one'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(expected_rows[i * 3 + 0], actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 3 + 1], actual_rows[i][0][1])
self.assertEqual(long(expected_rows[i * 3 + 2]),
actual_rows[i][0][2])
class TestMySQLTableSelect(TestCase):
"""
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/select')
self.today = date.today()
self.times = []
self.pdt_in_secs = 7 * 60 * 60
def tearDown(self):
for stmt in self.f.drop_schema('select'):
self.m.ddl(stmt)
self.m = None
self.f = None
self.today = None
self.times = None
def test_01_all_columns(self):
inputs = 'all_columns'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
t = datetime.now()
self.times.append(timedelta(hours=t.hour) +\
timedelta(minutes=t.minute) +\
timedelta(seconds=t.second) +\
timedelta(seconds=self.pdt_in_secs))
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(long(expected_rows[i * 6 + 0]),
actual_rows[i][0][0])
self.assertEqual(long(expected_rows[i * 6 + 1]),
actual_rows[i][0][1])
self.assertEqual(long(expected_rows[i * 6 + 2]),
actual_rows[i][0][2])
self.assertEqual(self.today, actual_rows[i][0][3])
self.assertAlmostEqual(self.times[i].seconds,
actual_rows[i][0][4].seconds,
delta=3)
self.assertEqual(Decimal(expected_rows[i * 6 + 5]),
actual_rows[i][0][5])
def test_02_single_column(self):
inputs = 'single_column'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_row = self.m.dql(self.f.test(inputs))
expected_row = self.f.expected(inputs)
self.assertEqual(Decimal(expected_row), actual_row[0][0][0])
def test_03_multiple_columns(self):
inputs = 'multiple_columns'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
self.assertEqual(Decimal(expected_rows[0]), actual_rows[0][0][0])
self.assertEqual(Decimal(expected_rows[1]), actual_rows[0][0][1])
def test_04_where_column_equals_value(self):
inputs = 'where_column_equals_value'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_row = self.m.dql(self.f.test(inputs))
expected_row = self.f.expected(inputs)
self.assertEqual(Decimal(expected_row), actual_row[0][0])
def test_05_where_column_in(self):
inputs = 'where_column_in'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_row = self.m.dql(self.f.test(inputs))
expected_row = self.f.expected(inputs)
self.assertEqual(expected_row, actual_row[0][0])
def test_06_where_column_between(self):
inputs = 'where_column_between'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_row = self.m.dql(self.f.test(inputs))
expected_row = self.f.expected(inputs)
self.assertEqual(Decimal(expected_row), actual_row[0][0])
def test_07_where_column_unequal_to_value(self):
inputs = 'where_column_unequal_to_value'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_row = self.m.dql(self.f.test(inputs))
expected_row = self.f.expected(inputs)
self.assertEqual(Decimal(expected_row), actual_row[0][0])
def test_08_where_column_not_value(self):
inputs = 'where_column_not_value'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_row = self.m.dql(self.f.test(inputs))
expected_row = self.f.expected(inputs)
self.assertEqual(Decimal(expected_row), actual_row[0][0])
def test_09_multiple_columns_multiple_tables(self):
inputs = 'multiple_columns_multiple_tables'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
self.assertEqual(expected_rows[0], actual_rows[0][0])
self.assertEqual(expected_rows[1], actual_rows[0][1])
self.assertEqual(expected_rows[2], actual_rows[0][2])
self.assertEqual(Decimal(expected_rows[3]), actual_rows[0][3])
class TestMySQLFunction(TestCase):
"""
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/function')
self.pst_in_secs = 7 * 60 * 60
def tearDown(self):
self.m.ddl(self.f.drop_schema('datatype'))
self.m = None
self.f = None
self.pst_in_secs = 0
def test_01_sum(self):
inputs = 'sum'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = Decimal(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_02_min(self):
inputs = 'min'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = Decimal(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_03_max(self):
inputs = 'max'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = Decimal(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_04_ceil(self):
inputs = 'ceil'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_05_floor(self):
inputs = 'floor'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_06_date(self):
inputs = 'date'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
t = datetime.now()
if t.hour > 16:
t = t + timedelta(hours=8)
actual = self.m.dql(self.f.test(inputs))
expected = date(t.year, t.month, t.day)
self.assertEqual(expected, actual[0][0])
def test_07_time(self):
inputs = 'time'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
t = datetime.now()
expected = timedelta(hours=t.hour) +\
timedelta(minutes=t.minute) +\
timedelta(seconds=t.second) +\
timedelta(seconds=self.pst_in_secs)
self.assertAlmostEqual(expected.seconds, actual[0][0].seconds, delta=3)
def test_08_current_timestamp(self):
inputs = 'current_timestamp'
self.m.ddl(self.f.load_schema(inputs))
actual = self.m.dql(self.f.test(inputs))
t = datetime.now() + timedelta(seconds=self.pst_in_secs)
expected = datetime(t.year, t.month, t.day, t.hour, t.minute, t.second)
self.assertAlmostEqual(expected.second, actual[0][0].second, delta=3)
def test_09_count(self):
inputs = 'count'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_10_date_format(self):
inputs = 'date_format'
self.m.ddl(self.f.load_schema(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = datetime.now().strftime('%y %b %e')
self.assertEqual(expected, actual[0][0])
def test_11_if(self):
inputs = 'if'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = self.f.expected(inputs)
self.assertEqual(expected, actual[0][0])
def test_12_in(self):
inputs = 'in'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_13_is_null(self):
inputs = 'is_null'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_14_is_not_null(self):
inputs = 'is_not_null'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
self.assertEqual(long(expected_rows[0]), actual_rows[0][0][0])
self.assertEqual(long(expected_rows[1]), actual_rows[1][0][0])
def test_15_like(self):
inputs = 'like'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = self.f.expected(inputs)
self.assertEqual(expected, actual[0][0])
def test_16_locate(self):
inputs = 'locate'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_17_md5(self):
inputs = 'md5'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = self.f.expected(inputs)
self.assertEqual(expected, actual[0][0])
def test_18_mod(self):
inputs = 'mod'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_19_now(self):
inputs = 'now'
self.m.ddl(self.f.load_schema(inputs))
actual = self.m.dql(self.f.test(inputs))
t = datetime.now() + timedelta(seconds=self.pst_in_secs)
expected = datetime(t.year, t.month, t.day, t.hour, t.minute, t.second)
self.assertAlmostEqual(expected.second, actual[0][0].second, delta=3)
def test_20_regexp(self):
inputs = 'regex'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
self.assertEqual(expected_rows[0], actual_rows[0][0][0])
self.assertEqual(expected_rows[1], actual_rows[1][0][0])
self.assertEqual(expected_rows[2], actual_rows[2][0][0])
def test_21_round(self):
inputs = 'round'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = Decimal(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_22_rowcount(self):
inputs = 'rowcount'
self.m.ddl(self.f.load_schema(inputs))
self.m.ddl(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_23_substr(self):
inputs = 'substr'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = self.f.expected(inputs)
self.assertEqual(expected, actual[0][0])
def test_24_sysdate(self):
inputs = 'sysdate'
self.m.ddl(self.f.load_schema(inputs))
actual = self.m.dql(self.f.test(inputs))
t = datetime.now() + timedelta(seconds=self.pst_in_secs)
expected = datetime(t.year, t.month, t.day, t.hour, t.minute, t.second)
self.assertAlmostEqual(expected.second, actual[0][0].second, delta=3)
def test_25_trim(self):
inputs = 'trim'
self.m.ddl(self.f.load_schema(inputs))
self.m.ddl(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = self.f.expected(inputs)
self.assertEqual(expected, actual[0][0])
def test_26_user(self):
inputs = 'user'
self.m.ddl(self.f.load_schema(inputs))
actual = self.m.dql(self.f.test(inputs))
# hack around returned hostname
expected = '{}@{}'.format(self.m.user, self.m.host[0:-2])
self.assertEqual(expected, actual[0][0])
def test_27_uuid(self):
inputs = 'uuid'
self.m.ddl(self.f.load_schema(inputs))
self.m.ddl(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertTrue(UUID(actual[0][0]))
def test_28_sleep(self):
inputs = 'sleep'
self.m.ddl(self.f.load_schema(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
class TestMySQLTableJoin(TestCase):
"""
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/join')
self.stmt = None
self.query = None
def tearDown(self):
for stmt in self.f.drop_schema('join'):
self.m.ddl(stmt)
self.m = None
self.f = None
def test_01_inner_join(self):
inputs = 'inner_join'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(expected_rows[i * 3 + 0], actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 3 + 1], actual_rows[i][0][1])
self.assertEqual(expected_rows[i * 3 + 2], actual_rows[i][0][2])
def test_02_left_join(self):
inputs = 'left_join'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
today = date.today()
tomorrow = date.today() + timedelta(days=1)
self.assertEqual(expected_rows[0], actual_rows[0][0][0])
self.assertEqual(long(expected_rows[1]), actual_rows[0][0][1])
self.assertEqual(today, actual_rows[0][0][2])
self.assertEqual(expected_rows[3], actual_rows[1][0][0])
self.assertEqual(long(expected_rows[4]), actual_rows[1][0][1])
self.assertEqual(tomorrow, actual_rows[1][0][2])
self.assertEqual(expected_rows[6], actual_rows[2][0][0])
self.assertEqual(None, actual_rows[2][0][1])
self.assertEqual(None, actual_rows[2][0][2])
def test_03_right_join(self):
inputs = 'right_join'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
today = date.today()
tomorrow = date.today() + timedelta(days=1)
self.assertEqual(expected_rows[0], actual_rows[0][0][0])
self.assertEqual(long(expected_rows[1]), actual_rows[0][0][1])
self.assertEqual(today, actual_rows[0][0][2])
self.assertEqual(expected_rows[3], actual_rows[1][0][0])
self.assertEqual(long(expected_rows[4]), actual_rows[1][0][1])
self.assertEqual(tomorrow, actual_rows[1][0][2])
self.assertEqual(expected_rows[6], actual_rows[2][0][0])
self.assertEqual(None, actual_rows[2][0][1])
self.assertEqual(None, actual_rows[2][0][2])
def test_04_full_outer_join(self):
# technically, this is a UNION of left join with right join
inputs = 'full_join'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
today = date.today()
tomorrow = date.today() + timedelta(days=1)
self.assertEqual(expected_rows[0], actual_rows[0][0][0])
self.assertEqual(long(expected_rows[1]), actual_rows[0][0][1])
self.assertEqual(today, actual_rows[0][0][2])
self.assertEqual(expected_rows[3], actual_rows[1][0][0])
self.assertEqual(long(expected_rows[4]), actual_rows[1][0][1])
self.assertEqual(tomorrow, actual_rows[1][0][2])
self.assertEqual(expected_rows[6], actual_rows[2][0][0])
self.assertEqual(None, actual_rows[2][0][1])
self.assertEqual(None, actual_rows[2][0][2])
def test_05_self_join(self):
inputs = 'self_join'
self.m.ddl(self.f.load_schema(inputs))
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual_rows = self.m.dql(self.f.test(inputs))
expected_rows = self.f.expected(inputs)
for i in range(len(actual_rows)):
self.assertEqual(expected_rows[i * 2 + 0], actual_rows[i][0][0])
self.assertEqual(expected_rows[i * 2 + 1], actual_rows[i][0][1])
class TestMySQLTableTrigger(TestCase):
"""
https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html
"""
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/trigger')
def tearDown(self):
self.m.ddl(self.f.drop_schema('trigger'))
self.m = None
self.f = None
def test_01_before_insert(self):
inputs = 'before_insert'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = Decimal(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_02_after_insert(self):
inputs = 'after_insert'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = Decimal(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_03_before_update(self):
inputs = 'before_update'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = Decimal(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_04_after_update(self):
inputs = 'after_update'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = Decimal(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_05_before_delete(self):
inputs = 'before_delete'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
def test_06_after_delete(self):
inputs = 'after_delete'
for stmt in self.f.load_schema(inputs):
self.m.ddl(stmt)
for stmt in self.f.populate(inputs):
self.m.dml(stmt)
actual = self.m.dql(self.f.test(inputs))
expected = long(self.f.expected(inputs))
self.assertEqual(expected, actual[0][0])
class TestMySQLDataType(TestCase):
def setUp(self):
self.m = MyMySQL()
self.f = Fixture('fixtures/datatype')
def tearDown(self):
self.m.ddl(self.f.drop_schema('datatype'))
self.m = None
self.f = None
def test_01_bit_min(self):
inputs = 'bit_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)),
int(b2a_hex(actual[0][0]), 16))
def test_02_bit_max(self):
inputs = 'bit_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)),
int(b2a_hex(actual[0][0]), 16))
def test_03_bit_range_exceeded(self):
inputs = 'bit_range_exceeded'
self.m.ddl(self.f.load_schema(inputs))
with self.assertRaises(DataError) as context:
self.m.dml(self.f.populate(inputs))
self.assertIn('Data too long for column', str(context.exception))
def test_04_tinyint_signed_min(self):
inputs = 'tinyint_signed_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_05_tinyint_signed_max(self):
inputs = 'tinyint_signed_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_06_smallint_signed_min(self):
inputs = 'smallint_signed_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_07_smallint_signed_max(self):
inputs = 'smallint_signed_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_08_mediumint_signed_min(self):
inputs = 'mediumint_signed_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_09_mediumint_signed_max(self):
inputs = 'mediumint_signed_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_10_int_signed_min(self):
inputs = 'int_signed_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_11_int_signed_max(self):
inputs = 'int_signed_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_12_bigint_signed_min(self):
inputs = 'bigint_signed_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_13_bigint_signed_max(self):
inputs = 'bigint_signed_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(int(self.f.expected(inputs)), actual[0][0])
def test_14_decimal_round_off(self):
inputs = 'decimal_round_off'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(Decimal(self.f.expected(inputs)), actual[0][0])
def test_15_decimal_round_up(self):
inputs = 'decimal_round_up'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(Decimal(self.f.expected(inputs)), actual[0][0])
def test_16_decimal_round_down(self):
inputs = 'decimal_round_down'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(Decimal(self.f.expected(inputs)), actual[0][0])
def test_17_float_round_off(self):
inputs = 'float_round_off'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(Decimal(self.f.expected(inputs)), actual[0][0])
def test_18_float_precision(self):
inputs = 'float_precision'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(float(self.f.expected(inputs)), actual[0][0])
def test_19_date_min(self):
"""
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_current-timestamp
https://docs.python.org/2/library/datetime.html
"""
inputs = 'date_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
(y, m, d) = self.f.expected(inputs).split('-')
expected = date(year=int(y), month=int(m), day=int(d))
self.assertEqual(expected, actual[0][0])
def test_20_date_max(self):
"""
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_current-timestamp
https://docs.python.org/2/library/datetime.html
"""
inputs = 'date_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
(y, m, d) = self.f.expected(inputs).split('-')
expected = date(year=int(y), month=int(m), day=int(d))
self.assertEqual(expected, actual[0][0])
def test_21_time_min(self):
"""
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_current-timestamp
https://docs.python.org/2/library/datetime.html
"""
inputs = 'time_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
(h, m, s) = self.f.expected(inputs).split(':')
expected = timedelta(hours=int(h)) + timedelta(
minutes=int(m)) + timedelta(seconds=int(s))
self.assertEqual(expected, actual[0][0])
def test_22_time_max(self):
"""
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_current-timestamp
https://docs.python.org/2/library/datetime.html
"""
inputs = 'time_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
(h, m, s) = self.f.expected(inputs).split(':')
expected = timedelta(hours=int(h)) + timedelta(
minutes=int(m)) + timedelta(seconds=int(s))
self.assertEqual(expected, actual[0][0])
def test_23_datetime_min(self):
"""
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_current-timestamp
https://docs.python.org/2/library/datetime.html
"""
inputs = 'datetime_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = datetime.strptime(self.f.expected(inputs),
'%Y-%m-%d %H:%M:%S')
self.assertEqual(expected, actual[0][0])
def test_24_datetime_max(self):
"""
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_current-timestamp
https://docs.python.org/2/library/datetime.html
"""
inputs = 'datetime_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = datetime.strptime(self.f.expected(inputs),
'%Y-%m-%d %H:%M:%S')
self.assertEqual(expected, actual[0][0])
def test_25_timestamp_min(self):
"""
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_current-timestamp
https://docs.python.org/2/library/datetime.html
"""
inputs = 'timestamp_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = datetime.strptime(self.f.expected(inputs),
'%Y-%m-%d %H:%M:%S')
self.assertEqual(expected, actual[0][0])
def test_26_timestamp_max(self):
"""
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_current-timestamp
https://docs.python.org/2/library/datetime.html
"""
inputs = 'timestamp_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
expected = datetime.strptime(self.f.expected(inputs),
'%Y-%m-%d %H:%M:%S')
self.assertEqual(expected, actual[0][0])
def test_27_char_min(self):
inputs = 'char_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(self.f.expected(inputs), actual[0][0])
def test_28_char_max(self):
inputs = 'char_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(self.f.expected(inputs), actual[0][0])
def test_29_char_trailing_spaces(self):
inputs = 'char_trailing_spaces'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(self.f.expected(inputs), actual[0][0])
def test_30_varchar_min(self):
inputs = 'varchar_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(self.f.expected(inputs), actual[0][0])
def test_31_varchar_max(self):
inputs = 'varchar_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(''.join(self.f.expected(inputs)), actual[0][0])
def test_32_text_min(self):
inputs = 'text_min'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(''.join(self.f.expected(inputs)), actual[0][0])
def test_33_text_max(self):
inputs = 'text_max'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(''.join(self.f.expected(inputs)), actual[0][0])
def test_34_enum(self):
inputs = 'enum'
self.m.ddl(self.f.load_schema(inputs))
self.m.dml(self.f.populate(inputs))
actual = self.m.dql(self.f.test(inputs))
self.assertEqual(''.join(self.f.expected(inputs)), actual[0][0])