From 60d005f8d174d21162cab2b029f74cfe2925acab Mon Sep 17 00:00:00 2001
From: Niall Sheridan <nsheridan@gmail.com>
Date: Tue, 7 Aug 2018 23:43:23 +0100
Subject: Change the primary key on the issued_certs table

In retrospect a primary key that has no relation to the certificate is preferred to using the certificate KeyID. The KeyID is also very large for a primary index.
This is a moderately tricky migration, especially for SQLite which has no means of altering the table in this fashion - it involves creating the new table and copying the data.
Order of commands also matters - index names are global in SQLite, so the `idx_expires_at` index needs to be created at the correct stages.

For MySQL migration the necessary steps are run as a single alter statement to minimise the risk of leaving the migration in an incomplete state if anything aborts.

When tested on a table with 250,000 rows (MySQL 5.7) the migration took 3 seconds to complete. As certificates will be requested infrequently the risk of prolonged locking is minimal.
---
 .../store/migrations/mysql/20180807224200_new_primary_key.sql | 11 +++++++++++
 1 file changed, 11 insertions(+)
 create mode 100644 server/store/migrations/mysql/20180807224200_new_primary_key.sql

(limited to 'server/store/migrations/mysql/20180807224200_new_primary_key.sql')

diff --git a/server/store/migrations/mysql/20180807224200_new_primary_key.sql b/server/store/migrations/mysql/20180807224200_new_primary_key.sql
new file mode 100644
index 0000000..ed6a3c2
--- /dev/null
+++ b/server/store/migrations/mysql/20180807224200_new_primary_key.sql
@@ -0,0 +1,11 @@
+-- +migrate Up
+ALTER TABLE `issued_certs`
+  DROP PRIMARY KEY,
+  ADD COLUMN `id` INT PRIMARY KEY AUTO_INCREMENT FIRST,
+  ADD UNIQUE INDEX `idx_key_id` (`key_id`);
+
+-- +migrate Down
+ALTER TABLE `issued_certs`
+  DROP PRIMARY KEY,
+  DROP COLUMN `id`,
+  ADD PRIMARY KEY (`key_id`);
-- 
cgit v1.2.3

