5 # Debian-specific script to upgrade the database between releases
6 # Roland Mas <lolando@debian.org>
16 use vars qw/$dbh @reqlist $query/ ;
17 use vars qw/$sys_default_domain $sys_scm_host $sys_download_host
18 $sys_shell_host $sys_users_host $sys_docs_host $sys_lists_host
19 $sys_dns1_host $sys_dns2_host $FTPINCOMING_DIR $FTPFILES_DIR
20 $sys_urlroot $sf_cache_dir $sys_name $sys_themeroot
21 $sys_news_group $sys_dbhost $sys_dbname $sys_dbuser $sys_dbpasswd
22 $sys_ldap_base_dn $sys_ldap_host $admin_login $admin_password
23 $server_admin $domain_name $newsadmin_groupid $statsadmin_groupid
24 $skill_list $libdir $sqldir/ ;
26 require ("/etc/gforge/local.pl") ;
27 $libdir="/usr/lib/gforge/lib";
28 $sqldir="/usr/lib/gforge/db";
29 require ("$libdir/sqlparser.pm") ; # Our magic SQL parser
30 require ("$libdir/sqlhelper.pm") ; # Our SQL functions
31 require ("$libdir/include.pl"); # Some other functions
33 &debug ("You'll see some debugging info during this installation.") ;
34 &debug ("Do not worry unless told otherwise.") ;
38 $dbh->{AutoCommit} = 0;
39 $dbh->{RaiseError} = 1;
41 my ($sth, @array, $version, $action, $path, $target) ;
43 # Do we have at least the basic schema?
44 # Create Sourceforge database
45 if (! &table_exists ($dbh, 'groups')) { # No 'groups' table
46 # Installing SF 2.6 from scratch
47 $action = "installation" ;
48 &debug ("Creating initial Sourceforge database from files.") ;
50 &create_metadata_table ("2.5.9999") ;
52 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
54 $sth = $dbh->prepare ($query) ;
56 @array = $sth->fetchrow_array () ;
59 &debug ("Updating debian_meta_data table.") ;
60 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', 'scratch-to-2.6')" ;
62 $sth = $dbh->prepare ($query) ;
66 &debug ("Committing.") ;
69 } else { # A 'groups' table exists
72 if (! &table_exists ($dbh, 'debian_meta_data')) { # No 'debian_meta_data' table
73 # If we're here, we're upgrading from 2.5-7 or earlier
74 # We therefore need to create the table
75 &create_metadata_table ("2.5-7+just+before+8") ;
78 $version = &get_db_version ;
79 if (&is_lesser ($version, "2.5.9999")) {
80 &debug ("Found an old (2.5) database, will upgrade to 2.6") ;
82 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
84 $sth = $dbh->prepare ($query) ;
86 @array = $sth->fetchrow_array () ;
90 # &debug ("Updating debian_meta_data table.") ;
91 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', '2.5-to-2.6')" ;
93 $sth = $dbh->prepare ($query) ;
96 &debug ("Committing.") ;
102 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
104 $sth = $dbh->prepare ($query) ;
106 @array = $sth->fetchrow_array () ;
109 if ($array[0] == 0) {
112 $query = "SELECT value from debian_meta_data where key = 'current-path'";
114 $sth = $dbh->prepare ($query) ;
116 @array = $sth->fetchrow_array () ;
123 ($path eq 'scratch-to-2.6') && do {
124 $version = &get_db_version ;
125 $target = "2.5.9999.1+global+data+done" ;
126 if (&is_lesser ($version, $target)) {
127 my @filelist = qw{ sf-2.6-complete.sql } ;
128 # TODO: user_rating.sql
130 foreach my $file (@filelist) {
131 &debug ("Processing $file") ;
132 @reqlist = @{ &parse_sql_file ($sqldir."/".$file) } ;
134 foreach my $s (@reqlist) {
137 $sth = $dbh->prepare ($query) ;
144 &update_db_version ($target) ;
145 &debug ("Committing.") ;
149 $version = &get_db_version ;
150 $target = "2.5.9999.2+local+data+done" ;
151 if (&is_lesser ($version, $target)) {
152 &debug ("Adding local data.") ;
154 do "/etc/gforge/local.pl" or die "Cannot read /etc/gforge/local.pl" ;
156 my ($login, $pwd, $md5pwd, $email, $noreplymail, $date) ;
158 $login = $admin_login ;
159 $pwd = $admin_password ;
160 $md5pwd=qx/echo -n $pwd | md5sum/ ;
162 $md5pwd =~ s/(.{32}) .*/$1/ ;
163 $email = $server_admin ;
164 $noreplymail="noreply\@$domain_name" ;
168 "UPDATE groups SET homepage = '$domain_name/admin/' where group_id = 1",
169 "UPDATE groups SET homepage = '$domain_name/news/' where group_id = 2",
170 "UPDATE groups SET homepage = '$domain_name/stats/' where group_id = 3",
171 "UPDATE groups SET homepage = '$domain_name/peerrating/' where group_id = 4",
172 "UPDATE users SET email = '$noreplymail' where user_id = 100",
173 "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)",
174 "SELECT setval ('\"users_pk_seq\"', 102, 'f')",
175 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 1, 'A')",
176 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 2, 'A')",
177 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 3, 'A')",
178 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 4, 'A')"
181 foreach my $s (@reqlist) {
184 $sth = $dbh->prepare ($query) ;
190 &update_db_version ($target) ;
191 &debug ("Committing.") ;
195 $version = &get_db_version ;
196 $target = "2.5.9999.3+skills+done" ;
197 if (&is_lesser ($version, $target)) {
198 &debug ("Inserting skills.") ;
200 foreach my $skill (split /;/, $skill_list) {
201 push @reqlist, "INSERT INTO people_skill (name) VALUES ('$skill')" ;
204 foreach my $s (@reqlist) {
207 $sth = $dbh->prepare ($query) ;
213 &update_db_version ($target) ;
214 &debug ("Committing.") ;
218 $version = &get_db_version ;
219 $target = "2.6-0+checkpoint+1" ;
220 if (&is_lesser ($version, $target)) {
221 &debug ("Updating debian_meta_data table.") ;
222 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
224 $sth = $dbh->prepare ($query) ;
228 &update_db_version ($target) ;
229 &debug ("Committing.") ;
236 ($path eq '2.5-to-2.6') && do {
238 $version = &get_db_version ;
240 if (&is_lesser ($version, $target)) {
241 &debug ("Adding row to people_job_category.") ;
242 $query = "INSERT INTO people_job_category VALUES (100, 'Undefined', 0)" ;
243 $sth = $dbh->prepare ($query) ;
247 &update_db_version ($target) ;
248 &debug ("Committing.") ;
252 $version = &get_db_version ;
254 if (&is_lesser ($version, $target)) {
255 &debug ("Adding row to supported_languages.") ;
256 $query = "INSERT INTO supported_languages VALUES (15, 'Korean', 'Korean.class', 'Korean', 'kr')" ;
257 $sth = $dbh->prepare ($query) ;
261 &update_db_version ($target) ;
262 &debug ("Committing.") ;
266 $version = &get_db_version ;
268 if (&is_lesser ($version, $target)) {
269 &debug ("Fixing unix_box entries.") ;
271 $query = "update groups set unix_box = 'shell'" ;
272 $sth = $dbh->prepare ($query) ;
276 $query = "update users set unix_box = 'shell'" ;
277 $sth = $dbh->prepare ($query) ;
281 &debug ("Also fixing a few sequences.") ;
283 &bump_sequence_to ($dbh, "bug_pk_seq", 100) ;
284 &bump_sequence_to ($dbh, "project_task_pk_seq", 100) ;
286 &update_db_version ($target) ;
287 &debug ("Committing.") ;
291 $version = &get_db_version ;
293 if (&is_lesser ($version, $target)) {
294 &debug ("Adding rows to supported_languages.") ;
296 "INSERT INTO supported_languages VALUES (16,'Bulgarian','Bulgarian.class','Bulgarian','bg')",
297 "INSERT INTO supported_languages VALUES (17,'Greek','Greek.class','Greek','el')",
298 "INSERT INTO supported_languages VALUES (18,'Indonesian','Indonesian.class','Indonesian','id')",
299 "INSERT INTO supported_languages VALUES (19,'Portuguese (Brazillian)','PortugueseBrazillian.class','PortugueseBrazillian', 'br')",
300 "INSERT INTO supported_languages VALUES (20,'Polish','Polish.class','Polish','pl')",
301 "INSERT INTO supported_languages VALUES (21,'Portuguese','Portuguese.class','Portuguese', 'pt')",
302 "INSERT INTO supported_languages VALUES (22,'Russian','Russian.class','Russian','ru')"
305 foreach my $s (@reqlist) {
308 $sth = $dbh->prepare ($query) ;
314 &update_db_version ($target) ;
315 &debug ("Committing.") ;
319 $version = &get_db_version ;
321 if (&is_lesser ($version, $target)) {
322 &debug ("Fixing unix_uid entries.") ;
324 $query = "UPDATE users SET unix_uid = nextval ('unix_uid_seq') WHERE unix_status != 'N' AND status != 'P' AND unix_uid = 0" ;
325 $sth = $dbh->prepare ($query) ;
329 &update_db_version ($target) ;
330 &debug ("Committing.") ;
334 $version = &get_db_version ;
335 $target = "2.5.9999.1+temp+data+dropped" ;
336 if (&is_lesser ($version, $target)) {
337 &debug ("Preparing to upgrade your database - dropping temporary tables") ;
339 my @tables = qw/ user_metric_tmp1_1 user_metric_tmp1_2
340 user_metric_tmp1_3 user_metric_tmp1_4
341 user_metric_tmp1_5 user_metric_tmp1_6
342 user_metric_tmp1_7 user_metric_tmp1_8 user_metric1
343 user_metric2 user_metric3 user_metric4 user_metric5
344 user_metric6 user_metric7 user_metric8
345 project_counts_tmp project_metric_tmp
346 project_metric_tmp1 project_counts_weekly_tmp
347 project_metric_weekly_tmp project_metric_weekly_tmp1
350 my @sequences = qw/ user_metric1_ranking_seq
351 user_metric2_ranking_seq user_metric3_ranking_seq
352 user_metric4_ranking_seq user_metric5_ranking_seq
353 user_metric6_ranking_seq user_metric7_ranking_seq
354 user_metric8_ranking_seq project_metric_weekly_seq
355 trove_treesum_trove_treesum_seq
356 project_metric_tmp1_pk_seq / ;
358 my @indexes = qw/ idx_project_metric_group
359 idx_project_metric_weekly_group
360 user_metric_history_date_userid / ;
362 foreach my $table (@tables) {
363 &drop_table_if_exists ($dbh, $table) ;
366 foreach my $sequence (@sequences) {
367 &drop_sequence_if_exists ($dbh, $sequence) ;
370 foreach my $index (@indexes) {
371 &drop_index_if_exists ($dbh, $index) ;
374 &update_db_version ($target) ;
375 &debug ("Committing.") ;
379 $version = &get_db_version ;
380 $target = "2.5.9999.2+data+upgraded" ;
381 if (&is_lesser ($version, $target)) {
382 &debug ("Upgrading your database scheme from 2.5") ;
385 "ALTER TABLE groups DROP CONSTRAINT groups_pkey",
386 "ALTER TABLE users DROP CONSTRAINT users_pkey",
388 foreach my $s (@reqlist) {
391 $sth = $dbh->prepare ($query) ;
396 @reqlist = @{ &parse_sql_file ("$sqldir/sf2.5-to-sf2.6.sql") } ;
397 foreach my $s (@reqlist) {
400 $sth = $dbh->prepare ($query) ;
406 &update_db_version ($target) ;
407 &debug ("Committing.") ;
411 $version = &get_db_version ;
412 $target = "2.5.9999.3+artifact+transcoded" ;
413 if (&is_lesser ($version, $target)) {
414 &debug ("Transcoding the artifact data fields") ;
416 $query = "SELECT id,bin_data FROM artifact_file ORDER BY id ASC" ;
418 $sth = $dbh->prepare ($query) ;
420 while (@array = $sth->fetchrow_array) {
421 my $query2 = "UPDATE artifact_file SET bin_data='" ;
422 $query2 .= encode_base64 (decode_entities ($array [1])) ;
423 $query2 .= "' WHERE id=" ;
424 $query2 .= $array [0] ;
427 my $sth2 =$dbh->prepare ($query2) ;
434 &update_db_version ($target) ;
435 &debug ("Committing.") ;
439 $version = &get_db_version ;
440 $target = "2.5.9999.4+groups+inserted" ;
441 if (&is_lesser ($version, $target)) {
442 &debug ("Inserting missing groups") ;
445 "INSERT INTO groups (group_name, homepage,
446 is_public, status, unix_group_name,
447 unix_box, http_domain, short_description,
448 cvs_box, license, register_purpose,
449 license_other, register_time, rand_hash,
450 use_mail, use_survey, use_forum, use_pm,
451 use_cvs, use_news, type, use_docman,
452 new_task_address, send_all_tasks,
454 VALUES ('Stats', '$domain_name/top/', 0,
455 'A', 'stats', 'shell', NULL, NULL, 'cvs',
456 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
458 "INSERT INTO groups (group_name, homepage,
459 is_public, status, unix_group_name,
460 unix_box, http_domain, short_description,
461 cvs_box, license, register_purpose,
462 license_other, register_time, rand_hash,
463 use_mail, use_survey, use_forum, use_pm,
464 use_cvs, use_news, type, use_docman,
465 new_task_address, send_all_tasks,
467 VALUES ('Peer Ratings', '$domain_name/people/', 0,
468 'A', 'peerrating', 'shell', NULL, NULL, 'cvs1',
469 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
473 foreach my $s (@reqlist) {
476 $sth = $dbh->prepare ($query) ;
481 &update_db_version ($target) ;
482 &debug ("Committing.") ;
486 $version = &get_db_version ;
487 $target = "2.6-0+checkpoint+1" ;
488 if (&is_lesser ($version, $target)) {
489 &debug ("Database has successfully been converted.") ;
490 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
492 $sth = $dbh->prepare ($query) ;
496 &update_db_version ($target) ;
497 &debug ("Committing.") ;
505 $version = &get_db_version ;
506 $target = "2.6-0+checkpoint+2" ;
507 if (&is_lesser ($version, $target)) {
508 &debug ("Updating permissions on system groups.") ;
509 $query = "UPDATE groups SET group_name='Site Admin', is_public=1 WHERE group_id=1" ;
511 $sth = $dbh->prepare ($query) ;
514 $query = "UPDATE groups SET group_name='Site News Admin', is_public=1 WHERE group_id=$sys_news_group" ;
516 $sth = $dbh->prepare ($query) ;
520 &update_db_version ($target) ;
521 &debug ("Committing.") ;
525 $version = &get_db_version ;
526 $target = "2.6-0+checkpoint+3" ;
527 if (&is_lesser ($version, $target)) {
528 &debug ("Creating table group_cvs_history.") ;
529 $query = "CREATE TABLE group_cvs_history (
530 id integer DEFAULT nextval('group_cvs_history_pk_seq'::text) NOT NULL,
531 group_id integer DEFAULT '0' NOT NULL,
532 user_name character varying(80) DEFAULT '' NOT NULL,
533 cvs_commits integer DEFAULT '0' NOT NULL,
534 cvs_commits_wk integer DEFAULT '0' NOT NULL,
535 cvs_adds integer DEFAULT '0' NOT NULL,
536 cvs_adds_wk integer DEFAULT '0' NOT NULL,
539 $sth = $dbh->prepare ($query) ;
543 &update_db_version ($target) ;
544 &debug ("Committing.") ;
548 $version = &get_db_version ;
549 $target = "2.6-0+checkpoint+4" ;
550 if (&is_lesser ($version, $target)) {
551 &debug ("Registering Savannah themes.") ;
553 $query = "SELECT max(theme_id) FROM themes" ;
555 $sth = $dbh->prepare ($query) ;
557 @array = $sth->fetchrow_array () ;
559 my $maxid = $array [0] ;
561 &bump_sequence_to ($dbh, "themes_pk_seq", $maxid) ;
564 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_codex', 'Savannah CodeX')",
565 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_forest', 'Savannah Forest')",
566 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_reverse', 'Savannah Reverse')",
567 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_sad', 'Savannah Sad')",
568 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_savannah', 'Savannah Original')",
569 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_slashd', 'Savannah SlashDot')",
570 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_startrek', 'Savannah StarTrek')",
571 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_transparent', 'Savannah Transparent')",
572 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_water', 'Savannah Water')",
573 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_www.gnu.org', 'Savannah www.gnu.org')"
575 foreach my $s (@reqlist) {
578 $sth = $dbh->prepare ($query) ;
584 &update_db_version ($target) ;
585 &debug ("Committing.") ;
589 $version = &get_db_version ;
590 $target = "2.6-0+checkpoint+5" ;
591 if (&is_lesser ($version, $target)) {
592 &debug ("Registering yet another Savannah theme.") ;
594 $query = "INSERT INTO themes (dirname, fullname) VALUES ('savannah_darkslate', 'Savannah Dark Slate')";
596 $sth = $dbh->prepare ($query) ;
600 &update_db_version ($target) ;
601 &debug ("Committing.") ;
605 $version = &get_db_version ;
606 $target = "2.6-0+checkpoint+6" ;
607 if (&is_lesser ($version, $target)) {
608 &debug ("Updating language codes.") ;
611 "UPDATE supported_languages SET language_code='en' where classname='English'",
612 "UPDATE supported_languages SET language_code='ja' where classname='Japanese'",
613 "UPDATE supported_languages SET language_code='iw' where classname='Hebrew'",
614 "UPDATE supported_languages SET language_code='es' where classname='Spanish'",
615 "UPDATE supported_languages SET language_code='th' where classname='Thai'",
616 "UPDATE supported_languages SET language_code='de' where classname='German'",
617 "UPDATE supported_languages SET language_code='it' where classname='Italian'",
618 "UPDATE supported_languages SET language_code='no' where classname='Norwegian'",
619 "UPDATE supported_languages SET language_code='sv' where classname='Swedish'",
620 "UPDATE supported_languages SET language_code='zh' where classname='Chinese'",
621 "UPDATE supported_languages SET language_code='nl' where classname='Dutch'",
622 "UPDATE supported_languages SET language_code='eo' where classname='Esperanto'",
623 "UPDATE supported_languages SET language_code='ca' where classname='Catalan'",
624 "UPDATE supported_languages SET language_code='ko' where classname='Korean'",
625 "UPDATE supported_languages SET language_code='bg' where classname='Bulgarian'",
626 "UPDATE supported_languages SET language_code='el' where classname='Greek'",
627 "UPDATE supported_languages SET language_code='id' where classname='Indonesian'",
628 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese (Brazillian)'",
629 "UPDATE supported_languages SET language_code='pl' where classname='Polish'",
630 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese'",
631 "UPDATE supported_languages SET language_code='ru' where classname='Russian'",
632 "UPDATE supported_languages SET language_code='fr' where classname='French'"
634 foreach my $s (@reqlist) {
637 $sth = $dbh->prepare ($query) ;
642 &update_db_version ($target) ;
643 &debug ("Committing.") ;
647 $version = &get_db_version ;
648 $target = "2.6-0+checkpoint+7" ;
649 if (&is_lesser ($version, $target)) {
650 &debug ("Fixing artifact-related views.") ;
652 &drop_view_if_exists ($dbh, "artifact_file_user_vw") ;
653 &drop_view_if_exists ($dbh, "artifact_history_user_vw") ;
654 &drop_view_if_exists ($dbh, "artifact_message_user_vw") ;
655 &drop_view_if_exists ($dbh, "artifactperm_artgrouplist_vw") ;
656 &drop_view_if_exists ($dbh, "artifactperm_user_vw") ;
657 &drop_view_if_exists ($dbh, "artifact_vw") ;
660 "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)",
661 "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)",
662 "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)",
663 "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)",
664 "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)",
665 "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))"
667 foreach my $s (@reqlist) {
670 $sth = $dbh->prepare ($query) ;
675 &update_db_version ($target) ;
676 &debug ("Committing.") ;
680 $version = &get_db_version ;
681 $target = "2.6-0+checkpoint+8" ;
682 if (&is_lesser ($version, $target)) {
683 &debug ("Adding integrity constraints between the Trove map tables.") ;
686 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
687 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
688 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
689 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
690 "DELETE FROM trove_treesums WHERE trove_cat_id NOT IN (SELECT trove_cat_id FROM trove_cat)",
691 "ALTER TABLE trove_treesums ADD CONSTRAINT trove_treesums_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
693 foreach my $s (@reqlist) {
696 $sth = $dbh->prepare ($query) ;
701 &update_db_version ($target) ;
702 &debug ("Committing.") ;
706 $version = &get_db_version ;
707 $target = "2.6-0+checkpoint+9" ;
708 if (&is_lesser ($version, $target)) {
709 &debug ("Adding extra fields to the groups table.") ;
712 "ALTER TABLE groups ADD COLUMN use_ftp integer",
713 "ALTER TABLE groups ALTER COLUMN use_ftp SET DEFAULT 1",
714 "UPDATE groups SET use_ftp = 1",
715 "ALTER TABLE groups ADD COLUMN use_tracker integer",
716 "ALTER TABLE groups ALTER COLUMN use_tracker SET DEFAULT 1",
717 "UPDATE groups SET use_tracker = 1",
718 "ALTER TABLE groups ADD COLUMN use_frs integer",
719 "ALTER TABLE groups ALTER COLUMN use_frs SET DEFAULT 1",
720 "UPDATE groups SET use_frs = 1",
721 "ALTER TABLE groups ADD COLUMN use_stats integer",
722 "ALTER TABLE groups ALTER COLUMN use_stats SET DEFAULT 1",
723 "UPDATE groups SET use_stats = 1",
724 "ALTER TABLE groups ADD COLUMN enable_pserver integer",
725 "ALTER TABLE groups ALTER COLUMN enable_pserver SET DEFAULT 1",
726 "UPDATE groups SET enable_pserver = 1",
727 "ALTER TABLE groups ADD COLUMN enable_anoncvs integer",
728 "ALTER TABLE groups ALTER COLUMN enable_anoncvs SET DEFAULT 1",
729 "UPDATE groups SET enable_anoncvs = 1",
731 foreach my $s (@reqlist) {
734 $sth = $dbh->prepare ($query) ;
739 &update_db_version ($target) ;
740 &debug ("Committing.") ;
744 $version = &get_db_version ;
745 $target = "2.6-0+checkpoint+10" ;
746 if (&is_lesser ($version, $target)) {
747 &debug ("Updating supported_languages table.") ;
750 "ALTER TABLE supported_languages RENAME COLUMN language_code TO language_code_old",
751 "ALTER TABLE supported_languages ADD COLUMN language_code character(5)",
752 "UPDATE supported_languages SET language_code = language_code_old",
753 "ALTER TABLE supported_languages DROP COLUMN language_code_old",
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.") ;
769 $version = &get_db_version ;
770 $target = "2.6-0+checkpoint+11" ;
771 if (&is_lesser ($version, $target)) {
772 &debug ("Adding tables for the plugin subsystem.") ;
775 "CREATE SEQUENCE plugins_pk_seq",
776 "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))",
777 "CREATE SEQUENCE group_plugin_pk_seq",
778 "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)",
779 "CREATE SEQUENCE user_plugin_pk_seq",
780 "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)",
782 foreach my $s (@reqlist) {
785 $sth = $dbh->prepare ($query) ;
790 &update_db_version ($target) ;
791 &debug ("Committing.") ;
795 $version = &get_db_version ;
796 $target = "2.6-0+checkpoint+12" ;
797 if (&is_lesser ($version, $target)) {
798 &debug ("Upgrading with 20021125.sql") ;
800 @reqlist = @{ &parse_sql_file ("$sqldir/20021125.sql") } ;
801 foreach my $s (@reqlist) {
804 $sth = $dbh->prepare ($query) ;
810 &update_db_version ($target) ;
811 &debug ("Committing $target.") ;
815 $version = &get_db_version ;
816 $target = "2.6-0+checkpoint+13" ;
817 if (&is_lesser ($version, $target)) {
818 &debug ("Upgrading with 20021212.sql") ;
820 @reqlist = @{ &parse_sql_file ("$sqldir/20021212.sql") } ;
821 foreach my $s (@reqlist) {
824 $sth = $dbh->prepare ($query) ;
830 &update_db_version ($target) ;
831 &debug ("Committing $target.") ;
835 $version = &get_db_version ;
836 $target = "2.6-0+checkpoint+14" ;
837 if (&is_lesser ($version, $target)) {
838 &debug ("Upgrading with 20021213.sql") ;
840 @reqlist = @{ &parse_sql_file ("$sqldir/20021213.sql") } ;
841 foreach my $s (@reqlist) {
844 $sth = $dbh->prepare ($query) ;
850 &update_db_version ($target) ;
851 &debug ("Committing $target.") ;
855 $version = &get_db_version ;
856 $target = "2.6-0+checkpoint+15" ;
857 if (&is_lesser ($version, $target)) {
858 &debug ("Transcoding documentation data fields") ;
859 $query = "SELECT docid,data FROM doc_data ORDER BY docid ASC" ;
861 $sth = $dbh->prepare ($query) ;
863 while (@array = $sth->fetchrow_array) {
864 my $query2 = "UPDATE doc_data SET data='" ;
865 $query2 .= encode_base64 (decode_entities ($array [1])) ;
866 $query2 .= "', filename='file".$array [0].".html'";
867 $query2 .= ", filetype='text/html'";
868 $query2 .= " WHERE docid=" ;
869 $query2 .= $array [0] ;
872 my $sth2 =$dbh->prepare ($query2) ;
879 &update_db_version ($target) ;
880 &debug ("Committing $target.") ;
884 $version = &get_db_version ;
885 $target = "2.6-0+checkpoint+16" ;
886 if (&is_lesser ($version, $target)) {
887 &debug ("Upgrading with 20021214.sql") ;
889 @reqlist = @{ &parse_sql_file ("$sqldir/20021214.sql") } ;
890 foreach my $s (@reqlist) {
893 $sth = $dbh->prepare ($query) ;
899 &update_db_version ($target) ;
900 &debug ("Committing $target.") ;
904 $version = &get_db_version ;
905 $target = "2.6-0+checkpoint+17" ;
906 if (&is_lesser ($version, $target)) {
907 &debug ("Upgrading with 20021215.sql") ;
909 @reqlist = @{ &parse_sql_file ("$sqldir/20021215.sql") } ;
910 foreach my $s (@reqlist) {
913 $sth = $dbh->prepare ($query) ;
919 &update_db_version ($target) ;
920 &debug ("Committing $target.") ;
924 $version = &get_db_version ;
925 $target = "2.6-0+checkpoint+18" ;
926 if (&is_lesser ($version, $target)) {
927 &debug ("Upgrading with 20021216.sql") ;
929 @reqlist = @{ &parse_sql_file ("$sqldir/20021216.sql") } ;
930 foreach my $s (@reqlist) {
933 $sth = $dbh->prepare ($query) ;
939 &update_db_version ($target) ;
940 &debug ("Committing $target.") ;
944 $version = &get_db_version ;
945 $target = "2.6-0+checkpoint+19" ;
946 if (&is_lesser ($version, $target)) {
947 &debug ("Upgrading with 20021223.sql") ;
949 @reqlist = @{ &parse_sql_file ("$sqldir/20021223.sql") } ;
950 foreach my $s (@reqlist) {
953 $sth = $dbh->prepare ($query) ;
959 &update_db_version ($target) ;
960 &debug ("Committing $target.") ;
964 $version = &get_db_version ;
965 $target = "2.6-0+checkpoint+20" ;
966 if (&is_lesser ($version, $target)) {
967 &debug ("Upgrading with 20030102.sql") ;
969 @reqlist = @{ &parse_sql_file ("$sqldir/20030102.sql") } ;
970 foreach my $s (@reqlist) {
973 $sth = $dbh->prepare ($query) ;
979 &update_db_version ($target) ;
980 &debug ("Committing $target.") ;
984 $version = &get_db_version ;
985 $target = "2.6-0+checkpoint+21" ;
986 if (&is_lesser ($version, $target)) {
987 &debug ("Upgrading with 20030105.sql") ;
989 @reqlist = @{ &parse_sql_file ("$sqldir/20030105.sql") } ;
990 foreach my $s (@reqlist) {
993 $sth = $dbh->prepare ($query) ;
999 &update_db_version ($target) ;
1000 &debug ("Committing $target.") ;
1004 $version = &get_db_version ;
1005 $target = "2.6-0+checkpoint+22" ;
1006 if (&is_lesser ($version, $target)) {
1007 &debug ("Upgrading with 20030107.sql") ;
1009 @reqlist = @{ &parse_sql_file ("$sqldir/20030107.sql") } ;
1010 foreach my $s (@reqlist) {
1013 $sth = $dbh->prepare ($query) ;
1019 &update_db_version ($target) ;
1020 &debug ("Committing $target.") ;
1024 $version = &get_db_version ;
1025 $target = "2.6-0+checkpoint+23" ;
1026 if (&is_lesser ($version, $target)) {
1027 &debug ("Upgrading with 20030109.sql") ;
1029 @reqlist = @{ &parse_sql_file ("$sqldir/20030109.sql") } ;
1030 foreach my $s (@reqlist) {
1033 $sth = $dbh->prepare ($query) ;
1039 &update_db_version ($target) ;
1040 &debug ("Committing $target.") ;
1044 $version = &get_db_version ;
1045 $target = "2.6-0+checkpoint+24" ;
1046 if (&is_lesser ($version, $target)) {
1048 &debug ("Adjusting language sequences") ;
1050 $query = "SELECT max(language_id) FROM supported_languages" ;
1051 $sth = $dbh->prepare ($query) ;
1053 @array = $sth->fetchrow_array () ;
1055 my $maxid = $array [0] ;
1056 &bump_sequence_to ($dbh, "supported_languages_pk_seq", $maxid) ;
1058 &debug ("Upgrading with 20030112.sql") ;
1060 @reqlist = @{ &parse_sql_file ("$sqldir/20030112.sql") } ;
1061 foreach my $s (@reqlist) {
1064 $sth = $dbh->prepare ($query) ;
1070 &update_db_version ($target) ;
1071 &debug ("Committing $target.") ;
1075 $version = &get_db_version ;
1076 $target = "2.6-0+checkpoint+25" ;
1077 if (&is_lesser ($version, $target)) {
1078 &debug ("Upgrading with 20030113.sql") ;
1080 @reqlist = @{ &parse_sql_file ("$sqldir/20030113.sql") } ;
1081 foreach my $s (@reqlist) {
1084 $sth = $dbh->prepare ($query) ;
1090 &update_db_version ($target) ;
1091 &debug ("Committing $target.") ;
1095 $version = &get_db_version ;
1096 $target = "2.6-0+checkpoint+26" ;
1097 if (&is_lesser ($version, $target)) {
1098 &debug ("Upgrading with 20030131.sql") ;
1100 @reqlist = @{ &parse_sql_file ("$sqldir/20030131.sql") } ;
1101 foreach my $s (@reqlist) {
1104 $sth = $dbh->prepare ($query) ;
1110 &update_db_version ($target) ;
1111 &debug ("Committing $target.") ;
1115 $version = &get_db_version ;
1116 $target = "2.6-0+checkpoint+27" ;
1117 if (&is_lesser ($version, $target)) {
1118 &debug ("Upgrading with 20030209.sql") ;
1120 @reqlist = @{ &parse_sql_file ("$sqldir/20030209.sql") } ;
1121 foreach my $s (@reqlist) {
1124 $sth = $dbh->prepare ($query) ;
1130 &update_db_version ($target) ;
1131 &debug ("Committing $target.") ;
1135 $version = &get_db_version ;
1136 $target = "2.6-0+checkpoint+28" ;
1137 if (&is_lesser ($version, $target)) {
1138 &debug ("Upgrading with 20030312.sql") ;
1140 @reqlist = @{ &parse_sql_file ("$sqldir/20030312.sql") } ;
1141 foreach my $s (@reqlist) {
1144 $sth = $dbh->prepare ($query) ;
1150 &update_db_version ($target) ;
1151 &debug ("Committing $target.") ;
1155 $version = &get_db_version ;
1156 $target = "2.6-0+checkpoint+29" ;
1157 if (&is_lesser ($version, $target)) {
1158 &debug ("Registering KDE theme.") ;
1160 $query = "INSERT INTO themes (dirname, fullname) VALUES ('kde', 'KDE')";
1162 $sth = $dbh->prepare ($query) ;
1166 &update_db_version ($target) ;
1167 &debug ("Committing.") ;
1172 $version = &get_db_version ;
1173 $target = "2.6-0+checkpoint+30" ;
1174 if (&is_lesser ($version, $target)) {
1175 &debug ("Registering Dark Aqua theme.") ;
1177 $query = "INSERT INTO themes (dirname, fullname) VALUES ('darkaqua', 'Dark Aqua')";
1179 $sth = $dbh->prepare ($query) ;
1183 &update_db_version ($target) ;
1184 &debug ("Committing.") ;
1189 $version = &get_db_version ;
1190 $target = "2.6-0+checkpoint+31" ;
1191 if (&is_lesser ($version, $target)) {
1192 &debug ("Upgrading with 20030513.sql") ;
1194 @reqlist = @{ &parse_sql_file ("$sqldir/20030513.sql") } ;
1195 foreach my $s (@reqlist) {
1198 $sth = $dbh->prepare ($query) ;
1204 &update_db_version ($target) ;
1205 &debug ("Committing.") ;
1210 $version = &get_db_version ;
1212 if (&is_lesser ($version, $target)) {
1213 &debug ("Database schema is now version 3.0-1.") ;
1215 &update_db_version ($target) ;
1216 &debug ("Committing.") ;
1221 $version = &get_db_version ;
1223 if (&is_lesser ($version, $target)) {
1224 &debug ("Upgrading with 20030822.sql") ;
1226 @reqlist = @{ &parse_sql_file ("$sqldir/20030822.sql") } ;
1227 foreach my $s (@reqlist) {
1230 $sth = $dbh->prepare ($query) ;
1236 &update_db_version ($target) ;
1237 &debug ("Committing.") ;
1241 $version = &get_db_version ;
1242 $target = "3.1-0+1" ;
1243 if (&is_lesser ($version, $target)) {
1244 &debug ("Upgrading with 20031105.sql") ;
1246 @reqlist = @{ &parse_sql_file ("$sqldir/20031105.sql") } ;
1247 foreach my $s (@reqlist) {
1250 $sth = $dbh->prepare ($query) ;
1255 &debug ("Upgrading with 20031124.sql") ;
1257 @reqlist = @{ &parse_sql_file ("$sqldir/20031124.sql") } ;
1258 foreach my $s (@reqlist) {
1261 $sth = $dbh->prepare ($query) ;
1267 &update_db_version ($target) ;
1268 &debug ("Committing.") ;
1272 $version = &get_db_version ;
1273 $target = "3.1-0+2" ;
1274 if (&is_lesser ($version, $target)) {
1275 &debug ("Upgrading with 20031129.sql") ;
1277 @reqlist = @{ &parse_sql_file ("$sqldir/20031129.sql") } ;
1278 foreach my $s (@reqlist) {
1281 $sth = $dbh->prepare ($query) ;
1287 &update_db_version ($target) ;
1288 &debug ("Committing.") ;
1292 $version = &get_db_version ;
1293 $target = "3.1-0+3" ;
1294 if (&is_lesser ($version, $target)) {
1295 # Yes, I know. 20031126 < 20031129, yet we apply that change later.
1296 # Blame tperdue for late committing.
1297 # They are independent anyway.
1298 &debug ("Upgrading with 20031126.sql") ;
1300 @reqlist = @{ &parse_sql_file ("$sqldir/20031126.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.2.1-0+2" ;
1317 if (&is_lesser ($version, $target)) {
1318 &debug ("Upgrading with 20031205.sql") ;
1320 @reqlist = @{ &parse_sql_file ("$sqldir/20031205.sql") } ;
1321 foreach my $s (@reqlist) {
1324 $sth = $dbh->prepare ($query) ;
1330 &update_db_version ($target) ;
1331 &debug ("Committing.") ;
1335 $version = &get_db_version ;
1336 $target = "3.2.1-0+3" ;
1337 if (&is_lesser ($version, $target)) {
1338 &debug ("Upgrading with 20040130.sql") ;
1340 @reqlist = @{ &parse_sql_file ("$sqldir/20040130.sql") } ;
1341 foreach my $s (@reqlist) {
1344 $sth = $dbh->prepare ($query) ;
1350 &update_db_version ($target) ;
1351 &debug ("Committing.") ;
1355 $version = &get_db_version ;
1356 $target = "3.2.1-0+4" ;
1357 if (&is_lesser ($version, $target)) {
1358 &debug ("Upgrading with 20040204.sql") ;
1360 @reqlist = @{ &parse_sql_file ("$sqldir/20040204.sql") } ;
1361 foreach my $s (@reqlist) {
1364 $sth = $dbh->prepare ($query) ;
1370 &update_db_version ($target) ;
1371 &debug ("Committing.") ;
1375 $version = &get_db_version ;
1376 $target = "3.2.1-0+5" ;
1377 if (&is_lesser ($version, $target)) {
1378 &debug ("Upgrading with 20040315.sql") ;
1380 @reqlist = @{ &parse_sql_file ("$sqldir/20040315.sql") } ;
1381 foreach my $s (@reqlist) {
1384 $sth = $dbh->prepare ($query) ;
1390 &update_db_version ($target) ;
1391 &debug ("Committing.") ;
1395 $version = &get_db_version ;
1396 $target = "3.3.0-0+0" ;
1397 if (&is_lesser ($version, $target)) {
1398 &debug ("Upgrading with 200403251.sql") ;
1400 @reqlist = @{ &parse_sql_file ("$sqldir/200403251.sql") } ;
1401 foreach my $s (@reqlist) {
1404 $sth = $dbh->prepare ($query) ;
1410 &update_db_version ($target) ;
1411 &debug ("Committing.") ;
1415 $version = &get_db_version ;
1416 $target = "3.3.0-0+1" ;
1417 if (&is_lesser ($version, $target)) {
1418 &debug ("Upgrading with 200403252.sql") ;
1420 @reqlist = @{ &parse_sql_file ("$sqldir/200403252.sql") } ;
1421 foreach my $s (@reqlist) {
1424 $sth = $dbh->prepare ($query) ;
1430 &update_db_version ($target) ;
1431 &debug ("Committing.") ;
1435 $version = &get_db_version ;
1436 $target = "3.3.0-0+3" ;
1437 if (&is_lesser ($version, $target)) {
1438 &debug ("Upgrading with 20040507.sql") ;
1440 @reqlist = @{ &parse_sql_file ("$sqldir/20040507.sql") } ;
1441 foreach my $s (@reqlist) {
1444 $sth = $dbh->prepare ($query) ;
1450 &update_db_version ($target) ;
1451 &debug ("Committing.") ;
1455 $version = &get_db_version ;
1456 $target = "3.3.0-0+4" ;
1457 if (&is_lesser ($version, $target)) {
1458 &debug ("Upgrading with 20040722.sql") ;
1460 @reqlist = @{ &parse_sql_file ("$sqldir/20040722.sql") } ;
1461 foreach my $s (@reqlist) {
1464 $sth = $dbh->prepare ($query) ;
1470 &update_db_version ($target) ;
1471 &debug ("Committing.") ;
1475 $version = &get_db_version ;
1476 $target = "3.3.0-0+6" ;
1477 if (&is_lesser ($version, $target)) {
1478 &debug ("Upgrading with 20040804.sql") ;
1480 @reqlist = @{ &parse_sql_file ("$sqldir/20040804.sql") } ;
1481 foreach my $s (@reqlist) {
1484 $sth = $dbh->prepare ($query) ;
1490 &update_db_version ($target) ;
1491 &debug ("Committing.") ;
1495 $version = &get_db_version ;
1496 $target = "3.3.0-0+7" ;
1497 if (&is_lesser ($version, $target)) {
1498 &debug ("Upgrading with 20040826.sql") ;
1500 @reqlist = @{ &parse_sql_file ("$sqldir/20040826.sql") } ;
1501 foreach my $s (@reqlist) {
1504 $sth = $dbh->prepare ($query) ;
1510 &update_db_version ($target) ;
1511 &debug ("Committing.") ;
1515 $version = &get_db_version ;
1516 $target = "3.3.0-2+1" ;
1517 if (&is_lesser ($version, $target)) {
1518 &debug ("Migrating forum names") ;
1520 $query = "SELECT group_forum_id,forum_name FROM forum_group_list" ;
1522 $sth = $dbh->prepare ($query) ;
1524 while (@array = $sth->fetchrow_array) {
1525 my $forumid = $array[0] ;
1526 my $oldname = $array[1] ;
1528 my $newname = lc $oldname ;
1529 $newname =~ s/[^_.0-9a-z-]/-/g ;
1531 my $query2 = "UPDATE forum_group_list SET forum_name='$newname' WHERE group_forum_id=$forumid" ;
1532 # &debug ($query2) ;
1533 my $sth2 =$dbh->prepare ($query2) ;
1539 &update_db_version ($target) ;
1540 &debug ("Committing.") ;
1544 $version = &get_db_version ;
1545 $target = "3.3.0-2+2" ;
1546 if (&is_lesser ($version, $target)) {
1547 &debug ("Migrating permissions to RBAC") ;
1549 my $defaultroles = {
1550 'Admin' => { 'projectadmin'=>'A', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'forum'=>'2', 'trackeradmin'=>'2', 'tracker'=>'2', 'pmadmin'=>'2', 'pm'=>'2' },
1551 'Senior Developer' => { 'projectadmin'=>'0', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'forum'=>'2', 'trackeradmin'=>'2', 'tracker'=>'2', 'pmadmin'=>'2', 'pm'=>'2' },
1552 'Junior Developer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'1', 'docman'=>'0', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'1', 'pmadmin'=>'0', 'pm'=>'1' },
1553 'Doc Writer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'0', 'pmadmin'=>'0', 'pm'=>'0' },
1554 'Support Tech' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'2', 'pmadmin'=>'0', 'pm'=>'0' }
1557 $query = "SELECT group_id FROM groups where status != 'P'" ;
1559 $sth = $dbh->prepare ($query) ;
1561 while (@array = $sth->fetchrow_array) {
1562 my $group_id = $array[0] ;
1564 my ($query2, $sth2, @array2, $admin_rid, $jd_rid, %roledata) ;
1565 foreach my $rname (keys %$defaultroles) {
1566 $query2 = "SELECT nextval('role_role_id_seq'::text)" ;
1567 # &debug ($query2) ;
1568 $sth2 =$dbh->prepare ($query2) ;
1570 @array2 = $sth2->fetchrow_array ;
1571 my $rid = $array2[0] ;
1573 if ($rname eq 'Admin') {
1575 } elsif ($rname eq 'Junior Developer') {
1579 $query2 = "INSERT INTO role (role_id, group_id, role_name)
1580 VALUES ($rid, $group_id, '$rname')" ;
1581 # &debug ($query2) ;
1582 $sth2 =$dbh->prepare ($query2) ;
1586 foreach my $section (keys %{$defaultroles->{$rname}}) {
1587 if ($section eq 'forum') {
1588 $query2 = "SELECT group_forum_id
1589 FROM forum_group_list
1590 WHERE group_id = $group_id" ;
1591 # &debug ($query2) ;
1592 $sth2 =$dbh->prepare ($query2) ;
1594 while (@array2 = $sth2->fetchrow_array) {
1595 $roledata{'forum'}{$array2[0]} = $defaultroles->{$rname}{'forum'} ;
1598 } elsif ($section eq 'pm') {
1599 $query2 = "SELECT group_project_id
1600 FROM project_group_list
1601 WHERE group_id = $group_id" ;
1602 # &debug ($query2) ;
1603 $sth2 =$dbh->prepare ($query2) ;
1605 while (@array2 = $sth2->fetchrow_array) {
1606 $roledata{'pm'}{$array2[0]} = $defaultroles->{$rname}{'pm'} ;
1609 } elsif ($section eq 'tracker') {
1610 $query2 = "SELECT group_artifact_id
1611 FROM artifact_group_list
1612 WHERE group_id = $group_id" ;
1613 # &debug ($query2) ;
1614 $sth2 =$dbh->prepare ($query2) ;
1616 while (@array2 = $sth2->fetchrow_array) {
1617 $roledata{'tracker'}{$array2[0]} = $defaultroles->{$rname}{'tracker'} ;
1621 $roledata{$section}{0} = $defaultroles->{$rname}{$section} ;
1624 foreach my $rd_it (keys %{$roledata{$section}}) {
1625 $query2 = "INSERT INTO role_setting (role_id, section_name, ref_id, value)
1626 VALUES ($rid, '$section', $rd_it, '$roledata{$section}{$rd_it}')" ;
1627 # &debug ($query2) ;
1628 $sth2 =$dbh->prepare ($query2) ;
1637 # affecter le rôle Admin aux admins, JD aux autres
1638 $query2 = "SELECT user_id, admin_flags FROM user_group WHERE group_id = $group_id" ;
1639 # &debug ($query2) ;
1640 $sth2 =$dbh->prepare ($query2) ;
1642 while (@array2 = $sth2->fetchrow_array) {
1643 my $uid = $array2[0] ;
1644 my $adminflags = $array2[1] ;
1647 $adminflags =~ s/\s//g ;
1648 if ($adminflags eq 'A') {
1653 $rname = 'Junior Developer' ;
1658 admin_flags = '$defaultroles->{$rname}{'projectadmin'}',
1659 forum_flags = '$defaultroles->{$rname}{'forumadmin'}',
1660 project_flags = '$defaultroles->{$rname}{'pmadmin'}',
1661 doc_flags = '$defaultroles->{$rname}{'docman'}',
1662 cvs_flags = '$defaultroles->{$rname}{'scm'}',
1663 release_flags = '$defaultroles->{$rname}{'frs'}',
1664 artifact_flags = '$defaultroles->{$rname}{'trackeradmin'}'
1665 WHERE user_id = $uid AND group_id = $group_id" ,
1667 SET perm_level=$defaultroles->{$rname}{'forum'}
1668 WHERE group_forum_id IN (
1669 SELECT group_forum_id
1670 FROM forum_group_list
1671 WHERE group_id=$group_id)
1673 "UPDATE project_perm
1674 SET perm_level=$defaultroles->{$rname}{'pm'}
1675 WHERE group_project_id IN (
1676 SELECT group_project_id
1677 FROM project_group_list
1678 WHERE group_id=$group_id)
1680 "UPDATE artifact_perm
1681 SET perm_level=$defaultroles->{$rname}{'tracker'}
1682 WHERE group_artifact_id IN (
1683 SELECT group_artifact_id
1684 FROM artifact_group_list
1685 WHERE group_id=$group_id)
1688 foreach my $query3 (@reqlist3) {
1689 # &debug ($query3) ;
1690 my $sth3 = $dbh->prepare ($query3) ;
1699 &update_db_version ($target) ;
1700 &debug ("Committing.") ;
1704 $version = &get_db_version ;
1705 $target = "3.3.0-2+4" ;
1706 if (&is_lesser ($version, $target)) {
1707 &debug ("Upgrading with 20040914.sql") ;
1709 @reqlist = @{ &parse_sql_file ("$sqldir/20040914.sql") } ;
1710 foreach my $s (@reqlist) {
1713 $sth = $dbh->prepare ($query) ;
1719 &update_db_version ($target) ;
1720 &debug ("Committing.") ;
1724 $version = &get_db_version ;
1725 $target = "3.3.0-2+4+1" ;
1726 if (&is_lesser ($version, $target)) {
1727 &debug ("Upgrading with 20041001.sql") ;
1729 @reqlist = @{ &parse_sql_file ("$sqldir/20041001.sql") } ;
1730 foreach my $s (@reqlist) {
1733 $sth = $dbh->prepare ($query) ;
1739 &update_db_version ($target) ;
1740 &debug ("Committing.") ;
1744 $version = &get_db_version ;
1745 $target = "3.3.0-2+5" ;
1746 if (&is_lesser ($version, $target)) {
1747 &debug ("Upgrading with 20041005.sql") ;
1749 @reqlist = @{ &parse_sql_file ("$sqldir/20041005.sql") } ;
1750 foreach my $s (@reqlist) {
1753 $sth = $dbh->prepare ($query) ;
1759 &update_db_version ($target) ;
1760 &debug ("Committing.") ;
1764 $version = &get_db_version ;
1765 $target = "3.3.0-2+6" ;
1766 if (&is_lesser ($version, $target)) {
1767 &debug ("Upgrading with 20041006.sql") ;
1769 @reqlist = @{ &parse_sql_file ("$sqldir/20041006.sql") } ;
1770 foreach my $s (@reqlist) {
1773 $sth = $dbh->prepare ($query) ;
1779 &update_db_version ($target) ;
1780 &debug ("Committing.") ;
1784 $version = &get_db_version ;
1785 $target = "3.3.0-3" ;
1786 if (&is_lesser ($version, $target)) {
1787 &debug ("Upgrading with 20041014.sql") ;
1789 @reqlist = @{ &parse_sql_file ("$sqldir/20041014.sql") } ;
1790 foreach my $s (@reqlist) {
1793 $sth = $dbh->prepare ($query) ;
1799 &update_db_version ($target) ;
1800 &debug ("Committing.") ;
1804 $version = &get_db_version ;
1805 $target = "3.3.0-4" ;
1806 if (&is_lesser ($version, $target)) {
1807 &debug ("Upgrading with 20041020.sql") ;
1809 @reqlist = @{ &parse_sql_file ("$sqldir/20041020.sql") } ;
1810 foreach my $s (@reqlist) {
1813 $sth = $dbh->prepare ($query) ;
1819 &update_db_version ($target) ;
1820 &debug ("Committing.") ;
1824 $version = &get_db_version ;
1825 $target = "4.0.0-0" ;
1826 # This is an exception, I reapply a modified version of 20040729.sql since it was doing nothing
1827 # the other call was deleted from this file
1828 if (&is_lesser ($version, $target)) {
1829 &debug ("Upgrading with 20040729.sql") ;
1831 @reqlist = @{ &parse_sql_file ("$sqldir/20040729.sql") } ;
1832 foreach my $s (@reqlist) {
1835 $sth = $dbh->prepare ($query) ;
1841 &update_db_version ($target) ;
1842 &debug ("Committing.") ;
1846 $version = &get_db_version ;
1847 $target = "4.0.0-0+1" ;
1848 if (&is_lesser ($version, $target)) {
1849 &debug ("Granting read access permissions to NSS") ;
1851 @reqlist = ( "GRANT SELECT ON nss_passwd TO gforge_nss",
1852 "GRANT SELECT ON nss_groups TO gforge_nss",
1853 "GRANT SELECT ON nss_usergroups TO gforge_nss",
1855 foreach my $s (@reqlist) {
1858 $sth = $dbh->prepare ($query) ;
1864 &update_db_version ($target) ;
1865 &debug ("Committing.") ;
1869 $version = &get_db_version ;
1870 $target = "4.0.0-0+2" ;
1871 if (&is_lesser ($version, $target)) {
1872 &debug ("Upgrading with 20041031.sql") ;
1874 @reqlist = @{ &parse_sql_file ("$sqldir/20041031.sql") } ;
1875 foreach my $s (@reqlist) {
1878 $sth = $dbh->prepare ($query) ;
1884 &debug ("Granting read access permissions to NSS") ;
1886 @reqlist = ( "GRANT SELECT ON mta_users TO gforge_mta",
1887 "GRANT SELECT ON mta_lists TO gforge_mta",
1889 foreach my $s (@reqlist) {
1892 $sth = $dbh->prepare ($query) ;
1898 &update_db_version ($target) ;
1899 &debug ("Committing.") ;
1903 $version = &get_db_version ;
1904 $target = "4.0.0-0+3" ;
1905 # This is an exception, I reapply a modified version of 20040729.sql since it was doing nothing
1906 # the other call was deleted from this file
1907 if (&is_lesser ($version, $target)) {
1908 &debug ("Upgrading with 20041104.sql") ;
1910 @reqlist = @{ &parse_sql_file ("$sqldir/20041104.sql") } ;
1911 foreach my $s (@reqlist) {
1914 $sth = $dbh->prepare ($query) ;
1920 &update_db_version ($target) ;
1921 &debug ("Committing.") ;
1925 $version = &get_db_version ;
1926 $target = "4.0.0-0+4" ;
1927 if (&is_lesser ($version, $target)) {
1928 &debug ("Upgrading with 20041108.sql") ;
1930 @reqlist = @{ &parse_sql_file ("$sqldir/20041108.sql") } ;
1931 foreach my $s (@reqlist) {
1934 $sth = $dbh->prepare ($query) ;
1940 &update_db_version ($target) ;
1941 &debug ("Committing.") ;
1945 $version = &get_db_version ;
1946 $target = "4.0.2-0+0" ;
1947 if (&is_lesser ($version, $target)) {
1948 &debug ("Upgrading with 20041124.sql") ;
1950 @reqlist = @{ &parse_sql_file ("$sqldir/20041124.sql") } ;
1951 foreach my $s (@reqlist) {
1954 $sth = $dbh->prepare ($query) ;
1960 &update_db_version ($target) ;
1961 &debug ("Committing.") ;
1965 $version = &get_db_version ;
1966 $target = "4.0.2-0+1" ;
1967 if (&is_lesser ($version, $target)) {
1968 &debug ("Creating automatic commit notification mailing-lists") ;
1971 $query = "SELECT group_id, unix_group_name FROM groups WHERE status='A' ORDER BY group_id" ;
1973 $sth = $dbh->prepare ($query) ;
1975 while (@array = $sth->fetchrow_array) {
1976 my $group_id = $array[0] ;
1977 my $group_name = $array[1] ;
1979 my $query2 = "SELECT count(*) FROM mail_group_list
1980 WHERE group_id = $group_id
1981 AND list_name = '".$group_name."-commits'" ;
1982 # &debug ($query2) ;
1983 my $sth2 =$dbh->prepare ($query2) ;
1985 my @array2 = $sth2->fetchrow_array ;
1987 if ($array2[0] == 0) {
1988 my $listname = $group_name."-commits" ;
1989 my $listpw = substr (Digest::MD5::md5_base64 ($listname . rand(1)), 0, 16) ;
1992 $query2 = "SELECT user_id FROM user_group
1993 WHERE admin_flags = 'A'
1994 AND group_id = $group_id" ;
1995 # &debug ($query2) ;
1996 $sth2 =$dbh->prepare ($query2) ;
1998 my $group_admin = -1 ;
1999 if (@array2 = $sth2->fetchrow_array) {
2000 $group_admin = $array2[0] ;
2004 $query2 = "INSERT INTO mail_group_list (group_id, list_name, is_public, password, list_admin, status, description)
2005 VALUES ($group_id, '$listname', 1, '$listpw', $group_admin, 1, 'commits')" ;
2006 # &debug ($query2) ;
2007 $sth2 =$dbh->prepare ($query2) ;
2014 &update_db_version ($target) ;
2015 &debug ("Committing.") ;
2019 # $version = &get_db_version ;
2020 # $target = "4.0.2-0+2" ;
2021 # if (&is_lesser ($version, $target)) {
2022 # &debug ("Upgrading with 20041222-debian.sql") ;
2024 # @reqlist = @{ &parse_sql_file ("$sqldir/20041222-debian.sql") } ;
2025 # foreach my $s (@reqlist) {
2028 # $sth = $dbh->prepare ($query) ;
2029 # $sth->execute () ;
2034 # &update_db_version ($target) ;
2035 # &debug ("Committing.") ;
2039 $version = &get_db_version ;
2040 $target = "4.0.2-0+3" ;
2041 if (&is_lesser ($version, $target)) {
2042 &debug ("Upgrading with 20050115.sql") ;
2044 @reqlist = @{ &parse_sql_file ("$sqldir/20050115.sql") } ;
2045 foreach my $s (@reqlist) {
2048 $sth = $dbh->prepare ($query) ;
2054 &update_db_version ($target) ;
2055 &debug ("Committing.") ;
2060 # We got this at upgrade
2062 #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.
2063 #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.
2064 #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.
2065 #Last SQL query was:
2066 #update project_task SET last_modified_date=EXTRACT(EPOCH FROM now())::integer;
2068 #Your database schema is at version 4.0.2-0+5
2070 # This is a hack to disconnect and reconnect the DB and solve the problem
2076 $dbh->{AutoCommit} = 0;
2077 $dbh->{RaiseError} = 1;
2079 $version = &get_db_version ;
2080 $target = "4.0.2-0+5" ;
2081 if (&is_lesser ($version, $target)) {
2082 &debug ("Upgrading with 20050130.sql") ;
2084 @reqlist = @{ &parse_sql_file ("$sqldir/20050130.sql") } ;
2085 foreach my $s (@reqlist) {
2088 $sth = $dbh->prepare ($query) ;
2094 &update_db_version ($target) ;
2095 &debug ("Committing.") ;
2099 $version = &get_db_version ;
2100 $target = "4.0.2-0+6" ;
2101 if (&is_lesser ($version, $target)) {
2102 &debug ("Upgrading with 20050212.sql") ;
2104 @reqlist = @{ &parse_sql_file ("$sqldir/20050212.sql") } ;
2105 foreach my $s (@reqlist) {
2108 $sth = $dbh->prepare ($query) ;
2114 &update_db_version ($target) ;
2115 &debug ("Committing.") ;
2119 $version = &get_db_version ;
2120 $target = "4.0.2-0+7" ;
2121 if (&is_lesser ($version, $target)) {
2122 &debug ("Upgrading with 20050214-nss.sql valantine") ;
2124 @reqlist = @{ &parse_sql_file ("$sqldir/20050214-nss.sql") } ;
2125 foreach my $s (@reqlist) {
2128 $sth = $dbh->prepare ($query) ;
2134 &update_db_version ($target) ;
2135 &debug ("Committing.") ;
2139 $version = &get_db_version ;
2141 if (&is_lesser ($version, $target)) {
2142 &debug ("Upgrading with 20050224.sql") ;
2144 @reqlist = @{ &parse_sql_file ("$sqldir/20050224.sql") } ;
2145 foreach my $s (@reqlist) {
2148 $sth = $dbh->prepare ($query) ;
2154 &update_db_version ($target) ;
2155 &debug ("Committing.") ;
2159 $version = &get_db_version ;
2161 if (&is_lesser ($version, $target)) {
2162 &debug ("Upgrading with 20050225-nsssetup.sql") ;
2164 @reqlist = @{ &parse_sql_file ("$sqldir/20050225-nsssetup.sql") } ;
2165 foreach my $s (@reqlist) {
2168 $sth = $dbh->prepare ($query) ;
2174 &update_db_version ($target) ;
2175 &debug ("Committing.") ;
2179 $version = &get_db_version ;
2181 if (&is_lesser ($version, $target)) {
2182 &debug ("Upgrading with 20050311.sql") ;
2184 @reqlist = @{ &parse_sql_file ("$sqldir/20050311.sql") } ;
2185 foreach my $s (@reqlist) {
2188 $sth = $dbh->prepare ($query) ;
2194 &update_db_version ($target) ;
2195 &debug ("Committing.") ;
2199 $version = &get_db_version ;
2201 if (&is_lesser ($version, $target)) {
2202 &debug ("Upgrading with 20050315.sql") ;
2204 @reqlist = @{ &parse_sql_file ("$sqldir/20050315.sql") } ;
2205 foreach my $s (@reqlist) {
2208 $sth = $dbh->prepare ($query) ;
2214 &update_db_version ($target) ;
2215 &debug ("Committing.") ;
2219 $version = &get_db_version ;
2221 if (&is_lesser ($version, $target)) {
2222 &debug ("Upgrading with 20050325-1.sql") ;
2224 @reqlist = @{ &parse_sql_file ("$sqldir/20050325-1.sql") } ;
2225 foreach my $s (@reqlist) {
2228 $sth = $dbh->prepare ($query) ;
2234 &update_db_version ($target) ;
2235 &debug ("Committing.") ;
2239 $version = &get_db_version ;
2241 if (&is_lesser ($version, $target)) {
2242 &debug ("Converting trackers to use their extra fields") ;
2244 $query = "SELECT group_id,group_artifact_id,use_resolution FROM artifact_group_list" ;
2246 $sth = $dbh->prepare ($query) ;
2248 while (@array = $sth->fetchrow_array) {
2249 my $group_id = $array[0] ;
2250 my $gaid = $array[1] ;
2251 my $ur = $array[2] ;
2253 # Ajout du champ Category
2254 my $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
2255 # &debug ($query2) ;
2256 my $sth2 = $dbh->prepare ($query2) ;
2258 my @array2 = $sth2->fetchrow_array ;
2260 my $aefid = $array2[0] ;
2262 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
2263 VALUES ($aefid, $gaid, 'Category', 1)" ;
2264 # &debug ($query2) ;
2265 $sth2 =$dbh->prepare ($query2) ;
2268 $query2 = "SELECT id, category_name FROM artifact_category WHERE group_artifact_id=$gaid" ;
2269 # &debug ($query2) ;
2270 $sth2 = $dbh->prepare ($query2) ;
2273 while (@array2 = $sth2->fetchrow_array) {
2274 my $cat_id = $array2[0] ;
2275 my $catname = $array2[1] ;
2277 if ($catname eq '') { $catname = '[empty]' ; }
2279 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
2280 # &debug ($query3) ;
2281 my $sth3 = $dbh->prepare ($query3) ;
2283 my @array3 = $sth3->fetchrow_array ;
2285 my $efeid = $array3[0] ;
2287 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
2288 VALUES ($efeid, $aefid, ?, 0)" ;
2289 # &debug ($query3) ;
2290 $sth3 =$dbh->prepare ($query3) ;
2291 $sth3->execute ($catname) ;
2294 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
2295 SELECT artifact_id,$efeid,$aefid FROM artifact
2296 WHERE category_id=$cat_id" ;
2297 # &debug ($query3) ;
2298 $sth3 =$dbh->prepare ($query3) ;
2302 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Category'
2303 WHERE old_value='$cat_id' AND field_name='category_id'" ;
2304 # &debug ($query3) ;
2305 $sth3 =$dbh->prepare ($query3) ;
2306 $sth3->execute ($catname) ;
2311 # Ajout du champ Group
2312 $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
2313 # &debug ($query2) ;
2314 $sth2 = $dbh->prepare ($query2) ;
2316 @array2 = $sth2->fetchrow_array ;
2318 $aefid = $array2[0] ;
2320 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
2321 VALUES ($aefid, $gaid, 'Group', 1)" ;
2322 # &debug ($query2) ;
2323 $sth2 =$dbh->prepare ($query2) ;
2326 $query2 = "SELECT id, group_name FROM artifact_group WHERE group_artifact_id=$gaid" ;
2327 # &debug ($query2) ;
2328 $sth2 = $dbh->prepare ($query2) ;
2331 while (@array2 = $sth2->fetchrow_array) {
2332 my $grp_id = $array2[0] ;
2333 my $grpname = $array2[1] ;
2335 if ($grpname eq '') { $grpname = '[empty]' ; }
2337 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
2338 # &debug ($query3) ;
2339 my $sth3 = $dbh->prepare ($query3) ;
2341 my @array3 = $sth3->fetchrow_array ;
2343 my $efeid = $array3[0] ;
2345 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
2346 VALUES ($efeid, $aefid, ?, 0)" ;
2347 # &debug ($query3) ;
2348 $sth3 =$dbh->prepare ($query3) ;
2349 $sth3->execute ($grpname) ;
2352 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
2353 SELECT artifact_id,$efeid,$aefid FROM artifact
2354 WHERE artifact_group_id=$grp_id" ;
2355 # &debug ($query3) ;
2356 $sth3 =$dbh->prepare ($query3) ;
2360 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Group'
2361 WHERE old_value='$grp_id' AND field_name='artifact_group_id'" ;
2362 # &debug ($query3) ;
2363 $sth3 =$dbh->prepare ($query3) ;
2364 $sth3->execute ($grpname) ;
2369 # Ajout du champ Resolution (s'il existe, cf. $ur)
2371 $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
2372 # &debug ($query2) ;
2373 $sth2 = $dbh->prepare ($query2) ;
2375 @array2 = $sth2->fetchrow_array ;
2377 $aefid = $array2[0] ;
2379 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
2380 VALUES ($aefid, $gaid, 'Resolution', 1)" ;
2381 # &debug ($query2) ;
2382 $sth2 =$dbh->prepare ($query2) ;
2385 $query2 = "SELECT id, resolution_name FROM artifact_resolution" ;
2386 # &debug ($query2) ;
2387 $sth2 = $dbh->prepare ($query2) ;
2390 while (@array2 = $sth2->fetchrow_array) {
2391 my $res_id = $array2[0] ;
2392 my $resname = $array2[1] ;
2394 if ($resname eq '') { $resname = '[empty]' ; }
2396 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
2397 # &debug ($query3) ;
2398 my $sth3 = $dbh->prepare ($query3) ;
2400 my @array3 = $sth3->fetchrow_array ;
2402 my $efeid = $array3[0] ;
2404 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
2405 VALUES ($efeid, $aefid, ?, 0)" ;
2406 # &debug ($query3) ;
2407 $sth3 =$dbh->prepare ($query3) ;
2408 $sth3->execute ($resname) ;
2411 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
2412 SELECT artifact_id,$efeid,$aefid FROM artifact
2413 WHERE resolution_id=$res_id and group_artifact_id=$gaid" ;
2414 # &debug ($query3) ;
2415 $sth3 =$dbh->prepare ($query3) ;
2419 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Resolution'
2420 WHERE old_value='$res_id' AND field_name='resolution_id'" ;
2421 # &debug ($query3) ;
2422 $sth3 =$dbh->prepare ($query3) ;
2423 $sth3->execute ($resname) ;
2430 &update_db_version ($target) ;
2431 &debug ("Committing.") ;
2435 $version = &get_db_version ;
2437 if (&is_lesser ($version, $target)) {
2438 &debug ("Upgrading with 20050325-3.sql") ;
2440 @reqlist = @{ &parse_sql_file ("$sqldir/20050325-3.sql") } ;
2441 foreach my $s (@reqlist) {
2444 $sth = $dbh->prepare ($query) ;
2450 &update_db_version ($target) ;
2451 &debug ("Committing.") ;
2455 $version = &get_db_version ;
2457 if (&is_lesser ($version, $target)) {
2458 &debug ("Upgrading with 20050605.sql") ;
2460 @reqlist = @{ &parse_sql_file ("$sqldir/20050605.sql") } ;
2461 foreach my $s (@reqlist) {
2464 $sth = $dbh->prepare ($query) ;
2470 &update_db_version ($target) ;
2471 &debug ("Committing.") ;
2475 $version = &get_db_version ;
2477 if (&is_lesser ($version, $target)) {
2478 &debug ("Creating aliases for the extra fields") ;
2480 $query = "ALTER TABLE artifact_extra_field_list ADD COLUMN alias TEXT" ;
2482 $sth = $dbh->prepare ($query) ;
2486 my %reserved_alias = (
2495 $query = "SELECT field_name, alias, group_artifact_id, extra_field_id FROM artifact_extra_field_list" ;
2497 $sth = $dbh->prepare ($query) ;
2499 while (@array = $sth->fetchrow_array) {
2500 my $name = $array[0] ;
2501 my $alias = $array[1] ;
2502 my $gaid = $array[2] ;
2503 my $efid = $array[3] ;
2506 my $newalias = lc $name ;
2507 $newalias =~ s/\s/_/g ;
2508 $newalias =~ s/[^_a-z]//g ;
2510 if ($newalias ne "") {
2511 if ($reserved_alias{$newalias}) {
2512 $newalias = "extra_" . $newalias ;
2519 $candidate = $newalias ;
2520 $candidate .= $count if ($count > 0) ;
2521 my $query2 = "SELECT count(*) FROM artifact_extra_field_list WHERE group_artifact_id=$gaid AND LOWER(alias)='$candidate' AND extra_field_id <> $efid" ;
2522 # &debug ($query2) ;
2523 my $sth2 =$dbh->prepare ($query2) ;
2525 my @array2 = $sth2->fetchrow_array ;
2526 if ($array2[0] == 0) {
2533 } until ($conflict == 0) ;
2535 my $query2 = "UPDATE artifact_extra_field_list SET alias='$candidate' WHERE extra_field_id=$efid" ;
2536 # &debug ($query2) ;
2537 my $sth2 =$dbh->prepare ($query2) ;
2546 &update_db_version ($target) ;
2547 &debug ("Committing.") ;
2551 $version = &get_db_version ;
2553 if (&is_lesser ($version, $target)) {
2554 &debug ("Upgrading with 20050628.sql") ;
2556 @reqlist = @{ &parse_sql_file ("$sqldir/20050628.sql") } ;
2557 foreach my $s (@reqlist) {
2560 $sth = $dbh->prepare ($query) ;
2566 &update_db_version ($target) ;
2567 &debug ("Committing.") ;
2571 $version = &get_db_version ;
2573 if (&is_lesser ($version, $target)) {
2574 &debug ("Upgrading with 20050711.sql") ;
2576 @reqlist = @{ &parse_sql_file ("$sqldir/20050711.sql") } ;
2577 foreach my $s (@reqlist) {
2580 $sth = $dbh->prepare ($query) ;
2586 &update_db_version ($target) ;
2587 &debug ("Committing.") ;
2591 &update_with_sql("20050906","4.5-2");
2592 # 20051027-1 was renamed 20050804-1
2593 #&update_with_sql("20051027-1","4.5-3");
2594 &update_with_sql("20050804-1","4.5-3");
2596 $version = &get_db_version ;
2598 if (&is_lesser ($version, $target)) {
2599 &debug ("Updating document sizes") ;
2601 $query = "SELECT docid, data FROM doc_data" ;
2603 $sth = $dbh->prepare ($query) ;
2605 while (@array = $sth->fetchrow_array) {
2606 my $docid = $array[0] ;
2607 my $b64data = $array[1] ;
2608 my $data = decode_base64 ($b64data) ;
2609 my $size = length ($data) ;
2611 my $query2 = "UPDATE doc_data SET filesize=$size WHERE docid=$docid" ;
2612 # &debug ($query2) ;
2613 my $sth2 =$dbh->prepare ($query2) ;
2619 &update_db_version ($target) ;
2620 &debug ("Committing.") ;
2624 $version = &get_db_version ;
2625 $target = "4.5.14-3" ;
2626 if (&is_lesser ($version, $target)) {
2627 &debug ("Setting up time tracking") ;
2629 if (&table_exists ($dbh, "rep_time_category")) {
2630 &debug ("...already set up.") ;
2632 &drop_table_if_exists ($dbh, "rep_time_category") ;
2633 &drop_sequence_if_exists ($dbh, "rep_time_category_time_code_seq") ;
2634 &drop_table_if_exists ($dbh, "rep_time_tracking") ;
2635 &drop_table_if_exists ($dbh, "rep_users_added_daily") ;
2636 &drop_table_if_exists ($dbh, "rep_users_added_weekly") ;
2637 &drop_table_if_exists ($dbh, "rep_users_added_monthly") ;
2638 &drop_table_if_exists ($dbh, "rep_users_cum_daily") ;
2639 &drop_table_if_exists ($dbh, "rep_users_cum_weekly") ;
2640 &drop_table_if_exists ($dbh, "rep_users_cum_monthly") ;
2641 &drop_table_if_exists ($dbh, "rep_groups_added_daily") ;
2642 &drop_table_if_exists ($dbh, "rep_groups_added_weekly") ;
2643 &drop_table_if_exists ($dbh, "rep_groups_added_monthly") ;
2644 &drop_table_if_exists ($dbh, "rep_groups_cum_daily") ;
2645 &drop_table_if_exists ($dbh, "rep_groups_cum_weekly") ;
2646 &drop_table_if_exists ($dbh, "rep_groups_cum_monthly") ;
2647 &drop_view_if_exists ($dbh, "rep_group_act_oa_vw") ;
2648 &drop_view_if_exists ($dbh, "rep_user_act_oa_vw") ;
2649 &drop_view_if_exists ($dbh, "rep_site_act_daily_vw") ;
2650 &drop_view_if_exists ($dbh, "rep_site_act_weekly_vw") ;
2651 &drop_view_if_exists ($dbh, "rep_site_act_monthly_vw") ;
2652 &drop_table_if_exists ($dbh, "rep_user_act_daily") ;
2653 &drop_table_if_exists ($dbh, "rep_user_act_weekly") ;
2654 &drop_table_if_exists ($dbh, "rep_user_act_monthly") ;
2655 &drop_table_if_exists ($dbh, "rep_group_act_daily") ;
2656 &drop_index_if_exists ($dbh, "repgroupactdaily_daily") ;
2657 &drop_table_if_exists ($dbh, "rep_group_act_weekly") ;
2658 &drop_index_if_exists ($dbh, "repgroupactweekly_weekly") ;
2659 &drop_table_if_exists ($dbh, "rep_group_act_monthly") ;
2660 &drop_index_if_exists ($dbh, "repgroupactmonthly_monthly") ;
2662 @reqlist = @{ &parse_sql_file ("$sqldir/timetracking-init.sql") } ;
2663 foreach my $s (@reqlist) {
2666 $sth = $dbh->prepare ($query) ;
2673 &update_db_version ($target) ;
2674 &debug ("Committing.") ;
2678 # I had to increase versions from 4.5.14 to 4.5.15
2679 # The activity view is created by 20060216-nocommit
2680 # If the view doesn't exists apply
2681 if (! &view_exists ($dbh, 'activity_vw')) {
2682 &update_with_sql("20050812","4.5.15-10merge");
2683 &update_with_sql("20050822","4.5.15-11merge");
2684 &update_with_sql("20050823","4.5.15-12merge");
2685 &update_with_sql("20050824","4.5.15-13merge");
2686 &update_with_sql("20050831","4.5.15-14merge");
2688 &update_with_sql("20060113","4.5.15-15");
2689 &update_with_sql("20060214","4.5.15-16");
2690 &update_with_sql("20060216-nocommit","4.5.15-17");
2693 $version = &get_db_version ;
2694 $target = "4.5.15-21" ;
2695 if (&is_lesser ($version, $target)) {
2696 &debug ("Fixing past mistakes in role naming") ;
2698 my $defaultroles_restricted = {
2699 'Admin' => { 'projectadmin'=>'A', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'trackeradmin'=>'2', 'pmadmin'=>'2' },
2700 'Senior Developer' => { 'projectadmin'=>'0', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'trackeradmin'=>'2', 'pmadmin'=>'2' },
2701 'Junior Developer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'1', 'docman'=>'0', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' },
2702 'Doc Writer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' },
2703 'Support Tech' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' }
2706 foreach my $drname (keys %{$defaultroles_restricted}) {
2707 $query = "UPDATE role SET role_name='$drname' WHERE role_id IN (SELECT role.role_id" ;
2712 foreach my $setting (keys %{$defaultroles_restricted->{$drname}}) {
2713 $value = $defaultroles_restricted->{$drname}->{$setting} ;
2714 $from .= ", role_setting rs_$setting" ;
2715 $where .= "role.role_id = rs_$setting.role_id AND rs_$setting.section_name='$setting' AND " ;
2716 $where .= "rs_$setting.value = '$value' \nAND " ;
2718 $query .= "\nFROM role$from" ;
2719 $query .= "\nWHERE $where role.role_name='rname')";
2720 push @reqlist, $query;
2723 foreach my $s (@reqlist) {
2726 $sth = $dbh->prepare ($query) ;
2732 &update_db_version ($target) ;
2733 &debug ("Committing.") ;
2737 &update_with_sql("20051103_transiciel_motscle_document","4.6-1");
2739 &update_with_sql("20070924-forum-perm","4.6.99-1");
2740 &update_with_sql("20070924-project-perm","4.6.99-2");
2741 &update_with_sql("20070924-artifact-perm","4.6.99-3");
2743 $version = &get_db_version ;
2744 $target = "4.6.99-4" ;
2745 if (&is_lesser ($version, $target)) {
2746 &debug ("Dropping old translations table") ;
2748 &drop_table_if_exists ($dbh, "tmp_lang") ;
2750 &update_db_version ($target) ;
2751 &debug ("Committing.") ;
2755 $version = &get_db_version ;
2756 $target = "4.6.99-5" ;
2757 if (&is_lesser ($version, $target)) {
2758 &debug ("Updating available themes") ;
2760 my @obsolete_themes = qw/ classic debian savannah
2761 savannah_codex savannah_forest
2762 savannah_reverse savannah_sad
2763 savannah_savannah savannah_slashd
2765 savannah_transparent savannah_water
2766 savannah_www.gnu.org
2767 savannah_darkslate forged kde
2770 my $otids = join (',', map { "'$_'" } @obsolete_themes) ;
2772 $query = "UPDATE users SET theme_id=1 WHERE theme_id IN
2773 (SELECT theme_id FROM themes WHERE dirname IN ($otids))" ;
2774 push @reqlist, $query;
2776 $query = "DELETE FROM themes WHERE dirname IN ($otids)" ;
2777 push @reqlist, $query;
2780 'gforge-classic' => 'GForge classic',
2781 'gforge-simple-theme' => 'GForge simple',
2782 'lite' => 'GForge lite'
2785 foreach my $dir (sort keys %new_themes) {
2786 $query = "INSERT INTO themes (dirname, fullname) VALUES ('$dir', '$new_themes{$dir}')" ;
2787 push @reqlist, $query;
2790 foreach my $s (@reqlist) {
2793 $sth = $dbh->prepare ($query) ;
2799 &update_db_version ($target) ;
2800 &debug ("Committing.") ;
2804 ########################### INSERT HERE #################################
2806 &debug ("It seems your database $action went well and smoothly. That's cool.") ;
2807 &debug ("Please enjoy using GForge.") ;
2809 # There should be a commit at the end of every block above.
2810 # If there is not, then it might be symptomatic of a problem.
2811 # For safety, we roll back.
2816 warn "Transaction aborted because $@" ;
2817 &debug ("Transaction aborted because $@") ;
2818 &debug ("Last SQL query was:\n$query\n(end of query)") ;
2820 my $version = &get_db_version ;
2822 &debug ("Your database schema is at version $version") ;
2824 &debug ("Couldn't get your database schema version.") ;
2826 &debug ("Please report this bug on the Debian bug-tracking system.") ;
2827 &debug ("Please include the previous messages as well to help debugging.") ;
2828 &debug ("You should not worry too much about this,") ;
2829 &debug ("your DB is still in a consistent state and should be usable.") ;
2836 sub get_pg_version () {
2838 if (-x '/usr/bin/pg_lsclusters' ) {
2839 $command = q(/usr/bin/pg_lsclusters | grep 5432 | grep online | cut -d' ' -f1) ;
2841 $command = q(dpkg -s postgresql | awk '/^Version: / { print $2 }') ;
2843 my $version = qx($command) ;
2848 sub create_metadata_table ( $ ) {
2849 my $v = shift || "2.5-7+just+before+8" ;
2851 my ($query, $sth, @array) ;
2853 # Let's create this table if we have it not
2854 if (! &table_exists ($dbh, 'debian_meta_data')) {
2855 &debug ("Creating debian_meta_data table.") ;
2856 $query = "CREATE TABLE debian_meta_data (key varchar primary key, value text not null)" ;
2858 $sth = $dbh->prepare ($query) ;
2863 $query = "SELECT count(*) FROM debian_meta_data WHERE key = 'db-version'";
2865 $sth = $dbh->prepare ($query) ;
2867 @array = $sth->fetchrow_array () ;
2870 # Empty table? We'll have to fill it up a bit
2872 if ($array [0] == 0) {
2873 &debug ("Inserting first data into debian_meta_data table.") ;
2874 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('db-version', '$v')" ;
2876 $sth = $dbh->prepare ($query) ;
2882 sub update_db_version ( $ ) {
2883 my $v = shift or die "Not enough arguments" ;
2885 &debug ("Updating debian_meta_data table.") ;
2886 $query = "UPDATE debian_meta_data SET value = '$v' WHERE key = 'db-version'" ;
2888 my $sth = $dbh->prepare ($query) ;
2893 sub get_db_version () {
2894 $query = "SELECT value FROM debian_meta_data WHERE key = 'db-version'" ;
2896 my $sth = $dbh->prepare ($query) ;
2898 my @array = $sth->fetchrow_array () ;
2901 my $version = $array [0] ;
2906 sub update_with_sql ( $ ) {
2907 my $sqldate = shift or die "Not enough arguments" ;
2908 my $target = shift or die "Not enough arguments" ;
2909 my $version = &get_db_version ;
2910 if (&is_lesser ($version, $target)) {
2911 &debug ("Upgrading with $sqldate.sql") ;
2913 @reqlist = @{ &parse_sql_file ("$sqldir/$sqldate.sql") } ;
2914 foreach my $s (@reqlist) {
2917 my $sth = $dbh->prepare ($query) ;
2923 &update_db_version ($target) ;
2924 &debug ("Committing.") ;