From f0f87c439037715cac407004e2220c678d96757a Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Sun, 6 Mar 2016 23:12:06 -0500 Subject: chaos! --- src/guff/api-methods.cr | 12 +++- src/guff/database-updater.cr | 16 +++++- src/guff/database.cr | 3 +- src/guff/model.cr | 45 ++++++++++++++- src/guff/models.cr | 5 ++ src/guff/post-model.cr | 131 ++++++++++++++++++++++++++++++++++++------ src/guff/results.cr | 34 +++++++++++ src/guff/tag-model.cr | 6 ++ src/guff/template-database.cr | 10 ++-- src/guff/template-token.cr | 14 +++-- src/guff/template.cr | 10 ++-- 11 files changed, 249 insertions(+), 37 deletions(-) create mode 100644 src/guff/results.cr diff --git a/src/guff/api-methods.cr b/src/guff/api-methods.cr index f0df8bc..598f050 100644 --- a/src/guff/api-methods.cr +++ b/src/guff/api-methods.cr @@ -321,7 +321,17 @@ module Guff context : HTTP::Server::Context, args : Hash(String, String) ) - @models.post.get_posts(args).to_json + @models.post.get_posts( + site_id: get_site_id(context.request.headers["host"]?), + q: args["q"]? || "", + page: args["page"]? ? args["page"].to_i : 1, + tags: args["tags"]? ? [args["tags"].split(/,/)] : [] of Array(String), + ).to_json + end + + private def get_site_id(host : String?) + # TODO + 0 end private def do_post_add_post( diff --git a/src/guff/database-updater.cr b/src/guff/database-updater.cr index c1d4188..daf77fd 100644 --- a/src/guff/database-updater.cr +++ b/src/guff/database-updater.cr @@ -86,7 +86,7 @@ module Guff CREATE INDEX in_posts_slug ON posts(slug) }], }, { - id: "2-create-indices", + id: "2-create-tags", backup: false, sql: [%{ @@ -96,6 +96,20 @@ module Guff ) }, %{ CREATE INDEX in_tags_name ON tags(name) + }, %{ + CREATE TABLE post_tags ( + tag_id INTEGER NOT NULL + REFERENCES tags(tag_id), + + post_id INTEGER NOT NULL + REFERENCES posts(post_id), + + UNIQUE(tag_id, post_id) + ) + }, %{ + CREATE INDEX in_post_tags_tag_id ON post_tags(tag_id) + }, %{ + CREATE INDEX in_post_tags_post_id ON post_tags(post_id) }], }, { id: "3-create-domains", diff --git a/src/guff/database.cr b/src/guff/database.cr index 6882712..bab300f 100644 --- a/src/guff/database.cr +++ b/src/guff/database.cr @@ -45,7 +45,7 @@ module Guff run(sql, args) do |rs| if rs.next - r = rs[0] as String + r = rs[0].to_s end end @@ -142,6 +142,7 @@ module Guff &block : Proc(::SQLite3::ResultSet, Nil) \ ) # build statement + puts "sql = %s" % [sql] st = prepare(sql) # exec and close statement diff --git a/src/guff/model.cr b/src/guff/model.cr index ce24427..515af4e 100644 --- a/src/guff/model.cr +++ b/src/guff/model.cr @@ -1,10 +1,49 @@ module Guff class Model - def initialize(@models : Models) + getter :db + + def initialize(@models : Models, @templates : TemplateCache) + @db = TemplateDatabase.new(@models.db, @templates) + end + + def one( + key : Symbol, + args : Array(String) | Hash(String, String) | Nil, + tmpl_args : Hash(String, String) | Nil + ) + @db.one(key, args, tmpl_args) + end + + def row( + key : Symbol, + args : Array(String) | Hash(String, String) | Nil, + tmpl_args : Hash(String, String) | Nil + ) + @db.row(key, args, tmpl_args) + end + + def all( + key : Symbol, + args : Array(String) | Hash(String, String) | Nil, + tmpl_args : Hash(String, String) | Nil, + &block : Proc(Hash(String, ::SQLite3::Value), Nil) \ + ) + @db.all(key, args, tmpl_args, &block) + end + + def query( + key : Symbol, + args : Array(String) | Hash(String, String) | Nil, + tmpl_args : Hash(String, String) | Nil + ) + @db.query(key, args, tmpl_args) end - def db - @models.db + def template( + key : Symbol, + args : Hash(String, String) | Nil + ) + @db.template(key, args) end end end diff --git a/src/guff/models.cr b/src/guff/models.cr index 631be8d..24cc9f8 100644 --- a/src/guff/models.cr +++ b/src/guff/models.cr @@ -32,5 +32,10 @@ module Guff post: PostModel, tag: TagModel, }) + + def default_site_id + # TODO + 0 + end end end diff --git a/src/guff/post-model.cr b/src/guff/post-model.cr index 112589b..4e2bf63 100644 --- a/src/guff/post-model.cr +++ b/src/guff/post-model.cr @@ -1,35 +1,132 @@ module Guff class PostModel < Model - SQL = { + 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 a.post_id, + a.site_id, a.name, a.body, - a.html + a.html, - FROM posts a - JOIN ( - SELECT DISTICT - c.post_id + (SELECT group_agg(d.name, '|') - FROM post_tags c + FROM post_tags c + JOIN tags d + ON (d.tag_id = c.tag_id) - JOIN tags d - ON (d.tag_id = c.tag_id) - JOIN (VALUES (%{tags}) e(name) - ON (e.name = d.name) - ) b + WHERE d.post_id = a.post_id) AS tags - WHERE c.%{filter} + 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} - OFFSET ? LIMIT ? + OFFSET :offset LIMIT :limit ", + }) + + def initialize(models : Models) + super(models, SQL) + end + + SORTS = { + "posted_at": "a.posted_at", } - def get_posts(req) - # TODO - [{foo: "bar"}, {foo: "asdf"}] + def get_posts( + site_id = nil : Int32?, + q = "" : String, + tags = [] of Array(String) : Array(Array(String)), + page = 1 : Int32, + limit = 20 : Int32, + sort = "posted_at" : String, + dir = "desc" : String, + ) + # build sql args + sql_args = { + "site_id": "0" # (site_id || @models.default_site_id).to_s, + } of String => String + + # build tmpl args + tmpl_args = { + # TODO + "filter": "1 = 1", # true + "sort": SORTS[sort], + "dir": dir, + "tags": get_tags_filter(tags), + } of String => String + + # 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 Hash(String, ::SQLite3::Value) + 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 << row + nil + end + end + + # return result + Results(Hash(String, ::SQLite3::Value)).new( + page: page, + limit: limit, + num_rows: num_rows, + rows: rows, + ) + end + + private def get_tags_filter(tag_sets : Array(Array(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 "), + }) : " + SELECT post_id + FROM posts + LIMIT 0 + " end def add_post(req) diff --git a/src/guff/results.cr b/src/guff/results.cr new file mode 100644 index 0000000..0dbc351 --- /dev/null +++ b/src/guff/results.cr @@ -0,0 +1,34 @@ +module Guff + class Results(R) + getter :page + getter :limit + getter :num_rows + getter :rows + + def initialize( + @page = 1 : Int32, + @limit = 1 : Int32, + @num_rows = 0 : Int32, + @rows = [] of R : Array(R), + ) + end + + def to_json + { + meta: { + page: @page, + limit: @limit, + num_pages: (@num_rows / @limit).ceil, + num_rows: @num_rows, + }, + + rows: @rows.map { |row| + row.reduce({} of String => String) do |r, k, v| + r[k] = v.to_s + r + end + }, + }.to_json + end + end +end diff --git a/src/guff/tag-model.cr b/src/guff/tag-model.cr index 311a4b5..5d758d1 100644 --- a/src/guff/tag-model.cr +++ b/src/guff/tag-model.cr @@ -1,4 +1,10 @@ module Guff class TagModel < Model + SQL = TemplateCache.new({ + } of Symbol => String) + + def initialize(models : Models) + super(models, SQL) + end end end diff --git a/src/guff/template-database.cr b/src/guff/template-database.cr index 2232de3..7ff9690 100644 --- a/src/guff/template-database.cr +++ b/src/guff/template-database.cr @@ -8,7 +8,7 @@ module Guff args : Array(String) | Hash(String, String) | Nil, tmpl_args : Hash(String, String)? ) - @db.one(sql_for(key, tmpl_args), args) + @db.one(template(key, tmpl_args), args) end def row( @@ -16,7 +16,7 @@ module Guff args : Array(String) | Hash(String, String) | Nil, tmpl_args : Hash(String, String)?, ) - @db.row(sql_for(key, tmpl_args), args) + @db.row(template(key, tmpl_args), args) end def all( @@ -25,7 +25,7 @@ module Guff tmpl_args : Hash(String, String)?, &block : Proc(Hash(String, ::SQLite3::Value), Nil) \ ) - @db.all(sql_for(key, tmpl_args), args, &block) + @db.all(template(key, tmpl_args), args, &block) end def query( @@ -33,10 +33,10 @@ module Guff args : Array(String) | Hash(String, String) | Nil, tmpl_args : Hash(String, String)? ) - @db.query(sql_for(key, tmpl_args), args) + @db.query(template(key, tmpl_args), args) end - private def sql_for( + def template( key : Symbol, args : Hash(String, String)? ) diff --git a/src/guff/template-token.cr b/src/guff/template-token.cr index 04e8f11..35170d8 100644 --- a/src/guff/template-token.cr +++ b/src/guff/template-token.cr @@ -1,20 +1,24 @@ module Guff class TemplateToken getter :type + getter :value - def initialize(@type : Symbol, @val : String) + TOKEN_TYPES = %i{key val} + + def initialize(@type : Symbol, @value : String) + raise "invalid type: %s" % [@type] unless TOKEN_TYPES.includes?(@type) end def get(args : Hash(String, String)) case @type when :key - raise "missing key: #{@val}" unless args.has_key?(@val) - args[@val] + raise "missing key: %s" % [@value] unless args.has_key?(@value) + args[@value] when :val - @val + @value else # never reached - raise "unknown token type: #{@type}" + raise "unknown token type: %s" % [@type] end end end diff --git a/src/guff/template.cr b/src/guff/template.cr index 01a4c86..4b54fb6 100644 --- a/src/guff/template.cr +++ b/src/guff/template.cr @@ -10,14 +10,16 @@ module Guff def run(args = nil : Hash(String, String)?) : String if @has_keys # check template args - if args || args.size == 0 - raise "missing template args: %s" % [@tokens.select { |t| + if !args || args.size == 0 + raise "missing template args: %s" % [@tokens.select { |t| t.type == :key - }.join(", ")] + }.map { |t| + t.value + }.sort.join(", ")] end # build result - String.builder do |r| + String.build do |r| @tokens.each do |t| r << t.get(args) end -- cgit v1.2.3