[SithasoDaisy5] Creating a SQLite CRUD WebApp for Shared Hosting Deployment

Mashiane

Expert
Licensed User
Longtime User
Hi Fam

Download

One app, a dozen lessons: reading the SithasoDaisy5 SQLite Address Book

1794573979155.png


Most sample apps teach you one thing: how to insert a row. You copy the insert, you change the table name, and you move on. Then you try to build something real and discover that the hard parts were never the insert — they were the lookups, the validation, the report, the dashboard, the thing that keeps your build from deleting the database.

This address book is worth reading slowly for exactly that reason. It is small enough to hold in your head — four pages, three tables, four libraries — but it carries most of the patterns a working web app needs. It is less a demo than a pattern catalogue with a UI attached.

Everything below is drawn from the actual project source, so you can open the modules side by side.

1. What the app is

Four pages, reachable from a slide-out drawer:

• Dashboard — three counter cards, a gender pie chart, a letter-distribution column chart.
• Contacts — the full record: name, mobile, telephone, email, category, gender, address, province, postal code.
• Categories — a lookup table with a colour per category.
• Provinces — a plain lookup table.

The data lives in SQLite, served by sithasobase, a small PHP REST layer (sithasobase/php/api.php) that maps table names to endpoints. The UI is SithasoDaisy5 (daisyUI + Tailwind components) driven from BANano, which transpiles your B4J code into JavaScript. The B4J project itself is JavaFX only because that is the host that compiles the app and opens your browser; nothing B4J-flavoured ever reaches the client.

Four libraries do all the work: banano, sithasodaisy5, plus jcore and jfx for the host.

2. The two-phase lifecycle (the part that trips up desktop developers)

If you come from B4J desktop, you expect AppStart to be where your app begins. In BANano it is where your app is built. The code you want to run — the code that touches the DOM — starts in BANano_Ready.

Code: Copy
Sub AppStart (Form1 As Form, Args() As String)
    BANano.Initialize("BANano", AppName, DateTime.now, AppName)
    BANano.Header.Title = AppTitle
    ...
    BANano.Build(Publish)
End Sub

Sub BANano_Ready
    Dim env As Map = BANano.Await(BANano.GetFileAsJSON("./assets/env.json?" & DateTime.Now, bfo))
    ApiKey = env.Get("apikey")
    ServerURL = env.Get("apiurl")
    ...
    pgIndex.Initialize
End Sub

AppStart configures the header, the PWA icons, the transpiler options, then calls Build. BANano_Ready is the first frame of your application's life in the browser.

Two things in BANano_Ready are worth stealing outright. It reads configuration from a file, not from the build. env.json holds the API URL, host, driver, and key. Republish to a different server and you edit one JSON file instead of recompiling. It builds a version gate. env.json carries a version string. If it no longer matches the compiled Version, the app reloads itself:

Code: Copy
If AppVersion <> "" Then
    If Version <> AppVersion Then
        BANano.Location.Reload(True)
        Return
    End If
End If

That is a two-line answer to "why are my users seeing last week's JavaScript?" — the question every web developer eventually asks.

3. The shell: a layout, a drawer, and a naming convention

baselayout.bjl
is where the furniture lives: a drawer, a navbar that owns the hamburger, a pageview container for content, a right drawer and a bottom drawer you can open later if you need them. pgIndex.Initialize wires it up:

Code: Copy
BANano.Await(App.Initialize(Me))
BANano.LoadLayoutAppend(App.Here, "baselayout")
appnavbar.Title = "My Address Book"
BANano.Await(AddDatabaseSchemas)
BANano.Await(App.UsesFontAwesome)
BANano.Await(App.UsesExcel)
BANano.Await(App.UsesColorWheel)

Note the Uses* calls. Font Awesome, ExcelJS, the colour wheel, the date picker and the chart library are not in the initial download — they are fetched on demand by UsesFontAwesome, UsesExcel, UsesColorWheel, UsesInfoBox, UsesToastChart. Load what your shell needs up front, load the rest when a page asks for it.

Navigation needs no router. The menu items carry a composite key and the click handler splits it:

