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@3: TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,
sawine@3: TASKS_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@3: def test_tables(db_file):
sawine@3: 	con, cur = session(db_file)
sawine@3: 	sql = "select * from sqlite_master where type='table'"
sawine@3: 	cur.execute(sql)
sawine@3: 	for c in cur:
sawine@3: 		print c
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@4: 		project = 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@5: 		activity = DEF_ACTIVITY
sawine@5: 	con.commit()
sawine@5: 	cur.close()
sawine@5: 	return activity
sawine@5: 
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@5: def end(db_file, project, activity, time):
sawine@5: 	if not activity:
sawine@5: 		activity = find_last_activity(db_file, time)
sawine@5: 	if not project:
sawine@5: 		project = find_last_project(db_file, time) 
sawine@5: 	con, cur = session(db_file)
sawine@5: 	values = (time, project_id(db_file, project), activity_id(db_file, activity))
sawine@5: 	sql = "update tasks set end=? where project=? and activity=?"
sawine@5: 	cur.execute(sql, values)
sawine@5: 	con.commit()
sawine@5: 	cur.execute("select * from tasks")
sawine@5: 	for c in cur:
sawine@5: 		print c
sawine@5: 	cur.close()
sawine@5: 
sawine@3: def begin(db_file, project, activity, time):
sawine@5: 	last_project = find_last_project(db_file, time) 
sawine@5: 	last_activity = find_last_activity(db_file, time)
sawine@4: 	if not activity:
sawine@4: 		activity = DEF_ACTIVITY
sawine@4: 	if not project:
sawine@5: 		project = last_project
sawine@5: 	if activity != last_activity or project != last_project:
sawine@5: 		end(db_file, project, last_activity, time)
sawine@3: 	con, cur = session(db_file)
sawine@4: 	values = (time, None, project_id(db_file, project), 
sawine@4: 				activity_id(db_file, activity), None)
sawine@3: 	sql = "insert into tasks values(?, ?, ?, ?, ?)"
sawine@3: 	cur.execute(sql, values)
sawine@3: 	con.commit()
sawine@3: 	cur.execute("select * from tasks")
sawine@3: 	for c in cur:
sawine@3: 		print c
sawine@3: 	con.commit()
sawine@3: 	cur.close()