B4J Tutorial [BANanoWebix] App Creation Process: The BackEnd with BANanoSQL

[Mashiane]

Ola

Part 1

This is part 2 of our App Creation Process. We focus on the backend to store our data. The first backend we will look at is BANanoSQL, then BANanoSQLite & BANanoMySQL. The last two use inline PHP methodology.

In lesson 1 we created our UI, a form named 'form' with 4 textboxes named id,field1,field2,field3. Now we need to link these so that they get saved to the DB. Before we look at the DB infrastructure, lets ensure that the data source targets are linked on the form and we have some events linked to the form.

The link to the underlying data source for each form component is done via the 'name' property of an element. In lesson 1 we did not set any names for our elements but just specified the 'id' property and label.

Step 1: Update the name property of our input elements

For each of the elements, id, field1, field2, field3, update the Details.Name property to be exactly like the id. In the end you will have this code added to each of the elements after you update each element and save it in the property bag.

• ID

id.SetName("id")

• Field 1

field1.SetName("field1")

• Field 2

field2.SetName("field2")

• Field 3

field3.SetName("field3")

Without the name property specified, the .GetValues and .SetValues webix methods for form manipulation will not work.

Step 2: Update the buttons to have events attached to them.

In our form, we added buttons for some CRUD operations.

• New - when clicked its supposed to clear the form contents. The dependency for this is step 1
• Insert - when clicked, the form contents are saved to the database table i.e. INSERT INTO
• Update - when clicked the form contents are sved to the database table using the update method i.e. UPDATE ... WHERE
• Read - when clicked, use the 'id' content and read a record from the database table i.e. SELECT ... WHERE
• Delete - when clicked, use the 'id' content and delete the record from the database table i.e. DELETE ... WHERE

What we need then is to update the .SetClick methods for each button. We already had this code in our form but commented it out. So let's update them..

btnnew.SetClick(BANano.CallBack(Me,"btnnew_click",Null))

btninsert.SetClick(BANano.CallBack(Me,"btninsert_click",Null))

btnupdate.SetClick(BANano.CallBack(Me,"btnupdate_click",Null))

btnread.SetClick(BANano.CallBack(Me,"btnread_click",Null))

btndelete.SetClick(BANano.CallBack(Me,"btndelete_click",Null))

So what we have done here is to ensure that our form contents can be read and saved using the 'name' property of each input element. The second part was to ensure that each time the buttons are clicked, a callback event attached to each event is executed by BANano. This completes our preparations to use BANanoSQL.

 

[Mashiane]

Now, what we have to do is to create the underlying database and table that will store our records. We will use BANanoSQL as a backend, the AlaSQL database that comes built in with BANano. To use the db we need to define a BANanoSQL variable on each code module that will access our database. This means Main (we will create the database and table there) and pgIndex (we will do our CRUD there).

Let's go back to our Form Designer.

On the form designer, click 'Database' on the tree and click 'Save' on the property bag toolbar.

By default, the BANanoSQL database is selected. In the source code section, there is an indication of what to do. Currently there are 3 database types, BANanoSQL, BANanoSQLite and BANanoMySQL. These helper classes are now built inside BANAnoWebix.

So we need to copy the variable to initialize the database on each module. With our BANanoWebixApp opened, we copy the init code for the db to Main and pgIndex.

And in pgIndex

Now that you have defined a variable as BANanoSQL, the BANano library will automatically pick up that you are using the alasql database, however the needed resource for your project for the database is not loaded yet. Before that our project didnt know that we will use BANanoSQL, now it knows and needs a resource.

To have the needed resource loaded, run the project, it will give out an error on the logs. Do not be alarmed. Running the projects adds the needed alasql.min.js file to your projects 'files' list tab. Now all you need to do is 'sync' your resources.'

Click on the Files tab of your project and click Sync. (Bottom of the files tab). As soon as your project is synced, the needed resource is added to the list of files needed for your project. The error above will not be reported again. If there is no reference to BANanoSQL anymore on your project, the alasql.min.js file will be deleted.

Now, let's create the underlying database to store our records.

 

[Mashiane]

Let's create the table in our database.

1. Click on the 'Database' on the tree. We had already clicked save, but if you didnt, click 'Save' on the property bag toolbar
2. On the property toolbar, click the 'Add' button, this will create 'table1' for now let's leave the table as is. Otherwise we can change the name to be what we want.

NB: Its better to change your table name to the real table name you will use for your table here.

When you click 'Save' the table structure and a new field will be added to the model we are creating. The primary field has been named 'id' in this case and is an 'INT'.

Now lets add field1, field2, field3 to the table.

Just like before, the next step is about...

1. Select the parent from the tree
2. Click the add button on the property bag toolbar
3. Change properties of the element
4. Click save.

We will add field1 to field4 this way.

1. Click on 'table.table1' on the tree.
2. In the property bag toolbar, click 'add', 'field1' is added to the property bag.
3. Click 'save' the named field is added to the table.

Do this for field2 and field 3. Now you have something like this..

As you might have noted, our field names are the name as our elements field names. The link between them in our code is the 'name' property of each element we added.

Now when our app starts, if the table does not exist, it needs to be created and ready for adding records.

On the tree, click on 'table.table1' and on the source code section, we have the source code to create and maintain the records in our project. Creating the table should only happen once, when our app starts, so lets copy the part of the code needed to be under BANano_Ready.

After, being updated, your BANano_Ready will look like this..

Sub BANano_Ready()
    db.OpenWait("db", "db")
    'create the table
    Dim newTable As Map = CreateMap()
    newTable.put("field1","STRING")
    newTable.put("field2","STRING")
    newTable.put("field3","STRING")
    newTable.put("id","INT")

    'initialize the helper class
    Dim alaSQL As BANanoAlaSQL
    alaSQL.Initialize
    'generate the create table sql
    Dim rs As AlaSQLResultSet = alaSQL.CreateTable("table1", newTable, "id")
    'execute the create table command
    rs.Result = db.ExecuteWait(rs.query, rs.args)
    
    'build a page and render it to the body of the page
    pgIndex.init("body")
End Sub

Here, we defined db as BANanoSQL.

In BANano_Ready, we open the database / create it if it does not exist with db.OpenWait("db", "db"). This is important.

We then paste the code to create the table. Our project currently has 1 table. If you have used DBUtils before, to create a table, it uses a map object with field names and field types.

We define a variable named alaSQL as BANanoAlaSQL (previously known as BANanoSQLUtils). We initialize this and then execute the statement to create the table. Run the app and then locate your table.

But where is the db and table created?

As soon as you run your app now, a db is created specific to your app as an indexeddb.

IndexedDB is a low-level API for client-side storage of significant amounts of structured data, including files/blobs. This API uses indexes to enable high-performance searches of this data. Sourced from https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_API

I am using Opera, so I went to Developer > Developer Tools > Applications > Storage > IndexedDB and my db> table1 is there ready for CRUD ops.

As we will be creating CRUD ops in our pgIndex module, we also need to ensure that the database is opened there to receive operatons.

In this module, we will not create the table but just open the db for operations. Update the Init method to reflect this..

Sub Init(pgContainer As String)
    db.OpenWait("db", "db")

The next section we will add code that runs each time a button is clicked for New, Insert, Update, Read and Delete.

 

[Mashiane]

Creating Records (copy code from form designer to b4j project)

The code discussed below is accessible when one clicks 'table.table1', the previous table we created.

To create records for this project, one needs to clear the contents of the form. This is achieved by the btnnew_click.

As we have linked the database table to the form using the 'name' attribute of each element, all we need is to call an internal webix method called .Clear.

New - lets copy this code from our form designer to our b4j project. When clicking the new button, this event is fired.

Sub btnnew_click
    'clear the contents of the form
    pg.Clear("form")
End Sub

Insert, when a user has entered the details on the form, the btninsert_click method is fired.

Sub btninsert_click
    'lets validate the form
    Dim bValid As Boolean = pg.Validate("form")
    If bValid = False Then Return
    'insert record to table
    Dim alaSQL As BANanoAlaSQL
    'initialize the helper class
    alaSQL.Initialize
    'Get values from the form
    Dim rec As Map = pg.GetValues("form")
    'save record to the database
    Dim rs As AlaSQLResultSet = alaSQL.Insert("table1", rec)
    rs.Result = db.ExecuteWait(rs.query, rs.args)
End Sub

This has an effect of returning the number of records affected by the transaction.

Reading Records

Reading - the reading of records happens by reading the primary key of the table named 'id'. If one saved the id as 1 or 2 or 3, by entering 1 or 2 or 3 in the ID textbox and clicking the Read button, the details of the record (if already saved) will be read and displayed on the form. The code as per form designer is depicted below. Let's copy this to pgIndex module.

Sub btnread_click
    'get the content of the primary key field
    Dim priValue As String = pg.GetValue("id")
    'read record from table
    Dim alaSQL As BANanoAlaSQL
    'initialize the helper class
    alaSQL.Initialize
    'generate the select where statement
    Dim rs As AlaSQLResultSet = alaSQL.Read("table1", "id", priValue)
    rs.result = db.ExecuteWait(rs.query, rs.args)
    'the record was found, set the values to the form
    If rs.result.size > 0 Then
        Dim rec As Map = rs.result.Get(0)
        'set returned map to form
        pg.SetValues("form", rec)
    End If
End Sub

In the insert method, we added a validate method. For how this works, see the BANanoWebixDemo examples and you can implement your validations.

 

[Mashiane]

Updating records..

The records are updated by primary key, being the 'id' of the record. So whatever is entered on the form, as soon as update is selected, it overwrites the fields applicable for the record.

The generated source code for inserts is also straight forward. This is fired when the btnupdate_click method is executed.

Sub btnupdate_click
    'lets validate the form
    Dim bValid As Boolean = pg.Validate("form")
    If bValid = False Then Return
    'Get values from the form
    Dim rec As Map = pg.GetValues("form")
    'get the primary key
    Dim priValue As String = pg.GetValue("id")
    Dim alaSQL As BANanoAlaSQL
    'initialize the helper class
    alaSQL.Initialize
    'update record in the table
    Dim rs As AlaSQLResultSet = alaSQL.UpdateWhere("table1", rec, CreateMap("id":priValue))
    rs.Result = db.ExecuteWait(rs.query, rs.args)
End Sub

Deleting Records

To delete records, the primary key is used in a where clause.

Let's copy our code from the form designer to the project.

Sub btndelete_click
    'get the primary key
    Dim priValue As String = pg.GetValue("id")
    Dim alaSQL As BANanoAlaSQL
    'initialize the helper class
    alaSQL.Initialize
    'delete record in the table
    Dim rs As AlaSQLResultSet = alaSQL.DeleteWhere("table1", CreateMap("id":priValue))
    rs.Result = db.ExecuteWait(rs.query, rs.args)
End Sub

We decided to update the form for more brevity

Getting all records for lists, grids etc

The BANanoWebixDemo has all the details about creating grids and assing data to it. You can use a method to get all records from the db and then load them to such lists/grids and format the display. Lets add a new button to the form and then execute the code when clicked.

Dim btngetall As WixButton
    btngetall.Initialize("btngetall").SetLabel("Get All")
    btngetall.SetClick(BANano.CallBack(Me,"btngetall_click",Null))
    r1c1.AddColumns(btngetall.Item)

..... 


Sub btngetall_click
    Dim alaSQL As BANanoAlaSQL
    'initialize the helper class
    alaSQL.Initialize
    'update record in the table
    Dim rs As AlaSQLResultSet = alaSQL.SelectAll("table1", Array("*"), Array("id"))
    rs.Result = db.ExecuteWait(rs.query, rs.args)
    Dim json As String = BANano.ToJson(rs.result)
    pg.Message(json)
End Sub

NB: Updated source code on first post

 

[Mashiane]

Summary

In Part 1, we created the UI of our Webix based application. In this part we link BANanoSQL to our form so that we have CRUD functionality to close this topic.

The BANanoWebixDemo app has a long of functionalities about the management of data e.g. grid generation and display including exporting etc. That process will not be part of this tutorial. Perhaps in the future we might add grid creation and column definitions.

Ta!

 

[Mashiane]

Adding a confirm dialog for deletes?

You can add a confirm dialog when a record is being deleted by following this process.

Sub btndelete_click
'draw a confirm dialog
Dim confirmDelete As Boolean = False
Dim cb As BANanoObject = BANano.CallBack(Me,"deletetable1",Array(confirmDelete))
pg.Confirm(cb, "Confirm Delete", "Are you sure that you want to delete this record?")
End Sub

Sub Deletetable1
'get the primary key
Dim priValue As String = pg.GetValue("id")
Dim alaSQL As BANanoAlaSQL
'initialize the helper class
alaSQL.Initialize
'delete record in the table
Dim rs As AlaSQLResultSet = alaSQL.DeleteWhere("table1", CreateMap("id":priValue))
rs.Result = db.ExecuteWait(rs.query, rs.args)
End Sub