Smart.Framework Logo

final class \SmartSQliteDb
{ } ->

Class: SmartSQliteDb - provides a Dynamic SQLite Database Client.
The SLQLite file path should be starting with '#db/' or 'tmp/', which are protected via HtAccess files or by config.
Do not use other dir prefixes unless you know what you are doing ...
Tested and Stable on SQLite versions: 3.x


class Methods

public function __construct ( STRING $sqlite_db_file, INTEGER+ $timeout_busy_sec = 60, BOOLEAN $register_extra_functions = true ) {} @ 
@param: {STRING} $sqlite_db_file: The path to the SQLite Database File :: Example: 'tmp/test.sqlite' ; (if DB does not exist, will create it)
@param: {INTEGER+} $timeout_busy_sec: The busy timeout in seconds
@param: {BOOLEAN} $register_extra_functions: If set to FALSE will not register the default extra SQL functions ; default is TRUE ; extra SQL functions can be set later with register_sql_function()
Class constructor
public function __destruct ( ) {} @ 
Class Destructor
This will automatically close the current DB of this class (if not closed explicit before).
public function open ( ) {} -> 
Opens the current SQLite DB (similar to server connect).
This must be called prior any other DB operations: read / write / count / ...
public function close ( ) {} -> 
Manually Closes the current SQLite DB (similar to server disconnect).
This is for safety and should be used when coding to explicit close the DB after ending operations to avoid DB corruption in high-load environments.
Otherwise, it will be closed automatically on object __destruct() ...
public function quote_likes ( STRING $y_string ) {} -> 
@param: {STRING} $y_string: A String or a Number to be Quoted for LIKES
Fix a string to be compliant with SQLite LIKE / SIMILAR syntax.
It will use special quotes for the LIKE / SIMILAR special characters: % _
This function IS NOT INTENDED TO ESCAPE AGAINST SQL INJECTIONS ; USE IT ONLY WITH PREPARED PARAMS OR USE escape_str() with mode 'likes'
public static function likes_escaper ( ) {} :: 
Will return the escaper character for likes syntax: \
This is intended to be used with the ESCAPE keyword that must follow a LIKE escaped syntax
public function escape_str ( STRING $string, ENUM $y_mode = "" ) {} -> STRING
@return: {STRING} The Escaped String / Number
@param: {STRING} $string: A String or a Number to be Escaped
@param: {ENUM} $y_mode: '' = default ; 'likes' = Escape LIKE Syntax (\ % _) ; SELECT * FROM table1 WHERE name LIKE '%a\_b' ESCAPE '\' ;
Escape a string to be compliant and Safe (against SQL Injection) with SQLite standards.
This function will not add the (single) quotes arround the string, but just will just escape it to be safe.
IMPORTANT: in SQLite when using this to escape a LIKE expression it must be followed by ESCAPE keyword to indicate the escaping character used
Ex: PHP $db->read_(a|as)data('SELECT * FROM table1 WHERE name LIKE \'%'.$db->escape_str('a_b', 'likes').'\' ESCAPE \''.$db->likes_escaper().'\'');
public function json_encode ( STRING $mixed_content ) {} -> STRING
@return: {STRING} JSON string
@param: {STRING} $mixed_content: A mixed variable
SQlite compliant and Safe Json Encode.
This should be used with SQlite json fields.
public function register_sql_function ( STRING $func, INTEGER $argnum, $sqlname ) {} -> 
@param: {STRING} $func: A PHP or custom Function Name (will be registered with `custom_fx_` as prefix
@param: {INTEGER} $argnum: The number of required args ; If this parameter is -1, then the SQL function may take any number of arguments
Registers a PHP function for use as an SQL scalar function with SQLite.
public function check_if_table_exists ( STRING $table_name ) {} -> BOOLEAN
@return: {BOOLEAN} TRUE if exists, FALSE if not
@param: {STRING} $table_name: The Table Name
Check if a Table exists in the current SQLite DataBase
public function count_data ( STRING $query, STRING $qparams = "", STRING $qtitle = "" ) {} -> INTEGER
@return: {INTEGER} the result of COUNT()
@param: {STRING} $query: the SQLite Query
@param: {STRING} $qparams: *optional* array of parameters (?, ?, ... ?)
@param: {STRING} $qtitle: *optional* query title for easy debugging
SQLite Query -> Count
This function is intended to be used for count type queries: SELECT COUNT().
public function read_data ( STRING $query, STRING $qparams = "", STRING $qtitle = "" ) {} -> ARRAY
@return: {ARRAY} (non-asociative) of results :: array('column-0-0', 'column-0-1', null, ..., 'column-0-n', 'column-1-0', 'column-1-1', ... 'column-1-n', ..., 'column-m-0', 'column-m-1', ..., 'column-m-n')
@param: {STRING} $query: the SQLite Query
@param: {STRING} $qparams: *optional* array of parameters (?, ?, ... ?)
@param: {STRING} $qtitle: *optional* query title for easy debugging
SQLite Query -> Read (Non-Associative) one or multiple rows.
This function is intended to be used for read type queries: SELECT.
public function read_adata ( STRING $query, STRING $qparams = "", STRING $qtitle = "" ) {} -> ARRAY
@return: {ARRAY} (asociative) of results :: array(0 => array('column1' => 'val1', 'column2' => null, ... 'column-n' => 't'), 1 => array('column1' => 'val2', 'column2' => 'val2', ... 'column-n' => 'f'), ..., m => array('column1' => 'valM', 'column2' => 'xyz', ... 'column-n' => 't'))
@param: {STRING} $query: the SQLite Query
@param: {STRING} $qparams: *optional* array of parameters (?, ?, ... ?)
@param: {STRING} $qtitle: *optional* query title for easy debugging
SQLite Query -> Read (Associative) one or multiple rows.
This function is intended to be used for read type queries: SELECT.
public function read_asdata ( STRING $query, STRING $qparams = "", STRING $qtitle = "" ) {} -> ARRAY
@hints: ALWAYS use a LIMIT 1 OFFSET 0 with all queries using this function to avoid situations that will return more than 1 rows and will raise ERROR with this function.
@return: {ARRAY} (asociative) of results :: Returns just a SINGLE ROW as: array('column1' => 'val1', 'column2' => null, ... 'column-n' => 't')
@param: {STRING} $query: the SQLite Query
@param: {STRING} $qparams: *optional* array of parameters (?, ?, ... ?)
@param: {STRING} $qtitle: *optional* query title for easy debugging
SQLite Query -> Read (Associative) - Single Row (just for 1 row, to easy the use of data from queries).
!!! This will raise an error if more than one row(s) are returned !!!
This function does not support multiple rows because the associative data is structured without row iterator.
For queries that return more than one row use: read_adata() or read_data().
This function is intended to be used for read type queries: SELECT.
public function write_data ( STRING $query, STRING $qparams = "", STRING $qtitle = "" ) {} -> ARRAY
@return: {ARRAY} [ 0 => 'control-message', 1 => #affected-rows, 2 => #last-inserted-id(autoincrement)|0|null ]
@param: {STRING} $query: the SQLite Query
@param: {STRING} $qparams: *optional* array of parameters (?, ?, ... ?)
@param: {STRING} $qtitle: *optional* query title for easy debugging
SQLite Query -> Write.
This function is intended to be used for write type queries: BEGIN (TRANSACTION) ; COMMIT ; ROLLBACK ; INSERT ; UPDATE ; CREATE SCHEMAS ; CALLING STORED PROCEDURES ...
public function prepare_statement ( ARRAY $arrdata, ENUM $mode ) {} -> STRING
@return: {STRING} The SQL partial Statement
@param: {ARRAY} $arrdata: associative array: array of form data as $arr=array(); $arr['field1'] = 'a string'; $arr['field2'] = 100; | non-associative array $arr[] = 'some value'; $arr[] = 'other-value', ...
@param: {ENUM} $mode: mode: 'insert' | 'update' | 'in-select'
Create Escaped Write SQL Statements from Data - to be used with SQLite for: INSERT ; UPDATE ; IN-SELECT
Can be used with: write_data() to build INSERT / UPDATE queries from an associative array
or can be used with read_data(), read_adata(), read_asdata(), count_data() to build IN-SELECT queries from a non-associative array
public function prepare_param_query ( STRING $query, ARRAY $arrdata ) {} -> STRING
@return: {STRING} The SQL processed (partial/full) Statement
@param: {STRING} $query: SQL Statement to process like ' WHERE (id = ?)'
@param: {ARRAY} $arrdata: The non-associative array as of: $arr=array('a');
Create Escaped SQL Statements from Parameters and Array of Data by replacing ? (question marks)
This can be used for a full SQL statement or just for a part.
The statement must not contain any Single Quotes to prevent SQL injections which are unpredictable if mixing several statements at once !
public function new_safe_id ( ENUM $mode, STRING $id_field, STRING $table_name ) {} -> STRING
@return: {STRING} the generated Unique ID
@param: {ENUM} $mode: mode: uid10str | uid10num | uid10seq | uid12seq | uid32 | uid34
@param: {STRING} $id_field: the field name
@param: {STRING} $table_name: the table name
Get A UNIQUE (SAFE) ID for DB Tables / Schema

class Properties


class Constants


Sample code: PHP

<?php

// IMPORTANT: https://sqlite.org/lang_keywords.html
 //     * SQLIte3 uses by default ' (single quotes) to quote strings and ` (backticks) or nothing to quote identifiers (table names / field names)
 //     * It is recommended to quote identifiers (table names / field names) using ` (backtick) instead of not quoting at all to avoid confusion with SQL reserved syntax
 //     * Using ` (backticks) is prefered as SELECT `field1` FROM `table1` is a strict syntax in SQLite3 and will throw error if either table1 or field1 do not exists
 //     * Using " (double quote) for quoting identifiers may result in unexpected results as SELECT "field1" FROM "table1" will not throw exception if field1 / table1 do not exists

 //Sample Usage
 $db = new SmartSQliteDb('tmp/testunit.sqlite');
 $db->open();
 $sq_rd = (array) $db->read_asdata("SELECT `description` FROM `mytable` WHERE (`id` = '".$db->escape_str($my_id)."') LIMIT 1 OFFSET 0");
 $sq_cnt = (int) $db->count_data("SELECT COUNT(1) FROM mytable WHERE (score > ?)", array(100));
 $arr_insert = array(
         'id' => 100,
         'active' => 1,
         'name' => 'Test Record'
 );
 $sq_ins = (array) $db->write_data('INSERT INTO other_table '.$db->prepare_statement($arr_insert, 'insert'));
 $sq_ins = (array) $db->write_data('INSERT OR REPLACE INTO other_table '.$db->prepare_statement($arr_insert, 'insert')); // upsert
 $sq_upd = (array) $db->write_data('UPDATE other_table SET active = 0 WHERE (id = ?)', array(100));
 $prepared_sql = $db->prepare_param_query('SELECT * FROM table WHERE id = ?', [99]);
 $db->close(); // optional, but safe

// #end php code

documentation generated on: 2023-10-19 23:15:52 +0000


Smart.Framework © 2009-2023 unix-world.org