From c758a064eb97b16fd875c9f5a624bb98f8a774dc Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Tue, 24 May 2016 12:00:43 -0400 Subject: add full editing, fts search --- src/guff.cr | 196 +++++++++++++++++++++++++++++++++++++++++++++++------------- 1 file changed, 153 insertions(+), 43 deletions(-) (limited to 'src/guff.cr') 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: " New Post @@ -1581,33 +1697,16 @@ module Guff title='Show additonal options.' data-toggle='dropdown' > - -