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 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 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 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 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 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 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 migrate(self, version=None, do_migrate=False):
"""Perform schema migration from current version to given version.
Parameters
----------
version : int or None
If None then migrate to latest known version, otherwise only
migrate to given version.
do_migrate : bool
If True performa migration, otherwise only print steps that
should be performed
Returns
-------
None if no migrations were performed or the version number at
which migration has stopped.
Raises
------
Exception is raised for any migration errors. The state of the
database is not guaranteed to be consistent after exception.
"""
# checks for requested version
if version is not None:
current = self.current_version()
if current >= version:
_log.debug(
"current version (%s) is already same or newer as requested (%s)",
current, version)
return None
final_versions = [s[1] for s in self.scripts]
if version not in final_versions:
raise ValueError(
"No known migration scripts for requested version ({})".
format(version))
# apply all migration scripts in a loop
result = None
current = self.current_version()
while True:
if version is not None and current >= version:
_log.debug(
"current version (%s) is now same or newer as requested (%s)",
current, version)
break
# find all migrations for current version
scripts = [s for s in self.scripts if s[0] == current]
if not scripts:
_log.debug(
"no migration scripts found for current version (%s)",
current)
break
# if there are more than one final version use the latest or requested one
final_versions = set([s[1] for s in scripts])
final = max(final_versions)
if version is not None:
if version in final_versions:
final = version
# only use scripts for that final version
scripts = sorted([s[2:4] for s in scripts if s[1] == final])
# apply all scripts
for script, path in scripts:
_log.info("--- Executing migration script %s", script)
if do_migrate:
utils.loadSqlScript(self.engine, path)
_log.info("+++ Script %s completed successfully", script)
# make sure that current version is updated in database
if do_migrate:
query = "UPDATE QMetadata SET value = {} WHERE metakey = 'version'".format(
final)
self.engine.execute(query)
# read it back and compare with expected
current = self.current_version()
if current != final:
raise RuntimeError(
"failed to update version number in database to {}, "
"current version is now {}".format(final, current))
else:
# pretend that we migrated to this new version
current = final
result = final
return result
(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)
def loadSql(self, filePath, database):
"""Load an SQL file into a database
@param filePath: the SQL file to load
@param database: the database to use
"""
utils.loadSqlScript(self.conn, filePath, database)
def loadSql(self, filePath, database):
"""Load an SQL file into a database
@param filePath: the SQL file to load
@param database: the database to use
"""
utils.loadSqlScript(self.conn, filePath, database)
def migrate(self, version=None, do_migrate=False):
"""Perform schema migration from current version to given version.
Parameters
----------
version : int or None
If None then migrate to latest known version, otherwise only
migrate to given version.
do_migrate : bool
If True performa migration, otherwise only print steps that
should be performed
Returns
-------
None if no migrations were performed or the version number at
which migration has stopped.
Raises
------
Exception is raised for any migration errors. The state of the
database is not guaranteed to be consistent after exception.
"""
# checks for requested version
if version is not None:
current = self.current_version()
if current >= version:
_log.debug("current version (%s) is already same or newer as requested (%s)",
current, version)
return None
final_versions = [s[1] for s in self.scripts]
if version not in final_versions:
raise ValueError("No known migration scripts for requested version ({})".format(version))
# apply all migration scripts in a loop
result = None
current = self.current_version()
while True:
if version is not None and current >= version:
_log.debug("current version (%s) is now same or newer as requested (%s)",
current, version)
break
# find all migrations for current version
scripts = [s for s in self.scripts if s[0] == current]
if not scripts:
_log.debug("no migration scripts found for current version (%s)", current)
break
# if there are more than one final version use the latest or requested one
final_versions = set([s[1] for s in scripts])
final = max(final_versions)
if version is not None:
if version in final_versions:
final = version
# only use scripts for that final version
scripts = sorted([s[2:4] for s in scripts if s[1] == final])
# apply all scripts
for script, path in scripts:
_log.info("--- Executing migration script %s", script)
if do_migrate:
utils.loadSqlScript(self.engine, path)
_log.info("+++ Script %s completed successfully", script)
# make sure that current version is updated in database
if do_migrate:
query = "UPDATE QMetadata SET value = {} WHERE metakey = 'version'".format(final)
self.engine.execute(query)
# read it back and compare with expected
current = self.current_version()
if current != final:
raise RuntimeError("failed to update version number in database to {}, "
"current version is now {}".format(final, current))
else:
# pretend that we migrated to this new version
current = final
result = final
return result
