def testEscapeRegex(self):
""" Make sure some of the characters that throw the Java regex
package into a tizzy have been escaped, mainly ($,/). However,
any other problem chars should be added as well. """
testStr = "$$$\\"
escapedStr = QueryExecutor.escapeRegex(testStr)
assert(escapedStr == "\$\$\$\\\\")
def testConvertDirtyQueryToPstmt(self):
""" Make sure we can convert some raw SQL into a prepared statement
even if it contains some regex borking chars. """
query = "select dollars from dual where 1 = 1 and x = 1.0 and r = '$500.00' "
values = JSONArray()
nQuery = QueryExecutor.convertQueryToPstmt(query, values)
assert(nQuery == "select dollars from dual where ? = ? and x = ? and r = ? ")
assert(values.toString() == '["1","1","1.0","\\\$500.00"]')
def testConvertQueryToPstmt(self):
""" Make sure we can convert some raw SQL into a
prepared statement """
query = "select sysdate from dual where 1 = 1 and x = 1.0 and r = 'test' "
values = JSONArray()
nQuery = QueryExecutor.convertQueryToPstmt(query, values)
assert(nQuery == "select sysdate from dual where ? = ? and x = ? and r = ? ")
assert(values.toString() == '["1","1","1.0","test"]')
def setUp(self):
""" Create a DB connection pool to an embedded Apache
Derby instance for testing. Create some macros for testing
as well. """
driver = "org.apache.derby.jdbc.EmbeddedDriver"
dsn = "jdbc:derby:/testdb;create=true"
self.maxObjects = 10
self.expirationTime = 10
self.connectionTimeout = 10
self.pool = JDBCConnectionPool(driver, dsn, "", "",
self.maxObjects,
self.expirationTime,
self.connectionTimeout,"")
self.executor = QueryExecutor(self.pool)
MacroCache.put("$macro", "SELECT 3 cold FROM SYSIBM.SYSDUMMY1")
MacroCache.put("$pmacro", "SELECT 4 cole FROM SYSIBM.SYSDUMMY1 where 1 = ?")
MacroCache.put("$umacro", "insert into qe_test (id,txt) values(7,'700')")
MacroCache.put("$upmacro", "insert into qe_test (id,txt) values(?,?)")
#Setup dummy table
reqStr = '[{query:"create table qe_test (id int, txt varchar(100))"}]'
self.executor.execute(reqStr, True, True)
class QueryExecutorTestCase(unittest.TestCase):
def setUp(self):
""" Create a DB connection pool to an embedded Apache
Derby instance for testing. Create some macros for testing
as well. """
driver = "org.apache.derby.jdbc.EmbeddedDriver"
dsn = "jdbc:derby:/testdb;create=true"
self.maxObjects = 10
self.expirationTime = 10
self.connectionTimeout = 10
self.pool = JDBCConnectionPool(driver, dsn, "", "",
self.maxObjects,
self.expirationTime,
self.connectionTimeout,"")
self.executor = QueryExecutor(self.pool)
MacroCache.put("$macro", "SELECT 3 cold FROM SYSIBM.SYSDUMMY1")
MacroCache.put("$pmacro", "SELECT 4 cole FROM SYSIBM.SYSDUMMY1 where 1 = ?")
MacroCache.put("$umacro", "insert into qe_test (id,txt) values(7,'700')")
MacroCache.put("$upmacro", "insert into qe_test (id,txt) values(?,?)")
#Setup dummy table
reqStr = '[{query:"create table qe_test (id int, txt varchar(100))"}]'
self.executor.execute(reqStr, True, True)
def testEscapeRegex(self):
""" Make sure some of the characters that throw the Java regex
package into a tizzy have been escaped, mainly ($,/). However,
any other problem chars should be added as well. """
testStr = "$$$\\"
escapedStr = QueryExecutor.escapeRegex(testStr)
assert(escapedStr == "\$\$\$\\\\")
def testConvertQueryToPstmt(self):
""" Make sure we can convert some raw SQL into a
prepared statement """
query = "select sysdate from dual where 1 = 1 and x = 1.0 and r = 'test' "
values = JSONArray()
nQuery = QueryExecutor.convertQueryToPstmt(query, values)
assert(nQuery == "select sysdate from dual where ? = ? and x = ? and r = ? ")
assert(values.toString() == '["1","1","1.0","test"]')
def testConvertDirtyQueryToPstmt(self):
""" Make sure we can convert some raw SQL into a prepared statement
even if it contains some regex borking chars. """
query = "select dollars from dual where 1 = 1 and x = 1.0 and r = '$500.00' "
values = JSONArray()
nQuery = QueryExecutor.convertQueryToPstmt(query, values)
assert(nQuery == "select dollars from dual where ? = ? and x = ? and r = ? ")
assert(values.toString() == '["1","1","1.0","\\\$500.00"]')
def testExecuteQueries(self):
""" Make sure we can execute one query of each supported type
as a query set. All the results should come back together. """
reqStr = '[{query:"SELECT 1 cola FROM SYSIBM.SYSDUMMY1"},\
{query:"SELECT 2 colb FROM SYSIBM.SYSDUMMY1 where 1 = ?",values:[1]},\
{query:"SELECT SCHEMANAME colc FROM SYS.SYSSCHEMAS where SCHEMANAME = \'SYSIBM\' ", convert:true},\
{query:"$macro"},\
{query:"$pmacro",values:[1]}]'
#Test w/Document Format
outJson = self.executor.execute(reqStr,False,True)
testJson = ['[',
'{"message":"","cols":["COLA"],"status":"success","types":["INTEGER"],"rows":[{"COLA":"1"}]},' ,
'{"message":"","cols":["COLB"],"status":"success","types":["INTEGER"],"rows":[{"COLB":"2"}]},' ,
'{"message":"","cols":["COLC"],"status":"success","types":["VARCHAR"],"rows":[{"COLC":"SYSIBM"}]},',
'{"message":"","cols":["COLD"],"status":"success","types":["INTEGER"],"rows":[{"COLD":"3"}]},' ,
'{"message":"","cols":["COLE"],"status":"success","types":["INTEGER"],"rows":[{"COLE":"4"}]}' ,
']']
assert(outJson == ''.join(testJson))
#Test w/o Document Format
outJson2 = self.executor.execute(reqStr,False,False)
testJson2 = ['[',
'{"message":"","cols":["COLA"],"status":"success","types":["INTEGER"],"rows":[["1"]]},' ,
'{"message":"","cols":["COLB"],"status":"success","types":["INTEGER"],"rows":[["2"]]},' ,
'{"message":"","cols":["COLC"],"status":"success","types":["VARCHAR"],"rows":[["SYSIBM"]]},',
'{"message":"","cols":["COLD"],"status":"success","types":["INTEGER"],"rows":[["3"]]},' ,
'{"message":"","cols":["COLE"],"status":"success","types":["INTEGER"],"rows":[["4"]]}' ,
']']
assert(outJson2 == ''.join(testJson2))
def testExecuteUpdates(self):
""" Make sure we can execute one update of each supported type
as a set in a single batch."""
#Setup dummy table
reqStr = '[{query:"drop table qe_test"},\
{query:"create table qe_test (id int, txt varchar(100))"}]'
self.executor.execute(reqStr,True,True)
#Insert data
reqStr = '[{query:"insert into qe_test (id,txt) values(1,\'100\')"}, \
{query:"insert into qe_test (id,txt) values(2,\'200\')"}, \
{query:"insert into qe_test (id,txt) values(3,\'300\')"}, \
{query:"insert into qe_test (id,txt) values(4,\'400\')"}, \
{query:"insert into qe_test (id,txt) values(?,?)",values:[5,\'500\']},\
{query:"insert into qe_test (id,txt) values(6,\'600\')",convert:true},\
{query:"$umacro"},\
{query:"$upmacro",values:[8,\'800\']}]'
outJson = self.executor.execute(reqStr,True,True)
assert(outJson == '[{"message":"","status":"success"}]')
#Retrieve and confirm w/document Format
outQJson = self.executor.execute('[{query:"select id,txt from qe_test order by id"}]',False,True)
testJson = ['[{"message":"","cols":["ID","TXT"],"status":"success","types":["INTEGER","VARCHAR"],',
'"rows":[',
'{"ID":"1","TXT":"100"},',
'{"ID":"2","TXT":"200"},',
'{"ID":"3","TXT":"300"},',
'{"ID":"4","TXT":"400"},',
'{"ID":"5","TXT":"500"},',
'{"ID":"6","TXT":"600"},',
'{"ID":"7","TXT":"700"},',
'{"ID":"8","TXT":"800"}]}]']
assert(outQJson == ''.join(testJson))
#Retrieve and confirm w/o document Format
outQJson = self.executor.execute('[{query:"select id,txt from qe_test order by id"}]',False,False)
testJson = ['[{"message":"","cols":["ID","TXT"],"status":"success","types":["INTEGER","VARCHAR"],',
'"rows":[',
'["1","100"],',
'["2","200"],',
'["3","300"],',
'["4","400"],',
'["5","500"],',
'["6","600"],',
'["7","700"],',
'["8","800"]]}]']
assert(outQJson == ''.join(testJson))
def testSingleUpdate(self):
""" Make sure running a single Raw SQL update
doesn't create a batch. """
reqStr = '[{query:"drop table qe_test"},\
{query:"create table qe_test (id int, txt varchar(100))"}]'
createAndDrop = self.executor.execute(reqStr,True,True)
assert(createAndDrop == '[{"message":"","status":"success"}]')
outJson = self.executor.execute('[{query:"insert into qe_test (id,txt) values(1,\'100\')"}]',True,True)
assert(outJson == '[{"message":"","status":"success"}]')
outQJson = self.executor.execute('[{query:"select * from qe_test order by id"}]',False,True)
assert(outQJson == '[{"message":"","cols":["ID","TXT"],"status":"success","types":["INTEGER","VARCHAR"],"rows":[{"ID":"1","TXT":"100"}]}]')
def testSinglePreparedUpdate(self):
""" Make sure running a single Prepared Statement update
doesn't create a batch. """
reqStr = '[{query:"drop table qe_test"},\
{query:"create table qe_test (id int, txt varchar(100))"}]'
createAndDrop = self.executor.execute(reqStr,True,True)
assert(createAndDrop == '[{"message":"","status":"success"}]')
outJson = self.executor.execute('[{query:"insert into qe_test (id,txt) values(?,?)",values:[9,\'900\']}]',True,True)
assert(outJson == '[{"message":"","status":"success"}]')
outQJson = self.executor.execute('[{query:"select * from qe_test order by id"}]',False,True)
assert(outQJson == '[{"message":"","cols":["ID","TXT"],"status":"success","types":["INTEGER","VARCHAR"],"rows":[{"ID":"9","TXT":"900"}]}]')
