Add SQLite high-level wrapper

2 files changed, 136 insertions, 28 deletions

diff --git a/hybring.cr b/hybring.cr
index 4b37469..f4a2f07 100644
--- a/hybring.cr
+++ b/hybring.cr
@@ -19,6 +19,7 @@
 require "http/server"
 require "uri"
 
+require "./sqlite"
 require "./xhtml"
 
 MAX_CONTENT_LENGTH = 4096
@@ -26,38 +27,11 @@ OPENNIC_TLD = Set{".bbs", ".chan", ".cyb", ".dyn", ".epic",
                   ".geek", ".gopher", ".indy", ".libre", ".neo",
                   ".null", ".o", ".oss", ".oz", ".parody", ".pirate"}
 
-@[Link("sqlite3")]
-lib SQLite
-  type Database = Void*
-  type Statement = 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 finalize = sqlite3_finalize(stmt : Statement) : LibC::Int
-  fun close = sqlite3_close(db : Database) : LibC::Int
-end
-
-DB_INIT = "CREATE TABLE member (
-  id INTEGER PRIMARY KEY,
-  nick TEXT NOT NULL UNIQUE,
-  opennic TEXT NOT NULL UNIQUE,
-  icann TEXT NOT NULL UNIQUE,
-);";
-
-SQLite.open "foo.db", out db
-begin
-ensure
-  SQLite.close db
-end
-
 def http_error(context, status, message = nil)
   context.response.respond_with_status status, message
 end
 
+db = Database.new "hybring.db"
 File.write "index.xhtml", page
 server = HTTP::Server.new do |context|
   # Manually crafted request
diff --git a/sqlite.cr b/sqlite.cr
new file mode 100644
index 0000000..7df496b
--- /dev/null
+++ b/sqlite.cr
@@ -0,0 +1,134 @@
+# 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 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)";
+
+  class Statement
+    def initialize(db, query)
+      bytes = query.to_slice
+      Database.check SQLite.prepare db.ref, bytes, bytes.size,
+                                    out @ref, out @tail
+    end
+
+    def ref
+      @ref
+    end
+
+    def step : LibC::Int
+      SQLite.step @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)
+    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
+      end
+    end
+  end
+
+  def ref
+    @ref
+  end
+
+  def exec(query : String)
+    stmt = Statement.new self, query
+    loop do
+      rc = stmt.step
+      case rc
+      when SQLite::ROW
+        yield Row.new stmt.ref
+      when SQLite::DONE
+        break
+      else
+        Database.check rc
+      end
+    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