1 2-- logged in users and their info and groups 3 4CREATE TABLE `users` 5( 6 `user` TEXT PRIMARY KEY, 7 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- last seen 8 `domain` TEXT DEFAULT NULL -- email domain 9); 10 11CREATE TABLE `groups` 12( 13 `user` TEXT NOT NULL REFERENCES `users` (`user`) ON DELETE CASCADE ON UPDATE CASCADE, 14 `group` TEXT NOT NULL, 15 PRIMARY KEY (`user`, `group`) 16); 17 18-- current browsing session 19 20CREATE TABLE `sessions` 21( 22 `session` TEXT PRIMARY KEY, 23 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 24 `end` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 25 `uid` TEXT NOT NULL, 26 `user` TEXT NOT NULL REFERENCES `users` (`user`) ON DELETE SET NULL ON UPDATE CASCADE, 27 `ua` TEXT NOT NULL, 28 `ua_info` TEXT NOT NULL, 29 `ua_type` TEXT NOT NULL, 30 `ua_ver` TEXT NOT NULL, 31 `os` TEXT NOT NULL 32); 33CREATE INDEX `idx_session_dt` ON `sessions` (`dt`); 34CREATE INDEX `idx_session_uid` ON `sessions` (`uid`); 35CREATE INDEX `idx_session_ua_type` ON `sessions` (`ua_type`); 36 37-- referrers 38 39CREATE TABLE `referers` 40( 41 `id` INTEGER PRIMARY KEY, 42 `url` TEXT NOT NULL, 43 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 44 `type` TEXT NOT NULL DEFAULT 'external' -- 'external', 'search' 45); 46CREATE UNIQUE INDEX `idx_referers_url` ON `referers` (`url`); 47CREATE INDEX `idx_referers_dt` ON `referers` (`dt`); 48CREATE INDEX `idx_referers_type` ON `referers` (`type`); 49 50-- page view logging 51 52CREATE TABLE `pageviews` 53( 54 `id` INTEGER PRIMARY KEY, 55 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 56 `ip` TEXT NOT NULL, 57 `session` TEXT NOT NULL REFERENCES `sessions` (`session`) ON DELETE CASCADE ON UPDATE CASCADE, 58 `page` TEXT NOT NULL, 59 `ref_id` INTEGER DEFAULT NULL REFERENCES `referers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, 60 `screen_x` INTEGER NOT NULL, 61 `screen_y` INTEGER NOT NULL, 62 `view_x` INTEGER NOT NULL, 63 `view_y` INTEGER NOT NULL 64); 65CREATE INDEX `idx_pageviews_page` ON `pageviews` (`page`); 66CREATE INDEX `idx_pageviews_dt` ON `pageviews` (`dt`); 67 68CREATE TABLE `iplocation` 69( 70 `ip` TEXT PRIMARY KEY, 71 `code` TEXT NOT NULL, 72 `country` TEXT NOT NULL, 73 `city` TEXT NOT NULL, 74 `host` TEXT NOT NULL, 75 `lastupd` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 76); 77CREATE INDEX `idx_iplocation_code` ON `iplocation` (`code`); 78 79CREATE TABLE `outlinks` 80( 81 `id` INTEGER PRIMARY KEY, 82 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 83 `session` TEXT NOT NULL REFERENCES `sessions` (`session`) ON DELETE CASCADE ON UPDATE CASCADE, 84 `link` TEXT NOT NULL, 85 `page` TEXT NOT NULL DEFAULT '' 86); 87CREATE INDEX `idx_outlinks_link` ON `outlinks` (`link`); 88CREATE INDEX `idx_outlinks_dt` ON `outlinks` (`dt`); 89 90 91-- Search engine query logging for internal searches 92CREATE TABLE `search` 93( 94 `id` INTEGER PRIMARY KEY, 95 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 96 `ip` TEXT NOT NULL, 97 `session` TEXT NOT NULL REFERENCES `sessions` (`session`) ON DELETE CASCADE ON UPDATE CASCADE, 98 `query` TEXT NOT NULL 99); 100CREATE INDEX `idx_search_dt` ON `search` (`dt`); 101 102CREATE TABLE `searchwords` 103( 104 `sid` INTEGER NOT NULL, 105 `word` TEXT NOT NULL, 106 FOREIGN KEY (`sid`) REFERENCES `search` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, 107 PRIMARY KEY (`sid`, `word`) 108); 109 110-- Edit logging for content changes 111CREATE TABLE `edits` 112( 113 `id` INTEGER PRIMARY KEY, 114 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 115 `ip` TEXT NOT NULL, 116 `session` TEXT NOT NULL REFERENCES `sessions` (`session`) ON DELETE CASCADE ON UPDATE CASCADE, 117 `page` TEXT NOT NULL, 118 `type` TEXT NOT NULL 119); 120CREATE INDEX `idx_edits_dt` ON `edits` (`dt`); 121CREATE INDEX `idx_edits_type` ON `edits` (`type`); 122 123 124CREATE TABLE `logins` 125( 126 `id` INTEGER PRIMARY KEY, 127 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 128 `ip` TEXT NOT NULL, 129 `user` TEXT NOT NULL, -- no references to users, as this is for all logins, including failed ones 130 `type` TEXT NOT NULL 131); 132CREATE INDEX `idx_logins_dt` ON `logins` (`dt`); 133CREATE INDEX `idx_logins_type` ON `logins` (`type`); 134 135 136 137CREATE TABLE `media` 138( 139 `id` INTEGER PRIMARY KEY, 140 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 141 `ip` TEXT, 142 `session` TEXT NOT NULL REFERENCES `sessions` (`session`) ON DELETE CASCADE ON UPDATE CASCADE, 143 `media` TEXT NOT NULL, 144 `size` INTEGER NOT NULL, 145 `mime1` TEXT NOT NULL, 146 `mime2` TEXT NOT NULL, 147 `inline` INTEGER NOT NULL 148); 149CREATE INDEX `idx_media_media` ON `media` (`media`); 150CREATE INDEX `idx_media_dt` ON `media` (`dt`); 151CREATE INDEX `idx_media_mime1` ON `media` (`mime1`); 152 153 154CREATE TABLE `history` 155( 156 `info` TEXT NOT NULL, 157 `dt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 158 `value` INTEGER NOT NULL, 159 PRIMARY KEY (`info`, `dt`) 160); 161 162