php ClickHouse wrapper

Overview

PHP ClickHouse wrapper

Build Status Downloads Packagist Licence Quality Score Code Coverage

Features

  • No dependency, only Curl (support php >=7.1 )
  • Select parallel queries (asynchronous)
  • Asynchronous bulk inserts from CSV file
  • Http compression (Gzip), for bulk inserts
  • Find active host, check cluster
  • Select WHERE IN ( local csv file )
  • SQL conditions & template
  • tablesSize & databaseSize
  • listPartitions
  • truncateTable in cluster
  • Insert array as column
  • Get master node replica in cluster
  • Get tableSize in all nodes
  • Async get ClickHouse progress function
  • streamRead/Write & Closure functions

Russian articles habr.com 1 on habr.com 2

Install composer

composer require smi2/phpclickhouse

In php

// vendor autoload 
$db = new ClickHouseDB\Client(['config_array']);
$db->ping();

Last stable version for

  • php 5.6 <= 1.1.2
  • php 7.2 <= 1.3.10
  • php 7.3 >= 1.4.x

Packagist

Start

Connect and select database:

$config = [
    'host' => '192.168.1.1',
    'port' => '8123',
    'username' => 'default',
    'password' => ''
];
$db = new ClickHouseDB\Client($config);
$db->database('default');
$db->setTimeout(1.5);      // 1500 ms
$db->setTimeout(10);       // 10 seconds
$db->setConnectTimeOut(5); // 5 seconds

Show tables:

print_r($db->showTables());

Create table:

$db->write('
    CREATE TABLE IF NOT EXISTS summing_url_views (
        event_date Date DEFAULT toDate(event_time),
        event_time DateTime,
        site_id Int32,
        site_key String,
        views Int32,
        v_00 Int32,
        v_55 Int32
    )
    ENGINE = SummingMergeTree(event_date, (site_id, site_key, event_time, event_date), 8192)
');

Show create table:

echo $db->showCreateTable('summing_url_views');

Insert data:

$stat = $db->insert('summing_url_views',
    [
        [time(), 'HASH1', 2345, 22, 20, 2],
        [time(), 'HASH2', 2345, 12, 9,  3],
        [time(), 'HASH3', 5345, 33, 33, 0],
        [time(), 'HASH3', 5345, 55, 0, 55],
    ],
    ['event_time', 'site_key', 'site_id', 'views', 'v_00', 'v_55']
);

If you need to insert UInt64 value, you can wrap the value in ClickHouseDB\Type\UInt64 DTO.

$statement = $db->insert('table_name',
    [
        [time(), UInt64::fromString('18446744073709551615')],
    ],
    ['event_time', 'uint64_type_column']
);
UInt64::fromString('18446744073709551615')

Select:

$statement = $db->select('SELECT * FROM summing_url_views LIMIT 2');

Work with Statement:

// Count select rows
$statement->count();

// Count all rows
$statement->countAll();

// fetch one row
$statement->fetchOne();

// get extremes min
print_r($statement->extremesMin());

// totals row
print_r($statement->totals());

// result all
print_r($statement->rows());

// totalTimeRequest
print_r($statement->totalTimeRequest());

// raw answer JsonDecode array, for economy memory
print_r($statement->rawData());

// raw curl_info answer
print_r($statement->responseInfo());

// human size info
print_r($statement->info());

// if clickhouse-server version >= 54011
$db->settings()->set('output_format_write_statistics',true);
print_r($statement->statistics());

Select result as tree:

$statement = $db->select('
    SELECT event_date, site_key, sum(views), avg(views)
    FROM summing_url_views
    WHERE site_id < 3333
    GROUP BY event_date, url_hash
    WITH TOTALS
');

print_r($statement->rowsAsTree('event_date.site_key'));

/*
(
    [2016-07-18] => Array
        (
            [HASH2] => Array
                (
                    [event_date] => 2016-07-18
                    [url_hash] => HASH2
                    [sum(views)] => 12
                    [avg(views)] => 12
                )
            [HASH1] => Array
                (
                    [event_date] => 2016-07-18
                    [url_hash] => HASH1
                    [sum(views)] => 22
                    [avg(views)] => 22
                )
        )
)
*/

Drop table:

$db->write('DROP TABLE IF EXISTS summing_url_views');

Features

Select parallel queries (asynchronous)

$state1 = $db->selectAsync('SELECT 1 as ping');
$state2 = $db->selectAsync('SELECT 2 as ping');

// run
$db->executeAsync();

// result
print_r($state1->rows());
print_r($state2->fetchOne('ping'));

Parallelizing massive inserts from CSV file

$file_data_names = [
    '/tmp/clickHouseDB_test.1.data',
    '/tmp/clickHouseDB_test.2.data',
    '/tmp/clickHouseDB_test.3.data',
    '/tmp/clickHouseDB_test.4.data',
    '/tmp/clickHouseDB_test.5.data',
];

// insert all files
$stat = $db->insertBatchFiles(
    'summing_url_views',
    $file_data_names,
    ['event_time', 'site_key', 'site_id', 'views', 'v_00', 'v_55']
);

Parallelizing errors

selectAsync without executeAsync

$select = $db->selectAsync('SELECT * FROM summing_url_views LIMIT 1');
$insert = $db->insertBatchFiles('summing_url_views', ['/tmp/clickHouseDB_test.1.data'], ['event_time']);
// 'Exception' with message 'Queue must be empty, before insertBatch, need executeAsync'

see example/exam5_error_async.php

Gzip & enable_http_compression

On fly read CSV file and compress zlib.deflate.

$db->settings()->max_execution_time(200);
$db->enableHttpCompression(true);

$result_insert = $db->insertBatchFiles('summing_url_views', $file_data_names, [...]);


foreach ($result_insert as $fileName => $state) {
    echo $fileName . ' => ' . json_encode($state->info_upload()) . PHP_EOL;
}

see speed test example/exam08_http_gzip_batch_insert.php

Max execution time

$db->settings()->max_execution_time(200); // second

Connection without port

$config['host']='blabla.com';
$config['port']=0;
// getUri() === 'http://blabla.com'


$config['host']='blabla.com/urls';
$config['port']=8765;
// getUri() === 'http://blabla.com/urls'

$config['host']='blabla.com:2224';
$config['port']=1234;
// getUri() === 'http://blabla.com:2224'



tablesSize & databaseSize

Result in human size

print_r($db->databaseSize());
print_r($db->tablesSize());
print_r($db->tableSize('summing_partions_views'));

Partitions

$count_result = 2;
print_r($db->partitions('summing_partions_views', $count_result));

Select WHERE IN ( local csv file )

$file_name_data1 = '/tmp/temp_csv.txt'; // two column file [int,string]
$whereIn = new \ClickHouseDB\Query\WhereInFile();
$whereIn->attachFile($file_name_data1, 'namex', ['site_id' => 'Int32', 'site_hash' => 'String'], \ClickHouseDB\Query\WhereInFile::FORMAT_CSV);
$result = $db->select($sql, [], $whereIn);

// see example/exam7_where_in.php

Bindings

Bindings:

$date1 = new DateTime("now"); // DateTimeInterface

$Bindings = [
  'select_date' => ['2000-10-10', '2000-10-11', '2000-10-12'],
  'datetime'=>$date,
  'limit' => 5,
  'from_table' => 'table'
];

$statement = $db->selectAsync("SELECT FROM {table} WHERE datetime=:datetime limit {limit}", $Bindings);

// Double bind in {KEY}
$keys=[
            'A'=>'{B}',
            'B'=>':C',
            'C'=>123,
            'Z'=>[':C',':B',':C']
        ];
$this->client->selectAsync('{A} :Z', $keys)->sql() // ==   "123 ':C',':B',':C' FORMAT JSON",

Simple sql conditions & template

Conditions is deprecated, if need use: $db->enableQueryConditions();

Example with QueryConditions:

$db->enableQueryConditions();

$input_params = [
  'select_date' => ['2000-10-10', '2000-10-11', '2000-10-12'],
  'limit' => 5,
  'from_table' => 'table'
];

$select = '
    SELECT * FROM {from_table}
    WHERE
    {if select_date}
        event_date IN (:select_date)
    {else}
        event_date=today()
    {/if}
    {if limit}
    LIMIT {limit}
    {/if}
';

$statement = $db->selectAsync($select, $input_params);
echo $statement->sql();

/*
SELECT * FROM table
WHERE
event_date IN ('2000-10-10','2000-10-11','2000-10-12')
LIMIT 5
FORMAT JSON
*/

$input_params['select_date'] = false;
$statement = $db->selectAsync($select, $input_params);
echo $statement->sql();

/*
SELECT * FROM table
WHERE
event_date=today()
LIMIT 5
FORMAT JSON
*/

$state1 = $db->selectAsync(
    'SELECT 1 as {key} WHERE {key} = :value',
    ['key' => 'ping', 'value' => 1]
);

// SELECT 1 as ping WHERE ping = "1"

Example custom query Degeneration in exam16_custom_degeneration.php

SELECT {ifint VAR} result_if_intval_NON_ZERO{/if}
SELECT {ifint VAR} result_if_intval_NON_ZERO {else} BLA BLA{/if}

Settings

3 way set any settings

// in array config
$config = [
    'host' => 'x',
    'port' => '8123',
    'username' => 'x',
    'password' => 'x',
    'settings' => ['max_execution_time' => 100]
];
$db = new ClickHouseDB\Client($config);

// settings via constructor
$config = [
    'host' => 'x',
    'port' => '8123',
    'username' => 'x',
    'password' => 'x'
];
$db = new ClickHouseDB\Client($config, ['max_execution_time' => 100]);

// set method
$config = [
    'host' => 'x',
    'port' => '8123',
    'username' => 'x',
    'password' => 'x'
];
$db = new ClickHouseDB\Client($config);
$db->settings()->set('max_execution_time', 100);

// apply array method
$db->settings()->apply([
    'max_execution_time' => 100,
    'max_block_size' => 12345
]);

// check
if ($db->settings()->getSetting('max_execution_time') !== 100) {
    throw new Exception('Bad work settings');
}

// see example/exam10_settings.php

Use session_id with ClickHouse

useSession() - make new session_id or use exists useSession(value)

// enable session_id
$db->useSession();
$sesion_AA=$db->getSession(); // return session_id

$db->write(' CREATE TEMPORARY TABLE IF NOT EXISTS temp_session_test (number UInt64)');
$db->write(' INSERT INTO temp_session_test SELECT number*1234 FROM system.numbers LIMIT 30');

// reconnect to continue with other session
$db->useSession($sesion_AA);

Array as column

$db->write('
    CREATE TABLE IF NOT EXISTS arrays_test_string (
        s_key String,
        s_arr Array(String)
    )
    ENGINE = Memory
');

$db->insert('arrays_test_string',
    [
        ['HASH1', ["a", "dddd", "xxx"]],
        ['HASH1', ["b'\tx"]],
    ],
    ['s_key', 's_arr']
);

// see example/exam12_array.php

Class for FormatLine array

var_dump(
    ClickHouseDB\Quote\FormatLine::CSV(
        ['HASH1', ["a", "dddd", "xxx"]]
    )
);

var_dump(
    ClickHouseDB\Quote\FormatLine::TSV(
        ['HASH1', ["a", "dddd", "xxx"]]
    )
);

// example write to file
$row=['event_time'=>date('Y-m-d H:i:s'),'arr1'=>[1,2,3],'arrs'=>["A","B\nD\nC"]];
file_put_contents($fileName,ClickHouseDB\Quote\FormatLine::TSV($row)."\n",FILE_APPEND);

HTTPS

$db = new ClickHouseDB\Client($config);
$db->settings()->https();

getServer System.Settings & Uptime

print_r($db->getServerUptime());

print_r($db->getServerSystemSettings());

print_r($db->getServerSystemSettings('merge_tree_min_rows_for_concurrent_read'));

ReadOnly ClickHouse user

$config = [
    'host' => '192.168.1.20',
    'port' => '8123',
    'username' => 'ro',
    'password' => 'ro',
    'readonly' => true
];

Direct write to file

Send result from clickhouse, without parse json.

$WriteToFile=new ClickHouseDB\WriteToFile('/tmp/_1_select.csv');
$db->select('select * from summing_url_views',[],null,$WriteToFile);
// or
$db->selectAsync('select * from summing_url_views limit 4',[],null,new ClickHouseDB\WriteToFile('/tmp/_3_select.tab',true,'TabSeparatedWithNames'));
$db->selectAsync('select * from summing_url_views limit 4',[],null,new ClickHouseDB\WriteToFile('/tmp/_4_select.tab',true,'TabSeparated'));
$statement=$db->selectAsync('select * from summing_url_views limit 54',[],null,new ClickHouseDB\WriteToFile('/tmp/_5_select.csv',true,ClickHouseDB\WriteToFile::FORMAT_CSV));

Stream

streamWrite() : Closure stream write

$streamWrite=new ClickHouseDB\Transport\StreamWrite($stream);

$client->streamWrite(
        $streamWrite,                                   // StreamWrite Class
        'INSERT INTO {table_name} FORMAT JSONEachRow',  // SQL Query
        ['table_name'=>'_phpCh_SteamTest']              // Binds
    );

streamWrite & custom Closure & Deflate

$stream = fopen('php://memory','r+');

for($f=0;$f<23;$f++) {  // Make json data in stream
        fwrite($stream, json_encode(['a'=>$f]).PHP_EOL );
}

rewind($stream); // rewind stream


$streamWrite=new ClickHouseDB\Transport\StreamWrite($stream);
$streamWrite->applyGzip();   // Add Gzip zlib.deflate in stream

$callable = function ($ch, $fd, $length) use ($stream) {
    return ($line = fread($stream, $length)) ? $line : '';
};
// Apply closure
$streamWrite->closure($callable);
// Run Query
$r=$client->streamWrite($streamWrite,'INSERT INTO {table_name} FORMAT JSONEachRow', ['table_name'=>'_phpCh_SteamTest']);
// Result
print_r($r->info_upload());

streamRead

streamRead is like WriteToFile

$stream = fopen('php://memory','r+');
$streamRead=new ClickHouseDB\Transport\StreamRead($stream);

$r=$client->streamRead($streamRead,'SELECT sin(number) as sin,cos(number) as cos FROM {table_name} LIMIT 4 FORMAT JSONEachRow', ['table_name'=>'system.numbers']);
rewind($stream);
while (($buffer = fgets($stream, 4096)) !== false) {
    echo ">>> ".$buffer;
}
fclose($stream); // Need Close Stream



// Send to closure

$stream = fopen('php://memory','r+');
$streamRead=new ClickHouseDB\Transport\StreamRead($stream);
$callable = function ($ch, $string) use ($stream) {
    // some magic for _BLOCK_ data
    fwrite($stream, str_ireplace('"sin"','"max"',$string));
    return strlen($string);
};

$streamRead->closure($callable);

$r=$client->streamRead($streamRead,'SELECT sin(number) as sin,cos(number) as cos FROM {table_name} LIMIT 44 FORMAT JSONEachRow', ['table_name'=>'system.numbers']);

insert Assoc Bulk

 $oneRow = [
            'one' => 1,
            'two' => 2,
            'thr' => 3,
            ];
            $failRow = [
                'two' => 2,
                'one' => 1,
                'thr' => 3,
            ];

$db->insertAssocBulk([$oneRow, $oneRow, $failRow])

Auth methods

   AUTH_METHOD_HEADER       = 1;
   AUTH_METHOD_QUERY_STRING = 2;
   AUTH_METHOD_BASIC_AUTH   = 3;

In config set auth_method

$config=[
    'host'=>'host.com',
    //...
    'auth_method'=>1,
];

progressFunction

// Apply function

$db->progressFunction(function ($data) {
    echo "CALL FUNCTION:".json_encode($data)."\n";
});
$st=$db->select('SELECT number,sleep(0.2) FROM system.numbers limit 5');


// Print
// ...
// CALL FUNCTION:{"read_rows":"2","read_bytes":"16","total_rows":"0"}
// CALL FUNCTION:{"read_rows":"3","read_bytes":"24","total_rows":"0"}
// ...

ssl CA

$config = [
    'host' => 'cluster.clickhouse.dns.com', // any node name in cluster
    'port' => '8123',
    'sslCA' => '...', 
];

Cluster

$config = [
    'host' => 'cluster.clickhouse.dns.com', // any node name in cluster
    'port' => '8123',
    'username' => 'default', // all node have one login+password
    'password' => ''
];


// client connect first node, by DNS, read list IP, then connect to ALL nodes for check is !OK!


$cl = new ClickHouseDB\Cluster($config);
$cl->setScanTimeOut(2.5); // 2500 ms, max time connect per one node

// Check replica state is OK
if (!$cl->isReplicasIsOk())
{
    throw new Exception('Replica state is bad , error='.$cl->getError());
}

// get array nodes, and clusers
print_r($cl->getNodes());
print_r($cl->getClusterList());


// get node by cluster
$name='some_cluster_name';
print_r($cl->getClusterNodes($name));

// get counts
echo "> Count Shard   = ".$cl->getClusterCountShard($name)."\n";
echo "> Count Replica = ".$cl->getClusterCountReplica($name)."\n";

// get nodes by table & print size per node
$nodes=$cl->getNodesByTable('shara.adpreview_body_views_sharded');
foreach ($nodes as $node)
{
    echo "$node > \n";
    // select one node
    print_r($cl->client($node)->tableSize('adpreview_body_views_sharded'));
    print_r($cl->client($node)->showCreateTable('shara.adpreview_body_views'));
}

// work with one node

// select by IP like "*.248*" = `123.123.123.248`, dilitmer `;`  , if not fount -- select first node
$cli=$cl->clientLike($name,'.298;.964'); // first find .298 then .964 , result is ClickHouseDB\Client

$cli->ping();



// truncate table on cluster
$result=$cl->truncateTable('dbNane.TableName_sharded');

// get one active node ( random )
$cl->activeClient()->setTimeout(0.01);
$cl->activeClient()->write("DROP TABLE IF EXISTS default.asdasdasd ON CLUSTER cluster2");


// find `is_leader` node
$cl->getMasterNodeForTable('dbNane.TableName_sharded');


// errors
var_dump($cl->getError());


//

Return Extremes

$db->enableExtremes(true);

Enable Log Query

You can log all query in ClickHouse

$db->enableLogQueries();
$db->select('SELECT 1 as p');
print_r($db->select('SELECT * FROM system.query_log')->rows());

isExists

$db->isExists($database,$table);

Debug & Verbose

$db->verbose();

Dev & PHPUnit Test

  • Don't forget to run composer install. It should setup PSR-4 autoloading.
  • Then you can simply run vendor/bin/phpunit and it should output the following
cp phpunit.xml.dist phpunit.xml
mcedit phpunit.xml

Edit in phpunit.xml constants:

<php>
    <env name="CLICKHOUSE_HOST" value="127.0.0.1" />
    <env name="CLICKHOUSE_PORT" value="8123" />
    <env name="CLICKHOUSE_USER" value="default" />
    <env name="CLICKHOUSE_DATABASE" value="phpChTestDefault" />
    <env name="CLICKHOUSE_PASSWORD" value="" />
    <env name="CLICKHOUSE_TMPPATH" value="/tmp" />
</php>

Run docker ClickHouse server

cd ./tests
docker-compose up

Run test

./vendor/bin/phpunit

./vendor/bin/phpunit --group ClientTest

./vendor/bin/phpunit --group ClientTest --filter testInsertNestedArray

./vendor/bin/phpunit --group ConditionsTest

Run PHPStan

# Main
./vendor/bin/phpstan analyse src tests --level 7
# SRC only
./vendor/bin/phpstan analyse src --level 7



# Examples
./vendor/bin/phpstan analyse example -a ./example/Helper.php



License

MIT

ChangeLog

See changeLog.md

Comments
  • Support for expressions

    Support for expressions

    How it's used in our project(simplified):

        $uuid = '0f372656-6a5b-4727-a4c4-f6357775d926';
        $uuidExpr = $chc->expr("UUIDStringToNum('{$uuid}')");
        $stat = $chc->insert(
            'sometable',
            ['2018-10-18', $uuidExpr],
            ['created_date', 'event_id',]
        );
    

    Where simplified sometable is:

    CREATE TABLE sometable (
        created_date Date,
        event_id FixedString(16)
    ) ENGINE = MergeTree(
        created_date,
        event_id,
        (created_date, event_id),
        8192
    );
    
    To Do 
    opened by dorantor 15
  • Неправильная подстановка bindings

    Неправильная подстановка bindings

    Вот такой код:

    $db = new ClickHouseDB\Client([
        "host" => "127.0.0.1",
        "port" => 8123,
        "username" => "default",
        "password" => ""
    ]);
    
    $db->select("SELECT :a, :b", [
        "a" => ":b",
        "b" => ":a"
    ])->rows();
    

    выдаёт ошибку:

    PHP Fatal error:  Uncaught ClickHouseDB\DatabaseException: Syntax error: failed at position 10: :a'', ':a' FORMAT JSON. Expected one of: identifier, alias, AS, token, AND, OR, BETWEEN, IS, LIKE, NOT LIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, Comma, QuestionMark, ORDER BY, SETTINGS, INTO OUTFILE, FORMAT, FROM, PREWHERE, WHERE, GROUP BY, WITH, HAVING, LIMIT, UNION ALL
    IN:SELECT '':a'', ':a' FORMAT JSON in /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php:162
    Stack trace:
    #0 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(196): ClickHouseDB\Statement->error()
    #1 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(210): ClickHouseDB\Statement->check()
    #2 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(459): ClickHouseDB\Statement->init()
    #3 /home/user/test/phpcl_bug/test.php(15): ClickHouseDB\Statement->rows()
    #4 {main}
      thrown in /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php on line 162
    

    Другой пример:

    $db->select("SELECT :a, :a2", [
        "a" => "x",
        "a2" => "x"
    ])->rows();
    
    PHP Fatal error:  Uncaught ClickHouseDB\DatabaseException: Syntax error: failed at position 16: 2 FORMAT JSON. Expected one of: identifier, alias, AS, token, AND, OR, BETWEEN, IS, LIKE, NOT LIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, Comma, QuestionMark, ORDER BY, SETTINGS, INTO OUTFILE, FORMAT, FROM, PREWHERE, WHERE, GROUP BY, WITH, HAVING, LIMIT, UNION ALL
    IN:SELECT 'x', 'x'2 FORMAT JSON in /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php:162
    Stack trace:
    #0 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(196): ClickHouseDB\Statement->error()
    #1 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(210): ClickHouseDB\Statement->check()
    #2 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(459): ClickHouseDB\Statement->init()
    #3 /home/user/test/phpcl_bug/test.php(22): ClickHouseDB\Statement->rows()
    #4 {main}
      thrown in /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php on line 162
    
    bug help wanted 
    opened by z80maniac 10
  • Drop support for legacy PHP versions

    Drop support for legacy PHP versions

    I have mentioned it few times ago, creating this issue just to be notified, when it is dropped so I don't have to come back regularly and read commits :)

    Current PHP version is 7.2, 7.3 will be released soon.

    5.6 is ~4 years old, currently with security support only, EOL in 5 months. 7.0 EOL in 6 months

    Therefore, old php versions support should be dropped and 7.1 set as minimum requirement.

    https://secure.php.net/supported-versions.php

    opened by simPod 7
  • StreamRead uses a lot of memory

    StreamRead uses a lot of memory

    When I try to get data from my big table, there is not enough memory. I am using StreamRead.

    $streamRead = new StreamRead($stream);
    $this->clickHouseManager->streamRead(
          $streamRead,
          'SELECT {columns} FROM {table_name} FORMAT CSVWithNames',
          [
                 'table_name' => $tableName,
                 'columns' => $columns,
          ]
    );
    

    In the public function execOne(CurlerRequest $request, $auto_close = false) after curl_exec($h); all data is buffered

    opened by Miriameri 6
  • Cleanup transport

    Cleanup transport

    I tried to cleanup Transport with Client a bit. This is first part. I tried to reflect changes in docs and examples as well. Currently it seems to me that there are usualy multiple APIs for the same thing so I'm trying to clean it up and make it more clear, simple so we avoid unwanted side effects.

    Eg. setTimeout and setConnectTimeout influence curl settings (CURLOPT_TIMEOUT_MS, CURLOPT_CONNECTTIMEOUT_MS). Before this PR it seemed like they controlled ClickHouse settings as they were called via eg. settings()->max_execution_time

    opened by simPod 6
  • Add Coding Standard

    Add Coding Standard

    I have added a CI check for coding standard https://github.com/doctrine/coding-standard

    It is set in a way that CI only checks the changed lines in pull requests and validates them against coding standard so there's no need to change to whole codebase for now.

    I recommend to check into the branch, run composer install and then eg. vendor/bin/phpcs src/Query/Degeneration/Bindings.php so you can see what it does.

    Also I have made changes to toSql() function in Query.php so it is in compliance with CS as a preview. Added a typehint so it is a BC break.

    When changed lines are not compliant with CS, it reports it in Travis

    image

    Let me know what you think and whether we will merge this or not.

    opened by simPod 6
  • Search with cyrillic symbols in 'LIKE' returns no results

    Search with cyrillic symbols in 'LIKE' returns no results

    I have a query, which has condition concat(name, ' ', last_name) LIKE '%some_string%' and I have found out, that if some_string has cyrillic symbols, the result has no rows. But when I use the same query in clickhouse-client, it returns correct results. Such behavior makes me think, that there is some problem with the body of request somewhere in phpClickHouse. I tried to debug it myself but have had no luck in detecting a place with the issue.

    opened by vetinary 6
  • Getter&setter for curler, refactoring

    Getter&setter for curler, refactoring

    Добрый день, спасибо за библиотеку. Добавил для возможности доступа к напрямую к курлеру Client->transport()->getCurler() (иногда бывает нужно).

    opened by Tgarifullin 6
  • fix clickhouse release 19.5.2.6 error parsing

    fix clickhouse release 19.5.2.6 error parsing

    After clean install new release 19.5.2.6 via docker, ClickHouse server errors ends with: (version 19.5.2.6 (official build)) Before: , e.what() = DB::Exception

    opened by toxbyte 5
  • Invalid user exception

    Invalid user exception

    I'm tried this library, it work fine when we submit correct during connect database, it work well. if we submit invalid user, php will failed. Hope it can use others handling like return false or etc to prevent php exit.

    opened by kstan79 5
  • cURL + fopen/fclose

    cURL + fopen/fclose

    Добрый день.

    Есть необходимость использовать прямую запись в файл (именно через простой select):

    $WriteToFile=new ClickHouseDB\WriteToFile('/tmp/_1_select.csv');
    $db->select('select * from summing_url_views',[],null,$WriteToFile);
    

    Однако, мне бы хотелось иметь возможность писать в уже существующий файл (с данными), а также дополнять его сразу после записи.

    Огромная просьба вывести в параметры WriteToFile мод fopen в getRequestRead() в Http.php.

    Заранее большое спасибо!

    opened by AntonSaykovsky 5
  • Cannot use socket_timeout

    Cannot use socket_timeout

    some big query ,can run in server it will be timeout when you select by sql in code, clickhouse give "socket_timeout=60000" ,like ''jdbc:clickhouse://127.0.0.1:8123/default?socket_timeout=60000' but i can't use this config in this project

    opened by suchen9209 1
  • Release 1.5.0 introduces BREAKING CHANGES

    Release 1.5.0 introduces BREAKING CHANGES

    Version 1.5.0 released yesterday introduced breaking changes and should be released as 2.0.0 ⚠️

    For example:

    Argument 1 passed to ClickHouseDB\Settings::setReadOnlyUser() must be an instance of ClickHouseDB\mixed, bool given, called in /app/vendor/smi2/phpclickhouse/src/Client.php on line 139

    This release contains types introduced in PHP8 while composer.json defines requirement "php": "^7.3|^8.0" and allows installation on PHP7.4 (which we're using).

    Please consider cleaning this up 🙂

    opened by Wirone 2
  • Error code not parsed after Clickhouse update

    Error code not parsed after Clickhouse update

    After update ClickHouse version from 20.8.2.3 to 22.8.9.24 exceptions thrown by this package have changed.


    ClickHouse v20.8.2.3:

    Response body:

    Code: 516, e.displayText() = DB::Exception: Username: Authentication failed: password is incorrect or there is no user with such name (version 20.8.2.3 (official build))

    Exception:

    \ClickHouseDB\Exception\DatabaseException

    Exception Message:

    Username: Authentication failed: password is incorrect or there is no user with such name IN:show databases FORMAT JSON

    Exception Code: 516


    ClickHouse v22.8.9.24:

    Response body:

    Code: 516. DB::Exception: Username: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED) (version 22.8.9.24 (official build))

    Exception:

    \ClickHouseDB\Exception\QueryException

    Exception Message:

    HttpCode:403 ; ;Code: 516. DB::Exception: Username: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED) (version 22.8.9.24 (official build))

    Exception Code: 403

    Can it be fixed in this package or it is Clickhouse problem?

    opened by DmytryHo 0
  • Fix the timeout test assertion

    Fix the timeout test assertion

    Changed log

    • Fix these following failed assertions:
    There was 1 failure:
    
    1) ClickHouseDB\Tests\FormatQueryTest::testClientTimeoutSettings
    Failed asserting that 0.0 is identical to 0.
    
    /data/phpClickHouse/tests/FormatQueryTest.php:74
    
    There was 1 failure:
    
    1) ClickHouseDB\Tests\FormatQueryTest::testClientTimeoutSettings
    Failed asserting that 10.0 is identical to 10.
    
    /data/phpClickHouse/tests/FormatQueryTest.php:69
    
    There was 1 failure:
    
    1) ClickHouseDB\Tests\FormatQueryTest::testClientTimeoutSettings
    Failed asserting that 5.14 is identical to 5.
    
    /data/phpClickHouse/tests/FormatQueryTest.php:74
    
    opened by peter279k 0
  • Migrating to the GitHub action

    Migrating to the GitHub action

    As title, since the Travis CI dot org is deprecated, the Travis CI dot com is not friendly for open source projects.

    Due to the above reason, I think it's time to migrate the CI to the GitHub action now.

    opened by peter279k 0
  • Backward compatibility for setTimeout

    Backward compatibility for setTimeout

    Hello with 1.4.5 version you've broke backward compatibility for https://github.com/smi2/phpClickHouse/blame/master/src/Client.php#L186

    Also please actualize readme examle https://github.com/smi2/phpClickHouse/blame/master/README.md#L66 $db->setTimeout(1.5); // 1 second , support only Int value o_O

    Thanks in advance

    opened by wezztt 3
