def deleteChunk(dbName, tblName, chunkId):
""" Delete chunk from a table, both chunk data and overlap data is dropped. """
_log.debug('request: %s', request)
_log.debug('request.form: %s', request.form)
_log.debug('DELETE => delete chunk')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
if chunkId < 0:
raise ExceptionResponse(400, "InvalidArgument", "Chunk ID argument is negative")
# 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))
chunkRepr = None
# chunk data table
# TODO: we need some central location for things like this
table = tblName + '_' + str(chunkId)
if utils.tableExists(dbConn, table, dbName):
# drop table
_log.debug('drop chunk table: %s', table)
utils.dropTable(dbConn, table, dbName)
chunkRepr = _chunkDict(dbName, tblName, chunkId)
chunkRepr['chunkTable'] = True
# overlap data table
# TODO: we need some central location for things like this
table = tblName + 'FullOverlap_' + str(chunkId)
if utils.tableExists(dbConn, table, dbName):
# drop table
_log.debug('drop chunk table: %s', table)
utils.dropTable(dbConn, table, dbName)
if chunkRepr is not None:
chunkRepr['overlapTable'] = True
else:
# chunk data table is missing
_log.error('Chunk does not exist, but overlap does')
raise ExceptionResponse(404, "ChunkDeleteFailed", "Cannot delete chunk data table",
"Chunk %s is not found for table %s.%s (but overlap table was deleted)" %
(chunkId, dbName, tblName))
if chunkRepr is None:
# nothing found
_log.error('Chunk does not exist')
raise ExceptionResponse(404, "ChunkDeleteFailed", "Cannot delete chunk data table",
"Chunk %s is not found for table %s.%s" % (chunkId, dbName, tblName))
return json.jsonify(result=chunkRepr)
def deleteChunk(dbName, tblName, chunkId):
""" Delete chunk from a table, both chunk data and overlap data is dropped. """
_log.debug('request: %s', request)
_log.debug('request.form: %s', request.form)
_log.debug('DELETE => delete chunk')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
if chunkId < 0:
raise ExceptionResponse(400, "InvalidArgument", "Chunk ID argument is negative")
# check that table exists
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))
chunkRepr = None
# chunk data table
# TODO: we need some central location for things like this
table = tblName + '_' + str(chunkId)
if utils.tableExists(dbConn, table, dbName):
# drop table
_log.debug('drop chunk table: %s', table)
utils.dropTable(dbConn, table, dbName)
chunkRepr = _chunkDict(dbName, tblName, chunkId)
chunkRepr['chunkTable'] = True
# overlap data table
# TODO: we need some central location for things like this
table = tblName + 'FullOverlap_' + str(chunkId)
if utils.tableExists(dbConn, table, dbName):
# drop table
_log.debug('drop chunk table: %s', table)
utils.dropTable(dbConn, table, dbName)
if chunkRepr is not None:
chunkRepr['overlapTable'] = True
else:
# chunk data table is missing
_log.error('Chunk does not exist, but overlap does')
raise ExceptionResponse(404, "ChunkDeleteFailed", "Cannot delete chunk data table",
"Chunk %s is not found for table %s.%s "
"(but overlap table was deleted)" % (chunkId, dbName, tblName))
if chunkRepr is None:
# nothing found
_log.error('Chunk does not exist')
raise ExceptionResponse(404, "ChunkDeleteFailed", "Cannot delete chunk data table",
"Chunk %s is not found for table %s.%s" % (chunkId, dbName, tblName))
return json.jsonify(result=chunkRepr)
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)
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 tableSchema(dbName, tblName):
""" Return result of SHOW CREATE TABLE statement for given table. """
_log.debug('request: %s', request)
_log.debug('GET => show create table')
# 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))
query = "SHOW CREATE TABLE `%s`.`%s`" % (dbName, tblName)
rows = dbConn.execute(query)
schema = rows.first()[1]
# Apparently mariadb (and probably mysql) can return BLOB instead of
# string for this query in some conditions (related to size of the
# returned string). BLOB is turned into Python bytes and JSON can't do
# bytes, so here we have to decode bytes. Potential issue is encoding
# of the BLOB, we use UTF-8 and expect it to be safe in all cases.
if isinstance(schema, bytes):
schema = schema.decode()
return json.jsonify(result=schema)
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 tableSchema(dbName, tblName):
""" Return result of SHOW CREATE TABLE statement for given table. """
_log.debug('request: %s', request)
_log.debug('GET => show create table')
# 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))
query = "SHOW CREATE TABLE `%s`.`%s`" % (dbName, tblName)
rows = dbConn.execute(query)
schema = rows.first()[1]
# Apparently mariadb (and probably mysql) can return BLOB instead of
# string for this query in some conditions (related to size of the
# returned string). BLOB is turned into Python bytes and JSON can't do
# bytes, so here we have to decode bytes. Potential issue is encoding
# of the BLOB, we use UTF-8 and expect it to be safe in all cases.
if isinstance(schema, bytes):
schema = schema.decode()
return json.jsonify(result=schema)
def _workerId():
""" Fetch and return a unique identifier of the worker. """
_log.debug('fetching a unique identifier of the worker')
dbName = 'qservw_worker'
tblName = 'Id'
# 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))
query = "SELECT id FROM %s.%s" % (dbName, tblName)
rows = dbConn.execute(query)
row = rows.first()
if row is None:
raise ExceptionResponse(
404, "NoWorkerIdFound",
"Table %s.%s does not have any worker identifier" %
(dbName, tblName))
return row[0]
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 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 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 tableSchema(dbName, tblName):
""" Return result of SHOW CREATE TABLE statement for given table. """
_log.debug('request: %s', request)
_log.debug('GET => show create table')
# 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))
query = "SHOW CREATE TABLE `%s`.`%s`" % (dbName, tblName)
rows = dbConn.execute(query)
return json.jsonify(result=rows.first()[1])
def tableSchema(dbName, tblName):
""" Return result of SHOW CREATE TABLE statement for given table. """
_log.debug('request: %s', request)
_log.debug('GET => show create table')
# 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))
query = "SHOW CREATE TABLE `%s`.`%s`" % (dbName, tblName)
rows = dbConn.execute(query)
return json.jsonify(result=rows.first()[1])
def tableColumns(dbName, tblName):
""" Return result of SHOW COLUMNS statement for given table. """
_log.debug('request: %s', request)
_log.debug('GET => show columns')
# 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))
query = "SHOW COLUMNS FROM `%s`.`%s`" % (dbName, tblName)
rows = dbConn.execute(query)
columns = [_columnDict(row) for row in rows]
return json.jsonify(results=columns)
def tableColumns(dbName, tblName):
""" Return result of SHOW COLUMNS statement for given table. """
_log.debug('request: %s', request)
_log.debug('GET => show columns')
# 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))
query = "SHOW COLUMNS FROM `%s`.`%s`" % (dbName, tblName)
rows = dbConn.execute(query)
columns = [_columnDict(row) for row in rows]
return json.jsonify(results=columns)
def current_version(self):
"""Returns current schema version.
Returns
-------
Integer number
"""
# Initial qservw_worker implementation did not have version number stored at all,
# and we call this version 0. Since version=1 version number is stored in
# QMetadata table with key="version"
if not utils.tableExists(self.engine, "QMetadata"):
_log.debug("QMetadata missing: version=0")
return 0
else:
query = "SELECT value FROM QMetadata WHERE metakey = 'version'"
result = self.engine.execute(query)
row = result.first()
if row:
_log.debug("found version in database: %s", row[0])
return int(row[0])
def current_version(self):
"""Returns current schema version.
Returns
-------
Integer number
"""
# Initial qservw_worker implementation did not have version number stored at all,
# and we call this version 0. Since version=1 version number is stored in
# QMetadata table with key="version"
if not utils.tableExists(self.engine, "QMetadata"):
_log.debug("QMetadata missing: version=0")
return 0
else:
query = "SELECT value FROM QMetadata WHERE metakey = 'version'"
result = self.engine.execute(query)
row = result.first()
if row:
_log.debug("found version in database: %s", row[0])
return int(row[0])
def _workerId():
""" Fetch and return a unique identifier of the worker. """
_log.debug('fetching a unique identifier of the worker')
dbName = 'qservw_worker'
tblName = 'Id'
# 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))
query = "SELECT id FROM %s.%s" % (dbName, tblName)
rows = dbConn.execute(query)
row = rows.first()
if row is None:
raise ExceptionResponse(404, "NoWorkerIdFound",
"Table %s.%s does not have any worker identifier" % (dbName, tblName))
return row[0]
def createChunk(dbName, tblName):
"""
Create new chunk, following parameters are expected to come in a request
(in request body with application/x-www-form-urlencoded content like regular form):
chunkId: chunk ID, non-negative integer
overlapFlag: if true then create overlap table too (default is true),
accepted values: '0', '1', 'yes', 'no', 'false', 'true'
This method is supposed to handle both regular tables and views.
"""
_log.debug('request: %s', request)
_log.debug('request.form: %s', request.form)
_log.debug('POST => create chunk')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
# parse chunk number
chunkId = request.form.get('chunkId', None)
if chunkId is None:
raise ExceptionResponse(400, "MissingArgument", "Chunk ID argument (chunkId) is missing")
try:
chunkId = int(chunkId)
if chunkId < 0:
raise ExceptionResponse(400, "InvalidArgument", "Chunk ID argument (chunkId) is negative")
except ValueError:
raise ExceptionResponse(400, "InvalidArgument", "Chunk ID argument (chunkId) is not an integer")
overlapFlag = _getArgFlag(request.form, 'overlapFlag', True)
# 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))
chunkRepr = _chunkDict(dbName, tblName, chunkId)
# make chunk table names
# TODO: we need some central location for things like this
tables = {'chunkTable': tblName + '_' + str(chunkId)}
if overlapFlag:
tables['overlapTable'] = tblName + 'FullOverlap_' + str(chunkId)
_log.debug('will create tables: %s', tables)
for tblType, chunkTable in tables.items():
# check if this table is actually a view
if utils.isView(dbConn, tblName, dbName):
# view needs more complicated algorithm to copy its definition, first copy
# its current definition, then rename existing view and then re-create it again
_log.debug('table %s is a view', tblName)
# get its current definition
query = "SHOW CREATE VIEW `{0}`.`{1}`".format(dbName, tblName)
rows = dbConn.execute(query)
viewDef = rows.first()[1]
# rename it
query = "RENAME TABLE `{0}`.`{1}` to `{0}`.`{2}`".format(dbName, tblName, chunkTable)
dbConn.execute(query)
# re-create it
dbConn.execute(viewDef)
else:
# make table using DDL from non-chunked one
_log.debug('make chunk table: %s', chunkTable)
try:
utils.createTableLike(dbConn, dbName, chunkTable, dbName, tblName)
except utils.TableExistError as exc:
_log.error('Db exception when creating table: %s', exc)
raise ExceptionResponse(409, "TableExists",
"Table %s.%s already exists" % (dbName, chunkTable))
if tblType == 'overlapTable':
_fixOverlapIndices(dbConn, dbName, chunkTable)
chunkRepr[tblType] = True
response = json.jsonify(result=chunkRepr)
response.status_code = 201
return response
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 loadData(dbName, tblName, chunkId=None):
"""
Upload data into a table or chunk using the file format supported by mysql
command LOAD DATA [LOCAL] INFILE.
For this method we expect all data to come in one request, and in addition
to table data we also need few parameters which define how data is formatted
(things like column separator, line terminator, escape, etc.) The whole
request must be multipart/form-data and contain two parts:
- one with the name "load-options" which contains set of options encoded
with usual application/x-www-form-urlencoded content type, options are:
- delimiter - defaults to TAB
- enclose - defaults to empty string (strings are not enclosed)
- escape - defaults to backslash
- terminate - defaults to newline
- compressed - "0" or "1", by default is guessed from file extension (.gz)
- one with the file data (name "table-data"), the data come in original
format with binary/octet-stream content type and binary encoding, and it
may be compressed with gzip.
"""
def _copy(tableData, fifoName, compressed):
""" Helper method to run data copy in a separate thread """
if compressed:
# gzip.GzipFile supports 'with' starting with python 2.7
with gzip.GzipFile(fileobj=tableData.stream, mode='rb') as src:
with open(fifoName, 'wb') as dst:
shutil.copyfileobj(src, dst)
_log.info('uncompressed table data to file %s', fifoName)
else:
tableData.save(fifoName)
_log.info('copied table data to file %s', fifoName)
@contextmanager
def tmpDirMaker():
""" Special context manager which creates/destroys temporary directory """
# create and return directory
tmpDir = tempfile.mkdtemp()
yield tmpDir
# do not forget to delete it on exit
try:
_log.info('deleting temporary directory %s', tmpDir)
shutil.rmtree(tmpDir)
except Exception as exc:
_log.warning('failed to delete temporary directory %s: %s', tmpDir,
exc)
@contextmanager
def threadStartJoin(thread):
""" Special context manager which starts a thread and joins it on exit """
thread.start()
yield thread
_log.debug('joining data copy thread')
thread.join()
_log.debug('request: %s', request)
_log.debug('POST => load data into chunk or overlap')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
# 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))
# determine chunk table name (if loading to chunk)
if chunkId is not None:
if request.path.endswith('/overlap'):
tblName = tblName + 'FullOverlap_' + str(chunkId)
else:
tblName = tblName + '_' + str(chunkId)
if not utils.tableExists(dbConn, tblName, dbName):
raise ExceptionResponse(
404, "ChunkTableMissing",
"Chunk table %s.%s does not exist" % (dbName, tblName))
# optional load-options
options = dict(delimiter='\t',
enclose='',
escape='\\',
terminate='\n',
compressed=None)
formOptions = request.form.get('load-options')
_log.debug('formOptions: %s', formOptions)
if formOptions:
formOptions = url_decode(formOptions)
# check that there are no non-recognized options
for key, value in formOptions.items():
if key not in options:
raise ExceptionResponse(
400, "IllegalOption",
"unrecognized option %s in load-options" % key)
options[key] = value
_log.debug('options: %s', options)
# get table data
table_data = request.files.get('table-data')
if table_data is None:
_log.debug("table-data part is missing from request")
raise ExceptionResponse(400, "MissingFileData",
"table-data part is missing from request")
with closing(table_data) as data:
_log.debug('data: name=%s filename=%s', data.name, data.filename)
# named pipe to send data to mysql, make it in a temporary dir
with tmpDirMaker() as tmpDir:
fifoName = os.path.join(tmpDir, 'tabledata.dat')
os.mkfifo(fifoName, 0o600)
# do we need to uncompress?
compressed = _getArgFlag(options, 'compressed', None)
if compressed is None:
compressed = data.filename.endswith('.gz')
_log.debug('starting data copy thread')
args = data, fifoName, compressed
with threadStartJoin(Thread(target=_copy,
args=args)) as copyThread:
# Build the query, we use LOCAL data loading to avoid messing with grants and
# file protection.
sql = "LOAD DATA LOCAL INFILE %(file)s INTO TABLE {0}.{1}".format(
dbName, tblName)
sql += " FIELDS TERMINATED BY %(delimiter)s ENCLOSED BY %(enclose)s \
ESCAPED BY %(escape)s LINES TERMINATED BY %(terminate)s"
del options['compressed']
options['file'] = fifoName
# execute query
_log.debug("query: %s, data: %s", sql, options)
results = dbConn.execute(sql, options)
count = results.rowcount
return json.jsonify(result=dict(status="OK", count=count))
def createChunk(dbName, tblName):
"""
Create new chunk, following parameters are expected to come in a request
(in request body with application/x-www-form-urlencoded content like regular form):
chunkId: chunk ID, non-negative integer
overlapFlag: if true then create overlap table too (default is true),
accepted values: '0', '1', 'yes', 'no', 'false', 'true'
This method is supposed to handle both regular tables and views.
"""
_log.debug('request: %s', request)
_log.debug('request.form: %s', request.form)
_log.debug('POST => create chunk')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
# parse chunk number
chunkId = request.form.get('chunkId', None)
if chunkId is None:
raise ExceptionResponse(400, "MissingArgument",
"Chunk ID argument (chunkId) is missing")
try:
chunkId = int(chunkId)
if chunkId < 0:
raise ExceptionResponse(400, "InvalidArgument",
"Chunk ID argument (chunkId) is negative")
except ValueError:
raise ExceptionResponse(
400, "InvalidArgument",
"Chunk ID argument (chunkId) is not an integer")
overlapFlag = _getArgFlag(request.form, 'overlapFlag', True)
# 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))
chunkRepr = _chunkDict(dbName, tblName, chunkId)
# make chunk table names
# TODO: we need some central location for things like this
tables = {'chunkTable': tblName + '_' + str(chunkId)}
if overlapFlag:
tables['overlapTable'] = tblName + 'FullOverlap_' + str(chunkId)
_log.debug('will create tables: %s', tables)
for tblType, chunkTable in tables.items():
# check if this table is actually a view
if utils.isView(dbConn, tblName, dbName):
# view needs more complicated algorithm to copy its definition, first copy
# its current definition, then rename existing view and then re-create it again
_log.debug('table %s is a view', tblName)
# get its current definition
query = "SHOW CREATE VIEW `{0}`.`{1}`".format(dbName, tblName)
rows = dbConn.execute(query)
viewDef = rows.first()[1]
# rename it
query = "RENAME TABLE `{0}`.`{1}` to `{0}`.`{2}`".format(
dbName, tblName, chunkTable)
dbConn.execute(query)
# re-create it
dbConn.execute(viewDef)
else:
# make table using DDL from non-chunked one
_log.debug('make chunk table: %s', chunkTable)
try:
utils.createTableLike(dbConn, dbName, chunkTable, dbName,
tblName)
except utils.TableExistsError as exc:
_log.error('Db exception when creating table: %s', exc)
raise ExceptionResponse(
409, "TableExists",
"Table %s.%s already exists" % (dbName, chunkTable))
if tblType == 'overlapTable':
_fixOverlapIndices(dbConn, dbName, chunkTable)
chunkRepr[tblType] = True
response = json.jsonify(result=chunkRepr)
response.status_code = 201
return response
def loadData(dbName, tblName, chunkId=None):
"""
Upload data into a table or chunk using the file format supported by mysql
command LOAD DATA [LOCAL] INFILE.
For this method we expect all data to come in one request, and in addition
to table data we also need few parameters which define how data is formatted
(things like column separator, line terminator, escape, etc.) The whole
request must be multipart/form-data and contain two parts:
- one with the name "load-options" which contains set of options encoded
with usual application/x-www-form-urlencoded content type, options are:
- delimiter - defaults to TAB
- enclose - defaults to empty string (strings are not enclosed)
- escape - defaults to backslash
- terminate - defaults to newline
- compressed - "0" or "1", by default is guessed from file extension (.gz)
- one with the file data (name "table-data"), the data come in original
format with binary/octet-stream content type and binary encoding, and it
may be compressed with gzip.
"""
def _copy(tableData, fifoName, compressed):
""" Helper method to run data copy in a separate thread """
if compressed:
# gzip.GzipFile supports 'with' starting with python 2.7
with gzip.GzipFile(fileobj=tableData.stream, mode='rb') as src:
with open(fifoName, 'wb') as dst:
shutil.copyfileobj(src, dst)
_log.info('uncompressed table data to file %s', fifoName)
else:
tableData.save(fifoName)
_log.info('copied table data to file %s', fifoName)
@contextmanager
def tmpDirMaker():
""" Special context manager which creates/destroys temporary directory """
# create and return directory
tmpDir = tempfile.mkdtemp()
yield tmpDir
# do not forget to delete it on exit
try:
_log.info('deleting temporary directory %s', tmpDir)
shutil.rmtree(tmpDir)
except Exception as exc:
_log.warning('failed to delete temporary directory %s: %s', tmpDir, exc)
@contextmanager
def threadStartJoin(thread):
""" Special context manager which starts a thread and joins it on exit """
thread.start()
yield thread
_log.debug('joining data copy thread')
thread.join()
_log.debug('request: %s', request)
_log.debug('POST => load data into chunk or overlap')
# validate params
_validateDbName(dbName)
_validateTableName(tblName)
# 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))
# determine chunk table name (if loading to chunk)
if chunkId is not None:
if request.path.endswith('/overlap'):
tblName = tblName + 'FullOverlap_' + str(chunkId)
else:
tblName = tblName + '_' + str(chunkId)
if not utils.tableExists(dbConn, tblName, dbName):
raise ExceptionResponse(404, "ChunkTableMissing",
"Chunk table %s.%s does not exist" % (dbName, tblName))
# optional load-options
options = dict(delimiter='\t', enclose='', escape='\\', terminate='\n', compressed=None)
formOptions = request.form.get('load-options')
_log.debug('formOptions: %s', formOptions)
if formOptions:
formOptions = url_decode(formOptions)
# check that there are no non-recognized options
for key, value in formOptions.items():
if key not in options:
raise ExceptionResponse(400, "IllegalOption",
"unrecognized option %s in load-options" % key)
options[key] = value
_log.debug('options: %s', options)
# get table data
table_data = request.files.get('table-data')
if table_data is None:
_log.debug("table-data part is missing from request")
raise ExceptionResponse(400, "MissingFileData", "table-data part is missing from request")
with closing(table_data) as data:
_log.debug('data: name=%s filename=%s', data.name, data.filename)
# named pipe to send data to mysql, make it in a temporary dir
with tmpDirMaker() as tmpDir:
fifoName = os.path.join(tmpDir, 'tabledata.dat')
os.mkfifo(fifoName, 0o600)
# do we need to uncompress?
compressed = _getArgFlag(options, 'compressed', None)
if compressed is None:
compressed = data.filename.endswith('.gz')
_log.debug('starting data copy thread')
args = data, fifoName, compressed
with threadStartJoin(Thread(target=_copy, args=args)) as copyThread:
# Build the query, we use LOCAL data loading to avoid messing with grants and
# file protection.
sql = "LOAD DATA LOCAL INFILE %(file)s INTO TABLE {0}.{1}".format(dbName, tblName)
sql += " FIELDS TERMINATED BY %(delimiter)s ENCLOSED BY %(enclose)s \
ESCAPED BY %(escape)s LINES TERMINATED BY %(terminate)s"
del options['compressed']
options['file'] = fifoName
# execute query
_log.debug("query: %s, data: %s", sql, options)
results = dbConn.execute(sql, options)
count = results.rowcount
return json.jsonify(result=dict(status="OK", count=count))
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))