Code: Copy
Sub CreateDrawerMenu
    drawermenu.AddItemChild("", "contacts-dashboard", "./assets/gauge-high-solid.svg", "Dashboard")
    drawermenu.AddItemChild("", "contacts-list", "./assets/users-solid-full.svg", "Contacts")
End Sub

Private Sub drawermenu_ItemClick (item As String)
    Dim sprefix As String = App.UI.MvField(item, 1, "-")
    Dim ssuffix As String = App.UI.MvField(item, 2, "-")
    Select Case sprefix
    Case "contacts"
        Select Case ssuffix
        Case "list"
            pgContacts.Action = "L"
            pgContacts.Show(App)
            Return
        End Select
    End Select
End Sub

page-action as a string, MvField to take it apart, a Select Case to dispatch. It costs nothing, it is readable, and it is honest about what a router is: a lookup from a name to a function.

4. Define the schema once

Every page needs to know the shape of the tables. The app declares that in one place, in `pgIndex`:

Code: Copy
Sub AddDatabaseSchemas
    App.AddDataModel("categories", "id", False)
    App.AddDataModelStrings("categories", Array("id","name","color"))

    App.AddDataModel("contacts", "id", False)
    App.AddDataModelStrings("contacts", Array("id","fullname","letter","mobile", ...))
End Sub

Every page then simply says:

Code: Copy
db.SetSchemaFromDataModel(App.DataModels)

One definition, reused by every read, write, count and raw query. When you add a column, you add it here and every page knows about it. This is the single highest-leverage line in the whole project. This can also be configured to be read from the database itself without manual intervention and for demonstration purposes has been left.

5. The list page anatomy

Categories and Provinces are the same page wearing different column names. Once you can read one, you can read all three list pages — and you can generate the fourth. Every one of them starts with the same five beats:

Code: Copy
Public Sub Show(MainApp As SDUI5App)
    App = MainApp
    App.PagePause
    pgIndex.PageViewPaddingTo10px
    App.PageViewToFullScreenHeight(Array("appnavbar"))
    BANano.LoadLayout(App.PageView, "categoriesview")
    BANano.Await(BuildCategoriesTable)
    BANano.Await(BuildCategoriesModal)
    BANano.Await(MountCategories(False))
    App.PageResume
End Sub

Load the layout. Build the table. Build the modal. Mount the data. Resume.

`App.PagePause` and `App.PageResume` bracket the work with a spinner. `BANano.Await` is not decoration: your B4J `Sub` becomes JavaScript, and JavaScript does not block. Every call that crosses the network or waits for the DOM must be awaited, or the next line runs before the first one finishes. If a page "sometimes shows empty data", you are missing an `Await`.

The table is the product. The app does not treat it as a grid of strings; it configures behaviour:

Code: Copy
tblCategories.HasAlphaChooser = False
tblCategories.HasColumnChooser = False
tblCategories.HasEdit = True
tblCategories.HasClone = True
tblCategories.HasDelete = True
tblCategories.BackTooltip = "Go back to Dashboard"

tblCategories.AddColumnColor("color", "Color", "name")
tblCategories.AddDesignerColums
tblCategories.UpdateColumnVisibility
tblCategories.MoveBackButton

On Contacts the same idea goes further: `AddColumnAvatarPlaceholder` for the initials bubble, `AddColumnTitleSubTitle("fullname", "Full Name", "mobile")` to stack a name over a number, `AddColumnEmail` to make the address a mailto link, `HasAlphaChooser = True` with `AlphaChooserColumn = "fullname"`, and `SetColumnHidden` for the fields that exist but rarely need to be seen.

Pagination state survives a round trip. Before any edit, delete or clone the page calls `tblCategories.SaveLastAccessedPage`, and after reloading it calls `ShowLastAccessedPage`. The user stays where they were. That one detail is the difference between an app that feels considered and one that feels like a demo.


#SharingTheGoodness
 
Last edited:
1794574050543.png



6. CRUD: one shape, three verbs

The modal is a state machine with two states and a lot of discipline. `Mode = "C"` for create, `"U"` for update. `AddMode`, `EditMode` and `CloneMode` each set the title, reset the fields, and populate.

