B4J Library [BANanoSQLite] SQLiteDB PHP CRUD Class for BANano

Mashiane

Expert
Licensed User
Ola

UPDATE 2020-05-19: Please use this library instead

Update: BANanoSQLite1 Example

If you are opting for MySQL connectivity, BANanoMySQL is available here.

My journey finally has led me to complete a basic wrapper that one can use for PHP SQLite access.

dbfile.png


BANanoSQLite.gif


Initially, we tried...

Distributing and Accessing SQLite Databases

However, the parent of this class here is Exploring and Using PHP and SQLite for your BANano WebApp as this continues on this note.

For reference, one can also look at BANanoMySQL, a class and PHP script for MySQL PHP CRUD functionality.

So in the same light, we explore, INSERT, SELECT, UPDATE and DELETE for SQLite dbs located in the server via PHP. This works just like BANanoMySQL as one needs to indicate the field types that they are processing when running queries.

I am using XAMPP for testing my developments, so first things first.

Open PHP.INI and edit the line to activate sqlite3 functionality...

B4X:
extension=sqlite3
You will also need BANanoSQLite attached in this demo. Let's look at the CRUD statements to complete this task...

Again, we look at parameter based statements to work with our database. We will create a database, create a table, insert some records, select them and delete some.

PHP Prepared Statements

