Sqlite.obx

module Sqlite

import SqliteLib

const
  OPEN_READONLY* 	= SqliteLib.OPEN_READONLY
  OPEN_READWRITE*	= SqliteLib.OPEN_READWRITE
  OPEN_CREATE* 		= SqliteLib.OPEN_CREATE
  OPEN_URI* 			= SqliteLib.OPEN_URI
  OPEN_MEMORY* 		= SqliteLib.OPEN_MEMORY
  TINTEGER* 			= SqliteLib.SQLITE_INTEGER
  TFLOAT*  				= SqliteLib.SQLITE_FLOAT
  TTEXT*  				= SqliteLib.SQLITE_TEXT
  TBLOB*  				= SqliteLib.SQLITE_BLOB
  TNULL*  				= SqliteLib.SQLITE_NULL
  DONE*						= SqliteLib.DONE
  
type
  String* = array of char
  PString* = pointer to String
  
  TError* = record
  	code : integer
  end
  Error* = pointer to TError
  
  TDB* = record
  	db: *SqliteLib.DB
  end
  Db* = pointer to TDB
  
  TStmt* = record
  	db: Db
  	stmt: *SqliteLib.DBStmt
  end
  Stmt* = pointer to TStmt
  
proc strcpy(src: *[]char): PString
var
  ret : PString
  i : integer
