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.cr | 2 +- src/guff/models/post.cr | 451 ++++++++++++++++++++++++++++++++++++++++++++++++ src/guff/models/site.cr | 27 +++ src/guff/models/tag.cr | 62 +++++++ src/guff/post-model.cr | 451 ------------------------------------------------ src/guff/site-model.cr | 27 --- src/guff/tag-model.cr | 62 ------- 7 files changed, 541 insertions(+), 541 deletions(-) create mode 100644 src/guff/models/post.cr create mode 100644 src/guff/models/site.cr create mode 100644 src/guff/models/tag.cr delete mode 100644 src/guff/post-model.cr delete mode 100644 src/guff/site-model.cr delete mode 100644 src/guff/tag-model.cr diff --git a/src/guff/models.cr b/src/guff/models.cr index 0e09752..52437d4 100644 --- a/src/guff/models.cr +++ b/src/guff/models.cr @@ -1,6 +1,6 @@ require "./database" require "./database-updater" -require "./site-model" +require "./models/*" private macro define_model_getters(hash) {% for name, klass in hash %} 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 diff --git a/src/guff/post-model.cr b/src/guff/post-model.cr deleted file mode 100644 index 580a9b7..0000000 --- a/src/guff/post-model.cr +++ /dev/null @@ -1,451 +0,0 @@ -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/site-model.cr b/src/guff/site-model.cr deleted file mode 100644 index 9c8899a..0000000 --- a/src/guff/site-model.cr +++ /dev/null @@ -1,27 +0,0 @@ -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/tag-model.cr b/src/guff/tag-model.cr deleted file mode 100644 index 997cdfd..0000000 --- a/src/guff/tag-model.cr +++ /dev/null @@ -1,62 +0,0 @@ -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