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

Discussion in 'B4J Tutorials' started by Mashiane, Jan 25, 2019.

Tags:
  1. Mashiane

    Mashiane Expert Licensed User

    Hi

    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...

    BANanoSQL.png
    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!
     
    Last edited: Jan 25, 2019
  2. Mashiane

    Mashiane Expert Licensed User

    Let's go through the class...

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

    Code:
    '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..

    Code:
    '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...

    Code:
    Sub SQL_SQLOpened()
        
    Log("Database: " & vDBName & " opened successfully!")
        
    'lets create the flights table
        CreateFlightsTable       
    End Sub
     
    Johan Schoeman likes this.
  3. Mashiane

    Mashiane Expert Licensed User

    Creating the flights table

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

    Code:
    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(
    sqlNull, DB_CREATE)
    End Sub
    Code:
    'return a sql command to create the table
    Sub CreateTableSQL(tblName As String, tblFields As Map, PK As StringAs 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
     
    Johan Schoeman likes this.
  4. Mashiane

    Mashiane Expert Licensed User

    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.

    Code:
    '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.

    Code:
    'return a sql to select record of table where one exists
    Sub SelectWhereSQLMap(tblName As String, tblWhere As MapAs 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
     
    Johan Schoeman likes this.
  5. Mashiane

    Mashiane Expert Licensed User

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

    Code:
    '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..

    Code:
    'return a sql map to insert record to table. sql = query string, values = list of values
    Sub InsertSQLMap(tblName As String, tblFields As MapAs 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
     
    Johan Schoeman likes this.
  6. Mashiane

    Mashiane Expert Licensed User

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

    Code:
    '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..

    Code:
    'return a sql to update record of table where one exists
    Sub UpdateWhereSQLMap(tblName As String, tblFields As Map, tblWhere As MapAs 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
     
    Johan Schoeman likes this.
  7. Mashiane

    Mashiane Expert Licensed User

    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.

    Code:
    '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.

    Code:
    'return a sql to update all records of table
    Sub UpdateSQLMap(tblName As String, tblFields As MapAs 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
     
    Johan Schoeman likes this.
  8. Mashiane

    Mashiane Expert Licensed User

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

    Code:
    '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..

    Code:
    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.
     
    Johan Schoeman likes this.
  9. Mashiane

    Mashiane Expert Licensed User

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

    Code:
    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..

    Code:
    'return a sql to delete record of table where one exists
    Sub DeleteWhereSQLMap(tblName As String, tblWhere As MapAs 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
     
    Johan Schoeman likes this.
  10. Mashiane

    Mashiane Expert Licensed User

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

    Code:
    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
     
    Johan Schoeman likes this.
  11. Mashiane

    Mashiane Expert Licensed User

    Here is the demo source code.

    Ta!
     

    Attached Files:

    Johan Schoeman likes this.
  12. Mashiane

    Mashiane Expert Licensed User

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

    Code:
    DeleteFlight(1)
    Enjoy!
     
  13. Mashiane

    Mashiane Expert Licensed User

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

    alaSQLHTML.png

    Code:
    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...
     
    Johan Schoeman and joulongleu like this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice