From 5e49cea82512285e4277496a242f29e948ec7b07 Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Sun, 22 May 2016 20:56:20 -0400 Subject: add database init, project and page handlers, bunch of model stubs, etc --- src/guff.cr | 486 +++++++++++++++++++++++++++++++++++++++++++++++++-- src/guff/database.cr | 24 ++- 2 files changed, 497 insertions(+), 13 deletions(-) (limited to 'src') diff --git a/src/guff.cr b/src/guff.cr index 4bbcba3..2147c33 100644 --- a/src/guff.cr +++ b/src/guff.cr @@ -3,12 +3,14 @@ require "http/server" require "ecr/macros" require "json" require "secure_random" +require "crypto/bcrypt" +require "sqlite3" require "./guff/*" -private macro define_model_set_getters(hash) +private macro define_lazy_getters(hash) {% for name, klass in hash %} def {{ name.id }} : {{ klass.id }} - (@cache[{{ name }}] ||= {{ klass.id }}.new(@context)) as {{ klass.id }} + (@cached_{{ name.id }} ||= {{ klass.id }}.new(@context)) end {% end %} end @@ -86,6 +88,10 @@ module Guff @system_dir = dir end + def db_path + File.join(@data_dir, "guff.db") + end + def self.parse( app : String, args : Array(String) @@ -199,6 +205,69 @@ module Guff end end + class PageModel < Model + SQL = { + get_id: " + SELECT b.post_id + + FROM sites a + JOIN posts b + ON (b.site_id = a.site_id) + JOIN pages c + ON (c.post_id = b.post_id) + JOIN states d + ON (d.state_id = b.state_id) + + WHERE a.site_id = ? + AND b.slug = ? + AND d.state = 'posted' + + ORDER BY b.created_at DESC + LIMIT 1 + ", + } + + def get_id( + site_id : Int64, + slug : String + ) : Int64? + r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug]) + r ? r.to_i64 : nil + end + end + + class ProjectModel < Model + SQL = { + get_id: " + SELECT b.post_id + + FROM sites a + JOIN posts b + ON (b.site_id = a.site_id) + JOIN projects c + ON (c.post_id = b.post_id) + JOIN states d + ON (d.state_id = b.state_id) + + WHERE a.site_id = ? + AND b.slug = ? + AND d.state = 'posted' + + ORDER BY b.created_at DESC + LIMIT 1 + ", + } + + def get_id( + site_id : Int64, + slug : String + ) : Int64? + r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug]) + r ? r.to_i64 : nil + end + end + + class UserModel < Model def login(user : String, pass : String) : String? if @context.development? @@ -349,17 +418,54 @@ module Guff end end end + + class SiteModel < Model + SQL = { + get_site_id: " + SELECT site_id + + FROM ( + SELECT a.site_id + + FROM site_domains a + JOIN sites b + ON (b.site_id = a.site_id) + + WHERE b.is_active + AND a.domain = $1 + + UNION ALL + + SELECT site_id + + FROM sites + + WHERE is_active + AND is_default + ) a + + LIMIT 1 + ", + } + + def get_id(host : String?) : Int64? + r = @context.dbs.ro.one(SQL[:get_site_id], [host || ""]) + r ? r.to_i64 : nil + end + end end class ModelSet def initialize(@context : Context) - @cache = {} of Symbol => Models::Model end - define_model_set_getters({ + define_lazy_getters({ user: Models::UserModel, session: Models::SessionModel, csrf: Models::CSRFModel, + page: Models::PageModel, + project: Models::ProjectModel, + site: Models::SiteModel, }) end @@ -436,10 +542,32 @@ module Guff end end + class DatabasePair + getter :ro, :rw + + def initialize( + path : String, + debug : Bool = false + ) + @ro = Database.new( + path: path, + read_only: true, + debug: debug + ) + + @rw = Database.new( + path: path, + read_only: false, + debug: debug + ) + end + end + class Context - getter :config + getter :config, :dbs def initialize(@config : Config) + @dbs = DatabasePair.new(@config.db_path, development?) end def models @@ -617,6 +745,10 @@ module Guff headers[key]? && headers[key].size > 0 end end + + protected def get_site_id(host : String?) : Int64? + @context.models.site.get_id(host) + end end abstract class AuthenticatedHandler < Handler @@ -765,7 +897,7 @@ module Guff super(context, %w{admin editor}) end - PATH_RE = %r{^/guff/admin.html$} + PATH_RE = %r{^/guff/admin\.html$} def authenticated_call(context : HTTP::Server::Context) if context.request.path.not_nil!.match(PATH_RE) @@ -780,7 +912,7 @@ module Guff end class LoginPageHandler < Handler - PATH_RE = %r{^/guff/login.html$} + PATH_RE = %r{^/guff/login\.html$} VALID_METHODS = %w{GET POST} def call(context : HTTP::Server::Context) @@ -874,7 +1006,7 @@ module Guff end class LogoutPageHandler < Handler - PATH_RE = %r{^/guff/logout.html$} + PATH_RE = %r{^/guff/logout\.html$} def call(context : HTTP::Server::Context) if context.request.method == "GET" && @@ -904,6 +1036,86 @@ module Guff end end + class PageHandler < Handler + PATH_RE = %r{^/(?[^/]+)\.html$} + + def call(context : HTTP::Server::Context) + if post_id = get_post_id(context.request) + # TODO: render page + context.response.content_type = "text/html; charset=utf-8" + context.response.status_code = 200 + context.response << "page: #{post_id}" + else + # unknown page + call_next(context) + end + end + + private def get_post_id(request : HTTP::Request) : Int64? + r = nil + + if request.method == "GET" + if md = PATH_RE.match(request.path.not_nil!) + if site_id = get_site_id(request.headers["host"]?) + STDERR.puts "DEBUG: searching for slug \"#{md["slug"]}\"" + + r = @context.models.page.get_id( + site_id: site_id, + slug: md["slug"], + ) + end + end + end + + # return result + r + end + end + + class ProjectHandler < Handler + PATH_RE = %r{^/(?[^/]+)/?$} + + def call(context : HTTP::Server::Context) + if post_id = get_post_id(context.request) + path = context.request.path.not_nil! + + if /\/$/.match(path) + # TODO: render page + context.response.content_type = "text/html; charset=utf-8" + context.response.status_code = 200 + context.response << "project: #{post_id}" + else + # redirect to project + context.response.headers["location"] = path + "/" + context.response.status_code = 302 + end + else + # unknown page + call_next(context) + end + end + + private def get_post_id(request : HTTP::Request) : Int64? + r = nil + + if request.method == "GET" + if md = PATH_RE.match(request.path.not_nil!) + if site_id = get_site_id(request.headers["host"]?) + STDERR.puts "DEBUG: searching for slug \"#{md["slug"]}\"" + + r = @context.models.project.get_id( + site_id: site_id, + slug: md["slug"], + ) + end + end + end + + # return result + r + end + end + HANDLERS = [{ dev: true, id: :error, @@ -931,6 +1143,12 @@ module Guff }, { dev: false, id: :logout, + }, { + dev: false, + id: :page, + }, { + dev: false, + id: :project, }] def self.get(context : Context) : Array(HTTP::Handler) @@ -964,6 +1182,10 @@ module Guff LoginPageHandler.new(context) when :logout LogoutPageHandler.new(context) + when :page + PageHandler.new(context) + when :project + ProjectHandler.new(context) else raise "unknown handler id: #{handler_id}" end @@ -984,14 +1206,258 @@ module Guff end class InitAction < Action + 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, + + -- internal role name + role TEXT UNIQUE NOT NULL CHECK ( + LENGTH(role) > 0 AND + role = LOWER(role) + ), + + -- user-visible role name + role_name TEXT UNIQUE NOT NULL + CHECK (LENGTH(role_name) > 0) + ) + }, %{ + INSERT INTO roles(role_id, role, role_name) VALUES + (1, 'viewer', 'Viewer'), + (2, 'editor', 'Editor'), + (3, 'admin', 'Admin') + }, %{ + 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) + ), + + -- user-visible state name + state_name TEXT UNIQUE NOT NULL + CHECK (LENGTH(state_name) > 0) + ) + }, %{ + INSERT INTO states(state_id, state, state_name) VALUES + (1, 'draft', 'Draft'), + (2, 'posted', 'Posted'), + (3, 'deletd', 'Deleted') + }, %{ + 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 true, + + body TEXT NOT NULL DEFAULT '' + ) + }, %{ + CREATE INDEX in_posts_site_id ON posts(site_id) + }, %{ + CREATE TABLE blog_posts ( + 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) + ) + }] + def run - STDERR.puts "TODO: building directory" + 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) + end + + # gen random password and add admin user + password = gen_password + add_admin_user(db, password) + add_test_data(db) + + STDERR.puts "admin user: admin@admin, password: #{password}" + end + end + + ADD_ADMIN_USER_SQL = %{ + INSERT INTO users( + user_id, + role_id, + name, + email, + password, + is_active + ) VALUES ( + 1, + (SELECT role_id FROM roles WHERE role = 'admin'), + 'Admin', + 'admin@admin', + ?, + 1 + ) + } + + private def add_admin_user(db : Database, password : String) + pass_hash = Crypto::Bcrypt::Password.create(password).to_s + # STDERR.puts "DEBUG: adding admin user (pass_hash = #{pass_hash}" + db.query(ADD_ADMIN_USER_SQL, [pass_hash]) + end + + # generate random password + private def gen_password + # STDERR.puts "DEBUG: generating random password" + SecureRandom.base64(6 + rand(6)).strip.gsub(/\=+$/, "") + 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 = 'posted'), + CURRENT_TIMESTAMP, + 'Test Page', + 'test-page', + 'This is the body of a test page.' + ), ( + 2, + 1, + 1, + (SELECT state_id FROM states WHERE state = 'posted'), + CURRENT_TIMESTAMP, + 'Test Project', + 'test-project', + 'This is the body of a test project.' + ) + }, %{ + INSERT INTO pages(post_id, layout_id) VALUES ( + 1, + (SELECT layout_id FROM layouts WHERE layout = 'default') + ) + }, %{ + INSERT INTO projects(post_id) VALUES (2) + }] + + private def add_test_data(db) + # STDERR.puts "DEBUG: adding test data" + TEST_DATA_SQL.each do |sql| + db.query(sql) + end end end class RunAction < Action def run - STDERR.puts "TODO: running web server" + STDERR.puts "Running web server" check_dirs # create context diff --git a/src/guff/database.cr b/src/guff/database.cr index 6e5906e..9811e80 100644 --- a/src/guff/database.cr +++ b/src/guff/database.cr @@ -15,18 +15,35 @@ module Guff pragma_foreign_keys: " PRAGMA foreign_keys = true ", + + pragma_query_only: " + PRAGMA query_only = true + ", } - def initialize(path) + def initialize( + path : String, + read_only : Bool = false, + @debug : Bool = false + ) super(path) @savepoint_id = 0_i64 + query(SQL[:pragma_foreign_keys]) + query(SQL[:pragma_query_only]) if read_only end - def initialize(path, &block : Database ->) + def initialize( + path : String, + read_only : Bool = false, + @debug : Bool = false, + &block : Database -> \ + ) super(path) @savepoint_id = 0_i64 + query(SQL[:pragma_foreign_keys]) + query(SQL[:pragma_query_only]) if read_only begin block.call(self) @@ -156,8 +173,9 @@ module Guff args : Array(String | Hash(String, String))? = nil, &block : ::SQLite3::ResultSet -> \ ) + STDERR.puts "DEBUG: sql = %s" % [sql] if @debug + # build statement - puts "sql = %s" % [sql] st = prepare(sql) # exec and close statement -- cgit v1.2.3