liberty_code/sql

Library

v1.0.0 2023-01-10 20:48 UTC

Requires

• php: ~7 || ~8
• liberty_code/authentication: ^1.0.
• liberty_code/di: ^1.0.
• liberty_code/item_browser: ^1.0.
• liberty_code/library: ^1.0.
• liberty_code/model: ^1.0.
• liberty_code/register: ^1.0.
• liberty_code/validation: ^1.0.

BSD-3-Clause

README

Description

Library contains SQL components, to use for SQL data storage type.

Requirement

• Script language: PHP: version 7 || 8

Installation

Several ways are possible:

Composer

1. Requirement

   It requires composer installation. For more information: https://getcomposer.org

2. Command: Move in project root path

    cd "<project_root_path>"
   

3. Command: Installation

    php composer.phar require liberty_code/sql ["<version>"]
   

4. Note

   • Include vendor

     If project uses composer, vendor must be included:

       require_once('<project_root_path>/vendor/autoload.php');
     

   • Configuration

     Installation command allows to add, on composer file "

       {
           "require": {
               "liberty_code/sql": "<version>"
           }
       }
     

Include

1. Download

   • Download following repository.
   • Put it on repository root path.

2. Include source

    require_once('<repository_root_path>/include/Include.php');
   

Usage

Database connection

Connection allows to design SQL database connection, to connect and request on specific SQL data storage, from specified configuration.

Elements

• Connection

  Allows to design a connection, who is an item containing all information, allows to connect and manage data.

• PdoConnection

  Extends connection features. Allows to design a SQL database connection, using specific PDO standard.

Example

// Define new PDO connection
use liberty_code\sql\database\connection\library\ConstConnection;
use liberty_code\sql\database\connection\pdo\library\ConstPdoConnection;
use liberty_code\sql\database\connection\pdo\model\PdoConnection;
class MysqlPdoConnection extends PdoConnection
{
    public function getStrConfigDsn()
    {
        $tabConfig = $this->getTabConfig();
        
        return sprintf(
            'host=%1$s;port=%2$s;dbname=%3$s;charset=%4$s',
            $tabConfig[ConstConnection::TAB_CONFIG_KEY_HOST],
            $tabConfig[ConstConnection::TAB_CONFIG_KEY_PORT],
            $tabConfig[ConstConnection::TAB_CONFIG_KEY_DB_NAME],
            $tabConfig[ConstConnection::TAB_CONFIG_KEY_CHARSET]
        )
    }
    
    public function setConfig(array $tabConfig, $boolConnect = true)
    {
        // Set MySQL driver
        $tabConfig[ConstPdoConnection::TAB_CONFIG_KEY_DRIVER] = 'mysql';
        
        parent::setConfig($tabConfig, $boolConnect);
    }
}
...
// Get connection
$connection = new MysqlPdoConnection(array(
    ConstConnection::TAB_CONFIG_KEY_HOST => 'host',
    ConstConnection::TAB_CONFIG_KEY_DB_NAME => 'db_name'
    ConstConnection::TAB_CONFIG_KEY_CHARSET => 'utf8',
    ConstConnection::TAB_CONFIG_KEY_LOGIN => 'login',
    ConstConnection::TAB_CONFIG_KEY_PASSWORD => 'password'
));
...
// Execute SQL command
$connection->execute('...SQL string command');
...

Database statement

Statement allows to design prepared query, to execute SQL command from specified query parameters, on specific SQL data storage.

Elements

• Statement

  Allows to design a statement, represents a prepared query, can be executed with specified parameters.

• PdoStatement

  Extends statement features. Allows to design a SQL database statement, using specific PDO standard.

Example

...
// Get statement from connection
$statement = $connection->getObjStatement('...SQL string command');
...
// Execute statement
$statement->execute(array(
    'param_1' => 'Value 1',
    ...,
    'param_N' => 'Value N'
));
...

Database result

Result allows to design query result, to read and get query results, on specific SQL data storage.

Elements

• Result

  Allows to design a query result, to read and get query results.

• PdoStatement

  Extends result features. Allows to design a SQL database result, using specific PDO standard.

Example

...
// Get SQL result from connection
$result = $connection->executeResult('...SQL string command');
...
// Or get SQL result from statement
$statement = $connection->getObjStatement('...SQL string command');
$result = $statement->executeResult(array(
    'param_1' => 'Value 1',
    ...,
    'param_N' => 'Value N'
));
...
if($result !== false) {
    while(($data = $result->getFetchData()) !== false) {
        var_dump($data);
    }
    /**
     * Show: 
     * row 1: array('column name 1' => 'value 1', ..., 'column name N' => 'value N')
     * ...
     * row N: array('column name 1' => 'value 1', ..., 'column name N' => 'value N')
     */
    ...
    // Close result
    $result->close();
}
...

