From 42dcfdbebbbbf8d0ce7d4acdc8507a96911323e0 Mon Sep 17 00:00:00 2001
From: Alexander Sulfrian <alexander@sulfrian.net>
Date: Thu, 16 Apr 2009 19:40:39 +0200
Subject: completed storage magic

---
 storage/sqlite.py | 62 +++++++++++++++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 60 insertions(+), 2 deletions(-)

(limited to 'storage/sqlite.py')

diff --git a/storage/sqlite.py b/storage/sqlite.py
index 8a24858..be3db7f 100644
--- a/storage/sqlite.py
+++ b/storage/sqlite.py
@@ -1,17 +1,75 @@
+# -*- 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.1'
+
     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.init_db()
+            
+
+
+    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 __del__(self):
         self.con.close()
         
     def load(self):
-        return itemList(self)
+        items = itemList(self)
+        todos = self.cur.execute('select * from todo').fetchall()
+        for todo in todos:
+            items += item(row=todo)
+            
+        return items
 
     def notifyChange(self, sender):
-        print '%s %s' % ('Save changes:', sender.getCreatedAt())
+        if sender.getId() >= 0:
+            print sender.getId()
+            self.cur.execute('update todo set title=?, createdAt=?, priority=? where id=?',
+                             (sender.getTitle(), sender.getCreatedAt(), sender.getPriority(), sender.getId()))
+            self.con.commit()
+        else:      
+            self.cur.execute('insert into todo (title, createdAt, priority) VALUES (?, ?, ?)',
+                             (sender.getTitle(), sender.getCreatedAt(), sender.getPriority()))
+            self.con.commit()
+            sender.setId(self.cur.execute('select last_insert_rowid()').fetchone()[0])
+
+            
+        print '%s %s' % ('Save changes:', sender.getId())
         return
-- 
cgit v1.2.3