cycle/database

Database Abstraction Layer, Schema Introspection, Schema Generation, Query Builders

database's Issues

Try to install cycle 2.0

Updating dependencies
Your requirements could not be resolved to an installable set of packages.

Problem 1
- cycle/database 2.0.0 requires spiral/core ^2.9 -> found spiral/core[dev-master, 2.9.x-dev (alias of dev-master)] but it does not match your minimum-stability.
- cycle/orm v2.0.0 requires cycle/database ^2.0 -> satisfiable by cycle/database[2.0.0].
- Root composer.json requires cycle/orm ^2.0 -> satisfiable by cycle/orm[v2.0.0].

You can also try re-running composer require with an explicit version constraint, e.g. "composer require cycle/orm:*" to figure out if any version is installable, or "composer require cycle/orm:^2.1" if you know which you need.

🐛 Memory leak on batch insert

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

I use DatabaseInterface::execute to batch insert. I detect a huge memory leak if queryCache: true.

The problem is that the number of VALUES may differ, and each time the request is cached.

I create demo-project with demo of issue: https://github.com/gam6itko/spiral-app-sandbox/blob/cb97213a8c12f7e98ae4186d3e90f2d8b2b09bea/src/app/app/src/Command/CycleMemoryLeakCommand.php#L49

Version

database 2.5.2
PHP 8.1

🐛 Unable to create auth token

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

PostgreSQL

What happened?

Original issue spiral/cycle-bridge#85

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bytea
CONTEXT:  unnamed portal parameter $5 = '...'

The payload contains an url. Upon json_encode, it will add backslashes to escape the uri e.g http:\/\/127.0.0.1:8080\/ which I guess somewhat invalid for bytea column. Removing the URL from the payload resolves the issue.

Version

Postgres 16.1
PHP 8.3

💡 Fragment support in column type

I have an idea!

It would be nice to add Fragment support when specifying the column type. When passing a Fragment, don't map it and use it as a custom type. For example, this will allow using the native json type in MySQL (and not map it to text).

#[Column(type: new Fragment('json'))]

🐛 Index on FK is created despite Relation::$indexCreate=false

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

PostgreSQL

What happened?

Even if the options is false, index will be created by Cycle\Schema\Generator\Migrations\GenerateMigrations

Sometimes relation db is used just like a persistence storage, with insert/select by id only operation,
not operational db, so index is unnecessary for these cases.

As a workaround, I deleted these indexes in the same migrations.
Cycle\Schema\Generator\Migrations\GenerateMigrations will not find any differences,
so it look like the bug happen only while generate migrations for new table.

Version

database 2.4.1
PHP 8.2

🐛 `where` float parameter passed as string

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

This code passes the parameter as a string

$amount = 300.05;
$s
   ->where(new Fragment('COALESCE(from_min, ?) <= ?', self::MIN_AMOUNT, $amount))
   ->where(new Fragment('COALESCE(from_max, ?) >= ?', self::MAX_AMOUNT, $amount));

In mysql server logs I see the following log

  (COALESCE(`from_min`, '1.0E-8') <= '300.05' AND COALESCE(`from_max`, 1000000000) >= '300.05' )

Correct work was achieved by such a design.

$amount = 300.05;
$s
   ->where(new Fragment(\sprintf('COALESCE(from_min, %.8f) <= %.8f', self::MIN_AMOUNT, $amount)))
   ->where(new Fragment(\sprintf('COALESCE(from_max, %d) >= %.8f', self::MAX_AMOUNT, $amount)));

In this case, everything is as it should be in the mysql logs

AND (COALESCE(from_min, 0.00000001) <= 300.05000000 AND COALESCE(from_max, 1000000000) >= 300.05000000 )

Version

database 2.5.1
PHP 8.1

OrderBy needs to be an interpolated

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

SQLite, MySQL, PostgreSQL, MSSQL

What happened?

OrderBy function in QueryBuilder needs to be interpolated. Some methods of builder already have this feauture but in this case it was passed.

Version

mysql:5.7
PHP 7.4

Update dependencies for `database` 1.x

