def testBasicOptionFileConn(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def testCheckExists(self):
"""
Test checkExist for databases and tables.
"""
conn = self._engine.connect()
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createDb(conn, self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createTable(conn, "t1", "(i int)", self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertTrue(utils.tableExists(conn, "t1", self._dbA))
# utils.useDb(conn, self._dbA)
conn = getEngineFromFile(self.CREDFILE, database=self._dbA).connect()
self.assertTrue(utils.tableExists(conn, "t1"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.dropDb(conn, self._dbA)
conn.close()
def testCheckExists(self):
"""
Test checkExist for databases and tables.
"""
conn = self._engine.connect()
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createDb(conn, self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createTable(conn, "t1", "(i int)", self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertTrue(utils.tableExists(conn, "t1", self._dbA))
# utils.useDb(conn, self._dbA)
conn = getEngineFromFile(self.CREDFILE, database=self._dbA).connect()
self.assertTrue(utils.tableExists(conn, "t1"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.dropDb(conn, self._dbA)
self.assertFalse(utils.userExists(conn, "d_Xx_u12my", "localhost"))
self.assertTrue(utils.userExists(conn, "root", "localhost"))
conn.close()
def testLoadDataInFile(self):
"""
Testing "LOAD DATA INFILE..."
"""
fd, fN = tempfile.mkstemp(suffix=".csv", text=True)
os.write(fd, '1\n2\n3\n4\n4\n4\n5\n3\n')
os.close(fd)
query = self._engine.url.query.copy()
query['local_infile'] = '1'
conn = getEngineFromFile(self.CREDFILE, query=query).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
x = conn.execute("SELECT COUNT(*) FROM t1")
self.assertEqual(8, conn.execute("SELECT COUNT(*) FROM t1").first()[0])
self.assertEqual(3, conn.execute("SELECT COUNT(*) FROM t1 WHERE i=4").first()[0])
# let's add some confusing data to the loaded file, it will get truncated
f = open(fN, 'w')
f.write('11,12,13,14\n2')
f.close()
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
def testCheckExists(self):
"""
Test checkExist for databases and tables.
"""
conn = getEngineFromArgs(
username=self._user, password=self._pass,
host=self._host, port=self._port).connect()
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createDb(conn, self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createTable(conn, "t1", "(i int)", self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertTrue(utils.tableExists(conn, "t1", self._dbA))
# utils.useDb(conn, self._dbA)
conn = getEngineFromArgs(
username=self._user, password=self._pass,
host=self._host, port=self._port,
database=self._dbA).connect()
self.assertTrue(utils.tableExists(conn, "t1"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.dropDb(conn, self._dbA)
conn.close()
def testBasicOptionFileConn(self):
conn = getEngineFromFile(self.CREDFILE+".ini").connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def testCheckExists(self):
"""
Test checkExist for databases and tables.
"""
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": self._sock}).connect()
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createDb(conn, self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createTable(conn, "t1", "(i int)", self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertTrue(utils.tableExists(conn, "t1", self._dbA))
# utils.useDb(conn, self._dbA)
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": self._sock},
database=self._dbA).connect()
self.assertTrue(utils.tableExists(conn, "t1"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.dropDb(conn, self._dbA)
self.assertFalse(utils.userExists(conn, "d_Xx_u12my", "localhost"))
self.assertTrue(utils.userExists(conn, "root", "localhost"))
conn.close()
def testLoadDataInFile(self):
"""
Testing "LOAD DATA INFILE..."
"""
fd, fN = tempfile.mkstemp(suffix=".csv", text=True)
os.write(fd, '1\n2\n3\n4\n4\n4\n5\n3\n')
os.close(fd)
query = self._engine.url.query.copy()
query['local_infile'] = '1'
conn = getEngineFromFile(self.CREDFILE, query=query).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
x = conn.execute("SELECT COUNT(*) FROM t1")
self.assertEqual(8, conn.execute("SELECT COUNT(*) FROM t1").first()[0])
self.assertEqual(
3,
conn.execute("SELECT COUNT(*) FROM t1 WHERE i=4").first()[0])
# let's add some confusing data to the loaded file, it will get truncated
f = open(fN, 'w')
f.write('11,12,13,14\n2')
f.close()
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
def testBasicOptionFileConn(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def testLoadDataInFile(self):
"""
Testing "LOAD DATA INFILE..."
"""
f, fN = tempfile.mkstemp(suffix=".csv", text=True)
f = open(fN,'w')
f.write('1\n2\n3\n4\n4\n4\n5\n3\n')
f.close()
conn = getEngineFromArgs(
username=self._user, password=self._pass,
query={"unix_socket": self._sock, "local_infile": "1"}).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
x = conn.execute("SELECT COUNT(*) FROM t1")
self.assertEqual(8, conn.execute("SELECT COUNT(*) FROM t1").first()[0])
self.assertEqual(3, conn.execute("SELECT COUNT(*) FROM t1 WHERE i=4").first()[0])
# let's add some confusing data to the loaded file, it will get truncated
f = open(fN,'w')
f.write('11,12,13,14\n2')
f.close()
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
def testResults(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(id INT, theValue FLOAT)")
conn.execute("INSERT INTO t1 VALUES(1, 1.1), (2, 2.2)")
ret = conn.execute("SELECT * FROM t1")
self.assertEqual(len(ret.keys()), 2)
def testResults(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(id INT, theValue FLOAT)")
conn.execute("INSERT INTO t1 VALUES(1, 1.1), (2, 2.2)")
ret = conn.execute("SELECT * FROM t1")
self.assertEqual(len(ret.keys()), 2)
def testCreateTableLike(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.createTableLike(conn, self._dbA, "t2", self._dbA, "t1")
self.assertTrue(utils.tableExists(conn, "t1", self._dbA))
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.createTableLike,
conn, self._dbA, "t2", self._dbA, "dummy")
def testCreateTableLike(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.createTableLike(conn, self._dbA, "t2", self._dbA, "t1")
self.assertTrue(utils.tableExists(conn, "t1", self._dbA))
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.createTableLike,
conn, self._dbA, "t2", self._dbA, "dummy")
def testOptParams(self):
"""
Testing optional parameter binding.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i char(64), j char(64))")
conn.execute("INSERT INTO t1 VALUES(%s, %s)", ("aaa", "bbb"))
utils.dropDb(conn, self._dbA)
def testOptParams(self):
"""
Testing optional parameter binding.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i char(64), j char(64))")
conn.execute("INSERT INTO t1 VALUES(%s, %s)", ("aaa", "bbb"))
utils.dropDb(conn, self._dbA)
def testResults(self):
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": "/x/sock"}).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(id INT, theValue FLOAT)")
conn.execute("INSERT INTO t1 VALUES(1, 1.1), (2, 2.2)")
ret = conn.execute("SELECT * FROM t1")
self.assertEqual(len(ret.keys()), 2)
def testCreateTableLike(self):
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": self._sock}).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.createTableLike(conn, self._dbA, "t2", self._dbA, "t1")
self.assertTrue(utils.tableExists(conn, "t1", self._dbA))
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.createTableLike,
conn, self._dbA, "t2", self._dbA, "dummy")
def testBasicSocketConn(self):
"""
Basic test: connect through socket, create db and connect to it, create one
table, drop the db, disconnect.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def testBasicSocketConn(self):
"""
Basic test: connect through socket, create db and connect to it, create one
table, drop the db, disconnect.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def testOptParams(self):
"""
Testing optional parameter binding.
"""
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": self._sock}).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i char(64), j char(64))")
conn.execute("INSERT INTO t1 VALUES(%s, %s)", ("aaa", "bbb"))
utils.dropDb(conn, self._dbA)
def testMultiCreateDef(self):
"""
Test creating db/table that already exists (in default db).
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.createDb(conn, self._dbA, mayExist=True)
self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA)
utils.useDb(conn, self._dbA)
self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
self.assertRaises(utils.TableExistsError, utils.createTable, conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
def testBasicSocketConn(self):
"""
Basic test: connect through socket, create db and connect to it, create one
table, drop the db, disconnect.
"""
conn = getEngineFromArgs(
username=self._user, password=self._pass,
query={"unix_socket":self._sock}).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def testBasicHostPortConn(self):
"""
Basic test: connect through port, create db and connect to it, create one
table, drop the db, disconnect.
"""
conn = getEngineFromArgs(
username=self._user, password=self._pass,
host=self._host, port=self._port).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def testViews(self):
"""
Testing functionality related to views.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int, j int)")
conn.execute("CREATE VIEW t2 AS SELECT i FROM t1")
self.assertFalse(utils.isView(conn, "t1"))
self.assertFalse(utils.isView(conn, "dummyT"))
self.assertTrue(utils.isView(conn, "t2"))
utils.dropDb(conn, self._dbA)
def testMultiCreateDef(self):
"""
Test creating db/table that already exists (in default db).
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.createDb(conn, self._dbA, mayExist=True)
self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA)
utils.useDb(conn, self._dbA)
self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
self.assertRaises(utils.TableExistsError, utils.createTable, conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
def testViews(self):
"""
Testing functionality related to views.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int, j int)")
conn.execute("CREATE VIEW t2 AS SELECT i FROM t1")
self.assertFalse(utils.isView(conn, "t1"))
self.assertFalse(utils.isView(conn, "dummyT"))
self.assertTrue(utils.isView(conn, "t2"))
utils.dropDb(conn, self._dbA)
def testGetEngineFromArgs(self):
url = self._engine.url
conn = getEngineFromArgs(drivername=url.drivername,
username=url.username,
password=url.password,
host=url.host,
port=url.port,
database=url.database,
query=url.query).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def testUseDb(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
self.assertRaises(utils.NoSuchDatabaseError,
utils.useDb, conn, "invDbName")
utils.dropDb(conn, self._dbA)
self.assertRaises(utils.InvalidDatabaseNameError,
utils.createTable, conn, "t1", "(i int)")
utils.createDb(conn, self._dbB)
utils.useDb(conn, self._dbB)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbB)
def testGetEngineFromArgs(self):
url = self._engine.url
conn = getEngineFromArgs(drivername=url.drivername,
username=url.username,
password=url.password,
host=url.host,
port=url.port,
database=url.database,
query=url.query).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def testUseDb(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
self.assertRaises(utils.NoSuchDatabaseError, utils.useDb, conn,
"invDbName")
utils.dropDb(conn, self._dbA)
self.assertRaises(utils.InvalidDatabaseNameError, utils.createTable,
conn, "t1", "(i int)")
utils.createDb(conn, self._dbB)
utils.useDb(conn, self._dbB)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbB)
def testViews(self):
"""
Testing functionality related to views.
"""
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": self._sock}).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int, j int)")
conn.execute("CREATE VIEW t2 AS SELECT i FROM t1")
self.assertFalse(utils.isView(conn, "t1"))
self.assertFalse(utils.isView(conn, "dummyT"))
self.assertTrue(utils.isView(conn, "t2"))
utils.dropDb(conn, self._dbA)
def testMultiCreateDef(self):
"""
Test creating db/table that already exists (in default db).
"""
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": "/x/sock"}).connect()
utils.createDb(conn, self._dbA)
utils.createDb(conn, self._dbA, mayExist=True)
self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA)
utils.useDb(conn, self._dbA)
self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
self.assertRaises(utils.TableExistsError, utils.createTable, conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
def testUseDb(self):
conn = getEngineFromArgs(
username=self._user, password=self._pass,
query={"unix_socket":self._sock}).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
self.assertRaises(utils.NoSuchDatabaseError,
utils.useDb, conn, "invDbName")
utils.dropDb(conn, self._dbA)
self.assertRaises(utils.InvalidDatabaseNameError,
utils.createTable, conn, "t1", "(i int)")
utils.createDb(conn, self._dbB)
utils.useDb(conn, self._dbB)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbB)
def testListTables(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)", self._dbA)
utils.createTable(conn, "t2", "(i int)", self._dbA)
ret = utils.listTables(conn, self._dbA)
self.assertEqual(len(ret), 2)
self.assertIn("t1", ret)
self.assertIn("t2", ret)
ret = utils.listTables(conn, self._dbB)
self.assertEqual(len(ret), 0)
conn = getEngineFromFile(self.CREDFILE, database=self._dbA).connect()
ret = utils.listTables(conn)
self.assertEqual(len(ret), 2)
self.assertIn("t1", ret)
self.assertIn("t2", ret)
utils.dropDb(conn, self._dbA)
def testListTables(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)", self._dbA)
utils.createTable(conn, "t2", "(i int)", self._dbA)
ret = utils.listTables(conn, self._dbA)
self.assertEqual(len(ret), 2)
self.assertIn("t1", ret)
self.assertIn("t2", ret)
ret = utils.listTables(conn, self._dbB)
self.assertEqual(len(ret), 0)
conn = getEngineFromFile(self.CREDFILE, database=self._dbA).connect()
ret = utils.listTables(conn)
self.assertEqual(len(ret), 2)
self.assertIn("t1", ret)
self.assertIn("t2", ret)
utils.dropDb(conn, self._dbA)
def testListTables(self):
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": "/x/sock"}).connect()
utils.createDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)", self._dbA)
utils.createTable(conn, "t2", "(i int)", self._dbA)
ret = utils.listTables(conn, self._dbA)
self.assertEqual(len(ret), 2)
self.assertTrue("t1" in ret)
self.assertTrue("t2" in ret)
ret = utils.listTables(conn, self._dbB)
self.assertEqual(len(ret), 0)
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": "/x/sock"},
database=self._dbA).connect()
ret = utils.listTables(conn)
self.assertEqual(len(ret), 2)
self.assertTrue("t1" in ret)
self.assertTrue("t2" in ret)
utils.dropDb(conn, self._dbA)
def testDropTable(self):
conn = self._engine.connect()
# using current db
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t2", "(i int)")
utils.dropTable(conn, "t2")
utils.dropTable(conn, "t2", mustExist=False)
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable,
conn, "t2")
utils.dropDb(conn, self._dbA)
# using no current db
utils.createDb(conn, self._dbB)
utils.createTable(conn, "t2", "(i int)", self._dbB)
utils.dropTable(conn, "t2", dbName=self._dbB)
utils.dropTable(conn, "t2", dbName=self._dbB, mustExist=False)
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable,
conn, "t2", self._dbB)
utils.dropDb(conn, self._dbB)
# mix of current and not current db
utils.createDb(conn, self._dbA)
utils.createDb(conn, self._dbB)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t2", "(i int)", self._dbB)
utils.createTable(conn, "t2", "(i int)")
utils.dropTable(conn, "t2")
utils.dropTable(conn, "t2", dbName=self._dbB)
utils.dropTable(conn, "t2", mustExist=False)
utils.dropTable(conn, "t2", dbName=self._dbB, mustExist=False)
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable,
conn, "t2")
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable,
conn, "t2", self._dbB)
utils.dropDb(conn, self._dbA)
utils.dropDb(conn, self._dbB)
conn.close()
def testDropTable(self):
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": self._sock}).connect()
# using current db
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t2", "(i int)")
utils.dropTable(conn, "t2")
utils.dropTable(conn, "t2", mustExist=False)
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2")
utils.dropDb(conn, self._dbA)
# using no current db
utils.createDb(conn, self._dbB)
utils.createTable(conn, "t2", "(i int)", self._dbB)
utils.dropTable(conn, "t2", dbName=self._dbB)
utils.dropTable(conn, "t2", dbName=self._dbB, mustExist=False)
self.assertRaises(sqlalchemy.exc.NoSuchTableError,
utils.dropTable, conn, "t2", self._dbB)
utils.dropDb(conn, self._dbB)
# mix of current and not current db
utils.createDb(conn, self._dbA)
utils.createDb(conn, self._dbB)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t2", "(i int)", self._dbB)
utils.createTable(conn, "t2", "(i int)")
utils.dropTable(conn, "t2")
utils.dropTable(conn, "t2", dbName=self._dbB)
utils.dropTable(conn, "t2", mustExist=False)
utils.dropTable(conn, "t2", dbName=self._dbB, mustExist=False)
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2")
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2", self._dbB)
utils.dropDb(conn, self._dbA)
utils.dropDb(conn, self._dbB)
conn.close()
def testDropTable(self):
conn = self._engine.connect()
# using current db
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t2", "(i int)")
utils.dropTable(conn, "t2")
utils.dropTable(conn, "t2", mustExist=False)
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2")
utils.dropDb(conn, self._dbA)
# using no current db
utils.createDb(conn, self._dbB)
utils.createTable(conn, "t2", "(i int)", self._dbB)
utils.dropTable(conn, "t2", dbName=self._dbB)
utils.dropTable(conn, "t2", dbName=self._dbB, mustExist=False)
self.assertRaises(sqlalchemy.exc.NoSuchTableError,
utils.dropTable, conn, "t2", self._dbB)
utils.dropDb(conn, self._dbB)
# mix of current and not current db
utils.createDb(conn, self._dbA)
utils.createDb(conn, self._dbB)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t2", "(i int)", self._dbB)
utils.createTable(conn, "t2", "(i int)")
utils.dropTable(conn, "t2")
utils.dropTable(conn, "t2", dbName=self._dbB)
utils.dropTable(conn, "t2", mustExist=False)
utils.dropTable(conn, "t2", dbName=self._dbB, mustExist=False)
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2")
self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2", self._dbB)
utils.dropDb(conn, self._dbA)
utils.dropDb(conn, self._dbB)
conn.close()
def createTable(dbName):
"""
Create new table, following parameters are expected to come in a request
(in request body with application/x-www-form-urlencoded content like regular form):
table: table name (optional)
schemaSource: source for schema name, possible values: 'request', 'css',
(default: 'request')
schema: complete "CREATE TABLE ..." statement (optional)
chunkColumns: boolean flag, false by default, accepted values:
'0', '1', 'yes', 'no', 'false', 'true'. If set to true then
delete columns "_chunkId", "_subChunkId" from table (if they
exist) and add columns "chunkId", "subChunkId" (if they don't exist)
If `schemaSource` is 'request' then request must include `schema` parameter
which is an SQL DDL statement starting with 'CREATE TABLE TableName ...'.
Table name will be extracted from SQL statement in this case, if `table`
parameter is specified then it must be the same as table name in SQL statement.
If `schemaSource` is 'css' then `table` parameter must be specified. Table
schema will be extracted from CSS in this case, `schemaSource` must not be given.
"""
_log.debug('request: %s', request)
_log.debug('request.form: %s', request.form)
_log.debug('POST => create table')
# validate database name
_validateDbName(dbName)
# get parameters
tblName = request.form.get('table', '').strip()
schemaSource = request.form.get('schemaSource', 'request').strip().lower()
schema = request.form.get('schema', '').strip()
chunkColumns = _getArgFlag(request.form, 'chunkColumns', False)
if tblName:
_validateTableName(tblName)
# verify parameters
if schemaSource == 'css':
if not tblName:
raise ExceptionResponse(
400, "MissingArgument",
"Required `table` parameter is missing (schemaSource=css)")
if schema:
raise ExceptionResponse(
400, "ConflictingArgument",
"`schema` parameter cannot be given with schemaSource=css")
elif schemaSource == 'request':
if not schema:
raise ExceptionResponse(
400, "MissingArgument",
"Required `schema` parameter is missing (schemaSource=request)"
)
else:
raise ExceptionResponse(
400, "InvalidArgument",
"`schemaSource` parameter has unexpected value \"%s\"" %
schemaSource)
dbConn = Config.instance().dbEngine().connect()
# create table
if schemaSource == 'request':
# assume that we have correct SQL already
try:
utils.useDb(dbConn, dbName)
utils.createTableFromSchema(dbConn, schema)
except utils.TableExistsError as exc:
_log.error('Exception when creating table: %s', exc)
raise ExceptionResponse(
409, "TableExists",
"Table %s.%s already exists" % (dbName, tblName))
elif schemaSource == 'css':
# get table schema from CSS
try:
css = Config.instance().cssAccess()
schema = css.getTableSchema(dbName, tblName)
_log.debug('schema from CSS: %s', schema)
except lsst.qserv.css.CssError as exc:
_log.error('Failed to retrieve table schema from CSS: %s', exc)
raise ExceptionResponse(
500, "CSSError", "Failed to retrieve table schema from CSS",
str(exc))
# schema in CSS is stored without CREATE TABLE, so we are already OK
try:
utils.createTable(dbConn, tblName, schema, dbName)
except utils.TableExistsError as exc:
_log.error('Exception when creating table: %s', exc)
raise ExceptionResponse(
409, "TableExists",
"Table %s.%s already exists" % (dbName, tblName))
_log.debug('table %s.%s created succesfully', dbName, tblName)
if chunkColumns:
# Change table schema, drop _chunkId, _subChunkId, add chunkId, subChunkId
try:
table = '`{0}`.`{1}`'.format(dbName, tblName)
# get current column set
q = "SHOW COLUMNS FROM %s" % table
result = dbConn.execute(q)
rows = result.fetchall()
columns = set(row[0] for row in rows)
# delete rows
toDelete = set(["_chunkId", "_subChunkId"]) & columns
mods = ['DROP COLUMN %s' % col for col in toDelete]
# create rows, want them in that order
toAdd = ["chunkId", "subChunkId"]
mods += [
'ADD COLUMN %s INT(11) NOT NULL' % col for col in toAdd
if col not in columns
]
if mods:
_log.info('Altering schema for table %s', table)
q = 'ALTER TABLE %s ' % table
q += ', '.join(mods)
_log.debug('query: %s', q)
dbConn.execute(q)
except Exception as exc:
_log.error('Failed to alter database table: %s', exc)
raise ExceptionResponse(500, "DbError",
"Failed to alter database table", str(exc))
# return representation for new database, 201 code is for CREATED
response = json.jsonify(result=_tblDict(dbName, tblName))
response.status_code = 201
return response
def testMultiDbs(self):
"""
Try interleaving operations on multiple databases.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.createDb(conn, self._dbB)
utils.createDb(conn, self._dbC)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)", self._dbB)
utils.createTable(conn, "t1", "(i int)")
utils.createTable(conn, "t1", "(i int)", self._dbC)
utils.dropDb(conn, self._dbB)
utils.createTable(conn, "t2", "(i int)", self._dbA)
utils.dropDb(conn, self._dbA)
utils.useDb(conn, self._dbC)
utils.createTable(conn, "t2", "(i int)")
utils.createTable(conn, "t3", "(i int)", self._dbC)
utils.dropDb(conn, self._dbC)
conn.close()
def createTable(dbName):
"""
Create new table, following parameters are expected to come in a request
(in request body with application/x-www-form-urlencoded content like regular form):
table: table name (optional)
schemaSource: source for schema name, possible values: 'request', 'css',
(default: 'request')
schema: complete "CREATE TABLE ..." statement (optional)
chunkColumns: boolean flag, false by default, accepted values:
'0', '1', 'yes', 'no', 'false', 'true'. If set to true then
delete columns "_chunkId", "_subChunkId" from table (if they
exist) and add columns "chunkId", "subChunkId" (if they don't exist)
If `schemaSource` is 'request' then request must include `schema` parameter
which is an SQL DDL statement starting with 'CREATE TABLE TableName ...'.
Table name will be extracted from SQL statement in this case, if `table`
parameter is specified then it must be the same as table name in SQL statement.
If `schemaSource` is 'css' then `table` parameter must be specified. Table
schema will be extracted from CSS in this case, `schemaSource` must not be given.
"""
_log.debug('request: %s', request)
_log.debug('request.form: %s', request.form)
_log.debug('POST => create table')
# validate database name
_validateDbName(dbName)
# get parameters
tblName = request.form.get('table', '').strip()
schemaSource = request.form.get('schemaSource', 'request').strip().lower()
schema = request.form.get('schema', '').strip()
chunkColumns = _getArgFlag(request.form, 'chunkColumns', False)
if tblName:
_validateTableName(tblName)
# verify parameters
if schemaSource == 'css':
if not tblName:
raise ExceptionResponse(400, "MissingArgument",
"Required `table` parameter is missing (schemaSource=css)")
if schema:
raise ExceptionResponse(400, "ConflictingArgument",
"`schema` parameter cannot be given with schemaSource=css")
elif schemaSource == 'request':
if not schema:
raise ExceptionResponse(400, "MissingArgument",
"Required `schema` parameter is missing (schemaSource=request)")
else:
raise ExceptionResponse(400, "InvalidArgument",
"`schemaSource` parameter has unexpected value \"%s\"" % schemaSource)
dbConn = Config.instance().dbEngine().connect()
# create table
if schemaSource == 'request':
# assume that we have correct SQL already
try:
utils.useDb(dbConn, dbName)
utils.createTableFromSchema(dbConn, schema)
except utils.TableExistsError as exc:
_log.error('Exception when creating table: %s', exc)
raise ExceptionResponse(409, "TableExists", "Table %s.%s already exists" % (dbName, tblName))
elif schemaSource == 'css':
# get table schema from CSS
try:
css = Config.instance().cssAccess()
schema = css.getTableSchema(dbName, tblName)
_log.debug('schema from CSS: %s', schema)
except css.CssError as exc:
_log.error('Failed to retrieve table schema from CSS: %s', exc)
raise ExceptionResponse(500, "CSSError", "Failed to retrieve table schema from CSS", str(exc))
# schema in CSS is stored without CREATE TABLE, so we are already OK
try:
utils.createTable(dbConn, tblName, schema, dbName)
except TableExistsError as exc:
_log.error('Exception when creating table: %s', exc)
raise ExceptionResponse(409, "TableExists", "Table %s.%s already exists" % (dbName, tblName))
_log.debug('table %s.%s created succesfully', dbName, tblName)
if chunkColumns:
# Change table schema, drop _chunkId, _subChunkId, add chunkId, subChunkId
try:
table = '`{0}`.`{1}`'.format(dbName, tblName)
# get current column set
q = "SHOW COLUMNS FROM %s" % table
result = dbConn.execute(q)
rows = result.fetchall()
columns = set(row[0] for row in rows)
# delete rows
toDelete = set(["_chunkId", "_subChunkId"]) & columns
mods = ['DROP COLUMN %s' % col for col in toDelete]
# create rows, want them in that order
toAdd = ["chunkId", "subChunkId"]
mods += ['ADD COLUMN %s INT(11) NOT NULL' % col for col in toAdd if col not in columns]
if mods:
_log.info('Altering schema for table %s', table)
q = 'ALTER TABLE %s ' % table
q += ', '.join(mods)
_log.debug('query: %s', q)
dbConn.execute(q)
except Exception as exc:
_log.error('Failed to alter database table: %s', exc)
raise ExceptionResponse(500, "DbError", "Failed to alter database table", str(exc))
# return representation for new database, 201 code is for CREATED
response = json.jsonify(result=_tblDict(dbName, tblName))
response.status_code = 201
return response
def testMultiDbs(self):
"""
Try interleaving operations on multiple databases.
"""
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": "/x/sock"}).connect()
utils.createDb(conn, self._dbA)
utils.createDb(conn, self._dbB)
utils.createDb(conn, self._dbC)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)", self._dbB)
utils.createTable(conn, "t1", "(i int)")
utils.createTable(conn, "t1", "(i int)", self._dbC)
utils.dropDb(conn, self._dbB)
utils.createTable(conn, "t2", "(i int)", self._dbA)
utils.dropDb(conn, self._dbA)
utils.useDb(conn, self._dbC)
utils.createTable(conn, "t2", "(i int)")
utils.createTable(conn, "t3", "(i int)", self._dbC)
utils.dropDb(conn, self._dbC)
conn.close()
def testMultiDbs(self):
"""
Try interleaving operations on multiple databases.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.createDb(conn, self._dbB)
utils.createDb(conn, self._dbC)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)", self._dbB)
utils.createTable(conn, "t1", "(i int)")
utils.createTable(conn, "t1", "(i int)", self._dbC)
utils.dropDb(conn, self._dbB)
utils.createTable(conn, "t2", "(i int)", self._dbA)
utils.dropDb(conn, self._dbA)
utils.useDb(conn, self._dbC)
utils.createTable(conn, "t2", "(i int)")
utils.createTable(conn, "t3", "(i int)", self._dbC)
utils.dropDb(conn, self._dbC)
conn.close()
