refactor migrations

1 files changed, 103 insertions, 75 deletions

diff --git a/src/guff/database-updater.cr b/src/guff/database-updater.cr
index 42686be..daa3ebd 100644
--- a/src/guff/database-updater.cr
+++ b/src/guff/database-updater.cr
@@ -20,73 +20,101 @@ module Guff
       ",
     }
 
-    IDS = %w{
-      0-null
-      1-create-tables
-      2-create-indices
-    }
-
-    MIGRATIONS = {
-      "1-create-tables": {
-        backup: false,
-
-        sql: [%{
-          CREATE TABLE metadata (
-            name    TEXT PRIMARY KEY,
-            value   TEXT NOT NULL
-          )
-        }, %{
-          CREATE TABLE tags (
-            tag_id      INTEGER PRIMARY KEY,
-            name        TEXT UNIQUE NOT NULL
-          )
-        }, %{
-          CREATE TABLE posts (
-            -- unique id
-            post_id     INTEGER PRIMARY KEY,
-
-            -- false if this post has been deleted
-            is_active   BOOLEAN NOT NULL
-                        DEFAULT true,
-
-            -- when this post was created
-            created_at  TIMESTAMP WITH TIME ZONE
-                        NOT NULL DEFAULT CURRENT_TIMESTAMP,
-
-            -- when this post was posted
-            -- (that is, the draft status was removed)
-            posted_at   TIMESTAMP WITH TIME ZONE
-                        NOT NULL DEFAULT CURRENT_TIMESTAMP,
-
-            -- title of post
-            name        TEXT NOT NULL
-                        CHECK (LENGTH(name) > 0),
-
-            -- slug of post (url fragment)
-            slug        TEXT NOT NULL
-                        CHECK (LENGTH(slug) > 0),
-
-            -- body (raw text before filters)
-            body        TEXT NOT NULL
-                        CHECK (LENGTH(body) > 0),
-
-            -- generated html (after filters)
-            html        TEXT NOT NULL
-                        CHECK (LENGTH(html) > 0)
-          )
-        }],
-      },
-
-      "2-create-indices": {
-        backup: false,
-
-        sql: [%{
-          CREATE INDEX in_tags_name ON tags(name)
-        }, %{
-          CREATE INDEX in_posts_slug ON posts(slug)
-        }],
-      },
-    }
+    MIGRATIONS = [{
+      id: "0-null",
+      backup: false,
+      sql: %w{},
+    }, {
+      id: "1-create-tags-and-posts",
+      backup: false,
+
+      sql: [%{
+        CREATE TABLE metadata (
+          name    TEXT PRIMARY KEY,
+          value   TEXT NOT NULL
+        )
+      }, %{
+        CREATE TABLE sites (
+          -- site id
+          site_id     INTEGER PRIMARY KEY,
+
+          -- name of site
+          name        TEXT UNIQUE NOT NULL
+                      CHECK (LENGTH(name) > 0),
+
+          -- is this the default site?
+          is_default  BOOLEAN NOT NULL DEFAULT false
+        )
+      }, %{
+        CREATE TABLE posts (
+          -- unique id
+          post_id     INTEGER PRIMARY KEY,
+
+          site_id     INTEGER NOT NULL
+                      REFERENCES sites(site_id),
+
+          -- false if this post has been deleted
+          is_active   BOOLEAN NOT NULL
+                      DEFAULT true,
+
+          -- when this post was created
+          created_at  TIMESTAMP WITH TIME ZONE
+                      NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+          -- when this post was posted
+          -- (that is, the draft status was removed)
+          posted_at   TIMESTAMP WITH TIME ZONE
+                      NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+          -- title of post
+          name        TEXT NOT NULL
+                      CHECK (LENGTH(name) > 0),
+
+          -- slug of post (url fragment)
+          slug        TEXT NOT NULL
+                      CHECK (LENGTH(slug) > 0),
+
+          -- body (raw text before filters)
+          body        TEXT NOT NULL
+                      CHECK (LENGTH(body) > 0),
+
+          -- generated html (after filters)
+          html        TEXT NOT NULL
+                      CHECK (LENGTH(html) > 0)
+        )
+      }, %{
+        CREATE INDEX in_posts_slug ON posts(slug)
+      }],
+    }, {
+      id: "2-create-indices",
+      backup: false,
+
+      sql: [%{
+        CREATE TABLE tags (
+          tag_id      INTEGER PRIMARY KEY,
+          name        TEXT UNIQUE NOT NULL
+        )
+      }, %{
+        CREATE INDEX in_tags_name ON tags(name)
+      }],
+    }, {
+      id: "3-create-domains",
+      backup: false,
+
+      sql: [%{
+        CREATE TABLE domains (
+          -- domain
+          domain      TEXT PRIMARY KEY CHECK (
+                        LENGTH(domain) > 0 AND
+                        domain = LOWER(domain)
+                      ),
+
+          -- site id
+          site_id     INTEGER NOT NULL
+                      REFERENCES sites(site_id)
+        )
+      }],
+    }]
 
     def self.run(path, config)
       new(path, config).run
@@ -101,15 +129,16 @@ module Guff
 
     def run
       # get version from database
-      version = @db.table_exists?("metadata") ? get_version : 0_i32
+      db_version = @db.table_exists?("metadata") ? get_version : 0_i32
+      app_version = MIGRATIONS.size - 1
 
-      if version < CURRENT_VERSION
+      if db_version < app_version
         puts "starting db migration (db = %d, app = %d)" % [
-          version,
-          CURRENT_VERSION,
+          db_version,
+          app_version
         ]
 
-        (version + 1).upto(CURRENT_VERSION) do |v|
+        (db_version + 1).upto(app_version) do |v|
           migrate_to(v)
         end
       end
@@ -117,9 +146,8 @@ module Guff
 
     private def migrate_to(version)
       # map version number to migration id
-      id = IDS[version]
-      m = MIGRATIONS[id]
-      puts "  * migration: %s" % [id]
+      m = MIGRATIONS[version]
+      puts "  * migration: %s" % [m[:id]]
 
       @db.transaction do
         # exec queries