From 7417966101a0b0bbd0fefc8723482ae5a2010f19 Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Wed, 9 Mar 2016 13:38:24 -0500 Subject: add state support --- src/guff/api/methods.cr | 32 +++++++++--- src/guff/api/post.cr | 9 ++-- src/guff/migrations.cr | 63 +++++++++++++++--------- src/guff/models/post.cr | 128 ++++++++++++++++++++++++++++++++---------------- 4 files changed, 157 insertions(+), 75 deletions(-) diff --git a/src/guff/api/methods.cr b/src/guff/api/methods.cr index f3a7f3c..6c38eb3 100644 --- a/src/guff/api/methods.cr +++ b/src/guff/api/methods.cr @@ -13,7 +13,6 @@ module Guff text: "Search string.", type: :text, required: false, - default: "", }, "year": { @@ -43,17 +42,14 @@ module Guff "tags": { text: "Comma-separated list of tags (union)", - # type: :tag_list, type: :json, required: false, - default: "", }, "sort": { text: "Sort order of results", type: :sort, required: false, - default: "date,desc", }, "cols": { @@ -61,6 +57,19 @@ module Guff type: :text, required: false, }, + + "slug": { + text: "Post slug", + type: :text, + required: false, + }, + + "state": { + text: "Post state (draft, posted, deleted)", + type: :state, + required: false, + default: "posted", + }, }, }, @@ -90,7 +99,13 @@ module Guff # type: :tag_list, type: :json, required: false, - default: "", + }, + + "state": { + text: "Post state (draft, posted, deleted)", + type: :state, + required: false, + default: "draft", }, }, }, @@ -122,9 +137,9 @@ module Guff required: false, }, - "posted": { - text: "Is this post posted?", - type: :bool, + "state": { + text: "Post state (draft, posted, deleted)", + type: :state, required: false, }, }, @@ -326,6 +341,7 @@ module Guff int: /^\d+$/, int_list: /^\d+(?:,\d+)*$/, sort: /^[a-z0-9_]+,(?:asc|desc)$/, + state: /^(?:draft|posted|deleted)$/, # FIXME: lock these down more json: /.*/, diff --git a/src/guff/api/post.cr b/src/guff/api/post.cr index 55abd86..499e7c0 100644 --- a/src/guff/api/post.cr +++ b/src/guff/api/post.cr @@ -8,6 +8,8 @@ module Guff year: "year", month: "month", day: "day", + slug: "slug", + state: "state", } private def do_post_get_posts( @@ -39,6 +41,7 @@ module Guff name: args["name"], body: args["body"], tags: get_tags(args["tags"]?), + state: args["state"]?, ) # return json @@ -53,10 +56,10 @@ module Guff site_id: get_site(context), post_id: args["post_id"].to_i, slug: args["slug"]?, - name: args["name"], - body: args["body"], + name: args["name"]?, + body: args["body"]?, tags: args.has_key?("tags") ? get_tags(args["tags"]?) : nil, - posted: args.has_key?("posted") ? (args["posted"] == "t") : nil, + state: args["state"]?, ) # return json diff --git a/src/guff/migrations.cr b/src/guff/migrations.cr index 2d08c9e..4763929 100644 --- a/src/guff/migrations.cr +++ b/src/guff/migrations.cr @@ -5,14 +5,18 @@ module Guff id: "0-null", sql: %w{}, }, { - id: "1-create-tags-and-posts", + id: "1-create-metadata", sql: [%{ CREATE TABLE metadata ( name TEXT PRIMARY KEY, value TEXT NOT NULL ) - }, %{ + }], + }, { + id: "2-create-sites", + + sql: [%{ CREATE TABLE sites ( -- site id site_id INTEGER PRIMARY KEY, @@ -26,6 +30,32 @@ module Guff ) }, %{ INSERT INTO sites(name, is_default) VALUES ('Default', 1) + }, %{ + CREATE TABLE domains ( + -- domain + domain TEXT PRIMARY KEY CHECK ( + LENGTH(domain) > 0 AND + domain = LOWER(domain) + ), + + -- site id + site_id INTEGER NOT NULL + REFERENCES sites(site_id) + ) + }], + }, { + id: "3-create-posts", + + sql: [%{ + CREATE TABLE post_states ( + state INTEGER PRIMARY KEY, + name TEXT UNIQUE NOT NULL + ) + }, %{ + INSERT INTO post_states(state, name) VALUES + (0, 'draft'), + (1, 'posted'), + (2, 'deleted') }, %{ CREATE TABLE posts ( -- unique id @@ -34,9 +64,8 @@ module Guff site_id INTEGER NOT NULL REFERENCES sites(site_id), - -- false if this post has been deleted - is_active BOOLEAN NOT NULL - DEFAULT true, + state INTEGER NOT NULL DEFAULT 0 + REFERENCES post_states(state), -- when this post was created created_at TIMESTAMP WITH TIME ZONE @@ -45,7 +74,7 @@ module Guff -- when this post was posted -- (that is, the draft status was removed) posted_at TIMESTAMP WITH TIME ZONE - NOT NULL DEFAULT CURRENT_TIMESTAMP, + DEFAULT NULL, -- title of post name TEXT NOT NULL @@ -63,11 +92,15 @@ module Guff body TEXT NOT NULL CHECK (LENGTH(body) > 0) ) + }, %{ + CREATE INDEX in_posts_site_id ON posts(site_id) }, %{ CREATE INDEX in_posts_slug ON posts(slug) + }, %{ + CREATE INDEX in_posts_state ON posts(state) }], }, { - id: "2-create-tags", + id: "4-create-tags", sql: [%{ CREATE TABLE tags ( @@ -91,21 +124,5 @@ module Guff }, %{ CREATE INDEX in_post_tags_post_id ON post_tags(post_id) }], - }, { - id: "3-create-domains", - - sql: [%{ - CREATE TABLE domains ( - -- domain - domain TEXT PRIMARY KEY CHECK ( - LENGTH(domain) > 0 AND - domain = LOWER(domain) - ), - - -- site id - site_id INTEGER NOT NULL - REFERENCES sites(site_id) - ) - }], }] end diff --git a/src/guff/models/post.cr b/src/guff/models/post.cr index 7fd6ec1..7814d9f 100644 --- a/src/guff/models/post.cr +++ b/src/guff/models/post.cr @@ -57,7 +57,11 @@ module Guff remove_posts: " UPDATE posts - SET is_active = 0 + + SET state = (SELECT state + FROM post_states + WHERE name = 'deleted') + WHERE site_id = :site_id AND post_id IN (%{post_ids}) ", @@ -199,45 +203,65 @@ module Guff ) end - DATE_FILTERS = { - :year => { - :re => /^\d{4}$/, - :fmt => "%Y", + FILTERS = { + year: { + type: :int, + re: /^\d{4}$/, + sql: "(strftime('%%Y', a.posted_at) + 0 = %d)", }, - :month => { - :re => /^\d{1,2}$/, - :fmt => "%m", + month: { + type: :int, + re: /^\d{1,2}$/, + sql: "(strftime('%%m', a.posted_at) + 0 = %d)", }, - :day => { - :re => /^\d{1,2}$/, - :fmt => "%d", + day: { + type: :int, + re: /^\d{1,2}$/, + sql: "(strftime('%%d', a.posted_at) + 0 = %d)", }, - } - DATE_FILTER_CLAUSE = "( - strftime('%s', a.posted_at) + 0 = %d - )" + slug: { + type: :string, + sql: "a.slug = '%s'" + }, - SLUG_FILTER_CLAUSE = "( - a.slug = '%s' - )" + state: { + type: :string, + sql: "( + a.state = ( + SELECT state + FROM post_states + WHERE name = '%s' + ) + )", + }, + } private def get_filter_clause( filters : Hash(Symbol, String) ) : String - r = [ - "1 = 1" # true - ] + # define results + r = ["1 = 1"] # true - DATE_FILTERS.each do |key, f| + FILTERS.each do |key, f| if val = filters[key]? # check value format - raise "invalid #{key} filter" unless val =~ (f[:re] as Regex) + if f[:re]? && val !~ (f[:re] as Regex) + raise "invalid #{key} filter" + end - # add to filters - r << DATE_FILTER_CLAUSE % [f[:fmt] as String, val.to_i] + # add to results + r << (f[:sql] as String) % [case f[:type] + when :int + val.to_i + when :string + @db.quote(val) + else + # never reached + raise "unknown format type: #{f[:type]}" + end] end end @@ -245,17 +269,12 @@ module Guff # TODO end - # add slug filter - if filters.has_key?(:slug) - r << SLUG_FILTER_CLAUSE % [@db.quote(filters[:slug])] - end - - # return result + # return filter clause r.join(" AND ") end private def get_sort_clause( - sort : String + sort = "posted_at" : String ) : String # verify sort column raise "unknown sort column" unless COLUMNS.has_key?(sort) @@ -307,6 +326,7 @@ module Guff name = "" : String, body = "" : String, tags = [] of String : Array(String), + state = "draft" : String?, ) : Int64 post_id = -1_i64 @@ -327,11 +347,12 @@ module Guff # get post id post_id = last_insert_row_id - # set post tags - set_tags( - site_id: site_id, - post_id: post_id, - tags: tags, + # update state and tags + update_post( + site_id: site_id, + post_id: post_id, + tags: tags, + state: state, ) end @@ -339,6 +360,10 @@ module Guff post_id end + ####################### + # update_post methods # + ####################### + def update_post( site_id = nil : Int?, post_id = nil : Int?, @@ -346,10 +371,11 @@ module Guff name = nil : String?, body = nil : String?, tags = nil : Array(String)?, - posted = nil : Bool? + state = nil : String? ) raise "null post_id" if post_id.nil? + # build initial query sets = [] of String args = { "site_id": (site_id || @models.site.get_default).to_s, @@ -371,9 +397,20 @@ module Guff args["body"] = body end - unless posted.nil? - val = posted ? "CURRENT_TIMESTAMP" : "NULL" - sets << "posted_at = %s" % [val] + if state + # update state + sets << "state = ( + SELECT state + FROM post_states + WHERE name = :state + )" + args["state"] = state + + # update posted_at + # FIXME: there should be a better way to do this + sets << "posted_at = %s" % [ + (state == "posted") ? "CURRENT_TIMESTAMP" : "NULL" + ] end if sets.size > 0 || tags @@ -399,6 +436,10 @@ module Guff end end + ################ + # remove_posts # + ################ + def remove_posts( site_id = nil : Int?, post_ids = [] of Int : Array(Int) @@ -415,6 +456,10 @@ module Guff nil end + #################### + # set_tags methods # + #################### + def set_tags( site_id = nil : Int?, post_id = nil : Int?, @@ -444,6 +489,7 @@ module Guff end end + # no return value nil end end -- cgit v1.2.3