blob: 26e61ee04dd284020fc4f87de5928520f2640a77 [file] [log] [blame]
droy27e97e02007-11-27 16:44:19 +00001-- 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
droyd0a9b1a2008-01-29 19:51:46 +000018DROP TABLE IF EXISTS event_log;
droy3e7f3a82007-11-29 19:35:51 +000019CREATE 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;
droy27e97e02007-11-27 16:44:19 +000029
droy5d4b64c2008-02-29 15:22:10 +000030DROP TABLE IF EXISTS `file_progress`;
31CREATE 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
droy27e97e02007-11-27 16:44:19 +000040DROP TABLE IF EXISTS `files`;
41CREATE TABLE `files` (
droye5c99812007-11-28 21:36:51 +000042 `file_id` int(10) unsigned NOT NULL auto_increment,
droy27e97e02007-11-27 16:44:19 +000043 `project_id` varchar(100) NOT NULL,
droyb9c2cb12008-10-08 13:58:14 +000044 `plugin_id` varchar(100) NOT NULL,
droy44cb1ba2007-12-18 15:19:34 +000045 `version` varchar(64) NOT NULL,
droy27e97e02007-11-27 16:44:19 +000046 `name` text NOT NULL,
47 `is_active` tinyint(3) unsigned NOT NULL default '1',
48 PRIMARY KEY (`file_id`),
49 KEY `project_id` (`project_id`),
droyc081e862007-11-30 16:37:35 +000050 CONSTRAINT `files_ibfk_1` FOREIGN KEY (`project_id`,`version`) REFERENCES `project_versions` (`project_id`,`version`) ON UPDATE CASCADE
droy27e97e02007-11-27 16:44:19 +000051) ENGINE=InnoDB DEFAULT CHARSET=utf8;
droy27e97e02007-11-27 16:44:19 +000052--
53-- Table structure for table `languages`
54--
55
56DROP TABLE IF EXISTS `languages`;
57CREATE TABLE `languages` (
58 `language_id` smallint(5) unsigned NOT NULL auto_increment,
droyc94a4522008-02-05 15:36:53 +000059 `iso_code` varchar(6) NOT NULL,
droy27e97e02007-11-27 16:44:19 +000060 `locale` varchar(12) default NULL,
61 `name` varchar(50) NOT NULL,
62 `is_active` tinyint(3) unsigned NOT NULL default '1',
63 PRIMARY KEY (`language_id`),
64 KEY `iso_code` (`iso_code`),
65 KEY `locale` (`locale`)
66) ENGINE=InnoDB DEFAULT CHARSET=utf8;
67
droy3bb0c962008-01-17 20:45:01 +000068
69
70--
71-- Table structure for table `map_files`
72--
73
74DROP TABLE IF EXISTS `map_files`;
75CREATE TABLE `map_files` (
76 `project_id` varchar(100) NOT NULL,
77 `version` varchar(64) NOT NULL,
78 `filename` varchar(100) NOT NULL,
79 `location` varchar(255) NOT NULL,
80 `is_active` tinyint(3) unsigned NOT NULL default '1',
81 PRIMARY KEY (`project_id`, `version`, `filename`),
82 CONSTRAINT `map_files_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON UPDATE CASCADE ON DELETE CASCADE
83) ENGINE=InnoDB DEFAULT CHARSET=utf8;
84
droy27e97e02007-11-27 16:44:19 +000085--
86-- Table structure for table `profiles`
87--
88
89DROP TABLE IF EXISTS `profiles`;
90CREATE TABLE `profiles` (
91 `userid` mediumint(9) NOT NULL auto_increment,
92 `login_name` varchar(255) NOT NULL default '',
93 `cryptpassword` varchar(128) default NULL,
94 `realname` varchar(255) NOT NULL default '',
95 `disabledtext` mediumtext NOT NULL,
96 `mybugslink` tinyint(4) NOT NULL default '1',
97 `extern_id` varchar(64) default NULL,
98 `disable_mail` tinyint(4) NOT NULL default '0',
99 PRIMARY KEY (`userid`),
100 UNIQUE KEY `profiles_login_name_idx` (`login_name`)
101) ENGINE=MyISAM DEFAULT CHARSET=latin1;
102
103--
104-- Table structure for table `projects`
105--
106
107DROP TABLE IF EXISTS `projects`;
108CREATE TABLE `projects` (
109 `project_id` varchar(100) NOT NULL,
110 `is_active` tinyint(3) unsigned NOT NULL default '1',
111 PRIMARY KEY (`project_id`)
112) ENGINE=InnoDB DEFAULT CHARSET=utf8;
113
droyd664f912008-05-14 15:45:10 +0000114DROP TABLE IF EXISTS `project_progress`;
115CREATE TABLE `project_progress` (
116 `project_id` varchar(100) NOT NULL,
117 `version` varchar(64) NOT NULL,
118 `language_id` smallint(5) unsigned NOT NULL,
119 `pct_complete` float NOT NULL,
droy6e6fb6d2008-05-15 17:11:29 +0000120 `is_stale` tinyint unsigned not null default 0,
droyd664f912008-05-14 15:45:10 +0000121 PRIMARY KEY (`project_id`, `version`, `language_id`),
122 CONSTRAINT `project_progress_ibfk_1` FOREIGN KEY (`project_id`, `version`) REFERENCES `project_versions` (`project_id`, `version`) ON UPDATE CASCADE ON DELETE CASCADE,
123 CONSTRAINT `project_progress_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON UPDATE CASCADE ON DELETE CASCADE
124) ENGINE=InnoDB DEFAULT CHARSET=utf8;
125
126
127
droyc081e862007-11-30 16:37:35 +0000128DROP TABLE IF EXISTS `project_versions`;
129CREATE TABLE `project_versions` (
130 `project_id` varchar(100) NOT NULL,
droy44cb1ba2007-12-18 15:19:34 +0000131 `version` varchar(64) NOT NULL,
droyc081e862007-11-30 16:37:35 +0000132 `is_active` tinyint(3) unsigned NOT NULL default '1',
133 PRIMARY KEY (`project_id`, `version`),
134 CONSTRAINT `project_versions_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON UPDATE CASCADE ON DELETE CASCADE
135) ENGINE=InnoDB DEFAULT CHARSET=utf8;
136
droy27e97e02007-11-27 16:44:19 +0000137--
138-- Table structure for table `ratings`
139--
140
141DROP TABLE IF EXISTS `ratings`;
142CREATE TABLE `ratings` (
143 `translation_id` int(10) unsigned NOT NULL,
144 `userid` int(10) unsigned NOT NULL,
145 `rating` tinyint(3) unsigned NOT NULL default '0',
146 `created_on` datetime NOT NULL,
147 PRIMARY KEY (`translation_id`,`userid`),
148 CONSTRAINT `ratings_ibfk_1` FOREIGN KEY (`translation_id`) REFERENCES `translations` (`translation_id`) ON UPDATE CASCADE
149) ENGINE=InnoDB DEFAULT CHARSET=utf8;
150
droy684f2b62008-06-02 20:52:29 +0000151DROP TABLE IF EXISTS `release_train_projects`;
152CREATE TABLE `release_train_projects` (
153 `train_id` varchar(30) NOT NULL,
154 `project_id` varchar(100) NOT NULL,
155 `version` varchar(64) NOT NULL,
156 PRIMARY KEY (`train_id`, `project_id`, `version`),
157 CONSTRAINT `release_train_progress_ibfk_1` FOREIGN KEY (`project_id`, `version`) REFERENCES `project_versions` (`project_id`, `version`) ON UPDATE CASCADE ON DELETE CASCADE
158) ENGINE=InnoDB DEFAULT CHARSET=utf8;
159
droy27e97e02007-11-27 16:44:19 +0000160--
161-- Table structure for table `schema_info`
162--
163
164DROP TABLE IF EXISTS `schema_info`;
165CREATE TABLE `schema_info` (
166 `version` int(11) default NULL
167) ENGINE=MyISAM DEFAULT CHARSET=latin1;
168
169--
170-- Table structure for table `sessions`
171--
172
173DROP TABLE IF EXISTS `sessions`;
174CREATE TABLE `sessions` (
175 `id` int(11) unsigned NOT NULL auto_increment,
176 `userid` int(11) NOT NULL,
177 `gid` char(32) default NULL,
178 `subnet` char(15) default NULL,
179 `updated_at` datetime default NULL,
180 PRIMARY KEY (`id`),
181 KEY `gid` (`gid`),
182 KEY `userid` (`userid`)
183) ENGINE=InnoDB DEFAULT CHARSET=latin1;
184
185--
186-- Table structure for table `strings`
187--
188
droy27e97e02007-11-27 16:44:19 +0000189CREATE TABLE `strings` (
190 `string_id` int(10) unsigned NOT NULL auto_increment,
191 `file_id` int(10) unsigned NOT NULL,
192 `name` text NOT NULL,
193 `value` text NOT NULL,
194 `userid` int(10) unsigned NOT NULL,
195 `created_on` datetime NOT NULL,
196 `is_active` tinyint(1) unsigned default NULL,
droy0e2dff42008-09-24 15:35:34 +0000197 `non_translatable` tinyint(4) default '0',
droy27e97e02007-11-27 16:44:19 +0000198 PRIMARY KEY (`string_id`),
199 KEY `file_id` (`file_id`),
200 KEY `userid` (`userid`),
201 CONSTRAINT `strings_ibfk_1` FOREIGN KEY (`file_id`) REFERENCES `files` (`file_id`) ON UPDATE CASCADE,
202 CONSTRAINT `strings_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE
droy0e2dff42008-09-24 15:35:34 +0000203) ENGINE=InnoDB DEFAULT CHARSET=utf8
droy27e97e02007-11-27 16:44:19 +0000204
droy44cb1ba2007-12-18 15:19:34 +0000205
droy44cb1ba2007-12-18 15:19:34 +0000206DELIMITER ;;
207CREATE TRIGGER `upd_string` AFTER UPDATE ON `strings` FOR EACH ROW
208 BEGIN
209 IF(NEW.value <> OLD.value) THEN
210 UPDATE translations SET possibly_incorrect = 1 WHERE string_id = NEW.string_id;
211 END IF;
212 END;
213;;
214DELIMITER ;
215
216
217
droy27e97e02007-11-27 16:44:19 +0000218--
219-- Table structure for table `translation_archives`
220--
221
222DROP TABLE IF EXISTS `translation_archives`;
223CREATE TABLE `translation_archives` (
224 `translation_id` int(10) unsigned NOT NULL,
225 `string_id` int(10) unsigned NOT NULL,
226 `language_id` smallint(5) unsigned NOT NULL,
227 `version` int(10) unsigned NOT NULL default '1',
228 `value` text NOT NULL,
229 `is_active` tinyint(3) unsigned NOT NULL default '1',
230 `userid` int(10) unsigned NOT NULL,
231 `created_on` datetime NOT NULL,
232 PRIMARY KEY (`string_id`,`language_id`,`version`),
233 KEY `language_id` (`language_id`),
234 KEY `userid` (`userid`),
235 CONSTRAINT `translation_archives_ibfk_1` FOREIGN KEY (`string_id`) REFERENCES `strings` (`string_id`) ON UPDATE CASCADE,
236 CONSTRAINT `translation_archives_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON UPDATE CASCADE,
237 CONSTRAINT `translation_archives_ibfk_3` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE
238) ENGINE=InnoDB DEFAULT CHARSET=utf8;
239
240--
241-- Table structure for table `translations`
242--
243
244DROP TABLE IF EXISTS `translations`;
245CREATE TABLE `translations` (
246 `translation_id` int(10) unsigned NOT NULL auto_increment,
247 `string_id` int(10) unsigned NOT NULL,
248 `language_id` smallint(5) unsigned NOT NULL,
249 `version` int(10) unsigned NOT NULL default '1',
250 `value` text NOT NULL,
droy44cb1ba2007-12-18 15:19:34 +0000251 `possibly_incorrect` tinyint unsigned NOT NULL default '0',
droy27e97e02007-11-27 16:44:19 +0000252 `is_active` tinyint(3) unsigned NOT NULL default '1',
253 `userid` int(10) unsigned NOT NULL,
254 `created_on` datetime NOT NULL,
255 PRIMARY KEY (`string_id`,`language_id`,`version`),
256 KEY `translation_id` (`translation_id`),
257 KEY `language_id` (`language_id`),
258 KEY `userid` (`userid`),
droyd664f912008-05-14 15:45:10 +0000259 KEY `created_on` (`created_on`),
droy27e97e02007-11-27 16:44:19 +0000260 CONSTRAINT `translations_ibfk_1` FOREIGN KEY (`string_id`) REFERENCES `strings` (`string_id`) ON UPDATE CASCADE,
261 CONSTRAINT `translations_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON UPDATE CASCADE,
262 CONSTRAINT `translations_ibfk_3` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE
263) ENGINE=InnoDB DEFAULT CHARSET=utf8;
264
droy5d4b64c2008-02-29 15:22:10 +0000265SET @OLD_SQL_MODE=@@SQL_MODE;
droy27e97e02007-11-27 16:44:19 +0000266DELIMITER ;;
267/*!50003 SET SESSION SQL_MODE="" */;;
droy28b28792008-05-21 20:54:29 +0000268DROP TRIGGER IF EXISTS `ins_version`;;
droy27e97e02007-11-27 16:44:19 +0000269
droy5d4b64c2008-02-29 15:22:10 +0000270/* This trigger sets the version to max(version) + 1. It also updates the file_progress table */
droyd664f912008-05-14 15:45:10 +0000271/* We check IF(NEW.version > 1) to determine if this is a NEW translation or one that replaces an existing one */
droy5d4b64c2008-02-29 15:22:10 +0000272/* (COUNT(t.string_id) + 1) because it's a BEFORE INSERT trigger, the translated str is not in the DB yet */
273/* and without the +1 the percent would always be "one behind" */
274CREATE TRIGGER `ins_version` BEFORE INSERT ON `translations` FOR EACH ROW BEGIN
275 SET NEW.version =
276 (SELECT IFNULL(MAX(version),0)+1 FROM translations
277 WHERE string_id = NEW.string_id and language_id = NEW.language_id);
droyd664f912008-05-14 15:45:10 +0000278
279 /* Define a few fields we need here */
280 SET @FILE_ID=(SELECT file_id FROM strings WHERE string_id = NEW.string_id);
281 SET @PROJECT=(SELECT project_id FROM files WHERE file_id = @FILE_ID);
282 SET @VERSION=(SELECT version FROM files WHERE file_id = @FILE_ID);
droy5d4b64c2008-02-29 15:22:10 +0000283
droyd664f912008-05-14 15:45:10 +0000284
285 /* update this file's progress */
286 DELETE FROM file_progress where file_id = @FILE_ID
droy5d4b64c2008-02-29 15:22:10 +0000287 AND language_id = NEW.language_id;
288
droyd664f912008-05-14 15:45:10 +0000289 /* See notes above for the hairy select */
290 INSERT INTO file_progress SET file_id = @FILE_ID,
droy5d4b64c2008-02-29 15:22:10 +0000291 language_id = NEW.language_id,
292 pct_complete = (
droyd664f912008-05-14 15:45:10 +0000293 SELECT IF(NEW.version > 1,
294 IF(COUNT(s.string_id) > 0, (COUNT(t.string_id))/COUNT(s.string_id)*100,0),
295 IF(COUNT(s.string_id) > 0, (COUNT(t.string_id) + 1)/COUNT(s.string_id)*100,0)
296 ) AS translate_percent
droy5d4b64c2008-02-29 15:22:10 +0000297 FROM files AS f
droyd664f912008-05-14 15:45:10 +0000298 LEFT JOIN strings AS s ON (s.file_id = f.file_id AND s.value <> "" and s.is_active)
droy5d4b64c2008-02-29 15:22:10 +0000299 LEFT JOIN translations AS t ON (s.string_id = t.string_id
300 AND t.language_id = NEW.language_id AND t.is_active = 1)
droyd664f912008-05-14 15:45:10 +0000301 WHERE f.file_id = @FILE_ID
302 );
droy6e6fb6d2008-05-15 17:11:29 +0000303
304
305 /* update project_progress table to indicate this proj/lang/vers stats are stale */
306 /* don't know if record is there or not, so do both an insert and an update */
307 UPDATE IGNORE project_progress SET is_stale = 1 where project_id = @PROJECT
droyd664f912008-05-14 15:45:10 +0000308 AND version = @VERSION
309 AND language_id = NEW.language_id;
droy6e6fb6d2008-05-15 17:11:29 +0000310
311 INSERT IGNORE INTO project_progress SET is_stale = 1, project_id = @PROJECT,
312 version = @VERSION,
313 language_id = NEW.language_id;
droyd664f912008-05-14 15:45:10 +0000314
droy5d4b64c2008-02-29 15:22:10 +0000315END;
316;;
droy27e97e02007-11-27 16:44:19 +0000317DELIMITER ;
318/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
319
320--
321-- Table structure for table `users`
322--
323
324DROP TABLE IF EXISTS `users`;
325CREATE TABLE `users` (
326 `userid` int(10) unsigned NOT NULL,
327 `username` varchar(256) NOT NULL default '',
328 `first_name` varchar(256) NOT NULL default '',
329 `last_name` varchar(256) NOT NULL default '',
330 `email` varchar(256) NOT NULL default '',
331 `primary_language_id` int(11) NOT NULL default '0',
332 `hours_per_week` int(11) NOT NULL default '0',
333 `password_hash` varchar(256) NOT NULL default '',
droy8da30b32007-11-29 21:00:26 +0000334 `is_committer` tinyint unsigned not null default 0,
droy27e97e02007-11-27 16:44:19 +0000335 `updated_on` date NOT NULL,
336 `updated_at` time NOT NULL,
337 `created_on` date NOT NULL,
338 `created_at` time NOT NULL,
339 PRIMARY KEY (`userid`),
340 KEY `primary_language_id` (`primary_language_id`)
341) ENGINE=InnoDB DEFAULT CHARSET=utf8;
droy97c86192007-11-28 22:00:22 +0000342
droy5bb81172008-04-04 19:25:24 +0000343DROP TABLE IF EXISTS `scoreboard`;
344CREATE TABLE `scoreboard` (
345 `id` int unsigned not null auto_increment,
346 `itemid` char(6) NOT NULL,
347 `value` varchar(256) NOT NULL default '',
348 `quantity` double NOT NULL default 0,
349 PRIMARY KEY (`id`),
350 KEY(`quantity`)
351) ENGINE=InnoDB DEFAULT CHARSET=utf8;
352
353
droy97c86192007-11-28 22:00:22 +0000354
355
droy27e97e02007-11-27 16:44:19 +0000356/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
357
358/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
359/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
360/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
361/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
362/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
363/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
364/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
365
droy6426bc02008-02-01 19:35:19 +0000366
367
368
369
370
371/*
372Babel data
373User: babel@eclipse.org
374pass: password
375*/
376
377insert into profiles set login_name = "test", cryptpassword = "", realname = "tester", disabledtext = false, mybugslink = 1, extern_id = 1, disable_mail = false;
droyeccd9832008-07-22 18:04:11 +0000378insert 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, updated_on = NOW(), updated_at='',created_on = NOW(), created_at='';
droy6426bc02008-02-01 19:35:19 +0000379insert into projects set project_id = 'eclipse', is_active = 1 ;
droy2df74062008-07-03 12:45:46 +0000380insert into projects set project_id = 'europa', is_active = 1 ;
381insert into projects set project_id = 'birt', is_active = 1 ;
382insert into projects set project_id = 'modeling.emf', is_active = 1 ;
383insert into projects set project_id = 'modeling.emft', is_active = 1 ;
384insert into projects set project_id = 'modeling.gmf', is_active = 1 ;
385insert into projects set project_id = 'modeling.mdt', is_active = 1 ;
386insert into projects set project_id = 'stp', is_active = 1 ;
387insert into projects set project_id = 'tools.cdt', is_active = 1 ;
388insert into projects set project_id = 'tools.gef', is_active = 1 ;
389insert into projects set project_id = 'tools.pdt', is_active = 1 ;
390insert into projects set project_id = 'webtools', is_active = 1 ;
391
droy6426bc02008-02-01 19:35:19 +0000392INSERT INTO `languages` VALUES (1,'en',NULL,'English',1),
393(2,'fr',NULL,'French',1),
droy6426bc02008-02-01 19:35:19 +0000394(4,'de',NULL,'German',1),
395(5,'es',NULL,'Spanish',1),
396(7,'it',NULL,'Italian',1),
397(8,'ja',NULL,'Japanese',1),
398(9,'ko',NULL,'Korean',1),
kitloa2b243f2008-03-05 14:55:26 +0000399(10,'pt_BR','Brazilian','Portuguese',1),
droy6426bc02008-02-01 19:35:19 +0000400(11,'zh','Simplified','Chinese',1),
droyc94a4522008-02-05 15:36:53 +0000401(12,'zh_TW','Traditional','Chinese',1),
droy6426bc02008-02-01 19:35:19 +0000402(13,'cs',NULL,'Czech',1),
403(14,'hu',NULL,'Hungarian',1),
404(15,'pl',NULL,'Polish',1),
405(16,'ru',NULL,'Russian',1),
kitlocba17902008-02-14 19:16:02 +0000406(17,'da',NULL,'Danish',1),
droy6426bc02008-02-01 19:35:19 +0000407(18,'nl',NULL,'Dutch',1),
408(19,'fi',NULL,'Finnish',1),
409(20,'el',NULL,'Greek',1),
410(21,'no',NULL,'Norwegian',1),
411(22,'pt',NULL,'Portuguese',1),
412(23,'sv',NULL,'Swedish',1),
413(24,'tr',NULL,'Turkish',1),
414(25,'ar',NULL,'Arabic',1),
kitlocba17902008-02-14 19:16:02 +0000415(26,'iw',NULL,'Hebrew',1);
droyc94a4522008-02-05 15:36:53 +0000416insert into languages set iso_code = 'hi', name = "Hindi";
droy99907e42008-03-05 21:59:21 +0000417insert into languages set iso_code = 'ro', name = "Romanian";
droyd664f912008-05-14 15:45:10 +0000418insert into languages set iso_code = 'uk', name = "Ukrainian";
atoulme8ce8b8f2008-12-17 14:37:34 +0000419insert into languages set iso_code = 'ca', name = "Catalan";
droy2bbc1ff2008-10-30 20:30:25 +0000420insert into languages values (null, "et_EE", "Estonia", "Estonian", 1);
droy484885e2008-12-05 16:13:55 +0000421insert into languages values (null, "en_CA", "English", "Canadian", 1);
422insert into languages values (null, "en_AU", "English", "Australian", 1);
droy27157622008-12-16 21:07:06 +0000423insert into languages values (null, "mn", "", "Mongolian", 1);
droy2bbc1ff2008-10-30 20:30:25 +0000424
425
droy6426bc02008-02-01 19:35:19 +0000426insert into project_versions set project_id = "eclipse", version = "3.4", is_active = 1;
427
droy2df74062008-07-03 12:45:46 +0000428insert into project_versions set project_id = "eclipse", version = "3.3.1", is_active = 0;
429insert into project_versions set project_id = "stp", version = "0.8", is_active = 0;
430insert into project_versions set project_id = "birt", version = "2.3.0", is_active = 1;
431insert into project_versions set project_id = "modeling.emf", version = "2.4.0", is_active = 1;
432insert into project_versions set project_id = "modeling.emft", version = "0.8", is_active = 1;
433insert into project_versions set project_id = "modeling.gmf", version = "2.1", is_active = 1;
434insert into project_versions set project_id = "modeling.mdt", version = "2.4.0", is_active = 1;
435insert into project_versions set project_id = "stp", version = "1.0", is_active = 1;
436insert into project_versions set project_id = "tools.cdt", version = "5.0", is_active = 1;
437insert into project_versions set project_id = "tools.gef", version = "3.4", is_active = 1;
438insert into project_versions set project_id = "tools.pdt", version = "1.5.1", is_active = 1;
439insert into project_versions set project_id = "webtools", version = "3.0", is_active = 1;
droy6426bc02008-02-01 19:35:19 +0000440
441/* MAP INPUTS */
442insert 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);
443insert 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);
444insert 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);
445insert 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);
446insert 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);
447insert 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);
448insert 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);
449insert 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);
450insert 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);
451insert 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);
452insert 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);
453insert 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);
454insert 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);
455insert 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);
456insert 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);
457insert 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);
458insert 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);
459insert 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);
460insert 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);
461insert 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);
462insert 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);
463insert 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);
464insert 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);
465insert 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);
466insert 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);
droy5d4b64c2008-02-29 15:22:10 +0000467insert 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);
droybafb7c42008-02-29 18:24:38 +0000468
droy684f2b62008-06-02 20:52:29 +0000469
470insert into release_train_projects values ('europa', 'eclipse', '3.3.1');
droy379e51d2008-06-11 19:36:02 +0000471insert into release_train_projects values ('europa', 'stp', '0.8');
droy684f2b62008-06-02 20:52:29 +0000472insert into release_train_projects values ('ganymede', 'birt', '2.3.0');
473insert into release_train_projects values ('ganymede', 'eclipse', '3.4');
474insert into release_train_projects values ('ganymede', 'modeling.emf', '2.4.0');
475insert into release_train_projects values ('ganymede', 'modeling.emft', '0.8');
476insert into release_train_projects values ('ganymede', 'modeling.gmf', '2.1');
477insert into release_train_projects values ('ganymede', 'modeling.mdt', '2.4.0');
478insert into release_train_projects values ('ganymede', 'stp', '1.0');
479insert into release_train_projects values ('ganymede', 'tools.cdt', '5.0');
480insert into release_train_projects values ('ganymede', 'tools.gef', '3.4');
481insert into release_train_projects values ('ganymede', 'tools.pdt', '1.5.1');
482insert into release_train_projects values ('ganymede', 'webtools', '3.0');
483
droybafb7c42008-02-29 18:24:38 +0000484/* populate file_progress table */
droydc294842008-05-21 17:38:57 +0000485/* See also: dbmaintenance_15min.php */
droybafb7c42008-02-29 18:24:38 +0000486truncate table file_progress;
487INSERT INTO file_progress
droyc2c26102008-05-21 16:01:40 +0000488select f.file_id, l.language_id, IF(COUNT(s.string_id) > 0, COUNT(t.string_id)/COUNT(s.string_id)*100,100) AS translate_percent
droybafb7c42008-02-29 18:24:38 +0000489FROM files AS f
490 INNER JOIN languages as l ON l.is_active = 1
droy45413ea2008-07-28 14:36:23 +0000491 LEFT JOIN strings as s ON (s.file_id = f.file_id AND s.is_active AND s.value <> "" AND s.non_translatable = 0)
droybafb7c42008-02-29 18:24:38 +0000492 LEFT JOIN translations AS t ON (s.string_id = t.string_id
493 AND t.language_id = l.language_id AND t.is_active = 1)
494WHERE f.is_active = 1
495GROUP BY f.file_id, l.language_id;
droy5bb81172008-04-04 19:25:24 +0000496DELETE FROM file_progress WHERE pct_complete = 0;
497
droyd664f912008-05-14 15:45:10 +0000498/* populate project_progress table */
499truncate table project_progress;
500INSERT INTO project_progress
501SELECT
502 p.project_id,
503 v.version,
504 l.language_id,
droyb156beb2008-05-15 17:16:44 +0000505 IF(COUNT(s.string_id) > 0, ROUND(COUNT(t.string_id)/COUNT(s.string_id) * 100, 2), 0) AS pct_complete,
506 0
droyd664f912008-05-14 15:45:10 +0000507FROM projects as p
508 INNER JOIN project_versions AS v ON v.project_id = p.project_id
509 INNER JOIN files AS f
510 ON (f.project_id = p.project_id AND f.version = v.version AND f.is_active)
511 INNER JOIN strings AS s
512 ON (s.file_id = f.file_id AND s.is_active)
513 INNER JOIN languages AS l
514 LEFT JOIN translations AS t
515 ON (t.string_id = s.string_id AND t.language_id = l.language_id AND t.is_active)
516WHERE
517 s.value <> ""
droy45413ea2008-07-28 14:36:23 +0000518 AND s.non_translatable = 0
droyd664f912008-05-14 15:45:10 +0000519 AND p.is_active
520GROUP BY p.project_id, v.version, l.language_id;
521
droy5bb81172008-04-04 19:25:24 +0000522/* populate scoreboard */
523truncate table scoreboard;
524INSERT 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;
525INSERT 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;
526INSERT INTO scoreboard SELECT NULL, "LASGEN", "Scoreboard Last Generated", MAX(translation_id) FROM translations;