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