diff options
Diffstat (limited to 'src/guff/migrations.cr')
-rw-r--r-- | src/guff/migrations.cr | 111 |
1 files changed, 111 insertions, 0 deletions
diff --git a/src/guff/migrations.cr b/src/guff/migrations.cr new file mode 100644 index 0000000..aef1f27 --- /dev/null +++ b/src/guff/migrations.cr @@ -0,0 +1,111 @@ +require "./database" + +module Guff + MIGRATIONS = [{ + id: "0-null", + sql: %w{}, + }, { + id: "1-create-tags-and-posts", + + 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 + ) + }, %{ + INSERT INTO sites(name, is_default) VALUES ('Default', 1) + }, %{ + 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-tags", + + sql: [%{ + CREATE TABLE tags ( + tag_id INTEGER PRIMARY KEY, + name TEXT UNIQUE NOT NULL + ) + }, %{ + CREATE INDEX in_tags_name ON tags(name) + }, %{ + CREATE TABLE post_tags ( + tag_id INTEGER NOT NULL + REFERENCES tags(tag_id), + + post_id INTEGER NOT NULL + REFERENCES posts(post_id), + + UNIQUE(tag_id, post_id) + ) + }, %{ + CREATE INDEX in_post_tags_tag_id ON post_tags(tag_id) + }, %{ + CREATE INDEX in_post_tags_post_id ON post_tags(post_id) + }], + }, { + id: "3-create-domains", + + 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) + ) + }], + }] +end |