aboutsummaryrefslogtreecommitdiff
path: root/src/guff.cr
diff options
context:
space:
mode:
Diffstat (limited to 'src/guff.cr')
-rw-r--r--src/guff.cr334
1 files changed, 0 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: "
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-01 20:43:23 +0000