db and migration fixes

2 files changed, 90 insertions, 122 deletions

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
diff --git a/src/guff/database.cr b/src/guff/database.cr
index e94a085..67a7a1a 100644
--- a/src/guff/database.cr
+++ b/src/guff/database.cr
@@ -4,60 +4,28 @@ module Guff
   class Database < ::SQLite3::Database
     SQL = {
       table_exists: "
-        SELECT tbl_name
+        SELECT name
 
           FROM sqlite_master
 
          WHERE type = 'table'
-           AND tbl_name = ?
+           AND name = ?
       ",
     }
 
-    # TODO (add table_exists?)
-    def table_exists?(table_name : String)
-      one(SQL[:table_exists], [table_name]) == table_name
+    def table_exists?(table : String) : Bool
+      one(SQL[:table_exists], [table]) == table
     end
 
     def one(
-      sql     : String
-    )
-      r = nil
-
-      run(sql).execute do |rs|
-        if rs.next
-          # FIXME
-          r = rs[0]
-        end
-      end
-
-      # return result
-      r
-    end
-
-    def one(
-      sql     : String,
-      args    : Array(String) | Hash(String, String)
+      sql         : String,
+      args = nil  : Array(String) | Hash(String, String) | Nil
     )
       r = nil
 
-      run(sql, args).execute do |rs|
+      run(sql, args) do |rs|
         if rs.next
-          # FIXME
-          r = rs[0]
-        end
-      end
-
-      # return result
-      r
-    end
-
-    def row(sql : String)
-      r = nil
-
-      # exec query
-      run(sql).execute do |rs|
-        if rs.next
-          r = to_row(rs)
+          r = rs[0] as String
         end
       end
 
@@ -66,16 +34,14 @@ module Guff
     end
 
     def row(
-      sql   : String, 
-      args  : Array(String) | Hash(String, String)
+      sql         : String,
+      args = nil  : Array(String) | Hash(String, String) | Nil
     )
       r = nil
 
       # exec query
-      run(sql, args).execute do |rs|
-        if rs.next
-          r = to_row(rs)
-        end
+      run(sql, args) do |rs|
+        r = to_row(rs) if rs.next
       end
 
       # return result
@@ -83,10 +49,12 @@ module Guff
     end
 
     def all(
-      sql     : String,
-      &block  : Proc(Hash(String, ::SQLite3::Value), Nil) \
+      sql         : String,
+      args = nil  : Array(String) | Hash(String, String) | Nil,
+      &block      : Proc(Hash(String, ::SQLite3::Value), Nil) \
     )
-      run(sql).execute do |rs|
+      # build statement
+      run(sql, args) do |rs|
         # walk results
         while rs.next
           # build row and pass it to callback
@@ -94,79 +62,66 @@ module Guff
         end
       end
 
-      # close statement, return nil
       nil
     end
 
-    def all(
-      sql     : String,
-      args    : Array(String) | Hash(String, String),
-      &block  : Proc(Hash(String, ::SQLite3::Value), Nil) \
+    def query(
+      sql     : String
     )
-      # build statement
-      run(sql, args).execute do |rs|
-        # walk results
-        while rs.next
-          # build row and pass it to callback
-          block.call(to_row(rs))
-        end
+      run(sql, nil) do |rs|
+        # make sure query executes
+        rs.next
+        nil
       end
+    end
 
-      nil
+    #
+    # NOTE: if you pass a block, be sure to call rs.next at least once,
+    # or the query will _not_ execute!!!
+    #
+    def query(
+      sql         : String,
+      args = nil  : Array(String) | Hash(String, String) | Nil,
+      &block      : Proc(::SQLite3::ResultSet, Nil) \
+    )
+      run(sql, args, &block)
     end
 
     def transaction(&block)
       query("BEGIN")
-      block.call(self)
+      block.call
       query("COMMIT")
     end
 
-    private def run(sql : String)
-      # build statement
-      prepare(sql)
-    end
-
     private def run(
       sql     : String,
-      args    : Array(String)?
+      args    : Hash(String, String),
+      &block  : Proc(::SQLite3::ResultSet, Nil) \
     )
-      # build statement
-      st = prepare(sql)
-
-      # bind args
-      if args && args.size > 0
-        args.each_with_index do |val, i|
-          st[i + 1] = val
-        end
-      end
-
-      # return result
-      st
+      run(sql, [args], &block)
     end
 
     private def run(
-      sql     : String,
-      args    : Hash(String, String)?
+      sql         : String,
+      args = nil  : Array(String | Hash(String, String))?,
+      &block      : Proc(::SQLite3::ResultSet, Nil) \
     )
       # build statement
       st = prepare(sql)
 
-      # bind args
+      # exec and close statement
       if args && args.size > 0
-        st[0] = args
+        st.execute(args, &block)
+      else
+        st.execute(&block)
       end
 
       # return result
-      st
+      nil
     end
 
-    private def to_row(rs : SQLite3::ResultSet) : Hash(String, ::SQLite3::Value)
-      0.upto(rs.column_count).inject(
-        {} of String => ::SQLite3::Value
-      ) do |r, i|
-        r[rs.columns[i]] = r[i]
-        r
-      end
+    private def to_row(rs)
+      Hash(String, ::SQLite3::Value).zip(rs.columns, rs.to_a)
     end
   end
 end