README

使用数据库服务，您可以轻松创建和管理数据库。

目录

• 入门
  • 需求
  • 亮点
• 文档
  • 数据库
    • 创建数据库
    • 添加数据库
    • 获取数据库
    • 默认数据库
  • PDO数据库
    • 创建PDO数据库
    • Pdo Database Factory
    • 使用PDO数据库
  • 迁移
    • 表模式
      • 列类型
      • 重命名和删除
      • 列参数
      • 索引
      • 外键
      • 项和填充
        • 项
        • Item Factory
        • Json文件项
      • Table Factory
      • Column Factory
      • Index Factory
    • 处理器
      • Pdo MySql Processor
      • Stack Processor
    • 存储
      • Pdo MySql Storage
      • Stack Storage
      • 自定义存储
    • 安全
    • 迁移器
      • 创建迁移
      • 创建迁移填充器
      • 安装和卸载迁移
• 致谢

入门

使用以下命令添加正在运行的数据库服务项目的最新版本。

composer require tobento/service-database

需求

• PHP 8.0或更高版本

亮点

• 框架无关，与任何项目兼容
• 解耦设计
• 数据库管理
• 简单的PDO数据库包装器
• 支持使用表模式构建器和填充项进行迁移

文档

数据库

创建数据库

use Tobento\Service\Database\Databases;
use Tobento\Service\Database\DatabasesInterface;

$databases = new Databases();

var_dump($databases instanceof DatabasesInterface);
// bool(true)

添加数据库

添加

use Tobento\Service\Database\Databases;
use Tobento\Service\Database\PdoDatabase;
use Tobento\Service\Database\DatabaseInterface;
use PDO;

$databases = new Databases();

$database = new PdoDatabase(
    pdo: new PDO('sqlite::memory:'),
    name: 'name',
);

var_dump($database instanceof DatabaseInterface);
// bool(true)

$databases->add($database);

注册

您可以使用register方法仅在请求时创建数据库。

use Tobento\Service\Database\Databases;
use Tobento\Service\Database\PdoDatabase;
use Tobento\Service\Database\DatabaseInterface;
use PDO;

$databases = new Databases();

$databases->register(
    'name',
    function(string $name): DatabaseInterface {        
        return new PdoDatabase(
            new PDO('sqlite::memory:'),
            $name
        );
    }
);

获取数据库

如果数据库不存在或无法创建，将抛出DatabaseException。

use Tobento\Service\Database\DatabaseInterface;
use Tobento\Service\Database\DatabaseException;

$database = $databases->get('name');

var_dump($database instanceof DatabaseInterface);
// bool(true)

$databases->get('unknown');
// throws DatabaseException

您可以使用has方法检查数据库是否存在。

var_dump($databases->has('name'));
// bool(false)

默认数据库

您可以为您的应用程序设计添加默认数据库。

use Tobento\Service\Database\Databases;
use Tobento\Service\Database\PdoDatabase;
use Tobento\Service\Database\DatabaseInterface;
use Tobento\Service\Database\DatabaseException;
use PDO;

$databases = new Databases();

$databases->add(
    new PdoDatabase(new PDO('sqlite::memory:'), 'sqlite')
);

// add default
$databases->addDefault(name: 'primary', database: 'sqlite');

// get default database for the specified name.
$primaryDatabase = $databases->default('primary');

var_dump($primaryDatabase instanceof DatabaseInterface);
// bool(true)

var_dump($databases->hasDefault('primary'));
// bool(true)

var_dump($databases->getDefaults());
// array(1) { ["primary"]=> string(6) "sqlite" }

$databases->default('unknown');
// throws DatabaseException

PDO数据库

创建PDO数据库

use Tobento\Service\Database\PdoDatabase;
use Tobento\Service\Database\DatabaseInterface;
use Tobento\Service\Database\PdoDatabaseInterface;
use PDO;

$database = new PdoDatabase(
    pdo: new PDO('sqlite::memory:'),
    name: 'sqlite',
);

var_dump($database instanceof DatabaseInterface);
// bool(true)

var_dump($database instanceof PdoDatabaseInterface);
// bool(true)

Pdo Database Factory

您可以使用工厂轻松创建数据库。

createDatabase

use Tobento\Service\Database\PdoDatabaseFactory;
use Tobento\Service\Database\DatabaseFactoryInterface;
use Tobento\Service\Database\PdoDatabaseInterface;
use Tobento\Service\Database\DatabaseInterface;
use Tobento\Service\Database\DatabaseException;
use PDO;

$factory = new PdoDatabaseFactory();

var_dump($factory instanceof DatabaseFactoryInterface);
// bool(true)

$database = $factory->createDatabase(
    name: 'mysql',
    config: [
        'driver' => 'mysql',
        'host' => 'localhost',
        'port' => null,
        'database' => 'db_name',
        'charset' => 'utf8mb4',
        'username' => 'root',
        'password' => '',
        'options' => [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
        ],
    ],
);
// throws DatabaseException on failure

var_dump($database instanceof PdoDatabaseInterface);
// bool(true)

var_dump($database instanceof DatabaseInterface);
// bool(true)

// with dsn parameter
$database = $factory->createDatabase(
    name: 'sqlite',
    config: [
        'dsn' => 'sqlite::memory:',
        'username' => '',
        'password' => '',
        'options' => [],
    ],
);

createPdo

use Tobento\Service\Database\PdoDatabaseFactory;
use Tobento\Service\Database\PdoFactoryInterface;
use Tobento\Service\Database\DatabaseException;
use PDO;

$factory = new PdoDatabaseFactory();

var_dump($factory instanceof PdoFactoryInterface);
// bool(true)

$pdo = $factory->createPdo(
    name: 'sqlite',
    config: [
        'dsn' => 'sqlite::memory:',
        'username' => '',
        'password' => '',
        'options' => [],
    ],
);
// throws DatabaseException on failure

var_dump($pdo instanceof PDO);
// bool(true)

使用PDO数据库

使用execute方法执行任何预编译语句。

use PDOStatement;

$statement = $database->execute(
    statement: 'ALTER TABLE products ADD color varchar(60)'
);

var_dump($statement instanceof PDOStatement);
// bool(true)

选择

$products = $database->execute(
    statement: 'SELECT title FROM products WHERE color = ?',
    bindings: ['blue']
)->fetchAll();

// with named parameters, order free
$products = $database->execute(
    'SELECT title FROM products WHERE color = :color',
    ['color' => 'blue']
)->fetchAll();

插入

$database->execute(
    statement: 'INSERT INTO shop_products (name, color) VALUES (?, ?)',
    bindings: ['Shirt', 'blue'],
);

// you might return the number of rows affected:
$rowsAffected = $database->execute(
    'INSERT INTO shop_products (name, color) VALUES (?, ?)',
    ['Shirt', 'blue']
)->rowCount();

var_dump($rowsAffected);
// int(1)

更新

执行插入语句并返回受影响的行数。

$rowsAffected = $database->execute(
    statement: 'UPDATE products SET name = ? WHERE color = ?',
    bindings: ['Shirt', 'blue']
)->rowCount();

var_dump($rowsAffected);
// int(2)

删除

执行删除语句并返回受影响的行数。

$rowsAffected = $database->execute(
    statement: 'DELETE FROM shop_products WHERE color = ?',
    bindings: ['blue']
)->rowCount();

var_dump($rowsAffected);
// int(2)

事务

您可以使用事务方法在事务中运行一系列数据库操作。如果在事务闭包中抛出异常，事务将自动回滚。如果闭包成功执行，事务将自动提交。

use Tobento\Service\Database\PdoDatabaseInterface;

$database->transaction(function(PdoDatabaseInterface $db): void {

    $db->execute(
        'UPDATE products SET active = ? WHERE color = ?',
        [true, 'red']
    );
    
    $db->execute(
        'UPDATE products SET active = ? WHERE color = ?',
        [false, 'bar']
    );    
});

提交

$database->begin();

// your queries

$database->commit();

回滚

$database->begin();

// your queries

$database->rollback();

支持嵌套事务

var_dump($database->supportsNestedTransactions());
// bool(true)

PDO

use PDO;

var_dump($database->pdo() instanceof PDO);
// bool(true)

迁移

表模式

表模式由处理器和存储用于迁移处理。

use Tobento\Service\Database\Schema\Table;

