« back to all changes in this revision
Viewing changes to src/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
src/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
def __setup(self):
50
"""
51
Delayed setup so we don't do everything at the same time
52
"""
53
if self.__setup.im_func.complete:
54
return
55
56
self.__con = None
57
self.__cur = None
58
59
from configuration import runtime
60
61
db_file = runtime.database_path
62
db_path, _ = os.path.split(os.path.realpath(db_file))
63
64
if not os.path.exists(db_path):
65
try:
66
os.makedirs(db_path, 0744)
67
except Exception, msg:
68
logging.error("could not create user dir (%s): %s" % (db_path, msg))
69
70
data_dir = runtime.data_dir
71
72
#check if db is here
73
if not os.path.exists(db_file):
74
logging.info("Database not found in %s - installing default from %s!" % (db_file, data_dir))
75
copyfile(os.path.join(data_dir, DB_FILE), db_file)
76
77
#change also permissions - sometimes they are 444
78
try:
79
os.chmod(db_file, 0664)
80
except Exception, msg:
81
logging.error("Could not change mode on %s!" % (db_file))
82
self.__setup.im_func.complete = True
83
self.run_fixtures()
84
85
86
87
__setup.complete = False
88
89
#tags, here we come!
90
def __get_tags(self, autocomplete = None):
91
query = "select * from tags"
92
if autocomplete:
93
query += " where autocomplete='true'"
94
95
query += " order by name"
96
return self.fetchall(query)
97
98
def __get_tag_ids(self, tags):
99
"""look up tags by their name. create if not found"""
100
101
db_tags = self.fetchall("select * from tags where name in (%s)"
102
% ",".join(["?"] * len(tags)), tags) # bit of magic here - using sqlites bind variables
103
104
changes = False
105
106
# check if any of tags needs ressurection
107
set_complete = [str(tag["id"]) for tag in db_tags if tag["autocomplete"] == "false"]
108
if set_complete:
109
changes = True
110
self.execute("update tags set autocomplete='true' where id in (%s)" % ", ".join(set_complete))
111
112
113
found_tags = [tag["name"] for tag in db_tags]
114
115
add = set(tags) - set(found_tags)
116
if add:
117
statement = "insert into tags(name) values(?)"
118
119
self.execute([statement] * len(add), [(tag,) for tag in add])
120
121
return self.__get_tag_ids(tags)[0], True # all done, recurse
122
else:
123
return db_tags, changes
124
125
def __update_autocomplete_tags(self, tags):
126
tags = [tag.strip() for tag in tags.split(",") if tag.strip()] # split by comma
127
128
#first we will create new ones
129
tags, changes = self.__get_tag_ids(tags)
130
tags = [tag["id"] for tag in tags]
131
132
#now we will find which ones are gone from the list
133
query = """
134
SELECT b.id as id, b.autocomplete, count(a.fact_id) as occurences
135
FROM tags b
136
LEFT JOIN fact_tags a on a.tag_id = b.id
137
WHERE b.id not in (%s)
138
GROUP BY b.id
139
""" % ",".join(["?"] * len(tags)) # bit of magic here - using sqlites bind variables
140
141
gone = self.fetchall(query, tags)
142
143
to_delete = [str(tag["id"]) for tag in gone if tag["occurences"] == 0]
144
to_uncomplete = [str(tag["id"]) for tag in gone if tag["occurences"] > 0 and tag["autocomplete"] == "true"]
145
146
if to_delete:
147
self.execute("delete from tags where id in (%s)" % ", ".join(to_delete))
148
149
if to_uncomplete:
150
self.execute("update tags set autocomplete='false' where id in (%s)" % ", ".join(to_uncomplete))
151
152
return changes or len(to_delete + to_uncomplete) > 0
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_activity = self.__get_activity_by_name(activity['name'], category_id)
161
162
if id == existing_activity['id']: # we are already there, go home
163
return False
164
165
if existing_activity: #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_activity['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
order = self.fetchone("select max(category_order) + 1 from categories")[0] or 1
194
query = """
195
INSERT INTO categories (name, category_order)
196
VALUES (?, ?)
197
"""
198
self.execute(query, (name, order))
199
return self.__last_insert_rowid()
200
201
def __update_category(self, id, name):
202
if id > -1: # Update, and ignore unsorted, if that was somehow triggered
203
update = """
204
UPDATE categories
205
SET name = ?
206
WHERE id = ?
207
"""
208
self.execute(update, (name, id))
209
210
def __move_activity(self, source_id, target_order, insert_after = True):
211
statement = "UPDATE activities SET activity_order = activity_order + 1"
212
213
if insert_after:
214
statement += " WHERE activity_order > ?"
215
else:
216
statement += " WHERE activity_order >= ?"
217
218
self.execute(statement, (target_order, ))
219
220
statement = "UPDATE activities SET activity_order = ? WHERE id = ?"
221
222
if insert_after:
223
self.execute(statement, (target_order + 1, source_id))
224
else:
225
self.execute(statement, (target_order, source_id))
226
227
228
229
def __get_activity_by_name(self, name, category_id = None, ressurect = True):
230
"""get most recent, preferably not deleted activity by it's name"""
231
232
if category_id:
233
query = """
234
SELECT a.id, a.name, a.deleted, coalesce(b.name, ?) as category
235
FROM activities a
236
LEFT JOIN categories b ON category_id = b.id
237
WHERE lower(a.name) = lower(?)
238
AND category_id = ?
239
ORDER BY a.deleted, a.id desc
240
LIMIT 1
241
"""
242
243
res = self.fetchone(query, (_("Unsorted"), name, category_id))
244
else:
245
query = """
246
SELECT a.id, a.name, a.deleted, coalesce(b.name, ?) as category
247
FROM activities a
248
LEFT JOIN categories b ON category_id = b.id
249
WHERE lower(a.name) = lower(?)
250
ORDER BY a.deleted, a.id desc
251
LIMIT 1
252
"""
253
res = self.fetchone(query, (_("Unsorted"), 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'] and not ressurect:
259
return None
260
elif res['deleted']:
261
update = """
262
UPDATE activities
263
SET deleted = null, category_id = -1
264
WHERE id = ?
265
"""
266
self.execute(update, (res['id'], ))
267
268
return res
269
270
return None
271
272
def __get_category_by_name(self, name):
273
"""returns category by it's name"""
274
275
query = """
276
SELECT id from categories
277
WHERE lower(name) = lower(?)
278
ORDER BY id desc
279
LIMIT 1
280
"""
281
282
res = self.fetchone(query, (name, ))
283
284
if res:
285
return res['id']
286
287
return None
288
289
def __get_fact(self, id):
290
query = """
291
SELECT a.id AS id,
292
a.start_time AS start_time,
293
a.end_time AS end_time,
294
a.description as description,
295
b.name AS name, b.id as activity_id,
296
coalesce(c.name, ?) as category, coalesce(c.id, -1) as category_id,
297
e.name as tag
298
FROM facts a
299
LEFT JOIN activities b ON a.activity_id = b.id
300
LEFT JOIN categories c ON b.category_id = c.id
301
LEFT JOIN fact_tags d ON d.fact_id = a.id
302
LEFT JOIN tags e ON e.id = d.tag_id
303
WHERE a.id = ?
304
ORDER BY e.name
305
"""
306
307
return self.__group_tags(self.fetchall(query, (_("Unsorted"), id)))[0]
308
309
def __group_tags(self, facts):
310
"""put the fact back together and move all the unique tags to an array"""
311
if not facts: return facts #be it None or whatever
312
313
grouped_facts = []
314
for fact_id, fact_tags in itertools.groupby(facts, lambda f: f["id"]):
315
fact_tags = list(fact_tags)
316
317
# first one is as good as the last one
318
grouped_fact = fact_tags[0]
319
320
# we need dict so we can modify it (sqlite.Row is read only)
321
# in python 2.5, sqlite does not have keys() yet, so we hardcode them (yay!)
322
keys = ["id", "start_time", "end_time", "description", "name",
323
"activity_id", "category", "tag"]
324
grouped_fact = dict([(key, grouped_fact[key]) for key in keys])
325
326
grouped_fact["tags"] = [ft["tag"] for ft in fact_tags if ft["tag"]]
327
grouped_facts.append(grouped_fact)
328
return grouped_facts
329
330
def __get_last_activity(self):
331
facts = self.__get_todays_facts()
332
last_activity = None
333
if facts and facts[-1]["end_time"] == None:
334
last_activity = facts[-1]
335
return last_activity
336
337
def __touch_fact(self, fact, end_time):
338
# tasks under one minute do not count
339
if end_time - fact['start_time'] < datetime.timedelta(minutes = 1):
340
self.__remove_fact(fact['id'])
341
else:
342
end_time = end_time.replace(microsecond = 0)
343
query = """
344
UPDATE facts
345
SET end_time = ?
346
WHERE id = ?
347
"""
348
self.execute(query, (end_time, fact['id']))
349
350
def __squeeze_in(self, start_time):
351
# tries to put task in the given date
352
# if there are conflicts, we will only truncate the ongoing task
353
# and replace it's end part with our activity
354
355
# we are checking if our start time is in the middle of anything
356
# or maybe there is something after us - so we know to adjust end time
357
# in the latter case go only few days ahead. everything else is madness, heh
358
query = """
359
SELECT a.*, b.name
360
FROM facts a
361
LEFT JOIN activities b on b.id = a.activity_id
362
WHERE ((start_time < ? and end_time > ?)
363
OR (start_time > ? and start_time < ?))
364
ORDER BY start_time
365
LIMIT 1
366
"""
367
fact = self.fetchone(query, (start_time,
368
start_time,
369
start_time,
370
start_time + dt.timedelta(seconds = 60 * 60 * 12)))
371
372
end_time = None
373
374
if fact:
375
if fact["end_time"] and start_time > fact["start_time"]:
376
#we are in middle of a fact - truncate it to our start
377
self.execute("UPDATE facts SET end_time=? WHERE id=?",
378
(start_time, fact["id"]))
379
380
# hamster is second-aware, but the edit dialog naturally is not
381
# so when an ongoing task is being edited, the seconds get truncated
382
# and the start time will be before previous task's end time.
383
# so set our end time only if it is not about seconds
384
if fact["end_time"].replace(second = 0) > start_time:
385
end_time = fact["end_time"]
386
else: #otherwise we have found a task that is after us
387
end_time = fact["start_time"]
388
389
return end_time
390
391
def __solve_overlaps(self, start_time, end_time):
392
"""finds facts that happen in given interval and shifts them to
393
make room for new fact"""
394
395
# this function is destructive - can't go with a wildcard
396
if not end_time or not start_time:
397
return
398
399
# activities that we are overlapping.
400
# second OR clause is for elimination - |new fact--|---old-fact--|--new fact|
401
query = """
402
SELECT a.*, b.name, c.name as category
403
FROM facts a
404
LEFT JOIN activities b on b.id = a.activity_id
405
LEFT JOIN categories c on b.category_id = c.id
406
WHERE ((start_time < ? and end_time > ?)
407
OR (start_time < ? and end_time > ?))
408
409
OR ((start_time < ? and start_time > ?)
410
OR (end_time < ? and end_time > ?))
411
ORDER BY start_time
412
"""
413
conflicts = self.fetchall(query, (start_time, start_time, end_time, end_time,
414
end_time, start_time, end_time, start_time))
415
416
for fact in conflicts:
417
# split - truncate until beginning of new entry and create new activity for end
418
if fact["start_time"] < start_time < fact["end_time"] and \
419
fact["start_time"] < end_time < fact["end_time"]:
420
421
logging.info("splitting %s" % fact["name"])
422
self.execute("""UPDATE facts
423
SET end_time = ?
424
WHERE id = ?""", (start_time, fact["id"]))
425
fact_name = fact["name"]
426
new_fact = self.__add_fact(fact["name"],
427
"", # will create tags in the next step
428
end_time,
429
fact["end_time"],
430
fact["category"],
431
fact["description"])
432
tag_update = """INSERT INTO fact_tags(fact_id, tag_id)
433
SELECT ?, tag_id
434
FROM fact_tags
435
WHERE fact_id = ?"""
436
self.execute(tag_update, (new_fact["id"], fact["id"])) #clone tags
437
438
#eliminate
439
elif fact["end_time"] and \
440
start_time < fact["start_time"] < end_time and \
441
start_time < fact["end_time"] < end_time:
442
logging.info("eliminating %s" % fact["name"])
443
self.__remove_fact(fact["id"])
444
445
# overlap start
446
elif start_time < fact["start_time"] < end_time:
447
logging.info("Overlapping start of %s" % fact["name"])
448
self.execute("UPDATE facts SET start_time=? WHERE id=?",
449
(end_time, fact["id"]))
450
451
# overlap end
452
elif start_time < fact["end_time"] < end_time:
453
logging.info("Overlapping end of %s" % fact["name"])
454
self.execute("UPDATE facts SET end_time=? WHERE id=?",
455
(start_time, fact["id"]))
456
457
458
def __add_fact(self, activity_name, tags, start_time = None,
459
end_time = None, category_name = None, description = None):
460
461
activity = stuff.parse_activity_input(activity_name)
462
463
tags = [tag.strip() for tag in tags.split(",") if tag.strip()] # split by comma
464
465
# explicitly stated takes precedence
466
activity.description = description or activity.description
467
468
tags = self.get_tag_ids(tags) #this will create any missing tags too
469
470
if category_name:
471
activity.category_name = category_name
472
if description:
473
activity.description = description #override
474
475
start_time = activity.start_time or start_time or datetime.datetime.now()
476
477
if start_time > datetime.datetime.now():
478
return None #no facts in future, please
479
480
start_time = start_time.replace(microsecond = 0)
481
end_time = activity.end_time or end_time
482
if end_time:
483
end_time = end_time.replace(microsecond = 0)
484
485
if not start_time or not activity.activity_name: # sanity check
486
return
487
488
# now check if maybe there is also a category
489
category_id = None
490
if activity.category_name:
491
category_id = self.__get_category_by_name(activity.category_name)
492
if not category_id:
493
category_id = self.__add_category(activity.category_name)
494
495
# try to find activity
496
activity_id = self.__get_activity_by_name(activity.activity_name,
497
category_id)
498
if not activity_id:
499
activity_id = self.__add_activity(activity.activity_name,
500
category_id)
501
else:
502
activity_id = activity_id['id']
503
504
505
# if we are working on +/- current day - check the last_activity
506
if (dt.datetime.now() - start_time <= dt.timedelta(days=1)):
507
508
# pull in previous facts
509
facts = self.__get_todays_facts()
510
511
previous = None
512
if facts and facts[-1]["end_time"] == None:
513
previous = facts[-1]
514
515
if previous and previous['start_time'] < start_time:
516
# check if maybe that is the same one, in that case no need to restart
517
if previous["activity_id"] == activity_id \
518
and previous["tags"] == sorted([tag["name"] for tag in tags]) \
519
and previous["description"] == (description or ""):
520
return previous
521
522
# otherwise, if no description is added
523
# see if maybe it is too short to qualify as an activity
524
if not previous["description"] \
525
and 60 >= (start_time - previous['start_time']).seconds >= 0:
526
self.__remove_fact(previous['id'])
527
528
# now that we removed the previous one, see if maybe the one
529
# before that is actually same as the one we want to start
530
# (glueing)
531
if len(facts) > 1 and 60 >= (start_time - facts[-2]['end_time']).seconds >= 0:
532
before = facts[-2]
533
if before["activity_id"] == activity_id \
534
and before["tags"] == sorted([tag["name"] for tag in tags]):
535
# essentially same activity - resume it and return
536
update = """
537
UPDATE facts
538
SET end_time = null
539
WHERE id = ?
540
"""
541
self.execute(update, (before["id"],))
542
543
return before
544
else:
545
# otherwise stop
546
update = """
547
UPDATE facts
548
SET end_time = ?
549
WHERE id = ?
550
"""
551
self.execute(update, (start_time, previous["id"]))
552
553
554
# done with the current activity, now we can solve overlaps
555
if not end_time:
556
end_time = self.__squeeze_in(start_time)
557
else:
558
self.__solve_overlaps(start_time, end_time)
559
560
561
# finally add the new entry
562
insert = """
563
INSERT INTO facts (activity_id, start_time, end_time, description)
564
VALUES (?, ?, ?, ?)
565
"""
566
self.execute(insert, (activity_id, start_time, end_time, activity.description))
567
568
fact_id = self.__last_insert_rowid()
569
570
#now link tags
571
insert = ["insert into fact_tags(fact_id, tag_id) values(?, ?)"] * len(tags)
572
params = [(fact_id, tag["id"]) for tag in tags]
573
self.execute(insert, params)
574
575
return fact_id
576
577
def __last_insert_rowid(self):
578
return self.fetchone("SELECT last_insert_rowid();")[0]
579
580
581
def __get_todays_facts(self):
582
from configuration import conf
583
day_start = conf.get("day_start_minutes")
584
day_start = dt.time(day_start / 60, day_start % 60)
585
today = (dt.datetime.now() - dt.timedelta(hours = day_start.hour,
586
minutes = day_start.minute)).date()
587
return self.__get_facts(today)
588
589
590
def __get_facts(self, date, end_date = None, search_terms = ""):
591
query = """
592
SELECT a.id AS id,
593
a.start_time AS start_time,
594
a.end_time AS end_time,
595
a.description as description,
596
b.name AS name, b.id as activity_id,
597
coalesce(c.name, ?) as category,
598
e.name as tag
599
FROM facts a
600
LEFT JOIN activities b ON a.activity_id = b.id
601
LEFT JOIN categories c ON b.category_id = c.id
602
LEFT JOIN fact_tags d ON d.fact_id = a.id
603
LEFT JOIN tags e ON e.id = d.tag_id
604
WHERE (a.end_time >= ? OR a.end_time IS NULL) AND a.start_time <= ?
605
"""
606
607
# let's see what we can do with search terms
608
# we will be looking in activity names, descriptions, categories and tags
609
# comma will be treated as OR
610
# space will be treated as AND or possible join
611
612
613
# split by comma and then by space and remove all extra spaces
614
or_bits = [[term.strip().lower().replace("'", "''") #striping removing case sensitivity and escaping quotes in term
615
for term in terms.strip().split(" ") if term.strip()]
616
for terms in search_terms.split(",") if terms.strip()]
617
618
def all_fields(term):
619
return """(lower(a.description) like '%%%(term)s%%'
620
or lower(b.name) = '%(term)s'
621
or lower(c.name) = '%(term)s'
622
or lower(e.name) = '%(term)s' )""" % dict(term = term)
623
624
if or_bits:
625
search_query = "1<>1 " # will be building OR chain, so start with a false
626
627
for and_bits in or_bits:
628
if len(and_bits) == 1:
629
and_query = all_fields(and_bits[0])
630
else:
631
and_query = "1=1 " # will be building AND chain, so start with a true
632
# if we have more than one word, go for "(a and b) or ab"
633
# to match two word tags
634
for bit1, bit2 in zip(and_bits, and_bits[1:]):
635
and_query += "and (%s and %s) or %s" % (all_fields(bit1),
636
all_fields(bit2),
637
all_fields("%s %s" % (bit1, bit2)))
638
639
search_query = "%s or (%s) " % (search_query, and_query)
640
641
query = "%s and (%s)" % (query, search_query)
642
643
644
645
query += " ORDER BY a.start_time, e.name"
646
end_date = end_date or date
647
648
from configuration import conf
649
day_start = conf.get("day_start_minutes")
650
day_start = dt.time(day_start / 60, day_start % 60)
651
652
split_time = day_start
653
datetime_from = dt.datetime.combine(date, split_time)
654
datetime_to = dt.datetime.combine(end_date, split_time) + dt.timedelta(days = 1)
655
656
facts = self.fetchall(query, (_("Unsorted"),
657
datetime_from,
658
datetime_to))
659
660
#first let's put all tags in an array
661
facts = self.__group_tags(facts)
662
663
res = []
664
for fact in facts:
665
# heuristics to assign tasks to proper days
666
667
# if fact has no end time, set the last minute of the day,
668
# or current time if fact has happened in last 24 hours
669
if fact["end_time"]:
670
fact_end_time = fact["end_time"]
671
elif (dt.date.today() - fact["start_time"].date()) <= dt.timedelta(days=1):
672
fact_end_time = dt.datetime.now().replace(microsecond = 0)
673
else:
674
fact_end_time = fact["start_time"]
675
676
fact_start_date = fact["start_time"].date() \
677
- dt.timedelta(1 if fact["start_time"].time() < split_time else 0)
678
fact_end_date = fact_end_time.date() \
679
- dt.timedelta(1 if fact_end_time.time() < split_time else 0)
680
fact_date_span = fact_end_date - fact_start_date
681
682
# check if the task spans across two dates
683
if fact_date_span.days == 1:
684
datetime_split = dt.datetime.combine(fact_end_date, split_time)
685
start_date_duration = datetime_split - fact["start_time"]
686
end_date_duration = fact_end_time - datetime_split
687
if start_date_duration > end_date_duration:
688
# most of the task was done during the previous day
689
fact_date = fact_start_date
690
else:
691
fact_date = fact_end_date
692
else:
693
# either doesn't span or more than 24 hrs tracked
694
# (in which case we give up)
695
fact_date = fact_start_date
696
697
if fact_date < date or fact_date > end_date:
698
# due to spanning we've jumped outside of given period
699
continue
700
701
fact["date"] = fact_date
702
fact["delta"] = fact_end_time - fact["start_time"]
703
res.append(fact)
704
705
return res
706
707
def __get_popular_categories(self):
708
"""returns categories used in the specified interval"""
709
query = """
710
SELECT coalesce(c.name, ?) as category, count(a.id) as popularity
711
FROM facts a
712
LEFT JOIN activities b on a.activity_id = b.id
713
LEFT JOIN categories c on c.id = b.category_id
714
GROUP BY b.category_id
715
ORDER BY popularity desc
716
"""
717
return self.fetchall(query, (_("Unsorted"), ))
718
719
def __remove_fact(self, fact_id):
720
statements = ["DELETE FROM fact_tags where fact_id = ?",
721
"DELETE FROM facts where id = ?"]
722
self.execute(statements, [(fact_id,)] * 2)
723
724
def __get_activities(self, category_id = None):
725
"""returns list of activities, if category is specified, order by name
726
otherwise - by activity_order"""
727
if category_id:
728
query = """
729
SELECT a.*, b.name as category
730
FROM activities a
731
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
732
WHERE category_id = ?
733
AND deleted is null
734
"""
735
736
# unsorted entries we sort by name - others by ID
737
if category_id == -1:
738
query += "ORDER BY lower(a.name)"
739
else:
740
query += "ORDER BY a.activity_order"
741
742
activities = self.fetchall(query, (category_id, ))
743
744
else:
745
query = """
746
SELECT a.*, b.name as category
747
FROM activities a
748
LEFT JOIN categories b on coalesce(b.id, -1) = a.category_id
749
WHERE deleted IS NULL
750
ORDER BY lower(a.name)
751
"""
752
activities = self.fetchall(query)
753
754
return activities
755
756
def __get_autocomplete_activities(self, search):
757
"""returns list of activities for autocomplete,
758
activity names converted to lowercase"""
759
760
query = """
761
SELECT lower(a.name) AS name, b.name AS category
762
FROM activities a
763
LEFT JOIN categories b ON coalesce(b.id, -1) = a.category_id
764
LEFT JOIN facts f ON a.id = f.activity_id
765
WHERE deleted IS NULL
766
AND a.name LIKE ? ESCAPE '\\'
767
GROUP BY a.id
768
ORDER BY max(f.start_time) DESC, lower(a.name)
769
LIMIT 50
770
"""
771
search = search.replace('\\', '\\\\').replace('%', '\\%').replace('_', '\\_')
772
activities = self.fetchall(query, (u'%s%%' % search, ))
773
774
return activities
775
776
def __remove_activity(self, id):
777
""" check if we have any facts with this activity and behave accordingly
778
if there are facts - sets activity to deleted = True
779
else, just remove it"""
780
781
query = "select count(*) as count from facts where activity_id = ?"
782
bound_facts = self.fetchone(query, (id,))['count']
783
784
if bound_facts > 0:
785
self.execute("UPDATE activities SET deleted = 1 WHERE id = ?", (id,))
786
else:
787
self.execute("delete from activities where id = ?", (id,))
788
789
def __remove_category(self, id):
790
"""move all activities to unsorted and remove category"""
791
792
update = "update activities set category_id = -1 where category_id = ?"
793
self.execute(update, (id, ))
794
795
self.execute("delete from categories where id = ?", (id, ))
796
797
798
def __swap_activities(self, id1, priority1, id2, priority2):
799
""" swaps nearby activities """
800
# TODO - 2 selects and 2 updates is wrong we could live without selects
801
self.execute(["update activities set activity_order = ? where id = ?",
802
"update activities set activity_order = ? where id = ?"],
803
[(priority1, id2), (priority2, id1)])
804
805
def __add_activity(self, name, category_id = None):
806
# first check that we don't have anything like that yet
807
activity = self.__get_activity_by_name(name, category_id)
808
if activity:
809
return activity['id']
810
811
#now do the create bit
812
category_id = category_id or -1
813
new_order = self.fetchone("select max(activity_order) + 1 from activities")[0] or 1
814
815
query = """
816
INSERT INTO activities (name, category_id, activity_order)
817
VALUES (?, ?, ?)
818
"""
819
self.execute(query, (name, category_id, new_order))
820
return self.__last_insert_rowid()
821
822
def __update_activity(self, id, name, category_id):
823
query = """
824
UPDATE activities
825
SET name = ?,
826
category_id = ?
827
WHERE id = ?
828
"""
829
self.execute(query, (name, category_id, id))
830
831
""" Here be dragons (lame connection/cursor wrappers) """
832
def get_connection(self):
833
from configuration import runtime
834
if self.con is None:
835
db_file = runtime.database_path
836
self.con = sqlite.connect(db_file, detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
837
self.con.row_factory = sqlite.Row
838
839
return self.con
840
841
connection = property(get_connection, None)
842
843
def fetchall(self, query, params = None):
844
from configuration import runtime
845
self.__setup()
846
847
con = self.connection
848
cur = con.cursor()
849
850
logging.debug("%s %s" % (query, params))
851
852
if params:
853
cur.execute(query, params)
854
else:
855
cur.execute(query)
856
857
res = cur.fetchall()
858
cur.close()
859
860
return res
861
862
def fetchone(self, query, params = None):
863
res = self.fetchall(query, params)
864
if res:
865
return res[0]
866
else:
867
return None
868
869
def execute(self, statement, params = ()):
870
"""
871
execute sql statement. optionally you can give multiple statements
872
to save on cursor creation and closure
873
"""
874
from configuration import runtime
875
self.__setup()
876
877
con = self.__con or self.connection
878
cur = self.__cur or con.cursor()
879
880
if isinstance(statement, list) == False: #we kind of think that we will get list of instructions
881
statement = [statement]
882
params = [params]
883
884
if isinstance(statement, list):
885
for i in range(len(statement)):
886
logging.debug("%s %s" % (statement[i], params[i]))
887
888
res = cur.execute(statement[i], params[i])
889
890
if not self.__con:
891
con.commit()
892
cur.close()
893
runtime.register_modification()
894
895
896
def start_transaction(self):
897
# will give some hints to execute not to close or commit anything
898
self.__con = self.connection
899
self.__cur = self.__con.cursor()
900
901
def end_transaction(self):
902
self.__con.commit()
903
self.__cur.close()
904
self.__con = None
905
from configuration import runtime
906
runtime.register_modification()
907
908
def run_fixtures(self):
909
self.start_transaction()
910
911
# defaults
912
work_category = {"name": _("Work"),
913
"entries": [_("Reading news"),
914
_("Checking stocks"),
915
_("Super secret project X"),
916
_("World domination")]}
917
918
nonwork_category = {"name": _("Day-to-day"),
919
"entries": [_("Lunch"),
920
_("Watering flowers"),
921
_("Doing handstands")]}
922
923
"""upgrade DB to hamster version"""
924
version = self.fetchone("SELECT version FROM version")["version"]
925
current_version = 6
926
927
if version < 2:
928
"""moving from fact_date, fact_time to start_time, end_time"""
929
930
self.execute("""
931
CREATE TABLE facts_new
932
(id integer primary key,
933
activity_id integer,
934
start_time varchar2(12),
935
end_time varchar2(12))
936
""")
937
938
self.execute("""
939
INSERT INTO facts_new
940
(id, activity_id, start_time)
941
SELECT id, activity_id, fact_date || fact_time
942
FROM facts
943
""")
944
945
self.execute("DROP TABLE facts")
946
self.execute("ALTER TABLE facts_new RENAME TO facts")
947
948
# run through all facts and set the end time
949
# if previous fact is not on the same date, then it means that it was the
950
# last one in previous, so remove it
951
# this logic saves our last entry from being deleted, which is good
952
facts = self.fetchall("""
953
SELECT id, activity_id, start_time,
954
substr(start_time,1, 8) start_date
955
FROM facts
956
ORDER BY start_time
957
""")
958
prev_fact = None
959
960
for fact in facts:
961
if prev_fact:
962
if prev_fact['start_date'] == fact['start_date']:
963
self.execute("UPDATE facts SET end_time = ? where id = ?",
964
(fact['start_time'], prev_fact['id']))
965
else:
966
#otherwise that's the last entry of the day - remove it
967
self.execute("DELETE FROM facts WHERE id = ?", (prev_fact["id"],))
968
969
prev_fact = fact
970
971
#it was kind of silly not to have datetimes in first place
972
if version < 3:
973
self.execute("""
974
CREATE TABLE facts_new
975
(id integer primary key,
976
activity_id integer,
977
start_time timestamp,
978
end_time timestamp)
979
""")
980
981
self.execute("""
982
INSERT INTO facts_new
983
(id, activity_id, start_time, end_time)
984
SELECT id, activity_id,
985
substr(start_time,1,4) || "-"
986
|| substr(start_time, 5, 2) || "-"
987
|| substr(start_time, 7, 2) || " "
988
|| substr(start_time, 9, 2) || ":"
989
|| substr(start_time, 11, 2) || ":00",
990
substr(end_time,1,4) || "-"
991
|| substr(end_time, 5, 2) || "-"
992
|| substr(end_time, 7, 2) || " "
993
|| substr(end_time, 9, 2) || ":"
994
|| substr(end_time, 11, 2) || ":00"
995
FROM facts;
996
""")
997
998
self.execute("DROP TABLE facts")
999
self.execute("ALTER TABLE facts_new RENAME TO facts")
1000
1001
1002
#adding categories table to categorize activities
1003
if version < 4:
1004
#adding the categories table
1005
self.execute("""
1006
CREATE TABLE categories
1007
(id integer primary key,
1008
name varchar2(500),
1009
color_code varchar2(50),
1010
category_order integer)
1011
""")
1012
1013
# adding default categories, and make sure that uncategorized stays on bottom for starters
1014
# set order to 2 in case, if we get work in next lines
1015
self.execute("""
1016
INSERT INTO categories
1017
(id, name, category_order)
1018
VALUES (1, ?, 2);
1019
""", (nonwork_category["name"],))
1020
1021
#check if we have to create work category - consider work everything that has been determined so, and is not deleted
1022
work_activities = self.fetchone("""
1023
SELECT count(*) as work_activities
1024
FROM activities
1025
WHERE deleted is null and work=1;
1026
""")['work_activities']
1027
1028
if work_activities > 0:
1029
self.execute("""
1030
INSERT INTO categories
1031
(id, name, category_order)
1032
VALUES (2, ?, 1);
1033
""", (work_category["name"],))
1034
1035
# now add category field to activities, before starting the move
1036
self.execute(""" ALTER TABLE activities
1037
ADD COLUMN category_id integer;
1038
""")
1039
1040
1041
# starting the move
1042
1043
# first remove all deleted activities with no instances in facts
1044
self.execute("""
1045
DELETE FROM activities
1046
WHERE deleted = 1
1047
AND id not in(select activity_id from facts);
1048
""")
1049
1050
1051
# moving work / non-work to appropriate categories
1052
# exploit false/true = 0/1 thing
1053
self.execute(""" UPDATE activities
1054
SET category_id = work + 1
1055
WHERE deleted is null
1056
""")
1057
1058
#finally, set category to -1 where there is none
1059
self.execute(""" UPDATE activities
1060
SET category_id = -1
1061
WHERE category_id is null
1062
""")
1063
1064
# drop work column and forget value of deleted
1065
# previously deleted records are now unsorted ones
1066
# user will be able to mark them as deleted again, in which case
1067
# they won't appear in autocomplete, or in categories
1068
# ressurection happens, when user enters the exact same name
1069
self.execute("""
1070
CREATE TABLE activities_new (id integer primary key,
1071
name varchar2(500),
1072
activity_order integer,
1073
deleted integer,
1074
category_id integer);
1075
""")
1076
1077
self.execute("""
1078
INSERT INTO activities_new
1079
(id, name, activity_order, category_id)
1080
SELECT id, name, activity_order, category_id
1081
FROM activities;
1082
""")
1083
1084
self.execute("DROP TABLE activities")
1085
self.execute("ALTER TABLE activities_new RENAME TO activities")
1086
1087
if version < 5:
1088
self.execute("ALTER TABLE facts add column description varchar2")
1089
1090
if version < 6:
1091
# facts table could use an index
1092
self.execute("CREATE INDEX idx_facts_start_end ON facts(start_time, end_time)")
1093
self.execute("CREATE INDEX idx_facts_start_end_activity ON facts(start_time, end_time, activity_id)")
1094
1095
# adding tags
1096
self.execute("""CREATE TABLE tags (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
1097
name TEXT NOT NULL,
1098
autocomplete BOOL DEFAULT true)""")
1099
self.execute("CREATE INDEX idx_tags_name ON tags(name)")
1100
1101
self.execute("CREATE TABLE fact_tags(fact_id integer, tag_id integer)")
1102
self.execute("CREATE INDEX idx_fact_tags_fact ON fact_tags(fact_id)")
1103
self.execute("CREATE INDEX idx_fact_tags_tag ON fact_tags(tag_id)")
1104
1105
# at the happy end, update version number
1106
if version < current_version:
1107
#lock down current version
1108
self.execute("UPDATE version SET version = %d" % current_version)
1109
1110
"""we start with an empty database and then populate with default
1111
values. This way defaults can be localized!"""
1112
1113
category_count = self.fetchone("select count(*) from categories")[0]
1114
1115
if category_count == 0:
1116
work_cat_id = self.__add_category(work_category["name"])
1117
for entry in work_category["entries"]:
1118
self.__add_activity(entry, work_cat_id)
1119
1120
nonwork_cat_id = self.__add_category(nonwork_category["name"])
1121
for entry in nonwork_category["entries"]:
1122
self.__add_activity(entry, nonwork_cat_id)
1123
1124
1125
self.end_transaction()
Loggerhead is a web-based interface for Breezy
Version: 2.0.1