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/sourceforge/lib/include.pl") ; # Include a few predefined functions
31 require ("/usr/lib/sourceforge/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 debug "Updating debian_meta_data table." ;
64 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', 'scratch-to-2.6')" ;
66 $sth = $dbh->prepare ($query) ;
72 } else { # A 'groups' table exists
75 $query = "SELECT count(*) from pg_class where relname = 'debian_meta_data' and relkind = 'r'";
77 $sth = $dbh->prepare ($query) ;
79 @array = $sth->fetchrow_array () ;
82 if ($array[0] == 0) { # No 'debian_meta_data' table
83 # If we're here, we're upgrading from 2.5-7 or earlier
84 # We therefore need to create the table
85 &create_metadata_table ("2.5-7+just+before+8") ;
88 $version = &get_db_version ;
89 if (is_lesser $version, "2.5.9999") {
90 debug "Found an old (2.5) database, will upgrade to 2.6" ;
92 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
94 $sth = $dbh->prepare ($query) ;
96 @array = $sth->fetchrow_array () ;
100 # debug "Updating debian_meta_data table." ;
101 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', '2.5-to-2.6')" ;
103 $sth = $dbh->prepare ($query) ;
106 debug "Committing." ;
112 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
114 $sth = $dbh->prepare ($query) ;
116 @array = $sth->fetchrow_array () ;
119 if ($array[0] == 0) {
122 $query = "SELECT value from debian_meta_data where key = 'current-path'";
124 $sth = $dbh->prepare ($query) ;
126 @array = $sth->fetchrow_array () ;
133 ($path eq 'scratch-to-2.6') && do {
134 $version = &get_db_version ;
135 $target = "2.5.9999.1+global+data+done" ;
136 if (is_lesser $version, $target) {
137 my @filelist = qw{ /usr/lib/sourceforge/db/sf-2.6-complete.sql } ;
138 # TODO: user_rating.sql
140 foreach my $file (@filelist) {
141 debug "Processing $file" ;
142 @reqlist = @{ &parse_sql_file ($file) } ;
144 foreach my $s (@reqlist) {
147 $sth = $dbh->prepare ($query) ;
154 &update_db_version ($target) ;
155 debug "Committing." ;
159 $version = &get_db_version ;
160 $target = "2.5.9999.2+local+data+done" ;
161 if (is_lesser $version, $target) {
162 debug "Adding local data." ;
164 do "/etc/sourceforge/local.pl" or die "Cannot read /etc/sourceforge/local.pl" ;
166 my ($login, $pwd, $md5pwd, $email, $noreplymail, $date) ;
168 $login = $admin_login ;
169 $pwd = $admin_password ;
170 $md5pwd=qx/echo -n $pwd | md5sum/ ;
172 $md5pwd =~ s/(.{32}) .*/$1/ ;
173 $email = $server_admin ;
174 $noreplymail="noreply\@$domain_name" ;
178 "UPDATE groups SET homepage = '$domain_name/admin/' where group_id = 1",
179 "UPDATE groups SET homepage = '$domain_name/news/' where group_id = 2",
180 "UPDATE groups SET homepage = '$domain_name/stats/' where group_id = 3",
181 "UPDATE groups SET homepage = '$domain_name/peerrating/' where group_id = 4",
182 "UPDATE users SET email = '$noreplymail' where user_id = 100",
183 "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)",
184 "SELECT setval ('\"users_pk_seq\"', 102, 'f')",
185 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 1, 'A')",
186 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 2, 'A')",
187 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 3, 'A')",
188 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 4, 'A')"
191 foreach my $s (@reqlist) {
194 $sth = $dbh->prepare ($query) ;
200 &update_db_version ($target) ;
201 debug "Committing." ;
205 $version = &get_db_version ;
206 $target = "2.5.9999.3+skills+done" ;
207 if (is_lesser $version, $target) {
208 debug "Inserting skills." ;
210 foreach my $skill (split /;/, $skill_list) {
211 push @reqlist, "INSERT INTO people_skill (name) VALUES ('$skill')" ;
214 foreach my $s (@reqlist) {
217 $sth = $dbh->prepare ($query) ;
223 &update_db_version ($target) ;
224 debug "Committing." ;
228 $version = &get_db_version ;
229 $target = "2.6-0+checkpoint+1" ;
230 if (is_lesser $version, $target) {
231 debug "Updating debian_meta_data table." ;
232 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
234 $sth = $dbh->prepare ($query) ;
238 &update_db_version ($target) ;
239 debug "Committing." ;
246 ($path eq '2.5-to-2.6') && do {
248 $version = &get_db_version ;
250 if (is_lesser $version, $target) {
251 debug "Adding row to people_job_category." ;
252 $query = "INSERT INTO people_job_category VALUES (100, 'Undefined', 0)" ;
253 $sth = $dbh->prepare ($query) ;
257 &update_db_version ($target) ;
258 debug "Committing." ;
262 $version = &get_db_version ;
264 if (is_lesser $version, $target) {
265 debug "Adding row to supported_languages." ;
266 $query = "INSERT INTO supported_languages VALUES (15, 'Korean', 'Korean.class', 'Korean', 'kr')" ;
267 $sth = $dbh->prepare ($query) ;
271 &update_db_version ($target) ;
272 debug "Committing." ;
276 $version = &get_db_version ;
278 if (is_lesser $version, $target) {
279 debug "Fixing unix_box entries." ;
281 $query = "update groups set unix_box = 'shell'" ;
282 $sth = $dbh->prepare ($query) ;
286 $query = "update users set unix_box = 'shell'" ;
287 $sth = $dbh->prepare ($query) ;
291 debug "Also fixing a few sequences." ;
293 &bump_sequence_to ("bug_pk_seq", 100) ;
294 &bump_sequence_to ("project_task_pk_seq", 100) ;
296 &update_db_version ($target) ;
297 debug "Committing." ;
301 $version = &get_db_version ;
303 if (is_lesser $version, $target) {
304 debug "Adding rows to supported_languages." ;
306 "INSERT INTO supported_languages VALUES (16,'Bulgarian','Bulgarian.class','Bulgarian','bg')",
307 "INSERT INTO supported_languages VALUES (17,'Greek','Greek.class','Greek','el')",
308 "INSERT INTO supported_languages VALUES (18,'Indonesian','Indonesian.class','Indonesian','id')",
309 "INSERT INTO supported_languages VALUES (19,'Portuguese (Brazillian)','PortugueseBrazillian.class','PortugueseBrazillian', 'br')",
310 "INSERT INTO supported_languages VALUES (20,'Polish','Polish.class','Polish','pl')",
311 "INSERT INTO supported_languages VALUES (21,'Portuguese','Portuguese.class','Portuguese', 'pt')",
312 "INSERT INTO supported_languages VALUES (22,'Russian','Russian.class','Russian','ru')"
315 foreach my $s (@reqlist) {
318 $sth = $dbh->prepare ($query) ;
324 &update_db_version ($target) ;
325 debug "Committing." ;
329 $version = &get_db_version ;
331 if (is_lesser $version, $target) {
332 debug "Fixing unix_uid entries." ;
334 $query = "UPDATE users SET unix_uid = nextval ('unix_uid_seq') WHERE unix_status != 'N' AND status != 'P' AND unix_uid = 0" ;
335 $sth = $dbh->prepare ($query) ;
339 &update_db_version ($target) ;
340 debug "Committing." ;
344 $version = &get_db_version ;
345 $target = "2.5.9999.1+temp+data+dropped" ;
346 if (is_lesser $version, $target) {
347 debug "Preparing to upgrade your database - dropping temporary tables" ;
349 my @tables = qw/ user_metric_tmp1_1 user_metric_tmp1_2
350 user_metric_tmp1_3 user_metric_tmp1_4
351 user_metric_tmp1_5 user_metric_tmp1_6
352 user_metric_tmp1_7 user_metric_tmp1_8 user_metric1
353 user_metric2 user_metric3 user_metric4 user_metric5
354 user_metric6 user_metric7 user_metric8
355 project_counts_tmp project_metric_tmp
356 project_metric_tmp1 project_counts_weekly_tmp
357 project_metric_weekly_tmp project_metric_weekly_tmp1
360 my @sequences = qw/ user_metric1_ranking_seq
361 user_metric2_ranking_seq user_metric3_ranking_seq
362 user_metric4_ranking_seq user_metric5_ranking_seq
363 user_metric6_ranking_seq user_metric7_ranking_seq
364 user_metric8_ranking_seq project_metric_weekly_seq
365 trove_treesum_trove_treesum_seq
366 project_metric_tmp1_pk_seq / ;
368 my @indexes = qw/ idx_project_metric_group
369 idx_project_metric_weekly_group
370 user_metric_history_date_userid / ;
372 foreach my $table (@tables) {
373 &drop_table_if_exists ($table) ;
376 foreach my $sequence (@sequences) {
377 &drop_sequence_if_exists ($sequence) ;
380 foreach my $index (@indexes) {
381 &drop_index_if_exists ($index) ;
384 &update_db_version ($target) ;
385 debug "Committing." ;
389 $version = &get_db_version ;
390 $target = "2.5.9999.2+data+upgraded" ;
391 if (is_lesser $version, $target) {
392 debug "Upgrading your database scheme from 2.5" ;
394 @reqlist = @{ &parse_sql_file ("/usr/lib/sourceforge/db/sf2.5-to-sf2.6.sql") } ;
395 foreach my $s (@reqlist) {
398 $sth = $dbh->prepare ($query) ;
404 &update_db_version ($target) ;
405 debug "Committing." ;
409 $version = &get_db_version ;
410 $target = "2.5.9999.3+artifact+transcoded" ;
411 if (is_lesser $version, $target) {
412 debug "Transcoding the artifact data fields" ;
414 $query = "SELECT id,bin_data FROM artifact_file ORDER BY id ASC" ;
416 $sth = $dbh->prepare ($query) ;
418 while (@array = $sth->fetchrow_array) {
419 my $query2 = "UPDATE artifact_file SET bin_data='" ;
420 $query2 .= encode_base64 (decode_entities ($array [1])) ;
421 $query2 .= "' WHERE id=" ;
422 $query2 .= $array [0] ;
425 my $sth2 =$dbh->prepare ($query2) ;
432 &update_db_version ($target) ;
433 debug "Committing." ;
437 $version = &get_db_version ;
438 $target = "2.5.9999.4+groups+inserted" ;
439 if (is_lesser $version, $target) {
440 debug "Inserting missing groups" ;
443 "INSERT INTO groups (group_name, homepage,
444 is_public, status, unix_group_name,
445 unix_box, http_domain, short_description,
446 cvs_box, license, register_purpose,
447 license_other, register_time, rand_hash,
448 use_mail, use_survey, use_forum, use_pm,
449 use_cvs, use_news, type, use_docman,
450 new_task_address, send_all_tasks,
452 VALUES ('Stats', '$domain_name/top/', 0,
453 'A', 'stats', 'shell', NULL, NULL, 'cvs',
454 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
456 "INSERT INTO groups (group_name, homepage,
457 is_public, status, unix_group_name,
458 unix_box, http_domain, short_description,
459 cvs_box, license, register_purpose,
460 license_other, register_time, rand_hash,
461 use_mail, use_survey, use_forum, use_pm,
462 use_cvs, use_news, type, use_docman,
463 new_task_address, send_all_tasks,
465 VALUES ('Peer Ratings', '$domain_name/people/', 0,
466 'A', 'peerrating', 'shell', NULL, NULL, 'cvs1',
467 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
471 foreach my $s (@reqlist) {
474 $sth = $dbh->prepare ($query) ;
479 &update_db_version ($target) ;
480 debug "Committing." ;
484 $version = &get_db_version ;
485 $target = "2.6-0+checkpoint+1" ;
486 if (is_lesser $version, $target) {
487 debug "Database has successfully been converted." ;
488 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
490 $sth = $dbh->prepare ($query) ;
494 &update_db_version ($target) ;
495 debug "Committing." ;
503 $version = &get_db_version ;
504 $target = "2.6-0+checkpoint+2" ;
505 if (is_lesser $version, $target) {
506 debug "Updating permissions on system groups." ;
507 $query = "UPDATE groups SET group_name='Site Admin', is_public=1 WHERE group_id=1" ;
509 $sth = $dbh->prepare ($query) ;
512 $query = "UPDATE groups SET group_name='Site News Admin', is_public=1 WHERE group_id=$sys_news_group" ;
514 $sth = $dbh->prepare ($query) ;
518 &update_db_version ($target) ;
519 debug "Committing." ;
523 $version = &get_db_version ;
524 $target = "2.6-0+checkpoint+3" ;
525 if (is_lesser $version, $target) {
526 debug "Creating table group_cvs_history." ;
527 $query = "CREATE TABLE group_cvs_history (
528 id integer DEFAULT nextval('group_cvs_history_pk_seq'::text) NOT NULL,
529 group_id integer DEFAULT '0' NOT NULL,
530 user_name character varying(80) DEFAULT '' NOT NULL,
531 cvs_commits integer DEFAULT '0' NOT NULL,
532 cvs_commits_wk integer DEFAULT '0' NOT NULL,
533 cvs_adds integer DEFAULT '0' NOT NULL,
534 cvs_adds_wk integer DEFAULT '0' NOT NULL,
537 $sth = $dbh->prepare ($query) ;
541 &update_db_version ($target) ;
542 debug "Committing." ;
546 $version = &get_db_version ;
547 $target = "2.6-0+checkpoint+4" ;
548 if (is_lesser $version, $target) {
549 debug "Registering Savannah themes." ;
551 $query = "SELECT max(theme_id) FROM themes" ;
553 $sth = $dbh->prepare ($query) ;
555 @array = $sth->fetchrow_array () ;
557 my $maxid = $array [0] ;
559 &bump_sequence_to ("themes_pk_seq", $maxid) ;
562 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_codex', 'Savannah CodeX')",
563 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_forest', 'Savannah Forest')",
564 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_reverse', 'Savannah Reverse')",
565 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_sad', 'Savannah Sad')",
566 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_savannah', 'Savannah Original')",
567 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_slashd', 'Savannah SlashDot')",
568 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_startrek', 'Savannah StarTrek')",
569 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_transparent', 'Savannah Transparent')",
570 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_water', 'Savannah Water')",
571 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_www.gnu.org', 'Savannah www.gnu.org')"
573 foreach my $s (@reqlist) {
576 $sth = $dbh->prepare ($query) ;
582 &update_db_version ($target) ;
583 debug "Committing." ;
587 $version = &get_db_version ;
588 $target = "2.6-0+checkpoint+5" ;
589 if (is_lesser $version, $target) {
590 debug "Registering yet another Savannah theme." ;
592 $query = "INSERT INTO themes (dirname, fullname) VALUES ('savannah_darkslate', 'Savannah Dark Slate')";
594 $sth = $dbh->prepare ($query) ;
598 &update_db_version ($target) ;
599 debug "Committing." ;
603 $version = &get_db_version ;
604 $target = "2.6-0+checkpoint+6" ;
605 if (is_lesser $version, $target) {
606 debug "Updating language codes." ;
609 "UPDATE supported_languages SET language_code='en' where classname='English'",
610 "UPDATE supported_languages SET language_code='ja' where classname='Japanese'",
611 "UPDATE supported_languages SET language_code='iw' where classname='Hebrew'",
612 "UPDATE supported_languages SET language_code='es' where classname='Spanish'",
613 "UPDATE supported_languages SET language_code='th' where classname='Thai'",
614 "UPDATE supported_languages SET language_code='de' where classname='German'",
615 "UPDATE supported_languages SET language_code='it' where classname='Italian'",
616 "UPDATE supported_languages SET language_code='no' where classname='Norwegian'",
617 "UPDATE supported_languages SET language_code='sv' where classname='Swedish'",
618 "UPDATE supported_languages SET language_code='zh' where classname='Chinese'",
619 "UPDATE supported_languages SET language_code='nl' where classname='Dutch'",
620 "UPDATE supported_languages SET language_code='eo' where classname='Esperanto'",
621 "UPDATE supported_languages SET language_code='ca' where classname='Catalan'",
622 "UPDATE supported_languages SET language_code='ko' where classname='Korean'",
623 "UPDATE supported_languages SET language_code='bg' where classname='Bulgarian'",
624 "UPDATE supported_languages SET language_code='el' where classname='Greek'",
625 "UPDATE supported_languages SET language_code='id' where classname='Indonesian'",
626 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese (Brazillian)'",
627 "UPDATE supported_languages SET language_code='pl' where classname='Polish'",
628 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese'",
629 "UPDATE supported_languages SET language_code='ru' where classname='Russian'",
630 "UPDATE supported_languages SET language_code='fr' where classname='French'"
632 foreach my $s (@reqlist) {
635 $sth = $dbh->prepare ($query) ;
640 &update_db_version ($target) ;
641 debug "Committing." ;
645 $version = &get_db_version ;
646 $target = "2.6-0+checkpoint+7" ;
647 if (is_lesser $version, $target) {
648 debug "Fixing artifact-related views." ;
650 &drop_view_if_exists ("artifact_file_user_vw") ;
651 &drop_view_if_exists ("artifact_history_user_vw") ;
652 &drop_view_if_exists ("artifact_message_user_vw") ;
653 &drop_view_if_exists ("artifactperm_artgrouplist_vw") ;
654 &drop_view_if_exists ("artifactperm_user_vw") ;
655 &drop_view_if_exists ("artifact_vw") ;
658 "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)",
659 "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)",
660 "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)",
661 "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)",
662 "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)",
663 "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))"
665 foreach my $s (@reqlist) {
668 $sth = $dbh->prepare ($query) ;
673 &update_db_version ($target) ;
674 debug "Committing." ;
678 $version = &get_db_version ;
679 $target = "2.6-0+checkpoint+8" ;
680 if (is_lesser $version, $target) {
681 debug "Adding integrity constraints between the Trove map tables." ;
684 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
685 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
686 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
687 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
688 "ALTER TABLE trove_treesums ADD CONSTRAINT trove_treesums_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
690 foreach my $s (@reqlist) {
693 $sth = $dbh->prepare ($query) ;
698 &update_db_version ($target) ;
699 debug "Committing." ;
703 $version = &get_db_version ;
704 $target = "2.6-0+checkpoint+9" ;
705 if (is_lesser $version, $target) {
706 debug "Adding extra fields to the groups table." ;
709 "ALTER TABLE groups ADD COLUMN use_ftp integer",
710 "ALTER TABLE groups ALTER COLUMN use_ftp SET DEFAULT 1",
711 "UPDATE groups SET use_ftp = 1",
712 "ALTER TABLE groups ADD COLUMN use_tracker integer",
713 "ALTER TABLE groups ALTER COLUMN use_tracker SET DEFAULT 1",
714 "UPDATE groups SET use_tracker = 1",
715 "ALTER TABLE groups ADD COLUMN use_frs integer",
716 "ALTER TABLE groups ALTER COLUMN use_frs SET DEFAULT 1",
717 "UPDATE groups SET use_frs = 1",
718 "ALTER TABLE groups ADD COLUMN use_stats integer",
719 "ALTER TABLE groups ALTER COLUMN use_stats SET DEFAULT 1",
720 "UPDATE groups SET use_stats = 1",
721 "ALTER TABLE groups ADD COLUMN enable_pserver integer",
722 "ALTER TABLE groups ALTER COLUMN enable_pserver SET DEFAULT 1",
723 "UPDATE groups SET enable_pserver = 1",
724 "ALTER TABLE groups ADD COLUMN enable_anoncvs integer",
725 "ALTER TABLE groups ALTER COLUMN enable_anoncvs SET DEFAULT 1",
726 "UPDATE groups SET enable_anoncvs = 1",
728 foreach my $s (@reqlist) {
731 $sth = $dbh->prepare ($query) ;
736 &update_db_version ($target) ;
737 debug "Committing." ;
741 $version = &get_db_version ;
742 $target = "2.6-0+checkpoint+10" ;
743 if (is_lesser $version, $target) {
744 debug "Updating supported_languages table." ;
747 "ALTER TABLE supported_languages RENAME TO supported_languages_old",
748 "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))",
749 "INSERT INTO supported_languages SELECT * FROM supported_languages_old",
750 "DROP TABLE supported_languages_old",
751 "ALTER TABLE supported_languages ADD CONSTRAINT supported_languages_pkey PRIMARY KEY (language_id)",
752 "ALTER TABLE users ADD CONSTRAINT users_languageid_fk FOREIGN KEY (language) REFERENCES supported_languages(language_id) MATCH FULL",
753 "ALTER TABLE doc_data ADD CONSTRAINT docdata_languageid_fk FOREIGN KEY (language_id) REFERENCES supported_languages(language_id) MATCH FULL",
754 "UPDATE supported_languages SET language_code='pt_BR', classname='PortugueseBrazilian', name='Pt. Brazilian', filename='PortugueseBrazilian.class' where classname='PortugueseBrazillian'",
756 foreach my $s (@reqlist) {
759 $sth = $dbh->prepare ($query) ;
764 &update_db_version ($target) ;
765 debug "Committing." ;
770 debug "It seems your database $action went well and smoothly. That's cool." ;
771 debug "Please enjoy using Debian Sourceforge." ;
773 # There should be a commit at the end of every block above.
774 # If there is not, then it might be symptomatic of a problem.
775 # For safety, we roll back.
780 warn "Transaction aborted because $@" ;
781 debug "Transaction aborted because $@" ;
782 debug "Last SQL query was:\n$query\n(end of query)" ;
784 debug "Please report this bug on the Debian bug-tracking system." ;
785 debug "Please include the previous messages as well to help debugging." ;
786 debug "You should not worry too much about this," ;
787 debug "your DB is still in a consistent state and should be usable." ;
794 sub is_lesser ( $$ ) {
795 my $v1 = shift || 0 ;
796 my $v2 = shift || 0 ;
798 my $rc = system "dpkg --compare-versions $v1 lt $v2" ;
803 sub is_greater ( $$ ) {
804 my $v1 = shift || 0 ;
805 my $v2 = shift || 0 ;
807 my $rc = system "dpkg --compare-versions $v1 gt $v2" ;
815 print STDERR "$v\n" ;
818 sub create_metadata_table ( $ ) {
819 my $v = shift || "2.5-7+just+before+8" ;
820 # Do we have the metadata table?
822 $query = "SELECT count(*) FROM pg_class WHERE relname = 'debian_meta_data' and relkind = 'r'";
824 my $sth = $dbh->prepare ($query) ;
826 my @array = $sth->fetchrow_array () ;
829 # Let's create this table if we have it not
831 if ($array [0] == 0) {
832 debug "Creating debian_meta_data table." ;
833 $query = "CREATE TABLE debian_meta_data (key varchar primary key, value text not null)" ;
835 $sth = $dbh->prepare ($query) ;
840 $query = "SELECT count(*) FROM debian_meta_data WHERE key = 'db-version'";
842 $sth = $dbh->prepare ($query) ;
844 @array = $sth->fetchrow_array () ;
847 # Empty table? We'll have to fill it up a bit
849 if ($array [0] == 0) {
850 debug "Inserting first data into debian_meta_data table." ;
851 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('db-version', '$v')" ;
853 $sth = $dbh->prepare ($query) ;
859 sub update_db_version ( $ ) {
860 my $v = shift or die "Not enough arguments" ;
862 debug "Updating debian_meta_data table." ;
863 $query = "UPDATE debian_meta_data SET value = '$v' WHERE key = 'db-version'" ;
865 my $sth = $dbh->prepare ($query) ;
870 sub get_db_version () {
871 $query = "SELECT value FROM debian_meta_data WHERE key = 'db-version'" ;
873 my $sth = $dbh->prepare ($query) ;
875 my @array = $sth->fetchrow_array () ;
878 my $version = $array [0] ;
883 sub drop_table_if_exists ( $ ) {
884 my $tname = shift or die "Not enough arguments" ;
885 $query = "SELECT count(*) FROM pg_class WHERE relname='$tname' AND relkind='r'" ;
886 my $sth = $dbh->prepare ($query) ;
888 my @array = $sth->fetchrow_array () ;
891 if ($array [0] != 0) {
892 # debug "Dropping table $tname" ;
893 $query = "DROP TABLE $tname" ;
895 $sth = $dbh->prepare ($query) ;
901 sub drop_sequence_if_exists ( $ ) {
902 my $sname = shift or die "Not enough arguments" ;
903 $query = "SELECT count(*) FROM pg_class WHERE relname='$sname' AND relkind='S'" ;
904 my $sth = $dbh->prepare ($query) ;
906 my @array = $sth->fetchrow_array () ;
909 if ($array [0] != 0) {
910 # debug "Dropping sequence $sname" ;
911 $query = "DROP SEQUENCE $sname" ;
913 $sth = $dbh->prepare ($query) ;
919 sub drop_index_if_exists ( $ ) {
920 my $iname = shift or die "Not enough arguments" ;
921 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='i'" ;
922 my $sth = $dbh->prepare ($query) ;
924 my @array = $sth->fetchrow_array () ;
927 if ($array [0] != 0) {
928 # debug "Dropping index $iname" ;
929 $query = "DROP INDEX $iname" ;
931 $sth = $dbh->prepare ($query) ;
937 sub drop_view_if_exists ( $ ) {
938 my $iname = shift or die "Not enough arguments" ;
939 $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='v'" ;
940 my $sth = $dbh->prepare ($query) ;
942 my @array = $sth->fetchrow_array () ;
945 if ($array [0] != 0) {
946 # debug "Dropping view $iname" ;
947 $query = "DROP VIEW $iname" ;
949 $sth = $dbh->prepare ($query) ;
955 sub bump_sequence_to ( $$ ) {
956 my ($sth, @array, $seqname, $targetvalue) ;
959 $targetvalue = shift ;
962 $query = "select nextval ('$seqname')" ;
963 $sth = $dbh->prepare ($query) ;
965 @array = $sth->fetchrow_array () ;
967 } until $array[0] >= $targetvalue ;