Hi!
I tried to found a way how to get values from "totals" element, which provided by ClickHouse if specified GROUP BY x WITH TOTALS. Does this DBAL driver provide this functionality?

[ "exception" => ErrorException { #message: "User Deprecated: The "FOD\DBALClickHouse\ClickHouseConnection" class implements "Doctrine\DBAL\Driver\PingableConnection" that is deprecated." #code: 0 #file: "./vendor/symfony/error-handler/DebugClassLoader.php" #line: 390 #severity: E_USER_DEPRECATED trace: { ./vendor/symfony/error-handler/DebugClassLoader.php:390 { …} ./vendor/friendsofdoctrine/dbal-clickhouse/src/Driver.php:57 { …} ./vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:398 { …} ./vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1938 { …} ./vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1276 { …} ./vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:212 { …} ./src/Command/DistribuneAggregateIncomeCommand.php:65 { App\Command\DistribuneAggregateIncomeCommand->getWagesData(): array^ › ->groupBy('v.video, v.publisher'); › $statisticsData = $qb->execute(); › } ./src/Command/DistribuneAggregateIncomeCommand.php:47 { …} ./vendor/symfony/console/Command/Command.php:256 { …} ./vendor/symfony/console/Application.php:989 { …} ./vendor/symfony/framework-bundle/Console/Application.php:96 { …} ./vendor/symfony/console/Application.php:290 { …} ./vendor/symfony/framework-bundle/Console/Application.php:82 { …} ./vendor/symfony/console/Application.php:166 { …} ./bin/console:43 { …} } } ]

Hello.

I try to check if records already exist, and get error = "An exception occurred while executing 'SELECT 1 dupl FROM payments WHERE order_id= :order_id AND appid= :appid LIMIT 1' with params ["xxx=:1:11", "yyy-Amazon"]:\n\nCan`t find meta"}

My code:
$stmt = $conn->prepare('SELECT 1 dupl FROM payments WHERE order_id= :order_id AND appid= :appid LIMIT 1'); $stmt->bindValue('order_id', $order_id); $stmt->bindValue('appid', $j->app_id); $stmt->execute(); while ($row = $stmt->fetch()) { file_put_contents('ch_dupl_' . date('Y-m-d') . '_' . $tbl . '_' . $j->app_id . '_' . uniqid() . '.json', json_encode($j)); return true; }

My table structure:
CREATE TABLE default.payments ( cohort Date, installdatetime DateTime, attributedtouchtime Nullable(DateTime), ... appid String, ... order_id String, ... ) ENGINE = MergeTree() PARTITION BY cohort ORDER BY appid SETTINGS index_granularity = 8192;

I'm using version 2.0.0, and when I'm using prepared statement and making insert, I have a problem with inccorect escaping of single quote. Example:

/** @var FOD\DBALClickHouse\ClickHouseConnection $connection */
$stmt = $connection->prepare("INSERT INTO my_table (some_val) VALUES (?)");
$stmt->execute(["some ' value"]);

// HttpCode:400 ;  ;Code: 62, e.displayText() = DB::Exception: Cannot parse expression of type String() here: 'some \'' value'

The problem as I see it lays inside class \FOD\DBALClickHouse\ClickHousePlatform.

public function quoteStringLiteral($str): string
{
    return parent::quoteStringLiteral(addslashes($str));
}

It adds slashes before single/double quote etc. But inside parent method of \Doctrine\DBAL\Platforms\AbstractPlatform, there is different type of escaping as I see (doubling of quote).

public function quoteStringLiteral($str)
{
    $c = $this->getStringLiteralQuoteCharacter();

    return $c . str_replace($c, $c . $c, $str) . $c;
}

So eventually in such conditions, my value becomes some \'' value. This leads to an error of query execution at ClickHouse server. Previosly, this quoteStringLiteral method was looking like this:

public function quoteStringLiteral($str) : string
{
    $c = $this->getStringLiteralQuoteCharacter();

    return $c . addslashes($str) . $c;
}

I think additional addslashes is redundant in current (v2.0.0) implementation. But I'm open to discuss this in order to find best working solutions with this problem.

Не могу понять как работь с IN
Пробовал разные варианты отсюда https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html
получаю всегда Notice: Array to string conversion in FOD\DBALClickHouse\ClickHouseStatement->getTypedParam() (line 390 of vendor/friendsofdoctrine/dbal-clickhouse/src/ClickHouseStatement.php).

FOD\DBALClickHouse\ClickHouseStatement->getTypedParam('bar') (Line: 296)
FOD\DBALClickHouse\ClickHouseStatement->execute(NULL) (Line: 153)
Doctrine\DBAL\Statement->execute() (Line: 75)

Приме кода:

<?php
$bar = [1, 2, 3];
$query = $conn->prepare('SELECT **** WHERE foo IN :bar');
$query->bindValue('bar', $bar);

Fatal error
Declaration of
FOD\DBALClickHouse\Driver::getSchemaManager(Doctrine\DBAL\Connection $conn)
must be compatible with
Doctrine\DBAL\Driver::getSchemaManager(
Doctrine\DBAL\Connection $conn,
Doctrine\DBAL\Platforms\AbstractPlatform $platform
)

Since v3.0.0 of Doctrine/DBAL (JUL, 2020) AbstractPlatform became a required arg

and in FOD settings are passed as a part of options array. That's why, for example, dbname setting is not applied and ClickHouse connection always uses "default" database.

Hello,
I tried to create a query using QueryBuilder, but got error:

Operation 'FOD\DBALClickHouse\ClickHousePlatform::appendLockHint' is not supported by platform.

The code which produces this error:

$qb = $this->createQueryBuilder('c')
->select('count(c)')
->andWhere('c.link = :link_id')
->setParameter('link_id', $linkId);

return $qb->getQuery()->getSingleScalarResult();

It produces DQL: SELECT count(c) FROM Trim\Clickhouse\Model\ClickStat c WHERE c.link = :link_id

Can you suggest where there is an error?

Btw, example with the connection use is working:

$conn = $this->getEntityManager()->getConnection();

return $conn->fetchColumn(
            'SELECT count() FROM clicks WHERE link_id = :link_id',
            ['link_id' => $linkId],
            0,
            ['link_id' => 'integer']
);

I try to define a column with default value = generateUUIDv4()

i try $newTable->addColumn('uuid', 'string', ['default' => 'generateUUIDv4()']);
but the value is string not the function ...

so for the moment i use a little trick like this:

public function up(Schema $schema) : void
    {
        /** @var Connection */
        $conn = $this->container->get('doctrine.dbal.clickhouse_connection');
        /** @var Schema */
        $fromSchema = new Schema();
        /** @var Schema */
        $toSchema = clone $fromSchema;

        $newTable = $toSchema->createTable('probe_datum');
        $newTable->addOption('engine', 'MergeTree');

        $newTable->addColumn('uuid', 'string', ['default' => 'generateUUIDv4()']);
        $newTable->addColumn('datetime_value', 'datetime');
        $newTable->addColumn('value', 'float');
        $newTable->addColumn('probe_id', 'integer');

        $newTable->setPrimaryKey(['uuid', 'datetime_value', 'probe_id']);

        $sqlArray = $fromSchema->getMigrateToSql($toSchema, $conn->getDatabasePlatform());

        foreach ($sqlArray as $sql) {
            $hackSql = str_replace('\'generateUUIDv4()\'', 'generateUUIDv4()', $sql);
            $conn->exec($hackSql);
        }
    }

how can i do this properly ?

Try using clickhouse db in my synfony 4.4 project...
I make a twig filter and try to make a request in ...

 /** @var EntityManager $em */
        $em = $this->container->get('doctrine.orm.clickhouse_entity_manager');
        /** @var ProbeDatumRepository $probeDatumRepository */
        $probeDatumRepository = $em->getRepository(ProbeDatum::class);

this throw exeption : TableGenerator not yet implemented.

Hello! Can you please tell me if this library can work with Symfony migrations? And if he can, could you give an example of how this can be done?

ORDER BY — Primary key.

A tuple of columns or arbitrary expressions. Example: ORDER BY (CounterID, EventDate). If a sampling expression is used, the primary key must contain it. Example: ORDER BY (CounerID, EventDate, intHash32(UserID)).

CREATE TABLE client_work_interval
(
uuid String,
timestamp DateTime,
date Date,
manager_id UInt32,
user_id UInt32,
status Int8
)
ENGINE=MergeTree() PARTITION BY toYYYYMM(date) ORDER BY (date, intHash32(user_id), intHash32(manager_id)) SAMPLE BY intHash32(user_id) SETTINGS index_granularity=8192

php bin/console doctrine:mapping:import 'App\Entity' annotation --path=src/Entity --em=clickhouse

In DatabaseDriver.php line 288:
                                                                                                 
  Table client_work_interval has no primary key. Doctrine does not support reverse engineering   
  from tables that don't have a primary key.                                                     
                                                                               

Although #11 has closed, there are still bugs present.

There is simple test from repo, just some modified tests/InsertTest.php - copy method testInsertViaQueryBuilder() and change order of calling setValue/setParameter:

    public function testInsertViaQueryBuilder2()
    {
        $qb = $this->connection->createQueryBuilder();

        $qb
            ->insert('test_insert_table')
            ->setValue('payload', ':payload')
            ->setValue('id', ':id')
            ->setParameter('payload', 'Is this string a question?')
            ->setParameter('id', 11, \PDO::PARAM_INT)
            ->execute();

        $this->assertEquals([
            [
                'payload' => 'Is this string a question?',
                'id' => 11,
            ],
        ], $this->connection->fetchAll("SELECT payload, id from test_insert_table ORDER BY id"));
    }

assertEquals also modified.

Test results:

There was 1 failure:

1) FOD\DBALClickHouse\Tests\InsertTest::testInsertViaQueryBuilder2
Failed asserting that two arrays are equal.
--- Expected
+++ Actual
@@ @@
 Array (
     0 => Array (
-        'payload' => 'Is this string a question?'
-        'id' => 11
+        'payload' => 'Is this string a question11'
+        'id' => 15
     )
 )

It happened because there are consecutive replacement of question mark in query 4b7dbe2:

            foreach (array_keys($this->values) as $key) {
                $sql = preg_replace(
                    '/(' . (is_int($key) ? '\?' : ':' . $key) . ')/i',
                    $this->getTypedParam($key),
                    $sql,
                    1
                );
            }

and question mark in string value replaced with next parameter value.
Real SQL executed:

INSERT INTO test_insert_table (payload, id) VALUES('Is this string a question11', ?)

Seriously, guys?

Hello, thanks a lot for the library.
Is there a way to get the statistics (like elapsed, rows count) and others data from Clickhouse ('meta', 'totals', 'extremes', 'rows_before_limit_at_least', 'statistics')?

Smi2 library provides them in Statement::init(), but I didn't find a way to get them back with the results (ClickhouseStatement::execute()).

Thanks a lot

php v7.4
friendsofdoctrine/dbal-clickhouse v1.5.3
clickhouse-server: yandex/clickhouse-server:20.3.8.53 (image from official yandex hub.docker.com)

Table schema:

CREATE TABLE new_table (
    `id` Int32, 
    `payload` String, 
    `date` DateTime
) 
ENGINE = MergeTree 
PARTITION BY toYYYYMMDD(date) 
ORDER BY (id, payload, date) 
SETTINGS index_granularity = 8192;

