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
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
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=`forge_get_config news_group`);
32 chomp($domain_name=`forge_get_config web_host`);
33 chomp($server_admin=`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.") ;
48 elsif (! &table_exists ($dbh, 'groups')) { # No 'groups' table
49 # Installing SF 2.6 from scratch
50 &debug ("Creating initial Sourceforge database from files.") ;
52 &create_metadata_table ("2.5.9999") ;
54 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
56 $sth = $dbh->prepare ($query) ;
58 @array = $sth->fetchrow_array () ;
61 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', 'scratch-to-2.6')" ;
63 $sth = $dbh->prepare ($query) ;
70 } else { # A 'groups' table exists
71 if (! &table_exists ($dbh, 'debian_meta_data')) { # No 'debian_meta_data' table
72 # If we're here, we're upgrading from 2.5-7 or earlier
73 # We therefore need to create the table
74 &create_metadata_table ("2.5-7+just+before+8") ;
77 $version = &get_db_version ;
78 if (&is_lesser ($version, "2.5.9999")) {
79 &debug ("Found an old (2.5) database, will upgrade to 2.6") ;
81 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
83 $sth = $dbh->prepare ($query) ;
85 @array = $sth->fetchrow_array () ;
89 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('current-path', '2.5-to-2.6')" ;
90 $sth = $dbh->prepare ($query) ;
98 $query = "SELECT count(*) from debian_meta_data where key = 'current-path'";
99 $sth = $dbh->prepare ($query) ;
101 @array = $sth->fetchrow_array () ;
104 if ($array[0] == 0) {
107 $query = "SELECT value from debian_meta_data where key = 'current-path'";
108 $sth = $dbh->prepare ($query) ;
110 @array = $sth->fetchrow_array () ;
117 ($path eq 'scratch-to-2.6') && do {
118 &update_with_sql ("sf-2.6-complete", "2.5.9999.1+global+data+done") ;
120 $version = &get_db_version ;
121 $target = "2.5.9999.2+local+data+done" ;
122 if (&is_lesser ($version, $target)) {
123 &debug ("Adding local data.") ;
125 my ($login, $md5pwd, $unixpwd, $email, $noreplymail, $date) ;
128 $md5pwd = 'INVALID' ;
129 $unixpwd = 'INVALID' ;
130 $email = $server_admin ;
131 $noreplymail="noreply\@$domain_name" ;
135 "UPDATE groups SET homepage = '$domain_name/admin/' where group_id = 1",
136 "UPDATE groups SET homepage = '$domain_name/news/' where group_id = 2",
137 "UPDATE groups SET homepage = '$domain_name/stats/' where group_id = 3",
138 "UPDATE groups SET homepage = '$domain_name/peerrating/' where group_id = 4",
139 "UPDATE users SET email = '$noreplymail' where user_id = 100",
140 "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)",
141 "SELECT setval ('\"users_pk_seq\"', 102, 'f')",
142 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 1, 'A')",
143 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 2, 'A')",
144 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 3, 'A')",
145 "INSERT INTO user_group (user_id, group_id, admin_flags) VALUES (101, 4, 'A')"
148 foreach my $s (@reqlist) {
151 $sth = $dbh->prepare ($query) ;
157 &update_db_version ($target) ;
162 $version = &get_db_version ;
163 $target = "2.5.9999.3+skills+done" ;
164 if (&is_lesser ($version, $target)) {
165 &debug ("Inserting skills.") ;
167 foreach my $skill (split m/;/, "Ada;C;C++;HTML;LISP;Perl;PHP;Python;SQL") {
168 push @reqlist, "INSERT INTO people_skill (name) VALUES ('$skill')" ;
171 foreach my $s (@reqlist) {
174 $sth = $dbh->prepare ($query) ;
180 &update_db_version ($target) ;
185 $version = &get_db_version ;
186 $target = "2.6-0+checkpoint+1" ;
187 if (&is_lesser ($version, $target)) {
188 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
190 $sth = $dbh->prepare ($query) ;
194 &update_db_version ($target) ;
202 ($path eq '2.5-to-2.6') && do {
204 $version = &get_db_version ;
206 if (&is_lesser ($version, $target)) {
207 &debug ("Adding row to people_job_category.") ;
208 $query = "INSERT INTO people_job_category VALUES (100, 'Undefined', 0)" ;
209 $sth = $dbh->prepare ($query) ;
213 &update_db_version ($target) ;
218 $version = &get_db_version ;
220 if (&is_lesser ($version, $target)) {
221 &debug ("Adding row to supported_languages.") ;
222 $query = "INSERT INTO supported_languages VALUES (15, 'Korean', 'Korean.class', 'Korean', 'kr')" ;
223 $sth = $dbh->prepare ($query) ;
227 &update_db_version ($target) ;
232 $version = &get_db_version ;
234 if (&is_lesser ($version, $target)) {
235 &debug ("Fixing unix_box entries.") ;
237 $query = "update groups set unix_box = 'shell'" ;
238 $sth = $dbh->prepare ($query) ;
242 $query = "update users set unix_box = 'shell'" ;
243 $sth = $dbh->prepare ($query) ;
247 &debug ("Also fixing a few sequences.") ;
249 &bump_sequence_to ($dbh, "bug_pk_seq", 100) ;
250 &bump_sequence_to ($dbh, "project_task_pk_seq", 100) ;
252 &update_db_version ($target) ;
257 $version = &get_db_version ;
259 if (&is_lesser ($version, $target)) {
260 &debug ("Adding rows to supported_languages.") ;
262 "INSERT INTO supported_languages VALUES (16,'Bulgarian','Bulgarian.class','Bulgarian','bg')",
263 "INSERT INTO supported_languages VALUES (17,'Greek','Greek.class','Greek','el')",
264 "INSERT INTO supported_languages VALUES (18,'Indonesian','Indonesian.class','Indonesian','id')",
265 "INSERT INTO supported_languages VALUES (19,'Portuguese (Brazillian)','PortugueseBrazillian.class','PortugueseBrazillian', 'br')",
266 "INSERT INTO supported_languages VALUES (20,'Polish','Polish.class','Polish','pl')",
267 "INSERT INTO supported_languages VALUES (21,'Portuguese','Portuguese.class','Portuguese', 'pt')",
268 "INSERT INTO supported_languages VALUES (22,'Russian','Russian.class','Russian','ru')"
271 foreach my $s (@reqlist) {
274 $sth = $dbh->prepare ($query) ;
280 &update_db_version ($target) ;
285 $version = &get_db_version ;
287 if (&is_lesser ($version, $target)) {
288 &debug ("Fixing unix_uid entries.") ;
290 $query = "UPDATE users SET unix_uid = nextval ('unix_uid_seq') WHERE unix_status != 'N' AND status != 'P' AND unix_uid = 0" ;
291 $sth = $dbh->prepare ($query) ;
295 &update_db_version ($target) ;
300 $version = &get_db_version ;
301 $target = "2.5.9999.1+temp+data+dropped" ;
302 if (&is_lesser ($version, $target)) {
303 &debug ("Preparing to upgrade your database - dropping temporary tables") ;
305 my @tables = qw/ user_metric_tmp1_1 user_metric_tmp1_2
306 user_metric_tmp1_3 user_metric_tmp1_4
307 user_metric_tmp1_5 user_metric_tmp1_6
308 user_metric_tmp1_7 user_metric_tmp1_8 user_metric1
309 user_metric2 user_metric3 user_metric4 user_metric5
310 user_metric6 user_metric7 user_metric8
311 project_counts_tmp project_metric_tmp
312 project_metric_tmp1 project_counts_weekly_tmp
313 project_metric_weekly_tmp project_metric_weekly_tmp1
316 my @sequences = qw/ user_metric1_ranking_seq
317 user_metric2_ranking_seq user_metric3_ranking_seq
318 user_metric4_ranking_seq user_metric5_ranking_seq
319 user_metric6_ranking_seq user_metric7_ranking_seq
320 user_metric8_ranking_seq project_metric_weekly_seq
321 trove_treesum_trove_treesum_seq
322 project_metric_tmp1_pk_seq / ;
324 my @indexes = qw/ idx_project_metric_group
325 idx_project_metric_weekly_group
326 user_metric_history_date_userid / ;
328 foreach my $table (@tables) {
329 &drop_table_if_exists ($dbh, $table) ;
332 foreach my $sequence (@sequences) {
333 &drop_sequence_if_exists ($dbh, $sequence) ;
336 foreach my $index (@indexes) {
337 &drop_index_if_exists ($dbh, $index) ;
340 &update_db_version ($target) ;
345 $version = &get_db_version ;
346 $target = "2.5.9999.2+data+upgraded" ;
347 if (&is_lesser ($version, $target)) {
348 &debug ("Upgrading your database scheme from 2.5") ;
351 "ALTER TABLE groups DROP CONSTRAINT groups_pkey",
352 "ALTER TABLE users DROP CONSTRAINT users_pkey",
354 foreach my $s (@reqlist) {
357 $sth = $dbh->prepare ($query) ;
362 @reqlist = @{ &parse_sql_file ("$sqldir/sf2.5-to-sf2.6.sql") } ;
363 foreach my $s (@reqlist) {
366 $sth = $dbh->prepare ($query) ;
372 &update_db_version ($target) ;
377 $version = &get_db_version ;
378 $target = "2.5.9999.3+artifact+transcoded" ;
379 if (&is_lesser ($version, $target)) {
380 &debug ("Transcoding the artifact data fields") ;
382 $query = "SELECT id,bin_data FROM artifact_file ORDER BY id ASC" ;
384 $sth = $dbh->prepare ($query) ;
386 while (@array = $sth->fetchrow_array) {
387 my $query2 = "UPDATE artifact_file SET bin_data='" ;
388 $query2 .= encode_base64 (decode_entities ($array [1])) ;
389 $query2 .= "' WHERE id=" ;
390 $query2 .= $array [0] ;
393 my $sth2 =$dbh->prepare ($query2) ;
400 &update_db_version ($target) ;
405 $version = &get_db_version ;
406 $target = "2.5.9999.4+groups+inserted" ;
407 if (&is_lesser ($version, $target)) {
408 &debug ("Inserting missing groups") ;
411 "INSERT INTO groups (group_name, homepage,
412 is_public, status, unix_group_name,
413 unix_box, http_domain, short_description,
414 cvs_box, license, register_purpose,
415 license_other, register_time, rand_hash,
416 use_mail, use_survey, use_forum, use_pm,
417 use_cvs, use_news, type, use_docman,
418 new_task_address, send_all_tasks,
420 VALUES ('Stats', '$domain_name/top/', 0,
421 'A', 'stats', 'shell', NULL, NULL, 'cvs',
422 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
424 "INSERT INTO groups (group_name, homepage,
425 is_public, status, unix_group_name,
426 unix_box, http_domain, short_description,
427 cvs_box, license, register_purpose,
428 license_other, register_time, rand_hash,
429 use_mail, use_survey, use_forum, use_pm,
430 use_cvs, use_news, type, use_docman,
431 new_task_address, send_all_tasks,
433 VALUES ('Peer Ratings', '$domain_name/people/', 0,
434 'A', 'peerrating', 'shell', NULL, NULL, 'cvs1',
435 'website', NULL, NULL, 0, NULL, 1, 0, 0, 0, 0,
439 foreach my $s (@reqlist) {
442 $sth = $dbh->prepare ($query) ;
447 &update_db_version ($target) ;
452 $version = &get_db_version ;
453 $target = "2.6-0+checkpoint+1" ;
454 if (&is_lesser ($version, $target)) {
455 $query = "DELETE FROM debian_meta_data WHERE key = 'current-path'" ;
457 $sth = $dbh->prepare ($query) ;
461 &update_db_version ($target) ;
470 $version = &get_db_version ;
471 $target = "2.6-0+checkpoint+2" ;
472 if (&is_lesser ($version, $target)) {
473 &debug ("Updating permissions on system groups.") ;
474 $query = "UPDATE groups SET group_name='Site Admin', is_public=1 WHERE group_id=1" ;
476 $sth = $dbh->prepare ($query) ;
479 $query = "UPDATE groups SET group_name='Site News Admin', is_public=1 WHERE group_id=$sys_news_group" ;
481 $sth = $dbh->prepare ($query) ;
485 &update_db_version ($target) ;
490 $version = &get_db_version ;
491 $target = "2.6-0+checkpoint+3" ;
492 if (&is_lesser ($version, $target)) {
493 &debug ("Creating table group_cvs_history.") ;
494 $query = "CREATE TABLE group_cvs_history (
495 id integer DEFAULT nextval('group_cvs_history_pk_seq'::text) NOT NULL,
496 group_id integer DEFAULT '0' NOT NULL,
497 user_name character varying(80) DEFAULT '' NOT NULL,
498 cvs_commits integer DEFAULT '0' NOT NULL,
499 cvs_commits_wk integer DEFAULT '0' NOT NULL,
500 cvs_adds integer DEFAULT '0' NOT NULL,
501 cvs_adds_wk integer DEFAULT '0' NOT NULL,
504 $sth = $dbh->prepare ($query) ;
508 &update_db_version ($target) ;
513 $version = &get_db_version ;
514 $target = "2.6-0+checkpoint+4" ;
515 if (&is_lesser ($version, $target)) {
516 &debug ("Registering Savannah themes.") ;
518 $query = "SELECT max(theme_id) FROM themes" ;
520 $sth = $dbh->prepare ($query) ;
522 @array = $sth->fetchrow_array () ;
524 my $maxid = $array [0] ;
526 &bump_sequence_to ($dbh, "themes_pk_seq", $maxid) ;
529 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_codex', 'Savannah CodeX')",
530 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_forest', 'Savannah Forest')",
531 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_reverse', 'Savannah Reverse')",
532 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_sad', 'Savannah Sad')",
533 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_savannah', 'Savannah Original')",
534 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_slashd', 'Savannah SlashDot')",
535 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_startrek', 'Savannah StarTrek')",
536 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_transparent', 'Savannah Transparent')",
537 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_water', 'Savannah Water')",
538 "INSERT INTO themes (dirname, fullname) VALUES ('savannah_www.gnu.org', 'Savannah www.gnu.org')"
540 foreach my $s (@reqlist) {
543 $sth = $dbh->prepare ($query) ;
549 &update_db_version ($target) ;
554 $version = &get_db_version ;
555 $target = "2.6-0+checkpoint+5" ;
556 if (&is_lesser ($version, $target)) {
557 &debug ("Registering yet another Savannah theme.") ;
559 $query = "INSERT INTO themes (dirname, fullname) VALUES ('savannah_darkslate', 'Savannah Dark Slate')";
561 $sth = $dbh->prepare ($query) ;
565 &update_db_version ($target) ;
570 $version = &get_db_version ;
571 $target = "2.6-0+checkpoint+6" ;
572 if (&is_lesser ($version, $target)) {
573 &debug ("Updating language codes.") ;
576 "UPDATE supported_languages SET language_code='en' where classname='English'",
577 "UPDATE supported_languages SET language_code='ja' where classname='Japanese'",
578 "UPDATE supported_languages SET language_code='iw' where classname='Hebrew'",
579 "UPDATE supported_languages SET language_code='es' where classname='Spanish'",
580 "UPDATE supported_languages SET language_code='th' where classname='Thai'",
581 "UPDATE supported_languages SET language_code='de' where classname='German'",
582 "UPDATE supported_languages SET language_code='it' where classname='Italian'",
583 "UPDATE supported_languages SET language_code='no' where classname='Norwegian'",
584 "UPDATE supported_languages SET language_code='sv' where classname='Swedish'",
585 "UPDATE supported_languages SET language_code='zh' where classname='Chinese'",
586 "UPDATE supported_languages SET language_code='nl' where classname='Dutch'",
587 "UPDATE supported_languages SET language_code='eo' where classname='Esperanto'",
588 "UPDATE supported_languages SET language_code='ca' where classname='Catalan'",
589 "UPDATE supported_languages SET language_code='ko' where classname='Korean'",
590 "UPDATE supported_languages SET language_code='bg' where classname='Bulgarian'",
591 "UPDATE supported_languages SET language_code='el' where classname='Greek'",
592 "UPDATE supported_languages SET language_code='id' where classname='Indonesian'",
593 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese (Brazillian)'",
594 "UPDATE supported_languages SET language_code='pl' where classname='Polish'",
595 "UPDATE supported_languages SET language_code='pt' where classname='Portuguese'",
596 "UPDATE supported_languages SET language_code='ru' where classname='Russian'",
597 "UPDATE supported_languages SET language_code='fr' where classname='French'"
599 foreach my $s (@reqlist) {
602 $sth = $dbh->prepare ($query) ;
607 &update_db_version ($target) ;
612 $version = &get_db_version ;
613 $target = "2.6-0+checkpoint+7" ;
614 if (&is_lesser ($version, $target)) {
615 &debug ("Fixing artifact-related views.") ;
617 &drop_view_if_exists ($dbh, "artifact_file_user_vw") ;
618 &drop_view_if_exists ($dbh, "artifact_history_user_vw") ;
619 &drop_view_if_exists ($dbh, "artifact_message_user_vw") ;
620 &drop_view_if_exists ($dbh, "artifactperm_artgrouplist_vw") ;
621 &drop_view_if_exists ($dbh, "artifactperm_user_vw") ;
622 &drop_view_if_exists ($dbh, "artifact_vw") ;
625 "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)",
626 "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)",
627 "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)",
628 "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)",
629 "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)",
630 "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))"
632 foreach my $s (@reqlist) {
635 $sth = $dbh->prepare ($query) ;
640 &update_db_version ($target) ;
645 $version = &get_db_version ;
646 $target = "2.6-0+checkpoint+8" ;
647 if (&is_lesser ($version, $target)) {
648 &debug ("Adding integrity constraints between the Trove map tables.") ;
651 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
652 "ALTER TABLE trove_group_link ADD CONSTRAINT tgl_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_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
654 "ALTER TABLE trove_agg ADD CONSTRAINT trove_agg_group_id_fk FOREIGN KEY (group_id) REFERENCES groups(group_id) MATCH FULL",
655 "DELETE FROM trove_treesums WHERE trove_cat_id NOT IN (SELECT trove_cat_id FROM trove_cat)",
656 "ALTER TABLE trove_treesums ADD CONSTRAINT trove_treesums_cat_id_fk FOREIGN KEY (trove_cat_id) REFERENCES trove_cat(trove_cat_id) MATCH FULL",
658 foreach my $s (@reqlist) {
661 $sth = $dbh->prepare ($query) ;
666 &update_db_version ($target) ;
671 $version = &get_db_version ;
672 $target = "2.6-0+checkpoint+9" ;
673 if (&is_lesser ($version, $target)) {
674 &debug ("Adding extra fields to the groups table.") ;
677 "ALTER TABLE groups ADD COLUMN use_ftp integer",
678 "ALTER TABLE groups ALTER COLUMN use_ftp SET DEFAULT 1",
679 "UPDATE groups SET use_ftp = 1",
680 "ALTER TABLE groups ADD COLUMN use_tracker integer",
681 "ALTER TABLE groups ALTER COLUMN use_tracker SET DEFAULT 1",
682 "UPDATE groups SET use_tracker = 1",
683 "ALTER TABLE groups ADD COLUMN use_frs integer",
684 "ALTER TABLE groups ALTER COLUMN use_frs SET DEFAULT 1",
685 "UPDATE groups SET use_frs = 1",
686 "ALTER TABLE groups ADD COLUMN use_stats integer",
687 "ALTER TABLE groups ALTER COLUMN use_stats SET DEFAULT 1",
688 "UPDATE groups SET use_stats = 1",
689 "ALTER TABLE groups ADD COLUMN enable_pserver integer",
690 "ALTER TABLE groups ALTER COLUMN enable_pserver SET DEFAULT 1",
691 "UPDATE groups SET enable_pserver = 1",
692 "ALTER TABLE groups ADD COLUMN enable_anoncvs integer",
693 "ALTER TABLE groups ALTER COLUMN enable_anoncvs SET DEFAULT 1",
694 "UPDATE groups SET enable_anoncvs = 1",
696 foreach my $s (@reqlist) {
699 $sth = $dbh->prepare ($query) ;
704 &update_db_version ($target) ;
709 $version = &get_db_version ;
710 $target = "2.6-0+checkpoint+10" ;
711 if (&is_lesser ($version, $target)) {
712 &debug ("Updating supported_languages table.") ;
715 "ALTER TABLE supported_languages RENAME COLUMN language_code TO language_code_old",
716 "ALTER TABLE supported_languages ADD COLUMN language_code character(5)",
717 "UPDATE supported_languages SET language_code = language_code_old",
718 "ALTER TABLE supported_languages DROP COLUMN language_code_old",
719 "UPDATE supported_languages SET language_code='pt_BR', classname='PortugueseBrazilian', name='Pt. Brazilian', filename='PortugueseBrazilian.class' where classname='PortugueseBrazillian'",
721 foreach my $s (@reqlist) {
724 $sth = $dbh->prepare ($query) ;
729 &update_db_version ($target) ;
734 $version = &get_db_version ;
735 $target = "2.6-0+checkpoint+11" ;
736 if (&is_lesser ($version, $target)) {
737 &debug ("Adding tables for the plugin subsystem.") ;
740 "CREATE SEQUENCE plugins_pk_seq",
741 "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))",
742 "CREATE SEQUENCE group_plugin_pk_seq",
743 "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)",
744 "CREATE SEQUENCE user_plugin_pk_seq",
745 "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)",
747 foreach my $s (@reqlist) {
750 $sth = $dbh->prepare ($query) ;
755 &update_db_version ($target) ;
760 &update_with_sql("20021125", "2.6-0+checkpoint+12") ;
761 &update_with_sql("20021212", "2.6-0+checkpoint+13") ;
762 &update_with_sql("20021213-1", "2.6-0+checkpoint+14") ;
764 $version = &get_db_version ;
765 $target = "2.6-0+checkpoint+15" ;
766 if (&is_lesser ($version, $target)) {
767 &debug ("Transcoding documentation data fields") ;
768 $query = "SELECT docid,data FROM doc_data ORDER BY docid ASC" ;
770 $sth = $dbh->prepare ($query) ;
772 while (@array = $sth->fetchrow_array) {
773 my $query2 = "UPDATE doc_data SET data='" ;
774 $query2 .= encode_base64 (decode_entities ($array [1])) ;
775 $query2 .= "', filename='file".$array [0].".html'";
776 $query2 .= ", filetype='text/html'";
777 $query2 .= " WHERE docid=" ;
778 $query2 .= $array [0] ;
781 my $sth2 =$dbh->prepare ($query2) ;
788 &update_db_version ($target) ;
793 &update_with_sql("20021214", "2.6-0+checkpoint+16") ;
794 &update_with_sql("20021215", "2.6-0+checkpoint+17") ;
795 &update_with_sql("20021216", "2.6-0+checkpoint+18") ;
796 &update_with_sql("20021223-2", "2.6-0+checkpoint+19") ;
797 &update_with_sql("20030102-2", "2.6-0+checkpoint+20") ;
798 &update_with_sql("20030105", "2.6-0+checkpoint+21") ;
799 &update_with_sql("20030107", "2.6-0+checkpoint+22") ;
800 &update_with_sql("20030109", "2.6-0+checkpoint+23") ;
802 $version = &get_db_version ;
803 $target = "2.6-0+checkpoint+24" ;
804 if (&is_lesser ($version, $target)) {
806 &debug ("Adjusting language sequences") ;
808 $query = "SELECT max(language_id) FROM supported_languages" ;
809 $sth = $dbh->prepare ($query) ;
811 @array = $sth->fetchrow_array () ;
813 my $maxid = $array [0] ;
814 &bump_sequence_to ($dbh, "supported_languages_pk_seq", $maxid) ;
816 &debug ("Upgrading with 20030112.sql") ;
818 @reqlist = @{ &parse_sql_file ("$sqldir/20030112.sql") } ;
819 foreach my $s (@reqlist) {
822 $sth = $dbh->prepare ($query) ;
828 &update_db_version ($target) ;
833 &update_with_sql("20030113-2", "2.6-0+checkpoint+25") ;
834 &update_with_sql("20030131", "2.6-0+checkpoint+26") ;
835 &update_with_sql("20030209", "2.6-0+checkpoint+27") ;
836 &update_with_sql("20030312", "2.6-0+checkpoint+28") ;
838 $version = &get_db_version ;
839 $target = "2.6-0+checkpoint+29" ;
840 if (&is_lesser ($version, $target)) {
841 &debug ("Registering KDE theme.") ;
843 $query = "INSERT INTO themes (dirname, fullname) VALUES ('kde', 'KDE')";
845 $sth = $dbh->prepare ($query) ;
849 &update_db_version ($target) ;
855 $version = &get_db_version ;
856 $target = "2.6-0+checkpoint+30" ;
857 if (&is_lesser ($version, $target)) {
858 &debug ("Registering Dark Aqua theme.") ;
860 $query = "INSERT INTO themes (dirname, fullname) VALUES ('darkaqua', 'Dark Aqua')";
862 $sth = $dbh->prepare ($query) ;
866 &update_db_version ($target) ;
872 &update_with_sql("20030513", "2.6-0+checkpoint+31") ;
874 $version = &get_db_version ;
876 if (&is_lesser ($version, $target)) {
877 &debug ("Database schema is now version 3.0-1.") ;
879 &update_db_version ($target) ;
884 &update_with_sql("20030822", "3.0-7") ;
885 &update_with_sql("20031105", "3.1-0+1") ;
886 &update_with_sql("20031124", "3.1-0+1.1") ;
887 &update_with_sql("20031129", "3.1-0+2") ;
888 &update_with_sql("20031126", "3.1-0+3") ;
889 &update_with_sql("20031205", "3.2.1-0+2") ;
890 &update_with_sql("20040130", "3.2.1-0+3") ;
891 &update_with_sql("20040204", "3.2.1-0+4") ;
892 &update_with_sql("20040315", "3.2.1-0+5") ;
893 &update_with_sql("200403251", "3.3.0-0+0") ;
894 &update_with_sql("200403252", "3.3.0-0+1") ;
895 &update_with_sql("20040507", "3.3.0-0+3") ;
896 &update_with_sql("20040722", "3.3.0-0+4") ;
897 &update_with_sql("20040804", "3.3.0-0+6") ;
898 &update_with_sql("20040826", "3.3.0-0+7") ;
900 $version = &get_db_version ;
901 $target = "3.3.0-2+1" ;
902 if (&is_lesser ($version, $target)) {
903 &debug ("Migrating forum names") ;
905 $query = "SELECT group_forum_id,forum_name FROM forum_group_list" ;
907 $sth = $dbh->prepare ($query) ;
909 while (@array = $sth->fetchrow_array) {
910 my $forumid = $array[0] ;
911 my $oldname = $array[1] ;
913 my $newname = lc $oldname ;
914 $newname =~ s/[^_.0-9a-z-]/-/g ;
916 my $query2 = "UPDATE forum_group_list SET forum_name='$newname' WHERE group_forum_id=$forumid" ;
918 my $sth2 =$dbh->prepare ($query2) ;
924 &update_db_version ($target) ;
929 $version = &get_db_version ;
930 $target = "3.3.0-2+2" ;
931 if (&is_lesser ($version, $target)) {
932 &debug ("Migrating permissions to RBAC") ;
935 'Admin' => { 'projectadmin'=>'A', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'forum'=>'2', 'trackeradmin'=>'2', 'tracker'=>'2', 'pmadmin'=>'2', 'pm'=>'2' },
936 'Senior Developer' => { 'projectadmin'=>'0', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'forum'=>'2', 'trackeradmin'=>'2', 'tracker'=>'2', 'pmadmin'=>'2', 'pm'=>'2' },
937 'Junior Developer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'1', 'docman'=>'0', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'1', 'pmadmin'=>'0', 'pm'=>'1' },
938 'Doc Writer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'0', 'pmadmin'=>'0', 'pm'=>'0' },
939 'Support Tech' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'forum'=>'1', 'trackeradmin'=>'0', 'tracker'=>'2', 'pmadmin'=>'0', 'pm'=>'0' }
942 $query = "SELECT group_id FROM groups where status != 'P'" ;
944 $sth = $dbh->prepare ($query) ;
946 while (@array = $sth->fetchrow_array) {
947 my $group_id = $array[0] ;
949 my ($query2, $sth2, @array2, $admin_rid, $jd_rid, %roledata) ;
950 foreach my $rname (keys %$defaultroles) {
951 $query2 = "SELECT nextval('role_role_id_seq'::text)" ;
953 $sth2 =$dbh->prepare ($query2) ;
955 @array2 = $sth2->fetchrow_array ;
956 my $rid = $array2[0] ;
958 if ($rname eq 'Admin') {
960 } elsif ($rname eq 'Junior Developer') {
964 $query2 = "INSERT INTO role (role_id, group_id, role_name)
965 VALUES ($rid, $group_id, '$rname')" ;
967 $sth2 =$dbh->prepare ($query2) ;
971 foreach my $section (keys %{$defaultroles->{$rname}}) {
972 if ($section eq 'forum') {
973 $query2 = "SELECT group_forum_id
974 FROM forum_group_list
975 WHERE group_id = $group_id" ;
977 $sth2 =$dbh->prepare ($query2) ;
979 while (@array2 = $sth2->fetchrow_array) {
980 $roledata{'forum'}{$array2[0]} = $defaultroles->{$rname}{'forum'} ;
983 } elsif ($section eq 'pm') {
984 $query2 = "SELECT group_project_id
985 FROM project_group_list
986 WHERE group_id = $group_id" ;
988 $sth2 =$dbh->prepare ($query2) ;
990 while (@array2 = $sth2->fetchrow_array) {
991 $roledata{'pm'}{$array2[0]} = $defaultroles->{$rname}{'pm'} ;
994 } elsif ($section eq 'tracker') {
995 $query2 = "SELECT group_artifact_id
996 FROM artifact_group_list
997 WHERE group_id = $group_id" ;
999 $sth2 =$dbh->prepare ($query2) ;
1001 while (@array2 = $sth2->fetchrow_array) {
1002 $roledata{'tracker'}{$array2[0]} = $defaultroles->{$rname}{'tracker'} ;
1006 $roledata{$section}{0} = $defaultroles->{$rname}{$section} ;
1009 foreach my $rd_it (keys %{$roledata{$section}}) {
1010 $query2 = "INSERT INTO role_setting (role_id, section_name, ref_id, value)
1011 VALUES ($rid, '$section', $rd_it, '$roledata{$section}{$rd_it}')" ;
1012 # &debug ($query2) ;
1013 $sth2 =$dbh->prepare ($query2) ;
1022 # affecter le rôle Admin aux admins, JD aux autres
1023 $query2 = "SELECT user_id, admin_flags FROM user_group WHERE group_id = $group_id" ;
1024 # &debug ($query2) ;
1025 $sth2 =$dbh->prepare ($query2) ;
1027 while (@array2 = $sth2->fetchrow_array) {
1028 my $uid = $array2[0] ;
1029 my $adminflags = $array2[1] ;
1032 $adminflags =~ s/\s//g ;
1033 if ($adminflags eq 'A') {
1038 $rname = 'Junior Developer' ;
1043 admin_flags = '$defaultroles->{$rname}{'projectadmin'}',
1044 forum_flags = '$defaultroles->{$rname}{'forumadmin'}',
1045 project_flags = '$defaultroles->{$rname}{'pmadmin'}',
1046 doc_flags = '$defaultroles->{$rname}{'docman'}',
1047 cvs_flags = '$defaultroles->{$rname}{'scm'}',
1048 release_flags = '$defaultroles->{$rname}{'frs'}',
1049 artifact_flags = '$defaultroles->{$rname}{'trackeradmin'}'
1050 WHERE user_id = $uid AND group_id = $group_id" ,
1052 SET perm_level=$defaultroles->{$rname}{'forum'}
1053 WHERE group_forum_id IN (
1054 SELECT group_forum_id
1055 FROM forum_group_list
1056 WHERE group_id=$group_id)
1058 "UPDATE project_perm
1059 SET perm_level=$defaultroles->{$rname}{'pm'}
1060 WHERE group_project_id IN (
1061 SELECT group_project_id
1062 FROM project_group_list
1063 WHERE group_id=$group_id)
1065 "UPDATE artifact_perm
1066 SET perm_level=$defaultroles->{$rname}{'tracker'}
1067 WHERE group_artifact_id IN (
1068 SELECT group_artifact_id
1069 FROM artifact_group_list
1070 WHERE group_id=$group_id)
1073 foreach my $query3 (@reqlist3) {
1074 # &debug ($query3) ;
1075 my $sth3 = $dbh->prepare ($query3) ;
1084 &update_db_version ($target) ;
1089 &update_with_sql("20040914", "3.3.0-2+4") ;
1090 &update_with_sql("20041001", "3.3.0-2+4+1") ;
1091 &update_with_sql("20041005", "3.3.0-2+5") ;
1092 &update_with_sql("20041006", "3.3.0-2+6") ;
1093 &update_with_sql("20041014", "3.3.0-3") ;
1094 &update_with_sql("20041020", "3.3.0-4") ;
1095 &update_with_sql("20040729", "4.0.0-0") ;
1097 $version = &get_db_version ;
1098 $target = "4.0.0-0+1" ;
1099 if (&is_lesser ($version, $target)) {
1100 &debug ("Granting read access permissions to NSS") ;
1102 @reqlist = ( "GRANT SELECT ON nss_passwd TO ${sys_dbuser}_nss",
1103 "GRANT SELECT ON nss_groups TO ${sys_dbuser}_nss",
1104 "GRANT SELECT ON nss_usergroups TO ${sys_dbuser}_nss",
1106 foreach my $s (@reqlist) {
1109 $sth = $dbh->prepare ($query) ;
1115 &update_db_version ($target) ;
1120 $version = &get_db_version ;
1121 $target = "4.0.0-0+2" ;
1122 if (&is_lesser ($version, $target)) {
1123 &debug ("Upgrading with 20041031.sql") ;
1125 @reqlist = @{ &parse_sql_file ("$sqldir/20041031.sql") } ;
1126 foreach my $s (@reqlist) {
1129 $sth = $dbh->prepare ($query) ;
1135 &debug ("Granting read access permissions to NSS") ;
1137 @reqlist = ( "GRANT SELECT ON mta_users TO ${sys_dbuser}_mta",
1138 "GRANT SELECT ON mta_lists TO ${sys_dbuser}_mta",
1140 foreach my $s (@reqlist) {
1143 $sth = $dbh->prepare ($query) ;
1149 &update_db_version ($target) ;
1154 &update_with_sql("20041104", "4.0.0-0+3") ;
1155 &update_with_sql("20041108", "4.0.0-0+4") ;
1156 &update_with_sql("20041124", "4.0.2-0+0") ;
1158 $version = &get_db_version ;
1159 $target = "4.0.2-0+1" ;
1160 if (&is_lesser ($version, $target)) {
1161 &debug ("Creating automatic commit notification mailing-lists") ;
1164 $query = "SELECT group_id, unix_group_name FROM groups WHERE status='A' ORDER BY group_id" ;
1166 $sth = $dbh->prepare ($query) ;
1168 while (@array = $sth->fetchrow_array) {
1169 my $group_id = $array[0] ;
1170 my $group_name = $array[1] ;
1172 my $query2 = "SELECT count(*) FROM mail_group_list
1173 WHERE group_id = $group_id
1174 AND list_name = '".$group_name."-commits'" ;
1175 # &debug ($query2) ;
1176 my $sth2 =$dbh->prepare ($query2) ;
1178 my @array2 = $sth2->fetchrow_array ;
1180 if ($array2[0] == 0) {
1181 my $listname = $group_name."-commits" ;
1182 my $listpw = substr (Digest::MD5::md5_base64 ($listname . rand(1)), 0, 16) ;
1185 $query2 = "SELECT user_id FROM user_group
1186 WHERE admin_flags = 'A'
1187 AND group_id = $group_id" ;
1188 # &debug ($query2) ;
1189 $sth2 =$dbh->prepare ($query2) ;
1191 my $group_admin = -1 ;
1192 if (@array2 = $sth2->fetchrow_array) {
1193 $group_admin = $array2[0] ;
1197 $query2 = "INSERT INTO mail_group_list (group_id, list_name, is_public, password, list_admin, status, description)
1198 VALUES ($group_id, '$listname', 1, '$listpw', $group_admin, 1, 'commits')" ;
1199 # &debug ($query2) ;
1200 $sth2 =$dbh->prepare ($query2) ;
1207 &update_db_version ($target) ;
1212 &update_with_sql("20050115", "4.0.2-0+3") ;
1214 # We got this at upgrade
1216 #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 #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.
1219 #Last SQL query was:
1220 #update project_task SET last_modified_date=EXTRACT(EPOCH FROM now())::integer;
1222 #Your database schema is at version 4.0.2-0+5
1224 # This is a hack to disconnect and reconnect the DB and solve the problem
1230 $dbh->{AutoCommit} = 0;
1231 $dbh->{RaiseError} = 1;
1233 &update_with_sql("20050130", "4.0.2-0+5") ;
1234 &update_with_sql("20050212", "4.0.2-0+6") ;
1236 $version = &get_db_version ;
1237 $target = "4.0.2-0+7" ;
1238 if (&is_lesser ($version, $target)) {
1239 &debug ("Upgrading with 20050214-nss.sql") ;
1241 @reqlist = @{ &parse_sql_file ("$sqldir/20050214-nss.sql") } ;
1242 foreach my $s (@reqlist) {
1244 $query =~ s/TO gforge_nss;/TO ${sys_dbuser}_nss;/ ;
1246 $sth = $dbh->prepare ($query) ;
1252 &update_db_version ($target) ;
1257 &update_with_sql("20050224-2", "4.1-0") ;
1259 $version = &get_db_version ;
1261 if (&is_lesser ($version, $target)) {
1262 &debug ("Upgrading with 20050225-nsssetup.sql") ;
1264 @reqlist = @{ &parse_sql_file ("$sqldir/20050225-nsssetup.sql") } ;
1265 foreach my $s (@reqlist) {
1267 $query =~ s/TO gforge_nss;/TO ${sys_dbuser}_nss;/ ;
1269 $sth = $dbh->prepare ($query) ;
1275 &update_db_version ($target) ;
1280 &update_with_sql("20050311", "4.1-2") ;
1281 &update_with_sql("20050315", "4.1-3") ;
1282 &update_with_sql("20050325-2", "4.1-4") ;
1284 $version = &get_db_version ;
1286 if (&is_lesser ($version, $target)) {
1287 &debug ("Converting trackers to use their extra fields") ;
1289 $query = "SELECT group_id,group_artifact_id,use_resolution FROM artifact_group_list" ;
1291 $sth = $dbh->prepare ($query) ;
1293 while (@array = $sth->fetchrow_array) {
1294 my $group_id = $array[0] ;
1295 my $gaid = $array[1] ;
1296 my $ur = $array[2] ;
1298 # Ajout du champ Category
1299 my $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
1300 # &debug ($query2) ;
1301 my $sth2 = $dbh->prepare ($query2) ;
1303 my @array2 = $sth2->fetchrow_array ;
1305 my $aefid = $array2[0] ;
1307 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
1308 VALUES ($aefid, $gaid, 'Category', 1)" ;
1309 # &debug ($query2) ;
1310 $sth2 =$dbh->prepare ($query2) ;
1313 $query2 = "SELECT id, category_name FROM artifact_category WHERE group_artifact_id=$gaid" ;
1314 # &debug ($query2) ;
1315 $sth2 = $dbh->prepare ($query2) ;
1318 while (@array2 = $sth2->fetchrow_array) {
1319 my $cat_id = $array2[0] ;
1320 my $catname = $array2[1] ;
1322 if ($catname eq '') { $catname = '[empty]' ; }
1324 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
1325 # &debug ($query3) ;
1326 my $sth3 = $dbh->prepare ($query3) ;
1328 my @array3 = $sth3->fetchrow_array ;
1330 my $efeid = $array3[0] ;
1332 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
1333 VALUES ($efeid, $aefid, ?, 0)" ;
1334 # &debug ($query3) ;
1335 $sth3 =$dbh->prepare ($query3) ;
1336 $sth3->execute ($catname) ;
1339 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
1340 SELECT artifact_id,$efeid,$aefid FROM artifact
1341 WHERE category_id=$cat_id" ;
1342 # &debug ($query3) ;
1343 $sth3 =$dbh->prepare ($query3) ;
1347 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Category'
1348 WHERE old_value='$cat_id' AND field_name='category_id'" ;
1349 # &debug ($query3) ;
1350 $sth3 =$dbh->prepare ($query3) ;
1351 $sth3->execute ($catname) ;
1356 # Ajout du champ Group
1357 $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
1358 # &debug ($query2) ;
1359 $sth2 = $dbh->prepare ($query2) ;
1361 @array2 = $sth2->fetchrow_array ;
1363 $aefid = $array2[0] ;
1365 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
1366 VALUES ($aefid, $gaid, 'Group', 1)" ;
1367 # &debug ($query2) ;
1368 $sth2 =$dbh->prepare ($query2) ;
1371 $query2 = "SELECT id, group_name FROM artifact_group WHERE group_artifact_id=$gaid" ;
1372 # &debug ($query2) ;
1373 $sth2 = $dbh->prepare ($query2) ;
1376 while (@array2 = $sth2->fetchrow_array) {
1377 my $grp_id = $array2[0] ;
1378 my $grpname = $array2[1] ;
1380 if ($grpname eq '') { $grpname = '[empty]' ; }
1382 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
1383 # &debug ($query3) ;
1384 my $sth3 = $dbh->prepare ($query3) ;
1386 my @array3 = $sth3->fetchrow_array ;
1388 my $efeid = $array3[0] ;
1390 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
1391 VALUES ($efeid, $aefid, ?, 0)" ;
1392 # &debug ($query3) ;
1393 $sth3 =$dbh->prepare ($query3) ;
1394 $sth3->execute ($grpname) ;
1397 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
1398 SELECT artifact_id,$efeid,$aefid FROM artifact
1399 WHERE artifact_group_id=$grp_id" ;
1400 # &debug ($query3) ;
1401 $sth3 =$dbh->prepare ($query3) ;
1405 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Group'
1406 WHERE old_value='$grp_id' AND field_name='artifact_group_id'" ;
1407 # &debug ($query3) ;
1408 $sth3 =$dbh->prepare ($query3) ;
1409 $sth3->execute ($grpname) ;
1414 # Ajout du champ Resolution (s'il existe, cf. $ur)
1416 $query2 = "SELECT nextval('artifact_extra_field_list_extra_field_id_seq'::text)" ;
1417 # &debug ($query2) ;
1418 $sth2 = $dbh->prepare ($query2) ;
1420 @array2 = $sth2->fetchrow_array ;
1422 $aefid = $array2[0] ;
1424 $query2 = "INSERT INTO artifact_extra_field_list (extra_field_id, group_artifact_id,field_name,field_type)
1425 VALUES ($aefid, $gaid, 'Resolution', 1)" ;
1426 # &debug ($query2) ;
1427 $sth2 =$dbh->prepare ($query2) ;
1430 $query2 = "SELECT id, resolution_name FROM artifact_resolution" ;
1431 # &debug ($query2) ;
1432 $sth2 = $dbh->prepare ($query2) ;
1435 while (@array2 = $sth2->fetchrow_array) {
1436 my $res_id = $array2[0] ;
1437 my $resname = $array2[1] ;
1439 if ($resname eq '') { $resname = '[empty]' ; }
1441 my $query3 = "SELECT nextval('artifact_extra_field_elements_element_id_seq'::text)" ;
1442 # &debug ($query3) ;
1443 my $sth3 = $dbh->prepare ($query3) ;
1445 my @array3 = $sth3->fetchrow_array ;
1447 my $efeid = $array3[0] ;
1449 $query3 = "INSERT INTO artifact_extra_field_elements (element_id, extra_field_id, element_name, status_id)
1450 VALUES ($efeid, $aefid, ?, 0)" ;
1451 # &debug ($query3) ;
1452 $sth3 =$dbh->prepare ($query3) ;
1453 $sth3->execute ($resname) ;
1456 $query3 = "INSERT INTO artifact_extra_field_data (artifact_id,field_data,extra_field_id)
1457 SELECT artifact_id,$efeid,$aefid FROM artifact
1458 WHERE resolution_id=$res_id and group_artifact_id=$gaid" ;
1459 # &debug ($query3) ;
1460 $sth3 =$dbh->prepare ($query3) ;
1464 $query3 = "UPDATE artifact_history SET old_value=?,field_name='Resolution'
1465 WHERE old_value='$res_id' AND field_name='resolution_id'" ;
1466 # &debug ($query3) ;
1467 $sth3 =$dbh->prepare ($query3) ;
1468 $sth3->execute ($resname) ;
1475 &update_db_version ($target) ;
1480 &update_with_sql("20050325-5", "4.1-6") ;
1481 &update_with_sql("20050605", "4.1-7") ;
1483 $version = &get_db_version ;
1485 if (&is_lesser ($version, $target)) {
1486 &debug ("Creating aliases for the extra fields") ;
1488 $query = "ALTER TABLE artifact_extra_field_list ADD COLUMN alias TEXT" ;
1490 $sth = $dbh->prepare ($query) ;
1494 my %reserved_alias = (
1503 $query = "SELECT field_name, alias, group_artifact_id, extra_field_id FROM artifact_extra_field_list" ;
1505 $sth = $dbh->prepare ($query) ;
1507 while (@array = $sth->fetchrow_array) {
1508 my $name = $array[0] ;
1509 my $alias = $array[1] ;
1510 my $gaid = $array[2] ;
1511 my $efid = $array[3] ;
1514 my $newalias = lc $name ;
1515 $newalias =~ s/\s/_/g ;
1516 $newalias =~ s/[^_a-z]//g ;
1518 if ($newalias ne "") {
1519 if ($reserved_alias{$newalias}) {
1520 $newalias = "extra_" . $newalias ;
1527 $candidate = $newalias ;
1528 $candidate .= $count if ($count > 0) ;
1529 my $query2 = "SELECT count(*) FROM artifact_extra_field_list WHERE group_artifact_id=$gaid AND LOWER(alias)='$candidate' AND extra_field_id <> $efid" ;
1530 # &debug ($query2) ;
1531 my $sth2 =$dbh->prepare ($query2) ;
1533 my @array2 = $sth2->fetchrow_array ;
1534 if ($array2[0] == 0) {
1541 } until ($conflict == 0) ;
1543 my $query2 = "UPDATE artifact_extra_field_list SET alias='$candidate' WHERE extra_field_id=$efid" ;
1544 # &debug ($query2) ;
1545 my $sth2 =$dbh->prepare ($query2) ;
1554 &update_db_version ($target) ;
1559 &update_with_sql("20050628", "4.1-9") ;
1560 &update_with_sql("20050711", "4.5-1") ;
1561 &update_with_sql("20050906","4.5-2");
1562 &update_with_sql("20050804-1","4.5-3");
1564 $version = &get_db_version ;
1566 if (&is_lesser ($version, $target)) {
1567 &debug ("Updating document sizes") ;
1569 $query = "SELECT docid, data FROM doc_data" ;
1571 $sth = $dbh->prepare ($query) ;
1573 while (@array = $sth->fetchrow_array) {
1574 my $docid = $array[0] ;
1575 my $b64data = $array[1] ;
1576 my $data = decode_base64 ($b64data) ;
1577 my $size = length ($data) ;
1579 my $query2 = "UPDATE doc_data SET filesize=$size WHERE docid=$docid" ;
1580 # &debug ($query2) ;
1581 my $sth2 =$dbh->prepare ($query2) ;
1587 &update_db_version ($target) ;
1592 $version = &get_db_version ;
1593 $target = "4.5.14-3" ;
1594 if (&is_lesser ($version, $target)) {
1595 &debug ("Setting up time tracking") ;
1597 if (&table_exists ($dbh, "rep_time_category")) {
1598 &debug ("...already set up.") ;
1600 &drop_table_if_exists ($dbh, "rep_time_category") ;
1601 &drop_sequence_if_exists ($dbh, "rep_time_category_time_code_seq") ;
1602 &drop_table_if_exists ($dbh, "rep_time_tracking") ;
1603 &drop_table_if_exists ($dbh, "rep_users_added_daily") ;
1604 &drop_table_if_exists ($dbh, "rep_users_added_weekly") ;
1605 &drop_table_if_exists ($dbh, "rep_users_added_monthly") ;
1606 &drop_table_if_exists ($dbh, "rep_users_cum_daily") ;
1607 &drop_table_if_exists ($dbh, "rep_users_cum_weekly") ;
1608 &drop_table_if_exists ($dbh, "rep_users_cum_monthly") ;
1609 &drop_table_if_exists ($dbh, "rep_groups_added_daily") ;
1610 &drop_table_if_exists ($dbh, "rep_groups_added_weekly") ;
1611 &drop_table_if_exists ($dbh, "rep_groups_added_monthly") ;
1612 &drop_table_if_exists ($dbh, "rep_groups_cum_daily") ;
1613 &drop_table_if_exists ($dbh, "rep_groups_cum_weekly") ;
1614 &drop_table_if_exists ($dbh, "rep_groups_cum_monthly") ;
1615 &drop_view_if_exists ($dbh, "rep_group_act_oa_vw") ;
1616 &drop_view_if_exists ($dbh, "rep_user_act_oa_vw") ;
1617 &drop_view_if_exists ($dbh, "rep_site_act_daily_vw") ;
1618 &drop_view_if_exists ($dbh, "rep_site_act_weekly_vw") ;
1619 &drop_view_if_exists ($dbh, "rep_site_act_monthly_vw") ;
1620 &drop_table_if_exists ($dbh, "rep_user_act_daily") ;
1621 &drop_table_if_exists ($dbh, "rep_user_act_weekly") ;
1622 &drop_table_if_exists ($dbh, "rep_user_act_monthly") ;
1623 &drop_table_if_exists ($dbh, "rep_group_act_daily") ;
1624 &drop_index_if_exists ($dbh, "repgroupactdaily_daily") ;
1625 &drop_table_if_exists ($dbh, "rep_group_act_weekly") ;
1626 &drop_index_if_exists ($dbh, "repgroupactweekly_weekly") ;
1627 &drop_table_if_exists ($dbh, "rep_group_act_monthly") ;
1628 &drop_index_if_exists ($dbh, "repgroupactmonthly_monthly") ;
1630 @reqlist = @{ &parse_sql_file ("$sqldir/timetracking-init.sql") } ;
1631 foreach my $s (@reqlist) {
1634 $sth = $dbh->prepare ($query) ;
1641 &update_db_version ($target) ;
1646 # I had to increase versions from 4.5.14 to 4.5.15
1647 # The activity view is created by 20060216-nocommit
1648 # If the view doesn't exists apply
1649 if (! &view_exists ($dbh, 'activity_vw')) {
1650 &update_with_sql("20050812","4.5.15-10merge");
1651 &update_with_sql("20050822-2","4.5.15-11merge");
1652 &update_with_sql("20050823","4.5.15-12merge");
1653 &update_with_sql("20050824","4.5.15-13merge");
1654 &update_with_sql("20050831","4.5.15-14merge");
1656 &update_with_sql("20060113","4.5.15-15");
1657 &update_with_sql("20060214","4.5.15-16");
1658 &update_with_sql("20060216-2-debian-nocommit","4.5.15-17");
1661 $version = &get_db_version ;
1662 $target = "4.5.15-21" ;
1663 if (&is_lesser ($version, $target)) {
1664 &debug ("Fixing past mistakes in role naming") ;
1666 my $defaultroles_restricted = {
1667 'Admin' => { 'projectadmin'=>'A', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'trackeradmin'=>'2', 'pmadmin'=>'2' },
1668 'Senior Developer' => { 'projectadmin'=>'0', 'frs'=>'1', 'scm'=>'1', 'docman'=>'1', 'forumadmin'=>'2', 'trackeradmin'=>'2', 'pmadmin'=>'2' },
1669 'Junior Developer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'1', 'docman'=>'0', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' },
1670 'Doc Writer' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' },
1671 'Support Tech' => { 'projectadmin'=>'0', 'frs'=>'0', 'scm'=>'0', 'docman'=>'1', 'forumadmin'=>'0', 'trackeradmin'=>'0', 'pmadmin'=>'0' }
1674 foreach my $drname (keys %{$defaultroles_restricted}) {
1675 $query = "UPDATE role SET role_name='$drname' WHERE role_id IN (SELECT role.role_id" ;
1680 foreach my $setting (keys %{$defaultroles_restricted->{$drname}}) {
1681 $value = $defaultroles_restricted->{$drname}->{$setting} ;
1682 $from .= ", role_setting rs_$setting" ;
1683 $where .= "role.role_id = rs_$setting.role_id AND rs_$setting.section_name='$setting' AND " ;
1684 $where .= "rs_$setting.value = '$value' \nAND " ;
1686 $query .= "\nFROM role$from" ;
1687 $query .= "\nWHERE $where role.role_name='rname')";
1688 push @reqlist, $query;
1691 foreach my $s (@reqlist) {
1694 $sth = $dbh->prepare ($query) ;
1700 &update_db_version ($target) ;
1705 &update_with_sql("20051103_transiciel_motscle_document","4.6-1");
1707 &update_with_sql("20070924-forum-perm","4.6.99-1");
1708 &update_with_sql("20070924-project-perm","4.6.99-2");
1709 &update_with_sql("20070924-artifact-perm","4.6.99-3");
1711 $version = &get_db_version ;
1712 $target = "4.6.99-4" ;
1713 if (&is_lesser ($version, $target)) {
1714 &debug ("Dropping old translations table") ;
1716 &drop_table_if_exists ($dbh, "tmp_lang") ;
1718 &update_db_version ($target) ;
1723 $version = &get_db_version ;
1724 $target = "4.6.99-5" ;
1725 if (&is_lesser ($version, $target)) {
1726 &debug ("Updating available themes") ;
1728 my @obsolete_themes = qw/ classic debian savannah
1729 savannah_codex savannah_forest
1730 savannah_reverse savannah_sad
1731 savannah_savannah savannah_slashd
1733 savannah_transparent savannah_water
1734 savannah_www.gnu.org
1735 savannah_darkslate forged kde
1738 my $otids = join (',', map { "'$_'" } @obsolete_themes) ;
1740 $query = "UPDATE users SET theme_id=1 WHERE theme_id IN
1741 (SELECT theme_id FROM themes WHERE dirname IN ($otids))" ;
1742 push @reqlist, $query;
1744 $query = "DELETE FROM themes WHERE dirname IN ($otids)" ;
1745 push @reqlist, $query;
1748 'gforge-classic' => 'GForge classic',
1749 'gforge-simple-theme' => 'GForge simple',
1750 'lite' => 'GForge lite'
1753 foreach my $dir (sort keys %new_themes) {
1754 $query = "INSERT INTO themes (dirname, fullname) VALUES ('$dir', '$new_themes{$dir}')" ;
1755 push @reqlist, $query;
1758 foreach my $s (@reqlist) {
1761 $sth = $dbh->prepare ($query) ;
1767 &update_db_version ($target) ;
1772 $version = &get_db_version ;
1773 $target = "4.6.99-6" ;
1774 if (&is_lesser ($version, $target)) {
1775 &debug ("DROP UNIQUE INDEX never UNIQUE") ;
1776 &drop_index_if_exists ($dbh, "statsaggsitebygrp_oid") ;
1777 &drop_index_if_exists ($dbh, "statsprojectmetric_oid") ;
1778 &drop_index_if_exists ($dbh, "statsagglogobygrp_oid") ;
1779 &drop_index_if_exists ($dbh, "statsprojectdevelop_oid") ;
1780 &drop_index_if_exists ($dbh, "statssubdpages_oid") ;
1781 &drop_index_if_exists ($dbh, "statscvsgrp_oid") ;
1782 &drop_index_if_exists ($dbh, "statsproject_oid") ;
1783 &drop_index_if_exists ($dbh, "statssite_oid") ;
1784 &drop_index_if_exists ($dbh, "statssitepgsbyday_oid") ;
1785 &update_db_version ($target) ;
1790 &update_with_sql("20090327_create_table_project_tags","4.6.99-7");
1791 &update_with_sql("20090402-add-projecttags-constraints","4.7.99-1");
1792 &update_with_sql("20090402-forum-attachment-types","4.7.99-2");
1794 &update_with_sql("20090507-add_artifact_workflow","4.8.99-1");
1795 &update_with_sql("20090507-add_element_pos","4.8.99-2");
1796 &update_with_sql("20090507-add_project_query","4.8.99-3");
1797 &update_with_sql("20090507-browse_list","4.8.99-4");
1799 $version = &get_db_version ;
1800 $target = "4.8.99-5" ;
1801 if (&is_lesser ($version, $target)) {
1802 &debug ("Initialising tracker workflows") ;
1805 $query = "SELECT group_id, artifact_group_list.group_artifact_id, element_id, artifact_extra_field_elements.extra_field_id
1806 FROM artifact_extra_field_list, artifact_extra_field_elements, artifact_group_list
1807 WHERE artifact_extra_field_list.extra_field_id=artifact_extra_field_elements.extra_field_id
1808 AND artifact_group_list.group_artifact_id = artifact_extra_field_list.group_artifact_id
1811 $sth = $dbh->prepare ($query) ;
1813 while (@array = $sth->fetchrow_array) {
1814 my $gid = $array[0];
1815 my $gaid = $array[1];
1816 my $eid = $array[2];
1818 my $query2 = "SELECT extra_field_id
1819 FROM artifact_extra_field_list
1820 WHERE group_artifact_id=$gaid
1822 ORDER BY field_type ASC" ;
1823 my $sth2 = $dbh->prepare ($query2) ;
1826 if (my @array2 = $sth2->fetchrow_array) {
1827 my $efid = $array2[0];
1830 $query2 = "SELECT element_id,element_name,status_id
1831 FROM artifact_extra_field_elements
1832 WHERE extra_field_id = $efid
1833 ORDER BY element_pos ASC, element_id ASC" ;
1835 $sth2 = $dbh->prepare ($query2) ;
1837 while (@array2 = $sth2->fetchrow_array) {
1838 my $eid2 = $array2[0];
1839 if ($eid2 != $eid) {
1840 my $query3 = "INSERT INTO artifact_workflow_event
1841 (group_artifact_id, field_id, from_value_id, to_value_id)
1842 VALUES ($gaid, $efid, $eid, $eid2)";
1844 my $sth3 = $dbh->prepare ($query3) ;
1847 $query3 = "INSERT INTO artifact_workflow_event
1848 (group_artifact_id, field_id, from_value_id, to_value_id)
1849 VALUES ($gaid, $efid, $eid2, $eid)";
1851 $sth3 = $dbh->prepare ($query3) ;
1857 my $query3 = "INSERT INTO artifact_workflow_event
1858 (group_artifact_id, field_id, from_value_id, to_value_id)
1859 VALUES ($gaid, $efid, 100, $eid)";
1861 my $sth3 = $dbh->prepare ($query3) ;
1869 &update_db_version ($target) ;
1874 &update_with_sql("20100308-forum-attachment-types","4.8.99-6");
1876 $version = &get_db_version ;
1877 $target = "4.8.99-7" ;
1878 if (&is_lesser ($version, $target)) {
1879 &debug ("Granting read access permissions to NSS and MTA") ;
1881 @reqlist = ( "GRANT SELECT ON nss_passwd TO ${sys_dbuser}_nss",
1882 "GRANT SELECT ON nss_groups TO ${sys_dbuser}_nss",
1883 "GRANT SELECT ON nss_usergroups TO ${sys_dbuser}_nss",
1884 "GRANT SELECT ON mta_users TO ${sys_dbuser}_mta",
1885 "GRANT SELECT ON mta_lists TO ${sys_dbuser}_mta",
1887 foreach my $s (@reqlist) {
1890 $sth = $dbh->prepare ($query) ;
1896 &update_db_version ($target) ;
1901 &update_with_sql("20100330-add-system-event","5.0.0-1");
1902 &update_with_sql("20100331-alter-system-event","5.0.0-2");
1903 &update_with_sql("20100505-alter-user-preference","5.0.1-1");
1904 &update_with_sql("20100506-add-widgets","5.0.1-2");
1905 &update_with_sql("20100517-add-project-widgets","5.0.1-3");
1906 &update_with_sql("20100518-pfo-rbac","5.0.1-4");
1907 &update_with_sql("20100524-pfo-rbac","5.0.1-5");
1908 &update_with_sql("20100606-clean-perm-views","5.0.1-6");
1909 &update_with_sql("20100610-pfo-rbac","5.0.1-7");
1910 &update_with_sql("20100730-docman","5.0.1-8");
1911 &update_with_sql("20100924-theme","5.0.1-9");
1912 &update_with_sql("20100926-pfo-rbac","5.0.1-10");
1913 &update_with_sql("20100927-pfo-rbac","5.0.1-11");
1914 &update_with_sql("20101012-docman-webdav","5.0.51-1");
1915 &update_with_sql("20101021-pfo-rbac","5.0.51-2");
1916 &update_with_sql("20101025-ipv6","5.0.51-3");
1918 $version = &get_db_version ;
1919 $target = "5.0.51-4" ;
1920 if (&is_lesser ($version, $target)) {
1921 &debug ("Granting read access permissions to NSS and MTA") ;
1923 @reqlist = ( "GRANT SELECT ON nss_passwd TO ${sys_dbuser}_nss",
1924 "GRANT SELECT ON nss_groups TO ${sys_dbuser}_nss",
1925 "GRANT SELECT ON nss_usergroups TO ${sys_dbuser}_nss",
1926 "GRANT SELECT ON mta_users TO ${sys_dbuser}_mta",
1927 "GRANT SELECT ON mta_lists TO ${sys_dbuser}_mta",
1929 foreach my $s (@reqlist) {
1932 $sth = $dbh->prepare ($query) ;
1938 &update_db_version ($target) ;
1943 &update_with_sql("20101027-docman-lock","5.0.51-5");
1944 &update_with_sql("20101105-pfo-rbac","5.0.51-6");
1945 &update_with_sql("20101029-docman-monitoring","5.0.51-7");
1946 &update_with_sql("20100402_add_query_options","5.0.51-8");
1947 &update_with_sql("20101024-docman-createonline","5.0.51-9");
1948 &update_with_sql("20101213-project-template","5.0.51-10");
1949 &update_with_sql("20110110-pw-size","5.0.51-11");
1950 &update_with_sql("20110405-forum_attachment_fix-bug284","5.0.51-12");
1951 &update_with_sql("20110408-anonymous-read-news","5.0.51-13");
1952 &update_with_sql("20110414-move-news-forums-to-own-project","5.0.51-14");
1953 &update_with_sql("20110701-gforge-5.1","5.1-1");
1954 &update_with_sql("20110728-fix-mta-lists-view","5.1-2");
1955 &update_with_sql("FTI","5.1-3");
1956 &update_with_sql("FTI-20050315","5.1-4");
1957 &update_with_sql("FTI-20050401","5.1-5");
1958 &update_with_sql("FTI-20050530","5.1-6");
1959 &update_with_sql("FTI-20060130","5.1-7");
1960 &update_with_sql("FTI-20061025","5.1-8");
1961 &update_with_sql("20111007-fti-aggregate-functions","5.1-9");
1962 &update_with_sql("20111007-add-fti-for-task-messages","5.1-10");
1963 &update_with_sql("20111009-string-aggregate-function","5.1-11");
1964 &update_with_sql("20120321-add-news-in-activity_vw","5.1-12");
1965 &update_with_sql("20120903-no-unix-account-for-deleted-users", "5.1-13");
1966 &update_with_sql("20110118-user-tooltips","5.1.51-1");
1967 &update_with_sql("20110211-docman-activityvw","5.1.51-2");
1968 &update_with_sql("20110222-pluginify-auth","5.1.51-3");
1970 &update_with_sql("20110310-docman-trashdir","5.1.51-4");
1971 &update_with_sql("20110405-forum_attachment_fix-bug284","5.1.51-5");
1972 &update_with_sql("20110408-anonymous-read-news","5.1.51-6");
1973 &update_with_sql("20110728-fix-mta-lists-view","5.1.51-7");
1974 &update_with_sql("20110824-plugin-projects-hierarchy-rename","5.1.51-8");
1975 &update_with_sql("20111002-docman-doc_groups-createdate-updatedate","5.1.51-9");
1977 &update_with_sql("schema-convergence-from-debian","5.1.51-10");
1979 $version = &get_db_version ;
1980 $target = "5.1.51-11" ;
1981 if (&is_lesser ($version, $target)) {
1982 if (!&table_exists ($dbh, 'plugin_cvstracker_data_artifact')) {
1983 &debug ("Conditional part of the database schema convergence") ;
1986 "CREATE SEQUENCE plugin_cvstracker_artifact_seq START WITH 1 INCREMENT BY 1 NO MINVALUE MAXVALUE 2147483647 CACHE 1",
1987 "CREATE SEQUENCE plugin_cvstracker_master_seq START WITH 1 INCREMENT BY 1 NO MINVALUE MAXVALUE 2147483647 CACHE 1",
1989 "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 )",
1990 "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 )",
1992 "CREATE INDEX plugin_cvstracker_group_artifact_id ON plugin_cvstracker_data_artifact USING btree (group_artifact_id)",
1994 "ALTER TABLE ONLY plugin_cvstracker_data_artifact ADD CONSTRAINT plugin_cvstracker_artifact_pkey PRIMARY KEY (id)",
1995 'ALTER TABLE ONLY plugin_cvstracker_data_master ADD CONSTRAINT "$1" FOREIGN KEY (holder_id) REFERENCES plugin_cvstracker_data_artifact(id)',
1996 'ALTER TABLE ONLY plugin_cvstracker_data_master ADD CONSTRAINT "$2" FOREIGN KEY (author) REFERENCES users(user_name)',
1997 "ALTER TABLE ONLY plugin_cvstracker_data_master ADD CONSTRAINT plugin_cvstracker_master_pkey PRIMARY KEY (id)",
1999 foreach my $s (@reqlist) {
2002 $sth = $dbh->prepare ($query) ;
2009 &update_db_version ($target) ;
2014 $version = &get_db_version ;
2015 $target = "5.1.51-12" ;
2016 if (&is_lesser ($version, $target)) {
2017 &debug ("Creating database_startpoint and database_changes") ;
2019 @reqlist = ( "CREATE TABLE database_changes (filename text)",
2020 "CREATE TABLE database_startpoint (db_version character varying(10), db_start_date integer)",
2021 "INSERT INTO database_startpoint (db_version, db_start_date) VALUES ('5.1.51', 20111017)",
2023 foreach my $s (@reqlist) {
2026 $sth = $dbh->prepare ($query) ;
2032 &update_db_version ($target) ;
2037 $version = &get_db_version ;
2038 if ($version eq "5.1.51-12") {
2039 &debug ("Dropping debian_meta_data table.") ;
2040 &drop_table_if_exists ($dbh, 'debian_meta_data') ;
2045 ########################### INSERT HERE #################################
2047 # There should be a commit at the end of every block above.
2048 # If there is not, then it might be symptomatic of a problem.
2049 # For safety, we roll back.
2054 warn "Transaction aborted because $@" ;
2055 &debug ("Transaction aborted because $@") ;
2056 &debug ("Last SQL query was:\n$query\n(end of query)") ;
2058 my $version = &get_db_version ;
2060 &debug ("Your database schema is at version $version") ;
2062 &debug ("Couldn't get your database schema version.") ;
2064 &debug ("Please report this bug on the Debian bug-tracking system.") ;
2065 &debug ("Please include the previous messages as well to help debugging.") ;
2066 &debug ("You should not worry too much about this,") ;
2067 &debug ("your DB is still in a consistent state and should be usable.") ;
2074 sub get_pg_version () {
2076 if (-x '/usr/bin/pg_lsclusters' ) {
2077 $command = q(/usr/bin/pg_lsclusters | grep 5432 | grep online | cut -d' ' -f1) ;
2079 $command = q(dpkg -s postgresql | awk '/^Version: / { print $2 }') ;
2081 my $version = qx($command) ;
2086 sub create_metadata_table ( $ ) {
2087 my $v = shift || "2.5-7+just+before+8" ;
2089 my ($query, $sth, @array) ;
2091 # Let's create this table if we have it not
2092 if (! &table_exists ($dbh, 'debian_meta_data')) {
2093 &debug ("Creating debian_meta_data table.") ;
2094 $query = "CREATE TABLE debian_meta_data (key varchar primary key, value text not null)" ;
2096 $sth = $dbh->prepare ($query) ;
2101 $query = "SELECT count(*) FROM debian_meta_data WHERE key = 'db-version'";
2103 $sth = $dbh->prepare ($query) ;
2105 @array = $sth->fetchrow_array () ;
2108 # Empty table? We'll have to fill it up a bit
2110 if ($array [0] == 0) {
2111 &debug ("Inserting first data into debian_meta_data table.") ;
2112 $query = "INSERT INTO debian_meta_data (key, value) VALUES ('db-version', '$v')" ;
2114 $sth = $dbh->prepare ($query) ;
2120 sub update_db_version ( $ ) {
2121 my $v = shift or die "Not enough arguments" ;
2123 $query = "UPDATE debian_meta_data SET value = '$v' WHERE key = 'db-version'" ;
2124 my $sth = $dbh->prepare ($query) ;
2129 sub get_db_version () {
2130 $query = "SELECT value FROM debian_meta_data WHERE key = 'db-version'" ;
2132 my $sth = $dbh->prepare ($query) ;
2134 my @array = $sth->fetchrow_array () ;
2137 my $version = $array [0] ;
2142 sub update_with_sql ( $$ ) {
2143 my $sqlfile = shift or die "Not enough arguments" ;
2144 my $target = shift or die "Not enough arguments" ;
2145 $sqlfile =~ s/\.sql$//;
2146 my $version = &get_db_version ;
2147 if (&is_lesser ($version, $target)) {
2148 &debug ("Upgrading database with $sqlfile.sql") ;
2150 @reqlist = @{ &parse_sql_file ("$sqldir/$sqlfile.sql") } ;
2151 foreach my $s (@reqlist) {
2154 my $sth = $dbh->prepare ($query) ;
2160 &update_db_version ($target) ;