Create a gist now

Instantly share code, notes, and snippets.

@coleifer /virtual_tables.py Secret
Last active Apr 11, 2016

What would you like to do?
# SUPER HACKY, WARNING!
import apsw
import httplib2
import json
import pickle
import urllib
conn = apsw.Connection(':memory:')
class RestSource(object):
def __init__(self, url, *filter_cols):
self.url = url
self.filter_cols = filter_cols
def Create(self, db, modulename, dbname, tablename, *args):
self.columns = args
self.tablename = 'rest_%s' % tablename
schema = 'create table %s (%s);' % (self.tablename, ', '.join(self.columns))
return schema, Table(self.tablename, self.columns, self.url, self.filter_cols)
Connect = Create
class Table(object):
def __init__(self, table, columns, url, filter_cols):
self.table = table
self.columns = columns
self.url = url
self.filter_cols = filter_cols
def BestIndex(self, constraints, orderbys):
"""
Example query: select * from foo where price > 74.99 and quantity<=10 and customer='Acme Widgets'
If customer is column 0, price column 2 and quantity column 5 then the constraints will be:
(2, apsw.SQLITE_INDEX_CONSTRAINT_GT),
(5, apsw.SQLITE_INDEX_CONSTRAINT_LE),
(0, apsw.SQLITE_INDEX_CONSTRAINT_EQ)
"""
filter_constraints = []
for col_idx, cmp_op in constraints:
if self.columns[col_idx] in self.filter_cols:
filter_constraints.append((col_idx, cmp_op))
if filter_constraints:
return (range(len(filter_constraints)), None, pickle.dumps(filter_constraints), False, 1000)
return None, None, pickle.dumps(None), False, 1000
def Open(self):
return Cursor(self.url, self.columns)
def Disconnect(self):
pass
Destroy=Disconnect
class Cursor(object):
constraint_mapping = {
apsw.SQLITE_INDEX_CONSTRAINT_EQ: 'eq',
apsw.SQLITE_INDEX_CONSTRAINT_GT: 'gt',
apsw.SQLITE_INDEX_CONSTRAINT_LE: 'le',
apsw.SQLITE_INDEX_CONSTRAINT_LT: 'lt',
apsw.SQLITE_INDEX_CONSTRAINT_GE: 'ge',
}
def __init__(self, url, columns):
self.url = url
self.columns = columns
def load_data(self, **query):
sock = httplib2.Http()
full_url = '%s%s%s' % (self.url, '?' in self.url and '&' or '?', urllib.urlencode(query))
headers, resp = sock.request(full_url)
data = json.loads(resp)
return [[obj[k] for k in self.columns] for obj in data['objects']]
def Filter(self, best_index, index_name, index_data):
self.pos = 0
# we've serialized the query into index_name, so deserialize it and build
# up a query
constraints = pickle.loads(index_name)
query = {}
for i, piece in enumerate(index_data):
col_idx, cmp_op = constraints[i]
if cmp_op in self.constraint_mapping:
query['%s__%s' % (self.columns[col_idx], self.constraint_mapping[cmp_op])] = piece
self._data = self.load_data(**query)
def Eof(self):
return self.pos >= len(self._data)
def Rowid(self):
return self._data[self.pos][0]
def Column(self, col):
return self._data[self.pos][col]
def Next(self):
self.pos += 1
def Close(self):
pass
# Register the module as rest
url = 'http://readthedocs.org/api/v1/project/?format=json&limit=1000'
conn.createmodule("rest", RestSource(url))
cursor = conn.cursor()
cursor.execute("create virtual table project_api using rest(id, absolute_url, description, name, project_url, pub_date, repo);")
for name, repo in cursor.execute("select name, repo from project_api order by name asc limit 100"):
print name, repo
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment