From 959e2baf6fc78c0fba694af27531b8c9d6ed5f7f Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Fri, 15 Jul 2016 16:28:09 -0400 Subject: add data/init.yaml and remove hard-coded queries --- src/guff.cr | 261 ++++-------------------------------------------------------- 1 file changed, 17 insertions(+), 244 deletions(-) (limited to 'src/guff.cr') 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 -- cgit v1.2.3