From d3fa3ce3712e6a46b1e9d1a2f3c8ac0ca6d5e25b Mon Sep 17 00:00:00 2001
From: Teddy Wing
Date: Fri, 9 Nov 2018 02:39:27 +0100
Subject: Add migrations for `purchasers` table

A database table to hold purchaser information so we can re-generate
licenses in case purchasers lose their license keys.

Needed to use a trigger to update the `updated_at` field on `UPDATE` as
you can't do `ON UPDATE UTC_TIMESTAMP()` in the column definition

> You can not specify UTC_TIMESTAMP as default to specify automatic
> properties

(https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp/21619#21619)

Weirdly, the trigger isn't working when applying the migration with

    $ migrant apply

but it is working when running

    $ mysql -u user dome_key < migrations/20181108234653_create-purchasers/up.sql

Not sure what's going on there, but 'migrant' appears to have trouble
realising there are errors coming back from MySQL and executes the
migrations regardless. It also doesn't print syntax error messages from
MySQL which is very inconvenient. Migrant seemed to be the most advanced
migration CLI on crates.io, and I was hoping to use a Rust program for
this, but for simplicity, I'm thinking I'll have to go with a different
migration runner. Considering https://github.com/golang-migrate/migrate.
---
 .../20181108234653_create-purchasers/down.sql       |  6 ++++++
 .../20181108234653_create-purchasers/up.sql         | 21 +++++++++++++++++++++
 2 files changed, 27 insertions(+)
 create mode 100644 license-generator/migrations/20181108234653_create-purchasers/down.sql
 create mode 100644 license-generator/migrations/20181108234653_create-purchasers/up.sql

(limited to 'license-generator')

diff --git a/license-generator/migrations/20181108234653_create-purchasers/down.sql b/license-generator/migrations/20181108234653_create-purchasers/down.sql
new file mode 100644
index 0000000..d17db9c
--- /dev/null
+++ b/license-generator/migrations/20181108234653_create-purchasers/down.sql
@@ -0,0 +1,6 @@
+BEGIN;
+
+DROP TRIGGER purchasers_updated_at;
+DROP TABLE purchasers;
+
+COMMIT;
diff --git a/license-generator/migrations/20181108234653_create-purchasers/up.sql b/license-generator/migrations/20181108234653_create-purchasers/up.sql
new file mode 100644
index 0000000..14927c2
--- /dev/null
+++ b/license-generator/migrations/20181108234653_create-purchasers/up.sql
@@ -0,0 +1,21 @@
+BEGIN;
+
+CREATE TABLE purchasers (
+    id INT AUTO_INCREMENT PRIMARY KEY,
+    name VARCHAR(255) NOT NULL,
+    email VARCHAR(255) NOT NULL,
+    secret VARCHAR(255),
+    created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
+    updated_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP()
+);
+
+DELIMITER $$
+    CREATE TRIGGER purchasers_updated_at
+    BEFORE UPDATE
+    ON purchasers FOR EACH ROW
+    BEGIN
+        SET NEW.updated_at = UTC_TIMESTAMP();
+    END$$
+DELIMITER ;
+
+COMMIT;
-- 
cgit v1.2.3