Skip to content

Instantly share code, notes, and snippets.

@alex-guenther

alex-guenther/libs sqlite3 lib_sqlite3.cr

Created May 9, 2014 19:46
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save alex-guenther/8954ac68a1ecb7b788b5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save alex-guenther/8954ac68a1ecb7b788b5 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
libs sqlite3 lib_sqlite3.cr
lib LibSQLite3("sqlite3")
# Assertions:
# ===========
# I'm expecting you to be working with UTF-8, the UTF-16 methods are not implemented.
type SQLite3 : Void*
type Sqlite3_stmt : Void*
type Str : UInt8*
fun libversion = sqlite3_libversion() : UInt8*
fun libversion_number = sqlite3_libversion_number() : Int32
fun sourceid = sqlite3_sourceid() : UInt8*
fun open = sqlite3_open(filename : UInt8*, db : SQLite3** ) : Int32
fun close = sqlite3_close_v2(db : SQLite3*)
#type RowCallback : Void*, Int32, UInt8**, UInt8** ->
# ==== Result Codes ====
SQLITE_OK = 0_u32 # Successful result
# beginning-of-error-codes
SQLITE_ERROR = 1_u32 # SQL error or missing database
SQLITE_INTERNAL = 2_u32 # Internal logic error in SQLite
SQLITE_PERM = 3_u32 # Access permission denied
SQLITE_ABORT = 4_u32 # Callback routine requested an abort
SQLITE_BUSY = 5_u32 # The database file is locked
SQLITE_LOCKED = 6_u32 # A table in the database is locked
SQLITE_NOMEM = 7_u32 # A malloc() failed
SQLITE_READONLY = 8_u32 # Attempt to write a readonly database
SQLITE_INTERRUPT = 9_u32 # Operation terminated by sqlite3_interrupt()
SQLITE_IOERR = 10_u32 # Some kind of disk I/O error occurred
SQLITE_CORRUPT = 11_u32 # The database disk image is malformed
SQLITE_NOTFOUND = 12_u32 # Unknown opcode in sqlite3_file_control()
SQLITE_FULL = 13_u32 # Insertion failed because database is full
SQLITE_CANTOPEN = 14_u32 # Unable to open the database file
SQLITE_PROTOCOL = 15_u32 # Database lock protocol error
SQLITE_EMPTY = 16_u32 # Database is empty
SQLITE_SCHEMA = 17_u32 # The database schema changed
SQLITE_TOOBIG = 18_u32 # String or BLOB exceeds size limit
SQLITE_CONSTRAINT = 19_u32 # Abort due to constraint violation
SQLITE_MISMATCH = 20_u32 # Data type mismatch
SQLITE_MISUSE = 21_u32 # Library used incorrectly
SQLITE_NOLFS = 22_u32 # Uses OS features not supported on host
SQLITE_AUTH = 23_u32 # Authorization denied
SQLITE_FORMAT = 24_u32 # Auxiliary database format error
SQLITE_RANGE = 25_u32 # 2nd parameter to sqlite3_bind out of range
SQLITE_NOTADB = 26_u32 # File opened that is not a database file
SQLITE_NOTICE = 27_u32 # Notifications from sqlite3_log()
SQLITE_WARNING = 28_u32 # Warnings from sqlite3_log()
SQLITE_ROW = 100_u32 # sqlite3_step() has another row ready
SQLITE_DONE = 101_u32 # sqlite3_step() has finished executing
# ==== Instant Queries ====
fun exec = sqlite3_exec(
db : SQLite3*, # 1. An open database
sql : UInt8*, # 2. SQL to be evaluated
callback : Void*, Int32, UInt8**, UInt8** ->, # 3. Callback function
callback_arg : Void*, # 4. 1st argument to callback
error_msg : UInt8** # 5. Error msg written here
) : Int32 # Status code
# ==-= Using prepared Statements: ====
# 1. Create the object using sqlite3_prepare_v2() or a related function.
# 2. Bind values to host parameters using the sqlite3_bind_*() interfaces.
# 3. Run the SQL by calling sqlite3_step() one or more times.
# 4. Reset the statement using sqlite3_reset() then go back to step 2. Do this zero or more times.
# 5. Destroy the object using sqlite3_finalize().
# int sqlite3_prepare_v2(
# sqlite3 *db, /* Database handle */
# const char *zSql, /* SQL statement, UTF-8 encoded */
# int nByte, /* Maximum length of zSql in bytes. */
# sqlite3_stmt **ppStmt, /* OUT: Statement handle */
# const char **pzTail /* OUT: Pointer to unused portion of zSql */
# );
fun prepare = sqlite3_prepare_v2(
db : SQLite3*,
sql : UInt8*,
nByte : Int32,
ppStmt : Sqlite3_stmt**,
pzTail : UInt8**
) : Int32
# int sqlite3_step(sqlite3_stmt*);
fun step = sqlite3_step(statement : Sqlite3_stmt*) : Int32
# int sqlite3_reset(sqlite3_stmt *pStmt);
fun reset = sqlite3_reset(statement : Sqlite3_stmt*) : Int32
# int sqlite3_finalize(sqlite3_stmt *pStmt);
fun finalize = sqlite3_finalize(statement : Sqlite3_stmt*) : Int32
# ==== Binding Paramters to Prepared Statements ====
# int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
fun bind_blob = sqlite3_bind_blob(
statement : Sqlite3_stmt*,
index : Int32,
data : Void*,
bytes_size : Int32,
destructor : Void* ->) : Int32
# int sqlite3_bind_double(sqlite3_stmt*, int, double);
# int sqlite3_bind_int(sqlite3_stmt*, int, int);
fun bind_int = sqlite3_bind_int(statement : Sqlite3_stmt*, index : Int32, value : Int32) : Int32
# int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
fun bind_int64 = sqlite3_bind_int64(statement : Sqlite3_stmt*,index : Int32, value : Int64) : Int32
# int sqlite3_bind_null(sqlite3_stmt*, int);
fun bind_null = sqlite3_bind_null(statement : Sqlite3_stmt*, index : Int32) : Int32
# int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
fun bind_text = sqlite3_bind_text(
statement : Sqlite3_stmt*,
index : Int32,
value : UInt8*,
size : Int32,
destructor : Void* ->
) : Int32
#typedef struct Mem sqlite3_value;
type Sqlite3_value : Void*
# int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
fun bind_value = sqlite3_bind_value(statement : Sqlite3_stmt*, index : Int32, value : Sqlite3_value*) : Int32
# int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
fun bind_zeroblob = sqlite3_bind_zeroblob(statement : Sqlite3_stmt*, index : Int32, size : Int32) : Int32
# ==== Retreaving Results ====
# const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
fun column_blob = sqlite3_column_blob(statement : Sqlite3_stmt*, index : Int32) : Void*
# int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
fun column_bytes = sqlite3_column_bytes(statement : Sqlite3_stmt*, index : Int32) : Int32
# TODO: Double seems to be brocken
# double sqlite3_column_double(sqlite3_stmt*, int iCol);
# fun column_double = sqlite3_column_double(statement : Sqlite3_stmt*, index : Int32) : Double
# int sqlite3_column_int(sqlite3_stmt*, int iCol);
fun column_int = sqlite3_column_int(statement : Sqlite3_stmt*, index : Int32) : Int32
# sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
fun column_int64 = sqlite3_column_int64(statement : Sqlite3_stmt*, index : Int32) : Int64
# const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
fun column_text = sqlite3_column_text(statement : Sqlite3_stmt*, index : Int32) : UInt8*
# int sqlite3_column_type(sqlite3_stmt*, int iCol);
fun column_type = sqlite3_column_type(statement : Sqlite3_stmt*, index : Int32) : Int32
# sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
fun colum_value = sqlite3_column_value(statement : Sqlite3_stmt*, index : Int32) : Sqlite3_value*
# ==== Data Type Codes ====
# Every value in SQLite has one of five fundamental datatypes:
SQLITE_INTEGER = 1_u32 # 64-bit signed integer
SQLITE_FLOAT = 2_u32 # 64-bit IEEE floating point number
SQLITE_BLOB = 4_u32 # BLOB
SQLITE_NULL = 5_u32 # NULL
SQLITE3_TEXT = 3_u32 # string
end
Raw
libs sqlite3 sqlite.cr
require "./lib_sqlite3"
module SQLite3
def self.version
String.new LibSQLite3.libversion
end
def self.sourceid
String.new LibSQLite3.sourceid
end
class Database
getter :db
def self.with_db(file : String)
db = self.new(file)
yield db
db.close
end
def initialize(db_file)
#@error_msg :: UInt8
status = LibSQLite3.open(db_file, out @db )
#@result_rows = Array(Hash(String, String)).new
raise SQLite3Exception.new("Failed to open a database connection. Error code: #{status}") unless status == LibSQLite3::SQLITE_OK
end
# PLAN: store block and call it with a function
def exec2(sql : String)
#def exec2(sql : String, &block)
#@block = block
res = LibSQLite3.exec(@db,
sql,
->callback2(Void*, Int32, UInt8**, UInt8**)
nil,
out error_msg) # TODO: sqlite3_free it after usage
#raise SQLite3Exception.new(error_msg) unless res == LibSQLite3::SQLITE_OK
unless res == LibSQLite3::SQLITE_OK
puts "SQLite3 error occured" #raise SQLite3Exception.new(error_msg, __LINE__, Nil, __FILE__)
end
res
end
#@result_rows :: [] of Hash(String, String)
# Array(Hash(String, String)) #of Hash # [] of {} #of String => String
# this method is not threas save
def exec(sql : String)
@result_rows = Array(Hash(String, String)).new
res = LibSQLite3.exec(@db,
sql,
->exec_callback(Void*, Int32, UInt8**, UInt8**)
nil,
out error_msg) # TODO: sqlite3_free it after usage
#raise SQLite3Exception.new(error_msg) unless res == LibSQLite3::SQLITE_OK
unless res == LibSQLite3::SQLITE_OK
puts "SQLite3 error occured" #raise SQLite3Exception.new(error_msg, __LINE__, Nil, __FILE__)
end
@result_rows
end
def exec_callback(whatever : Void*, count : Int32, contents : UInt8**, names : UInt8**)
return 1 if @result_rows.nil?
row = {} of String => String
count.times { |i| row[String.new(contents[i])] = String.new(names[i]) }
@result_rows << row
0
end
def close
# Applications should finalize all prepared statements, close all BLOB handles, and finish all sqlite3_backup objects associated with the sqlite3 object prior to attempting to close the object.I
# the deallocation of resources is deferred until all prepared statements, BLOB handles, and sqlite3_backup objects are also destroyed.
LibSQLite3.close(@db) # TODO check return is SQLITE_OK
end
def prepare(query)
status = LibSQLite3.prepare(@db, query, query.length, out statement, nil)
if status = LibSQLite3::SQLITE_OK
statement
else
puts "SQLite3#prepare caused an error"
nil
end
end
end
class QueryNoCallbacs
def initialize(@db, @sql : String)
end
def exec()
case LibSQLite3.compile(@db.db, @sql, nil, out sqlite_vm, out error_msg)
when LibSQLite3::SQLITE_OK
puts "compiling successfull"
when LibSQLite3::SQLITE_ERROR
# error
puts "error compiling"
puts error_msg
exit
else
#wth?
puts "error "
puts error_msg
exit
end
#vm = sqlite_vm as Pointer(LibSQLite3::SQLiteVM)
case LibSQLite3.step(sqlite_vm, out count, out column_data, out column_names)
when LibSQLite3::SQLITE_ROW
# process result
puts "PROCESS ROW ... " # TODO: continue here
when LibSQLite3::SQLITE_DONE
# no more rows
when LibSQLite3::SQLITE_BUSY
when LibSQLite3::SQLITE_ERROR
puts "Error compiling SQL" # TODO:
when LibSQLite3::SQLITE_MISUSE
puts "Misuse #{error_msg}"
else
# what the hack?
puts "Oh CraP! " # #{__FILE__}, #{__LINE__}"
exit
end
end
end
class SQLite3Exception < Exception
def initialize(message, @line_number, @column_number, @filename)
super(message)
puts message
end
end
end
Raw
samples runner.cr
require "../libs/sqlite3/lib_sqlite3"
require "../libs/sqlite3/sqlite3"
class Logger
# TODO define a constructor which can write to file or what ever
def debug(message)
puts "[debug] #{message}"
end
def info(message)
puts "[info] #{message}"
end
def warn(message)
puts "[warn] #{message}"
end
def error(message)
puts "[error] #{message}"
end
end
logger = Logger.new
i = 0
loop do
SQLite3::Database.with_db(":memory:") do |db|
# def callback(whatever : Void*, column_count : Int32, column_contents : UInt8**, column_names : UInt8**)
res = db.exec "CREATE TABLE users (id INTEGER, name VARCHAR(255));"
users = "INSERT INTO 'users' VALUES(1,'joe');"+ "INSERT INTO 'users' VALUES(2,'bob');"+ "INSERT INTO 'users' VALUES(3,'Trevor');"
res = db.exec users
res = db.exec "select 1 + 1, 3 * 4, 'abc';"
res.each do |row|
line = "columns: #{row.size} "
line += row.map { |name, value| line += "'#{name}': '#{value}' " }.join(", ")
puts line
end
res = db.exec "select * from users;"
res.each do |row|
line = "columns: #{row.size} "
line += row.map { |name, value| line += "'#{name}': '#{value}' " }.join(", ")
puts line
end
end
i += 1
LibSQLite3.open(":memory:", out db)
sql = "select 2 + #{i}, \"abc\";"
LibSQLite3.prepare(db, sql, sql.length, out statement, out tail)
logger.info "Prepared"
LibSQLite3.step(statement)
logger.info "Step"
res = LibSQLite3.column_int(statement, 0)
res2 = LibSQLite3.column_text(statement, 1)
logger.info "And the result is: #{res}, #{String.new res2}"
LibSQLite3.reset(statement)
logger.info "Reset"
LibSQLite3.step(statement)
logger.info "Step 2"
LibSQLite3.finalize(statement)
logger.info "Finalized"
#res = 0
#res =
#LibSQLite3.step(sqlite_vm, out count, out column_data, out column_names)
#puts "step res:"
#puts res
LibSQLite3.close(db)
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment