B4J Library [Web] Sithaso SDUIPocketBase - Use PocketBase BackEnd in your BANano Projects

Hi Fam

Please find attached a pocketbase class based on the PocketBase JavaScript SDK

In your BANano project, you need to add this line in App Start

BANano.Header.AddJavascriptFile("pocketbase.umd.js")

You declare a variable...

Private pb As SDUIPocketBase

Then you initialize it

pb.Initialize(Me, "pb", "http://127.0.0.1:8090", "projects")

For any CRUD related actions, you need to define the schema. This tells the class the field names and what types are they. For example

pb.SchemaAddText(Array("id", "name"))

There are various methods to add the schema fields.

Related Content

[Web] Mastering Pocketbase: Your Ultimate Guide to a Flawless SQLite WebServer Install on Windows!

Hi Fam Download PocketBase is a SQLite Webserver with some powerful functionality. Whilst it provides functionality to be a database, it also has a nice UI to manage that database. You can host it locally and also run it on a VPS. It does not support shared hosting. 1. Download and unzip this...

www.b4x.com

[BANano] Using PocketBase (Firebase Alternative) for your Apps.

Hi there. Update: This class is based on the JavaScript SDK available here, https://github.com/pocketbase/js-sdk REST API calls are dont internal to the library, if you want to use pure REST API, use BANanoFetch, the documentation should be easy to follow. I got to test the "Open Source...

www.b4x.com

You can also perform a search to get all PocketBase related content in the forum

 

[Mashiane]

SELECT ALL RECORDS

You can select all records in your table

BANano.Await(pb.SELECT_ALL)
Do While pb.NextRow
Dim rec As Map = pb.Record
Dim sid As String = pb.GetString("id")
Loop

We have various methods like GetBool, GetDouble, GetInt etc.

This will return all records with all fields in your table. You can limit the column names retrieved by specifiying which ones to return before the SELECT_ALL clause.

For example, I only want these named fields to be returned per record from my table. So I use ADD_FIELDS before SELECT_ALL

pb.ADD_FIELDS(Array("idnumber", "signature", "signature1","signature2","signature3","signature4","signature5"))
BANano.Await(pb.SELECT_ALL)

 

[Mashiane]

SELECT WHERE

At times you want to select records that meet a particular criteria.

The first variant returns a List of records meeting your criteria. For example.

SELECT_WHERE

pb.CLEAR_WHERE
pb.ADD_WHERE_STRING("attractive", "=", "true")
pb.ADD_ORDER_BY("attrname")
Dim result As List = BANano.Await(pb.SELECT_WHERE)
For Each record As Map In result
pb.EstablishFileUrl1(record, "fileobj", "fileurl")
Dim sid As String = record.Get("id")
Next

NB: If your field type is a string, you need to use .ADD_WHERE_STRING, else use .ADD_WHERE if the type is Int, or Boolean. In this case our column name "attractive" is a string type in the database.

The second variant uses an internal list that you can loop on.

SELECT_WHERE1

pb.CLEAR_WHERE
    pb.ADD_WHERE_STRING("provinceid", "=", sprovince)
    pb.ADD_FIELDS(Array("idnumber", "signature", "signature1","signature2","signature3","signature4","signature5"))
    If bWaitingList Then
        pb.ADD_WHERE_STRING("waitinglist", "=", "true")
    End If
    banano.Await(pb.SELECT_WHERE1)
    
    Do While pb.NextRow
        Dim ssignature As String = pb.GetString("signature")
        Dim ssignature1 As String = pb.GetString("signature1")
        Dim ssignature2 As String = pb.GetString("signature2")
        Dim ssignature3 As String = pb.GetString("signature3")
        Dim ssignature4 As String = pb.GetString("signature4")
        Dim ssignature5 As String = pb.GetString("signature5")
        Dim sidnumber As String = pb.GetString("idnumber")

...

'do whatever

'...
Loop

 

[Mashiane]

SELECTING RECORDS BETWEEN A RANGE ON THE SAME COLUMN

At times you want to select records between a range from the same column. For example, records between the first and last day of the month.

Internally the WHERE clauses use a map, so to ensure that this works, one can suffix the column name. The suffix can be anything and is only relevant to make the column unique. Only the first part before the . (dot) is used internally.

pb.CLEAR_WHERE
    pb.ADD_WHERE_STRING("checkindate.start", ">=", sstartdate)
    pb.ADD_WHERE_STRING("checkindate.end", "<=", senddate)
    Select Case provinceid
    Case "all"
    Case Else
        pb.ADD_WHERE_STRING("provinceid", "=", provinceid)
    End Select
    banano.Await(pb.SELECT_WHERE1)

In the first spec, we uses checkindate.start, a unique internal map variable key for the start date and for the end date we used checkindate.end

Our internal query will pick the column name to be checkindate on both instances.

 

[Mashiane]

CREATING RECORDS

To create records you can pass an already existing map or define the fields individually with setfield. If you do not specify the "id" field name value, PocketBase will allocate a unique value on the id by itself.

