Add applicant table

4 files changed, 266 insertions, 183 deletions

diff --git a/sqlite.cr b/sqlite.cr
deleted file mode 100644
index 5dfbe4d..0000000
--- a/sqlite.cr
+++ /dev/null
@@ -1,150 +0,0 @@
-# SQLite3 wrapper
-# Copyright (C) 2023  Nguyễn Gia Phong
-#
-# This file if part of hybring.
-#
-# Hybring is free software: you can redistribute it and/or modify
-# it under the terms of the GNU Affero General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
-#
-# Hybring is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-# GNU Affero General Public License for more details.
-#
-# You should have received a copy of the GNU Affero General Public License
-# along with hybring.  If not, see <https://www.gnu.org/licenses/>.
-
-@[Link("sqlite3")]
-lib SQLite
-  OK = 0
-  ROW = 100
-  DONE = 101
-
-  type Database = Void*
-  type Statement = Void*
-
-  fun errstr = sqlite3_errstr(rc : LibC::Int) : LibC::Char*
-  fun mprintf = sqlite3_mprintf(format : LibC::Char*, ...) : LibC::Char*
-  fun free = sqlite3_free(format : Void*, ...)
-
-  fun open = sqlite3_open(filename : LibC::Char*, db : Database*) : LibC::Int
-  fun prepare = sqlite3_prepare(db : Database, query : LibC::Char*,
-                                length : LibC::Int, stmt : Statement*,
-                                query_tail : LibC::Char**) : LibC::Int
-  fun step = sqlite3_step(stmt : Statement) : Int32
-  fun column_text = sqlite3_column_text(stmt : Statement,
-                                        col : LibC::Int) : LibC::Char*
-  fun column_int = sqlite3_column_int(stmt : Statement,
-                                      col : LibC::Int) : LibC::Int
-  fun finalize = sqlite3_finalize(stmt : Statement) : LibC::Int
-  fun close = sqlite3_close(db : Database) : LibC::Int
-end
-
-class Database
-  MIGRATIONS = [""] # migration #0 is reserved for schema initialization
-  SCHEMA = "CREATE TABLE member (
-    id INTEGER PRIMARY KEY,
-    nick TEXT NOT NULL UNIQUE,
-    opennic TEXT NOT NULL UNIQUE,
-    icann TEXT NOT NULL UNIQUE)"
-  INSERT_MEMBER = "INSERT INTO member (nick, opennic, icann)
-    VALUES (%Q, %Q, %Q)"
-
-  class Statement
-    def initialize(db, query)
-      bytes = query.to_slice
-      Database.check SQLite.prepare db, bytes, bytes.size,
-                                    out @ref, out @tail
-    end
-
-    def step : LibC::Int
-      SQLite.step @ref
-    end
-
-    def row
-      Row.new @ref
-    end
-
-    def finalize
-      Database.check SQLite.finalize @ref
-    end
-  end
-
-  class Column
-    def initialize(stmt : SQLite::Statement, i : LibC::Int)
-      @stmt = stmt
-      @i = i
-    end
-
-    def int
-      SQLite.column_int @stmt, @i
-    end
-
-    def text
-      String.new SQLite.column_text @stmt, @i
-    end
-  end
-
-  class Row
-    def initialize(stmt : SQLite::Statement)
-      @stmt = stmt
-    end
-
-    def [](i : LibC::Int)
-      Column.new @stmt, i
-    end
-  end
-
-  def initialize(path : String, opennic, icann)
-    Database.check SQLite.open path, out @ref
-    self.exec "PRAGMA user_version" do |row|
-      version = row[0].int
-      raise "negative schema version" if version < 0
-      raise "schema version newer than supported" if version > MIGRATIONS.size
-      if version == 0
-        self.exec SCHEMA do end
-        self.exec "PRAGMA user_version = #{MIGRATIONS.size}" do end
-        # Avoid out-of-bound when looking for neighbors.
-        self.exec INSERT_MEMBER, "self", opennic, icann do end
-      end
-    rescue ex
-      self.finalize
-      raise ex
-    end
-  end
-
-  def exec(query : String, *values)
-    sql = SQLite.mprintf query, *values
-    stmt = Statement.new @ref, String.new sql
-    SQLite.free sql
-    loop do
-      rc = stmt.step
-      case rc
-      when SQLite::ROW
-        yield stmt.row
-      when SQLite::DONE
-        break
-      else
-        Database.check rc
-      end
-    end
-  end
-
-  def members
-    result = [] of Tuple(String, String, String)
-    self.exec "SELECT nick, opennic, icann FROM member" do |row|
-      result << {row[0].text, row[1].text, row[2].text}
-    end
-    result
-  end
-
-  def finalize
-    Database.check SQLite.close @ref
-  end
-end
-
-def Database.check(rc : LibC::Int)
-  raise String.new SQLite.errstr rc if rc != SQLite::OK
-end
diff --git a/http.cr b/src/http.cr
index c1de4dc..63eee07 100644
--- a/http.cr
+++ b/src/http.cr
@@ -34,7 +34,7 @@ def http_error(context, status, message = nil)
 end
 
 if ARGV.empty?
