From d5f49eee6c08bda1dad31bc906f54e44737e843f Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Tue, 8 Mar 2016 17:12:46 -0500 Subject: mv src/guff/{,models/}*-model.cr --- src/guff/models/post.cr | 451 ++++++++++++++++++++++++++++++++++++++++++++++++ src/guff/models/site.cr | 27 +++ src/guff/models/tag.cr | 62 +++++++ 3 files changed, 540 insertions(+) create mode 100644 src/guff/models/post.cr create mode 100644 src/guff/models/site.cr create mode 100644 src/guff/models/tag.cr (limited to 'src/guff/models') diff --git a/src/guff/models/post.cr b/src/guff/models/post.cr new file mode 100644 index 0000000..580a9b7 --- /dev/null +++ b/src/guff/models/post.cr @@ -0,0 +1,451 @@ +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?, + filters = {} of Symbol => String : Hash(Symbol, 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": get_filter_clause(filters), + "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 + + DATE_FILTERS = { + :year => { + :re => /^\d{4}$/, + :fmt => "%Y", + }, + + :month => { + :re => /^\d{1,2}$/, + :fmt => "%m", + }, + + :day => { + :re => /^\d{1,2}$/, + :fmt => "%d", + }, + } + + DATE_FILTER_CLAUSE = "( + strftime('%s', a.posted_at) + 0 = %d + )" + + private def get_filter_clause( + filters : Hash(Symbol, String) + ) : String + r = [ + "1 = 1" # true + ] + + DATE_FILTERS.each do |key, f| + if val = filters[key]? + # check value format + raise "invalid #{key} filter" unless val =~ (f[:re] as Regex) + + # add to filters + r << DATE_FILTER_CLAUSE % [f[:fmt] as String, val.to_i] + end + end + + if filters.has_key?(:q) + # TODO + end + + # return result + r.join(" AND ") + 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 diff --git a/src/guff/models/site.cr b/src/guff/models/site.cr new file mode 100644 index 0000000..9c8899a --- /dev/null +++ b/src/guff/models/site.cr @@ -0,0 +1,27 @@ +module Guff + class SiteModel < Model + SQL = TemplateCache.new({ + get_default: " + SELECT site_id + FROM sites + WHERE is_default + LIMIT 1 + ", + }) + + def initialize(models : Models) + super(models, SQL) + end + + def get_default : Int + r = one(:get_default, nil, {} of String => String) + raise "no default site" unless r + r.to_i + end + + def to_site(host : String?) : Int + # TODO + get_default + end + end +end diff --git a/src/guff/models/tag.cr b/src/guff/models/tag.cr new file mode 100644 index 0000000..997cdfd --- /dev/null +++ b/src/guff/models/tag.cr @@ -0,0 +1,62 @@ +module Guff + class TagModel < Model + SQL = TemplateCache.new({ + add_tags: " + INSERT INTO tags(name) VALUES %{tags} + ", + + get_tags: " + SELECT tag_id, + name + + FROM tags + + WHERE name IN (%{tags}) + ", + } of Symbol => String) + + def initialize(models : Models) + super(models, SQL) + end + + def add_tags(tags : Array(String)) + missing_tags = get_missing_tags(tags) + + if missing_tags.size > 0 + query(:add_tags, nil, { + "tags": missing_tags.map { |tag| + "('" + @db.quote(tag) + "')" + }.join(','), + }) + end + end + + private def get_missing_tags( + tags : Array(String) + ) : Array(String) + # get ids of existing tags + ids = get_ids(tags) + + # return missing tags + tags.reject { |tag| ids[tag]? } + end + + private def get_ids( + tags = [] of String : Array(String) + ) : Hash(String, Int32) + r = {} of String => Int32 + + all(:get_tags, nil, { + "tags": tags.map { |tag| + "'" + @db.quote(tag) + "'" + }.join(','), + }) do |row| + # add to results + r[row["name"] as String] = row["tag_id"].to_s.to_i + end + + # return result + r + end + end +end -- cgit v1.2.3