The id of each record should be 15 characters long or less but now more. It can be numeric.

NB: The table schema should be specified first with SchemaAdd??? to ensure that the values are cast properly when saving to the table.

'generate the next id
xtitle = pb.NextID
'prepare internally
pb.PrepareRecord
pb.SetField("id", xtitle)
pb.SetField("name", svalue)
'only return the id column and not all columns
pb.GetAffectedID
banano.Await(pb.CREATE)

The .CREATE call will return the new record added with all the columns in the table. To limit it to only return some fields, you can use .ADD_FIELDS and specify which columns to return.

In the case above, using .GetAffectedID, we are calling an internal function to only return the "id" column of the created record and not the complete record.

To use an existing Map, just call .SetRecord(MapName) and there will be no need to call .SetField for each field.

At times I want to know if a record was indeed created...

Dim newid As String = banano.Await(pb.CREATE)
    If newid <> "" Then
        app.ShowSwalSuccessNotification("The Programme has been created!",3000)
    Else
        app.ShowSwalError("The Programme could not be created, please try again!")
    End If

 

[Mashiane]

UPDATING RECORDS

To update an existing record, you need to know its unique "id" value.

In this example I am getting the "id" column value based on a unique idnumber field and then use that to update the record

'find if record exists by id
        Dim bid As String = banano.Await(pb.GET_ID_BY_FIELD("idnumber", sidnumber))
        bCnt = banano.parseInt(bCnt) + 1
        ben.Put("id", bid)
        ben.Put("status", "Dropped Out")
        ben.Put("active", False)
        ben.Put("signature", "")
        ben.Put("signature1", "")
        ben.Put("signature2", "")
        ben.Put("signature3", "")
        ben.Put("signature4", "")
        ben.Put("signature5", "")
        ben.Put("signatureinitials", "")
        pb.SetRecord(ben)
        pb.GetAffectedID
        banano.Await(pb.UPDATE)

If you already know the "id" value of the record, there is no need to call GET_ID_BY_FIELD.

By executing .GetAffectedID, I want the update call to only return the "id" column of the updated record. If I removed this, it will read all the columns for the affected updated record, which is un-necessary in this case.

At times I want to know if a record was indeed updated.

Dim newid As String = banano.Await(pb.UPDATE)
        If newid <> "" Then
            app.ShowSwalSuccessNotification("The Attendance: Check Out has been saved!",3000)
            mdlbeneficiaryattendance.Hide
        Else
            app.ShowSwalError("The Attendance: Check Out could not be saved, please try again!")
            Return
        End If

 

[Mashiane]

READING RECORDS

To read a record from the DB, you use its "id" column value and also are able to read a record by a unique column value.

Dim up As Map = banano.Await(pb.READ_BY_STRING("idnumber", sidnumber))

This returns a map of the record

Here I am reading a record from the DB using the idnumber column.

If you know the "id" value for example, after calling GET_ID_BY_FIELD, you can also just execute a call like this.

banano.Await(pb.READ(sid))
    Dim sid As String = pb.GetString("id")
    Dim sprovinceid As String = pb.GetString("provinceid")
    Dim sidnumber As String = pb.GetString("idnumber")
    Dim saccountholder As String = pb.GetString("accountholder")
    Dim sbankname As String = pb.GetString("bankname")
    Dim sbranchcode As String = pb.GetString("branchcode")
    Dim saccountnumber As String = pb.GetString("accountnumber")
    Dim semailaddress As String = pb.GetString("mobilenumber")
    Dim smobileserviceprovider As String = pb.GetString("mobileserviceprovider")
    Dim bagree As Boolean = pb.GetBoolean("agree")
    Dim blocked As Boolean = pb.GetBoolean("locked")

NB: Reading records like this returns all the columns in the table for the record. You can limit the columns returns to only the ones you want by executing

.CLEAR_WHERE
.ADD_FIELDS(array())

Before calling .READ_BY_STRING / .READ

 

[Mashiane]

DELETING RECORDS

To delete a record, one uses the "id" value of the record.

You can also delete multiple records by using a loop.

For Each k As String In allBanks.Keys
        Dim bid As String = allBanks.Get(k)
        Dim bDelete As Boolean = banano.Await(pb.DELETE(bid))
        If bDelete Then
            dCnt = banano.parseint(dCnt) + 1
        End If
    Next

When I want to know whether a record was deleted...

Dim b As Boolean = banano.Await(pb.DELETE(uid))
        If b Then
            banano.Await(app.ShowSwalSuccessWait(sfullname, "The beneficiary profile was deleted successfully.", "Ok"))
        Else
            banano.Await(app.ShowSwalErrorWait(sfullname, "The beneficiary profile could NOT be deleted, please try again later.", "Ok"))
            Return
        End If

 

[Mashiane]

Other things you can do....

1. User Management & Authentication.
2. Subscriptions

Etc.

NB: This class is internal to SithasoDaisy