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); 168CREATE INDEX `idx_groups_dt` ON `groups` (`dt`); 169CREATE INDEX `idx_groups_type` ON `groups` (`type`); 170 171 172 173 174 175 176 177 178 179