test

The current DBAL code silently discards UNIQUE commands on columns. This has bitten me in production now :/

https://github.com/crate/crate-dbal/blob/master/src/Crate/DBAL/Platforms/CratePlatform.php#L699

Please throw an exception telling people to use a primary key instead as described on https://crate.io/docs/reference/sql/ddl.html#primary-key

this repo uses the old Crate docs build system. we should update it to use the new build system (https://github.com/crate/docs)

for instructions, see here: crate/crate-howtos#163

Hi guys,

I tried to create some row:

$viewsTest->setPage('1');
$viewsTest->setPrevPage('2');

$this->getEntityManager()->persist($viewsTest);
$this->getEntityManager()->flush();

and I see this exception:
Crate\PDO\Exception\UnsupportedException : Unsupported functionality

Looks strange and it was not sufficiently informative.

I check WriteTest.php https://github.com/crate/crate-dbal/blob/master/test/Crate/Test/DBAL/Functional/WriteTest.php and I see what they write insert query like a PDO style. But I don't want to use PDO style, I want to use ORM style 🙂

How I can make INSERT query through DBAL?

Hi everyone
this is a follow up to my last issue #75.
I'm still working on my automatically schema dump and update script.

This time I try to query the current amount of shards, partition options and number of replicas.
Unfortunately the result of $table->getOptions() is empty. Thats my code:

$connectionParams = [
    'user'        => null,
    'password'    => null,
    'host'        => '127.0.0.1',
    'port'        => 4200,
    'driverClass' => 'Crate\DBAL\Driver\PDOCrate\Driver',
];
$connection = DriverManager::getConnection($connectionParams);
$platform = $connection->getDatabasePlatform();

$SchemaManager = $connection->getSchemaManager();

$schema = $SchemaManager->createSchema();

foreach ($schema->getTables() as $table) {
    print_r($table->getOptions());
}

My results

Array
(
    [create_options] => Array
        (
        )

)
Array
(
    [create_options] => Array
        (
        )

)

This is my schema: https://github.com/statusengine/worker/blob/master/lib/crateDB.sql
In the docs I only found how to set the table options - not how to read theme.

I'm on version crate-dbal 2.2.0 and CrateDB 4.1.2

Playing with Symfony 2.6.3 i wanted to install latest crate-dbal package. During composer require i have the following error:

Loading composer repositories with package information
Updating dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Can only install one of: doctrine/dbal[v2.5.1, 2.3.5].
    - Can only install one of: doctrine/dbal[2.3.5, v2.5.1].
    - crate/crate-dbal 0.1.0 requires doctrine/dbal 2.3.5 -> satisfiable by doctrine/dbal[2.3.5].
    - Installation request for crate/crate-dbal ~0.1 -> satisfiable by crate/crate-dbal[0.1.0].
    - Installation request for doctrine/dbal == 2.5.1.0 -> satisfiable by doctrine/dbal[v2.5.1].

Installation failed, reverting ./composer.json to its original content

Why crate-dbal support only:
"doctrine/dbal": "2.3.5"
and does not support latest Doctrine2 stable version?

I needed to make some downgrades in my project to make it work.

Creating QueryBuilder and executing sample query with:

        $conn = DriverManager::getConnection(array(/*..*/));
        $qb = $conn->createQueryBuilder();
        $qb
            ->select('id', 'created_at')
            ->from('tweets', 't')
            ->where('t.retweeted = :retweeted')
            ->setParameter('retweeted', true)
            ->execute();

throws an Exception:

DBALException: An exception occurred while executing 'SELECT id, created_at FROM tweets t WHERE t.retweeted = ?' with params [true]:

SQLActionException[Can't convert "1" to boolean] 

About

@hlcianfagna updated the PDO repository to exercise CI tasks on PHP 8.3, together with an adjustment to the project's metadata.

• crate/crate-pdo#152

It will also be sensible to be applied here.

Hi there,

this library currently uses "doctrine/dbal": "^2.12". We might want to also check compatibility with version 3.x and 4.x. Doctrine DBAL stable is currently at version 3.6.2, and version 4.0.0 is around the corner (currently in beta).

With kind regards,
Andreas.

Hallo,
this is a follow up of my series #75 and #84.

I try to read the following table definition using DBAL:

CREATE TABLE statusengine_logentries (
    logentry_time timestamp,
    entry_time timestamp,
    logentry_type int,
    logentry_data string,
    node_name string,
    day as date_trunc('day', logentry_time * 1000)
) CLUSTERED INTO 4 shards partitioned by (day) with (number_of_replicas = '0');

My issue is to read the day column with all options. I didn't find any method to read the as date_trunc('day', logentry_time * 1000) part of the column definition.

Thats my code:

$connectionParams = [
    'user'        => null,
    'password'    => null,
    'host'        => '127.0.0.1',
    'port'        => 4200,
    'driverClass' => 'Crate\DBAL\Driver\PDOCrate\Driver',
];
$connection = DriverManager::getConnection($connectionParams);
$platform = $connection->getDatabasePlatform();

$SchemaManager = $connection->getSchemaManager();

$schema = $SchemaManager->createSchema();

foreach ($schema->getTables() as $table) {
    foreach ($table->getColumns() as $column) {
        print_r($column->getCustomSchemaOptions()); //empty array
        print_r($column->getPlatformOptions()); //empty array
    }
}

I used crate-dbal 2.3.0 and CrateDB 4.1.6.

Problem

GH-127 tripped CI with an error unrelated to the change at hand. It looks serious, something may be broken.

There was 1 error:

1) Crate\Test\DBAL\Functional\BindingTestCase::testBindPositionalParam
Doctrine\DBAL\Exception: An exception occurred while executing 'SELECT * FROM sys.cluster WHERE name = ?':

Declaration of Crate\PDO\PDOCrateDB::prepare(string $statement, array $options = Array) should be compatible with PDO::prepare($statement, $options = NULL)

-- https://github.com/crate/crate-dbal/actions/runs/7694875283/job/20966570450?pr=127#step:8:37

Hi,

When trying to install via compose getting error as below:

composer require crate/crate-dbal:~0.0.2
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - crate/crate-dbal 0.0.4 requires crate/crate-pdo 0.0.5 -> satisfiable by crate/crate-pdo[0.0.5].
    - crate/crate-dbal 0.0.3 requires crate/crate-pdo 0.0.4 -> satisfiable by crate/crate-pdo[0.0.4].
    - crate/crate-dbal 0.0.2 requires crate/crate-pdo 0.0.3 -> satisfiable by crate/crate-pdo[0.0.3].
    - crate/crate-pdo 0.0.5 requires amphp/artax ~1.0.0-rc2 -> no matching package found.
    - crate/crate-pdo 0.0.4 requires amphp/artax ~1.0.0-rc2 -> no matching package found.
    - crate/crate-pdo 0.0.3 requires amphp/artax ~1.0.0-rc2 -> no matching package found.
    - Installation request for crate/crate-dbal ~0.0.2 -> satisfiable by crate/crate-dbal[0.0.2, 0.0.3, 0.0.4].

Regards,
Chris

Doctrine\DBAL\Connection::insert() does this:

$query = 'INSERT INTO ' . $tableName
       . ' (' . implode(', ', $cols) . ')'
       . ' VALUES (' . implode(', ', $placeholders) . ')';

return $this->executeUpdate($query, array_values($data), $types);

I have the same basic table setup as you have in the readme.md:

$sm = $conn->getSchemaManager();

    $table = new \Doctrine\DBAL\Schema\Table('items');
    $objDefinition = array(
        'type' => \Crate\DBAL\Types\MapType::STRICT,
        'data' => array(
            new \Doctrine\DBAL\Schema\Column('id',  \Doctrine\DBAL\Types\Type::getType('integer'), array()),
            new \Doctrine\DBAL\Schema\Column('name',  \Doctrine\DBAL\Types\Type::getType('string'), array()),
        ),
    );
    $table->addColumn(
        'object_column', \Crate\DBAL\Types\MapType::NAME,
        array('platformOptions' => $objDefinition)
    );
    $sm->createTable($table);

Inserting a row with a map column:

$conn->insert('items', [
        $conn->quoteIdentifier('object_column')  => json_encode([
            'id' => 1,
            'name' => 'inner object test'
        ])
    ]);

Will result in the following exception:

DBALException: An exception occurred while executing 'INSERT INTO items ("object_column") VALUES (?)' with params ["{\"id\":1,\"name\":\"inner object test\"}"]:

SQLActionException[Validation failed for object_column: "'{"id":1,"name":"inner object test"}'" has a type that can't be implicitly cast to that of "items.object_column" (object)]

It looks like something in doctrine is stopping me from inserting this data.. any help?

Hi.

I just wanted to ask why the DBAL Version is locked to 2.9?
Due to the nature of ^ in semver the laravel-crate.io package requires always 2.0 and adds dbal at version ^2.10.

Can this maybe be fixed? If I know the issue, I can even create a PR

Thanks for the answer

Hi,

Latest version 0.3.1 of DBAL doesn't seem to be compatible with latest Crate.io 2.3.5. The reason being:

CrateSchemaManager::listTableColumns is broken as getListTableConstraintsSQL returns empty string because this line should read:

"WHERE table_name = '$t[1]' AND table_schema = '$t[0]' AND constraint_type = 'PRIMARY KEY'";

note: PRIMARY_KEY -> PRIMARY KEY, however even if we fix that, result of the above query differs between 2.0.4 (where DBAL works OK) and 2.3.5.

v. 2.0.4:

{"cols":["constraint_name","constraint_type"],"col_types":[[100,4],4],"rows":[[["id"],"PRIMARY_KEY"]],"rowcount":1,"duration":0.911611}
note: constraint_name returns array of PK column names

v. 2.3.5:

{"cols":["constraint_name","constraint_type"],"col_types":[4,4],"rows":[["cdr_pk","PRIMARY KEY"]],"rowcount":1,"duration":0.915925}

note: constraint_name returns string "table_name_pk" ?

Regards,
Chris

It seems like that the crate connection does not implement the dbal Connection interface and so currently failing. For dbal 2.13.* (2.12.* works). See comment by @mdumoulin here doctrine/dbal#4619 (comment).

EDIT: Seems like correct interface is used over the ServerInfoAwareInterface but the false statement class is set for PDO.

Hi,

Latest DBAL v. 0.3.1 depends on PDO 0.6. see:

therefore is NOT compatible with Crate.io 2.3. Would you be able to make new DBAL release that depends on PDO 1.0.0 to make it work with Crate.io 2.3?

Regards,
Chris

Hi ...

I'm currently playing with crate and symfony 2.8 on php 7.0.0-1~dotdeb+8.1.

I would love to install crate-dbal via composer in this environment. Do you have any information if php 7 will be supported in next version?

Thanks

Hi again,

while this library is currently based upon crate-pdo, in turn using the HTTP interface of CrateDB, we might want to check if it will be feasible to use the PostgreSQL protocol directly by using the PostgreSQL PDO extension.

With kind regards,
Andreas.

Hi there,

PHP 8.0.1 has been released and PHP 8.1.x is in the making. We might want to follow that.

With kind regards,
Andreas.

Hello,

the package is still dependant on dbal v2. The newest Release ist v4 and soon there will be v5.
Symfony 7 and Laravel 10 dropped support for this dbal version.

Here is the conflict in the package.json of the Symfony package.
https://github.com/symfony/symfony/blob/7.1/composer.json#L167

Do you have an idea how to upgrade?

Hi,
I want to use DBAL to manage schema updates.
Unfortunately I don't know how pass shards and replicas.

In addition, when I run this:

$connectionParams = [
                'user'        => null,
                'password'    => null,
                'host'        => '127.0.0.1',
                'port'        => 4200,
                'driverClass' => 'Crate\DBAL\Driver\PDOCrate\Driver',
];
$connection = DriverManager::getConnection($connectionParams);
$platform = $connection->getDatabasePlatform();

$SchemaManager = $connection->getSchemaManager();

$schema = $SchemaManager->createSchema();

I get:

PHP Notice:  Undefined offset: 0 in vendor/crate/crate-dbal/src/Crate/DBAL/Schema/CrateSchemaManager.php on line 62

Looks like there is a bug with primary keys.

I'm using version crate-dbal 2.0.0 and CrateDB 4.0.4

Do we really want to lock this here? Wouldn't we then miss if something break on newer library versions?

Originally posted by @seut in #98 (comment)