diff options
| -rw-r--r-- | src/guff.cr | 948 | ||||
| -rw-r--r-- | src/guff/model-set.cr | 25 | ||||
| -rw-r--r-- | src/guff/models/blog.cr | 179 | ||||
| -rw-r--r-- | src/guff/models/csrf.cr | 48 | ||||
| -rw-r--r-- | src/guff/models/page.cr | 131 | ||||
| -rw-r--r-- | src/guff/models/project.cr | 128 | ||||
| -rw-r--r-- | src/guff/models/role.cr | 25 | ||||
| -rw-r--r-- | src/guff/models/session.cr | 57 | ||||
| -rw-r--r-- | src/guff/models/site.cr | 72 | ||||
| -rw-r--r-- | src/guff/models/state.cr | 27 | ||||
| -rw-r--r-- | src/guff/models/user.cr | 173 | ||||
| -rw-r--r-- | src/guff/session.cr | 72 |
12 files changed, 937 insertions, 948 deletions
diff --git a/src/guff.cr b/src/guff.cr index 9696e5d..f0e2671 100644 --- a/src/guff.cr +++ b/src/guff.cr @@ -11,14 +11,6 @@ end require "./guff/**" -private macro define_lazy_getters(hash) - {% for name, klass in hash %} - def {{ name.id }} : {{ klass.id }} - (@cached_{{ name.id }} ||= {{ klass.id }}.new(@context)) - end - {% end %} -end - private macro include_api_modules(modules) {% for mod in modules.resolve %} include {{ mod.id }} @@ -46,946 +38,6 @@ private macro api_method_dispatch(modules) end module Guff - module Models - class PageModel < Model - SQL = { - get_id: " - SELECT b.post_id - - FROM sites a - JOIN posts b - ON (b.site_id = a.site_id) - JOIN pages c - ON (c.post_id = b.post_id) - JOIN states d - ON (d.state_id = b.state_id) - - WHERE a.site_id = ? - AND b.slug = ? - AND d.state = 'public' - - ORDER BY b.created_at DESC - LIMIT 1 - ", - - add: " - INSERT INTO pages(post_id, layout_id) - VALUES (?, (SELECT layout_id FROM layouts WHERE layout = 'default')) - ", - - set: " - UPDATE pages - SET layout_id = (SELECT layout_id FROM layouts WHERE layout = ?) - WHERE post_id = ? - ", - - get: " - SELECT a.post_id, - a.site_id, - c.state, - a.posted_at, - a.expires_at, - a.slug, - a.slug_lock, - a.name, - a.body, - d.layout - - FROM posts a - JOIN pages b - ON (b.post_id = a.post_id) - JOIN states c - ON (c.state_id = a.state_id) - JOIN layouts d - ON (d.layout_id = b.layout_id) - - WHERE a.post_id = ? - ", - } - - def get_id( - site_id : Int64, - slug : String - ) : Int64? - r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug]) - r ? r.to_i64 : nil - end - - def add( - site_id : Int64, - user_id : Int64, - ) : Int64 - db = @context.dbs.rw - post_id = -1_i64 - - db.transaction do - post_id = @context.models.post.add(site_id, user_id) - db.query(SQL[:add], [post_id.to_s]) - end - - 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, - - layout : String? = nil, - ) - db = @context.dbs.rw - - db.transaction do - @context.models.post.set( - post_id: post_id, - - site_id: site_id, - state: state, - - have_posted_at: have_posted_at, - posted_at: posted_at, - - have_expires_at: have_expires_at, - expires_at: expires_at, - - slug: slug, - slug_lock: slug_lock, - - name: name, - body: body, - ) - - if layout - db.query(SQL[:set], [layout, post_id.to_s]) - end - end - end - - def get(post_id : Int64) - @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil! - end - end - - class ProjectModel < Model - SQL = { - get_id: " - SELECT b.post_id - - FROM sites a - JOIN posts b - ON (b.site_id = a.site_id) - JOIN projects c - ON (c.post_id = b.post_id) - JOIN states d - ON (d.state_id = b.state_id) - - WHERE a.site_id = ? - AND b.slug = ? - AND d.state = 'public' - - ORDER BY b.created_at DESC - LIMIT 1 - ", - - add: " - INSERT INTO projects(post_id) VALUES (?) - ", - - set: " - UPDATE projects - SET repo_url = ? - WHERE post_id = ? - ", - - get: " - SELECT a.post_id, - a.site_id, - c.state, - a.posted_at, - a.expires_at, - a.slug, - a.slug_lock, - a.name, - a.body, - b.repo_url - - FROM posts a - JOIN projects b - ON (b.post_id = a.post_id) - JOIN states c - ON (c.state_id = a.state_id) - - WHERE a.post_id = ? - ", - } - - def get_id( - site_id : Int64, - slug : String - ) : Int64? - r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug]) - r ? r.to_i64 : nil - end - - def add( - site_id : Int64, - user_id : Int64, - ) : Int64 - db = @context.dbs.rw - post_id = -1_i64 - - db.transaction do - post_id = @context.models.post.add(site_id, user_id) - db.query(SQL[:add], [post_id.to_s]) - end - - 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, - - repo_url : String? = nil, - ) - db = @context.dbs.rw - - db.transaction do - @context.models.post.set( - post_id: post_id, - - site_id: site_id, - state: state, - - have_posted_at: have_posted_at, - posted_at: posted_at, - - have_expires_at: have_expires_at, - expires_at: expires_at, - - slug: slug, - slug_lock: slug_lock, - - name: name, - body: body, - ) - - if repo_url - db.query(SQL[:set], [repo_url, post_id.to_s]) - end - end - end - - def get(post_id : Int64) - @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil! - end - end - - class BlogModel < Model - SQL = { - get_ids: " - SELECT b.post_id - - FROM sites a - JOIN posts b - ON (b.site_id = a.site_id) - JOIN blogs c - ON (c.post_id = b.post_id) - JOIN states d - ON (d.state_id = b.state_id) - - WHERE a.site_id = ? - AND %s - AND d.state = 'public' - -- TODO: handle posted_at and expired_at - - ORDER BY COALESCE(b.posted_at, b.created_at) DESC - - LIMIT ? OFFSET ? - ", - - add: " - INSERT INTO blogs(post_id) VALUES (?) - ", - - get: " - SELECT a.post_id, - a.site_id, - c.state, - a.posted_at, - a.expires_at, - a.slug, - a.slug_lock, - a.name, - a.body - - FROM posts a - JOIN blogs b - ON (b.post_id = a.post_id) - JOIN states c - ON (c.state_id = a.state_id) - - WHERE a.post_id = ? - ", - } - - def get_id( - site_id : Int64, - year : Int32, - month : Int32, - day : Int32, - slug : String - ) : Int64? - ids = get_ids(site_id, year, month, day, slug) - (ids.size > 0) ? ids.first : nil - end - - # TODO: make this configurable - LIMIT = 50 - - def get_ids( - site_id : Int64, - year : Int32? = nil, - month : Int32? = nil, - day : Int32? = nil, - slug : String? = nil, - page : Int32? = nil - ) : Array(Int64) - sql = ["1"] - args = [site_id.to_s] - - # STDERR.puts "DEBUG: site_id = #{site_id}, year = #{year}, month = #{month}, day = #{day}, slug = \"#{slug}\"" - - if year - # add year filter - sql << "strftime('%Y', b.posted_at) + 0 = ? + 0" - args << year.to_s - - if month - # add month filter - sql << "strftime('%m', b.posted_at) + 0 = ? + 0" - args << month.to_s - - if day - # add day filter - sql << "strftime('%d', b.posted_at) + 0 = ? + 0" - args << day.to_s - end - end - end - - if slug - # add slug filter - sql << "b.slug = ?" - args << slug - end - - page ||= 1 - raise "invalid page: #{page}" if page < 1 - - args << LIMIT.to_s - args << ((page - 1) * LIMIT).to_s - - # STDERR.puts "DEBUG: args = #{args.to_json}" - - # exec query, build result - r = [] of Int64 - @context.dbs.ro.all(SQL[:get_ids] % sql.join(" AND "), args) do |row| - p row - r << row["post_id"] as Int64 - end - - # return results - r - end - - def add( - site_id : Int64, - user_id : Int64, - ) : Int64 - db = @context.dbs.rw - post_id = -1_i64 - - db.transaction do - post_id = @context.models.post.add(site_id, user_id) - db.query(SQL[:add], [post_id.to_s]) - end - - 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, - - repo_url : String? = nil, - ) - @context.models.post.set( - post_id: post_id, - - site_id: site_id, - state: state, - - have_posted_at: have_posted_at, - posted_at: posted_at, - - have_expires_at: have_expires_at, - expires_at: expires_at, - - slug: slug, - slug_lock: slug_lock, - - name: name, - body: body, - ) - end - - def get(post_id : Int64) - @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil! - end - end - - class UserModel < Model - SQL = { - login: " - SELECT user_id, - password - - FROM users - - WHERE is_active - AND email = ? - ", - - has_role: " - SELECT 1 - - FROM users - - WHERE is_active - AND user_id = ? - AND role_id IN (SELECT role_id FROM roles WHERE role IN (%s)) - ", - - add: " - INSERT INTO users(role_id, name, email, password, is_active) VALUES - ((SELECT role_id FROM roles where role = ?), ?, ?, ?, ?) - ", - - set: " - UPDATE users - SET %s - WHERE user_id = ? - ", - - get_users: " - SELECT a.user_id, - a.name, - a.email, - a.is_active, - b.role, - b.name AS role_name - - FROM users a - JOIN roles b - ON (b.role_id = a.role_id) - - ORDER BY LOWER(a.name) - ", - - get: " - SELECT a.user_id, - a.name, - a.email, - a.is_active, - b.role, - b.name AS role_name - - FROM users a - JOIN roles b - ON (b.role_id = a.role_id) - - WHERE a.user_id = ? - ", - } - - def login( - email : String, - password : String - ) : Int64? - r = nil - - if row = @context.dbs.ro.row(SQL[:login], [email]) - if Password.test(row["password"] as String, password) - # given email and password matches active user - r = row["user_id"] as Int64 - end - end - - # return result - r - end - - def has_role?(user_id : Int64, roles : Array(String)) - raise "empty role list" unless roles.size > 0 - - !!@context.dbs.ro.one(SQL[:has_role] % [ - (["?"] * roles.size).join(",") - ], [user_id.to_s].concat(roles)) - end - - def add( - name : String, - email : String, - password : String, - role : String, - active : Bool, - ) : Int64 - @context.dbs.rw.query(SQL[:add], [ - role, - name, - email, - Password.create(password), - active ? "1" : "0", - ]) - - @context.dbs.rw.last_insert_row_id.to_i64 - end - - def set( - user_id : Int64, - name : String? = nil, - email : String? = nil, - password : String? = nil, - role : String? = nil, - active : Bool? = nil, - ) - sets = [] of String - args = [] of String - - if name - sets << "name = ?" - args << name - end - - if email - sets << "email = ?" - args << email - end - - if role - sets << "role_id = (SELECT role_id FROM roles WHERE role = ?)" - args << role - end - - if password - sets << "password = ?" - args << Password.create(password) - end - - if active != nil - sets << "is_active = ?" - args << (active ? "1" : "0") - end - - if sets.size > 0 - args << user_id.to_s - @context.dbs.rw.query(SQL[:set] % sets.join(", "), args) - end - end - - def get_users - rows = [] of Hash(String, String) - - @context.dbs.ro.all(SQL[:get_users]) 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 - - rows - end - - def get(user_id : Int64) - row = @context.dbs.ro.row(SQL[:get], [user_id.to_s]) - raise "unknown user: #{user_id}" unless row - - row.reduce({} of String => String) do |r, kv| - r[kv[0]] = kv[1].to_s - r - end - end - end - - # TODO: handle session expiration - class SessionModel < Model - SQL = { - load: " - SELECT data - - FROM sessions - - WHERE id = ? - -- TODO: - -- AND strftime('%s', created_at, '1 week') > strftime('%s') - -- AND strftime('%s', updated_at, '2 hours') > strftime('%s') - ", - - save: " - UPDATE sessions - - SET updated_at = CURRENT_TIMESTAMP, - data = ? - - WHERE id = ? - ", - - delete: " - DELETE FROM sessions WHERE id = ? - ", - - create: " - INSERT INTO sessions(id, data) VALUES (?, ?) - ", - } - - def load(id : String) : String? - @context.dbs.ro.one(SQL[:load], [id]) - end - - def save(id : String, data : String) - @context.dbs.rw.query(SQL[:save], [data, id]) - nil - end - - def delete(id : String?) - @context.dbs.rw.query(SQL[:delete], [id]) if id - nil - end - - def create(data : String) : String - # generate id - r = SecureRandom.hex(32) - - # save session - @context.dbs.rw.query(SQL[:create], [r, data]) - - # return session id - r - end - end - - class CSRFModel < Model - getter :minutes - - def initialize(context : Context) - super(context) - @cache = {} of String => Int64 - - # expire form after 5 minutes - # TODO: make this configurable - @minutes = 5 - end - - def create_token - remove_expired_tokens - - # generate and cache new token - r = SecureRandom.hex(16) - @cache[r] = Time.now.epoch + 60 * @minutes - - # return token - r - end - - def use_token(id : String) - remove_expired_tokens - - if @cache.has_key?(id) - # remove token, return success - @cache.delete(id) - true - else - # return failure - false - end - end - - private def remove_expired_tokens - now = Time.now.epoch - - # FIXME: limit the size of the cache - # to prevent insane memory use - - # remove expired entries - @cache.delete_if do |key, val| - val < now - end - end - end - - class StateModel < Model - SQL = { - get_states: " - SELECT state_id, - state, - name, - icon - - FROM states - - ORDER BY sort - ", - } - - def get_states - rows = [] of Hash(String, String) - - @context.dbs.ro.all(SQL[:get_states]) do |row| - rows << row.reduce({} of String => String) do |r, kv| - r[kv[0]] = kv[1].to_s - r - end - end - - rows - end - end - - class SiteModel < Model - SQL = { - get_id: " - SELECT site_id - - FROM ( - SELECT a.site_id - - FROM site_domains a - JOIN sites b - ON (b.site_id = a.site_id) - - WHERE b.is_active - AND a.domain = $1 - - UNION ALL - - SELECT site_id - - FROM sites - - WHERE is_active - AND is_default - ) a - - LIMIT 1 - ", - - get_default_id: " - SELECT site_id - - FROM sites - - WHERE is_active - AND is_default - ", - - get_sites: " - SELECT site_id, - name, - is_active, - is_default - - FROM sites - - ORDER BY LOWER(name) - - ", - } - - def get_id(host : String?) : Int64? - r = @context.dbs.ro.one(SQL[:get_id], [host || ""]) - r ? r.to_i64 : nil - end - - def get_default_id : Int64 - @context.dbs.ro.one(SQL[:get_default_id]).not_nil!.to_i64 - end - - def get_sites - rows = [] of Hash(String, String) - - @context.dbs.ro.all(SQL[:get_sites]) do |row| - rows << row.reduce({} of String => String) do |r, kv| - r[kv[0]] = kv[1].to_s - r - end - end - - rows - end - end - - class RoleModel < Model - SQL = { - get_roles: " - SELECT role, - name - - FROM roles - - ORDER BY sort - ", - } - - def get_roles : Array(Hash(String, String)) - r = [] of Hash(String, String) - - @context.dbs.ro.all(SQL[:get_roles]) do |row| - r << { - "role" => row["role"] as String, - "name" => row["name"] as String, - } - end - - r - end - end - end - - class ModelSet - def initialize(@context : Context) - end - - define_lazy_getters({ - user: Models::UserModel, - session: Models::SessionModel, - csrf: Models::CSRFModel, - post: Models::PostModel, - page: Models::PageModel, - project: Models::ProjectModel, - blog: Models::BlogModel, - site: Models::SiteModel, - role: Models::RoleModel, - state: Models::StateModel, - }) - end - - class Session < Hash(String, String) - getter :session_id - - # session cookie name - # FIXME: does this belong here? - COOKIE = "guff_session" - - def initialize(@context : Context) - super() - @session_id = nil - end - - def load(id : String) - begin - # clear existing session - clear - - # load session values - JSON.parse(@context.models.session.load(id).not_nil!).each do |key, val| - self[key.as_s] = val.as_s - end - - # save session id - @session_id = id - - # return success - true - rescue err - STDERR.puts "session load failed: #{err}" - # invalid session id, return failure - false - end - end - - def create(hash : Hash(String, String)) : String - clear - merge!(hash) - @session_id = @context.models.session.create(hash.to_json) - end - - def save - if valid? - @context.models.session.save(@session_id.not_nil!, to_json) - - # return success - true - else - # no session, return failure - false - end - end - - def clear - super - @session_id = nil - end - - def delete : String? - r = @session_id - - if valid? - @context.models.session.delete(r) - clear - end - - r - end - - def valid? - @session_id != nil - end - end - module Views abstract class View def initialize(@context : Context) diff --git a/src/guff/model-set.cr b/src/guff/model-set.cr new file mode 100644 index 0000000..e4c4616 --- /dev/null +++ b/src/guff/model-set.cr @@ -0,0 +1,25 @@ +private macro define_lazy_getters(hash) + {% for name, klass in hash %} + def {{ name.id }} : {{ klass.id }} + (@cached_{{ name.id }} ||= {{ klass.id }}.new(@context)) + end + {% end %} +end + +class Guff::ModelSet + def initialize(@context : Context) + end + + define_lazy_getters({ + user: Models::UserModel, + session: Models::SessionModel, + csrf: Models::CSRFModel, + post: Models::PostModel, + page: Models::PageModel, + project: Models::ProjectModel, + blog: Models::BlogModel, + site: Models::SiteModel, + role: Models::RoleModel, + state: Models::StateModel, + }) +end diff --git a/src/guff/models/blog.cr b/src/guff/models/blog.cr new file mode 100644 index 0000000..9c25b77 --- /dev/null +++ b/src/guff/models/blog.cr @@ -0,0 +1,179 @@ +require "./model" + +class Guff::Models::BlogModel < Guff::Models::Model + SQL = { + get_ids: " + SELECT b.post_id + + FROM sites a + JOIN posts b + ON (b.site_id = a.site_id) + JOIN blogs c + ON (c.post_id = b.post_id) + JOIN states d + ON (d.state_id = b.state_id) + + WHERE a.site_id = ? + AND %s + AND d.state = 'public' + -- TODO: handle posted_at and expired_at + + ORDER BY COALESCE(b.posted_at, b.created_at) DESC + + LIMIT ? OFFSET ? + ", + + add: " + INSERT INTO blogs(post_id) VALUES (?) + ", + + get: " + SELECT a.post_id, + a.site_id, + c.state, + a.posted_at, + a.expires_at, + a.slug, + a.slug_lock, + a.name, + a.body + + FROM posts a + JOIN blogs b + ON (b.post_id = a.post_id) + JOIN states c + ON (c.state_id = a.state_id) + + WHERE a.post_id = ? + ", + } + + def get_id( + site_id : Int64, + year : Int32, + month : Int32, + day : Int32, + slug : String + ) : Int64? + ids = get_ids(site_id, year, month, day, slug) + (ids.size > 0) ? ids.first : nil + end + + # TODO: make this configurable + LIMIT = 50 + + def get_ids( + site_id : Int64, + year : Int32? = nil, + month : Int32? = nil, + day : Int32? = nil, + slug : String? = nil, + page : Int32? = nil + ) : Array(Int64) + sql = ["1"] + args = [site_id.to_s] + + # STDERR.puts "DEBUG: site_id = #{site_id}, year = #{year}, month = #{month}, day = #{day}, slug = \"#{slug}\"" + + if year + # add year filter + sql << "strftime('%Y', b.posted_at) + 0 = ? + 0" + args << year.to_s + + if month + # add month filter + sql << "strftime('%m', b.posted_at) + 0 = ? + 0" + args << month.to_s + + if day + # add day filter + sql << "strftime('%d', b.posted_at) + 0 = ? + 0" + args << day.to_s + end + end + end + + if slug + # add slug filter + sql << "b.slug = ?" + args << slug + end + + page ||= 1 + raise "invalid page: #{page}" if page < 1 + + args << LIMIT.to_s + args << ((page - 1) * LIMIT).to_s + + # STDERR.puts "DEBUG: args = #{args.to_json}" + + # exec query, build result + r = [] of Int64 + @context.dbs.ro.all(SQL[:get_ids] % sql.join(" AND "), args) do |row| + p row + r << row["post_id"] as Int64 + end + + # return results + r + end + + def add( + site_id : Int64, + user_id : Int64, + ) : Int64 + db = @context.dbs.rw + post_id = -1_i64 + + db.transaction do + post_id = @context.models.post.add(site_id, user_id) + db.query(SQL[:add], [post_id.to_s]) + end + + 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, + + repo_url : String? = nil, + ) + @context.models.post.set( + post_id: post_id, + + site_id: site_id, + state: state, + + have_posted_at: have_posted_at, + posted_at: posted_at, + + have_expires_at: have_expires_at, + expires_at: expires_at, + + slug: slug, + slug_lock: slug_lock, + + name: name, + body: body, + ) + end + + def get(post_id : Int64) + @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil! + end +end diff --git a/src/guff/models/csrf.cr b/src/guff/models/csrf.cr new file mode 100644 index 0000000..e719e02 --- /dev/null +++ b/src/guff/models/csrf.cr @@ -0,0 +1,48 @@ +class Guff::Models::CSRFModel < Guff::Models::Model + getter :minutes + + def initialize(context : Context) + super(context) + @cache = {} of String => Int64 + + # expire form after 5 minutes + # TODO: make this configurable + @minutes = 5 + end + + def create_token + remove_expired_tokens + + # generate and cache new token + r = SecureRandom.hex(16) + @cache[r] = Time.now.epoch + 60 * @minutes + + # return token + r + end + + def use_token(id : String) + remove_expired_tokens + + if @cache.has_key?(id) + # remove token, return success + @cache.delete(id) + true + else + # return failure + false + end + end + + private def remove_expired_tokens + now = Time.now.epoch + + # FIXME: limit the size of the cache + # to prevent insane memory use + + # remove expired entries + @cache.delete_if do |key, val| + val < now + end + end +end diff --git a/src/guff/models/page.cr b/src/guff/models/page.cr new file mode 100644 index 0000000..b30e94a --- /dev/null +++ b/src/guff/models/page.cr @@ -0,0 +1,131 @@ +class Guff::Models::PageModel < Guff::Models::Model + SQL = { + get_id: " + SELECT b.post_id + + FROM sites a + JOIN posts b + ON (b.site_id = a.site_id) + JOIN pages c + ON (c.post_id = b.post_id) + JOIN states d + ON (d.state_id = b.state_id) + + WHERE a.site_id = ? + AND b.slug = ? + AND d.state = 'public' + + ORDER BY b.created_at DESC + LIMIT 1 + ", + + add: " + INSERT INTO pages(post_id, layout_id) + VALUES (?, (SELECT layout_id FROM layouts WHERE layout = 'default')) + ", + + set: " + UPDATE pages + SET layout_id = (SELECT layout_id FROM layouts WHERE layout = ?) + WHERE post_id = ? + ", + + get: " + SELECT a.post_id, + a.site_id, + c.state, + a.posted_at, + a.expires_at, + a.slug, + a.slug_lock, + a.name, + a.body, + d.layout + + FROM posts a + JOIN pages b + ON (b.post_id = a.post_id) + JOIN states c + ON (c.state_id = a.state_id) + JOIN layouts d + ON (d.layout_id = b.layout_id) + + WHERE a.post_id = ? + ", + } + + def get_id( + site_id : Int64, + slug : String + ) : Int64? + r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug]) + r ? r.to_i64 : nil + end + + def add( + site_id : Int64, + user_id : Int64, + ) : Int64 + db = @context.dbs.rw + post_id = -1_i64 + + db.transaction do + post_id = @context.models.post.add(site_id, user_id) + db.query(SQL[:add], [post_id.to_s]) + end + + 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, + + layout : String? = nil, + ) + db = @context.dbs.rw + + db.transaction do + @context.models.post.set( + post_id: post_id, + + site_id: site_id, + state: state, + + have_posted_at: have_posted_at, + posted_at: posted_at, + + have_expires_at: have_expires_at, + expires_at: expires_at, + + slug: slug, + slug_lock: slug_lock, + + name: name, + body: body, + ) + + if layout + db.query(SQL[:set], [layout, post_id.to_s]) + end + end + end + + def get(post_id : Int64) + @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil! + end +end diff --git a/src/guff/models/project.cr b/src/guff/models/project.cr new file mode 100644 index 0000000..2ead9db --- /dev/null +++ b/src/guff/models/project.cr @@ -0,0 +1,128 @@ +class Guff::Models::ProjectModel < Guff::Models::Model + SQL = { + get_id: " + SELECT b.post_id + + FROM sites a + JOIN posts b + ON (b.site_id = a.site_id) + JOIN projects c + ON (c.post_id = b.post_id) + JOIN states d + ON (d.state_id = b.state_id) + + WHERE a.site_id = ? + AND b.slug = ? + AND d.state = 'public' + + ORDER BY b.created_at DESC + LIMIT 1 + ", + + add: " + INSERT INTO projects(post_id) VALUES (?) + ", + + set: " + UPDATE projects + SET repo_url = ? + WHERE post_id = ? + ", + + get: " + SELECT a.post_id, + a.site_id, + c.state, + a.posted_at, + a.expires_at, + a.slug, + a.slug_lock, + a.name, + a.body, + b.repo_url + + FROM posts a + JOIN projects b + ON (b.post_id = a.post_id) + JOIN states c + ON (c.state_id = a.state_id) + + WHERE a.post_id = ? + ", + } + + def get_id( + site_id : Int64, + slug : String + ) : Int64? + r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug]) + r ? r.to_i64 : nil + end + + def add( + site_id : Int64, + user_id : Int64, + ) : Int64 + db = @context.dbs.rw + post_id = -1_i64 + + db.transaction do + post_id = @context.models.post.add(site_id, user_id) + db.query(SQL[:add], [post_id.to_s]) + end + + 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, + + repo_url : String? = nil, + ) + db = @context.dbs.rw + + db.transaction do + @context.models.post.set( + post_id: post_id, + + site_id: site_id, + state: state, + + have_posted_at: have_posted_at, + posted_at: posted_at, + + have_expires_at: have_expires_at, + expires_at: expires_at, + + slug: slug, + slug_lock: slug_lock, + + name: name, + body: body, + ) + + if repo_url + db.query(SQL[:set], [repo_url, post_id.to_s]) + end + end + end + + def get(post_id : Int64) + @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil! + end +end diff --git a/src/guff/models/role.cr b/src/guff/models/role.cr new file mode 100644 index 0000000..f2e84a5 --- /dev/null +++ b/src/guff/models/role.cr @@ -0,0 +1,25 @@ +class Guff::Models::RoleModel < Guff::Models::Model + SQL = { + get_roles: " + SELECT role, + name + + FROM roles + + ORDER BY sort + ", + } + + def get_roles : Array(Hash(String, String)) + r = [] of Hash(String, String) + + @context.dbs.ro.all(SQL[:get_roles]) do |row| + r << { + "role" => row["role"] as String, + "name" => row["name"] as String, + } + end + + r + end +end diff --git a/src/guff/models/session.cr b/src/guff/models/session.cr new file mode 100644 index 0000000..b715d6f --- /dev/null +++ b/src/guff/models/session.cr @@ -0,0 +1,57 @@ +# TODO: handle session expiration +class Guff::Models::SessionModel < Guff::Models::Model + SQL = { + load: " + SELECT data + + FROM sessions + + WHERE id = ? + -- TODO: + -- AND strftime('%s', created_at, '1 week') > strftime('%s') + -- AND strftime('%s', updated_at, '2 hours') > strftime('%s') + ", + + save: " + UPDATE sessions + + SET updated_at = CURRENT_TIMESTAMP, + data = ? + + WHERE id = ? + ", + + delete: " + DELETE FROM sessions WHERE id = ? + ", + + create: " + INSERT INTO sessions(id, data) VALUES (?, ?) + ", + } + + def load(id : String) : String? + @context.dbs.ro.one(SQL[:load], [id]) + end + + def save(id : String, data : String) + @context.dbs.rw.query(SQL[:save], [data, id]) + nil + end + + def delete(id : String?) + @context.dbs.rw.query(SQL[:delete], [id]) if id + nil + end + + def create(data : String) : String + # generate id + r = SecureRandom.hex(32) + + # save session + @context.dbs.rw.query(SQL[:create], [r, data]) + + # return session id + r + end +end diff --git a/src/guff/models/site.cr b/src/guff/models/site.cr new file mode 100644 index 0000000..8ab4162 --- /dev/null +++ b/src/guff/models/site.cr @@ -0,0 +1,72 @@ +class Guff::Models::SiteModel < Guff::Models::Model + SQL = { + get_id: " + SELECT site_id + + FROM ( + SELECT a.site_id + + FROM site_domains a + JOIN sites b + ON (b.site_id = a.site_id) + + WHERE b.is_active + AND a.domain = $1 + + UNION ALL + + SELECT site_id + + FROM sites + + WHERE is_active + AND is_default + ) a + + LIMIT 1 + ", + + get_default_id: " + SELECT site_id + + FROM sites + + WHERE is_active + AND is_default + ", + + get_sites: " + SELECT site_id, + name, + is_active, + is_default + + FROM sites + + ORDER BY LOWER(name) + + ", + } + + def get_id(host : String?) : Int64? + r = @context.dbs.ro.one(SQL[:get_id], [host || ""]) + r ? r.to_i64 : nil + end + + def get_default_id : Int64 + @context.dbs.ro.one(SQL[:get_default_id]).not_nil!.to_i64 + end + + def get_sites + rows = [] of Hash(String, String) + + @context.dbs.ro.all(SQL[:get_sites]) do |row| + rows << row.reduce({} of String => String) do |r, kv| + r[kv[0]] = kv[1].to_s + r + end + end + + rows + end +end diff --git a/src/guff/models/state.cr b/src/guff/models/state.cr new file mode 100644 index 0000000..07557e9 --- /dev/null +++ b/src/guff/models/state.cr @@ -0,0 +1,27 @@ +class Guff::Models::StateModel < Guff::Models::Model + SQL = { + get_states: " + SELECT state_id, + state, + name, + icon + + FROM states + + ORDER BY sort + ", + } + + def get_states + rows = [] of Hash(String, String) + + @context.dbs.ro.all(SQL[:get_states]) do |row| + rows << row.reduce({} of String => String) do |r, kv| + r[kv[0]] = kv[1].to_s + r + end + end + + rows + end +end diff --git a/src/guff/models/user.cr b/src/guff/models/user.cr new file mode 100644 index 0000000..62538e1 --- /dev/null +++ b/src/guff/models/user.cr @@ -0,0 +1,173 @@ +class Guff::Models::UserModel < Guff::Models::Model + SQL = { + login: " + SELECT user_id, + password + + FROM users + + WHERE is_active + AND email = ? + ", + + has_role: " + SELECT 1 + + FROM users + + WHERE is_active + AND user_id = ? + AND role_id IN (SELECT role_id FROM roles WHERE role IN (%s)) + ", + + add: " + INSERT INTO users(role_id, name, email, password, is_active) VALUES + ((SELECT role_id FROM roles where role = ?), ?, ?, ?, ?) + ", + + set: " + UPDATE users + SET %s + WHERE user_id = ? + ", + + get_users: " + SELECT a.user_id, + a.name, + a.email, + a.is_active, + b.role, + b.name AS role_name + + FROM users a + JOIN roles b + ON (b.role_id = a.role_id) + + ORDER BY LOWER(a.name) + ", + + get: " + SELECT a.user_id, + a.name, + a.email, + a.is_active, + b.role, + b.name AS role_name + + FROM users a + JOIN roles b + ON (b.role_id = a.role_id) + + WHERE a.user_id = ? + ", + } + + def login( + email : String, + password : String + ) : Int64? + r = nil + + if row = @context.dbs.ro.row(SQL[:login], [email]) + if Password.test(row["password"] as String, password) + # given email and password matches active user + r = row["user_id"] as Int64 + end + end + + # return result + r + end + + def has_role?(user_id : Int64, roles : Array(String)) + raise "empty role list" unless roles.size > 0 + + !!@context.dbs.ro.one(SQL[:has_role] % [ + (["?"] * roles.size).join(",") + ], [user_id.to_s].concat(roles)) + end + + def add( + name : String, + email : String, + password : String, + role : String, + active : Bool, + ) : Int64 + @context.dbs.rw.query(SQL[:add], [ + role, + name, + email, + Password.create(password), + active ? "1" : "0", + ]) + + @context.dbs.rw.last_insert_row_id.to_i64 + end + + def set( + user_id : Int64, + name : String? = nil, + email : String? = nil, + password : String? = nil, + role : String? = nil, + active : Bool? = nil, + ) + sets = [] of String + args = [] of String + + if name + sets << "name = ?" + args << name + end + + if email + sets << "email = ?" + args << email + end + + if role + sets << "role_id = (SELECT role_id FROM roles WHERE role = ?)" + args << role + end + + if password + sets << "password = ?" + args << Password.create(password) + end + + if active != nil + sets << "is_active = ?" + args << (active ? "1" : "0") + end + + if sets.size > 0 + args << user_id.to_s + @context.dbs.rw.query(SQL[:set] % sets.join(", "), args) + end + end + + def get_users + rows = [] of Hash(String, String) + + @context.dbs.ro.all(SQL[:get_users]) 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 + + rows + end + + def get(user_id : Int64) + row = @context.dbs.ro.row(SQL[:get], [user_id.to_s]) + raise "unknown user: #{user_id}" unless row + + row.reduce({} of String => String) do |r, kv| + r[kv[0]] = kv[1].to_s + r + end + end +end diff --git a/src/guff/session.cr b/src/guff/session.cr new file mode 100644 index 0000000..2566a59 --- /dev/null +++ b/src/guff/session.cr @@ -0,0 +1,72 @@ +class Guff::Session < ::Hash(String, String) + getter :session_id + + # session cookie name + # FIXME: does this belong here? + COOKIE = "guff_session" + + def initialize(@context : Context) + super() + @session_id = nil + end + + def load(id : String) + begin + # clear existing session + clear + + # load session values + JSON.parse(@context.models.session.load(id).not_nil!).each do |key, val| + self[key.as_s] = val.as_s + end + + # save session id + @session_id = id + + # return success + true + rescue err + STDERR.puts "session load failed: #{err}" + # invalid session id, return failure + false + end + end + + def create(hash : Hash(String, String)) : String + clear + merge!(hash) + @session_id = @context.models.session.create(hash.to_json) + end + + def save + if valid? + @context.models.session.save(@session_id.not_nil!, to_json) + + # return success + true + else + # no session, return failure + false + end + end + + def clear + super + @session_id = nil + end + + def delete : String? + r = @session_id + + if valid? + @context.models.session.delete(r) + clear + end + + r + end + + def valid? + @session_id != nil + end +end |