-  puts "usage: #{PROGRAM_NAME} config.ini"
+  puts "usage: #{Path[PROGRAM_NAME].basename} config.ini"
   exit 1
 end
 cfg = INI.parse File.read ARGV[0]
@@ -43,14 +43,13 @@ opennic_host = URI.parse(cfg["opennic"]["remote"]).host
 Dir.mkdir_p Path[cfg["general"]["db"]].parent
 db = Database.new cfg["general"]["db"],
                   cfg["opennic"]["remote"], cfg["icann"]["remote"]
-members = db.members
 
 opennic_page = Page.new cfg["opennic"]["local"], cfg["opennic"]["remote"],
-                        cfg["general"]["api"]
-opennic_page.write members
+                        cfg["general"]["api"], db
+opennic_page.write
 icann_page = Page.new cfg["icann"]["local"], cfg["icann"]["remote"],
-                      cfg["general"]["api"]
-icann_page.write members
+                      cfg["general"]["api"], db
+icann_page.write
 
 server = HTTP::Server.new do |context|
   # Manually crafted request
@@ -101,22 +100,24 @@ server = HTTP::Server.new do |context|
   next http_error context, 400, "Invalid Parameter" if invalid_param
   next http_error context, 400, "Missing Parameter" unless params.size == 4
 
-  members.each do |nick, opennic, icann|
+  others = db.members.each_value.chain db.applicants.each_value
+  others.each do |nick, opennic, icann|
     errors["nick"] = "Must be unique" if nick == params["nick"]
     errors["opennic"] = "Must be unique" if opennic == params["opennic"]
     errors["icann"] = "Must be unique" if icann == params["icann"]
   end
 
   if errors.empty?
+    db.add_applicant params["nick"], params["opennic"], params["icann"]
     # TODO: write feed
   else
     context.response.status_code = 400 unless errors.empty?
   end
   context.response.content_type = "application/xhtml+xml"
   if params["host"] == opennic_host
-    context.response.print opennic_page.build members, errors, params
+    context.response.print opennic_page.build errors, params
   else
-    context.response.print icann_page.build members, errors, params
+    context.response.print icann_page.build errors, params
   end
   # TODO: schedule dynamic check
 end