Code: Copy
Private Sub AddMode
    Mode = "C"
    mdlCategories.Title = "Add Category"
    BANano.Await(SetCategoriesDefaults)
    BANano.Await(BuildCategoriesFakeData)
    txtId.Value = App.NextID
    mdlCategories.Show
    txtName.Focus
End Sub

Notice the order: defaults first, then fake data, then a fresh id, then show, then focus. Same order every time, in every page, for every entity. Boring is a feature here — you can copy the block, rename the fields, and it works.

Validation is one function that answers one question:

Code: Copy
Private Sub GetCategoriesValid As Boolean
    If txtId.IsBlank Then Return False
    If txtName.IsBlank Then Return False
    If txtColor.IsBlank Then Return False
    Return True
End Sub

The save path is equally predictable: `PrepareRecord`, `SetField` per column, a duplicate check, then `CREATE` or `UPDATE`.

Code: Copy
Case "C"
    Dim existingID As String = BANano.Await(db.READ_ID_BY("name", sName))
    If existingID <> "" Then
        BANano.Await(App.ShowSwalErrorWait("Category Exists", "A record with a matching <b>Name</b> already exists, cannot proceed.", "Ok"))
        Return
    End If
    Dim nid As String = BANano.Await(db.CREATE)
Case "U"
    ' ... same check, but tolerate the record's own id
    Dim nid As String = BANano.Await(db.UPDATE)
End Select

Deletion never happens without a confirmation that names the thing being deleted, built from the row itself:

Code: Copy
Dim sMsg As String = $"<h2 class="text-2xl font-bold mt-2">${toDeleteName}</h2><br>Are you sure that you want to delete this Category?"$
Dim bConfirm As Boolean = BANano.Await(App.ShowSwalConfirmWait("Confirm Delete", sMsg, "Yes", "No"))

And cloning is just an edit that swaps in a new id:

Code: Copy
item.Put("id", App.NextID)

Ids are generated client-side as 15-character strings. No round trip, no sequence, no collision in practice.

1794574085864.png


#SharingTheGoodness
 
Last edited:
1794574160942.png


7. Contacts: where the app gets interesting

Everything above is table stakes. The contacts page is the part to study, because this is where a flat CRUD form becomes a relational app — without a JOIN in sight.

Lookups become maps, maps become selects

The page loads each foreign table once, into a map of id → display name:

Code: Copy
Private Sub LoadCategories
    CategoriesObject.Initialize
    Dim db As SDUIMySQLREST
    db.Initialize(Me, "categories", Main.ServerURL, "categories")
    db.SetSchemaFromDataModel(App.DataModels)
    db.SetSQLiteConnectionApiKey(Main.DBHost, Main.ApiKey)
    db.CLEAR_WHERE
    db.ADD_ORDER_BY("name")
    db.ADD_FIELDS(Array("id","name"))
    BANano.Await(db.SELECT_ALL)
    Do While db.NextRow
        CategoriesObject.Put(db.GetString("id"), db.GetString("name"))
    Loop
End Sub

Then the select is fed from it, and the form gets a proper dropdown of categories — not a text box where the user can invent a new spelling of "Colleague":

Code: Copy
cboCategory.SetOptionsFromMap(CategoriesObject)

The list shows names; the table stores keys

The contacts table stores `category` as an id. Nobody wants to read `asbf7cnmkxwi4de` in a grid. Instead of asking SQL to join, the table asks B4X to translate per row:

Code: Copy
tblContacts.AddColumn("category", "Category")
tblContacts.SetColumnComputeValue("category", "ComputeValueCategory")

Private Sub ComputeValueCategory(item As Map) As String
    Dim scategory As String = item.Get("category")
    Return CategoriesObject.GetDefault(scategory, "")
End Sub

Same trick for province, and for the avatar: `SetColumnAvatarColor("abbreviation", True)` colours each placeholder deterministically, so "Castro Hester" always gets the same bubble.

This is a deliberate trade. You keep a simple REST layer that knows nothing about relationships, and you pay for it with a map lookup per visible row — which is nothing, because you only render a page of ten.

Creating related records without leaving the form

Here is the pattern I would copy first. Both selects carry a `+` button:

Code: Copy
cboCategory.AppendIcon = "./assets/plus-solid.svg"
cboCategory.AppendVisible = True

