1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/db.py	Fri Oct 01 00:45:07 2010 +0200
     1.3 @@ -0,0 +1,81 @@
     1.4 +"""
     1.5 +Database lib for cronrec.
     1.6 +Author: Eugen Sawin (sawine@me73.com)
     1.7 +Dependencies: libsqlite3-dev
     1.8 +"""
     1.9 +
    1.10 +import sqlite3
    1.11 +
    1.12 +ACTIVITIES_TABLE = """activities(
    1.13 +name text not null,
    1.14 +description text)"""
    1.15 +
    1.16 +COMPANIES_TABLE = """companies(
    1.17 +name text not null,
    1.18 +street text,
    1.19 +area text,
    1.20 +town text,
    1.21 +country text,
    1.22 +postal text)"""
    1.23 +
    1.24 +PROJECTS_TABLE = """projects(
    1.25 +name text not null,
    1.26 +number int,
    1.27 +customer int,
    1.28 +constraint customer_fk foreign key(customer) references companies(rowid) on delete
    1.29 +cascade)"""
    1.30 +
    1.31 +RATES_TABLE = """rates(
    1.32 +rate smallmoney
    1.33 +flatrate smallmoney,
    1.34 +project int,
    1.35 +activity int,
    1.36 +constraint project_fk foreign key(project) references projects(rowid) on delete
    1.37 +cascade,
    1.38 +constraint activity_fk foreign key(activity) references activities(rowid) on delete
    1.39 +cascade)"""
    1.40 +
    1.41 +TASKS_TABLE = """tasks(
    1.42 +begin datetime,
    1.43 +end datetime,
    1.44 +project int,
    1.45 +activity int,
    1.46 +log text,
    1.47 +constraint project_fk foreign key(project) references projects(rowid) on delete
    1.48 +cascade,
    1.49 +constraint activity_fk foreign key(activity) references activities(rowid) on delete
    1.50 +cascade)"""
    1.51 +
    1.52 +TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,
    1.53 +TASKS_TABLE)
    1.54 +
    1.55 +def session(db_file):
    1.56 +	con = sqlite3.connect(db_file)
    1.57 +	return con, con.cursor()
    1.58 +
    1.59 +def test_tables(db_file):
    1.60 +	con, cur = session(db_file)
    1.61 +	sql = "select * from sqlite_master where type='table'"
    1.62 +	cur.execute(sql)
    1.63 +	for c in cur:
    1.64 +		print c
    1.65 +
    1.66 +def init(db_file):
    1.67 +	con, cur = session(db_file)
    1.68 +	for table in TABLES:
    1.69 +		sql = "create table " + table
    1.70 +		cur.execute(sql)
    1.71 +	con.commit()
    1.72 +	cur.close()
    1.73 +
    1.74 +def begin(db_file, project, activity, time):
    1.75 +	con, cur = session(db_file)
    1.76 +	values = (time, None, project, activity, None)
    1.77 +	sql = "insert into tasks values(?, ?, ?, ?, ?)"
    1.78 +	cur.execute(sql, values)
    1.79 +	con.commit()
    1.80 +	cur.execute("select * from tasks")
    1.81 +	for c in cur:
    1.82 +		print c
    1.83 +	con.commit()
    1.84 +	cur.close()