sawine@3
|
1 |
"""
|
sawine@3
|
2 |
Database lib for cronrec.
|
sawine@3
|
3 |
Author: Eugen Sawin (sawine@me73.com)
|
sawine@3
|
4 |
Dependencies: libsqlite3-dev
|
sawine@3
|
5 |
"""
|
sawine@3
|
6 |
|
sawine@3
|
7 |
import sqlite3
|
sawine@3
|
8 |
|
sawine@3
|
9 |
ACTIVITIES_TABLE = """activities(
|
sawine@3
|
10 |
name text not null,
|
sawine@3
|
11 |
description text)"""
|
sawine@3
|
12 |
|
sawine@3
|
13 |
COMPANIES_TABLE = """companies(
|
sawine@3
|
14 |
name text not null,
|
sawine@3
|
15 |
street text,
|
sawine@3
|
16 |
area text,
|
sawine@3
|
17 |
town text,
|
sawine@3
|
18 |
country text,
|
sawine@3
|
19 |
postal text)"""
|
sawine@3
|
20 |
|
sawine@3
|
21 |
PROJECTS_TABLE = """projects(
|
sawine@3
|
22 |
name text not null,
|
sawine@3
|
23 |
number int,
|
sawine@3
|
24 |
customer int,
|
sawine@3
|
25 |
constraint customer_fk foreign key(customer) references companies(rowid) on delete
|
sawine@3
|
26 |
cascade)"""
|
sawine@3
|
27 |
|
sawine@3
|
28 |
RATES_TABLE = """rates(
|
sawine@3
|
29 |
rate smallmoney
|
sawine@3
|
30 |
flatrate smallmoney,
|
sawine@3
|
31 |
project int,
|
sawine@3
|
32 |
activity int,
|
sawine@3
|
33 |
constraint project_fk foreign key(project) references projects(rowid) on delete
|
sawine@3
|
34 |
cascade,
|
sawine@3
|
35 |
constraint activity_fk foreign key(activity) references activities(rowid) on delete
|
sawine@3
|
36 |
cascade)"""
|
sawine@3
|
37 |
|
sawine@3
|
38 |
TASKS_TABLE = """tasks(
|
sawine@3
|
39 |
begin datetime,
|
sawine@3
|
40 |
end datetime,
|
sawine@3
|
41 |
project int,
|
sawine@3
|
42 |
activity int,
|
sawine@3
|
43 |
log text,
|
sawine@3
|
44 |
constraint project_fk foreign key(project) references projects(rowid) on delete
|
sawine@3
|
45 |
cascade,
|
sawine@3
|
46 |
constraint activity_fk foreign key(activity) references activities(rowid) on delete
|
sawine@3
|
47 |
cascade)"""
|
sawine@3
|
48 |
|
sawine@3
|
49 |
TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,
|
sawine@3
|
50 |
TASKS_TABLE)
|
sawine@3
|
51 |
|
sawine@4
|
52 |
DEF_PROJECT = "default"
|
sawine@4
|
53 |
DEF_ACTIVITY = "default"
|
sawine@4
|
54 |
|
sawine@3
|
55 |
def session(db_file):
|
sawine@3
|
56 |
con = sqlite3.connect(db_file)
|
sawine@3
|
57 |
return con, con.cursor()
|
sawine@3
|
58 |
|
sawine@3
|
59 |
def test_tables(db_file):
|
sawine@3
|
60 |
con, cur = session(db_file)
|
sawine@3
|
61 |
sql = "select * from sqlite_master where type='table'"
|
sawine@3
|
62 |
cur.execute(sql)
|
sawine@3
|
63 |
for c in cur:
|
sawine@3
|
64 |
print c
|
sawine@3
|
65 |
|
sawine@3
|
66 |
def init(db_file):
|
sawine@3
|
67 |
con, cur = session(db_file)
|
sawine@3
|
68 |
for table in TABLES:
|
sawine@3
|
69 |
sql = "create table " + table
|
sawine@3
|
70 |
cur.execute(sql)
|
sawine@3
|
71 |
con.commit()
|
sawine@3
|
72 |
cur.close()
|
sawine@3
|
73 |
|
sawine@4
|
74 |
def last_project(db_file, time):
|
sawine@4
|
75 |
con, cur = session(db_file)
|
sawine@4
|
76 |
values = (time,)
|
sawine@4
|
77 |
sql = "select project from tasks where begin < ? order by begin desc"
|
sawine@4
|
78 |
cur.execute(sql, values)
|
sawine@4
|
79 |
id = cur.fetchone()
|
sawine@4
|
80 |
if id:
|
sawine@4
|
81 |
values = (id[0],)
|
sawine@4
|
82 |
sql = "select name from projects where rowid=?"
|
sawine@4
|
83 |
cur.execute(sql, values)
|
sawine@4
|
84 |
project = cur.fetchone()[0]
|
sawine@4
|
85 |
else:
|
sawine@4
|
86 |
project = DEF_PROJECT
|
sawine@4
|
87 |
con.commit()
|
sawine@4
|
88 |
cur.close()
|
sawine@4
|
89 |
return project
|
sawine@4
|
90 |
|
sawine@4
|
91 |
def row_id(db_file, table, name):
|
sawine@4
|
92 |
con, cur = session(db_file)
|
sawine@4
|
93 |
values = (name,)
|
sawine@4
|
94 |
sql = "select rowid from %s where name=?" % table
|
sawine@4
|
95 |
cur.execute(sql, values)
|
sawine@4
|
96 |
id = cur.fetchone()
|
sawine@4
|
97 |
if not id:
|
sawine@4
|
98 |
sql = "insert into %s(name) values(?)" % table
|
sawine@4
|
99 |
cur.execute(sql, values)
|
sawine@4
|
100 |
con.commit()
|
sawine@4
|
101 |
cur.close()
|
sawine@4
|
102 |
return row_id(db_file, table, name)
|
sawine@4
|
103 |
con.commit()
|
sawine@4
|
104 |
cur.close()
|
sawine@4
|
105 |
return id[0]
|
sawine@4
|
106 |
|
sawine@4
|
107 |
def project_id(db_file, name):
|
sawine@4
|
108 |
return row_id(db_file, "projects", name)
|
sawine@4
|
109 |
|
sawine@4
|
110 |
def activity_id(db_file, name):
|
sawine@4
|
111 |
return row_id(db_file, "activities", name)
|
sawine@4
|
112 |
|
sawine@3
|
113 |
def begin(db_file, project, activity, time):
|
sawine@4
|
114 |
if not activity:
|
sawine@4
|
115 |
activity = DEF_ACTIVITY
|
sawine@4
|
116 |
if not project:
|
sawine@4
|
117 |
project = last_project(db_file, time)
|
sawine@3
|
118 |
con, cur = session(db_file)
|
sawine@4
|
119 |
values = (time, None, project_id(db_file, project),
|
sawine@4
|
120 |
activity_id(db_file, activity), None)
|
sawine@3
|
121 |
sql = "insert into tasks values(?, ?, ?, ?, ?)"
|
sawine@3
|
122 |
cur.execute(sql, values)
|
sawine@3
|
123 |
con.commit()
|
sawine@3
|
124 |
cur.execute("select * from tasks")
|
sawine@3
|
125 |
for c in cur:
|
sawine@3
|
126 |
print c
|
sawine@3
|
127 |
con.commit()
|
sawine@3
|
128 |
cur.close()
|