I've noticed the following line in my db logs:

WARNING:  SET TRANSACTION can only be used in transaction blocks

Spiral's database driver first sets the isolation level and then begins the transaction. That's the expected approach for MySQL, but not for PostgreSQL, unfortunately. According to PostgreSQL docs:

If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it emits a warning and otherwise has no effect.

It is possible to dispense with SET TRANSACTION by instead specifying the desired transaction_modes in BEGIN or START TRANSACTION. But that option is not available for SET TRANSACTION SNAPSHOT.

This is a serious bug for critical use cases where strict ACID guarantees are required.

Simply define more exception types, create valid tree. @see PDODriver::clarifyException

При наличии ManyToMany связи между сущностями в случае добавления новых дочерних сущностей на этапе persist основной сущности ORM пытается вставить уже существующую связь.
Пример:
Сущности
Основная сущность Item

/**
 * @Cycle\Entity(
 *     table = "items",
 *     repository="App\Repository\ItemRepository"
 * )
 * @Cycle\Table(
 *     indexes={
 *          @Cycle\Table\Index(columns={"title"}, unique=true)
 *     }
 * )
 */
class Item
{
    /**
     * @Cycle\Column(type="string")
     */
    public string $title;

    /**
     * @Cycle\Relation\ManyToMany(target=Label::class, though="App\Entity\Through\ItemLabel")
     */
    protected PivotedCollectionInterface $labels;

    /**
     * @Cycle\Column(type = "bigPrimary", name = "id")
     */
    protected ?int $id = null;

    public function __construct(string $title)
    {
        $this->title = $title;

        $this->labels = new PivotedCollection();
    }
	
	public function getId(): ?int
    {
        return $this->id;
    }

    public function getResources(): PivotedCollectionInterface
    {
        return $this->labels;
    }

    public function hasLabel(string $label): bool
    {
        return $this->labels
                ->filter(
                    fn (Label $label) => $label->label === $label
                )
                ->count() > 0;
    }

    public function addLabel(Label $label): void
    {
        if (!$this->hasLabel($label->label)) {
            $this->labels->add($label);
        }
    }

    /**
     * @param string[] $labels
     *
     * @return PivotedCollectionInterface
     */
    public function filterLabels(array $labels): PivotedCollectionInterface
    {
        $this->labels = $this->labels
            ->filter(
                static fn (Label $label) => in_array($label->label, $labels, true)
            );

        return $this->labels;
    }
}

Дочерняя сущность Label

/**
 * @Cycle\Entity(
 *     table = "labels",
 *     repository="App\Repository\LabelRepository"
 * )
 * @Cycle\Table(
 *     indexes={
 *          @Cycle\Table\Index(columns={"label"}, unique=true)
 *     }
 * )
 */
class Label extends AbstractEntity
{
    /**
     * @Cycle\Column(type="string(50)")
     */
    public string $label;

    /**
     * @Cycle\Column(type="string")
     */
    public string $title;

    /**
     * @Cycle\Column(type = "bigPrimary", name = "id")
     */
    protected ?int $id = null;

    public function __construct(string $label, string $title)
    {
        $this->label = $label;
        $this->title = $title;
    }
	
	public function getId(): ?int
    {
        return $this->id;
    }
}

Создание тестовых данных

$label1 = new Label('lbl1', 'Label 1');
$label2 = new Label('lbl2', 'Label 2');
$label3 = new Label('lbl3', 'Label 3');

$item = new Item('Item 1');
$item->addLabel($label1);

$item2 = new Item('Item 2');
$item2->addLabel($label1);
$item2->addLabel($label2);

$transaction->persist($item);
$transaction->persist($item2);
$transaction->persist($label3);
$transaction->run();

Код для сравнения меток и добавления новых меток

$item = $itemRepository->findOne(['title' => 'Item 1']);

if (!$item instanceof Item) {
    throw new \Exception('No item!');
}

// Required labels list
$labels = ['lbl1', 'lbl3'];

// add  missing labels
foreach ($labels as $label) {
    if (!$item->hasLabel($label)) {
        $item->addLabel($this->receiveLabel($label, $labelRepository));
    }
}

// filter removed labels
$item->filterLabels($labels);

$transaction->persist($item);
$transaction->run();

В результате получаю такой SQL:

[2020-10-29 07:33:20] INFO: Begin transaction []
[2020-10-29 07:33:20] ERROR: INSERT INTO "item_label" ("item_id", "label_id") VALUES (1, 1) RETURNING "id" {"elapsed":0.00952005386352539,"rowCount":0}
[2020-10-29 07:33:20] ALERT: SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "item_label_index_item_id_label_id_5f9acd7e9384f" DETAIL:  Key (item_id, label_id)=(1, 1) already exists. []
[2020-10-29 07:33:20] INFO: Rollback transaction []

То есть происходит попытка добавления уже существующей связи. Разве на persist это не должно отслеживаться?

Feature request: disable orm aliasing

В некоторых "субд" не поддерживается алиасинг для таблиц, например к SphinxSearch/ManticoreSearch можно коннектиться по mysql протоколу, но запросы вида

SELECT `profileSearch`.`id` AS `c0` FROM `profile` AS `profileSearch`

вызывают ошибку. Алиасы там работают только такие:

SELECT `id` AS `c0` FROM `profile`

Чтоб всё работало необходимо добавить настройку на уровне коннекшена в database.php которая отключала бы в орм механизм алиасов.

Feature request: disable orm aliasing

In some "DBMS" aliasing for tables is not supported, for example, SphinxSearch / ManticoreSearch can be connected via the mysql protocol, but queries of the form

SELECT `profileSearch`.`id` AS` c0` FROM `profile` AS` profileSearch`

cause an error. Aliases only work there:

SELECT `id` AS` c0` FROM` profile`

In order for everything to work, you need to add a connection-level setting to database.php that would disable alias mechanism in the orm.

The consrc has been deprecated.

I've only briefly looked at the code in this repo, but I'd like to work on contributing an oci8 driver for Oracle support (in my experience the pdo_oci driver hasn't worked as well since PHP 7's release so I think the oci8 driver is still the best one for Oracle support).

Not sure if you have any tips as to all of the files that would need to be implemented to add the needed support (I have a rough idea based on how the other drivers are organized) but if you have any other direct tips please let me know and I'll see if I can take a crack at developing the needed classes and experimenting a bit more with Spiral over the weekend.

I have a database and there is a public scheme and a test scheme in it. How can I create a table in a test scheme ?
when I try to create this test.test_table, it is created in public with the name "test.test_table"

https://github.com/spiral/database/blob/626b7fc2a550423fa0a354ec76400bafe2eae3fe/src/Schema/AbstractColumn.php#L616

И Postgre и Mysql давно уже поддерживают varchar размером >60Kb, что даже в 8-байтных кодировках больше 8000 символов. Не пора ли и здесь снять ограничение?

Feature request: REPLACE INTO выражения

К имеющимся в орм SELECT, INSERT, UPDATE, DELETE выражениям добавить так же REPLACE выражение, полностью повторяющее INSERT выражение за исключением первого слова в запросе. Использоваться одно должно в следующем случае, если при создании новой сущности будет явно задан первичный ключ (возможно так же если задан уникальный ключ). Например:

$e = new SomeEntity;
$e->id = 123; // где id это PK
$e->someField = 'someValue';
$transaction->persisit($e);
$transaction->run();

В данном случае вместо INSERT INTO, должно быть использовано выражение REPLACE INTO, в остальном отличия от insert нету.

Либо, можно посмотреть как решаются такие кейсы в других орм, в некоторых, к примеру, есть метод ->replace(), который форсированно заставляет орм делать REPLACE INTO запрос вместо INSERT INTO. Это был бы более интуитивно понятный вариант. Вообще я предложил бы реализовать такие методы, скажем persist это аналог ->save() из других орм, а рядом с ним реализовать так же методы ->update(), ->replace() и ->create() которые форсированно заставляют делать UPDATE / REPLACE / INSERT соответственно, в то время как ->persist() действует по выше описанной логике, т.е. сам автоматически выбирает между REPLACE / INSERT / UPDATE.

Feature request: REPLACE INTO expressions

To the expressions in the SELECT, INSERT, UPDATE, DELETE expressions, add the REPLACE expression, which repeats the INSERT expression except for the first word in the query. One should be used in the following case, when creating a new entity, a primary key will be explicitly specified (possibly also if a unique key is specified). For instance:

$e = new SomeEntity;
$e->id = 123; // where id is PK
$e->someField = 'someValue';
$transaction->persisit($e);
$transaction->run();

In this case, instead of INSERT INTO, the REPLACE INTO expression should be used, otherwise there is no difference from insert.

Or, you can see how such cases are solved in other orm, in some, for example, there is a method ->replace(), which forces the orm to make a REPLACE INTO request instead of INSERT INTO. This would be a more intuitive option. In general, I would suggest implementing such methods, say persist is an analogue ->save() from other orm, and next to it, implement the same methods ->update (), ->replace() and ->create() which force to do UPDATE / REPLACE / INSERT respectively, while ->persist() acts according to the logic described above, i.e. it automatically chooses between REPLACE / INSERT / UPDATE.

Feature request: AbstractTable::getComparator type hint

Поменять тип возвращаемого результата метода Spiral\Database\Schema\AbstractTable::getComparator с Spiral\Database\Schema\Comparator на ComparatorInterface, данное изменение позволит в кастомных драйверах, использующих кастомные "Driver"Table использовать кастомные "Comparator" реализующие данный интерфейс

