diff options
| -rw-r--r-- | src/guff.cr | 334 | ||||
| -rw-r--r-- | src/guff/models/post.cr | 333 |
2 files changed, 333 insertions, 334 deletions
diff --git a/src/guff.cr b/src/guff.cr index e5eca75..9696e5d 100644 --- a/src/guff.cr +++ b/src/guff.cr @@ -47,340 +47,6 @@ end module Guff module Models - class PostModel < Model - SQL = { - add: " - INSERT INTO posts(site_id, created_by, state_id) VALUES - (?, ?, (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(*) - - FROM posts a - - JOIN states b - ON (b.state_id = a.state_id) - JOIN sites c - ON (c.site_id = a.site_id) - JOIN users d - ON (d.user_id = a.created_by) - LEFT JOIN blogs x - ON (x.post_id = a.post_id) - LEFT JOIN pages y - ON (y.post_id = a.post_id) - LEFT JOIN projects z - ON (z.post_id = a.post_id) - - WHERE %s - ", - - get_posts: " - SELECT a.post_id, - a.site_id, - c.name AS site_name, - b.state, - d.user_id, - d.name AS user_name, - d.email AS user_email, - - a.created_at, - date(a.created_at) AS created_at_text, - datetime(a.created_at) AS created_at_text_full, - - a.posted_at, - date(a.posted_at) AS posted_at_text, - datetime(a.posted_at) AS posted_at_text_full, - - a.expires_at, - date(a.expires_at) AS expires_at_text, - datetime(a.expires_at) AS expires_at_text_full, - - a.slug, - a.slug_lock, - a.name, - - (CASE WHEN x.post_id IS NOT NULL THEN - 'blog' - WHEN y.post_id IS NOT NULL THEN - 'page' - WHEN z.post_id IS NOT NULL THEN - 'project' - END) as post_type, - - (CASE WHEN b.state = 'public' THEN - CASE WHEN x.post_id IS NOT NULL THEN - strftime('/%%Y/%%m/%%d/', a.posted_at) || a.slug || '.html' - WHEN y.post_id IS NOT NULL THEN - '/' || a.slug || '.html' - WHEN z.post_id IS NOT NULL THEN - '/' || a.slug || '/' - END - ELSE - NULL - END) AS post_url - - FROM posts a - JOIN states b - ON (b.state_id = a.state_id) - JOIN sites c - ON (c.site_id = a.site_id) - JOIN users d - ON (d.user_id = a.created_by) - - LEFT JOIN blogs x - ON (x.post_id = a.post_id) - LEFT JOIN pages y - ON (y.post_id = a.post_id) - LEFT JOIN projects z - ON (z.post_id = a.post_id) - - WHERE c.is_active - AND %s - - ORDER BY COALESCE(a.posted_at, a.created_at) DESC - - LIMIT ? OFFSET ? - ", - } - - def add( - site_id : Int64, - user_id : Int64, - ) : Int64 - 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( - post_id : Int64, - - site_id : Int64? = nil, - state : String? = nil, - - have_posted_at : Bool = false, - posted_at : Time? = nil, - - have_expires_at : Bool = false, - expires_at : Time? = nil, - - slug : String? = nil, - slug_lock : Bool? = nil, - - name : String? = nil, - body : String? = nil, - ) - sets = [] of String - args = [] of String - fts_sets = [] of String - fts_args = [] of String - - if site_id - sets << "site_id = ?" - args << site_id.to_s - end - - if state - sets << "state_id = (SELECT state_id FROM states WHERE state = ?)" - args << state - - if state == "public" && !have_posted_at - # set posted_at by default - sets << "posted_at = COALESCE(posted_at, CURRENT_TIMESTAMP)" - end - end - - if have_posted_at - if posted_at - sets << "posted_at = ?" - args << ISO8601.format(posted_at) - else - sets << "posted_at = NULL" - end - end - - if have_expires_at - if expires_at - sets << "expires_at = ?" - args << ISO8601.format(expires_at) - else - sets << "expires_at = NULL" - end - end - - if slug - sets << "slug = ?" - args << slug - - fts_sets << "slug = ?" - fts_args << slug - end - - unless slug_lock.nil? - sets << "slug_lock = ?" - args << (slug_lock ? "1" : "0") - end - - 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 - - LIMIT = 50 - - def get_posts( - site_id : Int64? = nil, - user_id : Int64? = nil, - type : String? = nil, - state : String? = nil, - q : String? = nil, - page : Int32 = 1, - ) - filters = %w{1} - args = [] of String - - # check page - raise "invalid page: #{page}" unless page > 0 - - if site_id - # add site filter - filters << "a.site_id = ?" - args << site_id.to_s - end - - if user_id - # add user filter - filters << "a.created_by = ?" - args << user_id.to_s - end - - if type - # add type filter - filters << case type - when "blog" - "x.post_id IS NOT NULL" - when "page" - "y.post_id IS NOT NULL" - when "project" - "z.post_id IS NOT NULL" - when "all" - # allow "all" - "1" - else - raise "unknown post type: #{type}" - end - end - - if state && state != "default" - # add state filter - filters << "b.state = ?" - args << state - else - # default state filter - filters << "b.state IN ('draft', 'public')" - 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 ") - - # count matching rows - num_posts = (@context.dbs.ro.one(SQL[:count_posts] % [ - filter_sql - ], args) || "0").to_i64 - - # build result - rows = [] of Hash(String, String) - - if num_posts > 0 - # get matching rows - @context.dbs.ro.all(SQL[:get_posts] % [ - filter_sql - ], args.concat([ - LIMIT.to_s, - ((page - 1) * LIMIT).to_s, - ])) do |row| - # append row to result - rows << row.reduce({} of String => String) do |r, kv| - r[kv[0]] = kv[1].to_s - r - end - end - end - - # return results - PagedResultSet.new( - page: page, - num_rows: num_posts, - limit: LIMIT, - rows: rows, - ) - end - end - class PageModel < Model SQL = { get_id: " diff --git a/src/guff/models/post.cr b/src/guff/models/post.cr new file mode 100644 index 0000000..5c94d04 --- /dev/null +++ b/src/guff/models/post.cr @@ -0,0 +1,333 @@ +class Guff::Models::PostModel < Guff::Models::Model + SQL = { + add: " + INSERT INTO posts(site_id, created_by, state_id) VALUES + (?, ?, (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(*) + + FROM posts a + + JOIN states b + ON (b.state_id = a.state_id) + JOIN sites c + ON (c.site_id = a.site_id) + JOIN users d + ON (d.user_id = a.created_by) + LEFT JOIN blogs x + ON (x.post_id = a.post_id) + LEFT JOIN pages y + ON (y.post_id = a.post_id) + LEFT JOIN projects z + ON (z.post_id = a.post_id) + + WHERE %s + ", + + get_posts: " + SELECT a.post_id, + a.site_id, + c.name AS site_name, + b.state, + d.user_id, + d.name AS user_name, + d.email AS user_email, + + a.created_at, + date(a.created_at) AS created_at_text, + datetime(a.created_at) AS created_at_text_full, + + a.posted_at, + date(a.posted_at) AS posted_at_text, + datetime(a.posted_at) AS posted_at_text_full, + + a.expires_at, + date(a.expires_at) AS expires_at_text, + datetime(a.expires_at) AS expires_at_text_full, + + a.slug, + a.slug_lock, + a.name, + + (CASE WHEN x.post_id IS NOT NULL THEN + 'blog' + WHEN y.post_id IS NOT NULL THEN + 'page' + WHEN z.post_id IS NOT NULL THEN + 'project' + END) as post_type, + + (CASE WHEN b.state = 'public' THEN + CASE WHEN x.post_id IS NOT NULL THEN + strftime('/%%Y/%%m/%%d/', a.posted_at) || a.slug || '.html' + WHEN y.post_id IS NOT NULL THEN + '/' || a.slug || '.html' + WHEN z.post_id IS NOT NULL THEN + '/' || a.slug || '/' + END + ELSE + NULL + END) AS post_url + + FROM posts a + JOIN states b + ON (b.state_id = a.state_id) + JOIN sites c + ON (c.site_id = a.site_id) + JOIN users d + ON (d.user_id = a.created_by) + + LEFT JOIN blogs x + ON (x.post_id = a.post_id) + LEFT JOIN pages y + ON (y.post_id = a.post_id) + LEFT JOIN projects z + ON (z.post_id = a.post_id) + + WHERE c.is_active + AND %s + + ORDER BY COALESCE(a.posted_at, a.created_at) DESC + + LIMIT ? OFFSET ? + ", + } + + def add( + site_id : Int64, + user_id : Int64, + ) : Int64 + 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( + post_id : Int64, + + site_id : Int64? = nil, + state : String? = nil, + + have_posted_at : Bool = false, + posted_at : Time? = nil, + + have_expires_at : Bool = false, + expires_at : Time? = nil, + + slug : String? = nil, + slug_lock : Bool? = nil, + + name : String? = nil, + body : String? = nil, + ) + sets = [] of String + args = [] of String + fts_sets = [] of String + fts_args = [] of String + + if site_id + sets << "site_id = ?" + args << site_id.to_s + end + + if state + sets << "state_id = (SELECT state_id FROM states WHERE state = ?)" + args << state + + if state == "public" && !have_posted_at + # set posted_at by default + sets << "posted_at = COALESCE(posted_at, CURRENT_TIMESTAMP)" + end + end + + if have_posted_at + if posted_at + sets << "posted_at = ?" + args << ISO8601.format(posted_at) + else + sets << "posted_at = NULL" + end + end + + if have_expires_at + if expires_at + sets << "expires_at = ?" + args << ISO8601.format(expires_at) + else + sets << "expires_at = NULL" + end + end + + if slug + sets << "slug = ?" + args << slug + + fts_sets << "slug = ?" + fts_args << slug + end + + unless slug_lock.nil? + sets << "slug_lock = ?" + args << (slug_lock ? "1" : "0") + end + + 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 + + LIMIT = 50 + + def get_posts( + site_id : Int64? = nil, + user_id : Int64? = nil, + type : String? = nil, + state : String? = nil, + q : String? = nil, + page : Int32 = 1, + ) + filters = %w{1} + args = [] of String + + # check page + raise "invalid page: #{page}" unless page > 0 + + if site_id + # add site filter + filters << "a.site_id = ?" + args << site_id.to_s + end + + if user_id + # add user filter + filters << "a.created_by = ?" + args << user_id.to_s + end + + if type + # add type filter + filters << case type + when "blog" + "x.post_id IS NOT NULL" + when "page" + "y.post_id IS NOT NULL" + when "project" + "z.post_id IS NOT NULL" + when "all" + # allow "all" + "1" + else + raise "unknown post type: #{type}" + end + end + + if state && state != "default" + # add state filter + filters << "b.state = ?" + args << state + else + # default state filter + filters << "b.state IN ('draft', 'public')" + 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 ") + + # count matching rows + num_posts = (@context.dbs.ro.one(SQL[:count_posts] % [ + filter_sql + ], args) || "0").to_i64 + + # build result + rows = [] of Hash(String, String) + + if num_posts > 0 + # get matching rows + @context.dbs.ro.all(SQL[:get_posts] % [ + filter_sql + ], args.concat([ + LIMIT.to_s, + ((page - 1) * LIMIT).to_s, + ])) do |row| + # append row to result + rows << row.reduce({} of String => String) do |r, kv| + r[kv[0]] = kv[1].to_s + r + end + end + end + + # return results + PagedResultSet.new( + page: page, + num_rows: num_posts, + limit: LIMIT, + rows: rows, + ) + end +end |