refactor models/post

2 files changed, 333 insertions, 334 deletions

diff --git a/src/guff.cr b/src/guff.cr
index e5eca75..9696e5d 100644
--- a/src/guff.cr
+++ b/src/guff.cr
@@ -47,340 +47,6 @@ end
 
 module Guff
   module Models
-    class PostModel < Model
-      SQL = {
-        add: "
-          INSERT INTO posts(site_id, created_by, state_id) VALUES
-            (?, ?, (SELECT state_id FROM states WHERE state = 'draft'))
-        ",
-
-        fts_add: "
-          INSERT INTO posts_fts(rowid, name, slug, body)
-            SELECT post_id, name, slug, body FROM posts WHERE post_id = ?
-        ",
-
-        set: "
-          UPDATE posts
-             SET %s
-           WHERE post_id = ?
-        ",
-
-        fts_set: "
-          UPDATE posts_fts
-             SET %s
-           WHERE rowid = ?
-        ",
-
-        count_posts: "
-          SELECT COUNT(*)
-
-            FROM posts a
-
-            JOIN states b
-              ON (b.state_id = a.state_id)
-            JOIN sites c
-              ON (c.site_id = a.site_id)
-            JOIN users d
-              ON (d.user_id = a.created_by)
-            LEFT JOIN blogs x
-              ON (x.post_id = a.post_id)
-            LEFT JOIN pages y
-              ON (y.post_id = a.post_id)
-            LEFT JOIN projects z
-              ON (z.post_id = a.post_id)
-
-           WHERE %s
-        ",
-
-        get_posts: "
-          SELECT a.post_id,
-                 a.site_id,
-                 c.name AS site_name,
-                 b.state,
-                 d.user_id,
-                 d.name AS user_name,
-                 d.email AS user_email,
-
-                 a.created_at,
-                 date(a.created_at) AS created_at_text,
-                 datetime(a.created_at) AS created_at_text_full,
-
-                 a.posted_at,
-                 date(a.posted_at) AS posted_at_text,
-                 datetime(a.posted_at) AS posted_at_text_full,
-
-                 a.expires_at,
-                 date(a.expires_at) AS expires_at_text,
-                 datetime(a.expires_at) AS expires_at_text_full,
-
-                 a.slug,
-                 a.slug_lock,
-                 a.name,
-
-                 (CASE WHEN x.post_id IS NOT NULL THEN
-                   'blog'
-                 WHEN y.post_id IS NOT NULL THEN
-                   'page'
-                 WHEN z.post_id IS NOT NULL THEN
-                   'project'
-                 END) as post_type,
-
-                 (CASE WHEN b.state = 'public' THEN
-                   CASE WHEN x.post_id IS NOT NULL THEN
-                     strftime('/%%Y/%%m/%%d/', a.posted_at) || a.slug || '.html'
-                   WHEN y.post_id IS NOT NULL THEN
-                     '/' || a.slug || '.html'
-                   WHEN z.post_id IS NOT NULL THEN
-                     '/' || a.slug || '/'
-                   END
-                 ELSE
-                   NULL
-                 END) AS post_url
-
-            FROM posts a
-            JOIN states b
-              ON (b.state_id = a.state_id)
-            JOIN sites c
-              ON (c.site_id = a.site_id)
-            JOIN users d
-              ON (d.user_id = a.created_by)
-
-            LEFT JOIN blogs x
-              ON (x.post_id = a.post_id)
-            LEFT JOIN pages y
-              ON (y.post_id = a.post_id)
-            LEFT JOIN projects z
-              ON (z.post_id = a.post_id)
-
-           WHERE c.is_active
-             AND %s
-
-           ORDER BY COALESCE(a.posted_at, a.created_at) DESC
-
-           LIMIT ? OFFSET ?
-        ",
-      }
-
-      def add(
-        site_id : Int64,
-        user_id : Int64,
-      ) : Int64
-        db = @context.dbs.rw
-        post_id = -1_i64
-
-        db.transaction do
-          # add entry
-          db.query(SQL[:add], [site_id.to_s, user_id.to_s])
-          post_id = db.last_insert_row_id.to_i64
-
-          # populate fts index
-          db.query(SQL[:fts_add], [post_id.to_s])
-        end
-
-        # return post_id
-        post_id
-      end
-
-      def set(
-        post_id         : Int64,
-
-        site_id         : Int64?  = nil,
-        state           : String? = nil,
-
-        have_posted_at  : Bool    = false,
-        posted_at       : Time?   = nil,
-
-        have_expires_at : Bool    = false,
-        expires_at      : Time?   = nil,
-
-        slug            : String? = nil,
-        slug_lock       : Bool?   = nil,
-
-        name            : String? = nil,
-        body            : String? = nil,
-      )
-        sets = [] of String
-        args = [] of String
-        fts_sets = [] of String
-        fts_args = [] of String
-
-        if site_id
-          sets << "site_id = ?"
-          args << site_id.to_s
-        end
-
-        if state
-          sets << "state_id = (SELECT state_id FROM states WHERE state = ?)"
-          args << state
-
-          if state == "public" && !have_posted_at
-            # set posted_at by default
-            sets << "posted_at = COALESCE(posted_at, CURRENT_TIMESTAMP)"
-          end
-        end
-
-        if have_posted_at
-          if posted_at
-            sets << "posted_at = ?"
-            args << ISO8601.format(posted_at)
-          else
-            sets << "posted_at = NULL"
-          end
-        end
-
-        if have_expires_at
-          if expires_at
-            sets << "expires_at = ?"
-            args << ISO8601.format(expires_at)
-          else
-            sets << "expires_at = NULL"
-          end
-        end
-
-        if slug
-          sets << "slug = ?"
-          args << slug
-
-          fts_sets << "slug = ?"
-          fts_args << slug
-        end
-
-        unless slug_lock.nil?
-          sets << "slug_lock = ?"
-          args << (slug_lock ? "1" : "0")
-        end
-
-        if name
-          sets << "name = ?"
-          args << name
-
-          fts_sets << "name = ?"
-          fts_args << name
-        end
-
-        if body
-          sets << "body = ?"
-          args << body
-
-          fts_sets << "body = ?"
-          fts_args << body.gsub(/<.+?>/, " ")
-        end
-
-        if sets.size > 0
-          # update posts
-          args << post_id.to_s
-          @context.dbs.rw.query(SQL[:set] % sets.join(","), args)
-
-          if fts_sets.size > 0
-            # update posts fts
-            fts_args << post_id.to_s
-            @context.dbs.rw.query(SQL[:fts_set] % fts_sets.join(","), fts_args)
-          end
-        end
-      end
-
-      LIMIT = 50
-
-      def get_posts(
-        site_id : Int64?   = nil,
-        user_id : Int64?   = nil,
-        type    : String?  = nil,
-        state   : String?  = nil,
-        q       : String?  = nil,
-        page    : Int32    = 1,
-      )
-        filters = %w{1}
-        args = [] of String
-
-        # check page
-        raise "invalid page: #{page}" unless page > 0
-
-        if site_id
-          # add site filter
-          filters << "a.site_id = ?"
-          args << site_id.to_s
-        end
-
-        if user_id
-          # add user filter
-          filters << "a.created_by = ?"
-          args << user_id.to_s
-        end
-
-        if type
-          # add type filter
-          filters << case type
-          when "blog"
-            "x.post_id IS NOT NULL"
-          when "page"
-            "y.post_id IS NOT NULL"
-          when "project"
-            "z.post_id IS NOT NULL"
-          when "all"
-            # allow "all"
-            "1"
-          else
-            raise "unknown post type: #{type}"
-          end
-        end
-
-        if state && state != "default"
-          # add state filter
-          filters << "b.state = ?"
-          args << state
-        else
-          # default state filter
-          filters << "b.state IN ('draft', 'public')"
-        end
-
-        if q && q.match(/\S+/)
-          # add search filter
-          filters << "a.post_id IN (
-            SELECT rowid
-              FROM posts_fts
-             WHERE posts_fts MATCH ?
-          )"
-
-          args << q
-        end
-
-        # build where clause
-        filter_sql = filters.join(" AND ")
-
-        # count matching rows
-        num_posts = (@context.dbs.ro.one(SQL[:count_posts] % [
-          filter_sql
-        ], args) || "0").to_i64
-
-        # build result
-        rows = [] of Hash(String, String)
-
-        if num_posts > 0
-          # get matching rows
-          @context.dbs.ro.all(SQL[:get_posts] % [
-            filter_sql
-          ], args.concat([
-            LIMIT.to_s,
-            ((page - 1) * LIMIT).to_s,
-          ])) do |row|
-            # append row to result
-            rows << row.reduce({} of String => String) do |r, kv|
-              r[kv[0]] = kv[1].to_s
-              r
-            end
-          end
-        end
-
-        # return results
-        PagedResultSet.new(
-          page:     page,
-          num_rows: num_posts,
-          limit:    LIMIT,
-          rows:     rows,
-        )
-      end
-    end
-
     class PageModel < Model
       SQL = {
         get_id: "
diff --git a/src/guff/models/post.cr b/src/guff/models/post.cr
new file mode 100644
index 0000000..5c94d04
--- /dev/null
+++ b/src/guff/models/post.cr
@@ -0,0 +1,333 @@
+class Guff::Models::PostModel < Guff::Models::Model
+  SQL = {
+    add: "
+      INSERT INTO posts(site_id, created_by, state_id) VALUES
+        (?, ?, (SELECT state_id FROM states WHERE state = 'draft'))
+    ",
+
+    fts_add: "
+      INSERT INTO posts_fts(rowid, name, slug, body)
+        SELECT post_id, name, slug, body FROM posts WHERE post_id = ?
+    ",
+
+    set: "
+      UPDATE posts
+         SET %s
+       WHERE post_id = ?
+    ",
+
+    fts_set: "
+      UPDATE posts_fts
+         SET %s
+       WHERE rowid = ?
+    ",
+
+    count_posts: "
+      SELECT COUNT(*)
+
+        FROM posts a
+
+        JOIN states b
+          ON (b.state_id = a.state_id)
+        JOIN sites c
+          ON (c.site_id = a.site_id)
+        JOIN users d
+          ON (d.user_id = a.created_by)
+        LEFT JOIN blogs x
+          ON (x.post_id = a.post_id)
+        LEFT JOIN pages y
+          ON (y.post_id = a.post_id)
+        LEFT JOIN projects z
+          ON (z.post_id = a.post_id)
+
+       WHERE %s
+    ",
+
+    get_posts: "
+      SELECT a.post_id,
+             a.site_id,
+             c.name AS site_name,
+             b.state,
+             d.user_id,
+             d.name AS user_name,
+             d.email AS user_email,
+
+             a.created_at,
+             date(a.created_at) AS created_at_text,
+             datetime(a.created_at) AS created_at_text_full,
+
+             a.posted_at,
+             date(a.posted_at) AS posted_at_text,
+             datetime(a.posted_at) AS posted_at_text_full,
+
+             a.expires_at,
+             date(a.expires_at) AS expires_at_text,
+             datetime(a.expires_at) AS expires_at_text_full,
+
+             a.slug,
+             a.slug_lock,
+             a.name,
+
+             (CASE WHEN x.post_id IS NOT NULL THEN
+               'blog'
+             WHEN y.post_id IS NOT NULL THEN
+               'page'
+             WHEN z.post_id IS NOT NULL THEN
+               'project'
+             END) as post_type,
+
+             (CASE WHEN b.state = 'public' THEN
+               CASE WHEN x.post_id IS NOT NULL THEN
+                 strftime('/%%Y/%%m/%%d/', a.posted_at) || a.slug || '.html'
+               WHEN y.post_id IS NOT NULL THEN
+                 '/' || a.slug || '.html'
+               WHEN z.post_id IS NOT NULL THEN
+                 '/' || a.slug || '/'
+               END
+             ELSE
+               NULL
+             END) AS post_url
+
+        FROM posts a
+        JOIN states b
+          ON (b.state_id = a.state_id)
+        JOIN sites c
+          ON (c.site_id = a.site_id)
+        JOIN users d
+          ON (d.user_id = a.created_by)
+
+        LEFT JOIN blogs x
+          ON (x.post_id = a.post_id)
+        LEFT JOIN pages y
+          ON (y.post_id = a.post_id)
+        LEFT JOIN projects z
+          ON (z.post_id = a.post_id)
+
+       WHERE c.is_active
+         AND %s
+
+       ORDER BY COALESCE(a.posted_at, a.created_at) DESC
+
+       LIMIT ? OFFSET ?
+    ",
+  }
+
+  def add(
+    site_id : Int64,
+    user_id : Int64,
+  ) : Int64
+    db = @context.dbs.rw
+    post_id = -1_i64
+
+    db.transaction do
+      # add entry
+      db.query(SQL[:add], [site_id.to_s, user_id.to_s])
+      post_id = db.last_insert_row_id.to_i64
+
+      # populate fts index
+      db.query(SQL[:fts_add], [post_id.to_s])
+    end
+
+    # return post_id
+    post_id
+  end
+
+  def set(
+    post_id         : Int64,
+
+    site_id         : Int64?  = nil,
+    state           : String? = nil,
+
+    have_posted_at  : Bool    = false,
+    posted_at       : Time?   = nil,
+
+    have_expires_at : Bool    = false,
+    expires_at      : Time?   = nil,
+
+    slug            : String? = nil,
+    slug_lock       : Bool?   = nil,
+
+    name            : String? = nil,
+    body            : String? = nil,
+  )
+    sets = [] of String
+    args = [] of String
+    fts_sets = [] of String
+    fts_args = [] of String
+
+    if site_id
+      sets << "site_id = ?"
+      args << site_id.to_s
+    end
+
+    if state
+      sets << "state_id = (SELECT state_id FROM states WHERE state = ?)"
+      args << state
+
+      if state == "public" && !have_posted_at
+        # set posted_at by default
+        sets << "posted_at = COALESCE(posted_at, CURRENT_TIMESTAMP)"
+      end
+    end
+
+    if have_posted_at
+      if posted_at
+        sets << "posted_at = ?"
+        args << ISO8601.format(posted_at)
+      else
+        sets << "posted_at = NULL"
+      end
+    end
+
+    if have_expires_at
+      if expires_at
+        sets << "expires_at = ?"
+        args << ISO8601.format(expires_at)
+      else
+        sets << "expires_at = NULL"
+      end
+    end
+
+    if slug
+      sets << "slug = ?"
+      args << slug
+
+      fts_sets << "slug = ?"
+      fts_args << slug
+    end
+
+    unless slug_lock.nil?
+      sets << "slug_lock = ?"
+      args << (slug_lock ? "1" : "0")
+    end
+
+    if name
+      sets << "name = ?"
+      args << name
+
+      fts_sets << "name = ?"
+      fts_args << name
+    end
+
+    if body
+      sets << "body = ?"
+      args << body
+
+      fts_sets << "body = ?"
+      fts_args << body.gsub(/<.+?>/, " ")
+    end
+
+    if sets.size > 0
+      # update posts
+      args << post_id.to_s
+      @context.dbs.rw.query(SQL[:set] % sets.join(","), args)
+
+      if fts_sets.size > 0
+        # update posts fts
+        fts_args << post_id.to_s
+        @context.dbs.rw.query(SQL[:fts_set] % fts_sets.join(","), fts_args)
+      end
+    end
+  end
+
+  LIMIT = 50
+
+  def get_posts(
+    site_id : Int64?   = nil,
+    user_id : Int64?   = nil,
+    type    : String?  = nil,
+    state   : String?  = nil,
+    q       : String?  = nil,
+    page    : Int32    = 1,
+  )
+    filters = %w{1}
+    args = [] of String
+
+    # check page
+    raise "invalid page: #{page}" unless page > 0
+
+    if site_id
+      # add site filter
+      filters << "a.site_id = ?"
+      args << site_id.to_s
+    end
+
+    if user_id
+      # add user filter
+      filters << "a.created_by = ?"
+      args << user_id.to_s
+    end
+
+    if type
+      # add type filter
+      filters << case type
+      when "blog"
+        "x.post_id IS NOT NULL"
+      when "page"
+        "y.post_id IS NOT NULL"
+      when "project"
+        "z.post_id IS NOT NULL"
+      when "all"
+        # allow "all"
+        "1"
+      else
+        raise "unknown post type: #{type}"
+      end
+    end
+
+    if state && state != "default"
+      # add state filter
+      filters << "b.state = ?"
+      args << state
+    else
+      # default state filter
+      filters << "b.state IN ('draft', 'public')"
+    end
+
+    if q && q.match(/\S+/)
+      # add search filter
+      filters << "a.post_id IN (
+        SELECT rowid
+          FROM posts_fts
+         WHERE posts_fts MATCH ?
+      )"
+
+      args << q
+    end
+
+    # build where clause
+    filter_sql = filters.join(" AND ")
+
+    # count matching rows
+    num_posts = (@context.dbs.ro.one(SQL[:count_posts] % [
+      filter_sql
+    ], args) || "0").to_i64
+
+    # build result
+    rows = [] of Hash(String, String)
+
+    if num_posts > 0
+      # get matching rows
+      @context.dbs.ro.all(SQL[:get_posts] % [
+        filter_sql
+      ], args.concat([
+        LIMIT.to_s,
+        ((page - 1) * LIMIT).to_s,
+      ])) do |row|
+        # append row to result
+        rows << row.reduce({} of String => String) do |r, kv|
+          r[kv[0]] = kv[1].to_s
+          r
+        end
+      end
+    end
+
+    # return results
+    PagedResultSet.new(
+      page:     page,
+      num_rows: num_posts,
+      limit:    LIMIT,
+      rows:     rows,
+    )
+  end
+end