diff options
Diffstat (limited to 'data/init.yaml')
| -rw-r--r-- | data/init.yaml | 497 |
1 files changed, 248 insertions, 249 deletions
diff --git a/data/init.yaml b/data/init.yaml index 2afb808..7e177c5 100644 --- a/data/init.yaml +++ b/data/init.yaml @@ -1,259 +1,258 @@ --- -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) +init_sql: + - | + 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), + - | + 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 '% %' - ) - ) + 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, + - | + CREATE TABLE roles ( + role_id INTEGER PRIMARY KEY, - -- internal role name - role TEXT UNIQUE NOT NULL CHECK ( - LENGTH(role) > 0 AND - role = LOWER(role) - ), + -- 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 - ) + -- user-visible role name + name TEXT UNIQUE NOT NULL + CHECK (LENGTH(name) > 0), - - | - 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) + sort INTEGER UNIQUE NOT NULL + ) - - | - CREATE TABLE posts ( - post_id INTEGER PRIMARY KEY, + - | + 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), - site_id INTEGER NOT NULL - REFERENCES sites(site_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) - 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) + - | + 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) |