add full editing, fts search

1 files changed, 153 insertions, 43 deletions

diff --git a/src/guff.cr b/src/guff.cr
index b082b4e..6367671 100644
--- a/src/guff.cr
+++ b/src/guff.cr
@@ -215,6 +215,28 @@ module Guff
     end
   end
 
+  class PagedResultSet
+    def initialize(
+      @page       : Int32,
+      @num_rows   : Int64,
+      @limit      : Int32,
+      @rows       : Array(Hash(String, String))
+    )
+    end
+
+    def to_json(io)
+      {
+        "meta": {
+          "page":       @page,
+          "num_rows":   @num_rows,
+          "num_pages":  (1.0 * @num_rows / @limit).ceil
+        },
+
+        "rows": @rows
+      }.to_json(io)
+    end
+  end
+
   module Models
     abstract class Model
       def initialize(@context : Context)
@@ -228,12 +250,23 @@ module Guff
             (?, ?, (SELECT state_id FROM states WHERE state = 'draft'))
         ",
 
+        fts_add: "
+          INSERT INTO posts_fts(rowid, name, slug, body)
+            SELECT post_id, name, slug, body FROM posts WHERE post_id = ?
+        ",
+
         set: "
           UPDATE posts
              SET %s
            WHERE post_id = ?
         ",
 
+        fts_set: "
+          UPDATE posts_fts
+             SET %s
+           WHERE rowid = ?
+        ",
+
         count_posts: "
           SELECT COUNT(*)
 
@@ -290,8 +323,20 @@ module Guff
         site_id : Int64,
         user_id : Int64,
       ) : Int64
-        @context.dbs.rw.query(SQL[:add], [site_id.to_s, user_id.to_s])
-        @context.dbs.rw.last_insert_row_id.to_i64
+        db = @context.dbs.rw
+        post_id = -1_i64
+
+        db.transaction do
+          # add entry
+          db.query(SQL[:add], [site_id.to_s, user_id.to_s])
+          post_id = db.last_insert_row_id.to_i64
+
+          # populate fts index
+          db.query(SQL[:fts_add], [post_id.to_s])
+        end
+
+        # return post_id
+        post_id
       end
 
       def set(
@@ -314,6 +359,8 @@ module Guff
       )
         sets = [] of String
         args = [] of String
+        fts_sets = [] of String
+        fts_args = [] of String
 
         if site_id
           sets << "site_id = ?"
@@ -351,6 +398,9 @@ module Guff
         if slug
           sets << "slug = ?"
           args << slug
+
+          fts_sets << "slug = ?"
+          fts_args << slug
         end
 
         unless slug_lock.nil?
@@ -361,16 +411,29 @@ module Guff
         if name
           sets << "name = ?"
           args << name
+
+          fts_sets << "name = ?"
+          fts_args << name
         end
 
         if body
           sets << "body = ?"
           args << body
+
+          fts_sets << "body = ?"
+          fts_args << body.gsub(/<.+?>/, " ")
         end
 
         if sets.size > 0
+          # update posts
           args << post_id.to_s
           @context.dbs.rw.query(SQL[:set] % sets.join(","), args)
+
+          if fts_sets.size > 0
+            # update posts fts
+            fts_args << post_id.to_s
+            @context.dbs.rw.query(SQL[:fts_set] % fts_sets.join(","), fts_args)
+          end
         end
       end
 
@@ -380,6 +443,7 @@ module Guff
         site_id   : Int64?   = nil,
         post_type : String?  = nil,
         state     : String?  = nil,
+        q         : String?  = nil,
         page      : Int32    = 1,
       )
         filters = %w{1}
@@ -389,11 +453,13 @@ module Guff
         raise "invalid page: #{page}" unless page > 0
 
         if site_id
+          # add site filter
           filters << "a.site_id = ?"
           args << site_id.to_s
         end
 
         if post_type
+          # add type filter
           filters << case post_type
           when "blog"
             "c.post_id IS NOT NULL"
@@ -407,12 +473,25 @@ module Guff
         end
 
         if state
+          # add state filter
           filters << "b.state = ?"
           args << state
         else
+          # default state filter
           filters << "b.state IN ('draft', 'posted')"
         end
 
+        if q && q.match(/\S+/)
+          # add search filter
+          filters << "a.post_id IN (
+            SELECT rowid
+              FROM posts_fts 
+             WHERE posts_fts MATCH ?
+          )"
+
+          args << q
+        end
+
         # build where clause
         filter_sql = filters.join(" AND ")
 
@@ -441,15 +520,12 @@ module Guff
         end
 
         # return results
-        {
-          "meta": {
-            "page":      page,
-            "num_posts": num_posts,
-            "num_pages": (1.0 * num_posts / LIMIT).ceil
-          },
-
-          "rows": rows,
-        }
+        PagedResultSet.new(
+          page:     page,
+          num_rows: num_posts,
+          limit:    LIMIT,
+          rows:     rows,
+        )
       end
     end
 
@@ -581,7 +657,7 @@ module Guff
       end
 
       def get(post_id : Int64)
-        @context.dbs.ro.row(SQL[:get], [post_id.to_s])
+        @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
       end
     end
 
@@ -710,7 +786,7 @@ module Guff
       end
 
       def get(post_id : Int64)
-        @context.dbs.ro.row(SQL[:get], [post_id.to_s])
+        @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
       end
     end
 
@@ -876,7 +952,7 @@ module Guff
       end
 
       def get(post_id : Int64)
-        @context.dbs.ro.row(SQL[:get], [post_id.to_s])
+        @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
       end
     end
 
@@ -1138,12 +1214,25 @@ module Guff
 
           LIMIT 1
         ",
