« back to all changes in this revision
Viewing changes to hamster/db.py
- browse files at revision 1.1.4
- compare with another revision
- download diff
- download tarball
- view history from revision 1.1.4
- Committer: Bazaar Package Importer
- Author(s): Pedro Fragoso
- Date: 2008-12-15 17:42:14 UTC
- mto: (1.1.16 upstream) (20.1.1 lucid-proposed)
- mto: This revision was merged to the branch mainline in revision 6.
- Revision ID: james.westby@ubuntu.com-20081215174214-ri1ezw1c2pdnl3i6
Tags: upstream-2.25.3
Import upstream version 2.25.3
- files added:
- hamster/hamsterdbus.py
- files modified:
- ChangeLog
added
removed
hamster/db.py
21
21
22
22
"""separate file for database operations"""
23
23
24
from pysqlite2 import dbapi2 as sqlite
24
try:
25
import sqlite3 as sqlite
26
except ImportError:
27
try:
28
from pysqlite2 import dbapi2 as sqlite
29
except ImportError:
30
print "Error: Neither sqlite3 nor pysqlite2 found"
31
raise
25
32
import os, time
26
33
import datetime
27
34
import hamster
37
44
return self.fetchall("SELECT * FROM categories ORDER BY category_order")
38
45
39
46
def __change_category(self, id, category_id):
40
query = "SELECT max(activity_order) + 1 FROM activities WHERE category_id = ?"
41
max_order = self.fetchone(query, (category_id, ))[0] or 1
42
43
statement = """
44
UPDATE activities
45
SET category_id = ?, activity_order = ?
46
WHERE id = ?
47
"""
48
49
self.execute(statement, (category_id, max_order, id))
47
# first check if we don't have an activity with same name before us
48
activity = self.fetchone("select name from activities where id = ?", (id, ))
49
existing_id = self.__get_activity_by_name(activity['name'], category_id)
50
51
if existing_id: #ooh, we have something here!
52
# first move all facts that belong to movable activity to the new one
53
update = """
54
UPDATE facts
55
SET activity_id = ?
56
WHERE activity_id = ?
57
"""
58
59
self.execute(update, (existing_id, id))
60
61
# and now get rid of our friend
62
self.__remove_activity(id)
63
64
else: #just moving
65
query = "SELECT max(activity_order) + 1 FROM activities WHERE category_id = ?"
66
max_order = self.fetchone(query, (category_id, ))[0] or 1
67
68
statement = """
69
UPDATE activities
70
SET category_id = ?, activity_order = ?
71
WHERE id = ?
72
"""
73
74
self.execute(statement, (category_id, max_order, id))
50
75
51
def __insert_category(self, name):
76
def __add_category(self, name):
52
77
new_rec = self.fetchone("select max(id) +1, max(category_order) + 1 from categories")
53
78
54
79
id, order = new_rec[0] or 1, new_rec[1] or 1
88
113
89
114
90
115
91
def __get_activity_by_name(self, name):
116
def __get_activity_by_name(self, name, category_id = None):
92
117
"""get most recent, preferably not deleted activity by it's name"""
118
119
if category_id:
120
query = """
121
SELECT id, deleted from activities
122
WHERE lower(name) = lower(?)
123
AND category_id = ?
124
ORDER BY deleted, id desc
125
LIMIT 1
126
"""
127
128
res = self.fetchone(query, (name, category_id))
129
else:
130
query = """
131
SELECT id, deleted from activities
132
WHERE lower(name) = lower(?)
133
ORDER BY deleted, id desc
134
LIMIT 1
135
"""
136
137
res = self.fetchone(query, (name,))
138
139
if res:
140
# if the activity was marked as deleted, ressurect on first call
141
# and put in the unsorted category
142
if res['deleted']:
143
update = """
144
UPDATE activities
145
SET deleted = null, category_id = -1
146
WHERE id = ?
147
"""
148
self.execute(update, (res['id'], ))
149
150
return res['id']
151
152
return None
153
154
def __get_category_by_name(self, name):
155
"""returns category by it's name"""
156
93
157
query = """
94
SELECT id from activities
158
SELECT id from categories
95
159
WHERE lower(name) = lower(?)
96
ORDER BY deleted, id desc
160
ORDER BY id desc
97
161
LIMIT 1
98
162
"""
99
100
res = self.fetchone(query, (name,))
163
164
res = self.fetchone(query, (name, ))
101
165
102
166
if res:
103
167
return res['id']
108
172
query = """SELECT a.id AS id,
109
173
a.start_time AS start_time,
110
174
a.end_time AS end_time,
175
a.description as description,
111
176
b.name AS name, b.id as activity_id
112
177
FROM facts a
113
178
LEFT JOIN activities b ON a.activity_id = b.id
120
185
SELECT a.id AS id,
121
186
a.start_time AS start_time,
122
187
a.end_time AS end_time,
188
a.description as description,
123
189
b.name AS name, b.id as activity_id
124
190
FROM facts a
125
191
LEFT JOIN activities b ON a.activity_id = b.id
144
210
def __add_fact(self, activity_name, start_time = None, end_time = None):
145
211
start_time = start_time or datetime.datetime.now()
146
212
147
# try to lookup activity by it's name in db. active ones have priority
148
activity_id = self.__get_activity_by_name(activity_name)
213
#see if we have description of activity somewhere here (delimited by comma)
214
description = None
215
if activity_name.find(",") > 0:
216
activity_name, description = activity_name.split(",", 1)
217
description = description.strip()
218
219
# now check if maybe there is also a category
220
category_id = None
221
if activity_name.find("@") > 0:
222
#at symbol marks category
223
activity_name, category_name = activity_name.split("@", 1)
224
225
if category_name:
226
category_name = category_name.strip()
227
category_id = self.__get_category_by_name(category_name)
228
if not category_id:
229
category_id = self.__add_category(category_name)
230
231
# try to find activity
232
activity_id = self.__get_activity_by_name(activity_name, category_id)
149
233
150
234
if not activity_id:
151
activity_id = self.__insert_activity(activity_name)
235
activity_id = self.__add_activity(activity_name, category_id)
152
236
153
237
154
238
# now fetch facts for the specified day and check if we have to
219
303
220
304
221
305
# finally add the new entry
222
if end_time:
223
insert = """
224
INSERT INTO facts (activity_id, start_time, end_time)
225
VALUES (?, ?, ?)
226
"""
227
self.execute(insert, (activity_id, start_time, end_time))
228
else:
229
insert = """
230
INSERT INTO facts (activity_id, start_time)
231
VALUES (?, ?)
232
"""
233
self.execute(insert, (activity_id, start_time))
234
306
insert = """
307
INSERT INTO facts (activity_id, start_time, end_time, description)
308
VALUES (?, ?, ?, ?)
309
"""
310
self.execute(insert, (activity_id, start_time, end_time, description))
235
311
236
312
fact_id = self.fetchone("select max(id) as max_id from facts")['max_id']
237
313
243
319
SELECT a.id AS id,
244
320
a.start_time AS start_time,
245
321
a.end_time AS end_time,
322
a.description as description,
246
323
b.name AS name, b.id as activity_id,
247
324
coalesce(c.name, ?) as category, c.id as category_id
248
325
FROM facts a
262
339
"""
263
340
self.execute(query, (fact_id,))
264
341
265
def __get_sorted_activities(self):
266
"""returns list of acitivities that have categories"""
267
query = """
268
SELECT a.*, b.category_order
269
FROM activities a
270
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
271
WHERE a.category_id > -1
272
AND a.deleted is null
273
ORDER BY category_order, activity_order
274
"""
275
return self.fetchall(query)
276
277
278
342
def __get_activities(self, category_id = None):
279
343
"""returns list of activities, if category is specified, order by name
280
344
otherwise - by activity_order"""
281
345
if category_id:
282
346
query = """
283
SELECT *
284
FROM activities
347
SELECT a.*, b.name as category
348
FROM activities a
349
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
285
350
WHERE category_id = ?
286
351
AND deleted is null
287
352
"""
288
353
289
354
# unsorted entries we sort by name - others by ID
290
355
if category_id == -1:
291
query += "ORDER BY lower(name)"
356
query += "ORDER BY lower(a.name)"
292
357
else:
293
query += "ORDER BY activity_order"
358
query += "ORDER BY a.activity_order"
294
359
295
360
activities = self.fetchall(query, (category_id, ))
296
361
297
362
else:
298
363
query = """
299
SELECT *
300
FROM activities
364
SELECT a.*, b.name as category
365
FROM activities a
366
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
301
367
WHERE deleted is null
302
ORDER BY lower(name)
368
ORDER BY lower(a.name)
303
369
"""
304
370
activities = self.fetchall(query)
305
371
306
372
return activities
307
373
374
def __get_sorted_activities(self):
375
"""returns list of acitivities that have categories"""
376
query = """
377
SELECT a.*, b.name as category, b.category_order
378
FROM activities a
379
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
380
WHERE a.category_id > -1
381
AND a.deleted is null
382
ORDER BY category_order, activity_order
383
"""
384
return self.fetchall(query)
385
386
def __get_autocomplete_activities(self):
387
"""returns list of activities for autocomplete,
388
activity names converted to lowercase"""
389
390
query = """
391
SELECT lower(a.name) as name, b.name as category
392
FROM activities a
393
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
394
WHERE deleted is null
395
ORDER BY lower(a.name)
396
"""
397
activities = self.fetchall(query)
398
399
return activities
400
308
401
def __remove_activity(self, id):
309
402
""" check if we have any facts with this activity and behave accordingly
310
403
if there are facts - sets activity to deleted = True
335
428
self.execute("update activities set activity_order = ? where id = ?", (priority1, id2) )
336
429
self.execute("update activities set activity_order = ? where id = ?", (priority2, id1) )
337
430
338
def __insert_activity(self, name, category_id = -1):
431
def __add_activity(self, name, category_id = None):
432
# first check that we don't have anything like that yet
433
activity_id = self.__get_activity_by_name(name, category_id)
434
if activity_id:
435
return activity_id
436
437
#now do the create bit
438
category_id = category_id or -1
339
439
new_rec = self.fetchone("select max(id) + 1 , max(activity_order) + 1 from activities")
340
440
new_id, new_order = new_rec[0] or 1, new_rec[1] or 1
341
441
413
513
414
514
"""upgrade DB to hamster version"""
415
515
version = self.fetchone("SELECT version FROM version")["version"]
516
current_version = 5
416
517
417
518
if version < 2:
418
519
"""moving from fact_date, fact_time to start_time, end_time"""
573
674
574
675
self.execute("DROP TABLE activities")
575
676
self.execute("ALTER TABLE activities_new RENAME TO activities")
576
577
578
#lock down current version
579
self.execute("UPDATE version SET version = 4")
580
677
678
if version < 5:
679
self.execute("ALTER TABLE facts add column description varchar2")
680
681
682
# at the happy end, update version number
683
if version < current_version:
684
#lock down current version
685
self.execute("UPDATE version SET version = %d" % current_version)
686
687
581
688
582
689
"""we start with an empty database and then populate with default
583
690
values. This way defaults can be localized!"""
585
692
category_count = self.fetchone("select count(*) from categories")[0]
586
693
587
694
if category_count == 0:
588
work_cat_id = self.__insert_category(_(work_category["name"]))
695
work_cat_id = self.__add_category(_(work_category["name"]))
589
696
for entry in work_category["entries"]:
590
self.__insert_activity(_(entry), work_cat_id)
697
self.__add_activity(_(entry), work_cat_id)
591
698
592
nonwork_cat_id = self.__insert_category(_(nonwork_category["name"]))
699
nonwork_cat_id = self.__add_category(_(nonwork_category["name"]))
593
700
for entry in nonwork_category["entries"]:
594
self.__insert_activity(_(entry), nonwork_cat_id)
701
self.__add_activity(_(entry), nonwork_cat_id)
595
702
596
703
597
704
Loggerhead is a web-based interface for Breezy
Version: 2.0.1