From 1fe6de5ab9f97c027047743e10a0ce8676525916 Mon Sep 17 00:00:00 2001
From: Paul Duncan <pabs@pablotron.org>
Date: Sun, 6 Mar 2016 16:12:21 -0500
Subject: db and migration fixes

---
 src/guff/database-updater.cr | 71 ++++++++++++++++++++++++++------------------
 1 file changed, 42 insertions(+), 29 deletions(-)

(limited to 'src/guff/database-updater.cr')

diff --git a/src/guff/database-updater.cr b/src/guff/database-updater.cr
index 9a7bf97..b7a8af6 100644
--- a/src/guff/database-updater.cr
+++ b/src/guff/database-updater.cr
@@ -5,14 +5,22 @@ module Guff
     CURRENT_VERSION = 2_i64
 
     SQL = {
-      :get_version => "
+      get_version: "
         SELECT value
           FROM metadata
          WHERE name = 'version'
       ",
+
+      set_version_delete: "
+        DELETE FROM metadata where name = 'version'
+      ",
+
+      set_version_insert: "
+        INSERT INTO metadata(name, value) VALUES ('version', ?)
+      ",
     }
 
-    MIGRATION_IDS = %w{
+    IDS = %w{
       0-null
       1-create-tables
       2-create-indices
@@ -21,13 +29,12 @@ module Guff
     MIGRATIONS = {
       "1-create-tables": {
         backup: false,
+
         sql: [%{
           CREATE TABLE metadata (
             name    TEXT PRIMARY KEY,
-            value   TEXT NOT NULL 
-          );
-        }, %{
-          INSERT INTO metadata(name, value) VALUES ('version', '1')
+            value   TEXT NOT NULL
+          )
         }, %{
           CREATE TABLE tags (
             tag_id      INTEGER PRIMARY KEY,
@@ -39,7 +46,7 @@ module Guff
             post_id     INTEGER PRIMARY KEY,
 
             -- false if this post has been deleted
-            is_active   BOOLEAN NOT NULL 
+            is_active   BOOLEAN NOT NULL
                         DEFAULT true,
 
             -- when this post was created
@@ -53,15 +60,15 @@ module Guff
 
             -- title of post
             name        TEXT NOT NULL
-                        CHECK (LENGTH(name) > 0)
+                        CHECK (LENGTH(name) > 0),
 
             -- slug of post (url fragment)
             slug        TEXT NOT NULL
-                        CHECK (LENGTH(slug) > 0)
+                        CHECK (LENGTH(slug) > 0),
 
             -- body (raw text before filters)
             body        TEXT NOT NULL
-                        CHECK (LENGTH(body) > 0)
+                        CHECK (LENGTH(body) > 0),
 
             -- generated html (after filters)
             html        TEXT NOT NULL
@@ -72,17 +79,14 @@ module Guff
 
       "2-create-indices": {
         backup: false,
+
         sql: [%{
-          CREATE INDEX ON tags(name)
+          CREATE INDEX in_tags_name ON tags(name)
         }, %{
-          CREATE INDEX ON posts(slug)
-        }, %{
-          UPDATE metadata
-             SET value = '2'
-           WHERE name = 'version'
+          CREATE INDEX in_posts_slug ON posts(slug)
         }],
       },
-    } 
+    }
 
     def self.run(path, config)
       new(path, config).run
@@ -92,17 +96,14 @@ module Guff
       @path   : String,
       @config : Config
     )
-      @db = Database.new(path)
+      @db = Database.new(@path)
     end
 
     def run
-      version = 0_i64
-      
-      if @db.table_exists?("metadata")
-        version = @db.one(SQL[:get_version]) as Int64
-      end
+      # get version from database
+      version = @db.table_exists?("metadata") ? get_version : 0_i32
 
-      puts "versions: db = %d, code = %d" % [
+      puts "versions: db = %d, app = %d" % [
         version,
         CURRENT_VERSION,
       ]
@@ -114,18 +115,30 @@ module Guff
       end
     end
 
-    private def migrate_to(version : Int64)
-      id = MIGRATION_IDS[version]
+    private def migrate_to(version)
+      # map version number to migration id
+      id = IDS[version]
       m = MIGRATIONS[id]
       puts "migrating: %s" % [id]
 
-      (m[:sql] as Array(String)).each do |sql|
-        @db.query(sql)
+      @db.transaction do
+        # exec queries
+        (m[:sql] as Array(String)).each do |sql|
+          @db.query(sql)
+        end
+
+        # update database version
+        set_version(version)
       end
     end
 
     private def get_version
-      @db.one(:get_version) as Int64
+      (@db.one(SQL[:get_version]) || 0).to_i
+    end
+
+    private def set_version(version)
+      @db.query(SQL[:set_version_delete])
+      @db.query(SQL[:set_version_insert], [version])
     end
   end
 end
-- 
cgit v1.2.3