目前我正在编写一些代码,将请求记录到SQLite数据库中。为了使数据库不那么臃肿,我使用了不同的表来包含不变的数据(apps
、machineID
、ips
和platforms
),这些数据可能会出现很多次,但往往是唯一的,而主表(access
)仅通过它们的ID引用其他表中的行。现在我想创建一个视图,显示其他表中的主要数据,而不是对其他表的索引。
我的表格示例:
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
现在我想要创建一个视图,其外观如下所示:
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;
感谢您的帮助!