Feature request: AbstractTable :: getComparator type hint

Change the return type of the Spiral\Database\Schema\AbstractTable::getComparator method from Spiral\Database\Schema\Comparator to ComparatorInterface, this change will allow custom drivers using custom "Driver"Table to use custom "Comparator" that implement this interface

    public function up(): void
    {
        $this->table('long_table_name')
            ->addForeignKey(
                ['column'],
                'foreign_table',
                ['id'],
                ['name'   => 'long_table_name_column_hash', /* etc */]
            )
    }

[Spiral\Migrations\Exception\MigrationException]
Error in the migration (migration name) occurred: SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'long_table_name_column_hash' is too long in /var/www/vendor/spiral/migrations/src/Migrator.php:173

Almost any cloud provider uses this day env variables like DATABASE_URL. It would be cool, when we just pass that url and get a connection

Currently, there are too many indirections in the way spiral database configs can be written.

E.g here's what's being used for spiral's test configs:

'postgres'  => [
    'driver'     => Database\Driver\Postgres\PostgresDriver::class,
    'check'      => static function () {
        return !in_array('pgsql', \PDO::getAvailableDrivers(), true);
    },
    'conn'       => 'pgsql:host=127.0.0.1;port=15432;dbname=spiral',
    'user'       => 'postgres',
    'pass'       => 'postgres',
    'queryCache' => 100
],

And here's a latest(?) config example:

'postgres' => [
    'driver' => Spiral\Database\Driver\Postgres\PostgresDriver::class,
    'options' => [
        'connection' => 'pgsql:host=localhost;dbname=spiral',
        'username' => 'username',
        'password' => 'password',
        'options' => [
            PDO::ATTR_EMULATE_PREPARES => true,
            PDO::ATTR_STRINGIFY_FETCHES => true,
        ],
    ],
],

We could keep backward compatibility, but improve configs and update all the examples, docs, tests. I would like to suggest the following improvements:

1. Drop the first nested options key and write connection properties in the root config
2. Use dsn instead of connection/conn
3. Add host, port, and dbname configs. (but ignore them if dsn is set explicitly - I believe that's how it's done everywhere else)
4. Use dotenv in examples to allow for easier adoption

Following these suggestions, a config would look like:

'postgres' => [
    'driver' => Spiral\Database\Driver\Postgres\PostgresDriver::class,
    'host' => env('DB_HOST'),
    'port' => env('DB_PORT'),
    'dbname' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'options' => [
        PDO::ATTR_EMULATE_PREPARES => true,
        PDO::ATTR_STRINGIFY_FETCHES => true,
    ],
],

Not so coincidentally, this looks almost exactly like Laravel config.
I could take a crack at this once we agree on the format.

Hello @wolfy-j ,

just a simple suggestion as it's a feature I implemented many times:

• add IGNORE for INSERT (just need a boolean like SelectQuery::distinct)
• add the ability to compile this type of query INSERT INTO table (username, email) SELECT username, email FROM other_table WHERE.... with a new method InsertQuery::select(SelectQuery $select) and building the last part of the INSERT statement with the compiled SELECT statement.

kind regards,
maks

unrelated

• You can use self as a return type for any public function fluentMethod(): self
• In php-do blocks it's usually better to use @return $this if the return value is the same instance (and not for instance a clone)

Через некоторое время работы воркера при попытке записать что-то в БД с использованием транзакции начинает появляться ошибка:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT SVP-3 does not exist in /app/vendor/spiral/database/src/Driver/Driver.php:471
Stack trace:
#0 /app/vendor/spiral/database/src/Driver/Driver.php(471): PDOStatement->execute()
#1 /app/vendor/spiral/database/src/Driver/Driver.php(323): Spiral\Database\Driver\Driver->statement('RELEASE SAVEPOI...', Array)
#2 /app/vendor/spiral/database/src/Driver/Driver.php(634): Spiral\Database\Driver\Driver->execute('RELEASE SAVEPOI...')
#3 /app/vendor/spiral/database/src/Driver/Driver.php(412): Spiral\Database\Driver\Driver->releaseSavepoint(-3)
#4 /app/vendor/cycle/orm/src/Transaction/Runner.php(70): Spiral\Database\Driver\Driver->commitTransaction()
#5 /app/vendor/cycle/orm/src/Transaction.php(137): Cycle\ORM\Transaction\Runner->complete()
#6 /app/app/src/Repository/SecretAuthKeyRepository.php(42): Cycle\ORM\Transaction->run()

Ошибка зависит от воркера RR, т.е. проблема не на стороне БД. С каждым следующим запросом номер SVP уменьшается(--) на единицу. Предварительно проблема в дисконнекте воркера от MySQL и последующем реконнекте. Потенциально проблемное место:
vendor/spiral/database/src/Driver/Driver.php:353

