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