diff --git a/src/sqlite.cr b/src/sqlite.cr
new file mode 100644
index 0000000..1d89dc4
--- /dev/null
+++ b/src/sqlite.cr
@@ -0,0 +1,227 @@
+# SQLite3 wrapper
+# Copyright (C) 2023  Nguyễn Gia Phong
+#
+# This file if part of hybring.
+#
+# Hybring is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# Hybring is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with hybring.  If not, see <https://www.gnu.org/licenses/>.
+
+@[Link("sqlite3")]
+lib SQLite
+  OK = 0
+  DELETE = 9
+  INSERT = 18
+  UPDATE = 23
+  ROW = 100
+  DONE = 101
+
+  type Database = Void*
+  type Statement = Void*
+
+  fun errstr = sqlite3_errstr(rc : LibC::Int) : LibC::Char*
+  fun mprintf = sqlite3_mprintf(format : LibC::Char*, ...) : LibC::Char*
+  fun free = sqlite3_free(format : Void*, ...)
+
+  fun open = sqlite3_open(filename : LibC::Char*, db : Database*) : LibC::Int
+  fun close = sqlite3_close(db : Database) : LibC::Int
+  fun update_hook = sqlite3_update_hook(db : Database,
+                                        f : (Void*, LibC::Int, LibC::Char*,
+                                             LibC::Char*, Int64 ->),
+                                        arg : Void*)
+
+  fun prepare = sqlite3_prepare_v2(db : Database, query : LibC::Char*,
+                                   length : LibC::Int, stmt : Statement*,
+                                   query_tail : LibC::Char**) : LibC::Int
+  fun finalize = sqlite3_finalize(stmt : Statement) : LibC::Int
+  fun step = sqlite3_step(stmt : Statement) : Int32
+
+  fun column_int = sqlite3_column_int(stmt : Statement,
+                                      col : LibC::Int) : LibC::Int
+  fun column_int64 = sqlite3_column_int64(stmt : Statement,
+                                          col : LibC::Int) : Int64
+  fun column_text = sqlite3_column_text(stmt : Statement,
+                                        col : LibC::Int) : LibC::Char*
+end
+
+class Database
+  MIGRATIONS = [
+    ["CREATE TABLE member (id INTEGER PRIMARY KEY,
+        nick TEXT NOT NULL UNIQUE,
+        opennic TEXT NOT NULL UNIQUE,
+        icann TEXT NOT NULL UNIQUE)",
+     "CREATE TABLE applicant (id INTEGER PRIMARY KEY,
+        nick TEXT NOT NULL UNIQUE,
+        opennic TEXT NOT NULL UNIQUE,
+        icann TEXT NOT NULL UNIQUE)"]]
+  SELECT_MEMBER_ROW = "SELECT nick, opennic, icann FROM member
+    WHERE rowid = %lli"
+  SELECT_APPLICANT_ROW = "SELECT nick, opennic, icann FROM applicant
+    WHERE rowid = %lli"
+  INSERT_MEMBER = "INSERT INTO member (nick, opennic, icann)
+    VALUES (%Q, %Q, %Q)"
+  INSERT_APPLICANT = "INSERT INTO applicant (nick, opennic, icann)
+    VALUES (%Q, %Q, %Q)"
+
+  class Statement
+    def initialize(db, query)
+      Database.check SQLite.prepare db, query, LibC.strlen(query),
+                                    out @ref, out _
+    end
+
+    def step : LibC::Int
+      SQLite.step @ref
+    end
+
+    def row
+      Row.new @ref
+    end
+
+    def finalize
+      Database.check SQLite.finalize @ref
+    end
+  end
+
+  class Column
+    def initialize(stmt : SQLite::Statement, i : LibC::Int)
+      @stmt = stmt
+      @i = i
+    end
+
+    def int
+      SQLite.column_int @stmt, @i
+    end
+
+    def int64
+      SQLite.column_int64 @stmt, @i
+    end
+
+    def text
+      String.new SQLite.column_text @stmt, @i
+    end
+  end
+
+  class Row
+    def initialize(stmt : SQLite::Statement)
+      @stmt = stmt
+    end
+
+    def [](i : LibC::Int)
+      Column.new @stmt, i
+    end
+  end
+
+  def initialize(path : String, opennic, icann)
+    Database.check SQLite.open path, out @ref
+    @members = {} of Int64 => {String, String, String}
+    @applicants = {} of Int64 => {String, String, String}
+
+    self.exec "PRAGMA user_version" do |row|
+      version = row[0].int
+      raise "negative schema version" if version < 0
+      raise "schema version newer than supported" if version > MIGRATIONS.size
+      self.transact do
+        MIGRATIONS[version..].each do |migration|
+          migration.each do |sql|
+            self.exec sql do end
+          end
+        end
+        self.exec "PRAGMA user_version = %u", MIGRATIONS.size do end
+        if version == 0 # avoid out-of-bound when looking for neighbors
+          self.exec INSERT_MEMBER, "self", opennic, icann do end
+        end
+      end
+    rescue ex
+      self.finalize
+      raise ex
+    end
+
+    begin
+      self.exec "SELECT rowid, nick, opennic, icann FROM member" do |row|
+        @members[row[0].int64] = {row[1].text, row[2].text, row[3].text}
+      end
+      self.exec "SELECT rowid, nick, opennic, icann FROM applicant" do |row|
+        @applicants[row[0].int64] = {row[1].text, row[2].text, row[3].text}
+      end
+    rescue ex
+      self.finalize
+      raise ex
+    end
+    SQLite.update_hook @ref, ->(arg, action, db, table, rowid) {
+      return unless db == "main"
+      obj = arg.as Database
+      case table
+      when "member"
+        case action
+        when SQLite::DELETE
+          obj.members.delete rowid
+        when SQLite::INSERT, SQLite::UPDATE
+          obj.exec SELECT_MEMBER_ROW, rowid do |row|
+            obj.members[rowid] = {row[0].text, row[1].text, row[2].text}
+          end
+        end
+      when "applicant"
+        case action
+        when SQLite::DELETE
+          obj.applicants.delete rowid
+        when SQLite::INSERT, SQLite::UPDATE
+          obj.exec SELECT_APPLICANT_ROW, rowid do |row|
+            obj.applicants[rowid] = {row[0].text, row[1].text, row[2].text}
+          end
+        end
+      end
+    }, self.as Void*
+  end
+
+  def exec(query : String, *values)
+    sql = SQLite.mprintf query, *values
+    stmt = Statement.new @ref, sql
+    SQLite.free sql
+    loop do
+      rc = stmt.step
+      case rc
+      when SQLite::ROW
+        yield stmt.row
+      when SQLite::DONE
+        break
+      else
+        Database.check rc
+      end
+    end
+  end
+
+  def transact
+    self.exec "BEGIN TRANSACTION" do end
+    yield
+    self.exec "COMMIT" do end
+  end
+
+  def members
+    @members
+  end
+
+  def applicants
+    @applicants
+  end
+
+  def add_applicant(nick, opennic, icann)
+    self.exec INSERT_APPLICANT, nick, opennic, icann do end
+  end
+
+  def finalize
+    Database.check SQLite.close @ref
+  end
+end
+
+def Database.check(rc : LibC::Int)
+  raise String.new SQLite.errstr rc if rc != SQLite::OK
+end
diff --git a/xhtml.cr b/src/xhtml.cr
index 4749af1..ea4f87e 100644
--- a/xhtml.cr
+++ b/src/xhtml.cr
@@ -19,6 +19,8 @@
 require "uri"
 require "xml"
 
+require "./sqlite"
+
 CSS = "
       html {
           margin: auto;
@@ -36,12 +38,14 @@ CSS = "
     "
 
 class Page
-  def initialize(static_dir : String, static_url : String, api_url : String)
+  def initialize(static_dir : String, static_url : String, api_url : String,
+                 db : Database)
     Dir.mkdir_p static_dir
     @static_file = Path[static_dir] / "index.xhtml"
     @static_url = static_url
     @static_host = URI.parse(static_url).host
     @api_url = api_url
+    @db = db
   end
 
   def criteria(xml)
@@ -74,6 +78,7 @@ class Page
     if error
       xml.element "span"
       xml.element "span", class: "error" do xml.text error end
+      xml.element "br"
     end
     xml.element "label", for: name do xml.text label end
     xml.element "input", name: name, placeholder: hint, value: value,
@@ -84,30 +89,32 @@ class Page
   def form(xml, errors = {} of String => String,
            params = {} of String => String)
     xml.element "p" do xml.text "Then, please fill out the form below." end
+    nick = params.fetch("nick", nil)
     xml.element "form", action: @api_url, method: "POST" do
-      input xml, "nick", "Nickname", "digits and lowercase letters",
-        errors.fetch("nick", nil), params.fetch("nick", nil)
+      input xml, "nick", "Nickname", "digits or lowercase letters",
+            errors.fetch("nick", nil), nick || ""
       input xml, "opennic", "OpenNIC URL", "e.g. http://example.null",
-        errors.fetch("opennic", nil), params.fetch("opennic", nil)
+            errors.fetch("opennic", nil), params.fetch("opennic", "")
       input xml, "icann", "ICANN URL", "e.g. https://example.net",
-        errors.fetch("icann", nil), params.fetch("icann", nil)
+            errors.fetch("icann", nil), params.fetch("icann", "")
       xml.element "span" do
         xml.element "input", type: "hidden", name: "host", value: @static_host
       end
       xml.element "input", type: "submit", value: "Let me in!"
       xml.element "br"
     end
-  end
 
-  def member(xml, nick, opennic, icann, feed? = false)
     xml.element "p" do
-      xml.text "The following membership is pending for approval."
+      xml.text "Your application is pending for approval."
       xml.text "  Please subscribe to this "
       xml.element "a", href: "#{@static_url}/#{nick}.atom" do
           xml.text "web feed"
       end
       xml.text " for further instructions."
-    end if feed?
+    end if nick && errors.empty?
+  end
+
+  def member(xml, nick, opennic, icann)
     xml.element "h3" do xml.text nick end
     xml.text "OpenNIC: "
     xml.element "a", href: opennic do xml.text opennic end
@@ -117,7 +124,7 @@ class Page
     xml.element "br"
   end
 
-  def build(members, errors = {} of String => String,
+  def build(errors = {} of String => String,
             params = {} of String => String) : String
     XML.build encoding: "UTF-8", indent: "  " do |xml|
       xml.element "html", xmlns: "http://www.w3.org/1999/xhtml", lang: "en" do
@@ -136,6 +143,11 @@ class Page
           end
           criteria xml
 
+          xml.element "h2" do xml.text "members" end
+          @db.members.each_value do |nick, opennic, icann|
+            member xml, nick, opennic, icann
+          end
+
           xml.element "h2" do xml.text "joining" end
           xml.element "p" do
             xml.text "First, add "
@@ -152,26 +164,19 @@ class Page
           xml.element "ul" do
             xml.element "li" do
               xml.text "Left: "
-              _, left, _ = members[-1]
+              _, left, _ = @db.members.last_value
               xml.element "a", href: left do xml.text left end
             end
             xml.element "li" do
               xml.text "Right: "
-              _, right, _ = members[0]
+              _, right, _ = @db.members.first_value
               xml.element "a", href: right do xml.text right end
             end
           end
+          form xml, errors, params
 
-          if params.empty? # static page
-            form xml
-          elsif errors.empty?
-            member xml, params["nick"], params["opennic"], params["icann"], true
-          else
-            form xml, errors, params
-          end
-
-          xml.element "h2" do xml.text "members" end
-          members.each do |nick, opennic, icann|
+          xml.element "h2" do xml.text "applicants" end
+          @db.applicants.each_value do |nick, opennic, icann|
             member xml, nick, opennic, icann
           end
         end
@@ -179,7 +184,7 @@ class Page
     end
   end
 
-  def write(members)
-    File.write @static_file, self.build members
+  def write
+    File.write @static_file, self.build
   end
 end