begin
  if (src # nil) then 
    new(ret, strlen(src) + 1)
    i := 0
    while src[i] # 0x do
      ret[i] := src[i]
      inc(i)
    end
    ret[i] := 0x
  end
  return ret
end strcpy

proc (this: Error) str*(): PString
begin
  return strcpy(SqliteLib.errstr(this.code))
end str

proc (this: Db) openext*(in filename: String; opts: integer)
var
  ret: integer
  e: Error
  db: carray 1 of *SqliteLib.DB
begin
	ret := SqliteLib.open_v2(filename, db, opts, nil)
	this.db := db[0]
    if  ret # SqliteLib.OK then
    	new(e)
    	e.code := ret
    	raise(e)
    end
end openext

proc (this: Db) libversion*(): PString
begin
  return strcpy(SqliteLib.libversion())
end libversion

proc (this: Db) tryopen*(in filename: String): boolean
var
  ret: integer
  db: carray 1 of *SqliteLib.DB
begin
	ret := SqliteLib.open(filename, db)
	this.db := db[0]
	return ret = SqliteLib.OK
end tryopen

proc (this: Db) tryopenext*(in filename: String; opts: integer): boolean
var
  ret: integer
  db: carray 1 of *SqliteLib.DB
begin
	ret := SqliteLib.open_v2(filename, db, opts, nil)
	this.db := db[0]
    return ret = SqliteLib.OK
end tryopenext

proc (this: Db) close*()
begin
    if this.db # nil then
    	SqliteLib.close(this.db)
    	this.db := nil
    end		
end close

proc (this: Db) errmsg*(): PString
begin
  return strcpy(SqliteLib.errmsg(this.db))
end errmsg

proc (this: Db) execute*(in sql: String)
var
  ret: integer
  e: Error
begin
	ret := SqliteLib.exec(this.db, sql, nil, nil,nil)
    if  ret # SqliteLib.OK then
    	new(e)
    	e.code := ret
    	raise(e)
    end
end execute

proc (this: Db) tryexecute*(in sql: String): boolean
var
  ret: integer
begin
	ret := SqliteLib.exec(this.db, sql, nil, nil,nil)
	return ret = SqliteLib.OK
end tryexecute

proc (this: Db) prepare*(in sql: String): Stmt
var
  res: integer
  e: Error
  stmt: carray 1 of *SqliteLib.DBStmt
  ret: Stmt
begin
  res := SqliteLib.prepare_v2(this.db, sql,  -1,  stmt, nil)
  if  res # SqliteLib.OK then
    new(e)
    e.code := res
    raise(e)
  end
  new(ret)
  ret.db := this
  ret.stmt := stmt[0]
  return ret
end prepare

proc (this: Db) tryprepare*(in sql: String): Stmt
var
  stmt: carray 1 of *SqliteLib.DBStmt
  ret: Stmt
begin
  if SqliteLib.prepare_v2(this.db, sql,  -1,  stmt, nil)  = SqliteLib.OK then
    new(ret)
    ret.db := this
    ret.stmt := stmt[0]
  end
  return ret
end tryprepare

proc (this: Stmt) bind_longreal*(col: integer; value: longreal)
var
  res: integer
  e: Error
begin
  res := SqliteLib.bind_double(this.stmt, col, value)
  if  res # SqliteLib.OK then
    new(e)
    e.code := res
    raise(e)
  end
end bind_longreal

proc (this: Stmt) trybind_longreal*(col: integer; value: longreal): boolean
var
  res: integer
begin
  res := SqliteLib.bind_double(this.stmt, col, value)
  return res = SqliteLib.OK
end trybind_longreal

proc (this: Stmt) bind_integer*(col: integer; value: integer)
var
  res: integer
  e: Error
begin
  res := SqliteLib.bind_int(this.stmt, col, value)
  if  res # SqliteLib.OK then
    new(e)
    e.code := res
    raise(e)
  end
end bind_integer

proc (this: Stmt) trybind_integer*(col: integer; value: integer): boolean
var
  res: integer
begin
  res := SqliteLib.bind_int(this.stmt, col, value)
  return res = SqliteLib.OK
end trybind_integer

proc (this: Stmt) bind_longint*(col: integer; value: longint)
var
  res: integer
  e: Error
begin
  res := SqliteLib.bind_int64(this.stmt, col, value)
  if  res # SqliteLib.OK then
    new(e)
    e.code := res
    raise(e)
  end
end bind_longint

proc (this: Stmt) trybind_longint*(col: integer; value: longint): boolean
var
  res: integer
begin
  res := SqliteLib.bind_int64(this.stmt, col, value)
  return res = SqliteLib.OK
end trybind_longint

proc (this: Stmt) bind_text*(col: integer; in value: String)
var
  res: integer
  e: Error
begin
  res := SqliteLib.bind_text(this.stmt, col, value, -1, nil)
  if  res # SqliteLib.OK then
    new(e)
    e.code := res
    raise(e)
  end
end bind_text

proc (this: Stmt) trybind_text*(col: integer; in value: String): boolean
var
  res: integer
begin
  res := SqliteLib.bind_text(this.stmt, col, value, -1, nil)
  return res = SqliteLib.OK
end trybind_text

proc (this: Stmt) bind_null*(col: integer)
var
  res: integer
  e: Error
begin
  res := SqliteLib.bind_null(this.stmt, col)
  if  res # SqliteLib.OK then
    new(e)
    e.code := res
    raise(e)
  end
end bind_null

proc (this: Stmt) trybind_null*(col: integer): boolean
var
  res: integer
begin
  res := SqliteLib.bind_null(this.stmt, col)
  return res = SqliteLib.OK
end trybind_null

proc (this: Stmt) step*(): integer
var
  ret: integer
  e: Error
begin
  ret := SqliteLib.step(this.stmt)
  if (ret # SqliteLib.DONE) & (ret # SqliteLib.ROW) then
    new(e)
    e.code := ret
    raise(e)
  end
  return ret
end step

proc (this: Stmt) trystep*(): integer
begin
  return SqliteLib.step(this.stmt)
end trystep

proc (this: Stmt) finalize*(): integer
begin
  return SqliteLib.finalize(this.stmt)
end finalize

proc (this: Stmt) reset*(): integer
begin
  return SqliteLib.reset(this.stmt)
end reset

proc (this: Stmt) column_count*(): integer
begin
  return SqliteLib.column_count(this.stmt)
end column_count

proc (this: Stmt) data_count*(): integer
begin
  return SqliteLib.data_count(this.stmt)
end data_count

proc (this: Stmt) column_type*(col: integer): integer
begin
  return SqliteLib.column_type(this.stmt, col)
end column_type

proc (this: Stmt) column_integer*(col: integer): integer
begin
  return SqliteLib.column_int(this.stmt, col)
end column_integer

proc (this: Stmt) column_longint*(col: integer): longint
begin
  return SqliteLib.column_int64(this.stmt, col)
end column_longint

proc (this: Stmt) column_longreal*(col: integer): longreal
begin
  return SqliteLib.column_double(this.stmt, col)
end column_longreal

proc (this: Stmt) column_text*(col: integer): PString
begin
  return strcpy(SqliteLib.column_text(this.stmt, col))
end column_text

end Sqlite

SqliteLib.obx

definition SqliteLib [extern 'C', dll 'libsqlite3-0']
	type
	    DB = cstruct end
      DBStmt = cstruct end
      
    // Error codes  
    const
    OK 			=  0 	// Successful result
    ERROR 		=  1	// Generic error
		INTERNAL    =  2    // Internal logic error in SQLite
		PERM        =  3    // Access permission denied
		ABORT       =  4    // Callback routine requested an abort
		BUSY        =  5    // The database file is locked
		LOCKED      =  6    // A table in the database is locked
		NOMEM       =  7    // A malloc() failed
		READONLY    =  8    // Attempt to write a readonly database
		INTERRUPT   =  9    // Operation terminated by sqlite3_interrupt()
		IOERR       = 10    // Some kind of disk I/O error occurred
		CORRUPT     = 11    // The database disk image is malformed
		NOTFOUND    = 12    // Unknown opcode in sqlite3_file_control()
		FULL        = 13    // Insertion failed because database is full
		CANTOPEN    = 14    // Unable to open the database file
		PROTOCOL    = 15    // Database lock protocol error
		EMPTY       = 16    // Internal use only
		SCHEMA      = 17    // The database schema changed
		TOOBIG      = 18    // String or BLOB exceeds size limit
		CONSTRAINT  = 19    // Abort due to constraint violation
		MISMATCH    = 20    // Data type mismatch
		MISUSE      = 21    // Library used incorrectly
		NOLFS       = 22    // Uses OS features not supported on host
		AUTH        = 23    // Authorization denied
		FORMAT      = 24    // Not used
		RANGE       = 25    // 2nd parameter to sqlite3_bind out of range
		NOTADB      = 26    // File opened that is not a database file
		NOTICE      = 27    // Notifications from sqlite3_log()
		WARNING     = 28    // Warnings from sqlite3_log()
		ROW         = 100   // sqlite3_step() has another row ready
		DONE        = 101	// sqlite3_step() has finished executing
	
	// Open flags
	const
		OPEN_READONLY         = 00000001h  // Ok for sqlite3_open_v2()
		OPEN_READWRITE        = 00000002h  // Ok for sqlite3_open_v2()
		OPEN_CREATE           = 00000004h  // Ok for sqlite3_open_v2()
		OPEN_DELETEONCLOSE    = 00000008h  // VFS only
		OPEN_EXCLUSIVE        = 00000010h  // VFS only
		OPEN_AUTOPROXY        = 00000020h  // VFS only
		OPEN_URI              = 00000040h  // Ok for sqlite3_open_v2()
		OPEN_MEMORY           = 00000080h  // Ok for sqlite3_open_v2()
		OPEN_MAIN_DB          = 00000100h  // VFS only
		OPEN_TEMP_DB          = 00000200h  // VFS only
		OPEN_TRANSIENT_DB     = 00000400h  // VFS only
		OPEN_MAIN_JOURNAL     = 00000800h  // VFS only
		OPEN_TEMP_JOURNAL     = 00001000h  // VFS only
		OPEN_SUBJOURNAL       = 00002000h  // VFS only
		OPEN_SUPER_JOURNAL    = 00004000h  // VFS only
		OPEN_NOMUTEX          = 00008000h  // Ok for sqlite3_open_v2()
		OPEN_FULLMUTEX        = 00010000h  // Ok for sqlite3_open_v2()
		OPEN_SHAREDCACHE      = 00020000h  // Ok for sqlite3_open_v2()
		OPEN_PRIVATECACHE     = 00040000h  // Ok for sqlite3_open_v2()
		OPEN_WAL              = 00080000h  // VFS only
		OPEN_NOFOLLOW         = 01000000h  // Ok for sqlite3_open_v2()
		OPEN_EXRESCODE        = 02000000h  // Extended result codes
  
  // types
  const
    SQLITE_INTEGER  	= 1
    SQLITE_FLOAT    	= 2
    SQLITE_TEXT     	= 3
    SQLITE_BLOB     	= 4
    SQLITE_NULL     	= 5
  
	proc libversion_number(): integer [alias 'sqlite3_libversion_number']
	proc libversion(): *[]char [alias 'sqlite3_libversion']
	proc errmsg(db: *DB): *[]char [alias 'sqlite3_errmsg']
	proc errstr(err: integer): *[]char [alias 'sqlite3_errstr']
	proc open(filename: *[]char; dbref: *[]*DB): integer [alias 'sqlite3_open']
	proc open_v2(filename: *[]char; dbref: *[]*DB; flags: integer; zVfs: *[]char): integer [alias 'sqlite3_open_v2']
	proc exec(db: *DB; sql: *[]char; callback, arg, errmsg: *void): integer [alias 'sqlite3_exec']
	proc close(db: *DB) [alias 'sqlite3_close']
	proc close_v2(db: *DB) [alias 'sqlite3_close_v2']
	proc prepare_v2(db: *DB; sql: *[]char; nbyte: integer; stmtref: *[]*DBStmt; tail: *[]*[]char): integer [alias 'sqlite3_prepare_v2']
  proc bind_double(stmt: *DBStmt; col: integer; value: longreal): integer [alias 'sqlite3_bind_double']
  proc bind_int(stmt: *DBStmt; col: integer; value: integer): integer [alias 'sqlite3_bind_int']
  proc bind_int64(stmt: *DBStmt; col: integer; value: longint): integer [alias 'sqlite3_bind_int64']
  proc bind_text(stmt: *DBStmt; col: integer; value: *[]char; nbyte: integer; ptr: *void): integer [alias 'sqlite3_bind_text']
  proc bind_null(stmt: *DBStmt; col: integer): integer [alias 'sqlite3_bind_null']
  proc step(stmt: *DBStmt): integer [alias 'sqlite3_step']
  proc finalize(stmt: *DBStmt): integer [alias 'sqlite3_reset']
  proc reset(stmt: *DBStmt): integer [alias 'sqlite3_finalize']
  proc column_count(stmt: *DBStmt): integer [alias 'sqlite3_column_count']
  proc data_count(stmt: *DBStmt): integer [alias 'sqlite3_data_count']
  proc column_type(stmt: *DBStmt; col: integer): integer [alias 'sqlite3_column_type']
  proc column_double(stmt: *DBStmt; col: integer): longreal [alias 'sqlite3_column_double']
  proc column_int(stmt: *DBStmt; col: integer): integer [alias 'sqlite3_column_int']
  proc column_int64(stmt: *DBStmt; col: integer): longint [alias 'sqlite3_column_int64']
  proc column_text(stmt: *DBStmt; col: integer): *[]char [alias 'sqlite3_column_text']
end SqliteLib

Test.obx

module Test

import Sqlite

proc testdb(db: Sqlite.Db)
const
	sql = "DROP TABLE IF EXISTS Cars;" +
   			"CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" +
    		"INSERT INTO Cars VALUES(1, 'Audi', 52642);" +
      	"INSERT INTO Cars VALUES(2, 'Mercedes', 57127);" +
        "INSERT INTO Cars VALUES(3, 'Skoda', 9000);" +
        "INSERT INTO Cars VALUES(4, 'Volvo', 29000);" +
        "INSERT INTO Cars VALUES(5, 'Bentley', 350000);" +
        "INSERT INTO Cars VALUES(6, 'Citroen', 21000);" +
        "INSERT INTO Cars VALUES(7, 'Hummer', 41400);" +
        "INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);"
begin
	db.execute(sql)
end testdb

proc teststmt(db: Sqlite.Db)
var
  ret: integer
  stmt: Sqlite.Stmt
begin
  stmt := db.prepare("SELECT * from Cars WHERE Price > ?;")
  stmt.bind_longreal(1, 9000.)
  while stmt.trystep() # Sqlite.DONE do
    println(stmt.column_text(1)^)
  end
  stmt.finalize()
end teststmt

proc printerr(err: Sqlite.Error)
begin
  println(err.str()^)
  println(db.errmsg()^)
end printerr
 
var
	db: Sqlite.Db
	res: pointer to anyrec

begin
	println("Begin")
	new(db)
	if ~db.tryopen(":memory:") then
  	println("failed to open database")
  	raise()
	end
  pcall(res, testdb, db)
  case res of
  | Sqlite.TError: println(res.str()^) println(db.errmsg()^) raise()
	end
	pcall(res, teststmt, db)
	case res of
  | Sqlite.TError: println(res.str()^) println(db.errmsg()^) raise()
	end
	db.close()
	println("End")
end Test