Check-in [4d13bbac76]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added SQL file with a proposal for a data structure to be used in a more comprehensive approach to project-timekeeping.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4d13bbac7688cb1352662893b42fe3544458c1f6
User & Date: MCO 2013-12-03 12:57:47
Context
2013-12-03
14:17
Corrected triggers to work for SQLite. Made project remarks per-user. check-in: 7a29e882c9 user: MCO tags: trunk
12:57
Added SQL file with a proposal for a data structure to be used in a more comprehensive approach to project-timekeeping. check-in: 4d13bbac76 user: MCO tags: trunk
2013-11-28
12:13
Also export all data as an XML ATOM file conforming to Google Calendar's ATOM feed. check-in: 532c47695f user: MCO tags: trunk
Changes

Added doc/Generic project data structure.sql.













































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
CREATE TABLE Project
( project_id            INTEGER PRIMARY KEY
, name                  TEXT    NOT NULL    COLLATE NOCASE  UNIQUE
, code                  TEXT                COLLATE NOCASE
, description           TEXT
, customer              TEXT                COLLATE NOCASE
, remarks               TEXT
);

CREATE TABLE ProjectPeople
( projectpeople_id      INTEGER PRIMARY KEY
, project_id            INTEGER NOT NULL    REFERENCES Project
, person                TEXT    NOT NULL    COLLATE NOCASE
, user_id               INTEGER             REFERENCES User
, role                  TEXT                COLLATE NOCASE  -- REFERENCES PeopleRole ON UPDATE CASCADE
, remarks               TEXT
, UNIQUE (project_id, person, role)
);

CREATE TABLE PeopleRole
( role                  TEXT    NOT NULL    PRIMARY KEY COLLATE NOCASE
, description           TEXT
);

CREATE TABLE User
( user_id               INTEGER PRIMARY KEY
, code                  TEXT    NOT NULL    COLLATE NOCASE
, full_name             TEXT    NOT NULL    COLLATE NOCASE
, remarks               TEXT
);

CREATE TABLE Activity
( activity_id           INTEGER     PRIMARY KEY
, project_id            INTEGER     NOT NULL    REFERENCES Project
, user_id               INTEGER     NOT NULL    REFERENCES User
, remarks               TEXT
, start_time            TIMESTAMP   NOT NULL
, duration              INTEGER     NOT NULL    DEFAULT 0
, active                BOOLEAN     NOT NULL    DEFAULT 1
);

CREATE TABLE Comments
( comment_id            INTEGER     PRIMARY KEY
, text                  TEXT        NOT NULL    COLLATE NOCASE
, date_inserted         TIMESTAMP   NOT NULL
, date_updated          TIMESTAMP
);
/*

When creating tables, make sure project with ID 1 is '(pauze)';

When connecting to the database:
SELECT user_id FROM User WHERE code = :UserName;
if user_id = NULL then
    -- get UserName from login name, FullName from ... ActiveDirectory? Outlook? Windows users?
    -- see http://stackoverflow.com/a/3687647/60590
    INSERT INTO User (code, full_name) VALUES (:UserName, :FullName);
    :UserID = last_insert_rowid();

When entering an activity, check for presence of project
SELECT project_id FROM Project WHERE name = :ProjectName;
if project_id IS NULL then
    INSERT INTO Project (name) VALUES (:ProjectName);
    :ProjectID = last_insert_rowid();

When starting an activity
INSERT INTO Activity (project_id, user_id, remarks, start_time)
    SELECT :ProjectID, :UserID, :Remarks, julianday('now');
:ActivityID = last_insert_rowid();

When stopping an activity
UPDATE Activity
   SET duration = (julianday('now') - start_time) * 86400
   ,   active = 0
 WHERE activity_id = :ActivityID;

When updating an activity's remarks
UPDATE Activity
   SET remarks = :Remarks
 WHERE activity_id = :ActivityID;

When retroactively switching activities:
BEGIN;
-- delete all activities for this user that started after the :StartTime
DELETE FROM Activity
 WHERE user_id = :UserID
   AND start_time > :StartTime;
-- curtail all activities for this user that started *before* the :StartTime, AND EITHER whose 
--  duration extends until *after* the :StartTime OR which are still active.
UPDATE Activity
   SET duration = (julianday(:StartTime, 'utc') - start_time) * 86400
   ,   active = 0
 WHERE user_id = :UserID
   AND start_time <= julianday(:StartTime, 'utc')
   AND (active = 1 OR julianday(start_time, '+' || a.duration || ' seconds') > julianday(:StartTime, 'utc'))
-- and insert the new activity
INSERT INTO Activity (project_id, user_id, remarks, start_time)
    SELECT :ProjectID, :UserID, :Remarks, julianday(:StartTime, 'utc');
:ActivityID = last_insert_rowid();
COMMIT;

*/
 
/*
To allow FTS searching for projects as well as activities, perhaps add an FTS table for each:
*/
CREATE VIEW vfts_Project AS
    SELECT p.project_id
    ,      p.name, p.code, p.description, p.remarks, p.customer
    ,      group_concat(pp.person || ' (' || pp.role || ')' || ifnull(': ' || pp.remarks, ''), '; ')
      FROM Project p
           LEFT JOIN ProjectPeople pp ON p.project_id = pp.project_id
  GROUP BY p.project_id
;

CREATE VIRTUAL TABLE fts_Project USING FTS4 ( name, code, description, remarks, customer, people, tokenize=unicode61);

-- automatically update the FTS table using a few triggers
CREATE TRIGGER trg_aiu_Project
AFTER INSERT OR UPDATE ON Project
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, remarks, customer, people )
        SELECT *
          FROM vfts_Project
         WHERE project_id = NEW.project_id
    ;
END;
CREATE TRIGGER trg_aiu_ProjectPeople
AFTER INSERT OR UPDATE ON ProjectPeople
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, remarks, customer, people )
        SELECT *
          FROM vfts_Project
         WHERE project_id = NEW.project_id
    ;
END;
CREATE TRIGGER trg_au_User_Project
AFTER UPDATE OF code, full_name ON User
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, remarks, customer, people )
        SELECT *
          FROM vfts_Project
         WHERE project_id IN (SELECT DISTINCT project_id FROM ProjectPeople WHERE user_id = NEW.user_id)
    ;
END;


CREATE VIRTUAL TABLE fts_Activity USING FTS4 ( remarks, username, tokenize=unicode61 );

-- automatically update the FTS table using a few triggers
CREATE TRIGGER trg_aiu_Activity
AFTER INSERT OR UPDATE OF remarks, user_id ON Activity
BEGIN
    INSERT OR REPLACE INTO fts_Activity ( docid, remarks, username )
        SELECT a.activity_id
        ,      a.remarks
        ,      u.code || ifnull(' (' || u.full_name || ')', '')
          FROM Activity a
               LEFT JOIN User u ON a.user_id = u.user_id
         WHERE a.activity_id = NEW.activity_id
    ;
END;
CREATE TRIGGER trg_au_User_Activity
AFTER UPDATE OF code, full_name ON User
BEGIN
    INSERT OR REPLACE INTO fts_Activity ( docid, remarks, username )
        SELECT a.activity_id
        ,      a.remarks
        ,      u.code || ifnull(' (' || u.full_name || ')', '')
          FROM Activity a
               LEFT JOIN User u ON a.user_id = u.user_id
         WHERE a.user_id = NEW.user_id
    ;
END;


-- Query to perform the actual search:
SELECT p.project_id
,      p.name
,      snippet
,      datetime(a.start_time, 'localtime') AS time_start
,      datetime(a.start_time, 'localtime', '+' || a.duration || ' seconds') AS time_finish
  FROM (
    SELECT docid AS project_id
    ,      NULL AS activity_id
    ,      snippet(fts_Project) AS snippet
      FROM fts_Project
     WHERE fts_Project MATCH :SearchTerms
    UNION
    SELECT NULL AS project_id
    ,      docid AS activity_id
    ,      snippet(fts_Activity) AS snippet
      FROM fts_Activity
     WHERE fts_Activity MATCH :SearchTerms
       ) m
       LEFT JOIN Activity a ON m.activity_id = a.activity_id
       LEFT JOIN Project p ON coalesce(m.project_id, a.project_id) = p.project_id
;