README

Description

knot-lib/datawtore is a Repository-Entity-Storage pattern data access library.

Feature

Fluent Query Builder
Lazy connection
Multiple connection
DBMS-specific SQL(SqlProviderInterface)
Single/Composite primary key
Transaction(begin/rollback/commit/savepoint)
Support subquery

How to use

Configure database connection

use KnotLib\Datastore\Storage\Database\Database;

// Create database object for MySQL
$db = new Database('mysql:dbname=mysql_db_name;host=localhost','db_user','db_password');
 
// Create database object for Sqlite
$db = new Database('sqlite:/path/to/sqlite_db_file');

Database storage

use KnotLib\Datastore\Storage\Database\DatabaseStorage;

// Create database storage object
$storage = new DatabaseStorage($db->connection());

Database connection

use KnotLib\Datastore\Storage\Database\DatabaseConnection;

// default connection
$conn = $db->connection();

// another connection
$conn = $db->connection('sub');

Example 1: Execute Raw SQL

...

$result = $conn->sql('SELECT * FROM fruits WHERE name like ?', ['o%'])->findAll();

// Above code is equivalent to next SQL:
// SELECT * FROM fruits WHERE name like 'o%'

echo json_encode($result);    // [{"id":1,"name":"orange","weight":0.3,"quality":1,"updated_date":null}]

Example 2: Execute Raw Count SQL

...

$result = $conn->sql('SELECT COUNT(*) FROM fruits WHERE name like ?',['banana'])->count();

// Above code is equivalent to next SQL:
// SELECT COUNT(*) FROM fruits WHERE name like 'banana'

echo json_encode($result);    // 1

Example 3: Execute Raw Insert SQL

...

$sql = "INSERT INTO fruits(name, weight, quality, updated_date) VALUES(?, ?, ?, ?)";
$params = ['mango', 0.4, 11, Database::now()];
$result = $conn->sql($sql, $params)->execute();

// Above code is equivalent to next SQL(MySQL):
// INSERT INTO fruits(name, weight, quality, updated_date) VALUES('mango', 0.4, 11, NOW())

echo json_encode($result);    // {"last_inserted_id":"5","affected_rows":1}

Example 4: Query Builder & Join

...

$result = $conn->select('shop_name, name, stock')
    ->from('shops', 's')
    ->leftJoin('fruits_stock', 'fs.shop_id = s.id', 'fs')
    ->leftJoin('fruits', 'st.fruits_id = f.id', 'f')
    ->where('name', 'name = ?', 'melon')
    ->query($storage)
    ->findAll();
    
// Above code is equivalent to next SQL(MySQL):
// SELECT shop_name, name, stock
//   FROM shops as s 
//   LEFT JOIN fruits_stock as fs ON fs.shop_id = s.id
//   LEFT JOIN fruits as f ON  st.fruits_id = f.id
//   WHERE name = 'melon'

echo json_encode($result);    // [{"shop_name":"Super Fruits Market","name":"melon","stock":5},{"shop_name":"Mega Fruits","name":"melon","stock":15}]

Example 5: Entity & Repository

use KnotLib\Datastore\Sample\FruitsRepository;
use KnotLib\Datastore\Sample\FruitsEntity;

...

// create repository object from storage
$repository = new FruitsRepository($storage);

// count entities
echo $repository->count()->execute() . PHP_EOL;     // 4

// insert and get one entity
$fruits = $repository->save(FruitsEntity::newEntity([
    'name' => 'mango',
    'weight' =>  0.5,
    'quality' => 11
]));
echo json_encode($fruits->getFieldValues(['id','name','updated_date'])) . PHP_EOL;    // {"id":5,"name":"mango","updated_date":{}}

echo $repository->count()->execute() . PHP_EOL;     // 5

// get one entity from repository
$fruits = $repository->getFruits(5);
echo json_encode($fruits->getFieldValues(['id','name','updated_date'])) . PHP_EOL;    // {"id":5,"name":"mango","updated_date":"2018-05-19 20:32:27"}

// get multiple entities from repository
$all_fruits = $repository->getAllFruits();
$all_fruits = array_map(function(FruitsEntity $item){
    return $item->name;
}, $all_fruits);
echo json_encode($all_fruits) . PHP_EOL;    // ["orange","melon","kiwi","apple","mango"]

// delete one entity
$repository->deleteFruits(5);

echo $repository->count()->execute() . PHP_EOL;     // 4

Example 6: Batch Insert

...

// batch insert via connection
$res = $conn->batchInsert()
    ->into('fruits')
    ->values(['id' => 1, 'name' => 'apple', 'weight' => 0.2, 'quality' => 10, 'updated_date' => NULL])
    ->values(['id' => 2, 'name' => 'orange', 'weight' => 0.3, 'quality' => 11, 'updated_date' => NULL])
    ->query($storage)
    ->execute();
    
echo $res;    // 2

// batch insert via repository 
$repos = new FruitsRepository($storage);
$res = $repos->batchInsert(function(){        
    static $data = [
        ['id' => 1, 'name' => 'apple', 'weight' => 0.2, 'quality' => 10, 'updated_date' => NULL],
        ['id' => 2, 'name' => 'orange', 'weight' => 0.3, 'quality' => 11, 'updated_date' => NULL],
    ];
    return array_shift($data);
})->execute();

