Check-in [6fe726dc8e]
Not logged in

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

Overview
Comment:SQLite now supports foreign keys.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6fe726dc8e938f1f5f7e54d0df80833977a94984
User & Date: MCO 2014-02-12 15:13:55
Context
2014-07-04
11:23
Added all-new attempt at centralized time tracking project. check-in: 560965e03c user: MCO tags: trunk
2014-02-12
15:13
SQLite now supports foreign keys. check-in: 6fe726dc8e user: MCO tags: trunk
2014-02-07
17:18
Added all remaining extra files. Removed extraneous files. check-in: 5aa08a7411 user: MCO tags: trunk
Changes

Changes to Snapshooter/res/CreateDatabase.sql.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
..
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
..
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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275



CREATE TABLE IF NOT EXISTS Session
( ID					INTEGER PRIMARY KEY
, RunLocationID			INTEGER NOT NULL CONSTRAINT fk_session_runlocation REFERENCES RunLocation(ID)
, Version				CHAR
, DbVersion				CHAR
, TimeZoneBias			INTEGER
, StartupTime			CHAR NOT NULL
, PreviousShutdownTime	CHAR
, IsActive				INTEGER NOT NULL
, UnloadModeID			INTEGER CONSTRAINT fk_session_unloadmode REFERENCES UnloadMode(ID)
, Remarks				CHAR
, InsertDateTime		CHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
, LastUpdateDateTime	CHAR(20)
);

