Group=Default Group
ModulesStructureVersion=1
Type=StaticCode
Version=7.01
@EndOfDesignText@
'DBUtils
Sub Process_Globals
	Type DBFieldInfo(FieldName As String, DataType As String, _
						  CanBeNull As Boolean, DefValue As Object)
	Public DB_REAL, DB_INTEGER, DB_BLOB, DB_TEXT As String
	DB_REAL = "REAL"
	DB_INTEGER = "INTEGER"
	DB_BLOB = "BLOB"
	DB_TEXT = "TEXT"
	Public HtmlCSS As String = $"
		table {width: 100%;border: 1px solid #cef;text-align: left; }
		th { font-weight: bold;	background-color: #acf;	border-bottom: 1px solid #cef; }
		td,th {	padding: 4px 5px; }
		.odd {background-color: #def; } 
		.odd td {border-bottom: 1px solid #cef; }
		a { text-decoration:none; color: #000;}"$
End Sub

#if B4J
'Returns the path to a folder where you can create a database, preferably on the secondary storage.
Public Sub GetDBFolder (AppName As String) As String
	Return File.DirData(AppName)
End Sub

'Copies a database file that was added in the Files tab. The database must be copied to a writable location.
'This method copies the database to the storage card. If the storage card is not available the file is copied to the internal folder.
'The target folder is returned.
'If the database file already exists then no copying is done.
Public Sub CopyDBFromAssets (FileName As String, AppName As String) As String
	Dim TargetDir As String = GetDBFolder(AppName)
	If File.Exists(TargetDir, FileName) = False Then
		File.Copy(File.DirAssets, FileName, TargetDir, FileName)
	End If
	Return TargetDir
End Sub

#Else
'Returns the path to a folder where you can create a database, preferably on the secondary storage.
Public Sub GetDBFolder As String
#If B4A
	Dim rp As RuntimePermissions
	If File.ExternalWritable Then Return rp.GetSafeDirDefaultExternal("") Else Return File.DirInternal
#Else If B4i
	Return File.DirDocuments
#End If
End Sub

'Copies a database file that was added in the Files tab. The database must be copied to a writable location.
'This method copies the database to the storage card. If the storage card is not available the file is copied to the internal folder.
'The target folder is returned.
'If the database file already exists then no copying is done.
Public Sub CopyDBFromAssets (FileName As String) As String
	Dim TargetDir As String = GetDBFolder
	If File.Exists(TargetDir, FileName) = False Then
		File.Copy(File.DirAssets, FileName, TargetDir, FileName)
	End If
	Return TargetDir
End Sub

#End If

Private Sub EscapeField(f As String) As String
	Return "[" & f & "]"
End Sub

#If B4i
'Creates a new table with the given name.
'FieldsAndTypes - A map with the fields names as keys and the types as values.
'You can use the DB_... constants for the types.
'PrimaryKey - The column that will be the primary key. Pass empty string if not needed.
Public Sub CreateTable(SQL As SQL, TableName As String, Fields As List, Types As List, PrimaryKey As String)
	Dim sb As StringBuilder
	sb.Initialize
	sb.Append("(")
	For i = 0 To Fields.Size - 1
		If i > 0 Then sb.Append(", ")
		sb.Append("[").Append(Fields.Get(i)).Append("] ").Append(Types.Get(i))
		If Fields.Get(i) = PrimaryKey Then sb.Append(" PRIMARY KEY")
	Next
	sb.Append(")")
	Dim query As String
	query = "CREATE TABLE IF NOT EXISTS [" & TableName & "] " & sb.ToString
#IF Not(DBUTILS_NOLOGS)
	Log("CreateTable: " & query)
#End if
	SQL.ExecNonQuery(query)
End Sub
#Else
'Creates a new table with the given name.
'FieldsAndTypes - A map with the fields names as keys and the types as values.
'You can use the DB_... constants for the types.
'PrimaryKey - The column that will be the primary key. Pass empty string if not needed.
Public Sub CreateTable(SQL As SQL, TableName As String, FieldsAndTypes As Map, PrimaryKey As String)
	Dim sb As StringBuilder
	sb.Initialize
	sb.Append("(")
	For i = 0 To FieldsAndTypes.Size - 1
		Dim field, ftype As String
		field = FieldsAndTypes.GetKeyAt(i)
		ftype = FieldsAndTypes.GetValueAt(i)
		If i > 0 Then sb.Append(", ")
		sb.Append(EscapeField(field)).Append(" ").Append(ftype)
		If field = PrimaryKey Then sb.Append(" PRIMARY KEY")
	Next
	sb.Append(")")
	Dim query As String = "CREATE TABLE IF NOT EXISTS " & EscapeField(TableName) & " " & sb.ToString
#IF Not(DBUTILS_NOLOGS)
	Log("CreateTable: " & query)
#End If
	SQL.ExecNonQuery(query)
End Sub
#End If

'Deletes the given table.
Public Sub DropTable(SQL As SQL, TableName As String)
	Dim query As String = "DROP TABLE IF EXISTS" & EscapeField(TableName)
#IF Not(DBUTILS_NOLOGS)
	Log("DropTable: " & query)
#End If
	SQL.ExecNonQuery(query)
End Sub

'Inserts the data to the table.
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
'Returns True if the maps were inserted successfully.
Public Sub InsertMaps(SQL As SQL, TableName As String, ListOfMaps As List) As Boolean
	Dim sb, columns, values As StringBuilder
	Dim res As Boolean = False
	'Small check for a common error where the same map is used in a loop
	If ListOfMaps.Size > 1 And ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
		Log("Same Map found twice in list. Each item in the list should include a different map object.")
		Return res
	End If
	SQL.BeginTransaction
	Try
		For i1 = 0 To ListOfMaps.Size - 1
			sb.Initialize
			columns.Initialize
			values.Initialize
			Dim listOfValues As List
			listOfValues.Initialize
			sb.Append("INSERT INTO [" & TableName & "] (")
			Dim m As Map = ListOfMaps.Get(i1)
			For Each col As String In m.Keys
				Dim value As Object = m.Get(col)
				If listOfValues.Size > 0 Then
					columns.Append(", ")
					values.Append(", ")
				End If
				columns.Append(EscapeField(col))
				values.Append("?")
				listOfValues.Add(value)
			Next
			sb.Append(columns.ToString).Append(") VALUES (").Append(values.ToString).Append(")")
			#IF Not(DBUTILS_NOLOGS)
			If i1 = 0 Then Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
			#End If
			SQL.ExecNonQuery2(sb.ToString, listOfValues)
		Next
		SQL.TransactionSuccessful
		res = True
	Catch
		Log(LastException)
		#If B4i OR B4J
		SQL.Rollback
		#End If
	End Try
#If B4A
	SQL.EndTransaction
#End If
	Return res
End Sub

'Executes the query and returns the result as a list of arrays.
'Each item in the list is a strings array.
'StringArgs - Values to replace question marks in the query. Pass Null if not needed.
'Limit - Limits the results. Pass 0 for all results.
Public Sub ExecuteMemoryTable(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
	Dim cur As ResultSet
	If StringArgs = Null Then
		Dim StringArgs(0) As String
	End If
	cur = SQL.ExecQuery2(Query, StringArgs)
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteMemoryTable: " & Query)
#End If
	Dim table As List
	table.Initialize
	Do While cur.NextRow
		Dim values(cur.ColumnCount) As String
		For col = 0 To cur.ColumnCount - 1
			values(col) = cur.GetString2(col)
		Next
		table.Add(values)
		If Limit > 0 And table.Size >= Limit Then Exit
	Loop
	cur.Close
	Return table
End Sub

'Executes the query and returns a Map with the column names as the keys 
'and the first record values As the entries values.
'The keys are lower cased.
'Returns an uninitialized map if there are no results.
Public Sub ExecuteMap(SQL As SQL, Query As String, StringArgs() As String) As Map
	Dim res As Map
	Dim cur As ResultSet
	If StringArgs <> Null Then
		cur = SQL.ExecQuery2(Query, StringArgs)
	Else
		cur = SQL.ExecQuery(Query)
	End If
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteMap: " & Query)
#End If
	If cur.NextRow = False Then
#IF Not(DBUTILS_NOLOGS)
		Log("No records found.")
#End If
		Return res
	End If
	res.Initialize
	For i = 0 To cur.ColumnCount - 1
		res.Put(cur.GetColumnName(i).ToLowerCase, cur.GetString2(i))
	Next
	cur.Close
	Return res
End Sub

' ============================================================
' 🔥 TAMBAHAN 1: ExecuteMap2 (Return Map dengan Field Name Asli)
' ============================================================

' Executes the query and returns a Map with the column names as the keys 
' and the first record values as the entries values.
' 🔥 BEDA: Field names TIDAK di-lowercase (kaya Delphi!)
' Returns an uninitialized map if there are no results.
Public Sub ExecuteMap2(SQL As SQL, Query As String, StringArgs() As String) As Map
	Dim res As Map
	Dim cur As ResultSet
	If StringArgs <> Null Then
		cur = SQL.ExecQuery2(Query, StringArgs)
	Else
		cur = SQL.ExecQuery(Query)
	End If
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteMap2: " & Query)
#End If
	If cur.NextRow = False Then
#IF Not(DBUTILS_NOLOGS)
		Log("No records found.")
#End If
		Return res
	End If
	res.Initialize
	For i = 0 To cur.ColumnCount - 1
		' 🔥 BEDA: GA pake ToLowerCase!
		res.Put(cur.GetColumnName(i), cur.GetString2(i))
	Next
	cur.Close
	Return res
End Sub

' ============================================================
' 🔥 TAMBAHAN 2: ExecuteMapList (Return List of Maps)
' ============================================================

' Executes the query and returns a List of Maps.
' Each Map has column names as keys and values as entries.
' 🔥 KAYAK DELPHI! Field names tetap asli (ga di-lowercase)
Public Sub ExecuteMapList(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
	Dim res As List
	res.Initialize
	
	Dim cur As ResultSet
	If StringArgs <> Null Then
		cur = SQL.ExecQuery2(Query, StringArgs)
	Else
		cur = SQL.ExecQuery(Query)
	End If
	
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteMapList: " & Query)
#End If
	
	Dim rowCount As Int = 0
	Do While cur.NextRow
		Dim m As Map
		m.Initialize
		For i = 0 To cur.ColumnCount - 1
			' 🔥 Field names TIDAK di-lowercase!
			m.Put(cur.GetColumnName(i), cur.GetString2(i))
		Next
		res.Add(m)
		rowCount = rowCount + 1
		If Limit > 0 And rowCount >= Limit Then Exit
	Loop
	cur.Close
	Return res
End Sub

' ============================================================
' 🔥 TAMBAHAN 3: ExecuteMapListLowerCase (Field Name Lowercase)
' ============================================================

' Executes the query and returns a List of Maps.
' 🔥 Field names di-lowercase (buat yang suka case-insensitive)
Public Sub ExecuteMapListLC(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
	Dim res As List
	res.Initialize
	
	Dim cur As ResultSet
	If StringArgs <> Null Then
		cur = SQL.ExecQuery2(Query, StringArgs)
	Else
		cur = SQL.ExecQuery(Query)
	End If
	
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteMapListLC: " & Query)
#End If
	
	Dim rowCount As Int = 0
	Do While cur.NextRow
		Dim m As Map
		m.Initialize
		For i = 0 To cur.ColumnCount - 1
			m.Put(cur.GetColumnName(i).ToLowerCase, cur.GetString2(i))
		Next
		res.Add(m)
		rowCount = rowCount + 1
		If Limit > 0 And rowCount >= Limit Then Exit
	Loop
	cur.Close
	Return res
End Sub

' ============================================================
' 🔥 TAMBAHAN 4: ExecuteMapListTyped (Dengan Tipe Data)
' ============================================================

' Executes the query and returns a List of Maps with proper data types.
' DBTypes - List of types for each column (DB_TEXT, DB_INTEGER, DB_REAL)
Public Sub ExecuteMapListTyped(SQL As SQL, Query As String, StringArgs() As String, Limit As Int, DBTypes As List) As List
	Dim res As List
	res.Initialize
	
	Dim cur As ResultSet
	If StringArgs <> Null Then
		cur = SQL.ExecQuery2(Query, StringArgs)
	Else
		cur = SQL.ExecQuery(Query)
	End If
	
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteMapListTyped: " & Query)
#End If
	
	Dim rowCount As Int = 0
	Do While cur.NextRow
		Dim m As Map
		m.Initialize
		For i = 0 To cur.ColumnCount - 1
			Dim fieldName As String = cur.GetColumnName(i)
			Select DBTypes.Get(i)
				Case DB_TEXT
					m.Put(fieldName, cur.GetString2(i))
				Case DB_INTEGER
					m.Put(fieldName, cur.GetLong2(i))
				Case DB_REAL
					m.Put(fieldName, cur.GetDouble2(i))
				Case Else
					m.Put(fieldName, cur.GetString2(i))
			End Select
		Next
		res.Add(m)
		rowCount = rowCount + 1
		If Limit > 0 And rowCount >= Limit Then Exit
	Loop
	cur.Close
	Return res
End Sub

' ============================================================
' 🔥 TAMBAHAN 5: ExecuteSingleValue (Ambil Satu Nilai)
' ============================================================

' Executes query and returns single value from first row, first column
Public Sub ExecuteSingleValue(SQL As SQL, Query As String, StringArgs() As String) As Object
	Dim cur As ResultSet
	If StringArgs <> Null Then
		cur = SQL.ExecQuery2(Query, StringArgs)
	Else
		cur = SQL.ExecQuery(Query)
	End If
	
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteSingleValue: " & Query)
#End If
	
	If cur.NextRow Then
		Dim result As Object = cur.GetString2(0)
		cur.Close
		Return result
	End If
	cur.Close
	Return Null
End Sub

' ============================================================
' 🔥 TAMBAHAN 6: ExecuteExists (Cek Apakah Data Ada)
' ============================================================

' Returns True if the query returns at least one row
Public Sub ExecuteExists(SQL As SQL, Query As String, StringArgs() As String) As Boolean
	Dim cur As ResultSet
	If StringArgs <> Null Then
		cur = SQL.ExecQuery2(Query, StringArgs)
	Else
		cur = SQL.ExecQuery(Query)
	End If
	
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteExists: " & Query)
#End If
	
	Dim exists As Boolean = cur.NextRow
	cur.Close
	Return exists
End Sub

' ============================================================
' 🔥 TAMBAHAN 7: ExecNonQuery2 (Alias)
' ============================================================

' Alias untuk ExecNonQuery (biar konsisten)
Public Sub ExecNonQuery2(SQL As SQL, Query As String, StringArgs() As Object)
	SQL.ExecNonQuery2(Query, StringArgs)
End Sub

' ============================================================
' 🔥 TAMBAHAN 8: InsertMap (Single Record)
' ============================================================

' Insert a single map as a record
Public Sub InsertMap(SQL As SQL, TableName As String, Data As Map) As Boolean
	Dim list As List
	list.Initialize
	list.Add(Data)
	Return InsertMaps(SQL, TableName, list)
End Sub

' ============================================================
' 🔥 TAMBAHAN 9: UpdateRecord3 (Dengan Where Fields)
' ============================================================

' Update record with where fields as array
Public Sub UpdateRecord3(SQL As SQL, TableName As String, Fields As Map, WhereFields As Map) As Boolean
	Try
		UpdateRecord2(SQL, TableName, Fields, WhereFields)
		Return True
	Catch
		Log("UpdateRecord3 Error: " & LastException.Message)
		Return False
	End Try
End Sub

' ============================================================
' 🔥 TAMBAHAN 10: GetRowCount (Jumlah Record)
' ============================================================

' Returns number of rows in a table
Public Sub GetRowCount(SQL As SQL, TableName As String) As Int
	Dim count As Int = SQL.ExecQuerySingleResult("SELECT COUNT(*) FROM " & EscapeField(TableName))
	Return count
End Sub

' ============================================================
' 🔥 TAMBAHAN 11: GetLastInsertRowId
' ============================================================

' Returns the rowid of the last inserted row
Public Sub GetLastInsertRowId(SQL As SQL) As Long
	Return SQL.ExecQuerySingleResult("SELECT last_insert_rowid()")
End Sub

' ============================================================
' 🔥 TAMBAHAN 12: BeginTransaction / Commit / Rollback
' ============================================================

Public Sub BeginTransaction(SQL As SQL)
	SQL.BeginTransaction
End Sub

Public Sub Commit(SQL As SQL)
	SQL.TransactionSuccessful
	SQL.EndTransaction
End Sub

Public Sub Rollback(SQL As SQL)
	#If B4i OR B4J
	SQL.Rollback
	#Else
	SQL.EndTransaction
	#End If
End Sub

#if B4A
'Executes the query and fills the Spinner with the values in the first column
Sub ExecuteSpinner(SQL As SQL, Query As String, StringArgs() As String, Limit As Int, Spinner1 As Spinner)
	Spinner1.Clear
	Dim Table As List
	Table = ExecuteMemoryTable(SQL, Query, StringArgs, Limit)
	   
	Dim Cols() As String
	For i = 0 To Table.Size - 1
		Cols = Table.Get(i)
		Spinner1.Add(Cols(0))
	Next
End Sub

'Executes the query and fills the ListView with the value.
'If TwoLines is true then the first column is mapped to the first line and the second column is mapped
'to the second line.
'In both cases the value set to the row is the array with all the records values.
Public Sub ExecuteListView(SQL As SQL, Query As String, StringArgs() As String, Limit As Int, ListView1 As ListView, _
	TwoLines As Boolean)
	ListView1.Clear
	Dim Table As List
	Table = ExecuteMemoryTable(SQL, Query, StringArgs, Limit)
	Dim Cols() As String
	For i = 0 To Table.Size - 1
		Cols = Table.Get(i)
		If TwoLines Then
			ListView1.AddTwoLines2(Cols(0), Cols(1), Cols)
		Else
			ListView1.AddSingleLine2(Cols(0), Cols)
		End If
	Next
End Sub

#Else If B4J AND UI
Public Sub ExecuteTableView(SQL As SQL, Query As String, StringArgs() As String, Limit As Int, _
	TableView1 As TableView)
	TableView1.Items.Clear
	Dim cur As ResultSet
	If StringArgs = Null Then
		Dim StringArgs(0) As String
	End If
	cur = SQL.ExecQuery2(Query, StringArgs)
	Dim cols As List
	cols.Initialize
	For i = 0 To cur.ColumnCount - 1
		cols.Add(cur.GetColumnName(i))
	Next
	TableView1.SetColumns(cols)
	Do While cur.NextRow
		Dim values(cur.ColumnCount) As String
		For col = 0 To cur.ColumnCount - 1
			values(col) = cur.GetString2(col)
		Next
		TableView1.Items.Add(values)
		If Limit > 0 And TableView1.Items.Size >= Limit Then Exit
	Loop
	cur.Close
End Sub
#End If

'Executes the query and fills the given list with the values in the first column.
Public Sub ExecuteList2(SQL As SQL, Query As String, StringArgs() As String, Limit As Int, List As List)
	List.Clear
	List.AddAll(ExecuteList(SQL, Query, StringArgs, Limit))
End Sub
'Executes the query and returns a list with the values in the first column.
Public Sub ExecuteList(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
	Dim Table As List = ExecuteMemoryTable(SQL, Query, StringArgs, Limit)
	Dim res As List
	res.Initialize
	For Each Cols() As String In Table
		res.Add(Cols(0))
	Next
	Return res
End Sub

'Executes the given query and creates a Map that you can pass to JSONGenerator and generate JSON text.
'DBTypes - Lists the type of each column in the result set.
'Usage example: (don't forget to add a reference to the JSON library)
'<code>
'Dim gen As JSONGenerator
'gen.Initialize(DBUtils.ExecuteJSON(SQL, "SELECT Id, Birthday FROM Students", Null, _
'	0, Array As String(DBUtils.DB_TEXT, DBUtils.DB_INTEGER)))
'Dim JSONString As String
'JSONString = gen.ToPrettyString(4)
'Log(JSONString)</code>
Public Sub ExecuteJSON (SQL As SQL, Query As String, StringArgs() As String, Limit As Int, DBTypes As List) As Map
	Dim table As List
	Dim cur As ResultSet
	If StringArgs <> Null Then
		cur = SQL.ExecQuery2(Query, StringArgs)
	Else
		cur = SQL.ExecQuery(Query)
	End If
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteJSON: " & Query)
#End If
	Dim table As List
	table.Initialize
	Do While cur.NextRow
		Dim m As Map
		m.Initialize
		For i = 0 To cur.ColumnCount - 1
			Select DBTypes.Get(i)
				Case DB_TEXT
					m.Put(cur.GetColumnName(i), cur.GetString2(i))
				Case DB_INTEGER
					m.Put(cur.GetColumnName(i), cur.GetLong2(i))
				Case DB_REAL
					m.Put(cur.GetColumnName(i), cur.GetDouble2(i))
				Case Else
					Log("Invalid type: " & DBTypes.Get(i))
			End Select
		Next
		table.Add(m)
		If Limit > 0 And table.Size >= Limit Then Exit
	Loop
	cur.Close
	Dim root As Map
	root.Initialize
	root.Put("root", table)
	Return root
End Sub

'Creates a html text that displays the data in a table.
'The style of the table can be changed by modifying HtmlCSS variable.
Public Sub ExecuteHtml(SQL As SQL, Query As String, StringArgs() As String, Limit As Int, Clickable As Boolean) As String
	Dim cur As ResultSet
	If StringArgs <> Null Then
		cur = SQL.ExecQuery2(Query, StringArgs)
	Else
		cur = SQL.ExecQuery(Query)
	End If
#IF Not(DBUTILS_NOLOGS)
	Log("ExecuteHtml: " & Query)
#End If
																														 
	Dim sb As StringBuilder
	sb.Initialize
	sb.Append("<html><body>").Append(CRLF)
	sb.Append("<style type='text/css'>").Append(HtmlCSS).Append("</style>").Append(CRLF)
	sb.Append("<table><thead><tr>").Append(CRLF)
	For i = 0 To cur.ColumnCount - 1
		sb.Append("<th>").Append(cur.GetColumnName(i)).Append("</th>")
	Next
	sb.Append("</thead>")
	
	'	For i = 0 To cur.ColumnCount - 1
	'		If i = 1 Then
	'			sb.Append("<th style='width:200px;'>").Append(cur.GetColumnName(i)).Append("</th>")
	'		Else
	'			sb.Append("<th>").Append(cur.GetColumnName(i)).Append("</th>")
	'		End If
	'	Next
		
	sb.Append("</tr>").Append(CRLF)
	Dim row As Int
	Do While cur.NextRow
		If row Mod 2 = 0 Then
			sb.Append("<tr>")
		Else
			sb.Append("<tr class='odd'>")
		End If
		For i = 0 To cur.ColumnCount - 1
			sb.Append("<td>")
			If Clickable Then
				sb.Append("<a href='http://").Append(i).Append(".")
				sb.Append(row)
				sb.Append(".stub'>").Append(cur.GetString2(i)).Append("</a>")
			Else
				sb.Append(cur.GetString2(i))
			End If
			sb.Append("</td>")
		Next
		sb.Append("</tr>").Append(CRLF)
		row = row + 1
	Loop
	cur.Close
	sb.Append("</table></body></html>")
	Return sb.ToString
End Sub

'Gets the current version of the database. If the DBVersion table does not exist it is created and the current
'version is set to version 1.
Public Sub GetDBVersion (SQL As SQL) As Int
	Dim version As Int
	If TableExists(SQL, "DBVersion") Then
		version = SQL.ExecQuerySingleResult("SELECT version FROM DBVersion")
	Else
		'Create the versions table.
		#if B4i
			CreateTable(SQL, "DBVersion", Array("version"), Array(DB_INTEGER), "version")		
		#Else
		Dim m As Map = CreateMap("version": DB_INTEGER)
		CreateTable(SQL, "DBVersion", m, "version")
		#End If
		SQL.ExecNonQuery("INSERT INTO DBVersion VALUES (1)")
		version = 1
	End If
	Return version
End Sub

'Sets the database version to the given version number.
Public Sub SetDBVersion (SQL As SQL, Version As Int)
	SQL.ExecNonQuery2("UPDATE DBVersion set version = ?", Array As Object(Version))
End Sub

' updates a single field in a record
' Field is the column name
Public Sub UpdateRecord(SQL As SQL, TableName As String, Field As String, NewValue As Object, _
		WhereFieldEquals As Map)
	UpdateRecord2(SQL, TableName, CreateMap(Field: NewValue), WhereFieldEquals)
End Sub

' updates multiple fields in a record
' in the Fields map the keys are the column names
Public Sub UpdateRecord2(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map)
	If WhereFieldEquals.Size = 0 Then
		Log("WhereFieldEquals map empty!")
		Return
	End If
	If Fields.Size = 0 Then
		Log("Fields empty")
		Return
	End If
	Dim sb As StringBuilder
	sb.Initialize
	sb.Append("UPDATE ").Append(EscapeField(TableName)).Append(" SET ")
	Dim args As List
	args.Initialize
	For Each col As String In Fields.Keys
		sb.Append(EscapeField(col)).Append("=?")
		sb.Append(",")
		args.Add(Fields.Get(col))
	Next
	sb.Remove(sb.Length - 1, sb.Length)
	sb.Append(" WHERE ")
	For Each col As String In WhereFieldEquals.Keys
		sb.Append(EscapeField(col)).Append(" = ?")
		sb.Append(" AND ")
		args.Add(WhereFieldEquals.Get(col))
	Next
	sb.Remove(sb.Length - " AND ".Length, sb.Length)
	#IF Not(DBUTILS_NOLOGS)
	Log("UpdateRecord: " & sb.ToString)
	#End If
	SQL.ExecNonQuery2(sb.ToString, args)
End Sub

' deletes a record
Public Sub DeleteRecord(SQL As SQL, TableName As String, WhereFieldEquals As Map)
	Dim sb As StringBuilder
	sb.Initialize
	sb.Append("DELETE FROM " & EscapeField(TableName) & " WHERE ")
	If WhereFieldEquals.Size = 0 Then
		Log("WhereFieldEquals map empty!")
		Return
	End If
	Dim args As List
	args.Initialize
	For Each col As String In WhereFieldEquals.Keys
		If args.Size > 0 Then sb.Append(" AND ")
		sb.Append(EscapeField(col)).Append(" = ?")
		args.Add(WhereFieldEquals.Get(col))
	Next
#IF Not(DBUTILS_NOLOGS)
	Log("DeleteRecord: " & sb.ToString)
#End If
	SQL.ExecNonQuery2(sb.ToString, args)
End Sub

'Tests whether the given table exists
Public Sub TableExists(SQL As SQL, TableName As String) As Boolean
	Dim count As Int = SQL.ExecQuerySingleResult2("SELECT count(name) FROM sqlite_master WHERE type='table' AND name=? COLLATE NOCASE", Array As String(TableName))
	Return count > 0
End Sub

' Get all tables names as list
Public Sub GetTables(DB As SQL) As List
	Return ExecuteList(DB, "SELECT name FROM sqlite_master WHERE type = 'table'", Null, 0)
End Sub

' Gets informations about each fields in a table.
' Returns a list of DBFieldInfo
Public Sub GetFieldsInfo(DB As SQL, TableName As String) As List
	Dim lstFieldsInfo As List
	lstFieldsInfo.Initialize
	Dim FieldsList As List = ExecuteMemoryTable(DB, "PRAGMA table_info ('" & TableName & "')", Null, 0)
	For Each values() As String In FieldsList
		Dim FieldInfo As DBFieldInfo
		FieldInfo.Initialize
		FieldInfo.FieldName = values(1)
		FieldInfo.DataType = values(2)
		FieldInfo.CanBeNull = (values(3) = 1)
		FieldInfo.DefValue = values(4)
		lstFieldsInfo.Add(FieldInfo)
	Next

	Return lstFieldsInfo
End Sub

Public Sub HashMD5(text As String) As String
	Dim md As MessageDigest
	Dim bc As ByteConverter
	Dim hash() As Byte
	
	hash = md.GetMessageDigest(text.GetBytes("UTF8"), "MD5")
	Return bc.HexFromBytes(hash).ToLowerCase
End Sub