aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/guff/api/methods.cr32
-rw-r--r--src/guff/api/post.cr9
-rw-r--r--src/guff/migrations.cr63
-rw-r--r--src/guff/models/post.cr128
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
generated by cgit v1.2.3 (git 2.39.1) at 2024-09-19 17:14:24 +0000