#ignorewarnings:12
Sub Class_Globals
Public DB_REAL, DB_INTEGER, DB_BLOB, DB_TEXT, DB_VARCHAR, DB_BIGINT As String
Public DB_FLOAT, DB_NUMERIC, DB_BOOL As String
DB_REAL = "REAL"
DB_INTEGER = "INTEGER"
DB_BLOB = "BLOB"
DB_TEXT = "TEXT"
DB_VARCHAR = "VARCHAR(255)"
DB_BIGINT = "BIGINT"
DB_FLOAT = "FLOAT"
DB_NUMERIC = "NUMERIC"
DB_BOOL = "BOOL"
Dim 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;}"
'MySQL additions:
'Added below to make EscapeField more flexible
Private escapeCharStart As String = "[" ' Default sqlite
Private escapeCharEnd As String = "]" ' Default sqlite
'Public mysql As JdbcSQL
Private TableName As String
Private PrimaryKey As String
Private AutoIncremement As String
Private sSQL As SQL
Private Schema As Map
Public DBase As String
Public result As List
Public command As String
Public types As List
Public args As List
Public query As String
Public response As String
Public error As String
Public affectedRows As Long
Public json As String
Public OK As Boolean
Public TableName As String
Public PrimaryKey As String
Public Record As Map
Public lastID As Int
End Sub
'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize(jSQL As SQL, tblName As String, pk As String, ai As String)
TableName = tblName
AutoIncremement = ai
PrimaryKey = pk
sSQL = jSQL
'
Schema.Initialize
Record.Initialize
result.Initialize
command = ""
PrimaryKey = pk
TableName = tblName
types.Initialize
args.Initialize
query = ""
response = ""
error = ""
affectedRows = 0
json = ""
OK = False
lastID = 0
End Sub
Sub ReadConfig As Map
Return File.ReadMap(File.DirAssets, "config.properties")
End Sub
'check if a table field exists
Sub TableColumnExists(ColumnName As String) As Boolean
Dim cur As ResultSet
cur = sSQL.ExecQuery("PRAGMA table_info(" & TableName & ")")
Do While cur.NextRow
Dim MyColumn As String = cur.GetString("name")
If MyColumn.Trim.ToUpperCase = ColumnName.ToUpperCase Then Return True
Loop
Return False
End Sub
'Sub OpenMySQL As ResumableSub
' Dim config As Map = ReadConfig
' Dim sJdbc As String = config.Get("Jdbc")
' Dim sDBServer As String = config.Get("DBServer")
' Dim sDbPort As String = config.Get("DbPort")
' Dim sDatabase As String = config.Get("Database")
' Dim scharacterEncoding As String = config.Get("characterEncoding")
' Dim sDBuseSSL As String = config.Get("DBuseSSL")
' Dim sMaxPoolSize As Int = config.Get("MaxPoolSize")
' Dim sUser As String = config.Get("User")
' Dim sPassword As String = config.Get("Password")
' Dim sDriverClass As String = config.Get("DriverClass")
' '
' Dim jdbcUrl As StringBuilder
' jdbcUrl.Initialize
' jdbcUrl.Append($"${sJdbc}://"$)
' jdbcUrl.Append(sDBServer)
' jdbcUrl.Append(":")
' jdbcUrl.Append(sDbPort)
' jdbcUrl.Append("/")
' jdbcUrl.Append(sDatabase)
' jdbcUrl.Append($"?characterEncoding=${scharacterEncoding}"$)
' jdbcUrl.Append($"&useSSL=${sDBuseSSL}"$)
' '
' Dim sjdb As String = jdbcUrl.ToString
' 'mysql.InitializeAsync("mysql", sDriverClass, sjdb, sUser, sPassword)
' 'Wait For MySQL_Ready (Success As Boolean)
' 'If Success = False Then
' ' Log("Check unfiltered logs for JDBC errors.")
' 'End If
' 'Return Success
'End Sub
'return a sql to select record of table where one exists
'<code>
''select all records
'dbConnect.SelectAll(array("*"), array("name"))
'</code>
Sub SelectAll(tblfields As List, orderBy As List)
'are we selecting all fields or just some
Dim fld1 As String = tblfields.Get(0)
Dim selFIelds As String = ""
Select Case fld1
Case "*"
selFIelds = "*"
Case Else
selFIelds = JoinFields(",", tblfields)
End Select
Dim sb As StringBuilder
sb.Initialize
sb.Append($"SELECT ${selFIelds} FROM ${EscapeField(TableName)}"$)
If orderBy.IsInitialized Then
'order by
Dim stro As String = JoinFields(",", orderBy)
If stro.Length > 0 Then
sb.Append(" ORDER BY ").Append(stro)
End If
End If
query = sb.tostring
command = "select"
End Sub
'
'return a sql to select record of table where one exists
'<code>
''select all records
'dbConnect.SelectAllDesc(array("*"), array("name"), array("desc"))
'</code>
Sub SelectAllAscDesc(tblfields As List, orderBy As List, AscDesc As List)
'are we selecting all fields or just some
Dim fld1 As String = tblfields.Get(0)
Dim selFIelds As String = ""
Select Case fld1
Case "*"
selFIelds = "*"
Case Else
selFIelds = JoinFields(",", tblfields)
End Select
Dim sb As StringBuilder
sb.Initialize
sb.Append($"SELECT ${selFIelds} FROM ${EscapeField(TableName)}"$)
If orderBy.IsInitialized Then
'order by
Dim xOrder As List
xOrder.Initialize
'
Dim obTot As Int = orderBy.Size - 1
Dim obCnt As Int
For obCnt = 0 To obTot
Dim xfld As String = orderBy.Get(obCnt)
If AscDesc.IsInitialized Then
'does the field exist in sort order
If AscDesc.IndexOf(xfld) >= 0 Then
xfld = EscapeField(xfld) & " DESC"
xOrder.Add(xfld)
End If
Else
xOrder.Add(EscapeField(xfld))
End If
Next
Dim strO As String = Join(",", xOrder)
If strO.Length > 0 Then
sb.Append(" ORDER BY ").Append(strO)
End If
End If
query = sb.tostring
command = "select"
End Sub
'return a sql to select record of table where one exists
'<code>
''select distinct all order by
'dbConnect.SelectDistinctAll(array("name"), array("name"))
'</code>
Sub SelectDistinctAll(tblfields As List, orderBy As List)
'are we selecting all fields or just some
Dim fld1 As String = tblfields.Get(0)
Dim selFIelds As String = ""
Select Case fld1
Case "*"
selFIelds = "*"
Case Else
selFIelds = JoinFields(",", tblfields)
End Select
Dim sb As StringBuilder
sb.Initialize
sb.Append($"SELECT DISTINCT ${selFIelds} FROM ${EscapeField(TableName)}"$)
If orderBy.IsInitialized Then
'order by
Dim stro As String = JoinFields(",", orderBy)
If stro.Length > 0 Then
sb.Append(" ORDER BY ").Append(stro)
End If
End If
query = sb.tostring
command = "select"
End Sub
Sub CloseMySQL
'mysql.Close
End Sub
'Added to give EscapeField more flexibility
Public Sub SetEscapeChars(startChar As String, endChar As String)
escapeCharStart = startChar
escapeCharEnd = endChar
End Sub
'Make this Public
'Private Sub EscapeField(f As String) As String
Public Sub EscapeField(f As String) As String
'Change so it would work with other escape characters. For exampe, MySql uses back tik.
'Return "[" & f & "]"
Return $"${escapeCharStart}${f}${escapeCharEnd}"$
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).
Public Sub InsertMaps(ListOfMaps As List)
Dim sb, columns, values As StringBuilder
'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
End If
sSQL.BeginTransaction
Try
For i1 = 0 To ListOfMaps.Size - 1
sb.Initialize
columns.Initialize
values.Initialize
Dim listOfValues As List
listOfValues.Initialize
'MySQL modification
'MySQL escape character is different.
'sb.Append("INSERT INTO [" & TableName & "] (")
sb.Append($"INSERT INTO ${EscapeField(TableName)} ("$)
Dim m As Map
m = ListOfMaps.Get(i1)
For i2 = 0 To m.Size - 1
Dim col As String
Dim value As Object
col = m.GetKeyAt(i2)
value = m.GetValueAt(i2)
If i2 > 0 Then
columns.Append(", ")
values.Append(", ")
End If
columns.Append(EscapeField(col))
values.Append("?")
listOfValues.Add(value)
Next
sb.Append(columns.ToString)
sb.Append(") VALUES (")
sb.Append(values.ToString)
sb.Append(")")
If i1 = 0 Then
Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
End If
sSQL.ExecNonQuery2(sb.ToString, listOfValues)
Next
sSQL.TransactionSuccessful
Catch
Log(LastException)
End Try
sSQL.EndTransaction
End Sub
' updates a single field in a record
' Field is the column name
Public Sub UpdateField(Field As String, NewValue As Object, WhereFieldEquals As Map)
Dim sb As StringBuilder
sb.Initialize
sb.Append("UPDATE ").Append(EscapeField(TableName)).Append(" SET ").Append(EscapeField(Field)).Append(" = ? WHERE ")
If WhereFieldEquals.Size = 0 Then
Log("WhereFieldEquals map empty!")
Return
End If
Dim argsx As List
argsx.Initialize
argsx.Add(NewValue)
For i = 0 To WhereFieldEquals.Size - 1
If i > 0 Then sb.Append(" AND ")
sb.Append(EscapeField(WhereFieldEquals.GetKeyAt(i))).Append(" = ?")
argsx.Add(WhereFieldEquals.GetValueAt(i))
Next
Log("UpdateRecord: " & sb.ToString)
sSQL.ExecNonQuery2(sb.ToString, argsx)
End Sub
' updates multiple fields in a record
' in the Fields map the keys are the column names
Public Sub UpdateRecord2(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 argsx As List
argsx.Initialize
For i=0 To Fields.Size-1
If i<>Fields.Size-1 Then
sb.Append(EscapeField(Fields.GetKeyAt(i))).Append("=?,")
Else
sb.Append(EscapeField(Fields.GetKeyAt(i))).Append("=?")
End If
argsx.Add(Fields.GetValueAt(i))
Next
sb.Append(" WHERE ")
For i = 0 To WhereFieldEquals.Size - 1
If i > 0 Then
sb.Append(" AND ")
End If
sb.Append(EscapeField(WhereFieldEquals.GetKeyAt(i))).Append(" = ?")
argsx.Add(WhereFieldEquals.GetValueAt(i))
Next
Log("UpdateRecord: " & sb.ToString)
sSQL.ExecNonQuery2(sb.ToString, argsx)
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(StringArgs() As String, Limit As Int) As List
Dim cur As ResultSet
If StringArgs = Null Then
Dim StringArgs(0) As String
End If
cur = sSQL.ExecQuery2(query, StringArgs)
Log("ExecuteMemoryTable: " & query)
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(StringArgs() As String) As Map
Dim res As Map
Dim cur As ResultSet
If StringArgs <> Null Then
cur = sSQL.ExecQuery2(query, StringArgs)
Else
cur = sSQL.ExecQuery(query)
End If
Log("ExecuteMap: " & query)
If cur.NextRow = False Then
Log("No records found.")
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
'Executes the query and fills the list with the values in the first column.
Public Sub ExecuteList(StringArgs() As String, Limit As Int, List1 As List)
List1.Clear
Dim Table As List
Table = ExecuteMemoryTable(StringArgs, Limit)
If Table.Size = 0 Then Return
Dim Cols() As String
For i = 0 To Table.Size - 1
Cols = Table.Get(i)
List1.Add(Cols(0))
Next
End Sub
'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
'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(StringArgs() As String, Limit As Int) As String
Dim cur As ResultSet
If StringArgs <> Null Then
cur = sSQL.ExecQuery2(query, StringArgs)
Else
cur = sSQL.ExecQuery(query)
End If
Log("ExecuteHtml: " & query)
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>")
sb.Append(cur.GetString2(i))
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
Public Sub DeleteRecord(WhereFieldEquals As Map)
Dim sb As StringBuilder
sb.Initialize
'Modified 2017/06/12
'Use EscapeField instead of hard coded escaping
'sb.Append("DELETE FROM [").Append(TableName).Append("] WHERE ")
sb.Append($"DELETE FROM ${EscapeField(TableName)} WHERE "$)
If WhereFieldEquals.Size = 0 Then
Log("WhereFieldEquals map empty!")
Return
End If
Dim argsx As List
argsx.Initialize
For i = 0 To WhereFieldEquals.Size - 1
If i > 0 Then sb.Append(" AND ")
'Modified 2017/06/12
'Use EscapeField instead of hard coded escaping
'sb.Append("[").Append(WhereFieldEquals.GetKeyAt(i)).Append("] = ?")
sb.Append($"${EscapeField(WhereFieldEquals.GetKeyAt(i))} = ?"$)
argsx.Add(WhereFieldEquals.GetValueAt(i))
Next
Log("DeleteRecord: " & sb.ToString)
sSQL.ExecNonQuery2(sb.ToString, argsx)
End Sub
'Sets the database version to the given version number.
Sub SetDBVersion (Version As Int)
sSQL.ExecNonQuery2("UPDATE DBVersion set version = ?", Array As Object(Version))
End Sub
Sub TableExist(pSQL As SQL, pName As String) As Boolean
If pSQL.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name = '" & pName & "'") = 0 Then
Return False
Else
Return True
End If
End Sub
Sub FieldExist(fldName As String) As Boolean
If sSQL.ExecQuerySingleResult($"select count(*) from pragma_table_info('${TableName}') where name='${fldName}'"$) = 0 Then
Return False
Else
Return True
End If
End Sub
Sub RecordFromMap(sm As Map)
Record.Initialize
For Each k As String In sm.Keys
Dim v As Object = sm.Get(k)
Record.Put(k, v)
Next
End Sub
'add a field to the schame
'<code>
''add schema to table
'dbConnect.SchemaAddField("id", dbConntect.DB_INT)
'</code>
Sub SchemaAddField(fldName As String, fldType As String)
Schema.Put(fldName, fldType)
End Sub
'return a sql to delete record of table where one exists
'<code>
''get maximum
'dbConnect.GetMax
'End Select
'</code>
Sub GetMax
query = $"SELECT MAX(${PrimaryKey}) FROM ${EscapeField(TableName)}"$
command = "select"
End Sub
'return a sql to delete record of table where one exists
'<code>
''get minimum
'dbConnect.GetMin
'</code>
Sub GetMin
query = $"SELECT MIN(${PrimaryKey}) FROM ${EscapeField(TableName)}"$
command = "select"
End Sub
'get the column names
Sub ColumnNames
query = $"PRAGMA table_info('${TableName}')"$
command = "select"
End Sub
'get the table names
'<code>
''table names
'dbConnect.TableNames
'dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
'</code>
Sub TableNames
query = "SELECT name FROM sqlite_master WHERE type='table' AND name Not LIKE 'sqlite_%' ORDER BY name"
command = "select"
End Sub
'get table names
'<code>
''get table names
'dbConnect.GetTableNames
'dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
'dbConnect.FromJSON
'Select Case dbConnect.OK
'Case False
'Dim strError As String = dbConnect.Error
'vuetify.ShowSnackBarError("An error took place whilst running the command. " & strError)
'End Select
'</code>
Sub GetTableNames
query = $"select name FROM sqlite_master WHERE Type = 'table' ORDER BY name"$
command = "select"
End Sub
'get table structure
'<code>
''describe table
'dbConnect.DescribeTable
'dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
'dbConnect.FromJSON
'Select Case dbConnect.OK
'Case False
'Dim strError As String = dbConnect.Error
'vuetify.ShowSnackBarError("An error took place whilst running the command. " & strError)
'End Select
'</code>
Sub DescribeTable
query = $"describe ${EscapeField(TableName)}"$
command = "select"
End Sub
private Sub AndOrOperators(sm As Map) As List 'ignore
Dim nl As List
nl.initialize
For Each k As String In sm.Keys
nl.Add("AND")
Next
Return nl
End Sub
'return a sql to select record of table where one exists
'<code>
''select where
'dim sw As Map = CreateMap()
'sw.put("name", "Anele")
'dbConnect.SelectWhere1(array("id", "firstname", "lastname"), sw, array("="), array("and", "or"), array("name"))
'</code>
Sub SelectWhere1(tblfields As List, tblWhere As Map, operators As List, AndOr As List, orderBy As List)
If Schema.Size = 0 Then
Log($"B4XDBUtils.SelectWhere1: '${TableName}' schema is not set!"$)
End If
If operators.IsInitialized = False Then operators = EQOperators(tblWhere)
If AndOr.IsInitialized = False Then AndOr = AndOrOperators(tblWhere)
Dim listOfTypes As List = GetMapTypes(tblWhere)
Dim listOfValues As List = GetMapValues(tblWhere)
'are we selecting all fields or just some
Dim fld1 As String = tblfields.Get(0)
Dim selFIelds As String = ""
Select Case fld1
Case "*"
selFIelds = "*"
Case Else
selFIelds = JoinFields(",", tblfields)
End Select
Dim sb As StringBuilder
sb.Initialize
sb.Append($"SELECT ${selFIelds} FROM ${EscapeField(TableName)} WHERE "$)
Dim i As Int
Dim iWhere As Int = tblWhere.Size - 1
For i = 0 To iWhere
If i > 0 Then
Dim sandor As String = AndOr.get(i)
sb.Append($" ${sandor} "$)
End If
Dim col As String = tblWhere.GetKeyAt(i)
sb.Append(col)
Dim opr As String = operators.Get(i)
sb.Append($" ${opr} ?"$)
Next
If orderBy.IsInitialized Then
'order by
Dim stro As String = JoinFields(",", orderBy)
If stro.Length > 0 Then
sb.Append(" ORDER BY ").Append(stro)
End If
End If
query = sb.tostring
args = listOfValues
types = listOfTypes
command = "select"
response = ""
error = ""
result = NewList
json = ""
affectedRows = 0
End Sub
'return a sql to delete record of table where one exists
'<code>
''delete records where
'Dim uw As Map = CreateMap()
'uw.put("id", 10)
'dbConnect.DeleteWhere(uw, array("="))
'</code>
Sub DeleteWhere(tblWhere As Map, operators As List)
If Schema.Size = 0 Then
Log($"B4XDBUtils.DeleteWhere: '${TableName}' schema is not set!"$)
End If
If operators.IsInitialized = False Then operators = EQOperators(tblWhere)
Dim listOfTypes As List = GetMapTypes(tblWhere)
Dim listOfValues As List = GetMapValues(tblWhere)
Dim sb As StringBuilder
sb.Initialize
sb.Append($"DELETE FROM ${EscapeField(TableName)} WHERE "$)
Dim i As Int
Dim iWhere As Int = tblWhere.Size - 1
For i = 0 To iWhere
If i > 0 Then
sb.Append(" AND ")
End If
Dim col As String = tblWhere.GetKeyAt(i)
sb.Append(EscapeField(col))
Dim opr As String = operators.Get(i)
sb.Append($" ${opr} ?"$)
Next
query = sb.tostring
args = listOfValues
types = listOfTypes
command = "delete"
End Sub
'initialize a list
Sub NewList As List
Dim lst As List
lst.Initialize
Return lst
End Sub
'return id of record
'<code>
'dim nid As Int = dbConnect.GetNextID
'</code>
Sub GetNextID As String
Dim nextid As Int
Dim strid As String
If result.Size = 0 Then
nextid = 0
Else
Dim nr As Map = result.Get(0)
Dim ni As String = nr.GetDefault(PrimaryKey,"0")
nextid = CInt(ni)
End If
nextid = nextid + 1
strid = CStr(nextid)
nextid = CInt(nextid)
Return strid
End Sub
private Sub CStr(o As Object) As String
Return "" & o
End Sub
'prepare for new table definition
Sub SchemaClear
Schema.clear
End Sub
'prepare record for entry
Sub NewRecord
Record.Initialize
End Sub
'update a field value
Sub SetField(fldName As String, fldValue As Object)
Record.Put(fldName, fldValue)
End Sub
'schema add boolean
Sub SchemaAddBoolean(bools As List)
For Each b As String In bools
Schema.Put(b, DB_BOOL)
Next
End Sub
'
'add integer fields
Sub SchemaAddInt(bools As List)
For Each b As String In bools
Schema.Put(b, DB_INTEGER)
Next
End Sub
'add float fields
Sub SchemaAddFloat(bools As List)
For Each b As String In bools
Schema.Put(b, DB_FLOAT)
Next
End Sub
'add blob fields
Sub SchemaAddBlob(bools As List)
For Each b As String In bools
Schema.Put(b, DB_BLOB)
Next
End Sub
'add text fields
Sub SchemaAddText(bools As List)
For Each b As String In bools
Schema.Put(b, DB_TEXT)
Next
End Sub
'convert a json string to a map
private Sub Json2Map(strJSON As String) As Map
Dim jsonx As JSONParser
Dim Map1 As Map
Map1.Initialize
Map1.clear
Try
If strJSON.length > 0 Then
jsonx.Initialize(strJSON)
Map1 = jsonx.NextObject
End If
Return Map1
Catch
Return Map1
End Try
End Sub
'convert a map to a json string using JSONGenerator
private Sub Map2Json(mp As Map) As String
Dim jsonx As JSONGenerator
jsonx.Initialize(mp)
Return jsonx.ToString
End Sub
' return string to create database
'<code>
''create a database
'dbConnect.CreateDatabase
'</code>
Sub CreateDatabase
query = $"CREATE DATABASE IF NOT EXISTS ${EscapeField(DBase)}"$
command = "createdb"
End Sub
'drop the database
'<code>
''drop a database
'dbConnect.DropDatabase
'</code>
Sub DropDataBase
query = $"DROP DATABASE ${EscapeField(DBase)}"$
command = "dropdb"
End Sub
'execute your own sql query
'<code>
''execute a query string
'dbConnect.Execute("...")
'</code>
Sub Execute(strSQL As String)
query = strSQL
command = "execute"
End Sub
'schema create table
'<code>
''schema create table
'dbConnect.SchemaCreateTable
'</code>
Sub SchemaCreateTable
CreateTable(Schema)
End Sub
'return sql command to drop a table
'<code>
''drop a table
'dbConnect.DropTable
public Sub DropTable
'define the qry to execute
query = "DROP TABLE " & EscapeField(TableName)
command = "droptable"
End Sub
'get the list of types
private Sub GetMapTypes(sourceMap As Map) As List
Dim listOfTypes As List
listOfTypes.Initialize
Dim iCnt As Int
Dim iTot As Int
iTot = sourceMap.Size - 1
For iCnt = 0 To iTot
Dim col As String = sourceMap.GetKeyAt(iCnt)
Dim colType As String = Schema.GetDefault(col,"STRING")
Select Case colType
Case "INTEGER", "INT", "BOOL","BOOLEAN"
listOfTypes.add("i")
Case "BLOB"
listOfTypes.add("b")
Case "REAL","FLOAT","DOUBLE"
listOfTypes.add("d")
Case Else
listOfTypes.add("s")
End Select
Next
Return listOfTypes
End Sub
private Sub GetMapType(k As String) As String
If Schema.ContainsKey(k) Then
Dim colType As String = Schema.GetDefault(k, "STRING")
Select Case colType
Case "INTEGER", "INT", "BOOL","BOOLEAN"
Return "i"
Case "BLOB"
Return "b"
Case "REAL","FLOAT","DOUBLE"
Return "d"
Case Else
Return "s"
End Select
Else
Return "s"
End If
End Sub
'get map values
private Sub GetMapValues(sourceMap As Map) As List
Dim listOfValues As List
listOfValues.Initialize
Dim iCnt As Int
Dim iTot As Int
iTot = sourceMap.Size - 1
For iCnt = 0 To iTot
'get the value
Dim key As String = sourceMap.getkeyat(iCnt)
Dim value As String = sourceMap.GetValueAt(iCnt)
value = CStr(value)
value = value.trim
'get the type
Dim vtype As String = GetMapType(key)
Select Case vtype
Case "i"
'integer
value = CInt(value)
Case "d"
'double
value = CDbl(value)
Case "s", "b"
'string
End Select
listOfValues.Add(value)
Next
Return listOfValues
End Sub
'get map keys to a list
private Sub GetMapKeys(sourceMap As Map) As List
Dim listOfValues As List
listOfValues.Initialize
Dim iCnt As Int
Dim iTot As Int
iTot = sourceMap.Size - 1
For iCnt = 0 To iTot
Dim value As String = sourceMap.GetKeyAt(iCnt)
listOfValues.Add(value)
Next
Return listOfValues
End Sub
'return a sql insert statement to insert current record
'<code>
''insert current record
'dbConnect.Insert
'</code>
Sub Insert
Insert1(Record)
End Sub
'return a sql insert statement
'<code>
''insert a record using own record
'dbConnect.Insert1(Record)
'</code>
Sub Insert1(Rec As Map)
If Schema.Size = 0 Then
Log($"B4XDBUtils.Insert1: '${TableName}' schema is not set!"$)
End If
Dim sb As StringBuilder
Dim columns As StringBuilder
Dim values As StringBuilder
Dim listOfValues As List = GetMapValues(Rec)
Dim listOfTypes As List = GetMapTypes(Rec)
Dim iCnt As Int
Dim iTot As Int
sb.Initialize
columns.Initialize
values.Initialize
sb.Append($"INSERT INTO ${EscapeField(TableName)} ("$)
iTot = Rec.Size - 1
For iCnt = 0 To iTot
Dim col As String = Rec.GetKeyAt(iCnt)
If iCnt > 0 Then
columns.Append(", ")
values.Append(", ")
End If
columns.Append(EscapeField(col))
values.Append("?")
Next
sb.Append(columns.ToString)
sb.Append(") VALUES (")
sb.Append(values.ToString)
sb.Append(")")
query = sb.ToString
args = listOfValues
types = listOfTypes
command = "insert"
End Sub
'return a sql insert statement
'<code>
''insert replace a record
'dbConnect.InsertReplace
'</code>
Sub InsertReplace
If Schema.Size = 0 Then
Log($"B4XDBUtils.InsertReplace: '${TableName}' schema is not set!"$)
End If
Dim sb As StringBuilder
Dim columns As StringBuilder
Dim values As StringBuilder
Dim listOfValues As List = GetMapValues(Record)
Dim listOfTypes As List = GetMapTypes(Record)
Dim iCnt As Int
Dim iTot As Int
sb.Initialize
columns.Initialize
values.Initialize
sb.Append($"REPLACE INTO ${EscapeField(TableName)} ("$)
iTot = Record.Size - 1
For iCnt = 0 To iTot
Dim col As String = Record.GetKeyAt(iCnt)
If iCnt > 0 Then
columns.Append(", ")
values.Append(", ")
End If
columns.Append(EscapeField(col))
values.Append("?")
Next
sb.Append(columns.ToString)
sb.Append(") VALUES (")
sb.Append(values.ToString)
sb.Append(")")
query = sb.ToString
args = listOfValues
types = listOfTypes
command = "insert"
End Sub
'delete a single value based on the primary key
'<code>
''delete a record using primary key
'dbConnect.Delete(10)
'</code>
Sub Delete(primaryValue As String)
If Schema.Size = 0 Then
Log($"B4XDBUtils.Delete: '${TableName}' schema is not set!"$)
End If
Dim qw As Map = CreateMap()
qw.Put(PrimaryKey, primaryValue)
DeleteWhere(qw, Array("="))
End Sub
'join list to multi value string
private Sub JoinFields(delimiter As String, lst As List) As String
Dim i As Int
Dim sb As StringBuilder
Dim fld As String
sb.Initialize
fld = lst.Get(0)
fld = EscapeField(fld)
sb.Append(fld)
For i = 1 To lst.size - 1
Dim fld As String = lst.Get(i)
fld = EscapeField(fld)
sb.Append(delimiter).Append(fld)
Next
Return sb.ToString
End Sub
'join list to mv string
private Sub Join(delimiter As String, lst As List) As String
Dim i As Int
Dim sb As StringBuilder
Dim fld As String
sb.Initialize
fld = lst.Get(0)
sb.Append(fld)
For i = 1 To lst.size - 1
Dim fld As String = lst.Get(i)
sb.Append(delimiter).Append(fld)
Next
Return sb.ToString
End Sub
'<code>
''read a record
'dbConnect.Read(10)
'End Select
'</code>
Sub Read(primaryValue As String)
If Schema.Size = 0 Then
Log($"B4XDBUtils.Read: '${TableName}' schema is not set!"$)
End If
Dim qw As Map = CreateMap()
qw.Put(PrimaryKey, primaryValue)
SelectWhere(Array("*"), qw, Null, Array(PrimaryKey))
End Sub
'exists
'<code>
''check existence of a record
'dbConnect.Exists(10)
'End Select
'</code>
Sub Exists(primaryValue As String)
If Schema.Size = 0 Then
Log($"B4XDBUtils.Exists: '${TableName}' schema is not set!"$)
End If
Dim qw As Map = CreateMap()
qw.Put(PrimaryKey, primaryValue)
SelectWhere(Array(PrimaryKey), qw, Null, Array(PrimaryKey))
End Sub
'return a sql to select record of table where one exists
'<code>
''select where
'Dim sw As Map = CreateMap()
'sw.put("id", 10)
'sw.put("age", 20)
'dbConnect.SelectWhere(array("*"), sw, array(">=", "<"), array("name"))
'End Select
'</code>
Sub SelectWhere(tblfields As List, tblWhere As Map, operators As List, orderBy As List)
If Schema.Size = 0 Then
Log($"B4XDBUtils.SelectWhere: '${TableName}' schema is not set!"$)
End If
If operators.IsInitialized = False Then operators = EQOperators(tblWhere)
Dim listOfTypes As List = GetMapTypes(tblWhere)
Dim listOfValues As List = GetMapValues(tblWhere)
'are we selecting all fields or just some
Dim fld1 As String = tblfields.Get(0)
Dim selFIelds As String = ""
Select Case fld1
Case "*"
selFIelds = "*"
Case Else
selFIelds = JoinFields(",", tblfields)
End Select
Dim sb As StringBuilder
sb.Initialize
sb.Append($"SELECT ${selFIelds} FROM ${EscapeField(TableName)} WHERE "$)
Dim i As Int
Dim iWhere As Int = tblWhere.Size - 1
For i = 0 To iWhere
If i > 0 Then
sb.Append(" AND ")
End If
Dim col As String = tblWhere.GetKeyAt(i)
Dim oper As String = operators.Get(i)
sb.Append(EscapeField(col))
sb.Append($" ${oper} ?"$)
Next
If orderBy.IsInitialized Then
'order by
Dim stro As String = JoinFields(",", orderBy)
If stro.Length > 0 Then
sb.Append(" ORDER BY ").Append(stro)
End If
End If
query = sb.tostring
args = listOfValues
types = listOfTypes
command = "select"
End Sub
'return a sql to select record of table where one exists
'<code>
''select distinct where
'Dim sw As Map = CreateMap()
'sw.put("id", 10)
'dbConnect.SelectDistinctWhere(array("name"), sw, array("="), array("name"))
'</code>
Sub SelectDistinctWhere(tblfields As List, tblWhere As Map, operators As List, orderBy As List)
If Schema.Size = 0 Then
Log($"B4XDBUtils.SelectDistinctWhere: '${TableName}' schema is not set!"$)
End If
If operators.IsInitialized = False Then operators = EQOperators(tblWhere)
Dim listOfTypes As List = GetMapTypes(tblWhere)
Dim listOfValues As List = GetMapValues(tblWhere)
'are we selecting all fields or just some
Dim fld1 As String = tblfields.Get(0)
Dim selFIelds As String = ""
Select Case fld1
Case "*"
selFIelds = "*"
Case Else
selFIelds = JoinFields(",", tblfields)
End Select
Dim sb As StringBuilder
sb.Initialize
sb.Append($"SELECT DISTINCT ${selFIelds} FROM ${EscapeField(TableName)} WHERE "$)
Dim i As Int
Dim iWhere As Int = tblWhere.Size - 1
For i = 0 To iWhere
If i > 0 Then
sb.Append(" AND ")
End If
Dim col As String = tblWhere.GetKeyAt(i)
Dim oper As String = operators.Get(i)
sb.Append(EscapeField(col))
sb.Append($" ${oper} ?"$)
Next
If orderBy.IsInitialized Then
'order by
Dim stro As String = JoinFields(",", orderBy)
If stro.Length > 0 Then
sb.Append(" ORDER BY ").Append(stro)
End If
End If
query = sb.tostring
args = listOfValues
types = listOfTypes
command = "select"
End Sub
'return a sql to delete record of table where one exists
'<code>
''delete all records
'dbConnect.DeleteAll
'</code>
Sub DeleteAll
query = $"DELETE FROM ${EscapeField(TableName)}"$
command = "delete"
End Sub
'reset the auto-increment key
'<code>
''reset the auto-increment key
'dbConnect.ResetAutoIncrement
'</code>
Sub ResetAutoIncrement
query = $"delete from sqlite_sequence where name='${TableName}'"$
command = "delete"
End Sub
'return a sql to truncate table
'<code>
''delete all records and reset auto increment
'dbConnect.Truncate
'</code>
Sub Truncate
query = $"TRUNCATE TABLE ${EscapeField(TableName)}"$
command = "delete"
End Sub
private Sub EQOperators(sm As Map) As List 'ignore
Dim nl As List
nl.initialize
For Each k As String In sm.Keys
nl.Add("=")
Next
Return nl
End Sub
private Sub List2Array(lst As List) As String()
Dim rTot As Int = lst.size
Dim rCnt As Int = 0
Dim xout(rTot) As String
For rCnt = 0 To rTot - 1
xout(rCnt) = lst.Get(rCnt)
Next
Return xout
End Sub
'convert the json
'<code>
''convert response to readable map
'dbConnect.FromJSON
'</code>
Sub FromJSON
OK = False
result.Initialize
Try
Dim cur As ResultSet
If args.IsInitialized Then
Dim args1() As String = List2Array(args)
Select Case command
Case "execute", "select"
cur = sSQL.ExecQuery2(query, args1)
Do While cur.NextRow
Dim res As Map = CreateMap()
For i = 0 To cur.ColumnCount - 1
Dim colName As String = cur.GetColumnName(i).ToLowerCase
Dim fValue As String = cur.GetString2(i)
'fValue = FixNull(fValue)
res.Put(colName, fValue)
Next
result.Add(res)
Loop
cur.Close
Case Else
sSQL.ExecNonQuery2(query, args)
If command = "insert" Then
lastID = sSQL.ExecQuerySingleResult("SELECT last_insert_rowid()")
End If
End Select
Else
Select Case command
Case "execute", "select"
cur = sSQL.ExecQuery(query)
Do While cur.NextRow
Dim res As Map
res.Initialize
For i = 0 To cur.ColumnCount - 1
Dim colName As String = cur.GetColumnName(i).ToLowerCase
Dim fValue As String = cur.GetString2(i)
fValue = FixNull(fValue)
res.Put(colName, fValue)
Next
result.Add(res)
Loop
cur.Close
Case Else
If query.EndsWith(";") = False Then query = query & ";"
Dim commands() As String = StrParse(";", query)
For Each strcommand As String In commands
strcommand = strcommand.Trim
If strcommand <> "" Then
sSQL.ExecNonQuery(strcommand)
End If
Next
End Select
End If
affectedRows = result.size
response = "Success"
error = ""
OK = True
Catch
response = "Error"
error = LastException
affectedRows = -1
Log(LastException)
End Try
End Sub
Sub StrParse(Delimiter As String, MV As String) As String()
Delimiter = FixDelimiter(Delimiter)
Return Regex.Split(Delimiter, MV)
End Sub
private Sub FixDelimiter(sValue As String) As String
If sValue = "|" Then sValue = "\|"
If sValue = "." Then sValue = "\."
If sValue = "\" Then sValue = "\\"
If sValue = "^" Then sValue = "\^"
If sValue = "$" Then sValue = "\$"
If sValue = "?" Then sValue = "\?"
If sValue = "*" Then sValue = "\*"
If sValue = "+" Then sValue = "\+"
If sValue = "(" Then sValue = "\("
If sValue = ")" Then sValue = "\)"
If sValue = "[" Then sValue = "\["
If sValue = "{" Then sValue = "\{"
If sValue = ";" Then sValue = "\;"
If sValue = "$" Then sValue = "\$"
Return sValue
End Sub
private Sub FixNull(sObj As Object) As String
Dim sValue As String
If sObj = Null Then
sValue = ""
Else
sValue = CStr(sObj)
End If
sValue = sValue.Replace("NULL","").Replace("null","")
Return sValue
End Sub
'return a sql command to create the table
'<code>
''create table
'Dim schema As Map = CreateMap()
'schema.Put("id", dbConnect.DB_INT)
'schema.put("name", dbConnect.DB_TEXT)
'dbConnect.CreateTable(schema)
'</code>
private Sub CreateTable(tblFields As Map)
Dim fldName As String
Dim fldType As String
Dim fldTot As Int
Dim fldCnt As Int
fldTot = tblFields.Size - 1
Dim sb As StringBuilder
sb.Initialize
sb.Append("(")
For fldCnt = 0 To fldTot
fldName = tblFields.GetKeyAt(fldCnt)
fldType = tblFields.Get(fldName)
fldType = fldType.Replace("STRING", "TEXT")
fldType = fldType.Replace("TEXT", "VARCHAR(255)")
If fldCnt > 0 Then
sb.Append(", ")
End If
sb.Append(EscapeField(fldName))
sb.Append(" ")
sb.Append(fldType)
Select Case fldType
Case "STRING", "TEXT", "VARCHAR(255)"
sb.Append(" COLLATE NOCASE")
End Select
If fldName.EqualsIgnoreCase(PrimaryKey) Then
sb.Append(" PRIMARY KEY")
End If
If fldName.EqualsIgnoreCase(AutoIncremement) Then
sb.Append(" AUTOINCREMENT")
End If
Next
sb.Append(")")
'define the qry to execute
query = "CREATE TABLE IF NOT EXISTS " & EscapeField(TableName) & " " & sb.ToString
command = "createtable"
End Sub
'add a column use Schema
Sub AddColumns
Dim commands As List
commands.Initialize
Dim fldName As String
Dim fldType As String
Dim fldTot As Int
Dim fldCnt As Int
fldTot = Schema.Size - 1
For fldCnt = 0 To fldTot
fldName = Schema.GetKeyAt(fldCnt)
fldType = Schema.Get(fldName)
fldType = fldType.Replace("STRING", "TEXT")
commands.Add($"ALTER TABLE ${EscapeField(TableName)} ADD COLUMN ${EscapeField(fldName)} ${fldType}"$)
Next
query = Join(";", commands)
command = "altertable"
End Sub
private Sub CDbl(o As Object) As Double
Try
Dim ox As String = o
ox = GetNumbers(ox)
If ox = "" Then ox = "0"
Return ox
Catch
Return 0
End Try
End Sub
private Sub CInt(o As Object) As Int
Try
Dim ox As String = o
ox = GetNumbers(ox)
If ox = "" Then ox = "0"
Return ox
Catch
Return 0
End Try
End Sub
private Sub GetNumbers(value As String) As String
Dim strCnt As Int
Dim str As String
Dim sb As StringBuilder
sb.Initialize
Dim master As String = "0123456789-."
For strCnt = 0 To value.Length - 1
str = value.CharAt(strCnt)
If master.IndexOf(str) >= 0 Then
sb.Append(str)
End If
Next
Return sb.tostring
End Sub
'update a record
'<code>
''update current record
'dbConnect.Update(10)
'</code>
Sub Update(priValue As String)
If Schema.Size = 0 Then
Log($"B4XDBUtils.Update: '${TableName}' schema is not set!"$)
End If
Dim tblWhere As Map = CreateMap()
tblWhere.Put(PrimaryKey, priValue)
UpdateWhere(Record, tblWhere, Null)
End Sub
'update using primary key
'<code>
''update record using primary key
'Dim rec as Map = CreateMap()
'rec.put("name", "Anele")
'rec.put("email", "email@email.com")
'dbConnect.Update1(rec, 10)
'</code>
Sub Update1(Rec As Map, priValue As String)
If Schema.Size = 0 Then
Log($"B4XDBUtils.Update1: '${TableName}' schema is not set!"$)
End If
Record = Rec
Dim tblWhere As Map = CreateMap()
tblWhere.Put(PrimaryKey, priValue)
UpdateWhere(Rec, tblWhere, Null)
End Sub
'return a sql to update records of table where one exists
'<code>
''update where using map...
'dim rec As Map = CreateMap()
''define where clause
'rec.put("name", "Anele")
'Dim uw As Map = CreateMap()
'uw.put("id", 10)
'dbConnect.UpdateWhere(rec, uw, array("="))
'</code>
Sub UpdateWhere(tblfields As Map, tblWhere As Map, operators As List)
If Schema.Size = 0 Then
Log($"B4XDBUtils.UpdateWhere: '${TableName}' schema is not set!"$)
End If
If operators.IsInitialized = False Then operators = EQOperators(tblWhere)
Dim listOfTypes As List = GetMapTypes(tblfields)
Dim listOfTypes1 As List = GetMapTypes(tblWhere)
listOfTypes.AddAll(listOfTypes1)
Dim listOfValues As List = GetMapValues(tblfields)
Dim listOfValues1 As List = GetMapValues(tblWhere)
listOfValues.AddAll(listOfValues1)
Dim sb As StringBuilder
sb.Initialize
sb.Append($"UPDATE ${EscapeField(TableName)} SET "$)
Dim i As Int
Dim iTot As Int = tblfields.Size - 1
For i = 0 To iTot
Dim col As String = tblfields.GetKeyAt(i)
sb.Append(EscapeField(col))
If i <> iTot Then
sb.Append("= ?,")
Else
sb.Append("= ?")
End If
Next
sb.Append($" WHERE "$)
Dim iWhere As Int = tblWhere.Size - 1
For i = 0 To iWhere
If i > 0 Then
sb.Append(" AND ")
End If
Dim col As String = tblWhere.GetKeyAt(i)
sb.Append(EscapeField(col))
Dim opr As String = operators.Get(i)
sb.Append($" ${opr} ?"$)
Next
query = sb.tostring
args = listOfValues
types = listOfTypes
command = "update"
End Sub
'return a sql to update all records of table
'update all records
'<code>
''update all records with new field details
'dbConnect.UpdateAll(CreateMap("name":"Anele", "age":30))
'</code>
Sub UpdateAll(tblFields As Map)
If Schema.Size = 0 Then
Log($"B4XDBUtils.UpdateAll: '${TableName}' schema is not set!"$)
End If
Dim operators As List = EQOperators(tblFields)
Dim listOfTypes As List = GetMapTypes(tblFields)
Dim listOfValues As List = GetMapValues(tblFields)
Dim sb As StringBuilder
sb.Initialize
sb.Append($"UPDATE ${EscapeField(TableName)} SET "$)
Dim i As Int
Dim iTot As Int = tblFields.Size - 1
For i = 0 To iTot
Dim col As String = tblFields.GetKeyAt(i)
Dim oper As String = operators.Get(i)
sb.Append(col)
If i <> iTot Then
sb.Append($" ${oper} ?,"$)
Else
sb.Append($" ${oper} ?"$)
End If
Next
query = sb.tostring
args = listOfValues
types = listOfTypes
command = "update"
End Sub
