SQLite3 for GLFW

Import sql

Class TestApp

	Method New()
	
		Local _db:SQLite3DataBase = New SQLite3DataBase()
		_db.Load("database.db")
		
		_db.Query("DROP TABLE IF EXISTS Books")
		_db.Query("CREATE TABLE Books(Id INTEGER PRIMARY KEY AUTOINCREMENT, Title TEXT, Author TEXT, ISBN TEXT DEFAULT 'not available')")
		_db.Query("INSERT INTO Books(Id, Title, Author, ISBN)VALUES(1, 'War and Peace', 'Leo Tolstoy', '978-0345472403')")
		_db.Query("INSERT INTO Books(Id, Title, Author, ISBN)VALUES(2, 'diesdas', 'Leo Tolstoy', '978-0345472403')")
		_db.Query("INSERT INTO Books(Id, Title, Author, ISBN)VALUES(3, 'blibla', 'Leo Tolstoy', '978-23423')")
		_db.Query("INSERT INTO Books(Id, Title, Author, ISBN)VALUES(4, 'blubla', 'Leo Tolstoy', '978-2342342342')")
		
		Local result:= _db.Exec("SELECT * FROM BOOKS")
		While result.Read()
			For Local e:= Eachin result.StringMap
			      Print e.Key + "/" + e.Value
			End 
		Wend 

	End

End

Function Main()
	New TestApp
End

Local statement:= _db.CreateStatement("SELECT * FROM Books WHERE Id > ?")
statement.BindInt(1,3)
		
While statement.ExecuteStep()
	Print statement.GetText(0) + " / " + statement.GetText(1)
End 
	
	
statement = _db.CreateStatement("SELECT * FROM Books WHERE Id > @NNN")
statement.BindInt("NNN",0)
		
While statement.ExecuteStep()
	Print statement.GetText(0) + " / " + statement.GetText(1)
End 

Try
	db.BeginTransaction()
			
	db.Exec("INSERT INTO test VALUES (NULL, 'test')")
			
	db.EndTransaction()
			
Catch e:SQLite3Exception
	db.RollBackTransaction()
End

Method LastInsertId:Int()
	Local res := Exec("SELECT last_insert_rowid() AS InsertID ")
	res.Read()
	Return Int(res.StringMap.Get("InsertID"))
End

#include <string>

String GetText(int index)
	{
		const char* text = (const char*)sqlite3_column_text(stmt, index);
	    return String(text, strlen(text));
	}

Author: Leo Tolstoy
ISBN: 978-0345472403
Id: 1
Title: War and Peace
Author: Leo Tolstoy
ISBN: 978-0345472403
Id: 2
Title: diesdas
Author: Leo Tolstoy
ISBN: 978-23423
Id: 3
Title: blibla
Author: Leo Tolstoy
ISBN: 978-2342342342
Id: 4
Title: blubla

ID
Title
Author
ISBN

Import sql

Class TestApp

	Method New()
	
		Local _db:SQLite3DataBase = New SQLite3DataBase()
		_db.Load("database.db")
		
		_db.Query("DROP TABLE IF EXISTS Books")
		_db.Query("CREATE TABLE Books(Id INTEGER PRIMARY KEY AUTOINCREMENT, Dog INTEGER, Cat TEXT, Title TEXT, Author TEXT, Isbn TEXT DEFAULT 'not available')")
		_db.Query("INSERT INTO Books(Id, Dog, Cat, Title, Author, Isbn)VALUES(1, 5, 'cat', 'War and Peace', 'Leo Tolstoy', '978-0345472403')")
		
		Print "Alphabetical order"
		Print "----------"
		Local result:= _db.Exec("SELECT * FROM BOOKS")
		While result.Read()
			For Local e:= Eachin result.StringMap
			      Print e.Key + "/" + e.Value
			End 
		Wend 
		
		Print "--------------------------------"
		Print "Alphabetical order"
		Print "----------"
		result= _db.Exec("SELECT Id, Dog, Cat, Title, Author, Isbn FROM BOOKS")
		While result.Read()
			For Local e:= Eachin result.StringMap
			      Print e.Key + "/" + e.Value
			End 
		Wend 
		
		Print "--------------------------------"
		Print "Uppercase S goes before lowercase d"
		Print "----------"
		result= _db.Exec("SELECT Id, Dog, Cat, Title, Author, Isbn as ISBN FROM BOOKS")
		While result.Read()
			For Local e:= Eachin result.StringMap
			      Print e.Key + "/" + e.Value
			End 
		Wend 

	End

End

Function Main()
	New TestApp
End

Class SQLEntry
	Field Key:String
	Field Value:String
	Method New(key:String, value:String)
		Key = key
		Value = value
	End 
End 

Class SQLite3Column

	Field items:StringMap<String>
	Field arr:SQLEntry[]
	
	Method New(name_arr:String[], value_array:String[])
		items = New StringMap<String>
		Local length = name_arr.Length
		For Local i = 0 Until length
			items.Set(name_arr[i], value_array[i])
		End 

		arr = New SQLEntry[length]
		For Local i = 0 Until length
			arr[i] = New SQLEntry(name_arr[i], value_array[i])
		End 
	End 
	
End 

Class SQLite3Result

	Field columns:= New Stack<SQLite3Column>
	Field length:Int = 0
	Field index:Int = 0
	
	Method Add(name_arr:String[], value_array:String[])
		columns.Push(New SQLite3Column(name_arr,value_array))
		length+=1
	End 
	
	Method Read?()
		index+=1
		Return length - index >= 0
	End 
	
	Method Reset()
		index = 0
	End 
	
	Method GetString$(_index)
		Return columns.Get(index-1).arr[_index].Value
	End 
	
	Method GetString$(name$)
		Return columns.Get(index-1).items.Get(name)
	End 
	
	Method StringMap:SQLEntry[]() 
		Return columns.Get(index-1).arr
	End 
	
	Method Columns%()
		Return length
	End 
	
End