From 2259825299133759a5ba0febf2d88bda8539de7a Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Thu, 7 Dec 2006 10:01:43 +0000 Subject: [PATCH] Datenbankupgrades: Es gibt einige Probleme, wenn die CRM installiert ist, weil dann einige Spalten, die in den SQL-Scripten angelegt werden sollten, bereits existieren. Dieses wird nun ueber Perl-Upgradescripte abgefangen. --- .../Pg-upgrade-2.2.0.19-2.2.0.20.pl | 39 +++++++++++++++++++ .../Pg-upgrade-2.2.0.19-2.2.0.20.sql | 9 ----- .../Pg-upgrade-2.2.0.20-2.2.0.21.pl | 35 +++++++++++++++++ .../Pg-upgrade-2.2.0.20-2.2.0.21.sql | 3 -- 4 files changed, 74 insertions(+), 12 deletions(-) create mode 100644 sql/Pg-upgrade/Pg-upgrade-2.2.0.19-2.2.0.20.pl delete mode 100644 sql/Pg-upgrade/Pg-upgrade-2.2.0.19-2.2.0.20.sql create mode 100644 sql/Pg-upgrade/Pg-upgrade-2.2.0.20-2.2.0.21.pl delete mode 100644 sql/Pg-upgrade/Pg-upgrade-2.2.0.20-2.2.0.21.sql diff --git a/sql/Pg-upgrade/Pg-upgrade-2.2.0.19-2.2.0.20.pl b/sql/Pg-upgrade/Pg-upgrade-2.2.0.19-2.2.0.20.pl new file mode 100644 index 000000000..352a91e3e --- /dev/null +++ b/sql/Pg-upgrade/Pg-upgrade-2.2.0.19-2.2.0.20.pl @@ -0,0 +1,39 @@ +#!/usr/bin/perl + +die("This script cannot be run from the command line.") unless ($main::form); + +sub mydberror { + my ($msg) = @_; + die($dbup_locale->text("Database update error:") . + "
$msg
" . $DBI::errstr); +} + +sub mydoquery { + my ($query, $allow_fail) = @_; + + if (!$dbh->do($query)) { + mydberror($query) unless ($allow_fail); + $dbh->rollback(); + $dbh->begin_work(); + } +} + +sub update_contacts_add_columns { + # The following columns might already be present due to an + # existing CRM installation: + mydoquery("ALTER TABLE contacts ADD COLUMN cp_fax text", 1); + + # The following columns should not exist: + mydoquery("ALTER TABLE contacts ADD COLUMN cp_mobile1 text"); + mydoquery("ALTER TABLE contacts ADD COLUMN cp_mobile2 text"); + mydoquery("ALTER TABLE contacts ADD COLUMN cp_satphone text"); + mydoquery("ALTER TABLE contacts ADD COLUMN cp_satfax text"); + mydoquery("ALTER TABLE contacts ADD COLUMN cp_project text"); + mydoquery("ALTER TABLE contacts ADD COLUMN cp_privatphone text"); + mydoquery("ALTER TABLE contacts ADD COLUMN cp_privatemail text"); + mydoquery("ALTER TABLE contacts ADD COLUMN cp_birthday text"); + + return 1; +} + +return update_contacts_add_columns(); diff --git a/sql/Pg-upgrade/Pg-upgrade-2.2.0.19-2.2.0.20.sql b/sql/Pg-upgrade/Pg-upgrade-2.2.0.19-2.2.0.20.sql deleted file mode 100644 index e1951912d..000000000 --- a/sql/Pg-upgrade/Pg-upgrade-2.2.0.19-2.2.0.20.sql +++ /dev/null @@ -1,9 +0,0 @@ -alter table contacts add column cp_fax text; -alter table contacts add column cp_mobile1 text; -alter table contacts add column cp_mobile2 text; -alter table contacts add column cp_satphone text; -alter table contacts add column cp_satfax text; -alter table contacts add column cp_project text; -alter table contacts add column cp_privatphone text; -alter table contacts add column cp_privatemail text; -alter table contacts add column cp_birthday text; diff --git a/sql/Pg-upgrade/Pg-upgrade-2.2.0.20-2.2.0.21.pl b/sql/Pg-upgrade/Pg-upgrade-2.2.0.20-2.2.0.21.pl new file mode 100644 index 000000000..4fb5d91f1 --- /dev/null +++ b/sql/Pg-upgrade/Pg-upgrade-2.2.0.20-2.2.0.21.pl @@ -0,0 +1,35 @@ +#!/usr/bin/perl + +die("This script cannot be run from the command line.") unless ($main::form); + +sub mydberror { + my ($msg) = @_; + die($dbup_locale->text("Database update error:") . + "
$msg
" . $DBI::errstr); +} + +sub mydoquery { + my ($query, $allow_fail) = @_; + + if (!$dbh->do($query)) { + mydberror($query) unless ($allow_fail); + $dbh->rollback(); + $dbh->begin_work(); + } +} + +sub update_contacts_handle_department { + $dbh->selectrow_array("SELECT cp_abteilung FROM contacts LIMIT 1"); + if ($dbh->err) { + $dbh->rollback(); + $dbh->begin_work(); + mydoquery("ALTER TABLE contacts ADD COLUMN cp_abteilung text"); + mydoquery("UPDATE contacts SET cp_abteilung = cp_department"); + } + + mydoquery("ALTER TABLE contacts DROP COLUMN cp_department", 1); + + return 1; +} + +return update_contacts_handle_department(); diff --git a/sql/Pg-upgrade/Pg-upgrade-2.2.0.20-2.2.0.21.sql b/sql/Pg-upgrade/Pg-upgrade-2.2.0.20-2.2.0.21.sql deleted file mode 100644 index f87c8219d..000000000 --- a/sql/Pg-upgrade/Pg-upgrade-2.2.0.20-2.2.0.21.sql +++ /dev/null @@ -1,3 +0,0 @@ -alter table contacts add column cp_abteilung text; -update contacts set cp_abteilung=cp_department; -alter table contacts drop column cp_department; -- 2.20.1