require "sqlite3" module Guff class Database < ::SQLite3::Database SQL = { table_exists: " SELECT name FROM sqlite_master WHERE type = 'table' AND name = ? ", pragma_foreign_keys: " PRAGMA foreign_keys = true ", } def initialize(path) super(path) query(SQL[:pragma_foreign_keys]) end def initialize(path, &block) super(path) query(SQL[:pragma_foreign_keys]) begin block.call(self) ensure close unless closed? end end def table_exists?(table : String) : Bool one(SQL[:table_exists], [table]) == table end def one( sql : String, args = nil : Array(String) | Hash(String, String) | Nil ) r = nil run(sql, args) do |rs| if rs.next r = rs[0] as String end end # return result r end def row( sql : String, args = nil : Array(String) | Hash(String, String) | Nil ) r = nil # exec query run(sql, args) do |rs| r = to_row(rs) if rs.next end # return result r end def all( sql : String, args = nil : Array(String) | Hash(String, String) | Nil, &block : Proc(Hash(String, ::SQLite3::Value), Nil) \ ) # build statement run(sql, args) do |rs| # walk results while rs.next # build row and pass it to callback block.call(to_row(rs)) end end nil end def query( sql : String ) run(sql, nil) do |rs| # make sure query executes rs.next nil end end # # NOTE: if you pass a block, be sure to call rs.next at least once, # or the query will _not_ execute!!! # def query( sql : String, args = nil : Array(String) | Hash(String, String) | Nil, ) run(sql, args) do |rs| # make sure query executes rs.next nil end end # # NOTE: if you pass a block, be sure to call rs.next at least once, # or the query will _not_ execute!!! # def query( sql : String, args = nil : Array(String) | Hash(String, String) | Nil, &block : Proc(::SQLite3::ResultSet, Nil) \ ) run(sql, args, &block) end def transaction(&block) query("BEGIN") block.call query("COMMIT") end private def run( sql : String, args : Hash(String, String), &block : Proc(::SQLite3::ResultSet, Nil) \ ) run(sql, [args], &block) end private def run( sql : String, args = nil : Array(String | Hash(String, String))?, &block : Proc(::SQLite3::ResultSet, Nil) \ ) # build statement st = prepare(sql) # exec and close statement if args && args.size > 0 st.execute(args, &block) else st.execute(&block) end # return result nil end private def to_row(rs) Hash(String, ::SQLite3::Value).zip(rs.columns, rs.to_a) end end end