sawine@3
|
1 |
"""
|
sawine@3
|
2 |
Database lib for cronrec.
|
sawine@3
|
3 |
Author: Eugen Sawin (sawine@me73.com)
|
sawine@3
|
4 |
Dependencies: libsqlite3-dev
|
sawine@3
|
5 |
"""
|
sawine@3
|
6 |
|
sawine@3
|
7 |
import sqlite3
|
sawine@3
|
8 |
|
sawine@3
|
9 |
ACTIVITIES_TABLE = """activities(
|
sawine@3
|
10 |
name text not null,
|
sawine@3
|
11 |
description text)"""
|
sawine@3
|
12 |
|
sawine@3
|
13 |
COMPANIES_TABLE = """companies(
|
sawine@3
|
14 |
name text not null,
|
sawine@3
|
15 |
street text,
|
sawine@3
|
16 |
area text,
|
sawine@3
|
17 |
town text,
|
sawine@3
|
18 |
country text,
|
sawine@3
|
19 |
postal text)"""
|
sawine@3
|
20 |
|
sawine@3
|
21 |
PROJECTS_TABLE = """projects(
|
sawine@3
|
22 |
name text not null,
|
sawine@3
|
23 |
number int,
|
sawine@3
|
24 |
customer int,
|
sawine@3
|
25 |
constraint customer_fk foreign key(customer) references companies(rowid) on delete
|
sawine@3
|
26 |
cascade)"""
|
sawine@3
|
27 |
|
sawine@3
|
28 |
RATES_TABLE = """rates(
|
sawine@3
|
29 |
rate smallmoney
|
sawine@3
|
30 |
flatrate smallmoney,
|
sawine@3
|
31 |
project int,
|
sawine@3
|
32 |
activity int,
|
sawine@3
|
33 |
constraint project_fk foreign key(project) references projects(rowid) on delete
|
sawine@3
|
34 |
cascade,
|
sawine@3
|
35 |
constraint activity_fk foreign key(activity) references activities(rowid) on delete
|
sawine@3
|
36 |
cascade)"""
|
sawine@3
|
37 |
|
sawine@3
|
38 |
TASKS_TABLE = """tasks(
|
sawine@3
|
39 |
begin datetime,
|
sawine@3
|
40 |
end datetime,
|
sawine@3
|
41 |
project int,
|
sawine@3
|
42 |
activity int,
|
sawine@3
|
43 |
log text,
|
sawine@3
|
44 |
constraint project_fk foreign key(project) references projects(rowid) on delete
|
sawine@3
|
45 |
cascade,
|
sawine@3
|
46 |
constraint activity_fk foreign key(activity) references activities(rowid) on delete
|
sawine@3
|
47 |
cascade)"""
|
sawine@3
|
48 |
|
sawine@8
|
49 |
BREAKS_TABLE = """breaks(
|
sawine@8
|
50 |
begin datetime,
|
sawine@8
|
51 |
end datetime,
|
sawine@8
|
52 |
task int,
|
sawine@8
|
53 |
log text,
|
sawine@8
|
54 |
constraint task_fk foreign key(task) references tasks(rowid) on delete
|
sawine@8
|
55 |
cascade)"""
|
sawine@8
|
56 |
|
sawine@3
|
57 |
TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,
|
sawine@8
|
58 |
TASKS_TABLE, BREAKS_TABLE)
|
sawine@3
|
59 |
|
sawine@4
|
60 |
DEF_PROJECT = "default"
|
sawine@4
|
61 |
DEF_ACTIVITY = "default"
|
sawine@4
|
62 |
|
sawine@3
|
63 |
def session(db_file):
|
sawine@3
|
64 |
con = sqlite3.connect(db_file)
|
sawine@3
|
65 |
return con, con.cursor()
|
sawine@3
|
66 |
|
sawine@6
|
67 |
def test(db_file):
|
sawine@3
|
68 |
con, cur = session(db_file)
|
sawine@6
|
69 |
cur.execute("select * from projects")
|
sawine@6
|
70 |
print "\nprojects"
|
sawine@3
|
71 |
for c in cur:
|
sawine@3
|
72 |
print c
|
sawine@6
|
73 |
cur.execute("select * from activities")
|
sawine@6
|
74 |
print "\nactivities"
|
sawine@6
|
75 |
for c in cur:
|
sawine@6
|
76 |
print c
|
sawine@6
|
77 |
cur.execute("select * from tasks")
|
sawine@6
|
78 |
print "\ntasks"
|
sawine@6
|
79 |
for c in cur:
|
sawine@6
|
80 |
print c
|
sawine@8
|
81 |
cur.execute("select * from breaks")
|
sawine@8
|
82 |
print "\nbreaks"
|
sawine@8
|
83 |
for c in cur:
|
sawine@8
|
84 |
print c
|
sawine@6
|
85 |
con.commit()
|
sawine@6
|
86 |
cur.close()
|
sawine@3
|
87 |
|
sawine@3
|
88 |
def init(db_file):
|
sawine@3
|
89 |
con, cur = session(db_file)
|
sawine@3
|
90 |
for table in TABLES:
|
sawine@3
|
91 |
sql = "create table " + table
|
sawine@3
|
92 |
cur.execute(sql)
|
sawine@3
|
93 |
con.commit()
|
sawine@3
|
94 |
cur.close()
|
sawine@3
|
95 |
|
sawine@5
|
96 |
def find_last_project(db_file, time):
|
sawine@4
|
97 |
con, cur = session(db_file)
|
sawine@4
|
98 |
values = (time,)
|
sawine@5
|
99 |
sql = "select project from tasks where begin < ? and end is null order by \
|
sawine@5
|
100 |
begin desc"
|
sawine@4
|
101 |
cur.execute(sql, values)
|
sawine@4
|
102 |
id = cur.fetchone()
|
sawine@4
|
103 |
if id:
|
sawine@4
|
104 |
values = (id[0],)
|
sawine@4
|
105 |
sql = "select name from projects where rowid=?"
|
sawine@4
|
106 |
cur.execute(sql, values)
|
sawine@5
|
107 |
project = cur.fetchone()[0]
|
sawine@4
|
108 |
else:
|
sawine@6
|
109 |
project = None#DEF_PROJECT
|
sawine@4
|
110 |
con.commit()
|
sawine@4
|
111 |
cur.close()
|
sawine@4
|
112 |
return project
|
sawine@4
|
113 |
|
sawine@5
|
114 |
def find_last_activity(db_file, time):
|
sawine@5
|
115 |
con, cur = session(db_file)
|
sawine@5
|
116 |
values = (time,)
|
sawine@5
|
117 |
sql = "select activity from tasks where begin < ? and end is null order by \
|
sawine@5
|
118 |
begin desc"
|
sawine@5
|
119 |
cur.execute(sql, values)
|
sawine@5
|
120 |
id = cur.fetchone()
|
sawine@5
|
121 |
if id:
|
sawine@5
|
122 |
values = (id[0],)
|
sawine@5
|
123 |
sql = "select name from activities where rowid=?"
|
sawine@5
|
124 |
cur.execute(sql, values)
|
sawine@5
|
125 |
activity = cur.fetchone()[0]
|
sawine@5
|
126 |
else:
|
sawine@6
|
127 |
activity = None#DEF_ACTIVITY
|
sawine@5
|
128 |
con.commit()
|
sawine@5
|
129 |
cur.close()
|
sawine@5
|
130 |
return activity
|
sawine@5
|
131 |
|
sawine@8
|
132 |
def find_active_task(db_file):
|
sawine@7
|
133 |
con, cur = session(db_file)
|
sawine@7
|
134 |
sql = "select project, activity from tasks where end is null"
|
sawine@7
|
135 |
cur.execute(sql)
|
sawine@8
|
136 |
task = cur.fetchone()
|
sawine@8
|
137 |
if task:
|
sawine@8
|
138 |
task = task[0]
|
sawine@7
|
139 |
cur.close()
|
sawine@8
|
140 |
return task
|
sawine@7
|
141 |
|
sawine@7
|
142 |
def id_name(db_file, table, id):
|
sawine@7
|
143 |
con, cur = session(db_file)
|
sawine@7
|
144 |
values = (id,)
|
sawine@7
|
145 |
sql = "select name from %s where rowid=?" % table
|
sawine@7
|
146 |
cur.execute(sql, values)
|
sawine@7
|
147 |
id = cur.fetchone()
|
sawine@7
|
148 |
cur.close()
|
sawine@7
|
149 |
return id[0]
|
sawine@7
|
150 |
|
sawine@4
|
151 |
def row_id(db_file, table, name):
|
sawine@4
|
152 |
con, cur = session(db_file)
|
sawine@4
|
153 |
values = (name,)
|
sawine@4
|
154 |
sql = "select rowid from %s where name=?" % table
|
sawine@4
|
155 |
cur.execute(sql, values)
|
sawine@4
|
156 |
id = cur.fetchone()
|
sawine@4
|
157 |
if not id:
|
sawine@4
|
158 |
sql = "insert into %s(name) values(?)" % table
|
sawine@4
|
159 |
cur.execute(sql, values)
|
sawine@4
|
160 |
con.commit()
|
sawine@4
|
161 |
cur.close()
|
sawine@4
|
162 |
return row_id(db_file, table, name)
|
sawine@4
|
163 |
con.commit()
|
sawine@4
|
164 |
cur.close()
|
sawine@4
|
165 |
return id[0]
|
sawine@4
|
166 |
|
sawine@4
|
167 |
def project_id(db_file, name):
|
sawine@4
|
168 |
return row_id(db_file, "projects", name)
|
sawine@4
|
169 |
|
sawine@4
|
170 |
def activity_id(db_file, name):
|
sawine@4
|
171 |
return row_id(db_file, "activities", name)
|
sawine@4
|
172 |
|
sawine@5
|
173 |
def end(db_file, project, activity, time):
|
sawine@6
|
174 |
print "ending: %s:%s" % (project, activity)
|
sawine@6
|
175 |
if activity or project:
|
sawine@6
|
176 |
if not activity:
|
sawine@6
|
177 |
activity = find_last_activity(db_file, time)
|
sawine@6
|
178 |
elif not project:
|
sawine@6
|
179 |
project = find_last_project(db_file, time)
|
sawine@7
|
180 |
|
sawine@6
|
181 |
con, cur = session(db_file)
|
sawine@7
|
182 |
if activity == "all" and project == "all":
|
sawine@7
|
183 |
values = (time,)
|
sawine@7
|
184 |
sql = "update tasks set end=? where end is null"
|
sawine@7
|
185 |
else:
|
sawine@7
|
186 |
values = (time, project_id(db_file, project), activity_id(db_file, activity))
|
sawine@7
|
187 |
sql = "update tasks set end=? where project=? and activity=?"
|
sawine@6
|
188 |
cur.execute(sql, values)
|
sawine@6
|
189 |
con.commit()
|
sawine@6
|
190 |
cur.close()
|
sawine@6
|
191 |
test(db_file)
|
sawine@5
|
192 |
|
sawine@3
|
193 |
def begin(db_file, project, activity, time):
|
sawine@6
|
194 |
print "beginning: %s:%s" % (project, activity)
|
sawine@5
|
195 |
last_project = find_last_project(db_file, time)
|
sawine@6
|
196 |
last_activity = find_last_activity(db_file, time)
|
sawine@4
|
197 |
if not project:
|
sawine@5
|
198 |
project = last_project
|
sawine@6
|
199 |
elif not activity:
|
sawine@6
|
200 |
activity = DEF_ACTIVITY
|
sawine@5
|
201 |
if activity != last_activity or project != last_project:
|
sawine@6
|
202 |
end(db_file, last_project, last_activity, time)
|
sawine@6
|
203 |
con, cur = session(db_file)
|
sawine@6
|
204 |
values = (time, None, project_id(db_file, project),
|
sawine@6
|
205 |
activity_id(db_file, activity), None)
|
sawine@6
|
206 |
sql = "insert into tasks values(?, ?, ?, ?, ?)"
|
sawine@6
|
207 |
cur.execute(sql, values)
|
sawine@6
|
208 |
con.commit()
|
sawine@6
|
209 |
cur.close()
|
sawine@6
|
210 |
test(db_file)
|
sawine@8
|
211 |
|
sawine@8
|
212 |
def is_paused(db_file, time):
|
sawine@8
|
213 |
con, cur = session(db_file)
|
sawine@8
|
214 |
sql = "select rowid from breaks where end is null"
|
sawine@8
|
215 |
cur.execute(sql)
|
sawine@8
|
216 |
paused = cur.fetchone()
|
sawine@8
|
217 |
cur.close()
|
sawine@8
|
218 |
return paused
|
sawine@8
|
219 |
|
sawine@8
|
220 |
def pause(db_file, project, activity, time):
|
sawine@8
|
221 |
print "pausing"
|
sawine@8
|
222 |
if not is_paused(db_file, time):
|
sawine@8
|
223 |
project = find_last_project(db_file, time)
|
sawine@8
|
224 |
activity = find_last_activity(db_file, time)
|
sawine@8
|
225 |
con, cur = session(db_file)
|
sawine@8
|
226 |
values = (time, None, project_id(db_file, project),
|
sawine@8
|
227 |
activity_id(db_file, activity), None)
|
sawine@8
|
228 |
sql = "insert into breaks values(?, ?, ?, ?, ?)"
|
sawine@8
|
229 |
cur.execute(sql, values)
|
sawine@8
|
230 |
con.commit()
|
sawine@8
|
231 |
cur.close()
|
sawine@8
|
232 |
test(db_file)
|
sawine@8
|
233 |
|
sawine@8
|
234 |
def resume(db_file, project, activity, time):
|
sawine@8
|
235 |
print "resuming"
|
sawine@8
|
236 |
if is_paused(db_file, time):
|
sawine@8
|
237 |
task = find_active_task(db_file)
|
sawine@8
|
238 |
activity = find_last_activity(db_file, time)
|
sawine@8
|
239 |
project = find_last_project(db_file, time)
|
sawine@8
|
240 |
con, cur = session(db_file)
|
sawine@8
|
241 |
values = (time, project_id(db_file, project), activity_id(db_file, activity))
|
sawine@8
|
242 |
sql = "update breaks set end=? where project=? and activity=?"
|
sawine@8
|
243 |
cur.execute(sql, values)
|
sawine@8
|
244 |
con.commit()
|
sawine@8
|
245 |
cur.close()
|
sawine@8
|
246 |
test(db_file)
|
sawine@8
|
247 |
|
sawine@8
|
248 |
def status(db_file):
|
sawine@8
|
249 |
task = find_active_task(db_file)
|
sawine@8
|
250 |
con, cur = session(db_file)
|
sawine@8
|
251 |
result = None
|
sawine@8
|
252 |
if task:
|
sawine@8
|
253 |
values = (task,)
|
sawine@8
|
254 |
sql = "select project, activity from tasks where rowid=?"
|
sawine@8
|
255 |
cur.execute(sql, values)
|
sawine@8
|
256 |
result = cur.fetchone()
|
sawine@8
|
257 |
if result:
|
sawine@8
|
258 |
result = (id_name(db_file, "projects", result[0]),
|
sawine@8
|
259 |
id_name(db_file, "activities", result[1]))
|
sawine@8
|
260 |
return result
|
sawine@8
|
261 |
|