Click it and you get an input dialog, not a trip to another page:

Code: Copy
Private Sub cboCategory_append (e As BANanoEvent)
    Dim sname As String = BANano.Await(App.ShowSwalInputWait("Add Category", "Please type a new category to add below.", "Save", "Cancel"))
    sname = sname.Trim
    If sname = "" Then Return
    sname = App.UI.ProperCase(sname)

    Dim aid As String = BANano.Await(tblX.READ_ID_BY("name", sname))
    If aid <> "" Then
        cboCategory.Value = aid          ' it already existed: just select it
    Else
        tblX.PrepareRecord
        tblX.SetField("id", tblX.NextID)
        tblX.SetField("name", sname)
        aid = BANano.Await(tblX.CREATE)
        If aid <> "" Then
            BANano.Await(LoadCategories)
            BANano.Await(cboCategory.SetOptionsFromMap(CategoriesObject))
            cboCategory.Value = aid      ' select the new record
        End If
    End If
End Sub

Reuse-if-exists, create-if-not, refresh the list, keep the selection. Three lines of user-facing behaviour that prevent a whole class of duplicate lookup data.

Denormalise what you query

`letter` is derived — the first character of the full name — and it is stored anyway:

Code: Copy
Dim sLetter As String = App.UI.Left(sFullname, 1)

That single column powers the alpha chooser on the contacts table *and* the letter-distribution chart on the dashboard, with a plain `GROUP BY letter`. Classic denormalisation: write once, read cheaply, forever.

A form is a grid

The modal is built in code, but it thinks like the designer:

Code: Copy
mdlContacts.Form.Clear
mdlContacts.Form.AddRows1
mdlContacts.Form.AddColumns12
mdlContacts.Form.AddRows1
mdlContacts.Form.AddColumns2x6
...
BANano.Await(mdlContacts.Form.BuildGrid)

txtMobile.ParentID = mdlContacts.Form.CellID("3", "1")

Row, then column count, then place each control at `CellID(row, column)`. If you have ever laid out a form by hand, this will feel familiar — and unlike absolute positioning, it reflows on a phone.

1794574202434.png
 
Last edited:
1794574253541.png


8. Reports: template in, data out

The export button does not construct a spreadsheet. It opens a spreadsheet that already looks right:

Code: Copy
Dim report As SDUI5ExcelReport
report.Initialize
report.SheetName = "Sheet1"
report.TemplateFile = "./assets/contacts_report.xlsx"
report.ReportFileName = "Contacts Report"
report.StartRow = 3
report.SetRows(Result)
report.AddColumn("A", "fullname")
report.AddColumn("B", "mobile")
...
BANano.Await(report.execute)

`StartRow = 3` means rows one and two of the template keep your logo and title. Column letters are explicit, so the template can reorder, add computed cells, or skip fields without you touching the template's layout.

The contacts report shows the last honest detail: ids are resolved to names *before* the rows are handed over.

Code: Copy
Dim sCategory As String = rec.Get("category")
rec.Put("category", CategoriesObject.GetDefault(sCategory, ""))

Reports are where raw keys become embarrassing. Fix them at the boundary, not in the template.

1794574285907.png
 
Last edited:
9. Dashboard: aggregate on the server, draw in the browser

The dashboard is built the other way round from the list pages — no layout file, everything in code:

Code: Copy
app.UI.ClearByID("pageview")
dashboard.Initialize(Me, "dashboard", "dashboard")
dashboard.ParentID = "pageview"
...
BANano.Await(dashboard.AddComponent)

That contrast is worth noticing. `LoadLayout` when the page is static and you want a designer. `AddComponent` when the page is data-driven or generated. Both are first-class; you are not locked into either.

The counters are one call each:

Code: Copy
Dim tContacts As Int = BANano.Await(db.COUNT_ALL)

And the charts are fed by raw SQL, not by looping over every record in the browser:

Code: Copy
BANano.Await(db.SELECT_RAW("select gender, count(*) as records from contacts group by gender"))
chartgender.Clear
chartgender.AddCategory("Gender")
Do While db.NextRow
    Dim gPerc As String = app.UI.PercentOf(db.GetString("records"), tContacts)
    chartgender.AddSeriesValue(db.GetString("gender"), gPerc)
Loop
chartgender.Refresh

The database does what it is good at; the browser draws what it is good at. The only arithmetic on the client is turning counts into percentages.


10. The small things that make it pleasant to build

These rarely make it into tutorials, and they are the difference between a project you finish and one you abandon.

Fake data on demand.
`SDUI5FakeIT` generates plausible values by field type — `fullname`, `phone`, `email`, `street`, `province`, `hexcolor`. Add mode opens pre-filled:

Code: Copy
fakeStructure.Put("fullname", "fullname")
fakeStructure.Put("mobile", "phone")
fakeStructure.Put("color", "hexcolor")
fakeList = BANano.Await(fd.GetRecordsWithStructure(fakeStructure, 1))

You can click through a form, a table and a chart in seconds without typing a thing. It also makes screenshots for a forum post suspiciously easy.

Protect the folders the build must not touch.

Code: Copy
BANano.TranspilerOptions.DoNotDeleteFolderOnCompilation($"${Publish}\${AppName}\assets"$)
BANano.TranspilerOptions.DoNotDeleteFolderOnCompilation($"${Publish}\${AppName}\sithasobase"$)

`Files/` is published into `assets/`. The PHP API and `data.db` live in `sithasobase/`. Without these two lines, a compile can take your asset folder and your database with it. Scream this from the rooftops.

Unregister service workers before building
(`BANano.UnregisterServiceWorkers`) so a stale worker cannot serve yesterday's app after you deploy.

Layouts and JSON stay in sync.
The project carries IDE macros that run `JsonLayouts.jar` after a save:

Code: Copy
#Macro: After Save, Sync Layouts, ide://run?File=%ADDITIONAL%\JsonLayouts.jar&Args=%PROJECT%&Args=%PROJECT_NAME%

11. Three exercises, if you are following along

1. Fix the asymmetry. Categories and Provinces check for duplicate names on create *and* update. Contacts checks full name, mobile, telephone and email on create but not on update. Make the update path consistent, and notice how much a shared helper would help.
2. Move the search to the server. `HasSearch` filters rows already in memory. With a page of ten, that is fine; with ten thousand contacts, it is not. Add a `WHERE` clause and re-run the query.
3. Extract a repository. The same `db.Initialize` / `SetSchemaFromDataModel` / `SetSQLiteConnectionApiKey` trio appears a dozen times. Wrap it in a class with `SelectAll`, `Read`, `Create`, `Update`, `Delete` and see how much of each page collapses.

Each exercise points at a real tension in the code, which is the most useful thing a sample app can do.
 
12. Running it

1794574344603.png


1. Install Laragon (or any web server with PHP - pdo_sqlite enabled) and put the publish root at `C:\laragon\www`, or change `Publish` in `AppStart` to suit.
2. Get BANano and SithasoDaisy5 from the links in the project's ReadMe region.
3. Copy `sithasobase` into the published app folder. The SQLite file lives at `sithasobase/data/data.db`.
4. Check `Files/env.json` — `apiurl` must point at `sithasobase/php/api.php`.
5. Run the project in the IDE. It publishes, then opens `http://localhost/sqliteaddressbook/index.html`.

Download from Github


13. Honest caveats

- The API key in `env.json` is shipped to the browser. It identifies the app to sithasobase; it does not authorise a *user*. The sign-in hooks in `pgIndex` are commented out. Treat authentication as an exercise, not a feature.
- SQLite behind a PHP bridge is ideal for demos, small internal tools and single-server deployments. It is not a concurrency story. Swap `SDUIMySQLREST` for the MySQL/Postgres setup before you invite a crowd.
- `db.ShowLog = True` is left on in a couple of places from development. Turn it off before you ship; the console gets loud.

Closing

What this app teaches, in the end, is not a component or an API call. It is that the shape of a page can be a contract. `Show` always does the same five things. A modal is always a mode, defaults, a validity check, a save, a reload. Foreign keys always become maps, and maps always become selects. Once you see the contract, a new entity is an afternoon, not a week — and the interesting decisions move to where they belong: what the user should see, and what the database should do.

If you build something with it, post it. That is how the pattern catalogue grows.
 
Last edited:
Back
Top