xref: /plugin/statistics/db/update0001.sql (revision 0b8b7abaed7f89fee28f5eb20bfbf6a3d71ec2a0) 
1CREATE TABLE `access`
2(
3    `id`       INTEGER PRIMARY KEY,
4    `dt`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
5    `page`     TEXT    NOT NULL,
6    `ip`       TEXT    NOT NULL,
7    `ua`       TEXT    NOT NULL,
8    `ua_info`  TEXT    NOT NULL,
9    `ua_type`  TEXT    NOT NULL,
10    `ua_ver`   TEXT    NOT NULL,
11    `os`       TEXT    NOT NULL,
12    `ref_md5`  TEXT    NOT NULL,
13    `ref_type` TEXT    NOT NULL,
14    `ref`      TEXT    NOT NULL,
15    `screen_x` INTEGER NOT NULL,
16    `screen_y` INTEGER NOT NULL,
17    `view_x`   INTEGER NOT NULL,
18    `view_y`   INTEGER NOT NULL,
19    `user`     TEXT    NOT NULL,
20    `session`  TEXT    NOT NULL,
21    `js`       INTEGER NOT NULL DEFAULT 1,
22    `uid`      TEXT    NOT NULL DEFAULT ''
23);
24CREATE INDEX `idx_access_ref_type` ON `access` (`ref_type`);
25CREATE INDEX `idx_access_page` ON `access` (`page`);
26CREATE INDEX `idx_access_ref_md5` ON `access` (`ref_md5`);
27CREATE INDEX `idx_access_dt` ON `access` (`dt`);
28CREATE INDEX `idx_access_ua_type` ON `access` (`ua_type`);
29
30
31CREATE TABLE `iplocation`
32(
33    `ip`      TEXT PRIMARY KEY,
34    `code`    TEXT NOT NULL,
35    `country` TEXT NOT NULL,
36    `city`    TEXT NOT NULL,
37    `host`    TEXT NOT NULL,
38    `lastupd` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
39);
40CREATE INDEX `idx_iplocation_code` ON `iplocation` (`code`);
41
42CREATE TABLE `outlinks`
43(
44    `id`       INTEGER PRIMARY KEY,
45    `dt`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
46    `session`  TEXT NOT NULL,
47    `link_md5` TEXT NOT NULL,
48    `link`     TEXT NOT NULL,
49    `page`     TEXT NOT NULL DEFAULT ''
50);
51CREATE INDEX `idx_outlinks_link_md5` ON `outlinks` (`link_md5`);
52CREATE INDEX `idx_outlinks_dt` ON `outlinks` (`dt`);
53
54
55CREATE TABLE `search`
56(
57    `id`     INTEGER PRIMARY KEY,
58    `dt`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
59    `page`   TEXT NOT NULL,
60    `query`  TEXT NOT NULL,
61    `engine` TEXT NOT NULL
62);
63CREATE INDEX `idx_search_engine` ON `search` (`engine`);
64CREATE INDEX `idx_search_dt` ON `search` (`dt`);
65
66CREATE TABLE `searchwords`
67(
68    `sid`  INTEGER NOT NULL,
69    `word` TEXT    NOT NULL,
70    PRIMARY KEY (`sid`, `word`)
71);
72
73CREATE TABLE `refseen`
74(
75    `ref_md5` TEXT PRIMARY KEY,
76    `dt`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
77);
78CREATE INDEX `idx_refseen_dt` ON `refseen` (`dt`);
79
80CREATE TABLE `edits`
81(
82    `id`      INTEGER PRIMARY KEY,
83    `dt`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
84    `ip`      TEXT NOT NULL,
85    `user`    TEXT NOT NULL,
86    `session` TEXT NOT NULL,
87    `uid`     TEXT NOT NULL,
88    `page`    TEXT NOT NULL,
89    `type`    TEXT NOT NULL
90);
91CREATE INDEX `idx_edits_dt` ON `edits` (`dt`);
92CREATE INDEX `idx_edits_type` ON `edits` (`type`);
93
94
95CREATE TABLE `session`
96(
97    `session` TEXT PRIMARY KEY,
98    `dt`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
99    `end`     TEXT    NOT NULL,
100    `views`   INTEGER NOT NULL,
101    `uid`     TEXT    NOT NULL
102);
103CREATE INDEX `idx_session_dt` ON `session` (`dt`);
104CREATE INDEX `idx_session_views` ON `session` (`views`);
105CREATE INDEX `idx_session_uid` ON `session` (`uid`);
106
107
108CREATE TABLE `logins`
109(
110    `id`      INTEGER PRIMARY KEY,
111    `dt`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
112    `ip`      TEXT NOT NULL,
113    `user`    TEXT NOT NULL,
114    `session` TEXT NOT NULL,
115    `uid`     TEXT NOT NULL,
116    `type`    TEXT NOT NULL
117);
118CREATE INDEX `idx_logins_dt` ON `logins` (`dt`);
119CREATE INDEX `idx_logins_type` ON `logins` (`type`);
120
121
122CREATE TABLE `lastseen`
123(
124    `user` TEXT PRIMARY KEY,
125    `dt`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
126);
127
128CREATE TABLE `media`
129(
130    `id`      INTEGER PRIMARY KEY,
131    `dt`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
132    `media`   TEXT    NOT NULL,
133    `ip`      TEXT,
134    `ua`      TEXT    NOT NULL,
135    `ua_info` TEXT    NOT NULL,
136    `ua_type` TEXT    NOT NULL,
137    `ua_ver`  TEXT    NOT NULL,
138    `os`      TEXT    NOT NULL,
139    `user`    TEXT    NOT NULL,
140    `session` TEXT    NOT NULL,
141    `uid`     TEXT    NOT NULL,
142    `size`    INTEGER NOT NULL,
143    `mime1`   TEXT    NOT NULL,
144    `mime2`   TEXT    NOT NULL,
145    `inline`  INTEGER NOT NULL
146);
147CREATE INDEX `idx_media_media` ON `media` (`media`);
148CREATE INDEX `idx_media_dt` ON `media` (`dt`);
149CREATE INDEX `idx_media_ua_type` ON `media` (`ua_type`);
150CREATE INDEX `idx_media_mime1` ON `media` (`mime1`);
151
152
153CREATE TABLE `history`
154(
155    `info`  TEXT    NOT NULL,
156    `dt`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
157    `value` INTEGER NOT NULL,
158    PRIMARY KEY (`info`, `dt`)
159);
160
161CREATE TABLE `groups`
162(
163    `id`    INTEGER PRIMARY KEY,
164    `dt`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
165    `group` TEXT NOT NULL,
166    `type`  TEXT NOT NULL,
167    `pid`   INTEGER REFERENCES access(id)
168);
169CREATE INDEX `idx_groups_dt` ON `groups` (`dt`);
170CREATE INDEX `idx_groups_type` ON `groups` (`type`);
171
172
173CREATE TABLE `domain` (
174    id      INTEGER PRIMARY KEY,
175    dt      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
176    domain  TEXT NOT NULL,
177    type    TEXT NOT NULL,
178    pid     INTEGER REFERENCES access(id)
179);
180
181
182
183
184
185