psx/sql

从您的数据库生成类型安全的PHP类

维护者

详细信息

github.com/apioo/psx-sql

主页

源代码

问题

资助包维护！
chriskapp
Patreon
www.paypal.me/fusioapi

安装次数: 63,944

依赖项: 3

建议者: 0

安全性: 0

星标: 15

关注者: 3

分支: 1

开放问题: 2

v4.1.3 2024-07-23 18:46 UTC

Requires

Requires (Dev)

Apache-2.0 260fe0cfcea73993b8bec6f0d42e0875014f9da9

sql

This package is auto-updated.

Last update: 2024-08-23 19:05:55 UTC

README

此库可以从您的数据库表中生成类型安全的PHP类，因此您可以通过完全类型安全的方式与数据库交互。

关于

在传统的ORM中，您编写一个类并添加特定的元数据，然后根据此类生成您的表，这意味着我们的源代码定义了表应该如何看起来。此库采取相反的方法（以数据库为起点），这意味着您首先构建您的数据库模式，例如通过类似doctrine迁移的工具，然后您可以使用此库根据表模式自动生成所有仓库和模型类。这有一个很大的优点，我们可以生成完全类型化的仓库。我们自动为每行（实体）生成一个类，并为接受此行的仓库生成一个类。这个概念并不新颖，Java世界中的jOOQ也遵循这个想法。当然，这也意味着如果更改您的模式，您需要重新生成您的类。

生成

要生成表和行类，您可以将 PSX\Command\GenerateCommand 集成到您的Symfony控制台应用程序中，或者您也可以通过 PSX\Sql\Generator 类以编程方式完成此操作。

<?php

use PSX\Sql\Generator\Generator;

$connection   = null; // a doctrine DBAL connection
$target = __DIR__;

$generator = new Generator($connection, 'Acme\\Table');
foreach ($generator->generate() as $className => $source) {
    file_put_contents($target . '/' . $className . '.php', '<?php' . "\n\n" . $source);
}

基本用法

以下是一些基本示例，说明您如何使用生成的表类。

<?php

use PSX\Sql\Condition;
use PSX\Sql\OrderBy;
use PSX\Sql\TableManager;

$connection   = null; // a doctrine DBAL connection
$tableManager = new TableManager($connection);

/** @var \PSX\Sql\Tests\Generator\SqlTableTestTable $table */
$table = $tableManager->getTable(\PSX\Sql\Tests\Generator\SqlTableTestTable::class);

// returns by default 16 entries from the table ordered by the primary column descending
$table->findAll();

// returns 12 entries starting at index 0
$table->findAll(startIndex: 0, count: 12);

// orders the entries after the column "id" descending
$table->findAll(startIndex: 0, count: 12, sortBy: 'id', sortOrder: OrderBy::DESC);

// returns all rows which match the specified title
$table->findByTitle('foo%');

// returns a row by the primary key
$table->find(1);

// returns the count of entries in the table. It is also possible to provide a condition
$table->getCount();

// creates a new row
$row = new \PSX\Sql\Tests\Generator\SqlTableTestRow();
$row->setTitle('foo');
$table->create($row);

// updates a row
$row = $table->find(1);
$row->setTitle('bar');
$table->update($row);

// deletes a row
$row = $table->find(1);
$table->delete($row);

表

以下是一个生成的表类的示例。

<?php

namespace PSX\Sql\Tests\Generator;

/**
 * @extends \PSX\Sql\TableAbstract<\PSX\Sql\Tests\Generator\SqlTableTestRow>
 */
