mv src/guff/{,models/}*-model.cr

3 files changed, 540 insertions, 0 deletions

diff --git a/src/guff/models/post.cr b/src/guff/models/post.cr
new file mode 100644
index 0000000..580a9b7
--- /dev/null
+++ b/src/guff/models/post.cr
@@ -0,0 +1,451 @@
+module Guff
+  class PostModel < Model
+    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 %{cols}
+
+          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}
+         LIMIT :limit OFFSET :offset
+      ",
+
+      update_post: "
+        UPDATE posts
+           SET %{sets}
+         WHERE site_id = :site_id
+           AND post_id = :post_id
+      ",
+
+      add_post: "
+        INSERT INTO posts(site_id, slug, name, raw_body, body)
+             VALUES (:site_id, :slug, :name, :raw_body, :body)
+      ",
+
+      remove_posts: "
+        UPDATE posts
+           SET is_active = 0
+         WHERE site_id = :site_id
+           AND post_id IN (%{post_ids})
+      ",
+
+      set_tags_delete: "
+        DELETE FROM post_tags WHERE post_id = (
+          SELECT post_id
+            FROM posts
+           WHERE site_id = :site_id
+             AND post_id = :post_id
+        )
+      ",
+
+      set_tags_insert: "
+        INSERT INTO post_tags(post_id, tag_id)
+          SELECT a.post_id,
+                 b.tag_id
+
+            FROM posts a
+           CROSS JOIN tags b
+
+           WHERE a.site_id = :site_id
+             AND a.post_id = :post_id
+             AND b.name IN (%{tags})
+      ",
+    })
+
+    def initialize(models : Models)
+      super(models, SQL)
+    end
+
+    COLUMNS = {
+      "post_id": {
+        default:  true,
+        sortable: true,
+        clause:   "a.post_id",
+      },
+
+      "name": {
+        default:  true,
+        sortable: true,
+        clause:   "a.name",
+      },
+
+      "body": {
+        default:  false,
+        sortable: true,
+        clause:   "a.body",
+      },
+
+      "raw_body": {
+        default:  false,
+        sortable: true,
+        clause:   "a.raw_body",
+      },
+
+      "posted_at": {
+        default:  true,
+        sortable: true,
+        clause:   "a.posted_at",
+      },
+
+      "created_at": {
+        default:  true,
+        sortable: true,
+        clause:   "a.created_at",
+      },
+
+      "tags": {
+        default:  true,
+        sortable: false,
+        clause:   "
+          (SELECT group_concat(d.name, '|')
+
+             FROM post_tags c
+             JOIN tags d
+               ON (d.tag_id = c.tag_id)
+
+            WHERE c.post_id = a.post_id)
+        ",
+      },
+    }
+
+    #####################
+    # get_posts methods #
+    #####################
+
+    def get_posts(
+      cols = nil                        : Array(String)?,
+      site_id = nil                     : Int?,
+      filters = {} of Symbol => String  : Hash(Symbol, String),
+      tags = [] of Array(String)        : Array(Array(String)),
+      page = 1                          : Int,
+      limit = 20                        : Int,
+      sort = "posted_at"                : String,
+      dir = "desc"                      : String,
+    )
+      # build sql args
+      sql_args = {
+        "site_id": (site_id || @models.site.get_default).to_s,
+      } of String => String
+
+      # build tmpl args
+      tmpl_args = {
+        # TODO
+        "filter":   get_filter_clause(filters),
+        "sort":     get_sort_clause(sort),
+        "dir":      dir,
+        "tags":     get_tags_filter(tags),
+        "cols":     get_columns_clause(cols),
+      }
+
+      # 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 Post
+      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 << Post.new(row)
+          nil
+        end
+      end
+
+      # return result
+      Results(Post).new(
+        page:     page,
+        limit:    limit,
+        num_rows: num_rows,
+        rows:     rows,
+      )
+    end
+
+    DATE_FILTERS = {
+      :year => {
+        :re   => /^\d{4}$/,
+        :fmt  => "%Y",
+      },
+
+      :month => {
+        :re   => /^\d{1,2}$/,
+        :fmt  => "%m",
+      },
+
+      :day => {
+        :re   => /^\d{1,2}$/,
+        :fmt  => "%d",
+      },
+    }
+
+    DATE_FILTER_CLAUSE = "(
+      strftime('%s', a.posted_at) + 0 = %d
+    )"
+
+    private def get_filter_clause(
+      filters : Hash(Symbol, String)
+    ) : String
+      r = [
+        "1 = 1" # true
+      ]
+
+      DATE_FILTERS.each do |key, f|
+        if val = filters[key]?
+          # check value format
+          raise "invalid #{key} filter" unless val =~ (f[:re] as Regex)
+
+          # add to filters
+          r << DATE_FILTER_CLAUSE % [f[:fmt] as String, val.to_i]
+        end
+      end
+
+      if filters.has_key?(:q)
+        # TODO
+      end
+
+      # return result
+      r.join(" AND ")
+    end
+
+    private def get_sort_clause(
+      sort : String
+    ) : String
+      # verify sort column
+      raise "unknown sort column" unless COLUMNS.has_key?(sort)
+      raise "column is not sortable" unless COLUMNS[sort][:sortable]
+
+      # return result
+      COLUMNS[sort][:clause] as String
+    end
+
+    private def get_columns_clause(
+      cols : Array(String)?
+    ) : String
+      (cols || COLUMNS.keys.select { |col|
+        COLUMNS[col][:default]
+      }).map { |col|
+        "%s AS %s" % [COLUMNS[col][:clause], col]
+      }.join(", ")
+    end
+
+    NO_POSTS = "
+      SELECT post_id
+        FROM posts
+       LIMIT 0
+    "
+
+    private def get_tags_filter(
+      tag_sets : Array(Array(String))
+    ) : 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 "),
+      }) : NO_POSTS
+    end
+
+    ####################
+    # add_post methods #
+    ####################
+
+    def add_post(
+      site_id = nil       : Int?,
+      slug = ""           : String,
+      name = ""           : String,
+      body = ""           : String,
+      tags = [] of String : Array(String),
+    ) : Int64
+      post_id = -1_i64
+
+      # check slug, name, and body
+      { "slug": slug, "name": name, "body": body }.each do |name, text|
+        raise "invalid %s" % [name] unless text.size > 0
+      end
+
+      transaction do
+        query(:add_post, {
+          "site_id":  (site_id || @models.site.get_default).to_s,
+          "slug":     slug,
+          "name":     name,
+          "raw_body": body,
+          "body":     body,
+        }, nil)
+
+        # get post id
+        post_id = last_insert_row_id
+
+        # set post tags
+        set_tags(
+          site_id:          site_id,
+          post_id:          post_id,
+          tags:             tags,
+          use_transaction:  false,
+        )
+      end
+
+      # return post id
+      post_id
+    end
+
+    def update_post(
+      site_id = nil           : Int?,
+      post_id = nil           : Int?,
+      slug = nil              : String?,
+      name = nil              : String?,
+      body = nil              : String?,
+      tags = nil              : Array(String)?,
+      posted = nil            : Bool?
+    )
+      raise "null post_id" if post_id.nil?
+
+      sets = [] of String
+      args = {
+        "site_id": (site_id || @models.site.get_default).to_s,
+        "post_id": post_id.to_s,
+      }
+
+      if slug
+        sets << "slug = :slug"
+        args["slug"] = slug
+      end
+
+      if name
+        sets << "name = :name"
+        args["name"] = name
+      end
+
+      if body
+        sets << "body = :body, raw_body = :body"
+        args["body"] = body
+      end
+
+      unless posted.nil?
+        val = posted ? "CURRENT_TIMESTAMP" : "NULL"
+        sets << "posted_at = %s" % [val]
+      end
+
+      if sets.size > 0 || tags
+        transaction do
+          if sets.size > 0
+            # update post
+            query(:update_post, args, {
+              "sets": sets.join(','),
+            })
+          end
+
+          # TODO: post history
+
+          if tags
+            # update tags
+            set_tags(
+              site_id:          site_id,
+              post_id:          post_id,
+              tags:             tags,
+              use_transaction:  false,
+            )
+          end
+        end
+      end
+    end
+
+    def remove_posts(
+      site_id = nil : Int?,
+      post_ids = [] of Int : Array(Int)
+    )
+      query(:remove_posts, {
+        "site_id":  (site_id || @models.site.get_default).to_s,
+      }, {
+        "post_ids": post_ids.map { |post_id|
+          "'" + @db.quote(post_id.to_s) + "'"
+        }.join(',')
+      })
+
+      # no return value
+      nil
+    end
+
+    def set_tags(
+      site_id = nil           : Int?,
+      post_id = nil           : Int?,
+      tags = [] of String     : Array(String)?,
+      use_transaction = true  : Bool
+    )
+      if use_transaction
+        transaction do
+          raw_set_tags(site_id, post_id, tags)
+        end
+      else
+        raw_set_tags(site_id, post_id, tags)
+      end
+
+      nil
+    end
+
+    private def raw_set_tags(
+      site_id = nil       : Int?,
+      post_id = nil       : Int?,
+      tags = [] of String : Array(String)?,
+    )
+      return unless post_id && tags
+
+      # build sql args
+      args = {
+        "site_id": (site_id || @models.site.get_default).to_s,
+        "post_id": post_id.to_s,
+      }
+
+      # delete existing post tags
+      query(:set_tags_delete, args, nil)
+
+      if tags.size > 0
+        @models.tag.add_tags(tags)
+
+        # add new post tags
+        query(:set_tags_insert, args, {
+          "tags": tags.map { |tag|
+            "'" + @db.quote(tag) + "'"
+          }.join(','),
+        })
+      end
+    end
+  end
+end
diff --git a/src/guff/models/site.cr b/src/guff/models/site.cr
new file mode 100644
index 0000000..9c8899a
--- /dev/null
+++ b/src/guff/models/site.cr
@@ -0,0 +1,27 @@
+module Guff
+  class SiteModel < Model
+    SQL = TemplateCache.new({
+      get_default: "
+        SELECT site_id
+          FROM sites
+         WHERE is_default
+         LIMIT 1
+      ",
+    })
+
+    def initialize(models : Models)
+      super(models, SQL)
+    end
+
+    def get_default : Int
+      r = one(:get_default, nil, {} of String => String)
+      raise "no default site" unless r
+      r.to_i
+    end
+
+    def to_site(host : String?) : Int
+      # TODO
+      get_default
+    end
+  end
+end
diff --git a/src/guff/models/tag.cr b/src/guff/models/tag.cr
new file mode 100644
index 0000000..997cdfd
--- /dev/null
+++ b/src/guff/models/tag.cr
@@ -0,0 +1,62 @@
+module Guff
+  class TagModel < Model
+    SQL = TemplateCache.new({
+      add_tags: "
+        INSERT INTO tags(name) VALUES %{tags}
+      ",
+
+      get_tags: "
+        SELECT tag_id,
+               name
+
+          FROM tags
+
+         WHERE name IN (%{tags})
+      ",
+    } of Symbol => String)
+
+    def initialize(models : Models)
+      super(models, SQL)
+    end
+
+    def add_tags(tags : Array(String))
+      missing_tags = get_missing_tags(tags)
+
+      if missing_tags.size > 0
+        query(:add_tags, nil, {
+          "tags": missing_tags.map { |tag|
+            "('" + @db.quote(tag) + "')"
+          }.join(','),
+        })
+      end
+    end
+
+    private def get_missing_tags(
+      tags : Array(String)
+    ) : Array(String)
+      # get ids of existing tags
+      ids = get_ids(tags)
+
+      # return missing tags
+      tags.reject { |tag| ids[tag]? }
+    end
+
+    private def get_ids(
+      tags = [] of String : Array(String)
+    ) : Hash(String, Int32)
+      r = {} of String => Int32
+
+      all(:get_tags, nil, {
+        "tags": tags.map { |tag|
+          "'" + @db.quote(tag) + "'"
+        }.join(','),
+      }) do |row|
+        # add to results
+        r[row["name"] as String] = row["tag_id"].to_s.to_i
+      end
+
+      # return result
+      r
+    end
+  end
+end