QBqUSeZY

1"""
2Author: William Roscoe
3Date: 2010.1
4This is an attempt at a flat database with single and dynamically created indexes to entity ids. 
5If you know of a better or comprable simple system please let me know.
6
7This idea is a simple mimic of Google's BigFile system and Friend Feed's python/mysql implementation.
8"""
9
10import os, cPickle, time, sqlite3, uuid
11import config
12
13INDEX_PREFIX = "index_"        
14
15class Datastore():
16    """Datastore is a way to store data in a blob it is called through indexes"""
17    def __init__(self, path, indexed_properties):
18        self.conn = sqlite3.connect(path)
19        self.indexed_properties = indexed_properties
20
21        #create datastore and index tables if they don't exist
22        c = self.conn.cursor()
23        if not self.table_exists('datastore'):
24            c.execute('create table datastore ( entity_id TEXT, blob BLOB, updated INTEGER)' ) 
25        for property_name in indexed_properties:
26            if not self.table_exists(INDEX_PREFIX+property_name):
27                c.execute('create table %s ( entity_id TEXT, %s TEXT)' %(INDEX_PREFIX+property_name, property_name) )
28        self.conn.commit()    
29                
30    def table_exists(self, table_name):
31        """check if table_name exists"""
32        c = self.conn.cursor()
33        c.execute('SELECT name from sqlite_master WHERE name=?', [table_name])
34        if  c.fetchone() > 0:
35            return True
36        return False
37        
38    def put(self, new_entity):
39        """Insert new_entity into datastore table and add indexed properties to index tables.
40        Create a unique entity id with uuid1 and an integer time value."""
41        new_entity['id'] = uuid.uuid1().get_hex()
42        new_entity['updated'] =  int(time.time())
43        c = self.conn.cursor()
44        c.execute('INSERT INTO datastore(entity_id, blob, updated) VALUES(?, ?, ?);', [new_entity['id'], cPickle.dumps(new_entity), int(time.time()) ]  )
45        self.conn.commit()
46        
47        for property_name in self.indexed_properties:        
48            if new_entity.has_key(property_name):
49                self.put_in_index(property_name, new_entity['id'], new_entity[property_name])
50        
51        return new_entity['id']
52    
53    def put_in_index(self, property_name, id, property_value):
54        """put an (entity_id, property_name) row into the property's index"""
55        query = 'INSERT INTO %s (entity_id, %s) VALUES(?, ?);'%(INDEX_PREFIX+property_name, property_name)
56        c = self.conn.cursor()
57        c.execute(query, [id, property_value])
58        self.conn.commit()
59
60    def get_id(self, id):
61        """SELECT an entity with its id"""
62        c = self.conn.cursor()
63        c.execute('SELECT blob FROM datastore WHERE entity_id = ?', [id])
64        self.conn.commit()
65        return cPickle.loads(str(c.fetchone()[0]))
66
67    def get_id_list(self, id_list):
68        """get a list of entities by passing a list of entity ids"""
69        in_list = self.make_list_a_string(id_list)
70        print in_list
71        c = self.conn.cursor()
72        query = 'SELECT blob FROM datastore WHERE entity_id IN (%s)' % (in_list)
73        c.execute(query)
74        self.conn.commit()
75        results = []
76        for row in c.fetchall():
77            results.append(cPickle.loads(str(row[0])))
78        return results
79    
80    def get_where(self, property_name, property_value_list):
81        """get a list of entities that match a passed list of property names"""
82        in_list = self.make_list_a_string(property_value_list)
83        c = self.conn.cursor()
84        query = 'SELECT * FROM %s WHERE %s IN (%s)' %(INDEX_PREFIX+property_name, property_name, in_list)
85        c.execute(query)
86        self.conn.commit()
87        out_list = []
88        for row in c.fetchall():
89            out_list.append(row[0])
90        print out_list
91        return self.get_id_list(out_list)
92    
93    def get_index_older(self, property_name, older):
94        """SELECT entities indexted in the properties index. Limit the number of values returned"""
95        index_name = INDEX_PREFIX+property_name
96        query = 'SELECT datastore.blob FROM datastore, %s WHERE %s.entity_id = datastore.entity_id AND updated < %s  LIMIT 20' %(index_name, index_name, older)
97        c = self.conn.cursor()
98        c.execute(query)
99        results = []
100        for row in c.fetchall():
101            results.append(cPickle.loads(str(row[0])))
102        return results
103    
104        
105        
106    def make_list_a_string(self, list1):
107        """convert a list to a string that can be inserted into an SQL "IN" statment"""
108        list2 = []            
109        for i in list1:
110            list2.append(str(i))
111        return str(list2).strip("[]")
112
113if __name__ == '__main__':
114    d = Datastore(config.PATH_DB, ['test'])
115    d.put({'test':'hello', 'other':'blob..lasdfasdf;lasdjflasd'})
116    print d.get_where('test', ['hello'])