Mercurialcronrec / file revision
summary | shortlog | changelog | graph | tags | bookmarks | branches | files | changeset | file | latest | revisions | annotate | diff | raw | help
db.py
author Eugen Sawin <sawine@me73.com>
Fri, 01 Oct 2010 18:59:45 +0200
changeset 6 6f89b07e12cc
parent 5 9d0e6ae739cf
child 7 878956edb936
permissions -rw-r--r--
Fixed some issues with (auto) beginning and ending. 
     1 """

     2 Database lib for cronrec.

     3 Author: Eugen Sawin (sawine@me73.com)

     4 Dependencies: libsqlite3-dev

     5 """

     6 

     7 import sqlite3

     8 

     9 ACTIVITIES_TABLE = """activities(

    10 name text not null,

    11 description text)"""

    12 

    13 COMPANIES_TABLE = """companies(

    14 name text not null,

    15 street text,

    16 area text,

    17 town text,

    18 country text,

    19 postal text)"""

    20 

    21 PROJECTS_TABLE = """projects(

    22 name text not null,

    23 number int,

    24 customer int,

    25 constraint customer_fk foreign key(customer) references companies(rowid) on delete

    26 cascade)"""

    27 

    28 RATES_TABLE = """rates(

    29 rate smallmoney

    30 flatrate smallmoney,

    31 project int,

    32 activity int,

    33 constraint project_fk foreign key(project) references projects(rowid) on delete

    34 cascade,

    35 constraint activity_fk foreign key(activity) references activities(rowid) on delete

    36 cascade)"""

    37 

    38 TASKS_TABLE = """tasks(

    39 begin datetime,

    40 end datetime,

    41 project int,

    42 activity int,

    43 log text,

    44 constraint project_fk foreign key(project) references projects(rowid) on delete

    45 cascade,

    46 constraint activity_fk foreign key(activity) references activities(rowid) on delete

    47 cascade)"""

    48 

    49 TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,

    50 TASKS_TABLE)

    51 

    52 DEF_PROJECT = "default"

    53 DEF_ACTIVITY = "default"

    54 

    55 def session(db_file):

    56 	con = sqlite3.connect(db_file)

    57 	return con, con.cursor()

    58 

    59 def test(db_file):

    60 	con, cur = session(db_file)

    61 	cur.execute("select * from projects")

    62 	print "\nprojects"

    63 	for c in cur:

    64 		print c

    65 	cur.execute("select * from activities")

    66 	print "\nactivities"

    67 	for c in cur:

    68 		print c

    69 	cur.execute("select * from tasks")

    70 	print "\ntasks"

    71 	for c in cur:

    72 		print c

    73 	con.commit()

    74 	cur.close()

    75 

    76 def init(db_file):

    77 	con, cur = session(db_file)

    78 	for table in TABLES:

    79 		sql = "create table " + table

    80 		cur.execute(sql)

    81 	con.commit()

    82 	cur.close()

    83 

    84 def find_last_project(db_file, time):

    85 	con, cur = session(db_file)

    86 	values = (time,)

    87 	sql = "select project from tasks where begin < ? and end is null order by \

    88 begin desc"

    89 	cur.execute(sql, values)

    90 	id = cur.fetchone()

    91 	if id:

    92 		values = (id[0],)

    93 		sql = "select name from projects where rowid=?"

    94 		cur.execute(sql, values)

    95 		project = cur.fetchone()[0]

    96 	else:

    97 		project = None#DEF_PROJECT

    98 	con.commit()

    99 	cur.close()

   100 	return project

   101 

   102 def find_last_activity(db_file, time):

   103 	con, cur = session(db_file)

   104 	values = (time,)

   105 	sql = "select activity from tasks where begin < ? and end is null order by \

   106 begin desc"

   107 	cur.execute(sql, values)

   108 	id = cur.fetchone()

   109 	if id:

   110 		values = (id[0],)

   111 		sql = "select name from activities where rowid=?"

   112 		cur.execute(sql, values)

   113 		activity = cur.fetchone()[0]

   114 	else:

   115 		activity = None#DEF_ACTIVITY

   116 	con.commit()

   117 	cur.close()

   118 	return activity

   119 

   120 def row_id(db_file, table, name):

   121 	con, cur = session(db_file)

   122 	values = (name,)

   123 	sql = "select rowid from %s where name=?" % table

   124 	cur.execute(sql, values)

   125 	id = cur.fetchone()

   126 	if not id:

   127 		sql = "insert into %s(name) values(?)" % table

   128 		cur.execute(sql, values)

   129 		con.commit()

   130 		cur.close()

   131 		return row_id(db_file, table, name)

   132 	con.commit()

   133 	cur.close()

   134 	return id[0]

   135 

   136 def project_id(db_file, name):

   137 	return row_id(db_file, "projects", name)

   138 

   139 def activity_id(db_file, name):

   140 	return row_id(db_file, "activities", name)

   141 

   142 def end(db_file, project, activity, time):

   143 	print "ending: %s:%s" % (project, activity)

   144 	if activity or project:		

   145 		if not activity:

   146 			activity = find_last_activity(db_file, time)

   147 		elif not project:

   148 			project = find_last_project(db_file, time) 	

   149 		con, cur = session(db_file)

   150 		values = (time, project_id(db_file, project), activity_id(db_file, activity))

   151 		sql = "update tasks set end=? where project=? and activity=?"

   152 		cur.execute(sql, values)

   153 		con.commit()

   154 		cur.close()

   155 	test(db_file)

   156 

   157 def begin(db_file, project, activity, time):

   158 	print "beginning: %s:%s" % (project, activity)

   159 	last_project = find_last_project(db_file, time) 

   160 	last_activity = find_last_activity(db_file, time)	

   161 	if not project:

   162 		project = last_project

   163 	elif not activity:

   164 			activity = DEF_ACTIVITY

   165 	if activity != last_activity or project != last_project:

   166 		end(db_file, last_project, last_activity, time)

   167 		con, cur = session(db_file)

   168 		values = (time, None, project_id(db_file, project), 

   169 					activity_id(db_file, activity), None)

   170 		sql = "insert into tasks values(?, ?, ?, ?, ?)"

   171 		cur.execute(sql, values)

   172 		con.commit()

   173 		cur.close()

   174 	test(db_file)
cronrec