diff options
Diffstat (limited to 'src/guff/migrations.cr')
| -rw-r--r-- | src/guff/migrations.cr | 63 |
1 files changed, 40 insertions, 23 deletions
diff --git a/src/guff/migrations.cr b/src/guff/migrations.cr index 2d08c9e..4763929 100644 --- a/src/guff/migrations.cr +++ b/src/guff/migrations.cr @@ -5,14 +5,18 @@ module Guff id: "0-null", sql: %w{}, }, { - id: "1-create-tags-and-posts", + id: "1-create-metadata", sql: [%{ CREATE TABLE metadata ( name TEXT PRIMARY KEY, value TEXT NOT NULL ) - }, %{ + }], + }, { + id: "2-create-sites", + + sql: [%{ CREATE TABLE sites ( -- site id site_id INTEGER PRIMARY KEY, @@ -27,6 +31,32 @@ module Guff }, %{ INSERT INTO sites(name, is_default) VALUES ('Default', 1) }, %{ + 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) + ) + }], + }, { + id: "3-create-posts", + + sql: [%{ + CREATE TABLE post_states ( + state INTEGER PRIMARY KEY, + name TEXT UNIQUE NOT NULL + ) + }, %{ + INSERT INTO post_states(state, name) VALUES + (0, 'draft'), + (1, 'posted'), + (2, 'deleted') + }, %{ CREATE TABLE posts ( -- unique id post_id INTEGER PRIMARY KEY, @@ -34,9 +64,8 @@ module Guff site_id INTEGER NOT NULL REFERENCES sites(site_id), - -- false if this post has been deleted - is_active BOOLEAN NOT NULL - DEFAULT true, + state INTEGER NOT NULL DEFAULT 0 + REFERENCES post_states(state), -- when this post was created created_at TIMESTAMP WITH TIME ZONE @@ -45,7 +74,7 @@ module Guff -- when this post was posted -- (that is, the draft status was removed) posted_at TIMESTAMP WITH TIME ZONE - NOT NULL DEFAULT CURRENT_TIMESTAMP, + DEFAULT NULL, -- title of post name TEXT NOT NULL @@ -64,10 +93,14 @@ module Guff CHECK (LENGTH(body) > 0) ) }, %{ + CREATE INDEX in_posts_site_id ON posts(site_id) + }, %{ CREATE INDEX in_posts_slug ON posts(slug) + }, %{ + CREATE INDEX in_posts_state ON posts(state) }], }, { - id: "2-create-tags", + id: "4-create-tags", sql: [%{ CREATE TABLE tags ( @@ -91,21 +124,5 @@ module Guff }, %{ 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 |