Presently I am writting a bit of code for logging requests into a sqlite database.
To get the database to be not so bloated up I am using different tables containing the invariant data(apps,machineID,ips and platforms) which can appear a lot of times, but are very often unique and the main table (access) just keeps references on rows in the other tables by their ids. Now I want to create a view that shows the main data from the other tables instead of the indexes to the other tables.
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
Any clues on how to do this with Sqlite. Sorry if this might look like a novice question, but I am not so familiar with SQL.
Here would be the code for setting up the example tables:
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;
Thanks for your help!