Research the possibility to add spiral/* v3 dependencies here

database/composer.json

Lines 18 to 20 in 32ef32a

	"spiral/core": "^2.8",
	"spiral/logger": "^2.8",
	"spiral/pagination": "^2.8"

🐛 Unable to use Expression

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

No response

What happened?

A bug happened!

I'm unable to bind parameters in Expression inside Order By statements.

Version

database 2.0
PHP 8.1

💡 Support for Querying JSON Columns in Databases for better DX

I have an idea!

Feature Request:

I am requesting the inclusion of enhanced methods to query JSON column types directly via cycle/orm. This feature not only aligns with the capabilities offered by frameworks like Laravel, but also significantly improves the overall Developer Experience (DX).

Current Behavior:

Currently, querying JSON in cycle/orm involves more complex, less intuitive code:

...

use Cycle\Database\Injection\Parameter;

...

$exists = $webhookCallsRepository
    ->select()
    ->where(['name' => 'stripe'])
    ->andWhere("JSON_EXTRACT(payload, '$.id')", '=', new Parameter(['payloadId' => $request->get('id')]))
    ->count();

Full class located here: https://github.com/wayofdev/laravel-stripe-webhooks/blob/master/src/Profile/StripeWebhookProfile.php

Desired Behavior:

$users = $repository->select()
                ->whereJson('preferences->dining->meal', 'salad')
                ->fetch();

$users = $repository->select()
                ->whereJsonContains('options->languages', 'en')
                ->fetch();

$users = $repository->select()
                ->whereJsonLength('options->languages', '>', 1)
                ->fetch();

Proposal:

I've checked internal code of cycle/orm and found that first thing to modify would be WhereTrait.php, where additional methods could be implemented:

<?php

trait WhereTrait
{
    // ... (other existing methods) ...
    
    public function whereJson(string $column, $value): self
    {
        $this->registerToken(
            'AND',
            [$column, '->', $value],
            $this->whereTokens,
            $this->whereWrapper()
        );

        return $this;
    }

    public function whereJsonContains(string $column, $value): self
    {
        $this->registerToken(
            'AND',
            [$column, '->', $value],
            $this->whereTokens,
            $this->whereWrapper()
        );

        return $this;
    }

    public function whereJsonLength(string $column, $operator, $value = null): self
    {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }

        $this->registerToken(
            'AND',
            [$column, 'json_length', $operator, $value],
            $this->whereTokens,
            $this->whereWrapper()
        );

        return $this;
    }
}

Benefits:

Improved Developer Experience (DX): This change would significantly reduce the complexity and verbosity of JSON-related queries, leading to faster and more intuitive coding.

Reduced Errors: Leveraging built-in methods reduces the likelihood of errors from manually written SQL statements.

Alignment with Popular Frameworks: It brings cycle/orm on par with other frameworks, making it a more attractive option for developers familiar with those patterns.
I believe this feature, coupled with other necessary changes, would be a valuable addition to cycle/orm. I look forward to feedback and potential inclusion in a future release.

💡 MySQL: add supproting for `CHECK` constraint

I have an idea!

CREATE TABLE Customers
(
  Age INT DEFAULT 18 CHECK(Age >0 AND Age < 100),
)

Or

CREATE TABLE Customers
(
    Age INT DEFAULT 18,
    CHECK((Age >0 AND Age<100) AND (Email !='') AND (Phone !=''))
);

See

🐛 Psalm errors in 2.6.0

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

After updating cycle/database to 2.6.0 psalm start to show errors.

ERROR: InternalMethod - src/Service/ClassA.php:131:15 - The method Cycle\Database\Query\DeleteQuery::run is internal to Cycle, Cycle, and Cycle but called from ClassA::doDelete (see https://psalm.dev/175)
            ->run();


ERROR: InternalMethod - ClassB.php:40:15 - The method Cycle\Database\Query\SelectQuery::from is internal to Cycle, Cycle, and Cycle but called from ClassB::getFormDbByCode (see https://psalm.dev/175)
            ->from('city')

// a
 $this->db
            ->delete('table_a')
            ->where('id', 'IN', new Parameter($toDeleteIdList))
            ->run();

// b

        $list = $this->db
            ->select(['id'])
            ->from('city')
            ->where('code', $code)
            ->fetchAll();

I think the problem is here: 54dab6f

Version

database 2.6.0
PHP 8.2

💡 Add `truncate` methods

I have an idea!

To add an ability to delete a table or table data

• ignoring FK
• delete all tables
• remove table data

🐛 Quote table name bug

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MSSQL

What happened?

A bug happened!

->leftJoin('_1SCONST _1SCONST2(NOLOCK)')
->on('SC3271.ID', '=', '_1SCONST2.OBJID')

output

LEFT JOIN _1[SCONST] _1SCONST2([NOLOCK])
    ON [SC3271].[ID] = [_1SCONST2].[OBJID]

there is issue here

LEFT JOIN _1[SCONST]

=>

LEFT JOIN [_1SCONST]

Version

database 2.0
PHP 8.1

🐛 Incorrect insertion of FragmentInterface parameters into WHERE IN queries

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

A bug happened!

FragmentInterface parameters are not functioning properly in WHERE IN queries:

    // $guid implements FragmentInterface
    $guid1 = Guid::fromString('0189a22f-1bd0-7005-959a-46860de7d841');
    $guid2 = Guid::fromString('015033e7-a2f0-9ad2-fa9e-e075f4d07a6a');
    $ids = [$guid2]; 
    $select = $this->repository
            ->select()
            ->where('categoryGuid', $guid1);
            ->andWhere('someGuid', 'IN', new Parameter($ids))
            ->fetchData();

Actual behavior:

WHERE `CategoryGuid` = UUID_TO_BIN('018c11c6-0cae-725e-b885-499e53e8ec5f') 
AND SomeGuid` IN ('018c11c6-5657-7fdc-b2fd-7a6909c1683f') 

Expected behavior:

All FragmentInterface objects correctly inserted into the query (added UUID_TO_BIN also in IN parameters)

WHERE `CategoryGuid` = UUID_TO_BIN('018c11c6-0cae-725e-b885-499e53e8ec5f') 
AND SomeGuid` IN (UUID_TO_BIN('018c11c6-5657-7fdc-b2fd-7a6909c1683f')) 

Version

database 2.0
PHP 8.2

Reuse of PDOStatement when query cache is enabled results in SQLite "21 bad parameter or other API misuse" for file connections

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

SQLite

What happened?

When query caching is enabled, if a constraint violation is encountered during an insert statement, a subsequent attempt to perform the same insert query will reuse the PDOStatement object stored in the cache, and its reuse will result in SQLite producing the following error:

PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 21 bad parameter or other API misuse in vendor/cycle/database/src/Driver/Driver.php:444

This situation occurs under two additional conditions:

1. The SQLite database is a file.
2. The SQLite file is opened, not created.

Reproduction

I've attached a test script. The output will consist of o, record inserted, or x constraint violated. Execution of this script under different conditions follows.

<?php
declare(strict_types=1);

use Cycle\Database\Config\DatabaseConfig;
use Cycle\Database\Config\SQLite\FileConnectionConfig;
use Cycle\Database\Config\SQLite\MemoryConnectionConfig;
use Cycle\Database\Config\SQLiteDriverConfig;
use Cycle\Database\DatabaseManager;
use Cycle\Database\Exception\StatementException\ConstrainException;
use Cycle\ORM\EntityManager;
use Cycle\ORM\Factory;
use Cycle\ORM\Mapper\StdMapper;
use Cycle\ORM\ORM;
use Cycle\ORM\Schema;
use Cycle\ORM\SchemaInterface;

require __DIR__ . '/vendor/autoload.php';

$driver = new SQLiteDriverConfig(
	connection: new FileConnectionConfig(
		database: __DIR__ . '/blah.db',
	),
	reconnect: true,
	queryCache: true,
);
//$driver = new SQLiteDriverConfig(
//	connection: new MemoryConnectionConfig(),
//	reconnect: true,
//	queryCache: true,
//);

$databaseName = 'default';
$dm = new DatabaseManager(new DatabaseConfig([
	'default' => $databaseName,
	'databases' => [
		$databaseName => ['connection' => 'sqlite']
	],
	'connections' => ['sqlite' => $driver]
]));

$tableName = 'testing';

$table = $dm->database('default')
	->table($tableName)
	->getSchema();
$table->setPrimaryKeys(['a', 'b']);
$table->string('a')->nullable(false);
$table->string('b')->nullable(false);
$table->save();
unset($table);

$orm = new ORM(
	new Factory($dm),
	new Schema([
		$tableName => [
			SchemaInterface::MAPPER => StdMapper::class,
			SchemaInterface::DATABASE => 'default',
			SchemaInterface::TABLE => $tableName,
			SchemaInterface::PRIMARY_KEY => ['a', 'b'],
			SchemaInterface::COLUMNS => [
				'a' => 'a',
				'b' => 'b',
			],
		]
	]),
);

$things = ['123', '123', '456'];
foreach ($things as $value) {
	$em = new EntityManager($orm);
	$record = $orm->make($tableName);
	$record->a = 'static';
	$record->b = $value;
	$em->persist($record);
	print '_';
	try {
		$em->run();
		print chr(8) . 'o';
	} catch (ConstrainException) {
		// 23000: UNIQUE constraint failed
		print chr(8) . 'x';
	}
}
print "\n";

Fresh database file, queryCache: false

$ rm blah.db
$ php testorm.php
oxo

Fresh database file, queryCache: true

$ rm blah.db
$ php testorm.php
oxo

Existing database file, queryCache: false

$ php testorm.php 
xxx

Existing database file, queryCache: true

$ php testorm.php
x_PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 21 bad parameter or other API misuse in /home/whmcs/sync/soylent/vendor/cycle/database/src/Driver/Driver.php:444
Stack trace:
#0 vendor/cycle/database/src/Driver/Driver.php(444): PDOStatement->execute()
#1 vendor/cycle/database/src/Driver/Driver.php(270): Cycle\Database\Driver\Driver->statement()
#2 vendor/cycle/database/src/Query/InsertQuery.php(125): Cycle\Database\Driver\Driver->execute()
#3 vendor/cycle/orm/src/Command/Database/Insert.php(85): Cycle\Database\Query\InsertQuery->run()
#4 vendor/cycle/orm/src/Transaction/Runner.php(61): Cycle\ORM\Command\Database\Insert->execute()
#5 vendor/cycle/orm/src/Transaction/UnitOfWork.php(150): Cycle\ORM\Transaction\Runner->run()
#6 vendor/cycle/orm/src/Transaction/UnitOfWork.php(327): Cycle\ORM\Transaction\UnitOfWork->runCommand()
#7 vendor/cycle/orm/src/Transaction/UnitOfWork.php(375): Cycle\ORM\Transaction\UnitOfWork->resolveSelfWithEmbedded()
#8 vendor/cycle/orm/src/Transaction/UnitOfWork.php(219): Cycle\ORM\Transaction\UnitOfWork->resolveRelations()
#9 vendor/cycle/orm/src/Transaction/UnitOfWork.php(96): Cycle\ORM\Transaction\UnitOfWork->walkPool()
#10 vendor/cycle/orm/src/EntityManager.php(47): Cycle\ORM\Transaction\UnitOfWork->run()
#11 testorm.php(77): Cycle\ORM\EntityManager->run()
#12 {main}

Expectation

Regardless of whether the SQLite database file is created within the same process, a constraint violation in a prepared INSERT statement should not result in the same INSERT query later failing; whether it would encounter another constraint violation or not.

Version

PHP 8.1.23 (cli) (built: Sep  2 2023 06:59:15) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.1.23, Copyright (c) Zend Technologies
    with Zend OPcache v8.1.23, Copyright (c), by Zend Technologies

cycle/database                   2.5.2   DBAL, schema introspection, migration and pagination
cycle/orm                        v2.3.4  PHP DataMapper ORM and Data Modelling Engine

$ php -i | grep -i sqlite
/etc/php/8.1/cli/conf.d/20-pdo_sqlite.ini,
/etc/php/8.1/cli/conf.d/20-sqlite3.ini,
PDO drivers => sqlite
pdo_sqlite
PDO Driver for SQLite 3.x => enabled
SQLite Library => 3.37.2
sqlite3
SQLite3 support => enabled
SQLite Library => 3.37.2
sqlite3.defensive => On => On
sqlite3.extension_dir => no value => no value

💡 Postgres Composite Primary Keys

I have an idea!

Why did you disable support for composite PKs?

[🐛 BUG]: Wrong bind parameter order

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

Hi!
I recently encountered a strange behavior when working with joins.
The order of parameter binding is distorted when code like this is used:

$query->columns([
    'UG.id as memberId',
    'UG.name as name',
    new Fragment('? as memberType', 1),
    new Fragment('? as accessType', 3),
    new Fragment('IF(PA.id, ?, ?) as invited', 1, 0),
])
...
->leftJoin('some_table', 'PAS')->on('PAS.groupId', 'UG.id')->onWhere('PAS.pId', 37);

In this case, query bind parameter list ($params) will start from 37, the one from onWhere clause.

I assume this is because the join parameters are added to $params much earlier than, for example, the column parameters when compiling the query.

Here Compiler.php#L171 and here CompilerCache.php#L154.

Version

database 2.1
PHP 8.0

💡 Add transaction events

I have an idea!

Add events for transaction begin/commit/rollback and others

🐛 Parameters are duplicated for subquery when query cache is disabled

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

PostgreSQL

What happened?

When queryCache is disabled, sub-query parameters are duplicated in the result statement:

Scenario to reproduce the bug:

$db = new Database(
    name: 'default',
    prefix: '',
    driver: PostgresDriver::create(new PostgresDriverConfig(
        connection: new TcpConnectionConfig(
            database: 'test',
        ),
        queryCache: false,
    )),
);

$subSelect = $db->select()
    ->from('posts AS p')
    ->where('p.feed', 'news');

$select = $db->select()
    ->from(['users as u'])
    ->innerJoin($subSelect, 'p')->on('p.user_id', 'u.id')
    ->where('u.status', 'active');

$this->assertSameQueryWithParameters(
    'SELECT *
            FROM {users} AS {u}
            INNER JOIN (
              SELECT *
              FROM {posts} AS {p}
              WHERE {p}.{feed} = ?
            ) AS {p} ON {p}.{user_id} = {u}.{id}
          WHERE {u}.{status} = ?',
    ['news', 'active'],
    $select
);

Expected parameters: ['news', 'active']
Actual parameters: ['news', 'news', 'active']

Version

database 2.4.1
PHP 8.1

🐛 Server closed connection unexpectedly

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

PostgreSQL

What happened?

A bug happened!

Sometimes this error occurred:

General error: 7 server closed the connection unexpectedly \tThis probably means the server terminated abnormally \tbefore or while processing the request

Perhaps this error should be mapped as ConnectionException and statement must be retried.

Version

database 14.2
PHP 8.1

💡 Add `getComment()` method to `ColumnInterface`

I have an idea!

Problem:

\Cycle\Database\ColumnInterface doesn't have something like getComment() method.
Also no one Driver directory contains comment keyword :)

So the idea is to have a getComment() method inside ColumnInterface to be able to introspect as more properties as we can.

💡 Disable/Enable foreign keys checking

I have an idea!

We have to have the ability out of the box.

🐛 Interpolator optimization

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

No matter.

What happened?

I insert 1000-5000 line batches and the default php.ini settings with memory_limit: 256M , then I periodically get allowed memory size of bytes exhausted. Worst of all, this error happens after the insertion, which is why my application can not correctly count the number of inserted batches. The error occurs on this line:

database/src/Driver/Driver.php

Line 462 in bd48dec

$queryString = Interpolator::interpolate($query, $parameters);

I hope this helps.

Version

database 2.0
PHP 8.1

🐛 Database reconnection logic

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

PostgreSQL

What happened?

A bug happened!

The bug with transactionLevel, when reconnect option is enabled. The more details can be found in discord https://discord.com/channels/538114875570913290/696388943440248963/1015169936357855242.

Version

database 14.2
PHP 8.1

The generated migration does not respect the binary field size.

CycleORM: 2.4
CycleMigrations: 4.2

Entity:

#[Entity]
class MyEntity
{
    #[Column(type: 'binary(16)', name: 'id', primary: true)]
    private Id $id;
}

will be generated as:

        $this->table('mytable')
        ->addColumn('id', 'binary', ['nullable' => false, 'default' => null])
        ->setPrimaryKeys(['id'])
        ->create();

Even if I change binary to binary(16) in the migration, the next cycle:migrate will generate and update to change the column type back to binary without any size.

Please allow specifying the size of binary columns.

💡 MySQL support unsigned integer attribute

I have an idea!

I have a DB with many columns of int unsigned. I want to manage this DB with cycle ORM. Required feature to mark integer columns as unsigned

💡 MySQL smallPrimary type

I have an idea!

Like bigPrimary we need to support smallPrimary type.

#[Cycle\Column(type: 'smallPrimary', unsigned: true)]
private ?int $id = null;

Skip testCreateOrderedIndex for MYSQL 5.7

Issue with where('ball', '<', 'water')🐛

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

When I put a where-statement like where('ball', '<', 'water'), I'm being presented with an error like:

Type: TypeError
Code: 0
Message: strtoupper(): Argument #1 ($string) must be of type string, int given
File: D:\websites\slim.local\vendor\cycle\database\src\Query\Traits\TokenTrait.php
Line: 171

I have the impression it has something to do with the operator value of '<'. But that isn't an int, is it?

Obviously I didn't expect an error.
Or am I doing something wrong?

Version

database 2.0
PHP 8.1

💡 Subquery column alias injection

I have an idea!

I have the query with subquery columns. I need to make aliases for subquery columns for having;

$result = $database
    ->select([
        'table1.id',
        $database->select('COUNT(1)')->from('table2')->where('inner_id', new Expression('table1.id')), // as cnt1
    ])
    ->from('table1')
    ->fetchAll();

now I need to do weird things like this

$result = $database
    ->select([
        'table1.id',
        new Fragment('('.$database->select('COUNT(1)')->from('table2')->where('inner_id', new Expression('table1.id')).') as cnt1'),
    ])
    ->from('table1')
    ->having('cnt1', 1)
    ->fetchAll();

We need something like ColumnAliasInjection to make it prettier

$result = $database
    ->select([
        'table1.id',
        new ColumnAliasInjection(
            $database->select('COUNT(1)')->from('table2')->where('inner_id', new Expression('table1.id')),
            'cnt1'
        )
    ])
    ->from('table1')
    ->having('cnt1', 1)
    ->fetchAll();

💡 Feature request: __set_state for driver configs

I have an idea!

There is some config:

new DatabaseConfig([
        'default' => 'default',

        'databases' => [
            'default' => [
                'connection' => 'mysql',
            ],
        ],

        'connections' => [
            'sqlite' => new \Cycle\Database\Config\SQLiteDriverConfig(
                connection: new \Cycle\Database\Config\SQLite\MemoryConnectionConfig(),
                driver: \Cycle\Database\Driver\SQLite\SQLiteDriver::class,
                queryCache: true,
            ),

            'mysql' => new \Cycle\Database\Config\MySQLDriverConfig(
                connection: new \Cycle\Database\Config\MySQL\TcpConnectionConfig(
                    database: 'project_name',
                    host: '127.0.0.1',
                    port: 3306,
                    user: 'test',
                    password: 'secret',
                ),
                queryCache: true,
            ),
        ],
    ]),

It would be good if these settings will be cached. It works well with var_export. But it cannot be recovered because driver configs don't have __set_state methods.

I have a suggestion to add default method __set_state to \Cycle\Database\Config\DriverConfig and override in another configs when it's required. I can create pull request if this idea will be accepted.

🐛 MySQL `SET` column is seen as of `unknown` type

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

When introspecting an existing database, one of the columns was marked as of "unknown" type.
The column is declared as

`Groups` set('A','B','C',…) DEFAULT NULL COMMENT 'Группы',

$column->getEnumValues() returns an empty array.

Version

database 2.2.2
PHP 8.0
MariaDB 10

💡 Get already configured \PDO connection from `Database` instance.

I have an idea!

Hello! Some legacy classes in my project required \PDO instance. At the moment I am not able to get it from Database instance.

In doctrine we have this opportunity.

/* @var \Doctrine\DBAL\Driver\PDOConnection $pdo */
$pdo = $entityManager->getConnection()->getWrappedConnection();