Releases(1.5.0)
  • 1.5.0(Dec 21, 2022)

    2022-12-21 [Release 1.5.0]

    • Change exceptionCode in Clickhouse version 22.8.3.13 (official build) #180

    • Fix Docker for tests, Change the correct Docker image name #177

    • Some type fix

    • Fix types: max_execution_time & setConnectTimeOut, undo: Support floats in timeout and connect_timeout #173

    • add mbstring to composer require #183

    • fixed progressFunction #182

    • Add allow_plugins setting since Composer 2.2.x #178

    • Re upload 1.5.0 - hot fix types error

    Source code(tar.gz)
    Source code(zip)
  • 1.4.6(Jun 22, 2022)

  • 1.4.4(Apr 23, 2022)

  • 1.4.3(Apr 20, 2022)

    2022-04-20 [Release 1.4.3]

    • Fix: prevent enable_http_compression parameter from being overridden #164
    • For correct work with utf-8 . I am working on server with PHP 5.6.40 Update CurlerRequest.php #158
    • Add curl setStdErrOut, for custom StdOutErr.
    • Fix some test for check exceptions
    Source code(tar.gz)
    Source code(zip)
  • 1.4.2(Feb 11, 2022)

    2022-02-11 [Release 1.4.2]

    • Fixed issue with non-empty raw data processing during init() on every fetchRow() and fetchOne() call - PR #161
    Source code(tar.gz)
    Source code(zip)
  • 1.4.1(Jan 20, 2021)

  • 1.3.10(Sep 29, 2020)

    2019-09-29 [Release 1.3.10]

    Add two new types of authentication #139 Fixed typo in streamRead exception text #140 fix the exception(multi-statement not allow) when sql end with ';' #138 Added more debug info for empty response with error #135

    Source code(tar.gz)
    Source code(zip)
  • 1.3.9(Feb 3, 2020)

  • 1.3.8(Jan 17, 2020)

    2020-01-17 [Release 1.3.8]

    • #131 Fix: async loop breaks after 20 seconds
    • #129 Add client certificate support to able to work with Yandex ClickHouse cloud hosting
    • Delete dropOldPartitions
    • Fix error : The error of array saving #127
    • More test
    Source code(tar.gz)
    Source code(zip)
  • 1.3.7(Sep 20, 2019)

  • 1.3.6(Aug 24, 2019)

  • 1.3.5(Apr 28, 2019)

    2019-04-29 [Release 1.3.4]

    • #118 Fix Error in Conditions & more ConditionsTest
    • Fix phpStan warnings in getConnectTimeOut() & max_execution_time()
    Source code(tar.gz)
    Source code(zip)
  • 1.3.3(Apr 25, 2019)

  • 1.3.2(Mar 18, 2019)

    2019-03-18 [Release 1.3.2]

    • fix: add CSVWithNames to supported formats #107
    • Upgraded Expression proposal #106 -> UUIDStringToNum
    • Correct query format parsing #108
    • Can not use numbers() function in read requests #110
    • #109 Do not use keep-alive or reuse them across requests
    Source code(tar.gz)
    Source code(zip)
  • 1.3.1(Sep 25, 2018)

  • 1.2.4(Sep 11, 2018)

    2018-09-11 [Release 1.2.4]

    • Fix #91 ,Does not work inserting with the database name in the table
    • pull request #90 from simPod: Refactor partitions()
    Source code(tar.gz)
    Source code(zip)
  • 1.2.3(Aug 30, 2018)

    2018-08-30 [Release 1.2.3]

    • Escape values in arrays, pull request #87 from simPod/fix-escape
    • fix-bindings: pull request #84 from simPod/fix-bindings
    • Added quotes arount table and column names in the insert wrapper.
    • Docker Compose in tests
    Source code(tar.gz)
    Source code(zip)
  • 1.2.2(Jul 24, 2018)

  • 1.2.1(Jul 16, 2018)

    Release 1.2.1

    • New $client->getServerVersion()
    • Rewrite method $client->ping()
    • Fix include.php - ClickHouseException before exceptions
    • Add CHANGELOG.md
    • New interface ClickHouseException
    Source code(tar.gz)
    Source code(zip)
  • 1.1.2(Jul 4, 2018)

    Bindings wrong work - fix

    $keys=[
                'A'=>'{B}',
                'B'=>':C',
                'C'=>123,
                'Z'=>[':C',':B',':C']
            ];
    $this->client->selectAsync('{A} :Z', $keys)->sql() // ==   "123 ':C',':B',':C' FORMAT JSON",
    
    Source code(tar.gz)
    Source code(zip)
  • 1.1.1(Jul 3, 2018)

    Bindings wrong work - fix

    $keys=[
                'A'=>'{B}',
                'B'=>':C',
                'C'=>123,
                'Z'=>[':C',':B',':C']
            ];
    $this->client->selectAsync('{A} :Z', $keys)->sql() // ==   "123 ':C',':B',':C' FORMAT JSON",
    
    Source code(tar.gz)
    Source code(zip)
  • 1.1.0(Jul 2, 2018)

    2018-07-02 [Release 1.1.0]

    New:

    • $client->getServerUptime() Returns the server's uptime in seconds.
    • $client->getServerSystemSettings() Read system.settings table and return array
    • $client->streamWrite() function
    • $client->streamRead() function

    Warning:

    • Now default enableHttpCompression set true
    • Deprecated StreamInsert class

    Fix:

    • Fix rawData() result in JSONCompact & JSONEachRow format
    • Fix Statement - unnecessary memory usage
    • Fix support php5.6
    Source code(tar.gz)
    Source code(zip)
  • 1.0.1(Jun 29, 2018)

  • 1.0.0(Jun 25, 2018)

  • 0.18.06.19(Jun 19, 2018)

  • 0.18.06.18(Jun 18, 2018)

  • 0.18.06.17(Jun 17, 2018)

  • 0.18.05.09(May 10, 2018)

    0.18.05.09

    WARN!!!

    !Move class in namespace! !Check your code!

    Move \ClickHouseDB\WhereInFile to \ClickHouseDB\Query\WhereInFile
    Move \ClickHouseDB\QueryException to \ClickHouseDB\Exception\QueryException
    Move \ClickHouseDB\DatabaseException to ClickHouseDB\Exception\DatabaseException
    Move \ClickHouseDB\FormatLine to \ClickHouseDB\Quote\FormatLine
    Move \ClickHouseDB\WriteToFile to ClickHouseDB\Query\WriteToFile
    Move \Curler\Request to \ClickHouseDB\Transport\CurlerRequest
    Move \Curler\CurlerRolling to \ClickHouseDB\Transport\CurlerRolling
    
    Source code(tar.gz)
    Source code(zip)
  • 0.17.12.1(Dec 28, 2017)

