5 # Debian-specific script to upgrade the database between releases
6 # Roland Mas <lolando@debian.org>
16 use vars qw/$dbh @reqlist $query/ ;
17 use vars qw/$sys_default_domain $sys_scm_host $sys_download_host
18 $sys_shell_host $sys_users_host $sys_docs_host $sys_lists_host
19 $sys_dns1_host $sys_dns2_host $FTPINCOMING_DIR $FTPFILES_DIR
20 $sys_urlroot $sf_cache_dir $sys_name $sys_themeroot
21 $sys_news_group $sys_dbhost $sys_dbname $sys_dbuser $sys_dbpasswd
22 $sys_ldap_base_dn $sys_ldap_host $admin_password
23 $server_admin $domain_name $newsadmin_groupid $statsadmin_groupid
26 require ("/etc/gforge/local.pl") ;
27 $libdir="/usr/share/gforge/lib";
28 $sqldir="/usr/share/gforge/db";
29 require ("$libdir/sqlparser.pm") ; # Our magic SQL parser
30 require ("$libdir/sqlhelper.pm") ; # Our SQL functions
31 require ("$libdir/include.pl"); # Some other functions
35 $dbh->{AutoCommit} = 0;
36 $dbh->{RaiseError} = 1;
38 my ($sth, @array, $version, $path, $target) ;
40 # Do we have at least the basic schema?
41 # Create Sourceforge database
42 if (! &table_exists ($dbh, 'groups')) { # No 'groups' table
43 # Installing SF 2.6 from scratch
44 &debug ("Creating initial Sourceforge database from files.") ;
46 &create_metadata_table ("2.5.9999") ;
48 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
50 $sth = $dbh->prepare ($query) ;
52 @array = $sth->fetchrow_array () ;
55 &debug ("Updating debian_meta_data table.") ;
56 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', 'scratch-to-2.6')" ;
58 $sth = $dbh->prepare ($query) ;
62 &debug ("Committing.") ;
65 } else { # A 'groups' table exists
66 if (! &table_exists ($dbh, 'debian_meta_data')) { # No 'debian_meta_data' table
67 # If we're here, we're upgrading from 2.5-7 or earlier
68 # We therefore need to create the table
69 &create_metadata_table ("2.5-7+just+before+8") ;
72 $version = &get_db_version ;
73 if (&is_lesser ($version, "2.5.9999")) {
74 &debug ("Found an old (2.5) database, will upgrade to 2.6") ;
76 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
78 $sth = $dbh->prepare ($query) ;
80 @array = $sth->fetchrow_array () ;
84 # &debug ("Updating debian_meta_data table.") ;
85 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', '2.5-to-2.6')" ;
87 $sth = $dbh->prepare ($query) ;
90 &debug ("Committing.") ;
96 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
98 $sth = $dbh->prepare ($query) ;
100 @array = $sth->fetchrow_array () ;
103 if ($array[0] == 0) {
106 $query = "SELECT value from debian_meta_data where key = 'current-path'";
108 $sth = $dbh->prepare ($query) ;
110 @array = $sth->fetchrow_array () ;
117 ($path eq 'scratch-to-2.6') && do {
118 $version = &get_db_version ;
119 $target = "2.5.9999.1+global+data+done" ;
120 if (&is_lesser ($version, $target)) {
121 my @filelist = qw{ sf-2.6-complete.sql } ;
122 # TODO: user_rating.sql
124 foreach my $file (@filelist) {
125 &debug ("Processing $file") ;
126 @reqlist = @{ &parse_sql_file ($sqldir."/".$file) } ;
128 foreach my $s (@reqlist) {
131 $sth = $dbh->prepare ($query) ;
138 &update_db_version ($target) ;
139 &debug ("Committing.") ;
143 $version = &get_db_version ;
144 $target = "2.5.9999.2+local+data+done" ;
145 if (&is_lesser ($version, $target)) {
146 &debug ("Adding local data.") ;
148 do "/etc/gforge/local.pl" or die "Cannot read /etc/gforge/local.pl" ;
150 my ($login, $md5pwd, $email, $noreplymail, $date) ;
153 $md5pwd = 'INVALID' ;
154 $email = $server_admin ;
155 $noreplymail="noreply\@$domain_name" ;
159 "UPDATE groups SET homepage = '$domain_name/admin/' where group_id = 1",
160 "UPDATE groups SET homepage = '$domain_name/news/' where group_id = 2",
161 "UPDATE groups SET homepage = '$domain_name/stats/' where group_id = 3",
162 "UPDATE groups SET homepage = '$domain_name/peerrating/' where group_id = 4",
163 "UPDATE users SET email = '$noreplymail' where user_id = 100",
164 "INSERT INTO users VALUES (101,'$login','$email','$md5pwd','Sourceforge admin','A','/bin/bash','','N',2000,'shell',$date,'',1,0,NULL,NULL,0,'','GMT', 1, 0)",
165 "SELECT setval ('\"users_pk_seq\"', 102, 'f')",
166 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 1, 'A')",
167 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 2, 'A')",
168 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 3, 'A')",
169 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 4, 'A')"
172 foreach my $s (@reqlist) {
175 $sth = $dbh->prepare ($query) ;
181 &update_db_version ($target) ;
182 &debug ("Committing.") ;
186 $version = &get_db_version ;
187 $target = "2.5.9999.3+skills+done" ;
188 if (&is_lesser ($version, $target)) {
189 &debug ("Inserting skills.") ;
191 foreach my $skill (split m/;/, "Ada;C;C++;HTML;LISP;Perl;PHP;Python;SQL") {
192 push @reqlist, "INSERT INTO people_skill (name) VALUES ('$skill')" ;
195 foreach my $s (@reqlist) {
198 $sth = $dbh->prepare ($query) ;
204 &update_db_version ($target) ;
205 &debug ("Committing.") ;
209 $version = &get_db_version ;
210 $target = "2.6-0+checkpoint+1" ;
211 if (&is_lesser ($version, $target)) {
212 &debug ("Updating debian_meta_data table.") ;
213 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
215 $sth = $dbh->prepare ($query) ;
219 &update_db_version ($target) ;
220 &debug ("Committing.") ;
227 ($path eq '2.5-to-2.6') && do {
229 $version = &get_db_version ;
231 if (&is_lesser ($version, $target)) {
232 &debug ("Adding row to people_job_category.") ;
233 $query = "INSERT INTO people_job_category VALUES (100, 'Undefined', 0)" ;
234 $sth = $dbh->prepare ($query) ;
238 &update_db_version ($target) ;
239 &debug ("Committing.") ;
243 $version = &get_db_version ;
245 if (&is_lesser ($version, $target)) {
246 &debug ("Adding row to supported_languages.") ;
247 $query = "INSERT INTO supported_languages VALUES (15, 'Korean', 'Korean.class', 'Korean', 'kr')" ;
248 $sth = $dbh->prepare ($query) ;
252 &update_db_version ($target) ;
253 &debug ("Committing.") ;
257 $version = &get_db_version ;
259 if (&is_lesser ($version, $target)) {
260 &debug ("Fixing unix_box entries.") ;
262 $query = "update groups set unix_box = 'shell'" ;
263 $sth = $dbh->prepare ($query) ;
267 $query = "update users set unix_box = 'shell'" ;
268 $sth = $dbh->prepare ($query) ;
272 &debug ("Also fixing a few sequences.") ;
274 &bump_sequence_to ($dbh, "bug_pk_seq", 100) ;
275 &bump_sequence_to ($dbh, "project_task_pk_seq", 100) ;
277 &update_db_version ($target) ;
278 &debug ("Committing.") ;
282 $version = &get_db_version ;
284 if (&is_lesser ($version, $target)) {
285 &debug ("Adding rows to supported_languages.") ;
287 "INSERT INTO supported_languages VALUES (16,'Bulgarian','Bulgarian.class','Bulgarian','bg')",
288 "INSERT INTO supported_languages VALUES (17,'Greek','Greek.class','Greek','el')",
289 "INSERT INTO supported_languages VALUES (18,'Indonesian','Indonesian.class','Indonesian','id')",
290 "INSERT INTO supported_languages VALUES (19,'Portuguese (Brazillian)','PortugueseBrazillian.class','PortugueseBrazillian', 'br')",
291 "INSERT INTO supported_languages VALUES (20,'Polish','Polish.class','Polish','pl')",
292 "INSERT INTO supported_languages VALUES (21,'Portuguese','Portuguese.class','Portuguese', 'pt')",
293 "INSERT INTO supported_languages VALUES (22,'Russian','Russian.class','Russian','ru')"
296 foreach my $s (@reqlist) {
299 $sth = $dbh->prepare ($query) ;
305 &update_db_version ($target) ;
306 &debug ("Committing.") ;
310 $version = &get_db_version ;
312 if (&is_lesser ($version, $target)) {
313 &debug ("Fixing unix_uid entries.") ;
315 $query = "UPDATE users SET unix_uid = nextval ('unix_uid_seq') WHERE unix_status != 'N' AND status != 'P' AND unix_uid = 0" ;
316 $sth = $dbh->prepare ($query) ;
320 &update_db_version ($target) ;
321 &debug ("Committing.") ;
325 $version = &get_db_version ;
326 $target = "2.5.9999.1+temp+data+dropped" ;
327 if (&is_lesser ($version, $target)) {
328 &debug ("Preparing to upgrade your database - dropping temporary tables") ;
330 my @tables = qw/ user_metric_tmp1_1 user_metric_tmp1_2
331 user_metric_tmp1_3 user_metric_tmp1_4
332 user_metric_tmp1_5 user_metric_tmp1_6
333 user_metric_tmp1_7 user_metric_tmp1_8 user_metric1
334 user_metric2 user_metric3 user_metric4 user_metric5
335 user_metric6 user_metric7 user_metric8
336 project_counts_tmp project_metric_tmp
337 project_metric_tmp1 project_counts_weekly_tmp
338 project_metric_weekly_tmp project_metric_weekly_tmp1
341 my @sequences = qw/ user_metric1_ranking_seq
342 user_metric2_ranking_seq user_metric3_ranking_seq
343 user_metric4_ranking_seq user_metric5_ranking_seq
344 user_metric6_ranking_seq user_metric7_ranking_seq
345 user_metric8_ranking_seq project_metric_weekly_seq
346 trove_treesum_trove_treesum_seq
347 project_metric_tmp1_pk_seq / ;
349 my @indexes = qw/ idx_project_metric_group
350 idx_project_metric_weekly_group
351 user_metric_history_date_userid / ;
353 foreach my $table (@tables) {
354 &drop_table_if_exists ($dbh, $table) ;
357 foreach my $sequence (@sequences) {
358 &drop_sequence_if_exists ($dbh, $sequence) ;
361 foreach my $index (@indexes) {
362 &drop_index_if_exists ($dbh, $index) ;
365 &update_db_version ($target) ;
366 &debug ("Committing.") ;
370 $version = &get_db_version ;
371 $target = "2.5.9999.2+data+upgraded" ;
372 if (&is_lesser ($version, $target)) {
373 &debug ("Upgrading your database scheme from 2.5") ;
376 "ALTER TABLE groups DROP CONSTRAINT groups_pkey",
377 "ALTER TABLE users DROP CONSTRAINT users_pkey",
379 foreach my $s (@reqlist) {
382 $sth = $dbh->prepare ($query) ;
387 @reqlist = @{ &parse_sql_file ("$sqldir/sf2.5-to-sf2.6.sql") } ;
388 foreach my $s (@reqlist) {
391 $sth = $dbh->prepare ($query) ;
397 &update_db_version ($target) ;
398 &debug ("Committing.") ;
402 $version = &get_db_version ;
403 $target = "2.5.9999.3+artifact+transcoded" ;
404 if (&is_lesser ($version, $target)) {
405 &debug ("Transcoding the artifact data fields") ;
407 $query = "SELECT id,bin_data FROM artifact_file ORDER BY id ASC" ;
409 $sth = $dbh->prepare ($query) ;
411 while (@array = $sth->fetchrow_array) {
412 my $query2 = "UPDATE artifact_file SET bin_data='" ;
413 $query2 .= encode_base64 (decode_entities ($array [1])) ;
414 $query2 .= "' WHERE id=" ;
415 $query2 .= $array [0] ;
418 my $sth2 =$dbh->prepare ($query2) ;
425 &update_db_version ($target) ;
426 &debug ("Committing.") ;
430 $version = &get_db_version ;
431 $target = "2.5.9999.4+groups+inserted" ;
432 if (&is_lesser ($version, $target)) {
433 &debug ("Inserting missing groups") ;
436 "INSERT INTO groups (group_name, homepage,
437 is_public, status, unix_group_name,
438 unix_box, http_domain, short_description,
439 cvs_box, license, register_purpose,
440 license_other, register_time, rand_hash,
441 use_mail, use_survey, use_forum, use_pm,
442 use_cvs, use_news, type, use_docman,
443 new_task_address, send_all_tasks,
445 VALUES ('Stats', '$domain_name/top/', 0,
446 'A', 'stats', 'shell', NULL, NULL, 'cvs',
447 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
449 "INSERT INTO groups (group_name, homepage,
450 is_public, status, unix_group_name,
451 unix_box, http_domain, short_description,
452 cvs_box, license, register_purpose,
453 license_other, register_time, rand_hash,
454 use_mail, use_survey, use_forum, use_pm,
455 use_cvs, use_news, type, use_docman,
456 new_task_address, send_all_tasks,
458 VALUES ('Peer Ratings', '$domain_name/people/', 0,
459 'A', 'peerrating', 'shell', NULL, NULL, 'cvs1',
460 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
464 foreach my $s (@reqlist) {
467 $sth = $dbh->prepare ($query) ;
472 &update_db_version ($target) ;
473 &debug ("Committing.") ;
477 $version = &get_db_version ;
478 $target = "2.6-0+checkpoint+1" ;
479 if (&is_lesser ($version, $target)) {
480 &debug ("Database has successfully been converted.") ;
481 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
483 $sth = $dbh->prepare ($query) ;
487 &update_db_version ($target) ;
488 &debug ("Committing.") ;
496 $version = &get_db_version ;
497 $target = "2.6-0+checkpoint+2" ;
498 if (&is_lesser ($version, $target)) {
499 &debug ("Updating permissions on system groups.") ;
500 $query = "UPDATE groups SET group_name='Site Admin', is_public=1 WHERE group_id=1" ;
502 $sth = $dbh->prepare ($query) ;
505 $query = "UPDATE groups SET group_name='Site News Admin', is_public=1 WHERE group_id=$sys_news_group" ;
507 $sth = $dbh->prepare ($query) ;
511 &update_db_version ($target) ;
512 &debug ("Committing.") ;
516 $version = &get_db_version ;
517 $target = "2.6-0+checkpoint+3" ;
518 if (&is_lesser ($version, $target)) {
519 &debug ("Creating table group_cvs_history.") ;
520 $query = "CREATE TABLE group_cvs_history (
521 id integer DEFAULT nextval('group_cvs_history_pk_seq'::text) NOT NULL,
522 group_id integer DEFAULT '0' NOT NULL,
523 user_name character varying(80) DEFAULT '' NOT NULL,
524 cvs_commits integer DEFAULT '0' NOT NULL,
525 cvs_commits_wk integer DEFAULT '0' NOT NULL,
526 cvs_adds integer DEFAULT '0' NOT NULL,
527 cvs_adds_wk integer DEFAULT '0' NOT NULL,
530 $sth = $dbh->prepare ($query) ;
534 &update_db_version ($target) ;
535 &debug ("Committing.") ;
539 $version = &get_db_version ;
540 $target = "2.6-0+checkpoint+4" ;
541 if (&is_lesser ($version, $target)) {
542 &debug ("Registering Savannah themes.") ;
544 $query = "SELECT max(theme_id) FROM themes" ;
546 $sth = $dbh->prepare ($query) ;
548 @array = $sth->fetchrow_array () ;
550 my $maxid = $array [0] ;
552 &bump_sequence_to ($dbh, "themes_pk_seq", $maxid) ;
555 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_codex', 'Savannah CodeX')",
556 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_forest', 'Savannah Forest')",
557 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_reverse', 'Savannah Reverse')",
558 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_sad', 'Savannah Sad')",
559 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_savannah', 'Savannah Original')",
560 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_slashd', 'Savannah SlashDot')",
561 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_startrek', 'Savannah StarTrek')",
562 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_transparent', 'Savannah Transparent')",
563 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_water', 'Savannah Water')",
564 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_www.gnu.org', 'Savannah www.gnu.org')"
566 foreach my $s (@reqlist) {
569 $sth = $dbh->prepare ($query) ;
575 &update_db_version ($target) ;
576 &debug ("Committing.") ;
580 $version = &get_db_version ;
581 $target = "2.6-0+checkpoint+5" ;
582 if (&is_lesser ($version, $target)) {
583 &debug ("Registering yet another Savannah theme.") ;
585 $query = "INSERT INTO themes (dirname, fullname) VALUES ('savannah_darkslate', 'Savannah Dark Slate')";
587 $sth = $dbh->prepare ($query) ;
591 &update_db_version ($target) ;
592 &debug ("Committing.") ;
596 $version = &get_db_version ;
597 $target = "2.6-0+checkpoint+6" ;
598 if (&is_lesser ($version, $target)) {
599 &debug ("Updating language codes.") ;
602 "UPDATE supported_languages SET language_code='en' where classname='English'",
603 "UPDATE supported_languages SET language_code='ja' where classname='Japanese'",
604 "UPDATE supported_languages SET language_code='iw' where classname='Hebrew'",
605 "UPDATE supported_languages SET language_code='es' where classname='Spanish'",
606 "UPDATE supported_languages SET language_code='th' where classname='Thai'",
607 "UPDATE supported_languages SET language_code='de' where classname='German'",
608 "UPDATE supported_languages SET language_code='it' where classname='Italian'",
609 "UPDATE supported_languages SET language_code='no' where classname='Norwegian'",
610 "UPDATE supported_languages SET language_code='sv' where classname='Swedish'",
611 "UPDATE supported_languages SET language_code='zh' where classname='Chinese'",
612 "UPDATE supported_languages SET language_code='nl' where classname='Dutch'",
613 "UPDATE supported_languages SET language_code='eo' where classname='Esperanto'",
614 "UPDATE supported_languages SET language_code='ca' where classname='Catalan'",
615 "UPDATE supported_languages SET language_code='ko' where classname='Korean'",
616 "UPDATE supported_languages SET language_code='bg' where classname='Bulgarian'",
617 "UPDATE supported_languages SET language_code='el' where classname='Greek'",
618 "UPDATE supported_languages SET language_code='id' where classname='Indonesian'",
619 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese (Brazillian)'",
620 "UPDATE supported_languages SET language_code='pl' where classname='Polish'",
621 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese'",
622 "UPDATE supported_languages SET language_code='ru' where classname='Russian'",
623 "UPDATE supported_languages SET language_code='fr' where classname='French'"
625 foreach my $s (@reqlist) {
628 $sth = $dbh->prepare ($query) ;
633 &update_db_version ($target) ;
634 &debug ("Committing.") ;
638 $version = &get_db_version ;
639 $target = "2.6-0+checkpoint+7" ;
640 if (&is_lesser ($version, $target)) {
641 &debug ("Fixing artifact-related views.") ;
643 &drop_view_if_exists ($dbh, "artifact_file_user_vw") ;
644 &drop_view_if_exists ($dbh, "artifact_history_user_vw") ;
645 &drop_view_if_exists ($dbh, "artifact_message_user_vw") ;
646 &drop_view_if_exists ($dbh, "artifactperm_artgrouplist_vw") ;
647 &drop_view_if_exists ($dbh, "artifactperm_user_vw") ;
648 &drop_view_if_exists ($dbh, "artifact_vw") ;
651 "CREATE VIEW artifact_file_user_vw as SELECT af.id, af.artifact_id, af.description, af.bin_data, af.filename, af.filesize, af.filetype, af.adddate, af.submitted_by, users.user_name, users.realname FROM artifact_file af, users WHERE (af.submitted_by = users.user_id)",
652 "CREATE VIEW artifact_history_user_vw as SELECT ah.id, ah.artifact_id, ah.field_name, ah.old_value, ah.entrydate, users.user_name FROM artifact_history ah, users WHERE (ah.mod_by = users.user_id)",
653 "CREATE VIEW artifact_message_user_vw as SELECT am.id, am.artifact_id, am.from_email, am.body, am.adddate, users.user_id, users.email, users.user_name, users.realname FROM artifact_message am, users WHERE (am.submitted_by = users.user_id)",
654 "CREATE VIEW artifactperm_artgrouplist_vw as SELECT agl.group_artifact_id, agl.name, agl.description, agl.group_id, ap.user_id, ap.perm_level FROM artifact_perm ap, artifact_group_list agl WHERE (ap.group_artifact_id = agl.group_artifact_id)",
655 "CREATE VIEW artifactperm_user_vw as SELECT ap.id, ap.group_artifact_id, ap.user_id, ap.perm_level, users.user_name, users.realname FROM artifact_perm ap, users WHERE (users.user_id = ap.user_id)",
656 "CREATE VIEW artifact_vw as SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.status_id, artifact.category_id, artifact.artifact_group_id, artifact.resolution_id, artifact.priority, artifact.submitted_by, artifact.assigned_to, artifact.open_date, artifact.close_date, artifact.summary, artifact.details, u.user_name AS assigned_unixname, u.realname AS assigned_realname, u.email AS assigned_email, u2.user_name AS submitted_unixname, u2.realname AS submitted_realname, u2.email AS submitted_email, artifact_status.status_name, artifact_category.category_name, artifact_group.group_name, artifact_resolution.resolution_name FROM users u, users u2, artifact, artifact_status, artifact_category, artifact_group, artifact_resolution WHERE ((((((artifact.assigned_to = u.user_id) AND (artifact.submitted_by = u2.user_id)) AND (artifact.status_id = artifact_status.id)) AND (artifact.category_id = artifact_category.id)) AND (artifact.artifact_group_id = artifact_group.id)) AND (artifact.resolution_id = artifact_resolution.id))"
658 foreach my $s (@reqlist) {
661 $sth = $dbh->prepare ($query) ;
666 &update_db_version ($target) ;
667 &debug ("Committing.") ;
671 $version = &get_db_version ;
672 $target = "2.6-0+checkpoint+8" ;
673 if (&is_lesser ($version, $target)) {
674 &debug ("Adding integrity constraints between the Trove map tables.") ;
677 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
678 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
679 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
680 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
681 "DELETE FROM trove_treesums WHERE trove_cat_id NOT IN (SELECT trove_cat_id FROM trove_cat)",
682 "ALTER TABLE trove_treesums ADD CONSTRAINT trove_treesums_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
684 foreach my $s (@reqlist) {
687 $sth = $dbh->prepare ($query) ;
692 &update_db_version ($target) ;
693 &debug ("Committing.") ;
697 $version = &get_db_version ;
698 $target = "2.6-0+checkpoint+9" ;
699 if (&is_lesser ($version, $target)) {
700 &debug ("Adding extra fields to the groups table.") ;
703 "ALTER TABLE groups ADD COLUMN use_ftp integer",
704 "ALTER TABLE groups ALTER COLUMN use_ftp SET DEFAULT 1",
705 "UPDATE groups SET use_ftp = 1",
706 "ALTER TABLE groups ADD COLUMN use_tracker integer",
707 "ALTER TABLE groups ALTER COLUMN use_tracker SET DEFAULT 1",
708 "UPDATE groups SET use_tracker = 1",
709 "ALTER TABLE groups ADD COLUMN use_frs integer",
710 "ALTER TABLE groups ALTER COLUMN use_frs SET DEFAULT 1",
711 "UPDATE groups SET use_frs = 1",
712 "ALTER TABLE groups ADD COLUMN use_stats integer",
713 "ALTER TABLE groups ALTER COLUMN use_stats SET DEFAULT 1",
714 "UPDATE groups SET use_stats = 1",
715 "ALTER TABLE groups ADD COLUMN enable_pserver integer",
716 "ALTER TABLE groups ALTER COLUMN enable_pserver SET DEFAULT 1",
717 "UPDATE groups SET enable_pserver = 1",
718 "ALTER TABLE groups ADD COLUMN enable_anoncvs integer",
719 "ALTER TABLE groups ALTER COLUMN enable_anoncvs SET DEFAULT 1",
720 "UPDATE groups SET enable_anoncvs = 1",
722 foreach my $s (@reqlist) {
725 $sth = $dbh->prepare ($query) ;
730 &update_db_version ($target) ;
731 &debug ("Committing.") ;
735 $version = &get_db_version ;
736 $target = "2.6-0+checkpoint+10" ;
737 if (&is_lesser ($version, $target)) {
738 &debug ("Updating supported_languages table.") ;
741 "ALTER TABLE supported_languages RENAME COLUMN language_code TO language_code_old",
742 "ALTER TABLE supported_languages ADD COLUMN language_code character(5)",
743 "UPDATE supported_languages SET language_code = language_code_old",
744 "ALTER TABLE supported_languages DROP COLUMN language_code_old",
745 "UPDATE supported_languages SET language_code='pt_BR', classname='PortugueseBrazilian', name='Pt. Brazilian', filename='PortugueseBrazilian.class' where classname='PortugueseBrazillian'",
747 foreach my $s (@reqlist) {
750 $sth = $dbh->prepare ($query) ;
755 &update_db_version ($target) ;
756 &debug ("Committing.") ;
760 $version = &get_db_version ;
761 $target = "2.6-0+checkpoint+11" ;
762 if (&is_lesser ($version, $target)) {
763 &debug ("Adding tables for the plugin subsystem.") ;
766 "CREATE SEQUENCE plugins_pk_seq",
767 "CREATE TABLE plugins (plugin_id integer DEFAULT nextval('plugins_pk_seq'::text) NOT NULL, plugin_name varchar(32) UNIQUE NOT NULL, plugin_desc text, CONSTRAINT plugins_pkey PRIMARY KEY (plugin_id))",
768 "CREATE SEQUENCE group_plugin_pk_seq",
769 "CREATE TABLE group_plugin (group_plugin_id integer DEFAULT nextval('group_plugin_pk_seq'::text) NOT NULL, group_id integer, plugin_id integer, CONSTRAINT group_plugin_pkey PRIMARY KEY (group_plugin_id), CONSTRAINT group_plugin_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL, CONSTRAINT group_plugin_plugin_id_fk FOREIGN KEY (plugin_id) REFERENCES plugins(plugin_id) MATCH FULL)",
770 "CREATE SEQUENCE user_plugin_pk_seq",
771 "CREATE TABLE user_plugin (user_plugin_id integer DEFAULT nextval('user_plugin_pk_seq'::text) NOT NULL, user_id integer, plugin_id integer, CONSTRAINT user_plugin_pkey PRIMARY KEY (user_plugin_id), CONSTRAINT user_plugin_user_id_fk FOREIGN KEY (user_id) REFERENCES users(user_id) MATCH FULL, CONSTRAINT user_plugin_plugin_id_fk FOREIGN KEY (plugin_id) REFERENCES plugins(plugin_id) MATCH FULL)",
773 foreach my $s (@reqlist) {
776 $sth = $dbh->prepare ($query) ;
781 &update_db_version ($target) ;
782 &debug ("Committing.") ;
786 $version = &get_db_version ;
787 $target = "2.6-0+checkpoint+12" ;
788 if (&is_lesser ($version, $target)) {
789 &debug ("Upgrading with 20021125.sql") ;
791 @reqlist = @{ &parse_sql_file ("$sqldir/20021125.sql") } ;
792 foreach my $s (@reqlist) {
795 $sth = $dbh->prepare ($query) ;
801 &update_db_version ($target) ;
802 &debug ("Committing $target.") ;
806 $version = &get_db_version ;
807 $target = "2.6-0+checkpoint+13" ;
808 if (&is_lesser ($version, $target)) {
809 &debug ("Upgrading with 20021212.sql") ;
811 @reqlist = @{ &parse_sql_file ("$sqldir/20021212.sql") } ;
812 foreach my $s (@reqlist) {
815 $sth = $dbh->prepare ($query) ;
821 &update_db_version ($target) ;
822 &debug ("Committing $target.") ;
826 $version = &get_db_version ;
827 $target = "2.6-0+checkpoint+14" ;
828 if (&is_lesser ($version, $target)) {
829 &debug ("Upgrading with 20021213-1.sql") ;
831 @reqlist = @{ &parse_sql_file ("$sqldir/20021213-1.sql") } ;
832 foreach my $s (@reqlist) {
835 $sth = $dbh->prepare ($query) ;
841 &update_db_version ($target) ;
842 &debug ("Committing $target.") ;
846 $version = &get_db_version ;
847 $target = "2.6-0+checkpoint+15" ;
848 if (&is_lesser ($version, $target)) {
849 &debug ("Transcoding documentation data fields") ;
850 $query = "SELECT docid,data FROM doc_data ORDER BY docid ASC" ;
852 $sth = $dbh->prepare ($query) ;
854 while (@array = $sth->fetchrow_array) {
855 my $query2 = "UPDATE doc_data SET data='" ;
856 $query2 .= encode_base64 (decode_entities ($array [1])) ;
857 $query2 .= "', filename='file".$array [0].".html'";
858 $query2 .= ", filetype='text/html'";
859 $query2 .= " WHERE docid=" ;
860 $query2 .= $array [0] ;
863 my $sth2 =$dbh->prepare ($query2) ;
870 &update_db_version ($target) ;
871 &debug ("Committing $target.") ;
875 $version = &get_db_version ;
876 $target = "2.6-0+checkpoint+16" ;
877 if (&is_lesser ($version, $target)) {
878 &debug ("Upgrading with 20021214.sql") ;
880 @reqlist = @{ &parse_sql_file ("$sqldir/20021214.sql") } ;
881 foreach my $s (@reqlist) {
884 $sth = $dbh->prepare ($query) ;
890 &update_db_version ($target) ;
891 &debug ("Committing $target.") ;
895 $version = &get_db_version ;
896 $target = "2.6-0+checkpoint+17" ;
897 if (&is_lesser ($version, $target)) {
898 &debug ("Upgrading with 20021215.sql") ;
900 @reqlist = @{ &parse_sql_file ("$sqldir/20021215.sql") } ;
901 foreach my $s (@reqlist) {
904 $sth = $dbh->prepare ($query) ;
910 &update_db_version ($target) ;
911 &debug ("Committing $target.") ;
915 $version = &get_db_version ;
916 $target = "2.6-0+checkpoint+18" ;
917 if (&is_lesser ($version, $target)) {
918 &debug ("Upgrading with 20021216.sql") ;
920 @reqlist = @{ &parse_sql_file ("$sqldir/20021216.sql") } ;
921 foreach my $s (@reqlist) {
924 $sth = $dbh->prepare ($query) ;
930 &update_db_version ($target) ;
931 &debug ("Committing $target.") ;
935 $version = &get_db_version ;
936 $target = "2.6-0+checkpoint+19" ;
937 if (&is_lesser ($version, $target)) {
938 &debug ("Upgrading with 20021223-2.sql") ;
940 @reqlist = @{ &parse_sql_file ("$sqldir/20021223-2.sql") } ;
941 foreach my $s (@reqlist) {
944 $sth = $dbh->prepare ($query) ;
950 &update_db_version ($target) ;
951 &debug ("Committing $target.") ;
955 $version = &get_db_version ;
956 $target = "2.6-0+checkpoint+20" ;
957 if (&is_lesser ($version, $target)) {
958 &debug ("Upgrading with 20030102-2.sql") ;
960 @reqlist = @{ &parse_sql_file ("$sqldir/20030102-2.sql") } ;
961 foreach my $s (@reqlist) {
964 $sth = $dbh->prepare ($query) ;
970 &update_db_version ($target) ;
971 &debug ("Committing $target.") ;
975 $version = &get_db_version ;
976 $target = "2.6-0+checkpoint+21" ;
977 if (&is_lesser ($version, $target)) {
978 &debug ("Upgrading with 20030105.sql") ;
980 @reqlist = @{ &parse_sql_file ("$sqldir/20030105.sql") } ;
981 foreach my $s (@reqlist) {
984 $sth = $dbh->prepare ($query) ;
990 &update_db_version ($target) ;
991 &debug ("Committing $target.") ;
995 $version = &get_db_version ;
996 $target = "2.6-0+checkpoint+22" ;
997 if (&is_lesser ($version, $target)) {
998 &debug ("Upgrading with 20030107.sql") ;
1000 @reqlist = @{ &parse_sql_file ("$sqldir/20030107.sql") } ;
1001 foreach my $s (@reqlist) {
1004 $sth = $dbh->prepare ($query) ;
1010 &update_db_version ($target) ;
1011 &debug ("Committing $target.") ;
1015 $version = &get_db_version ;
1016 $target = "2.6-0+checkpoint+23" ;
1017 if (&is_lesser ($version, $target)) {
1018 &debug ("Upgrading with 20030109.sql") ;
1020 @reqlist = @{ &parse_sql_file ("$sqldir/20030109.sql") } ;
1021 foreach my $s (@reqlist) {
1024 $sth = $dbh->prepare ($query) ;
1030 &update_db_version ($target) ;
1031 &debug ("Committing $target.") ;
1035 $version = &get_db_version ;
1036 $target = "2.6-0+checkpoint+24" ;
1037 if (&is_lesser ($version, $target)) {
1039 &debug ("Adjusting language sequences") ;
1041 $query = "SELECT max(language_id) FROM supported_languages" ;
1042 $sth = $dbh->prepare ($query) ;
1044 @array = $sth->fetchrow_array () ;
1046 my $maxid = $array [0] ;
1047 &bump_sequence_to ($dbh, "supported_languages_pk_seq", $maxid) ;
1049 &debug ("Upgrading with 20030112.sql") ;
1051 @reqlist = @{ &parse_sql_file ("$sqldir/20030112.sql") } ;
1052 foreach my $s (@reqlist) {
1055 $sth = $dbh->prepare ($query) ;
1061 &update_db_version ($target) ;
1062 &debug ("Committing $target.") ;
1066 $version = &get_db_version ;
1067 $target = "2.6-0+checkpoint+25" ;
1068 if (&is_lesser ($version, $target)) {
1069 &debug ("Upgrading with 20030113-2.sql") ;
1071 @reqlist = @{ &parse_sql_file ("$sqldir/20030113-2.sql") } ;
1072 foreach my $s (@reqlist) {
1075 $sth = $dbh->prepare ($query) ;
1081 &update_db_version ($target) ;
1082 &debug ("Committing $target.") ;
1086 $version = &get_db_version ;
1087 $target = "2.6-0+checkpoint+26" ;
1088 if (&is_lesser ($version, $target)) {
1089 &debug ("Upgrading with 20030131.sql") ;
1091 @reqlist = @{ &parse_sql_file ("$sqldir/20030131.sql") } ;
1092 foreach my $s (@reqlist) {
1095 $sth = $dbh->prepare ($query) ;
1101 &update_db_version ($target) ;
1102 &debug ("Committing $target.") ;
1106 $version = &get_db_version ;
1107 $target = "2.6-0+checkpoint+27" ;
1108 if (&is_lesser ($version, $target)) {
1109 &debug ("Upgrading with 20030209.sql") ;
1111 @reqlist = @{ &parse_sql_file ("$sqldir/20030209.sql") } ;
1112 foreach my $s (@reqlist) {
1115 $sth = $dbh->prepare ($query) ;
1121 &update_db_version ($target) ;
1122 &debug ("Committing $target.") ;
1126 $version = &get_db_version ;
1127 $target = "2.6-0+checkpoint+28" ;
1128 if (&is_lesser ($version, $target)) {
1129 &debug ("Upgrading with 20030312.sql") ;
1131 @reqlist = @{ &parse_sql_file ("$sqldir/20030312.sql") } ;
1132 foreach my $s (@reqlist) {
1135 $sth = $dbh->prepare ($query) ;
1141 &update_db_version ($target) ;
1142 &debug ("Committing $target.") ;
1146 $version = &get_db_version ;
1147 $target = "2.6-0+checkpoint+29" ;
1148 if (&is_lesser ($version, $target)) {
1149 &debug ("Registering KDE theme.") ;
1151 $query = "INSERT INTO themes (dirname, fullname) VALUES ('kde', 'KDE')";
1153 $sth = $dbh->prepare ($query) ;
1157 &update_db_version ($target) ;
1158 &debug ("Committing.") ;
1163 $version = &get_db_version ;
1164 $target = "2.6-0+checkpoint+30" ;
1165 if (&is_lesser ($version, $target)) {
1166 &debug ("Registering Dark Aqua theme.") ;
1168 $query = "INSERT INTO themes (dirname, fullname) VALUES ('darkaqua', 'Dark Aqua')";
1170 $sth = $dbh->prepare ($query) ;
1174 &update_db_version ($target) ;
1175 &debug ("Committing.") ;
1180 $version = &get_db_version ;
1181 $target = "2.6-0+checkpoint+31" ;
1182 if (&is_lesser ($version, $target)) {
1183 &debug ("Upgrading with 20030513.sql") ;
1185 @reqlist = @{ &parse_sql_file ("$sqldir/20030513.sql") } ;
1186 foreach my $s (@reqlist) {
1189 $sth = $dbh->prepare ($query) ;
1195 &update_db_version ($target) ;
1196 &debug ("Committing.") ;
1201 $version = &get_db_version ;
1203 if (&is_lesser ($version, $target)) {
1204 &debug ("Database schema is now version 3.0-1.") ;
1206 &update_db_version ($target) ;
1207 &debug ("Committing.") ;
1212 $version = &get_db_version ;
1214 if (&is_lesser ($version, $target)) {
1215 &debug ("Upgrading with 20030822.sql") ;
1217 @reqlist = @{ &parse_sql_file ("$sqldir/20030822.sql") } ;
1218 foreach my $s (@reqlist) {
1221 $sth = $dbh->prepare ($query) ;
1227 &update_db_version ($target) ;
1228 &debug ("Committing.") ;
1232 $version = &get_db_version ;
1233 $target = "3.1-0+1" ;
1234 if (&is_lesser ($version, $target)) {
1235 &debug ("Upgrading with 20031105.sql") ;
1237 @reqlist = @{ &parse_sql_file ("$sqldir/20031105.sql") } ;
1238 foreach my $s (@reqlist) {
1241 $sth = $dbh->prepare ($query) ;
1246 &debug ("Upgrading with 20031124.sql") ;
1248 @reqlist = @{ &parse_sql_file ("$sqldir/20031124.sql") } ;
1249 foreach my $s (@reqlist) {
1252 $sth = $dbh->prepare ($query) ;
1258 &update_db_version ($target) ;
1259 &debug ("Committing.") ;
1263 $version = &get_db_version ;
1264 $target = "3.1-0+2" ;
1265 if (&is_lesser ($version, $target)) {
1266 &debug ("Upgrading with 20031129.sql") ;
1268 @reqlist = @{ &parse_sql_file ("$sqldir/20031129.sql") } ;
1269 foreach my $s (@reqlist) {
1272 $sth = $dbh->prepare ($query) ;
1278 &update_db_version ($target) ;
1279 &debug ("Committing.") ;
1283 $version = &get_db_version ;
1284 $target = "3.1-0+3" ;
1285 if (&is_lesser ($version, $target)) {
1286 # Yes, I know. 20031126 < 20031129, yet we apply that change later.
1287 # Blame tperdue for late committing.
1288 # They are independent anyway.
1289 &debug ("Upgrading with 20031126.sql") ;
1291 @reqlist = @{ &parse_sql_file ("$sqldir/20031126.sql") } ;
1292 foreach my $s (@reqlist) {
1295 $sth = $dbh->prepare ($query) ;
1301 &update_db_version ($target) ;
1302 &debug ("Committing.") ;
1306 $version = &get_db_version ;
1307 $target = "3.2.1-0+2" ;
1308 if (&is_lesser ($version, $target)) {
1309 &debug ("Upgrading with 20031205.sql") ;
1311 @reqlist = @{ &parse_sql_file ("$sqldir/20031205.sql") } ;
1312 foreach my $s (@reqlist) {
1315 $sth = $dbh->prepare ($query) ;
1321 &update_db_version ($target) ;
1322 &debug ("Committing.") ;
1326 $version = &get_db_version ;
1327 $target = "3.2.1-0+3" ;
1328 if (&is_lesser ($version, $target)) {
1329 &debug ("Upgrading with 20040130.sql") ;
1331 @reqlist = @{ &parse_sql_file ("$sqldir/20040130.sql") } ;
1332 foreach my $s (@reqlist) {
1335 $sth = $dbh->prepare ($query) ;
1341 &update_db_version ($target) ;
1342 &debug ("Committing.") ;
1346 $version = &get_db_version ;
1347 $target = "3.2.1-0+4" ;
1348 if (&is_lesser ($version, $target)) {
1349 &debug ("Upgrading with 20040204.sql") ;
1351 @reqlist = @{ &parse_sql_file ("$sqldir/20040204.sql") } ;
1352 foreach my $s (@reqlist) {
1355 $sth = $dbh->prepare ($query) ;
1361 &update_db_version ($target) ;
1362 &debug ("Committing.") ;
1366 $version = &get_db_version ;
1367 $target = "3.2.1-0+5" ;
1368 if (&is_lesser ($version, $target)) {
1369 &debug ("Upgrading with 20040315.sql") ;
1371 @reqlist = @{ &parse_sql_file ("$sqldir/20040315.sql") } ;
1372 foreach my $s (@reqlist) {
1375 $sth = $dbh->prepare ($query) ;
1381 &update_db_version ($target) ;
1382 &debug ("Committing.") ;
1386 $version = &get_db_version ;
1387 $target = "3.3.0-0+0" ;
1388 if (&is_lesser ($version, $target)) {
1389 &debug ("Upgrading with 200403251.sql") ;
1391 @reqlist = @{ &parse_sql_file ("$sqldir/200403251.sql") } ;
1392 foreach my $s (@reqlist) {
1395 $sth = $dbh->prepare ($query) ;
1401 &update_db_version ($target) ;
1402 &debug ("Committing.") ;
1406 $version = &get_db_version ;
1407 $target = "3.3.0-0+1" ;
1408 if (&is_lesser ($version, $target)) {
1409 &debug ("Upgrading with 200403252.sql") ;
1411 @reqlist = @{ &parse_sql_file ("$sqldir/200403252.sql") } ;
1412 foreach my $s (@reqlist) {
1415 $sth = $dbh->prepare ($query) ;
1421 &update_db_version ($target) ;
1422 &debug ("Committing.") ;
1426 $version = &get_db_version ;
1427 $target = "3.3.0-0+3" ;
1428 if (&is_lesser ($version, $target)) {
1429 &debug ("Upgrading with 20040507.sql") ;
1431 @reqlist = @{ &parse_sql_file ("$sqldir/20040507.sql") } ;
1432 foreach my $s (@reqlist) {
1435 $sth = $dbh->prepare ($query) ;
1441 &update_db_version ($target) ;
1442 &debug ("Committing.") ;
1446 $version = &get_db_version ;
1447 $target = "3.3.0-0+4" ;
1448 if (&is_lesser ($version, $target)) {
1449 &debug ("Upgrading with 20040722.sql") ;
1451 @reqlist = @{ &parse_sql_file ("$sqldir/20040722.sql") } ;
1452 foreach my $s (@reqlist) {
1455 $sth = $dbh->prepare ($query) ;
1461 &update_db_version ($target) ;
1462 &debug ("Committing.") ;
1466 $version = &get_db_version ;
1467 $target = "3.3.0-0+6" ;
1468 if (&is_lesser ($version, $target)) {
1469 &debug ("Upgrading with 20040804.sql") ;
1471 @reqlist = @{ &parse_sql_file ("$sqldir/20040804.sql") } ;
1472 foreach my $s (@reqlist) {
1475 $sth = $dbh->prepare ($query) ;
1481 &update_db_version ($target) ;
1482 &debug ("Committing.") ;
1486 $version = &get_db_version ;
1487 $target = "3.3.0-0+7" ;
1488 if (&is_lesser ($version, $target)) {
1489 &debug ("Upgrading with 20040826.sql") ;
1491 @reqlist = @{ &parse_sql_file ("$sqldir/20040826.sql") } ;
1492 foreach my $s (@reqlist) {
1495 $sth = $dbh->prepare ($query) ;
1501 &update_db_version ($target) ;
1502 &debug ("Committing.") ;
1506 $version = &get_db_version ;
1507 $target = "3.3.0-2+1" ;
1508 if (&is_lesser ($version, $target)) {
1509 &debug ("Migrating forum names") ;
1511 $query = "SELECT group_forum_id,forum_name FROM forum_group_list" ;
1513 $sth = $dbh->prepare ($query) ;
1515 while (@array = $sth->fetchrow_array) {
1516 my $forumid = $array[0] ;
1517 my $oldname = $array[1] ;
1519 my $newname = lc $oldname ;
1520 $newname =~ s/[^_.0-9a-z-]/-/g ;
1522 my $query2 = "UPDATE forum_group_list SET forum_name='$newname' WHERE group_forum_id=$forumid" ;
1523 # &debug ($query2) ;
1524 my $sth2 =$dbh->prepare ($query2) ;
1530 &update_db_version ($target) ;
1531 &debug ("Committing.") ;
1535 $version = &get_db_version ;
1536 $target = "3.3.0-2+2" ;
1537 if (&is_lesser ($version, $target)) {
1538 &debug ("Migrating permissions to RBAC") ;
1540 my $defaultroles = {
1541 'Admin' => { 'projectadmin'=>'A', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'forum'=>'2', 'trackeradmin'=>'2', 'tracker'=>'2', 'pmadmin'=>'2', 'pm'=>'2' },
1542 'Senior Developer' => { 'projectadmin'=>'0', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'forum'=>'2', 'trackeradmin'=>'2', 'tracker'=>'2', 'pmadmin'=>'2', 'pm'=>'2' },
1543 'Junior Developer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'1', 'docman'=>'0', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'1', 'pmadmin'=>'0', 'pm'=>'1' },
1544 'Doc Writer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'0', 'pmadmin'=>'0', 'pm'=>'0' },
1545 'Support Tech' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'2', 'pmadmin'=>'0', 'pm'=>'0' }
1548 $query = "SELECT group_id FROM groups where status != 'P'" ;
1550 $sth = $dbh->prepare ($query) ;
1552 while (@array = $sth->fetchrow_array) {
1553 my $group_id = $array[0] ;
1555 my ($query2, $sth2, @array2, $admin_rid, $jd_rid, %roledata) ;
1556 foreach my $rname (keys %$defaultroles) {
1557 $query2 = "SELECT nextval('role_role_id_seq'::text)" ;
1558 # &debug ($query2) ;
1559 $sth2 =$dbh->prepare ($query2) ;
1561 @array2 = $sth2->fetchrow_array ;
1562 my $rid = $array2[0] ;
1564 if ($rname eq 'Admin') {
1566 } elsif ($rname eq 'Junior Developer') {
1570 $query2 = "INSERT INTO role (role_id, group_id, role_name)
1571 VALUES ($rid, $group_id, '$rname')" ;
1572 # &debug ($query2) ;
1573 $sth2 =$dbh->prepare ($query2) ;
1577 foreach my $section (keys %{$defaultroles->{$rname}}) {
1578 if ($section eq 'forum') {
1579 $query2 = "SELECT group_forum_id
1580 FROM forum_group_list
1581 WHERE group_id = $group_id" ;
1582 # &debug ($query2) ;
1583 $sth2 =$dbh->prepare ($query2) ;
1585 while (@array2 = $sth2->fetchrow_array) {
1586 $roledata{'forum'}{$array2[0]} = $defaultroles->{$rname}{'forum'} ;
1589 } elsif ($section eq 'pm') {
1590 $query2 = "SELECT group_project_id
1591 FROM project_group_list
1592 WHERE group_id = $group_id" ;
1593 # &debug ($query2) ;
1594 $sth2 =$dbh->prepare ($query2) ;
1596 while (@array2 = $sth2->fetchrow_array) {
1597 $roledata{'pm'}{$array2[0]} = $defaultroles->{$rname}{'pm'} ;
1600 } elsif ($section eq 'tracker') {
1601 $query2 = "SELECT group_artifact_id
1602 FROM artifact_group_list
1603 WHERE group_id = $group_id" ;
1604 # &debug ($query2) ;
1605 $sth2 =$dbh->prepare ($query2) ;
1607 while (@array2 = $sth2->fetchrow_array) {
1608 $roledata{'tracker'}{$array2[0]} = $defaultroles->{$rname}{'tracker'} ;
1612 $roledata{$section}{0} = $defaultroles->{$rname}{$section} ;
1615 foreach my $rd_it (keys %{$roledata{$section}}) {
1616 $query2 = "INSERT INTO role_setting (role_id, section_name, ref_id, value)
1617 VALUES ($rid, '$section', $rd_it, '$roledata{$section}{$rd_it}')" ;
1618 # &debug ($query2) ;
1619 $sth2 =$dbh->prepare ($query2) ;
1628 # affecter le rôle Admin aux admins, JD aux autres
1629 $query2 = "SELECT user_id, admin_flags FROM user_group WHERE group_id = $group_id" ;
1630 # &debug ($query2) ;
1631 $sth2 =$dbh->prepare ($query2) ;
1633 while (@array2 = $sth2->fetchrow_array) {
1634 my $uid = $array2[0] ;
1635 my $adminflags = $array2[1] ;
1638 $adminflags =~ s/\s//g ;
1639 if ($adminflags eq 'A') {
1644 $rname = 'Junior Developer' ;
1649 admin_flags = '$defaultroles->{$rname}{'projectadmin'}',
1650 forum_flags = '$defaultroles->{$rname}{'forumadmin'}',
1651 project_flags = '$defaultroles->{$rname}{'pmadmin'}',
1652 doc_flags = '$defaultroles->{$rname}{'docman'}',
1653 cvs_flags = '$defaultroles->{$rname}{'scm'}',
1654 release_flags = '$defaultroles->{$rname}{'frs'}',
1655 artifact_flags = '$defaultroles->{$rname}{'trackeradmin'}'
1656 WHERE user_id = $uid AND group_id = $group_id" ,
1658 SET perm_level=$defaultroles->{$rname}{'forum'}
1659 WHERE group_forum_id IN (
1660 SELECT group_forum_id
1661 FROM forum_group_list
1662 WHERE group_id=$group_id)
1664 "UPDATE project_perm
1665 SET perm_level=$defaultroles->{$rname}{'pm'}
1666 WHERE group_project_id IN (
1667 SELECT group_project_id
1668 FROM project_group_list
1669 WHERE group_id=$group_id)
1671 "UPDATE artifact_perm
1672 SET perm_level=$defaultroles->{$rname}{'tracker'}
1673 WHERE group_artifact_id IN (
1674 SELECT group_artifact_id
1675 FROM artifact_group_list
1676 WHERE group_id=$group_id)
1679 foreach my $query3 (@reqlist3) {
1680 # &debug ($query3) ;
1681 my $sth3 = $dbh->prepare ($query3) ;
1690 &update_db_version ($target) ;
1691 &debug ("Committing.") ;
1695 $version = &get_db_version ;
1696 $target = "3.3.0-2+4" ;
1697 if (&is_lesser ($version, $target)) {
1698 &debug ("Upgrading with 20040914.sql") ;
1700 @reqlist = @{ &parse_sql_file ("$sqldir/20040914.sql") } ;
1701 foreach my $s (@reqlist) {
1704 $sth = $dbh->prepare ($query) ;
1710 &update_db_version ($target) ;
1711 &debug ("Committing.") ;
1715 $version = &get_db_version ;
1716 $target = "3.3.0-2+4+1" ;
1717 if (&is_lesser ($version, $target)) {
1718 &debug ("Upgrading with 20041001.sql") ;
1720 @reqlist = @{ &parse_sql_file ("$sqldir/20041001.sql") } ;
1721 foreach my $s (@reqlist) {
1724 $sth = $dbh->prepare ($query) ;
1730 &update_db_version ($target) ;
1731 &debug ("Committing.") ;
1735 $version = &get_db_version ;
1736 $target = "3.3.0-2+5" ;
1737 if (&is_lesser ($version, $target)) {
1738 &debug ("Upgrading with 20041005.sql") ;
1740 @reqlist = @{ &parse_sql_file ("$sqldir/20041005.sql") } ;
1741 foreach my $s (@reqlist) {
1744 $sth = $dbh->prepare ($query) ;
1750 &update_db_version ($target) ;
1751 &debug ("Committing.") ;
1755 $version = &get_db_version ;
1756 $target = "3.3.0-2+6" ;
1757 if (&is_lesser ($version, $target)) {
1758 &debug ("Upgrading with 20041006.sql") ;
1760 @reqlist = @{ &parse_sql_file ("$sqldir/20041006.sql") } ;
1761 foreach my $s (@reqlist) {
1764 $sth = $dbh->prepare ($query) ;
1770 &update_db_version ($target) ;
1771 &debug ("Committing.") ;
1775 $version = &get_db_version ;
1776 $target = "3.3.0-3" ;
1777 if (&is_lesser ($version, $target)) {
1778 &debug ("Upgrading with 20041014.sql") ;
1780 @reqlist = @{ &parse_sql_file ("$sqldir/20041014.sql") } ;
1781 foreach my $s (@reqlist) {
1784 $sth = $dbh->prepare ($query) ;
1790 &update_db_version ($target) ;
1791 &debug ("Committing.") ;
1795 $version = &get_db_version ;
1796 $target = "3.3.0-4" ;
1797 if (&is_lesser ($version, $target)) {
1798 &debug ("Upgrading with 20041020.sql") ;
1800 @reqlist = @{ &parse_sql_file ("$sqldir/20041020.sql") } ;
1801 foreach my $s (@reqlist) {
1804 $sth = $dbh->prepare ($query) ;
1810 &update_db_version ($target) ;
1811 &debug ("Committing.") ;
1815 $version = &get_db_version ;
1816 $target = "4.0.0-0" ;
1817 # This is an exception, I reapply a modified version of 20040729.sql since it was doing nothing
1818 # the other call was deleted from this file
1819 if (&is_lesser ($version, $target)) {
1820 &debug ("Upgrading with 20040729.sql") ;
1822 @reqlist = @{ &parse_sql_file ("$sqldir/20040729.sql") } ;
1823 foreach my $s (@reqlist) {
1826 $sth = $dbh->prepare ($query) ;
1832 &update_db_version ($target) ;
1833 &debug ("Committing.") ;
1837 $version = &get_db_version ;
1838 $target = "4.0.0-0+1" ;
1839 if (&is_lesser ($version, $target)) {
1840 &debug ("Granting read access permissions to NSS") ;
1842 @reqlist = ( "GRANT SELECT ON nss_passwd TO gforge_nss",
1843 "GRANT SELECT ON nss_groups TO gforge_nss",
1844 "GRANT SELECT ON nss_usergroups TO gforge_nss",
1846 foreach my $s (@reqlist) {
1849 $sth = $dbh->prepare ($query) ;
1855 &update_db_version ($target) ;
1856 &debug ("Committing.") ;
1860 $version = &get_db_version ;
1861 $target = "4.0.0-0+2" ;
1862 if (&is_lesser ($version, $target)) {
1863 &debug ("Upgrading with 20041031.sql") ;
1865 @reqlist = @{ &parse_sql_file ("$sqldir/20041031.sql") } ;
1866 foreach my $s (@reqlist) {
1869 $sth = $dbh->prepare ($query) ;
1875 &debug ("Granting read access permissions to NSS") ;
1877 @reqlist = ( "GRANT SELECT ON mta_users TO gforge_mta",
1878 "GRANT SELECT ON mta_lists TO gforge_mta",
1880 foreach my $s (@reqlist) {
1883 $sth = $dbh->prepare ($query) ;
1889 &update_db_version ($target) ;
1890 &debug ("Committing.") ;
1894 $version = &get_db_version ;
1895 $target = "4.0.0-0+3" ;
1896 # This is an exception, I reapply a modified version of 20040729.sql since it was doing nothing
1897 # the other call was deleted from this file
1898 if (&is_lesser ($version, $target)) {
1899 &debug ("Upgrading with 20041104.sql") ;
1901 @reqlist = @{ &parse_sql_file ("$sqldir/20041104.sql") } ;
1902 foreach my $s (@reqlist) {
1905 $sth = $dbh->prepare ($query) ;
1911 &update_db_version ($target) ;
1912 &debug ("Committing.") ;
1916 $version = &get_db_version ;
1917 $target = "4.0.0-0+4" ;
1918 if (&is_lesser ($version, $target)) {
1919 &debug ("Upgrading with 20041108.sql") ;
1921 @reqlist = @{ &parse_sql_file ("$sqldir/20041108.sql") } ;
1922 foreach my $s (@reqlist) {
1925 $sth = $dbh->prepare ($query) ;
1931 &update_db_version ($target) ;
1932 &debug ("Committing.") ;
1936 $version = &get_db_version ;
1937 $target = "4.0.2-0+0" ;
1938 if (&is_lesser ($version, $target)) {
1939 &debug ("Upgrading with 20041124.sql") ;
1941 @reqlist = @{ &parse_sql_file ("$sqldir/20041124.sql") } ;
1942 foreach my $s (@reqlist) {
1945 $sth = $dbh->prepare ($query) ;
1951 &update_db_version ($target) ;
1952 &debug ("Committing.") ;
1956 $version = &get_db_version ;
1957 $target = "4.0.2-0+1" ;
1958 if (&is_lesser ($version, $target)) {
1959 &debug ("Creating automatic commit notification mailing-lists") ;
1962 $query = "SELECT group_id, unix_group_name FROM groups WHERE status='A' ORDER BY group_id" ;
1964 $sth = $dbh->prepare ($query) ;
1966 while (@array = $sth->fetchrow_array) {
1967 my $group_id = $array[0] ;
1968 my $group_name = $array[1] ;
1970 my $query2 = "SELECT count(*) FROM mail_group_list
1971 WHERE group_id = $group_id
1972 AND list_name = '".$group_name."-commits'" ;
1973 # &debug ($query2) ;
1974 my $sth2 =$dbh->prepare ($query2) ;
1976 my @array2 = $sth2->fetchrow_array ;
1978 if ($array2[0] == 0) {
1979 my $listname = $group_name."-commits" ;
1980 my $listpw = substr (Digest::MD5::md5_base64 ($listname . rand(1)), 0, 16) ;
1983 $query2 = "SELECT user_id FROM user_group
1984 WHERE admin_flags = 'A'
1985 AND group_id = $group_id" ;
1986 # &debug ($query2) ;
1987 $sth2 =$dbh->prepare ($query2) ;
1989 my $group_admin = -1 ;
1990 if (@array2 = $sth2->fetchrow_array) {
1991 $group_admin = $array2[0] ;
1995 $query2 = "INSERT INTO mail_group_list (group_id, list_name, is_public, password, list_admin, status, description)
1996 VALUES ($group_id, '$listname', 1, '$listpw', $group_admin, 1, 'commits')" ;
1997 # &debug ($query2) ;
1998 $sth2 =$dbh->prepare ($query2) ;
2005 &update_db_version ($target) ;
2006 &debug ("Committing.") ;
2010 # $version = &get_db_version ;
2011 # $target = "4.0.2-0+2" ;
2012 # if (&is_lesser ($version, $target)) {
2013 # &debug ("Upgrading with 20041222-debian.sql") ;
2015 # @reqlist = @{ &parse_sql_file ("$sqldir/20041222-debian.sql") } ;
2016 # foreach my $s (@reqlist) {
2019 # $sth = $dbh->prepare ($query) ;
2020 # $sth->execute () ;
2025 # &update_db_version ($target) ;
2026 # &debug ("Committing.") ;
2030 $version = &get_db_version ;
2031 $target = "4.0.2-0+3" ;
2032 if (&is_lesser ($version, $target)) {
2033 &debug ("Upgrading with 20050115.sql") ;
2035 @reqlist = @{ &parse_sql_file ("$sqldir/20050115.sql") } ;
2036 foreach my $s (@reqlist) {
2039 $sth = $dbh->prepare ($query) ;
2045 &update_db_version ($target) ;
2046 &debug ("Committing.") ;
2051 # We got this at upgrade
2053 #DBD::Pg::st execute failed: ERREUR: la relation avec l'OID 387345 n'existe pas at /usr/share/gforge/bin/db-upgrade.pl line 1970.
2054 #Transaction aborted because DBD::Pg::st execute failed: ERREUR: la relation avec l'OID 387345 n'existe pas at /usr/share/gforge/bin/db-upgrade.pl line 1970.
2055 #Transaction aborted because DBD::Pg::st execute failed: ERREUR: la relation avec l'OID 387345 n'existe pas at /usr/share/gforge/bin/db-upgrade.pl line 1970.
2056 #Last SQL query was:
2057 #update project_task SET last_modified_date=EXTRACT(EPOCH FROM now())::integer;
2059 #Your database schema is at version 4.0.2-0+5
2061 # This is a hack to disconnect and reconnect the DB and solve the problem
2067 $dbh->{AutoCommit} = 0;
2068 $dbh->{RaiseError} = 1;
2070 $version = &get_db_version ;
2071 $target = "4.0.2-0+5" ;
2072 if (&is_lesser ($version, $target)) {
2073 &debug ("Upgrading with 20050130.sql") ;
2075 @reqlist = @{ &parse_sql_file ("$sqldir/20050130.sql") } ;
2076 foreach my $s (@reqlist) {
2079 $sth = $dbh->prepare ($query) ;
2085 &update_db_version ($target) ;
2086 &debug ("Committing.") ;
2090 $version = &get_db_version ;
2091 $target = "4.0.2-0+6" ;
2092 if (&is_lesser ($version, $target)) {
2093 &debug ("Upgrading with 20050212.sql") ;
2095 @reqlist = @{ &parse_sql_file ("$sqldir/20050212.sql") } ;
2096 foreach my $s (@reqlist) {
2099 $sth = $dbh->prepare ($query) ;
2105 &update_db_version ($target) ;
2106 &debug ("Committing.") ;
2110 $version = &get_db_version ;
2111 $target = "4.0.2-0+7" ;
2112 if (&is_lesser ($version, $target)) {
2113 &debug ("Upgrading with 20050214-nss.sql valantine") ;
2115 @reqlist = @{ &parse_sql_file ("$sqldir/20050214-nss.sql") } ;
2116 foreach my $s (@reqlist) {
2119 $sth = $dbh->prepare ($query) ;
2125 &update_db_version ($target) ;
2126 &debug ("Committing.") ;
2130 $version = &get_db_version ;
2132 if (&is_lesser ($version, $target)) {
2133 &debug ("Upgrading with 20050224-2.sql") ;
2135 @reqlist = @{ &parse_sql_file ("$sqldir/20050224-2.sql") } ;
2136 foreach my $s (@reqlist) {
2139 $sth = $dbh->prepare ($query) ;
2145 &update_db_version ($target) ;
2146 &debug ("Committing.") ;
2150 $version = &get_db_version ;
2152 if (&is_lesser ($version, $target)) {
2153 &debug ("Upgrading with 20050225-nsssetup.sql") ;
2155 @reqlist = @{ &parse_sql_file ("$sqldir/20050225-nsssetup.sql") } ;
2156 foreach my $s (@reqlist) {
2159 $sth = $dbh->prepare ($query) ;
2165 &update_db_version ($target) ;
2166 &debug ("Committing.") ;
2170 $version = &get_db_version ;
2172 if (&is_lesser ($version, $target)) {
2173 &debug ("Upgrading with 20050311.sql") ;
2175 @reqlist = @{ &parse_sql_file ("$sqldir/20050311.sql") } ;
2176 foreach my $s (@reqlist) {
2179 $sth = $dbh->prepare ($query) ;
2185 &update_db_version ($target) ;
2186 &debug ("Committing.") ;
2190 $version = &get_db_version ;
2192 if (&is_lesser ($version, $target)) {
2193 &debug ("Upgrading with 20050315.sql") ;
2195 @reqlist = @{ &parse_sql_file ("$sqldir/20050315.sql") } ;
2196 foreach my $s (@reqlist) {
2199 $sth = $dbh->prepare ($query) ;
2205 &update_db_version ($target) ;
2206 &debug ("Committing.") ;
2210 $version = &get_db_version ;
2212 if (&is_lesser ($version, $target)) {
2213 &debug ("Upgrading with 20050325-2.sql") ;
2215 @reqlist = @{ &parse_sql_file ("$sqldir/20050325-2.sql") } ;
2216 foreach my $s (@reqlist) {
2219 $sth = $dbh->prepare ($query) ;
2225 &update_db_version ($target) ;
2226 &debug ("Committing.") ;
2230 $version = &get_db_version ;
2232 if (&is_lesser ($version, $target)) {
2233 &debug ("Converting trackers to use their extra fields") ;
2235 $query = "SELECT group_id,group_artifact_id,use_resolution FROM artifact_group_list" ;
2237 $sth = $dbh->prepare ($query) ;
2239 while (@array = $sth->fetchrow_array) {
2240 my $group_id = $array[0] ;
2241 my $gaid = $array[1] ;
2242 my $ur = $array[2] ;
2244 # Ajout du champ Category
2245 my $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
2246 # &debug ($query2) ;
2247 my $sth2 = $dbh->prepare ($query2) ;
2249 my @array2 = $sth2->fetchrow_array ;
2251 my $aefid = $array2[0] ;
2253 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
2254 VALUES ($aefid, $gaid, 'Category', 1)" ;
2255 # &debug ($query2) ;
2256 $sth2 =$dbh->prepare ($query2) ;
2259 $query2 = "SELECT id, category_name FROM artifact_category WHERE group_artifact_id=$gaid" ;
2260 # &debug ($query2) ;
2261 $sth2 = $dbh->prepare ($query2) ;
2264 while (@array2 = $sth2->fetchrow_array) {
2265 my $cat_id = $array2[0] ;
2266 my $catname = $array2[1] ;
2268 if ($catname eq '') { $catname = '[empty]' ; }
2270 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
2271 # &debug ($query3) ;
2272 my $sth3 = $dbh->prepare ($query3) ;
2274 my @array3 = $sth3->fetchrow_array ;
2276 my $efeid = $array3[0] ;
2278 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
2279 VALUES ($efeid, $aefid, ?, 0)" ;
2280 # &debug ($query3) ;
2281 $sth3 =$dbh->prepare ($query3) ;
2282 $sth3->execute ($catname) ;
2285 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
2286 SELECT artifact_id,$efeid,$aefid FROM artifact
2287 WHERE category_id=$cat_id" ;
2288 # &debug ($query3) ;
2289 $sth3 =$dbh->prepare ($query3) ;
2293 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Category'
2294 WHERE old_value='$cat_id' AND field_name='category_id'" ;
2295 # &debug ($query3) ;
2296 $sth3 =$dbh->prepare ($query3) ;
2297 $sth3->execute ($catname) ;
2302 # Ajout du champ Group
2303 $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
2304 # &debug ($query2) ;
2305 $sth2 = $dbh->prepare ($query2) ;
2307 @array2 = $sth2->fetchrow_array ;
2309 $aefid = $array2[0] ;
2311 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
2312 VALUES ($aefid, $gaid, 'Group', 1)" ;
2313 # &debug ($query2) ;
2314 $sth2 =$dbh->prepare ($query2) ;
2317 $query2 = "SELECT id, group_name FROM artifact_group WHERE group_artifact_id=$gaid" ;
2318 # &debug ($query2) ;
2319 $sth2 = $dbh->prepare ($query2) ;
2322 while (@array2 = $sth2->fetchrow_array) {
2323 my $grp_id = $array2[0] ;
2324 my $grpname = $array2[1] ;
2326 if ($grpname eq '') { $grpname = '[empty]' ; }
2328 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
2329 # &debug ($query3) ;
2330 my $sth3 = $dbh->prepare ($query3) ;
2332 my @array3 = $sth3->fetchrow_array ;
2334 my $efeid = $array3[0] ;
2336 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
2337 VALUES ($efeid, $aefid, ?, 0)" ;
2338 # &debug ($query3) ;
2339 $sth3 =$dbh->prepare ($query3) ;
2340 $sth3->execute ($grpname) ;
2343 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
2344 SELECT artifact_id,$efeid,$aefid FROM artifact
2345 WHERE artifact_group_id=$grp_id" ;
2346 # &debug ($query3) ;
2347 $sth3 =$dbh->prepare ($query3) ;
2351 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Group'
2352 WHERE old_value='$grp_id' AND field_name='artifact_group_id'" ;
2353 # &debug ($query3) ;
2354 $sth3 =$dbh->prepare ($query3) ;
2355 $sth3->execute ($grpname) ;
2360 # Ajout du champ Resolution (s'il existe, cf. $ur)
2362 $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
2363 # &debug ($query2) ;
2364 $sth2 = $dbh->prepare ($query2) ;
2366 @array2 = $sth2->fetchrow_array ;
2368 $aefid = $array2[0] ;
2370 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
2371 VALUES ($aefid, $gaid, 'Resolution', 1)" ;
2372 # &debug ($query2) ;
2373 $sth2 =$dbh->prepare ($query2) ;
2376 $query2 = "SELECT id, resolution_name FROM artifact_resolution" ;
2377 # &debug ($query2) ;
2378 $sth2 = $dbh->prepare ($query2) ;
2381 while (@array2 = $sth2->fetchrow_array) {
2382 my $res_id = $array2[0] ;
2383 my $resname = $array2[1] ;
2385 if ($resname eq '') { $resname = '[empty]' ; }
2387 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
2388 # &debug ($query3) ;
2389 my $sth3 = $dbh->prepare ($query3) ;
2391 my @array3 = $sth3->fetchrow_array ;
2393 my $efeid = $array3[0] ;
2395 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
2396 VALUES ($efeid, $aefid, ?, 0)" ;
2397 # &debug ($query3) ;
2398 $sth3 =$dbh->prepare ($query3) ;
2399 $sth3->execute ($resname) ;
2402 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
2403 SELECT artifact_id,$efeid,$aefid FROM artifact
2404 WHERE resolution_id=$res_id and group_artifact_id=$gaid" ;
2405 # &debug ($query3) ;
2406 $sth3 =$dbh->prepare ($query3) ;
2410 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Resolution'
2411 WHERE old_value='$res_id' AND field_name='resolution_id'" ;
2412 # &debug ($query3) ;
2413 $sth3 =$dbh->prepare ($query3) ;
2414 $sth3->execute ($resname) ;
2421 &update_db_version ($target) ;
2422 &debug ("Committing.") ;
2426 $version = &get_db_version ;
2428 if (&is_lesser ($version, $target)) {
2429 &debug ("Upgrading with 20050325-5.sql") ;
2431 @reqlist = @{ &parse_sql_file ("$sqldir/20050325-5.sql") } ;
2432 foreach my $s (@reqlist) {
2435 $sth = $dbh->prepare ($query) ;
2441 &update_db_version ($target) ;
2442 &debug ("Committing.") ;
2446 $version = &get_db_version ;
2448 if (&is_lesser ($version, $target)) {
2449 &debug ("Upgrading with 20050605.sql") ;
2451 @reqlist = @{ &parse_sql_file ("$sqldir/20050605.sql") } ;
2452 foreach my $s (@reqlist) {
2455 $sth = $dbh->prepare ($query) ;
2461 &update_db_version ($target) ;
2462 &debug ("Committing.") ;
2466 $version = &get_db_version ;
2468 if (&is_lesser ($version, $target)) {
2469 &debug ("Creating aliases for the extra fields") ;
2471 $query = "ALTER TABLE artifact_extra_field_list ADD COLUMN alias TEXT" ;
2473 $sth = $dbh->prepare ($query) ;
2477 my %reserved_alias = (
2486 $query = "SELECT field_name, alias, group_artifact_id, extra_field_id FROM artifact_extra_field_list" ;
2488 $sth = $dbh->prepare ($query) ;
2490 while (@array = $sth->fetchrow_array) {
2491 my $name = $array[0] ;
2492 my $alias = $array[1] ;
2493 my $gaid = $array[2] ;
2494 my $efid = $array[3] ;
2497 my $newalias = lc $name ;
2498 $newalias =~ s/\s/_/g ;
2499 $newalias =~ s/[^_a-z]//g ;
2501 if ($newalias ne "") {
2502 if ($reserved_alias{$newalias}) {
2503 $newalias = "extra_" . $newalias ;
2510 $candidate = $newalias ;
2511 $candidate .= $count if ($count > 0) ;
2512 my $query2 = "SELECT count(*) FROM artifact_extra_field_list WHERE group_artifact_id=$gaid AND LOWER(alias)='$candidate' AND extra_field_id <> $efid" ;
2513 # &debug ($query2) ;
2514 my $sth2 =$dbh->prepare ($query2) ;
2516 my @array2 = $sth2->fetchrow_array ;
2517 if ($array2[0] == 0) {
2524 } until ($conflict == 0) ;
2526 my $query2 = "UPDATE artifact_extra_field_list SET alias='$candidate' WHERE extra_field_id=$efid" ;
2527 # &debug ($query2) ;
2528 my $sth2 =$dbh->prepare ($query2) ;
2537 &update_db_version ($target) ;
2538 &debug ("Committing.") ;
2542 $version = &get_db_version ;
2544 if (&is_lesser ($version, $target)) {
2545 &debug ("Upgrading with 20050628.sql") ;
2547 @reqlist = @{ &parse_sql_file ("$sqldir/20050628.sql") } ;
2548 foreach my $s (@reqlist) {
2551 $sth = $dbh->prepare ($query) ;
2557 &update_db_version ($target) ;
2558 &debug ("Committing.") ;
2562 $version = &get_db_version ;
2564 if (&is_lesser ($version, $target)) {
2565 &debug ("Upgrading with 20050711.sql") ;
2567 @reqlist = @{ &parse_sql_file ("$sqldir/20050711.sql") } ;
2568 foreach my $s (@reqlist) {
2571 $sth = $dbh->prepare ($query) ;
2577 &update_db_version ($target) ;
2578 &debug ("Committing.") ;
2582 &update_with_sql("20050906","4.5-2");
2583 # 20051027-1 was renamed 20050804-1
2584 #&update_with_sql("20051027-1","4.5-3");
2585 &update_with_sql("20050804-1","4.5-3");
2587 $version = &get_db_version ;
2589 if (&is_lesser ($version, $target)) {
2590 &debug ("Updating document sizes") ;
2592 $query = "SELECT docid, data FROM doc_data" ;
2594 $sth = $dbh->prepare ($query) ;
2596 while (@array = $sth->fetchrow_array) {
2597 my $docid = $array[0] ;
2598 my $b64data = $array[1] ;
2599 my $data = decode_base64 ($b64data) ;
2600 my $size = length ($data) ;
2602 my $query2 = "UPDATE doc_data SET filesize=$size WHERE docid=$docid" ;
2603 # &debug ($query2) ;
2604 my $sth2 =$dbh->prepare ($query2) ;
2610 &update_db_version ($target) ;
2611 &debug ("Committing.") ;
2615 $version = &get_db_version ;
2616 $target = "4.5.14-3" ;
2617 if (&is_lesser ($version, $target)) {
2618 &debug ("Setting up time tracking") ;
2620 if (&table_exists ($dbh, "rep_time_category")) {
2621 &debug ("...already set up.") ;
2623 &drop_table_if_exists ($dbh, "rep_time_category") ;
2624 &drop_sequence_if_exists ($dbh, "rep_time_category_time_code_seq") ;
2625 &drop_table_if_exists ($dbh, "rep_time_tracking") ;
2626 &drop_table_if_exists ($dbh, "rep_users_added_daily") ;
2627 &drop_table_if_exists ($dbh, "rep_users_added_weekly") ;
2628 &drop_table_if_exists ($dbh, "rep_users_added_monthly") ;
2629 &drop_table_if_exists ($dbh, "rep_users_cum_daily") ;
2630 &drop_table_if_exists ($dbh, "rep_users_cum_weekly") ;
2631 &drop_table_if_exists ($dbh, "rep_users_cum_monthly") ;
2632 &drop_table_if_exists ($dbh, "rep_groups_added_daily") ;
2633 &drop_table_if_exists ($dbh, "rep_groups_added_weekly") ;
2634 &drop_table_if_exists ($dbh, "rep_groups_added_monthly") ;
2635 &drop_table_if_exists ($dbh, "rep_groups_cum_daily") ;
2636 &drop_table_if_exists ($dbh, "rep_groups_cum_weekly") ;
2637 &drop_table_if_exists ($dbh, "rep_groups_cum_monthly") ;
2638 &drop_view_if_exists ($dbh, "rep_group_act_oa_vw") ;
2639 &drop_view_if_exists ($dbh, "rep_user_act_oa_vw") ;
2640 &drop_view_if_exists ($dbh, "rep_site_act_daily_vw") ;
2641 &drop_view_if_exists ($dbh, "rep_site_act_weekly_vw") ;
2642 &drop_view_if_exists ($dbh, "rep_site_act_monthly_vw") ;
2643 &drop_table_if_exists ($dbh, "rep_user_act_daily") ;
2644 &drop_table_if_exists ($dbh, "rep_user_act_weekly") ;
2645 &drop_table_if_exists ($dbh, "rep_user_act_monthly") ;
2646 &drop_table_if_exists ($dbh, "rep_group_act_daily") ;
2647 &drop_index_if_exists ($dbh, "repgroupactdaily_daily") ;
2648 &drop_table_if_exists ($dbh, "rep_group_act_weekly") ;
2649 &drop_index_if_exists ($dbh, "repgroupactweekly_weekly") ;
2650 &drop_table_if_exists ($dbh, "rep_group_act_monthly") ;
2651 &drop_index_if_exists ($dbh, "repgroupactmonthly_monthly") ;
2653 @reqlist = @{ &parse_sql_file ("$sqldir/timetracking-init.sql") } ;
2654 foreach my $s (@reqlist) {
2657 $sth = $dbh->prepare ($query) ;
2664 &update_db_version ($target) ;
2665 &debug ("Committing.") ;
2669 # I had to increase versions from 4.5.14 to 4.5.15
2670 # The activity view is created by 20060216-nocommit
2671 # If the view doesn't exists apply
2672 if (! &view_exists ($dbh, 'activity_vw')) {
2673 &update_with_sql("20050812","4.5.15-10merge");
2674 &update_with_sql("20050822-2","4.5.15-11merge");
2675 &update_with_sql("20050823","4.5.15-12merge");
2676 &update_with_sql("20050824","4.5.15-13merge");
2677 &update_with_sql("20050831","4.5.15-14merge");
2679 &update_with_sql("20060113","4.5.15-15");
2680 &update_with_sql("20060214","4.5.15-16");
2681 &update_with_sql("20060216-2-debian-nocommit","4.5.15-17");
2684 $version = &get_db_version ;
2685 $target = "4.5.15-21" ;
2686 if (&is_lesser ($version, $target)) {
2687 &debug ("Fixing past mistakes in role naming") ;
2689 my $defaultroles_restricted = {
2690 'Admin' => { 'projectadmin'=>'A', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'trackeradmin'=>'2', 'pmadmin'=>'2' },
2691 'Senior Developer' => { 'projectadmin'=>'0', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'trackeradmin'=>'2', 'pmadmin'=>'2' },
2692 'Junior Developer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'1', 'docman'=>'0', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' },
2693 'Doc Writer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' },
2694 'Support Tech' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' }
2697 foreach my $drname (keys %{$defaultroles_restricted}) {
2698 $query = "UPDATE role SET role_name='$drname' WHERE role_id IN (SELECT role.role_id" ;
2703 foreach my $setting (keys %{$defaultroles_restricted->{$drname}}) {
2704 $value = $defaultroles_restricted->{$drname}->{$setting} ;
2705 $from .= ", role_setting rs_$setting" ;
2706 $where .= "role.role_id = rs_$setting.role_id AND rs_$setting.section_name='$setting' AND " ;
2707 $where .= "rs_$setting.value = '$value' \nAND " ;
2709 $query .= "\nFROM role$from" ;
2710 $query .= "\nWHERE $where role.role_name='rname')";
2711 push @reqlist, $query;
2714 foreach my $s (@reqlist) {
2717 $sth = $dbh->prepare ($query) ;
2723 &update_db_version ($target) ;
2724 &debug ("Committing.") ;
2728 &update_with_sql("20051103_transiciel_motscle_document","4.6-1");
2730 &update_with_sql("20070924-forum-perm","4.6.99-1");
2731 &update_with_sql("20070924-project-perm","4.6.99-2");
2732 &update_with_sql("20070924-artifact-perm","4.6.99-3");
2734 $version = &get_db_version ;
2735 $target = "4.6.99-4" ;
2736 if (&is_lesser ($version, $target)) {
2737 &debug ("Dropping old translations table") ;
2739 &drop_table_if_exists ($dbh, "tmp_lang") ;
2741 &update_db_version ($target) ;
2742 &debug ("Committing.") ;
2746 $version = &get_db_version ;
2747 $target = "4.6.99-5" ;
2748 if (&is_lesser ($version, $target)) {
2749 &debug ("Updating available themes") ;
2751 my @obsolete_themes = qw/ classic debian savannah
2752 savannah_codex savannah_forest
2753 savannah_reverse savannah_sad
2754 savannah_savannah savannah_slashd
2756 savannah_transparent savannah_water
2757 savannah_www.gnu.org
2758 savannah_darkslate forged kde
2761 my $otids = join (',', map { "'$_'" } @obsolete_themes) ;
2763 $query = "UPDATE users SET theme_id=1 WHERE theme_id IN
2764 (SELECT theme_id FROM themes WHERE dirname IN ($otids))" ;
2765 push @reqlist, $query;
2767 $query = "DELETE FROM themes WHERE dirname IN ($otids)" ;
2768 push @reqlist, $query;
2771 'gforge-classic' => 'GForge classic',
2772 'gforge-simple-theme' => 'GForge simple',
2773 'lite' => 'GForge lite'
2776 foreach my $dir (sort keys %new_themes) {
2777 $query = "INSERT INTO themes (dirname, fullname) VALUES ('$dir', '$new_themes{$dir}')" ;
2778 push @reqlist, $query;
2781 foreach my $s (@reqlist) {
2784 $sth = $dbh->prepare ($query) ;
2790 &update_db_version ($target) ;
2791 &debug ("Committing.") ;
2795 $version = &get_db_version ;
2796 $target = "4.6.99-6" ;
2797 if (&is_lesser ($version, $target)) {
2798 &debug ("DROP UNIQUE INDEX never UNIQUE") ;
2799 &drop_index_if_exists ($dbh, "statsaggsitebygrp_oid") ;
2800 &drop_index_if_exists ($dbh, "statsprojectmetric_oid") ;
2801 &drop_index_if_exists ($dbh, "statsagglogobygrp_oid") ;
2802 &drop_index_if_exists ($dbh, "statsprojectdevelop_oid") ;
2803 &drop_index_if_exists ($dbh, "statssubdpages_oid") ;
2804 &drop_index_if_exists ($dbh, "statscvsgrp_oid") ;
2805 &drop_index_if_exists ($dbh, "statsproject_oid") ;
2806 &drop_index_if_exists ($dbh, "statssite_oid") ;
2807 &drop_index_if_exists ($dbh, "statssitepgsbyday_oid") ;
2808 &update_db_version ($target) ;
2809 &debug ("Committing.") ;
2813 &update_with_sql("20090327_create_table_project_tags","4.6.99-7");
2814 &update_with_sql("20090402-add-projecttags-constraints","4.7.99-1");
2815 &update_with_sql("20090402-forum-attachment-types","4.7.99-2");
2817 &update_with_sql("20090507-add_artifact_workflow","4.8.99-1");
2818 &update_with_sql("20090507-add_element_pos","4.8.99-2");
2819 &update_with_sql("20090507-add_project_query","4.8.99-3");
2820 &update_with_sql("20090507-browse_list","4.8.99-4");
2822 $version = &get_db_version ;
2823 $target = "4.8.99-5" ;
2824 if (&is_lesser ($version, $target)) {
2825 &debug ("Initialising tracker workflows") ;
2828 $query = "SELECT group_id, artifact_group_list.group_artifact_id, element_id, artifact_extra_field_elements.extra_field_id
2829 FROM artifact_extra_field_list, artifact_extra_field_elements, artifact_group_list
2830 WHERE artifact_extra_field_list.extra_field_id=artifact_extra_field_elements.extra_field_id
2831 AND artifact_group_list.group_artifact_id = artifact_extra_field_list.group_artifact_id
2834 $sth = $dbh->prepare ($query) ;
2836 while (@array = $sth->fetchrow_array) {
2837 my $gid = $array[0];
2838 my $gaid = $array[1];
2839 my $eid = $array[2];
2841 my $query2 = "SELECT extra_field_id
2842 FROM artifact_extra_field_list
2843 WHERE group_artifact_id=$gaid
2845 ORDER BY field_type ASC" ;
2846 my $sth2 = $dbh->prepare ($query2) ;
2849 if (my @array2 = $sth2->fetchrow_array) {
2850 my $efid = $array2[0];
2853 $query2 = "SELECT element_id,element_name,status_id
2854 FROM artifact_extra_field_elements
2855 WHERE extra_field_id = $efid
2856 ORDER BY element_pos ASC, element_id ASC" ;
2858 $sth2 = $dbh->prepare ($query2) ;
2860 while (@array2 = $sth2->fetchrow_array) {
2861 my $eid2 = $array2[0];
2862 if ($eid2 != $eid) {
2863 my $query3 = "INSERT INTO artifact_workflow_event
2864 (group_artifact_id, field_id, from_value_id, to_value_id)
2865 VALUES ($gaid, $efid, $eid, $eid2)";
2867 my $sth3 = $dbh->prepare ($query3) ;
2870 $query3 = "INSERT INTO artifact_workflow_event
2871 (group_artifact_id, field_id, from_value_id, to_value_id)
2872 VALUES ($gaid, $efid, $eid2, $eid)";
2874 $sth3 = $dbh->prepare ($query3) ;
2880 my $query3 = "INSERT INTO artifact_workflow_event
2881 (group_artifact_id, field_id, from_value_id, to_value_id)
2882 VALUES ($gaid, $efid, 100, $eid)";
2884 my $sth3 = $dbh->prepare ($query3) ;
2892 &update_db_version ($target) ;
2893 &debug ("Committing.") ;
2897 &update_with_sql("20100308-forum-attachment-types","4.8.99-6");
2899 &update_with_sql("20100330-add-system-event","5.0.0-1");
2900 &update_with_sql("20100331-alter-system-event","5.0.0-2");
2901 &update_with_sql("20100505-alter-user-preference","5.0.1-1");
2902 &update_with_sql("20100506-add-widgets","5.0.1-2");
2903 &update_with_sql("20100517-add-project-widgets","5.0.1-3");
2904 &update_with_sql("20100518-pfo-rbac","5.0.1-4");
2905 &update_with_sql("20100524-pfo-rbac","5.0.1-5");
2906 &update_with_sql("20100606-clean-perm-views","5.0.1-6");
2907 &update_with_sql("20100610-pfo-rbac","5.0.1-7");
2908 &update_with_sql("20100730-docman","5.0.1-8");
2909 &update_with_sql("20100924-theme","5.0.1-9");
2910 &update_with_sql("20100926-pfo-rbac","5.0.1-10");
2911 &update_with_sql("20100927-pfo-rbac","5.0.1-11");
2913 ########################### INSERT HERE #################################
2915 # There should be a commit at the end of every block above.
2916 # If there is not, then it might be symptomatic of a problem.
2917 # For safety, we roll back.
2922 warn "Transaction aborted because $@" ;
2923 &debug ("Transaction aborted because $@") ;
2924 &debug ("Last SQL query was:\n$query\n(end of query)") ;
2926 my $version = &get_db_version ;
2928 &debug ("Your database schema is at version $version") ;
2930 &debug ("Couldn't get your database schema version.") ;
2932 &debug ("Please report this bug on the Debian bug-tracking system.") ;
2933 &debug ("Please include the previous messages as well to help debugging.") ;
2934 &debug ("You should not worry too much about this,") ;
2935 &debug ("your DB is still in a consistent state and should be usable.") ;
2942 sub get_pg_version () {
2944 if (-x '/usr/bin/pg_lsclusters' ) {
2945 $command = q(/usr/bin/pg_lsclusters | grep 5432 | grep online | cut -d' ' -f1) ;
2947 $command = q(dpkg -s postgresql | awk '/^Version: / { print $2 }') ;
2949 my $version = qx($command) ;
2954 sub create_metadata_table ( $ ) {
2955 my $v = shift || "2.5-7+just+before+8" ;
2957 my ($query, $sth, @array) ;
2959 # Let's create this table if we have it not
2960 if (! &table_exists ($dbh, 'debian_meta_data')) {
2961 &debug ("Creating debian_meta_data table.") ;
2962 $query = "CREATE TABLE debian_meta_data (key varchar primary key, value text not null)" ;
2964 $sth = $dbh->prepare ($query) ;
2969 $query = "SELECT count(*) FROM debian_meta_data WHERE key = 'db-version'";
2971 $sth = $dbh->prepare ($query) ;
2973 @array = $sth->fetchrow_array () ;
2976 # Empty table? We'll have to fill it up a bit
2978 if ($array [0] == 0) {
2979 &debug ("Inserting first data into debian_meta_data table.") ;
2980 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('db-version', '$v')" ;
2982 $sth = $dbh->prepare ($query) ;
2988 sub update_db_version ( $ ) {
2989 my $v = shift or die "Not enough arguments" ;
2991 &debug ("Updating debian_meta_data table.") ;
2992 $query = "UPDATE debian_meta_data SET value = '$v' WHERE key = 'db-version'" ;
2994 my $sth = $dbh->prepare ($query) ;
2999 sub get_db_version () {
3000 $query = "SELECT value FROM debian_meta_data WHERE key = 'db-version'" ;
3002 my $sth = $dbh->prepare ($query) ;
3004 my @array = $sth->fetchrow_array () ;
3007 my $version = $array [0] ;
3012 sub update_with_sql ( $ ) {
3013 my $sqldate = shift or die "Not enough arguments" ;
3014 my $target = shift or die "Not enough arguments" ;
3015 my $version = &get_db_version ;
3016 if (&is_lesser ($version, $target)) {
3017 &debug ("Upgrading database with $sqldate.sql") ;
3019 @reqlist = @{ &parse_sql_file ("$sqldir/$sqldate.sql") } ;
3020 foreach my $s (@reqlist) {
3023 my $sth = $dbh->prepare ($query) ;
3029 &update_db_version ($target) ;
3030 &debug ("Committing.") ;