public function beginTransaction(string $isolationLevel = null): bool
{
    $this->transactionLevel++;
    if ($this->transactionLevel === 1) {
        if ($isolationLevel !== null) {
            $this->setIsolationLevel($isolationLevel);
        }
        if ($this->logger !== null) {
            $this->logger->info('Begin transaction');
        }
        try {
            return $this->getPDO()->beginTransaction();
        } catch (Throwable  $e) {
            $e = $this->mapException($e, 'BEGIN TRANSACTION');
            if (
                $e instanceof StatementException\ConnectionException
                && $this->options['reconnect']
            ) {
                $this->disconnect();
                try {
                    return $this->getPDO()->beginTransaction();
                } catch (Throwable $e) {
                    throw $this->mapException($e, 'BEGIN TRANSACTION');
                }
            }
        }
    }
    $this->createSavepoint($this->transactionLevel);
    return true;
}

вызов $this->disconnect(); где вунтри
vendor/spiral/database/src/Driver/Driver.php:271

public function disconnect(): void
{
    try {
        $this->queryCache = [];
        $this->pdo = null;
    } catch (Throwable $e) {
        // disconnect error
        if ($this->logger !== null) {
            $this->logger->error($e->getMessage());
        }
    }
    $this->transactionLevel = 0;
}

$this->transactionLevel = 0; - присваивается 0

Some strange log behvior was detected with binded params:

1. LoggingBootLoader

