B4J Library [BANanoSQLite] SQLiteDB PHP CRUD Class for BANano

Discussion in 'B4J Libraries & Classes' started by Mashiane, Jul 8, 2019.

  1. Mashiane

    Mashiane Expert Licensed User

    Ola

    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...

    Code:
    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

    Code:
    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...

    Code:
    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..

    Code:
    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..

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

    Code:
    '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.

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


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

    Attached Files:

    Last edited: Aug 13, 2019
    joulongleu and Johan Hormaza like this.
  2. Mashiane

    Mashiane Expert Licensed User

    Creating a table

    Code:
    '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...
     
    joulongleu and Johan Hormaza like this.
  3. Mashiane

    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...

    Code:
    '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)
     
    joulongleu and Johan Hormaza like this.
  4. Mashiane

    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...

    Code:
    '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)
     
    joulongleu and Johan Hormaza like this.
  5. Mashiane

    Mashiane Expert Licensed User

    Delete Records meeting a criteria

    Code:
    'DELETE RECORD WHERE
        sqlite.Initialize
        
    Dim sql As String = sqlite.DeleteWhere("family", CreateMap("firstname":"Anele"))
        
    Log(sql)
        
    Dim res As String = BANano.CallInlinePHPWait("BANanoSQLite", CreateMap("dbname": dbName, "data":sql))
        
    Log(res)
    Watch this space...
     
    Johan Hormaza likes this.
  6. Mashiane

    Mashiane Expert Licensed User

    Inserting Records

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

    Code:
    '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....

    Code:
    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)
     
    joulongleu likes this.
  7. Mashiane

    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.

    Code:
    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'

    Code:
    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..

    Code:
    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.

    Code:
    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!
     
    joulongleu likes this.
  8. Mashiane

    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..

    Code:
    '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

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

    Code:
    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.

    Code:
    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.

    Code:
    '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...

    Code:
    '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

    Code:
    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,

    Code:
    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)
     

    Attached Files:

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice