1#!/usr/local/bin/python3.8 2# -*- coding: utf-8 -*- 3# 4# gPodder - A media aggregator and podcast client 5# Copyright (c) 2005-2018 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 (thp.io/about)" 26 27# This module is also available separately at: 28# http://thp.io/2010/minidb/ 29 30 31# For Python 2.5, we need to request the "with" statement 32 33 34try: 35 import sqlite3.dbapi2 as sqlite 36except ImportError: 37 try: 38 from pysqlite2 import dbapi2 as sqlite 39 except ImportError: 40 raise Exception('Please install SQLite3 support.') 41 42 43import threading 44 45 46class Store(object): 47 def __init__(self, filename=':memory:'): 48 self.db = sqlite.connect(filename, check_same_thread=False) 49 self.lock = threading.RLock() 50 51 def _schema(self, class_): 52 return class_.__name__, list(sorted(class_.__slots__)) 53 54 def _set(self, o, slot, value): 55 # Set a slot on the given object to value, doing a cast if 56 # necessary. The value None is special-cased and never cast. 57 cls = o.__class__.__slots__[slot] 58 if value is not None: 59 if isinstance(value, bytes): 60 value = value.decode('utf-8') 61 value = cls(value) 62 setattr(o, slot, value) 63 64 def commit(self): 65 with self.lock: 66 self.db.commit() 67 68 def close(self): 69 with self.lock: 70 self.db.isolation_level = None 71 self.db.execute('VACUUM') 72 self.db.isolation_level = '' 73 self.db.close() 74 75 def _register(self, class_): 76 with self.lock: 77 table, slots = self._schema(class_) 78 cur = self.db.execute('PRAGMA table_info(%s)' % table) 79 available = cur.fetchall() 80 81 if available: 82 available = [row[1] for row in available] 83 missing_slots = (s for s in slots if s not in available) 84 for slot in missing_slots: 85 self.db.execute('ALTER TABLE %s ADD COLUMN %s TEXT' % (table, 86 slot)) 87 else: 88 self.db.execute('CREATE TABLE %s (%s)' % (table, 89 ', '.join('%s TEXT' % s for s in slots))) 90 91 def convert(self, v): 92 if isinstance(v, str): 93 return v 94 elif isinstance(v, str): 95 # XXX: Rewrite ^^^ as "isinstance(v, bytes)" in Python 3 96 return v.decode('utf-8') 97 else: 98 return str(v) 99 100 def update(self, o, **kwargs): 101 self.remove(o) 102 for k, v in list(kwargs.items()): 103 setattr(o, k, v) 104 self.save(o) 105 106 def save(self, o): 107 if hasattr(o, '__iter__'): 108 klass = None 109 for child in o: 110 if klass is None: 111 klass = child.__class__ 112 self._register(klass) 113 table, slots = self._schema(klass) 114 115 if not isinstance(child, klass): 116 raise ValueError('Only one type of object allowed') 117 118 used = [s for s in slots if getattr(child, s, None) is not None] 119 values = [self.convert(getattr(child, slot)) for slot in used] 120 self.db.execute('INSERT INTO %s (%s) VALUES (%s)' % (table, 121 ', '.join(used), ', '.join('?' * len(used))), values) 122 return 123 124 with self.lock: 125 self._register(o.__class__) 126 table, slots = self._schema(o.__class__) 127 128 values = [self.convert(getattr(o, slot)) for slot in slots] 129 self.db.execute('INSERT INTO %s (%s) VALUES (%s)' % (table, 130 ', '.join(slots), ', '.join('?' * len(slots))), values) 131 132 def delete(self, class_, **kwargs): 133 with self.lock: 134 self._register(class_) 135 table, slots = self._schema(class_) 136 sql = 'DELETE FROM %s' % (table,) 137 if kwargs: 138 sql += ' WHERE %s' % (' AND '.join('%s=?' % k for k in kwargs)) 139 try: 140 self.db.execute(sql, list(kwargs.values())) 141 return True 142 except Exception as e: 143 return False 144 145 def remove(self, o): 146 if hasattr(o, '__iter__'): 147 for child in o: 148 self.remove(child) 149 return 150 151 with self.lock: 152 self._register(o.__class__) 153 table, slots = self._schema(o.__class__) 154 155 # Use "None" as wildcard selector in remove actions 156 slots = [s for s in slots if getattr(o, s, None) is not None] 157 158 values = [self.convert(getattr(o, slot)) for slot in slots] 159 self.db.execute('DELETE FROM %s WHERE %s' % (table, 160 ' AND '.join('%s=?' % s for s in slots)), values) 161 162 def load(self, class_, **kwargs): 163 with self.lock: 164 self._register(class_) 165 table, slots = self._schema(class_) 166 sql = 'SELECT %s FROM %s' % (', '.join(slots), table) 167 if kwargs: 168 sql += ' WHERE %s' % (' AND '.join('%s=?' % k for k in kwargs)) 169 try: 170 cur = self.db.execute(sql, list(kwargs.values())) 171 except Exception as e: 172 raise 173 174 def apply(row): 175 o = class_.__new__(class_) 176 for attr, value in zip(slots, row): 177 try: 178 self._set(o, attr, value) 179 except ValueError as ve: 180 return None 181 return o 182 return [x for x in [apply(row) for row in cur] if x is not None] 183 184 def get(self, class_, **kwargs): 185 result = self.load(class_, **kwargs) 186 if result: 187 return result[0] 188 else: 189 return None 190 191 192if __name__ == '__main__': 193 class Person(object): 194 __slots__ = {'username': str, 'id': int} 195 196 def __init__(self, username, id): 197 self.username = username 198 self.id = id 199 200 def __repr__(self): 201 return '<Person "%s" (%d)>' % (self.username, self.id) 202 203 m = Store() 204 m.save(Person('User %d' % x, x * 20) for x in range(50)) 205 206 p = m.get(Person, id=200) 207 print(p) 208 m.remove(p) 209 p = m.get(Person, id=200) 210 211 # Remove some persons again (deletion by value!) 212 m.remove(Person('User %d' % x, x * 20) for x in range(40)) 213 214 class Person(object): 215 __slots__ = {'username': str, 'id': int, 'mail': str} 216 217 def __init__(self, username, id, mail): 218 self.username = username 219 self.id = id 220 self.mail = mail 221 222 def __repr__(self): 223 return '<Person "%s" (%s)>' % (self.username, self.mail) 224 225 # A schema update takes place here 226 m.save(Person('User %d' % x, x * 20, 'user@home.com') for x in range(50)) 227 print(m.load(Person)) 228