I am currently writing some code to register queries in sqlite database. That the database was not so bloated, I use different tables containing invariant data ( apps, machineID, ipsand platforms) that can appear many times, but very often unique and the main table ( access) simply stores references to rows in other tables by their IDs. Now I want to create a view that shows the main data from other tables, and not indexes to other tables.
An example for my tables:
apps Table
----------
id application buildNum
1 app1 24.112
2 app2 24.113
machineID Table
--------------
id machineID
1 12345
2 1235
ips Table
---------
id ip
1 192.168.9.53
platforms Table
---------------
id platform os
1 windows win7
2 windows win8
access Table
------------
date ip_id machineID_id platform_id application_id responseCode
1391677790.7363 1 1 1 1 404
1391677797.5792 1 1 1 1 404
1391677800.7379 1 2 2 2 404
1391677802.493 1 2 2 2 404
1391677889.7193 1 1 1 1 404
1391677890.6034 1 2 2 2 404
Now I would like to create a view that looks like this:
date ip machineID platform os application buildNum responseCode
1391677790.7363 192.168.9.53 12345 windows win7 app1 24.112 404
1391677797.5792 192.168.9.53 12345 windows win7 app1 24.112 404
1391677800.7379 192.168.9.53 1235 windows win8 app2 24.113 404
1391677802.493 192.168.9.53 1235 windows win8 app2 24.113 404
1391677889.7193 192.168.9.53 12345 windows win7 app1 24.112 404
1391677890.6034 192.168.9.53 1235 windows win8 app2 24.113 404
, Sqlite. , , SQL.
:
BEGIN TRANSACTION;
CREATE TABLE ips (id INTEGER PRIMARY KEY,ip TEXT NOT NULL UNIQUE);
INSERT INTO "ips" VALUES(1,'192.168.9.53');
CREATE TABLE platforms (id INTEGER PRIMARY KEY,platform TEXT NOT NULL,os TEXT NOT NULL, UNIQUE(platform,os));
INSERT INTO "platforms" VALUES(1,'windows','win7');
INSERT INTO "platforms" VALUES(2,'windows','win8');
CREATE TABLE apps (id INTEGER PRIMARY KEY,application TEXT NOT NULL,buildNum TEXT not null, UNIQUE(application,buildNum));
INSERT INTO "apps" VALUES(1,'app1','24.112');
INSERT INTO "apps" VALUES(2,'app2','24.113');
CREATE TABLE machineIDs (id INTEGER PRIMARY KEY,machineID TEXT NOT NULL UNIQUE);
INSERT INTO "machineIDs" VALUES(1,'12345');
INSERT INTO "machineIDs" VALUES(2,'1235');
CREATE TABLE access (date REAL PRIMERY KEY DEFAULT ((julianday('now') - 2440587.5)*86400.0),ip_id INTEGER NOT NULL,machineID_id INTEGER,platform_id INTEGER,application_id INTEGER,responseCode INTEGER);
INSERT INTO "access" VALUES(1391677790.7363,1,1,1,1,404);
INSERT INTO "access" VALUES(1391677797.5792,1,1,1,1,404);
INSERT INTO "access" VALUES(1391677800.7379,1,2,2,2,404);
INSERT INTO "access" VALUES(1391677802.493,1,2,2,2,404);
INSERT INTO "access" VALUES(1391677889.7193,1,1,1,1,404);
INSERT INTO "access" VALUES(1391677890.6034,1,2,2,2,404);
COMMIT;
!