5 # Debian-specific script to upgrade the database between releases
6 # Roland Mas <lolando@debian.org>
15 use vars qw/$dbh @reqlist $query/ ;
16 use vars qw/$sys_default_domain $sys_cvs_host $sys_download_host
17 $sys_shell_host $sys_users_host $sys_docs_host $sys_lists_host
18 $sys_dns1_host $sys_dns2_host $FTPINCOMING_DIR $FTPFILES_DIR
19 $sys_urlroot $sf_cache_dir $sys_name $sys_themeroot
20 $sys_news_group $sys_dbhost $sys_dbname $sys_dbuser $sys_dbpasswd
21 $sys_ldap_base_dn $sys_ldap_host $admin_login $admin_password
22 $server_admin $domain_name $newsadmin_groupid $statsadmin_groupid
25 sub is_lesser ( $$ ) ;
26 sub is_greater ( $$ ) ;
28 sub parse_sql_file ( $ ) ;
30 require ("/usr/lib/gforge/lib/include.pl") ; # Include a few predefined functions
31 require ("/usr/lib/gforge/lib/sqlparser.pm") ; # Our magic SQL parser
33 debug "You'll see some debugging info during this installation." ;
34 debug "Do not worry unless told otherwise." ;
38 # debug "Connected to the database OK." ;
40 $dbh->{AutoCommit} = 0;
41 $dbh->{RaiseError} = 1;
43 my ($sth, @array, $version, $action, $path, $target) ;
45 # Do we have at least the basic schema?
47 $query = "SELECT count(*) from pg_class where relname = 'groups' and relkind = 'r'";
49 $sth = $dbh->prepare ($query) ;
51 @array = $sth->fetchrow_array () ;
54 # Create Sourceforge database
56 if ($array [0] == 0) { # No 'groups' table
57 # Installing SF 2.6 from scratch
58 $action = "installation" ;
59 debug "Creating initial Sourceforge database from files." ;
61 &create_metadata_table ("2.5.9999") ;
63 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
65 $sth = $dbh->prepare ($query) ;
67 @array = $sth->fetchrow_array () ;
70 debug "Updating debian_meta_data table." ;
71 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', 'scratch-to-2.6')" ;
73 $sth = $dbh->prepare ($query) ;
80 } else { # A 'groups' table exists
83 $query = "SELECT count(*) from pg_class where relname = 'debian_meta_data' and relkind = 'r'";
85 $sth = $dbh->prepare ($query) ;
87 @array = $sth->fetchrow_array () ;
90 if ($array[0] == 0) { # No 'debian_meta_data' table
91 # If we're here, we're upgrading from 2.5-7 or earlier
92 # We therefore need to create the table
93 &create_metadata_table ("2.5-7+just+before+8") ;
96 $version = &get_db_version ;
97 if (is_lesser $version, "2.5.9999") {
98 debug "Found an old (2.5) database, will upgrade to 2.6" ;
100 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
102 $sth = $dbh->prepare ($query) ;
104 @array = $sth->fetchrow_array () ;
107 if ($array[0] == 0) {
108 # debug "Updating debian_meta_data table." ;
109 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', '2.5-to-2.6')" ;
111 $sth = $dbh->prepare ($query) ;
114 debug "Committing." ;
120 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
122 $sth = $dbh->prepare ($query) ;
124 @array = $sth->fetchrow_array () ;
127 if ($array[0] == 0) {
130 $query = "SELECT value from debian_meta_data where key = 'current-path'";
132 $sth = $dbh->prepare ($query) ;
134 @array = $sth->fetchrow_array () ;
141 ($path eq 'scratch-to-2.6') && do {
142 $version = &get_db_version ;
143 $target = "2.5.9999.1+global+data+done" ;
144 if (is_lesser $version, $target) {
145 my @filelist = qw{ /usr/lib/gforge/db/sf-2.6-complete.sql } ;
146 # TODO: user_rating.sql
148 foreach my $file (@filelist) {
149 debug "Processing $file" ;
150 @reqlist = @{ &parse_sql_file ($file) } ;
152 foreach my $s (@reqlist) {
155 $sth = $dbh->prepare ($query) ;
162 &update_db_version ($target) ;
163 debug "Committing." ;
167 $version = &get_db_version ;
168 $target = "2.5.9999.2+local+data+done" ;
169 if (is_lesser $version, $target) {
170 debug "Adding local data." ;
172 do "/etc/gforge/local.pl" or die "Cannot read /etc/gforge/local.pl" ;
174 my ($login, $pwd, $md5pwd, $email, $noreplymail, $date) ;
176 $login = $admin_login ;
177 $pwd = $admin_password ;
178 $md5pwd=qx/echo -n $pwd | md5sum/ ;
180 $md5pwd =~ s/(.{32}) .*/$1/ ;
181 $email = $server_admin ;
182 $noreplymail="noreply\@$domain_name" ;
186 "UPDATE groups SET homepage = '$domain_name/admin/' where group_id = 1",
187 "UPDATE groups SET homepage = '$domain_name/news/' where group_id = 2",
188 "UPDATE groups SET homepage = '$domain_name/stats/' where group_id = 3",
189 "UPDATE groups SET homepage = '$domain_name/peerrating/' where group_id = 4",
190 "UPDATE users SET email = '$noreplymail' where user_id = 100",
191 "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)",
192 "SELECT setval ('\"users_pk_seq\"', 102, 'f')",
193 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 1, 'A')",
194 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 2, 'A')",
195 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 3, 'A')",
196 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 4, 'A')"
199 foreach my $s (@reqlist) {
202 $sth = $dbh->prepare ($query) ;
208 &update_db_version ($target) ;
209 debug "Committing." ;
213 $version = &get_db_version ;
214 $target = "2.5.9999.3+skills+done" ;
215 if (is_lesser $version, $target) {
216 debug "Inserting skills." ;
218 foreach my $skill (split /;/, $skill_list) {
219 push @reqlist, "INSERT INTO people_skill (name) VALUES ('$skill')" ;
222 foreach my $s (@reqlist) {
225 $sth = $dbh->prepare ($query) ;
231 &update_db_version ($target) ;
232 debug "Committing." ;
236 $version = &get_db_version ;
237 $target = "2.6-0+checkpoint+1" ;
238 if (is_lesser $version, $target) {
239 debug "Updating debian_meta_data table." ;
240 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
242 $sth = $dbh->prepare ($query) ;
246 &update_db_version ($target) ;
247 debug "Committing." ;
254 ($path eq '2.5-to-2.6') && do {
256 $version = &get_db_version ;
258 if (is_lesser $version, $target) {
259 debug "Adding row to people_job_category." ;
260 $query = "INSERT INTO people_job_category VALUES (100, 'Undefined', 0)" ;
261 $sth = $dbh->prepare ($query) ;
265 &update_db_version ($target) ;
266 debug "Committing." ;
270 $version = &get_db_version ;
272 if (is_lesser $version, $target) {
273 debug "Adding row to supported_languages." ;
274 $query = "INSERT INTO supported_languages VALUES (15, 'Korean', 'Korean.class', 'Korean', 'kr')" ;
275 $sth = $dbh->prepare ($query) ;
279 &update_db_version ($target) ;
280 debug "Committing." ;
284 $version = &get_db_version ;
286 if (is_lesser $version, $target) {
287 debug "Fixing unix_box entries." ;
289 $query = "update groups set unix_box = 'shell'" ;
290 $sth = $dbh->prepare ($query) ;
294 $query = "update users set unix_box = 'shell'" ;
295 $sth = $dbh->prepare ($query) ;
299 debug "Also fixing a few sequences." ;
301 &bump_sequence_to ("bug_pk_seq", 100) ;
302 &bump_sequence_to ("project_task_pk_seq", 100) ;
304 &update_db_version ($target) ;
305 debug "Committing." ;
309 $version = &get_db_version ;
311 if (is_lesser $version, $target) {
312 debug "Adding rows to supported_languages." ;
314 "INSERT INTO supported_languages VALUES (16,'Bulgarian','Bulgarian.class','Bulgarian','bg')",
315 "INSERT INTO supported_languages VALUES (17,'Greek','Greek.class','Greek','el')",
316 "INSERT INTO supported_languages VALUES (18,'Indonesian','Indonesian.class','Indonesian','id')",
317 "INSERT INTO supported_languages VALUES (19,'Portuguese (Brazillian)','PortugueseBrazillian.class','PortugueseBrazillian', 'br')",
318 "INSERT INTO supported_languages VALUES (20,'Polish','Polish.class','Polish','pl')",
319 "INSERT INTO supported_languages VALUES (21,'Portuguese','Portuguese.class','Portuguese', 'pt')",
320 "INSERT INTO supported_languages VALUES (22,'Russian','Russian.class','Russian','ru')"
323 foreach my $s (@reqlist) {
326 $sth = $dbh->prepare ($query) ;
332 &update_db_version ($target) ;
333 debug "Committing." ;
337 $version = &get_db_version ;
339 if (is_lesser $version, $target) {
340 debug "Fixing unix_uid entries." ;
342 $query = "UPDATE users SET unix_uid = nextval ('unix_uid_seq') WHERE unix_status != 'N' AND status != 'P' AND unix_uid = 0" ;
343 $sth = $dbh->prepare ($query) ;
347 &update_db_version ($target) ;
348 debug "Committing." ;
352 $version = &get_db_version ;
353 $target = "2.5.9999.1+temp+data+dropped" ;
354 if (is_lesser $version, $target) {
355 debug "Preparing to upgrade your database - dropping temporary tables" ;
357 my @tables = qw/ user_metric_tmp1_1 user_metric_tmp1_2
358 user_metric_tmp1_3 user_metric_tmp1_4
359 user_metric_tmp1_5 user_metric_tmp1_6
360 user_metric_tmp1_7 user_metric_tmp1_8 user_metric1
361 user_metric2 user_metric3 user_metric4 user_metric5
362 user_metric6 user_metric7 user_metric8
363 project_counts_tmp project_metric_tmp
364 project_metric_tmp1 project_counts_weekly_tmp
365 project_metric_weekly_tmp project_metric_weekly_tmp1
368 my @sequences = qw/ user_metric1_ranking_seq
369 user_metric2_ranking_seq user_metric3_ranking_seq
370 user_metric4_ranking_seq user_metric5_ranking_seq
371 user_metric6_ranking_seq user_metric7_ranking_seq
372 user_metric8_ranking_seq project_metric_weekly_seq
373 trove_treesum_trove_treesum_seq
374 project_metric_tmp1_pk_seq / ;
376 my @indexes = qw/ idx_project_metric_group
377 idx_project_metric_weekly_group
378 user_metric_history_date_userid / ;
380 foreach my $table (@tables) {
381 &drop_table_if_exists ($table) ;
384 foreach my $sequence (@sequences) {
385 &drop_sequence_if_exists ($sequence) ;
388 foreach my $index (@indexes) {
389 &drop_index_if_exists ($index) ;
392 &update_db_version ($target) ;
393 debug "Committing." ;
397 $version = &get_db_version ;
398 $target = "2.5.9999.2+data+upgraded" ;
399 if (is_lesser $version, $target) {
400 debug "Upgrading your database scheme from 2.5" ;
402 my $pg_version = &get_pg_version ;
404 if (is_lesser $pg_version, "7.3") {
406 "DROP INDEX groups_pkey",
407 "DROP INDEX users_pkey",
411 "ALTER TABLE groups DROP CONSTRAINT groups_pkey",
412 "ALTER TABLE users DROP CONSTRAINT users_pkey",
415 foreach my $s (@reqlist) {
418 $sth = $dbh->prepare ($query) ;
423 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/sf2.5-to-sf2.6.sql") } ;
424 foreach my $s (@reqlist) {
427 $sth = $dbh->prepare ($query) ;
433 &update_db_version ($target) ;
434 debug "Committing." ;
438 $version = &get_db_version ;
439 $target = "2.5.9999.3+artifact+transcoded" ;
440 if (is_lesser $version, $target) {
441 debug "Transcoding the artifact data fields" ;
443 $query = "SELECT id,bin_data FROM artifact_file ORDER BY id ASC" ;
445 $sth = $dbh->prepare ($query) ;
447 while (@array = $sth->fetchrow_array) {
448 my $query2 = "UPDATE artifact_file SET bin_data='" ;
449 $query2 .= encode_base64 (decode_entities ($array [1])) ;
450 $query2 .= "' WHERE id=" ;
451 $query2 .= $array [0] ;
454 my $sth2 =$dbh->prepare ($query2) ;
461 &update_db_version ($target) ;
462 debug "Committing." ;
466 $version = &get_db_version ;
467 $target = "2.5.9999.4+groups+inserted" ;
468 if (is_lesser $version, $target) {
469 debug "Inserting missing groups" ;
472 "INSERT INTO groups (group_name, homepage,
473 is_public, status, unix_group_name,
474 unix_box, http_domain, short_description,
475 cvs_box, license, register_purpose,
476 license_other, register_time, rand_hash,
477 use_mail, use_survey, use_forum, use_pm,
478 use_cvs, use_news, type, use_docman,
479 new_task_address, send_all_tasks,
481 VALUES ('Stats', '$domain_name/top/', 0,
482 'A', 'stats', 'shell', NULL, NULL, 'cvs',
483 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
485 "INSERT INTO groups (group_name, homepage,
486 is_public, status, unix_group_name,
487 unix_box, http_domain, short_description,
488 cvs_box, license, register_purpose,
489 license_other, register_time, rand_hash,
490 use_mail, use_survey, use_forum, use_pm,
491 use_cvs, use_news, type, use_docman,
492 new_task_address, send_all_tasks,
494 VALUES ('Peer Ratings', '$domain_name/people/', 0,
495 'A', 'peerrating', 'shell', NULL, NULL, 'cvs1',
496 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
500 foreach my $s (@reqlist) {
503 $sth = $dbh->prepare ($query) ;
508 &update_db_version ($target) ;
509 debug "Committing." ;
513 $version = &get_db_version ;
514 $target = "2.6-0+checkpoint+1" ;
515 if (is_lesser $version, $target) {
516 debug "Database has successfully been converted." ;
517 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
519 $sth = $dbh->prepare ($query) ;
523 &update_db_version ($target) ;
524 debug "Committing." ;
532 $version = &get_db_version ;
533 $target = "2.6-0+checkpoint+2" ;
534 if (is_lesser $version, $target) {
535 debug "Updating permissions on system groups." ;
536 $query = "UPDATE groups SET group_name='Site Admin', is_public=1 WHERE group_id=1" ;
538 $sth = $dbh->prepare ($query) ;
541 $query = "UPDATE groups SET group_name='Site News Admin', is_public=1 WHERE group_id=$sys_news_group" ;
543 $sth = $dbh->prepare ($query) ;
547 &update_db_version ($target) ;
548 debug "Committing." ;
552 $version = &get_db_version ;
553 $target = "2.6-0+checkpoint+3" ;
554 if (is_lesser $version, $target) {
555 debug "Creating table group_cvs_history." ;
556 $query = "CREATE TABLE group_cvs_history (
557 id integer DEFAULT nextval('group_cvs_history_pk_seq'::text) NOT NULL,
558 group_id integer DEFAULT '0' NOT NULL,
559 user_name character varying(80) DEFAULT '' NOT NULL,
560 cvs_commits integer DEFAULT '0' NOT NULL,
561 cvs_commits_wk integer DEFAULT '0' NOT NULL,
562 cvs_adds integer DEFAULT '0' NOT NULL,
563 cvs_adds_wk integer DEFAULT '0' NOT NULL,
566 $sth = $dbh->prepare ($query) ;
570 &update_db_version ($target) ;
571 debug "Committing." ;
575 $version = &get_db_version ;
576 $target = "2.6-0+checkpoint+4" ;
577 if (is_lesser $version, $target) {
578 debug "Registering Savannah themes." ;
580 $query = "SELECT max(theme_id) FROM themes" ;
582 $sth = $dbh->prepare ($query) ;
584 @array = $sth->fetchrow_array () ;
586 my $maxid = $array [0] ;
588 &bump_sequence_to ("themes_pk_seq", $maxid) ;
591 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_codex', 'Savannah CodeX')",
592 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_forest', 'Savannah Forest')",
593 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_reverse', 'Savannah Reverse')",
594 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_sad', 'Savannah Sad')",
595 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_savannah', 'Savannah Original')",
596 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_slashd', 'Savannah SlashDot')",
597 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_startrek', 'Savannah StarTrek')",
598 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_transparent', 'Savannah Transparent')",
599 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_water', 'Savannah Water')",
600 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_www.gnu.org', 'Savannah www.gnu.org')"
602 foreach my $s (@reqlist) {
605 $sth = $dbh->prepare ($query) ;
611 &update_db_version ($target) ;
612 debug "Committing." ;
616 $version = &get_db_version ;
617 $target = "2.6-0+checkpoint+5" ;
618 if (is_lesser $version, $target) {
619 debug "Registering yet another Savannah theme." ;
621 $query = "INSERT INTO themes (dirname, fullname) VALUES ('savannah_darkslate', 'Savannah Dark Slate')";
623 $sth = $dbh->prepare ($query) ;
627 &update_db_version ($target) ;
628 debug "Committing." ;
632 $version = &get_db_version ;
633 $target = "2.6-0+checkpoint+6" ;
634 if (is_lesser $version, $target) {
635 debug "Updating language codes." ;
638 "UPDATE supported_languages SET language_code='en' where classname='English'",
639 "UPDATE supported_languages SET language_code='ja' where classname='Japanese'",
640 "UPDATE supported_languages SET language_code='iw' where classname='Hebrew'",
641 "UPDATE supported_languages SET language_code='es' where classname='Spanish'",
642 "UPDATE supported_languages SET language_code='th' where classname='Thai'",
643 "UPDATE supported_languages SET language_code='de' where classname='German'",
644 "UPDATE supported_languages SET language_code='it' where classname='Italian'",
645 "UPDATE supported_languages SET language_code='no' where classname='Norwegian'",
646 "UPDATE supported_languages SET language_code='sv' where classname='Swedish'",
647 "UPDATE supported_languages SET language_code='zh' where classname='Chinese'",
648 "UPDATE supported_languages SET language_code='nl' where classname='Dutch'",
649 "UPDATE supported_languages SET language_code='eo' where classname='Esperanto'",
650 "UPDATE supported_languages SET language_code='ca' where classname='Catalan'",
651 "UPDATE supported_languages SET language_code='ko' where classname='Korean'",
652 "UPDATE supported_languages SET language_code='bg' where classname='Bulgarian'",
653 "UPDATE supported_languages SET language_code='el' where classname='Greek'",
654 "UPDATE supported_languages SET language_code='id' where classname='Indonesian'",
655 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese (Brazillian)'",
656 "UPDATE supported_languages SET language_code='pl' where classname='Polish'",
657 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese'",
658 "UPDATE supported_languages SET language_code='ru' where classname='Russian'",
659 "UPDATE supported_languages SET language_code='fr' where classname='French'"
661 foreach my $s (@reqlist) {
664 $sth = $dbh->prepare ($query) ;
669 &update_db_version ($target) ;
670 debug "Committing." ;
674 $version = &get_db_version ;
675 $target = "2.6-0+checkpoint+7" ;
676 if (is_lesser $version, $target) {
677 debug "Fixing artifact-related views." ;
679 &drop_view_if_exists ("artifact_file_user_vw") ;
680 &drop_view_if_exists ("artifact_history_user_vw") ;
681 &drop_view_if_exists ("artifact_message_user_vw") ;
682 &drop_view_if_exists ("artifactperm_artgrouplist_vw") ;
683 &drop_view_if_exists ("artifactperm_user_vw") ;
684 &drop_view_if_exists ("artifact_vw") ;
687 "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)",
688 "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)",
689 "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)",
690 "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)",
691 "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)",
692 "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))"
694 foreach my $s (@reqlist) {
697 $sth = $dbh->prepare ($query) ;
702 &update_db_version ($target) ;
703 debug "Committing." ;
707 $version = &get_db_version ;
708 $target = "2.6-0+checkpoint+8" ;
709 if (is_lesser $version, $target) {
710 debug "Adding integrity constraints between the Trove map tables." ;
713 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
714 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
715 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
716 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
717 "DELETE FROM trove_treesums WHERE trove_cat_id NOT IN (SELECT trove_cat_id FROM trove_cat)",
718 "ALTER TABLE trove_treesums ADD CONSTRAINT trove_treesums_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
720 foreach my $s (@reqlist) {
723 $sth = $dbh->prepare ($query) ;
728 &update_db_version ($target) ;
729 debug "Committing." ;
733 $version = &get_db_version ;
734 $target = "2.6-0+checkpoint+9" ;
735 if (is_lesser $version, $target) {
736 debug "Adding extra fields to the groups table." ;
739 "ALTER TABLE groups ADD COLUMN use_ftp integer",
740 "ALTER TABLE groups ALTER COLUMN use_ftp SET DEFAULT 1",
741 "UPDATE groups SET use_ftp = 1",
742 "ALTER TABLE groups ADD COLUMN use_tracker integer",
743 "ALTER TABLE groups ALTER COLUMN use_tracker SET DEFAULT 1",
744 "UPDATE groups SET use_tracker = 1",
745 "ALTER TABLE groups ADD COLUMN use_frs integer",
746 "ALTER TABLE groups ALTER COLUMN use_frs SET DEFAULT 1",
747 "UPDATE groups SET use_frs = 1",
748 "ALTER TABLE groups ADD COLUMN use_stats integer",
749 "ALTER TABLE groups ALTER COLUMN use_stats SET DEFAULT 1",
750 "UPDATE groups SET use_stats = 1",
751 "ALTER TABLE groups ADD COLUMN enable_pserver integer",
752 "ALTER TABLE groups ALTER COLUMN enable_pserver SET DEFAULT 1",
753 "UPDATE groups SET enable_pserver = 1",
754 "ALTER TABLE groups ADD COLUMN enable_anoncvs integer",
755 "ALTER TABLE groups ALTER COLUMN enable_anoncvs SET DEFAULT 1",
756 "UPDATE groups SET enable_anoncvs = 1",
758 foreach my $s (@reqlist) {
761 $sth = $dbh->prepare ($query) ;
766 &update_db_version ($target) ;
767 debug "Committing." ;
771 $version = &get_db_version ;
772 $target = "2.6-0+checkpoint+10" ;
773 if (is_lesser $version, $target) {
774 debug "Updating supported_languages table." ;
776 my $pg_version = &get_pg_version ;
778 if (is_lesser $pg_version, "7.3") {
780 "ALTER TABLE supported_languages RENAME TO supported_languages_old",
781 "CREATE TABLE supported_languages (language_id integer DEFAULT nextval('supported_languages_pk_seq'::text) NOT NULL, name text, filename text, classname text, language_code character(5))",
782 "INSERT INTO supported_languages SELECT * FROM supported_languages_old",
783 "DROP TABLE supported_languages_old",
784 "ALTER TABLE supported_languages ADD CONSTRAINT supported_languages_pkey PRIMARY KEY (language_id)",
785 "ALTER TABLE users ADD CONSTRAINT users_languageid_fk FOREIGN KEY (language) REFERENCES supported_languages(language_id) MATCH FULL",
786 "ALTER TABLE doc_data ADD CONSTRAINT docdata_languageid_fk FOREIGN KEY (language_id) REFERENCES supported_languages(language_id) MATCH FULL",
787 "UPDATE supported_languages SET language_code='pt_BR', classname='PortugueseBrazilian', name='Pt. Brazilian', filename='PortugueseBrazilian.class' where classname='PortugueseBrazillian'",
791 "ALTER TABLE supported_languages RENAME COLUMN language_code TO language_code_old",
792 "ALTER TABLE supported_languages ADD COLUMN language_code character(5)",
793 "UPDATE supported_languages SET language_code = language_code_old",
794 "ALTER TABLE supported_languages DROP COLUMN language_code_old",
795 "UPDATE supported_languages SET language_code='pt_BR', classname='PortugueseBrazilian', name='Pt. Brazilian', filename='PortugueseBrazilian.class' where classname='PortugueseBrazillian'",
798 foreach my $s (@reqlist) {
801 $sth = $dbh->prepare ($query) ;
806 &update_db_version ($target) ;
807 debug "Committing." ;
811 $version = &get_db_version ;
812 $target = "2.6-0+checkpoint+11" ;
813 if (is_lesser $version, $target) {
814 debug "Adding tables for the plugin subsystem." ;
817 "CREATE SEQUENCE plugins_pk_seq",
818 "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))",
819 "CREATE SEQUENCE group_plugin_pk_seq",
820 "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)",
821 "CREATE SEQUENCE user_plugin_pk_seq",
822 "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)",
824 foreach my $s (@reqlist) {
827 $sth = $dbh->prepare ($query) ;
832 &update_db_version ($target) ;
833 debug "Committing." ;
837 $version = &get_db_version ;
838 $target = "2.6-0+checkpoint+12" ;
839 if (is_lesser $version, $target) {
840 debug "Upgrading with 20021125.sql" ;
842 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021125.sql") } ;
843 foreach my $s (@reqlist) {
846 $sth = $dbh->prepare ($query) ;
852 &update_db_version ($target) ;
853 debug "Committing $target." ;
857 $version = &get_db_version ;
858 $target = "2.6-0+checkpoint+13" ;
859 if (is_lesser $version, $target) {
860 debug "Upgrading with 20021212.sql" ;
862 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021212.sql") } ;
863 foreach my $s (@reqlist) {
866 $sth = $dbh->prepare ($query) ;
872 &update_db_version ($target) ;
873 debug "Committing $target." ;
877 $version = &get_db_version ;
878 $target = "2.6-0+checkpoint+14" ;
879 if (is_lesser $version, $target) {
880 debug "Upgrading with 20021213.sql" ;
882 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021213.sql") } ;
883 foreach my $s (@reqlist) {
886 $sth = $dbh->prepare ($query) ;
892 &update_db_version ($target) ;
893 debug "Committing $target." ;
897 $version = &get_db_version ;
898 $target = "2.6-0+checkpoint+15" ;
899 if (is_lesser $version, $target) {
900 debug "Transcoding documentation data fields" ;
901 $query = "SELECT docid,data FROM doc_data ORDER BY docid ASC" ;
903 $sth = $dbh->prepare ($query) ;
905 while (@array = $sth->fetchrow_array) {
906 my $query2 = "UPDATE doc_data SET data='" ;
907 $query2 .= encode_base64 (decode_entities ($array [1])) ;
908 $query2 .= "', filename='file".$array [0].".html'";
909 $query2 .= ", filetype='text/html'";
910 $query2 .= " WHERE docid=" ;
911 $query2 .= $array [0] ;
914 my $sth2 =$dbh->prepare ($query2) ;
921 &update_db_version ($target) ;
922 debug "Committing $target." ;
926 $version = &get_db_version ;
927 $target = "2.6-0+checkpoint+16" ;
928 if (is_lesser $version, $target) {
929 debug "Upgrading with 20021214.sql" ;
931 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021214.sql") } ;
932 foreach my $s (@reqlist) {
935 $sth = $dbh->prepare ($query) ;
941 &update_db_version ($target) ;
942 debug "Committing $target." ;
946 $version = &get_db_version ;
947 $target = "2.6-0+checkpoint+17" ;
948 if (is_lesser $version, $target) {
949 debug "Upgrading with 20021215.sql" ;
951 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021215.sql") } ;
952 foreach my $s (@reqlist) {
955 $sth = $dbh->prepare ($query) ;
961 &update_db_version ($target) ;
962 debug "Committing $target." ;
966 $version = &get_db_version ;
967 $target = "2.6-0+checkpoint+18" ;
968 if (is_lesser $version, $target) {
969 debug "Upgrading with 20021216.sql" ;
971 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021216.sql") } ;
972 foreach my $s (@reqlist) {
975 $sth = $dbh->prepare ($query) ;
981 &update_db_version ($target) ;
982 debug "Committing $target." ;
986 $version = &get_db_version ;
987 $target = "2.6-0+checkpoint+19" ;
988 if (is_lesser $version, $target) {
989 debug "Upgrading with 20021223.sql" ;
991 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021223.sql") } ;
992 foreach my $s (@reqlist) {
995 $sth = $dbh->prepare ($query) ;
1001 &update_db_version ($target) ;
1002 debug "Committing $target." ;
1006 $version = &get_db_version ;
1007 $target = "2.6-0+checkpoint+20" ;
1008 if (is_lesser $version, $target) {
1009 debug "Upgrading with 20030102.sql" ;
1011 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030102.sql") } ;
1012 foreach my $s (@reqlist) {
1015 $sth = $dbh->prepare ($query) ;
1021 &update_db_version ($target) ;
1022 debug "Committing $target." ;
1026 $version = &get_db_version ;
1027 $target = "2.6-0+checkpoint+21" ;
1028 if (is_lesser $version, $target) {
1029 debug "Upgrading with 20030105.sql" ;
1031 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030105.sql") } ;
1032 foreach my $s (@reqlist) {
1035 $sth = $dbh->prepare ($query) ;
1041 &update_db_version ($target) ;
1042 debug "Committing $target." ;
1046 $version = &get_db_version ;
1047 $target = "2.6-0+checkpoint+22" ;
1048 if (is_lesser $version, $target) {
1049 debug "Upgrading with 20030107.sql" ;
1051 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030107.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+23" ;
1068 if (is_lesser $version, $target) {
1069 debug "Upgrading with 20030109.sql" ;
1071 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030109.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+24" ;
1088 if (is_lesser $version, $target) {
1090 debug "Adjusting language sequense" ;
1092 $query = "SELECT max(language_id) FROM supported_languages" ;
1093 $sth = $dbh->prepare ($query) ;
1095 @array = $sth->fetchrow_array () ;
1097 my $maxid = $array [0] ;
1098 &bump_sequence_to ("supported_languages_pk_seq", $maxid) ;
1100 debug "Upgrading with 20030112.sql" ;
1102 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030112.sql") } ;
1103 foreach my $s (@reqlist) {
1106 $sth = $dbh->prepare ($query) ;
1112 &update_db_version ($target) ;
1113 debug "Committing $target." ;
1117 $version = &get_db_version ;
1118 $target = "2.6-0+checkpoint+25" ;
1119 if (is_lesser $version, $target) {
1120 debug "Upgrading with 20030113.sql" ;
1122 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030113.sql") } ;
1123 foreach my $s (@reqlist) {
1126 $sth = $dbh->prepare ($query) ;
1132 &update_db_version ($target) ;
1133 debug "Committing $target." ;
1137 $version = &get_db_version ;
1138 $target = "2.6-0+checkpoint+26" ;
1139 if (is_lesser $version, $target) {
1140 debug "Upgrading with 20030131.sql" ;
1142 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030131.sql") } ;
1143 foreach my $s (@reqlist) {
1146 $sth = $dbh->prepare ($query) ;
1152 &update_db_version ($target) ;
1153 debug "Committing $target." ;
1157 $version = &get_db_version ;
1158 $target = "2.6-0+checkpoint+27" ;
1159 if (is_lesser $version, $target) {
1160 debug "Upgrading with 20030209.sql" ;
1162 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030209.sql") } ;
1163 foreach my $s (@reqlist) {
1166 $sth = $dbh->prepare ($query) ;
1172 &update_db_version ($target) ;
1173 debug "Committing $target." ;
1177 $version = &get_db_version ;
1178 $target = "2.6-0+checkpoint+28" ;
1179 if (is_lesser $version, $target) {
1180 debug "Upgrading with 20030312.sql" ;
1182 my $pg_version = &get_pg_version ;
1184 if (is_lesser $pg_version, "7.3") {
1186 "DROP TRIGGER projtask_insert_depend_trig ON project_task",
1187 "DROP FUNCTION projtask_insert_depend ()",
1188 "CREATE OR REPLACE FUNCTION projtask_insert_depend () RETURNS OPAQUE AS '
1193 IF NEW.start_date > NEW.end_date THEN
1194 RAISE EXCEPTION ''START DATE CANNOT BE AFTER END DATE'';
1196 FOR dependon IN SELECT * FROM project_dependon_vw
1197 WHERE project_task_id=NEW.project_task_id LOOP
1198 IF dependon.end_date > NEW.start_date THEN
1199 delta := dependon.end_date-NEW.start_date;
1200 RAISE NOTICE ''Bumping Back: % Delta: % '',NEW.project_task_id,delta;
1201 NEW.start_date := NEW.start_date+delta;
1202 NEW.end_date := NEW.end_date+delta;
1207 ' LANGUAGE 'plpgsql'",
1208 "CREATE TRIGGER projtask_insert_depend_trig BEFORE INSERT OR UPDATE ON project_task
1209 FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend()",
1212 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030312.sql") } ;
1214 foreach my $s (@reqlist) {
1217 $sth = $dbh->prepare ($query) ;
1223 &update_db_version ($target) ;
1224 debug "Committing $target." ;
1228 $version = &get_db_version ;
1229 $target = "2.6-0+checkpoint+29" ;
1230 if (is_lesser $version, $target) {
1231 debug "Registering KDE theme." ;
1233 $query = "INSERT INTO themes (dirname, fullname) VALUES ('kde', 'KDE')";
1235 $sth = $dbh->prepare ($query) ;
1239 &update_db_version ($target) ;
1240 debug "Committing." ;
1245 $version = &get_db_version ;
1246 $target = "2.6-0+checkpoint+30" ;
1247 if (is_lesser $version, $target) {
1248 debug "Registering Dark Aqua theme." ;
1250 $query = "INSERT INTO themes (dirname, fullname) VALUES ('darkaqua', 'Dark Aqua')";
1252 $sth = $dbh->prepare ($query) ;
1256 &update_db_version ($target) ;
1257 debug "Committing." ;
1262 $version = &get_db_version ;
1263 $target = "2.6-0+checkpoint+31" ;
1264 if (is_lesser $version, $target) {
1265 debug "Upgrading with 20030513.sql" ;
1267 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030513.sql") } ;
1268 foreach my $s (@reqlist) {
1271 $sth = $dbh->prepare ($query) ;
1277 &update_db_version ($target) ;
1278 debug "Committing." ;
1283 $version = &get_db_version ;
1285 if (is_lesser $version, $target) {
1286 debug "Database schema is now version 3.0-1." ;
1288 &update_db_version ($target) ;
1289 debug "Committing." ;
1294 $version = &get_db_version ;
1296 if (is_lesser $version, $target) {
1297 debug "Upgrading with 20030822.sql" ;
1300 "DROP TRIGGER artifactgroup_update_trig ON artifact",
1301 "DROP FUNCTION artifactgroup_update_agg ()",
1302 @{ &parse_sql_file ("/usr/lib/gforge/db/20030822.sql") },
1303 "CREATE TRIGGER artifactgroup_update_trig AFTER UPDATE ON artifact FOR EACH ROW EXECUTE PROCEDURE artifactgroup_update_agg()",
1305 foreach my $s (@reqlist) {
1308 $sth = $dbh->prepare ($query) ;
1314 &update_db_version ($target) ;
1315 debug "Committing." ;
1319 debug "It seems your database $action went well and smoothly. That's cool." ;
1320 debug "Please enjoy using Gforge." ;
1322 # There should be a commit at the end of every block above.
1323 # If there is not, then it might be symptomatic of a problem.
1324 # For safety, we roll back.
1329 warn "Transaction aborted because $@" ;
1330 debug "Transaction aborted because $@" ;
1331 debug "Last SQL query was:\n$query\n(end of query)" ;
1333 my $version = &get_db_version ;
1335 debug "Your database schema is at version $version" ;
1337 debug "Couldn't get your database schema version." ;
1339 debug "Please report this bug on the Debian bug-tracking system." ;
1340 debug "Please include the previous messages as well to help debugging." ;
1341 debug "You should not worry too much about this," ;
1342 debug "your DB is still in a consistent state and should be usable." ;
1349 sub is_lesser ( $$ ) {
1350 my $v1 = shift || 0 ;
1351 my $v2 = shift || 0 ;
1353 my $rc = system "dpkg --compare-versions $v1 lt $v2" ;
1358 sub is_greater ( $$ ) {
1359 my $v1 = shift || 0 ;
1360 my $v2 = shift || 0 ;
1362 my $rc = system "dpkg --compare-versions $v1 gt $v2" ;
1367 sub get_pg_version () {
1368 my $command = q(dpkg -s postgresql | awk '/^Version: / { print $2 }') ;
1369 my $version = qx($command) ;
1377 print STDERR "$v\n" ;
1380 sub create_metadata_table ( $ ) {
1381 my $v = shift || "2.5-7+just+before+8" ;
1382 # Do we have the metadata table?
1384 $query = "SELECT count(*) FROM pg_class WHERE relname = 'debian_meta_data' and relkind = 'r'";
1386 my $sth = $dbh->prepare ($query) ;
1388 my @array = $sth->fetchrow_array () ;
1391 # Let's create this table if we have it not
1393 if ($array [0] == 0) {
1394 debug "Creating debian_meta_data table." ;
1395 $query = "CREATE TABLE debian_meta_data (key varchar primary key, value text not null)" ;
1397 $sth = $dbh->prepare ($query) ;
1402 $query = "SELECT count(*) FROM debian_meta_data WHERE key = 'db-version'";
1404 $sth = $dbh->prepare ($query) ;
1406 @array = $sth->fetchrow_array () ;
1409 # Empty table? We'll have to fill it up a bit
1411 if ($array [0] == 0) {
1412 debug "Inserting first data into debian_meta_data table." ;
1413 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('db-version', '$v')" ;
1415 $sth = $dbh->prepare ($query) ;
1421 sub update_db_version ( $ ) {
1422 my $v = shift or die "Not enough arguments" ;
1424 debug "Updating debian_meta_data table." ;
1425 $query = "UPDATE debian_meta_data SET value = '$v' WHERE key = 'db-version'" ;
1427 my $sth = $dbh->prepare ($query) ;
1432 sub get_db_version () {
1433 $query = "SELECT value FROM debian_meta_data WHERE key = 'db-version'" ;
1435 my $sth = $dbh->prepare ($query) ;
1437 my @array = $sth->fetchrow_array () ;
1440 my $version = $array [0] ;
1445 sub drop_table_if_exists ( $ ) {
1446 my $tname = shift or die "Not enough arguments" ;
1447 $query = "SELECT count(*) FROM pg_class WHERE relname='$tname' AND relkind='r'" ;
1448 my $sth = $dbh->prepare ($query) ;
1450 my @array = $sth->fetchrow_array () ;
1453 if ($array [0] != 0) {
1454 # debug "Dropping table $tname" ;
1455 $query = "DROP TABLE $tname" ;
1457 $sth = $dbh->prepare ($query) ;
1463 sub drop_sequence_if_exists ( $ ) {
1464 my $sname = shift or die "Not enough arguments" ;
1465 $query = "SELECT count(*) FROM pg_class WHERE relname='$sname' AND relkind='S'" ;
1466 my $sth = $dbh->prepare ($query) ;
1468 my @array = $sth->fetchrow_array () ;
1471 if ($array [0] != 0) {
1472 # debug "Dropping sequence $sname" ;
1473 $query = "DROP SEQUENCE $sname" ;
1475 $sth = $dbh->prepare ($query) ;
1481 sub drop_index_if_exists ( $ ) {
1482 my $iname = shift or die "Not enough arguments" ;
1483 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='i'" ;
1484 my $sth = $dbh->prepare ($query) ;
1486 my @array = $sth->fetchrow_array () ;
1489 if ($array [0] != 0) {
1490 # debug "Dropping index $iname" ;
1491 $query = "DROP INDEX $iname" ;
1493 $sth = $dbh->prepare ($query) ;
1499 sub drop_view_if_exists ( $ ) {
1500 my $iname = shift or die "Not enough arguments" ;
1501 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='v'" ;
1502 my $sth = $dbh->prepare ($query) ;
1504 my @array = $sth->fetchrow_array () ;
1507 if ($array [0] != 0) {
1508 # debug "Dropping view $iname" ;
1509 $query = "DROP VIEW $iname" ;
1511 $sth = $dbh->prepare ($query) ;
1517 sub bump_sequence_to ( $$ ) {
1518 my ($sth, @array, $seqname, $targetvalue) ;
1521 $targetvalue = shift ;
1524 $query = "select nextval ('$seqname')" ;
1525 $sth = $dbh->prepare ($query) ;
1527 @array = $sth->fetchrow_array () ;
1529 } until $array[0] >= $targetvalue ;