Source Code for Module commons.sqlhash

  1  # Based on <http://code.activestate.com/recipes/576638/>. 
  2   
  3  ''' Dbm based on sqlite -- Needed to support shelves 
  4   
  5  Key and values are always stored as bytes. This means that when strings are 
  6  used they are implicitly converted to the default encoding before being 
  7  stored. 
  8   
  9  @todo Issues: 
 10      - ??? how to coordinate with whichdb 
 11      - ??? Size of text fields fixed or varchar (do we need blobs) 
 12      - ??? does default encoding affect str-->bytes or PySqlite3 always use UTF-8 
 13      - ??? if pure python overhead and pysqlite overhead is too high, rewrite in C 
 14  ''' 
 15   
 16  __all__ = ['error', 'open'] 
 17   
 18  import sqlite3, itertools, collections, sys, shelve, cPickle, threading 
 19  if sys.version_info < (3,0): 
 20    from itertools import imap as map 
 21   
 22  error = sqlite3.DatabaseError 
 23   
 24 -class SQLhash(collections.MutableMapping): 
 25   
 26 -    def __init__(self, filename=':memory:', flags='r', mode=None): 
 27          # XXX add flag/mode handling 
 28          #   c -- create if it doesn't exist 
 29          #   n -- new empty 
 30          #   w -- open existing 
 31          #   r -- readonly 
 32   
 33          MAKE_SHELF = 'CREATE TABLE IF NOT EXISTS shelf (key BLOB NOT NULL, value BLOB NOT NULL)' 
 34          MAKE_INDEX = 'CREATE UNIQUE INDEX IF NOT EXISTS keyndx ON shelf (key)' 
 35          self.conn = sqlite3.connect(filename) 
 36          self.conn.text_factory = bytes 
 37          self.conn.execute(MAKE_SHELF) 
 38          self.conn.execute(MAKE_INDEX) 
 39          self.conn.commit() 
 40   
 41 -    def __len__(self): 
 42          GET_LEN =  'SELECT COUNT(*) FROM shelf' 
 43          return self.conn.execute(GET_LEN).fetchone()[0] 
 44   
 45 -    def keys(self): 
 46          return SQLhashKeysView(self) 
 47   
 48 -    def values(self): 
 49          return SQLhashValuesView(self) 
 50   
 51 -    def items(self): 
 52          return SQLhashItemsView(self) 
 53   
 54 -    def __iter__(self): 
 55          return iter(self.keys()) 
 56   
 57 -    def __contains__(self, key): 
 58          GET_ITEM = 'SELECT value FROM shelf WHERE key = ?' 
 59          return self.conn.execute(GET_ITEM, (sqlite3.Binary(key),)).fetchone() is not None 
 60   
 61 -    def __getitem__(self, key): 
 62          GET_ITEM = 'SELECT value FROM shelf WHERE key = ?' 
 63          item = self.conn.execute(GET_ITEM, (sqlite3.Binary(key),)).fetchone() 
 64          if item is None: 
 65              raise KeyError(key) 
 66          return str(item[0]) 
 67   
 68 -    def __setitem__(self, key, value):        
 69          ADD_ITEM = 'REPLACE INTO shelf (key, value) VALUES (?,?)' 
 70          self.conn.execute(ADD_ITEM, (sqlite3.Binary(key), sqlite3.Binary(value))) 
 71          self.conn.commit() 
 72   
 73 -    def __delitem__(self, key): 
 74          if key not in self: 
 75              raise KeyError(key) 
 76          DEL_ITEM = 'DELETE FROM shelf WHERE key = ?'        
 77          self.conn.execute(DEL_ITEM, (sqlite3.Binary(key),)) 
 78          self.conn.commit() 
 79   
 80 -    def update(self, items=(), **kwds): 
 81          if isinstance(items, collections.Mapping): 
 82              items = items.items() 
 83          items = ((sqlite3.Binary(k),sqlite3.Binary(v)) for k,v in items) 
 84          UPDATE_ITEMS = 'REPLACE INTO shelf (key, value) VALUES (?, ?)' 
 85          self.conn.executemany(UPDATE_ITEMS, items) 
 86          self.conn.commit() 
 87          if kwds: 
 88              self.update(kwds) 
 89   
 90 -    def clear(self):         
 91          CLEAR_ALL = 'DELETE FROM shelf;  VACUUM;'         
 92          self.conn.executescript(CLEAR_ALL) 
 93          self.conn.commit() 
 94   
 95 -    def close(self): 
 96          if self.conn is not None: 
 97              self.conn.commit() 
 98              self.conn.close() 
 99              self.conn = None 
