B4J Tutorial [BANanoVueMaterial]: Create a CRUD App with BANanoSQL (IndexedDB) as a backend

Ola

NB: First set up BANanoVueMaterial The code for this tutorial is under the folder 4. Examples\6. Form Utilities


This tutorial will take you through the process of creating a CRUD app with BANanoVueMaterial and using BANanoSQL (IndexedDB) as a backend.

Previously we looked at how we can create a CRUD app using MySQL as a backend. For more details about that, go here.

If you have been following my posts, I started talking about BANanoSQLUtilities in January 2019. Those utilities were helper functions that enable one to build SQL commands for BANanoSQL. We later updated these and called the helper class BANanoAlaSQL. Some articles were also written using it.

So here wer are using the same class to speak to BANanoSQL. We will:

1. Create Records
2. Read Records
3. Update existing records
4. Delete records.

For that we are creating a simple table and just saving records to it using BANanoSQL. The code here is similar to our code in creating a CRUD app using MySQL however we are using BANanoSQL.

 

[Mashiane]

1. Let's create a table using BANanoSQL to store our records.

'open the database
    db.OpenWait("alasql", "formutilities")
    'create the necessary tables
    Dim tblTables As BANanoAlaSQL
    tblTables.Initialize
    tblTables.SchemaAddText(Array("tablename","description","primarykey","autoincrement"))
    'build the resultset
    Dim rs As AlaSQLResultSet = tblTables.SchemaCreateTable("tables", "tablename")
    'execute the table creation
    db.ExecuteWait(rs.query, rs.args)

2. Let's create a table to show the records from the table.

tables = vm.CreateGijgoTable("tables", "id", Me)
    tables.SetTitle("Tables")
    tables.AddColumn("tablename", "Table Name")
    tables.AddColumn("primarykey", "Primary Key")
    tables.AddColumn("autoincrement", "Auto Increment")
    tables.AddColumn("description", "Description")
    tables.SetDataSource(Array())
    tables.AddEditTrash
    cont.AddComponent(1,1, tables.tostring)

3. Let's create a modal form to add the records for the table.

mdlTable = vm.CreateDialog("mdlTable", Me).SetTitle("New Table").SetWidth("600px").SetModal
    mdlTable.AddOK("btnCancelTable", "Cancel")
    mdlTable.AddOK("btnSaveTable", "Save")
    '
    Dim txtTableName As VMInputControl = mdlTable.container.NewText("tablename", "Table Name", "", True, "", 20, "", "The table name is required", 0)
    mdlTable.container.AddControlS(txtTableName, 1, 1, 12, 12, 12, 12)
    '
    Dim txtPrimaryKey As VMInputControl = mdlTable.container.NewText("primarykey", "Primary Key", "", True, "", 20, "", "The primary key field is required", 0)
    mdlTable.container.AddControlS(txtPrimaryKey, 2, 1, 12, 12, 12, 12)
    '
    Dim chkAI As VMInputControl = mdlTable.container.NewCheckBox("autoincrement", "Auto Increment", "Yes", True, 0)
    chkAI.SetString
    mdlTable.container.AddControlS(chkAI, 3, 1, 12, 12, 12, 12)
    '
    Dim txtDescription As VMInputControl = mdlTable.container.NewTextArea("description", "Description", "", False, False, "", 100, "", "", 0)
    mdlTable.container.AddControlS(txtDescription, 4, 1, 12, 12, 12, 12)
    
    'add the dialog to the page
    vm.AddDialog(mdlTable)

 

[Mashiane]

Creating Records

When a user clicks the add button on the navigation button, we clear the modal dialog and prepare the form for data entry.

Sub btnAddTable_click(e As BANanoEvent)
    modTables.mode = "A"
    modTables.mdlTable.Container.setdefaults
    vm.showdialog("mdlTable")
End Sub

As the Mode=A, then insert the record to the table

Sub btnSaveTable_click(e As BANanoEvent)
    'get the data of the container
    Dim rec As Map = mdlTable.Container.GetData
    'ensure table name and field dont have spaces
    rec = mdlTable.container.NoSpaces(rec, Array("tablename","primarykey"))
    'validate the record
    Dim bValid As Boolean = vm.Validate(rec, mdlTable.container.required)
    If bValid = False Then Return
    ' get the table name and ensure there are no spaces
    Dim stablename As String = rec.Get("tablename")
    
    'open the database
    db.OpenWait("alasql", "formutilities")
    '
    alaSQL.Initialize
    Select Case Mode
    Case "A"
        'does the table exist
        Dim rsTables As AlaSQLResultSet = alaSQL.Exists("tables", "tablename", stablename)
        rsTables.result = db.ExecuteWait(rsTables.query, rsTables.args)
        If rsTables.result.size = 0 Then
            Dim rsInsert As AlaSQLResultSet = alaSQL.Insert("tables", rec)
            'execute the table creation
            rsInsert.result = db.ExecuteWait(rsInsert.query, rsInsert.args)
            vm.CallMethod("RefreshTables")
            vm.hidedialog("mdlTable")
        Else
            vm.ShowSnackBar($"The table ${stablename} already exists!"$)
        End If
    Case "E"

