droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 1 | -- MySQL dump 10.10 |
| 2 | -- |
| 3 | -- Host: localhost Database: babelstg |
| 4 | -- ------------------------------------------------------ |
| 5 | -- Server version 5.0.18 |
| 6 | |
| 7 | /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; |
| 8 | /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; |
| 9 | /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; |
| 10 | /*!40101 SET NAMES utf8 */; |
| 11 | /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
| 12 | /*!40103 SET TIME_ZONE='+00:00' */; |
| 13 | /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
| 14 | /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
| 15 | /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; |
| 16 | /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
| 17 | |
droy | d0a9b1a | 2008-01-29 19:51:46 +0000 | [diff] [blame] | 18 | DROP TABLE IF EXISTS event_log; |
droy | 3e7f3a8 | 2007-11-29 19:35:51 +0000 | [diff] [blame] | 19 | CREATE TABLE `event_log` ( |
| 20 | `event_id` int(10) unsigned NOT NULL auto_increment, |
| 21 | `table_name` varchar(100) NOT NULL, |
| 22 | `key_name` varchar(100) NOT NULL, |
| 23 | `key_value` varchar(100) default NULL, |
| 24 | `action` varchar(100) NOT NULL, |
| 25 | `userid` mediumint(9) NOT NULL, |
| 26 | `created_on` datetime NOT NULL, |
| 27 | PRIMARY KEY (`event_id`) |
| 28 | ) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 29 | |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 30 | DROP TABLE IF EXISTS `file_progress`; |
| 31 | CREATE TABLE `file_progress` ( |
| 32 | `file_id` int(10) unsigned NOT NULL, |
| 33 | `language_id` smallint(5) unsigned NOT NULL, |
| 34 | `pct_complete` float NOT NULL, |
| 35 | PRIMARY KEY (`file_id`, `language_id`), |
| 36 | CONSTRAINT `file_progress_ibfk_1` FOREIGN KEY (`file_id`) REFERENCES `files` (`file_id`) ON UPDATE CASCADE ON DELETE CASCADE, |
| 37 | CONSTRAINT `file_progress_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON UPDATE CASCADE ON DELETE CASCADE |
| 38 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 39 | |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 40 | DROP TABLE IF EXISTS `files`; |
| 41 | CREATE TABLE `files` ( |
droy | e5c9981 | 2007-11-28 21:36:51 +0000 | [diff] [blame] | 42 | `file_id` int(10) unsigned NOT NULL auto_increment, |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 43 | `project_id` varchar(100) NOT NULL, |
droy | 44cb1ba | 2007-12-18 15:19:34 +0000 | [diff] [blame] | 44 | `version` varchar(64) NOT NULL, |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 45 | `name` text NOT NULL, |
| 46 | `is_active` tinyint(3) unsigned NOT NULL default '1', |
| 47 | PRIMARY KEY (`file_id`), |
| 48 | KEY `project_id` (`project_id`), |
droy | c081e86 | 2007-11-30 16:37:35 +0000 | [diff] [blame] | 49 | CONSTRAINT `files_ibfk_1` FOREIGN KEY (`project_id`,`version`) REFERENCES `project_versions` (`project_id`,`version`) ON UPDATE CASCADE |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 50 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 51 | -- |
| 52 | -- Table structure for table `languages` |
| 53 | -- |
| 54 | |
| 55 | DROP TABLE IF EXISTS `languages`; |
| 56 | CREATE TABLE `languages` ( |
| 57 | `language_id` smallint(5) unsigned NOT NULL auto_increment, |
droy | c94a452 | 2008-02-05 15:36:53 +0000 | [diff] [blame] | 58 | `iso_code` varchar(6) NOT NULL, |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 59 | `locale` varchar(12) default NULL, |
| 60 | `name` varchar(50) NOT NULL, |
| 61 | `is_active` tinyint(3) unsigned NOT NULL default '1', |
| 62 | PRIMARY KEY (`language_id`), |
| 63 | KEY `iso_code` (`iso_code`), |
| 64 | KEY `locale` (`locale`) |
| 65 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 66 | |
droy | 3bb0c96 | 2008-01-17 20:45:01 +0000 | [diff] [blame] | 67 | |
| 68 | |
| 69 | -- |
| 70 | -- Table structure for table `map_files` |
| 71 | -- |
| 72 | |
| 73 | DROP TABLE IF EXISTS `map_files`; |
| 74 | CREATE TABLE `map_files` ( |
| 75 | `project_id` varchar(100) NOT NULL, |
| 76 | `version` varchar(64) NOT NULL, |
| 77 | `filename` varchar(100) NOT NULL, |
| 78 | `location` varchar(255) NOT NULL, |
| 79 | `is_active` tinyint(3) unsigned NOT NULL default '1', |
| 80 | PRIMARY KEY (`project_id`, `version`, `filename`), |
| 81 | CONSTRAINT `map_files_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON UPDATE CASCADE ON DELETE CASCADE |
| 82 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 83 | |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 84 | -- |
| 85 | -- Table structure for table `profiles` |
| 86 | -- |
| 87 | |
| 88 | DROP TABLE IF EXISTS `profiles`; |
| 89 | CREATE TABLE `profiles` ( |
| 90 | `userid` mediumint(9) NOT NULL auto_increment, |
| 91 | `login_name` varchar(255) NOT NULL default '', |
| 92 | `cryptpassword` varchar(128) default NULL, |
| 93 | `realname` varchar(255) NOT NULL default '', |
| 94 | `disabledtext` mediumtext NOT NULL, |
| 95 | `mybugslink` tinyint(4) NOT NULL default '1', |
| 96 | `extern_id` varchar(64) default NULL, |
| 97 | `disable_mail` tinyint(4) NOT NULL default '0', |
| 98 | PRIMARY KEY (`userid`), |
| 99 | UNIQUE KEY `profiles_login_name_idx` (`login_name`) |
| 100 | ) ENGINE=MyISAM DEFAULT CHARSET=latin1; |
| 101 | |
| 102 | -- |
| 103 | -- Table structure for table `projects` |
| 104 | -- |
| 105 | |
| 106 | DROP TABLE IF EXISTS `projects`; |
| 107 | CREATE TABLE `projects` ( |
| 108 | `project_id` varchar(100) NOT NULL, |
| 109 | `is_active` tinyint(3) unsigned NOT NULL default '1', |
| 110 | PRIMARY KEY (`project_id`) |
| 111 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 112 | |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 113 | DROP TABLE IF EXISTS `project_progress`; |
| 114 | CREATE TABLE `project_progress` ( |
| 115 | `project_id` varchar(100) NOT NULL, |
| 116 | `version` varchar(64) NOT NULL, |
| 117 | `language_id` smallint(5) unsigned NOT NULL, |
| 118 | `pct_complete` float NOT NULL, |
droy | 6e6fb6d | 2008-05-15 17:11:29 +0000 | [diff] [blame] | 119 | `is_stale` tinyint unsigned not null default 0, |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 120 | PRIMARY KEY (`project_id`, `version`, `language_id`), |
| 121 | CONSTRAINT `project_progress_ibfk_1` FOREIGN KEY (`project_id`, `version`) REFERENCES `project_versions` (`project_id`, `version`) ON UPDATE CASCADE ON DELETE CASCADE, |
| 122 | CONSTRAINT `project_progress_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON UPDATE CASCADE ON DELETE CASCADE |
| 123 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 124 | |
| 125 | |
| 126 | |
droy | c081e86 | 2007-11-30 16:37:35 +0000 | [diff] [blame] | 127 | DROP TABLE IF EXISTS `project_versions`; |
| 128 | CREATE TABLE `project_versions` ( |
| 129 | `project_id` varchar(100) NOT NULL, |
droy | 44cb1ba | 2007-12-18 15:19:34 +0000 | [diff] [blame] | 130 | `version` varchar(64) NOT NULL, |
droy | c081e86 | 2007-11-30 16:37:35 +0000 | [diff] [blame] | 131 | `is_active` tinyint(3) unsigned NOT NULL default '1', |
| 132 | PRIMARY KEY (`project_id`, `version`), |
| 133 | CONSTRAINT `project_versions_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON UPDATE CASCADE ON DELETE CASCADE |
| 134 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 135 | |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 136 | -- |
| 137 | -- Table structure for table `ratings` |
| 138 | -- |
| 139 | |
| 140 | DROP TABLE IF EXISTS `ratings`; |
| 141 | CREATE TABLE `ratings` ( |
| 142 | `translation_id` int(10) unsigned NOT NULL, |
| 143 | `userid` int(10) unsigned NOT NULL, |
| 144 | `rating` tinyint(3) unsigned NOT NULL default '0', |
| 145 | `created_on` datetime NOT NULL, |
| 146 | PRIMARY KEY (`translation_id`,`userid`), |
| 147 | CONSTRAINT `ratings_ibfk_1` FOREIGN KEY (`translation_id`) REFERENCES `translations` (`translation_id`) ON UPDATE CASCADE |
| 148 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 149 | |
| 150 | -- |
| 151 | -- Table structure for table `schema_info` |
| 152 | -- |
| 153 | |
| 154 | DROP TABLE IF EXISTS `schema_info`; |
| 155 | CREATE TABLE `schema_info` ( |
| 156 | `version` int(11) default NULL |
| 157 | ) ENGINE=MyISAM DEFAULT CHARSET=latin1; |
| 158 | |
| 159 | -- |
| 160 | -- Table structure for table `sessions` |
| 161 | -- |
| 162 | |
| 163 | DROP TABLE IF EXISTS `sessions`; |
| 164 | CREATE TABLE `sessions` ( |
| 165 | `id` int(11) unsigned NOT NULL auto_increment, |
| 166 | `userid` int(11) NOT NULL, |
| 167 | `gid` char(32) default NULL, |
| 168 | `subnet` char(15) default NULL, |
| 169 | `updated_at` datetime default NULL, |
| 170 | PRIMARY KEY (`id`), |
| 171 | KEY `gid` (`gid`), |
| 172 | KEY `userid` (`userid`) |
| 173 | ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
| 174 | |
| 175 | -- |
| 176 | -- Table structure for table `strings` |
| 177 | -- |
| 178 | |
| 179 | DROP TABLE IF EXISTS `strings`; |
| 180 | CREATE TABLE `strings` ( |
| 181 | `string_id` int(10) unsigned NOT NULL auto_increment, |
| 182 | `file_id` int(10) unsigned NOT NULL, |
| 183 | `name` text NOT NULL, |
| 184 | `value` text NOT NULL, |
| 185 | `userid` int(10) unsigned NOT NULL, |
| 186 | `created_on` datetime NOT NULL, |
| 187 | `is_active` tinyint(1) unsigned default NULL, |
| 188 | PRIMARY KEY (`string_id`), |
| 189 | KEY `file_id` (`file_id`), |
| 190 | KEY `userid` (`userid`), |
| 191 | CONSTRAINT `strings_ibfk_1` FOREIGN KEY (`file_id`) REFERENCES `files` (`file_id`) ON UPDATE CASCADE, |
| 192 | CONSTRAINT `strings_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE |
| 193 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 194 | |
droy | 44cb1ba | 2007-12-18 15:19:34 +0000 | [diff] [blame] | 195 | |
droy | 44cb1ba | 2007-12-18 15:19:34 +0000 | [diff] [blame] | 196 | DELIMITER ;; |
| 197 | CREATE TRIGGER `upd_string` AFTER UPDATE ON `strings` FOR EACH ROW |
| 198 | BEGIN |
| 199 | IF(NEW.value <> OLD.value) THEN |
| 200 | UPDATE translations SET possibly_incorrect = 1 WHERE string_id = NEW.string_id; |
| 201 | END IF; |
| 202 | END; |
| 203 | ;; |
| 204 | DELIMITER ; |
| 205 | |
| 206 | |
| 207 | |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 208 | -- |
| 209 | -- Table structure for table `translation_archives` |
| 210 | -- |
| 211 | |
| 212 | DROP TABLE IF EXISTS `translation_archives`; |
| 213 | CREATE TABLE `translation_archives` ( |
| 214 | `translation_id` int(10) unsigned NOT NULL, |
| 215 | `string_id` int(10) unsigned NOT NULL, |
| 216 | `language_id` smallint(5) unsigned NOT NULL, |
| 217 | `version` int(10) unsigned NOT NULL default '1', |
| 218 | `value` text NOT NULL, |
| 219 | `is_active` tinyint(3) unsigned NOT NULL default '1', |
| 220 | `userid` int(10) unsigned NOT NULL, |
| 221 | `created_on` datetime NOT NULL, |
| 222 | PRIMARY KEY (`string_id`,`language_id`,`version`), |
| 223 | KEY `language_id` (`language_id`), |
| 224 | KEY `userid` (`userid`), |
| 225 | CONSTRAINT `translation_archives_ibfk_1` FOREIGN KEY (`string_id`) REFERENCES `strings` (`string_id`) ON UPDATE CASCADE, |
| 226 | CONSTRAINT `translation_archives_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON UPDATE CASCADE, |
| 227 | CONSTRAINT `translation_archives_ibfk_3` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE |
| 228 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 229 | |
| 230 | -- |
| 231 | -- Table structure for table `translations` |
| 232 | -- |
| 233 | |
| 234 | DROP TABLE IF EXISTS `translations`; |
| 235 | CREATE TABLE `translations` ( |
| 236 | `translation_id` int(10) unsigned NOT NULL auto_increment, |
| 237 | `string_id` int(10) unsigned NOT NULL, |
| 238 | `language_id` smallint(5) unsigned NOT NULL, |
| 239 | `version` int(10) unsigned NOT NULL default '1', |
| 240 | `value` text NOT NULL, |
droy | 44cb1ba | 2007-12-18 15:19:34 +0000 | [diff] [blame] | 241 | `possibly_incorrect` tinyint unsigned NOT NULL default '0', |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 242 | `is_active` tinyint(3) unsigned NOT NULL default '1', |
| 243 | `userid` int(10) unsigned NOT NULL, |
| 244 | `created_on` datetime NOT NULL, |
| 245 | PRIMARY KEY (`string_id`,`language_id`,`version`), |
| 246 | KEY `translation_id` (`translation_id`), |
| 247 | KEY `language_id` (`language_id`), |
| 248 | KEY `userid` (`userid`), |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 249 | KEY `created_on` (`created_on`), |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 250 | CONSTRAINT `translations_ibfk_1` FOREIGN KEY (`string_id`) REFERENCES `strings` (`string_id`) ON UPDATE CASCADE, |
| 251 | CONSTRAINT `translations_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON UPDATE CASCADE, |
| 252 | CONSTRAINT `translations_ibfk_3` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE |
| 253 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 254 | |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 255 | SET @OLD_SQL_MODE=@@SQL_MODE; |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 256 | DELIMITER ;; |
| 257 | /*!50003 SET SESSION SQL_MODE="" */;; |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 258 | DROP TRIGGER `ins_version`;; |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 259 | |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 260 | /* This trigger sets the version to max(version) + 1. It also updates the file_progress table */ |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 261 | /* We check IF(NEW.version > 1) to determine if this is a NEW translation or one that replaces an existing one */ |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 262 | /* (COUNT(t.string_id) + 1) because it's a BEFORE INSERT trigger, the translated str is not in the DB yet */ |
| 263 | /* and without the +1 the percent would always be "one behind" */ |
| 264 | CREATE TRIGGER `ins_version` BEFORE INSERT ON `translations` FOR EACH ROW BEGIN |
| 265 | SET NEW.version = |
| 266 | (SELECT IFNULL(MAX(version),0)+1 FROM translations |
| 267 | WHERE string_id = NEW.string_id and language_id = NEW.language_id); |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 268 | |
| 269 | /* Define a few fields we need here */ |
| 270 | SET @FILE_ID=(SELECT file_id FROM strings WHERE string_id = NEW.string_id); |
| 271 | SET @PROJECT=(SELECT project_id FROM files WHERE file_id = @FILE_ID); |
| 272 | SET @VERSION=(SELECT version FROM files WHERE file_id = @FILE_ID); |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 273 | |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 274 | |
| 275 | /* update this file's progress */ |
| 276 | DELETE FROM file_progress where file_id = @FILE_ID |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 277 | AND language_id = NEW.language_id; |
| 278 | |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 279 | /* See notes above for the hairy select */ |
| 280 | INSERT INTO file_progress SET file_id = @FILE_ID, |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 281 | language_id = NEW.language_id, |
| 282 | pct_complete = ( |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 283 | SELECT IF(NEW.version > 1, |
| 284 | IF(COUNT(s.string_id) > 0, (COUNT(t.string_id))/COUNT(s.string_id)*100,0), |
| 285 | IF(COUNT(s.string_id) > 0, (COUNT(t.string_id) + 1)/COUNT(s.string_id)*100,0) |
| 286 | ) AS translate_percent |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 287 | FROM files AS f |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 288 | LEFT JOIN strings AS s ON (s.file_id = f.file_id AND s.value <> "" and s.is_active) |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 289 | LEFT JOIN translations AS t ON (s.string_id = t.string_id |
| 290 | AND t.language_id = NEW.language_id AND t.is_active = 1) |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 291 | WHERE f.file_id = @FILE_ID |
| 292 | ); |
droy | 6e6fb6d | 2008-05-15 17:11:29 +0000 | [diff] [blame] | 293 | |
| 294 | |
| 295 | /* update project_progress table to indicate this proj/lang/vers stats are stale */ |
| 296 | /* don't know if record is there or not, so do both an insert and an update */ |
| 297 | UPDATE IGNORE project_progress SET is_stale = 1 where project_id = @PROJECT |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 298 | AND version = @VERSION |
| 299 | AND language_id = NEW.language_id; |
droy | 6e6fb6d | 2008-05-15 17:11:29 +0000 | [diff] [blame] | 300 | |
| 301 | INSERT IGNORE INTO project_progress SET is_stale = 1, project_id = @PROJECT, |
| 302 | version = @VERSION, |
| 303 | language_id = NEW.language_id; |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 304 | |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 305 | END; |
| 306 | ;; |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 307 | DELIMITER ; |
| 308 | /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */; |
| 309 | |
| 310 | -- |
| 311 | -- Table structure for table `users` |
| 312 | -- |
| 313 | |
| 314 | DROP TABLE IF EXISTS `users`; |
| 315 | CREATE TABLE `users` ( |
| 316 | `userid` int(10) unsigned NOT NULL, |
| 317 | `username` varchar(256) NOT NULL default '', |
| 318 | `first_name` varchar(256) NOT NULL default '', |
| 319 | `last_name` varchar(256) NOT NULL default '', |
| 320 | `email` varchar(256) NOT NULL default '', |
| 321 | `primary_language_id` int(11) NOT NULL default '0', |
| 322 | `hours_per_week` int(11) NOT NULL default '0', |
| 323 | `password_hash` varchar(256) NOT NULL default '', |
droy | 8da30b3 | 2007-11-29 21:00:26 +0000 | [diff] [blame] | 324 | `is_committer` tinyint unsigned not null default 0, |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 325 | `updated_on` date NOT NULL, |
| 326 | `updated_at` time NOT NULL, |
| 327 | `created_on` date NOT NULL, |
| 328 | `created_at` time NOT NULL, |
| 329 | PRIMARY KEY (`userid`), |
| 330 | KEY `primary_language_id` (`primary_language_id`) |
| 331 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
droy | 97c8619 | 2007-11-28 22:00:22 +0000 | [diff] [blame] | 332 | |
droy | 5bb8117 | 2008-04-04 19:25:24 +0000 | [diff] [blame] | 333 | DROP TABLE IF EXISTS `scoreboard`; |
| 334 | CREATE TABLE `scoreboard` ( |
| 335 | `id` int unsigned not null auto_increment, |
| 336 | `itemid` char(6) NOT NULL, |
| 337 | `value` varchar(256) NOT NULL default '', |
| 338 | `quantity` double NOT NULL default 0, |
| 339 | PRIMARY KEY (`id`), |
| 340 | KEY(`quantity`) |
| 341 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 342 | |
| 343 | |
droy | 97c8619 | 2007-11-28 22:00:22 +0000 | [diff] [blame] | 344 | |
| 345 | |
droy | 27e97e0 | 2007-11-27 16:44:19 +0000 | [diff] [blame] | 346 | /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
| 347 | |
| 348 | /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
| 349 | /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
| 350 | /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
| 351 | /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; |
| 352 | /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; |
| 353 | /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
| 354 | /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; |
| 355 | |
droy | 6426bc0 | 2008-02-01 19:35:19 +0000 | [diff] [blame] | 356 | |
| 357 | |
| 358 | |
| 359 | |
| 360 | |
| 361 | /* |
| 362 | Babel data |
| 363 | User: babel@eclipse.org |
| 364 | pass: password |
| 365 | */ |
| 366 | |
| 367 | insert into profiles set login_name = "test", cryptpassword = "", realname = "tester", disabledtext = false, mybugslink = 1, extern_id = 1, disable_mail = false; |
| 368 | insert into users set userid = 1, username = "babel@eclipse.org",first_name="babel",last_name="fish",email="babel@eclipse.org",primary_language_id = "",password_hash = "HSD9a.ShTTdvo", is_committer = true; |
| 369 | insert into projects set project_id = 'eclipse', is_active = 1 ; |
| 370 | INSERT INTO `languages` VALUES (1,'en',NULL,'English',1), |
| 371 | (2,'fr',NULL,'French',1), |
| 372 | (3,'ja',NULL,'Japanese',1), |
| 373 | (4,'de',NULL,'German',1), |
| 374 | (5,'es',NULL,'Spanish',1), |
| 375 | (7,'it',NULL,'Italian',1), |
| 376 | (8,'ja',NULL,'Japanese',1), |
| 377 | (9,'ko',NULL,'Korean',1), |
kitlo | a2b243f | 2008-03-05 14:55:26 +0000 | [diff] [blame] | 378 | (10,'pt_BR','Brazilian','Portuguese',1), |
droy | 6426bc0 | 2008-02-01 19:35:19 +0000 | [diff] [blame] | 379 | (11,'zh','Simplified','Chinese',1), |
droy | c94a452 | 2008-02-05 15:36:53 +0000 | [diff] [blame] | 380 | (12,'zh_TW','Traditional','Chinese',1), |
droy | 6426bc0 | 2008-02-01 19:35:19 +0000 | [diff] [blame] | 381 | (13,'cs',NULL,'Czech',1), |
| 382 | (14,'hu',NULL,'Hungarian',1), |
| 383 | (15,'pl',NULL,'Polish',1), |
| 384 | (16,'ru',NULL,'Russian',1), |
kitlo | cba1790 | 2008-02-14 19:16:02 +0000 | [diff] [blame] | 385 | (17,'da',NULL,'Danish',1), |
droy | 6426bc0 | 2008-02-01 19:35:19 +0000 | [diff] [blame] | 386 | (18,'nl',NULL,'Dutch',1), |
| 387 | (19,'fi',NULL,'Finnish',1), |
| 388 | (20,'el',NULL,'Greek',1), |
| 389 | (21,'no',NULL,'Norwegian',1), |
| 390 | (22,'pt',NULL,'Portuguese',1), |
| 391 | (23,'sv',NULL,'Swedish',1), |
| 392 | (24,'tr',NULL,'Turkish',1), |
| 393 | (25,'ar',NULL,'Arabic',1), |
kitlo | cba1790 | 2008-02-14 19:16:02 +0000 | [diff] [blame] | 394 | (26,'iw',NULL,'Hebrew',1); |
droy | c94a452 | 2008-02-05 15:36:53 +0000 | [diff] [blame] | 395 | insert into languages set iso_code = 'hi', name = "Hindi"; |
droy | 99907e4 | 2008-03-05 21:59:21 +0000 | [diff] [blame] | 396 | insert into languages set iso_code = 'ro', name = "Romanian"; |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 397 | insert into languages set iso_code = 'uk', name = "Ukrainian"; |
droy | d0b8959 | 2008-05-15 19:18:20 +0000 | [diff] [blame^] | 398 | insert into languages set iso_code = 'es_CA', name = "Spanish-Catalonian"; |
droy | 6426bc0 | 2008-02-01 19:35:19 +0000 | [diff] [blame] | 399 | insert into project_versions set project_id = "eclipse", version = "3.4", is_active = 1; |
| 400 | |
| 401 | |
| 402 | /* MAP INPUTS */ |
| 403 | insert into map_files values ("eclipse", "3.4", "ant.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/ant.map?view=co", 1); |
| 404 | insert into map_files values ("eclipse", "3.4", "base.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/base.map?view=co", 1); |
| 405 | insert into map_files values ("eclipse", "3.4", "compare.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/compare.map?view=co", 1); |
| 406 | insert into map_files values ("eclipse", "3.4", "core-hpux.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/core-hpux.map?view=co", 1); |
| 407 | insert into map_files values ("eclipse", "3.4", "core-macosx.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/core-macosx.map?view=co", 1); |
| 408 | insert into map_files values ("eclipse", "3.4", "core-qnx.map","http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/core-qnx.map?view=co", 1); |
| 409 | insert into map_files values ("eclipse", "3.4", "core-variables.map","http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/core-variables.map?view=co", 1); |
| 410 | insert into map_files values ("eclipse", "3.4", "core.map","http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/core.map?view=co", 1); |
| 411 | insert into map_files values ("eclipse", "3.4", "doc.map","http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/doc.map?view=co", 1); |
| 412 | insert into map_files values ("eclipse", "3.4", "equinox-incubator.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/equinox-incubator.map?view=co", 1); |
| 413 | insert into map_files values ("eclipse", "3.4", "feature.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/feature.map?view=co", 1); |
| 414 | insert into map_files values ("eclipse", "3.4", "jdtapt.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/jdtapt.map?view=co", 1); |
| 415 | insert into map_files values ("eclipse", "3.4", "jdtcore.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/jdtcore.map?view=co", 1); |
| 416 | insert into map_files values ("eclipse", "3.4", "jdtdebug.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/jdtdebug.map?view=co", 1); |
| 417 | insert into map_files values ("eclipse", "3.4", "jdtui.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/jdtui.map?view=co", 1); |
| 418 | insert into map_files values ("eclipse", "3.4", "orbit.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/orbit.map?view=co", 1); |
| 419 | insert into map_files values ("eclipse", "3.4", "pde.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/pde.map?view=co", 1); |
| 420 | insert into map_files values ("eclipse", "3.4", "rcp.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/rcp.map?view=co", 1); |
| 421 | insert into map_files values ("eclipse", "3.4", "releng.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/releng.map?view=co", 1); |
| 422 | insert into map_files values ("eclipse", "3.4", "swt.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/swt.map?view=co", 1); |
| 423 | insert into map_files values ("eclipse", "3.4", "team.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/team.map?view=co", 1); |
| 424 | insert into map_files values ("eclipse", "3.4", "testframework.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/testframework.map?view=co", 1); |
| 425 | insert into map_files values ("eclipse", "3.4", "text.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/text.map?view=co", 1); |
| 426 | insert into map_files values ("eclipse", "3.4", "ui.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/ui.map?view=co", 1); |
| 427 | insert into map_files values ("eclipse", "3.4", "update.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/update.map?view=co", 1); |
droy | 5d4b64c | 2008-02-29 15:22:10 +0000 | [diff] [blame] | 428 | insert into map_files values ("eclipse", "3.4", "userassist.map", "http://dev.eclipse.org/viewcvs/index.cgi/org.eclipse.releng/maps/userassist.map?view=co", 1); |
droy | bafb7c4 | 2008-02-29 18:24:38 +0000 | [diff] [blame] | 429 | |
| 430 | /* populate file_progress table */ |
| 431 | truncate table file_progress; |
| 432 | INSERT INTO file_progress |
| 433 | select f.file_id, l.language_id, IF(COUNT(s.string_id) > 0, COUNT(t.string_id)/COUNT(s.string_id)*100,0) AS translate_percent |
| 434 | FROM files AS f |
| 435 | INNER JOIN languages as l ON l.is_active = 1 |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 436 | LEFT JOIN strings as s ON (s.file_id = f.file_id AND s.is_active) |
droy | bafb7c4 | 2008-02-29 18:24:38 +0000 | [diff] [blame] | 437 | LEFT JOIN translations AS t ON (s.string_id = t.string_id |
| 438 | AND t.language_id = l.language_id AND t.is_active = 1) |
| 439 | WHERE f.is_active = 1 |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 440 | AND s.value <> "" |
droy | bafb7c4 | 2008-02-29 18:24:38 +0000 | [diff] [blame] | 441 | GROUP BY f.file_id, l.language_id; |
droy | 5bb8117 | 2008-04-04 19:25:24 +0000 | [diff] [blame] | 442 | DELETE FROM file_progress WHERE pct_complete = 0; |
| 443 | |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 444 | /* populate project_progress table */ |
| 445 | truncate table project_progress; |
| 446 | INSERT INTO project_progress |
| 447 | SELECT |
| 448 | p.project_id, |
| 449 | v.version, |
| 450 | l.language_id, |
droy | b156beb | 2008-05-15 17:16:44 +0000 | [diff] [blame] | 451 | IF(COUNT(s.string_id) > 0, ROUND(COUNT(t.string_id)/COUNT(s.string_id) * 100, 2), 0) AS pct_complete, |
| 452 | 0 |
droy | d664f91 | 2008-05-14 15:45:10 +0000 | [diff] [blame] | 453 | FROM projects as p |
| 454 | INNER JOIN project_versions AS v ON v.project_id = p.project_id |
| 455 | INNER JOIN files AS f |
| 456 | ON (f.project_id = p.project_id AND f.version = v.version AND f.is_active) |
| 457 | INNER JOIN strings AS s |
| 458 | ON (s.file_id = f.file_id AND s.is_active) |
| 459 | INNER JOIN languages AS l |
| 460 | LEFT JOIN translations AS t |
| 461 | ON (t.string_id = s.string_id AND t.language_id = l.language_id AND t.is_active) |
| 462 | WHERE |
| 463 | s.value <> "" |
| 464 | AND p.is_active |
| 465 | GROUP BY p.project_id, v.version, l.language_id; |
| 466 | |
droy | 5bb8117 | 2008-04-04 19:25:24 +0000 | [diff] [blame] | 467 | /* populate scoreboard */ |
| 468 | truncate table scoreboard; |
| 469 | INSERT INTO scoreboard SELECT NULL, "LANGPR", CONCAT(b.name,IF(ISNULL(b.locale),"",CONCAT(" ", b.locale))), count(*) as StringCount from translations as a inner join languages as b on b.language_id = a.language_id where a.value <> '' and a.is_active = 1 group by a.language_id order by StringCount desc; |
| 470 | INSERT INTO scoreboard SELECT NULL, "TOPTR", CONCAT(first_name, IF(ISNULL(last_name),"",CONCAT(" ", last_name))), count(translations.string_id) as cnt from translations inner join users on users.userid = translations.userid where is_active=1 group by first_name, last_name order by cnt desc limit 20; |
| 471 | INSERT INTO scoreboard SELECT NULL, "LASGEN", "Scoreboard Last Generated", MAX(translation_id) FROM translations; |