diff options
Diffstat (limited to 'data/init.yaml')
| -rw-r--r-- | data/init.yaml | 259 |
1 files changed, 259 insertions, 0 deletions
diff --git a/data/init.yaml b/data/init.yaml new file mode 100644 index 0000000..2afb808 --- /dev/null +++ b/data/init.yaml @@ -0,0 +1,259 @@ +--- +sql: + init: + - | + CREATE TABLE sites ( + site_id INTEGER PRIMARY KEY, + + name TEXT UNIQUE NOT NULL + CHECK (LENGTH(name) > 0), + + is_active BOOLEAN NOT NULL DEFAULT false, + + is_default BOOLEAN NOT NULL DEFAULT false + ) + + - | + INSERT INTO sites(site_id, name, is_active, is_default) VALUES + (1, 'default', 1, 1) + + - | + CREATE TABLE site_domains ( + site_id INTEGER NOT NULL + REFERENCES sites(site_id), + + domain TEXT UNIQUE NOT NULL CHECK ( + LENGTH(domain) > 0 AND + domain = LOWER(domain) AND + domain NOT LIKE '% %' + ) + ) + + - | + CREATE TABLE roles ( + role_id INTEGER PRIMARY KEY, + + -- internal role name + role TEXT UNIQUE NOT NULL CHECK ( + LENGTH(role) > 0 AND + role = LOWER(role) + ), + + -- user-visible role name + name TEXT UNIQUE NOT NULL + CHECK (LENGTH(name) > 0), + + sort INTEGER UNIQUE NOT NULL + ) + + - | + INSERT INTO roles(role_id, role, name, sort) VALUES + (1, 'viewer', 'Viewer', 1), + (2, 'editor', 'Editor', 2), + (3, 'admin', 'Admin', 3) + + - | + CREATE TABLE users ( + user_id INTEGER PRIMARY KEY, + + role_id INTEGER NOT NULL + REFERENCES roles(role_id), + + name TEXT UNIQUE NOT NULL, + email TEXT UNIQUE NOT NULL CHECK ( + LENGTH(email) > 0 AND + email LIKE '%@%' + ), + + password TEXT NOT NULL DEFAULT '', + + is_active BOOLEAN NOT NULL DEFAULT false + ) + + - | + CREATE TABLE states ( + state_id INTEGER PRIMARY KEY, + + -- internal state name + state TEXT UNIQUE NOT NULL CHECK ( + LENGTH(state) > 0 AND + state = LOWER(state) + ), + + icon TEXT UNIQUE NOT NULL CHECK ( + LENGTH(state) > 0 AND + state = LOWER(state) + ), + + -- user-visible state name + name TEXT UNIQUE NOT NULL + CHECK (LENGTH(name) > 0), + + sort INTEGER UNIQUE NOT NULL + ) + + - | + INSERT INTO states(state_id, state, icon, name, sort) VALUES + (1, 'draft', 'fa-wrench', 'Draft', 1), + (2, 'public', 'fa-bullhorn', 'Public', 2), + (3, 'deleted', 'fa-trash', 'Deleted', 3) + + - | + CREATE TABLE posts ( + post_id INTEGER PRIMARY KEY, + + site_id INTEGER NOT NULL + REFERENCES sites(site_id), + + created_at TIMESTAMP WITH TIME ZONE NOT NULL + DEFAULT CURRENT_TIMESTAMP, + + created_by INTEGER NOT NULL + REFERENCES users(user_id), + + state_id INTEGER NOT NULL + REFERENCES states(state_id), + + posted_at TIMESTAMP WITH TIME ZONE, + expires_at TIMESTAMP WITH TIME ZONE, + + name TEXT NOT NULL DEFAULT '', + + slug TEXT NOT NULL DEFAULT '' CHECK ( + slug NOT LIKE '% %' AND + slug = LOWER(slug) + ), + + slug_lock BOOLEAN NOT NULL DEFAULT 1, + + body TEXT NOT NULL DEFAULT '' + ) + + - | + CREATE INDEX in_posts_site_id ON posts(site_id) + + - | + CREATE VIRTUAL TABLE posts_fts USING fts4( + name, + slug, + body + ) + + - | + CREATE TABLE blogs ( + post_id INTEGER PRIMARY KEY + REFERENCES posts(post_id) + ) + + - | + CREATE TABLE projects ( + post_id INTEGER PRIMARY KEY + REFERENCES posts(post_id), + + repo_url TEXT NOT NULL DEFAULT '' + CHECK (repo_url NOT LIKE '% %') + ) + + - | + CREATE TABLE layouts ( + layout_id INTEGER PRIMARY KEY, + + -- internal layout name + layout TEXT UNIQUE NOT NULL CHECK ( + LENGTH(layout) > 0 AND + layout = LOWER(layout) + ), + + -- user-visible layout name + layout_name TEXT UNIQUE NOT NULL + CHECK (LENGTH(layout_name) > 0), + + is_default BOOLEAN NOT NULL + ) + + - | + INSERT INTO layouts(layout_id, layout, layout_name, is_default) VALUES + (1, 'blank', 'Blank', 0), + (2, 'default', 'Default', 1) + + - | + CREATE TABLE pages ( + post_id INTEGER PRIMARY KEY + REFERENCES posts(post_id), + + layout_id INTEGER NOT NULL + REFERENCES layouts(layout_id) + ) + + - | + CREATE TABLE sessions ( + id TEXT PRIMARY KEY, + + created_at TIMESTAMP WITH TIME ZONE NOT NULL + DEFAULT CURRENT_TIMESTAMP, + + updated_at TIMESTAMP WITH TIME ZONE NOT NULL + DEFAULT CURRENT_TIMESTAMP, + + data TEXT NOT NULL + ) + + add_user: | + INSERT INTO users(name, email, password, role_id, is_active) VALUES + (?, ?, ?, (SELECT role_id FROM roles WHERE role = ?), 1) + + test_posts: + - | + INSERT INTO posts ( + post_id, + site_id, + created_by, + state_id, + posted_at, + name, + slug, + body + ) VALUES ( + 1, + 1, + 1, + (SELECT state_id FROM states WHERE state = 'public'), + CURRENT_TIMESTAMP, + 'Test Page', + 'test-page', + 'This is the body of a test page.' + ), ( + 2, + 1, + 1, + (SELECT state_id FROM states WHERE state = 'public'), + CURRENT_TIMESTAMP, + 'Test Project', + 'test-project', + 'This is the body of a test project.' + ), ( + 3, + 1, + 1, + (SELECT state_id FROM states WHERE state = 'public'), + CURRENT_TIMESTAMP, + 'Test Blog', + 'test-blog', + 'This is the body of a test blog entry.' + ) + + - | + INSERT INTO posts_fts(rowid, name, slug, body) + SELECT post_id, name, slug, body FROM posts + + - | + INSERT INTO pages(post_id, layout_id) VALUES ( + 1, + (SELECT layout_id FROM layouts WHERE layout = 'default') + ) + + - | + INSERT INTO projects(post_id) VALUES (2) + + - | + INSERT INTO blogs(post_id) VALUES (3) |