Above we get the contents of the modal and validate them. We ensure we dont have spaces for the table name and primary key field names. We open the BANanoSQL db and then run an .Insert on the database.

 

[Mashiane]

Updating Records

Each time a user clicks the 'Edit' column in the table for the record they want to update, the edit mode is changed to 'E'

Sub tables_edit(e As BANanoEvent)
    'get the record corresponding to the row
    Dim rec As Map = tables.GetRecordFromEvent(e)
    Dim stablename As String = rec.GetDefault("tablename","")
    If stablename = "" Then Return
    BANano.SetSessionStorage("oldname", stablename)
    'turn the mode to edit
    Mode = "E"
    'update the title of the modal dialog
    mdlTable.SetTitle("Edit Table")
    'set the default values for the modal
    mdlTable.Container.SetDefaults
    'update the states for the modeal
    vm.SetState(rec)
    'show the modal with new states
    vm.ShowDialog("mdlTable")
End Sub

The corresponding record is read from the table. Using the table name, that is saved as "oldname". This oldname is used in case the table name changes because in this case the table name is the primary key of the table.

We read the record and then display the modal dialog that shows the record.

Once the record is updates, we save it to the db. First we check if the table name has changed. If it has changed, we use the oldname to update the record, else we use the existing name.

Mode=E

Case "E"
        'get the old table name
        Dim oldname As String =    BANano.GetSessionStorage("oldname")
        If oldname.EqualsIgnoreCase(stablename) Then
            'the table names are the same
            Dim rsEdit As AlaSQLResultSet = alaSQL.Update("tables", "tablename", stablename, rec)
            rsEdit.result = db.ExecuteWait(rsEdit.query, rsEdit.args)
        Else
            'the table names have changed
            'update the table details using the old name
            Dim rsEdit As AlaSQLResultSet = alaSQL.Update("tables", "tablename", oldname, rec)   
            rsEdit.result = db.ExecuteWait(rsEdit.query, rsEdit.args)
        End If
        vm.CallMethod("RefreshTables")
        vm.hidedialog("mdlTable")
    End Select

 

[Mashiane]

Reading the records

We have a table listing, this is based on a select of all records from the BANanoSQL database.

This sub is called each time, we add & update a record in the database and also called when the page is shown.

Sub RefreshTables
    vm.pagepause
    'open the database
    db.OpenWait("alasql", "formutilities")
    'init library
    alaSQL.Initialize
    'select all records in table
    Dim rsTables As AlaSQLResultSet = alaSQL.SelectAll("tables", Array("*"), Array("tablename"))
    'execute the table creation
    rsTables.result = db.ExecuteWait(rsTables.query, rsTables.args)
    tables.SetDataSource(rsTables.result)
    tables.refresh
    vm.pageresume
End Sub

So we built up a SQL command and then feed it to BANanoSQL to extract the results, we then run a table .Refresh with the datasource.

 

[Mashiane]

Deleting Records

To delete records, one has to click the Delete button on the table listing.

This brings out a confirmation dialog and then if the user clicks "Ok" the table record is deleted.

Sub tables_delete(e As BANanoEvent)
    'get the record for the row
    Dim rec As Map = tables.GetRecordFromEvent(e)
    Dim sTableName As String = rec.GetDefault("tablename","")
    If sTableName = "" Then Return
    'save the id to delete
    vm.SetStateSingle("tablename", sTableName)
    'indicate confirm dialog
    vm.ShowConfirm("delete_table", $"Confirm Delete: ${sTableName}"$, _
    "Are you sure that you want to delete this table. You will not be able to undo your actions. Continue?","Ok","Cancel")
End Sub

Clicking the OK button in the dialog fires pgIndex.confirm_ok.

We have saved the state for "tablename" as the tablename we have selected. We have also set a .ShowConfirm("delete_table"... process.

Sub confirm_ok(e As BANanoEvent)
    Dim sconfirm As String = vm.getconfirm
    Select Case sconfirm
    Case "delete_table"
        'delete the table
        Dim stablename As String = vm.getstate("tablename","")
        'open the database
        db.OpenWait("alasql", "formutilities")
        alaSQL.Initialize
        Dim rsDelete As AlaSQLResultSet = alaSQL.Delete("tables", "tablename", stablename)
        rsDelete.result = db.ExecuteWait(rsDelete.query, rsDelete.args)
        Log(rsDelete.result)
        vm.CallMethod("RefreshTables")
    End Select
End Sub

So on confirm_ok, we detect the process we are running and then read the table name to delete and then we execute a delete SQL command and the refresh the table listing.