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 = 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 testMultiCreateDef(self): """ Test creating db/table that already exists (in default db). """ 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.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 testUseDb(self): 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)") 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 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 = 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 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 testDropTable(self): conn = getEngineFromArgs( username=self._user, password=self._pass, host=self._host, port=self._port, query={"unix_socket": self._sock}).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 createTable(dbName): """ Create new table, following parameters are expected to come in a request (in request body with application/x-www-form-urlencoded content like regular form): table: table name (optional) schemaSource: source for schema name, possible values: 'request', 'css', (default: 'request') schema: complete "CREATE TABLE ..." statement (optional) chunkColumns: boolean flag, false by default, accepted values: '0', '1', 'yes', 'no', 'false', 'true'. If set to true then delete columns "_chunkId", "_subChunkId" from table (if they exist) and add columns "chunkId", "subChunkId" (if they don't exist) If `schemaSource` is 'request' then request must include `schema` parameter which is an SQL DDL statement starting with 'CREATE TABLE TableName ...'. Table name will be extracted from SQL statement in this case, if `table` parameter is specified then it must be the same as table name in SQL statement. If `schemaSource` is 'css' then `table` parameter must be specified. Table schema will be extracted from CSS in this case, `schemaSource` must not be given. """ _log.debug('request: %s', request) _log.debug('request.form: %s', request.form) _log.debug('POST => create table') # validate database name _validateDbName(dbName) # get parameters tblName = request.form.get('table', '').strip() schemaSource = request.form.get('schemaSource', 'request').strip().lower() schema = request.form.get('schema', '').strip() chunkColumns = _getArgFlag(request.form, 'chunkColumns', False) if tblName: _validateTableName(tblName) # verify parameters if schemaSource == 'css': if not tblName: raise ExceptionResponse( 400, "MissingArgument", "Required `table` parameter is missing (schemaSource=css)") if schema: raise ExceptionResponse( 400, "ConflictingArgument", "`schema` parameter cannot be given with schemaSource=css") elif schemaSource == 'request': if not schema: raise ExceptionResponse( 400, "MissingArgument", "Required `schema` parameter is missing (schemaSource=request)" ) else: raise ExceptionResponse( 400, "InvalidArgument", "`schemaSource` parameter has unexpected value \"%s\"" % schemaSource) dbConn = Config.instance().dbEngine().connect() # create table if schemaSource == 'request': # assume that we have correct SQL already try: utils.useDb(dbConn, dbName) utils.createTableFromSchema(dbConn, schema) except utils.TableExistsError as exc: _log.error('Exception when creating table: %s', exc) raise ExceptionResponse( 409, "TableExists", "Table %s.%s already exists" % (dbName, tblName)) elif schemaSource == 'css': # get table schema from CSS try: css = Config.instance().cssAccess() schema = css.getTableSchema(dbName, tblName) _log.debug('schema from CSS: %s', schema) except lsst.qserv.css.CssError as exc: _log.error('Failed to retrieve table schema from CSS: %s', exc) raise ExceptionResponse( 500, "CSSError", "Failed to retrieve table schema from CSS", str(exc)) # schema in CSS is stored without CREATE TABLE, so we are already OK try: utils.createTable(dbConn, tblName, schema, dbName) except utils.TableExistsError as exc: _log.error('Exception when creating table: %s', exc) raise ExceptionResponse( 409, "TableExists", "Table %s.%s already exists" % (dbName, tblName)) _log.debug('table %s.%s created succesfully', dbName, tblName) if chunkColumns: # Change table schema, drop _chunkId, _subChunkId, add chunkId, subChunkId try: table = '`{0}`.`{1}`'.format(dbName, tblName) # get current column set q = "SHOW COLUMNS FROM %s" % table result = dbConn.execute(q) rows = result.fetchall() columns = set(row[0] for row in rows) # delete rows toDelete = set(["_chunkId", "_subChunkId"]) & columns mods = ['DROP COLUMN %s' % col for col in toDelete] # create rows, want them in that order toAdd = ["chunkId", "subChunkId"] mods += [ 'ADD COLUMN %s INT(11) NOT NULL' % col for col in toAdd if col not in columns ] if mods: _log.info('Altering schema for table %s', table) q = 'ALTER TABLE %s ' % table q += ', '.join(mods) _log.debug('query: %s', q) dbConn.execute(q) except Exception as exc: _log.error('Failed to alter database table: %s', exc) raise ExceptionResponse(500, "DbError", "Failed to alter database table", str(exc)) # return representation for new database, 201 code is for CREATED response = json.jsonify(result=_tblDict(dbName, tblName)) response.status_code = 201 return response
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 createTable(dbName): """ Create new table, following parameters are expected to come in a request (in request body with application/x-www-form-urlencoded content like regular form): table: table name (optional) schemaSource: source for schema name, possible values: 'request', 'css', (default: 'request') schema: complete "CREATE TABLE ..." statement (optional) chunkColumns: boolean flag, false by default, accepted values: '0', '1', 'yes', 'no', 'false', 'true'. If set to true then delete columns "_chunkId", "_subChunkId" from table (if they exist) and add columns "chunkId", "subChunkId" (if they don't exist) If `schemaSource` is 'request' then request must include `schema` parameter which is an SQL DDL statement starting with 'CREATE TABLE TableName ...'. Table name will be extracted from SQL statement in this case, if `table` parameter is specified then it must be the same as table name in SQL statement. If `schemaSource` is 'css' then `table` parameter must be specified. Table schema will be extracted from CSS in this case, `schemaSource` must not be given. """ _log.debug('request: %s', request) _log.debug('request.form: %s', request.form) _log.debug('POST => create table') # validate database name _validateDbName(dbName) # get parameters tblName = request.form.get('table', '').strip() schemaSource = request.form.get('schemaSource', 'request').strip().lower() schema = request.form.get('schema', '').strip() chunkColumns = _getArgFlag(request.form, 'chunkColumns', False) if tblName: _validateTableName(tblName) # verify parameters if schemaSource == 'css': if not tblName: raise ExceptionResponse(400, "MissingArgument", "Required `table` parameter is missing (schemaSource=css)") if schema: raise ExceptionResponse(400, "ConflictingArgument", "`schema` parameter cannot be given with schemaSource=css") elif schemaSource == 'request': if not schema: raise ExceptionResponse(400, "MissingArgument", "Required `schema` parameter is missing (schemaSource=request)") else: raise ExceptionResponse(400, "InvalidArgument", "`schemaSource` parameter has unexpected value \"%s\"" % schemaSource) dbConn = Config.instance().dbEngine().connect() # create table if schemaSource == 'request': # assume that we have correct SQL already try: utils.useDb(dbConn, dbName) utils.createTableFromSchema(dbConn, schema) except utils.TableExistsError as exc: _log.error('Exception when creating table: %s', exc) raise ExceptionResponse(409, "TableExists", "Table %s.%s already exists" % (dbName, tblName)) elif schemaSource == 'css': # get table schema from CSS try: css = Config.instance().cssAccess() schema = css.getTableSchema(dbName, tblName) _log.debug('schema from CSS: %s', schema) except css.CssError as exc: _log.error('Failed to retrieve table schema from CSS: %s', exc) raise ExceptionResponse(500, "CSSError", "Failed to retrieve table schema from CSS", str(exc)) # schema in CSS is stored without CREATE TABLE, so we are already OK try: utils.createTable(dbConn, tblName, schema, dbName) except TableExistsError as exc: _log.error('Exception when creating table: %s', exc) raise ExceptionResponse(409, "TableExists", "Table %s.%s already exists" % (dbName, tblName)) _log.debug('table %s.%s created succesfully', dbName, tblName) if chunkColumns: # Change table schema, drop _chunkId, _subChunkId, add chunkId, subChunkId try: table = '`{0}`.`{1}`'.format(dbName, tblName) # get current column set q = "SHOW COLUMNS FROM %s" % table result = dbConn.execute(q) rows = result.fetchall() columns = set(row[0] for row in rows) # delete rows toDelete = set(["_chunkId", "_subChunkId"]) & columns mods = ['DROP COLUMN %s' % col for col in toDelete] # create rows, want them in that order toAdd = ["chunkId", "subChunkId"] mods += ['ADD COLUMN %s INT(11) NOT NULL' % col for col in toAdd if col not in columns] if mods: _log.info('Altering schema for table %s', table) q = 'ALTER TABLE %s ' % table q += ', '.join(mods) _log.debug('query: %s', q) dbConn.execute(q) except Exception as exc: _log.error('Failed to alter database table: %s', exc) raise ExceptionResponse(500, "DbError", "Failed to alter database table", str(exc)) # return representation for new database, 201 code is for CREATED response = json.jsonify(result=_tblDict(dbName, tblName)) response.status_code = 201 return response
def testMultiDbs(self): """ Try interleaving operations on multiple databases. """ 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.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()