From 946324538fb1706eb9f3ff7f98ecad8e27710c95 Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Sun, 20 Sep 2009 00:43:46 +0200 Subject: [PATCH] migration from cp_greeting to cp_gender this commit replaces commit e09347c89ca119213c4d8ba43083653cda793399 which introduced gender.sql In database table contacts cp_greeting is removed and instead cp_gender is introduced For print variables cp_greeting is generated from cp_gender and generic_translations During migration user gets a chance to * set gender of contacts where gender couldn't be easily inferred from cp_greeting * set default greeting (Herr/Frau) for male and female * transfer any data in cp_greeting that is not gender-related to cp_title gender.sql is deleted, as there was a risk of losing data in cp_title Migration code was originally copied from warehouse code --- sql/Pg-upgrade2/cp_greeting_migration.pl | 170 ++++++++++++++++++ sql/Pg-upgrade2/gender.sql | 14 -- .../dbupgrade/cp_greeting_update_form_de.html | 36 ++++ .../cp_greeting_update_form_master.html | 36 ++++ 4 files changed, 242 insertions(+), 14 deletions(-) create mode 100644 sql/Pg-upgrade2/cp_greeting_migration.pl delete mode 100644 sql/Pg-upgrade2/gender.sql create mode 100644 templates/webpages/dbupgrade/cp_greeting_update_form_de.html create mode 100644 templates/webpages/dbupgrade/cp_greeting_update_form_master.html diff --git a/sql/Pg-upgrade2/cp_greeting_migration.pl b/sql/Pg-upgrade2/cp_greeting_migration.pl new file mode 100644 index 000000000..1ee6abd61 --- /dev/null +++ b/sql/Pg-upgrade2/cp_greeting_migration.pl @@ -0,0 +1,170 @@ +# @tag: cp_greeting_migration +# @description: Migration of cp_greeting to cp_gender +# @depends: generic_translations + +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 do_query { + my ($query, $may_fail) = @_; + + if (!$dbh->do($query)) { + mydberror($query) unless ($may_fail); + $dbh->rollback(); + $dbh->begin_work(); + } +} + + +sub query_result { + + # list of all entries where cp_greeting is empty, meaning can't determine gender from parsing Herr/Frau/... + # this assumes cp_greeting still exists, i.e. gender.sql was not run yet + my $gender_table; + + my $sql2 = "select cp_id,cp_givenname,cp_name,cp_title,cp_greeting from contacts where not (cp_greeting ILIKE '%frau%' OR cp_greeting ILIKE '%herr%' or cp_greeting ILIKE '%mrs.%' or cp_greeting ILIKE '%miss%') "; + my $sth2 = $dbh->prepare($sql2) or die $dbh->errstr(); + $sth2->execute() or die $dbh->errstr(); + + my $i = 1; + $gender_table .= ''; + $gender_table .= "\n"; + + while (my $row = $sth2->fetchrow_hashref()) { + if ( main::form->{"gender_$i"} eq "f" ) { + $mchecked = ""; + $fchecked = "checked"; + } else { + $mchecked = "checked"; + $fchecked = ""; + }; + + $gender_table .= "{cp_id}\"> \n"; + $i++; + }; + + $gender_table .= ""; + $gender_table .= "
cp_givennamecp_namecp_titlecp_greetingmale/female
$row->{cp_givenname} $row->{cp_name} $row->{cp_title} $row->{cp_greeting}
"; + + $main::form->{gender_table} = $gender_table; + + my $title_table; + + my $sql3 = "select cp_id,cp_givenname,cp_name,cp_title,cp_greeting from contacts where not ( (cp_greeting ILIKE '%frau%' OR cp_greeting ILIKE '%herr%' or cp_greeting ILIKE '%mrs.%' or cp_greeting ILIKE '%miss%')) and not (cp_greeting like ''); "; + + my $sth3 = $dbh->prepare($sql3) or die $dbh->errstr(); + $sth3->execute() or die $dbh->errstr(); + + $title_table = ''; + + my $j = 1; + while (my $row = $sth3->fetchrow_hashref()) { +# Vorschlagsfeld fuer neuen Titel mit Werten von cp_greeting und cp_title vorbelegen + my $value = "$row->{cp_greeting}"; + $value .= " " if $row->{cp_greeting}; + $value .= "$row->{cp_title}"; + + $title_table .= "\n"; + $j++; + }; + + $title_table .= ""; + $title_table .= "
cp_givennamecp_namecp_titlecp_greetingcp_title new
{cp_id}> $row->{cp_givenname} $row->{cp_name}$row->{cp_title} $row->{cp_greeting}
"; + $main::form->{title_table} = $title_table; + +}; + + + +sub print_question { + query_result(); + # parse html form in /templates/webpages/dbupgrade/cp_greeting_update_form + print $main::form->parse_html_template("dbupgrade/cp_greeting_update_form"); +} + +sub do_update { + # main function + + # first of all check if gender.sql was already run and thus cp_gender exists + # if it exists there is no need for this update anymore, so return + # without doing anything + + my $column_exists = 1; + if (!$dbh->do("SELECT cp_gender FROM contacts LIMIT 1")) { + $dbh->rollback(); + $dbh->begin_work(); + $column_exists = 0; + } + return 1 if $column_exists; + + + if (!$main::form->{do_migrate}) { + # case 1: first call of page + set_default_greetings(); + print_question(); + return 2; + } else { + # case 2: submit button was pressed, hidden field do_migrate was set + migrate_data(); + }; + +return 1; + +} + +sub migrate_data { + + my $sqlcode = <{number_of_gender_entries}; $i++ ) { + next unless $main::form->{"cp_id_$i"}; + if ( $main::form->{"gender_$i"} eq "f" ) { + $sqlcode .= "UPDATE contacts SET cp_gender = \'f\' WHERE cp_id = $main::form->{\"cp_id_$i\"};\n"; + }; + }; + + for (my $i = 1; $i <= $main::form->{number_of_title_entries}; $i++ ) { + next unless $main::form->{"cp_id_title_$i"} and $main::form->{"cp_id_$i"}; + $sqlcode .= "UPDATE contacts SET cp_title = \'$main::form->{\"cp_name_$i\"}\' WHERE cp_id = $main::form->{\"cp_id_$i\"};\n"; + }; + $sqlcode .= "ALTER TABLE contacts DROP COLUMN cp_greeting;"; + + # insert chosen default values + $sqlcode .= "INSERT INTO generic_translations (translation_type, translation) VALUES ('greetings::male','$main::form->{default_male}');"; + $sqlcode .= "INSERT INTO generic_translations (translation_type, translation) VALUES ('greetings::female','$main::form->{default_female}');"; + + my $query = $sqlcode; + do_query($query); +}; + + + +sub set_default_greetings { + # add html input boxes to template so user can specify default greetings + + my $default_male = "Herr"; + my $default_female = "Frau"; + + my $default_greeting_text_male = "
"; + my $default_greeting_text_female = "
"; + $main::form->{default_greeting_text_male} = $default_greeting_text_male; + $main::form->{default_greeting_text_female} = $default_greeting_text_female; +}; + + +return do_update(); + diff --git a/sql/Pg-upgrade2/gender.sql b/sql/Pg-upgrade2/gender.sql deleted file mode 100644 index 2f92f5387..000000000 --- a/sql/Pg-upgrade2/gender.sql +++ /dev/null @@ -1,14 +0,0 @@ --- @tag: gender --- @description: Feld "Geschlecht" zu Kontaktdaten hinzufuegen, cp_greeting entferne --- @depends: release_2_6_0 - -ALTER TABLE contacts ADD COLUMN cp_gender char(1); -UPDATE contacts SET cp_gender = 'm'; -UPDATE contacts SET cp_gender = 'f' - WHERE (cp_greeting ILIKE '%frau%') - OR (cp_greeting ILIKE '%mrs.%') - OR (cp_greeting ILIKE '%miss%'); - -UPDATE contacts SET cp_title = cp_greeting WHERE NOT (cp_greeting ILIKE '%frau%' OR cp_greeting ILIKE '%herr%' or cp_greeting ILIKE '%mrs.%' or cp_greeting ILIKE '%miss%'); - -ALtER TABLE contacts DROP COLUMN cp_greeting; diff --git a/templates/webpages/dbupgrade/cp_greeting_update_form_de.html b/templates/webpages/dbupgrade/cp_greeting_update_form_de.html new file mode 100644 index 000000000..02e7b9796 --- /dev/null +++ b/templates/webpages/dbupgrade/cp_greeting_update_form_de.html @@ -0,0 +1,36 @@ +[% USE HTML %]
cp_greeting to cp_gender migration
+ + +
+
+

