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

Discussion in 'B4J Tutorials' started by Mashiane, Jul 7, 2019.

  1. Mashiane

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

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

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

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

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

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

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

    Code:
    '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
     
    micro, Johan Hormaza and joulongleu like this.
  2. Axel Moeller

    Axel Moeller New Member

    Hi Mashiane,

    looks great but WEBIX is not free and the license fee is very high.

    BR Axel
     
  3. Mashiane

    Mashiane Expert Licensed User

    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/t...e-webix-ux-wrapper-for-banano.106703/#content

    Thanks hey!
     
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