B4J Tutorial [BANano]: BANanoSQL CRUD-ing around with BANanoSQLUtils

Hi

UPDATE 2020-05-19: Please use this library instead

Source Code

Honestly I am not qualified enough to write this tutorial, but anyway, its a shot. I want to use this for one of my backends and will share some things I have learned so far. I could be wrong in some things and stand to be corrected and advised. There is something I havent figured out yet and Im sure help will be provided.

Anyway let me towards the point. I have written some subs here that are familiar with most experiences with DBUtils to Create,Read,Update and Delete records. The approach followed here is the one without the Wait(), I'm sure as soon as I figure that out. Well I need to figure how to use this in a real-world app.

To understand the sequence, let me put my Firefox log herein...

Steps and sequence of followed

1. A database is created
2. After its opened, a CreateTable method is called using a map. The map has field-names and field-types. You will remember this with dbUtils. The returned tag for the table creation is DB_CREATE
3. Sub SQL_SQLExecuteResult(Tag As String, Result As List) is tracing and executing sub calls
4. The table has a primary key, I tried to create indexes on other fields, dololo. Later perhaps.
5. As I need to add a record to the table, it has a primary key, I need to first find if such a record exists, this is done with CheckFlight, this returns a DB_EXISTS tag. This is just a select where clause. If the record does not exist the result will be Zero and if not 1. If the record exists, update it if not insert it. Inserts return DB_INSERT tag whilst update returns DB_UPDATE
6. Just to demo, after the record is inserted, we update it. After it is updated, all the records are updated from East London to Israel with DB_UPDATEALL tag
7. Then a query to select an existing record is executed, this is done to return a map of the record. Its like ExecuteMap(s) in DBUtils. This is returned as an object{}, with DB_SELECT
8. Then we call a delete sub to delete the record.

As noted above, the sequencing of events here is rather important.

So there is it.

1. Open DB
2. Create Table
3. Create Record (DB_INSERT)
4. Read Record (DB_SELECT)
5. Update Record (DB_UPDATE)
6. Delete Record (DB_DELETE)

Ta!

 

[Mashiane]

Let's go through the class...

I have defined global variables to ease things up...

'things to note
'the field names are case sensitive
Sub Class_Globals
    Public BANano As BANano
    Public const DB_BOOL As String = "BOOL"
    Public const DB_INT As String = "INT"
    Public const DB_STRING As String = "STRING"
    Public const DB_DATE As String = "DATE"
    Public const DB_CREATE As String = "CREATE"
    Public const DB_INSERT As String = "INSERT"
    Public const DB_UPDATE As String = "UPDATE"
    Public const DB_UPDATEALL As String = "UPDATEALL"
    Public const DB_SELECT As String = "SELECT"
    Public const DB_EXISTS As String = "EXISTS"
    Public const DB_CREATEINDEX As String = "CREATEINDEX"
    Public const DB_DELETE As String = "DELETE"
    Public alaSQL As BANanoSQL
    Private vDBName As String
    Private ToExecute As Int = 0
    Private Executed As Int = 0
End Sub

Whenever the class is being initialized, it will open/create the database..

'open the database
Public Sub Initialize(dbName As String)
    vDBName = dbName
    alaSQL.Open("SQL", dbName)
End Sub

After the database is opened, a sub to create a table is called...

Sub SQL_SQLOpened()
    Log("Database: " & vDBName & " opened successfully!")
    'lets create the flights table
    CreateFlightsTable       
End Sub

 

[Mashiane]

Creating the flights table

A method to generate an SQL statement to create a table has been written up...

Sub CreateFlightsTable
    'lets create a table to store data
    'flights (flightNo INT, fromCity STRING, toCity STRING, isInTheAir BOOL)
    Dim flights As Map
    flights.Initialize
    flights.Put("flightNo", DB_INT)
    flights.Put("fromCity", DB_STRING)
    flights.Put("toCity", DB_STRING)
    flights.Put("isInTheAir", DB_BOOL)
    Dim sql As String = CreateTableSQL("flights",flights,"flightNo")
    Log(sql)
    alaSQL.Execute(sql, Null, DB_CREATE)
