1 files changed, 25 insertions, 51 deletions

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