B4X:
function prepareStatement($db, $sql, $types, $values) {
        /* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
        $stmt = $db->prepare($sql);
        $n = count($types);
        for($i = 0; $i < $n; $i++) {
            $param_type = $types[$i];
            $param_value = $values[$i];
            $loc = $i + 1;
            switch($param_type){
                case "s":
                    $stmt->bindValue($loc, '$param_value', SQLITE3_TEXT);
                    break;
                case "i":
                    $stmt->bindValue($loc, $param_value, SQLITE3_INTEGER);
                    break;
                case "d":
                    $stmt->bindValue($loc, $param_value, SQLITE3_FLOAT);
                    break;
                case "b":
                    $stmt->bindValue($loc, $param_value, SQLITE3_BLOB);
                    break;
            }
        }
        return $stmt;
}
This is the function that builds up our prepared statement for SQLite...

The Engine that does everything...

B4X:
function BANanoSQLite($dbname,$data) {
       $db;
    //set the header
    header('content-type: application/json; charset=utf-8');
       $db = new SQLite3($dbname);
    if(!$db) {
          $response = $db->lastErrorMsg();
          $output = json_encode(Array("response" => $response));
          die($output);
    }
    //data Is json, set it As a php variable
    $data = json_decode($data, True);
    //get the command To execute
    $command = $data["command"];
    $sql = $data["sql"];
    $values = $data["args"];
    $types = $data["types"];
    $fields = $data["fields"];
    switch($command){
        Case "select":
            $res = $db->query($sql);
            $rows = Array();
            while($row = $res->fetchArray()) {
                $rows[] = $row;
            }
            $output = json_encode(array("response" => "OK", "data" => $rows));
              echo $output;
            break;
        case "deletewhere":
            //build the prepared statement
            $stmt = prepareStatement($db, $sql, $types, $values);
            $res = $stmt->execute();
            $affRows = $db->changes();
            $output = json_encode(array("response" => "OK", "data" => $affRows));
              echo $output;
            break;
.....
.....
.....
As noted above, when we call BANanoSQLite, the db, if it does not exist is created, if it does, it gets opened...

Let's look at the usage...

We define a few parameters..

B4X:
Dim sqlite As BANanoSQLite
    sqlite.Initialize
    dbName = $"${AppName}.db"$
Let's look at some other things...

How to use this class?

In Process Globals I have defined a variable..

B4X:
Public AppName As String = "BANanoSQLiteDemo"
In AppStart of your BANanoApp, define your php paramaters for your SQLite database connection..

B4X:
'set php settings
    BANano.PHP_NAME = "bananosqlite.php"
    BANano.PHPHost = $"http://localhost/${AppName}/"$
    BANano.PHPAddHeader("Access-Control-Allow-Origin: *")
In BANano_Ready, let's created the needed tables for our SQLite db, the database will be created on the root of your server for the app.

B4X:
Dim sqlite As BANanoSQLite
    sqlite.Initialize
    dbName = $"${AppName}.db"$


NB: The lib will be loaded soon for us to enjoy...
 

Attachments

Last edited:

Mashiane

Expert
Licensed User
Creating a table

B4X:
'create table
    Dim tbl As Map = CreateMap()
    tbl.Put("id", sqlite.DB_INT)
    tbl.Put("firstname", sqlite.DB_STRING)
    tbl.Put("lastname", sqlite.DB_STRING)
    tbl.Put("salary", sqlite.DB_REAL)
    tbl.Put("dob", sqlite.DB_DATE)
    tbl.Put("alive", sqlite.DB_BOOL)
    tbl.Put("fatherid", sqlite.DB_INT)
    'CREATE TABLE
    Dim sql As String = sqlite.CreateTable("family", tbl, "id")
    Log(sql)
    Dim res As String = BANano.CallInlinePHPWait("BANanoSQLite", CreateMap("dbname": dbName, "data":sql))
    Log(res)
This is just an example of code adopting DBUtils structure of doing things...
 

Mashiane

Expert
Licensed User
Selecting all records

If you have used BANanoMySQL, you will notice a lot of similarities here.. We have just added the dbName as we cannot define this inside the php file for the sake of flexibility...

B4X:
'SELECT ALL RECORDS
    sqlite.Initialize
    Dim sql As String = sqlite.SelectAll("family", Array("*"), Array("id"))
    Log(sql)
    Dim res As String = BANano.CallInlinePHPWait("BANanoSQLite", CreateMap("dbname": dbName, "data":sql))
    Log(res)
 

Mashiane

Expert
Licensed User
Select Where

As this has a where, the field types are by default all strings, but in case any are different, one can use .AddBlobs, .AddDoubles etc etc...

B4X:
'SELECT RECORDS WHERE
    sqlite.Initialize
    Dim sql As String = sqlite.SelectWhere("family", Array("*"), CreateMap("firstname":"Anele"), Array("firstname", "lastname"))
    Log(sql)
    Dim res As String = BANano.CallInlinePHPWait("BANanoSQLite", CreateMap("dbname": dbName, "data":sql))
    Log(res)
 

Mashiane

Expert
Licensed User
Inserting Records

To insert records we pass a map based on the structure of our table, for example...

B4X:
'INSERT RECORD, lets activate the dummy generator
    Dim struct As Map = CreateMap()
    struct.Put("id", 1)
    struct.Put("firstname", "Anele")
    struct.Put("lastname", "Mbanga")
    struct.Put("salary", 20.00)
    struct.Put("dob", "9999-04-15")
    struct.Put("alive", 1)
    struct.Put("fatherid", 2)
We need to indicate the field types of the fields we are adding so that the prepared statements parses the data properly....

B4X:
sqlite.Initialize
    'ensure you specify the field types , rest are strings
    sqlite.AddIntegers(Array("id","fatherid"))
    sqlite.AddDoubles(Array("salary"))
    sqlite.AddBooleans(Array("alive"))
    'define the field types
    Dim sql As String = sqlite.Insert("family", struct)
    Log(sql)
    Dim res As String = BANano.CallInlinePHPWait("BANanoSQLite", CreateMap("dbname": dbName, "data":sql))
    Log(res)
 

Mashiane

Expert
Licensed User
CRUD Operations for Single Specific Records .i.e WHERE CLAUSE

In examples above we looked at doing, select, updates and deletes on all the records in the table. In this exercise we look at maintaining a single record. This means using

SELECT WHERE, UPDATE WHERE and DELETE WHERE...

BANanoSQLiteDemo1.gif


CREATE / INSERT

Here we create a single specific record and add it to the table.

B4X:
Sub insert1
    ClearFirst
    'initialize bananosqlite
    sqlite.Initialize()
    'create a record
    Dim rec As Map = CreateMap()
    rec.Put("id", "A")
    rec.Put("jsoncontent", "B")
    rec.Put("tabindex", 1)
    rec.put("parentid", "form")
    'indicate field data types
    sqlite.AddStrings(Array("id"))
    sqlite.AddIntegers(Array("tabindex"))
    'execute the insert
    Dim sql As String = sqlite.Insert("items", rec)
    Dim res As String = BANano.CallInlinePHPWait("BANanoSQLite", CreateMap("dbname": dbname, "data": sql))
    'return the resultset
    Dim rs As ResultSet = sqlite.GetResultSet(sql,res)
    '
    elCommand.SetText("Command: " & rs.query)
    elArgs.SetText("Arguements: " & BANano.ToJson(rs.args))
    elNotif.SetText("Result: " & BANano.ToJson(rs.result) & " rowid!")
End Sub
The key for this record is the id field, and we give this the id of "A".

READ

We want to select the record using its id, i.e. 'A'

B4X:
Sub Select1
    ClearFirst
    'initialize bananosqlite
    sqlite.Initialize()
    'indicate field data types
    sqlite.AddStrings(Array("id"))
    'execute select where
    Dim sql As String = sqlite.SelectWhere("items", Array("*"), CreateMap("id":"A"), Array("id"))
    Dim res As String = BANano.CallInlinePHPWait("BANanoSQLite", CreateMap("dbname": dbname, "data": sql))
    'get the result
    Dim rs As ResultSet = sqlite.GetResultSet(sql,res)
    '
    elCommand.SetText("Command: " & rs.query)
    elArgs.SetText("Arguements: " & BANano.ToJson(rs.args))
    elNotif.SetText("Result: " & BANano.ToJson(rs.result))
End Sub
UPDATE

To test this, we will update the "parentid" field for the record with id = "A" to a random text..

B4X:
Sub update1
    ClearFirst
    'initialize bananosqlite
    sqlite.Initialize()
    'define field types
    sqlite.AddStrings(Array("id","parentid"))
    'generate random data
    dummy.Initialize
    Dim parentid As String = dummy.Rand_Company_Name
    'define query data & execute
    Dim sql As String = sqlite.UpdateWhere("items", CreateMap("parentid":parentid), CreateMap("id":"A"))
    Dim res As String = BANano.CallInlinePHPWait("BANanoSQLite", CreateMap("dbname": dbname, "data": sql))
    'get the result
    Dim rs As ResultSet = sqlite.GetResultSet(sql,res)
    '
    elCommand.SetText("Command: " & rs.query)
    elArgs.SetText("Arguements: " & BANano.ToJson(rs.args))
    elNotif.SetText("Result: " & BANano.ToJson(rs.result))
    
End Sub
DELETE

We want to delete the record with id = 'A', so lets execute a specific command.

B4X:
Sub Delete1
    ClearFirst
    'initialize bananosqlite
    sqlite.Initialize()
    'define field types
    sqlite.AddStrings(Array("id"))
    'define query data & execute
    Dim sql As String = sqlite.DeleteWhere("items", CreateMap("id":"A"))
    Dim res As String = BANano.CallInlinePHPWait("BANanoSQLite", CreateMap("dbname": dbname, "data": sql))
    'get the result
    Dim rs As ResultSet = sqlite.GetResultSet(sql,res)
    
    elCommand.SetText("Command: " & rs.query)
    elArgs.SetText("Arguements: ")
    elNotif.SetText("Result: " & BANano.ToJson(rs.result))
End Sub
NB: Update class on 1st Post.

Ta!
 

Mashiane

Expert
Licensed User
Update: BANanoSQLite1

I have been trying to streamline this class so that its easy to port from BANanoSQL to BANanoSQLite as a result after a lot of tweaking, here is BANanoSQLite1.

The same setting up process applies..

On App_Start set up the connectivity..

B4X:
'set php settings
    BANano.PHP_NAME = "otar.php"
    BANano.PHPHost = $"http://localhost/otar/"$
    BANano.PHPAddHeader("Access-Control-Allow-Origin: *")
Let's look at maintaining the db...

Let's specify the db

B4X:
Dim sqlite As BANanoSQLite1
    sqlite.Initialize
    'create a db on the root folder
    sqlite.SetDB("./otar.db3")
Let's create a table.

B4X:
Dim nt As Map = CreateMap()
    nt.Put("id", "INT")
    nt.Put("a", "STRING")
    nt.put("b", "STRING")
    nt.Put("c", "INT")
    'create a table
    Dim rst As ResultSet = sqlite.CreateTable("test", nt, "id")
    rst.result = BANano.FromJson(BANano.CallInlinePHPWait("BANanoSQLite1", sqlite.Build(rst)))
Let's add some records, we get the max of the id field and then increment it... we also need to indicate the types of fields we are passing. id and c are integers. The rest of the fields are assumed to be strings by the library.

B4X:
sqlite.ResetTypes
    Dim rsadd As ResultSet = sqlite.GetMax("test","id")
    rsadd.result = BANano.FromJson(BANano.CallInlinePHPWait("BANanoSQLite1", sqlite.Build(rsadd)))
    Dim nextid As String = sqlite.GetNextID("id", rsadd.result)
    '
    Dim nr1 As Map = CreateMap()
    nr1.Put("id", nextid)
    nr1.Put("a", "a")
    nr1.put("b", "b")
    nr1.Put("c", "1")
    sqlite.ResetTypes
    sqlite.AddIntegers(Array("id","c"))
    Dim rsnew As ResultSet = sqlite.Insert("test", nr1)
    rsnew.result = BANano.FromJson(BANano.CallInlinePHPWait("BANanoSQLite1", sqlite.Build(rsnew)))
    '
    'add another record
    sqlite.ResetTypes
    Dim rsadd1 As ResultSet = sqlite.GetMax("test","id")
    rsadd1.result = BANano.FromJson(BANano.CallInlinePHPWait("BANanoSQLite1", sqlite.Build(rsadd1)))
    Dim nextid1 As String = sqlite.GetNextID("id", rsadd1.result)
    Dim nr2 As Map = CreateMap()
    nr2.Put("id", nextid1)
    nr2.Put("a", "b")
    nr2.put("b", "c")
    nr2.Put("c", "2")
    sqlite.ResetTypes
    sqlite.AddIntegers(Array("id","c"))
    Dim rsnew1 As ResultSet = sqlite.Insert("test", nr2)
    rsnew1.result = BANano.FromJson(BANano.CallInlinePHPWait("BANanoSQLite1", sqlite.Build(rsnew1)))
    Log(rsnew1)

Let's select all the records in the table.

B4X:
'try and select records
    sqlite.ResetTypes
    Dim rssel As ResultSet = sqlite.SelectAll("test", Array("*"), Array("c"))
    rssel.result = BANano.FromJson(BANano.CallInlinePHPWait("BANanoSQLite1", sqlite.Build(rssel)))
    Log(rssel.result)
Let's update one of the records...

B4X:
'do an update
    sqlite.ResetTypes
    sqlite.AddIntegers(Array("id","c"))
    Dim nr3 As Map = CreateMap()
    nr3.Put("id", "1")
    nr3.Put("a", "c")
    nr3.put("b", "d")
    nr3.Put("c", "0")
    Dim rsup As ResultSet = sqlite.UpdateWhere("test", nr3, CreateMap("id":"1"))
    rsup.result = BANano.FromJson(BANano.CallInlinePHPWait("BANanoSQLite1", sqlite.Build(rsup)))
    Log(rsup.result)
Let's delete one of the records

B4X:
sqlite.ResetTypes
    sqlite.AddIntegers(Array("id","c"))
    Dim rsdel As ResultSet = sqlite.DeleteWhere("test", CreateMap("c":"0"))
    rsdel.result = BANano.FromJson(BANano.CallInlinePHPWait("BANanoSQLite1", sqlite.Build(rsdel)))
    Log(rsdel.result)
Let's do an INSERT_REPLACE of one of the records. This deletes an existing records by id (if it exists) and then adds it,

B4X:
Dim nr4 As Map = CreateMap()
    nr4.Put("id", "7")
    nr4.Put("a", "z")
    nr4.put("b", "z")
    nr4.Put("c", "7")
    sqlite.resettypes
    Dim rsrep As ResultSet = sqlite.InsertReplace("test", nr4)
    rsrep.result = BANano.FromJson(BANano.CallInlinePHPWait("BANanoSQLite1", sqlite.Build(rsrep)))
    Log(rsrep.result)
 

Attachments

Mashiane

Expert
Licensed User
It worked, but at one point or another it stopped working and creating the SQLite file
Is there any particular error that its giving you? The contacts example that you have provided me has 0% SQLite in it, can you send me something that at least shows that you tried to implement something on your code based on your understanding of this tutorial?
 
Top