sawine@3: """
sawine@3: Database lib for cronrec.
sawine@3: Author: Eugen Sawin (sawine@me73.com)
sawine@3: Dependencies: libsqlite3-dev
sawine@3: """
sawine@3: 
sawine@3: import sqlite3
sawine@3: 
sawine@3: ACTIVITIES_TABLE = """activities(
sawine@3: name text not null,
sawine@3: description text)"""
sawine@3: 
sawine@3: COMPANIES_TABLE = """companies(
sawine@3: name text not null,
sawine@3: street text,
sawine@3: area text,
sawine@3: town text,
sawine@3: country text,
sawine@3: postal text)"""
sawine@3: 
sawine@3: PROJECTS_TABLE = """projects(
sawine@3: name text not null,
sawine@3: number int,
sawine@3: customer int,
sawine@3: constraint customer_fk foreign key(customer) references companies(rowid) on delete
sawine@3: cascade)"""
sawine@3: 
sawine@3: RATES_TABLE = """rates(
sawine@3: rate smallmoney
sawine@3: flatrate smallmoney,
sawine@3: project int,
sawine@3: activity int,
sawine@3: constraint project_fk foreign key(project) references projects(rowid) on delete
sawine@3: cascade,
sawine@3: constraint activity_fk foreign key(activity) references activities(rowid) on delete
sawine@3: cascade)"""
sawine@3: 
sawine@3: TASKS_TABLE = """tasks(
sawine@3: begin datetime,
sawine@3: end datetime,
sawine@3: project int,
sawine@3: activity int,
sawine@3: log text,
sawine@3: constraint project_fk foreign key(project) references projects(rowid) on delete
sawine@3: cascade,
sawine@3: constraint activity_fk foreign key(activity) references activities(rowid) on delete
sawine@3: cascade)"""
sawine@3: 
sawine@8: BREAKS_TABLE = """breaks(
sawine@8: begin datetime,
sawine@8: end datetime,
sawine@8: task int,
sawine@8: log text,
sawine@8: constraint task_fk foreign key(task) references tasks(rowid) on delete
sawine@8: cascade)"""
sawine@8: 
sawine@3: TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,
sawine@8: TASKS_TABLE, BREAKS_TABLE)
sawine@3: 
sawine@4: DEF_PROJECT = "default"
sawine@4: DEF_ACTIVITY = "default"
sawine@4: 
sawine@3: def session(db_file):
sawine@3: 	con = sqlite3.connect(db_file)
sawine@3: 	return con, con.cursor()
sawine@3: 
sawine@6: def test(db_file):
sawine@3: 	con, cur = session(db_file)
sawine@6: 	cur.execute("select * from projects")
sawine@6: 	print "\nprojects"
sawine@3: 	for c in cur:
sawine@3: 		print c
sawine@6: 	cur.execute("select * from activities")
sawine@6: 	print "\nactivities"
sawine@6: 	for c in cur:
sawine@6: 		print c
sawine@6: 	cur.execute("select * from tasks")
sawine@6: 	print "\ntasks"
sawine@6: 	for c in cur:
sawine@6: 		print c
sawine@8: 	cur.execute("select * from breaks")
sawine@8: 	print "\nbreaks"
sawine@8: 	for c in cur:
sawine@8: 		print c
sawine@6: 	con.commit()
sawine@6: 	cur.close()
sawine@3: 
sawine@3: def init(db_file):
sawine@3: 	con, cur = session(db_file)
sawine@3: 	for table in TABLES:
sawine@3: 		sql = "create table " + table
sawine@3: 		cur.execute(sql)
sawine@3: 	con.commit()
sawine@3: 	cur.close()
sawine@3: 
sawine@5: def find_last_project(db_file, time):
sawine@4: 	con, cur = session(db_file)
sawine@4: 	values = (time,)
sawine@5: 	sql = "select project from tasks where begin < ? and end is null order by \
sawine@5: begin desc"
sawine@4: 	cur.execute(sql, values)
sawine@4: 	id = cur.fetchone()
sawine@4: 	if id:
sawine@4: 		values = (id[0],)
sawine@4: 		sql = "select name from projects where rowid=?"
sawine@4: 		cur.execute(sql, values)
sawine@5: 		project = cur.fetchone()[0]
sawine@4: 	else:
sawine@6: 		project = None#DEF_PROJECT
sawine@4: 	con.commit()
sawine@4: 	cur.close()
sawine@4: 	return project
sawine@4: 
sawine@5: def find_last_activity(db_file, time):
sawine@5: 	con, cur = session(db_file)
sawine@5: 	values = (time,)
sawine@5: 	sql = "select activity from tasks where begin < ? and end is null order by \
sawine@5: begin desc"
sawine@5: 	cur.execute(sql, values)
sawine@5: 	id = cur.fetchone()
sawine@5: 	if id:
sawine@5: 		values = (id[0],)
sawine@5: 		sql = "select name from activities where rowid=?"
sawine@5: 		cur.execute(sql, values)
sawine@5: 		activity = cur.fetchone()[0]
sawine@5: 	else:
sawine@6: 		activity = None#DEF_ACTIVITY
sawine@5: 	con.commit()
sawine@5: 	cur.close()
sawine@5: 	return activity
sawine@5: 
sawine@8: def find_active_task(db_file):
sawine@7: 	con, cur = session(db_file)
sawine@7: 	sql = "select project, activity from tasks where end is null"
sawine@7: 	cur.execute(sql)	
sawine@8: 	task = cur.fetchone()
sawine@8: 	if task:
sawine@8: 		task = task[0]	
sawine@7: 	cur.close()
sawine@8: 	return task			
sawine@7: 
sawine@7: def id_name(db_file, table, id):
sawine@7: 	con, cur = session(db_file)
sawine@7: 	values = (id,)
sawine@7: 	sql = "select name from %s where rowid=?" % table
sawine@7: 	cur.execute(sql, values)
sawine@7: 	id = cur.fetchone()
sawine@7: 	cur.close()
sawine@7: 	return id[0]
sawine@7: 
sawine@4: def row_id(db_file, table, name):
sawine@4: 	con, cur = session(db_file)
sawine@4: 	values = (name,)
sawine@4: 	sql = "select rowid from %s where name=?" % table
sawine@4: 	cur.execute(sql, values)
sawine@4: 	id = cur.fetchone()
sawine@4: 	if not id:
sawine@4: 		sql = "insert into %s(name) values(?)" % table
sawine@4: 		cur.execute(sql, values)
sawine@4: 		con.commit()
sawine@4: 		cur.close()
sawine@4: 		return row_id(db_file, table, name)
sawine@4: 	con.commit()
sawine@4: 	cur.close()
sawine@4: 	return id[0]
sawine@4: 
sawine@4: def project_id(db_file, name):
sawine@4: 	return row_id(db_file, "projects", name)
sawine@4: 
sawine@4: def activity_id(db_file, name):
sawine@4: 	return row_id(db_file, "activities", name)
sawine@4: 
sawine@9: def end(db_file, project, activity, time, log):
sawine@6: 	print "ending: %s:%s" % (project, activity)
sawine@6: 	if activity or project:		
sawine@6: 		if not activity:
sawine@6: 			activity = find_last_activity(db_file, time)
sawine@6: 		elif not project:
sawine@6: 			project = find_last_project(db_file, time) 	
sawine@7: 		
sawine@6: 		con, cur = session(db_file)
sawine@7: 		if activity == "all" and project == "all":
sawine@9: 			values = (time, log)
sawine@9: 			sql = "update tasks set end=?, log=? where end is null"
sawine@7: 		else:
sawine@9: 			values = (time, log, project_id(db_file, project), 
sawine@9: 						activity_id(db_file, activity))
sawine@9: 			sql = "update tasks set end=?, log=? where project=? and activity=?"
sawine@6: 		cur.execute(sql, values)
sawine@6: 		con.commit()
sawine@6: 		cur.close()
sawine@6: 	test(db_file)
sawine@5: 
sawine@3: def begin(db_file, project, activity, time):
sawine@6: 	print "beginning: %s:%s" % (project, activity)
sawine@5: 	last_project = find_last_project(db_file, time) 
sawine@6: 	last_activity = find_last_activity(db_file, time)	
sawine@4: 	if not project:
sawine@5: 		project = last_project
sawine@6: 	elif not activity:
sawine@6: 			activity = DEF_ACTIVITY
sawine@5: 	if activity != last_activity or project != last_project:
sawine@9: 		end(db_file, last_project, last_activity, time, None)
sawine@6: 		con, cur = session(db_file)
sawine@6: 		values = (time, None, project_id(db_file, project), 
sawine@6: 					activity_id(db_file, activity), None)
sawine@6: 		sql = "insert into tasks values(?, ?, ?, ?, ?)"
sawine@6: 		cur.execute(sql, values)
sawine@6: 		con.commit()
sawine@6: 		cur.close()
sawine@6: 	test(db_file)
sawine@8: 
sawine@8: def is_paused(db_file, time):
sawine@8: 	con, cur = session(db_file)
sawine@8: 	sql = "select rowid from breaks where end is null"
sawine@8: 	cur.execute(sql)
sawine@8: 	paused = cur.fetchone()
sawine@8: 	cur.close()
sawine@8: 	return paused
sawine@8: 
sawine@8: def pause(db_file, project, activity, time):
sawine@8: 	print "pausing" 
sawine@8: 	if not is_paused(db_file, time):
sawine@9: 		task = find_active_task(db_file)
sawine@8: 		con, cur = session(db_file)
sawine@9: 		values = (time, None, task, None)
sawine@9: 		sql = "insert into breaks values(?, ?, ?, ?)"
sawine@8: 		cur.execute(sql, values)
sawine@8: 		con.commit()
sawine@8: 		cur.close()
sawine@8: 	test(db_file)
sawine@8: 
sawine@9: def resume(db_file, task, time, log):
sawine@8: 	print "resuming"
sawine@9: 	if is_paused(db_file, time):		
sawine@8: 		con, cur = session(db_file)
sawine@9: 		values = (time, log, task)
sawine@9: 		sql = "update breaks set end=?, log=? where task=?"
sawine@8: 		cur.execute(sql, values)
sawine@8: 		con.commit()
sawine@8: 		cur.close()
sawine@8: 	test(db_file)
sawine@8: 
sawine@8: def status(db_file):
sawine@8: 	task = find_active_task(db_file)
sawine@8: 	con, cur = session(db_file)
sawine@8: 	result = None
sawine@8: 	if task:
sawine@8: 		values = (task,)
sawine@8: 		sql = "select project, activity from tasks where rowid=?"
sawine@8: 		cur.execute(sql, values)
sawine@8: 		result = cur.fetchone()
sawine@8: 		if result:
sawine@8: 			result = (id_name(db_file, "projects", result[0]),
sawine@8: 						id_name(db_file, "activities", result[1]))
sawine@8: 	return result
sawine@8: