From 85bb05cbd88a0021cfa5974d9dc15d3aae3814df Mon Sep 17 00:00:00 2001 From: Nguyễn Gia Phong Date: Mon, 6 Mar 2023 01:20:33 +0900 Subject: Use relational DB more properly --- src/http.cr | 30 ++++++++--------------- src/sqlite.cr | 76 ++++++++++++++++++++--------------------------------------- src/xhtml.cr | 40 ++++++++++++++++++------------- 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 -- cgit 1.4.1