B4J Library BANanoSQLiteR - Distributing and accessing an existing SQLite Databases - Part 2

Ola

Download

Part 1 is here.

At some stage we explored how one can distribute / embed SQLite database on their BANano app. These databases are mainly for read only access purposes where one just wants to display data.

Here we explore part 2 of the tutorial, however now made into a library. This does not depend on PHP but just two js libs, sql.js and filesave.js.

We are logging everything the demo does to console.log.



In this exercise, we open an existing database. Whatever changes we make to it will not be persisted, unless of course we download the file. We might even be able to use this to backup a BANanoSQL database.

1. We will open the database. Ensure the database is loaded under your Files tab.
2. We will create a table called users with auto increment fields.
3. We will add some records to the users table.
4. We will select and view all records from the users table.
5. We will update an existing record.
6. We will delete an existing record

We will explore how we can create an in-memory db and then backup an existing BANanoSQL db to it and then download the content.

Ta!

 

[Mashiane]

1. We define a global database variable. We will execute a command here to open the database.

Sub Process_Globals
    Private BANano As BANano
    Private SQLite As BANanoSQLiteR
End Sub

2. On BANano_Ready, lets open the database, the database was added via the Files tab and will thus be sitting on the assets folder.

Sub BANano_Ready
    SQLite.OpenDatabase(Me, "sqlite_opened", "./assets/chinook.db")
End Sub

3. When the database is opened, we want to run some CRUD, but we will create a new table here called users and then manipulate records inside it,

Sub sqlite_opened
    Log("sqlite opened")

 

[Mashiane]

4. Lets' create the users table, you will be familiar with the syntax from BANanoConnect

The primary key id, is an auto-increment primary key.

Log("initialize 'users' table")
    SQLite.Initialize("users", "id")
    SQLite.SchemaAddInt(Array("id"))
    SQLite.SchemaAddText(Array("firstname", "lastname", "telephone"))
    Log("create 'users' table")
    SQLite.SchemaCreateTable

In the console.log, we can see that our table is created when we run the following command to get all existing tables.

'get the list of tables
    Log("list available tables")
    SQLite.TableNames
    Log(SQLite.Result)

 

[Mashiane]

5. Now that we have created the users table, lets add some records to it. We will add 2 records. We have to define the data types for the fields, this is just for consistency,

We have excluded our primary key id for the additions as this will be added by our in-memory backend.

'add some records
    Log("add a record")
    Dim Record As Map = CreateMap()
    Record.put("firstname", "Anele")
    Record.Put("lastname", "Mbanga")
    Record.put("telephone", "987654321")
    'define data types
    SQLite.ResetTypes
    SQLite.AddIntegers(Array("id"))
    SQLite.AddStrings(Array("firstname", "lastname", "telephone"))
    SQLite.Insert1(Record)
    '
    Log("add another record")
    Dim Record As Map = CreateMap()
    Record.put("firstname", "Ndim")
    Record.Put("lastname", "Mbanga")
    Record.put("telephone", "0123456789")
    'define data types
    SQLite.ResetTypes
    SQLite.AddIntegers(Array("id"))
    SQLite.AddStrings(Array("firstname", "lastname", "telephone"))
    SQLite.Insert1(Record)

We then run a query to select all records in the users table. This will just indicate if our addition was successful.

'get added records
    Log("get all records")
    SQLite.SelectAll(Array("*"), Array("firstname","lastname"))
    Log(SQLite.result)

 

[Mashiane]

6. Remember, we initially ran this command.

SQLite.Initialize("users", "id")

So the active table us users with primary key id.

We now need to read a record. We know one of the primary keys is 1. Let's perform a read from the users table and show it.

'read a record
    Log("read user(1)")
    SQLite.Read(1)
    Log(SQLite.result)

Let's also run another variant of a select and specify our own parameters.

'select where & define data types
    Log("select user firstname=Ndim")
    SQLite.ResetTypes
    SQLite.AddIntegers(Array("id"))
    SQLite.AddStrings(Array("firstname", "lastname", "telephone"))
    Dim sw As Map = CreateMap()
    sw.Put("firstname", "Ndim")
    SQLite.SelectWhere(Array("*"), sw, Array("="), Array("firstname"))
    Log(SQLite.Result)

 

[Mashiane]

7. Now we want to update one of the existing records in the table. Remember, the data we maintain is never permanently persisted, until we can download the file.

Let's update the telephone number and retrieve all records to see if the changes took effect.

'update a record
    Log("update 'users' record(1) telephone based on primary key")
    SQLite.ResetTypes
    SQLite.AddIntegers(Array("id"))
    SQLite.AddStrings(Array("firstname", "lastname", "telephone"))
    Dim Record As Map = CreateMap()
    Record.put("telephone", "11111111")
    SQLite.Update1(Record, "1")

And then we select all records in the users table.

'view records after update
    Log("see all users after update")
    SQLite.SelectAll(Array("*"), Array("firstname","lastname"))
    Log(SQLite.result)

 

[Mashiane]

8. Now that we have been able to Create, Read and Update records, its time we can Delete some.

Lets delete a record from the users table and then select existing records just to see if the record was deleted!

Log("delete a record based on primary key field")
    SQLite.ResetTypes
    SQLite.AddIntegers(Array("id"))
    SQLite.AddStrings(Array("firstname", "lastname", "telephone"))
    SQLite.Delete("1")
    '
    Log("see all users after delete")
    SQLite.SelectAll(Array("*"), Array("firstname","lastname"))
    Log(SQLite.result)

We deleted the record with primary key 1 after it was updated.

 

