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 17:09:30 +0200
changeset 5 9d0e6ae739cf
parent 4 5e41c4b578da
child 6 6f89b07e12cc
permissions -rw-r--r--
Added basic end command. 
     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_tables(db_file):

    60 	con, cur = session(db_file)

    61 	sql = "select * from sqlite_master where type='table'"

    62 	cur.execute(sql)

    63 	for c in cur:

    64 		print c

    65 

    66 def init(db_file):

    67 	con, cur = session(db_file)

    68 	for table in TABLES:

    69 		sql = "create table " + table

    70 		cur.execute(sql)

    71 	con.commit()

    72 	cur.close()

    73 

    74 def find_last_project(db_file, time):

    75 	con, cur = session(db_file)

    76 	values = (time,)

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

    78 begin desc"

    79 	cur.execute(sql, values)

    80 	id = cur.fetchone()

    81 	if id:

    82 		values = (id[0],)

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

    84 		cur.execute(sql, values)

    85 		project = cur.fetchone()[0]

    86 	else:

    87 		project = DEF_PROJECT

    88 	con.commit()

    89 	cur.close()

    90 	return project

    91 

    92 def find_last_activity(db_file, time):

    93 	con, cur = session(db_file)

    94 	values = (time,)

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

    96 begin desc"

    97 	cur.execute(sql, values)

    98 	id = cur.fetchone()

    99 	if id:

   100 		values = (id[0],)

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

   102 		cur.execute(sql, values)

   103 		activity = cur.fetchone()[0]

   104 	else:

   105 		activity = DEF_ACTIVITY

   106 	con.commit()

   107 	cur.close()

   108 	return activity

   109 

   110 def row_id(db_file, table, name):

   111 	con, cur = session(db_file)

   112 	values = (name,)

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

   114 	cur.execute(sql, values)

   115 	id = cur.fetchone()

   116 	if not id:

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

   118 		cur.execute(sql, values)

   119 		con.commit()

   120 		cur.close()

   121 		return row_id(db_file, table, name)

   122 	con.commit()

   123 	cur.close()

   124 	return id[0]

   125 

   126 def project_id(db_file, name):

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

   128 

   129 def activity_id(db_file, name):

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

   131 

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

   133 	if not activity:

   134 		activity = find_last_activity(db_file, time)

   135 	if not project:

   136 		project = find_last_project(db_file, time) 

   137 	con, cur = session(db_file)

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

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

   140 	cur.execute(sql, values)

   141 	con.commit()

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

   143 	for c in cur:

   144 		print c

   145 	cur.close()

   146 

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

   148 	last_project = find_last_project(db_file, time) 

   149 	last_activity = find_last_activity(db_file, time)

   150 	if not activity:

   151 		activity = DEF_ACTIVITY

   152 	if not project:

   153 		project = last_project

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

   155 		end(db_file, project, last_activity, time)

   156 	con, cur = session(db_file)

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

   158 				activity_id(db_file, activity), None)

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

   160 	cur.execute(sql, values)

   161 	con.commit()

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

   163 	for c in cur:

   164 		print c

   165 	con.commit()

   166 	cur.close()
cronrec