Revision 94632453
Von G. Richardson vor mehr als 15 Jahren hinzugefügt
| sql/Pg-upgrade2/cp_greeting_migration.pl | ||
|---|---|---|
|
# @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:") .
|
||
|
"<br>$msg<br>" . $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 .= '<table border="1"><tr><th>cp_givenname</th><th>cp_name</th><th>cp_title</th><th>cp_greeting</th><th><translate>male/female</th></tr>';
|
||
|
$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 .= "<tr><input type=hidden name=\"cp_id_$i\" value=\"$row->{cp_id}\"> <td>$row->{cp_givenname}</td> <td>$row->{cp_name}</td> <td>$row->{cp_title} </td> <td>$row->{cp_greeting} </td><td> <input type=\"radio\" name=\"gender_$i\" value=\"m\" $mchecked> <input type=\"radio\" name=\"gender_$i\" value=\"f\" $fchecked></td></tr>\n";
|
||
|
$i++;
|
||
|
};
|
||
|
|
||
|
$gender_table .= "<input type=hidden name=\"number_of_gender_entries\" value=\"$i\">";
|
||
|
$gender_table .= "</table>";
|
||
|
|
||
|
$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 = '<table border="1"><tr><th>cp_givenname</th><th>cp_name</th><th>cp_title</th><th>cp_greeting</th><th>cp_title new</th></tr>';
|
||
|
|
||
|
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 .= "<tr> <td><input type=hidden name=\"cp_id_title_$j\" value=$row->{cp_id}> $row->{cp_givenname}</td> <td>$row->{cp_name}</td><td>$row->{cp_title}</td> <td>$row->{cp_greeting}</td><td><input type=\"text\" id=\"cp_title_$j\" name=\"cp_name_$j\" value=\"$value\"></td> </tr>\n";
|
||
|
$j++;
|
||
|
};
|
||
|
|
||
|
$title_table .= "<input type=hidden name=\"number_of_title_entries\" value=\"$j\">";
|
||
|
$title_table .= "</table>";
|
||
|
$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 = <<EOF
|
||
|
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%');
|
||
|
EOF
|
||
|
;
|
||
|
|
||
|
for (my $i = 1; $i <= $main::form->{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 = "<input type=\"text\" id=\"default_male\" name=\"default_male\" value=\"$default_male\"><br>";
|
||
|
my $default_greeting_text_female = "<input type=\"text\" id=\"default_female\" name=\"default_female\" value=\"$default_female\"><br>";
|
||
|
$main::form->{default_greeting_text_male} = $default_greeting_text_male;
|
||
|
$main::form->{default_greeting_text_female} = $default_greeting_text_female;
|
||
|
};
|
||
|
|
||
|
|
||
|
return do_update();
|
||
|
|
||
| sql/Pg-upgrade2/gender.sql | ||
|---|---|---|
|
-- @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;
|
||
| templates/webpages/dbupgrade/cp_greeting_update_form_de.html | ||
|---|---|---|
|
[% USE HTML %]<div class="listtop">cp_greeting to cp_gender migration</div>
|
||
|
|
||
|
|
||
|
<form action="[% script %]" method="POST">
|
||
|
<br>
|
||
|
<p> 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.</p>
|
||
|
|
||
|
<p>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.</p>
|
||
|
|
||
|
During the conversion process:<br>
|
||
|
1. All contact persons will have their gender set to "m" (male) as a default<br>
|
||
|
2. All contact persons where the greeting contains "frau,mrs,miss" are set to "f" (female)<br>
|
||
|
|
||
|
<p>The gender of the following contact persons couldn't be determined automatically from the greeting, please set the gender manually:</p>
|
||
|
[% gender_table %]
|
||
|
|
||
|
|
||
|
<p>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
|
||
|
</p>
|
||
|
|
||
|
<p>
|
||
|
Greeting for men: [% default_greeting_text_male %] <br>
|
||
|
Greeting for women: [% default_greeting_text_female %] <br>
|
||
|
</p>
|
||
|
|
||
|
<br><br>
|
||
|
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:
|
||
|
<br>
|
||
|
[% title_table %]
|
||
|
|
||
|
<input type=hidden name="do_migrate" value="1">
|
||
|
<input type=submit VALUE="submit">
|
||
|
|
||
|
</form>
|
||
|
|
||
| templates/webpages/dbupgrade/cp_greeting_update_form_master.html | ||
|---|---|---|
|
[% USE HTML %]<div class="listtop"><translate>cp_greeting to cp_gender migration</translate></div>
|
||
|
|
||
|
|
||
|
<form action="[% script %]" method="POST">
|
||
|
<br>
|
||
|
<p> 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.</p>
|
||
|
|
||
|
<p>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.</p>
|
||
|
|
||
|
During the conversion process:<br>
|
||
|
1. All contact persons will have their gender set to "m" (male) as a default<br>
|
||
|
2. All contact persons where the greeting contains "frau,mrs,miss" are set to "f" (female)<br>
|
||
|
|
||
|
<p>The gender of the following contact persons couldn't be determined automatically from the greeting, please set the gender manually:</p>
|
||
|
[% gender_table %]
|
||
|
|
||
|
|
||
|
<p>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
|
||
|
</p>
|
||
|
|
||
|
<p>
|
||
|
Greeting for men: [% default_greeting_text_male %] <br>
|
||
|
Greeting for women: [% default_greeting_text_female %] <br>
|
||
|
</p>
|
||
|
|
||
|
<br><br>
|
||
|
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:
|
||
|
<br>
|
||
|
[% title_table %]
|
||
|
|
||
|
<input type=hidden name="do_migrate" value="1">
|
||
|
<input type=submit VALUE="submit">
|
||
|
|
||
|
</form>
|
||
|
|
||
Auch abrufbar als: Unified diff
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
During migration user gets a chance toFor print variables cp_greeting is generated from cp_gender and generic_translations
gender.sql is deleted, as there was a risk of losing data in cp_title
Migration code was originally copied from warehouse code