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 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 testConn_badHostPortGoodSocket(self):
# invalid host, but good socket
engine = getEngineFromArgs(
username=self._user, password=self._pass, host="invalidHost",
port=self._port, query={"unix_socket": self._sock})
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
# invalid port but good socket
engine = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=9876543, query={"unix_socket": self._sock})
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
def testConn_invalidSocket(self):
# make sure retry is disabled, otherwise it wil try to reconnect
# (it will assume the server is down and socket valid).
engine = getEngineFromArgs(
username=self._user, password=self._pass,
query={"unix_socket": "/x/sock"})
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
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 dbEngine(self):
""" Return database engine.
Standard Pool class causes delays in releasing connections, so we
disable connection pooling by using NullPool class. Another reason to
disable pool is that MySQL connections cannot be shared across many
threads.
"""
if self._db is not None:
return self._db
kwargs = dict(poolclass=NullPool, query={})
# To use LOCA DATA LOCAL INFILE we need to enable it explicitely
kwargs['query']['local_infile'] = 1
if self.dbHost:
kwargs['host'] = self.dbHost
if self.dbPort:
kwargs['port'] = self.dbPort
if self.dbSocket:
kwargs['query']['unix_socket'] = self.dbSocket
if self.dbUser:
kwargs['username'] = self.dbUser
_log.debug('creating new engine (password not shown) %s', kwargs)
if self.dbPasswd:
kwargs['password'] = self.dbPasswd
self._db = getEngineFromArgs(**kwargs)
return self._db
def testGetEngine(self):
"""
Simplest test, just get the engine and check if default backed is mysql
"""
engine = getEngineFromArgs(
username=self._user, password=self._pass,
host=self._host, port=self._port)
self.assertEqual("mysql", engine.url.get_backend_name())
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 proxyDbEngine(self):
""" Return database engine for proxy """
kwargs = {}
kwargs['query'] = dict(local_infile=1)
if self.proxyHost: kwargs['host'] = self.proxyHost
if self.proxyPort: kwargs['port'] = self.proxyPort
if self.proxyUser: kwargs['username'] = self.proxyUser
_log.debug('creating new connection (password not shown) %s', kwargs)
if self.proxyPasswd: kwargs['password'] = self.proxyPasswd
return getEngineFromArgs(**kwargs)
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 dbEngine(self):
""" Return database engine """
kwargs = {}
kwargs['query'] = dict(local_infile=1)
if self.dbHost: kwargs['host'] = self.dbHost
if self.dbPort: kwargs['port'] = self.dbPort
if self.dbSocket: kwargs['query']['unix_socket'] = self.dbSocket
if self.dbUser: kwargs['username'] = self.dbUser
_log.debug('creating new connection (password not shown) %s', kwargs)
if self.dbPasswd: kwargs['password'] = self.dbPasswd
return getEngineFromArgs(**kwargs)
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 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 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 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 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 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 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 setUp(self):
dict = readCredentialFile(self.CREDFILE+".mysql")
(self._host, self._port, self._user, self._pass) = \
[dict[k] for k in ('host', 'port', 'user', 'passwd')]
if self._pass is None:
self._pass = ''
self._dbA = "%s_dbWrapperTestDb_A" % self._user
self._dbB = "%s_dbWrapperTestDb_B" % self._user
self._dbC = "%s_dbWrapperTestDb_C" % self._user
conn = getEngineFromArgs(
username=self._user, password=self._pass,
host=self._host, port=self._port).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 proxyDbEngine(self):
""" Return database engine for proxy """
if self._dbProxy is not None:
return self._dbProxy
kwargs = dict(poolclass=NullPool, query={})
if self.proxyHost:
kwargs['host'] = self.proxyHost
if self.proxyPort:
kwargs['port'] = self.proxyPort
if self.proxyUser:
kwargs['username'] = self.proxyUser
_log.debug('creating new engine (password not shown) %s', kwargs)
if self.proxyPasswd:
kwargs['password'] = self.proxyPasswd
self._dbProxy = getEngineFromArgs(**kwargs)
return self._dbProxy
def privDbEngine(self):
""" Return database engine for priviledged account """
if self._dbPriv is not None:
return self._dbPriv
kwargs = dict(poolclass=NullPool, query={})
if self.dbHost:
kwargs['host'] = self.dbHost
if self.dbPort:
kwargs['port'] = self.dbPort
if self.dbSocket:
kwargs['query']['unix_socket'] = self.dbSocket
if self.dbUserPriv:
kwargs['username'] = self.dbUserPriv
_log.debug('creating new engine (password not shown) %s', kwargs)
if self.dbPasswdPriv:
kwargs['password'] = self.dbPasswdPriv
self._dbPriv = getEngineFromArgs(**kwargs)
return self._dbPriv
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 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 main():
parser = argparse.ArgumentParser(
description="Qserv database schema migration.")
parser.add_argument("-v",
"--verbose",
default=0,
action="count",
help="Use one -v for INFO logging, two for DEBUG.")
group = parser.add_mutually_exclusive_group()
group.add_argument(
"-m",
"--do-migrate",
default=False,
action="store_true",
help="Do migration, without this option script prints various info "
"and exits.")
group.add_argument(
"--check",
default=False,
action="store_true",
help="Check that migration is needed, script returns 0 if schema is "
"up-to-date, 1 otherwise.")
parser.add_argument(
"-n",
"--final",
default=None,
action="store",
type=int,
metavar="VERSION",
help="Stop migration at given version, by default update to "
"latest version.")
parser.add_argument(
"--scripts",
default=_def_scripts,
action="store",
metavar="PATH",
help="Location for migration scripts, def: %(default)s.")
group = parser.add_mutually_exclusive_group(required=True)
group.add_argument(
"-c",
"--connection",
metavar="CONNECTION",
help="Connection string in format mysql://user:pass@host:port/database."
)
group.add_argument(
"-f",
"--config-file",
metavar="PATH",
help=
"Name of configuration file in INI format with connection parameters.")
parser.add_argument(
"-s",
"--config-section",
metavar="NAME",
help="Name of configuration section in configuration file.")
parser.add_argument(
"module",
help="Name of Qserv module for which to update schema, e.g. qmeta.")
args = parser.parse_args()
# configure logging
levels = {0: logging.WARNING, 1: logging.INFO, 2: logging.DEBUG}
level = levels.get(args.verbose, logging.DEBUG)
fmt = "%(asctime)s [%(levelname)s] %(name)s: %(message)s"
logging.basicConfig(level=level, format=fmt)
if args.connection:
url = make_url(args.connection)
engine = sqlalchemy.create_engine(url)
elif args.config_file:
if not args.config_section:
parser.error("-s options required with -f")
cfg = configparser.SafeConfigParser()
if not cfg.read([args.config_file]):
# file was not found, generate exception which should happen
# if we tried to open that file
raise IOError(
2, "No such file or directory: '{}'".format(args.config_file))
# will throw is section is missing
config = dict(cfg.items(args.config_section))
# instantiate database engine
config = _normalizeConfig(config)
engine = engineFactory.getEngineFromArgs(**config)
# make an object which will manage migration process
mgr = _load_migration_mgr(args.module,
engine=engine,
scripts_dir=args.scripts)
current = mgr.current_version()
print("Current schema version: {}".format(current))
latest = mgr.latest_version()
print("Latest schema version: {}".format(latest))
migrations = mgr.migrations()
print("Known migrations:")
for v0, v1, script in migrations:
tag = " (X)" if v0 >= current else ""
print(" {} -> {} : {}{}".format(v0, v1, script, tag))
if args.check:
return 0 if mgr.current_version() == mgr.latest_version() else 1
# do the migrations
final = mgr.migrate(args.final, args.do_migrate)
if final is None:
print("No migration was needed")
else:
if args.do_migrate:
print("Database was migrated to version {}".format(final))
else:
print("Database would be migrated to version {}".format(final))
def __init__(self, dbHostName, portNumber, user, passwd):
self.conn = getEngineFromArgs(username=user,
password=passwd,
host=dbHostName,
port=portNumber).connect()
def setUp(self):
global _options
engine = getEngineFromArgs(username=_options.user,
password=_options.password,
query={"unix_socket": _options.socketFile})
self._conn = engine.connect()
def testConn_invalidHost2(self):
engine = getEngineFromArgs(
username=self._user, password=self._pass,
host="dummyHost", port=3036)
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
def testConn_wrongPortNo(self):
engine = getEngineFromArgs(
username=self._user, password=self._pass,
host=self._host, port=1579)
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
def testConn_badSocketGoodHostPort(self):
# invalid socket, but good host/port
conn = getEngineFromArgs(
username=self._user, password=self._pass, host=self._host,
port=self._port, query={"unix_socket": "/x/sock"}).connect()
conn.close()
