add database init, project and page handlers, bunch of model stubs, etc

1 files changed, 476 insertions, 10 deletions

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{^/(?<slug>[^/]+)\.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{^/(?<slug>[^/]+)/?$}
+
+      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