diff options
| -rw-r--r-- | src/guff/database-updater.cr | 131 | ||||
| -rw-r--r-- | src/guff/database.cr | 199 | ||||
| -rw-r--r-- | src/guff/model.cr | 12 |
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 |