function alterTablePersons()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Persons", "Remove columns");
dbCommand("ALTER TABLE Persons\n DROP COLUMN `localName`,\n DROP COLUMN `romanName`");
}
function alterTableCountries()
{
#----------------------------------------------------------------------
reportAction("Countries", "Alter field set");
dbCommand("\n ALTER TABLE Countries\n ADD COLUMN `latitude` INTEGER NOT NULL DEFAULT 0,\n ADD COLUMN `longitude` INTEGER NOT NULL DEFAULT 0,\n ADD COLUMN `zoom` TINYINT NOT NULL DEFAULT 0\n ");
reportAction("Countries", "Fill");
dbCommand("\n UPDATE Countries\n SET latitude='50503887',\n longitude='4469936',\n zoom='7'\n WHERE id = 'Belgium'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='-14235004',\n longitude='-51925280',\n zoom='4'\n WHERE id = 'Brazil'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='56130366',\n longitude='-106346771',\n zoom='3'\n WHERE id = 'Canada'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='35861660',\n longitude='104195397',\n zoom='4'\n WHERE id = 'China'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='49817492',\n longitude='15472962',\n zoom='7'\n WHERE id = 'Czech Republic'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='56263920',\n longitude='9501785',\n zoom='6'\n WHERE id = 'Denmark'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='61924110',\n longitude='25748151',\n zoom='5'\n WHERE id = 'Finland'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='46227638',\n longitude='2213749',\n zoom='5'\n WHERE id = 'France'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='51165691',\n longitude='10451526',\n zoom='5'\n WHERE id = 'Germany'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='22396428',\n longitude='114109497',\n zoom='10'\n WHERE id = 'Hong Kong'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='47162494',\n longitude='19503304',\n zoom='7'\n WHERE id = 'Hungary'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='20593684',\n longitude='78962880',\n zoom='4'\n WHERE id = 'India'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='41871940',\n longitude='12567380',\n zoom='5'\n WHERE id = 'Italy'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='36204824',\n longitude='138252924',\n zoom='5'\n WHERE id = 'Japan'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='35907757',\n longitude='127766922',\n zoom='6'\n WHERE id = 'Korea'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='23634501',\n longitude='-102552784',\n zoom='5'\n WHERE id = 'Mexico'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='52132633',\n longitude='5291266',\n zoom='7'\n WHERE id = 'Netherlands'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='65146114',\n longitude='13183593',\n zoom='4'\n WHERE id = 'Norway'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='12879721',\n longitude='121774017',\n zoom='5'\n WHERE id = 'Philippines'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='51919438',\n longitude='19145136',\n zoom='6'\n WHERE id = 'Poland'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='39399872',\n longitude='-8224454',\n zoom='6'\n WHERE id = 'Portugal'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='40463667',\n longitude='-3749220',\n zoom='6'\n WHERE id = 'Spain'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='60128161',\n longitude='18643501',\n zoom='4'\n WHERE id = 'Sweden'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='46818188',\n longitude='8227512',\n zoom='7'\n WHERE id = 'Switzerland'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='23697810',\n longitude='120960515',\n zoom='7'\n WHERE id = 'Taiwan'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='55378051',\n longitude='-3435973',\n zoom='5'\n WHERE id = 'United Kingdom'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='37090240',\n longitude='-95712891',\n zoom='4'\n WHERE id = 'USA'\n ");
}
function alterTablePreregs()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Preregs", "Alter field set");
dbCommand("\n ALTER TABLE Preregs\n ADD COLUMN `eventIds` TEXT NOT NULL DEFAULT ''\n ");
$i = 0;
$len = 1000;
$preregs = dbQuery(" SELECT * FROM Preregs LIMIT {$i},{$len}");
while (count($preregs) != 0) {
foreach ($preregs as $prereg) {
$id = $prereg['id'];
$eventIds = '';
foreach (array_merge(getAllEventIds(), getAllUnofficialEventIds()) as $eventId) {
if ($prereg["E{$eventId}"] != 0) {
$eventIds .= "{$eventId} ";
}
}
rtrim($eventIds);
dbCommand("UPDATE Preregs SET eventIds='{$eventIds}' WHERE id='{$id}'");
}
$i += $len;
$preregs = dbQuery(" SELECT * FROM Preregs LIMIT {$i},{$len}");
}
foreach (array_merge(getAllEventIds(), getAllUnofficialEventIds()) as $eventId) {
dbCommand("ALTER TABLE Preregs\n DROP COLUMN `E{$eventId}`");
}
}
function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Alter field set");
dbCommand("\n ALTER TABLE Competitions\n DROP COLUMN `articles`,\n DROP COLUMN `reports`,\n DROP COLUMN `multimedia`\n ");
}
function moveOldMulti()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Results", "Move");
$results = dbQuery(" SELECT * FROM Results WHERE eventId='333mbo' ");
foreach ($results as $result) {
$oneGood = false;
$theBest = 0;
$values = array(0, 0, 0);
foreach (range(1, $result['formatId']) as $n) {
$value = $result["value{$n}"];
if ($value <= 0) {
$values[$n] = $value;
continue;
}
$old = intval($value / 1000000000);
if ($old) {
$time = $value % 100000;
$value = intval($value / 100000);
$attempted = $value % 100;
$value = intval($value / 100);
$solved = 99 - $value % 100;
$value = intval($value / 100);
$difference = 2 * $solved - $attempted;
} else {
$missed = $value % 100;
$value = intval($value / 100);
$time = $value % 100000;
$value = intval($value / 100000);
$difference = 99 - $value % 100;
$solved = $difference + $missed;
$attempted = $solved + $missed;
}
if ($time <= 3600 && $time <= 600 * $attempted && $difference >= 0) {
$oneGood = true;
$missed = $attempted - $solved;
$difference = $solved - $missed;
$value = 99 - $difference;
$value = $value * 100000 + $time;
$value = $value * 100 + $missed;
$values[$n] = $value;
if ($theBest > 0) {
$theBest = min($theBest, $value);
} else {
$theBest = $value;
}
} else {
$values[$n] = -2;
}
}
if ($oneGood) {
extract($result);
dbCommand(" INSERT INTO Results (pos, personId, personName, countryId, competitionId, eventId, roundId, formatId, value1, value2, value3, value4, value5, best, average, regionalSingleRecord, regionalAverageRecord)\n VALUES ('{$pos}', '{$personId}', '{$personName}', '{$countryId}', '{$competitionId}', '333mbf', '{$roundId}', '{$formatId}', '{$values['1']}', '{$values['2']}', '{$values['3']}', '0', '0', '{$theBest}', '{$average}', '{$regionalSingleRecord}', '{$regionalAverageRecord}')");
}
}
}
function createTablePreregs()
{
#----------------------------------------------------------------------
#--- Create the table.
reportAction("Preregs", "Create");
foreach (getAllEvents() as $event) {
extract($event);
$eventFields .= "E{$id} BOOLEAN NOT NULL DEFAULT 0,";
}
dbCommand("\n CREATE TABLE Preregs (\n id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,\n competitionId VARCHAR(32) NOT NULL,\n name VARCHAR(80) NOT NULL,\n personId VARCHAR(10) NOT NULL,\n countryId VARCHAR(50) NOT NULL,\n gender CHAR(1) NOT NULL,\n birthYear SMALLINT(6) UNSIGNED NOT NULL,\n birthMonth TINYINT(4) UNSIGNED NOT NULL,\n birthDay TINYINT(4) UNSIGNED NOT NULL,\n email VARCHAR(80) NOT NULL,\n guests TEXT NOT NULL,\n comments TEXT NOT NULL,\n ip VARCHAR(16) NOT NULL,\n status CHAR(1) NOT NULL,\n {$eventFields}\n PRIMARY KEY ( id )\n )");
}
function alterTablePersons()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Persons", "Alter field set");
dbCommand("\n ALTER TABLE Persons\n ADD COLUMN `romanName` VARCHAR(80) CHARACTER SET utf8 NOT NULL DEFAULT ''\n ");
dbCommand("\n ALTER TABLE Persons\n CHANGE `name` `name` VARCHAR(80) CHARACTER SET utf8\n ");
dbCommand("\n ALTER TABLE Results\n CHANGE `personName` `personName` VARCHAR(80) CHARACTER SET utf8\n ");
dbCommand("\n ALTER TABLE Preregs\n CHANGE `name` `name` VARCHAR(80) CHARACTER SET utf8\n ");
dbCommand("\n UPDATE Persons\n SET romanName=name\n ");
}
function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Drop comments, rename eventIds to eventSpecs");
dbCommand("\n ALTER TABLE Competitions\n DROP COLUMN `comments`,\n CHANGE COLUMN `eventIds` `eventSpecs` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL\n ");
#--- Repace endMonth/endDay zeroes with positive numbers.
reportAction("Competitions", "Replace endMonth zeroes");
dbCommand("UPDATE Competitions SET endMonth=month WHERE endMonth=0");
#--- Repace endMonth/endDay zeroes with positive numbers.
reportAction("Competitions", "Replace endDay zeroes");
dbCommand("UPDATE Competitions SET endDay=day WHERE endDay=0");
}
function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Alter field set");
dbCommand("\n ALTER TABLE Competitions\n ADD COLUMN `showAtAll` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `showResults` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `password` VARCHAR(45) NOT NULL;\n ");
#--- Make showAtAll true, and showResults true for all competitions with results.
reportAction("Competitions", "Set {showAtAll,showResults}");
dbCommand("UPDATE Competitions SET showAtAll=1");
dbCommand("\n UPDATE Competitions competition, Results result\n SET competition.showResults = 1\n WHERE competition.id = result.competitionId;\n ");
#--- Generate passwords for all competitions.
reportAction("Competitions", "Generate passwords");
$competitions = dbQuery("SELECT id FROM Competitions");
foreach ($competitions as $competition) {
extract($competition);
$password = generateNewPassword($id);
dbCommand("\n UPDATE Competitions\n SET password='******'\n WHERE id='{$id}'\n ");
}
}
function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Change columns");
dbCommand("ALTER TABLE Competitions\n ALTER COLUMN `showAtAll` SET DEFAULT '0'");
dbCommand("ALTER TABLE Competitions\n ADD COLUMN `adminPassword` VARCHAR(45) NOT NULL DEFAULT ''");
dbCommand("ALTER TABLE Competitions\n ADD COLUMN `isConfirmed` TINYINT(1) NOT NULL DEFAULT '0'");
dbCommand("ALTER TABLE Competitions\n CHANGE password organiserPassword VARCHAR(45)");
dbCommand("ALTER TABLE Competitions\n DROP COLUMN `showResults`");
#--- Generate admin passwords for all competitions.
reportAction("Competitions", "Generate admin passwords");
$competitions = dbQuery("SELECT id FROM Competitions");
foreach ($competitions as $competition) {
extract($competition);
$password = generateNewPassword($id);
dbCommand("\n UPDATE Competitions\n SET adminPassword='******'\n WHERE id='{$id}'\n ");
}
#--- Set all competitions to confirmed status.
reportAction("Competitions", "Change to confirmed");
dbCommand("\n UPDATE Competitions\n SET isConfirmed='1'\n ");
}
function convertToUTF8()
{
#----------------------------------------------------------------------
reportAction("Persons", "Change names to UTF-8");
$persons = dbQuery("SELECT id, name FROM Persons");
foreach ($persons as $person) {
extract($person);
$utfname = mysql_real_escape_string(utf8_encode($name));
if ($utfname != $name) {
dbCommand("UPDATE Persons SET name='{$utfname}' WHERE id='{$id}'");
}
}
reportAction("Preregs", "Change names to UTF-8");
$preregs = dbQuery("SELECT id, name FROM Preregs");
foreach ($preregs as $prereg) {
extract($prereg);
$utfname = mysql_real_escape_string(utf8_encode($name));
if ($utfname != $name) {
dbCommand("UPDATE Preregs SET name='{$utfname}' WHERE id='{$id}'");
}
}
reportAction("Results", "Change names to UTF-8");
$step = 10000;
$from = 0;
$results = dbQuery("SELECT id, personName FROM Results ORDER BY id LIMIT {$from},{$step}");
# Can't do it in one query. Too big.
while (count($results) != 0) {
foreach ($results as $result) {
extract($result);
$utfname = mysql_real_escape_string(utf8_encode($personName));
if ($utfname != $personName) {
dbCommand("UPDATE Results SET personName='{$utfname}' WHERE id='{$id}'");
}
}
$from += $step;
$results = dbQuery("SELECT id, personName FROM Results ORDER BY id LIMIT {$from},{$step}");
}
reportAction("Competitions", "Change names to UTF-8");
$competitions = dbQuery("SELECT id, name, cityName, countryId FROM Preregs");
foreach ($preregs as $prereg) {
extract($prereg);
$utfname = mysql_real_escape_string(utf8_encode($name));
if ($utfname != $name) {
dbCommand("UPDATE Preregs SET name='{$utfname}' WHERE id='{$id}'");
}
}
}
function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Alter field set");
dbCommand("\n ALTER TABLE Competitions\n DROP COLUMN `comments`,\n DROP COLUMN `articles`,\n DROP COLUMN `reports`,\n DROP COLUMN `multimedia`,\n CHANGE COLUMN `eventIds` `eventSpecs` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,\n ADD COLUMN `preregPreface` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,\n ADD COLUMN `showAtAll` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `showResults` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `showPreregForm` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `showPreregList` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `viewPassword` VARCHAR(45) NOT NULL,\n ADD COLUMN `editPassword` VARCHAR(45) NOT NULL;\n ");
#--- Make showAtAll true, and showResults true for all competitions with results.
reportAction("Competitions", "Set {showAtAll,showResults}");
dbCommand("UPDATE Competitions SET showAtAll=1");
dbCommand("\n UPDATE Competitions competition, Results result\n SET competition.showResults = 1\n WHERE competition.id = result.competitionId;\n ");
#--- Generate passwords for all competitions.
reportAction("Competitions", "Generate passwords");
$competitions = dbQuery("SELECT id FROM Competitions");
foreach ($competitions as $competition) {
extract($competition);
$password = generateNewPassword($id);
dbCommand("\n UPDATE Competitions\n SET viewPassword='******', editPassword='******'\n WHERE id='{$id}'\n ");
}
#--- Repace endMonth/endDay zeroes with positive numbers.
reportAction("Competitions", "Replace endMonth/endDay zeroes");
dbCommand("UPDATE Competitions SET endMonth=month WHERE endMonth=0");
dbCommand("UPDATE Competitions SET endDay=day WHERE endDay=0");
}
