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_scm_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 require ("/etc/gforge/local.pl") ;
26 require ("/usr/lib/gforge/lib/sqlparser.pm") ; # Our magic SQL parser
27 require ("/usr/lib/gforge/lib/sqlhelper.pm") ; # Our SQL functions
29 &debug ("You'll see some debugging info during this installation.") ;
30 &debug ("Do not worry unless told otherwise.") ;
32 if ( "$sys_dbname" ne "gforge" || "$sys_dbuser" ne "gforge" ) {
33 $dbh ||= DBI->connect("DBI:Pg:dbname=$sys_dbname","$sys_dbuser","$sys_dbpasswd");
35 $dbh ||= DBI->connect("DBI:Pg:dbname=$sys_dbname");
37 die "Cannot connect to database: $!" if ( ! $dbh );
39 # debug "Connected to the database OK." ;
41 $dbh->{AutoCommit} = 0;
42 $dbh->{RaiseError} = 1;
44 my ($sth, @array, $version, $action, $path, $target) ;
46 # Do we have at least the basic schema?
48 $query = "SELECT count(*) from pg_class where relname = 'groups' and relkind = 'r'";
50 $sth = $dbh->prepare ($query) ;
52 @array = $sth->fetchrow_array () ;
55 # Create Sourceforge database
57 if ($array [0] == 0) { # No 'groups' table
58 # Installing SF 2.6 from scratch
59 $action = "installation" ;
60 &debug ("Creating initial Sourceforge database from files.") ;
62 &create_metadata_table ("2.5.9999") ;
64 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
66 $sth = $dbh->prepare ($query) ;
68 @array = $sth->fetchrow_array () ;
71 &debug ("Updating debian_meta_data table.") ;
72 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', 'scratch-to-2.6')" ;
74 $sth = $dbh->prepare ($query) ;
78 &debug ("Committing.") ;
81 } else { # A 'groups' table exists
84 $query = "SELECT count(*) from pg_class where relname = 'debian_meta_data' and relkind = 'r'";
86 $sth = $dbh->prepare ($query) ;
88 @array = $sth->fetchrow_array () ;
91 if ($array[0] == 0) { # No 'debian_meta_data' table
92 # If we're here, we're upgrading from 2.5-7 or earlier
93 # We therefore need to create the table
94 &create_metadata_table ("2.5-7+just+before+8") ;
97 $version = &get_db_version ;
98 if (&is_lesser ($version, "2.5.9999")) {
99 &debug ("Found an old (2.5) database, will upgrade to 2.6") ;
101 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
103 $sth = $dbh->prepare ($query) ;
105 @array = $sth->fetchrow_array () ;
108 if ($array[0] == 0) {
109 # &debug ("Updating debian_meta_data table.") ;
110 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', '2.5-to-2.6')" ;
112 $sth = $dbh->prepare ($query) ;
115 &debug ("Committing.") ;
121 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
123 $sth = $dbh->prepare ($query) ;
125 @array = $sth->fetchrow_array () ;
128 if ($array[0] == 0) {
131 $query = "SELECT value from debian_meta_data where key = 'current-path'";
133 $sth = $dbh->prepare ($query) ;
135 @array = $sth->fetchrow_array () ;
142 ($path eq 'scratch-to-2.6') && do {
143 $version = &get_db_version ;
144 $target = "2.5.9999.1+global+data+done" ;
145 if (&is_lesser ($version, $target)) {
146 my @filelist = qw{ /usr/lib/gforge/db/sf-2.6-complete.sql } ;
147 # TODO: user_rating.sql
149 foreach my $file (@filelist) {
150 &debug ("Processing $file") ;
151 @reqlist = @{ &parse_sql_file ($file) } ;
153 foreach my $s (@reqlist) {
156 $sth = $dbh->prepare ($query) ;
163 &update_db_version ($target) ;
164 &debug ("Committing.") ;
168 $version = &get_db_version ;
169 $target = "2.5.9999.2+local+data+done" ;
170 if (&is_lesser ($version, $target)) {
171 &debug ("Adding local data.") ;
173 do "/etc/gforge/local.pl" or die "Cannot read /etc/gforge/local.pl" ;
175 my ($login, $pwd, $md5pwd, $email, $noreplymail, $date) ;
177 $login = $admin_login ;
178 $pwd = $admin_password ;
179 $md5pwd=qx/echo -n $pwd | md5sum/ ;
181 $md5pwd =~ s/(.{32}) .*/$1/ ;
182 $email = $server_admin ;
183 $noreplymail="noreply\@$domain_name" ;
187 "UPDATE groups SET homepage = '$domain_name/admin/' where group_id = 1",
188 "UPDATE groups SET homepage = '$domain_name/news/' where group_id = 2",
189 "UPDATE groups SET homepage = '$domain_name/stats/' where group_id = 3",
190 "UPDATE groups SET homepage = '$domain_name/peerrating/' where group_id = 4",
191 "UPDATE users SET email = '$noreplymail' where user_id = 100",
192 "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)",
193 "SELECT setval ('\"users_pk_seq\"', 102, 'f')",
194 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 1, 'A')",
195 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 2, 'A')",
196 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 3, 'A')",
197 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 4, 'A')"
200 foreach my $s (@reqlist) {
203 $sth = $dbh->prepare ($query) ;
209 &update_db_version ($target) ;
210 &debug ("Committing.") ;
214 $version = &get_db_version ;
215 $target = "2.5.9999.3+skills+done" ;
216 if (&is_lesser ($version, $target)) {
217 &debug ("Inserting skills.") ;
219 foreach my $skill (split /;/, $skill_list) {
220 push @reqlist, "INSERT INTO people_skill (name) VALUES ('$skill')" ;
223 foreach my $s (@reqlist) {
226 $sth = $dbh->prepare ($query) ;
232 &update_db_version ($target) ;
233 &debug ("Committing.") ;
237 $version = &get_db_version ;
238 $target = "2.6-0+checkpoint+1" ;
239 if (&is_lesser ($version, $target)) {
240 &debug ("Updating debian_meta_data table.") ;
241 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
243 $sth = $dbh->prepare ($query) ;
247 &update_db_version ($target) ;
248 &debug ("Committing.") ;
255 ($path eq '2.5-to-2.6') && do {
257 $version = &get_db_version ;
259 if (&is_lesser ($version, $target)) {
260 &debug ("Adding row to people_job_category.") ;
261 $query = "INSERT INTO people_job_category VALUES (100, 'Undefined', 0)" ;
262 $sth = $dbh->prepare ($query) ;
266 &update_db_version ($target) ;
267 &debug ("Committing.") ;
271 $version = &get_db_version ;
273 if (&is_lesser ($version, $target)) {
274 &debug ("Adding row to supported_languages.") ;
275 $query = "INSERT INTO supported_languages VALUES (15, 'Korean', 'Korean.class', 'Korean', 'kr')" ;
276 $sth = $dbh->prepare ($query) ;
280 &update_db_version ($target) ;
281 &debug ("Committing.") ;
285 $version = &get_db_version ;
287 if (&is_lesser ($version, $target)) {
288 &debug ("Fixing unix_box entries.") ;
290 $query = "update groups set unix_box = 'shell'" ;
291 $sth = $dbh->prepare ($query) ;
295 $query = "update users set unix_box = 'shell'" ;
296 $sth = $dbh->prepare ($query) ;
300 &debug ("Also fixing a few sequences.") ;
302 &bump_sequence_to ($dbh, "bug_pk_seq", 100) ;
303 &bump_sequence_to ($dbh, "project_task_pk_seq", 100) ;
305 &update_db_version ($target) ;
306 &debug ("Committing.") ;
310 $version = &get_db_version ;
312 if (&is_lesser ($version, $target)) {
313 &debug ("Adding rows to supported_languages.") ;
315 "INSERT INTO supported_languages VALUES (16,'Bulgarian','Bulgarian.class','Bulgarian','bg')",
316 "INSERT INTO supported_languages VALUES (17,'Greek','Greek.class','Greek','el')",
317 "INSERT INTO supported_languages VALUES (18,'Indonesian','Indonesian.class','Indonesian','id')",
318 "INSERT INTO supported_languages VALUES (19,'Portuguese (Brazillian)','PortugueseBrazillian.class','PortugueseBrazillian', 'br')",
319 "INSERT INTO supported_languages VALUES (20,'Polish','Polish.class','Polish','pl')",
320 "INSERT INTO supported_languages VALUES (21,'Portuguese','Portuguese.class','Portuguese', 'pt')",
321 "INSERT INTO supported_languages VALUES (22,'Russian','Russian.class','Russian','ru')"
324 foreach my $s (@reqlist) {
327 $sth = $dbh->prepare ($query) ;
333 &update_db_version ($target) ;
334 &debug ("Committing.") ;
338 $version = &get_db_version ;
340 if (&is_lesser ($version, $target)) {
341 &debug ("Fixing unix_uid entries.") ;
343 $query = "UPDATE users SET unix_uid = nextval ('unix_uid_seq') WHERE unix_status != 'N' AND status != 'P' AND unix_uid = 0" ;
344 $sth = $dbh->prepare ($query) ;
348 &update_db_version ($target) ;
349 &debug ("Committing.") ;
353 $version = &get_db_version ;
354 $target = "2.5.9999.1+temp+data+dropped" ;
355 if (&is_lesser ($version, $target)) {
356 &debug ("Preparing to upgrade your database - dropping temporary tables") ;
358 my @tables = qw/ user_metric_tmp1_1 user_metric_tmp1_2
359 user_metric_tmp1_3 user_metric_tmp1_4
360 user_metric_tmp1_5 user_metric_tmp1_6
361 user_metric_tmp1_7 user_metric_tmp1_8 user_metric1
362 user_metric2 user_metric3 user_metric4 user_metric5
363 user_metric6 user_metric7 user_metric8
364 project_counts_tmp project_metric_tmp
365 project_metric_tmp1 project_counts_weekly_tmp
366 project_metric_weekly_tmp project_metric_weekly_tmp1
369 my @sequences = qw/ user_metric1_ranking_seq
370 user_metric2_ranking_seq user_metric3_ranking_seq
371 user_metric4_ranking_seq user_metric5_ranking_seq
372 user_metric6_ranking_seq user_metric7_ranking_seq
373 user_metric8_ranking_seq project_metric_weekly_seq
374 trove_treesum_trove_treesum_seq
375 project_metric_tmp1_pk_seq / ;
377 my @indexes = qw/ idx_project_metric_group
378 idx_project_metric_weekly_group
379 user_metric_history_date_userid / ;
381 foreach my $table (@tables) {
382 &drop_table_if_exists ($dbh, $table) ;
385 foreach my $sequence (@sequences) {
386 &drop_sequence_if_exists ($dbh, $sequence) ;
389 foreach my $index (@indexes) {
390 &drop_index_if_exists ($dbh, $index) ;
393 &update_db_version ($target) ;
394 &debug ("Committing.") ;
398 $version = &get_db_version ;
399 $target = "2.5.9999.2+data+upgraded" ;
400 if (&is_lesser ($version, $target)) {
401 &debug ("Upgrading your database scheme from 2.5") ;
403 my $pg_version = &get_pg_version ;
405 if (&is_lesser ($pg_version, "7.3")) {
407 "DROP INDEX groups_pkey",
408 "DROP INDEX users_pkey",
412 "ALTER TABLE groups DROP CONSTRAINT groups_pkey",
413 "ALTER TABLE users DROP CONSTRAINT users_pkey",
416 foreach my $s (@reqlist) {
419 $sth = $dbh->prepare ($query) ;
424 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/sf2.5-to-sf2.6.sql") } ;
425 foreach my $s (@reqlist) {
428 $sth = $dbh->prepare ($query) ;
434 &update_db_version ($target) ;
435 &debug ("Committing.") ;
439 $version = &get_db_version ;
440 $target = "2.5.9999.3+artifact+transcoded" ;
441 if (&is_lesser ($version, $target)) {
442 &debug ("Transcoding the artifact data fields") ;
444 $query = "SELECT id,bin_data FROM artifact_file ORDER BY id ASC" ;
446 $sth = $dbh->prepare ($query) ;
448 while (@array = $sth->fetchrow_array) {
449 my $query2 = "UPDATE artifact_file SET bin_data='" ;
450 $query2 .= encode_base64 (decode_entities ($array [1])) ;
451 $query2 .= "' WHERE id=" ;
452 $query2 .= $array [0] ;
455 my $sth2 =$dbh->prepare ($query2) ;
462 &update_db_version ($target) ;
463 &debug ("Committing.") ;
467 $version = &get_db_version ;
468 $target = "2.5.9999.4+groups+inserted" ;
469 if (&is_lesser ($version, $target)) {
470 &debug ("Inserting missing groups") ;
473 "INSERT INTO groups (group_name, homepage,
474 is_public, status, unix_group_name,
475 unix_box, http_domain, short_description,
476 cvs_box, license, register_purpose,
477 license_other, register_time, rand_hash,
478 use_mail, use_survey, use_forum, use_pm,
479 use_cvs, use_news, type, use_docman,
480 new_task_address, send_all_tasks,
482 VALUES ('Stats', '$domain_name/top/', 0,
483 'A', 'stats', 'shell', NULL, NULL, 'cvs',
484 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
486 "INSERT INTO groups (group_name, homepage,
487 is_public, status, unix_group_name,
488 unix_box, http_domain, short_description,
489 cvs_box, license, register_purpose,
490 license_other, register_time, rand_hash,
491 use_mail, use_survey, use_forum, use_pm,
492 use_cvs, use_news, type, use_docman,
493 new_task_address, send_all_tasks,
495 VALUES ('Peer Ratings', '$domain_name/people/', 0,
496 'A', 'peerrating', 'shell', NULL, NULL, 'cvs1',
497 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
501 foreach my $s (@reqlist) {
504 $sth = $dbh->prepare ($query) ;
509 &update_db_version ($target) ;
510 &debug ("Committing.") ;
514 $version = &get_db_version ;
515 $target = "2.6-0+checkpoint+1" ;
516 if (&is_lesser ($version, $target)) {
517 &debug ("Database has successfully been converted.") ;
518 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
520 $sth = $dbh->prepare ($query) ;
524 &update_db_version ($target) ;
525 &debug ("Committing.") ;
533 $version = &get_db_version ;
534 $target = "2.6-0+checkpoint+2" ;
535 if (&is_lesser ($version, $target)) {
536 &debug ("Updating permissions on system groups.") ;
537 $query = "UPDATE groups SET group_name='Site Admin', is_public=1 WHERE group_id=1" ;
539 $sth = $dbh->prepare ($query) ;
542 $query = "UPDATE groups SET group_name='Site News Admin', is_public=1 WHERE group_id=$sys_news_group" ;
544 $sth = $dbh->prepare ($query) ;
548 &update_db_version ($target) ;
549 &debug ("Committing.") ;
553 $version = &get_db_version ;
554 $target = "2.6-0+checkpoint+3" ;
555 if (&is_lesser ($version, $target)) {
556 &debug ("Creating table group_cvs_history.") ;
557 $query = "CREATE TABLE group_cvs_history (
558 id integer DEFAULT nextval('group_cvs_history_pk_seq'::text) NOT NULL,
559 group_id integer DEFAULT '0' NOT NULL,
560 user_name character varying(80) DEFAULT '' NOT NULL,
561 cvs_commits integer DEFAULT '0' NOT NULL,
562 cvs_commits_wk integer DEFAULT '0' NOT NULL,
563 cvs_adds integer DEFAULT '0' NOT NULL,
564 cvs_adds_wk integer DEFAULT '0' NOT NULL,
567 $sth = $dbh->prepare ($query) ;
571 &update_db_version ($target) ;
572 &debug ("Committing.") ;
576 $version = &get_db_version ;
577 $target = "2.6-0+checkpoint+4" ;
578 if (&is_lesser ($version, $target)) {
579 &debug ("Registering Savannah themes.") ;
581 $query = "SELECT max(theme_id) FROM themes" ;
583 $sth = $dbh->prepare ($query) ;
585 @array = $sth->fetchrow_array () ;
587 my $maxid = $array [0] ;
589 &bump_sequence_to ($dbh, "themes_pk_seq", $maxid) ;
592 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_codex', 'Savannah CodeX')",
593 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_forest', 'Savannah Forest')",
594 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_reverse', 'Savannah Reverse')",
595 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_sad', 'Savannah Sad')",
596 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_savannah', 'Savannah Original')",
597 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_slashd', 'Savannah SlashDot')",
598 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_startrek', 'Savannah StarTrek')",
599 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_transparent', 'Savannah Transparent')",
600 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_water', 'Savannah Water')",
601 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_www.gnu.org', 'Savannah www.gnu.org')"
603 foreach my $s (@reqlist) {
606 $sth = $dbh->prepare ($query) ;
612 &update_db_version ($target) ;
613 &debug ("Committing.") ;
617 $version = &get_db_version ;
618 $target = "2.6-0+checkpoint+5" ;
619 if (&is_lesser ($version, $target)) {
620 &debug ("Registering yet another Savannah theme.") ;
622 $query = "INSERT INTO themes (dirname, fullname) VALUES ('savannah_darkslate', 'Savannah Dark Slate')";
624 $sth = $dbh->prepare ($query) ;
628 &update_db_version ($target) ;
629 &debug ("Committing.") ;
633 $version = &get_db_version ;
634 $target = "2.6-0+checkpoint+6" ;
635 if (&is_lesser ($version, $target)) {
636 &debug ("Updating language codes.") ;
639 "UPDATE supported_languages SET language_code='en' where classname='English'",
640 "UPDATE supported_languages SET language_code='ja' where classname='Japanese'",
641 "UPDATE supported_languages SET language_code='iw' where classname='Hebrew'",
642 "UPDATE supported_languages SET language_code='es' where classname='Spanish'",
643 "UPDATE supported_languages SET language_code='th' where classname='Thai'",
644 "UPDATE supported_languages SET language_code='de' where classname='German'",
645 "UPDATE supported_languages SET language_code='it' where classname='Italian'",
646 "UPDATE supported_languages SET language_code='no' where classname='Norwegian'",
647 "UPDATE supported_languages SET language_code='sv' where classname='Swedish'",
648 "UPDATE supported_languages SET language_code='zh' where classname='Chinese'",
649 "UPDATE supported_languages SET language_code='nl' where classname='Dutch'",
650 "UPDATE supported_languages SET language_code='eo' where classname='Esperanto'",
651 "UPDATE supported_languages SET language_code='ca' where classname='Catalan'",
652 "UPDATE supported_languages SET language_code='ko' where classname='Korean'",
653 "UPDATE supported_languages SET language_code='bg' where classname='Bulgarian'",
654 "UPDATE supported_languages SET language_code='el' where classname='Greek'",
655 "UPDATE supported_languages SET language_code='id' where classname='Indonesian'",
656 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese (Brazillian)'",
657 "UPDATE supported_languages SET language_code='pl' where classname='Polish'",
658 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese'",
659 "UPDATE supported_languages SET language_code='ru' where classname='Russian'",
660 "UPDATE supported_languages SET language_code='fr' where classname='French'"
662 foreach my $s (@reqlist) {
665 $sth = $dbh->prepare ($query) ;
670 &update_db_version ($target) ;
671 &debug ("Committing.") ;
675 $version = &get_db_version ;
676 $target = "2.6-0+checkpoint+7" ;
677 if (&is_lesser ($version, $target)) {
678 &debug ("Fixing artifact-related views.") ;
680 &drop_view_if_exists ($dbh, "artifact_file_user_vw") ;
681 &drop_view_if_exists ($dbh, "artifact_history_user_vw") ;
682 &drop_view_if_exists ($dbh, "artifact_message_user_vw") ;
683 &drop_view_if_exists ($dbh, "artifactperm_artgrouplist_vw") ;
684 &drop_view_if_exists ($dbh, "artifactperm_user_vw") ;
685 &drop_view_if_exists ($dbh, "artifact_vw") ;
688 "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)",
689 "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)",
690 "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)",
691 "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)",
692 "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)",
693 "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))"
695 foreach my $s (@reqlist) {
698 $sth = $dbh->prepare ($query) ;
703 &update_db_version ($target) ;
704 &debug ("Committing.") ;
708 $version = &get_db_version ;
709 $target = "2.6-0+checkpoint+8" ;
710 if (&is_lesser ($version, $target)) {
711 &debug ("Adding integrity constraints between the Trove map tables.") ;
714 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
715 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_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_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
717 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
718 "DELETE FROM trove_treesums WHERE trove_cat_id NOT IN (SELECT trove_cat_id FROM trove_cat)",
719 "ALTER TABLE trove_treesums ADD CONSTRAINT trove_treesums_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
721 foreach my $s (@reqlist) {
724 $sth = $dbh->prepare ($query) ;
729 &update_db_version ($target) ;
730 &debug ("Committing.") ;
734 $version = &get_db_version ;
735 $target = "2.6-0+checkpoint+9" ;
736 if (&is_lesser ($version, $target)) {
737 &debug ("Adding extra fields to the groups table.") ;
740 "ALTER TABLE groups ADD COLUMN use_ftp integer",
741 "ALTER TABLE groups ALTER COLUMN use_ftp SET DEFAULT 1",
742 "UPDATE groups SET use_ftp = 1",
743 "ALTER TABLE groups ADD COLUMN use_tracker integer",
744 "ALTER TABLE groups ALTER COLUMN use_tracker SET DEFAULT 1",
745 "UPDATE groups SET use_tracker = 1",
746 "ALTER TABLE groups ADD COLUMN use_frs integer",
747 "ALTER TABLE groups ALTER COLUMN use_frs SET DEFAULT 1",
748 "UPDATE groups SET use_frs = 1",
749 "ALTER TABLE groups ADD COLUMN use_stats integer",
750 "ALTER TABLE groups ALTER COLUMN use_stats SET DEFAULT 1",
751 "UPDATE groups SET use_stats = 1",
752 "ALTER TABLE groups ADD COLUMN enable_pserver integer",
753 "ALTER TABLE groups ALTER COLUMN enable_pserver SET DEFAULT 1",
754 "UPDATE groups SET enable_pserver = 1",
755 "ALTER TABLE groups ADD COLUMN enable_anoncvs integer",
756 "ALTER TABLE groups ALTER COLUMN enable_anoncvs SET DEFAULT 1",
757 "UPDATE groups SET enable_anoncvs = 1",
759 foreach my $s (@reqlist) {
762 $sth = $dbh->prepare ($query) ;
767 &update_db_version ($target) ;
768 &debug ("Committing.") ;
772 $version = &get_db_version ;
773 $target = "2.6-0+checkpoint+10" ;
774 if (&is_lesser ($version, $target)) {
775 &debug ("Updating supported_languages table.") ;
777 my $pg_version = &get_pg_version ;
779 if (&is_lesser ($pg_version, "7.3")) {
781 "ALTER TABLE supported_languages RENAME TO supported_languages_old",
782 "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))",
783 "INSERT INTO supported_languages SELECT * FROM supported_languages_old",
784 "DROP TABLE supported_languages_old",
785 "ALTER TABLE supported_languages ADD CONSTRAINT supported_languages_pkey PRIMARY KEY (language_id)",
786 "ALTER TABLE users ADD CONSTRAINT users_languageid_fk FOREIGN KEY (language) REFERENCES supported_languages(language_id) MATCH FULL",
787 "ALTER TABLE doc_data ADD CONSTRAINT docdata_languageid_fk FOREIGN KEY (language_id) REFERENCES supported_languages(language_id) MATCH FULL",
788 "UPDATE supported_languages SET language_code='pt_BR', classname='PortugueseBrazilian', name='Pt. Brazilian', filename='PortugueseBrazilian.class' where classname='PortugueseBrazillian'",
792 "ALTER TABLE supported_languages RENAME COLUMN language_code TO language_code_old",
793 "ALTER TABLE supported_languages ADD COLUMN language_code character(5)",
794 "UPDATE supported_languages SET language_code = language_code_old",
795 "ALTER TABLE supported_languages DROP COLUMN language_code_old",
796 "UPDATE supported_languages SET language_code='pt_BR', classname='PortugueseBrazilian', name='Pt. Brazilian', filename='PortugueseBrazilian.class' where classname='PortugueseBrazillian'",
799 foreach my $s (@reqlist) {
802 $sth = $dbh->prepare ($query) ;
807 &update_db_version ($target) ;
808 &debug ("Committing.") ;
812 $version = &get_db_version ;
813 $target = "2.6-0+checkpoint+11" ;
814 if (&is_lesser ($version, $target)) {
815 &debug ("Adding tables for the plugin subsystem.") ;
818 "CREATE SEQUENCE plugins_pk_seq",
819 "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))",
820 "CREATE SEQUENCE group_plugin_pk_seq",
821 "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)",
822 "CREATE SEQUENCE user_plugin_pk_seq",
823 "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)",
825 foreach my $s (@reqlist) {
828 $sth = $dbh->prepare ($query) ;
833 &update_db_version ($target) ;
834 &debug ("Committing.") ;
838 $version = &get_db_version ;
839 $target = "2.6-0+checkpoint+12" ;
840 if (&is_lesser ($version, $target)) {
841 &debug ("Upgrading with 20021125.sql") ;
843 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021125.sql") } ;
844 foreach my $s (@reqlist) {
847 $sth = $dbh->prepare ($query) ;
853 &update_db_version ($target) ;
854 &debug ("Committing $target.") ;
858 $version = &get_db_version ;
859 $target = "2.6-0+checkpoint+13" ;
860 if (&is_lesser ($version, $target)) {
861 &debug ("Upgrading with 20021212.sql") ;
863 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021212.sql") } ;
864 foreach my $s (@reqlist) {
867 $sth = $dbh->prepare ($query) ;
873 &update_db_version ($target) ;
874 &debug ("Committing $target.") ;
878 $version = &get_db_version ;
879 $target = "2.6-0+checkpoint+14" ;
880 if (&is_lesser ($version, $target)) {
881 &debug ("Upgrading with 20021213.sql") ;
883 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021213.sql") } ;
884 foreach my $s (@reqlist) {
887 $sth = $dbh->prepare ($query) ;
893 &update_db_version ($target) ;
894 &debug ("Committing $target.") ;
898 $version = &get_db_version ;
899 $target = "2.6-0+checkpoint+15" ;
900 if (&is_lesser ($version, $target)) {
901 &debug ("Transcoding documentation data fields") ;
902 $query = "SELECT docid,data FROM doc_data ORDER BY docid ASC" ;
904 $sth = $dbh->prepare ($query) ;
906 while (@array = $sth->fetchrow_array) {
907 my $query2 = "UPDATE doc_data SET data='" ;
908 $query2 .= encode_base64 (decode_entities ($array [1])) ;
909 $query2 .= "', filename='file".$array [0].".html'";
910 $query2 .= ", filetype='text/html'";
911 $query2 .= " WHERE docid=" ;
912 $query2 .= $array [0] ;
915 my $sth2 =$dbh->prepare ($query2) ;
922 &update_db_version ($target) ;
923 &debug ("Committing $target.") ;
927 $version = &get_db_version ;
928 $target = "2.6-0+checkpoint+16" ;
929 if (&is_lesser ($version, $target)) {
930 &debug ("Upgrading with 20021214.sql") ;
932 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021214.sql") } ;
933 foreach my $s (@reqlist) {
936 $sth = $dbh->prepare ($query) ;
942 &update_db_version ($target) ;
943 &debug ("Committing $target.") ;
947 $version = &get_db_version ;
948 $target = "2.6-0+checkpoint+17" ;
949 if (&is_lesser ($version, $target)) {
950 &debug ("Upgrading with 20021215.sql") ;
952 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021215.sql") } ;
953 foreach my $s (@reqlist) {
956 $sth = $dbh->prepare ($query) ;
962 &update_db_version ($target) ;
963 &debug ("Committing $target.") ;
967 $version = &get_db_version ;
968 $target = "2.6-0+checkpoint+18" ;
969 if (&is_lesser ($version, $target)) {
970 &debug ("Upgrading with 20021216.sql") ;
972 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021216.sql") } ;
973 foreach my $s (@reqlist) {
976 $sth = $dbh->prepare ($query) ;
982 &update_db_version ($target) ;
983 &debug ("Committing $target.") ;
987 $version = &get_db_version ;
988 $target = "2.6-0+checkpoint+19" ;
989 if (&is_lesser ($version, $target)) {
990 &debug ("Upgrading with 20021223.sql") ;
992 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20021223.sql") } ;
993 foreach my $s (@reqlist) {
996 $sth = $dbh->prepare ($query) ;
1002 &update_db_version ($target) ;
1003 &debug ("Committing $target.") ;
1007 $version = &get_db_version ;
1008 $target = "2.6-0+checkpoint+20" ;
1009 if (&is_lesser ($version, $target)) {
1010 &debug ("Upgrading with 20030102.sql") ;
1012 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030102.sql") } ;
1013 foreach my $s (@reqlist) {
1016 $sth = $dbh->prepare ($query) ;
1022 &update_db_version ($target) ;
1023 &debug ("Committing $target.") ;
1027 $version = &get_db_version ;
1028 $target = "2.6-0+checkpoint+21" ;
1029 if (&is_lesser ($version, $target)) {
1030 &debug ("Upgrading with 20030105.sql") ;
1032 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030105.sql") } ;
1033 foreach my $s (@reqlist) {
1036 $sth = $dbh->prepare ($query) ;
1042 &update_db_version ($target) ;
1043 &debug ("Committing $target.") ;
1047 $version = &get_db_version ;
1048 $target = "2.6-0+checkpoint+22" ;
1049 if (&is_lesser ($version, $target)) {
1050 &debug ("Upgrading with 20030107.sql") ;
1052 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030107.sql") } ;
1053 foreach my $s (@reqlist) {
1056 $sth = $dbh->prepare ($query) ;
1062 &update_db_version ($target) ;
1063 &debug ("Committing $target.") ;
1067 $version = &get_db_version ;
1068 $target = "2.6-0+checkpoint+23" ;
1069 if (&is_lesser ($version, $target)) {
1070 &debug ("Upgrading with 20030109.sql") ;
1072 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030109.sql") } ;
1073 foreach my $s (@reqlist) {
1076 $sth = $dbh->prepare ($query) ;
1082 &update_db_version ($target) ;
1083 &debug ("Committing $target.") ;
1087 $version = &get_db_version ;
1088 $target = "2.6-0+checkpoint+24" ;
1089 if (&is_lesser ($version, $target)) {
1091 &debug ("Adjusting language sequences") ;
1093 $query = "SELECT max(language_id) FROM supported_languages" ;
1094 $sth = $dbh->prepare ($query) ;
1096 @array = $sth->fetchrow_array () ;
1098 my $maxid = $array [0] ;
1099 &bump_sequence_to ($dbh, "supported_languages_pk_seq", $maxid) ;
1101 &debug ("Upgrading with 20030112.sql") ;
1103 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030112.sql") } ;
1104 foreach my $s (@reqlist) {
1107 $sth = $dbh->prepare ($query) ;
1113 &update_db_version ($target) ;
1114 &debug ("Committing $target.") ;
1118 $version = &get_db_version ;
1119 $target = "2.6-0+checkpoint+25" ;
1120 if (&is_lesser ($version, $target)) {
1121 &debug ("Upgrading with 20030113.sql") ;
1123 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030113.sql") } ;
1124 foreach my $s (@reqlist) {
1127 $sth = $dbh->prepare ($query) ;
1133 &update_db_version ($target) ;
1134 &debug ("Committing $target.") ;
1138 $version = &get_db_version ;
1139 $target = "2.6-0+checkpoint+26" ;
1140 if (&is_lesser ($version, $target)) {
1141 &debug ("Upgrading with 20030131.sql") ;
1143 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030131.sql") } ;
1144 foreach my $s (@reqlist) {
1147 $sth = $dbh->prepare ($query) ;
1153 &update_db_version ($target) ;
1154 &debug ("Committing $target.") ;
1158 $version = &get_db_version ;
1159 $target = "2.6-0+checkpoint+27" ;
1160 if (&is_lesser ($version, $target)) {
1161 &debug ("Upgrading with 20030209.sql") ;
1163 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030209.sql") } ;
1164 foreach my $s (@reqlist) {
1167 $sth = $dbh->prepare ($query) ;
1173 &update_db_version ($target) ;
1174 &debug ("Committing $target.") ;
1178 $version = &get_db_version ;
1179 $target = "2.6-0+checkpoint+28" ;
1180 if (&is_lesser ($version, $target)) {
1181 &debug ("Upgrading with 20030312.sql") ;
1183 my $pg_version = &get_pg_version ;
1185 if (&is_lesser ($pg_version, "7.3")) {
1187 "DROP TRIGGER projtask_insert_depend_trig ON project_task",
1188 "DROP FUNCTION projtask_insert_depend ()",
1189 "CREATE OR REPLACE FUNCTION projtask_insert_depend () RETURNS OPAQUE AS '
1194 IF NEW.start_date > NEW.end_date THEN
1195 RAISE EXCEPTION ''START DATE CANNOT BE AFTER END DATE'';
1197 FOR dependon IN SELECT * FROM project_dependon_vw
1198 WHERE project_task_id=NEW.project_task_id LOOP
1199 IF dependon.end_date > NEW.start_date THEN
1200 delta := dependon.end_date-NEW.start_date;
1201 RAISE NOTICE ''Bumping Back: % Delta: % '',NEW.project_task_id,delta;
1202 NEW.start_date := NEW.start_date+delta;
1203 NEW.end_date := NEW.end_date+delta;
1208 ' LANGUAGE 'plpgsql'",
1209 "CREATE TRIGGER projtask_insert_depend_trig BEFORE INSERT OR UPDATE ON project_task
1210 FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend()",
1213 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030312.sql") } ;
1215 foreach my $s (@reqlist) {
1218 $sth = $dbh->prepare ($query) ;
1224 &update_db_version ($target) ;
1225 &debug ("Committing $target.") ;
1229 $version = &get_db_version ;
1230 $target = "2.6-0+checkpoint+29" ;
1231 if (&is_lesser ($version, $target)) {
1232 &debug ("Registering KDE theme.") ;
1234 $query = "INSERT INTO themes (dirname, fullname) VALUES ('kde', 'KDE')";
1236 $sth = $dbh->prepare ($query) ;
1240 &update_db_version ($target) ;
1241 &debug ("Committing.") ;
1246 $version = &get_db_version ;
1247 $target = "2.6-0+checkpoint+30" ;
1248 if (&is_lesser ($version, $target)) {
1249 &debug ("Registering Dark Aqua theme.") ;
1251 $query = "INSERT INTO themes (dirname, fullname) VALUES ('darkaqua', 'Dark Aqua')";
1253 $sth = $dbh->prepare ($query) ;
1257 &update_db_version ($target) ;
1258 &debug ("Committing.") ;
1263 $version = &get_db_version ;
1264 $target = "2.6-0+checkpoint+31" ;
1265 if (&is_lesser ($version, $target)) {
1266 &debug ("Upgrading with 20030513.sql") ;
1268 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030513.sql") } ;
1269 foreach my $s (@reqlist) {
1272 $sth = $dbh->prepare ($query) ;
1278 &update_db_version ($target) ;
1279 &debug ("Committing.") ;
1284 $version = &get_db_version ;
1286 if (&is_lesser ($version, $target)) {
1287 &debug ("Database schema is now version 3.0-1.") ;
1289 &update_db_version ($target) ;
1290 &debug ("Committing.") ;
1295 $version = &get_db_version ;
1297 if (&is_lesser ($version, $target)) {
1298 &debug ("Upgrading with 20030822.sql") ;
1300 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20030822.sql") } ;
1301 foreach my $s (@reqlist) {
1304 $sth = $dbh->prepare ($query) ;
1310 &update_db_version ($target) ;
1311 &debug ("Committing.") ;
1315 $version = &get_db_version ;
1316 $target = "3.1-0+1" ;
1317 if (&is_lesser ($version, $target)) {
1318 &debug ("Upgrading with 20031105.sql") ;
1320 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20031105.sql") } ;
1321 foreach my $s (@reqlist) {
1324 $sth = $dbh->prepare ($query) ;
1329 &debug ("Upgrading with 20031124.sql") ;
1331 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20031124.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.1-0+2" ;
1348 if (&is_lesser ($version, $target)) {
1349 &debug ("Upgrading with 20031129.sql") ;
1351 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20031129.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.1-0+3" ;
1368 if (&is_lesser ($version, $target)) {
1369 # Yes, I know. 20031126 < 20031129, yet we apply that change later.
1370 # Blame tperdue for late committing.
1371 # They are independent anyway.
1372 &debug ("Upgrading with 20031126.sql") ;
1374 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20031126.sql") } ;
1375 foreach my $s (@reqlist) {
1378 $sth = $dbh->prepare ($query) ;
1384 &update_db_version ($target) ;
1385 &debug ("Committing.") ;
1389 $version = &get_db_version ;
1390 $target = "3.2.1-0+2" ;
1391 if (&is_lesser ($version, $target)) {
1392 &debug ("Upgrading with 20031205.sql") ;
1394 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20031205.sql") } ;
1395 foreach my $s (@reqlist) {
1398 $sth = $dbh->prepare ($query) ;
1404 &update_db_version ($target) ;
1405 &debug ("Committing.") ;
1409 $version = &get_db_version ;
1410 $target = "3.2.1-0+3" ;
1411 if (&is_lesser ($version, $target)) {
1412 &debug ("Upgrading with 20040130.sql") ;
1414 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20040130.sql") } ;
1415 foreach my $s (@reqlist) {
1418 $sth = $dbh->prepare ($query) ;
1424 &update_db_version ($target) ;
1425 &debug ("Committing.") ;
1429 $version = &get_db_version ;
1430 $target = "3.2.1-0+4" ;
1431 if (&is_lesser ($version, $target)) {
1432 &debug ("Upgrading with 20040204.sql") ;
1434 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20040204.sql") } ;
1435 foreach my $s (@reqlist) {
1438 $sth = $dbh->prepare ($query) ;
1444 &update_db_version ($target) ;
1445 &debug ("Committing.") ;
1449 $version = &get_db_version ;
1450 $target = "3.2.1-0+5" ;
1451 if (&is_lesser ($version, $target)) {
1452 &debug ("Upgrading with 20040315.sql") ;
1454 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20040315.sql") } ;
1455 foreach my $s (@reqlist) {
1458 $sth = $dbh->prepare ($query) ;
1464 &update_db_version ($target) ;
1465 &debug ("Committing.") ;
1469 $version = &get_db_version ;
1470 $target = "3.3.0-0+0" ;
1471 if (&is_lesser ($version, $target)) {
1472 &debug ("Upgrading with 200403251.sql") ;
1474 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/200403251.sql") } ;
1475 foreach my $s (@reqlist) {
1478 $sth = $dbh->prepare ($query) ;
1484 &update_db_version ($target) ;
1485 &debug ("Committing.") ;
1489 $version = &get_db_version ;
1490 $target = "3.3.0-0+1" ;
1491 if (&is_lesser ($version, $target)) {
1492 &debug ("Upgrading with 200403252.sql") ;
1494 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/200403252.sql") } ;
1495 foreach my $s (@reqlist) {
1498 $sth = $dbh->prepare ($query) ;
1504 &update_db_version ($target) ;
1505 &debug ("Committing.") ;
1509 $version = &get_db_version ;
1510 $target = "3.3.0-0+3" ;
1511 if (&is_lesser ($version, $target)) {
1512 &debug ("Upgrading with 20040507.sql") ;
1514 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20040507.sql") } ;
1515 foreach my $s (@reqlist) {
1518 $sth = $dbh->prepare ($query) ;
1524 &update_db_version ($target) ;
1525 &debug ("Committing.") ;
1529 $version = &get_db_version ;
1530 $target = "3.3.0-0+4" ;
1531 if (&is_lesser ($version, $target)) {
1532 &debug ("Upgrading with 20040722.sql") ;
1534 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20040722.sql") } ;
1535 foreach my $s (@reqlist) {
1538 $sth = $dbh->prepare ($query) ;
1544 &update_db_version ($target) ;
1545 &debug ("Committing.") ;
1549 $version = &get_db_version ;
1550 $target = "3.3.0-0+6" ;
1551 if (&is_lesser ($version, $target)) {
1552 &debug ("Upgrading with 20040804.sql") ;
1554 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20040804.sql") } ;
1555 foreach my $s (@reqlist) {
1558 $sth = $dbh->prepare ($query) ;
1564 &update_db_version ($target) ;
1565 &debug ("Committing.") ;
1569 $version = &get_db_version ;
1570 $target = "3.3.0-0+7" ;
1571 if (&is_lesser ($version, $target)) {
1572 &debug ("Upgrading with 20040826.sql") ;
1574 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20040826.sql") } ;
1575 foreach my $s (@reqlist) {
1578 $sth = $dbh->prepare ($query) ;
1584 &update_db_version ($target) ;
1585 &debug ("Committing.") ;
1589 $version = &get_db_version ;
1590 $target = "3.3.0-2+1" ;
1591 if (&is_lesser ($version, $target)) {
1592 &debug ("Upgrading with migrateforum.php") ;
1593 system("php -q -d include_path=/etc/gforge:/usr/share/gforge/:/usr/share/gforge/www/include /usr/lib/gforge/db/20040826_migrateforum.php") == 0
1594 or die "system call of 20040826_migrateforum.php failed: $?" ;
1595 &update_db_version ($target) ;
1596 &debug ("Committing.") ;
1600 $version = &get_db_version ;
1601 $target = "3.3.0-2+2" ;
1602 if (&is_lesser ($version, $target)) {
1603 &debug ("Upgrading with migraterbac.php") ;
1604 system("php -q -d include_path=/etc/gforge:/usr/share/gforge/:/usr/share/gforge/www/include /usr/lib/gforge/db/20040826_migraterbac.php") == 0
1605 or die "system call of 20040826_migraterbac.php failed: $?" ;
1606 &update_db_version ($target) ;
1607 &debug ("Committing.") ;
1611 $version = &get_db_version ;
1612 $target = "3.3.0-2+4" ;
1613 if (&is_lesser ($version, $target)) {
1614 &debug ("Upgrading with 20040914.sql") ;
1616 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20040914.sql") } ;
1617 foreach my $s (@reqlist) {
1620 $sth = $dbh->prepare ($query) ;
1626 &update_db_version ($target) ;
1627 &debug ("Committing.") ;
1631 $version = &get_db_version ;
1632 $target = "3.3.0-2+4+1" ;
1633 if (&is_lesser ($version, $target)) {
1634 &debug ("Upgrading with 20041001.sql") ;
1636 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041001.sql") } ;
1637 foreach my $s (@reqlist) {
1640 $sth = $dbh->prepare ($query) ;
1646 &update_db_version ($target) ;
1647 &debug ("Committing.") ;
1651 $version = &get_db_version ;
1652 $target = "3.3.0-2+5" ;
1653 if (&is_lesser ($version, $target)) {
1654 &debug ("Upgrading with 20041005.sql") ;
1656 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041005.sql") } ;
1657 foreach my $s (@reqlist) {
1660 $sth = $dbh->prepare ($query) ;
1666 &update_db_version ($target) ;
1667 &debug ("Committing.") ;
1671 $version = &get_db_version ;
1672 $target = "3.3.0-2+6" ;
1673 if (&is_lesser ($version, $target)) {
1674 &debug ("Upgrading with 20041006.sql") ;
1676 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041006.sql") } ;
1677 foreach my $s (@reqlist) {
1680 $sth = $dbh->prepare ($query) ;
1686 &update_db_version ($target) ;
1687 &debug ("Committing.") ;
1691 $version = &get_db_version ;
1692 $target = "3.3.0-3" ;
1693 if (&is_lesser ($version, $target)) {
1694 &debug ("Upgrading with 20041014.sql") ;
1696 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041014.sql") } ;
1697 foreach my $s (@reqlist) {
1700 $sth = $dbh->prepare ($query) ;
1706 &update_db_version ($target) ;
1707 &debug ("Committing.") ;
1711 $version = &get_db_version ;
1712 $target = "3.3.0-4" ;
1713 if (&is_lesser ($version, $target)) {
1714 &debug ("Upgrading with 20041020.sql") ;
1716 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041020.sql") } ;
1717 foreach my $s (@reqlist) {
1720 $sth = $dbh->prepare ($query) ;
1726 &update_db_version ($target) ;
1727 &debug ("Committing.") ;
1731 $version = &get_db_version ;
1732 $target = "4.0.0-0" ;
1733 # This is an exception, I reapply a modified version of 20040729.sql since it was doing nothing
1734 # the other call was deleted from this file
1735 if (&is_lesser ($version, $target)) {
1736 &debug ("Upgrading with 20040729.sql") ;
1738 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20040729.sql") } ;
1739 foreach my $s (@reqlist) {
1742 $sth = $dbh->prepare ($query) ;
1748 &update_db_version ($target) ;
1749 &debug ("Committing.") ;
1753 $version = &get_db_version ;
1754 $target = "4.0.0-0+1" ;
1755 if (&is_lesser ($version, $target)) {
1756 &debug ("Granting read access permissions to NSS") ;
1758 @reqlist = ( "GRANT SELECT ON nss_passwd TO gforge_nss",
1759 "GRANT SELECT ON nss_groups TO gforge_nss",
1760 "GRANT SELECT ON nss_usergroups TO gforge_nss",
1762 foreach my $s (@reqlist) {
1765 $sth = $dbh->prepare ($query) ;
1771 &update_db_version ($target) ;
1772 &debug ("Committing.") ;
1776 $version = &get_db_version ;
1777 $target = "4.0.0-0+2" ;
1778 if (&is_lesser ($version, $target)) {
1779 &debug ("Upgrading with 20041031.sql") ;
1781 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041031.sql") } ;
1782 foreach my $s (@reqlist) {
1785 $sth = $dbh->prepare ($query) ;
1791 &debug ("Granting read access permissions to NSS") ;
1793 @reqlist = ( "GRANT SELECT ON mta_users TO gforge_mta",
1794 "GRANT SELECT ON mta_lists TO gforge_mta",
1796 foreach my $s (@reqlist) {
1799 $sth = $dbh->prepare ($query) ;
1805 &update_db_version ($target) ;
1806 &debug ("Committing.") ;
1810 $version = &get_db_version ;
1811 $target = "4.0.0-0+3" ;
1812 # This is an exception, I reapply a modified version of 20040729.sql since it was doing nothing
1813 # the other call was deleted from this file
1814 if (&is_lesser ($version, $target)) {
1815 &debug ("Upgrading with 20041104.sql") ;
1817 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041104.sql") } ;
1818 foreach my $s (@reqlist) {
1821 $sth = $dbh->prepare ($query) ;
1827 &update_db_version ($target) ;
1828 &debug ("Committing.") ;
1832 $version = &get_db_version ;
1833 $target = "4.0.0-0+4" ;
1834 if (&is_lesser ($version, $target)) {
1835 &debug ("Upgrading with 20041108.sql") ;
1837 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041108.sql") } ;
1838 foreach my $s (@reqlist) {
1841 $sth = $dbh->prepare ($query) ;
1847 &update_db_version ($target) ;
1848 &debug ("Committing.") ;
1852 $version = &get_db_version ;
1853 $target = "4.0.2-0+0" ;
1854 if (&is_lesser ($version, $target)) {
1855 &debug ("Upgrading with 20041124.sql") ;
1857 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041124.sql") } ;
1858 foreach my $s (@reqlist) {
1861 $sth = $dbh->prepare ($query) ;
1867 &update_db_version ($target) ;
1868 &debug ("Committing.") ;
1872 $version = &get_db_version ;
1873 $target = "4.0.2-0+1" ;
1874 if (&is_lesser ($version, $target)) {
1875 &debug ("Upgrading with 20041211-syncmail.php") ;
1876 system("php -q -d include_path=/etc/gforge:/usr/share/gforge/:/usr/share/gforge/www/include /usr/lib/gforge/db/20041211-syncmail.php") == 0
1877 or die "system call of 20041211-syncmail.php failed: $?" ;
1878 &update_db_version ($target) ;
1879 &debug ("Committing.") ;
1883 # $version = &get_db_version ;
1884 # $target = "4.0.2-0+2" ;
1885 # if (&is_lesser ($version, $target)) {
1886 # &debug ("Upgrading with 20041222-debian.sql") ;
1888 # @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20041222-debian.sql") } ;
1889 # foreach my $s (@reqlist) {
1892 # $sth = $dbh->prepare ($query) ;
1893 # $sth->execute () ;
1898 # &update_db_version ($target) ;
1899 # &debug ("Committing.") ;
1903 $version = &get_db_version ;
1904 $target = "4.0.2-0+3" ;
1905 if (&is_lesser ($version, $target)) {
1906 &debug ("Upgrading with 20050115.sql") ;
1908 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20050115.sql") } ;
1909 foreach my $s (@reqlist) {
1912 $sth = $dbh->prepare ($query) ;
1918 &update_db_version ($target) ;
1919 &debug ("Committing.") ;
1924 # We got this at upgrade
1926 #DBD::Pg::st execute failed: ERREUR: la relation avec l'OID 387345 n'existe pas at /usr/lib/gforge/bin/db-upgrade.pl line 1970.
1927 #Transaction aborted because DBD::Pg::st execute failed: ERREUR: la relation avec l'OID 387345 n'existe pas at /usr/lib/gforge/bin/db-upgrade.pl line 1970.
1928 #Transaction aborted because DBD::Pg::st execute failed: ERREUR: la relation avec l'OID 387345 n'existe pas at /usr/lib/gforge/bin/db-upgrade.pl line 1970.
1929 #Last SQL query was:
1930 #update project_task SET last_modified_date=EXTRACT(EPOCH FROM now())::integer;
1932 #Your database schema is at version 4.0.2-0+5
1934 # This is a hack to disconnect and reconnect the DB and solve the problem
1941 if ( "$sys_dbname" ne "gforge" || "$sys_dbuser" ne "gforge" ) {
1942 $dbh ||= DBI->connect("DBI:Pg:dbname=$sys_dbname","$sys_dbuser","$sys_dbpasswd");
1944 $dbh ||= DBI->connect("DBI:Pg:dbname=$sys_dbname");
1946 die "Cannot connect to database: $!" if ( ! $dbh );
1948 # debug "Connected to the database OK." ;
1949 $dbh->{AutoCommit} = 0;
1950 $dbh->{RaiseError} = 1;
1952 $version = &get_db_version ;
1953 $target = "4.0.2-0+5" ;
1954 if (&is_lesser ($version, $target)) {
1955 &debug ("Upgrading with 20050130.sql") ;
1957 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20050130.sql") } ;
1958 foreach my $s (@reqlist) {
1961 $sth = $dbh->prepare ($query) ;
1967 &update_db_version ($target) ;
1968 &debug ("Committing.") ;
1972 $version = &get_db_version ;
1973 $target = "4.0.2-0+6" ;
1974 if (&is_lesser ($version, $target)) {
1975 &debug ("Upgrading with 20050212.sql") ;
1977 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20050212.sql") } ;
1978 foreach my $s (@reqlist) {
1981 $sth = $dbh->prepare ($query) ;
1987 &update_db_version ($target) ;
1988 &debug ("Committing.") ;
1992 $version = &get_db_version ;
1993 $target = "4.0.2-0+7" ;
1994 if (&is_lesser ($version, $target)) {
1995 &debug ("Upgrading with 20050214-nss.sql valantine") ;
1997 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20050214-nss.sql") } ;
1998 foreach my $s (@reqlist) {
2001 $sth = $dbh->prepare ($query) ;
2007 &update_db_version ($target) ;
2008 &debug ("Committing.") ;
2012 $version = &get_db_version ;
2014 if (&is_lesser ($version, $target)) {
2015 &debug ("Upgrading with 20050224.sql") ;
2017 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20050224.sql") } ;
2018 foreach my $s (@reqlist) {
2021 $sth = $dbh->prepare ($query) ;
2027 &update_db_version ($target) ;
2028 &debug ("Committing.") ;
2032 $version = &get_db_version ;
2034 if (&is_lesser ($version, $target)) {
2035 &debug ("Upgrading with 20050225-nsssetup.sql") ;
2037 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20050225-nsssetup.sql") } ;
2038 foreach my $s (@reqlist) {
2041 $sth = $dbh->prepare ($query) ;
2047 &update_db_version ($target) ;
2048 &debug ("Committing.") ;
2052 $version = &get_db_version ;
2054 if (&is_lesser ($version, $target)) {
2055 &debug ("Upgrading with 20050311.sql") ;
2057 @reqlist = @{ &parse_sql_file ("/usr/lib/gforge/db/20050311.sql") } ;
2058 foreach my $s (@reqlist) {
2061 $sth = $dbh->prepare ($query) ;
2067 &update_db_version ($target) ;
2068 &debug ("Committing.") ;
2072 &debug ("It seems your database $action went well and smoothly. That's cool.") ;
2073 &debug ("Please enjoy using GForge.") ;
2075 # There should be a commit at the end of every block above.
2076 # If there is not, then it might be symptomatic of a problem.
2077 # For safety, we roll back.
2082 warn "Transaction aborted because $@" ;
2083 &debug ("Transaction aborted because $@") ;
2084 &debug ("Last SQL query was:\n$query\n(end of query)") ;
2086 my $version = &get_db_version ;
2088 &debug ("Your database schema is at version $version") ;
2090 &debug ("Couldn't get your database schema version.") ;
2092 &debug ("Please report this bug on the Debian bug-tracking system.") ;
2093 &debug ("Please include the previous messages as well to help debugging.") ;
2094 &debug ("You should not worry too much about this,") ;
2095 &debug ("your DB is still in a consistent state and should be usable.") ;
2102 sub get_pg_version () {
2103 my $command = q(dpkg -s postgresql | awk '/^Version: / { print $2 }') ;
2104 my $version = qx($command) ;
2109 sub create_metadata_table ( $ ) {
2110 my $v = shift || "2.5-7+just+before+8" ;
2111 # Do we have the metadata table?
2113 $query = "SELECT count(*) FROM pg_class WHERE relname = 'debian_meta_data' and relkind = 'r'";
2115 my $sth = $dbh->prepare ($query) ;
2117 my @array = $sth->fetchrow_array () ;
2120 # Let's create this table if we have it not
2122 if ($array [0] == 0) {
2123 &debug ("Creating debian_meta_data table.") ;
2124 $query = "CREATE TABLE debian_meta_data (key varchar primary key, value text not null)" ;
2126 $sth = $dbh->prepare ($query) ;
2131 $query = "SELECT count(*) FROM debian_meta_data WHERE key = 'db-version'";
2133 $sth = $dbh->prepare ($query) ;
2135 @array = $sth->fetchrow_array () ;
2138 # Empty table? We'll have to fill it up a bit
2140 if ($array [0] == 0) {
2141 &debug ("Inserting first data into debian_meta_data table.") ;
2142 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('db-version', '$v')" ;
2144 $sth = $dbh->prepare ($query) ;
2150 sub update_db_version ( $ ) {
2151 my $v = shift or die "Not enough arguments" ;
2153 &debug ("Updating debian_meta_data table.") ;
2154 $query = "UPDATE debian_meta_data SET value = '$v' WHERE key = 'db-version'" ;
2156 my $sth = $dbh->prepare ($query) ;
2161 sub get_db_version () {
2162 $query = "SELECT value FROM debian_meta_data WHERE key = 'db-version'" ;
2164 my $sth = $dbh->prepare ($query) ;
2166 my @array = $sth->fetchrow_array () ;
2169 my $version = $array [0] ;