README

描述

Calgamo/DataStore是一个仓库-实体-存储模式的数据访问库。

特性

• 流畅的查询构建器
• 懒加载连接
• 多连接
• DBMS特定SQL（SqlProviderInterface）
• 单/复合主键
• 事务（begin/rollback/commit/savepoint）
• 支持子查询

如何使用

配置数据库连接

use Calgamo\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');

数据库存储

use Calgamo\DataStore\Storage\Database\DatabaseStorage;

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

数据库连接

use Calgamo\DataStore\Storage\Database\DatabaseConnection;

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

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

示例 1：执行原始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}]

示例 2：执行原始计数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

示例 3：执行原始插入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}

示例 4：查询构建器 & 连接

...

$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}]

示例 5：实体 & 仓库

use Calgamo\DataStore\Sample\FruitsRepository;
use Calgamo\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

示例 6：批量插入

...

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

示例 7：缩写占位符

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

示例 8：使用数据库存储连接表

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

示例 9：表模型工厂

...
// 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');

示例 10：事件（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

示例 11：事件（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

要求

PHP 7.1或更高版本

安装 calgamolib/datastore

推荐通过Composer安装calgamolib/datastore。

composer require calgamolib/datastore

安装后，您需要要求Composer的自动加载器

require 'vendor/autoload.php';

许可证

本库采用MIT许可证。

作者

stk2k

免责声明

本软件不提供任何保证。

我们不承担因使用本软件而引起的任何结果的任何责任。

请自行承担责任。