End Sub

'return a sql command to create the table
Sub CreateTableSQL(tblName As String, tblFields As Map, PK As String) As String
    If tblFields.Size = 0 Then
        Log($"CreateTableSQL: ${tblName} - tblFields map empty!"$)
        Return Null
    End If   
    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)
        If fldCnt > 0 Then
            sb.Append(", ")
        End If
        sb.Append(EscapeField(fldName))
        sb.Append(" ")
        sb.Append(fldType)
        If fldName.EqualsIgnoreCase(PK) Then
            sb.Append(" PRIMARY KEY")
        End If       
    Next
    sb.Append(")")
    'define the qry to execute
    Dim query As String = "CREATE TABLE IF NOT EXISTS " & EscapeField(tblName) & " " & sb.ToString
    Return query
End Sub

 

[Mashiane]

After tha table is created, we check if the flights exists. I could be wrong but it seems we dont have unique primary keys on the created fields, thus the need to check existance.

'to insert a record, first lets see if its existing
Sub CheckFlight(fn As Int)
    Dim flights As Map = CreateMap("flightNo": fn)
    Dim sqlm As Map = SelectWhereSQLMap("flights", flights)
    Dim sql As String = sqlm.Get("sql")
    Dim values As List = sqlm.Get("values")
    Log(sql)
    Log(values)
    alaSQL.Execute(sql, values, DB_EXISTS)
End Sub

This runs using a SelectWhereSQLMap sub. That sub is the same sub we use to Read records. It returns a map with two keys, "sql" (the sql statement to execute) and "values" (the arguesments to pass to BANanoSQL.

The result of this call is then a size check, 0 for non-existence of the record and a non-zero value for existence.

'return a sql to select record of table where one exists
Sub SelectWhereSQLMap(tblName As String, tblWhere As Map) As Map
    If tblWhere.Size = 0 Then
        Log($"SelectWhereSQLMap: ${tblName} - tblWhere map empty!"$)
        Return Null
    End If
    Dim sb As StringBuilder
    Dim args As List
    sb.Initialize
    args.Initialize
    sb.Append($"SELECT * FROM ${EscapeField(tblName)} 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 colwhere As String = tblWhere.GetKeyAt(i)
        Dim colvalue As String = tblWhere.Get(colwhere)
        sb.Append(colwhere)
        sb.Append(" = ?")
        args.Add(colvalue)
    Next
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.tostring)
    m.Put("values", args)
    Return m
End Sub

 

[Mashiane]

So, when the record does not exist (Results = 0), we need to insert it to the table. InsertFlight is called.

'should be specific to project
Sub InsertFlight(fn As Int)
    Dim flights As Map
    flights.Initialize
    flights.Put("flightNo", fn)
    flights.Put("fromCity", "Brussels")
    flights.Put("toCity", "Paris")
    flights.Put("isInTheAir", True)
    Dim sqlm As Map = InsertSQLMap("flights", flights)
    Dim sql As String = sqlm.Get("sql")
    Dim values As List = sqlm.Get("values")
    Log(sql)
    Log(values)
    alaSQL.Execute(sql, values, DB_INSERT)
End Sub

The InsertSQLMap sub also builts up the SQL statement and arguements to return for the call..

'return a sql map to insert record to table. sql = query string, values = list of values
Sub InsertSQLMap(tblName As String, tblFields As Map) As Map
    If tblFields.Size = 0 Then
        Log($"InsertSQLMap: ${tblName} - tblFields map empty!"$)
        Return Null
    End If
    Dim sb As StringBuilder
    Dim columns As StringBuilder
    Dim values As StringBuilder
    Dim listOfValues As List
    Dim iCnt As Int
    Dim iTot As Int
    sb.Initialize
    columns.Initialize
    values.Initialize
    listOfValues.Initialize
    sb.Append($"INSERT INTO ${EscapeField(tblName)} ("$)
    iTot = tblFields.Size - 1
    For iCnt = 0 To iTot
        Dim col As String = tblFields.GetKeyAt(iCnt)
        Dim value As String = tblFields.Get(col)
        If iCnt > 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(")")
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.ToString)
    m.Put("values", listOfValues)
    Return m
