diff options
| -rw-r--r-- | src/guff/database-updater.cr | 71 | ||||
| -rw-r--r-- | src/guff/database.cr | 141 |
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 |