def upsert(user, database, table, format=None):
require(user, database, 'write', format)
try:
db = db_factory.create(user, database)
except NamingException, ne:
raise WebstoreException('Invalid DB name: %s' % ne.field,
format, state='error', code=400)
def delete(user, database, table, format=None):
require(user, database, 'delete', format)
_table = _get_table(user, database, table, format)
_table.drop()
_table.commit()
raise WebstoreException('Table dropped: %s' % table,
format, state='success', code=410)
def row(user, database, table, row, format=None):
require(user, database, 'read', format)
_table = _get_table(user, database, table, format)
try:
row = int(row)
except ValueError:
raise WebstoreException('Invalid row ID: %s' % row,
format,
state='error',
code=400)
if row == 0:
raise WebstoreException('Starting at offset 1 to allow header row',
format,
state='error',
code=400)
params, select_args = _request_query(_table, request.args, format)
select_args['limit'] = 1
select_args['offset'] = row - 1
try:
statement = _table.table.select('', **select_args)
log.debug("Read row: %s" % statement)
results = _table.bind.execute(statement)
except OperationalError, oe:
raise WebstoreException('Invalid query: %s' % oe.message,
format,
state='error',
code=400)
def upsert(user, database, table, format=None):
require(user, database, 'write', format)
try:
db = db_factory.create(user, database)
except NamingException, ne:
raise WebstoreException('Invalid DB name: %s' % ne.field,
format, state='error', code=400)
def sql(user, database, format=None):
""" Execute an SQL statement on the database. """
# TODO: do we really, really need this?
if request.content_type == 'text/sql':
query = request.data
params = None
attaches = []
elif request.content_type.lower() == 'application/json':
query = request.json.get('query', '')
params = request.json.get('params')
attaches = request.json.get('attach', [])
else:
raise WebstoreException('Only text/sql, application/json is supported',
format,
state='error',
code=400)
try:
if has(user, database, 'delete'):
db = db_factory.create(user, database)
else:
require(user, database, 'read', format)
db = db_factory.create_readonly(user, database)
except NamingException, ne:
raise WebstoreException('Invalid DB name: %s' % ne.field,
format,
state='error',
code=400)
def delete(user, database, table, format=None):
require(user, database, 'delete', format)
_table = _get_table(user, database, table, format)
_table.drop()
log.debug("Dropping: %s" % _table.table.name)
_table.commit()
raise WebstoreException('Table dropped: %s' % table,
format, state='success', code=410)
def index(user, database, format=None):
""" Give a list of all tables in the database. """
require(user, database, 'read', format)
try:
db = db_factory.create(user, database)
except NamingException, ne:
raise WebstoreException('Invalid name: %s' % ne.field,
format, state='error', code=400)
def read(user, database, table, format=None):
require(user, database, 'read', format)
_table = _get_table(user, database, table, format)
params, select_args = _request_query(_table, request.args,
format)
try:
clause = _table.args_to_clause(params)
except KeyError, ke:
raise WebstoreException('Invalid filter: %s' % ke,
format, state='error', code=400)
def schema(user, database, table, format=None):
require(user, database, 'read', format)
_table = _get_table(user, database, table, format)
schema = []
for column in _table.table.columns:
values_url = url_for('webstore.distinct', user=user, database=database,
table=table, column=column.name)
schema.append(dict(name=column.name,
values_url=values_url,
type=unicode(column.type).lower()))
return render_table(request, schema, schema[0].keys(), format)
def schema(user, database, table, format=None):
require(user, database, 'read', format)
_table = _get_table(user, database, table, format)
schema = []
for column in _table.table.columns:
values_url = url_for('webstore.distinct', user=user, database=database,
table=table, column=column.name)
schema.append(dict(name=column.name,
values_url=values_url,
type=unicode(column.type).lower()))
return render_table(request, schema, schema[0].keys(), format)
def sql(user, database, format=None):
""" Execute an SQL statement on the database. """
# TODO: do we really, really need this?
require(user, database, 'delete', format)
if request.content_type != 'text/sql':
raise WebstoreException('Only text/sql content is supported',
format, state='error', code=400)
try:
db = db_factory.create(user, database)
except NamingException, ne:
raise WebstoreException('Invalid DB name: %s' % ne.field,
format, state='error', code=400)
def update(user, database, table, format=None):
require(user, database, 'write', format)
_table = _get_table(user, database, table, format)
unique = request.args.getlist('unique')
if len(unique):
require(user, database, 'delete', format)
_table = _get_table(user, database, table, format)
reader = read_request(request, format)
try:
for row in reader:
if not len(row.keys()):
continue
if not _table.update_row(unique, row):
_table.add_row(row)
except NamingException, ne:
raise WebstoreException('Invalid column name: %s' % ne.field,
format, state='error', code=400)
def distinct(user, database, table, column, format=None):
require(user, database, 'read', format)
_table = _get_table(user, database, table, format)
if not column in _table.table.columns:
raise WebstoreException('No such column: %s' % column,
format, state='error', code=404)
params, select_args = _request_query(_table, request.args,
format)
select_args['distinct'] = True
if not len(select_args['order_by']):
select_args['order_by'].append(desc('_count'))
try:
col = _table.table.c[column]
statement = select([col, func.count(col).label('_count')],
group_by=[col], **select_args)
results = _table.bind.execute(statement)
except OperationalError, oe:
raise WebstoreException('Invalid query: %s' % oe.message,
format, state='error', code=400)
def distinct(user, database, table, column, format=None):
require(user, database, 'read', format)
_table = _get_table(user, database, table, format)
if not column in _table.table.columns:
raise WebstoreException('No such column: %s' % column,
format, state='error', code=404)
params, select_args = _request_query(_table, request.args,
format)
select_args['distinct'] = True
if not len(select_args['order_by']):
select_args['order_by'].append(desc('_count'))
try:
col = _table.table.c[column]
statement = select([col, func.count(col).label('_count')],
group_by=[col], **select_args)
log.debug("Distinct: %s" % statement)
results = _table.bind.execute(statement)
except OperationalError, oe:
raise WebstoreException('Invalid query: %s' % oe.message,
format, state='error', code=400)
def row(user, database, table, row, format=None):
require(user, database, 'read', format)
_table = _get_table(user, database, table, format)
try:
row = int(row)
except ValueError:
raise WebstoreException('Invalid row ID: %s' % row,
format, state='error', code=400)
if row == 0:
raise WebstoreException(
'Starting at offset 1 to allow header row',
format, state='error', code=400)
params, select_args = _request_query(_table, request.args, format)
select_args['limit'] = 1
select_args['offset'] = row-1
try:
statement = _table.table.select('', **select_args)
log.debug("Read row: %s" % statement)
results = _table.bind.execute(statement)
except OperationalError, oe:
raise WebstoreException('Invalid query: %s' % oe.message,
format, state='error', code=400)
def sql(user, database, format=None):
""" Execute an SQL statement on the database. """
# TODO: do we really, really need this?
if request.content_type == 'text/sql':
query = request.data
params = None
attaches = []
elif request.content_type.lower() == 'application/json':
query = request.json.get('query', '')
params = request.json.get('params')
attaches = request.json.get('attach', [])
else:
raise WebstoreException('Only text/sql, application/json is supported',
format, state='error', code=400)
try:
if has(user, database, 'delete'):
db = db_factory.create(user, database)
else:
require(user, database, 'read', format)
db = db_factory.create_readonly(user, database)
except NamingException, ne:
raise WebstoreException('Invalid DB name: %s' % ne.field,
format, state='error', code=400)
format, state='error', code=400)
try:
if has(user, database, 'delete'):
db = db_factory.create(user, database)
else:
require(user, database, 'read', format)
db = db_factory.create_readonly(user, database)
except NamingException, ne:
raise WebstoreException('Invalid DB name: %s' % ne.field,
format, state='error', code=400)
try:
connection = db.engine.connect()
for attach in attaches:
attach_user = attach.get('user', user)
attach_db = attach['database']
require(attach_user, attach_db, 'read', format)
connection = db_factory.attach(db.authorizer, connection,
attach_user, attach_db, attach.get('alias', attach_db))
params_dict = params if isinstance(params, dict) else {}
params_list = [] if isinstance(params, dict) or not params else params
log.debug("Query: %s (params: %s)" % (query, params))
results = connection.execute(query, *params_list, **params_dict)
return render_table(request, result_proxy_iterator(results),
results.keys(), format)
except NamingException, ne:
raise WebstoreException('Invalid attach DB name: %s' % ne.field,
format, state='error', code=400)
except KeyError, ke:
raise WebstoreException('Invalid attach DB: %s' % ke,
format, state='error', code=400)
except DatabaseError, de:
format, state='error', code=400)
try:
if has(user, database, 'delete'):
db = db_factory.create(user, database)
else:
require(user, database, 'read', format)
db = db_factory.create_readonly(user, database)
except NamingException, ne:
raise WebstoreException('Invalid DB name: %s' % ne.field,
format, state='error', code=400)
try:
connection = db.engine.connect()
for attach in attaches:
attach_user = attach.get('user', user)
attach_db = attach['database']
require(attach_user, attach_db, 'read', format)
connection = db_factory.attach(db.authorizer, connection,
attach_user, attach_db, attach.get('alias', attach_db))
params_dict = params if isinstance(params, dict) else {}
params_list = [] if isinstance(params, dict) or not params else params
log.debug("Query: %s (params: %s)" % (query, params))
results = connection.execute(query, *params_list, **params_dict)
return render_table(request, result_proxy_iterator(results),
results.keys(), format)
except NamingException, ne:
raise WebstoreException('Invalid attach DB name: %s' % ne.field,
format, state='error', code=400)
except KeyError, ke:
raise WebstoreException('Invalid attach DB: %s' % ke,
format, state='error', code=400)
except DatabaseError, de:
