add initial migration support (busted atm)

3 files changed, 342 insertions, 0 deletions

diff --git a/src/guff/database-updater.cr b/src/guff/database-updater.cr
new file mode 100644
index 0000000..e06aff3
--- /dev/null
+++ b/src/guff/database-updater.cr
@@ -0,0 +1,131 @@
+require "./database"
+
+module Guff
+  class DatabaseUpdater
+    CURRENT_VERSION = 2_i64
+
+    SQL = {
+      :get_version => "
+        SELECT value
+          FROM metadata
+         WHERE name = 'version'
+      ",
+    }
+
+    MIGRATION_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 
+          )
+        }, %{
+          INSERT INTO metadata(name, value) VALUES ('version', '1')
+        }, %{
+          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 ON tags(name)
+        }, %{
+          CREATE INDEX ON posts(slug)
+        }, %{
+          UPDATE metadata
+             SET value = '2'
+           WHERE name = 'version'
+        }],
+      },
+    } 
+
+    def self.run(path, config)
+      new(path, config).run
+    end
+
+    def initialize(
+      @path   : String,
+      @config : Config
+    )
+      @db = Database.new(path)
+    end
+
+    def run
+      version = 0_i64
+      
+      if @db.table_exists?("metadata")
+        version = @db.one(SQL[:get_version]) as Int64
+      end
+
+      puts "versions: db = %d, code = %d" % [
+        version,
+        CURRENT_VERSION,
+      ]
+
+      if version < CURRENT_VERSION
+        (version + 1).upto(CURRENT_VERSION) do |v|
+          migrate_to(v)
+        end
+      end
+    end
+
+    private def migrate_to(version : Int64)
+      id = MIGRATION_IDS[version]
+      m = MIGRATIONS[id]
+      puts "migrating: %s" % [id]
+
+      (m[:sql] as Array(String)).each do |sql|
+        @db.query(sql)
+      end
+    end
+
+    private def get_version
+      @db.one(:get_version) as Int64
+    end
+  end
+end
diff --git a/src/guff/database.cr b/src/guff/database.cr
index de11eb2..b21a26a 100644
--- a/src/guff/database.cr
+++ b/src/guff/database.cr
@@ -2,6 +2,205 @@ require "sqlite3"
 
 module Guff
   class Database < ::SQLite3::Database
+    SQL = {
+      table_exists: "
+        SELECT tbl_name
+
+          FROM sqlite_master
+
+         WHERE type = 'table'
+           AND tbl_name = ?
+      ",
+    }
+
     # TODO (add table_exists?)
+    def table_exists?(table_name : String)
+      one(SQL[:table_exists], [table_name]) == table_name
+    end
+
+    def one(
+      sql     : String
+    )
+      r = nil
+
+      bind(sql).execute do |rs|
+        if rs.next
+          # FIXME
+          r = rs[0]
+        end
+      end
+
+      # return result
+      r
+    end
+
+    def one(
+      sql     : String,
+      args    : Array(String)
+    )
+      r = nil
+
+      bind(sql, args).execute do |rs|
+        if rs.next
+          # FIXME
+          r = rs[0]
+        end
+      end
+
+      # return result
+      r
+    end
+
+    def one(
+      sql     : String,
+      args    : Hash(String, String),
+    )
+      r = nil
+
+      bind(sql, args).execute do |rs|
+        if rs.next
+          # FIXME
+          r = rs[0]
+        end
+      end
+
+      # return result
+      r
+    end
+
+    def row(sql : String)
+      r = nil
+
+      # exec query
+      bind(sql).execute do |rs|
+        if rs.next
+          r = to_row(rs)
+        end
+      end
+
+      # return result
+      r
+    end
+
+    def row(
+      sql   : String, 
+      args  : Array(String)
+    )
+      r = nil
+
+      # exec query
+      bind(sql, args).execute do |rs|
+        if rs.next
+          r = to_row(rs)
+        end
+      end
+
+      # return result
+      r
+    end
+
+    def row(
+      sql   : String, 
+      args  : Hash(String, String)
+    )
+      r = nil
+
+      # exec query
+      bind(sql, args).execute do |rs|
+        if rs.next
+          r = to_row(rs)
+        end
+      end
+
+      # return result
+      r
+    end
+
+    def all(
+      sql     : String,
+      args    : Array(String),
+      &block  : Proc(Hash(String, ::SQLite3::Value), Nil) \
+    )
+      # build statement
+      bind(sql, args).execute do |rs|
+        # walk results
+        while rs.next
+          # build row and pass it to callback
+          block.call(to_row(rs))
+        end
+      end
+
+      nil
+    end
+
+    def all(
+      sql     : String,
+      args    : Hash(String, String),
+      &block  : Proc(Hash(String, ::SQLite3::Value), Nil) \
+    )
+      bind(sql, args).execute do |rs|
+        # walk results
+        while rs.next
+          # build row and pass it to callback
+          block.call(to_row(rs))
+        end
+      end
+
+      # close statement, return nil
+      nil
+    end
+
+    def transaction(&block)
+      query("BEGIN")
+      block.call(self)
+      query("COMMIT")
+    end
+
+    private def bind(sql : String)
+      prepare(sql)
+    end
+
+    private def bind(
+      sql     : String,
+      args    : Array(String)?
+    )
+      # build statement
+      st = prepare(sql)
+
+      # build args
+      if args.size > 0
+        args.each_with_index do |val, i|
+          st[i + 1] = val
+        end
+      end
+
+      # return result
+      st
+    end
+
+    private def bind(
+      sql     : String,
+      args    : Hash(String, String)?
+    )
+      # build statement
+      if args.size > 0
+        st = prepare(sql)
+      end
+
+      # bind args
+      st[0] = args
+
+      # return result
+      st
+    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
+    end
   end
 end
diff --git a/src/guff/model.cr b/src/guff/model.cr
index 0bc0e78..1df437b 100644
--- a/src/guff/model.cr
+++ b/src/guff/model.cr
@@ -1,3 +1,6 @@
+require "./database"
+require "./database-updater"
+
 module Guff
   class Model
     getter :config
@@ -6,7 +9,16 @@ module Guff
     def initialize(@config : Config)
       # create site database
       db_path = "%s/site.db" % [config["data"]]
+
+      # update database (if necessary)
+      update_db(db_path)
+
+      # open db
       @db = Database.new(db_path)
     end
+
+    private def update_db(path)
+      DatabaseUpdater.run(path, @config)
+    end
   end
 end