Code example:

/** @var \FOD\DBALClickHouse\Connection $connection */
$connection = $this->getDoctrine()->getConnection('clickhouse');
$connection->insert('new_table', [
    'id' => 2,
    'payload' => 'test?o',
    'date' => (new DateTime())->format('Y-m-d H:i:s'),
]);

Result:

An exception occurred while executing 'INSERT INTO new_table (id, payload, date) VALUES (?, ?, ?)' with params [2, "test?o", "2020-05-14 13:57:42"]:

Cannot parse expression of type String here: 'test'2020-05-14 13:57:42'o', ?)
IN:INSERT INTO new_table (id, payload, date) VALUES (2, 'test'2020-05-14 13:57:42'o', ?)

If change code to:

/** @var \FOD\DBALClickHouse\Connection $connection */
$connection = $this->getDoctrine()->getConnection('clickhouse');
$data = [
    'id' => 2,
    'payload' => 'test?o',
    'date' => (new DateTime())->format('Y-m-d H:i:s'),
];
$query = sprintf(
    'INSERT INTO new_table (%s) VALUES (%s)',
    implode(', ', array_keys($data)),
    substr(str_repeat('?, ', count($data)), 0, -2)
);
$statement = $connection->prepare($query);
$statement->execute(array_values($data));

Result: insert is successful

I have few tables imported from old database which was a MySQL database. The tables contain columns with Decimal data type.
While doing

$fromSchema = $conn->getSchemaManager()->createSchema();
$toSchema = clone $fromSchema;

I'm getting following Exception

[Doctrine\DBAL\DBALException] Unknown database type decimal(3, 2) requested, FOD\DBALClickHouse\ClickHousePlatform may not support it. in public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/AbstractPlatform.php on line 443

Is it possible to use QueryBuilder for select function?

Thanks

