add data/init.yaml and remove hard-coded queries

2 files changed, 276 insertions, 244 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)
diff --git a/src/guff.cr b/src/guff.cr
index 4ac6408..1e536cf 100644
--- a/src/guff.cr
+++ b/src/guff.cr
@@ -2,6 +2,7 @@ require "option_parser"
 require "http/server"
 require "ecr/macros"
 require "json"
+require "yaml"
 require "secure_random"
 require "crypto/bcrypt"
 require "sqlite3"
@@ -2710,219 +2711,43 @@ module Guff
       end
 
       class InitAction < Action
-        SQL = [%{
-          CREATE TABLE sites (
-            site_id     INTEGER PRIMARY KEY,
+        def initialize(config : Config)
+          super(config)
 
-            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
-          )
-        }]
+          # read init data
+          @data = YAML.parse(File.read(File.join(
+            @config.system_dir,
+            "init.yaml"
+          )))
+        end
 
         def run
           STDERR.puts "Initializing data directory"
           Dir.mkdir(@config.data_dir) unless Dir.exists?(@config.data_dir)
 
           Guff::Database.new(@config.db_path) do |db|
-            SQL.each do |sql|
-              db.query(sql)
+            @data["sql"]["init"].each do |sql|
+              db.query(sql.as_s)
             end
 
             # gen random password and add admin user
+            # TODO: move these to init.yaml
             password = Password.random_password
             add_user(db, "Admin", "admin@admin", password)
             add_user(db, "Test", "test@test", "test")
-            add_test_data(db)
+            add_test_posts(db)
 
             STDERR.puts "admin user: admin@admin, password: #{password}"
           end
         end
 
-        ADD_USER_SQL = %{
-          INSERT INTO users(name, email, password, role_id, is_active) VALUES
-            (?, ?, ?, (SELECT role_id FROM roles WHERE role = ?), 1)
-        }
-
         private def add_user(
           db        : Database,
           name      : String,
           email     : String,
           password  : String
         ) : Int64
-          db.query(ADD_USER_SQL, [
+          db.query(@data["sql"]["add_user"].as_s, [
             name,
             email,
             Password.create(password),
@@ -2931,62 +2756,10 @@ module Guff
           db.last_insert_row_id.to_i64
         end
 
-        TEST_DATA_SQL = [%{
-          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)
-        }]
-
-        private def add_test_data(db)
+        private def add_test_posts(db)
           # STDERR.puts "DEBUG: adding test data"
-          TEST_DATA_SQL.each do |sql|
-            db.query(sql)
+          @data["sql"]["test_posts"].each do |sql|
+            db.query(sql.as_s)
           end
         end
       end