// batch insert with entity data provider
$repos = new FruitsRepository($storage);
$data = [
    new FruitsEntity(['id' => 1, 'name' => 'apple', 'weight' => 0.2, 'quality' => 10, 'updated_date' => NULL]),
    new FruitsEntity(['id' => 2, 'name' => 'orange', 'weight' => 0.3, 'quality' => 11, 'updated_date' => NULL]),
];
$res = $repos->batchInsert(function() use(&$data){  
    return array_shift($data);
}, FruitsEntity::class)->execute();

// Above code is equivalent to next SQL:
// INSERT INTO fruits ('id', 'name', 'weight', 'quality', 'updated_date') VALUES
//   (1, 'apple', 0.2, 10, NULL),
//   (2, 'orange', 0.3, 11, NULL);
    
echo $res;    // 2

Example 7: Abbreviated placeholders

...
$result = $conn->select()
    ->from('fruits')
    ->where('id', 'id IN (...?)', [1,2,3])       // '...?' will be automatically replaced by '?,?,?' for values [1,2,3]
    ->query($storage)
    ->findAll();
    
// Above code is equivalent to next SQL(With placeholders):
// SELECT *
//   FROM fruits 
//   WHERE id IN (?,?,?)          // ? will be binded by: [1, 2, 3]

Example 8: Join table by using database storage

...
$storage->registerTableModel(new FruitsTableModel(), 'fruits');
$storage->registerTableModel(new FruitsStockTableModel(), 'fruits_stock');

$result = $conn->select()
            ->from('fruits', 'f')
            ->innerJoin('fruits_stock', 'fs.fruits_id = f.id', 'fs')
            ->where('stock', 'fs.stock > ?', 5)
            ->query($storage)
            ->execute();

// Above code is equivalent to next SQL(With placeholders):
// SELECT f.id,f.name,f.weight,f.quality,f.updated_date,fs.shop_id,fs.fruits_id,fs.stock
//   FROM fruits as f
//   INNER JOIN fruits_stock as fs on fs.fruits_id = f.id
//   WHERE fs.stock > 5

Example 9: Table model factory

...
// Closure or anonymous function
$storage->setTableModelFactory(function($table){
    switch($table){
    case 'fruits':
        return new FruitsTableModel();
    case 'fruits_stock':
        return new FruitsStockTableModel();
    }
});

// TableModelFactoryInterface
$storage->setTableModelFactory(
    new class implements TableModelFactoryInterface{
        public function createTableModel(string $table){
            switch($table){
            case 'fruits':
                return new FruitsTableModel();
            case 'fruits_stock':
                return new FruitsStockTableModel();
            }
        }
    }
);

// Above code is equivalent to next code:
// 
// $storage->registerTableModel(new FruitsTableModel(), 'fruits');
// $storage->registerTableModel(new FruitsStockTableModel(), 'fruits_stock');

Example 10: Events(DatabaseQuery#query())

use Stk2k\EventStream\Event;

$db->getEventChannel()->listen(Events::STORAGE_DB_QUERY_PERFORMED, function(Event $e){
    $args = $e->getPayload();
    $last_sql = $args['last_sql'] ?? '';
    $row_count = $args['row_count'] ?? 0;
    $elapsed_time = $args['elapsed_time'] ?? 0;

    echo 'last_sql: ' . $last_sql . PHP_EOL;
    echo 'row_count: ' . $row_count . PHP_EOL;
    echo 'elapsed_time: ' . $elapsed_time . ' msec' . PHP_EOL;
});

$db->sql('SELECT * FROM fruits WHERE name like ?', ['o%'])->findAll();
// last_sql: SELECT * FROM fruits WHERE name like 'o%'
// row_count: 0
// elapsed_time: 0.2029 msec

Example 11: Events(DatabaseQuery#execute())

use Stk2k\EventStream\Event;

$db->getEventChannel()->listen(Events::STORAGE_DB_QUERY_EXECUTED, function(Event $e){
    $args = $e->getPayload();
    $last_sql = $args['last_sql'] ?? '';
    $last_inserted_id = $args['last_inserted_id'] ?? 0;
    $affected_rows = $args['affected_rows'] ?? 0;
    $elapsed_time = $args['elapsed_time'] ?? 0;

    echo 'last_sql: ' . $last_sql . PHP_EOL;
    echo 'last_inserted_id: ' . $last_inserted_id . PHP_EOL;
    echo 'affected_rows: ' . $affected_rows . PHP_EOL;
    echo 'elapsed_time: ' . $elapsed_time . ' msec' . PHP_EOL;
});

$db->sql('DELETE FROM fruits WHERE id<3')->execute();
// last_sql: DELETE FROM fruits WHERE id<3
// last_inserted_id: 4
// affected_rows: 2
// elapsed_time: 74.7011 msec

$sql = "INSERT INTO `fruits` (`id`, `name`, `weight`, `quality`, `updated_date`) VALUES (null, 'melon', 1.3, 11, NULL);";
$db->sql($sql)->execute();

// last_sql: INSERT INTO `fruits` (`id`, `name`, `weight`, `quality`, `updated_date`) VALUES (null, 'melon', 1.3, 11, NULL);
// last_inserted_id: 5
// affected_rows: 1
// elapsed_time: 79.3831 msec

Requirement

PHP 7.2 or later

Installing knot-lib/datastore

The recommended way to install knot-lib/datastore is through Composer.

composer require knot-lib/datastore

After installing, you need to require Composer's autoloader:

require 'vendor/autoload.php';

License

This library is licensed under the MIT license.

Author

stk2k

Disclaimer

This software is no warranty.

We are not responsible for any results caused by the use of this software.

Please use the responsibility of the your self.

knot-lib / datastore

Maintainers

Details