I need some similar method in cycle.

🐛 Select / Columns alias bug with prefix

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

I have a bug when I try to use alias on select or column, the "prefix" is added wrongly to the field.
I have try with select and column and the same bug happend, but it's works well with alias on form.

Example :
$select = $dbal->database('default')->select()->from('col as c')->columns(['id_col as id']); $sqlStatement = $select->sqlStatement();

Result:
SELECT myprefix_id_col AS id
FROM myprefix_col AS c

So I don't know why the query get "myprefix_id_col" instead of "id_col" in the SELECT.

Thanks

Version

database 2.4
PHP 8.0.13

🐛 [Datetime Format] Incorrect format for [Y-m-d H:i:s.u]

From spiral-modules/database#85

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?

🐛 Bad query parameters interpolation

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

No response

What happened?

This part of code can work incorrectly

$query = str_replace(
	[':' . $index, $index],
	self::resolveValue($parameter),
	$query
);

All named parameters in a query parameter list should be prenormalized

Version

database 2.0
PHP 8.1

🐛 It seems that there is an issue with changing primary keys when you try to add sequenced type.

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

What happened?

I have the table:

class MyTable
{
    #[Column(type: 'uuid', name: 'uuid', primary: true)]
    private string $id;
}

When trying to add a new column to this table using the following migration command:

public function up(): void
{
    $this->database()->execute("ALTER TABLE my_table ADD COLUMN my_column SERIAL;")
}

I receive the following error when running "php ./app.php cycle:sync":

Detecting schema changes:
• my_db.my_table: 1 change(s) detected
[Cycle\Schema\Exception\SyncException]
Unable to change primary keys for existed table
in vendor/cycle/schema-builder/src/Generator/SyncTables.php:47

Previous: [Cycle\Database\Exception\DBALException]
Unable to change primary keys for existed table
in vendor/cycle/database/src/Driver/Handler.php:66

Also expression like
#[Column(type: 'bigPrimary', name: 'm_index', primary: false)]
not working too.

Thank you.

Version

ORM v2.2.2
PHP 8.1

💡 Using Common Table Expression (CTE)

I have an idea!

Please consider implementing a function to use a common table expression (CTE).
in SelectQuery.
This feature can greatly simplify complex queries and make them more understandable.

This might look like function withQuery() or withCTE()

Support json type for MySQL driver

According to Cycle documentation json column type is not supported for MySQL driver but JSON column exists in MySQL since version 5.7.
So it would be nice to cast json to proper type for MySQL driver in schema.

Columns use quote parsing to cause SQL error

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

I have the query columns a bug happened!

$result = $database
    ->select([
        'table1.id as t.id'
    ])
    ->from('table1')
    ->fetchAll();

