1.1 --- a/cronrec.py	Thu Sep 30 16:19:14 2010 +0200
     1.2 +++ b/cronrec.py	Fri Oct 01 00:45:07 2010 +0200
     1.3 @@ -10,7 +10,8 @@
     1.4  import sys
     1.5  import argparse
     1.6  from datetime import datetime
     1.7 -import sqlite3
     1.8 +
     1.9 +import db
    1.10  
    1.11  WD = "working_path"
    1.12  CONFIG = {WD: str}
    1.13 @@ -31,14 +32,6 @@
    1.14  DEF_PROJECT = "default"
    1.15  DEF_ACTIVITY = "default"
    1.16  
    1.17 -def db_session():
    1.18 -	global config
    1.19 -	if WD not in config:
    1.20 -		print "Error: working directory is not configured. Use init command."
    1.21 -		sys.exit()
    1.22 -	con = sqlite3.connect(config[WD] + "/" + DB_FILE)
    1.23 -	return con, con.cursor()
    1.24 -
    1.25  def read_config():
    1.26  	config = {}
    1.27  	with open(CONFIG_FILE, "r") as config_stream:
    1.28 @@ -56,10 +49,14 @@
    1.29  		config_input.write("\n".join([CONFIG_SEP.join((k, v)) 
    1.30  							for k, v in config.iteritems() if k in CONFIG]))
    1.31  
    1.32 -def db_init(db_file):
    1.33 -	con, cur = db_session()
    1.34 -	sql = "create table projects()"
    1.35 -
    1.36 +def db_file():
    1.37 +	global config
    1.38 +	if WD not in config:
    1.39 +		print "Working directory path is not configured. \
    1.40 +Please use the init command."
    1.41 +		return None
    1.42 +	return config[WD] + "/" + DB_FILE
    1.43 +	
    1.44  def init(args):
    1.45  	global config
    1.46  	last_wd = None
    1.47 @@ -68,9 +65,8 @@
    1.48  	config[WD] = args.working_path
    1.49  	write_config(config)
    1.50  	path_exists = os.path.exists(config[WD]) and os.path.isdir(config[WD])
    1.51 -	db_file = config[WD] + "/" + DB_FILE
    1.52 -	db_exists = path_exists and os.path.exists(db_file) 
    1.53 -				and os.path.isfile(db_file)
    1.54 +	db_exists = path_exists and os.path.exists(db_file())\
    1.55 +				and os.path.isfile(db_file())
    1.56  	if last_wd != config[WD]:		
    1.57  		print "Changed working directory from %s to %s." % (last_wd, config[WD])
    1.58  	else:
    1.59 @@ -80,16 +76,10 @@
    1.60  		os.makedirs(config[WD])
    1.61  		print "Working directory %s created." % config[WD]
    1.62  	elif db_exists:
    1.63 -		print "Database file %s already exists, please delete it before\
    1.64 -				initiating a new database at this location." % db_file
    1.65 +		print "Database file %s already exists, please delete it before \
    1.66 +initiating a new database at this location." % db_file()
    1.67  	if not db_exists:
    1.68 -		db_init(db_file)	
    1.69 -
    1.70 -def db_begin(project, activity, time):
    1.71 -	con, cur = db_session()
    1.72 -	cur.execute("")
    1.73 -	con.commit()
    1.74 -	cur.close()
    1.75 +		db.init(db_file())	
    1.76  
    1.77  def begin(args):
    1.78  	project = None
    1.79 @@ -103,7 +93,7 @@
    1.80  		project = DEF_PROJECT
    1.81  	if not activity:
    1.82  		activity = DEF_ACTIVITY
    1.83 -	db_begin(project, activity, datetime.now())	
    1.84 +	db.begin(db_file(), project, activity, datetime.now())	
    1.85  	print "begins %s:%s" % (project, activity)
    1.86      
    1.87  def end(args):

     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/db.py	Fri Oct 01 00:45:07 2010 +0200
     2.3 @@ -0,0 +1,81 @@
     2.4 +"""
     2.5 +Database lib for cronrec.
     2.6 +Author: Eugen Sawin (sawine@me73.com)
     2.7 +Dependencies: libsqlite3-dev
     2.8 +"""
     2.9 +
    2.10 +import sqlite3
    2.11 +
    2.12 +ACTIVITIES_TABLE = """activities(
    2.13 +name text not null,
    2.14 +description text)"""
    2.15 +
    2.16 +COMPANIES_TABLE = """companies(
    2.17 +name text not null,
    2.18 +street text,
    2.19 +area text,
    2.20 +town text,
    2.21 +country text,
    2.22 +postal text)"""
    2.23 +
    2.24 +PROJECTS_TABLE = """projects(
    2.25 +name text not null,
    2.26 +number int,
    2.27 +customer int,
    2.28 +constraint customer_fk foreign key(customer) references companies(rowid) on delete
    2.29 +cascade)"""
    2.30 +
    2.31 +RATES_TABLE = """rates(
    2.32 +rate smallmoney
    2.33 +flatrate smallmoney,
    2.34 +project int,
    2.35 +activity int,
    2.36 +constraint project_fk foreign key(project) references projects(rowid) on delete
    2.37 +cascade,
    2.38 +constraint activity_fk foreign key(activity) references activities(rowid) on delete
    2.39 +cascade)"""
    2.40 +
    2.41 +TASKS_TABLE = """tasks(
    2.42 +begin datetime,
    2.43 +end datetime,
    2.44 +project int,
    2.45 +activity int,
    2.46 +log text,
    2.47 +constraint project_fk foreign key(project) references projects(rowid) on delete
    2.48 +cascade,
    2.49 +constraint activity_fk foreign key(activity) references activities(rowid) on delete
    2.50 +cascade)"""
    2.51 +
    2.52 +TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,
    2.53 +TASKS_TABLE)
    2.54 +
    2.55 +def session(db_file):
    2.56 +	con = sqlite3.connect(db_file)
    2.57 +	return con, con.cursor()
    2.58 +
    2.59 +def test_tables(db_file):
    2.60 +	con, cur = session(db_file)
    2.61 +	sql = "select * from sqlite_master where type='table'"
    2.62 +	cur.execute(sql)
    2.63 +	for c in cur:
    2.64 +		print c
    2.65 +
    2.66 +def init(db_file):
    2.67 +	con, cur = session(db_file)
    2.68 +	for table in TABLES:
    2.69 +		sql = "create table " + table
    2.70 +		cur.execute(sql)
    2.71 +	con.commit()
    2.72 +	cur.close()
    2.73 +
    2.74 +def begin(db_file, project, activity, time):
    2.75 +	con, cur = session(db_file)
    2.76 +	values = (time, None, project, activity, None)
    2.77 +	sql = "insert into tasks values(?, ?, ?, ?, ?)"
    2.78 +	cur.execute(sql, values)
    2.79 +	con.commit()
    2.80 +	cur.execute("select * from tasks")
    2.81 +	for c in cur:
    2.82 +		print c
    2.83 +	con.commit()
    2.84 +	cur.close()