#!/usr/bin/python
# ORM wie eine Kirchenmaus - a very poor ORM implementation
# thp, 2009-11-29 (thpinfo.com/about)

import sqlite3.dbapi2 as sqlite

class Maus(object):
    def __init__(self, filename=':memory:'):
        self.db = sqlite.connect(filename)

    def _schema(self, class_):
        return class_.__name__, list(sorted(class_.__slots__))

    def _set(self, o, slot, value):
        setattr(o, slot, o.__class__.__slots__[slot](value))

    def commit(self):
        self.db.commit()

    def close(self):
        self.db.close()

    def _register(self, class_):
        table, slots = self._schema(class_)
        cur = self.db.execute('PRAGMA table_info(%s)' % table)
        available = cur.fetchall()

        if available:
            available = [row[1] for row in available]
            missing_slots = (s for s in slots if s not in available)
            for slot in missing_slots:
                self.db.execute('ALTER TABLE %s ADD COLUMN %s TEXT' % (table,
                    slot))
        else:
            self.db.execute('CREATE TABLE %s (%s)' % (table,
                    ', '.join('%s TEXT'%s for s in slots)))

    def save(self, o):
        if hasattr(o, '__iter__'):
            for child in o:
                self.save(child)
            return
        self._register(o.__class__)
        table, slots = self._schema(o.__class__)
        values = [str(getattr(o, slot)) for slot in slots]
        self.db.execute('INSERT INTO %s (%s) VALUES (%s)' % (table,
            ', '.join(slots), ', '.join('?'*len(slots))), values)

    def remove(self, o):
        if hasattr(o, '__iter__'):
            for child in o:
                self.remove(child)
            return
        self._register(o.__class__)
        table, slots = self._schema(o.__class__)
        values = [getattr(o, slot) for slot in slots]
        self.db.execute('DELETE FROM %s WHERE %s' % (table,
            ' AND '.join('%s=?'%s for s in slots)), values)

    def load(self, class_, **kwargs):
        table, slots = self._schema(class_)
        sql = 'SELECT %s FROM %s' % (', '.join(slots), table)
        if kwargs:
            sql += ' WHERE %s' % (' '.join('%s=?' % k for k in kwargs))
        cur = self.db.execute(sql, kwargs.values())
        def apply(row):
            o = class_.__new__(class_)
            for attr, value in zip(slots, row):
                self._set(o, attr, value)
            return o
        return [apply(row) for row in cur.fetchall()]

    def get(self, class_, **kwargs):
        result = self.load(class_, **kwargs)
        if result:
            return result[0]
        else:
            return None

if __name__ == '__main__':
    class Person(object):
        __slots__ = {'username': str, 'id': int}

        def __init__(self, username, id):
            self.username = username
            self.id = id

        def __repr__(self):
            return '<Person "%s" (%d)>' % (self.username, self.id)

    m = Maus()
    m.save(Person('User %d' % x, x*20) for x in range(50))

    p = m.get(Person, id=200)
    print p
    m.remove(p)
    p = m.get(Person, id=200)

    # Remove some persons again (deletion by value!)
    m.remove(Person('User %d' % x, x*20) for x in range(40))

    class Person(object):
        __slots__ = {'username': str, 'id': int, 'mail': str}

        def __init__(self, username, id, mail):
            self.username = username
            self.id = id
            self.mail = mail

        def __repr__(self):
            return '<Person "%s" (%s)>' % (self.username, self.mail)

    # A schema update takes place here
    m.save(Person('User %d' % x, x*20, 'user@home.com') for x in range(50))
    print m.load(Person)