def showColumnsInTables(self):
tables = utils.listTables(self._conn)
s = str(tables)
for tbl in tables:
ret = self._conn.execute("SHOW COLUMNS from %s" % tbl)
s += str(ret.fetchall())
return s
def listChunks(dbName, tblName):
""" Return the list of chunks in a table. For non-chunked table empty list is returned. """
_log.debug('request: %s', request)
_log.debug('GET => get chunk list')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
# check the db exists (listTables() does not fail on non-existing databases)
with Config.instance().dbEngine().begin() as dbConn:
if not utils.tableExists(dbConn, tblName, dbName):
raise ExceptionResponse(404, "TableMissing", "Table %s.%s does not exist" % (dbName, tblName))
# regexp matching chunk table names
# TODO: we need some central location for things like this
tblRe = re.compile('^' + tblName + '(FullOverlap)?_([0-9]+)$')
chunks = {}
for table in utils.listTables(dbConn, dbName):
match = tblRe.match(table)
if match is not None:
chunkId = int(match.group(2))
chunk = chunks.get(chunkId)
if chunk is None:
chunk = _chunkDict(dbName, tblName, chunkId)
chunks[chunkId] = chunk
if match.group(1) is None:
chunk['chunkTable'] = True
else:
chunk['overlapTable'] = True
_log.debug('found chunks: %s', chunks.keys())
return json.jsonify(results=chunks.values())
def showColumnsInTables(self):
tables = utils.listTables(self._conn)
s = str(tables)
for tbl in tables:
ret = self._conn.execute("SHOW COLUMNS from %s" % tbl)
s += str(ret.fetchall())
return s
def listChunks(dbName, tblName):
""" Return the list of chunks in a table. For non-chunked table empty list is returned. """
_log.debug('request: %s', request)
_log.debug('GET => get chunk list')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
# check the db exists (listTables() does not fail on non-existing databases)
dbConn = Config.instance().dbEngine().connect()
if not utils.tableExists(dbConn, tblName, dbName):
raise ExceptionResponse(404, "TableMissing", "Table %s.%s does not exist" % (dbName, tblName))
# regexp matching chunk table names
# TODO: we need some central location for things like this
tblRe = re.compile('^' + tblName + '(FullOverlap)?_([0-9]+)$')
chunks = {}
for table in utils.listTables(dbConn, dbName):
match = tblRe.match(table)
if match is not None:
chunkId = int(match.group(2))
chunk = chunks.get(chunkId)
if chunk is None:
chunk = _chunkDict(dbName, tblName, chunkId)
chunks[chunkId] = chunk
if match.group(1) is None:
chunk['chunkTable'] = True
else:
chunk['overlapTable'] = True
_log.debug('found chunks: %s', chunks.keys())
return json.jsonify(results=chunks.values())
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 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 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 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 getIndex(dbName, tblName, chunkId=None):
"""
Return index data (array of (objectId, chunkId, subChunkId) tuples). This only works
on partitined tables and is only supposed to be used with director table (but there is
no check currently that table is a director table.
Expects one parameter 'columns' which specifies comma-separated list of three column
names. Default column names are "objectId", "chunkId", "subChunkId". Result returns
columns in the same order as they are specified in 'columns' argument.
"""
_log.debug('request: %s', request)
_log.debug('GET => get index')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
if chunkId is not None and chunkId < 0:
raise ExceptionResponse(400, "InvalidArgument",
"Chunk ID argument is negative")
# get column names and validate
columns = request.args.get('columns',
"objectId,chunkId,subChunkId").strip()
columns = columns.split(',')
if len(columns) != 3:
raise ExceptionResponse(
400, "InvalidArgument",
"'columns' parameter requires comma-separated list of three column names"
)
# check that table exists
dbConn = Config.instance().dbEngine().connect()
if not utils.tableExists(dbConn, tblName, dbName):
raise ExceptionResponse(
404, "TableMissing",
"Table %s.%s does not exist" % (dbName, tblName))
# regexp matching chunk table names (but not overlap tables).
# TODO: we need some central location for things like this
tblRe = re.compile('^' + tblName + '_([0-9]+)$')
tables = []
for table in utils.listTables(dbConn, dbName):
match = tblRe.match(table)
if match is not None:
if chunkId is not None:
if chunkId == int(match.group(1)):
tables.append(table)
break # only one table can match
else:
tables.append(table)
# we expect at least one chunk table to be found
if not tables:
_log.error('No matching chunk tables found for table %s.%s chunkId=%s',
dbName, tblName, chunkId)
raise ExceptionResponse(
404, "NoMatchingChunks", "Failed to find any chunk data table",
"No matching chunks for table %s.%s chunkId=%s" %
(dbName, tblName, chunkId))
_log.debug("tables to scan: %s", tables)
# TODO: list of lists is probably not the most efficient storage
result = []
for table in tables:
query = "SELECT {0}, {1}, {2} FROM {3}.{4}"
query = query.format(columns[0], columns[1], columns[2], dbName, table)
_log.debug('query: %s', query)
allRows = dbConn.execute(query)
if allRows.keys():
descr = [
dict(name=d[0], type=utils.typeCode2Name(dbConn, d[1]))
for d in allRows.cursor.description
]
else:
descr = [dict(name=name) for name in columns]
_log.debug("description: %s", descr)
while True:
rows = allRows.fetchmany(1000000)
if not rows:
break
for row in rows:
result.append(tuple(row))
_log.debug("retrieved %d index rows", len(result))
return json.jsonify(result=dict(rows=result, description=descr))
def getIndex(dbName, tblName, chunkId=None):
"""
Return index data (array of (objectId, chunkId, subChunkId) tuples). This only works
on partitined tables and is only supposed to be used with director table (but there is
no check currently that table is a director table.
Expects one parameter 'columns' which specifies comma-separated list of three column
names. Default column names are "objectId", "chunkId", "subChunkId". Result returns
columns in the same order as they are specified in 'columns' argument.
"""
_log.debug('request: %s', request)
_log.debug('GET => get index')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
if chunkId is not None and chunkId < 0:
raise ExceptionResponse(400, "InvalidArgument", "Chunk ID argument is negative")
# get column names and validate
columns = request.args.get('columns', "objectId,chunkId,subChunkId").strip()
columns = columns.split(',')
if len(columns) != 3:
raise ExceptionResponse(400, "InvalidArgument",
"'columns' parameter requires comma-separated list of three column names")
# check that table exists
dbConn = Config.instance().dbEngine().connect()
if not utils.tableExists(dbConn, tblName, dbName):
raise ExceptionResponse(404, "TableMissing", "Table %s.%s does not exist" % (dbName, tblName))
# regexp matching chunk table names (but not overlap tables).
# TODO: we need some central location for things like this
tblRe = re.compile('^' + tblName + '_([0-9]+)$')
tables = []
for table in utils.listTables(dbConn, dbName):
match = tblRe.match(table)
if match is not None:
if chunkId is not None:
if chunkId == int(match.group(1)):
tables.append(table)
break # only one table can match
else:
tables.append(table)
# we expect at least one chunk table to be found
if not tables:
_log.error('No matching chunk tables found for table %s.%s chunkId=%s',
dbName, tblName, chunkId)
raise ExceptionResponse(404, "NoMatchingChunks", "Failed to find any chunk data table",
"No matching chunks for table %s.%s chunkId=%s" % (dbName, tblName, chunkId))
_log.debug("tables to scan: %s", tables)
# TODO: list of lists is probably not the most efficient storage
result = []
for table in tables:
query = "SELECT {0}, {1}, {2} FROM {3}.{4}"
query = query.format(columns[0], columns[1], columns[2], dbName, table)
_log.debug('query: %s', query)
allRows = dbConn.execute(query)
if allRows.keys():
descr = [dict(name=d[0], type=utils.typeCode2Name(dbConn, d[1])) for d in allRows.cursor.description]
else:
descr = [dict(name=name) for name in columns]
_log.debug("description: %s", descr)
while True:
rows = allRows.fetchmany(1000000)
if not rows:
break
for row in rows:
result.append(tuple(row))
_log.debug("retrieved %d index rows", len(result))
return json.jsonify(result=dict(rows=result, description=descr))