Use relational DB more properly

3 files changed, 59 insertions, 87 deletions

diff --git a/src/http.cr b/src/http.cr
index bfd7bf7..192a398 100644
--- a/src/http.cr
+++ b/src/http.cr
@@ -93,11 +93,14 @@ class Server
       next http_error context, 400, "Invalid Parameter" if invalid_param
       next http_error context, 400, "Missing Parameter" unless params.size == 4
 
-      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"]
+
+      @db.exec "SELECT count(nick), count(opennic), count(icann)
+                FROM member
+                WHERE nick = %Q OR opennic = %Q OR icann = %Q",
+               params["nick"], params["opennic"], params["icann"] do |row|
+        errors["nick"] = "Must be unique" if row[0].int > 0
+        errors["opennic"] = "Must be unique" if row[1].int > 0
+        errors["icann"] = "Must be unique" if row[2].int > 0
       end
       if HTML_HEADINGS.includes? params["nick"]
         errors["nick"] = "Reserved names: #{HTML_HEADINGS.join ", "}"
@@ -130,22 +133,9 @@ class Server
       obj = arg.as Server
       case table
       when "member"
-        case action
-        in .delete?
-          obj.db.members.delete rowid
-        in .insert?, .update?
-          obj.db.update_member rowid
-        end
-      when "applicant"
-        case action
-        in .delete?
-          obj.db.applicants.delete rowid
-        in .insert?, .update?
-          obj.db.update_applicant rowid
-        end
+        obj.opennic_page.write
+        obj.icann_page.write
       end
-      obj.opennic_page.write
-      obj.icann_page.write
     }, self.as Void*
 
     puts "Listening on http://#{@server.bind_tcp port}"
diff --git a/src/sqlite.cr b/src/sqlite.cr
index 0032694..9ae1c51 100644
--- a/src/sqlite.cr
+++ b/src/sqlite.cr
@@ -56,25 +56,19 @@ lib SQLite
                                         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)"
+MIGRATIONS = [
+  ["CREATE TABLE member (nick TEXT NOT NULL UNIQUE, -- de facto primary
+      opennic TEXT NOT NULL UNIQUE,
+      icann TEXT NOT NULL UNIQUE,
+      official INTEGER NOT NULL DEFAULT 0,
+      left REFERENCES member(nick),
+      right REFERENCES member(nick))",
+   "CREATE TABLE change (type INTEGER, -- enum
+      nick REFERENCES member(nick) ON DELETE CASCADE,
+      time TEXT)"],
+]
 
+class Database
   alias UpdateAction = SQLite::UpdateAction
 
   class Statement
@@ -128,9 +122,8 @@ class Database
   def initialize(path, opennic, icann)
     Dir.mkdir_p path.parent
     Database.check SQLite.open path.to_s, out @ref
-    @members = {} of Int64 => {String, String, String}
-    @applicants = {} of Int64 => {String, String, String}
 
+    self.exec "PRAGMA foreign_keys = ON" do end
     self.exec "PRAGMA user_version" do |row|
       version = row[0].int
       raise "negative schema version" if version < 0
@@ -142,21 +135,11 @@ class Database
           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}
+        self.exec "INSERT INTO member (nick, opennic, icann, official,
+                                       left, right)
+                   VALUES (%Q, %Q, %Q, 1, %Q, %Q)",
+                  "self", opennic, icann, "self", "self" do
+        end if version == 0 # avoid out-of-bound when looking for neighbors
       end
     rescue ex
       self.finalize
@@ -191,23 +174,14 @@ class Database
     self.exec "COMMIT" do end
   end
 
-  getter members
-  getter applicants
-
-  def update_member(rowid)
-    self.exec SELECT_MEMBER_ROW, rowid do |row|
-      self.members[rowid] = {row[0].text, row[1].text, row[2].text}
-    end
-  end
-
-  def update_applicant(rowid)
-    self.exec SELECT_APPLICANT_ROW, rowid do |row|
-      self.applicants[rowid] = {row[0].text, row[1].text, row[2].text}
-    end
-  end
-
   def add_applicant(nick, opennic, icann)
-    self.exec INSERT_APPLICANT, nick, opennic, icann do end
+    self.exec "INSERT INTO member (nick, opennic, icann, left, right)
+               SELECT %Q, %Q, %Q, nick, left
+               FROM member
+               WHERE official = 1
+               ORDER BY rowid ASC
+               LIMIT 1",
+              nick, opennic, icann do end
   end
 
   def finalize
diff --git a/src/xhtml.cr b/src/xhtml.cr
index be3d12c..d53eb04 100644
--- a/src/xhtml.cr
+++ b/src/xhtml.cr
@@ -53,9 +53,8 @@ class Page
   end
 
   def heading(xml, level, text)
-    id = text.gsub ' ', '-'
-    xml.element "h#{level}", id: id do
-      xml.element "a", href: "##{id}" do xml.text text end
+    xml.element "h#{level}", id: text do
+      xml.element "a", href: "##{text}" do xml.text text end
     end
   end
 
@@ -155,8 +154,10 @@ class Page
           criteria xml
 
           heading xml, 2, "members"
-          @db.members.each_value do |nick, opennic, icann|
-            member xml, nick, opennic, icann
+          @db.exec "SELECT nick, opennic, icann
+                    FROM member
+                    WHERE official = 1" do |row|
+            member xml, row[0].text, row[1].text, row[2].text
           end
 
           heading xml, 2, "joining"
@@ -173,22 +174,29 @@ class Page
             xml.text " to keep the hyperlinks up to date."
           end
           xml.element "ul" do
-            xml.element "li" do
-              xml.text "Left: "
-              _, left, _ = @db.members.last_value
-              xml.element "a", href: left do xml.text left end
-            end
-            xml.element "li" do
-              xml.text "Right: "
-              _, right, _ = @db.members.first_value
-              xml.element "a", href: right do xml.text right end
+            @db.exec "SELECT neighbor.opennic, member.opennic
+                      FROM member INNER JOIN member AS neighbor
+                      ON member.left = neighbor.nick
+                      WHERE member.official = 1
+                      ORDER BY member.rowid ASC LIMIT 1" do |row|
+              left, right = row[0].text, row[1].text
+              xml.element "li" do
+                xml.text "Left: "
+                xml.element "a", href: left do xml.text left end
+              end
+              xml.element "li" do
+                xml.text "Right: "
+                xml.element "a", href: right do xml.text right end
+              end
             end
           end
           form xml, errors, params
 
           heading xml, 2, "applicants"
-          @db.applicants.each_value do |nick, opennic, icann|
-            member xml, nick, opennic, icann
+          @db.exec "SELECT nick, opennic, icann
+                    FROM member
+                    WHERE official = 0" do |row|
+            member xml, row[0].text, row[1].text, row[2].text
           end
         end
       end