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: |
6fe726dc8e938f1f5f7e54d0df808339 |
| 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; |