B4J Tutorial [BANanoWebix] Creating the Form Designer CRUD Backend BANanoSQL DB

Mashiane

Expert
Licensed User
Ola

The BANanoWebix FD (Form Designer) uses BANanoSQL as a backend db to temporary store form and element properties. The designer is created in such a way that the attributes stored in it are never permanent but just a gateway for one to design each form they need.

One creates a new form by erasing existing definitions from the database. So we thought to do this we could use BANanoSQL as a backend using BANanoSQLUtils. This module has now been included as part of BANanoWebix.

1. Database Creation.

In process globals we define the bananosql variable..

B4X:
Private SQL As BANanoSQL
In BANano_Ready, we open/create the database. This creates an indexeddb under developer console > applications.

B4X:
SQL.OpenWait("fd","fd")
2. Creating the tables

We need two tables for this exercise one to store form information and the other for the elements.

B4X:
'create forms table
    Dim tbl As Map = CreateMap()
    tbl.Put("id", BANanoSQLUtils.DB_STRING)
    tbl.Put("json", BANanoSQLUtils.DB_STRING)
    Dim qry As String = BANanoSQLUtils.CreateTable("forms", tbl, "id", "")
    SQL.ExecuteWait(qry,Null)
    'create items table
    Dim els As Map = CreateMap()
    els.Put("id", BANanoSQLUtils.DB_STRING)
    els.Put("json", BANanoSQLUtils.DB_STRING)
    els.Put("tabindex", BANanoSQLUtils.DB_INT)
    els.put("parentid", BANanoSQLUtils.DB_STRING)
    Dim qry As String = BANanoSQLUtils.CreateTable("items", els, "id", "")
    SQL.ExecuteWait(qry,Null)
After the tables are created, we initialize the page to show the UX.

3. Loading the form and existing elements

If there are existing elements already saved, we want these to be loaded to the tree for the form. We call RefreshTree. This runs a select from the elements table and loads each one.

B4X:
'refresh the tree whenever an item is saved
Sub RefreshTree
    'clear tree
    pg.ClearAll("tree")
    'add form element
    pg.AddNode("tree", "", "form", "Form", "", pg.EnumWixIcons.Folder,"","",True)
    'open items and load to the tree
    rec = BANanoSQLUtils.SelectWhere("items", Array("*"), CreateMap("parentid":"form"), Array("id"))
    qry = rec.Get("sql")
    args = rec.Get("args")
    result = SQL.ExecuteWait(qry,args)
    For Each item As Map In result
        Dim itmID As String = item.Get("id")
        pg.AddNode("tree", "form", itmID, itmID, "", pg.EnumWixIcons.FileIcon,"","",False)
    Next
    pg.Refresh("tree")
End Sub
4. After the elements are loaded, when an element is selected, a table read needs to happen to get the record and then display it in the designer and also the property bag. This happens on tree_clickwait.

B4X:
Sub tree_clickwait(recid As String)
    Select Case recid
    Case "form"
        'we have clicked a form
        dForm.BuildBag(pg, propBag)
        ' read record from db
        rec = BANanoSQLUtils.SelectWhere("forms", Array("*"), CreateMap("id":"form"),Array("id"))
        qry = rec.Get("sql")
        args = rec.get("args")
        result = SQL.ExecuteWait(qry,args)
        If result.Size = 0 Then
        Else
            'read from db and update property bag
            record = result.Get(0)
            json = record.Get("json")
            rec = pg.Json2Map(json)
            pg.SetValues("propbag", rec)
        End If
        
        'Dim m As Map = BANano.GetSessionStorage("form")
    Case Else
        rec = BANanoSQLUtils.SelectWhere("items", Array("*"), CreateMap("id":recid),Array("id"))
        qry = rec.Get("sql")
        args = rec.Get("args")
        result = SQL.ExecuteWait(qry,args)
        If result.Size = 0 Then
        Else   
            'read record to assign to property bag
            record = result.Get(0)
            json = record.get("json")
            rec = pg.Json2Map(json)
            Dim v As String = rec.get("view")
            v = v.ToLowerCase
            DrawPropBag(v)
            pg.SetValues("propbag", rec)
            Dim m As Map = CreateView(rec)
            PreView(m)
        End If
    End Select
End Sub
In the future when one selects the form, a form will be created with all the elements under it. For now selecting each element ensures a read happens using the id of the element, draws the property bag and then executes the preview. Records are stored in JSON in both form and elements tables.

5. Creating elements

To design elements, one needs to select an element from the sidebar, this is generated as a preview and then the property bag updated. One then updates the needed properties and to keep the element, needs to save it. This happens with prop_save.

Due to the small db that this is, a read to check the existence of the record happens, if a record exist using the id, the record is updated else a new record is added.

B4X:
'save the item
Sub prop_saveWait
    'get the property bag
    Dim prop As Map = pg.GetValues("propbag")
    Dim v As String = prop.Get("view")
    Dim i As String = prop.Get("id")
    Dim p As String = prop.GetDefault("parentid","")
    Dim idx As String = prop.getdefault("tabindex","0")
    json = BANano.ToJson(prop)
    '
    i = i.tolowercase
    '
    Select Case v
    Case "Form", "form"
        'does the record exist, if not add it
        rec = BANanoSQLUtils.SelectWhere("forms", Array("*"), CreateMap("id":i),Array("id"))
        qry = rec.Get("sql")
        args = rec.get("args")
        result = SQL.ExecuteWait(qry,args)
        'new record
        rec = CreateMap()
        rec.Put("json", json)
        rec.Put("id", i)
        If result.size = 0 Then
            record = BANanoSQLUtils.Insert("forms", rec)
            qry = record.Get("sql")
            args = record.Get("args")
            affected = SQL.ExecuteWait(qry,args)
            pg.Message_Success(affected & " record(s) affected!")
        Else
            'update form record   
            record = BANanoSQLUtils.UpdateWhere("forms",rec,CreateMap("id":i))
            qry = record.Get("sql")
            args = record.Get("args")
            affected = SQL.ExecuteWait(qry,args)
            pg.Message_Success(affected & " record(s) affected!")
        End If
    Case Else
        'preview the item on designer   
        Dim m As Map = CreateView(prop)
        PreView(m)
        'save record to db, does it exist
        rec = BANanoSQLUtils.SelectWhere("items", Array("*"), CreateMap("id":i), Array("id"))
        qry = rec.Get("sql")
        args = rec.Get("args")
        result = SQL.ExecuteWait(qry,args)
        rec = CreateMap()
        rec.Put("json", json)
        rec.put("parentid", p)
        rec.Put("tabindex", idx)
        rec.Put("id", i)
        If result.Size = 0 Then
            'item does not exist
            record = BANanoSQLUtils.Insert("items", rec)
            qry = record.Get("sql")
            args = record.Get("args")
            affected = SQL.ExecuteWait(qry,args)
            pg.Message_Success(affected & " record(s) affected!")
        Else
            'item exist, update it
            record = BANanoSQLUtils.UpdateWhere("items",rec,CreateMap("id":i))
            qry = record.Get("sql")
            args = record.Get("args")
            affected = SQL.ExecuteWait(qry,args)
            pg.Message_Success(affected & " record(s) affected!")
        End If
    End Select
    'refresh tree
    RefreshTree
End Sub
6. Deleting an element

Assuming that an element is no longer needed on the form, a delete method for the element is available. This is un-doable as it removes the record from the elements table.

B4X:
'delete the property
Sub prop_delete
    Dim rp As Map = pg.GetValues("propbag")
    delID = rp.Get("id")
    If delID = "" Then Return
    '
    Dim confirmresult As Boolean = False
    Dim cb As BANanoObject = BANano.CallBack(Me,"deleteprop",Array(confirmresult))
    pg.Confirm(cb, "Confirm Delete", "Are you sure that you want to delete this item? You will not be able to undo your changes. Continue?")
End Sub

Sub deleteprop(confirmresult As Boolean)
    If confirmresult = False Then Return
    Dim delmap As Map = BANanoSQLUtils.DeleteWhere("items",CreateMap("id":delID))
    qry = delmap.Get("sql")
    args = delmap.Get("args")
    affected = SQL.ExecuteWait(qry, args)
    pg.Message_Success(affected & " record(s) affected!")
    RefreshTree
End Sub
Summary

The steps above demonstrate how the same BANanoSQLUtils module that has been used in other UX frameworks we have used has been used in BANanoWebix for backend functionality. The same process to SELECT, INSERT, UPDATE and DELETE records applies and has been easily implemented without any changes.

Ta!

Get BANanoSQLUtils inside the BANanoWebix source code
 

Mashiane

Expert
Licensed User
looks great but WEBIX is not free and the license fee is very high.
We know hey, however, if you want to write projects for internal use and also open source projects, you can use the GPL license. For open source the expectation is for you to distribute the source code. BANanoWebix is open source and for now we will be using it internally.

I think I like the fact that they dont expect you to pay the whole amount in full also and have options for installments.

In other news, I had noted this when I first started Webix on https://www.b4x.com/android/forum/threads/bananowebix-an-opensource-webix-ux-wrapper-for-banano.106703/#content

Thanks hey!
 
Top