The method for generating greetings ("Dear Mr. Smith") is being changed so the greeting is controlled by the gender. For this a new database field cp_gender is introduced, while the old database field cp_greeting will be removed. The various translations (Mr./Herr/Monsieur) will be stored in the database, under generic_translations. The print variable cp_greeting still exists, however, so print templates don't have to be changed, and the variable is assigned according to the gender of the contact person and the desired language.

+ +

During the conversion process all contact persons need to be assigned a gender. The gender is detected automatically where possible, but in some cases will have to be set manually.

+ +During the conversion process:
+1. All contact persons will have their gender set to "m" (male) as a default
+2. All contact persons where the greeting contains "frau,mrs,miss" are set to "f" (female)
+ +

The gender of the following contact persons couldn't be determined automatically from the greeting, please set the gender manually:

+[% gender_table %] + + +

Please enter your desired default greeting for men and women. To change the +default greeting or add a greeting in more languages, go to: System -> Languages -> Greetings +

+ +

+Greeting for men: [% default_greeting_text_male %]
+Greeting for women: [% default_greeting_text_female %]
+

+ +

+The data from the cp_greeting field will now be dropped. To prevent any data loss if you used the cp_greeting field for anything else, we suggest you transfer this information to the cp_title field: +
+[% title_table %] + + + + +
+ diff --git a/templates/webpages/dbupgrade/cp_greeting_update_form_master.html b/templates/webpages/dbupgrade/cp_greeting_update_form_master.html new file mode 100644 index 000000000..42db00987 --- /dev/null +++ b/templates/webpages/dbupgrade/cp_greeting_update_form_master.html @@ -0,0 +1,36 @@ +[% USE HTML %]
cp_greeting to cp_gender migration
+ + +
+
+

