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

This package is auto-updated.

Last update: 2024-03-10 23:10:34 UTC


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 "/composer.json", following configuration:

        {
            "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));
...