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