virtual_tables.py

# 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