Database command

Command allows to design query builder, to get SQL string command, from specified configuration, to use on specific SQL data storage.

Elements

• Command

  Allows to design a query builder, to get SQL string command, from specified configuration.

• DbUseCommand

  Extends command features. Allows to design query builder for SQL database use string command.

• StandardDbUseCommand

  Extends database use command features. Uses standard SQL.

• DbShowCommand

  Extends command features. Allows to design query builder for SQL database show string command.

• StandardDbShowCommand

  Extends database show command features. Uses standard SQL.

• DbCreateCommand

  Extends command features. Allows to design query builder for SQL database create string command.

• StandardDbCreateCommand

  Extends database create command features. Uses standard SQL.

• DbAlterCommand

  Extends command features. Allows to design query builder for SQL database alter string command.

• StandardDbAlterCommand

  Extends database alter command features. Uses standard SQL.

• DbDropCommand

  Extends command features. Allows to design query builder for SQL database drop string command.

• StandardDbDropCommand

  Extends database drop command features. Uses standard SQL.

• TableShowCommand

  Extends command features. Allows to design query builder for SQL table show string command.

• StandardTableShowCommand

  Extends table show command features. Uses standard SQL.

• TableDropCommand

  Extends command features. Allows to design query builder for SQL table drop string command.

• StandardTableDropCommand

  Extends table drop command features. Uses standard SQL.

• SelectCommand

  Extends command features. Allows to design query builder for SQL select string command.

• StandardSelectCommand

  Extends select command features. Uses standard SQL.

• InsertCommand

  Extends command features. Allows to design query builder for SQL insert string command.

• StandardInsertCommand

  Extends insert command features. Uses standard SQL.

• UpdateCommand

  Extends command features. Allows to design query builder for SQL update string command.

• StandardUpdateCommand

  Extends update command features. Uses standard SQL.

• DeleteCommand

  Extends command features. Allows to design query builder for SQL delete string command.

• StandardDeleteCommand

  Extends delete command features. Uses standard SQL.

• CommandFactory

  Allows to design a query builder factory, to provide new command objects, from specified SQL database connection.

• StandardCommandFactory

  Extends command factory features. Provides command objects using standard SQL.

Example

...
// Get command factory
use liberty_code\sql\database\command\factory\standard\model\StandardCommandFactory;
$commandFacto = new StandardCommandFactory($connection);
...
// Get SQL select command
$command = $commandFacto->getObjSelectCommand(array(
    ... SQL select command configuration format
));
...
// Get SQL result
$result = $command->executeResult();
...
if($result !== false) {
    while(($data = $result->getFetchData()) !== false) {
        var_dump($data);
    }
    /**
     * Show: 
     * row 1: array('column name 1' => 'value 1', ..., 'column name N' => 'value N')
     * ...
     * row N: array('column name 1' => 'value 1', ..., 'column name N' => 'value N')
     */
    ...
    // Close result
    $result->close();
}
...

Register

Register using specific SQL data storage as storage support.

Elements

• Register (SQL default)

  Extends default register features. Allows to manage items, using specific SQL data storage.

• TableRegister

  Extends SQL default register features. Each key considered as column name. Store each item on row, associated to correct column (key).

• DataRegister

  Extends SQL default register features. Store keys on same specific column. Store items on same specific column.

Example

...
// Get register
use liberty_code\sql\register\library\ConstRegister;
use liberty_code\sql\register\data\library\ConstDataRegister;
use liberty_code\sql\register\data\model\DataRegister;
$register = new DataRegister(
    array(
        ConstRegister::TAB_CONFIG_KEY_TABLE_NAME => 'table name',
        ConstDataRegister::TAB_CONFIG_KEY_COLUMN_NAME_KEY => 'column name for keys',
        ConstDataRegister::TAB_CONFIG_KEY_COLUMN_NAME_ITEM => 'column name for items',
        ConstDataRegister::TAB_CONFIG_KEY_COLUMN_NAME_EXPIRE_TIMEOUT_DATETIME => 'column name for expiration datetimes'
    ), 
    $commandFacto
);
...
$register->putItem('key_1', '...'); // Register specified item for key 1
$register->putItem('key_N', '...'); // Register specified item for key N
...
foreach($register->getTabKey() as $key) {
    var_dump($register->getItem($key));
}
/**
 * Show: 
 * item for key 1
 * item for key N
 */