class SqlTableTestTable extends \PSX\Sql\TableAbstract
{
    public const NAME = 'psx_sql_table_test';
    public const COLUMN_ID = 'id';
    public const COLUMN_TITLE = 'title';
    public const COLUMN_DATE = 'date';
    public function getName() : string
    {
        return self::NAME;
    }
    public function getColumns() : array
    {
        return array(self::COLUMN_ID => 0x3020000a, self::COLUMN_TITLE => 0xa00020, self::COLUMN_DATE => 0x800000);
    }
    /**
     * @return array<\PSX\Sql\Tests\Generator\SqlTableTestRow>
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function findAll(?\PSX\Sql\Condition $condition = null, ?int $startIndex = null, ?int $count = null, ?string $sortBy = null, ?\PSX\Sql\OrderBy $sortOrder = null) : array
    {
        return $this->doFindAll($condition, $startIndex, $count, $sortBy, $sortOrder);
    }
    /**
     * @return array<\PSX\Sql\Tests\Generator\SqlTableTestRow>
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function findBy(\PSX\Sql\Condition $condition, ?int $startIndex = null, ?int $count = null, ?string $sortBy = null, ?\PSX\Sql\OrderBy $sortOrder = null) : array
    {
        return $this->doFindBy($condition, $startIndex, $count, $sortBy, $sortOrder);
    }
    /**
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function findOneBy(\PSX\Sql\Condition $condition) : ?\PSX\Sql\Tests\Generator\SqlTableTestRow
    {
        return $this->doFindOneBy($condition);
    }
    /**
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function find(int $id) : ?\PSX\Sql\Tests\Generator\SqlTableTestRow
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->equals('id', $id);
        return $this->doFindOneBy($condition);
    }
    /**
     * @return array<\PSX\Sql\Tests\Generator\SqlTableTestRow>
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function findById(int $value, ?int $startIndex = null, ?int $count = null, ?string $sortBy = null, ?\PSX\Sql\OrderBy $sortOrder = null) : array
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->equals('id', $value);
        return $this->doFindBy($condition, $startIndex, $count, $sortBy, $sortOrder);
    }
    /**
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function findOneById(int $value) : ?\PSX\Sql\Tests\Generator\SqlTableTestRow
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->equals('id', $value);
        return $this->doFindOneBy($condition);
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function updateById(int $value, \PSX\Sql\Tests\Generator\SqlTableTestRow $record) : int
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->equals('id', $value);
        return $this->doUpdateBy($condition, $record->toRecord());
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function deleteById(int $value) : int
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->equals('id', $value);
        return $this->doDeleteBy($condition);
    }
    /**
     * @return array<\PSX\Sql\Tests\Generator\SqlTableTestRow>
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function findByTitle(string $value, ?int $startIndex = null, ?int $count = null, ?string $sortBy = null, ?\PSX\Sql\OrderBy $sortOrder = null) : array
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->like('title', $value);
        return $this->doFindBy($condition, $startIndex, $count, $sortBy, $sortOrder);
    }
    /**
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function findOneByTitle(string $value) : ?\PSX\Sql\Tests\Generator\SqlTableTestRow
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->like('title', $value);
        return $this->doFindOneBy($condition);
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function updateByTitle(string $value, \PSX\Sql\Tests\Generator\SqlTableTestRow $record) : int
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->like('title', $value);
        return $this->doUpdateBy($condition, $record->toRecord());
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function deleteByTitle(string $value) : int
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->like('title', $value);
        return $this->doDeleteBy($condition);
    }
    /**
     * @return array<\PSX\Sql\Tests\Generator\SqlTableTestRow>
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function findByDate(\PSX\DateTime\LocalDateTime $value, ?int $startIndex = null, ?int $count = null, ?string $sortBy = null, ?\PSX\Sql\OrderBy $sortOrder = null) : array
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->equals('date', $value);
        return $this->doFindBy($condition, $startIndex, $count, $sortBy, $sortOrder);
    }
    /**
     * @throws \PSX\Sql\Exception\QueryException
     */
    public function findOneByDate(\PSX\DateTime\LocalDateTime $value) : ?\PSX\Sql\Tests\Generator\SqlTableTestRow
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->equals('date', $value);
        return $this->doFindOneBy($condition);
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function updateByDate(\PSX\DateTime\LocalDateTime $value, \PSX\Sql\Tests\Generator\SqlTableTestRow $record) : int
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->equals('date', $value);
        return $this->doUpdateBy($condition, $record->toRecord());
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function deleteByDate(\PSX\DateTime\LocalDateTime $value) : int
    {
        $condition = \PSX\Sql\Condition::withAnd();
        $condition->equals('date', $value);
        return $this->doDeleteBy($condition);
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function create(\PSX\Sql\Tests\Generator\SqlTableTestRow $record) : int
    {
        return $this->doCreate($record->toRecord());
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function update(\PSX\Sql\Tests\Generator\SqlTableTestRow $record) : int
    {
        return $this->doUpdate($record->toRecord());
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function updateBy(\PSX\Sql\Condition $condition, \PSX\Sql\Tests\Generator\SqlTableTestRow $record) : int
    {
        return $this->doUpdateBy($condition, $record->toRecord());
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function delete(\PSX\Sql\Tests\Generator\SqlTableTestRow $record) : int
    {
        return $this->doDelete($record->toRecord());
    }
    /**
     * @throws \PSX\Sql\Exception\ManipulationException
     */
    public function deleteBy(\PSX\Sql\Condition $condition) : int
    {
        return $this->doDeleteBy($condition);
    }
    /**
     * @param array<string, mixed> $row
     */
    protected function newRecord(array $row) : \PSX\Sql\Tests\Generator\SqlTableTestRow
    {
        return \PSX\Sql\Tests\Generator\SqlTableTestRow::from($row);
    }
}

行

以下是一个生成的表行的示例。

<?php

namespace PSX\Sql\Tests\Generator;

class SqlTableTestRow implements \JsonSerializable, \PSX\Record\RecordableInterface
{
    private ?int $id = null;
    private ?string $title = null;
    private ?\PSX\DateTime\LocalDateTime $date = null;
    public function setId(int $id) : void
    {
        $this->id = $id;
    }
    public function getId() : int
    {
        return $this->id;
    }
    public function setTitle(string $title) : void
    {
        $this->title = $title;
    }
    public function getTitle() : string
    {
        return $this->title;
    }
    public function setDate(\PSX\DateTime\LocalDateTime $date) : void
    {
        $this->date = $date;
    }
    public function getDate() : \PSX\DateTime\LocalDateTime
    {
        return $this->date;
    }
    public function toRecord() : \PSX\Record\RecordInterface
    {
        $record = new \PSX\Record\Record();
        $record->put('id', $this->id);
        $record->put('title', $this->title);
        $record->put('date', $this->date);
        return $record;
    }
    public function jsonSerialize() : object
    {
        return (object) $this->toRecord()->getAll();
    }
    public static function from(array|\ArrayAccess $data) : self
    {
        $row = new self();
        $row->id = $data['id'] ?? null;
        $row->title = $data['title'] ?? null;
        $row->date = isset($data['date']) ? \PSX\DateTime\LocalDateTime::from($data['date']) : null;
        return $row;
    }
}