def dropDb(dbName):
""" Drop database """
_log.debug('request: %s', request)
_log.debug('request.form: %s', request.form)
_log.debug('DELETE => drop database')
# validate it
_validateDbName(dbName)
# use non-privileged account
dbConn = Config.instance().dbEngine().connect()
if not utils.dbExists(dbConn, dbName):
raise ExceptionResponse(404, "DatabaseMissing",
"Database %s does not exist" % dbName)
try:
utils.dropDb(dbConn, dbName)
except SQLAlchemyError as exc:
_log.error('Db exception when dropping database %s: %s', dbName, exc)
raise
_log.debug('successfully dropped database %s', dbName)
# return representation for deleted database
return json.jsonify(result=_dbDict(dbName))
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 = 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 testDropDb(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.dropDb(conn, self._dbA)
utils.dropDb(conn, self._dbA, mustExist=False)
self.assertRaises(utils.NoSuchDatabaseError, utils.dropDb, conn, self._dbA)
conn.close()
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 testDropDb(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.dropDb(conn, self._dbA)
utils.dropDb(conn, self._dbA, mustExist=False)
self.assertRaises(utils.NoSuchDatabaseError, utils.dropDb, conn, self._dbA)
conn.close()
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 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 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, 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..."
"""
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 testMultiCreateNonDef(self):
"""
Test creating db/table that already exists (in non default db).
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn,
self._dbA)
utils.useDb(conn, self._dbA)
utils.createDb(conn, self._dbB)
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.createTable(conn, "t2", "(i int)", self._dbA)
self.assertRaises(utils.TableExistsError, utils.createTable, conn,
"t1", "(i int)", self._dbA)
self.assertRaises(utils.TableExistsError, utils.createTable, conn,
"t2", "(i int)", self._dbA)
utils.createTable(conn, "t1", "(i int)", self._dbB)
utils.createTable(conn, "t1", "(i int)", self._dbB, mayExist=True)
self.assertRaises(utils.TableExistsError, utils.createTable, conn,
"t1", "(i int)", self._dbB)
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).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 testDropDb(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.dropDb(conn, self._dbA)
utils.dropDb(conn, self._dbA, mustExist=False)
self.assertRaises(utils.NoSuchDatabaseError, utils.dropDb, conn, self._dbA)
conn.close()
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 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 testLoadSqlScriptWithDb(self):
fd, fN = tempfile.mkstemp(suffix=".csv", text=True)
os.write(fd, "create table t(i int, d double);\n")
os.write(fd, "insert into t values (1, 1.1), (2, 2.2);\n")
os.close(fd)
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.loadSqlScript(conn, fN, self._dbA)
self.assertEqual(3, conn.execute("select sum(i) from %s.t" % self._dbA).first()[0])
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
def testLoadSqlScriptWithDb(self):
fd, fN = tempfile.mkstemp(suffix=".csv", text=True)
os.write(fd, "create table t(i int, d double);\n")
os.write(fd, "insert into t values (1, 1.1), (2, 2.2);\n")
os.close(fd)
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.loadSqlScript(conn, fN, self._dbA)
self.assertEqual(3, conn.execute("select sum(i) from %s.t" % self._dbA).first()[0])
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
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 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 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 testLoadSqlScriptNoDb(self):
fd, fN = tempfile.mkstemp(suffix=".csv", text=True)
os.write(fd, "create database %s;\n" % self._dbA)
os.write(fd, "use %s;\n" % self._dbA)
os.write(fd, "create table t(i int);\n")
os.write(fd, "insert into t values (1), (2), (2), (5);\n")
os.close(fd)
conn = self._engine.connect()
utils.loadSqlScript(conn, fN)
self.assertEqual(10, conn.execute("select sum(i) from %s.t" % self._dbA).first()[0])
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
def testServerRestart(self):
"""
Testing recovery from lost connection.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
# time.sleep(10)
# ##########################################################################
# FIXME!!! now getting (OperationalError) (2006, 'MySQL server has gone away
# ##########################################################################
utils.createDb(conn, self._dbB)
utils.dropDb(conn, self._dbA)
utils.dropDb(conn, self._dbB)
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 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 testServerRestart(self):
"""
Testing recovery from lost connection.
"""
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
# time.sleep(10)
# ##########################################################################
# FIXME!!! now getting (OperationalError) (2006, 'MySQL server has gone away
# ##########################################################################
utils.createDb(conn, self._dbB)
utils.dropDb(conn, self._dbA)
utils.dropDb(conn, self._dbB)
def testLoadSqlScriptPlainPassword(self):
# password is disallowed through loadsqlscript, check on that.
f, fN = tempfile.mkstemp(suffix=".csv", text=True)
conn = getEngineFromArgs(
username=self._user, password=self._pass,
host=self._host, port=self._port).connect()
utils.createDb(conn, self._dbA)
args = dict()
args["db"] = self._dbA
self.assertRaises(CannotExecuteScriptError, loadSqlScript, fN, **args)
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
def testLoadSqlScriptNoDb(self):
fd, fN = tempfile.mkstemp(suffix=".csv", text=True)
os.write(fd, "create database %s;\n" % self._dbA)
os.write(fd, "use %s;\n" % self._dbA)
os.write(fd, "create table t(i int);\n")
os.write(fd, "insert into t values (1), (2), (2), (5);\n")
os.close(fd)
conn = self._engine.connect()
utils.loadSqlScript(conn, fN)
self.assertEqual(10, conn.execute("select sum(i) from %s.t" % self._dbA).first()[0])
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
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 testLoadSqlScriptWithDb(self):
f, fN = tempfile.mkstemp(suffix=".csv", text=True)
f = open(fN,'w')
f.write("create table t(i int, d double);\n")
f.write("insert into t values (1, 1.1), (2, 2.2);\n")
f.close()
conn = getEngineFromFile(self.CREDFILE+".ini").connect()
utils.createDb(conn, self._dbA)
loadSqlScript(
fN, username=self._user, host=self._host, port=self._port, db=self._dbA)
self.assertEqual(3, conn.execute("select sum(i) from %s.t" % self._dbA).first()[0])
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
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 testLoadSqlScriptFromPath(self):
conn = getEngineFromFile(self.CREDFILE).connect()
dbName = "%s_dbWrapperTestDb" % conn.engine.url.username
commands = ["create database %s;" % dbName,
"use %s;" % dbName,
"create table t(i int);",
"insert into t values (1), (2), (2), (5);"]
# make file but pass the name of that file to loadSqlScript
script = tempfile.NamedTemporaryFile()
script.write('\n'.join(commands))
script.seek(0)
utils.loadSqlScript(conn, script.name)
utils.dropDb(conn, dbName)
def testServerRestart(self):
"""
Testing recovery from lost connection.
"""
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)
#time.sleep(10)
# ##########################################################################
# FIXME!!! now getting (OperationalError) (2006, 'MySQL server has gone away
# ##########################################################################
utils.createDb(conn, self._dbB)
utils.dropDb(conn, self._dbA)
utils.dropDb(conn, self._dbB)
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 testLoadSqlScriptFromPath(self):
conn = getEngineFromFile(self.CREDFILE).connect()
dbName = "%s_dbWrapperTestDb" % conn.engine.url.username
commands = [
"create database %s;" % dbName,
"use %s;" % dbName, "create table t(i int);",
"insert into t values (1), (2), (2), (5);"
]
# make file but pass the name of that file to loadSqlScript
script = tempfile.NamedTemporaryFile()
script.write('\n'.join(commands))
script.seek(0)
utils.loadSqlScript(conn, script.name)
utils.dropDb(conn, dbName)
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 dbDestroyCreate(credFile, code, logger=log):
'''Open the database userDb, delete tables, then re-create them.
Returns dbName (or None if code was not passed)
'''
conn = getEngineFromFile(credFile).connect()
dbName = "{}_fitsTest".format(conn.engine.url.username)
if (code == "DELETE"):
utils.dropDb(conn, dbName, mustExist=False)
utils.createDb(conn, dbName)
conn = getEngineFromFile(credFile, database=dbName).connect()
for q in schemaToCreate:
logger.info(q)
conn.execute(q)
logger.info("DbSetup done")
def dbDestroyCreate(credFile, code, logger=log):
'''Open the database userDb, delete tables, then re-create them.
Returns dbName (or None if code was not passed)
'''
conn = getEngineFromFile(credFile).connect()
dbName = "{}_fitsTest".format(conn.engine.url.username)
if (code == "DELETE"):
utils.dropDb(conn, dbName, mustExist=False)
utils.createDb(conn, dbName)
conn = getEngineFromFile(credFile, database=dbName).connect()
for q in schemaToCreate:
logger.info(q)
conn.execute(q)
logger.info("DbSetup done")
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 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 setUp(self):
self._engine = getEngineFromFile(self.CREDFILE)
self._dbA = "%s_dbWrapperTestDb_A" % self._engine.url.username
self._dbB = "%s_dbWrapperTestDb_B" % self._engine.url.username
self._dbC = "%s_dbWrapperTestDb_C" % self._engine.url.username
conn = self._engine.connect()
if utils.dbExists(conn, self._dbA):
utils.dropDb(conn, self._dbA)
if utils.dbExists(conn, self._dbB):
utils.dropDb(conn, self._dbB)
if utils.dbExists(conn, 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()
def tearDownClass(cls):
# drop temporary database
dropDb(cls.engine, cls.dbName)
cls.engine = None
(4, 'S13_v0.7'),
(5, 'jacek_db1x'),
(6, 'john_tmpDb5')''',
'''INSERT INTO DDT_Table(tableId, dbMetaId, tableName, descr) VALUES
(1, 5, 'Object', 'my object tablexx'),
(2, 5, 'Source', 'source table'),
(3, 6, 'DeepSource', 'deep src')''',
'''INSERT INTO DDT_Column(columnId, columnName, tableId, descr, ucd, units) VALUES
( 1, 'oId', 1, 'the object id, PK', 'meta.id;src', ''),
( 2, 'PS_ra', 1, 'right ascension', 'pos.eq.ra', 'degree'),
( 3, 'PS_decl',1, 'declination', 'pos.eq.dec', 'degree'),
( 4, 'flux', 1, 'measured flux', 'phot.count', 'nmgy'),
( 5, 'sId', 2, 'source id, PK', 'meta.id;src', ''),
( 6, 'oId', 2, 'ptr to object', 'meta.id;src', ''),
( 7, 'ra', 2, 'right ascension', 'pos.eq.ra', 'degree'),
( 8, 'decl', 2, 'declination', 'pos.eq.dec', 'degree'),
( 9, 'flux', 2, 'measured flux', 'phot.count', 'nmgy'),
(10, 'dsId', 3, 'deep src id, PK', 'meta.id;src', ''),
(11, 'flags', 3, 'my flags', '', '')''')
conn = getEngineFromFile("~/.lsst/dbAuth-metaServ.txt").connect()
utils.dropDb(conn, "metaServ_core", mustExist=False)
utils.createDb(conn, "metaServ_core")
utils.loadSqlScript(conn, "sql/global.sql")
utils.loadSqlScript(conn, "sql/dbRepo.sql")
utils.loadSqlScript(conn, "sql/fileRepo.sql")
utils.useDb(conn, "metaServ_core")
for q in queries:
conn.execute(q)