End Sub

 

[Mashiane]

Now the record exists, lets update it. We call UpdateFlight.

'should be specific to project
Sub UpdateFlight(fn As Int)
    'fields to update
    Dim flights As Map
    flights.Initialize
    flights.Put("fromCity", "Paris")
    flights.Put("toCity", "Brussels")
    flights.Put("isInTheAir", False)
    'select this one
    Dim wherem As Map
    wherem.Initialize
    wherem.Put("flightNo", fn)
    Dim sqlm As Map = UpdateWhereSQLMap("flights", flights, wherem)
    Dim sql As String = sqlm.Get("sql")
    Dim values As List = sqlm.Get("values")
    Log(sql)
    Log(values)
    alaSQL.Execute(sql, values, DB_UPDATE)
End Sub

This calls UpdateWhereSQLMap - a sub to generate the sql and arguements list for our call..

'return a sql to update record of table where one exists
Sub UpdateWhereSQLMap(tblName As String, tblFields As Map, tblWhere As Map) As Map
    If tblWhere.Size = 0 Then
        Log($"UpdateWhereSQLMap: ${tblName} - tblWhere map empty!"$)
        Return Null
    End If
    If tblFields.Size = 0 Then
        Log($"UpdateWhereSQLMap: ${tblName} - tblFields map empty!"$)
        Return Null
    End If
    Dim sb As StringBuilder
    Dim args As List
    sb.Initialize
    args.Initialize
    sb.Append($"UPDATE ${EscapeField(tblName)} 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 colvalue As String = tblFields.Get(col)
        sb.Append(col)
        If i <> iTot Then
            sb.Append("=?,")
        Else
            sb.Append("=?")
        End If
        args.Add(colvalue)
    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 colwhere As String = tblWhere.GetKeyAt(i)
        Dim colvalue As String = tblWhere.Get(colwhere)
        sb.Append(colwhere)
        sb.Append(" = ?")
        args.Add(colvalue)
    Next
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.tostring)
    m.Put("values", args)
    Return m
End Sub

 

[Mashiane]

We know the flight has been add, updated, lets change all our flights to be from East London to Israel, we do this with UpdateAllFlights.

'update all flights
Sub UpdateAllFlights
   'fields to update
   Dim flights As Map
   flights.Initialize
   flights.Put("fromCity", "East London")
   flights.Put("toCity", "Israel")
   flights.Put("isInTheAir", True)
   Dim sqlm As Map = UpdateSQLMap("flights", flights)
   Dim sql As String = sqlm.Get("sql")
   Dim values As List = sqlm.Get("values")
   Log(sql)
   Log(values)
   alaSQL.Execute(sql, values, DB_UPDATEALL)
End Sub

This calls UpdateSQLMap, which generates an sql and arguements list for the fields we want updated and their values...

UpdateSQLMap does not have a where clause because it updates all records in the table.

'return a sql to update all records of table
Sub UpdateSQLMap(tblName As String, tblFields As Map) As Map
    If tblFields.Size = 0 Then
        Log($"UpdateSQLMap: ${tblName} - tblFields map empty!"$)
        Return Null
    End If
    Dim sb As StringBuilder
    Dim args As List
    sb.Initialize
    args.Initialize
    sb.Append($"UPDATE ${EscapeField(tblName)} 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 colvalue As String = tblFields.Get(col)
        sb.Append(col)
        If i <> iTot Then
            sb.Append("=?,")
        Else
            sb.Append("=?")
        End If
        args.Add(colvalue)
    Next
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.tostring)
    m.Put("values", args)
    Return m
End Sub

 

[Mashiane]

After all the changes, let's read an existing record, we do this with SelectAFlight.

