add user models

4 files changed, 156 insertions, 0 deletions

diff --git a/TODO.md b/TODO.md
index ddc8542..9874b78 100644
--- a/TODO.md
+++ b/TODO.md
@@ -9,6 +9,7 @@ TODO
 * triggers for removing unused `post_tag` and `tag` entries
 * configurable dashboard
   (like wordpress, choose modules, columns, layout)
+* user password strength
 
 Editors
 -------
diff --git a/src/guff/migrations.cr b/src/guff/migrations.cr
index c32e671..495b3dd 100644
--- a/src/guff/migrations.cr
+++ b/src/guff/migrations.cr
@@ -145,5 +145,71 @@ module Guff
     }, %{
       CREATE INDEX in_sessions_sid on sessions(session_id)
     }],
+  }, {
+    id: "6-roles",
+
+    sql: [%{
+      CREATE TABLE roles (
+        -- unique identifier
+        role_id   INTEGER PRIMARY KEY,
+
+        -- user-visible name of role
+        role_name TEXT UNIQUE NOT NULL,
+
+        -- brief description of role
+        role_desc TEXT NOT NULL
+                  CHECK (LENGTH(role_desc) > 0)
+      )
+    }, %{
+      INSERT INTO roles(role_id, role_name, role_desc) VALUES
+        (0, 'guest',  'Guest account, no login.'),
+        (1, 'viewer', 'Login and read-only access.'),
+        (2, 'editor', 'Can create and edit posts.'),
+        (3, 'admin',  'Can create and edit posts and modify site.')
+    }],
+  }, {
+    id: "7-users",
+
+    sql: [%{
+      CREATE TABLE users (
+        user_id         INTEGER PRIMARY KEY,
+
+        -- when was this user created?
+        created_at      TIMESTAMP WITH TIME ZONE
+                        NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+        -- is this user active?
+        is_active       BOOLEAN NOT NULL DEFAULT false,
+
+        -- role of this user
+        role_id         INTEGER NOT NULL DEFAULT 0
+                        REFERENCES roles(role_id),
+
+        -- display name (not the same as their login)
+        user_name       TEXT UNIQUE NOT NULL
+                        CHECK (LENGTH(user_name) > 0)
+      )
+    }, %{
+      INSERT INTO users(user_id, is_active, role_id, user_name) VALUES
+        (0, 1, (SELECT role_id FROM roles WHERE role_name = 'guest'), 'Guest'),
+        (1, 1, (SELECT role_id FROM roles WHERE role_name = 'admin'), 'Admin')
+    }],
+  }, {
+    id: "8-user-logins",
+    sql: [%{
+      CREATE TABLE user_logins (
+        user_id         INTEGER UNIQUE NOT NULL
+                        REFERENCES users(user_id),
+
+        -- email address of user
+        email           TEXT UNIQUE NOT NULL
+                        CHECK (email LIKE '%_@_%'),
+
+        -- bcrypt hash of password
+        pass_hash       TEXT NOT NULL
+      )
+    }, %{
+      CREATE INDEX in_user_logins_email ON user_logins(email)
+    }],
   }]
 end
diff --git a/src/guff/models.cr b/src/guff/models.cr
index e9102b5..4da8c2d 100644
--- a/src/guff/models.cr
+++ b/src/guff/models.cr
@@ -34,6 +34,7 @@ module Guff
       tag:      TagModel,
       site:     SiteModel,
       session:  SessionModel,
+      user:     UserModel,
     })
   end
 end
diff --git a/src/guff/models/user.cr b/src/guff/models/user.cr
new file mode 100644
index 0000000..4dc6d0a
--- /dev/null
+++ b/src/guff/models/user.cr
@@ -0,0 +1,88 @@
+class Guff::UserModel < Guff::Model
+  SQL = TemplateCache.new({
+    add_user: "
+      INSERT INTO users(user_name) VALUES (:user_name)
+    ",
+
+    update_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)
+    ",
+  })
+
+  def initialize(models : Models)
+    super(models, SQL)
+  end
+
+  def add_user(user_name : String)
+    query(:add_user, {
+      "user_name": user_name
+    }, nil)
+
+    # return user id
+    last_insert_row_id
+  end
+
+  def update_user(
+    user_id   : Int32,
+    user_name : String?   = nil,
+    active    : Boolean?  = nil,
+  )
+    sets = [] of String
+    args = { "user_id": user_id.to_s }
+
+    if user_name != nil
+      sets << "user_name = :user_name"
+      args["user_name"] = user_name
+    end
+
+    if active != nil
+      sets << "is_active = :is_active"
+      args["is_active"] = active ? "1" : "0"
+    end
+
+    query(:update_user, args, {
+      "sets": sets.join(","),
+    }) if sets.length > 0
+  end
+
+  def delete_login(user_id : Int32)
+    query(:delete_login, {
+      "user_id": user_id.to_s
+    }, nil)
+  end
+
+  def add_login(
+    user_id   : Int32,
+    email     : String,
+    password  : String,
+  )
+    # TODO: check password strength
+    raise "password too short" if password.length < 4
+
+    # hash password
+    pass_hash = Crypto::Bcrypt::Password.create(password, cost: 10)
+
+    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
+end