Owner
SMI2
SMI2
A simple wrapper for hCaptcha

hCaptcha Wrapper Quick start Sign up at hCaptcha. Fetch your public key and site key from the settings tab. Get this package composer require pablosan

Pablo Sanches 4 Nov 19, 2021
TO DO LIST WITH LOGIN AND SIGN UP and LOGOUT using PHP and MySQL please do edit the _dbconnect.php before viewing the website.

TO-DO-LIST-WITH-LOGIN-AND-SIGN-UP TO DO LIST WITH LOGIN AND SIGN UP and LOGOUT using PHP and MySQL please do edit the _dbconnect.php before viewing th

Aniket Singh 2 Sep 28, 2021
The lightweight PHP database framework to accelerate development

The lightweight PHP database framework to accelerate development Features Lightweight - Less than 100 KB, portable with only one file Easy - Extremely

Angel Lai 4.6k Dec 28, 2022
Propel2 is an open-source high-performance Object-Relational Mapping (ORM) for modern PHP

Propel2 Propel2 is an open-source Object-Relational Mapping (ORM) for PHP. Requirements Propel uses the following Symfony Components: Config Console F

Propel 1.2k Dec 27, 2022
A data mapper implementation for your persistence model in PHP.

Atlas.Orm Atlas is a data mapper implementation for persistence models (not domain models). As such, Atlas uses the term "record" to indicate that its