'find a record flights
Sub SelectAFlight
    Dim flights As Map
    flights.Initialize
    flights.Put("flightNo", 1)
    flights.Put("fromCity", "East London")
    Dim sqlm As Map = SelectWhereSQLMap("flights", flights)
    Dim sql As String = sqlm.Get("sql")
    Dim values As List = sqlm.Get("values")
    Log(sql)
    Log(values)
    alaSQL.Execute(sql, values, DB_SELECT)
End Sub

For tests we also included the fromCity. Note that flightNo was created as an Int, so we cannot pass it a string "1". So one needs to be type cautious it seems.

Once the flight is found, the record is logged to the console..

Log("SELECT DONE!")
            Log(Result.Size)
            For Each recordm As Map In Result
                Log(recordm)
            Next
            DeleteFlight(1)

We are calling DeleteFlight after that to demo how the record is deleted.

 

[Mashiane]

With DeleteFlight, the flight is supposed to be deleted from the table.

Sub DeleteFlight(fn As Int)
    'please note the operator =
    Dim flights As Map = CreateMap("flightNo =": fn)
    Dim sqlm As Map = DeleteWhereSQLMap("flights", flights)
    Dim sql As String = sqlm.Get("sql")
    Dim values As List = sqlm.Get("values")
    Log(sql)
    Log(values)
    alaSQL.Execute(sql, values, DB_DELETE)
End Sub

This calls DeleteWhereSQLMap, as usual this generates an sql statement and an arguements list from our map..

'return a sql to delete record of table where one exists
Sub DeleteWhereSQLMap(tblName As String, tblWhere As Map) As Map
    If tblWhere.Size = 0 Then
        Log($"DeleteWhereSQLMap: ${tblName} - tblWhere map empty!"$)
        Return Null
    End If
    Dim sb As StringBuilder
    Dim args As List
    sb.Initialize
    args.Initialize
    sb.Append($"DELETE FROM ${EscapeField(tblName)} 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 colwhere As String = tblWhere.GetKeyAt(i)
        Dim colvalue As String = tblWhere.Get(colwhere)
        sb.Append(colwhere)
        sb.Append(" ? ")
        args.Add(colvalue)
    Next
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.tostring)
    m.Put("values", args)
    Return m
End Sub

 

[Mashiane]

As we are not using the waiting version, all of the code execution is controlled here...

Sub SQL_SQLExecuteResult(Tag As String, Result As List)
    Select Case Tag
        Case DB_CREATEINDEX
            'lets add indexes to the table
            Executed = Executed + 1
            Log(ToExecute)
            Log(Executed)
        Case DB_CREATE
            Log("Create Done!")
            'CreateFlightsIndexes
            'check the record existance first before you add it
            CheckFlight(1)
        Case DB_EXISTS
            Log("EXISTS DONE!")
            Log(Result.Size)
            Select Case Result.Size
            Case 0
                'record does not exist, add it
                InsertFlight(1)
            Case Else
                'record exists, update it
                UpdateFlight(1)
            End Select
        Case DB_INSERT
            Log("INSERT DONE!")
            UpdateFlight(1)
        Case DB_UPDATE
            Log("UPDATE DONE!")
            UpdateAllFlights
        Case DB_UPDATEALL
            Log("UPDATE ALL DONE!")
            SelectAFlight
        Case DB_SELECT
            Log("SELECT DONE!")
            Log(Result.Size)
            For Each recordm As Map In Result
                Log(recordm)
            Next
            DeleteFlight(1)
        Case DB_DELETE
            Log("DELETE DONE!")
    End Select
End Sub

 

[Mashiane]

Here is the demo source code.

Ta!

 

[Mashiane]

Ohh, to be able to see your record in your browser storage>indexeddb>flightdb>flights table, just comment out this line...

DeleteFlight(1)

Enjoy!

 

[Mashiane]

As it is, the alasql.js code can also generate html directly from your table contents.

Sub CreateHTMLTable(elID As String, tblName As String)
    elID = elID.tolowercase
    Dim sql As String = $"SELECT * INTO HTML("#${elID}",{headers:true}) FROM ${tblName}"$
    alaSQL.Execute(sql,Null, DB_HTML)
End Sub

Would be interesting to see how to format the html table after this. You pass the function the element id to render the table to. Interesting...