$patch = new Patch(20);
if (!$patch->exists()) {
$emptySql = "SELECT count(id) FROM s3_files WHERE add_date='0000-00-00 00:00:00'";
$originalCount = db()->getValue($emptySql);
$patch->log("Starting with {$originalCount} files with invalid dates.");
// Fix known images
$sql = "UPDATE s3_files, webcam_images\n\t\t\tSET s3_files.add_date=webcam_images.timestamp\n\t\t\tWHERE s3_files.id = webcam_images.image_id\n\t\t\tAND s3_files.add_date='0000-00-00 00:00:00'";
$currentCount = fix_timestamp($sql, "Known image timestamps", $originalCount);
// Fix gcode files uploaded directly
$sql = "UPDATE s3_files, jobs\n\t\t\tSET s3_files.add_date=jobs.created_time\n\t\t\tWHERE jobs.source_file_id=s3_files.id\n\t\t\tAND s3_files.add_date='0000-00-00 00:00:00'";
$currentCount = fix_timestamp($sql, "Known gcode files", $currentCount);
// Fix gcode files made by a slicer
$sql = "UPDATE s3_files, jobs, slice_jobs\n\t\t\tSET s3_files.add_date=slice_jobs.finish_date\n\t\t\tWHERE jobs.id=slice_jobs.job_id\n\t\t\tAND slice_jobs.input_id=jobs.source_file_id\n\t\t\tAND slice_jobs.output_id=jobs.file_id\n AND s3_files.id=jobs.file_id\n\t\t\tAND slice_jobs.finish_date!='0000-00-00 00:00:00'\n\t\t\tAND s3_files.add_date='0000-00-00 00:00:00'";
$currentCount = fix_timestamp($sql, "Sliced gcode files", $currentCount);
// Fix files if a child has a timestamp, but the parent doesn't.
$sql = "UPDATE s3_files a, s3_files b\n\t\t\tSET a.add_date=b.add_date\n\t\t\tWHERE a.id=b.parent_id\n\t\t\tAND b.parent_id!=0\n\t\t\tAND b.add_date!='0000-00-00 00:00:00'\n\t\t\tAND a.add_date='0000-00-00 00:00:00'";
$currentCount = fix_timestamp($sql, "Fixing parent files", $currentCount);
// Fix files if a parent has a timestamp, but the child doesn't.
$sql = "UPDATE s3_files a, s3_files b\n\t\t\tSET a.add_date=b.add_date\n\t\t\tWHERE a.parent_id=b.id\n\t\t\tAND a.parent_id!=0\n\t\t\tAND b.add_date!='0000-00-00 00:00:00'\n\t\t\tAND a.add_date='0000-00-00 00:00:00'";
$currentCount = fix_timestamp($sql, "Fixing child files", $currentCount);
$patch->log("Total: " . ($originalCount - $currentCount) . " fixed, {$currentCount} remaining");
$patch->finish("Fixing bad timestamps for s3_files");
}
function fix_timestamp($sql, $method, $lastCount)
{
global $patch, $emptySql;
db()->execute($sql);
$currentCount = db()->getValue($emptySql);
$patch->log("{$method}: " . ($lastCount - $currentCount) . " fixed");
return $currentCount;
}
$user_id = $job->getUser()->id;
$rowSql = "('" . date("Y-m-d H:i:s", $timestamp) . "', ";
$rowSql .= "{$image_id}, {$user_id}, {$job->id}, ";
$bot = $job->getBot();
if ($bot->isHydrated()) {
$rowSql .= "{$bot->id}";
} else {
$rowSql .= "NULL";
}
$rowSql .= ")";
$rowData[] = $rowSql;
} else {
$failCount++;
}
} else {
// Remove it from the array to save memory
unset($existingImages[$image_id]);
}
}
if (count($rowData) > 0) {
db()->execute("INSERT IGNORE INTO webcam_images(`timestamp`, `image_id`, `user_id`, `job_id`, `bot_id`) VALUES " . implode(",", $rowData));
}
$count++;
$patch->progress($count * 100 / $total);
}
}
if ($failCount > 0) {
$patch->log($failCount . " images no longer exist in the database");
}
$patch->finish("Added webcam images table");
}
<?php
include "../patches.php";
$patch = new Patch(9);
if (!$patch->exists()) {
$addDroppedSQL = "ALTER TABLE job_clock\n \t\tMODIFY COLUMN status\n \t\tENUM('idle','slicing','working','waiting','error','maintenance','offline', 'dropped')";
db()->execute($addDroppedSQL);
$patch->finish("Adding dropped to the job_clock");
}
<?php
include "../patches.php";
$patch = new Patch(6);
if (!$patch->exists()) {
$createTable = "CREATE TABLE `engine_os` (\n\t\t`engine_id` INT(11) UNSIGNED NOT NULL,\n\t\t`os` ENUM('osx','linux','win','raspberrypi'),\n\t\tPRIMARY KEY (`engine_id`, `os`)\n\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8";
db()->execute($createTable);
$patch->finish("Creating Engine OS table");
}
<?php
include "../patches.php";
$patch = new Patch(10);
if (!$patch->exists()) {
$statsViewSQL = "\n CREATE VIEW stats AS\n SELECT (unix_timestamp(end_date) - unix_timestamp(start_date)) AS seconds,\n bot_id, user_id, status, start_date, end_date\n FROM job_clock\n WHERE status != 'working'\n ORDER by seconds DESC\n ";
db()->execute($statsViewSQL);
$patch->finish("Adding stats view");
}
include "../patches.php";
$patch = new Patch(19);
if (!$patch->exists()) {
$createSql = "CREATE TABLE IF NOT EXISTS `notifications` (\n\t\t\t `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t `timestamp` datetime NOT NULL,\n\t\t\t `from_user_id` int(11) unsigned NULL,\n\t\t\t `to_user_id` int(11) unsigned NULL,\n\t\t\t `title` varchar(255) NOT NULL,\n\t\t\t `content` text NOT NULL,\n\t\t\t PRIMARY KEY (`id`),\n\t\t\t FOREIGN KEY (`from_user_id`) REFERENCES users(`id`) ON DELETE CASCADE,\n\t\t\t FOREIGN KEY (`to_user_id`) REFERENCES users(`id`) ON DELETE CASCADE\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
db()->execute($createSql);
$sql = "ALTER TABLE users ADD COLUMN `last_notification` int(11) NOT NULL DEFAULT 0 AFTER `registered_on`";
db()->execute($sql);
$content = "Welcome to the new notification center! I'm going to use this to let you know ";
$content .= "about awesome new updates that are happening to BotQueue. Eventually, you will ";
$content .= "receive updates to comments and messages from other users through this system. ";
$content .= "I'm still working on the placement of the notification icon in the full screen ";
$content .= "mode, because I want the icon to be to the left of the username.";
$content .= "\n\n";
$content .= "If you have any issues, or even suggestions, please let me know in either the ";
$content .= "<a href=\"https://groups.google.com/forum/#!forum/botqueue\">google group</a> or at ";
$content .= "<a href=\"https://github.com/Hoektronics/BotQueue/issues\">GitHub issues</a>.";
$content .= "\n\n";
$content .= "Thank you for using BotQueue!";
$content .= "\n\n";
$content .= " ~ Justin Nesselrotte";
$notification = new Notification();
$notification->set('from_user_id', null);
// From the system
$notification->set('to_user_id', null);
// To everyone
$notification->set('timestamp', date("2014-12-24 00:00:00"));
$notification->set('title', 'New notification system');
$notification->set('content', $content);
$notification->save();
$patch->finish("Added notifications table");
}
<?php
include "../patches.php";
$patch = new Patch(4);
if (!$patch->exists()) {
$addPausedSQL = "ALTER TABLE bots\n MODIFY COLUMN status\n ENUM('idle','slicing','working','paused','waiting','error','maintenance','offline','retired') DEFAULT 'idle'";
db()->execute($addPausedSQL);
$patch->finish("Allowing a bot to be paused");
}
<?php
include "../patches.php";
$patch = new Patch(11);
if (!$patch->exists()) {
$addDroppedSQL = "ALTER TABLE job_clock\n \t\tMODIFY COLUMN status\n \t\tenum('working','waiting', 'complete', 'dropped')";
db()->execute($addDroppedSQL);
$patch->finish("Changing job_clock enum");
}
<?php
include "../patches.php";
$patch = new Patch(16);
if (!$patch->exists()) {
$tables = array('activities', 'bots', 'comments', 'email_queue', 'error_log', 'job_clock', 'jobs', 'oauth_consumer', 'oauth_consumer_nonce', 'oauth_token', 'queues', 's3_files', 'shortcodes', 'slice_configs', 'slice_engines', 'engine_os', 'slice_jobs', 'tokens', 'users', 'patches', 'bot_queues');
foreach ($tables as $table) {
$sql = "ALTER TABLE {$table} ENGINE=InnoDB";
db()->execute($sql);
}
$patch->finish("Converted tables to InnoDB");
}
<?php
include "../patches.php";
$patch = new Patch(5);
if (!$patch->exists()) {
$addContentIDKey = "ALTER TABLE comments ADD KEY `content_id` (`content_id`)";
db()->execute($addContentIDKey);
$addContentTypeKey = "ALTER TABLE comments ADD KEY `content_type` (`content_type`)";
db()->execute($addContentTypeKey);
$addWebcamID = "ALTER TABLE jobs ADD COLUMN `webcam_image_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER verified_time;";
db()->execute($addWebcamID);
$addWebcamImages = "ALTER TABLE jobs ADD COLUMN `webcam_images` TEXT NOT NULL AFTER webcam_image_id";
db()->execute($addWebcamImages);
$dropTimestamp = "ALTER TABLE oauth_consumer_nonce DROP INDEX timestamp";
db()->execute($dropTimestamp);
$dropNonce = "ALTER TABLE oauth_consumer_nonce DROP INDEX nonce";
db()->execute($dropNonce);
$addIPAddressKey = "ALTER TABLE oauth_token ADD KEY `ip_address` (`ip_address`)";
db()->execute($addIPAddressKey);
$addParentIDKey = "ALTER TABLE s3_files ADD KEY `parent_id` (`parent_id`)";
db()->execute($addParentIDKey);
$modifyThingiverseToken = "ALTER TABLE users MODIFY `thingiverse_token` VARCHAR(40) NOT NULL DEFAULT ''";
db()->execute($modifyThingiverseToken);
$modifyThumbnail = "ALTER TABLE users MODIFY `dashboard_style` ENUM('list','large_thumbnails','medium_thumbnails','small_thumbnails') NOT NULL DEFAULT 'large_thumbnails'";
db()->execute($modifyThumbnail);
$patch->finish("Updating the dev table to BotQueue production");
}
$sql = "ALTER TABLE bots MODIFY COLUMN user_id int(11) unsigned NOT NULL";
db()->execute($sql);
$sql = "ALTER TABLE email_queue MODIFY COLUMN user_id int(11) unsigned NOT NULL";
db()->execute($sql);
$sql = "ALTER TABLE jobs MODIFY COLUMN user_id int(11) unsigned NOT NULL";
db()->execute($sql);
$sql = "ALTER TABLE job_clock MODIFY COLUMN user_id int(11) unsigned NOT NULL";
db()->execute($sql);
$sql = "ALTER TABLE oauth_consumer MODIFY COLUMN user_id int(11) unsigned NOT NULL";
db()->execute($sql);
$sql = "ALTER TABLE oauth_token MODIFY COLUMN user_id int(11) unsigned";
db()->execute($sql);
$sql = "UPDATE oauth_token set user_id=NULL where user_id=0";
db()->execute($sql);
$sql = "ALTER TABLE queues MODIFY COLUMN user_id int(11) unsigned NOT NULL";
db()->execute($sql);
$sql = "ALTER TABLE s3_files MODIFY COLUMN user_id int(11) unsigned NOT NULL";
db()->execute($sql);
foreach ($tables as $table) {
$sql = "DROP INDEX user_id on {$table}";
db()->execute($sql);
}
$tables = array_merge($tables, array('comments', 'oauth_consumer', 's3_files', 'tokens'));
foreach ($tables as $table) {
$sql = "DELETE FROM {$table} WHERE user_id NOT IN (SELECT id FROM users) AND user_id IS NOT NULL";
db()->execute($sql);
$sql = "ALTER TABLE {$table} ADD FOREIGN KEY (`user_id`) REFERENCES users(`id`) ON DELETE CASCADE";
db()->execute($sql);
}
$patch->finish("Added user ID constraint");
}
<?php
include "../patches.php";
$patch = new Patch(13);
if (!$patch->exists()) {
$sql = "ALTER TABLE bots MODIFY COLUMN `error_text` text NOT NULL DEFAULT ''";
db()->execute($sql);
$patch->finish("Expanded error_text field");
}
<?php
include "../patches.php";
$patch = new Patch(8);
if (!$patch->exists()) {
$removeSlicerDescription = "alter table slice_engines drop column engine_description";
db()->execute($removeSlicerDescription);
$patch->finish("Removing the engine_description");
}
<?php
include "../patches.php";
$patch = new Patch(14);
if (!$patch->exists()) {
$sql = "CREATE TABLE IF NOT EXISTS `bot_queues` (\n\t\t `queue_id` INT(11) UNSIGNED NOT NULL,\n\t\t `bot_id` INT(11) UNSIGNED NOT NULL,\n\t\t `priority` INT(11) UNSIGNED NOT NULL,\n\t\t PRIMARY KEY (`queue_id`, `bot_id`, `priority`)\n\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8";
db()->execute($sql);
$sql = "SELECT id, queue_id from bots";
$bots = new Collection($sql);
$bots->bindType("id", "Bot");
$bots->bindType("queue_id", "Queue");
foreach ($bots->getAll() as $row) {
$bot = $row['Bot'];
$queue = $row['Queue'];
$sql = "INSERT INTO bot_queues VALUES(?, ?, 1)";
$data = array($queue->id, $bot->id);
db()->execute($sql, $data);
}
$sql = "DROP INDEX queue_id ON bots";
db()->execute($sql);
$sql = "ALTER TABLE bots DROP COLUMN queue_id";
db()->execute($sql);
$patch->finish("Added bots to queues");
}
$job = new Job($row['id']);
$fixed_data = fix_temp_data($job->get('temperature_data'));
$job->set('temperature_data', $fixed_data);
$job->save();
}
$rs = db()->query("SELECT * from bots");
while ($row = $rs->fetch(PDO::FETCH_ASSOC)) {
$bot = new Bot($row['id']);
$fixed_data = fix_temp_data($bot->get('temperature_data'));
$bot->set('temperature_data', $fixed_data);
$bot->save();
}
$expandTemperatureData = "\n\t\tALTER TABLE jobs\n \t\tMODIFY COLUMN temperature_data longtext NOT NULL";
db()->execute($expandTemperatureData);
$expandTemperatureData = "\n\t\tALTER TABLE bots\n \t\tMODIFY COLUMN temperature_data longtext NOT NULL";
$patch->finish("Expanded temperature data fields");
}
function fix_temp_data($data)
{
if (strlen($data) == 0) {
return "";
}
$data .= "}";
while (JSON::decode($data) === null) {
// Remove last two characters
$data = substr($data, 0, -2);
// Add the end of the temperature data
$data .= "}";
}
return $data;
}
<?php
include "../patches.php";
$patch = new Patch(2);
if (!$patch->exists()) {
$addRetirementSQL = "ALTER TABLE bots\n MODIFY COLUMN status\n ENUM('idle','slicing','working','waiting','error','maintenance','offline','retired')\n DEFAULT 'idle'";
db()->execute($addRetirementSQL);
$patch->finish("Allowing a bot to be retired");
}
<?php
include "../patches.php";
$patch = new Patch(15);
if (!$patch->exists()) {
$sql = "ALTER TABLE queues ADD COLUMN `delay` int(11) unsigned NOT NULL DEFAULT 0 AFTER name";
db()->execute($sql);
$patch->finish("Added queue delay");
}
<?php
include "../patches.php";
$patch = new Patch(21);
if (!$patch->exists()) {
// Create new type column
$sql = "ALTER TABLE oauth_token ADD COLUMN `type2` enum('request', 'verified', 'access') AFTER type";
db()->execute($sql);
// Kill tokens that are stuck in a bad state due to a previous issue
$sql = "DELETE from oauth_token where type=2 AND verified=0";
// Migrate the 3 types over
$sql = "UPDATE oauth_token SET type2='request' WHERE type=1 AND verified=0";
db()->execute($sql);
$sql = "UPDATE oauth_token SET type2='verified' WHERE type=1 AND verified=1";
db()->execute($sql);
$sql = "UPDATE oauth_token SET type2='access' WHERE type=2 AND verified=1";
db()->execute($sql);
$sql = "ALTER TABLE oauth_token DROP COLUMN type";
db()->execute($sql);
$sql = "ALTER TABLE oauth_token DROP COLUMN verified";
db()->execute($sql);
$sql = "ALTER TABLE oauth_token CHANGE type2 type enum('request', 'verified', 'access')";
db()->execute($sql);
$patch->finish("Converting oauth token codes");
}
<?php
include "../patches.php";
$patch = new Patch(7);
if (!$patch->exists()) {
$addWebcamID = "ALTER TABLE bots ADD COLUMN `webcam_image_id` INT(11) UNSIGNED NOT NULL DEFAULT '0'";
db()->execute($addWebcamID);
$patch->finish("Adding webcam image to bot temporarily");
}
<?php
include "../patches.php";
$patch = new Patch(25);
if (!$patch->exists()) {
$sql = "ALTER table bots ADD KEY `name` (`name`)";
db()->execute($sql);
$patch->finish("Adding MySQL keys");
}
<?php
include "../patches.php";
$patch = new Patch(1);
// Special case, we create it if it doesn't exist, so no error occurs.
$createPatches = "CREATE TABLE IF NOT EXISTS `patches` (\n `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,\n `patch_num` INT(11) UNSIGNED NOT NULL,\n `description` TEXT NOT NULL,\n PRIMARY KEY (`id`),\n KEY `patch_num` (`patch_num`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
db()->execute($createPatches);
if (!$patch->exists()) {
$patch->finish("Starting the patch system");
}
<?php
include "../patches.php";
$patch = new Patch(3);
if (!$patch->exists()) {
$addCanceledSQL = "ALTER TABLE jobs\n MODIFY COLUMN status\n ENUM('available','taken','slicing','downloading','qa','complete','failure','canceled') NOT NULL DEFAULT 'available'";
db()->execute($addCanceledSQL);
$patch->finish("Allowing a job to be canceled");
}
<?php
include "../patches.php";
$patch = new Patch(22);
if (!$patch->exists()) {
$sql = "UPDATE jobs set user_sort=0 WHERE status IN (\n\t\t\t'complete',\n\t\t\t'failure',\n\t\t\t'canceled'\n\t\t)";
$jobsAffected = db()->execute($sql);
$patch->log("{$jobsAffected} jobs cleaned");
$patch->finish("Cleaning up user_sort for jobs");
}