null 427 Dec 30, 2022
PHP Object Model Manager for Postgresql

POMM: The PHP Object Model Manager for Postgresql Note This is the 1,x version of Pomm. This package is not maintained anymore, the stable Pomm 2.0 is

Grégoire HUBERT 161 Oct 17, 2022
[READ-ONLY] A flexible, lightweight and powerful Object-Relational Mapper for PHP, implemented using the DataMapper pattern. This repo is a split of the main code that can be found in https://github.com/cakephp/cakephp

CakePHP ORM The CakePHP ORM provides a powerful and flexible way to work with relational databases. Using a datamapper pattern the ORM allows you to m

CakePHP 146 Sep 28, 2022
PHP DataMapper, ORM

Cycle ORM Cycle is PHP DataMapper, ORM and Data Modelling engine designed to safely work in classic and daemonized PHP applications (like RoadRunner).

Cycle ORM 1.1k Jan 8, 2023
PHP Database Migrations for Everyone

Phinx: Simple PHP Database Migrations Intro Phinx makes it ridiculously easy to manage the database migrations for your PHP app. In less than 5 minute

CakePHP 4.3k Jan 7, 2023
Database management in a single PHP file

Adminer - Database management in a single PHP file Adminer Editor - Data manipulation for end-users https://www.adminer.org/ Supports: MySQL, MariaDB

