src/gpodder/minidb.py

   1 #!/usr/bin/python
   2 # -*- coding: utf-8 -*-
   3 #
   4 # gPodder - A media aggregator and podcast client
   5 # Copyright (c) 2005-2010 Thomas Perl and the gPodder Team
   6 #
   7 # gPodder is free software; you can redistribute it and/or modify
   8 # it under the terms of the GNU General Public License as published by
   9 # the Free Software Foundation; either version 3 of the License, or
  10 # (at your option) any later version.
  11 #
  12 # gPodder is distributed in the hope that it will be useful,
  13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 # GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with this program.  If not, see <http://www.gnu.org/licenses/>.
  19 #
  20
  21 # gpodder.minidb - A simple SQLite store for Python objects
  22 # Thomas Perl, 2010-01-28
  23
  24 # based on: "ORM wie eine Kirchenmaus - a very poor ORM implementation
  25 #            by thp, 2009-11-29 (thpinfo.com/about)"
  26
  27
  28 # For Python 2.5, we need to request the "with" statement
  29 from __future__ import with_statement
  30
  31 try:
  32     import sqlite3.dbapi2 as sqlite
  33 except ImportError:
  34     try:
  35         from pysqlite2 import dbapi2 as sqlite
  36     except ImportError:
  37         raise Exception('Please install SQLite3 support.')
  38
  39
  40 import threading
  41
  42 class Store(object):
  43     def __init__(self, filename=':memory:'):
  44         self.db = sqlite.connect(filename, check_same_thread=False)
  45         self.lock = threading.RLock()
  46
  47     def _schema(self, class_):
  48         return class_.__name__, list(sorted(class_.__slots__))
  49
  50     def _set(self, o, slot, value):
  51         # Set a slot on the given object to value, doing a cast if
  52         # necessary. The value None is special-cased and never cast.
  53         cls = o.__class__.__slots__[slot]
  54         if value is not None:
  55             if isinstance(value, unicode):
  56                 value = value.decode('utf-8')
  57             value = cls(value)
  58         setattr(o, slot, value)
  59
  60     def commit(self):
  61         with self.lock:
  62             self.db.commit()
  63
  64     def close(self):
  65         with self.lock:
  66             self.db.close()
  67
  68     def _register(self, class_):
  69         with self.lock:
  70             table, slots = self._schema(class_)
  71             cur = self.db.execute('PRAGMA table_info(%s)' % table)
  72             available = cur.fetchall()
  73
  74             if available:
  75                 available = [row[1] for row in available]
  76                 missing_slots = (s for s in slots if s not in available)
  77                 for slot in missing_slots:
  78                     self.db.execute('ALTER TABLE %s ADD COLUMN %s TEXT' % (table,
  79                         slot))
  80             else:
  81                 self.db.execute('CREATE TABLE %s (%s)' % (table,
  82                         ', '.join('%s TEXT'%s for s in slots)))
  83
  84     def convert(self, v):
  85         if isinstance(v, str) or isinstance(v, unicode):
  86             return v
  87         else:
  88             return str(v)
  89
  90     def update(self, o, **kwargs):
  91         self.remove(o)
  92         for k, v in kwargs.items():
  93             setattr(o, k, v)
  94         self.save(o)
  95
  96     def save(self, o):
  97         if hasattr(o, '__iter__'):
  98             for child in o:
  99                 self.save(child)
 100             return
 101
 102         with self.lock:
 103             self._register(o.__class__)
 104             table, slots = self._schema(o.__class__)
 105
 106             # Only save values that have values set (non-None values)
 107             slots = [s for s in slots if getattr(o, s, None) is not None]
 108
 109             values = [self.convert(getattr(o, slot)) for slot in slots]
 110             self.db.execute('INSERT INTO %s (%s) VALUES (%s)' % (table,
 111                 ', '.join(slots), ', '.join('?'*len(slots))), values)
 112
 113     def delete(self, class_, **kwargs):
 114         with self.lock:
 115             self._register(class_)
 116             table, slots = self._schema(class_)
 117             sql = 'DELETE FROM %s' % (table,)
 118             if kwargs:
 119                 sql += ' WHERE %s' % (' AND '.join('%s=?' % k for k in kwargs))
 120             try:
 121                 self.db.execute(sql, kwargs.values())
 122                 return True
 123             except Exception, e:
 124                 return False
 125
 126     def remove(self, o):
 127         if hasattr(o, '__iter__'):
 128             for child in o:
 129                 self.remove(child)
 130             return
 131
 132         with self.lock:
 133             self._register(o.__class__)
 134             table, slots = self._schema(o.__class__)
 135
 136             # Use "None" as wildcard selector in remove actions
 137             slots = [s for s in slots if getattr(o, s, None) is not None]
 138
 139             values = [self.convert(getattr(o, slot)) for slot in slots]
 140             self.db.execute('DELETE FROM %s WHERE %s' % (table,
 141                 ' AND '.join('%s=?'%s for s in slots)), values)
 142
 143     def load(self, class_, **kwargs):
 144         with self.lock:
 145             self._register(class_)
 146             table, slots = self._schema(class_)
 147             sql = 'SELECT %s FROM %s' % (', '.join(slots), table)
 148             if kwargs:
 149                 sql += ' WHERE %s' % (' AND '.join('%s=?' % k for k in kwargs))
 150             try:
 151                 cur = self.db.execute(sql, kwargs.values())
 152             except Exception, e:
 153                 raise
 154             def apply(row):
 155                 o = class_.__new__(class_)
 156                 for attr, value in zip(slots, row):
 157                     try:
 158                         self._set(o, attr, value)
 159                     except ValueError, ve:
 160                         return None
 161                 return o
 162             return filter(lambda x: x is not None, [apply(row) for row in cur])
 163
 164     def get(self, class_, **kwargs):
 165         result = self.load(class_, **kwargs)
 166         if result:
 167             return result[0]
 168         else:
 169             return None
 170
 171 if __name__ == '__main__':
 172     class Person(object):
 173         __slots__ = {'username': str, 'id': int}
 174
 175         def __init__(self, username, id):
 176             self.username = username
 177             self.id = id
 178
 179         def __repr__(self):
 180             return '<Person "%s" (%d)>' % (self.username, self.id)
 181
 182     m = Store()
 183     m.save(Person('User %d' % x, x*20) for x in range(50))
 184
 185     p = m.get(Person, id=200)
 186     print p
 187     m.remove(p)
 188     p = m.get(Person, id=200)
 189
 190     # Remove some persons again (deletion by value!)
 191     m.remove(Person('User %d' % x, x*20) for x in range(40))
 192
 193     class Person(object):
 194         __slots__ = {'username': str, 'id': int, 'mail': str}
 195
 196         def __init__(self, username, id, mail):
 197             self.username = username
 198             self.id = id
 199             self.mail = mail
 200
 201         def __repr__(self):
 202             return '<Person "%s" (%s)>' % (self.username, self.mail)
 203
 204     # A schema update takes place here
 205     m.save(Person('User %d' % x, x*20, 'user@home.com') for x in range(50))
 206     print m.load(Person)
 207