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