diff options
Diffstat (limited to 'src/sqlite.cr')
| -rw-r--r-- | src/sqlite.cr | 227 |
1 files changed, 227 insertions, 0 deletions
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 |