$table = new Table(name: 'products');
$table->primary('id');
$table->string('name', 100)->nullable(false)->default('');
$table->bool('active', true);

列类型

可用类型

Lengthable

设置列长度。

$table->string('name')->length(21);

Nullable

将列设置为NOT NULL，使用带有false参数的nullable方法

$table->string('name')->nullable(false);

Defaultable

为列设置默认值。

$table->bool('name')->default(true);

Unsignable

将列设置为UNSIGNED。

$table->int('name')->unsigned(true);

主键 / 大主键

主键和大主键列仅在创建时设置。

重命名和删除

renameColumn

$table->renameColumn('column', 'new_column');

dropColumn

$table->dropColumn('column');

renameTable

$table->renameTable('new_name');

dropTable

$table->dropTable();

truncate

$table->truncate();

列参数

charset

您可能设置列的字符集。

$table->string('column')->parameter('charset', 'utf8mb4');

collation

您可能设置列的校对规则。

$table->string('column')->parameter('collation', 'utf8mb4_roman_ci');

索引

简单索引

$table->index('index_name')->column('name');

组合索引

$table->index('index_name')->column('name', 'another_name');

简单唯一索引

$table->index('index_name')->column('name')->unique();

组合唯一索引

$table->index('index_name')->column('name', 'another_name')->unique();

主键索引

$table->index()->column('name')->primary();

重命名索引

$table->index('index_name')->rename('new_name');

删除索引

$table->index('index_name')->drop();

外键

尚未支持！

项和填充

项

use Tobento\Service\Database\Schema\ItemsInterface;

$items = $table->items(iterable: [
    ['name' => 'Foo', 'active' => true],
    ['name' => 'Bar', 'active' => true],
    // ...
])
->chunk(length: 100)
->useTransaction(false) // default is true
->forceInsert(true); // default is false

var_dump($items instanceof ItemsInterface);
// bool(true)

块

您可以在有多个项的情况下调整块长度参数以实现速度提升。

useTransaction

如果设置为 true，在数据库支持的情况下，处理时会使用事务。

forceInsert

如果设置为 true，则始终插入项目；否则，只有在还没有项目时才会插入。

Item Factory

您可以使用项目工厂迭代器来初始化项目，并使用Seeder 服务来生成假数据。

use Tobento\Service\Iterable\ItemFactoryIterator;
use Tobento\Service\Seeder\Str;
use Tobento\Service\Seeder\Arr;

$table->items(new ItemFactoryIterator(
    factory: function(): array {
        return [
            'name' => Str::string(10),
            'color' => Arr::item(['green', 'red', 'blue']),
        ];
    },
    create: 1000000 // create 1 million items
))
->chunk(length: 10000)
->useTransaction(false) // default is true
->forceInsert(true); // default is false

Json文件项

use Tobento\Service\Iterable\JsonFileIterator;
use Tobento\Service\Iterable\ModifyIterator;

$iterator = new JsonFileIterator(
    file: 'private/src/countries.json',
);

// you may use the modify iterator:
$iterator = new ModifyIterator(
    iterable: $iterator,
    modifier: function(array $item): array {
        return [
          'iso' => $item['iso'] ?? '',
          'name' => $item['country'] ?? '',
        ];
    }
);
        
$table->items($iterator)
      ->chunk(length: 100)
      ->useTransaction(true) // default is true
      ->forceInsert(false); // default is false

Table Factory

您可以使用表工厂来创建一个表。

use Tobento\Service\Database\Schema\TableFactoryInterface;
use Tobento\Service\Database\Schema\TableFactory;
use Tobento\Service\Database\Schema\Table;

$tableFactory = new TableFactory();

var_dump($tableFactory instanceof TableFactoryInterface);
// bool(true)

$table = $tableFactory->createTable(name: 'users');

var_dump($table instanceof Table);
// bool(true)

Column Factory

您可以使用列工厂来创建一个列。

use Tobento\Service\Database\Schema\ColumnFactoryInterface;
use Tobento\Service\Database\Schema\ColumnFactory;

$columnFactory = new ColumnFactory();

var_dump($columnFactory instanceof ColumnFactoryInterface);
// bool(true)

createColumn

use Tobento\Service\Database\Schema\ColumnFactory;
use Tobento\Service\Database\Schema\ColumnInterface;
use Tobento\Service\Database\Schema\CreateColumnException;

try {
    $column = (new ColumnFactory())->createColumn(type: 'int', name: 'foo');
    
    var_dump($column instanceof ColumnInterface);
    // bool(true)
    
} catch(CreateColumnException $e) {
    //
}

查看其类型名称支持的列类型。

createColumnFromArray

use Tobento\Service\Database\Schema\ColumnFactory;
use Tobento\Service\Database\Schema\ColumnInterface;
use Tobento\Service\Database\Schema\CreateColumnException;

try {
    $column = (new ColumnFactory())->createColumnFromArray([
        'type' => 'int',
        'name' => 'foo',
    ]);
    
    var_dump($column instanceof ColumnInterface);
    // bool(true)
    
} catch(CreateColumnException $e) {
    //
}

可测量、可为空、有默认值、不可符号和参数列定义

use Tobento\Service\Database\Schema\ColumnFactory;

$column = (new ColumnFactory())->createColumnFromArray([
    'type' => 'int',
    'name' => 'foo',
    
    'length' => 99,
    'nullable' => false,
    'default' => 'value',
    'unsigned' => true,
    
    'parameters' => [
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_roman_ci',
    ],
    
    // Decimal column
    'precision' => 8,
    'scale' => 4,
]);

Index Factory

use Tobento\Service\Database\Schema\IndexFactoryInterface;
use Tobento\Service\Database\Schema\IndexFactory;

$indexFactory = new IndexFactory();

var_dump($indexFactory instanceof IndexFactoryInterface);
// bool(true)

createIndex

use Tobento\Service\Database\Schema\IndexFactory;
use Tobento\Service\Database\Schema\IndexInterface;
use Tobento\Service\Database\Schema\CreateIndexException;

try {
    $index = (new IndexFactory())->createIndex(name: 'foo');
    
    var_dump($index instanceof IndexInterface);
    // bool(true)
    
} catch(CreateIndexException $e) {
    //
}

createIndexFromArray

use Tobento\Service\Database\Schema\IndexFactory;
use Tobento\Service\Database\Schema\IndexInterface;
use Tobento\Service\Database\Schema\CreateIndexException;

try {
    $index = (new IndexFactory())->createIndexFromArray([
        'name' => 'foo',
    ]);
    
    var_dump($index instanceof IndexInterface);
    // bool(true)
    
} catch(CreateIndexException $e) {
    //
}

其他参数定义

use Tobento\Service\Database\Schema\IndexFactory;

$index = (new IndexFactory())->createIndexFromArray([
    'name' => 'foo',
    
    'column' => 'name',
    // or multiple
    'column' => ['name', 'another_name'],
    
    'unique' => true,
    'primary' => true,
    
    'rename' => 'newname',
    'drop' => true,
]);

处理器

处理器用于在指定的数据库上处理表。

Pdo MySql Processor

处理器将自动确定是否添加或修改表列和索引。

use Tobento\Service\Database\Processor\PdoMySqlProcessor;
use Tobento\Service\Database\Processor\ProcessorInterface;
use Tobento\Service\Database\Processor\ProcessException;
use Tobento\Service\Database\PdoDatabaseInterface;
use Tobento\Service\Database\Schema\Table;

$processor = new PdoMySqlProcessor();

var_dump($processor instanceof ProcessorInterface);
// bool(true)

try {
    $processor->process(
        $table, // Table
        $database // PdoDatabaseInterface
    );    
} catch (ProcessException $e) {
    // Handle exception.
}

您可以为处理器创建一个自定义存储。默认存储 Pdo MySql Storage 将查询数据库以创建当前表，以确定修改。

use Tobento\Service\Database\Processor\PdoMySqlProcessor;

$processor = new PdoMySqlProcessor(new CustomStorage());

Stack Processor

您可以使用 "stack processor" 来支持多个数据库。只有第一个支持指定数据库的处理器才会处理操作。

use Tobento\Service\Database\Processor\Processors;
use Tobento\Service\Database\Processor\PdoMySqlProcessor;
use Tobento\Service\Database\Processor\ProcessorInterface;
use Tobento\Service\Database\Processor\ProcessException;

