module Guff class PostModel < Model SQL = TemplateCache.new({ tags: " SELECT DISTINCT post_id FROM (%{sql}) ", tag: " SELECT a.post_id FROM post_tags a JOIN tags b ON (b.tag_id = a.tag_id) WHERE b.name = '%{tag}' ", count_posts: " SELECT COUNT(*) FROM posts a JOIN (%{tags}) b ON (b.post_id = a.post_id) WHERE a.site_id = :site_id AND %{filter} ", get_posts: " SELECT %{cols} FROM posts a JOIN (%{tags}) b ON (b.post_id = a.post_id) WHERE a.site_id = :site_id AND %{filter} ORDER BY %{sort} %{dir} LIMIT :limit OFFSET :offset ", update_post: " UPDATE posts SET %{sets} WHERE site_id = :site_id AND post_id = :post_id ", add_post: " INSERT INTO posts(site_id, slug, name, raw_body, body) VALUES (:site_id, :slug, :name, :raw_body, :body) ", remove_posts: " UPDATE posts SET is_active = 0 WHERE site_id = :site_id AND post_id IN (%{post_ids}) ", set_tags_delete: " DELETE FROM post_tags WHERE post_id = ( SELECT post_id FROM posts WHERE site_id = :site_id AND post_id = :post_id ) ", set_tags_insert: " INSERT INTO post_tags(post_id, tag_id) SELECT a.post_id, b.tag_id FROM posts a CROSS JOIN tags b WHERE a.site_id = :site_id AND a.post_id = :post_id AND b.name IN (%{tags}) ", }) def initialize(models : Models) super(models, SQL) end COLUMNS = { "post_id": { default: true, sortable: true, clause: "a.post_id", }, "name": { default: true, sortable: true, clause: "a.name", }, "body": { default: false, sortable: true, clause: "a.body", }, "raw_body": { default: false, sortable: true, clause: "a.raw_body", }, "posted_at": { default: true, sortable: true, clause: "a.posted_at", }, "created_at": { default: true, sortable: true, clause: "a.created_at", }, "tags": { default: true, sortable: false, clause: " (SELECT group_concat(d.name, '|') FROM post_tags c JOIN tags d ON (d.tag_id = c.tag_id) WHERE c.post_id = a.post_id) ", }, } ##################### # get_posts methods # ##################### def get_posts( cols = nil : Array(String)?, site_id = nil : Int?, q = "" : String, tags = [] of Array(String) : Array(Array(String)), page = 1 : Int, limit = 20 : Int, sort = "posted_at" : String, dir = "desc" : String, ) # build sql args sql_args = { "site_id": (site_id || @models.site.get_default).to_s, } of String => String # build tmpl args tmpl_args = { # TODO "filter": "1 = 1", # true "sort": get_sort_clause(sort), "dir": dir, "tags": get_tags_filter(tags), "cols": get_columns_clause(cols), } # count number of matching rows num_rows = (one(:count_posts, sql_args, tmpl_args).to_s || 0).to_i # TODO: add Post class? rows = [] of Post if num_rows > 0 all(:get_posts, sql_args.merge({ "offset": ((page - 1) * limit).to_s, "limit": limit.to_s, }), tmpl_args) do |row| rows << Post.new(row) nil end end # return result Results(Post).new( page: page, limit: limit, num_rows: num_rows, rows: rows, ) end private def get_sort_clause( sort : String ) : String # verify sort column raise "unknown sort column" unless COLUMNS.has_key?(sort) raise "column is not sortable" unless COLUMNS[sort][:sortable] # return result COLUMNS[sort][:clause] as String end private def get_columns_clause( cols : Array(String)? ) : String (cols || COLUMNS.keys.select { |col| COLUMNS[col][:default] }).map { |col| "%s AS %s" % [COLUMNS[col][:clause], col] }.join(", ") end NO_POSTS = " SELECT post_id FROM posts LIMIT 0 " private def get_tags_filter( tag_sets : Array(Array(String)) ) : String (tag_sets.size > 0) ? template(:tags, { "sql": tag_sets.map { |tags| template(:tags, { "sql": tags.map { |tag| template(:tag, { "tag": @models.db.quote(tag), }) }.join(" INTERSECTS "), }) }.join(" UNION "), }) : NO_POSTS end #################### # add_post methods # #################### def add_post( site_id = nil : Int?, slug = "" : String, name = "" : String, body = "" : String, tags = [] of String : Array(String), ) : Int64 post_id = -1_i64 # check slug, name, and body { "slug": slug, "name": name, "body": body }.each do |name, text| raise "invalid %s" % [name] unless text.size > 0 end transaction do query(:add_post, { "site_id": (site_id || @models.site.get_default).to_s, "slug": slug, "name": name, "raw_body": body, "body": body, }, nil) # get post id post_id = last_insert_row_id # set post tags set_tags( site_id: site_id, post_id: post_id, tags: tags, use_transaction: false, ) end # return post id post_id end def update_post( site_id = nil : Int?, post_id = nil : Int?, slug = nil : String?, name = nil : String?, body = nil : String?, tags = nil : Array(String)?, posted = nil : Bool? ) raise "null post_id" if post_id.nil? sets = [] of String args = { "site_id": (site_id || @models.site.get_default).to_s, "post_id": post_id.to_s, } if slug sets << "slug = :slug" args["slug"] = slug end if name sets << "name = :name" args["name"] = name end if body sets << "body = :body, raw_body = :body" args["body"] = body end unless posted.nil? val = posted ? "CURRENT_TIMESTAMP" : "NULL" sets << "posted_at = %s" % [val] end if sets.size > 0 || tags transaction do if sets.size > 0 # update post query(:update_post, args, { "sets": sets.join(','), }) end # TODO: post history if tags # update tags set_tags( site_id: site_id, post_id: post_id, tags: tags, use_transaction: false, ) end end end end def remove_posts( site_id = nil : Int?, post_ids = [] of Int : Array(Int) ) query(:remove_posts, { "site_id": (site_id || @models.site.get_default).to_s, }, { "post_ids": post_ids.map { |post_id| "'" + @db.quote(post_id.to_s) + "'" }.join(',') }) # no return value nil end def set_tags( site_id = nil : Int?, post_id = nil : Int?, tags = [] of String : Array(String)?, use_transaction = true : Bool ) if use_transaction transaction do raw_set_tags(site_id, post_id, tags) end else raw_set_tags(site_id, post_id, tags) end nil end private def raw_set_tags( site_id = nil : Int?, post_id = nil : Int?, tags = [] of String : Array(String)?, ) return unless post_id && tags # build sql args args = { "site_id": (site_id || @models.site.get_default).to_s, "post_id": post_id.to_s, } # delete existing post tags query(:set_tags_delete, args, nil) if tags.size > 0 @models.tag.add_tags(tags) # add new post tags query(:set_tags_insert, args, { "tags": tags.map { |tag| "'" + @db.quote(tag) + "'" }.join(','), }) end end end end