1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
# -*- 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),
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().getId(),
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().getId(),
sender.getDescription(), sender.getCompleted(), sender.getRemoved(), sender.getDue()))
self.con.commit()
sender.setId(self.cur.execute('select last_insert_rowid()').fetchone()[0])
|
