require "./database" module Guff class DatabaseUpdater CURRENT_VERSION = 2_i64 SQL = { 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', ?) ", } 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 ) }, %{ 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 in_tags_name ON tags(name) }, %{ CREATE INDEX in_posts_slug ON posts(slug) }], }, } def self.run(path, config) new(path, config).run end def initialize( @path : String, @config : Config ) @db = Database.new(@path) end def run # get version from database version = @db.table_exists?("metadata") ? get_version : 0_i32 puts "versions: db = %d, app = %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) # map version number to migration id id = IDS[version] m = MIGRATIONS[id] puts "migrating: %s" % [id] @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(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