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))