I need this result

select `table1`.`id` as `t.id` from `table1`;

The actual result is

select `table1`.`id` as `t`.`id` from `table1`;

Result in query error：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

Version

database 2.0
PHP 8.1

Postgresql migrations endlessly tries to create enums

We have PostgreSQL table state like:

create table logs
(
    ...,
    target      varchar(7)
        constraint logs_target_enum_62e2522d7df9c
            check ((target)::text = 'catalog'::text)
);

but cycle:migrate can't detect that field already have enum type and each next migration tries to fix it in next way:

class OrmDefault561cd35f5d5487d642a5bd8b65997299 extends Migration
{
    public function up(): void
    {        
        $this->table('logs')
            ->alterColumn('target', 'enum', [
                'nullable' => true,
                'default'  => null,
                'values'   => [
                    'catalog'
                ]
            ])
            ->update();
    }

    public function down(): void
    {
        $this->table('logs')
            ->alterColumn('target', 'string', [
                'nullable' => true,
                'default'  => null,
                'size'     => 7
            ])
            ->update();
    }
}

As I can see migrator can't understand that field already have enum functionality based on constraints

feature request: a non-blocking driver

With the arrival of PHP 8.1, async code in PHP land doesn't have to use Promise or fall into the callback hell that we got trapped into before.

