def testBasicOptionFileConn(self): conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbA) 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 testDropDb(self): conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.dropDb(conn, self._dbA) utils.dropDb(conn, self._dbA, mustExist=False) self.assertRaises(utils.NoSuchDatabaseError, utils.dropDb, conn, self._dbA) conn.close()
def testBasicOptionFileConn(self): conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbA) conn.close()
def testLoadDataInFile(self): """ Testing "LOAD DATA INFILE..." """ f, fN = tempfile.mkstemp(suffix=".csv", text=True) f = open(fN,'w') f.write('1\n2\n3\n4\n4\n4\n5\n3\n') f.close() conn = getEngineFromArgs( username=self._user, password=self._pass, query={"unix_socket": self._sock, "local_infile": "1"}).connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN) x = conn.execute("SELECT COUNT(*) FROM t1") self.assertEqual(8, conn.execute("SELECT COUNT(*) FROM t1").first()[0]) self.assertEqual(3, conn.execute("SELECT COUNT(*) FROM t1 WHERE i=4").first()[0]) # let's add some confusing data to the loaded file, it will get truncated f = open(fN,'w') f.write('11,12,13,14\n2') f.close() conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN) utils.dropDb(conn, self._dbA) conn.close() os.remove(fN)
def testLoadDataInFile(self): """ Testing "LOAD DATA INFILE..." """ fd, fN = tempfile.mkstemp(suffix=".csv", text=True) os.write(fd, '1\n2\n3\n4\n4\n4\n5\n3\n') os.close(fd) query = self._engine.url.query.copy() query['local_infile'] = '1' conn = getEngineFromFile(self.CREDFILE, query=query).connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN) x = conn.execute("SELECT COUNT(*) FROM t1") self.assertEqual(8, conn.execute("SELECT COUNT(*) FROM t1").first()[0]) self.assertEqual( 3, conn.execute("SELECT COUNT(*) FROM t1 WHERE i=4").first()[0]) # let's add some confusing data to the loaded file, it will get truncated f = open(fN, 'w') f.write('11,12,13,14\n2') f.close() conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN) utils.dropDb(conn, self._dbA) conn.close() os.remove(fN)
def testMultiCreateNonDef(self): """ Test creating db/table that already exists (in non default db). """ conn = self._engine.connect() utils.createDb(conn, self._dbA) self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA) utils.useDb(conn, self._dbA) utils.createDb(conn, self._dbB) self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA) utils.createTable(conn, "t1", "(i int)") self.assertRaises(utils.TableExistsError, utils.createTable, conn, "t1", "(i int)") utils.createTable(conn, "t2", "(i int)", self._dbA) self.assertRaises(utils.TableExistsError, utils.createTable, conn, "t1", "(i int)", self._dbA) self.assertRaises(utils.TableExistsError, utils.createTable, conn, "t2", "(i int)", self._dbA) utils.createTable(conn, "t1", "(i int)", self._dbB) utils.createTable(conn, "t1", "(i int)", self._dbB, mayExist=True) self.assertRaises(utils.TableExistsError, utils.createTable, conn, "t1", "(i int)", self._dbB) utils.dropDb(conn, self._dbA) conn.close()
def testLoadDataInFile(self): """ Testing "LOAD DATA INFILE..." """ fd, fN = tempfile.mkstemp(suffix=".csv", text=True) os.write(fd, '1\n2\n3\n4\n4\n4\n5\n3\n') os.close(fd) query = self._engine.url.query.copy() query['local_infile'] = '1' conn = getEngineFromFile(self.CREDFILE, query=query).connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN) x = conn.execute("SELECT COUNT(*) FROM t1") self.assertEqual(8, conn.execute("SELECT COUNT(*) FROM t1").first()[0]) self.assertEqual(3, conn.execute("SELECT COUNT(*) FROM t1 WHERE i=4").first()[0]) # let's add some confusing data to the loaded file, it will get truncated f = open(fN, 'w') f.write('11,12,13,14\n2') f.close() conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN) utils.dropDb(conn, self._dbA) conn.close() os.remove(fN)
def setUpClass(cls): try: engine = getEngineFromFile(cls.CREDFILE) except IOError: raise unittest.SkipTest( "%s: No credentials file %s, skipping tests." % (cls.__name__, cls.CREDFILE)) username = engine.url.username testId = int(time.time() * 10.0) cls.dbName = "%s_test_%d" % (username, testId) # make temporary database createDb(engine, cls.dbName) # make engine with database name cls.engine = getEngineFromFile(cls.CREDFILE, database=cls.dbName) # load scripts scripts = [ "lsstSchema4mysqlPT1_2.sql", "setup_perRunTablesS12_lsstsim.sql", "setup_storedFunctions.sql" ] sqldir = os.path.join(lsst.utils.getPackageDir("cat"), "sql") for script in scripts: loadSqlScript(cls.engine, os.path.join(sqldir, script))
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, 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 testBasicOptionFileConn(self): conn = getEngineFromFile(self.CREDFILE+".ini").connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbA) conn.close()
def testDropDb(self): conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.dropDb(conn, self._dbA) utils.dropDb(conn, self._dbA, mustExist=False) self.assertRaises(utils.NoSuchDatabaseError, utils.dropDb, conn, self._dbA) conn.close()
def setUpClass(cls): try: engine = getEngineFromFile(cls.CREDFILE) except IOError: raise unittest.SkipTest("%s: No credentials file %s, skipping tests." % (cls.__name__, cls.CREDFILE)) username = engine.url.username testId = int(time.time() * 10.0) cls.dbName = "%s_test_%d" % (username, testId) # make temporary database createDb(engine, cls.dbName) # make engine with database name cls.engine = getEngineFromFile(cls.CREDFILE, database=cls.dbName) # load scripts scripts = ["lsstSchema4mysqlPT1_2.sql", "setup_perRunTablesS12_lsstsim.sql", "setup_storedFunctions.sql"] sqldir = os.path.join(lsst.utils.getPackageDir("cat"), "sql") for script in scripts: loadSqlScript(cls.engine, os.path.join(sqldir, script))
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 testResults(self): conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(id INT, theValue FLOAT)") conn.execute("INSERT INTO t1 VALUES(1, 1.1), (2, 2.2)") ret = conn.execute("SELECT * FROM t1") self.assertEqual(len(ret.keys()), 2)
def testResults(self): conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(id INT, theValue FLOAT)") conn.execute("INSERT INTO t1 VALUES(1, 1.1), (2, 2.2)") ret = conn.execute("SELECT * FROM t1") self.assertEqual(len(ret.keys()), 2)
def testDropDb(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.dropDb(conn, self._dbA) utils.dropDb(conn, self._dbA, mustExist=False) self.assertRaises(utils.NoSuchDatabaseError, utils.dropDb, conn, self._dbA) conn.close()
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 testOptParams(self): """ Testing optional parameter binding. """ conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i char(64), j char(64))") conn.execute("INSERT INTO t1 VALUES(%s, %s)", ("aaa", "bbb")) utils.dropDb(conn, self._dbA)
def testOptParams(self): """ Testing optional parameter binding. """ conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i char(64), j char(64))") conn.execute("INSERT INTO t1 VALUES(%s, %s)", ("aaa", "bbb")) utils.dropDb(conn, self._dbA)
def testResults(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.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(id INT, theValue FLOAT)") conn.execute("INSERT INTO t1 VALUES(1, 1.1), (2, 2.2)") ret = conn.execute("SELECT * FROM t1") self.assertEqual(len(ret.keys()), 2)
def testBasicSocketConn(self): """ Basic test: connect through socket, create db and connect to it, create one table, drop the db, disconnect. """ conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbA) conn.close()
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 createDb(): """ Create new database, database name comes in a query string. In addition to creating database itself this method also grants all privileges on this database to regular non-privileged account. Following parameters are expected to come in a request (in request body with application/x-www-form-urlencoded content like regular form): db: database name (required) """ _log.debug('request: %s', request) _log.debug('request.form: %s', request.form) _log.debug('POST => make database') # get database name from query dbName = request.form.get('db', '').strip() if not dbName: raise ExceptionResponse(400, "MissingArgument", "Database name argument (db) is missing") # validate it _validateDbName(dbName) # create database, use privileged account dbConn = Config.instance().privDbEngine().connect() try: utils.createDb(dbConn, dbName) except utils.DatabaseExistsError as e: _log.error('exception when creating database %s: %s', dbName, e) raise ExceptionResponse(409, "DatabaseExists", "Database %s already exists" % dbName) _log.debug('database %s created', dbName) # grant full access to non-privileged account cmd = "GRANT ALL PRIVILEGES ON {0}.* TO '{1}'@'{2}'" user = Config.instance().dbUser hostnames = ['localhost'] for host in hostnames: try: dbConn.execute(cmd.format(dbName, user, host)) except OperationalError as exc: _log.error('exception when adding grants on database: %s', exc) raise ExceptionResponse( 500, "GrantFailed", "Database %s created but GRANT failed" % dbName, str(exc)) _log.debug('grants added') # return representation for new database, 201 code is for CREATED response = json.jsonify(result=_dbDict(dbName)) response.status_code = 201 return response
def testBasicSocketConn(self): """ Basic test: connect through socket, create db and connect to it, create one table, drop the db, disconnect. """ conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbA) conn.close()
def testOptParams(self): """ Testing optional parameter binding. """ 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 char(64), j char(64))") conn.execute("INSERT INTO t1 VALUES(%s, %s)", ("aaa", "bbb")) utils.dropDb(conn, self._dbA)
def testLoadSqlScriptWithDb(self): fd, fN = tempfile.mkstemp(suffix=".csv", text=True) os.write(fd, "create table t(i int, d double);\n") os.write(fd, "insert into t values (1, 1.1), (2, 2.2);\n") os.close(fd) conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.loadSqlScript(conn, fN, self._dbA) self.assertEqual(3, conn.execute("select sum(i) from %s.t" % self._dbA).first()[0]) utils.dropDb(conn, self._dbA) conn.close() os.remove(fN)
def testLoadSqlScriptWithDb(self): fd, fN = tempfile.mkstemp(suffix=".csv", text=True) os.write(fd, "create table t(i int, d double);\n") os.write(fd, "insert into t values (1, 1.1), (2, 2.2);\n") os.close(fd) conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.loadSqlScript(conn, fN, self._dbA) self.assertEqual(3, conn.execute("select sum(i) from %s.t" % self._dbA).first()[0]) utils.dropDb(conn, self._dbA) conn.close() os.remove(fN)
def testMultiCreateDef(self): """ Test creating db/table that already exists (in default db). """ conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.createDb(conn, self._dbA, mayExist=True) self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA) utils.useDb(conn, self._dbA) self.assertRaises(utils.DatabaseExistsError, utils.createDb, conn, self._dbA) utils.createTable(conn, "t1", "(i int)") self.assertRaises(utils.TableExistsError, utils.createTable, conn, "t1", "(i int)") utils.dropDb(conn, self._dbA)
def testLoadSqlScriptPlainPassword(self): # password is disallowed through loadsqlscript, check on that. f, fN = tempfile.mkstemp(suffix=".csv", text=True) conn = getEngineFromArgs( username=self._user, password=self._pass, host=self._host, port=self._port).connect() utils.createDb(conn, self._dbA) args = dict() args["db"] = self._dbA self.assertRaises(CannotExecuteScriptError, loadSqlScript, fN, **args) utils.dropDb(conn, self._dbA) conn.close() os.remove(fN)
def testBasicSocketConn(self): """ Basic test: connect through socket, create db and connect to it, create one table, drop the db, disconnect. """ conn = getEngineFromArgs( username=self._user, password=self._pass, query={"unix_socket":self._sock}).connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbA) conn.close()
def testViews(self): """ Testing functionality related to views. """ conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int, j int)") conn.execute("CREATE VIEW t2 AS SELECT i FROM t1") self.assertFalse(utils.isView(conn, "t1")) self.assertFalse(utils.isView(conn, "dummyT")) self.assertTrue(utils.isView(conn, "t2")) utils.dropDb(conn, self._dbA)
def testServerRestart(self): """ Testing recovery from lost connection. """ conn = self._engine.connect() utils.createDb(conn, self._dbA) # time.sleep(10) # ########################################################################## # FIXME!!! now getting (OperationalError) (2006, 'MySQL server has gone away # ########################################################################## utils.createDb(conn, self._dbB) utils.dropDb(conn, self._dbA) utils.dropDb(conn, self._dbB)
def testBasicHostPortConn(self): """ Basic test: connect through port, create db and connect to it, create one table, drop the db, disconnect. """ conn = getEngineFromArgs( username=self._user, password=self._pass, host=self._host, port=self._port).connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbA) conn.close()
def testViews(self): """ Testing functionality related to views. """ conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int, j int)") conn.execute("CREATE VIEW t2 AS SELECT i FROM t1") self.assertFalse(utils.isView(conn, "t1")) self.assertFalse(utils.isView(conn, "dummyT")) self.assertTrue(utils.isView(conn, "t2")) utils.dropDb(conn, self._dbA)
def testServerRestart(self): """ Testing recovery from lost connection. """ conn = self._engine.connect() utils.createDb(conn, self._dbA) # time.sleep(10) # ########################################################################## # FIXME!!! now getting (OperationalError) (2006, 'MySQL server has gone away # ########################################################################## utils.createDb(conn, self._dbB) utils.dropDb(conn, self._dbA) utils.dropDb(conn, self._dbB)
def createDb(): """ Create new database, database name comes in a query string. In addition to creating database itself this method also grants all privileges on this database to regular non-privileged account. Following parameters are expected to come in a request (in request body with application/x-www-form-urlencoded content like regular form): db: database name (required) """ _log.debug('request: %s', request) _log.debug('request.form: %s', request.form) _log.debug('POST => make database') # get database name from query dbName = request.form.get('db', '').strip() if not dbName: raise ExceptionResponse(400, "MissingArgument", "Database name argument (db) is missing") # validate it _validateDbName(dbName) # create database, use privileged account dbConn = Config.instance().privDbEngine().connect() try: utils.createDb(dbConn, dbName) except utils.DatabaseExistsError as e: _log.error('exception when creating database %s: %s', dbName, e) raise ExceptionResponse(409, "DatabaseExists", "Database %s already exists" % dbName) _log.debug('database %s created', dbName) # grant full access to non-privileged account cmd = "GRANT ALL PRIVILEGES ON {0}.* TO '{1}'@'{2}'" user = Config.instance().dbUser hostnames = ['localhost'] for host in hostnames: try: dbConn.execute(cmd.format(dbName, user, host)) except OperationalError as exc: _log.error('exception when adding grants on database: %s', exc) raise ExceptionResponse(500, "GrantFailed", "Database %s created but GRANT failed" % dbName, str(exc)) _log.debug('grants added') # return representation for new database, 201 code is for CREATED response = json.jsonify(result=_dbDict(dbName)) response.status_code = 201 return response
def testGetEngineFromArgs(self): url = self._engine.url conn = getEngineFromArgs(drivername=url.drivername, username=url.username, password=url.password, host=url.host, port=url.port, database=url.database, query=url.query).connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbA) conn.close()
def testUseDb(self): conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") self.assertRaises(utils.NoSuchDatabaseError, utils.useDb, conn, "invDbName") utils.dropDb(conn, self._dbA) self.assertRaises(utils.InvalidDatabaseNameError, utils.createTable, conn, "t1", "(i int)") utils.createDb(conn, self._dbB) utils.useDb(conn, self._dbB) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbB)
def testUseDb(self): conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") self.assertRaises(utils.NoSuchDatabaseError, utils.useDb, conn, "invDbName") utils.dropDb(conn, self._dbA) self.assertRaises(utils.InvalidDatabaseNameError, utils.createTable, conn, "t1", "(i int)") utils.createDb(conn, self._dbB) utils.useDb(conn, self._dbB) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbB)
def testLoadSqlScriptWithDb(self): f, fN = tempfile.mkstemp(suffix=".csv", text=True) f = open(fN,'w') f.write("create table t(i int, d double);\n") f.write("insert into t values (1, 1.1), (2, 2.2);\n") f.close() conn = getEngineFromFile(self.CREDFILE+".ini").connect() utils.createDb(conn, self._dbA) loadSqlScript( fN, username=self._user, host=self._host, port=self._port, db=self._dbA) self.assertEqual(3, conn.execute("select sum(i) from %s.t" % self._dbA).first()[0]) utils.dropDb(conn, self._dbA) conn.close() os.remove(fN)
def testGetEngineFromArgs(self): url = self._engine.url conn = getEngineFromArgs(drivername=url.drivername, username=url.username, password=url.password, host=url.host, port=url.port, database=url.database, query=url.query).connect() utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)") utils.dropDb(conn, self._dbA) conn.close()
def testServerRestart(self): """ Testing recovery from lost connection. """ 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) #time.sleep(10) # ########################################################################## # FIXME!!! now getting (OperationalError) (2006, 'MySQL server has gone away # ########################################################################## utils.createDb(conn, self._dbB) utils.dropDb(conn, self._dbA) utils.dropDb(conn, self._dbB)
def testViews(self): """ Testing functionality related to views. """ 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, j int)") conn.execute("CREATE VIEW t2 AS SELECT i FROM t1") self.assertFalse(utils.isView(conn, "t1")) self.assertFalse(utils.isView(conn, "dummyT")) self.assertTrue(utils.isView(conn, "t2")) utils.dropDb(conn, self._dbA)
def dbDestroyCreate(credFile, code, logger=log): '''Open the database userDb, delete tables, then re-create them. Returns dbName (or None if code was not passed) ''' conn = getEngineFromFile(credFile).connect() dbName = "{}_fitsTest".format(conn.engine.url.username) if (code == "DELETE"): utils.dropDb(conn, dbName, mustExist=False) utils.createDb(conn, dbName) conn = getEngineFromFile(credFile, database=dbName).connect() for q in schemaToCreate: logger.info(q) conn.execute(q) logger.info("DbSetup done")
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 testDropTable(self): conn = self._engine.connect() # using current db utils.createDb(conn, self._dbA) utils.useDb(conn, self._dbA) utils.createTable(conn, "t2", "(i int)") utils.dropTable(conn, "t2") utils.dropTable(conn, "t2", mustExist=False) self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2") utils.dropDb(conn, self._dbA) # using no current db utils.createDb(conn, self._dbB) utils.createTable(conn, "t2", "(i int)", self._dbB) utils.dropTable(conn, "t2", dbName=self._dbB) utils.dropTable(conn, "t2", dbName=self._dbB, mustExist=False) self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2", self._dbB) utils.dropDb(conn, self._dbB) # mix of current and not current db utils.createDb(conn, self._dbA) utils.createDb(conn, self._dbB) utils.useDb(conn, self._dbA) utils.createTable(conn, "t2", "(i int)", self._dbB) utils.createTable(conn, "t2", "(i int)") utils.dropTable(conn, "t2") utils.dropTable(conn, "t2", dbName=self._dbB) utils.dropTable(conn, "t2", mustExist=False) utils.dropTable(conn, "t2", dbName=self._dbB, mustExist=False) self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2") self.assertRaises(sqlalchemy.exc.NoSuchTableError, utils.dropTable, conn, "t2", self._dbB) utils.dropDb(conn, self._dbA) utils.dropDb(conn, self._dbB) conn.close()
def testMultiDbs(self): """ Try interleaving operations on multiple databases. """ conn = self._engine.connect() utils.createDb(conn, self._dbA) utils.createDb(conn, self._dbB) utils.createDb(conn, self._dbC) utils.useDb(conn, self._dbA) utils.createTable(conn, "t1", "(i int)", self._dbB) utils.createTable(conn, "t1", "(i int)") utils.createTable(conn, "t1", "(i int)", self._dbC) utils.dropDb(conn, self._dbB) utils.createTable(conn, "t2", "(i int)", self._dbA) utils.dropDb(conn, self._dbA) utils.useDb(conn, self._dbC) utils.createTable(conn, "t2", "(i int)") utils.createTable(conn, "t3", "(i int)", self._dbC) utils.dropDb(conn, self._dbC) conn.close()
def createDb(self, dbName): """Create a database @param dbName name of database to create """ utils.createDb(self.conn, dbName)
(4, 'S13_v0.7'), (5, 'jacek_db1x'), (6, 'john_tmpDb5')''', '''INSERT INTO DDT_Table(tableId, dbMetaId, tableName, descr) VALUES (1, 5, 'Object', 'my object tablexx'), (2, 5, 'Source', 'source table'), (3, 6, 'DeepSource', 'deep src')''', '''INSERT INTO DDT_Column(columnId, columnName, tableId, descr, ucd, units) VALUES ( 1, 'oId', 1, 'the object id, PK', 'meta.id;src', ''), ( 2, 'PS_ra', 1, 'right ascension', 'pos.eq.ra', 'degree'), ( 3, 'PS_decl',1, 'declination', 'pos.eq.dec', 'degree'), ( 4, 'flux', 1, 'measured flux', 'phot.count', 'nmgy'), ( 5, 'sId', 2, 'source id, PK', 'meta.id;src', ''), ( 6, 'oId', 2, 'ptr to object', 'meta.id;src', ''), ( 7, 'ra', 2, 'right ascension', 'pos.eq.ra', 'degree'), ( 8, 'decl', 2, 'declination', 'pos.eq.dec', 'degree'), ( 9, 'flux', 2, 'measured flux', 'phot.count', 'nmgy'), (10, 'dsId', 3, 'deep src id, PK', 'meta.id;src', ''), (11, 'flags', 3, 'my flags', '', '')''') conn = getEngineFromFile("~/.lsst/dbAuth-metaServ.txt").connect() utils.dropDb(conn, "metaServ_core", mustExist=False) utils.createDb(conn, "metaServ_core") utils.loadSqlScript(conn, "sql/global.sql") utils.loadSqlScript(conn, "sql/dbRepo.sql") utils.loadSqlScript(conn, "sql/fileRepo.sql") utils.useDb(conn, "metaServ_core") for q in queries: conn.execute(q)