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