As @Ocramius suggested here doctrine/dbal#2981 (comment) I tried to do multiple insert with prepared statement (because writing of plain SQL is quite dangerous) and encountered with strange behavior - if text field value has question mark it would be replaced inline with next value to insert. Example:
Table structure (taken from doc with slight modification):

$fromSchema = $conn->getSchemaManager()->createSchema();
$toSchema = clone $fromSchema;


// create new table object
$newTable = $toSchema->createTable('new_table');

// add columns
$newTable->addColumn('id', 'integer', ['unsigned' => true]);
$newTable->addColumn('payload', 'string');
$newTable->addColumn('url', 'string');
$newTable->setPrimaryKey(['id']);

// execute migration SQLs to create table in ClickHouse
$sqlArray = $fromSchema->getMigrateToSql($toSchema, $conn->getDatabasePlatform());
foreach ($sqlArray as $sql) {
    $conn->exec($sql);
}

Insert query:

$statement = $conn->prepare('INSERT INTO new_table(id, payload, url) VALUES (?, ?, ?), (?, ?, ?);');
$statement->execute([123, 'foo-bar', 'https://some.url.com/?first=param', 456, 'bar-baz', 'http://example.com?p=1']);

Error produced by code:

PHP Fatal error: Uncaught ClickHouseDB\Exception\DatabaseException: Type mismatch in IN or VALUES section. Expected: UInt32. Got: String
IN:INSERT INTO new_table(id, payload, url) VALUES (123, 'foo-bar', 'https://some.url.com/456first=param'), ('bar-baz', 'http://example.com?p=1', ?);

Hello. Php8 has been around for half a year, is there any support planned?

Thanks

Symfony 5.0.2

$ php bin/console doctrine:database:create

throws exception

In Driver.php line 54:
  Connection parameter `dbname` is required                                               

As far as I can see vendor/doctrine/doctrine-bundle/Command/CreateDatabaseDoctrineCommand.php:86 unsets dbname to check whether db exists and connects without driver:

// Need to get rid of _every_ occurrence of dbname from connection configuration and we have already extracted all relevant info from url
unset($params['dbname'], $params['path'], $params['url']);

$tmpConnection = DriverManager::getConnection($params);
$tmpConnection->connect($input->getOption('shard'));
$shouldNotCreateDatabase = $ifNotExists && in_array($name, $tmpConnection->getSchemaManager()->listDatabases());

but \FOD\DBALClickHouse\Driver::connect does not let that.

if (! isset($params['dbname'])) {
     throw new ClickHouseException('Connection parameter `dbname` is required');
}

Such as

• Nullable(String)
• Nullable(Float64)
• Nullable(Int32)
• Array(Nullable(String))
• Nullable(UInt8)

etc.

When do you plan to add support for DBAL 3.5 and above?

https://github.com/FriendsOfDoctrine/dbal-clickhouse/blob/master/src/ClickHousePlatform.php#L1052

doctrine/dbal@e9a7aeb#diff-80f8e09bba3385ad6ffd160d565b52166f469a09b0c45b3cb0f93751490a3542R2010

we need to do something about this

I tried the symfony example in the readme and only get http errors from clickhouse. I created a trial account on their page, got the credentials and enabled the mysql interface. Their example works fine in the terminal.

mysql -h MY_INSTANCE.eu-central-1.aws.clickhouse.cloud -u MY_USER -P 3306 --password

But no matter what i set in my doctrine.yml file i get errors like this

Could not create database `myproject` for connection named default
An exception occurred while executing 'CREATE DATABASE `myproject`':

Received HTTP/0.9 when not allowed```

The port 8123 you provided in the readme also does not work.

In DBALException.php line 185:

An exception occurred while executing 'SELECT database, name FROM system.tables WHERE database != 'system' AND engine != 'View'':

An exception occurred while executing 'SELECT database, name FROM system.tables WHERE database != 'system' AND engine != 'View'':

Failed to connect to MY_INSTANCE.eu-central-1.aws.clickhouse.cloud port 8123 after 5004 ms: Timeout was reached

My config looks like this.

```yaml
doctrine:
    dbal:
        host:          MY_INSTANCE.eu-central-1.aws.clickhouse.cloud
        port:          3306
        user:          default
        password:      "MY_PASSWORD"
        dbname:        myproject
        driver_class:  FOD\DBALClickHouse\Driver
        wrapper_class: FOD\DBALClickHouse\Connection
        options:
            enable_http_compression: 1
            max_execution_time:      60

And i get this error

bin/console doctrine:schema:update --dump-sql --complete --force

