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.

3 files changed, 44 insertions, 0 deletions

diff --git a/server/store/migrations/migrations_test.go b/server/store/migrations/migrations_test.go
index ad2259b..1283668 100644
--- a/server/store/migrations/migrations_test.go
+++ b/server/store/migrations/migrations_test.go
@@ -75,6 +75,7 @@ func runMigrations(t *testing.T, db *sql.DB, directory string) {
 	assert.NoError(t, err)
 	// Verify that reversing migrations works
 	n, err = migrate.Exec(db, directory, m, migrate.Down)
+	assert.NoError(t, err)
 	assert.Len(t, files, n)
 }
 
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`);
diff --git a/server/store/migrations/sqlite3/20180807224200_new_primary_key.sql b/server/store/migrations/sqlite3/20180807224200_new_primary_key.sql
new file mode 100644
index 0000000..40f333c
--- /dev/null
+++ b/server/store/migrations/sqlite3/20180807224200_new_primary_key.sql
@@ -0,0 +1,32 @@
+-- +migrate Up
+CREATE TABLE `issued_certs_new` (
+  `id` INTEGER PRIMARY KEY,
+  `key_id` varchar(255) UNIQUE NOT NULL,
+  `principals` varchar(255) DEFAULT '[]',
+  `created_at` datetime DEFAULT '1970-01-01 00:00:01',
+  `expires_at` datetime DEFAULT '1970-01-01 00:00:01',
+  `revoked` tinyint(1) DEFAULT '0',
+  `raw_key` text
+);
+INSERT INTO `issued_certs_new` (key_id, principals, created_at, expires_at, revoked, raw_key)
+  SELECT key_id, principals, created_at, expires_at, revoked, raw_key FROM `issued_certs`;
+DROP TABLE `issued_certs`;
+ALTER TABLE `issued_certs_new` RENAME TO `issued_certs`;
+CREATE INDEX `idx_expires_at` ON `issued_certs` (`expires_at`);
+
+-- +migrate Down
+CREATE TABLE IF NOT EXISTS `issued_certs_old` (
+  `key_id` varchar(255) NOT NULL,
+  `principals` varchar(255) DEFAULT "[]",
+  `created_at` datetime DEFAULT '1970-01-01 00:00:01',
+  `expires_at` datetime DEFAULT '1970-01-01 00:00:01',
+  `revoked` tinyint(1) DEFAULT 0,
+  `raw_key` text,
+  PRIMARY KEY (`key_id`)
+);
+
+INSERT INTO `issued_certs_old` (key_id, principals, created_at, expires_at, revoked, raw_key)
+  SELECT key_id, principals, created_at, expires_at, revoked, raw_key FROM `issued_certs`;
+DROP TABLE `issued_certs`;
+ALTER TABLE `issued_certs_old` RENAME TO `issued_certs`;
+CREATE INDEX `idx_expires_at` ON `issued_certs` (`expires_at`);
\ No newline at end of file