public function boot(
        MonologBootloader $monolog
    ): void {
    $monolog->addHandler(
        MySQLDriver::class,
        $this->monolog->logRotate($this->getLogsDir() . 'sql.log')
    );

2. Query

/** @var $database DatabaseInterface **/
$database->query(
    'SELECT `a`.* FROM `admins` `a` LEFT JOIN `users` `u` ON `a`.`email` = `u`.`email` WHERE `u`.`id` IS NULL LIMIT :start, :limit',
    [
        'start' => 0,
        'limit' => 100
    ]
)->fetchAll();

3. Log record with useless colons before binded params

[2019-11-29 05:03:32] INFO: SELECT `a`.* FROM `admins` `a` LEFT JOIN `users` `u` ON `a`.`email` = `u`.`email` WHERE `u`.`id` IS NULL LIMIT :0, :100

Компилятор запросов: PostgreSQL
Пример воспроизведения:

use Spiral\Database\Injection\Expression;
//...
$select = $this->select()->buildQuery();

$rankQ = sprintf(
    'CASE WHEN %s ILIKE ? THEN 1 WHEN %s ILIKE ? THEN 2 WHEN %s ILIKE ? THEN 3 END AS rank',
    $params->field, // name
    $params->field, // name
    $params->field, // name
);

$select->columns(
    new Expression(
        $rankQ,
        "{$params->q}%", // any string
        "% {$params->q}%", // any string
        "%{$params->q}%" // any string
    )
);

Компилируется в:

SELECT "CASE WHEN name ILIKE ? THEN 1 WHEN name ILIKE ? THEN 2 WHEN name ILIKE ? THEN 3 END" AS "rank"
FROM "companies" AS "company"

А должно компилироваться в:

SELECT CASE WHEN name ILIKE ? THEN 1 WHEN name ILIKE ? THEN 2 WHEN name ILIKE ? THEN 3 END AS "rank"
FROM "companies" AS "company"

For GHA to upload code coverage to codecov, there needs to be a secrets.CODECOV_TOKEN.

More info: https://help.github.com/en/actions/automating-your-workflow-with-github-actions/creating-and-using-encrypted-secrets

Hello, could you help me to define a column with ON UPDATE CURRENT_TIMESTAMP attribute?
Screen: https://prnt.sc/1r9lisg

Интро

В long-running приложениях (асинхронных/многопоточных/многопоточно-асинхронных) часто применяется подход пулинга соединений (ConnectionPool). К БД открывается N соединений и пока приложение дожидается результатов от БД, оно может начать выполнять параллельно другие запросы к БД, используя свободные соединия из пула, что очень сильно увеличивает производительность такого приложения.

При использовании пула соединений очень важно правильно управлять транзакциями БД, а именно: Соединения, на котором выполняется транзакция не должно быть возвращено обратно в пул до тех пор, пока транзакция не будет завершена/отменена. Это гарантирует консистентность данных, и что другой код не начнет выполнять свои запросы в чужой транзакции.
Для этого в таких языках как Go/NodeJS используется механизм создания "объекта" транзакции.
Пример псевдокода на PHP:

// Забрали соединение из пула и инициировали транзакцию
$transaction = $pool->createTransaction($isolationLevel);

// Запрос выполняется на другом соединии, которое не занято исполнением транзакции
$pool->query('SELECT 1');

// Выполняем запросы внутри транзакции
$transaction->query('INSERT INTO table (col1, col2) VALUES (?, ?)', $val1, $val2);
$transaction->query('UPDATE table SET col1 = ? WHERE col2 = ?', $newVal, $id);
$transaction->query('DELETE FROM table WHERE col2 = ?', $id);

$rows = $transaction->query('SELECT * FROM table');
foreach ($rows as $row) {
    // ...
}

// Закоммитили транзакцию, объект транзакции более недействителен, соединение вернулось обратно в пул
$transaction->commit();
// $transaction->rollback();
// $transaction->createSavepoint(); и т.д.

Проблематика

Сейчас данный компонент не умеет выполнять запросы на конкретной транзакции, т.к. не рассчитан на то, что не будет применяться пулинг соединений. Драйвер, который реализует пул соединений вынужден хранить внутреннее состояние и ограничивать возможности запуска нескольких транзакций в рамках одной ветки исполнения кода.
Внутреннее состояние - это какой корутине (либо же ветви кода), принадлежит транзакция.
И основываясь на идентификаторе корутины драйвер должен сам определять - нужно выполнить запрос в транзакции или же на свободном соединении из пула.

Предложение

Контракт DriverInterface имеет следующую сигнатуру: public function beginTransaction(string $isolationLevel = null): bool.

Но данный подход вносит ограничения в приложение, т.е. драйвер реализующий пул соединений вынужден хранить состояние, т.к. данный компонент не умеет выполнять запросы на транзакции самостоятельно.

Почему бы не реализовать новый метод - createTransaction(string $isolationLevel = null): Transaction ?
(@wolfy-j возможно ты еще оставишь комментарии по этому поводу.)
Который бы использоваться для создания транзакций на тех драйверах, которые поддерживают пулинг соединеий.
А также научить данный компонент работать с такими объетами транзакций (выполнять на них запросы).

• Transaction в createTransaction(string $isolationLevel = null): Transaction это другая транзакция, "физическая" транзакция БД. А также реализует контракт соединения.
  Примеры:
  • Swoole - https://github.com/makise-co/sql-common/blob/master/src/Contracts/Transaction.php
  • Swoole - https://github.com/makise-co/sql-common/blob/master/src/PooledTransaction.php
  • Swoole - https://github.com/makise-co/postgres/blob/master/examples/pool/transaction.php#L35
  • AmPHP - https://github.com/amphp/sql/blob/master/src/Transaction.php
  • AmPHP - https://github.com/amphp/sql-common/blob/master/src/PooledTransaction.php
  • Go - https://www.sohamkamani.com/golang/sql-transactions/#basic-transactions

Связанная issue: cycle/orm#115

Between query supports reverted order where args are not values but fields:

$source->where(
    123,
    'BETWEEN',
    'created', 'updated'
);

The output is equals to:

SELECT * FROM table_name WHERE "123" BETWEEN 'created' AND 'updated'

expected:

SELECT * FROM table_name WHERE 123 BETWEEN 'created' AND 'updated'

Hey, I have long running application in production and I faced with an issue.

The setup is:

• php:8.0.11-cli official docker image
• mysql:8.0.26 official docker image
• spiral/database:v2.9.2

Globally it is RoadRunner v1.9.2, Spiral Framework v2.8.13, Cycle ORM v1.6.1.

Every day (i assume after long time of alive connection without any query) first attempt to execute any query fails with an error Packets out of order. Expected 1 received 0. Packet size=145.

This is not a MySQL version but a mysqlnd error: https://github.com/php/php-src/blob/PHP-8.0.11/ext/mysqlnd/mysqlnd_protocol_frame_codec.c#L352

By MySQL driver this error mapped to a Spiral\Database\Exception\StatementException but actually it is not true, because second attempt to launch same query is successful.

So I assume this type of error should be mapped into Spiral\Database\Exception\StatementException\ConnectionException as it is related to a connection rather to a statement. I guess this allows driver to perform reconnecting once this error occurs.

[Spiral\Database\Exception\StatementException]
Packets out of order. Expected 1 received 0. Packet size=145in /app/vendor/spiral/database/src/Driver/MySQL/MySQLDriver.php:75

Exception Trace:
Spiral\Database\Driver\MySQL\MySQLDriver->mapException() at /app/vendor/spiral/database/src/Driver/MySQL/MySQLDriver.php:75
Spiral\Database\Driver\MySQL\MySQLDriver->mapException() at /app/vendor/spiral/database/src/Driver/Driver.php:561
Spiral\Database\Driver\Driver->statement() at /app/vendor/spiral/database/src/Driver/Driver.php:348
Spiral\Database\Driver\Driver->query() at /app/vendor/spiral/database/src/Query/SelectQuery.php:288
Spiral\Database\Query\SelectQuery->run() at /app/vendor/cycle/orm/src/Select/RootLoader.php:112
Cycle\ORM\Select\RootLoader->loadData() at /app/vendor/cycle/orm/src/Select.php:430
Cycle\ORM\Select->fetchData() at /app/vendor/cycle/orm/src/Select.php:387
Cycle\ORM\Select->fetchOne() at /app/vendor/cycle/orm/src/Select/Repository.php:48
Cycle\ORM\Select\Repository->findByPK() at /app/app/src/Repository/UserRepository.php:26
App\Repository\UserRepository->getActor() at /app/vendor/spiral/framework/src/Framework/Bootloader/Auth/AuthBootloader.php:65
Spiral\Bootloader\Auth\AuthBootloader->getActor() at /app/vendor/spiral/framework/src/Auth/src/AuthContext.php:76
Spiral\Auth\AuthContext->getActor() at /app/app/src/Controller/Auth/RefreshController.php:31
App\Controller\Auth\RefreshController->refresh() at n/a:n/a
ReflectionMethod->invokeArgs() at /app/vendor/spiral/framework/src/Hmvc/src/AbstractCore.php:82
Spiral\Core\AbstractCore->Spiral\Core\{closure}() at /app/vendor/spiral/framework/src/Core/src/ContainerScope.php:50
Spiral\Core\ContainerScope::runScope() at /app/vendor/spiral/framework/src/Hmvc/src/AbstractCore.php:83
Spiral\Core\AbstractCore->callAction() at /app/vendor/spiral/framework/src/Router/src/CoreHandler.php:128
Spiral\Router\CoreHandler->Spiral\Router\{closure}() at /app/vendor/spiral/framework/src/Core/src/Container.php:268
Spiral\Core\Container->runScope() at /app/vendor/spiral/framework/src/Router/src/CoreHandler.php:130
Spiral\Router\CoreHandler->handle() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:86
Spiral\Http\Pipeline->Spiral\Http\{closure}() at /app/vendor/spiral/framework/src/Core/src/Container.php:268
Spiral\Core\Container->runScope() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:87
Spiral\Http\Pipeline->handle() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:68
Spiral\Http\Pipeline->process() at /app/vendor/spiral/framework/src/Router/src/Route.php:123
Spiral\Router\Route->handle() at /app/vendor/spiral/framework/src/Router/src/Router.php:86
Spiral\Router\Router->handle() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:86
Spiral\Http\Pipeline->Spiral\Http\{closure}() at /app/vendor/spiral/framework/src/Core/src/Container.php:268
Spiral\Core\Container->runScope() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:87
Spiral\Http\Pipeline->handle() at /app/app/src/Middleware/LocaleSelector.php:47
App\Middleware\LocaleSelector->process() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:82
Spiral\Http\Pipeline->handle() at /app/vendor/spiral/framework/src/AuthHttp/src/Middleware/AuthMiddleware.php:78
Spiral\Auth\Middleware\AuthMiddleware::Spiral\Auth\Middleware\{closure}() at /app/vendor/spiral/framework/src/Core/src/Container.php:268
Spiral\Core\Container->runScope() at /app/vendor/spiral/framework/src/AuthHttp/src/Middleware/AuthMiddleware.php:79
Spiral\Auth\Middleware\AuthMiddleware->process() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:82
Spiral\Http\Pipeline->handle() at /app/vendor/spiral/framework/src/Framework/Debug/StateCollector/HttpCollector.php:34
Spiral\Debug\StateCollector\HttpCollector->process() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:82
Spiral\Http\Pipeline->handle() at /app/vendor/spiral/framework/src/Csrf/src/Middleware/CsrfMiddleware.php:60
Spiral\Csrf\Middleware\CsrfMiddleware->process() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:82
Spiral\Http\Pipeline->handle() at /app/vendor/spiral/framework/src/Framework/Session/Middleware/SessionMiddleware.php:86
Spiral\Session\Middleware\SessionMiddleware->Spiral\Session\Middleware\{closure}() at /app/vendor/spiral/framework/src/Core/src/Container.php:268
Spiral\Core\Container->runScope() at /app/vendor/spiral/framework/src/Framework/Session/Middleware/SessionMiddleware.php:87
Spiral\Session\Middleware\SessionMiddleware->process() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:82
Spiral\Http\Pipeline->handle() at /app/vendor/spiral/framework/src/Cookies/src/Middleware/CookiesMiddleware.php:60
Spiral\Cookies\Middleware\CookiesMiddleware->process() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:82
Spiral\Http\Pipeline->handle() at /app/vendor/spiral/framework/src/Framework/Http/Middleware/JsonPayloadMiddleware.php:56
Spiral\Http\Middleware\JsonPayloadMiddleware->process() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:82
Spiral\Http\Pipeline->handle() at /app/vendor/spiral/framework/src/Framework/Http/Middleware/ErrorHandlerMiddleware.php:78
Spiral\Http\Middleware\ErrorHandlerMiddleware->process() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:82
Spiral\Http\Pipeline->handle() at /app/app/src/Middleware/CorsMiddleware.php:39
App\Middleware\CorsMiddleware->process() at /app/vendor/spiral/framework/src/Http/src/Pipeline.php:82
Spiral\Http\Pipeline->handle() at /app/vendor/spiral/framework/src/Http/src/Http.php:100
Spiral\Http\Http->handle() at /app/vendor/spiral/framework/src/Framework/Http/LegacyRrDispatcher.php:80
Spiral\Http\LegacyRrDispatcher->serve() at /app/vendor/spiral/framework/src/Core/src/ContainerScope.php:50
Spiral\Core\ContainerScope::runScope() at /app/vendor/spiral/framework/src/Core/src/Container.php:265
Spiral\Core\Container->runScope() at /app/vendor/spiral/framework/src/Boot/src/AbstractKernel.php:104
Spiral\Boot\AbstractKernel->serve() at /app/app.php:14

Avoid cloning Compiler for each for the query, clone AliasCarrier instead. Introduce SQL query caching (not data caching) for performance improvement.

Feature request: disableMigrations option

Добавить в конфигурацию соединения boolean опцию disableMigrations которая глобально для соединения отключает миграции, в том числе создание таблицы migrations. Это полезно в следующих случаях:

1. в SphinxSearch/ManticoreSearch в принципе нет миграций и запрос на создание таблицы migrations валится.
2. К примеру, есть база, которая мигрируется другим проектом, и отслеживать её в спирали не следует, спираль имеет с ней соединение в, скажем, read-only режиме.

Feature request: disableMigrations option

Add the disableMigrations (boolean) option to the connection configuration, which globally for the connection disables migrations, including the creation of the migrations table. This is useful in the following cases:

1. in SphinxSearch / ManticoreSearch, in principle, there are no migrations and the query to create the migrations table falls.
2. For example, there is a database that is being migrated by another project, and you should not track it in a spiral; the spiral has a connection to it in, say, read-only mode.

Hi,

the following query should returns an array of anonymous objects

$items = $database->table("any_table")->limit(3)->fetchAll(\PDO::FETCH_OBJ);

Instead, it is returning an array of arrays. Using the fetchAll directly as:

$q = $pdo->query("select * from any_table limit 3");
$items = $q->fetchAll(\PDO::FETCH_OBJ);

works as expected.

Apache/2.4.46 (Win64) OpenSSL/1.1.1g PHP/7.4.10

What steps will reproduce the problem?

Install cycle/orm and cycle/migrations
Now cycle not full support json type for mysql, because it automatically convert json to text
https://github.com/spiral/database/blob/master/src/Driver/MySQL/Schema/MySQLColumn.php#L88

Example of migration

    public function up(): void
    {
        $this
            ->table(self::$table)
            ->addColumn('id', 'primary')
            ->addColumn('message', 'json')
            ->create();
    }

I've used following hack, but it looks terrible

    public function up(): void
    {
        $tbl = $this
            ->table(self::$table);

        $tbl
            ->addColumn('id', 'primary')
            ->create();

        $schema = $tbl->getSchema();
        $col = $schema->json('message');
        $prop = (new ReflectionClass($col))->getProperty('type');
        $prop->setAccessible(true);
        $prop->setValue($col, 'JSON');

        $schema->save();
    }

What is the expected result?

I've expected json column

What do you get instead?

I've get a text column

Additional info

https://github.com/spiral/database/blob/626b7fc2a550423fa0a354ec76400bafe2eae3fe/src/Query/Traits/JoinTrait.php#L72-L95

1. Нет тестов на join() с использованием 2+ параметров.
2. return call_user_func_array([$this, 'on'], array_slice(func_get_args(), 2)); не работает с предлагаемыми ниже тестами.
3. В комментариях указана возможность передачи Closure. Не понятно, как это использовать -- нужны тесты и примеры.

Дополнительно предлагаемые тесты для tests/Database/SelectQueryTest.php

    public function testLeftJoin4(): void
    {
        $select = $this->database->select()
                         ->from(['users'])
                         ->join('LEFT', 'photos', 'pht', ['pht.user_id', 'users.id']);

        $this->assertSameQuery(
            'SELECT * FROM {users} LEFT JOIN {photos} AS {pht} ON {pht}.{user_id} = {users}.{id}',
            $select
        );
    }

    public function testLeftJoin5(): void
    {
        $select = $this->database->select()
                                 ->from(['users'])
                                 ->join('LEFT', 'photos', 'pht', ['@AND' => ['pht.user_id' => 'users.id']]);

        $this->assertSameQuery(
            'SELECT * FROM {users} LEFT JOIN {photos} AS {pht} ON {pht}.{user_id} = {users}.{id}',
            $select
        );
    }

    public function testLeftJoin6(): void
    {
        $select = $this->database->select()
                                 ->from(['users'])
                                 ->join('LEFT', 'photos', 'pht', [['pht.user_id' => 'users.id']]);

        $this->assertSameQuery(
            'SELECT * FROM {users} LEFT JOIN {photos} AS {pht} ON {pht}.{user_id} = {users}.{id}',
            $select
        );
    }

Связанная проблема: cycle/orm#169

Создаём сущность, в параметрах указываем readonlySchema

/**
 * @Entity (readonlySchema = true)
 */
class SomeEntity
{
  ...
}

Выполняем команду: php app.php cycle:migrate
Ожидаемый результат: миграция на сущность не создаётся
Фактический результат: миграция создалась

Create an entity, specify readonlySchema in the parameters

/**
 * @Entity (readonlySchema = true)
 */
class SomeEntity
{
  ...
}

Run the command: php app.php cycle:migrate
Expected result: migration to the entity will not be created.
Actual result: migration created

https://github.com/spiral/database/blob/632488c5f7dfd3d4dc47edc9f1c2c992724e7526/src/Driver/Driver.php#L659

I have the issue of inserting datetime in the following format Y-m-d H:i:s.u - u - dosent insert.

How can I insert my format to DB in another way?

Простите, с английским будет гугл-транслейт..

При выполнении не корректного запроса в бд, падает ConnectionException в то время как соединение с бд в общем то не терялось. Воспроизвести можно так, создаём тестовую модель с не корректными данными для запроса:

When an incorrect query is executed in the database, ConnectionException drops while the connection to the database is generally not lost. You can play it like this, create a test model with incorrect data for the query:

<?php
declare(strict_types=1);

namespace App\models\Db;

use Cycle\Annotated\Annotation\Entity;
use Cycle\Annotated\Annotation\Column;


/**
 * @Entity
 */
class Test
{

    /**
     * @var int
     * @Column(type = "primary")
     */
    public $test_id;


    /**
     * @var string
     * @Column(type="string", nullable=false)
     */
    public $name;

    /**
     *
     * @var string
     * @Column(type="json", nullable=false)
     */
    public $description = [];
}

Далее код который выполнит некорректный запрос

Next, the code that will execute the incorrect query

        $t = new Transaction($orm);
        $e = new Test();
        $e->name = 'azazaza';
        $t->persist($e);
        $t->run();

Результат: выкидывается ConnectionException:

Result: throws ConnectionException:

Spiral\Database\Exception\StatementException\ConnectionException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 2 in /var/www/vendor/spiral/database/src/Driver/MySQL/MySQLDriver.php at line 101
• caused by PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 2 in /var/www/vendor/spiral/database/src/Driver/Driver.php at line 526

MariaDB
version: 10.4.4-MariaDB-1:10.4.4+maria~bionic - mariadb.org binary distribution
Protocol version: 10
Official docker image: mariadb:10.4.4

This code creates non unique index:

    $table = $this->database()->table('addresses')->getSchema();
    $table->column('id')->uuid();
    $table->setPrimaryKeys(['id']);

    $table->column('company_id')->uuid()->nullable(false);
    $table->column('country_id')->uuid()->nullable(false);

    $table->column('title')->text()->nullable(false);
    $table->column('note')->text();

    $table->timestamp('created_at')->nullable(false);
    $table->timestamp('updated_at')->nullable(false);
    $table->timestamp('deleted_at');

    $table->index(['company_id']);
    $table->index(['country_id']);
    $table->index(['company_id', 'title'])->unique(true); // <<<<<<<

    $table
        ->foreignKey(['company_id'])
        ->references('companies', ['id'], false)
        ->onUpdate('CASCADE')
        ->onDelete('RESTRICT');

    $table->save();

UPD:

This code also doesn't create unique index:

$table->index(['title'])->unique(true);
$table->save();

Hi,

This Builder prevents SQL Injection? Or do we need to escape before using the builder?

I searched for this topic in the docs, but I didn't find.

Thanks!

Currently, it's not possible to define order for index columns, so an annotation like @Cycle\Table\Index(columns={"user_id", "id DESC"}) won't work.

As of Postgresql v10, in addition to standard sequences and serial type, there is also an identity column.
Laravel has 2 methods for this:

->generatedAs($expression) | Create an identity column with specified sequence options (PostgreSQL)
-- | --
->always() | Defines the precedence of sequence values over input for an identity column (PostgreSQL)

Could You please add support for this feature?