In DBALException.php line 185:

  An exception occurred while executing 'SELECT database, name FROM system.tables WHERE database != 'system' AND engine != 'View'':

  An exception occurred while executing 'SELECT database, name FROM system.tables WHERE database != 'system' AND engine != 'View'':

  Received HTTP/0.9 when not allowed


In DBALException.php line 185:

  An exception occurred while executing 'SELECT database, name FROM system.tables WHERE database != 'system' AND engine != 'View'':

  Received HTTP/0.9 when not allowed


In Statement.php line 190:

  Received HTTP/0.9 when not allowed


doctrine:schema:update [--em EM] [--complete] [--dump-sql] [-f|--force]```

In migration when I add.

$table->addColumn('field', Type::STRING, ['length' => 3, 'notnull' => false]);

Error Unknown database type nullable(fixedstring(3)) requested, FOD\DBALClickHouse\ClickHousePlatform may not support it.

Hi guys!
Is there a way how to pass already prepared smi2 clickhouse client instead of just clickhouse connection options? In my way it exists as symfony service and has set additional options such compression.

Hi.
While doing migrations (Symfony5) and perform table creation I get now:

HttpCode:500 ; ;Code: 36. DB::Exception: This syntax for *MergeTree engine is deprecated. Use extended storage definition syntax with ORDER BY/PRIMARY KEY clause.See
also allow_deprecated_syntax_for_merge_tree setting. (BAD_ARGUMENTS) (version 22.7.2.15 (official build))

When I try to execute bin/console doctrine:fixtures:load
I get an error Operation 'FOD\DBALClickHouse\Connection::beginTransaction' is not supported by platform

Could you help me please? How can I execute and apply fixtures?

I try using a clickhouse and maria db in my symfony 4.4 project ...

i setup doctrine like this :
config/packages/doctrine.yaml

doctrine:
    dbal:
        default_connection: maria
        connections:
            maria:
                url: '%env(resolve:DATABASE_URL)%'
                charset: utf8mb4
                default_table_options:
                    collate: utf8mb4_unicode_ci
            clickhouse:
                host:     '%env(resolve:CLICKHOUSE_URL)%'
                port:     '%env(resolve:CLICKHOUSE_PORT)%'
                user:     '%env(resolve:CLICKHOUSE_USER)%'
                password: '%env(resolve:CLICKHOUSE_PWD)%'
                dbname:   '%env(resolve:CLICKHOUSE_DBNAME)%'
                driver_class: FOD\DBALClickHouse\Driver
                wrapper_class: FOD\DBALClickHouse\Connection
                options:
                    enable_http_compression: 1
                    max_execution_time: 60
    orm:
        auto_generate_proxy_classes: true
        default_entity_manager: default
        entity_managers:
            default:
                connection: maria
                naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
                auto_mapping: true
                mappings:
                    App:
                        is_bundle: false
                        type: staticphp
                        dir: '%kernel.project_dir%/src/Entity/'
                        prefix: 'App\Entity'
                        alias: App
            clickhouse:
                connection: clickhouse
                naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
                mappings:
                    App:
                        is_bundle: false
                        type: staticphp
                        dir: '%kernel.project_dir%/src/EntityClickhouse/'
                        prefix: 'App\EntityClickhouse'
                        alias: Clickhouse

Then i generate a Migration and i try to create a table following the Reame

but $fromSchema = $conn->getSchemaManager()->createSchema();
generate a error :

  [ClickHouseDB\Exception\DatabaseException (60)]        
  Table api_client.computed doesn't exist.               
  IN:DESCRIBE TABLE `api_client`.`computed` FORMAT JSON  

'api_client' is the dbname ( already created ).
i didn't know why they look for 'computed' table ...
( this table didn't exist, the db is empty )

When I'm trying to create mapping from table in MergeTree by command

php bin/console doctrine:mapping:import App\\Entity xml --path=config/doctrine --em=clickhouse

it returns following

In DatabaseDriver.php line 288:
                                                                                                 
  Table stat has no primary key. Doctrine does not support reverse engineering from tables that  
   don't have a primary key.                 

src/ClickHouseConnection.php:42
first params only have "host port username password"
but actually it also support other params "config readonly https sslCA"

outdated interface ResultStatement error on execution:

Compile Error: Declaration of FOD\DBALClickHouse\ClickHouseStatement::fetch($fetchMode = null) must be compatible with Doctrine\DBA  
  L\Driver\ResultStatement::fetch($fetchMode = null, $cursorOrientation = PDO::FETCH_ORI_NEXT, $cursorOffset = 0) 

changed the interface to mysql port 9004