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_password
23 $server_admin $domain_name $newsadmin_groupid $statsadmin_groupid
26 $libdir="/usr/share/gforge/lib";
27 $sqldir="/usr/share/gforge/db";
28 require ("$libdir/sqlparser.pm") ; # Our magic SQL parser
29 require ("$libdir/sqlhelper.pm") ; # Our SQL functions
30 require ("$libdir/include.pl"); # Some other functions
31 chomp($sys_news_group=`/usr/share/gforge/bin/forge_get_config news_group`);
32 chomp($domain_name=`/usr/share/gforge/bin/forge_get_config web_host`);
33 chomp($server_admin=`/usr/share/gforge/bin/forge_get_config admin_email`);
37 $dbh->{AutoCommit} = 0;
38 $dbh->{RaiseError} = 1;
40 my ($sth, @array, $version, $path, $target) ;
41 # Is this script still relevant?
42 if (&table_exists ($dbh, 'database_startpoint')) {
43 &debug ("Database maintenance already moved to unified script,") ;
44 &debug ("nothing to do in old upgrader.") ;
47 elsif (! &table_exists ($dbh, 'groups')) { # No 'groups' table
48 # Installing SF 2.6 from scratch
49 &debug ("Creating initial Sourceforge database from files.") ;
51 &create_metadata_table ("2.5.9999") ;
53 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
55 $sth = $dbh->prepare ($query) ;
57 @array = $sth->fetchrow_array () ;
60 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', 'scratch-to-2.6')" ;
62 $sth = $dbh->prepare ($query) ;
69 } else { # A 'groups' table exists
70 if (! &table_exists ($dbh, 'debian_meta_data')) { # No 'debian_meta_data' table
71 # If we're here, we're upgrading from 2.5-7 or earlier
72 # We therefore need to create the table
73 &create_metadata_table ("2.5-7+just+before+8") ;
76 $version = &get_db_version ;
77 if (&is_lesser ($version, "2.5.9999")) {
78 &debug ("Found an old (2.5) database, will upgrade to 2.6") ;
80 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
82 $sth = $dbh->prepare ($query) ;
84 @array = $sth->fetchrow_array () ;
88 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', '2.5-to-2.6')" ;
89 $sth = $dbh->prepare ($query) ;
97 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
98 $sth = $dbh->prepare ($query) ;
100 @array = $sth->fetchrow_array () ;
103 if ($array[0] == 0) {
106 $query = "SELECT value from debian_meta_data where key = 'current-path'";
107 $sth = $dbh->prepare ($query) ;
109 @array = $sth->fetchrow_array () ;
116 ($path eq 'scratch-to-2.6') && do {
117 &update_with_sql ("sf-2.6-complete", "2.5.9999.1+global+data+done") ;
119 $version = &get_db_version ;
120 $target = "2.5.9999.2+local+data+done" ;
121 if (&is_lesser ($version, $target)) {
122 &debug ("Adding local data.") ;
124 my ($login, $md5pwd, $unixpwd, $email, $noreplymail, $date) ;
127 $md5pwd = 'INVALID' ;
128 $unixpwd = 'INVALID' ;
129 $email = $server_admin ;
130 $noreplymail="noreply\@$domain_name" ;
134 "UPDATE groups SET homepage = '$domain_name/admin/' where group_id = 1",
135 "UPDATE groups SET homepage = '$domain_name/news/' where group_id = 2",
136 "UPDATE groups SET homepage = '$domain_name/stats/' where group_id = 3",
137 "UPDATE groups SET homepage = '$domain_name/peerrating/' where group_id = 4",
138 "UPDATE users SET email = '$noreplymail' where user_id = 100",
139 "INSERT INTO users VALUES (101,'$login','$email','$md5pwd','Sourceforge admin','A','/bin/bash','$unixpwd','N',2000,'shell',$date,'',1,0,NULL,NULL,0,'','GMT', 1, 0)",
140 "SELECT setval ('\"users_pk_seq\"', 102, 'f')",
141 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 1, 'A')",
142 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 2, 'A')",
143 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 3, 'A')",
144 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 4, 'A')"
147 foreach my $s (@reqlist) {
150 $sth = $dbh->prepare ($query) ;
156 &update_db_version ($target) ;
161 $version = &get_db_version ;
162 $target = "2.5.9999.3+skills+done" ;
163 if (&is_lesser ($version, $target)) {
164 &debug ("Inserting skills.") ;
166 foreach my $skill (split m/;/, "Ada;C;C++;HTML;LISP;Perl;PHP;Python;SQL") {
167 push @reqlist, "INSERT INTO people_skill (name) VALUES ('$skill')" ;
170 foreach my $s (@reqlist) {
173 $sth = $dbh->prepare ($query) ;
179 &update_db_version ($target) ;
184 $version = &get_db_version ;
185 $target = "2.6-0+checkpoint+1" ;
186 if (&is_lesser ($version, $target)) {
187 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
189 $sth = $dbh->prepare ($query) ;
193 &update_db_version ($target) ;
201 ($path eq '2.5-to-2.6') && do {
203 $version = &get_db_version ;
205 if (&is_lesser ($version, $target)) {
206 &debug ("Adding row to people_job_category.") ;
207 $query = "INSERT INTO people_job_category VALUES (100, 'Undefined', 0)" ;
208 $sth = $dbh->prepare ($query) ;
212 &update_db_version ($target) ;
217 $version = &get_db_version ;
219 if (&is_lesser ($version, $target)) {
220 &debug ("Adding row to supported_languages.") ;
221 $query = "INSERT INTO supported_languages VALUES (15, 'Korean', 'Korean.class', 'Korean', 'kr')" ;
222 $sth = $dbh->prepare ($query) ;
226 &update_db_version ($target) ;
231 $version = &get_db_version ;
233 if (&is_lesser ($version, $target)) {
234 &debug ("Fixing unix_box entries.") ;
236 $query = "update groups set unix_box = 'shell'" ;
237 $sth = $dbh->prepare ($query) ;
241 $query = "update users set unix_box = 'shell'" ;
242 $sth = $dbh->prepare ($query) ;
246 &debug ("Also fixing a few sequences.") ;
248 &bump_sequence_to ($dbh, "bug_pk_seq", 100) ;
249 &bump_sequence_to ($dbh, "project_task_pk_seq", 100) ;
251 &update_db_version ($target) ;
256 $version = &get_db_version ;
258 if (&is_lesser ($version, $target)) {
259 &debug ("Adding rows to supported_languages.") ;
261 "INSERT INTO supported_languages VALUES (16,'Bulgarian','Bulgarian.class','Bulgarian','bg')",
262 "INSERT INTO supported_languages VALUES (17,'Greek','Greek.class','Greek','el')",
263 "INSERT INTO supported_languages VALUES (18,'Indonesian','Indonesian.class','Indonesian','id')",
264 "INSERT INTO supported_languages VALUES (19,'Portuguese (Brazillian)','PortugueseBrazillian.class','PortugueseBrazillian', 'br')",
265 "INSERT INTO supported_languages VALUES (20,'Polish','Polish.class','Polish','pl')",
266 "INSERT INTO supported_languages VALUES (21,'Portuguese','Portuguese.class','Portuguese', 'pt')",
267 "INSERT INTO supported_languages VALUES (22,'Russian','Russian.class','Russian','ru')"
270 foreach my $s (@reqlist) {
273 $sth = $dbh->prepare ($query) ;
279 &update_db_version ($target) ;
284 $version = &get_db_version ;
286 if (&is_lesser ($version, $target)) {
287 &debug ("Fixing unix_uid entries.") ;
289 $query = "UPDATE users SET unix_uid = nextval ('unix_uid_seq') WHERE unix_status != 'N' AND status != 'P' AND unix_uid = 0" ;
290 $sth = $dbh->prepare ($query) ;
294 &update_db_version ($target) ;
299 $version = &get_db_version ;
300 $target = "2.5.9999.1+temp+data+dropped" ;
301 if (&is_lesser ($version, $target)) {
302 &debug ("Preparing to upgrade your database - dropping temporary tables") ;
304 my @tables = qw/ user_metric_tmp1_1 user_metric_tmp1_2
305 user_metric_tmp1_3 user_metric_tmp1_4
306 user_metric_tmp1_5 user_metric_tmp1_6
307 user_metric_tmp1_7 user_metric_tmp1_8 user_metric1
308 user_metric2 user_metric3 user_metric4 user_metric5
309 user_metric6 user_metric7 user_metric8
310 project_counts_tmp project_metric_tmp
311 project_metric_tmp1 project_counts_weekly_tmp
312 project_metric_weekly_tmp project_metric_weekly_tmp1
315 my @sequences = qw/ user_metric1_ranking_seq
316 user_metric2_ranking_seq user_metric3_ranking_seq
317 user_metric4_ranking_seq user_metric5_ranking_seq
318 user_metric6_ranking_seq user_metric7_ranking_seq
319 user_metric8_ranking_seq project_metric_weekly_seq
320 trove_treesum_trove_treesum_seq
321 project_metric_tmp1_pk_seq / ;
323 my @indexes = qw/ idx_project_metric_group
324 idx_project_metric_weekly_group
325 user_metric_history_date_userid / ;
327 foreach my $table (@tables) {
328 &drop_table_if_exists ($dbh, $table) ;
331 foreach my $sequence (@sequences) {
332 &drop_sequence_if_exists ($dbh, $sequence) ;
335 foreach my $index (@indexes) {
336 &drop_index_if_exists ($dbh, $index) ;
339 &update_db_version ($target) ;
344 $version = &get_db_version ;
345 $target = "2.5.9999.2+data+upgraded" ;
346 if (&is_lesser ($version, $target)) {
347 &debug ("Upgrading your database scheme from 2.5") ;
350 "ALTER TABLE groups DROP CONSTRAINT groups_pkey",
351 "ALTER TABLE users DROP CONSTRAINT users_pkey",
353 foreach my $s (@reqlist) {
356 $sth = $dbh->prepare ($query) ;
361 @reqlist = @{ &parse_sql_file ("$sqldir/sf2.5-to-sf2.6.sql") } ;
362 foreach my $s (@reqlist) {
365 $sth = $dbh->prepare ($query) ;
371 &update_db_version ($target) ;
376 $version = &get_db_version ;
377 $target = "2.5.9999.3+artifact+transcoded" ;
378 if (&is_lesser ($version, $target)) {
379 &debug ("Transcoding the artifact data fields") ;
381 $query = "SELECT id,bin_data FROM artifact_file ORDER BY id ASC" ;
383 $sth = $dbh->prepare ($query) ;
385 while (@array = $sth->fetchrow_array) {
386 my $query2 = "UPDATE artifact_file SET bin_data='" ;
387 $query2 .= encode_base64 (decode_entities ($array [1])) ;
388 $query2 .= "' WHERE id=" ;
389 $query2 .= $array [0] ;
392 my $sth2 =$dbh->prepare ($query2) ;
399 &update_db_version ($target) ;
404 $version = &get_db_version ;
405 $target = "2.5.9999.4+groups+inserted" ;
406 if (&is_lesser ($version, $target)) {
407 &debug ("Inserting missing groups") ;
410 "INSERT INTO groups (group_name, homepage,
411 is_public, status, unix_group_name,
412 unix_box, http_domain, short_description,
413 cvs_box, license, register_purpose,
414 license_other, register_time, rand_hash,
415 use_mail, use_survey, use_forum, use_pm,
416 use_cvs, use_news, type, use_docman,
417 new_task_address, send_all_tasks,
419 VALUES ('Stats', '$domain_name/top/', 0,
420 'A', 'stats', 'shell', NULL, NULL, 'cvs',
421 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
423 "INSERT INTO groups (group_name, homepage,
424 is_public, status, unix_group_name,
425 unix_box, http_domain, short_description,
426 cvs_box, license, register_purpose,
427 license_other, register_time, rand_hash,
428 use_mail, use_survey, use_forum, use_pm,
429 use_cvs, use_news, type, use_docman,
430 new_task_address, send_all_tasks,
432 VALUES ('Peer Ratings', '$domain_name/people/', 0,
433 'A', 'peerrating', 'shell', NULL, NULL, 'cvs1',
434 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
438 foreach my $s (@reqlist) {
441 $sth = $dbh->prepare ($query) ;
446 &update_db_version ($target) ;
451 $version = &get_db_version ;
452 $target = "2.6-0+checkpoint+1" ;
453 if (&is_lesser ($version, $target)) {
454 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
456 $sth = $dbh->prepare ($query) ;
460 &update_db_version ($target) ;
469 $version = &get_db_version ;
470 $target = "2.6-0+checkpoint+2" ;
471 if (&is_lesser ($version, $target)) {
472 &debug ("Updating permissions on system groups.") ;
473 $query = "UPDATE groups SET group_name='Site Admin', is_public=1 WHERE group_id=1" ;
475 $sth = $dbh->prepare ($query) ;
478 $query = "UPDATE groups SET group_name='Site News Admin', is_public=1 WHERE group_id=$sys_news_group" ;
480 $sth = $dbh->prepare ($query) ;
484 &update_db_version ($target) ;
489 $version = &get_db_version ;
490 $target = "2.6-0+checkpoint+3" ;
491 if (&is_lesser ($version, $target)) {
492 &debug ("Creating table group_cvs_history.") ;
493 $query = "CREATE TABLE group_cvs_history (
494 id integer DEFAULT nextval('group_cvs_history_pk_seq'::text) NOT NULL,
495 group_id integer DEFAULT '0' NOT NULL,
496 user_name character varying(80) DEFAULT '' NOT NULL,
497 cvs_commits integer DEFAULT '0' NOT NULL,
498 cvs_commits_wk integer DEFAULT '0' NOT NULL,
499 cvs_adds integer DEFAULT '0' NOT NULL,
500 cvs_adds_wk integer DEFAULT '0' NOT NULL,
503 $sth = $dbh->prepare ($query) ;
507 &update_db_version ($target) ;
512 $version = &get_db_version ;
513 $target = "2.6-0+checkpoint+4" ;
514 if (&is_lesser ($version, $target)) {
515 &debug ("Registering Savannah themes.") ;
517 $query = "SELECT max(theme_id) FROM themes" ;
519 $sth = $dbh->prepare ($query) ;
521 @array = $sth->fetchrow_array () ;
523 my $maxid = $array [0] ;
525 &bump_sequence_to ($dbh, "themes_pk_seq", $maxid) ;
528 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_codex', 'Savannah CodeX')",
529 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_forest', 'Savannah Forest')",
530 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_reverse', 'Savannah Reverse')",
531 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_sad', 'Savannah Sad')",
532 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_savannah', 'Savannah Original')",
533 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_slashd', 'Savannah SlashDot')",
534 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_startrek', 'Savannah StarTrek')",
535 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_transparent', 'Savannah Transparent')",
536 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_water', 'Savannah Water')",
537 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_www.gnu.org', 'Savannah www.gnu.org')"
539 foreach my $s (@reqlist) {
542 $sth = $dbh->prepare ($query) ;
548 &update_db_version ($target) ;
553 $version = &get_db_version ;
554 $target = "2.6-0+checkpoint+5" ;
555 if (&is_lesser ($version, $target)) {
556 &debug ("Registering yet another Savannah theme.") ;
558 $query = "INSERT INTO themes (dirname, fullname) VALUES ('savannah_darkslate', 'Savannah Dark Slate')";
560 $sth = $dbh->prepare ($query) ;
564 &update_db_version ($target) ;
569 $version = &get_db_version ;
570 $target = "2.6-0+checkpoint+6" ;
571 if (&is_lesser ($version, $target)) {
572 &debug ("Updating language codes.") ;
575 "UPDATE supported_languages SET language_code='en' where classname='English'",
576 "UPDATE supported_languages SET language_code='ja' where classname='Japanese'",
577 "UPDATE supported_languages SET language_code='iw' where classname='Hebrew'",
578 "UPDATE supported_languages SET language_code='es' where classname='Spanish'",
579 "UPDATE supported_languages SET language_code='th' where classname='Thai'",
580 "UPDATE supported_languages SET language_code='de' where classname='German'",
581 "UPDATE supported_languages SET language_code='it' where classname='Italian'",
582 "UPDATE supported_languages SET language_code='no' where classname='Norwegian'",
583 "UPDATE supported_languages SET language_code='sv' where classname='Swedish'",
584 "UPDATE supported_languages SET language_code='zh' where classname='Chinese'",
585 "UPDATE supported_languages SET language_code='nl' where classname='Dutch'",
586 "UPDATE supported_languages SET language_code='eo' where classname='Esperanto'",
587 "UPDATE supported_languages SET language_code='ca' where classname='Catalan'",
588 "UPDATE supported_languages SET language_code='ko' where classname='Korean'",
589 "UPDATE supported_languages SET language_code='bg' where classname='Bulgarian'",
590 "UPDATE supported_languages SET language_code='el' where classname='Greek'",
591 "UPDATE supported_languages SET language_code='id' where classname='Indonesian'",
592 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese (Brazillian)'",
593 "UPDATE supported_languages SET language_code='pl' where classname='Polish'",
594 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese'",
595 "UPDATE supported_languages SET language_code='ru' where classname='Russian'",
596 "UPDATE supported_languages SET language_code='fr' where classname='French'"
598 foreach my $s (@reqlist) {
601 $sth = $dbh->prepare ($query) ;
606 &update_db_version ($target) ;
611 $version = &get_db_version ;
612 $target = "2.6-0+checkpoint+7" ;
613 if (&is_lesser ($version, $target)) {
614 &debug ("Fixing artifact-related views.") ;
616 &drop_view_if_exists ($dbh, "artifact_file_user_vw") ;
617 &drop_view_if_exists ($dbh, "artifact_history_user_vw") ;
618 &drop_view_if_exists ($dbh, "artifact_message_user_vw") ;
619 &drop_view_if_exists ($dbh, "artifactperm_artgrouplist_vw") ;
620 &drop_view_if_exists ($dbh, "artifactperm_user_vw") ;
621 &drop_view_if_exists ($dbh, "artifact_vw") ;
624 "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)",
625 "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)",
626 "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)",
627 "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)",
628 "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)",
629 "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))"
631 foreach my $s (@reqlist) {
634 $sth = $dbh->prepare ($query) ;
639 &update_db_version ($target) ;
644 $version = &get_db_version ;
645 $target = "2.6-0+checkpoint+8" ;
646 if (&is_lesser ($version, $target)) {
647 &debug ("Adding integrity constraints between the Trove map tables.") ;
650 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
651 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
652 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
653 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
654 "DELETE FROM trove_treesums WHERE trove_cat_id NOT IN (SELECT trove_cat_id FROM trove_cat)",
655 "ALTER TABLE trove_treesums ADD CONSTRAINT trove_treesums_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
657 foreach my $s (@reqlist) {
660 $sth = $dbh->prepare ($query) ;
665 &update_db_version ($target) ;
670 $version = &get_db_version ;
671 $target = "2.6-0+checkpoint+9" ;
672 if (&is_lesser ($version, $target)) {
673 &debug ("Adding extra fields to the groups table.") ;
676 "ALTER TABLE groups ADD COLUMN use_ftp integer",
677 "ALTER TABLE groups ALTER COLUMN use_ftp SET DEFAULT 1",
678 "UPDATE groups SET use_ftp = 1",
679 "ALTER TABLE groups ADD COLUMN use_tracker integer",
680 "ALTER TABLE groups ALTER COLUMN use_tracker SET DEFAULT 1",
681 "UPDATE groups SET use_tracker = 1",
682 "ALTER TABLE groups ADD COLUMN use_frs integer",
683 "ALTER TABLE groups ALTER COLUMN use_frs SET DEFAULT 1",
684 "UPDATE groups SET use_frs = 1",
685 "ALTER TABLE groups ADD COLUMN use_stats integer",
686 "ALTER TABLE groups ALTER COLUMN use_stats SET DEFAULT 1",
687 "UPDATE groups SET use_stats = 1",
688 "ALTER TABLE groups ADD COLUMN enable_pserver integer",
689 "ALTER TABLE groups ALTER COLUMN enable_pserver SET DEFAULT 1",
690 "UPDATE groups SET enable_pserver = 1",
691 "ALTER TABLE groups ADD COLUMN enable_anoncvs integer",
692 "ALTER TABLE groups ALTER COLUMN enable_anoncvs SET DEFAULT 1",
693 "UPDATE groups SET enable_anoncvs = 1",
695 foreach my $s (@reqlist) {
698 $sth = $dbh->prepare ($query) ;
703 &update_db_version ($target) ;
708 $version = &get_db_version ;
709 $target = "2.6-0+checkpoint+10" ;
710 if (&is_lesser ($version, $target)) {
711 &debug ("Updating supported_languages table.") ;
714 "ALTER TABLE supported_languages RENAME COLUMN language_code TO language_code_old",
715 "ALTER TABLE supported_languages ADD COLUMN language_code character(5)",
716 "UPDATE supported_languages SET language_code = language_code_old",
717 "ALTER TABLE supported_languages DROP COLUMN language_code_old",
718 "UPDATE supported_languages SET language_code='pt_BR', classname='PortugueseBrazilian', name='Pt. Brazilian', filename='PortugueseBrazilian.class' where classname='PortugueseBrazillian'",
720 foreach my $s (@reqlist) {
723 $sth = $dbh->prepare ($query) ;
728 &update_db_version ($target) ;
733 $version = &get_db_version ;
734 $target = "2.6-0+checkpoint+11" ;
735 if (&is_lesser ($version, $target)) {
736 &debug ("Adding tables for the plugin subsystem.") ;
739 "CREATE SEQUENCE plugins_pk_seq",
740 "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))",
741 "CREATE SEQUENCE group_plugin_pk_seq",
742 "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)",
743 "CREATE SEQUENCE user_plugin_pk_seq",
744 "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)",
746 foreach my $s (@reqlist) {
749 $sth = $dbh->prepare ($query) ;
754 &update_db_version ($target) ;
759 &update_with_sql("20021125", "2.6-0+checkpoint+12") ;
760 &update_with_sql("20021212", "2.6-0+checkpoint+13") ;
761 &update_with_sql("20021213-1", "2.6-0+checkpoint+14") ;
763 $version = &get_db_version ;
764 $target = "2.6-0+checkpoint+15" ;
765 if (&is_lesser ($version, $target)) {
766 &debug ("Transcoding documentation data fields") ;
767 $query = "SELECT docid,data FROM doc_data ORDER BY docid ASC" ;
769 $sth = $dbh->prepare ($query) ;
771 while (@array = $sth->fetchrow_array) {
772 my $query2 = "UPDATE doc_data SET data='" ;
773 $query2 .= encode_base64 (decode_entities ($array [1])) ;
774 $query2 .= "', filename='file".$array [0].".html'";
775 $query2 .= ", filetype='text/html'";
776 $query2 .= " WHERE docid=" ;
777 $query2 .= $array [0] ;
780 my $sth2 =$dbh->prepare ($query2) ;
787 &update_db_version ($target) ;
792 &update_with_sql("20021214", "2.6-0+checkpoint+16") ;
793 &update_with_sql("20021215", "2.6-0+checkpoint+17") ;
794 &update_with_sql("20021216", "2.6-0+checkpoint+18") ;
795 &update_with_sql("20021223-2", "2.6-0+checkpoint+19") ;
796 &update_with_sql("20030102-2", "2.6-0+checkpoint+20") ;
797 &update_with_sql("20030105", "2.6-0+checkpoint+21") ;
798 &update_with_sql("20030107", "2.6-0+checkpoint+22") ;
799 &update_with_sql("20030109", "2.6-0+checkpoint+23") ;
801 $version = &get_db_version ;
802 $target = "2.6-0+checkpoint+24" ;
803 if (&is_lesser ($version, $target)) {
805 &debug ("Adjusting language sequences") ;
807 $query = "SELECT max(language_id) FROM supported_languages" ;
808 $sth = $dbh->prepare ($query) ;
810 @array = $sth->fetchrow_array () ;
812 my $maxid = $array [0] ;
813 &bump_sequence_to ($dbh, "supported_languages_pk_seq", $maxid) ;
815 &debug ("Upgrading with 20030112.sql") ;
817 @reqlist = @{ &parse_sql_file ("$sqldir/20030112.sql") } ;
818 foreach my $s (@reqlist) {
821 $sth = $dbh->prepare ($query) ;
827 &update_db_version ($target) ;
832 &update_with_sql("20030113-2", "2.6-0+checkpoint+25") ;
833 &update_with_sql("20030131", "2.6-0+checkpoint+26") ;
834 &update_with_sql("20030209", "2.6-0+checkpoint+27") ;
835 &update_with_sql("20030312", "2.6-0+checkpoint+28") ;
837 $version = &get_db_version ;
838 $target = "2.6-0+checkpoint+29" ;
839 if (&is_lesser ($version, $target)) {
840 &debug ("Registering KDE theme.") ;
842 $query = "INSERT INTO themes (dirname, fullname) VALUES ('kde', 'KDE')";
844 $sth = $dbh->prepare ($query) ;
848 &update_db_version ($target) ;
854 $version = &get_db_version ;
855 $target = "2.6-0+checkpoint+30" ;
856 if (&is_lesser ($version, $target)) {
857 &debug ("Registering Dark Aqua theme.") ;
859 $query = "INSERT INTO themes (dirname, fullname) VALUES ('darkaqua', 'Dark Aqua')";
861 $sth = $dbh->prepare ($query) ;
865 &update_db_version ($target) ;
871 &update_with_sql("20030513", "2.6-0+checkpoint+31") ;
873 $version = &get_db_version ;
875 if (&is_lesser ($version, $target)) {
876 &debug ("Database schema is now version 3.0-1.") ;
878 &update_db_version ($target) ;
883 &update_with_sql("20030822", "3.0-7") ;
884 &update_with_sql("20031105", "3.1-0+1") ;
885 &update_with_sql("20031124", "3.1-0+1.1") ;
886 &update_with_sql("20031129", "3.1-0+2") ;
887 &update_with_sql("20031126", "3.1-0+3") ;
888 &update_with_sql("20031205", "3.2.1-0+2") ;
889 &update_with_sql("20040130", "3.2.1-0+3") ;
890 &update_with_sql("20040204", "3.2.1-0+4") ;
891 &update_with_sql("20040315", "3.2.1-0+5") ;
892 &update_with_sql("200403251", "3.3.0-0+0") ;
893 &update_with_sql("200403252", "3.3.0-0+1") ;
894 &update_with_sql("20040507", "3.3.0-0+3") ;
895 &update_with_sql("20040722", "3.3.0-0+4") ;
896 &update_with_sql("20040804", "3.3.0-0+6") ;
897 &update_with_sql("20040826", "3.3.0-0+7") ;
899 $version = &get_db_version ;
900 $target = "3.3.0-2+1" ;
901 if (&is_lesser ($version, $target)) {
902 &debug ("Migrating forum names") ;
904 $query = "SELECT group_forum_id,forum_name FROM forum_group_list" ;
906 $sth = $dbh->prepare ($query) ;
908 while (@array = $sth->fetchrow_array) {
909 my $forumid = $array[0] ;
910 my $oldname = $array[1] ;
912 my $newname = lc $oldname ;
913 $newname =~ s/[^_.0-9a-z-]/-/g ;
915 my $query2 = "UPDATE forum_group_list SET forum_name='$newname' WHERE group_forum_id=$forumid" ;
917 my $sth2 =$dbh->prepare ($query2) ;
923 &update_db_version ($target) ;
928 $version = &get_db_version ;
929 $target = "3.3.0-2+2" ;
930 if (&is_lesser ($version, $target)) {
931 &debug ("Migrating permissions to RBAC") ;
934 'Admin' => { 'projectadmin'=>'A', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'forum'=>'2', 'trackeradmin'=>'2', 'tracker'=>'2', 'pmadmin'=>'2', 'pm'=>'2' },
935 'Senior Developer' => { 'projectadmin'=>'0', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'forum'=>'2', 'trackeradmin'=>'2', 'tracker'=>'2', 'pmadmin'=>'2', 'pm'=>'2' },
936 'Junior Developer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'1', 'docman'=>'0', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'1', 'pmadmin'=>'0', 'pm'=>'1' },
937 'Doc Writer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'0', 'pmadmin'=>'0', 'pm'=>'0' },
938 'Support Tech' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'2', 'pmadmin'=>'0', 'pm'=>'0' }
941 $query = "SELECT group_id FROM groups where status != 'P'" ;
943 $sth = $dbh->prepare ($query) ;
945 while (@array = $sth->fetchrow_array) {
946 my $group_id = $array[0] ;
948 my ($query2, $sth2, @array2, $admin_rid, $jd_rid, %roledata) ;
949 foreach my $rname (keys %$defaultroles) {
950 $query2 = "SELECT nextval('role_role_id_seq'::text)" ;
952 $sth2 =$dbh->prepare ($query2) ;
954 @array2 = $sth2->fetchrow_array ;
955 my $rid = $array2[0] ;
957 if ($rname eq 'Admin') {
959 } elsif ($rname eq 'Junior Developer') {
963 $query2 = "INSERT INTO role (role_id, group_id, role_name)
964 VALUES ($rid, $group_id, '$rname')" ;
966 $sth2 =$dbh->prepare ($query2) ;
970 foreach my $section (keys %{$defaultroles->{$rname}}) {
971 if ($section eq 'forum') {
972 $query2 = "SELECT group_forum_id
973 FROM forum_group_list
974 WHERE group_id = $group_id" ;
976 $sth2 =$dbh->prepare ($query2) ;
978 while (@array2 = $sth2->fetchrow_array) {
979 $roledata{'forum'}{$array2[0]} = $defaultroles->{$rname}{'forum'} ;
982 } elsif ($section eq 'pm') {
983 $query2 = "SELECT group_project_id
984 FROM project_group_list
985 WHERE group_id = $group_id" ;
987 $sth2 =$dbh->prepare ($query2) ;
989 while (@array2 = $sth2->fetchrow_array) {
990 $roledata{'pm'}{$array2[0]} = $defaultroles->{$rname}{'pm'} ;
993 } elsif ($section eq 'tracker') {
994 $query2 = "SELECT group_artifact_id
995 FROM artifact_group_list
996 WHERE group_id = $group_id" ;
998 $sth2 =$dbh->prepare ($query2) ;
1000 while (@array2 = $sth2->fetchrow_array) {
1001 $roledata{'tracker'}{$array2[0]} = $defaultroles->{$rname}{'tracker'} ;
1005 $roledata{$section}{0} = $defaultroles->{$rname}{$section} ;
1008 foreach my $rd_it (keys %{$roledata{$section}}) {
1009 $query2 = "INSERT INTO role_setting (role_id, section_name, ref_id, value)
1010 VALUES ($rid, '$section', $rd_it, '$roledata{$section}{$rd_it}')" ;
1011 # &debug ($query2) ;
1012 $sth2 =$dbh->prepare ($query2) ;
1021 # affecter le rôle Admin aux admins, JD aux autres
1022 $query2 = "SELECT user_id, admin_flags FROM user_group WHERE group_id = $group_id" ;
1023 # &debug ($query2) ;
1024 $sth2 =$dbh->prepare ($query2) ;
1026 while (@array2 = $sth2->fetchrow_array) {
1027 my $uid = $array2[0] ;
1028 my $adminflags = $array2[1] ;
1031 $adminflags =~ s/\s//g ;
1032 if ($adminflags eq 'A') {
1037 $rname = 'Junior Developer' ;
1042 admin_flags = '$defaultroles->{$rname}{'projectadmin'}',
1043 forum_flags = '$defaultroles->{$rname}{'forumadmin'}',
1044 project_flags = '$defaultroles->{$rname}{'pmadmin'}',
1045 doc_flags = '$defaultroles->{$rname}{'docman'}',
1046 cvs_flags = '$defaultroles->{$rname}{'scm'}',
1047 release_flags = '$defaultroles->{$rname}{'frs'}',
1048 artifact_flags = '$defaultroles->{$rname}{'trackeradmin'}'
1049 WHERE user_id = $uid AND group_id = $group_id" ,
1051 SET perm_level=$defaultroles->{$rname}{'forum'}
1052 WHERE group_forum_id IN (
1053 SELECT group_forum_id
1054 FROM forum_group_list
1055 WHERE group_id=$group_id)
1057 "UPDATE project_perm
1058 SET perm_level=$defaultroles->{$rname}{'pm'}
1059 WHERE group_project_id IN (
1060 SELECT group_project_id
1061 FROM project_group_list
1062 WHERE group_id=$group_id)
1064 "UPDATE artifact_perm
1065 SET perm_level=$defaultroles->{$rname}{'tracker'}
1066 WHERE group_artifact_id IN (
1067 SELECT group_artifact_id
1068 FROM artifact_group_list
1069 WHERE group_id=$group_id)
1072 foreach my $query3 (@reqlist3) {
1073 # &debug ($query3) ;
1074 my $sth3 = $dbh->prepare ($query3) ;
1083 &update_db_version ($target) ;
1088 &update_with_sql("20040914", "3.3.0-2+4") ;
1089 &update_with_sql("20041001", "3.3.0-2+4+1") ;
1090 &update_with_sql("20041005", "3.3.0-2+5") ;
1091 &update_with_sql("20041006", "3.3.0-2+6") ;
1092 &update_with_sql("20041014", "3.3.0-3") ;
1093 &update_with_sql("20041020", "3.3.0-4") ;
1094 &update_with_sql("20040729", "4.0.0-0") ;
1096 $version = &get_db_version ;
1097 $target = "4.0.0-0+1" ;
1098 if (&is_lesser ($version, $target)) {
1099 &debug ("Granting read access permissions to NSS") ;
1101 @reqlist = ( "GRANT SELECT ON nss_passwd TO ${sys_dbuser}_nss",
1102 "GRANT SELECT ON nss_groups TO ${sys_dbuser}_nss",
1103 "GRANT SELECT ON nss_usergroups TO ${sys_dbuser}_nss",
1105 foreach my $s (@reqlist) {
1108 $sth = $dbh->prepare ($query) ;
1114 &update_db_version ($target) ;
1119 $version = &get_db_version ;
1120 $target = "4.0.0-0+2" ;
1121 if (&is_lesser ($version, $target)) {
1122 &debug ("Upgrading with 20041031.sql") ;
1124 @reqlist = @{ &parse_sql_file ("$sqldir/20041031.sql") } ;
1125 foreach my $s (@reqlist) {
1128 $sth = $dbh->prepare ($query) ;
1134 &debug ("Granting read access permissions to NSS") ;
1136 @reqlist = ( "GRANT SELECT ON mta_users TO ${sys_dbuser}_mta",
1137 "GRANT SELECT ON mta_lists TO ${sys_dbuser}_mta",
1139 foreach my $s (@reqlist) {
1142 $sth = $dbh->prepare ($query) ;
1148 &update_db_version ($target) ;
1153 &update_with_sql("20041104", "4.0.0-0+3") ;
1154 &update_with_sql("20041108", "4.0.0-0+4") ;
1155 &update_with_sql("20041124", "4.0.2-0+0") ;
1157 $version = &get_db_version ;
1158 $target = "4.0.2-0+1" ;
1159 if (&is_lesser ($version, $target)) {
1160 &debug ("Creating automatic commit notification mailing-lists") ;
1163 $query = "SELECT group_id, unix_group_name FROM groups WHERE status='A' ORDER BY group_id" ;
1165 $sth = $dbh->prepare ($query) ;
1167 while (@array = $sth->fetchrow_array) {
1168 my $group_id = $array[0] ;
1169 my $group_name = $array[1] ;
1171 my $query2 = "SELECT count(*) FROM mail_group_list
1172 WHERE group_id = $group_id
1173 AND list_name = '".$group_name."-commits'" ;
1174 # &debug ($query2) ;
1175 my $sth2 =$dbh->prepare ($query2) ;
1177 my @array2 = $sth2->fetchrow_array ;
1179 if ($array2[0] == 0) {
1180 my $listname = $group_name."-commits" ;
1181 my $listpw = substr (Digest::MD5::md5_base64 ($listname . rand(1)), 0, 16) ;
1184 $query2 = "SELECT user_id FROM user_group
1185 WHERE admin_flags = 'A'
1186 AND group_id = $group_id" ;
1187 # &debug ($query2) ;
1188 $sth2 =$dbh->prepare ($query2) ;
1190 my $group_admin = -1 ;
1191 if (@array2 = $sth2->fetchrow_array) {
1192 $group_admin = $array2[0] ;
1196 $query2 = "INSERT INTO mail_group_list (group_id, list_name, is_public, password, list_admin, status, description)
1197 VALUES ($group_id, '$listname', 1, '$listpw', $group_admin, 1, 'commits')" ;
1198 # &debug ($query2) ;
1199 $sth2 =$dbh->prepare ($query2) ;
1206 &update_db_version ($target) ;
1211 &update_with_sql("20050115", "4.0.2-0+3") ;
1213 # We got this at upgrade
1215 #DBD::Pg::st execute failed: ERREUR: la relation avec l'OID 387345 n'existe pas at /usr/share/gforge/bin/db-upgrade.pl line 1970.
1216 #Transaction aborted because DBD::Pg::st execute failed: ERREUR: la relation avec l'OID 387345 n'existe pas at /usr/share/gforge/bin/db-upgrade.pl line 1970.
1217 #Transaction aborted because DBD::Pg::st execute failed: ERREUR: la relation avec l'OID 387345 n'existe pas at /usr/share/gforge/bin/db-upgrade.pl line 1970.
1218 #Last SQL query was:
1219 #update project_task SET last_modified_date=EXTRACT(EPOCH FROM now())::integer;
1221 #Your database schema is at version 4.0.2-0+5
1223 # This is a hack to disconnect and reconnect the DB and solve the problem
1229 $dbh->{AutoCommit} = 0;
1230 $dbh->{RaiseError} = 1;
1232 &update_with_sql("20050130", "4.0.2-0+5") ;
1233 &update_with_sql("20050212", "4.0.2-0+6") ;
1235 $version = &get_db_version ;
1236 $target = "4.0.2-0+7" ;
1237 if (&is_lesser ($version, $target)) {
1238 &debug ("Upgrading with 20050214-nss.sql") ;
1240 @reqlist = @{ &parse_sql_file ("$sqldir/20050214-nss.sql") } ;
1241 foreach my $s (@reqlist) {
1243 $query =~ s/TO gforge_nss;/TO ${sys_dbuser}_nss;/ ;
1245 $sth = $dbh->prepare ($query) ;
1251 &update_db_version ($target) ;
1256 &update_with_sql("20050224-2", "4.1-0") ;
1258 $version = &get_db_version ;
1260 if (&is_lesser ($version, $target)) {
1261 &debug ("Upgrading with 20050225-nsssetup.sql") ;
1263 @reqlist = @{ &parse_sql_file ("$sqldir/20050225-nsssetup.sql") } ;
1264 foreach my $s (@reqlist) {
1266 $query =~ s/TO gforge_nss;/TO ${sys_dbuser}_nss;/ ;
1268 $sth = $dbh->prepare ($query) ;
1274 &update_db_version ($target) ;
1279 &update_with_sql("20050311", "4.1-2") ;
1280 &update_with_sql("20050315", "4.1-3") ;
1281 &update_with_sql("20050325-2", "4.1-4") ;
1283 $version = &get_db_version ;
1285 if (&is_lesser ($version, $target)) {
1286 &debug ("Converting trackers to use their extra fields") ;
1288 $query = "SELECT group_id,group_artifact_id,use_resolution FROM artifact_group_list" ;
1290 $sth = $dbh->prepare ($query) ;
1292 while (@array = $sth->fetchrow_array) {
1293 my $group_id = $array[0] ;
1294 my $gaid = $array[1] ;
1295 my $ur = $array[2] ;
1297 # Ajout du champ Category
1298 my $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
1299 # &debug ($query2) ;
1300 my $sth2 = $dbh->prepare ($query2) ;
1302 my @array2 = $sth2->fetchrow_array ;
1304 my $aefid = $array2[0] ;
1306 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
1307 VALUES ($aefid, $gaid, 'Category', 1)" ;
1308 # &debug ($query2) ;
1309 $sth2 =$dbh->prepare ($query2) ;
1312 $query2 = "SELECT id, category_name FROM artifact_category WHERE group_artifact_id=$gaid" ;
1313 # &debug ($query2) ;
1314 $sth2 = $dbh->prepare ($query2) ;
1317 while (@array2 = $sth2->fetchrow_array) {
1318 my $cat_id = $array2[0] ;
1319 my $catname = $array2[1] ;
1321 if ($catname eq '') { $catname = '[empty]' ; }
1323 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
1324 # &debug ($query3) ;
1325 my $sth3 = $dbh->prepare ($query3) ;
1327 my @array3 = $sth3->fetchrow_array ;
1329 my $efeid = $array3[0] ;
1331 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
1332 VALUES ($efeid, $aefid, ?, 0)" ;
1333 # &debug ($query3) ;
1334 $sth3 =$dbh->prepare ($query3) ;
1335 $sth3->execute ($catname) ;
1338 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
1339 SELECT artifact_id,$efeid,$aefid FROM artifact
1340 WHERE category_id=$cat_id" ;
1341 # &debug ($query3) ;
1342 $sth3 =$dbh->prepare ($query3) ;
1346 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Category'
1347 WHERE old_value='$cat_id' AND field_name='category_id'" ;
1348 # &debug ($query3) ;
1349 $sth3 =$dbh->prepare ($query3) ;
1350 $sth3->execute ($catname) ;
1355 # Ajout du champ Group
1356 $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
1357 # &debug ($query2) ;
1358 $sth2 = $dbh->prepare ($query2) ;
1360 @array2 = $sth2->fetchrow_array ;
1362 $aefid = $array2[0] ;
1364 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
1365 VALUES ($aefid, $gaid, 'Group', 1)" ;
1366 # &debug ($query2) ;
1367 $sth2 =$dbh->prepare ($query2) ;
1370 $query2 = "SELECT id, group_name FROM artifact_group WHERE group_artifact_id=$gaid" ;
1371 # &debug ($query2) ;
1372 $sth2 = $dbh->prepare ($query2) ;
1375 while (@array2 = $sth2->fetchrow_array) {
1376 my $grp_id = $array2[0] ;
1377 my $grpname = $array2[1] ;
1379 if ($grpname eq '') { $grpname = '[empty]' ; }
1381 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
1382 # &debug ($query3) ;
1383 my $sth3 = $dbh->prepare ($query3) ;
1385 my @array3 = $sth3->fetchrow_array ;
1387 my $efeid = $array3[0] ;
1389 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
1390 VALUES ($efeid, $aefid, ?, 0)" ;
1391 # &debug ($query3) ;
1392 $sth3 =$dbh->prepare ($query3) ;
1393 $sth3->execute ($grpname) ;
1396 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
1397 SELECT artifact_id,$efeid,$aefid FROM artifact
1398 WHERE artifact_group_id=$grp_id" ;
1399 # &debug ($query3) ;
1400 $sth3 =$dbh->prepare ($query3) ;
1404 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Group'
1405 WHERE old_value='$grp_id' AND field_name='artifact_group_id'" ;
1406 # &debug ($query3) ;
1407 $sth3 =$dbh->prepare ($query3) ;
1408 $sth3->execute ($grpname) ;
1413 # Ajout du champ Resolution (s'il existe, cf. $ur)
1415 $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
1416 # &debug ($query2) ;
1417 $sth2 = $dbh->prepare ($query2) ;
1419 @array2 = $sth2->fetchrow_array ;
1421 $aefid = $array2[0] ;
1423 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
1424 VALUES ($aefid, $gaid, 'Resolution', 1)" ;
1425 # &debug ($query2) ;
1426 $sth2 =$dbh->prepare ($query2) ;
1429 $query2 = "SELECT id, resolution_name FROM artifact_resolution" ;
1430 # &debug ($query2) ;
1431 $sth2 = $dbh->prepare ($query2) ;
1434 while (@array2 = $sth2->fetchrow_array) {
1435 my $res_id = $array2[0] ;
1436 my $resname = $array2[1] ;
1438 if ($resname eq '') { $resname = '[empty]' ; }
1440 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
1441 # &debug ($query3) ;
1442 my $sth3 = $dbh->prepare ($query3) ;
1444 my @array3 = $sth3->fetchrow_array ;
1446 my $efeid = $array3[0] ;
1448 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
1449 VALUES ($efeid, $aefid, ?, 0)" ;
1450 # &debug ($query3) ;
1451 $sth3 =$dbh->prepare ($query3) ;
1452 $sth3->execute ($resname) ;
1455 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
1456 SELECT artifact_id,$efeid,$aefid FROM artifact
1457 WHERE resolution_id=$res_id and group_artifact_id=$gaid" ;
1458 # &debug ($query3) ;
1459 $sth3 =$dbh->prepare ($query3) ;
1463 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Resolution'
1464 WHERE old_value='$res_id' AND field_name='resolution_id'" ;
1465 # &debug ($query3) ;
1466 $sth3 =$dbh->prepare ($query3) ;
1467 $sth3->execute ($resname) ;
1474 &update_db_version ($target) ;
1479 &update_with_sql("20050325-5", "4.1-6") ;
1480 &update_with_sql("20050605", "4.1-7") ;
1482 $version = &get_db_version ;
1484 if (&is_lesser ($version, $target)) {
1485 &debug ("Creating aliases for the extra fields") ;
1487 $query = "ALTER TABLE artifact_extra_field_list ADD COLUMN alias TEXT" ;
1489 $sth = $dbh->prepare ($query) ;
1493 my %reserved_alias = (
1502 $query = "SELECT field_name, alias, group_artifact_id, extra_field_id FROM artifact_extra_field_list" ;
1504 $sth = $dbh->prepare ($query) ;
1506 while (@array = $sth->fetchrow_array) {
1507 my $name = $array[0] ;
1508 my $alias = $array[1] ;
1509 my $gaid = $array[2] ;
1510 my $efid = $array[3] ;
1513 my $newalias = lc $name ;
1514 $newalias =~ s/\s/_/g ;
1515 $newalias =~ s/[^_a-z]//g ;
1517 if ($newalias ne "") {
1518 if ($reserved_alias{$newalias}) {
1519 $newalias = "extra_" . $newalias ;
1526 $candidate = $newalias ;
1527 $candidate .= $count if ($count > 0) ;
1528 my $query2 = "SELECT count(*) FROM artifact_extra_field_list WHERE group_artifact_id=$gaid AND LOWER(alias)='$candidate' AND extra_field_id <> $efid" ;
1529 # &debug ($query2) ;
1530 my $sth2 =$dbh->prepare ($query2) ;
1532 my @array2 = $sth2->fetchrow_array ;
1533 if ($array2[0] == 0) {
1540 } until ($conflict == 0) ;
1542 my $query2 = "UPDATE artifact_extra_field_list SET alias='$candidate' WHERE extra_field_id=$efid" ;
1543 # &debug ($query2) ;
1544 my $sth2 =$dbh->prepare ($query2) ;
1553 &update_db_version ($target) ;
1558 &update_with_sql("20050628", "4.1-9") ;
1559 &update_with_sql("20050711", "4.5-1") ;
1560 &update_with_sql("20050906","4.5-2");
1561 &update_with_sql("20050804-1","4.5-3");
1563 $version = &get_db_version ;
1565 if (&is_lesser ($version, $target)) {
1566 &debug ("Updating document sizes") ;
1568 $query = "SELECT docid, data FROM doc_data" ;
1570 $sth = $dbh->prepare ($query) ;
1572 while (@array = $sth->fetchrow_array) {
1573 my $docid = $array[0] ;
1574 my $b64data = $array[1] ;
1575 my $data = decode_base64 ($b64data) ;
1576 my $size = length ($data) ;
1578 my $query2 = "UPDATE doc_data SET filesize=$size WHERE docid=$docid" ;
1579 # &debug ($query2) ;
1580 my $sth2 =$dbh->prepare ($query2) ;
1586 &update_db_version ($target) ;
1591 $version = &get_db_version ;
1592 $target = "4.5.14-3" ;
1593 if (&is_lesser ($version, $target)) {
1594 &debug ("Setting up time tracking") ;
1596 if (&table_exists ($dbh, "rep_time_category")) {
1597 &debug ("...already set up.") ;
1599 &drop_table_if_exists ($dbh, "rep_time_category") ;
1600 &drop_sequence_if_exists ($dbh, "rep_time_category_time_code_seq") ;
1601 &drop_table_if_exists ($dbh, "rep_time_tracking") ;
1602 &drop_table_if_exists ($dbh, "rep_users_added_daily") ;
1603 &drop_table_if_exists ($dbh, "rep_users_added_weekly") ;
1604 &drop_table_if_exists ($dbh, "rep_users_added_monthly") ;
1605 &drop_table_if_exists ($dbh, "rep_users_cum_daily") ;
1606 &drop_table_if_exists ($dbh, "rep_users_cum_weekly") ;
1607 &drop_table_if_exists ($dbh, "rep_users_cum_monthly") ;
1608 &drop_table_if_exists ($dbh, "rep_groups_added_daily") ;
1609 &drop_table_if_exists ($dbh, "rep_groups_added_weekly") ;
1610 &drop_table_if_exists ($dbh, "rep_groups_added_monthly") ;
1611 &drop_table_if_exists ($dbh, "rep_groups_cum_daily") ;
1612 &drop_table_if_exists ($dbh, "rep_groups_cum_weekly") ;
1613 &drop_table_if_exists ($dbh, "rep_groups_cum_monthly") ;
1614 &drop_view_if_exists ($dbh, "rep_group_act_oa_vw") ;
1615 &drop_view_if_exists ($dbh, "rep_user_act_oa_vw") ;
1616 &drop_view_if_exists ($dbh, "rep_site_act_daily_vw") ;
1617 &drop_view_if_exists ($dbh, "rep_site_act_weekly_vw") ;
1618 &drop_view_if_exists ($dbh, "rep_site_act_monthly_vw") ;
1619 &drop_table_if_exists ($dbh, "rep_user_act_daily") ;
1620 &drop_table_if_exists ($dbh, "rep_user_act_weekly") ;
1621 &drop_table_if_exists ($dbh, "rep_user_act_monthly") ;
1622 &drop_table_if_exists ($dbh, "rep_group_act_daily") ;
1623 &drop_index_if_exists ($dbh, "repgroupactdaily_daily") ;
1624 &drop_table_if_exists ($dbh, "rep_group_act_weekly") ;
1625 &drop_index_if_exists ($dbh, "repgroupactweekly_weekly") ;
1626 &drop_table_if_exists ($dbh, "rep_group_act_monthly") ;
1627 &drop_index_if_exists ($dbh, "repgroupactmonthly_monthly") ;
1629 @reqlist = @{ &parse_sql_file ("$sqldir/timetracking-init.sql") } ;
1630 foreach my $s (@reqlist) {
1633 $sth = $dbh->prepare ($query) ;
1640 &update_db_version ($target) ;
1645 # I had to increase versions from 4.5.14 to 4.5.15
1646 # The activity view is created by 20060216-nocommit
1647 # If the view doesn't exists apply
1648 if (! &view_exists ($dbh, 'activity_vw')) {
1649 &update_with_sql("20050812","4.5.15-10merge");
1650 &update_with_sql("20050822-2","4.5.15-11merge");
1651 &update_with_sql("20050823","4.5.15-12merge");
1652 &update_with_sql("20050824","4.5.15-13merge");
1653 &update_with_sql("20050831","4.5.15-14merge");
1655 &update_with_sql("20060113","4.5.15-15");
1656 &update_with_sql("20060214","4.5.15-16");
1657 &update_with_sql("20060216-2-debian-nocommit","4.5.15-17");
1660 $version = &get_db_version ;
1661 $target = "4.5.15-21" ;
1662 if (&is_lesser ($version, $target)) {
1663 &debug ("Fixing past mistakes in role naming") ;
1665 my $defaultroles_restricted = {
1666 'Admin' => { 'projectadmin'=>'A', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'trackeradmin'=>'2', 'pmadmin'=>'2' },
1667 'Senior Developer' => { 'projectadmin'=>'0', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'trackeradmin'=>'2', 'pmadmin'=>'2' },
1668 'Junior Developer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'1', 'docman'=>'0', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' },
1669 'Doc Writer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' },
1670 'Support Tech' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' }
1673 foreach my $drname (keys %{$defaultroles_restricted}) {
1674 $query = "UPDATE role SET role_name='$drname' WHERE role_id IN (SELECT role.role_id" ;
1679 foreach my $setting (keys %{$defaultroles_restricted->{$drname}}) {
1680 $value = $defaultroles_restricted->{$drname}->{$setting} ;
1681 $from .= ", role_setting rs_$setting" ;
1682 $where .= "role.role_id = rs_$setting.role_id AND rs_$setting.section_name='$setting' AND " ;
1683 $where .= "rs_$setting.value = '$value' \nAND " ;
1685 $query .= "\nFROM role$from" ;
1686 $query .= "\nWHERE $where role.role_name='rname')";
1687 push @reqlist, $query;
1690 foreach my $s (@reqlist) {
1693 $sth = $dbh->prepare ($query) ;
1699 &update_db_version ($target) ;
1704 &update_with_sql("20051103_transiciel_motscle_document","4.6-1");
1706 &update_with_sql("20070924-forum-perm","4.6.99-1");
1707 &update_with_sql("20070924-project-perm","4.6.99-2");
1708 &update_with_sql("20070924-artifact-perm","4.6.99-3");
1710 $version = &get_db_version ;
1711 $target = "4.6.99-4" ;
1712 if (&is_lesser ($version, $target)) {
1713 &debug ("Dropping old translations table") ;
1715 &drop_table_if_exists ($dbh, "tmp_lang") ;
1717 &update_db_version ($target) ;
1722 $version = &get_db_version ;
1723 $target = "4.6.99-5" ;
1724 if (&is_lesser ($version, $target)) {
1725 &debug ("Updating available themes") ;
1727 my @obsolete_themes = qw/ classic debian savannah
1728 savannah_codex savannah_forest
1729 savannah_reverse savannah_sad
1730 savannah_savannah savannah_slashd
1732 savannah_transparent savannah_water
1733 savannah_www.gnu.org
1734 savannah_darkslate forged kde
1737 my $otids = join (',', map { "'$_'" } @obsolete_themes) ;
1739 $query = "UPDATE users SET theme_id=1 WHERE theme_id IN
1740 (SELECT theme_id FROM themes WHERE dirname IN ($otids))" ;
1741 push @reqlist, $query;
1743 $query = "DELETE FROM themes WHERE dirname IN ($otids)" ;
1744 push @reqlist, $query;
1747 'gforge-classic' => 'GForge classic',
1748 'gforge-simple-theme' => 'GForge simple',
1749 'lite' => 'GForge lite'
1752 foreach my $dir (sort keys %new_themes) {
1753 $query = "INSERT INTO themes (dirname, fullname) VALUES ('$dir', '$new_themes{$dir}')" ;
1754 push @reqlist, $query;
1757 foreach my $s (@reqlist) {
1760 $sth = $dbh->prepare ($query) ;
1766 &update_db_version ($target) ;
1771 $version = &get_db_version ;
1772 $target = "4.6.99-6" ;
1773 if (&is_lesser ($version, $target)) {
1774 &debug ("DROP UNIQUE INDEX never UNIQUE") ;
1775 &drop_index_if_exists ($dbh, "statsaggsitebygrp_oid") ;
1776 &drop_index_if_exists ($dbh, "statsprojectmetric_oid") ;
1777 &drop_index_if_exists ($dbh, "statsagglogobygrp_oid") ;
1778 &drop_index_if_exists ($dbh, "statsprojectdevelop_oid") ;
1779 &drop_index_if_exists ($dbh, "statssubdpages_oid") ;
1780 &drop_index_if_exists ($dbh, "statscvsgrp_oid") ;
1781 &drop_index_if_exists ($dbh, "statsproject_oid") ;
1782 &drop_index_if_exists ($dbh, "statssite_oid") ;
1783 &drop_index_if_exists ($dbh, "statssitepgsbyday_oid") ;
1784 &update_db_version ($target) ;
1789 &update_with_sql("20090327_create_table_project_tags","4.6.99-7");
1790 &update_with_sql("20090402-add-projecttags-constraints","4.7.99-1");
1791 &update_with_sql("20090402-forum-attachment-types","4.7.99-2");
1793 &update_with_sql("20090507-add_artifact_workflow","4.8.99-1");
1794 &update_with_sql("20090507-add_element_pos","4.8.99-2");
1795 &update_with_sql("20090507-add_project_query","4.8.99-3");
1796 &update_with_sql("20090507-browse_list","4.8.99-4");
1798 $version = &get_db_version ;
1799 $target = "4.8.99-5" ;
1800 if (&is_lesser ($version, $target)) {
1801 &debug ("Initialising tracker workflows") ;
1804 $query = "SELECT group_id, artifact_group_list.group_artifact_id, element_id, artifact_extra_field_elements.extra_field_id
1805 FROM artifact_extra_field_list, artifact_extra_field_elements, artifact_group_list
1806 WHERE artifact_extra_field_list.extra_field_id=artifact_extra_field_elements.extra_field_id
1807 AND artifact_group_list.group_artifact_id = artifact_extra_field_list.group_artifact_id
1810 $sth = $dbh->prepare ($query) ;
1812 while (@array = $sth->fetchrow_array) {
1813 my $gid = $array[0];
1814 my $gaid = $array[1];
1815 my $eid = $array[2];
1817 my $query2 = "SELECT extra_field_id
1818 FROM artifact_extra_field_list
1819 WHERE group_artifact_id=$gaid
1821 ORDER BY field_type ASC" ;
1822 my $sth2 = $dbh->prepare ($query2) ;
1825 if (my @array2 = $sth2->fetchrow_array) {
1826 my $efid = $array2[0];
1829 $query2 = "SELECT element_id,element_name,status_id
1830 FROM artifact_extra_field_elements
1831 WHERE extra_field_id = $efid
1832 ORDER BY element_pos ASC, element_id ASC" ;
1834 $sth2 = $dbh->prepare ($query2) ;
1836 while (@array2 = $sth2->fetchrow_array) {
1837 my $eid2 = $array2[0];
1838 if ($eid2 != $eid) {
1839 my $query3 = "INSERT INTO artifact_workflow_event
1840 (group_artifact_id, field_id, from_value_id, to_value_id)
1841 VALUES ($gaid, $efid, $eid, $eid2)";
1843 my $sth3 = $dbh->prepare ($query3) ;
1846 $query3 = "INSERT INTO artifact_workflow_event
1847 (group_artifact_id, field_id, from_value_id, to_value_id)
1848 VALUES ($gaid, $efid, $eid2, $eid)";
1850 $sth3 = $dbh->prepare ($query3) ;
1856 my $query3 = "INSERT INTO artifact_workflow_event
1857 (group_artifact_id, field_id, from_value_id, to_value_id)
1858 VALUES ($gaid, $efid, 100, $eid)";
1860 my $sth3 = $dbh->prepare ($query3) ;
1868 &update_db_version ($target) ;
1873 &update_with_sql("20100308-forum-attachment-types","4.8.99-6");
1875 $version = &get_db_version ;
1876 $target = "4.8.99-7" ;
1877 if (&is_lesser ($version, $target)) {
1878 &debug ("Granting read access permissions to NSS and MTA") ;
1880 @reqlist = ( "GRANT SELECT ON nss_passwd TO ${sys_dbuser}_nss",
1881 "GRANT SELECT ON nss_groups TO ${sys_dbuser}_nss",
1882 "GRANT SELECT ON nss_usergroups TO ${sys_dbuser}_nss",
1883 "GRANT SELECT ON mta_users TO ${sys_dbuser}_mta",
1884 "GRANT SELECT ON mta_lists TO ${sys_dbuser}_mta",
1886 foreach my $s (@reqlist) {
1889 $sth = $dbh->prepare ($query) ;
1895 &update_db_version ($target) ;
1900 &update_with_sql("20100330-add-system-event","5.0.0-1");
1901 &update_with_sql("20100331-alter-system-event","5.0.0-2");
1902 &update_with_sql("20100505-alter-user-preference","5.0.1-1");
1903 &update_with_sql("20100506-add-widgets","5.0.1-2");
1904 &update_with_sql("20100517-add-project-widgets","5.0.1-3");
1905 &update_with_sql("20100518-pfo-rbac","5.0.1-4");
1906 &update_with_sql("20100524-pfo-rbac","5.0.1-5");
1907 &update_with_sql("20100606-clean-perm-views","5.0.1-6");
1908 &update_with_sql("20100610-pfo-rbac","5.0.1-7");
1909 &update_with_sql("20100730-docman","5.0.1-8");
1910 &update_with_sql("20100924-theme","5.0.1-9");
1911 &update_with_sql("20100926-pfo-rbac","5.0.1-10");
1912 &update_with_sql("20100927-pfo-rbac","5.0.1-11");
1913 &update_with_sql("20101012-docman-webdav","5.0.51-1");
1914 &update_with_sql("20101021-pfo-rbac","5.0.51-2");
1915 &update_with_sql("20101025-ipv6","5.0.51-3");
1917 $version = &get_db_version ;
1918 $target = "5.0.51-4" ;
1919 if (&is_lesser ($version, $target)) {
1920 &debug ("Granting read access permissions to NSS and MTA") ;
1922 @reqlist = ( "GRANT SELECT ON nss_passwd TO ${sys_dbuser}_nss",
1923 "GRANT SELECT ON nss_groups TO ${sys_dbuser}_nss",
1924 "GRANT SELECT ON nss_usergroups TO ${sys_dbuser}_nss",
1925 "GRANT SELECT ON mta_users TO ${sys_dbuser}_mta",
1926 "GRANT SELECT ON mta_lists TO ${sys_dbuser}_mta",
1928 foreach my $s (@reqlist) {
1931 $sth = $dbh->prepare ($query) ;
1937 &update_db_version ($target) ;
1942 &update_with_sql("20101027-docman-lock","5.0.51-5");
1943 &update_with_sql("20101105-pfo-rbac","5.0.51-6");
1944 &update_with_sql("20101029-docman-monitoring","5.0.51-7");
1945 &update_with_sql("20100402_add_query_options","5.0.51-8");
1946 &update_with_sql("20101024-docman-createonline","5.0.51-9");
1947 &update_with_sql("20101213-project-template","5.0.51-10");
1948 &update_with_sql("20110110-pw-size","5.0.51-11");
1949 &update_with_sql("20110405-forum_attachment_fix-bug284","5.0.51-12");
1950 &update_with_sql("20110408-anonymous-read-news","5.0.51-13");
1951 &update_with_sql("20110414-move-news-forums-to-own-project","5.0.51-14");
1952 &update_with_sql("20110701-gforge-5.1","5.1-1");
1953 &update_with_sql("20110728-fix-mta-lists-view","5.1-2");
1954 &update_with_sql("FTI","5.1-3");
1955 &update_with_sql("FTI-20050315","5.1-4");
1956 &update_with_sql("FTI-20050401","5.1-5");
1957 &update_with_sql("FTI-20050530","5.1-6");
1958 &update_with_sql("FTI-20060130","5.1-7");
1959 &update_with_sql("FTI-20061025","5.1-8");
1960 &update_with_sql("20111007-fti-aggregate-functions","5.1-9");
1961 &update_with_sql("20111007-add-fti-for-task-messages","5.1-10");
1962 &update_with_sql("20111009-string-aggregate-function","5.1-11");
1963 &update_with_sql("20110118-user-tooltips","5.1.51-1");
1964 &update_with_sql("20110211-docman-activityvw","5.1.51-2");
1965 &update_with_sql("20110222-pluginify-auth","5.1.51-3");
1967 &update_with_sql("20110310-docman-trashdir","5.1.51-4");
1968 &update_with_sql("20110405-forum_attachment_fix-bug284","5.1.51-5");
1969 &update_with_sql("20110408-anonymous-read-news","5.1.51-6");
1970 &update_with_sql("20110728-fix-mta-lists-view","5.1.51-7");
1971 &update_with_sql("20110824-plugin-projects-hierarchy-rename","5.1.51-8");
1972 &update_with_sql("20111002-docman-doc_groups-createdate-updatedate","5.1.51-9");
1974 &update_with_sql("schema-convergence-deb-1","5.1.51-10");
1977 $version = &get_db_version ;
1978 $target = "5.1.51-11" ;
1979 if (&is_lesser ($version, $target)) {
1980 if (!&table_exists ($dbh, 'plugin_cvstracker_data_artifact')) {
1981 &debug ("Conditional part of the database schema convergence") ;
1984 "CREATE SEQUENCE plugin_cvstracker_artifact_seq START WITH 1 INCREMENT BY 1 NO MINVALUE MAXVALUE 2147483647 CACHE 1",
1985 "CREATE SEQUENCE plugin_cvstracker_master_seq START WITH 1 INCREMENT BY 1 NO MINVALUE MAXVALUE 2147483647 CACHE 1",
1987 "CREATE TABLE plugin_cvstracker_data_artifact ( id integer DEFAULT nextval(('plugin_cvstracker_artifact_seq'::text)::regclass) NOT NULL, kind integer DEFAULT 0 NOT NULL, group_artifact_id integer, project_task_id integer )",
1988 "CREATE TABLE plugin_cvstracker_data_master ( id integer DEFAULT nextval(('plugin_cvstracker_master_seq'::text)::regclass) NOT NULL, holder_id integer NOT NULL, log_text text DEFAULT ''::text, file text DEFAULT ''::text NOT NULL, prev_version text DEFAULT ''::text, actual_version text DEFAULT ''::text, author text DEFAULT ''::text NOT NULL, cvs_date integer NOT NULL )",
1990 "CREATE INDEX plugin_cvstracker_group_artifact_id ON plugin_cvstracker_data_artifact USING btree (group_artifact_id)",
1992 "ALTER TABLE ONLY plugin_cvstracker_data_artifact ADD CONSTRAINT plugin_cvstracker_artifact_pkey PRIMARY KEY (id)",
1993 "ALTER TABLE ONLY plugin_cvstracker_data_master ADD CONSTRAINT "$1" FOREIGN KEY (holder_id) REFERENCES plugin_cvstracker_data_artifact(id)",
1994 "ALTER TABLE ONLY plugin_cvstracker_data_master ADD CONSTRAINT "$2" FOREIGN KEY (author) REFERENCES users(user_name)",
1995 "ALTER TABLE ONLY plugin_cvstracker_data_master ADD CONSTRAINT plugin_cvstracker_master_pkey PRIMARY KEY (id)",
1997 foreach my $s (@reqlist) {
2000 $sth = $dbh->prepare ($query) ;
2007 &update_db_version ($target) ;
2012 $version = &get_db_version ;
2013 $target = "5.1.51-12" ;
2014 if (&is_lesser ($version, $target)) {
2015 &debug ("Creating database_startpoint and database_changes") ;
2017 @reqlist = ( "CREATE TABLE database_changes (filename text)",
2018 "CREATE TABLE database_startpoint (db_version character varying(10), db_start_date integer)",
2019 "INSERT INTO database_startpoint (db_version, db_start_date) VALUES ('5.1.51', 20111017)",
2021 foreach my $s (@reqlist) {
2024 $sth = $dbh->prepare ($query) ;
2030 &update_db_version ($target) ;
2035 $version = &get_db_version ;
2036 if ($version eq "5.1.51-11") {
2037 &debug ("Dropping debian_meta_data table.") ;
2038 &drop_table_if_exists ($dbh, 'debian_meta_data') ;
2043 ########################### INSERT HERE #################################
2045 # There should be a commit at the end of every block above.
2046 # If there is not, then it might be symptomatic of a problem.
2047 # For safety, we roll back.
2052 warn "Transaction aborted because $@" ;
2053 &debug ("Transaction aborted because $@") ;
2054 &debug ("Last SQL query was:\n$query\n(end of query)") ;
2056 my $version = &get_db_version ;
2058 &debug ("Your database schema is at version $version") ;
2060 &debug ("Couldn't get your database schema version.") ;
2062 &debug ("Please report this bug on the Debian bug-tracking system.") ;
2063 &debug ("Please include the previous messages as well to help debugging.") ;
2064 &debug ("You should not worry too much about this,") ;
2065 &debug ("your DB is still in a consistent state and should be usable.") ;
2072 sub get_pg_version () {
2074 if (-x '/usr/bin/pg_lsclusters' ) {
2075 $command = q(/usr/bin/pg_lsclusters | grep 5432 | grep online | cut -d' ' -f1) ;
2077 $command = q(dpkg -s postgresql | awk '/^Version: / { print $2 }') ;
2079 my $version = qx($command) ;
2084 sub create_metadata_table ( $ ) {
2085 my $v = shift || "2.5-7+just+before+8" ;
2087 my ($query, $sth, @array) ;
2089 # Let's create this table if we have it not
2090 if (! &table_exists ($dbh, 'debian_meta_data')) {
2091 &debug ("Creating debian_meta_data table.") ;
2092 $query = "CREATE TABLE debian_meta_data (key varchar primary key, value text not null)" ;
2094 $sth = $dbh->prepare ($query) ;
2099 $query = "SELECT count(*) FROM debian_meta_data WHERE key = 'db-version'";
2101 $sth = $dbh->prepare ($query) ;
2103 @array = $sth->fetchrow_array () ;
2106 # Empty table? We'll have to fill it up a bit
2108 if ($array [0] == 0) {
2109 &debug ("Inserting first data into debian_meta_data table.") ;
2110 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('db-version', '$v')" ;
2112 $sth = $dbh->prepare ($query) ;
2118 sub update_db_version ( $ ) {
2119 my $v = shift or die "Not enough arguments" ;
2121 $query = "UPDATE debian_meta_data SET value = '$v' WHERE key = 'db-version'" ;
2122 my $sth = $dbh->prepare ($query) ;
2127 sub get_db_version () {
2128 $query = "SELECT value FROM debian_meta_data WHERE key = 'db-version'" ;
2130 my $sth = $dbh->prepare ($query) ;
2132 my @array = $sth->fetchrow_array () ;
2135 my $version = $array [0] ;
2140 sub update_with_sql ( $$ ) {
2141 my $sqlfile = shift or die "Not enough arguments" ;
2142 my $target = shift or die "Not enough arguments" ;
2143 my $version = &get_db_version ;
2144 if (&is_lesser ($version, $target)) {
2145 &debug ("Upgrading database with $sqlfile.sql") ;
2147 @reqlist = @{ &parse_sql_file ("$sqldir/$sqlfile.sql") } ;
2148 foreach my $s (@reqlist) {
2151 my $sth = $dbh->prepare ($query) ;
2157 &update_db_version ($target) ;