If cycle would provide an integration with a non-blocking database driver, this would mean that the ORM could be used to do multiple operations in parallel at the application level, and would allow for handling multiple connections as the worker level ( spiral/roadrunner-worker#19 ).

Currently, there's 2 available packages that allow you to create a non-blocking database connection:

1. amphp/mysql: https://github.com/amphp/mysql/tree/v3 ( branch v3 )
2. amphp/postgres: https://github.com/amphp/postgres/tree/v2 ( branch v2 )

🐛 Postgres: Sorting for Postgres tables does not work poperly.

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

PostgreSQL

What happened?

When Reflector starts sorting tables with dependencies https://github.com/cycle/database/blob/2.x/src/Schema/Reflector.php#L223 , depended table names don't contain table schema prefixes.

array(4) {
  ["public.threads"]=>
  array(0) {
  }
  ["public.messages"]=>
  array(2) {
    [0]=>
    string(7) "threads" <===== Should be `public.threads`
    [1]=>
    string(5) "users" <===== Should be `public.threads`
  }
  ["public.users"]=>
  array(0) {
  }
  ["public.auth_tokens"]=>
  array(0) {
  }
}

Version

database 2.0
PHP 8.1

🐛 The `?` char in criteria breaks query

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

No response

What happened?

This request

$searchQuery = '%foo?+%';
$user
    ->andWhere([
        '@or' => [
            ['@and' => [['foo' => ['like' => $searchQuery]]]],
            ['@and' => [['bar' => ['like' => $searchQuery]]]],
            ['@and' => [['baz' => true]]],
        ],
    ]);

throws error MySQL server version for the right syntax to use near '%foo'%fooTRUE+%'+%'+%'

Temporary solution looks like that:

$searchQuery = 'foo?';
$searchQuery= new Fragment("'" . $searchQuery. "'");

Version

database 2.0
PHP 8.1

Schema diff fails when decimal column precision defined as positional arg

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

What happened?

In short, this works:

#[Column(
    type: 'decimal(8,2)',
)]
public string $amount;

