5 # Debian-specific script to upgrade the database between releases
6 # Roland Mas <lolando@debian.org>
15 use vars qw/$dbh @reqlist/ ;
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 ($query, $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 $email = $server_admin ;
173 $noreplymail="noreply\@$domain_name" ;
177 "UPDATE groups SET homepage = '$domain_name/admin/' where group_id = 1",
178 "UPDATE groups SET homepage = '$domain_name/news/' where group_id = 2",
179 "UPDATE groups SET homepage = '$domain_name/stats/' where group_id = 3",
180 "UPDATE groups SET homepage = '$domain_name/peerrating/' where group_id = 4",
181 "UPDATE users SET email = '$noreplymail' where user_id = 100",
182 "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)",
183 "SELECT setval ('\"users_pk_seq\"', 102, 'f')",
184 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 1, 'A')",
185 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 2, 'A')",
186 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 3, 'A')",
187 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 4, 'A')"
190 foreach my $s (@reqlist) {
193 $sth = $dbh->prepare ($query) ;
199 &update_db_version ($target) ;
200 debug "Committing." ;
204 $version = &get_db_version ;
205 $target = "2.5.9999.3+skills+done" ;
206 if (is_lesser $version, $target) {
207 debug "Inserting skills." ;
209 foreach my $skill (split /;/, $skill_list) {
210 push @reqlist, "INSERT INTO people_skill (name) VALUES ('$skill')" ;
213 foreach my $s (@reqlist) {
216 $sth = $dbh->prepare ($query) ;
222 &update_db_version ($target) ;
223 debug "Committing." ;
227 $version = &get_db_version ;
228 $target = "2.6-0+checkpoint+1" ;
229 if (is_lesser $version, $target) {
230 debug "Updating debian_meta_data table." ;
231 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
233 $sth = $dbh->prepare ($query) ;
237 &update_db_version ($target) ;
238 debug "Committing." ;
245 ($path eq '2.5-to-2.6') && do {
247 $version = &get_db_version ;
249 if (is_lesser $version, $target) {
250 debug "Adding row to people_job_category." ;
251 $query = "INSERT INTO people_job_category VALUES (100, 'Undefined', 0)" ;
252 $sth = $dbh->prepare ($query) ;
256 &update_db_version ($target) ;
257 debug "Committing." ;
261 $version = &get_db_version ;
263 if (is_lesser $version, $target) {
264 debug "Adding row to supported_languages." ;
265 $query = "INSERT INTO supported_languages VALUES (15, 'Korean', 'Korean.class', 'Korean', 'kr')" ;
266 $sth = $dbh->prepare ($query) ;
270 &update_db_version ($target) ;
271 debug "Committing." ;
275 $version = &get_db_version ;
277 if (is_lesser $version, $target) {
278 debug "Fixing unix_box entries." ;
280 $query = "update groups set unix_box = 'shell'" ;
281 $sth = $dbh->prepare ($query) ;
285 $query = "update users set unix_box = 'shell'" ;
286 $sth = $dbh->prepare ($query) ;
290 debug "Also fixing a few sequences." ;
292 &bump_sequence_to ("bug_pk_seq", 100) ;
293 &bump_sequence_to ("project_task_pk_seq", 100) ;
295 &update_db_version ($target) ;
296 debug "Committing." ;
300 $version = &get_db_version ;
301 $target = "2.5.9999.1+temp+data+dropped" ;
302 if (is_lesser $version, $target) {
303 debug "Preparing to upgrade your database - dropping temporary tables" ;
305 my @tables = qw/ user_metric_tmp1_1 user_metric_tmp1_2
306 user_metric_tmp1_3 user_metric_tmp1_4
307 user_metric_tmp1_5 user_metric_tmp1_6
308 user_metric_tmp1_7 user_metric_tmp1_8 user_metric1
309 user_metric2 user_metric3 user_metric4 user_metric5
310 user_metric6 user_metric7 user_metric8
311 project_counts_tmp project_metric_tmp
312 project_metric_tmp1 project_counts_weekly_tmp
313 project_metric_weekly_tmp project_metric_weekly_tmp1
316 my @sequences = qw/ user_metric1_ranking_seq
317 user_metric2_ranking_seq user_metric3_ranking_seq
318 user_metric4_ranking_seq user_metric5_ranking_seq
319 user_metric6_ranking_seq user_metric7_ranking_seq
320 user_metric8_ranking_seq project_metric_weekly_seq
321 trove_treesum_trove_treesum_seq
322 project_metric_tmp1_pk_seq / ;
324 my @indexes = qw/ idx_project_metric_group
325 idx_project_metric_weekly_group
326 user_metric_history_date_userid / ;
328 foreach my $table (@tables) {
329 &drop_table_if_exists ($table) ;
332 foreach my $sequence (@sequences) {
333 &drop_sequence_if_exists ($sequence) ;
336 foreach my $index (@indexes) {
337 &drop_index_if_exists ($index) ;
340 &update_db_version ($target) ;
341 debug "Committing." ;
345 $version = &get_db_version ;
346 $target = "2.5.9999.2+data+upgraded" ;
347 if (is_lesser $version, $target) {
348 debug "Upgrading your database scheme from 2.5" ;
350 @reqlist = @{ &parse_sql_file ("/usr/lib/sourceforge/db/sf2.5-to-sf2.6.sql") } ;
351 foreach my $s (@reqlist) {
354 $sth = $dbh->prepare ($query) ;
360 &update_db_version ($target) ;
361 debug "Committing." ;
365 $version = &get_db_version ;
366 $target = "2.5.9999.3+artifact+transcoded" ;
367 if (is_lesser $version, $target) {
368 debug "Transcoding the artifact data fields" ;
370 $query = "SELECT id,bin_data FROM artifact_file ORDER BY id ASC" ;
372 $sth = $dbh->prepare ($query) ;
374 while (@array = $sth->fetchrow_array) {
375 my $query2 = "UPDATE artifact_file SET bin_data='" ;
376 $query2 .= encode_base64 (decode_entities ($array [1])) ;
377 $query2 .= "' WHERE id=" ;
378 $query2 .= $array [0] ;
381 my $sth2 =$dbh->prepare ($query2) ;
388 &update_db_version ($target) ;
389 debug "Committing." ;
393 $version = &get_db_version ;
394 $target = "2.5.9999.4+groups+inserted" ;
395 if (is_lesser $version, $target) {
396 debug "Inserting missing groups" ;
399 "INSERT INTO groups (group_name, homepage,
400 is_public, status, unix_group_name,
401 unix_box, http_domain, short_description,
402 cvs_box, license, register_purpose,
403 license_other, register_time, rand_hash,
404 use_mail, use_survey, use_forum, use_pm,
405 use_cvs, use_news, type, use_docman,
406 new_task_address, send_all_tasks,
408 VALUES ('Stats', '$domain_name/top/', 0,
409 'A', 'stats', 'shell', NULL, NULL, 'cvs',
410 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
412 "INSERT INTO groups (group_name, homepage,
413 is_public, status, unix_group_name,
414 unix_box, http_domain, short_description,
415 cvs_box, license, register_purpose,
416 license_other, register_time, rand_hash,
417 use_mail, use_survey, use_forum, use_pm,
418 use_cvs, use_news, type, use_docman,
419 new_task_address, send_all_tasks,
421 VALUES ('Peer Ratings', '$domain_name/people/', 0,
422 'A', 'peerrating', 'shell', NULL, NULL, 'cvs1',
423 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
427 foreach my $s (@reqlist) {
430 $sth = $dbh->prepare ($query) ;
435 &update_db_version ($target) ;
436 debug "Committing." ;
440 $version = &get_db_version ;
441 $target = "2.6-0+checkpoint+1" ;
442 if (is_lesser $version, $target) {
443 debug "Database has successfully been converted." ;
444 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
446 $sth = $dbh->prepare ($query) ;
450 &update_db_version ($target) ;
451 debug "Committing." ;
459 $version = &get_db_version ;
460 $target = "2.6-0+checkpoint+2" ;
461 if (is_lesser $version, $target) {
462 debug "Updating permissions on system groups." ;
463 $query = "UPDATE groups SET group_name='Site Admin', is_public=1 WHERE group_id=1" ;
465 $sth = $dbh->prepare ($query) ;
468 $query = "UPDATE groups SET group_name='Site News Admin', is_public=1 WHERE group_id=$sys_news_group" ;
470 $sth = $dbh->prepare ($query) ;
474 &update_db_version ($target) ;
475 debug "Committing." ;
479 $version = &get_db_version ;
480 $target = "2.6-0+checkpoint+3" ;
481 if (is_lesser $version, $target) {
482 debug "Creating table group_cvs_history." ;
483 $query = "CREATE TABLE group_cvs_history (
484 id integer DEFAULT nextval('group_cvs_history_pk_seq'::text) NOT NULL,
485 group_id integer DEFAULT '0' NOT NULL,
486 user_name character varying(80) DEFAULT '' NOT NULL,
487 cvs_commits integer DEFAULT '0' NOT NULL,
488 cvs_commits_wk integer DEFAULT '0' NOT NULL,
489 cvs_adds integer DEFAULT '0' NOT NULL,
490 cvs_adds_wk integer DEFAULT '0' NOT NULL,
493 $sth = $dbh->prepare ($query) ;
497 &update_db_version ($target) ;
498 debug "Committing." ;
502 $version = &get_db_version ;
503 $target = "2.6-0+checkpoint+4" ;
504 if (is_lesser $version, $target) {
505 debug "Registering Savannah themes." ;
507 $query = "SELECT max(theme_id) FROM themes" ;
509 $sth = $dbh->prepare ($query) ;
511 @array = $sth->fetchrow_array () ;
513 my $maxid = $array [0] ;
515 &bump_sequence_to ("themes_pk_seq", $maxid) ;
518 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_codex', 'Savannah CodeX') ;",
519 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_forest', 'Savannah Forest') ;",
520 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_reverse', 'Savannah Reverse') ;",
521 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_sad', 'Savannah Sad') ;",
522 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_savannah', 'Savannah Original') ;",
523 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_slashd', 'Savannah SlashDot') ;",
524 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_startrek', 'Savannah StarTrek') ;",
525 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_transparent', 'Savannah Transparent') ;",
526 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_water', 'Savannah Water') ;",
527 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_www.gnu.org', 'Savannah www.gnu.org') ;"
529 foreach my $s (@reqlist) {
532 $sth = $dbh->prepare ($query) ;
538 &update_db_version ($target) ;
539 debug "Committing." ;
543 debug "It seems your database $action went well and smoothly. That's cool." ;
544 debug "Please enjoy using Debian Sourceforge." ;
546 # There should be a commit at the end of every block above.
547 # If there is not, then it might be symptomatic of a problem.
548 # For safety, we roll back.
553 warn "Transaction aborted because $@" ;
554 debug "Transaction aborted because $@" ;
556 debug "Please report this bug on the Debian bug-tracking system." ;
557 debug "Please include the previous messages as well to help debugging." ;
558 debug "You should not worry too much about this," ;
559 debug "your DB is still in a consistent state and should be usable." ;
566 sub is_lesser ( $$ ) {
567 my $v1 = shift || 0 ;
568 my $v2 = shift || 0 ;
570 my $rc = system "dpkg --compare-versions $v1 lt $v2" ;
575 sub is_greater ( $$ ) {
576 my $v1 = shift || 0 ;
577 my $v2 = shift || 0 ;
579 my $rc = system "dpkg --compare-versions $v1 gt $v2" ;
587 print STDERR "$v\n" ;
590 sub create_metadata_table ( $ ) {
591 my $v = shift || "2.5-7+just+before+8" ;
592 # Do we have the metadata table?
594 my $query = "SELECT count(*) FROM pg_class WHERE relname = 'debian_meta_data' and relkind = 'r'";
596 my $sth = $dbh->prepare ($query) ;
598 my @array = $sth->fetchrow_array () ;
601 # Let's create this table if we have it not
603 if ($array [0] == 0) {
604 debug "Creating debian_meta_data table." ;
605 $query = "CREATE TABLE debian_meta_data (key varchar primary key, value text not null)" ;
607 $sth = $dbh->prepare ($query) ;
612 $query = "SELECT count(*) FROM debian_meta_data WHERE key = 'db-version'";
614 $sth = $dbh->prepare ($query) ;
616 @array = $sth->fetchrow_array () ;
619 # Empty table? We'll have to fill it up a bit
621 if ($array [0] == 0) {
622 debug "Inserting first data into debian_meta_data table." ;
623 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('db-version', '$v')" ;
625 $sth = $dbh->prepare ($query) ;
631 sub update_db_version ( $ ) {
632 my $v = shift or die "Not enough arguments" ;
634 debug "Updating debian_meta_data table." ;
635 my $query = "UPDATE debian_meta_data SET value = '$v' WHERE key = 'db-version'" ;
637 my $sth = $dbh->prepare ($query) ;
642 sub get_db_version () {
643 my $query = "SELECT value FROM debian_meta_data WHERE key = 'db-version'" ;
645 my $sth = $dbh->prepare ($query) ;
647 my @array = $sth->fetchrow_array () ;
650 my $version = $array [0] ;
655 sub drop_table_if_exists ( $ ) {
656 my $tname = shift or die "Not enough arguments" ;
657 my $query = "SELECT count(*) FROM pg_class WHERE relname='$tname' AND relkind='r'" ;
658 my $sth = $dbh->prepare ($query) ;
660 my @array = $sth->fetchrow_array () ;
663 if ($array [0] != 0) {
664 # debug "Dropping table $tname" ;
665 $query = "DROP TABLE $tname" ;
667 $sth = $dbh->prepare ($query) ;
673 sub drop_sequence_if_exists ( $ ) {
674 my $sname = shift or die "Not enough arguments" ;
675 my $query = "SELECT count(*) FROM pg_class WHERE relname='$sname' AND relkind='S'" ;
676 my $sth = $dbh->prepare ($query) ;
678 my @array = $sth->fetchrow_array () ;
681 if ($array [0] != 0) {
682 # debug "Dropping sequence $sname" ;
683 $query = "DROP SEQUENCE $sname" ;
685 $sth = $dbh->prepare ($query) ;
691 sub drop_index_if_exists ( $ ) {
692 my $iname = shift or die "Not enough arguments" ;
693 my $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='i'" ;
694 my $sth = $dbh->prepare ($query) ;
696 my @array = $sth->fetchrow_array () ;
699 if ($array [0] != 0) {
700 # debug "Dropping index $iname" ;
701 $query = "DROP INDEX $iname" ;
703 $sth = $dbh->prepare ($query) ;
709 sub bump_sequence_to ( $$ ) {
710 my ($query, $sth, @array, $seqname, $targetvalue) ;
713 $targetvalue = shift ;
716 $query = "select nextval ('$seqname')" ;
717 $sth = $dbh->prepare ($query) ;
719 @array = $sth->fetchrow_array () ;
721 } until $array[0] >= $targetvalue ;