add state support

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,
@@ -27,6 +31,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
         post_id     INTEGER PRIMARY KEY,
@@ -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
@@ -64,10 +93,14 @@ module Guff
                     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