path: root/data/init.yaml

---
init_sql:
  - |
    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 '%@%'
                  ),

      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 asset_types (
      type_id     INTEGER PRIMARY KEY,
      name        TEXT UNIQUE NOT NULL
                  CHECK (LENGTH(name) > 0)
    )

  - |
    INSERT INTO asset_types(type_id, name) VALUES
      (1, 'script'),
      (2, 'style')

  - |
    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
      -- (allow NULL for system themes)
      theme_hash    TEXT UNIQUE CHECK (
                      LENGTH(theme_hash) > 0 AND
                      theme_hash NOT LIKE '% %' AND
                      theme_hash = LOWER(theme_hash)
                    ),

      -- is this a system theme?
      -- note: system themes are located in system_dir/themes/<slug>/
      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', NULL, 1),
      (2, 'blank', 'Blank', '1.0', '2016-07-18', NULL, 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),

      -- path of file
      file_path   TEXT NOT NULL CHECK (
                    LENGTH(file_path) > 1 AND
                    file_path NOT LIKE '/%'
                  ),

      -- 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 '% %'
                  ),

      UNIQUE (theme_id, file_path)
    )

  - |
    CREATE TABLE theme_assets (
      -- theme file
      file_id     INTEGER NOT NULL
                  REFERENCES theme_files(file_id),

      -- asset type
      type_id     INTEGER NOT NULL
                  REFERENCES asset_types(type_id),

      -- load order
      sort_order  INTEGER NOT NULL,

      PRIMARY KEY (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,

      -- 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,

      -- TODO: add window for visibility
      -- 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),

      -- repository url
      repo_url    TEXT NOT NULL DEFAULT ''
                  CHECK (repo_url NOT LIKE '% %'),

      -- prefix to strip from release tags
      tag_prefix      TEXT NOT NULL DEFAULT ''
                      CHECK (tag_prefix NOT LIKE '% %'),

      -- secret string used for hooks
      hook_secret TEXT NOT NULL DEFAULT ''
                  CHECK (hook_secret NOT LIKE '% %')
    )

  - |
    CREATE TABLE project_releases (
      post_id     INTEGER NOT NULL
                  REFERENCES posts(post_id),

      name        TEXT NOT NULL CHECK (
                    LENGTH(name) > 0 AND
                    name NOT LIKE '% %'
                  ),

      created_at  TIMESTAMP WITH TIME ZONE
                  NOT NULL DEFAULT CURRENT_TIMESTAMP,

      PRIMARY KEY (post_id, name)
    )

  - |
    CREATE TABLE pages (
      post_id     INTEGER PRIMARY KEY
                  REFERENCES posts(post_id),

      -- nullable, NULL means site theme
      theme_id    INTEGER DEFAULT NULL
                  REFERENCES themes(theme_id)
    )

  - |
    CREATE TABLE page_assets (
      post_id     INTEGER NOT NULL
                  REFERENCES posts(post_id),

      -- asset type
      type_id     INTEGER NOT NULL
                  REFERENCES asset_types(type_id),

      -- asset path
      path        TEXT NOT NULL
                  CHECK (LENGTH(path) > 0),

      -- load order
      sort_order  INTEGER NOT NULL,

      PRIMARY KEY (post_id, type_id, path)
    )

  - |
    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, theme_id) VALUES (
      1,
      (SELECT theme_id FROM themes WHERE theme_slug = 'default')
    )

  - |
    INSERT INTO projects(post_id) VALUES (2)

  - |
    INSERT INTO blogs(post_id) VALUES (3)