changed note field in tc_accomplishment to notes

[eq/.git] / bin / upgrade_1_1_0_to_1_1_1

diff --git a/bin/upgrade_1_1_0_to_1_1_1 b/bin/upgrade_1_1_0_to_1_1_1
index 9bb7ff609f2fd0e0f7ff3acb4d7cbe904b45b385..7936a7dfc6e374d2cb6b7083dd53843b2d374300 100755 (executable)
--- a/bin/upgrade_1_1_0_to_1_1_1
+++ b/bin/upgrade_1_1_0_to_1_1_1
@@ -36,6 +36,64 @@ $sth->execute or die "-E- DB error: $DBI::errstr\n";
 $sth = $dbh->prepare("ALTER TABLE `tc_visit` DROP `visit_type` ");
 $sth->execute or die "-E- DB error: $DBI::errstr\n";
 
+# add type field to tc_companionship
+$sth = $dbh->prepare("ALTER TABLE `tc_companionship` ADD `type` ENUM( 'H', 'P' ) NOT NULL DEFAULT 'H' AFTER `district`");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# rename presidency field in tc_appointment to leader
+$sth = $dbh->prepare("ALTER TABLE `tc_appointment` CHANGE `presidency` `leader` INT( 16 ) UNSIGNED NOT NULL DEFAULT '0'");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# rename presidency field in tc_leader to leader
+$sth = $dbh->prepare("ALTER TABLE `tc_presidency` CHANGE `presidency` `leader` INT( 16 ) UNSIGNED NOT NULL AUTO_INCREMENT");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# rename tc_presidency to tc_leader
+$sth = $dbh->prepare("RENAME TABLE `tc_presidency` TO `tc_leader`");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# add type field to tc_leader in place of president, counselor, secretary fields
+$sth = $dbh->prepare("ALTER TABLE `tc_leader` ADD `type` ENUM( 'P', 'C', 'S', 'D' ) NOT NULL AFTER `email`");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+$sth = $dbh->prepare("UPDATE tc_leader SET type='P' WHERE president=1");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+$sth = $dbh->prepare("UPDATE tc_leader SET type='C' WHERE counselor=1");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+$sth = $dbh->prepare("UPDATE tc_leader SET type='S' WHERE secretary=1");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+$sth = $dbh->prepare("UPDATE tc_leader SET type='D' WHERE president=0 AND counselor=0 AND secretary=0");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+$sth = $dbh->prepare("ALTER TABLE `tc_leader` DROP `president`, DROP `counselor`, DROP `secretary`");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# rename supervisor field in tc_district to leader
+$sth = $dbh->prepare("ALTER TABLE `tc_district` CHANGE `supervisor` `leader` INT( 16 ) UNSIGNED NULL DEFAULT NULL");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+$sth = $dbh->prepare("UPDATE tc_district AS td JOIN tc_leader AS tl SET td.leader=tl.leader WHERE td.leader=tl.individual AND td.district=tl.district AND tl.valid=1 AND td.valid=1");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# rename supervisor field in tc_district_sandbox to leader
+$sth = $dbh->prepare("ALTER TABLE `tc_district_sandbox` CHANGE `supervisor` `leader` INT( 16 ) UNSIGNED NULL DEFAULT NULL");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+$sth = $dbh->prepare("UPDATE tc_district_sandbox AS tdc JOIN tc_leader AS tl SET tdc.leader=tl.leader WHERE tdc.leader=tl.individual AND tdc.district=tl.district AND tl.valid=1");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# remove district field from tc_leader
+$sth = $dbh->prepare("ALTER TABLE `tc_leader` DROP `district`");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# remove companion1 and companion2 from tc_visit
+$sth = $dbh->prepare("ALTER TABLE `tc_visit` DROP `companion1`, DROP `companion2`");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# change tc_interview so interviewer points to tc_leader.leader
+$sth = $dbh->prepare("UPDATE tc_interview AS ti JOIN tc_leader AS tl SET ti.interviewer=tl.leader WHERE ti.interviewer=tl.individual");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
+# rename note field in tc_accomplishment to notes
+$sth = $dbh->prepare("ALTER TABLE `tc_accomplishment` CHANGE `note` `notes` VARCHAR( 128 ) NOT NULL ");
+$sth->execute or die "-E- DB error: $DBI::errstr\n";
+
 print "-> Done!\n";
 
 ###################################################