Jakub Vrána 5.5k Jan 1, 2023
PHP version of mysqldump cli that comes with MySQL

MySQLDump - PHP Requirements | Installing | Getting started | API | Settings | PDO Settings | TODO | License | Credits This is a php version of mysqld

diego torres 1.1k Jan 8, 2023
MongoDB PHP library

MongoDB PHP Library This library provides a high-level abstraction around the lower-level PHP driver (mongodb extension). While the extension provides

mongodb 1.5k Dec 31, 2022
Griffin is a Graph-Oriented Migration Framework for PHP

Griffin is a generic migration framework that uses graph theory to provision anything. It plans execution based on migration dependencies and runs them in the correct order.

Griffin 14 Feb 7, 2022
A php securised login system, using Hash, Salt and prevent from SQL Injections

A Basic Secure Login Implementation Hashed & Salted password ( only hashed in ShA-512 for now ) No SQL injection possible Prevent XSS attacks from the

Yohann Boniface 1 Mar 6, 2022
Symfony 5 PHP 8 DDD CQRS Backend POC

Book Shop A POC for DDD and CQRS applications using Symfony as framework and running with php8 User Stories Come store manager voglio aggiungere/modif

Patrick Luca Fazzi 15 Sep 23, 2022
Symfony 5.2 + api platform project with ELK stack + elastic FileBeats for the log management. All running in 7 docker containers: nginx, php 8, mysql, elastic search, logstash, kibana, fileBeats.

Symfony with ELK and Elastic FileBeats Stack Prerequisites: Make sure that docker and docker-compose are installed in your machine and available for y

null 13 May 20, 2022
A php class for managing and connecting to a database

Query builder class php This class is responsible for creating and executing sql commands and helps you to execute as easily as possible and safely. I

Mohammad Qasemi 39 Dec 11, 2022
A simple PHP and MySQL based internet forum that displays the messages in classical threaded view (tree structure)

my little forum my little forum is a simple PHP and MySQL based internet forum that displays the messages in classical threaded view (tree structure).

Mark Hoschek 97 Dec 29, 2022
phpMyFAQ - Open Source FAQ web application for PHP and MySQL, PostgreSQL and other databases

phpMyFAQ 3.1 What is phpMyFAQ? phpMyFAQ is a multilingual, completely database-driven FAQ-system. It supports various databases to store all data, PHP

Thorsten Rinne 547 Dec 27, 2022