+
+        get_default_id: "
+          SELECT site_id
+
+            FROM sites
+
+           WHERE is_active
+             AND is_default
+        "
       }
 
       def get_id(host : String?) : Int64?
         r = @context.dbs.ro.one(SQL[:get_id], [host || ""])
         r ? r.to_i64 : nil
       end
+
+      def get_default_id : Int64
+        @context.dbs.ro.one(SQL[:get_default_id]).not_nil!.to_i64
+      end
     end
 
     class RoleModel < Model
@@ -1317,6 +1406,7 @@ module Guff
           site_id:    params["site_id"]? ? params["site_id"].to_i64 : nil,
           state:      params["state"]?,
           post_type:  params["post_type"]?,
+          q:          params["q"]?,
           page:       params["page"].to_i32,
         )
       end
@@ -1325,7 +1415,7 @@ module Guff
     module PageAPI
       def do_page_add(params : HTTP::Params)
         post_id = @context.models.page.add(
-          site_id:   params["site_id"].to_i64,
+          site_id:   params["site_id"]? ? params["site_id"].to_i64 : @context.models.site.get_default_id,
           user_id:   @context.user_id.not_nil!,
         )
 
@@ -1356,12 +1446,21 @@ module Guff
 
         nil
       end
+
+      def do_page_get(params : HTTP::Params)
+        @context.models.page.get(
+          post_id: params["post_id"].to_i64
+        ).reduce({} of String => String) do |r, k, v|
+          r[k] = v.to_s
+          r
+        end
+      end
     end
 
     module ProjectAPI
       def do_project_add(params : HTTP::Params)
         post_id = @context.models.project.add(
-          site_id:   params["site_id"].to_i64,
+          site_id:   params["site_id"]? ? params["site_id"].to_i64 : @context.models.site.get_default_id,
           user_id:   @context.user_id.not_nil!,
         )
 
@@ -1392,12 +1491,21 @@ module Guff
 
         nil
       end
+
+      def do_project_get(params : HTTP::Params)
+        @context.models.project.get(
+          post_id: params["post_id"].to_i64
+        ).reduce({} of String => String) do |r, k, v|
+          r[k] = v.to_s
+          r
+        end
+      end
     end
 
     module BlogAPI
       def do_blog_add(params : HTTP::Params)
         post_id = @context.models.blog.add(
-          site_id:   params["site_id"].to_i64,
+          site_id:   params["site_id"]? ? params["site_id"].to_i64 : @context.models.site.get_default_id,
           user_id:   @context.user_id.not_nil!,
         )
 
@@ -1426,6 +1534,15 @@ module Guff
 
         nil
       end
+
+      def do_blog_get(params : HTTP::Params)
+        @context.models.blog.get(
+          post_id: params["post_id"].to_i64
+        ).reduce({} of String => String) do |r, k, v|
+          r[k] = v.to_s
+          r
+        end
+      end
     end
 
     module UserAPI
@@ -1566,10 +1683,9 @@ module Guff
         new_post_button: "
           <a
             href='#'
-            class='btn btn-primary'
+            class='btn btn-primary add-post'
             title='Create new blog post.'
-            data-toggle='modal'
-            data-target='#blog-edit-dialog'
+            data-type='blog'
           >
             <i class='fa fa-plus-circle'></i>
             New Post
@@ -1581,33 +1697,16 @@ module Guff
             title='Show additonal options.'
             data-toggle='dropdown'
           >
-            <span class='hidden'>
-              <i class='fa fa-plus-circle'></i>
-              New
-            </span>
-
             <i class='fa fa-caret-down'></i>
           </a>
 
           <ul class='dropdown-menu'>
-            <li class='hidden'>
-              <a
-                href='#'
-                title='Create new blog post.'
-                data-toggle='modal'
-                data-target='#blog-edit-dialog'
-              >
-                <i class='fa fa-fw fa-bullhorn'></i>
-                New Post
-              </a>
-            </li>
-
             <li>
               <a
                 href='#'
                 title='Create new page.'
-                data-toggle='modal'
-                data-target='#page-edit-dialog'
+                class='add-post'
+                data-type='page'
               >
                 <i class='fa fa-fw fa-bookmark-o'></i>
                 New Page
@@ -1618,8 +1717,8 @@ module Guff
               <a
                 href='#'
                 title='Create new project.'
-                data-toggle='modal'
-                data-target='#project-edit-dialog'
+                class='add-post'
+                data-type='project'
               >
                 <i class='fa fa-fw fa-cube'></i>
                 New Project
@@ -1633,6 +1732,7 @@ module Guff
             href='#'
             class='btn %s'
             title='Mark as %s.'
+            data-val='%s'
           >
             <i class='fa %s'></i>
             %s
@@ -1679,6 +1779,7 @@ module Guff
             io << TEMPLATES[:state_button] % [
               h(row[:css]),
               h(row[:name]),
+              h(row[:id]),
               h(row[:icon]),
               h(row[:name])
             ]
@@ -2368,7 +2469,7 @@ module Guff
           INSERT INTO states(state_id, state, state_name) VALUES
             (1, 'draft', 'Draft'),
             (2, 'posted', 'Posted'),
-            (3, 'deletd', 'Deleted')
+            (3, 'deleted', 'Deleted')
         }, %{
           CREATE TABLE posts (
             post_id     INTEGER PRIMARY KEY,
@@ -2395,13 +2496,19 @@ module Guff
                           slug = LOWER(slug)
                         ),
 
-            slug_lock   BOOLEAN NOT NULL DEFAULT true,
+            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)
@@ -2540,6 +2647,9 @@ module Guff
             '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')