And this

#[Column(
    type: 'decimal',
    precision: 8,
    scale: 2
)]
public string $amount;

produces an exception while trying to do a schema diff (php app.php cycle:migrate)

exception is:

php app.php cycle:migrate
Detecting schema changes...
[2023-12-17T05:30:27.458514+00:00] roadrunner.ERROR: Cycle\Schema\Exception\ColumnException: Invalid column type definition in 'public.invoices'.'amount' in /app/vendor/cycle/schema-builder/src/Table/Column.php at line 122 [] []
[Cycle\Schema\Exception\ColumnException]
Invalid column type definition in 'public.invoices'.'amount'
in vendor/cycle/schema-builder/src/Table/Column.php:122

Previous: [ArgumentCountError]
Too few arguments to function Cycle\Database\Schema\AbstractColumn::decimal(), 0 passed and at least 1 expected
in vendor/cycle/database/src/Schema/AbstractColumn.php:603

Version

PHP 8.3.0
spiral framework v3.10.1
cycle-bridge v2.8.0
orm v2.5.0

PHP Deprecated

PHP8.1.1 Cycle 1.8

PHP Deprecated: Return type of Spiral\Core\InjectableConfig::getIterator() should either be compatible with IteratorAggregate::getIterator(): Traversable, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /app/vendor/spiral/core/src/InjectableConfig.php on line 108
PHP Deprecated: Return type of Spiral\Core\InjectableConfig::offsetExists($offset) should either be compatible with ArrayAccess::offsetExists(mixed $offset): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /app/vendor/spiral/core/src/InjectableConfig.php on line 64
PHP Deprecated: Return type of Spiral\Core\InjectableConfig::offsetGet($offset) should either be compatible with ArrayAccess::offsetGet(mixed $offset): mixed, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /app/vendor/spiral/core/src/InjectableConfig.php on line 72

