diff options
| -rw-r--r-- | src/guff/database-updater.cr | 178 |
1 files changed, 103 insertions, 75 deletions
diff --git a/src/guff/database-updater.cr b/src/guff/database-updater.cr index 42686be..daa3ebd 100644 --- a/src/guff/database-updater.cr +++ b/src/guff/database-updater.cr @@ -20,73 +20,101 @@ module Guff ", } - 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) - }], - }, - } + MIGRATIONS = [{ + id: "0-null", + backup: false, + sql: %w{}, + }, { + id: "1-create-tags-and-posts", + backup: false, + + sql: [%{ + CREATE TABLE metadata ( + name TEXT PRIMARY KEY, + value TEXT NOT NULL + ) + }, %{ + CREATE TABLE sites ( + -- site id + site_id INTEGER PRIMARY KEY, + + -- name of site + name TEXT UNIQUE NOT NULL + CHECK (LENGTH(name) > 0), + + -- is this the default site? + is_default BOOLEAN NOT NULL DEFAULT false + ) + }, %{ + CREATE TABLE posts ( + -- unique id + post_id INTEGER PRIMARY KEY, + + site_id INTEGER NOT NULL + REFERENCES sites(site_id), + + -- 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) + ) + }, %{ + CREATE INDEX in_posts_slug ON posts(slug) + }], + }, { + id: "2-create-indices", + backup: false, + + sql: [%{ + CREATE TABLE tags ( + tag_id INTEGER PRIMARY KEY, + name TEXT UNIQUE NOT NULL + ) + }, %{ + CREATE INDEX in_tags_name ON tags(name) + }], + }, { + id: "3-create-domains", + backup: false, + + sql: [%{ + CREATE TABLE domains ( + -- domain + domain TEXT PRIMARY KEY CHECK ( + LENGTH(domain) > 0 AND + domain = LOWER(domain) + ), + + -- site id + site_id INTEGER NOT NULL + REFERENCES sites(site_id) + ) + }], + }] def self.run(path, config) new(path, config).run @@ -101,15 +129,16 @@ module Guff def run # get version from database - version = @db.table_exists?("metadata") ? get_version : 0_i32 + db_version = @db.table_exists?("metadata") ? get_version : 0_i32 + app_version = MIGRATIONS.size - 1 - if version < CURRENT_VERSION + if db_version < app_version puts "starting db migration (db = %d, app = %d)" % [ - version, - CURRENT_VERSION, + db_version, + app_version ] - (version + 1).upto(CURRENT_VERSION) do |v| + (db_version + 1).upto(app_version) do |v| migrate_to(v) end end @@ -117,9 +146,8 @@ module Guff private def migrate_to(version) # map version number to migration id - id = IDS[version] - m = MIGRATIONS[id] - puts " * migration: %s" % [id] + m = MIGRATIONS[version] + puts " * migration: %s" % [m[:id]] @db.transaction do # exec queries |