[Mashiane]

9. Now lets execute any normal SQL command we want.

We will select records from an existing table called Album. We then close the database when done. Closing the db is very crucial to free up the memory.

Log("execute own query")
    SQLite.Execute("select * from Album")
    Log(SQLite.result)
 
    'close the database
    SQLite.close[/close]

[ATTACH type="full"]94581[/ATTACH]

 

[alwaysbusy]

I do appreciate the work you put into those BANano DB libraries, but is there any way you can make clear what the difference is between these libraries (BANanoSQLite, BANanoSQLiteR, BANanoConnect (SQLite, MSSQL, MySQL), BANanoMSSQL, BANanoMySQL,...) as it is getting mighty confusing which one someone should use. Are any obsolete/depreciated/not further developed?

I was under the impression you bundled everything to do with DB in the BANanoConnect library which I thought was a great idea, or am I wrong here?

 

[Mashiane]

Noted with thanks, in my previous post of BANanoConnect where I consolidated these into single library, I mentioned the quote below. This should provide green lights in terms of what each class of the library does.

BANAnoAlaSQLE - CRUD functionality for BANanoSQL
BANanoMSSQLE - CRUD functionality for MSSQL (uses PHP)
BANanoMySQLE - CRUD functionality for MySQL (uses PHP)
BANanoSQLite - CRUD functionality for SQLite (uses PHP)

With this library, one uses an already existing SQLite database for READ ONLY and this library unlike BANanoConnect which is just pure code, is dependent on js files.

 

[alwaysbusy]

Thanks I read that but because you now have a new one SQLiteR, I thought the SQLite one in BANanoConnect didn't work anymore.

So in the future if someone starting with BANano asks me a DB lib to use other than the default BANanoSQL, I can point them to BANanoConnect which includes everything they need and they can ignore any of the other libs, right?

Wouldn't it be a good idea then to post a link in all the other posts to BANanoConnect and say they are obsolete and replaced by BANanoConnect?

 

[Mashiane]

So in the future if someone starting with BANano asks me a DB lib to use other than the default BANanoSQL, I can point them to BANanoConnect which includes everything they need and they can ignore any of the other libs, right?

Yep, and already last week I updated all the old threads with a link to point people to BANanoConnect as the main library.

 

[alwaysbusy]

Ok, clear! But I think you may have missed the link in de MSSql one, which I read just before posting ;-) I haven't checked the other ones. https://www.b4x.com/android/forum/threads/bananomssql-pdo-crud-class-for-mssql.110015/#content

 

[Mashiane]

you may have missed the link in de MSSql one

Thanks key, I guess I need thicker spectacles, lol. Let me do another sweep. Appreciated!

 

[Mashiane]

@alwaysbusy. I just remembered something. What if one can on their project, File > Add Files, and select an existing SQLite db. On Build, BANano picks this up, converts it into json/js and makes that part of the project. When one starts the WebApp, if the BANanoSQL db does not exist, its created. Users can then persist the data directly using BANanoSQL. The actual file though does not become part of the assets file as it has been "consumed". The assumptions here of course is that these will come with already predefined data, or perhaps not. #Just a thought.

 

[alwaysbusy]

I don't think this should be part of the BANano core engine as I like to keep it as abstract from any app logic as possible. The programmer can then write this himself how he sees fit.

I see there is some kind of sqlite 2 indexeddb already available in alaSQL: https://github.com/agershun/alasql/wiki/SQLite
I read people have mixed results with this so you will need to investigate it more.

So the strategy could be:
1. check if the database exists on the Browser side
2. If not, fetch the sqlite database
3. execute the attach command to convert the sqlite DB to an indexeddb, e.g.

I haven't done any tests with this.

Another way would be, as this is a one-time-thing to prepare the data anyways, to just export the data from sqlite to csv: https://www.sqlitetutorial.net/sqlite-tutorial/sqlite-export-csv/
So in this case the strategy would be:
1. check if the database exists on the Browsers side
2. if not, create it with "CREATE" queries (you can dump these queries from the sqlite database, and make the changes so they are alaSQL compatible)
3. fetch the data from the csv file
4. Execute a number of "INSERT" queries to append the data

I personally prefer to have an alaSQL on the browser side and a jServer+MySQL on the back-end side. With BANanoServer (which is a wrapped jServer) it is then a 100% B4X solution and you never have to leave the B4X environment (in fact, it is all done in one IDE). One can then write a sync between them if the app is online: so offline as a PWA it uses the alaSQL database. When it becomes online, you can sync the data between them using a B4J/BANano REST API (fetch new data from the server, update the server with data entered/changed/deleted when offline).

The key in such a sync is using UUIDs for IDs in your record instead of numbers.

In case of such a design, you can then use this strategy:
1. check if the database exists on the Browsers side
2. if not, create it with "CREATE" queries
3. use the REST API to fill the alaSQL database with initial data

Alwaysbusy

 

[Mashiane]

Sounds cool. I need to learn BANAnoServer stuff, please consider doing a simple CRUD example for us with any of the dbs. Thanks.

 

[asales]

I compiled this lib (to try embedded a SQLite database), but I get this error when I run the demo project:

Starting to transpile...
Building C:\laragon\www\BANanoSQLiteDemoR\scripts\app.js
Loading library: C:\B4J\Library\bananosqliter.xml
C:\B4J\Library\bananosqliter.js is missing!
C:\B4J\Library\bananosqliter.dependsOn is missing!
java.io.FileNotFoundException: C:\B4J\Library\bananosqliter.dependsOn