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 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).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 = 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 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 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 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 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 deleteTable(dbName, tblName):
"""
Drop a table and optionally all chunk/overlap tables.
Following parameters are expected to come in a request (in query string):
dropChunks: boolean flag, false by default, accepted values:
'0', '1', 'yes', 'no', 'false', 'true'
"""
_log.debug('request: %s', request)
_log.debug('DELETE => drop table')
# validate names
_validateDbName(dbName)
_validateTableName(tblName)
# get options
dropChunks = _getArgFlag(request.args, 'dropChunks', False)
_log.debug('dropChunks: %s', dropChunks)
dbConn = Config.instance().dbEngine().connect()
if not utils.dbExists(dbConn, dbName):
raise ExceptionResponse(404, "DatabaseMissing",
"Database %s does not exist" % dbName)
try:
# drop chunks first
nChunks = 0
if dropChunks:
# regexp matching all chunk table names
tblRe = re.compile('^' + tblName + '(FullOverlap)?_[0-9]+$')
for table in utils.listTables(dbConn, dbName):
if tblRe.match(table):
_log.debug('dropping chunk table %s.%s', dbName, table)
utils.dropTable(dbConn, table, dbName)
nChunks += 1
# drop main table
_log.debug('dropping main table %s.%s', dbName, tblName)
utils.dropTable(dbConn, tblName, dbName)
except NoSuchTableError as exc:
_log.error('Exception when dropping table: %s', exc)
chunkMsg = ""
if nChunks:
chunkMsg = ", but {0} chunk tables have been dropped".format(
nChunks)
raise ExceptionResponse(
404, "TableMissing",
"Table %s.%s does not exist%s" % (dbName, tblName, chunkMsg))
return json.jsonify(result=_tblDict(dbName, tblName))
def deleteTable(dbName, tblName):
"""
Drop a table and optionally all chunk/overlap tables.
Following parameters are expected to come in a request (in query string):
dropChunks: boolean flag, false by default, accepted values:
'0', '1', 'yes', 'no', 'false', 'true'
"""
_log.debug('request: %s', request)
_log.debug('DELETE => drop table')
# validate names
_validateDbName(dbName)
_validateTableName(tblName)
# get options
dropChunks = _getArgFlag(request.args, 'dropChunks', False)
_log.debug('dropChunks: %s', dropChunks)
dbConn = Config.instance().dbEngine().connect()
if not utils.dbExists(dbConn, dbName):
raise ExceptionResponse(404, "DatabaseMissing", "Database %s does not exist" % dbName)
try:
# drop chunks first
nChunks = 0
if dropChunks:
# regexp matching all chunk table names
tblRe = re.compile('^' + tblName + '(FullOverlap)?_[0-9]+$')
for table in utils.listTables(dbConn, dbName):
if tblRe.match(table):
_log.debug('dropping chunk table %s.%s', dbName, table)
utils.dropTable(dbConn, table, dbName)
nChunks += 1
# drop main table
_log.debug('dropping main table %s.%s', dbName, tblName)
utils.dropTable(dbConn, tblName, dbName)
except NoSuchTableError as exc:
_log.error('Exception when dropping table: %s', exc)
chunkMsg = ""
if nChunks:
chunkMsg = ", but {0} chunk tables have been dropped".format(nChunks)
raise ExceptionResponse(404, "TableMissing",
"Table %s.%s does not exist%s" % (dbName, tblName, chunkMsg))
return json.jsonify(result=_tblDict(dbName, tblName))
def listTables(dbName):
""" Return the list of tables in a database """
_log.debug('request: %s', request)
_log.debug('GET => get table list')
# validate it
_validateDbName(dbName)
# check the db exists (listTables() does not fail on non-existing databases)
dbConn = Config.instance().dbEngine().connect()
if not utils.dbExists(dbConn, dbName):
raise ExceptionResponse(404, "DatabaseMissing", "Database %s does not exist" % dbName)
# list tables
tables = utils.listTables(dbConn, dbName)
_log.debug('tables=%s', tables)
tblData = [_tblDict(dbName, tblName) for tblName in tables]
return json.jsonify(results=tblData)
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 SQLAlchemyErr 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 addDbDescr(self, dbName, schemaFile, level, dataRel, owner,
accessibility, projectName, dbMysqlAuthF):
"""
Add a database along with additional schema description provided through
@schemaFile.
@param dbName database name
@param schemaFile ascii file containing schema with description
@param level level (e.g., L1, L2, L3)
@param dataRel data release
@param owner owner of the database
@param accessibility accessibility of the database (pending/public/private)
@param projectName name of the project the db is associated with
@param dbMysqlAuthF mysql auth file for the db that we are adding
The function connects to two database servers:
a) one that has the database that is being loaded
b) one that has the metaserv database
If they are both on the same server, the connection is reused.
The course of action:
* connect to the server that has database that is being loaded
* parse the ascii schema file
* fetch schema information from the information_schema
* do the matching, add info fetched from information_schema to the
in memory structure produced by parsing ascii schema file
* fetch schema description and version (which is kept as data inside
a special table in the database that is being loaded). Ignore if it
does not exist.
* Capture information from mysql auth file about connection information
* connect to the metaserv database
* validate owner, project (these must be loaded into metaserv prior to
calling this function)
* load all the information into metaserv in various tables (Repo,
DDT_Table, DDT_Column)
It raises following MetaBEXceptions:
* DB_DOES_NOT_EXISTS if database dbName does not exist
* NOT_MATCHING if the database schema and ascii schema don't match
* TB_NOT_IN_DB if the table is described in ascii schema, but it is missing
in the database
* COL_NOT_IN_TB if the column is described in ascii schema, but it is
missing in the database
* COL_NOT_IN_FL if the column is in the database schema, but not in ascii
schema
* Db object can throw various DbException and MySQL exceptions
"""
# Connect to the server that has database that is being added
conn = getEngineFromFile(dbMysqlAuthF).connect()
if not utils.dbExists(conn, dbName):
self._log.error("Db '%s' not found.", dbName)
raise MetaBException(MetaBException.DB_DOES_NOT_EXIST, dbName)
# Parse the ascii schema file
theTable = parseSchema(schemaFile)
# Fetch the schema information from the database
ret = conn.execute(
"SELECT table_name, column_name, ordinal_position "
"FROM information_schema.COLUMNS WHERE "
"TABLE_SCHEMA = %s ORDER BY table_name", (dbName,))
# Count the number of columns in the ascii file
nColumns = sum(len(t["columns"]) for t in theTable.values())
# Check if the number of columns matches
if nColumns != ret.rowcount:
self._log.error("Number of columns in ascii file "
"(%d) != number of columns in db (%d)", nColumns, ret.rowcount)
raise MetaBException(MetaBException.NOT_MATCHING)
rows = ret.fetchall()
# Fetch ordinal_positions from information_schema and add it to "theTable"
for (tName, cName, ordP) in rows:
t = theTable.get(tName, None)
if not t:
self._log.error(
"Table '%s' not found in db, present in ascii file.", tName)
raise MetaBException(MetaBException.TB_NOT_IN_DB, tName)
foundColumn = False
for c in t["columns"]:
if c["name"] == cName:
foundColumn = True
c["ord_pos"] = int(ordP)
break
if not foundColumn:
self._log.error(
"Column '%s.%s' not found in db, present in ascii file.",
tName, cName)
raise MetaBException(MetaBException.COL_NOT_IN_TB, cName, tName)
# Check if we covered all columns
for t in theTable:
for c in theTable[t]["columns"]:
if "ord_pos" not in c:
self._log.error(
"Column '%s.%s' not found in ascii file, present in db.",
t, c)
raise MetaBException(MetaBException.COL_NOT_IN_FL, str(c), str(t))
# Get schema description and version, it is ok if it is missing
ret = conn.execute(
"SELECT version, descr FROM %s.ZZZ_Schema_Description" % dbName)
if ret.rowcount != 1:
self._log.error(
"Db '%s' does not contain schema version/description", dbName)
schemaVersion = "unknown"
schemaDescr = ""
else:
(schemaVersion, schemaDescr) = ret.first()
# This can be sometimes handy for debugging. (uncomment import too)
# pp = pprint.PrettyPrinter(indent=2)
# pp.pprint(theTable)
# Get host/port from engine
host = conn.engine.url.host
port = conn.egine.url.port
# Now, we will be talking to the metaserv database, so change
# connection as needed
if self._msMysqlAuthF != dbMysqlAuthF:
conn = getEngineFromFile(self._msMysqlAuthF).connect()
# get ownerId, this serves as validation that this is a valid owner name
ret = conn.execute("SELECT userId FROM User WHERE mysqlUserName = %s",
(owner,))
if ret.rowcount != 1:
self._log.error("Owner '%s' not found.", owner)
raise MetaBException(MetaBException.OWNER_NOT_FOUND, owner)
ownerId = ret.scalar()
# get projectId, this serves as validation that this is a valid project name
ret = conn.execute("SELECT projectId FROM Project WHERE projectName =%s",
(projectName,))
if ret.rowcount != 1:
self._log.error("Project '%s' not found.", owner)
raise MetaBException(MetaBException.PROJECT_NOT_FOUND, projectName)
projectId = ret.scalar()
# Finally, save things in the MetaServ database
cmd = "INSERT INTO Repo(url, projectId, repoType, lsstLevel, dataRelease, "
cmd += "version, shortName, description, ownerId, accessibility) "
cmd += "VALUES('/dummy',%s,'db',%s,%s,%s,%s,%s,%s,%s) "
opts = (projectId, level, dataRel, schemaVersion, dbName, schemaDescr,
ownerId, accessibility)
results = conn.execute(cmd, opts)
repoId = results.lastrowid
cmd = "INSERT INTO DbRepo(dbRepoId, dbName, connHost, connPort) "
cmd += "VALUES(%s,%s,%s,%s)"
conn.execute(cmd, (repoId, dbName, host, port))
for t in theTable:
cmd = 'INSERT INTO DDT_Table(dbRepoId, tableName, descr) '
cmd += 'VALUES(%s, %s, %s)'
results = conn.execute(cmd, (repoId, t,
theTable[t].get("description", "")))
tableId = results.lastrowid
isFirst = True
for c in theTable[t]["columns"]:
if isFirst:
cmd = 'INSERT INTO DDT_Column(columnName, tableId, '
cmd += 'ordinalPosition, descr, ucd, units) VALUES '
opts = ()
isFirst = False
else:
cmd += ', '
cmd += '(%s, %s, %s, %s, %s, %s)'
opts += (c["name"], tableId, c["ord_pos"],
c.get("description", ""), c.get("ucd", ""),
c.get("unit", ""))
conn.execute(cmd, opts)
def dbExists(self, dbName):
"""Check for database existence
@return True if database exists, False otherwise
"""
return utils.dbExists(self.conn, dbName)
def dbExists(self, dbName):
"""Check for database existence
@return True if database exists, False otherwise
"""
return utils.dbExists(self.conn, dbName)