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