Added add command.
2 Database lib for cronrec.
3 Author: Eugen Sawin (sawine@me73.com)
4 Dependencies: libsqlite3-dev
9 ACTIVITIES_TABLE = """activities(
13 COMPANIES_TABLE = """companies(
21 PROJECTS_TABLE = """projects(
25 constraint customer_fk foreign key(customer) references companies(rowid) on delete
28 RATES_TABLE = """rates(
33 constraint project_fk foreign key(project) references projects(rowid) on delete
35 constraint activity_fk foreign key(activity) references activities(rowid) on delete
38 TASKS_TABLE = """tasks(
44 constraint project_fk foreign key(project) references projects(rowid) on delete
46 constraint activity_fk foreign key(activity) references activities(rowid) on delete
49 BREAKS_TABLE = """breaks(
54 constraint task_fk foreign key(task) references tasks(rowid) on delete
57 TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,
58 TASKS_TABLE, BREAKS_TABLE)
60 DEF_PROJECT = "default"
61 DEF_ACTIVITY = "default"
64 con = sqlite3.connect(db_file)
65 return con, con.cursor()
68 con, cur = session(db_file)
69 cur.execute("select * from projects")
73 cur.execute("select * from activities")
77 cur.execute("select * from tasks")
81 cur.execute("select * from breaks")
89 con, cur = session(db_file)
91 sql = "create table " + table
96 def find_last_project(db_file, time):
97 con, cur = session(db_file)
99 sql = "select project from tasks where begin < ? and end is null order by \
101 cur.execute(sql, values)
105 sql = "select name from projects where rowid=?"
106 cur.execute(sql, values)
107 project = cur.fetchone()[0]
109 project = None#DEF_PROJECT
114 def find_last_activity(db_file, time):
115 con, cur = session(db_file)
117 sql = "select activity from tasks where begin < ? and end is null order by \
119 cur.execute(sql, values)
123 sql = "select name from activities where rowid=?"
124 cur.execute(sql, values)
125 activity = cur.fetchone()[0]
127 activity = None#DEF_ACTIVITY
132 def find_active_task(db_file):
133 con, cur = session(db_file)
134 sql = "select project, activity from tasks where end is null"
136 task = cur.fetchone()
142 def id_name(db_file, table, id):
143 con, cur = session(db_file)
145 sql = "select name from %s where rowid=?" % table
146 cur.execute(sql, values)
151 def row_id(db_file, table, name):
152 con, cur = session(db_file)
154 sql = "select rowid from %s where name=?" % table
155 cur.execute(sql, values)
158 sql = "insert into %s(name) values(?)" % table
159 cur.execute(sql, values)
162 return row_id(db_file, table, name)
167 def project_id(db_file, name):
168 return row_id(db_file, "projects", name)
170 def activity_id(db_file, name):
171 return row_id(db_file, "activities", name)
173 def end(db_file, project, activity, time, log):
174 print "ending: %s:%s" % (project, activity)
175 if activity or project:
177 activity = find_last_activity(db_file, time)
179 project = find_last_project(db_file, time)
181 con, cur = session(db_file)
182 if activity == "all" and project == "all":
184 sql = "update tasks set end=?, log=? where end is null"
186 values = (time, log, project_id(db_file, project),
187 activity_id(db_file, activity))
188 sql = "update tasks set end=?, log=? where project=? and activity=?"
189 cur.execute(sql, values)
194 def begin(db_file, project, activity, time):
195 print "beginning: %s:%s" % (project, activity)
196 last_project = find_last_project(db_file, time)
197 last_activity = find_last_activity(db_file, time)
199 project = last_project
201 activity = DEF_ACTIVITY
202 if activity != last_activity or project != last_project:
203 end(db_file, last_project, last_activity, time, None)
204 con, cur = session(db_file)
205 values = (time, None, project_id(db_file, project),
206 activity_id(db_file, activity), None)
207 sql = "insert into tasks values(?, ?, ?, ?, ?)"
208 cur.execute(sql, values)
213 def is_paused(db_file, time):
214 con, cur = session(db_file)
215 sql = "select rowid from breaks where end is null"
217 paused = cur.fetchone()
221 def pause(db_file, project, activity, time):
223 if not is_paused(db_file, time):
224 task = find_active_task(db_file)
225 con, cur = session(db_file)
226 values = (time, None, task, None)
227 sql = "insert into breaks values(?, ?, ?, ?)"
228 cur.execute(sql, values)
233 def resume(db_file, task, time, log):
235 if is_paused(db_file, time):
236 con, cur = session(db_file)
237 values = (time, log, task)
238 sql = "update breaks set end=?, log=? where task=?"
239 cur.execute(sql, values)
245 task = find_active_task(db_file)
246 con, cur = session(db_file)
250 sql = "select project, activity from tasks where rowid=?"
251 cur.execute(sql, values)
252 result = cur.fetchone()
254 result = (id_name(db_file, "projects", result[0]),
255 id_name(db_file, "activities", result[1]))