CREATE TABLE IF NOT EXISTS RunLocation
( ID				INTEGER PRIMARY KEY
................................................................................
( ID				INTEGER PRIMARY KEY
, Code				CHAR
, Description		CHAR
);

CREATE TABLE IF NOT EXISTS Executable
( ID				INTEGER PRIMARY KEY
, RunLocationID		INTEGER CONSTRAINT fk_executable_runlocation REFERENCES RunLocation(ID)
, Name              CHAR
, Path				CHAR NOT NULL
, FileDateTime		CHAR(20)
, LatestVersion		CHAR
, Description		CHAR
, Icon				BINARY
, Company			CHAR
................................................................................
, InsertDateTime	CHAR(25) NOT NULL DEFAULT CURRENT_TIMESTAMP
, UpdateDateTime	CHAR(25)
, UNIQUE (RunLocationID, Path)
);

CREATE TABLE IF NOT EXISTS Process
( ID					INTEGER PRIMARY KEY
, SessionID				INTEGER NOT NULL CONSTRAINT fk_process_session REFERENCES Session(ID)
, SysProcessID			INTEGER NOT NULL
, StartupTime			CHAR(20)
, TerminateTime 		CHAR(20)
, ExecutableID			INTEGER NOT NULL CONSTRAINT fk_process_executable REFERENCES Executable(ID)
, UserName				CHAR
, Version				CHAR
, Description			CHAR
, WindowTitle			CHAR
, InsertDateTime		CHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
, LastUpdateDateTime	CHAR(20)
, UNIQUE (SessionID, SysProcessID, StartupTime)
);

CREATE TABLE IF NOT EXISTS Window
( ID					INTEGER PRIMARY KEY
, ProcessID				INTEGER NOT NULL CONSTRAINT fk_window_process REFERENCES Process(ID)
, Handle				INTEGER NOT NULL
, Title					CHAR
, Class					CHAR
, Icon 					BLOB
, InsertDateTime		CHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
, LastUpdateDateTime	CHAR(20)
, UNIQUE (ProcessID, Handle)
);

CREATE TABLE IF NOT EXISTS Focus
( ID					INTEGER PRIMARY KEY
, WindowID				INTEGER NOT NULL CONSTRAINT fk_focus_window REFERENCES Window(ID)
, Title					CHAR
, WindowState			INTEGER
, Top					INTEGER
, Left					INTEGER
, Right					INTEGER
, Bottom				INTEGER
, IdleMilliseconds		CHAR(20) -- GetLastInputInfo
, ScreenshotFile		CHAR
, InsertDateTime		CHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
, UpdateDateTime		CHAR(20)
);


INSERT INTO UnloadMode VALUES (0, 'vbFormControlMenu', 'User has chosen Close command from Control menu box on form.');

INSERT INTO UnloadMode VALUES (1, 'vbFormCode', 'Unload method invoked from code.');

INSERT INTO UnloadMode VALUES (2, 'vbAppWindows', 'Current Windows session ending.');

INSERT INTO UnloadMode VALUES (3, 'vbAppTaskManager', 'Windows Task Manager is closing the application.');

INSERT INTO UnloadMode VALUES (4, 'vbFormMDIForm', 'MDI child form is closing because the MDI form is closing.');

INSERT INTO UnloadMode VALUES (5, 'vbFormOwner', 'The owner of the form is closing.');

INSERT INTO UnloadMode VALUES (100, 'ServiceDestroyed', 'The service was destroyed.');

INSERT INTO UnloadMode VALUES (101, 'ServiceShutdown', 'The service was shutdown.');

INSERT INTO UnloadMode VALUES (102, 'ServiceStopped', 'The service was stopped.');


-- Foreign Key Preventing insert
CREATE TRIGGER fki_Session_RunLocationID_RunLocation_ID
BEFORE INSERT ON [Session]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "Session" violates foreign key constraint "fki_Session_RunLocationID_RunLocation_ID"')
  WHERE (SELECT ID FROM RunLocation WHERE ID = NEW.RunLocationID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_Session_RunLocationID_RunLocation_ID
BEFORE UPDATE ON [Session]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "Session" violates foreign key constraint "fku_Session_RunLocationID_RunLocation_ID"')
      WHERE (SELECT ID FROM RunLocation WHERE ID = NEW.RunLocationID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_Session_RunLocationID_RunLocation_ID
BEFORE DELETE ON RunLocation
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "RunLocation" violates foreign key constraint "fkd_Session_RunLocationID_RunLocation_ID"')
  WHERE (SELECT RunLocationID FROM Session WHERE RunLocationID = OLD.ID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_Session_UnloadModeID_UnloadMode_ID
BEFORE INSERT ON [Session]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "Session" violates foreign key constraint "fki_Session_UnloadModeID_UnloadMode_ID"')
  WHERE NEW.UnloadModeID IS NOT NULL AND (SELECT ID FROM UnloadMode WHERE ID = NEW.UnloadModeID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_Session_UnloadModeID_UnloadMode_ID
BEFORE UPDATE ON [Session]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "Session" violates foreign key constraint "fku_Session_UnloadModeID_UnloadMode_ID"')
      WHERE NEW.UnloadModeID IS NOT NULL AND (SELECT ID FROM UnloadMode WHERE ID = NEW.UnloadModeID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_Session_UnloadModeID_UnloadMode_ID
BEFORE DELETE ON UnloadMode
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "UnloadMode" violates foreign key constraint "fkd_Session_UnloadModeID_UnloadMode_ID"')
  WHERE (SELECT UnloadModeID FROM Session WHERE UnloadModeID = OLD.ID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_Executable_RunLocationID_RunLocation_ID
BEFORE INSERT ON [Executable]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "Executable" violates foreign key constraint "fki_Executable_RunLocationID_RunLocation_ID"')
  WHERE NEW.RunLocationID IS NOT NULL AND (SELECT ID FROM RunLocation WHERE ID = NEW.RunLocationID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_Executable_RunLocationID_RunLocation_ID
BEFORE UPDATE ON [Executable]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "Executable" violates foreign key constraint "fku_Executable_RunLocationID_RunLocation_ID"')
      WHERE NEW.RunLocationID IS NOT NULL AND (SELECT ID FROM RunLocation WHERE ID = NEW.RunLocationID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_Executable_RunLocationID_RunLocation_ID
BEFORE DELETE ON RunLocation
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "RunLocation" violates foreign key constraint "fkd_Executable_RunLocationID_RunLocation_ID"')
  WHERE (SELECT RunLocationID FROM Executable WHERE RunLocationID = OLD.ID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_Process_SessionID_Session_ID
BEFORE INSERT ON [Process]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "Process" violates foreign key constraint "fki_Process_SessionID_Session_ID"')
  WHERE (SELECT ID FROM Session WHERE ID = NEW.SessionID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_Process_SessionID_Session_ID
BEFORE UPDATE ON [Process]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "Process" violates foreign key constraint "fku_Process_SessionID_Session_ID"')
      WHERE (SELECT ID FROM Session WHERE ID = NEW.SessionID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_Process_SessionID_Session_ID
BEFORE DELETE ON Session
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "Session" violates foreign key constraint "fkd_Process_SessionID_Session_ID"')
  WHERE (SELECT SessionID FROM Process WHERE SessionID = OLD.ID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_Process_ExecutableID_Executable_ID
BEFORE INSERT ON [Process]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "Process" violates foreign key constraint "fki_Process_ExecutableID_Executable_ID"')
  WHERE (SELECT ID FROM Executable WHERE ID = NEW.ExecutableID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_Process_ExecutableID_Executable_ID
BEFORE UPDATE ON [Process]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "Process" violates foreign key constraint "fku_Process_ExecutableID_Executable_ID"')
      WHERE (SELECT ID FROM Executable WHERE ID = NEW.ExecutableID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_Process_ExecutableID_Executable_ID
BEFORE DELETE ON Executable
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "Executable" violates foreign key constraint "fkd_Process_ExecutableID_Executable_ID"')
  WHERE (SELECT ExecutableID FROM Process WHERE ExecutableID = OLD.ID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_Window_ProcessID_Process_ID
BEFORE INSERT ON [Window]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "Window" violates foreign key constraint "fki_Window_ProcessID_Process_ID"')
  WHERE (SELECT ID FROM Process WHERE ID = NEW.ProcessID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_Window_ProcessID_Process_ID
BEFORE UPDATE ON [Window]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "Window" violates foreign key constraint "fku_Window_ProcessID_Process_ID"')
      WHERE (SELECT ID FROM Process WHERE ID = NEW.ProcessID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_Window_ProcessID_Process_ID
BEFORE DELETE ON Process
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "Process" violates foreign key constraint "fkd_Window_ProcessID_Process_ID"')
  WHERE (SELECT ProcessID FROM Window WHERE ProcessID = OLD.ID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_Focus_WindowID_Window_ID
BEFORE INSERT ON [Focus]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "Focus" violates foreign key constraint "fki_Focus_WindowID_Window_ID"')
  WHERE (SELECT ID FROM [Window] WHERE ID = NEW.WindowID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_Focus_WindowID_Window_ID
BEFORE UPDATE ON [Focus]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "Focus" violates foreign key constraint "fku_Focus_WindowID_Window_ID"')
      WHERE (SELECT ID FROM Window WHERE ID = NEW.WindowID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_Focus_WindowID_Window_ID
BEFORE DELETE ON Window
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "Window" violates foreign key constraint "fkd_Focus_WindowID_Window_ID"')
  WHERE (SELECT WindowID FROM Focus WHERE WindowID = OLD.ID) IS NOT NULL;
END;

>
>


|






|







 







|







 







|



|











|











|






|





>

<

<

<

<

<

<

<

<

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
..
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
..
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
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS Session
( ID					INTEGER PRIMARY KEY
, RunLocationID			INTEGER NOT NULL REFERENCES RunLocation(ID)
, Version				CHAR
, DbVersion				CHAR
, TimeZoneBias			INTEGER
, StartupTime			CHAR NOT NULL
, PreviousShutdownTime	CHAR
, IsActive				INTEGER NOT NULL
, UnloadModeID			INTEGER REFERENCES UnloadMode(ID)
, Remarks				CHAR
, InsertDateTime		CHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
, LastUpdateDateTime	CHAR(20)
);

CREATE TABLE IF NOT EXISTS RunLocation
( ID				INTEGER PRIMARY KEY
................................................................................
( ID				INTEGER PRIMARY KEY
, Code				CHAR
, Description		CHAR
);

CREATE TABLE IF NOT EXISTS Executable
( ID				INTEGER PRIMARY KEY
, RunLocationID		INTEGER REFERENCES RunLocation(ID)
, Name              CHAR
, Path				CHAR NOT NULL
, FileDateTime		CHAR(20)
, LatestVersion		CHAR
, Description		CHAR
, Icon				BINARY
, Company			CHAR
................................................................................
, InsertDateTime	CHAR(25) NOT NULL DEFAULT CURRENT_TIMESTAMP
, UpdateDateTime	CHAR(25)
, UNIQUE (RunLocationID, Path)
);

CREATE TABLE IF NOT EXISTS Process
( ID					INTEGER PRIMARY KEY
, SessionID				INTEGER NOT NULL REFERENCES Session(ID)
, SysProcessID			INTEGER NOT NULL
, StartupTime			CHAR(20)
, TerminateTime 		CHAR(20)
, ExecutableID			INTEGER NOT NULL REFERENCES Executable(ID)
, UserName				CHAR
, Version				CHAR
, Description			CHAR
, WindowTitle			CHAR
, InsertDateTime		CHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
, LastUpdateDateTime	CHAR(20)
, UNIQUE (SessionID, SysProcessID, StartupTime)
);

CREATE TABLE IF NOT EXISTS Window
( ID					INTEGER PRIMARY KEY
, ProcessID				INTEGER NOT NULL REFERENCES Process(ID)
, Handle				INTEGER NOT NULL
, Title					CHAR
, Class					CHAR
, Icon 					BLOB
, InsertDateTime		CHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
, LastUpdateDateTime	CHAR(20)
, UNIQUE (ProcessID, Handle)
);

CREATE TABLE IF NOT EXISTS Focus
( ID					INTEGER PRIMARY KEY
, WindowID				INTEGER NOT NULL REFERENCES Window(ID)
, Title					CHAR
, WindowState			INTEGER
, Top					INTEGER
, Left					INTEGER
, Right					INTEGER
, Bottom				INTEGER
, IdleMilliseconds		INTEGER -- GetLastInputInfo
, ScreenshotFile		CHAR
, InsertDateTime		CHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
, UpdateDateTime		CHAR(20)
);

BEGIN TRANSACTION;
INSERT INTO UnloadMode VALUES (0, 'vbFormControlMenu', 'User has chosen Close command from Control menu box on form.');

INSERT INTO UnloadMode VALUES (1, 'vbFormCode', 'Unload method invoked from code.');

INSERT INTO UnloadMode VALUES (2, 'vbAppWindows', 'Current Windows session ending.');

INSERT INTO UnloadMode VALUES (3, 'vbAppTaskManager', 'Windows Task Manager is closing the application.');

INSERT INTO UnloadMode VALUES (4, 'vbFormMDIForm', 'MDI child form is closing because the MDI form is closing.');

INSERT INTO UnloadMode VALUES (5, 'vbFormOwner', 'The owner of the form is closing.');

INSERT INTO UnloadMode VALUES (100, 'ServiceDestroyed', 'The service was destroyed.');

INSERT INTO UnloadMode VALUES (101, 'ServiceShutdown', 'The service was shutdown.');

INSERT INTO UnloadMode VALUES (102, 'ServiceStopped', 'The service was stopped.');









































































































































































COMMIT TRANSACTION;