path: root/storage/sqlite.py

# -*- coding: utf-8 -*-

from storageBase import storageBase
from itemList import itemList
from item import item
from pysqlite2 import dbapi2 as sqliteBackend

class sqlite(storageBase):
    dbVersion = '0.5'

    def __init__(self):
        self.con = sqliteBackend.connect(self.getConfigDir() + '/data.sqlite')
        self.cur = self.con.cursor()

        # wenn todo tabelle noch nicht exsistiert, dann sollten wir sie anlegen
        cur = self.cur.execute("select name from sqlite_master where type='table';")
        tables = cur.fetchall()
        if ('todo',) not in tables or ('control',) not in tables:
            self.init_db()

        # überprüfung ob die db-version identisch ist
        version = self.cur.execute("select value from control where setting='db-version'").fetchone()
        if (sqlite.dbVersion,) != version:
            # TODO: update not init
            self.update_db(version)
            


    def init_db(self):
        print '(re)create todo table...'
        self.cur.execute('drop table if exists todo')
        self.cur.execute('''create table todo (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT(255),
                createdAt INTEGER,
                priority INTEGER,
                desc BLOB,
                completed INTEGER(1) DEFAULT (0),
                removed INTEGER(1) DEFAULT (0),
                add due INTEGER DEFAULT (0)
        )''')

        self.cur.execute('drop table if exists control')
        self.cur.execute('''create table control (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                setting TEXT,
                value TEXT
        )''')

        self.cur.execute("insert into control (setting, value) VALUES ('db-version', ?)", (sqlite.dbVersion,))
        self.con.commit()

    def update_db(self, updateFrom):
        print 'updating todo table from %s...' % updateFrom
        if updateFrom == ('0.1',):
            self.cur.execute('''alter table todo
                add desc BLOB''')
            self.con.commit()
            updateFrom = ('0.2',)

        if updateFrom == ('0.2',):
            self.cur.execute('''alter table todo
                add completed INTEGER(1) DEFAULT (0)''')
            self.con.commit()
            updateFrom = ('0.3',)

        if updateFrom == ('0.3',):
            self.cur.execute('''alter table todo
                add removed INTEGER(1) DEFAULT (0)''')
            self.con.commit()
            updateFrom = ('0.4',)

        if updateFrom == ('0.4',):
            self.cur.execute('''alter table todo
                add due INTEGER DEFAULT (0)''')
            self.con.commit()
            updateFrom = ('0.5',)

        self.cur.execute("update control set value = ? where setting = 'db-version'", updateFrom)
        self.con.commit()

    def __del__(self):
        self.con.close()
        
    def load(self):
        items = itemList(self)
        todos = self.cur.execute('select * from todo where removed=0').fetchall()
        for todo in todos:
            items += item(row=list(todo))
            
        return items

    def notifyChange(self, sender):
        if sender.getId() >= 0:
            self.cur.execute('update todo set title=?, createdAt=?, priority=?, desc=?, completed=?, removed=?, due=? where id=?',
                             (sender.getTitle(), sender.getCreatedAt(), sender.getPriority(),
                              sender.getDescription(), sender.getCompleted(), sender.getRemoved(), sender.getDue(), sender.getId()))
            self.con.commit()
        else:      
            self.cur.execute('insert into todo (title, createdAt, priority, desc, completed, removed, due) VALUES (?, ?, ?, ?, ?, ?, ?)',
                             (sender.getTitle(), sender.getCreatedAt(), sender.getPriority(),
                              sender.getDescription(), sender.getCompleted(), sender.getRemoved(), sender.getDue()))
            self.con.commit()
            sender.setId(self.cur.execute('select last_insert_rowid()').fetchone()[0])