summaryrefslogblamecommitdiffstats
path: root/storage/sqlite.py
blob: bfdae4227015dce8fd126f7565b7d15c20e207c5 (plain) (tree)
1
2
3
4
5
6
7
8
9

                       

                                   
                     


                                             
                     
 

                                                                              











                                                                                                     
                                   






















                                                                                                              
 
                                    

                                                           


                                                
                                 
 






                                                                                                 

                         



                        


                                                                 
                                         

                    

                                   
                               

                                                                                                                                                              

                             

                                                                                                                                              

                                                                                      
# -*- 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.3'

    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
        )''')

        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',)

        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').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=? where id=?',
                             (sender.getTitle(), sender.getCreatedAt(), sender.getPriority(), sender.getDescription(), sender.getCompleted(), sender.getId()))
            self.con.commit()
        else:      
            self.cur.execute('insert into todo (title, createdAt, priority, desc, completed) VALUES (?, ?, ?, ?, ?)',
                             (sender.getTitle(), sender.getCreatedAt(), sender.getPriority(), sender.getDescription(), sender.getCompleted()))
            self.con.commit()
            sender.setId(self.cur.execute('select last_insert_rowid()').fetchone()[0])