diff options
Diffstat (limited to 'src/guff.cr')
| -rw-r--r-- | src/guff.cr | 196 |
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') |