100   
101 -    def __del__(self): 
102          self.close()     
103   
104 -class ListRepr: 
105   
106 -    def __repr__(self): 
107          return repr(list(self))     
108   
109 -class SQLhashKeysView(collections.KeysView, ListRepr): 
110       
111 -    def __iter__(self): 
112          GET_KEYS = 'SELECT key FROM shelf ORDER BY ROWID' 
113          return (str(row[0]) for row in self._mapping.conn.cursor().execute(GET_KEYS)) 
114   
115 -class SQLhashValuesView(collections.ValuesView, ListRepr): 
116       
117 -    def __iter__(self): 
118          GET_VALUES = 'SELECT value FROM shelf ORDER BY ROWID' 
119          return (str(row[0]) for row in self._mapping.conn.cursor().execute(GET_VALUES)) 
120   
121 -class SQLhashItemsView(collections.ValuesView, ListRepr): 
122       
123 -    def __iter__(self): 
124          GET_ITEMS = 'SELECT key, value FROM shelf ORDER BY ROWID' 
125          return ((str(k), str(v)) for k,v in 
126                  iter(self._mapping.conn.cursor().execute(GET_ITEMS))) 
127   
128 -def open(file=None, *args): 
129      if file is not None: 
130          return SQLhash(file) 
131      return SQLhash() 
132   
133 -class Shelf(shelve.Shelf): 
134 -  def __init__(self, *args, **okwargs): 
135      kwargs = okwargs.copy() 
136      try: del kwargs['cache'] 
137      except KeyError: pass 
138      shelve.Shelf.__init__(self, *args, **kwargs) 
139      if not okwargs.get('cache', True): self.cache = None 
140 -  def __getitem__(self, k): 
141      val = lambda: cPickle.loads(self.dict[k]) 
142      if self.cache is None: 
143        return val() 
144      else: 
145        try: return self.cache[k] 
146        except KeyError: return self.cache.setdefault(k, val()) 
147 -  def __setitem__(self, key, value): 
148      if self.cache is not None and self.writeback: self.cache[key] = value 
149      else: self.dict[key] = cPickle.dumps(value, self._protocol) 
150 -  def __delitem__(self, key): 
151      try: 
152        del self.dict[key] 
153      except: 
154        if not self.writeback: raise 
155        # If writeback is enabled then it's OK if it's missing in the 
156        # underlying dict but present in the cache; it just means that 
157        # we only recently inserted it so it's only in the cache.  If 
158        # it's not in the cache either then we should end with a 
159        # KeyError. 
160        del self.cache[key] 
161      else: 
162        # Also make sure it's removed from the cache. 
163        try: del self.cache[key] 
164        except KeyError: pass 
165 -  def iteritems(self): 
166      return ((k, cPickle.loads(v)) for k,v in self.dict.items()) 
167 -  def sync(self): 
168      if self.cache: 
169        self.dict.update( (k, cPickle.dumps(v, self._protocol)) for k,v in self.cache.items() ) 
170        self.cache.clear() 
171   
172  # Attempt to make a *safe* background-writeback Shelf is hard. 
173   
174  #class Shelf(shelve.Shelf): 
175  #  def __init__(self, *args, **kwargs): 
176  #    shelve.Shelf.__init__(self, *args, **kwargs) 
177  #    threading.Thread(target = self.syncer_proc).start() 
178  #    self.syncer_queue = Queue.Queue() 
179  #  def syncer_proc(self): 
180  #    while True: 
181  #      cache = self.syncer_queue.get() 
182  #      if cache is None: break 
183  #      self.dict.update( (k, cPickle.dumps(v, self._protocol)) for k,v in self.cache.items() ) 
184  #  def __setitem__(self, key, value): 
185  #    if self.writeback: self.cache[key] = value 
186  #    else: self.dict[key] = cPickle.dumps(value, self._protocol) 
187  #  def __delitem__(self, key): 
188  #    try: 
189  #      del self.dict[key] 
190  #    except: 
191  #      del self.cache[key] 
192  #    else: 
193  #      try: del self.cache[key] 
194  #      except KeyError: pass 
195  #  def sync(self): 
196  #    if self.cache: 
197  #      self.syncer_queue.push(self.cache) 
198  #      self.cache = {} 
199  #      with self.syncer_lock: 
200  #        while self.syncer_busy: self.syncer_free.wait() 
201  #      self.dict.update( (k, cPickle.dumps(v, self._protocol)) for k,v in self.cache.items() ) 
202   
203  if __name__ in '__main___': 
204      for d in SQLhash(), SQLhash('example'): 
205          print(list(d), "start") 
206          d['abc'] = 'lmno' 
207          print(d['abc'])     
208          d['abc'] = 'rsvp' 
209          d['xyz'] = 'pdq' 
210          print(d.items()) 
211          print(d.values()) 
212          print(d.keys()) 
213          print(list(d), 'list') 
214          d.update(p='x', q='y', r='z') 
215          print(d.items()) 
216           
217          del d['abc'] 
218          try: 
219              print(d['abc']) 
220          except KeyError: 
221              pass 
222          else: 
223              raise Exception('oh noooo!') 
224           
225          try: 
226              del d['abc'] 
227          except KeyError: 
228              pass 
229          else: 
230              raise Exception('drat!') 
231   
232          print(list(d)) 
233          d.clear() 
234          print(list(d)) 
235          d.update(p='x', q='y', r='z') 
236          print(list(d)) 
237          d['xyz'] = 'pdq' 
238   
239          print() 
240          d.close() 
241