crate / crate-dbal

Doctrine Database Access Layer for Crate.IO

---

Help us improve CrateDB by taking our User Survey!

About

CrateDB is a distributed SQL database that makes it simple to store and analyze massive amounts of data in real-time.

CrateDB offers the benefits of an SQL database and the scalability and flexibility typically associated with NoSQL databases. Modest CrateDB clusters can ingest tens of thousands of records per second without breaking a sweat. You can run ad-hoc queries using standard SQL. CrateDB's blazing-fast distributed query execution engine parallelizes query workloads across the whole cluster.

CrateDB is well suited to containerization, can be scaled horizontally using ephemeral virtual machines (e.g., Kubernetes, AWS, and Azure) with no shared state. You can deploy and run CrateDB on any sort of network — from personal computers to multi-region hybrid clouds and the edge.

Features

• Use standard SQL via the PostgreSQL wire protocol or an HTTP API.
• Dynamic table schemas and queryable objects provide document-oriented features in addition to the relational features of SQL.
• Support for time-series data, real-time full-text search, geospatial data types and search capabilities.
• Horizontally scalable, highly available and fault-tolerant clusters that run very well in virtualized and containerized environments.
• Extremely fast distributed query execution.
• Auto-partitioning, auto-sharding, and auto-replication.
• Self-healing and auto-rebalancing.
• User-defined functions (UDFs) can be used to extend the functionality of CrateDB.

Screenshots

CrateDB provides an Admin UI:

Try CrateDB

The fastest way to try CrateDB out is by running:

sh$ bash -c "$(curl -L try.crate.io)"

Or spin up the official Docker image:

sh$ docker run --publish 4200:4200 --publish 5432:5432 --env CRATE_HEAP_SIZE=1g crate -Cdiscovery.type=single-node

Visit the installation documentation to see all the available download and install options.

Once you're up and running, head over to the introductory docs. To interact with CrateDB, you can use the Admin UI sql console or the CrateDB shell CLI tool. Alternatively, review the list of recommended clients and tools that work with CrateDB.

For container-specific documentation, check out the CrateDB on Docker how-to guide or the CrateDB on Kubernetes how-to guide.

Contributing

This project is primarily maintained by Crate.io, but we welcome community contributions!

See the developer docs and the contribution docs for more information.

Security

The CrateDB team and community take security bugs seriously. We appreciate your efforts to responsibly disclose your findings, and will make every effort to acknowledge your contributions.

If you think you discovered a security flaw, please follow the guidelines at SECURITY.md.

Help

Looking for more help?

• Try one of our beginner tutorials, how-to guides, or consult the reference manual.
• Check out our support channels.
• Crate.io also offers CrateDB Cloud, a fully-managed CrateDB Database as a Service (DBaaS). The CrateDB Cloud Tutorials will get you started.

crate-dbal's Issues

Locking dependencies

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)

Incompatibility to current doctrine/dbal 2.13.*

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.

Add compatibility with PHP 8.0

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.

How to get column options

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.

How to define number of shards and replicas?

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

PHP 7 support

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

PHP Fatal error: Uncaught Declaration of `PDOCrateDB::prepare` should be compatible with `PDO::prepare`

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

Unable to install via composer

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

Inserting a map

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?

DBAL 2.10

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

Update to PHP 8.3

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.

test

test

How to get number of shards and replicas?

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

Latest DBAL v. 0.3.1 not compatible with Crate.io v. 2.3

Hi,

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

crate-dbal/composer.json

Line 12 in 54ac194

"crate/crate-pdo": "~0.6.0"

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

Add compatibility with Doctrine DBAL 3.x and 4.x

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.

upgrade docs build system

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

Not compatible with 2.3.5

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

Flush functionality

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?

Check if CrateDB DBAL can be used with PostgreSQL PDO instead of CrateDB PDO

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.

BOOLEAN support for Crate-DBAL

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] 

Doctrine DBAL version problem with Symfony2

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.

UNIQUE handling

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