require "crypto/bcrypt" class Guff::UserModel < Guff::Model SQL = TemplateCache.new({ get_users: " SELECT a.user_id, a.created_at, a.is_active, a.user_name, b.role_name, c.email FROM users a JOIN roles b ON (b.role_id = a.role_id) JOIN user_logins c ON (c.user_id = a.user_id) ORDER BY LOWER(a.user_name) ", add_user: " INSERT INTO users(user_name) VALUES (:user_name) ", edit_user: " UPDATE users SET %{sets} WHERE user_id = :user_id ", delete_login: " DELETE FROM user_logins WHERE user_id = :user_id ", add_login: " INSERT INTO user_logins(user_id, email, pass_hash) VALUES (:user_id, :email, :pass_hash) ", edit_login: " UPDATE user_logins SET %{sets} WHERE user_id = :user_id ", login: " SELECT a.user_id, a.pass_hash FROM user_logins a JOIN users b ON (b.user_id = a.user_id AND b.is_active) WHERE a.email = :email ", }) def initialize(models : Models) super(models, SQL) end def get_users r = [] of Hash(String, Int64 | String | Bool) all(:get_users, nil, nil) do |row| r << { "user_id": row["user_id"] as Int64, "created_at": row["created_at"] as String, "is_active": ((row["is_active"] as Int64) == 1), "user_name": row["user_name"] as String, "role_name": row["role_name"] as String, "email": row["email"] as String, } end r end def add_user( name : String, active : Bool? = nil, role : String? = nil, email : String? = nil, password : String? = nil, ) user_id = -1_i64 transaction do # add user query(:add_user, { "user_name": name }, nil) # get user_id user_id = last_insert_row_id as Int64 # set active and/or role if active || role edit_user(user_id, active: active, role: role, ) end # add login if email || password add_login(user_id, email.not_nil!, password.not_nil! ) end end # return id user_id end def edit_user( user_id : Int64, name : String? = nil, active : Bool? = nil, role : String? = nil, email : String? = nil, password : String? = nil, ) transaction do sets = [] of String args = { "user_id": user_id.to_s } if name != nil args["name"] = name.not_nil! sets << "user_name = :name" end if active != nil args["is_active"] = active.not_nil! ? "1" : "0" sets << "is_active = :is_active" end if role != nil args["role"] = role.not_nil! sets << " role_id = (SELECT role_id FROM roles WHERE role_name = :role) " end # exec query query(:edit_user, args, { "sets": sets.join(", "), }) if sets.size > 0 if email != nil || password != nil edit_login(user_id, email, password) end end end def delete_login(user_id : Int64) query(:delete_login, { "user_id": user_id.to_s }, nil) end def add_login( user_id : Int64, email : String, password : String, ) # hash password pass_hash = hash_password(password) transaction do # clear old credentials delete_login(user_id) # add new credentials query(:add_login, { "user_id": user_id.to_s, "email": email, "pass_hash": pass_hash, }, nil) end end private def edit_login( user_id : Int64, email : String? = nil, password : String? = nil, ) transaction do sets = [] of String args = { "user_id": user_id.to_s } if email != nil args["email"] = email.not_nil! sets << "email = :email" end if password != nil args["pass_hash"] = hash_password(password.not_nil!) sets << "pass_hash = :pass_hash" end # exec query query(:edit_login, args, { "sets": sets.join(", "), }) if sets.size > 0 end end def login( email : String, password : String ) # map email to user id and pass hash row = row(:login, { "email": email, }, nil) raise "invalid login" unless row # shut compiler up row = row.not_nil! # get pass hash from db pass_hash = Crypto::Bcrypt::Password.new(row["pass_hash"].to_s) # compare hash against password raise "invalid login" unless pass_hash == password # return user id row["user_id"] as Int64 end private def hash_password(password : String) # TODO: check password strength raise "password too short" if password.size < 4 # hash password Crypto::Bcrypt::Password.create(password, cost: 10).to_s end end