...

Rule

SQL Rules allows to check specified data validation, using SQL data storage.

Elements

• SqlRule

  Extends rule features. Allows to check specified data validation, using SQL data storage.

• ExistSqlRule

  Extends SQL rule features. Allows to check if specified data exists, in specified SQL data storage.

Persistence

Persistence using specific SQL data storage as storage support.

Elements

• Persistor (SQL default)

  Extends default persistor features. Allows to design save engine for entities, on specific SQL data storage.

• TablePersistor

  Extends SQL default persistor features. Each attribute name considered as column name. Store each entity attribute value on same row, associated to correct column (attribute name).

• DataPersistor

  Extends SQL default persistor features. Store attribute names on same specific column. Store attribute values on same specific column.

Example

...
// Get persistor
use liberty_code\sql\persistence\library\ConstPersistor;
use liberty_code\sql\persistence\table\model\TablePersistor;
$persistor = new TablePersistor($commandFacto);
$config = array(
    ConstPersistor::TAB_CONFIG_KEY_TABLE_NAME => 'table name',
    ConstPersistor::TAB_CONFIG_KEY_COLUMN_NAME_ID => 'column name for ids',
);
...
// Select entities attributes from specified ids
$tabData = $persistor->getTabData(
    array(
        'entity id 1',
        ...,
        'entity id N'
    ), 
    $config
);
...
if ($tabData !== false) {
    foreach($tabData as $data) {
        var_dump($data);
    }
    /**
     * Show: 
     * entity 1 data: array('attribute name 1' => 'value 1', ..., 'attribute name N' => 'value N')
     * ...
     * entity N data: array('attribute name 1' => 'value 1', ..., 'attribute name N' => 'value N')
     */
}
...

Item browser

Browser using specific SQL data storage to provide items.

Elements

• Browser (SQL default)

  Extends criteria browser features. Allows to provide items, from specific SQL data storage.

• TableBrowser

  Extends SQL default browser features. Each item key considered as column name. Considered each item value stored on same row, associated to correct column (item key).

• DataBrowser

  Extends SQL default browser features. Considered item keys stored on same specific column. Considered item values stored on same specific column.

Example

...
// Get SQL table browser
use liberty_code\register\register\memory\model\MemoryRegister;
use liberty_code\item_browser\operation\model\OperatorData;
use liberty_code\item_browser\browser\library\ConstBrowser
use liberty_code\sql\browser\table\model\TableBrowser;
$config = array(
    ConstBrowser::TAB_CONFIG_KEY_QUERY => ... SQL select command configuration format
);
$operatorData = new OperatorData();
$register = new MemoryRegister();
$browser = new TableBrowser(
    $operatorData,
    $commandFacto,
    $register,
    $config,
    array(),
    array()
);
...
// Set number items per page
$browser->setItemCountPerPage(10);
...
$pageCount = $browser->getIntPageCount(); // Get number of pages
for($cpt = 0; $cpt < count($pageCount); $cpt++) {
    $browser->setActivePageIndex($cpt);
    var_dump($browser->getTabItem());
}
/**
 * Show: 
 * index array of items, on page 1
 * ...
 * index array of items, on page N
 */
...

Authenticator

SQL authenticator using specific SQL data storage, to check identification and authentication.

Elements

• SqlAuthenticator

  Extends authenticator features. Allows to check identification and authentication, using SQL data storage.

Example

...
// Get SQL authenticator
use liberty_code\sql\authentication\authenticator\library\ConstSqlAuthenticator;
use liberty_code\sql\authentication\authenticator\model\SqlAuthenticator;
$config = array(
    ConstSqlAuthenticator::TAB_CONFIG_KEY_QUERY => ... SQL select command configuration format,
    ConstSqlAuthenticator::TAB_CONFIG_KEY_DEFAULT_ID_DATA_COLUMN_NAME => ... default identification column name,
    ConstSqlAuthenticator::TAB_CONFIG_KEY_DEFAULT_AUTH_DATA_COLUMN_NAME => ... default authentication column name
);
$authenticator = new SqlAuthenticator(
    $config,
    $commandFacto
);
...
// Check if specified authentication is identified
var_dump($authenticator->checkIsIdentified(...authentication object));
...
// Check if specified authentication is authenticated
var_dump($authenticator->checkIsAuthenticated(...authentication object));
...