🐛 Default value as array for json type

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

No response

What happened?

A bug happened!

Array to string conversion in vendor/cycle/database/src/Schema/AbstractColumn.php:342

Why is not possible to set default value as array for json type? It's a good feature. Just add something like this:

if ($this->getAbstractType() === 'json') {
    return json_encode($this->defaultValue, JSON_THROW_ON_ERROR);
}

Version

database 2.0
PHP 8.1

💡 Enhance error message

I have an idea!

It would be nice to have a file reference in the following error:

Cycle\Database\Exception\StatementException: SQLSTATE[HY000] [14] unable to open database file in {project}/vendor/cycle/database/src/Driver/SQLite/SQLiteDriver.php:39

Or if it's possible to check the file separately before the driver call.

💡 Add support for MySQL: ON DUPLICATE KEY UPDATE, PostgreSQL: ON CONFLICT

I have an idea!

Hello, Cycle team!

I've been looking for an ability to write more complex insert queries.
And so far I can't find a way to build a query like this one:

INSERT INTO some_table (`key`, `value`) VALUES (...) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)

As I understand PostgreSQL also has this functionality:
https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

So I believe It would be really great to be able to do something like that:

$this->database()
  ->insert('some_table')
  ->columns('key', 'value')
  ->values([['key_1', 'value_1'], ['key_2', 'value_2']])
  ->onConflict(new Fragment('value = VALUES(value)')) // or ->onDuplicate(...)
  ->run();

Because now, I have to write it like:

$rows = [...];
$query = <<<SQL
INSERT INTO `some_table` (`key`, `value`) VALUES $rows ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)
SQL;

$this->database()->getDriver()->execute($query);

Any feedback on this would be appreciated.