From 433dbf9f3f10d1fabf7598e87475aad0f0b0da91 Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Sat, 16 Jul 2016 01:59:36 -0400 Subject: show blog contents --- src/guff/handlers.cr | 20 ++++--- src/guff/models/blog.cr | 111 ++++++++++++++++++++++++++++----------- src/guff/paged-result-set.cr | 2 + src/guff/views/blog/list-item.cr | 2 +- src/guff/views/blog/list.cr | 6 +-- src/guff/views/blog/post.cr | 7 +++ src/views/blog/list-item.ecr | 7 ++- src/views/blog/post.ecr | 30 +++++++++++ 8 files changed, 140 insertions(+), 45 deletions(-) create mode 100644 src/guff/views/blog/post.cr create mode 100644 src/views/blog/post.ecr diff --git a/src/guff/handlers.cr b/src/guff/handlers.cr index 8b5d96c..8690095 100644 --- a/src/guff/handlers.cr +++ b/src/guff/handlers.cr @@ -446,31 +446,35 @@ module Guff::Handlers $}x def call(context : HTTP::Server::Context) - if id = get_id(context.request) + if r = get(context.request) # TODO: render page context.response.headers["x-frame-options"] = "SAMEORIGIN" context.response.content_type = "text/html; charset=utf-8" context.response.status_code = 200 - context.response << "blog post id: #{id}" + Views::Blog::PostView.new(@context, r).to_s(context.response) else # unknown page call_next(context) end end - private def get_id(request : HTTP::Request) : Int64? + private def get(request : HTTP::Request) : Hash(String, String)? r = nil if request.method == "GET" if md = PATH_RE.match(request.path.not_nil!) if site_id = get_site_id(request.headers["host"]?) - r = @context.models.blog.get_id( + results = @context.models.blog.find( site_id: site_id, year: md["year"].to_i, month: md["month"].to_i, day: md["day"].to_i, slug: md["slug"], ) + + if results.num_rows > 0 + r = results.rows.first + end end end end @@ -494,20 +498,20 @@ module Guff::Handlers $}x def call(context : HTTP::Server::Context) - if ids = get_ids(context.request) + if r = find(context.request) # TODO: render page context.response.headers["x-frame-options"] = "SAMEORIGIN" context.response.content_type = "text/html; charset=utf-8" context.response.status_code = 200 - Views::Blog::ListView.new(@context, ids).to_s(context.response) + Views::Blog::ListView.new(@context, r).to_s(context.response) else # unknown page call_next(context) end end - private def get_ids(request : HTTP::Request) : Array(Int64)? + private def find(request : HTTP::Request) : PagedResultSet? r = nil if request.method == "GET" @@ -516,7 +520,7 @@ module Guff::Handlers # get request parameters params = request.query_params - r = @context.models.blog.get_ids( + r = @context.models.blog.find( site_id: site_id, year: md["year"]? ? md["year"].to_i : nil, month: md["month"]? ? md["month"].to_i : nil, diff --git a/src/guff/models/blog.cr b/src/guff/models/blog.cr index 9c25b77..ed5bddc 100644 --- a/src/guff/models/blog.cr +++ b/src/guff/models/blog.cr @@ -2,8 +2,8 @@ require "./model" class Guff::Models::BlogModel < Guff::Models::Model SQL = { - get_ids: " - SELECT b.post_id + count: " + SELECT COUNT(*) FROM sites a JOIN posts b @@ -12,21 +12,50 @@ class Guff::Models::BlogModel < Guff::Models::Model ON (c.post_id = b.post_id) JOIN states d ON (d.state_id = b.state_id) + JOIN users e + ON (e.user_id = b.created_by) WHERE a.site_id = ? + AND d.state = 'public' + AND (0 + strftime('%%s')) + BETWEEN (0 + strftime('%%s', COALESCE(b.posted_at, 'now'))) + AND (0 + strftime('%%s', COALESCE(b.expires_at,'now'))) AND %s + ", + + find: " + SELECT b.post_id, + b.site_id, + b.posted_at, + datetime(b.posted_at, 'localtime') AS posted_at_text, + b.slug, + b.name, + b.body, + e.user_id, + e.name AS user_name + + FROM sites a + JOIN posts b + ON (b.site_id = a.site_id) + JOIN blogs c + ON (c.post_id = b.post_id) + JOIN states d + ON (d.state_id = b.state_id) + JOIN users e + ON (e.user_id = b.created_by) + + WHERE a.site_id = ? AND d.state = 'public' - -- TODO: handle posted_at and expired_at + AND (0 + strftime('%%s')) + BETWEEN (0 + strftime('%%s', COALESCE(b.posted_at, 'now'))) + AND (0 + strftime('%%s', COALESCE(b.expires_at,'now'))) + AND %s ORDER BY COALESCE(b.posted_at, b.created_at) DESC LIMIT ? OFFSET ? ", - add: " - INSERT INTO blogs(post_id) VALUES (?) - ", - get: " SELECT a.post_id, a.site_id, @@ -46,35 +75,36 @@ class Guff::Models::BlogModel < Guff::Models::Model WHERE a.post_id = ? ", - } - def get_id( - site_id : Int64, - year : Int32, - month : Int32, - day : Int32, - slug : String - ) : Int64? - ids = get_ids(site_id, year, month, day, slug) - (ids.size > 0) ? ids.first : nil - end + + add: " + INSERT INTO blogs(post_id) VALUES (?) + ", + } # TODO: make this configurable LIMIT = 50 - def get_ids( + def find( site_id : Int64, + post_id : Int32? = nil, year : Int32? = nil, month : Int32? = nil, day : Int32? = nil, slug : String? = nil, page : Int32? = nil - ) : Array(Int64) + ) : PagedResultSet sql = ["1"] args = [site_id.to_s] # STDERR.puts "DEBUG: site_id = #{site_id}, year = #{year}, month = #{month}, day = #{day}, slug = \"#{slug}\"" + if post_id + # add post_id filter + sql << "b.post_id = ?" + args << post_id.to_s + end + if year # add year filter sql << "strftime('%Y', b.posted_at) + 0 = ? + 0" @@ -99,25 +129,46 @@ class Guff::Models::BlogModel < Guff::Models::Model args << slug end + # get page page ||= 1 raise "invalid page: #{page}" if page < 1 + # STDERR.puts "DEBUG: args = #{args.to_json}" + + num_rows = @context.dbs.ro.one(SQL[:count] % [ + sql.join(" AND ") + ], args).not_nil!.to_i64 + + # add limit and offset to args args << LIMIT.to_s args << ((page - 1) * LIMIT).to_s - # STDERR.puts "DEBUG: args = #{args.to_json}" - - # exec query, build result - r = [] of Int64 - @context.dbs.ro.all(SQL[:get_ids] % sql.join(" AND "), args) do |row| - p row - r << row["post_id"] as Int64 + # get rows + rows = [] of Hash(String, String) + @context.dbs.ro.all(SQL[:find] % [ + sql.join(" AND ") + ], args) do |row| + # p row + rows << row.reduce({} of String => String) do |r, kv| + r[kv[0]] = kv[1].to_s + r + end end # return results - r + PagedResultSet.new( + page: page, + num_rows: num_rows, + limit: LIMIT, + rows: rows, + ) + end + + def get(post_id : Int64) + @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil! end + def add( site_id : Int64, user_id : Int64, @@ -172,8 +223,4 @@ class Guff::Models::BlogModel < Guff::Models::Model body: body, ) end - - def get(post_id : Int64) - @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil! - end end diff --git a/src/guff/paged-result-set.cr b/src/guff/paged-result-set.cr index 2db3619..2798eb9 100644 --- a/src/guff/paged-result-set.cr +++ b/src/guff/paged-result-set.cr @@ -1,6 +1,8 @@ require "json" class Guff::PagedResultSet + getter :page, :num_rows, :limit, :rows + def initialize( @page : Int32, @num_rows : Int64, diff --git a/src/guff/views/blog/list-item.cr b/src/guff/views/blog/list-item.cr index 3ebe411..12add51 100644 --- a/src/guff/views/blog/list-item.cr +++ b/src/guff/views/blog/list-item.cr @@ -1,5 +1,5 @@ class Guff::Views::Blog::ListItemView < Guff::Views::HTMLView - def initialize(context : Context, @post_id : Int64) + def initialize(context : Context, @item : Hash(String, String)) super(context) end diff --git a/src/guff/views/blog/list.cr b/src/guff/views/blog/list.cr index 31437a6..0b26f98 100644 --- a/src/guff/views/blog/list.cr +++ b/src/guff/views/blog/list.cr @@ -4,14 +4,14 @@ class Guff::Views::Blog::ListView < Guff::Views::HTMLView TITLE = "Blog List" - def initialize(context : Context, @post_ids : Array(Int64)) + def initialize(context : Context, @results : PagedResultSet) super(context) end def posts String.build do |io| - @post_ids.each do |id| - ListItemView.new(@context, id).to_s(io) + @results.rows.each do |row| + ListItemView.new(@context, row).to_s(io) end end end diff --git a/src/guff/views/blog/post.cr b/src/guff/views/blog/post.cr new file mode 100644 index 0000000..4b1dc1e --- /dev/null +++ b/src/guff/views/blog/post.cr @@ -0,0 +1,7 @@ +class Guff::Views::Blog::PostView < Guff::Views::HTMLView + def initialize(context : Context, @item : Hash(String, String)) + super(context) + end + + ECR.def_to_s("src/views/blog/post.ecr") +end diff --git a/src/views/blog/list-item.ecr b/src/views/blog/list-item.ecr index 13131e0..23b868e 100644 --- a/src/views/blog/list-item.ecr +++ b/src/views/blog/list-item.ecr @@ -1,3 +1,8 @@
- id: <%= @post_id %> + <%= h(@item["name"]) %>
+ + by <%= h(@item["user_name"]) %> + on <%= h(@item["posted_at_text"]) %> +

+ <%= @item["body"] %>
diff --git a/src/views/blog/post.ecr b/src/views/blog/post.ecr new file mode 100644 index 0000000..d0aa534 --- /dev/null +++ b/src/views/blog/post.ecr @@ -0,0 +1,30 @@ + + + + + <%= + h(@item["name"]) + %> + + <% + # TODO: add theme styles + %> + + + + blog post + +
+ <%= h(@item["name"]) %>
+ + by <%= h(@item["user_name"]) %> + on <%= h(@item["posted_at_text"]) %> +

+ <%= @item["body"] %> +
+ + + <% + # TODO: add theme scripts + %> + -- cgit v1.2.3