$processors = new Processors(
    new PdoMySqlProcessor(),
);

var_dump($processor instanceof ProcessorInterface);
// bool(true)

try {
    $processors->process($table, $database);
} catch (ProcessException $e) {
    //
}

存储

存储用于检索当前表或存储处理后的数据。

Pdo MySql Storage

fetchTable

存储将查询数据库以创建当前表。

use Tobento\Service\Database\Processor\PdoMySqlStorage;
use Tobento\Service\Database\Processor\StorageInterface;
use Tobento\Service\Database\Processor\StorageFetchException;
use Tobento\Service\Database\PdoDatabaseInterface;
use Tobento\Service\Database\Schema\Table;

$storage = new PdoMySqlStorage();

var_dump($storage instanceof StorageInterface);
// bool(true)

try {
    $table = $storage->fetchTable(
        $database, // PdoDatabaseInterface
        'table_name'
    );
    
    var_dump($table instanceof Table);
    // bool(true) or NULL if table does not exist.

} catch (StorageFetchException $e) {
    // Handle exception.
}

storeTable

没有存储表数据，因为检索将创建表。

use Tobento\Service\Database\Processor\PdoMySqlStorage;
use Tobento\Service\Database\Processor\StorageInterface;
use Tobento\Service\Database\Processor\StorageStoreException;
use Tobento\Service\Database\PdoDatabaseInterface;
use Tobento\Service\Database\Schema\Table;

$storage = new PdoMySqlStorage();

var_dump($storage instanceof StorageInterface);
// bool(true)

try {
    $storage->storeTable(
        $database, // PdoDatabaseInterface
        $table // Table
    );

} catch (StorageStoreException $e) {
    // Handle exception.
}

Stack Storage

您可以使用 "stack storage" 来支持多个数据库。只有第一个支持指定数据库的存储才会处理操作。

use Tobento\Service\Database\Processor\Storages;
use Tobento\Service\Database\Processor\PdoMySqlStorage;
use Tobento\Service\Database\Processor\StorageInterface;
use Tobento\Service\Database\Processor\StorageStoreException;

$storages = new Storages(
    new PdoMySqlStorage(),
);

var_dump($storages instanceof StorageInterface);
// bool(true)

try {
    $storages->storeTable($database, $table);
} catch (StorageStoreException $e) {
    // Handle exception.
}

自定义存储

您可以为处理器或作为独立使用创建自定义存储。

use Tobento\Service\Database\Processor\PdoMySqlProcessor;
use Tobento\Service\Database\Processor\StorageInterface;
use Tobento\Service\Database\Processor\StorageFetchException;
use Tobento\Service\Database\Processor\StorageStoreException;
use Tobento\Service\Database\Processor\ProcessException;
use Tobento\Service\Database\Schema\Table;
use Tobento\Service\Database\DatabaseInterface;

class CustomStorage implements StorageInterface
{
    /**
     * Returns true if the processor supports the database, otherwise false.
     *
     * @param DatabaseInterface $database
     * @return bool
     */
    public function supportsDatabase(DatabaseInterface $database): bool
    {
        return true;
    }
    
    /**
     * Returns the specified table if exist, otherwise null.
     *
     * @param DatabaseInterface $database
     * @param string $name The table name
     * @return null|Table
     * @throws StorageFetchException
     */    
    public function fetchTable(DatabaseInterface $database, string $table): null|Table
    {
        // your logic.
        return null;
    }
    
    /**
     * Store the table.
     *
     * @param DatabaseInterface $database
     * @param string $name
     * @return void
     * @throws StorageStoreException
     */    
    public function storeTable(DatabaseInterface $database, Table $table): void
    {
        // your logic.
    }
}

try {
    $table = (new CustomStorage())->fetchTable($database, 'table_name');
} catch (ProcessException $e) {
    // Handle exception.
}

安全

⚠️ 请勿在没有适当白名单的情况下使用用户提供的数据创建表模式！

迁移器

您可能需要安装并使用迁移服务进行迁移处理。

创建迁移

通过扩展 DatabaseMigration::class 创建迁移类。

使用 registerTables 方法

您可以使用 registerTables 方法来注册表，以便进行安装和卸载过程。

