refactor models/post

1 files changed, 0 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: "