« back to all changes in this revision
Viewing changes to hamster/db.py
- browse files at revision 19
- compare with another revision
- download diff
- download tarball
- view history from revision 19
- Committer: Bazaar Package Importer
- Author(s): Chris Coulson
- Date: 2010-02-10 02:52:31 UTC
- mfrom: (1.1.14 upstream)
- Revision ID: james.westby@ubuntu.com-20100210025231-x0q5h4q7nlvihl09
Tags: 2.29.90-0ubuntu1
* New upstream version
- workspace tracking - switch activity, when switching desktops
- chart improvements - theme friendly and less noisier
- for those without GNOME panel there is now a standalone version,
accessible via Applications -> Accessories -> Time Tracker
- overview window remembers position
- maintaining cursor on the selected row after edits / refreshes
- descriptions once again in the main input field, delimited by comma
- activity suggestion box now sorts items by recency (Patryk Zawadzki)
- searching
- simplified save report dialog, thanks to the what you see is what you
report revamp
- overview/stats replaced with activities / totals and stats accessible
from totals
- interactive graphs to drill down in totals
- miscellaneous performance improvements
- pixel-perfect graphs
* Updated 01_startup-fix.patch to apply to new source layout
* debian/control:
- Add build-depend on gnome-doc-utils
- workspace tracking - switch activity, when switching desktops
- chart improvements - theme friendly and less noisier
- for those without GNOME panel there is now a standalone version,
accessible via Applications -> Accessories -> Time Tracker
- overview window remembers position
- maintaining cursor on the selected row after edits / refreshes
- descriptions once again in the main input field, delimited by comma
- activity suggestion box now sorts items by recency (Patryk Zawadzki)
- searching
- simplified save report dialog, thanks to the what you see is what you
report revamp
- overview/stats replaced with activities / totals and stats accessible
from totals
- interactive graphs to drill down in totals
- miscellaneous performance improvements
- pixel-perfect graphs
* Updated 01_startup-fix.patch to apply to new source layout
* debian/control:
- Add build-depend on gnome-doc-utils
- files added:
- MAINTAINERS
- help
- help/C
- help/es
- src
- src/hamster
- src/hamster/keybinder
- src/hamster/widgets
- files removed:
- data/stats_overview.ui
- hamster
- hamster/keybinder
- hamster/widgets
- files modified:
- Makefile.am
added
removed
hamster/db.py
1
# - coding: utf-8 -
2
3
# Copyright (C) 2007-2009 Toms Bauģis <toms.baugis at gmail.com>
4
# Copyright (C) 2007 Patryk Zawadzki <patrys at pld-linux.org>
5
6
# This file is part of Project Hamster.
7
8
# Project Hamster is free software: you can redistribute it and/or modify
9
# it under the terms of the GNU General Public License as published by
10
# the Free Software Foundation, either version 3 of the License, or
11
# (at your option) any later version.
12
13
# Project Hamster is distributed in the hope that it will be useful,
14
# but WITHOUT ANY WARRANTY; without even the implied warranty of
15
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16
# GNU General Public License for more details.
17
18
# You should have received a copy of the GNU General Public License
19
# along with Project Hamster. If not, see <http://www.gnu.org/licenses/>.
20
21
22
"""separate file for database operations"""
23
import logging
24
25
try:
26
import sqlite3 as sqlite
27
except ImportError:
28
try:
29
logging.warn("Using sqlite2")
30
from pysqlite2 import dbapi2 as sqlite
31
except ImportError:
32
logging.error("Neither sqlite3 nor pysqlite2 found")
33
raise
34
35
import os, time
36
import datetime
37
import storage
38
import stuff
39
from shutil import copy as copyfile
40
import datetime as dt
41
import gettext
42
43
import itertools
44
45
DB_FILE = 'hamster.db'
46
47
class Storage(storage.Storage):
48
con = None # Connection will be created on demand
49
50
def __setup(self):
51
"""
52
Delayed setup so we don't do everything at the same time
53
"""
54
if self.__setup.im_func.complete:
55
return
56
57
from configuration import runtime, GconfStore
58
59
db_file = runtime.database_file
60
db_path, _ = os.path.split(os.path.realpath(db_file))
61
62
if not os.path.exists(db_path):
63
try:
64
os.makedirs(db_path, 0744)
65
except Exception, msg:
66
logging.error("could not create user dir (%s): %s" % (db_path, msg))
67
68
data_dir = runtime.data_dir
69
70
#check if db is here
71
if not os.path.exists(db_file):
72
logging.info("Database not found in %s - installing default from %s!" % (db_file, data_dir))
73
copyfile(os.path.join(data_dir, DB_FILE), db_file)
74
75
#change also permissions - sometimes they are 444
76
try:
77
os.chmod(db_file, 0664)
78
except Exception, msg:
79
logging.error("Could not change mode on %s!" % (db_file))
80
self.__setup.im_func.complete = True
81
self.run_fixtures()
82
83
84
85
__setup.complete = False
86
87
#tags, here we come!
88
def __get_tags(self, autocomplete = None):
89
query = "select * from tags"
90
if autocomplete:
91
query += " where autocomplete='true'"
92
93
query += " order by name"
94
return self.fetchall(query)
95
96
def __get_tag_ids(self, tags):
97
"""look up tags by their name. create if not found"""
98
99
# filter descriptions out, just in case they have wandered in here
100
tags = [tag for tag in tags if tag.startswith("!") == False and len(tag.split(" ")) < 3]
101
102
db_tags = self.fetchall("select * from tags where name in (%s)"
103
% ",".join(["?"] * len(tags)), tags) # bit of magic here - using sqlites bind variables
104
105
changes = False
106
107
# check if any of tags needs ressurection
108
set_complete = [str(tag["id"]) for tag in db_tags if tag["autocomplete"] == "false"]
109
if set_complete:
110
changes = True
111
self.execute("update tags set autocomplete='true' where id in (%s)" % ", ".join(set_complete))
112
113
114
found_tags = [tag["name"] for tag in db_tags]
115
116
add = set(tags) - set(found_tags)
117
if add:
118
statement = "insert into tags(name) values(?)"
119
120
self.execute([statement] * len(add), [(tag,) for tag in add])
121
122
return self.__get_tag_ids(tags)[0], True # all done, recurse
123
else:
124
return db_tags, changes
125
126
def __update_autocomplete_tags(self, tags):
127
tags = [tag.strip() for tag in tags.split(",") if tag.strip()] # split by comma
128
129
#first we will create new ones
130
tags, changes = self.__get_tag_ids(tags)
131
tags = [tag["id"] for tag in tags]
132
133
#now we will find which ones are gone from the list
134
query = """
135
SELECT b.id as id, count(a.fact_id) as occurences
136
FROM tags b
137
LEFT JOIN fact_tags a on a.tag_id = b.id
138
WHERE b.id not in (%s)
139
GROUP BY b.id
140
""" % ",".join(["?"] * len(tags)) # bit of magic here - using sqlites bind variables
141
142
gone = self.fetchall(query, tags)
143
144
to_delete = [str(tag["id"]) for tag in gone if tag["occurences"] == 0]
145
to_uncomplete = [str(tag["id"]) for tag in gone if tag["occurences"] > 0]
146
147
if to_delete:
148
self.execute("delete from tags where id in (%s)" % ", ".join(to_delete))
149
150
if to_uncomplete:
151
self.execute("update tags set autocomplete='false' where id in (%s)" % ", ".join(to_uncomplete))
152
153
154
def __get_category_list(self):
155
return self.fetchall("SELECT * FROM categories ORDER BY category_order")
156
157
def __change_category(self, id, category_id):
158
# first check if we don't have an activity with same name before us
159
activity = self.fetchone("select name from activities where id = ?", (id, ))
160
existing_id = self.__get_activity_by_name(activity['name'], category_id)
161
162
if id == existing_id: # we are already there, go home
163
return False
164
165
if existing_id: #ooh, we have something here!
166
# first move all facts that belong to movable activity to the new one
167
update = """
168
UPDATE facts
169
SET activity_id = ?
170
WHERE activity_id = ?
171
"""
172
173
self.execute(update, (existing_id, id))
174
175
# and now get rid of our friend
176
self.__remove_activity(id)
177
178
else: #just moving
179
query = "SELECT max(activity_order) + 1 FROM activities WHERE category_id = ?"
180
max_order = self.fetchone(query, (category_id, ))[0] or 1
181
182
statement = """
183
UPDATE activities
184
SET category_id = ?, activity_order = ?
185
WHERE id = ?
186
"""
187
188
self.execute(statement, (category_id, max_order, id))
189
190
return True
191
192
def __add_category(self, name):
193
new_rec = self.fetchone("select max(id) +1, max(category_order) + 1 from categories")
194
195
id, order = new_rec[0] or 1, new_rec[1] or 1
196
197
query = """
198
INSERT INTO categories (id, name, category_order)
199
VALUES (?, ?, ?)
200
"""
201
self.execute(query, (id, name, order))
202
return id
203
204
def __update_category(self, id, name):
205
if id > -1: # Update, and ignore unsorted, if that was somehow triggered
206
update = """
207
UPDATE categories
208
SET name = ?
209
WHERE id = ?
210
"""
211
self.execute(update, (name, id))
212
213
def __move_activity(self, source_id, target_order, insert_after = True):
214
statement = "UPDATE activities SET activity_order = activity_order + 1"
215
216
if insert_after:
217
statement += " WHERE activity_order > ?"
218
else:
219
statement += " WHERE activity_order >= ?"
220
221
self.execute(statement, (target_order, ))
222
223
statement = "UPDATE activities SET activity_order = ? WHERE id = ?"
224
225
if insert_after:
226
self.execute(statement, (target_order + 1, source_id))
227
else:
228
self.execute(statement, (target_order, source_id))
229
230
231
232
def __get_activity_by_name(self, name, category_id = None):
233
"""get most recent, preferably not deleted activity by it's name"""
234
235
if category_id:
236
query = """
237
SELECT id, deleted from activities
238
WHERE lower(name) = lower(?)
239
AND category_id = ?
240
ORDER BY deleted, id desc
241
LIMIT 1
242
"""
243
244
res = self.fetchone(query, (name, category_id))
245
else:
246
query = """
247
SELECT id, deleted from activities
248
WHERE lower(name) = lower(?)
249
ORDER BY deleted, id desc
250
LIMIT 1
251
"""
252
253
res = self.fetchone(query, (name, ))
254
255
if res:
256
# if the activity was marked as deleted, ressurect on first call
257
# and put in the unsorted category
258
if res['deleted']:
259
update = """
260
UPDATE activities
261
SET deleted = null, category_id = -1
262
WHERE id = ?
263
"""
264
self.execute(update, (res['id'], ))
265
266
return res['id']
267
268
return None
269
270
def __get_category_by_name(self, name):
271
"""returns category by it's name"""
272
273
query = """
274
SELECT id from categories
275
WHERE lower(name) = lower(?)
276
ORDER BY id desc
277
LIMIT 1
278
"""
279
280
res = self.fetchone(query, (name, ))
281
282
if res:
283
return res['id']
284
285
return None
286
287
def __get_fact(self, id):
288
query = """
289
SELECT a.id AS id,
290
a.start_time AS start_time,
291
a.end_time AS end_time,
292
a.description as description,
293
b.name AS name, b.id as activity_id,
294
coalesce(c.name, ?) as category, coalesce(c.id, -1) as category_id,
295
e.name as tags
296
FROM facts a
297
LEFT JOIN activities b ON a.activity_id = b.id
298
LEFT JOIN categories c ON b.category_id = c.id
299
LEFT JOIN fact_tags d ON d.fact_id = a.id
300
LEFT JOIN tags e ON e.id = d.tag_id
301
WHERE a.id = ?
302
"""
303
304
return self.__group_tags(self.fetchall(query, (_("Unsorted"), id)))[0]
305
306
def __group_tags(self, facts):
307
"""put the fact back together and move all the unique tags to an array"""
308
if not facts: return facts #be it None or whatever
309
310
grouped_facts = []
311
for fact_id, fact_tags in itertools.groupby(facts, lambda f: f["id"]):
312
fact_tags = list(fact_tags)
313
314
# first one is as good as the last one
315
grouped_fact = fact_tags[0]
316
317
# we need dict so we can modify it (sqlite.Row is read only)
318
# in python 2.5, sqlite does not have keys() yet, so we hardcode them (yay!)
319
keys = ["id", "start_time", "end_time", "description", "name",
320
"activity_id", "category", "category_id", "tags"]
321
grouped_fact = dict([(key, grouped_fact[key]) for key in keys])
322
323
grouped_fact["tags"] = [ft["tags"] for ft in fact_tags if ft["tags"]]
324
grouped_facts.append(grouped_fact)
325
return grouped_facts
326
327
def __get_last_activity(self):
328
query = """
329
SELECT a.id AS id,
330
a.start_time AS start_time,
331
a.end_time AS end_time,
332
a.description as description,
333
b.name AS name, b.id as activity_id,
334
coalesce(c.name, ?) as category, coalesce(c.id, -1) as category_id,
335
e.name as tags
336
FROM facts a
337
LEFT JOIN activities b ON a.activity_id = b.id
338
LEFT JOIN categories c ON b.category_id = c.id
339
LEFT JOIN fact_tags d ON d.fact_id = a.id
340
LEFT JOIN tags e ON e.id = d.tag_id
341
WHERE date(a.start_time) = ?
342
ORDER BY a.start_time desc
343
LIMIT 20
344
"""
345
last = self.fetchall(query, (_("Unsorted"), dt.date.today()))
346
if not last:
347
#try yesterday if there is nothing today
348
last = self.fetchall(query,
349
(# unsorted category
350
_("Unsorted"),
351
dt.date.today() - dt.timedelta(days=1)))
352
if last:
353
last = self.__group_tags(last)[0]
354
355
if last and last["end_time"]: #will consider as last only if it is going on
356
last = None
357
358
return last
359
360
def __touch_fact(self, fact, end_time):
361
# tasks under one minute do not count
362
if end_time - fact['start_time'] < datetime.timedelta(minutes = 1):
363
self.__remove_fact(fact['id'])
364
else:
365
end_time = end_time.replace(microsecond = 0)
366
query = """
367
UPDATE facts
368
SET end_time = ?
369
WHERE id = ?
370
"""
371
self.execute(query, (end_time, fact['id']))
372
373
def __squeeze_in(self, start_time):
374
# tries to put task in the given date
375
# if there are conflicts, we will only truncate the ongoing task
376
# and replace it's end part with our activity
377
378
# we are checking if our start time is in the middle of anything
379
# or maybe there is something after us - so we know to adjust end time
380
# in the latter case go only few days ahead. everything else is madness, heh
381
query = """
382
SELECT a.*, b.name
383
FROM facts a
384
LEFT JOIN activities b on b.id = a.activity_id
385
WHERE ((start_time < ? and end_time > ?)
386
OR (start_time > ? and start_time < ?))
387
ORDER BY start_time
388
LIMIT 1
389
"""
390
fact = self.fetchone(query, (start_time,
391
start_time,
392
start_time,
393
start_time + dt.timedelta(days=2)))
394
395
end_time = None
396
397
if fact:
398
if fact["end_time"] and start_time > fact["start_time"]:
399
#we are in middle of a fact - truncate it to our start
400
self.execute("UPDATE facts SET end_time=? WHERE id=?",
401
(start_time, fact["id"]))
402
403
# hamster is second-aware, but the edit dialog naturally is not
404
# so when an ongoing task is being edited, the seconds get truncated
405
# and the start time will be before previous task's end time.
406
# so set our end time only if it is not about seconds
407
if fact["end_time"].replace(second = 0) > start_time:
408
end_time = fact["end_time"]
409
else: #otherwise we have found a task that is after us
410
end_time = fact["start_time"]
411
412
return end_time
413
414
def __solve_overlaps(self, start_time, end_time):
415
"""finds facts that happen in given interval and shifts them to
416
make room for new fact"""
417
418
# this function is destructive - can't go with a wildcard
419
if not end_time or not start_time:
420
return
421
422
# activities that we are overlapping.
423
# second OR clause is for elimination - |new fact--|---old-fact--|--new fact|
424
query = """
425
SELECT a.*, b.name, c.name as category
426
FROM facts a
427
LEFT JOIN activities b on b.id = a.activity_id
428
LEFT JOIN categories c on b.category_id = c.id
429
WHERE ((start_time < ? and end_time > ?)
430
OR (start_time < ? and end_time > ?))
431
432
OR ((start_time < ? and start_time > ?)
433
OR (end_time < ? and end_time > ?))
434
ORDER BY start_time
435
"""
436
conflicts = self.fetchall(query, (start_time, start_time, end_time, end_time,
437
end_time, start_time, end_time, start_time))
438
439
for fact in conflicts:
440
# split - truncate until beginning of new entry and create new activity for end
441
if fact["start_time"] < start_time < fact["end_time"] and \
442
fact["start_time"] < end_time < fact["end_time"]:
443
444
logging.info("splitting %s" % fact["name"])
445
self.execute("""UPDATE facts
446
SET end_time = ?
447
WHERE id = ?""", (start_time, fact["id"]))
448
fact_name = fact["name"]
449
new_fact = self.__add_fact(fact["name"],
450
"", # will create tags in the next step
451
end_time,
452
fact["end_time"],
453
fact["category"],
454
fact["description"])
455
tag_update = """INSERT INTO fact_tags(fact_id, tag_id)
456
SELECT ?, tag_id
457
FROM fact_tags
458
WHERE fact_id = ?"""
459
self.execute(tag_update, (new_fact["id"], fact["id"])) #clone tags
460
461
#eliminate
462
elif fact["end_time"] and \
463
start_time < fact["start_time"] < end_time and \
464
start_time < fact["end_time"] < end_time:
465
logging.info("eliminating %s" % fact["name"])
466
self.__remove_fact(fact["id"])
467
468
# overlap start
469
elif start_time < fact["start_time"] < end_time:
470
logging.info("Overlapping start of %s" % fact["name"])
471
self.execute("UPDATE facts SET start_time=? WHERE id=?",
472
(end_time, fact["id"]))
473
474
# overlap end
475
elif start_time < fact["end_time"] < end_time:
476
logging.info("Overlapping end of %s" % fact["name"])
477
self.execute("UPDATE facts SET end_time=? WHERE id=?",
478
(start_time, fact["id"]))
479
480
481
def __add_fact(self, activity_name, tags, start_time = None,
482
end_time = None, category_name = None, description = None):
483
484
activity = stuff.parse_activity_input(activity_name)
485
486
tags = [tag.strip() for tag in tags.split(",") if tag.strip()] # split by comma
487
descriptions = [tag for tag in tags if len(tag.split(" ")) > 2 or tag.startswith("!")] #extract description
488
tags = list(set(tags) - set(descriptions)) #remove any found descriptions from tag list
489
490
# TODO - untangle descriptions - allow just one place where to enter them
491
activity.description = ", ".join(descriptions) # somebody will file bug on "why tags can't be seven words"
492
tags = self.get_tag_ids(tags) #this will create any missing tags too
493
494
if category_name:
495
activity.category_name = category_name
496
if description:
497
activity.description = description #override
498
499
start_time = activity.start_time or start_time or datetime.datetime.now()
500
501
if start_time > datetime.datetime.now():
502
return None #no facts in future, please
503
504
start_time = start_time.replace(microsecond = 0)
505
end_time = activity.end_time or end_time
506
if end_time:
507
end_time = end_time.replace(microsecond = 0)
508
509
510
# now check if maybe there is also a category
511
category_id = None
512
if activity.category_name:
513
category_id = self.__get_category_by_name(activity.category_name)
514
if not category_id:
515
category_id = self.__add_category(activity.category_name)
516
517
# try to find activity
518
activity_id = self.__get_activity_by_name(activity.activity_name,
519
category_id)
520
if not activity_id:
521
activity_id = self.__add_activity(activity.activity_name,
522
category_id)
523
524
525
# if we are working on +/- current day - check the last_activity
526
if (dt.datetime.now() - start_time <= dt.timedelta(days=1)):
527
last_activity = self.__get_last_activity()
528
529
if last_activity and last_activity['start_time'] < start_time:
530
#if this is the same, ongoing activity, then there is no need to create another one
531
if not tags and not activity.description \
532
and last_activity['activity_id'] == activity_id:
533
return last_activity
534
535
#if duration is less than a minute - it must have been a mistake
536
if not tags and not activity.description \
537
and not last_activity["description"] \
538
and 60 >= (start_time - last_activity['start_time']).seconds >= 0:
539
self.__remove_fact(last_activity['id'])
540
start_time = last_activity['start_time']
541
else:
542
#otherwise stop
543
update = """
544
UPDATE facts
545
SET end_time = ?
546
WHERE id = ?
547
"""
548
self.execute(update, (start_time, last_activity["id"]))
549
550
551
# done with the current activity, now we can solve overlaps
552
if not end_time:
553
end_time = self.__squeeze_in(start_time)
554
else:
555
self.__solve_overlaps(start_time, end_time)
556
557
558
# finally add the new entry
559
insert = """
560
INSERT INTO facts (activity_id, start_time, end_time, description)
561
VALUES (?, ?, ?, ?)
562
"""
563
self.execute(insert, (activity_id, start_time, end_time, activity.description))
564
565
fact_id = self.fetchone("select max(id) as max_id from facts")['max_id']
566
567
#now link tags
568
insert = ["insert into fact_tags(fact_id, tag_id) values(?, ?)"] * len(tags)
569
params = [(fact_id, tag["id"]) for tag in tags]
570
self.execute(insert, params)
571
572
return self.__get_fact(fact_id)
573
574
575
def __get_facts(self, date, end_date = None, category_id = None):
576
query = """
577
SELECT a.id AS id,
578
a.start_time AS start_time,
579
a.end_time AS end_time,
580
a.description as description,
581
b.name AS name, b.id as activity_id,
582
coalesce(c.name, ?) as category, coalesce(c.id, -1) as category_id,
583
e.name as tags
584
FROM facts a
585
LEFT JOIN activities b ON a.activity_id = b.id
586
LEFT JOIN categories c ON b.category_id = c.id
587
LEFT JOIN fact_tags d ON d.fact_id = a.id
588
LEFT JOIN tags e ON e.id = d.tag_id
589
WHERE (a.end_time >= ? OR a.end_time IS NULL) AND a.start_time <= ?
590
"""
591
592
if category_id and isinstance(category_id, int):
593
query += " and b.category_id = %d" % category_id
594
elif category_id and isinstance(category_id, list):
595
query += " and b.category_id IN (%s)" % (",".join([str(id) for id in category_id]))
596
597
query += " ORDER BY a.start_time"
598
end_date = end_date or date
599
600
from configuration import GconfStore
601
day_start = GconfStore().get_day_start()
602
603
split_time = day_start
604
datetime_from = dt.datetime.combine(date, split_time)
605
datetime_to = dt.datetime.combine(end_date, split_time) + dt.timedelta(days = 1)
606
607
facts = self.fetchall(query, (_("Unsorted"), datetime_from, datetime_to))
608
609
#first let's put all tags in an array
610
facts = self.__group_tags(facts)
611
612
res = []
613
for fact in facts:
614
# heuristics to assign tasks to proper days
615
616
# if fact has no end time, set the last minute of the day,
617
# or current time if fact has happened in last 24 hours
618
if fact["end_time"]:
619
fact_end_time = fact["end_time"]
620
elif (dt.date.today() - fact["start_time"].date()) <= dt.timedelta(days=1):
621
fact_end_time = dt.datetime.now().replace(microsecond = 0)
622
else:
623
fact_end_time = fact["start_time"].replace(hour=23, minute=59)
624
625
fact_start_date = fact["start_time"].date() \
626
- dt.timedelta(1 if fact["start_time"].time() < split_time else 0)
627
fact_end_date = fact_end_time.date() \
628
- dt.timedelta(1 if fact_end_time.time() < split_time else 0)
629
fact_date_span = fact_end_date - fact_start_date
630
631
# check if the task spans across two dates
632
if fact_date_span.days == 1:
633
datetime_split = dt.datetime.combine(fact_end_date, split_time)
634
start_date_duration = datetime_split - fact["start_time"]
635
end_date_duration = fact_end_time - datetime_split
636
if start_date_duration > end_date_duration:
637
# most of the task was done during the previous day
638
fact_date = fact_start_date
639
else:
640
fact_date = fact_end_date
641
else:
642
# either doesn't span or more than 24 hrs tracked
643
# (in which case we give up)
644
fact_date = fact_start_date
645
646
if fact_date < date or fact_date > end_date:
647
# due to spanning we've jumped outside of given period
648
continue
649
650
fact["date"] = fact_date
651
fact["delta"] = fact_end_time - fact["start_time"]
652
res.append(fact)
653
654
return res
655
656
def __get_popular_categories(self):
657
"""returns categories used in the specified interval"""
658
query = """
659
SELECT coalesce(c.name, ?) as category, count(a.id) as popularity
660
FROM facts a
661
LEFT JOIN activities b on a.activity_id = b.id
662
LEFT JOIN categories c on c.id = b.category_id
663
GROUP BY b.category_id
664
ORDER BY popularity desc
665
"""
666
return self.fetchall(query, (_("Unsorted"), ))
667
668
def __remove_fact(self, fact_id):
669
statements = ["DELETE FROM fact_tags where fact_id = ?",
670
"DELETE FROM facts where id = ?"]
671
self.execute(statements, [(fact_id,)] * 2)
672
673
def __get_activities(self, category_id = None):
674
"""returns list of activities, if category is specified, order by name
675
otherwise - by activity_order"""
676
if category_id:
677
query = """
678
SELECT a.*, b.name as category
679
FROM activities a
680
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
681
WHERE category_id = ?
682
AND deleted is null
683
"""
684
685
# unsorted entries we sort by name - others by ID
686
if category_id == -1:
687
query += "ORDER BY lower(a.name)"
688
else:
689
query += "ORDER BY a.activity_order"
690
691
activities = self.fetchall(query, (category_id, ))
692
693
else:
694
query = """
695
SELECT a.*, b.name as category
696
FROM activities a
697
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
698
WHERE deleted is null
699
ORDER BY lower(a.name)
700
"""
701
activities = self.fetchall(query)
702
703
return activities
704
705
def __get_sorted_activities(self):
706
"""returns list of acitivities that have categories"""
707
query = """
708
SELECT a.*, b.name as category, b.category_order
709
FROM activities a
710
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
711
WHERE a.category_id > -1
712
AND a.deleted is null
713
ORDER BY category_order, activity_order
714
"""
715
return self.fetchall(query)
716
717
def __get_autocomplete_activities(self):
718
"""returns list of activities for autocomplete,
719
activity names converted to lowercase"""
720
721
query = """
722
SELECT lower(a.name) as name, b.name as category
723
FROM activities a
724
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
725
WHERE deleted is null
726
ORDER BY lower(a.name)
727
"""
728
activities = self.fetchall(query)
729
730
return activities
731
732
def __remove_activity(self, id):
733
""" check if we have any facts with this activity and behave accordingly
734
if there are facts - sets activity to deleted = True
735
else, just remove it"""
736
737
query = "select count(*) as count from facts where activity_id = ?"
738
bound_facts = self.fetchone(query, (id,))['count']
739
740
if bound_facts > 0:
741
self.execute("UPDATE activities SET deleted = 1 WHERE id = ?", (id,))
742
else:
743
self.execute("delete from activities where id = ?", (id,))
744
745
def __remove_category(self, id):
746
"""move all activities to unsorted and remove category"""
747
748
update = "update activities set category_id = -1 where category_id = ?"
749
self.execute(update, (id, ))
750
751
self.execute("delete from categories where id = ?", (id, ))
752
753
754
def __swap_activities(self, id1, priority1, id2, priority2):
755
""" swaps nearby activities """
756
# TODO - 2 selects and 2 updates is wrong we could live without selects
757
self.execute(["update activities set activity_order = ? where id = ?",
758
"update activities set activity_order = ? where id = ?"],
759
[(priority1, id2), (priority2, id1)])
760
761
def __add_activity(self, name, category_id = None):
762
# first check that we don't have anything like that yet
763
activity_id = self.__get_activity_by_name(name, category_id)
764
if activity_id:
765
return activity_id
766
767
#now do the create bit
768
category_id = category_id or -1
769
new_rec = self.fetchone("select max(id) + 1 , max(activity_order) + 1 from activities")
770
new_id, new_order = new_rec[0] or 1, new_rec[1] or 1
771
772
query = """
773
INSERT INTO activities (id, name, category_id, activity_order)
774
VALUES (?, ?, ?, ?)
775
"""
776
self.execute(query, (new_id, name, category_id, new_order))
777
return new_id
778
779
def __update_activity(self, id, name, category_id):
780
query = """
781
UPDATE activities
782
SET name = ?,
783
category_id = ?
784
WHERE id = ?
785
"""
786
self.execute(query, (name, category_id, id))
787
788
""" Here be dragons (lame connection/cursor wrappers) """
789
def get_connection(self):
790
from configuration import runtime
791
if self.con is None:
792
db_file = runtime.database_file
793
self.con = sqlite.connect(db_file, detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
794
self.con.row_factory = sqlite.Row
795
796
return self.con
797
798
connection = property(get_connection, None)
799
800
def fetchall(self, query, params = None):
801
from configuration import runtime
802
self.__setup()
803
804
con = self.connection
805
cur = con.cursor()
806
807
logging.debug("%s %s" % (query, params))
808
809
if params:
810
cur.execute(query, params)
811
else:
812
cur.execute(query)
813
814
res = cur.fetchall()
815
cur.close()
816
817
return res
818
819
def fetchone(self, query, params = None):
820
res = self.fetchall(query, params)
821
if res:
822
return res[0]
823
else:
824
return None
825
826
def execute(self, statement, params = ()):
827
"""
828
execute sql statement. optionally you can give multiple statements
829
to save on cursor creation and closure
830
"""
831
from configuration import runtime
832
self.__setup()
833
834
con = self.connection
835
cur = con.cursor()
836
837
if isinstance(statement, list) == False: #we kind of think that we will get list of instructions
838
statement = [statement]
839
params = [params]
840
841
if isinstance(statement, list):
842
for i in range(len(statement)):
843
logging.debug("%s %s" % (statement[i], params[i]))
844
845
res = cur.execute(statement[i], params[i])
846
847
con.commit()
848
cur.close()
849
850
def run_fixtures(self):
851
# defaults
852
work_category = {"name": _("Work"),
853
"entries": [_("Reading news"),
854
_("Checking stocks"),
855
_("Super secret project X"),
856
_("World domination")]}
857
858
nonwork_category = {"name": _("Day-to-day"),
859
"entries": [_("Lunch"),
860
_("Watering flowers"),
861
_("Doing handstands")]}
862
863
"""upgrade DB to hamster version"""
864
version = self.fetchone("SELECT version FROM version")["version"]
865
current_version = 6
866
867
if version < 2:
868
"""moving from fact_date, fact_time to start_time, end_time"""
869
870
self.execute("""
871
CREATE TABLE facts_new
872
(id integer primary key,
873
activity_id integer,
874
start_time varchar2(12),
875
end_time varchar2(12))
876
""")
877
878
self.execute("""
879
INSERT INTO facts_new
880
(id, activity_id, start_time)
881
SELECT id, activity_id, fact_date || fact_time
882
FROM facts
883
""")
884
885
self.execute("DROP TABLE facts")
886
self.execute("ALTER TABLE facts_new RENAME TO facts")
887
888
# run through all facts and set the end time
889
# if previous fact is not on the same date, then it means that it was the
890
# last one in previous, so remove it
891
# this logic saves our last entry from being deleted, which is good
892
facts = self.fetchall("""
893
SELECT id, activity_id, start_time,
894
substr(start_time,1, 8) start_date
895
FROM facts
896
ORDER BY start_time
897
""")
898
prev_fact = None
899
900
for fact in facts:
901
if prev_fact:
902
if prev_fact['start_date'] == fact['start_date']:
903
self.execute("UPDATE facts SET end_time = ? where id = ?",
904
(fact['start_time'], prev_fact['id']))
905
else:
906
#otherwise that's the last entry of the day - remove it
907
self.execute("DELETE FROM facts WHERE id = ?", (prev_fact["id"],))
908
909
prev_fact = fact
910
911
#it was kind of silly not to have datetimes in first place
912
if version < 3:
913
self.execute("""
914
CREATE TABLE facts_new
915
(id integer primary key,
916
activity_id integer,
917
start_time timestamp,
918
end_time timestamp)
919
""")
920
921
self.execute("""
922
INSERT INTO facts_new
923
(id, activity_id, start_time, end_time)
924
SELECT id, activity_id,
925
substr(start_time,1,4) || "-"
926
|| substr(start_time, 5, 2) || "-"
927
|| substr(start_time, 7, 2) || " "
928
|| substr(start_time, 9, 2) || ":"
929
|| substr(start_time, 11, 2) || ":00",
930
substr(end_time,1,4) || "-"
931
|| substr(end_time, 5, 2) || "-"
932
|| substr(end_time, 7, 2) || " "
933
|| substr(end_time, 9, 2) || ":"
934
|| substr(end_time, 11, 2) || ":00"
935
FROM facts;
936
""")
937
938
self.execute("DROP TABLE facts")
939
self.execute("ALTER TABLE facts_new RENAME TO facts")
940
941
942
#adding categories table to categorize activities
943
if version < 4:
944
#adding the categories table
945
self.execute("""
946
CREATE TABLE categories
947
(id integer primary key,
948
name varchar2(500),
949
color_code varchar2(50),
950
category_order integer)
951
""")
952
953
# adding default categories, and make sure that uncategorized stays on bottom for starters
954
# set order to 2 in case, if we get work in next lines
955
self.execute("""
956
INSERT INTO categories
957
(id, name, category_order)
958
VALUES (1, ?, 2);
959
""", (nonwork_category["name"],))
960
961
#check if we have to create work category - consider work everything that has been determined so, and is not deleted
962
work_activities = self.fetchone("""
963
SELECT count(*) as work_activities
964
FROM activities
965
WHERE deleted is null and work=1;
966
""")['work_activities']
967
968
if work_activities > 0:
969
self.execute("""
970
INSERT INTO categories
971
(id, name, category_order)
972
VALUES (2, ?, 1);
973
""", (work_category["name"],))
974
975
# now add category field to activities, before starting the move
976
self.execute(""" ALTER TABLE activities
977
ADD COLUMN category_id integer;
978
""")
979
980
981
# starting the move
982
983
# first remove all deleted activities with no instances in facts
984
self.execute("""
985
DELETE FROM activities
986
WHERE deleted = 1
987
AND id not in(select activity_id from facts);
988
""")
989
990
991
# moving work / non-work to appropriate categories
992
# exploit false/true = 0/1 thing
993
self.execute(""" UPDATE activities
994
SET category_id = work + 1
995
WHERE deleted is null
996
""")
997
998
#finally, set category to -1 where there is none
999
self.execute(""" UPDATE activities
1000
SET category_id = -1
1001
WHERE category_id is null
1002
""")
1003
1004
# drop work column and forget value of deleted
1005
# previously deleted records are now unsorted ones
1006
# user will be able to mark them as deleted again, in which case
1007
# they won't appear in autocomplete, or in categories
1008
# ressurection happens, when user enters the exact same name
1009
self.execute("""
1010
CREATE TABLE activities_new (id integer primary key,
1011
name varchar2(500),
1012
activity_order integer,
1013
deleted integer,
1014
category_id integer);
1015
""")
1016
1017
self.execute("""
1018
INSERT INTO activities_new
1019
(id, name, activity_order, category_id)
1020
SELECT id, name, activity_order, category_id
1021
FROM activities;
1022
""")
1023
1024
self.execute("DROP TABLE activities")
1025
self.execute("ALTER TABLE activities_new RENAME TO activities")
1026
1027
if version < 5:
1028
self.execute("ALTER TABLE facts add column description varchar2")
1029
1030
if version < 6:
1031
# facts table could use an index
1032
self.execute("CREATE INDEX idx_facts_start_end ON facts(start_time, end_time)")
1033
self.execute("CREATE INDEX idx_facts_start_end_activity ON facts(start_time, end_time, activity_id)")
1034
1035
# adding tags
1036
self.execute("""CREATE TABLE tags (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
1037
name TEXT NOT NULL,
1038
autocomplete BOOL DEFAULT true)""")
1039
self.execute("CREATE INDEX idx_tags_name ON tags(name)")
1040
1041
self.execute("CREATE TABLE fact_tags(fact_id integer, tag_id integer)")
1042
self.execute("CREATE INDEX idx_fact_tags_fact ON fact_tags(fact_id)")
1043
self.execute("CREATE INDEX idx_fact_tags_tag ON fact_tags(tag_id)")
1044
1045
# at the happy end, update version number
1046
if version < current_version:
1047
#lock down current version
1048
self.execute("UPDATE version SET version = %d" % current_version)
1049
1050
"""we start with an empty database and then populate with default
1051
values. This way defaults can be localized!"""
1052
1053
category_count = self.fetchone("select count(*) from categories")[0]
1054
1055
if category_count == 0:
1056
work_cat_id = self.__add_category(work_category["name"])
1057
for entry in work_category["entries"]:
1058
self.__add_activity(entry, work_cat_id)
1059
1060
nonwork_cat_id = self.__add_category(nonwork_category["name"])
1061
for entry in nonwork_category["entries"]:
1062
self.__add_activity(entry, nonwork_cat_id)
1063
Loggerhead is a web-based interface for Breezy
Version: 2.0.1