diff options
Diffstat (limited to 'data/init.yaml')
| -rw-r--r-- | data/init.yaml | 243 |
1 files changed, 189 insertions, 54 deletions
diff --git a/data/init.yaml b/data/init.yaml index 7e177c5..e11f82b 100644 --- a/data/init.yaml +++ b/data/init.yaml @@ -1,34 +1,6 @@ --- 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), - - 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, @@ -64,12 +36,196 @@ init_sql: email LIKE '%@%' ), + created_at TIMESTAMP WITH TIME ZONE NOT NULL + DEFAULT CURRENT_TIMESTAMP, + password TEXT NOT NULL DEFAULT '', is_active BOOLEAN NOT NULL DEFAULT false ) - | + CREATE TABLE themes ( + theme_id INTEGER PRIMARY KEY, + + -- generated (by guff) url component of theme + theme_slug TEXT UNIQUE NOT NULL CHECK ( + LENGTH(theme_slug) > 0 AND + theme_slug NOT LIKE '% %' AND + theme_slug = LOWER(theme_slug) + ), + + -- name + theme_name TEXT NOT NULL + CHECK (LENGTH(theme_name) > 0), + + -- version + theme_version TEXT NOT NULL + CHECK (LENGTH(theme_version) > 0), + + -- theme release date + theme_date DATE NOT NULL, + + -- sha1 digest of theme file + theme_hash TEXT NOT NULL CHECK ( + LENGTH(theme_hash) > 0 AND + theme_hash NOT LIKE '% %' AND + theme_hash = LOWER(theme_hash) + ), + + is_system BOOLEAN NOT NULL DEFAULT false + ) + + - | + INSERT INTO themes( + theme_id, + theme_slug, + theme_name, + theme_version, + theme_date, + theme_hash, + is_system + ) VALUES + (1, 'default', 'Default', '1.0', '2016-07-18', 'n/a', 1), + (2, 'blank', 'Blank', '1.0', '2016-07-18', 'n/a', 1) + + - | + CREATE TABLE theme_data_types ( + type_id INTEGER PRIMARY KEY, + + name TEXT UNIQUE NOT NULL CHECK ( + LENGTH(name) > 0 AND + name NOT LIKE '% %' AND + name = LOWER(name) + ) + ) + + - | + INSERT INTO theme_data_types(type_id, name) VALUES + (1, 'metadata'), + (2, 'template') + + - | + CREATE TABLE theme_data ( + -- theme that data entry is associated with + theme_id INTEGER NOT NULL + REFERENCES themes(theme_id), + + type_id INTEGER NOT NULL + REFERENCES theme_data_types(type_id), + + -- data key + -- e.g. "author", "url" + data_key TEXT NOT NULL CHECK ( + LENGTH(data_key) > 0 AND + data_key NOT LIKE '% %' AND + data_key = LOWER(data_key) + ), + + -- data value + data_val TEXT NOT NULL, + + PRIMARY KEY (theme_id, type_id, data_key) + ) + + - | + CREATE TABLE theme_files ( + file_id INTEGER PRIMARY KEY, + + -- theme that this file is associated with + theme_id INTEGER NOT NULL + REFERENCES themes(theme_id), + + -- size of file, in bytes + file_size INTEGER NOT NULL + CHECK (file_size > 0), + + -- sha1 digest of file + file_hash TEXT NOT NULL CHECK ( + LENGTH(file_hash) > 1 AND + file_hash NOT LIKE '% %' + ), + + -- path of file + file_path TEXT NOT NULL CHECK ( + LENGTH(file_path) > 1 AND + file_path NOT LIKE '/%' + ), + + UNIQUE (theme_id, file_path) + ) + + - | + CREATE TABLE theme_asset_types ( + type_id INTEGER PRIMARY KEY, + name TEXT UNIQUE NOT NULL + CHECK (LENGTH(name) > 0) + ) + + - | + INSERT INTO theme_asset_types(type_id, name) VALUES + (1, 'script'), + (2, 'style') + + - | + CREATE TABLE theme_assets ( + -- theme file + file_id INTEGER NOT NULL + REFERENCES theme_files(file_id), + + -- asset type + type_id INTEGER NOT NULL + REFERENCES theme_asset_types(type_id), + + -- load order + sort_order INTEGER NOT NULL, + + PRIMARY KEY (file_id, type_id), + UNIQUE (file_id, type_id) + ) + + - | + CREATE TABLE sites ( + site_id INTEGER PRIMARY KEY, + + -- make sure name does not begin with a dot or + -- contain slashes + name TEXT UNIQUE NOT NULL CHECK ( + LENGTH(name) > 0 AND + name NOT LIKE '.%' AND + name NOT LIKE '%/%' + ), + + -- date that site was created + created_at TIMESTAMP WITH TIME ZONE NOT NULL + DEFAULT CURRENT_TIMESTAMP, + + -- theme for this site + theme_id INTEGER NOT NULL + REFERENCES themes(theme_id), + + is_active BOOLEAN NOT NULL DEFAULT false, + + is_default BOOLEAN NOT NULL DEFAULT false + ) + + - | + INSERT INTO sites(site_id, name, theme_id, is_active, is_default) VALUES + (1, 'default', 1, 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 states ( state_id INTEGER PRIMARY KEY, @@ -154,34 +310,13 @@ init_sql: ) - | - 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) + -- nullable, NULL means site theme + theme_id INTEGER DEFAULT NULL + REFERENCES themes(theme_id) ) - | @@ -246,9 +381,9 @@ test_posts: SELECT post_id, name, slug, body FROM posts - | - INSERT INTO pages(post_id, layout_id) VALUES ( + INSERT INTO pages(post_id, theme_id) VALUES ( 1, - (SELECT layout_id FROM layouts WHERE layout = 'default') + (SELECT theme_id FROM themes WHERE theme_slug = 'default') ) - | |