cidb: add deleted column to annotationsTable.

We don't want to actually delete entries from the table. Instead, mark
the entry as deleted in this new column.

BUG=chromium:449615
TEST=cidb_integration_test
     Verify that the new column gets created in the new DB, since there
     is no test coverage for annotationsTable in the integration test
     yet.

Change-Id: Ie96009218e12c3a3fd7c1619c852db7b1536d8a6
Reviewed-on: https://chromium-review.googlesource.com/253870
Tested-by: Prathmesh Prabhu <pprabhu@chromium.org>
Reviewed-by: Aviv Keshet <akeshet@chromium.org>

3 files changed, 18 insertions, 12 deletions

diff --git a/cidb/migrations/00039_alter_annotations_table_add_deleted.sql b/cidb/migrations/00039_alter_annotations_table_add_deleted.sql
new file mode 100644
index 000000000..fddf87189
--- /dev/null
+++ b/cidb/migrations/00039_alter_annotations_table_add_deleted.sql
@@ -0,0 +1,5 @@
+ALTER TABLE annotationsTable
+  ADD COLUMN deleted BOOL NOT NULL DEFAULT false;
+
+INSERT INTO schemaVersionTable (schemaVersion, scriptName) VALUES
+  (39, '00039_alter_annotations_table_add_deleted.sql');
diff --git a/cidb/schema.dump b/cidb/schema.dump
index 5f545346e..4bf1a7635 100644
--- a/cidb/schema.dump
+++ b/cidb/schema.dump
@@ -23,6 +23,7 @@ CREATE TABLE `annotationsTable` (
   `failure_message` varchar(1024) DEFAULT NULL,
   `blame_url` varchar(512) DEFAULT NULL,
   `notes` varchar(1024) DEFAULT NULL,
+  `deleted` tinyint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   KEY `build_id` (`build_id`),
   KEY `last_updated` (`last_updated`),
@@ -271,9 +272,9 @@ CREATE TABLE `schemaVersionTable` (
 /*!50001 SET @saved_cs_client          = @@character_set_client */;
 /*!50001 SET @saved_cs_results         = @@character_set_results */;
 /*!50001 SET @saved_col_connection     = @@collation_connection */;
-/*!50001 SET character_set_client      = latin1 */;
-/*!50001 SET character_set_results     = latin1 */;
-/*!50001 SET collation_connection      = latin1_swedish_ci */;
+/*!50001 SET character_set_client      = utf8 */;
+/*!50001 SET character_set_results     = utf8 */;
+/*!50001 SET collation_connection      = utf8_general_ci */;
 /*!50001 CREATE ALGORITHM=UNDEFINED */
 /*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */
 /*!50001 VIEW `clActionView` AS select `c`.`id` AS `id`,`c`.`build_id` AS `build_id`,`c`.`change_number` AS `change_number`,`c`.`patch_number` AS `patch_number`,`c`.`change_source` AS `change_source`,`c`.`action` AS `action`,`c`.`reason` AS `reason`,`c`.`timestamp` AS `timestamp`,`b`.`last_updated` AS `last_updated`,`b`.`master_build_id` AS `master_build_id`,`b`.`buildbot_generation` AS `buildbot_generation`,`b`.`builder_name` AS `builder_name`,`b`.`waterfall` AS `waterfall`,`b`.`build_number` AS `build_number`,`b`.`build_config` AS `build_config`,`b`.`bot_hostname` AS `bot_hostname`,`b`.`start_time` AS `start_time`,`b`.`finish_time` AS `finish_time`,`b`.`status` AS `status`,`b`.`build_type` AS `build_type`,`b`.`chrome_version` AS `chrome_version`,`b`.`milestone_version` AS `milestone_version`,`b`.`platform_version` AS `platform_version`,`b`.`full_version` AS `full_version`,`b`.`sdk_version` AS `sdk_version`,`b`.`toolchain_url` AS `toolchain_url`,`b`.`final` AS `final`,`b`.`metadata_url` AS `metadata_url`,`b`.`summary` AS `summary`,`b`.`deadline` AS `deadline` from (`clActionTable` `c` join `buildTable` `b` on((`c`.`build_id` = `b`.`id`))) */;
@@ -287,9 +288,9 @@ CREATE TABLE `schemaVersionTable` (
 /*!50001 SET @saved_cs_client          = @@character_set_client */;
 /*!50001 SET @saved_cs_results         = @@character_set_results */;
 /*!50001 SET @saved_col_connection     = @@collation_connection */;
-/*!50001 SET character_set_client      = latin1 */;
-/*!50001 SET character_set_results     = latin1 */;
-/*!50001 SET collation_connection      = latin1_swedish_ci */;
+/*!50001 SET character_set_client      = utf8 */;
+/*!50001 SET character_set_results     = utf8 */;
+/*!50001 SET collation_connection      = utf8_general_ci */;
 /*!50001 CREATE ALGORITHM=UNDEFINED */
 /*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */
 /*!50001 VIEW `failureView` AS select `f`.`id` AS `id`,`f`.`build_stage_id` AS `build_stage_id`,`f`.`outer_failure_id` AS `outer_failure_id`,`f`.`exception_type` AS `exception_type`,`f`.`exception_message` AS `exception_message`,`f`.`exception_category` AS `exception_category`,`f`.`extra_info` AS `extra_info`,`f`.`timestamp` AS `timestamp`,`bs`.`name` AS `stage_name`,`bs`.`board` AS `board`,`bs`.`status` AS `stage_status`,`bs`.`last_updated` AS `stage_last_updated`,`bs`.`start_time` AS `stage_start_time`,`bs`.`finish_time` AS `stage_finish_time`,`bs`.`final` AS `stage_final`,`b`.`id` AS `build_id`,`b`.`last_updated` AS `build_last_updated`,`b`.`master_build_id` AS `master_build_id`,`b`.`buildbot_generation` AS `buildbot_generation`,`b`.`builder_name` AS `builder_name`,`b`.`waterfall` AS `waterfall`,`b`.`build_number` AS `build_number`,`b`.`build_config` AS `build_config`,`b`.`bot_hostname` AS `bot_hostname`,`b`.`start_time` AS `build_start_time`,`b`.`finish_time` AS `build_finish_time`,`b`.`status` AS `build_status`,`b`.`build_type` AS `build_type`,`b`.`chrome_version` AS `chrome_version`,`b`.`milestone_version` AS `milestone_version`,`b`.`platform_version` AS `platform_version`,`b`.`full_version` AS `full_version`,`b`.`sdk_version` AS `sdk_version`,`b`.`toolchain_url` AS `toolchain_url`,`b`.`final` AS `build_final`,`b`.`metadata_url` AS `metadata_url`,`b`.`summary` AS `summary`,`b`.`deadline` AS `deadline` from ((`failureTable` `f` join `buildStageTable` `bs` on((`f`.`build_stage_id` = `bs`.`id`))) join `buildTable` `b` on((`bs`.`build_id` = `b`.`id`))) */;
diff --git a/lib/cidb_integration_test.py b/lib/cidb_integration_test.py
index 913e9fb1f..528cda862 100644
--- a/lib/cidb_integration_test.py
+++ b/lib/cidb_integration_test.py
@@ -174,9 +174,9 @@ def GetTestDataSeries(test_data_path):
 class DataSeries0Test(CIDBIntegrationTest):
   """Simulate a set of 630 master/slave CQ builds."""
 
-  def testCQWithSchema32(self):
-    """Run the CQ test with schema version 32."""
-    self._PrepareFreshDatabase(32)
+  def testCQWithSchema39(self):
+    """Run the CQ test with schema version 39."""
+    self._PrepareFreshDatabase(39)
     self._runCQTest()
 
   def _runCQTest(self):
@@ -429,7 +429,7 @@ class BuildTableTest(CIDBIntegrationTest):
 
   def testInsertWithDeadline(self):
     """Test deadline setting/querying API."""
-    self._PrepareFreshDatabase(32)
+    self._PrepareFreshDatabase(39)
     bot_db = cidb.CIDBConnection(TEST_DB_CRED_BOT)
 
     build_id = bot_db.InsertBuild('build_name',
@@ -463,7 +463,7 @@ class BuildTableTest(CIDBIntegrationTest):
   def testExtendDeadline(self):
     """Test that a deadline in the future can be extended."""
 
-    #self._PrepareFreshDatabase(32)
+    self._PrepareFreshDatabase(39)
     bot_db = cidb.CIDBConnection(TEST_DB_CRED_BOT)
 
     build_id = bot_db.InsertBuild('build_name',
@@ -506,7 +506,7 @@ class DataSeries1Test(CIDBIntegrationTest):
     # Migrate db to specified version. As new schema versions are added,
     # migrations to later version can be applied after the test builds are
     # simulated, to test that db contents are correctly migrated.
-    self._PrepareFreshDatabase(32)
+    self._PrepareFreshDatabase(39)
 
     bot_db = cidb.CIDBConnection(TEST_DB_CRED_BOT)