def setUp(self, *args, **kwargs): self.queries = queries = [ """insert into people(name, phone, email) values ('Jay', '123', '*****@*****.**'), ('Elmer', '234', '*****@*****.**')""", """insert into people(name, phone, email) values ('Bob', '456', '*****@*****.**')""", """select * from people""", """select * from people where name in ('Jay', 'Elmer')""", """select * from people where name in ('Jay', 'Elmer', 'Bob')""", """select * from people where name in ('J', 'E')""" ] self.dt_now = dt_now = datetime.now() self.dts = dts = [ dt_now - timedelta(minutes=5), dt_now - timedelta(minutes=4), dt_now - timedelta(minutes=3), dt_now - timedelta(minutes=2), dt_now - timedelta(minutes=1), dt_now, ] self.lister = lister = c.QueryLister() for dt, query in zip(dts, queries): query, __, canonicalized_query, __ = c.canonicalize_statement(query)[0] lister.append_statement(statement=query, canonicalized_statement=canonicalized_query, dt=dt)
def test_canonicalize_statement_7(self): sql = r""" select t1.c1 , t2.c1 from t1 , t2 where t1.id = t2.id and ( t1.id = 1 or t1.id = 2 ) """ ret = c.canonicalize_statement(sql) expected_ret = [( c.query_strip(sql), u"SELECT `t1`.`c1`,`t2`.`c1` FROM `t1`,`t2` WHERE `t1`.`id`=`t2`.`id` AND (`t1`.`id`=1 OR `t1`.`id`=2)", u"SELECT `t1`.`c1`,`t2`.`c1` FROM `t1`,`t2` WHERE `t1`.`id`=`t2`.`id` AND (`t1`.`id`=%d OR `t1`.`id`=%d)", [1, 2] )] self.assertEqual(ret, expected_ret)
def test_canonicalize_statement_5(self): sql = r"""insert into foo.bar ( a, b , c) values ( 'ab\'c' , "d\"ef" , 'ghi' )""" ret = c.canonicalize_statement(sql) expected_ret = [( c.query_strip(sql), ur"INSERT INTO `foo`.bar(`a`,`b`,`c`) VALUES (N)", ur'INSERT INTO `foo`.bar(`a`,`b`,`c`) VALUES (N)', [] )] self.assertEqual(ret, expected_ret)
def test_canonicalize_statement_4(self): sql = 'insert into foo ( col1, col2, col3 ) values ( 50.00, \'string\', 25 )' ret = c.canonicalize_statement(sql) expected_ret = [( c.query_strip(sql), u'INSERT INTO foo(`col1`,`col2`,`col3`) VALUES (N)', u'INSERT INTO foo(`col1`,`col2`,`col3`) VALUES (N)', [] )] self.assertEqual(ret, expected_ret)
def test_canonicalize_statement_3(self): sql = 'insert into bar values ( \'string\', 25, 50.00 )' ret = c.canonicalize_statement(sql) expected_ret = [( c.query_strip(sql), u'INSERT INTO `bar` VALUES (N)', u'INSERT INTO `bar` VALUES (N)', [] )] self.assertEqual(ret, expected_ret)
def test_canonicalize_statement_2(self): sql = 'select * from foo where id in ( 1, 2, 3 )' ret = c.canonicalize_statement(sql) expected_ret = [( c.query_strip(sql), u'SELECT * FROM `foo` WHERE `id` IN (N)', u'SELECT * FROM `foo` WHERE `id` IN (N)', [] )] self.assertEqual(ret, expected_ret)
def test_canonicalize_statement_9(self): sql = r'select @@version_comment limit 1' ret = c.canonicalize_statement(sql) expected_ret = [( c.query_strip(sql), u"SELECT @@version_comment LIMIT 1", u"SELECT @@version_comment LIMIT %d", [1] )] self.assertEqual(ret, expected_ret)
def test_canonicalize_statement_16(self): sql = r""" select * from people where name in ('Jay', 'Elmer', 'Bob') """ ret = c.canonicalize_statement(sql) expected_ret = [( c.query_strip(sql), ur"SELECT * FROM `people` WHERE `name` IN (N)", ur"SELECT * FROM `people` WHERE `name` IN (N)", [] )] self.assertEqual(ret, expected_ret)
def test_canonicalize_statement_14(self): sql = r""" insert into people(name, phone, email) values ('Bob', '456', '*****@*****.**') """ ret = c.canonicalize_statement(sql) expected_ret = [( c.query_strip(sql), ur"""INSERT INTO people(`name`,`phone`,`email`) VALUES (N)""", ur'INSERT INTO people(`name`,`phone`,`email`) VALUES (N)', [] )] self.assertEqual(ret, expected_ret)
def test_canonicalize_statement_13(self): sql = ur""" insert into people(name, phone, email) values ('Jay', '123', '*****@*****.**'),('Elmer', '234', '*****@*****.**') """ ret = c.canonicalize_statement(sql) expected_ret = [( c.query_strip(sql), ur"""INSERT INTO people(`name`,`phone`,`email`) VALUES (N)""", ur'INSERT INTO people(`name`,`phone`,`email`) VALUES (N)', [] )] self.assertEqual(ret, expected_ret)
def test_canonicalize_statement_1(self): sql = u'select * from foo where id = 1' ret = c.canonicalize_statement(sql) expected_ret = [( # original sql c.query_strip(sql), # canonicalized sql u'SELECT * FROM `foo` WHERE `id`=1', # parameterized sql u'SELECT * FROM `foo` WHERE `id`=%d', # values for parameterized sql [1] )] self.assertEqual(ret, expected_ret)
def _test_canonicalize_statement(self, original_queries, parameterized_queries): for original, parameterized in zip(original_queries, parameterized_queries): ret = sqlcanonclient.canonicalize_statement(original) self.assertEqual(parameterized, ret[0][2])