$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"); }