From d836a4496de7b24a9d3317e274800d35053a04f6 Mon Sep 17 00:00:00 2001 From: Niall Sheridan Date: Sun, 5 Aug 2018 23:00:58 +0100 Subject: Manage db schema with rubenv/sql-migrate It's currently hard to make changes to the database schema. Use sql-migrate to make incremental changes. Stop hard-coding the database name (the default is still "certs" for backward-compatibility) The `automigrate()` function will automatically run pending migrations. Use a different migration directory per database driver. This carries a cost of duplication, but is easier than creating migrations which will cleanly execute in both SQLite and MySQL. Migrations are shipped using the packr utility. --- .../sqlite3/20180626224600_create_issued_certs.sql | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 server/store/migrations/sqlite3/20180626224600_create_issued_certs.sql (limited to 'server/store/migrations/sqlite3') diff --git a/server/store/migrations/sqlite3/20180626224600_create_issued_certs.sql b/server/store/migrations/sqlite3/20180626224600_create_issued_certs.sql new file mode 100644 index 0000000..c5b80e0 --- /dev/null +++ b/server/store/migrations/sqlite3/20180626224600_create_issued_certs.sql @@ -0,0 +1,15 @@ +-- +migrate Up +CREATE TABLE IF NOT EXISTS `issued_certs` ( + `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`) +); +CREATE INDEX `idx_expires_at` ON `issued_certs` (`expires_at`); +CREATE INDEX `idx_revoked_expires_at` ON `issued_certs` (`revoked`, `expires_at`); + +-- +migrate Down +DROP TABLE `issued_certs`; -- cgit v1.2.3