chaos!

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