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 = 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 = 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 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 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 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 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 setUpClass(cls):
try:
engine = getEngineFromFile(cls.CREDFILE)
except IOError:
raise unittest.SkipTest(
"%s: No credentials file %s, skipping tests." %
(cls.__name__, cls.CREDFILE))
username = engine.url.username
testId = int(time.time() * 10.0)
cls.dbName = "%s_test_%d" % (username, testId)
# make temporary database
createDb(engine, cls.dbName)
# make engine with database name
cls.engine = getEngineFromFile(cls.CREDFILE, database=cls.dbName)
# load scripts
scripts = [
"lsstSchema4mysqlPT1_2.sql", "setup_perRunTablesS12_lsstsim.sql",
"setup_storedFunctions.sql"
]
sqldir = os.path.join(lsst.utils.getPackageDir("cat"), "sql")
for script in scripts:
loadSqlScript(cls.engine, os.path.join(sqldir, script))
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 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 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 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 setUpClass(cls):
try:
engine = getEngineFromFile(cls.CREDFILE)
except IOError:
raise unittest.SkipTest("%s: No credentials file %s, skipping tests." %
(cls.__name__, cls.CREDFILE))
username = engine.url.username
testId = int(time.time() * 10.0)
cls.dbName = "%s_test_%d" % (username, testId)
# make temporary database
createDb(engine, cls.dbName)
# make engine with database name
cls.engine = getEngineFromFile(cls.CREDFILE, database=cls.dbName)
# load scripts
scripts = ["lsstSchema4mysqlPT1_2.sql",
"setup_perRunTablesS12_lsstsim.sql",
"setup_storedFunctions.sql"]
sqldir = os.path.join(lsst.utils.getPackageDir("cat"), "sql")
for script in scripts:
loadSqlScript(cls.engine, os.path.join(sqldir, script))
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 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 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 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 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 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 createDb():
"""
Create new database, database name comes in a query string. In addition to creating
database itself this method also grants all privileges on this database to regular
non-privileged account.
Following parameters are expected to come in a request (in request body
with application/x-www-form-urlencoded content like regular form):
db: database name (required)
"""
_log.debug('request: %s', request)
_log.debug('request.form: %s', request.form)
_log.debug('POST => make database')
# get database name from query
dbName = request.form.get('db', '').strip()
if not dbName:
raise ExceptionResponse(400, "MissingArgument",
"Database name argument (db) is missing")
# validate it
_validateDbName(dbName)
# create database, use privileged account
dbConn = Config.instance().privDbEngine().connect()
try:
utils.createDb(dbConn, dbName)
except utils.DatabaseExistsError as e:
_log.error('exception when creating database %s: %s', dbName, e)
raise ExceptionResponse(409, "DatabaseExists",
"Database %s already exists" % dbName)
_log.debug('database %s created', dbName)
# grant full access to non-privileged account
cmd = "GRANT ALL PRIVILEGES ON {0}.* TO '{1}'@'{2}'"
user = Config.instance().dbUser
hostnames = ['localhost']
for host in hostnames:
try:
dbConn.execute(cmd.format(dbName, user, host))
except OperationalError as exc:
_log.error('exception when adding grants on database: %s', exc)
raise ExceptionResponse(
500, "GrantFailed",
"Database %s created but GRANT failed" % dbName, str(exc))
_log.debug('grants added')
# return representation for new database, 201 code is for CREATED
response = json.jsonify(result=_dbDict(dbName))
response.status_code = 201
return response
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 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 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 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 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 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 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 createDb():
"""
Create new database, database name comes in a query string. In addition to creating
database itself this method also grants all privileges on this database to regular
non-privileged account.
Following parameters are expected to come in a request (in request body
with application/x-www-form-urlencoded content like regular form):
db: database name (required)
"""
_log.debug('request: %s', request)
_log.debug('request.form: %s', request.form)
_log.debug('POST => make database')
# get database name from query
dbName = request.form.get('db', '').strip()
if not dbName:
raise ExceptionResponse(400, "MissingArgument", "Database name argument (db) is missing")
# validate it
_validateDbName(dbName)
# create database, use privileged account
dbConn = Config.instance().privDbEngine().connect()
try:
utils.createDb(dbConn, dbName)
except utils.DatabaseExistsError as e:
_log.error('exception when creating database %s: %s', dbName, e)
raise ExceptionResponse(409, "DatabaseExists", "Database %s already exists" % dbName)
_log.debug('database %s created', dbName)
# grant full access to non-privileged account
cmd = "GRANT ALL PRIVILEGES ON {0}.* TO '{1}'@'{2}'"
user = Config.instance().dbUser
hostnames = ['localhost']
for host in hostnames:
try:
dbConn.execute(cmd.format(dbName, user, host))
except OperationalError as exc:
_log.error('exception when adding grants on database: %s', exc)
raise ExceptionResponse(500, "GrantFailed", "Database %s created but GRANT failed" % dbName,
str(exc))
_log.debug('grants added')
# return representation for new database, 201 code is for CREATED
response = json.jsonify(result=_dbDict(dbName))
response.status_code = 201
return response
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 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 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 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 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 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 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 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 createDb(self, dbName):
"""Create a database
@param dbName name of database to create
"""
utils.createDb(self.conn, dbName)
(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)