use Tobento\Service\Database\Migration\DatabaseMigration;
use Tobento\Service\Database\Schema\Table;

class DbMigrations extends DatabaseMigration
{
    public function description(): string
    {
        return 'db migrations';
    }

    /**
     * Register tables used by the install and uninstall methods
     * to create the actions from.
     *
     * @return void
     */
    protected function registerTables(): void
    {
        $this->registerTable(
            table: function(): Table {
                $table = new Table(name: 'users');
                $table->primary('id');
                return $table;
            },
            database: $this->databases->default('pdo'),
            name: 'Users',
            description: 'Users desc',
        );
        
        $this->registerTable(
            table: function(): Table {
                $table = new Table(name: 'products');
                $table->primary('id');
                return $table;
            },
            database: $this->databases->default('pdo'),
        );
    }
}

查看表模式的文档。

使用 install 和 uninstall 方法

您可以使用 install 和 uninstall 方法来指定您自己的操作。

use Tobento\Service\Database\Migration\DatabaseMigration;
use Tobento\Service\Database\Migration\DatabaseAction;
use Tobento\Service\Database\Migration\DatabaseDeleteAction;
use Tobento\Service\Database\Schema\Table;
use Tobento\Service\Migration\ActionsInterface;
use Tobento\Service\Migration\Actions;

class DbMigrations extends DatabaseMigration
{
    public function description(): string
    {
        return 'db migrations';
    }

    /**
     * Return the actions to be processed on install.
     *
     * @return ActionsInterface
     */
    public function install(): ActionsInterface
    {
        return new Actions(
            new DatabaseAction(
                processor: $this->processor,
                database: $this->databases->default('pdo'),
                table: function(): Table {
                    $table = new Table(name: 'products');
                    $table->primary('id');
                    return $table;
                },
                name: 'Products',
                description: 'Products table installed',
            ),
        );
    }
    
    /**
     * Return the actions to be processed on uninstall.
     *
     * @return ActionsInterface
     */
    public function uninstall(): ActionsInterface
    {
        return $this->createDatabaseDeleteActionsFromInstall();
        
        // or manually:
        return new Actions(
            new DatabaseDeleteAction(
                processor: $this->processor,
                database: $this->databases->default('pdo'),
                table: new Table(name: 'products'),
                name: 'Products',
                description: 'Products table uninstalled',
            ),
        );
    }
}

查看表模式的文档。

创建迁移填充器

首先，您需要安装Seeder 服务并将 SeedInterface::class 实现绑定到您的容器中，以便在 DatabaseMigrationSeeder::class 上注入。

接下来，通过扩展 DatabaseMigrationSeeder::class 创建迁移种子类。

然后使用 registerTables 方法为安装过程注册表。

use Tobento\Service\Database\Migration\DatabaseMigrationSeeder;
use Tobento\Service\Database\Schema\Table;
use Tobento\Service\Iterable\ItemFactoryIterator;

class DbMigrationsSeeder extends DatabaseMigrationSeeder
{
    public function description(): string
    {
        return 'db migrations seeding';
    }

    /**
     * Register tables used by the install method
     * to create the actions from.
     * The uninstall method returns empty actions.
     *
     * @return void
     */
    protected function registerTables(): void
    {
        $this->registerTable(
            table: function(): Table {
                $table = new Table(name: 'users');
                // no need to specifiy columns again
                // if you the table migrated before.
                
                // seeding:
                $table->items(new ItemFactoryIterator(
                    factory: function(): array {
                        return [
                            'name' => $this->seed->fullname(),
                            'email' => $this->seed->email(),
                        ];
                    },
                    create: 10000
                ))
                ->chunk(length: 2000)
                ->useTransaction(false) // default is true
                ->forceInsert(true); // default is false
                
                return $table;
            },
            database: $this->databases->default('pdo'),
        );
    }
}

查看Seeder 服务的文档。

查看表模式的文档。

安装和卸载迁移

$result = $migrator->install(DbMigrations::class);

$result = $migrator->uninstall(DbMigrations::class);

查看以下迁移服务文档以了解更多信息。

• 创建迁移器
• 安装迁移
• 卸载迁移

致谢

• Tobias Strub
• 所有贡献者