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..
In BANano_Ready, we open/create the database. This creates an indexeddb under developer console > applications.
2. Creating the tables
We need two tables for this exercise one to store form information and the other for the elements.
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.
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.
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.
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.
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
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 BANanoSQLIn 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 Sub4. 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 SubIn 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 Sub6. 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 SubSummary
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
