1CREATE TABLE `stats_access` (
2  `id`       BIGINT(20) UNSIGNED     NOT NULL AUTO_INCREMENT,
3  `dt`       TIMESTAMP               NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4  `page`     VARCHAR(255)
5             COLLATE utf8_unicode_ci NOT NULL,
6  `ip`       VARCHAR(40)
7             COLLATE utf8_unicode_ci NOT NULL,
8  `ua`       VARCHAR(255)
9             COLLATE utf8_unicode_ci NOT NULL,
10  `ua_info`  VARCHAR(255)
11             COLLATE utf8_unicode_ci NOT NULL,
12  `ua_type`  VARCHAR(32)
13             COLLATE utf8_unicode_ci NOT NULL,
14  `ua_ver`   VARCHAR(10)
15             COLLATE utf8_unicode_ci NOT NULL,
16  `os`       VARCHAR(32)
17             COLLATE utf8_unicode_ci NOT NULL,
18  `ref_md5`  VARCHAR(32)
19             COLLATE utf8_unicode_ci NOT NULL,
20  `ref_type` VARCHAR(32)
21             COLLATE utf8_unicode_ci NOT NULL,
22  `ref`      TEXT
23             COLLATE utf8_unicode_ci NOT NULL,
24  `screen_x` INT(10) UNSIGNED        NOT NULL,
25  `screen_y` INT(10) UNSIGNED        NOT NULL,
26  `view_x`   INT(10) UNSIGNED        NOT NULL,
27  `view_y`   INT(10) UNSIGNED        NOT NULL,
28  `user`     VARCHAR(255)
29             COLLATE utf8_unicode_ci NOT NULL,
30  `session`  VARCHAR(255)
31             COLLATE utf8_unicode_ci NOT NULL,
32  PRIMARY KEY (`id`),
33  KEY `ref_type` (`ref_type`),
34  KEY `page` (`page`),
35  KEY `ref_md5` (`ref_md5`),
36  KEY `dt` (`dt`)
37)
38  ENGINE =MyISAM
39  DEFAULT CHARSET =utf8
40  COLLATE =utf8_unicode_ci;
41
42CREATE TABLE `stats_iplocation` (
43  `ip`      VARCHAR(40)
44            COLLATE utf8_unicode_ci NOT NULL,
45  `code`    VARCHAR(3)
46            COLLATE utf8_unicode_ci NOT NULL,
47  `country` VARCHAR(255)
48            COLLATE utf8_unicode_ci NOT NULL,
49  `city`    VARCHAR(255)
50            COLLATE utf8_unicode_ci NOT NULL,
51  `host`    VARCHAR(255)
52            COLLATE utf8_unicode_ci NOT NULL,
53  `lastupd` TIMESTAMP               NOT NULL DEFAULT CURRENT_TIMESTAMP,
54  PRIMARY KEY (`ip`),
55  KEY `code` (`code`)
56)
57  ENGINE =MyISAM
58  DEFAULT CHARSET =utf8
59  COLLATE =utf8_unicode_ci;
60
61-- UPGRADE added 2007-01-28
62ALTER TABLE `stats_access` CHANGE `dt` `dt` DATETIME NOT NULL;
63ALTER TABLE `stats_access` ADD `js` TINYINT(1) NOT NULL
64AFTER `view_y`;
65UPDATE `stats_access`
66SET js = 1;
67
68-- UPGRADE added 2007-01-31
69ALTER TABLE `stats_access` ADD `uid` VARCHAR(50) NOT NULL;
70
71CREATE TABLE `stats_outlinks` (
72  `id`       BIGINT(20) UNSIGNED     NOT NULL AUTO_INCREMENT,
73  `dt`       DATETIME                NOT NULL,
74  `session`  VARCHAR(255)
75             COLLATE utf8_unicode_ci NOT NULL,
76  `link_md5` VARCHAR(32)
77             COLLATE utf8_unicode_ci NOT NULL,
78  `link`     TEXT
79             COLLATE utf8_unicode_ci NOT NULL,
80  PRIMARY KEY (`id`),
81  KEY `link_md5` (`link_md5`)
82)
83  ENGINE =MyISAM
84  DEFAULT CHARSET =utf8
85  COLLATE =utf8_unicode_ci;
86
87-- UPGRADE added 2007-02-04
88ALTER TABLE `stats_outlinks` ADD `page` VARCHAR(255) NOT NULL
89AFTER `dt`;
90
91CREATE TABLE `stats_search` (
92  `id`     BIGINT(20) UNSIGNED     NOT NULL AUTO_INCREMENT,
93  `dt`     DATETIME                NOT NULL,
94  `page`   VARCHAR(255)
95           COLLATE utf8_unicode_ci NOT NULL,
96  `query`  VARCHAR(255)
97           COLLATE utf8_unicode_ci NOT NULL,
98  `engine` VARCHAR(255)
99           COLLATE utf8_unicode_ci NOT NULL,
100  PRIMARY KEY (`id`)
101)
102  ENGINE =MyISAM
103  DEFAULT CHARSET =utf8
104  COLLATE =utf8_unicode_ci;
105
106CREATE TABLE `stats_searchwords` (
107  `sid`  BIGINT UNSIGNED NOT NULL,
108  `word` VARCHAR(255)    NOT NULL,
109  PRIMARY KEY (`sid`, `word`)
110)
111  ENGINE = MYISAM
112  CHARACTER SET utf8
113  COLLATE utf8_unicode_ci;
114
115-- statistic fixes
116UPDATE stats_access
117SET ref_type='external'
118WHERE ref LIKE 'http://digg.com/%';
119UPDATE stats_access
120SET ref_type='external'
121WHERE ref LIKE 'http://del.icio.us/%';
122UPDATE stats_access
123SET ref_type='external'
124WHERE ref LIKE 'http://www.stumbleupon.com/%';
125UPDATE stats_access
126SET ref_type='external'
127WHERE ref LIKE 'http://swik.net/%';
128UPDATE stats_access
129SET ref_type='external'
130WHERE ref LIKE 'http://segnalo.alice.it/%';
131
132-- UPGRADE added 2008-06-15
133CREATE TABLE `stats_refseen` (
134  `ref_md5` VARCHAR(32)
135            COLLATE utf8_unicode_ci NOT NULL,
136  `dt`      DATETIME                NOT NULL,
137  PRIMARY KEY (`ref_md5`),
138  KEY `dt` (`dt`)
139)
140  ENGINE = MYISAM
141  CHARACTER SET utf8
142  COLLATE utf8_unicode_ci;
143
144-- This will take some time...
145INSERT INTO stats_refseen (`ref_md5`, `dt`) SELECT
146                                          `ref_md5`,
147                                          MIN(`dt`)
148                                        FROM stats_access
149                                        GROUP BY `ref_md5`;
150
151-- UPGRADE added 2012-02-08
152CREATE TABLE `stats_edits` (
153  `id`      BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
154  `dt`      DATETIME            NOT NULL,
155  `ip`      VARCHAR(40)         NOT NULL,
156  `user`    VARCHAR(255)        NOT NULL,
157  `session` VARCHAR(255)        NOT NULL,
158  `uid`     VARCHAR(50)         NOT NULL,
159  `page`    VARCHAR(255)        NOT NULL,
160  `type`    CHAR(1)
161            COLLATE 'ascii_bin' NOT NULL
162)
163  ENGINE ='MyISAM'
164  COLLATE 'utf8_general_ci';
165
166ALTER TABLE `stats_access` CHANGE `ip` `ip` VARCHAR(40);
167
168ALTER TABLE `stats_search` ADD INDEX `engine` (`engine`);
169
170CREATE TABLE `stats_session` (
171  `session` VARCHAR(255) NOT NULL PRIMARY KEY,
172  `dt`      DATETIME     NOT NULL,
173  `end`     DATETIME     NOT NULL,
174  `views`   INT UNSIGNED NOT NULL,
175  `uid`     VARCHAR(50)  NOT NULL
176)
177  COMMENT =''
178  ENGINE ='MyISAM'
179  COLLATE 'utf8_general_ci';
180
181CREATE TABLE `stats_logins` (
182  `id`      BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
183  `dt`      DATETIME            NOT NULL,
184  `ip`      VARCHAR(40)         NOT NULL,
185  `user`    VARCHAR(255)        NOT NULL,
186  `session` VARCHAR(255)        NOT NULL,
187  `uid`     VARCHAR(50)         NOT NULL,
188  `type`    CHAR(1)
189            COLLATE 'ascii_bin' NOT NULL
190)
191  ENGINE ='MyISAM'
192  COLLATE 'utf8_general_ci';
193
194ALTER TABLE `stats_edits` ADD INDEX `dt` (`dt`);
195ALTER TABLE `stats_edits` ADD INDEX `type` (`type`);
196ALTER TABLE `stats_logins` ADD INDEX `dt` (`dt`);
197ALTER TABLE `stats_logins` ADD INDEX `type` (`type`);
198ALTER TABLE `stats_outlinks` ADD INDEX `dt` (`dt`);
199ALTER TABLE `stats_search` ADD INDEX `dt` (`dt`);
200ALTER TABLE `stats_session` ADD INDEX `dt` (`dt`);
201ALTER TABLE `stats_session` ADD INDEX `views` (`views`);
202ALTER TABLE `stats_session` ADD INDEX `uid` (`uid`);
203ALTER TABLE `stats_access` ADD INDEX `ua_type` (`ua_type`);
204
205-- UPGRADE added 2014-06-18
206CREATE TABLE `stats_lastseen` (
207  `user` VARCHAR(255) NOT NULL,
208  `dt`   TIMESTAMP    NOT NULL,
209  PRIMARY KEY (`user`)
210)
211  ENGINE ='MEMORY'
212  COLLATE 'utf8_general_ci';
213
214CREATE TABLE `stats_media` (
215  `id`      BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
216  `dt`      DATETIME            NOT NULL,
217  `media`   VARCHAR(255)        NOT NULL,
218  `ip`      VARCHAR(40) DEFAULT NULL,
219  `ua`      VARCHAR(255)        NOT NULL,
220  `ua_info` VARCHAR(255)        NOT NULL,
221  `ua_type` VARCHAR(32)         NOT NULL,
222  `ua_ver`  VARCHAR(10)         NOT NULL,
223  `os`      VARCHAR(32)         NOT NULL,
224  `user`    VARCHAR(255)        NOT NULL,
225  `session` VARCHAR(255)        NOT NULL,
226  `uid`     VARCHAR(50)         NOT NULL,
227  `size`    INT UNSIGNED        NOT NULL,
228  `mime1`   VARCHAR(50)         NOT NULL,
229  `mime2`   VARCHAR(50)         NOT NULL,
230  `inline`  TINYINT(1)          NOT NULL,
231  PRIMARY KEY (`id`),
232  KEY `media` (`media`),
233  KEY `dt` (`dt`),
234  KEY `ua_type` (`ua_type`)
235)
236  ENGINE ='MyISAM'
237  COLLATE ='utf8_unicode_ci';
238
239ALTER TABLE `stats_media` ADD INDEX `mime1` (`mime1`);
240
241CREATE TABLE `stats_history` (
242  `info`    VARCHAR(50)         NOT NULL,
243  `dt`      DATE                NOT NULL,
244  `value`   INT UNSIGNED        NOT NULL,
245  PRIMARY KEY (`info`, `dt`)
246)
247  ENGINE ='MyISAM'
248  COLLATE ='utf8_unicode_ci';
249
250CREATE TABLE `stats_groups` (
251  `id`      BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
252  `dt`      DATETIME            NOT NULL,
253  `group`   VARCHAR(255)        NOT NULL,
254  `type`    VARCHAR(50)         NOT NULL,
255  PRIMARY KEY (`id`),
256  KEY `dt` (`dt`),
257  KEY `type` (`type`)
258)
259  ENGINE ='MyISAM'
260  COLLATE ='utf8_unicode_ci';
261
262-- UPGRADE added 2019-04-10
263ALTER TABLE `stats_history` MODIFY COLUMN `value` BIGINT;
264
265-- UPGRADE added 2023-12-08
266ALTER TABLE `stats_iplocation` MODIFY COLUMN `ip` VARCHAR(40);
267ALTER TABLE `stats_access` MODIFY COLUMN `ip` VARCHAR(40);
268