The method for generating greetings ("Dear Mr. Smith") is being changed so the greeting is controlled by the gender. For this a new database field cp_gender is introduced, while the old database field cp_greeting will be removed. The various translations (Mr./Herr/Monsieur) will be stored in the database, under generic_translations. The print variable cp_greeting still exists, however, so print templates don't have to be changed, and the variable is assigned according to the gender of the contact person and the desired language.

+ +

During the conversion process all contact persons need to be assigned a gender. The gender is detected automatically where possible, but in some cases will have to be set manually.

+ +During the conversion process:
+1. All contact persons will have their gender set to "m" (male) as a default
+2. All contact persons where the greeting contains "frau,mrs,miss" are set to "f" (female)
+ +

The gender of the following contact persons couldn't be determined automatically from the greeting, please set the gender manually:

+[% gender_table %] + + +

Please enter your desired default greeting for men and women. To change the +default greeting or add a greeting in more languages, go to: System -> Languages -> Greetings +

+ +

+Greeting for men: [% default_greeting_text_male %]
+Greeting for women: [% default_greeting_text_female %]
+

+ +

+The data from the cp_greeting field will now be dropped. To prevent any data loss if you used the cp_greeting field for anything else, we suggest you transfer this information to the cp_title field: +
+[% title_table %] + + + + +
+ -- 2.20.1