aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/guff/api-methods.cr12
-rw-r--r--src/guff/database-updater.cr16
-rw-r--r--src/guff/database.cr3
-rw-r--r--src/guff/model.cr45
-rw-r--r--src/guff/models.cr5
-rw-r--r--src/guff/post-model.cr131
-rw-r--r--src/guff/results.cr34
-rw-r--r--src/guff/tag-model.cr6
-rw-r--r--src/guff/template-database.cr10
-rw-r--r--src/guff/template-token.cr14
-rw-r--r--src/guff/template.cr10
11 files changed, 249 insertions, 37 deletions
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
generated by cgit v1.2.3 (git 2.39.1) at 2024-09-19 16:48:23 +0000