DBD::Crate - DBI driver for Crate db
use DBI; my $dbh = DBI->connect('dbi:Crate:' ); my $sth = $dbh->prepare( 'SELECT id, content FROM articles WHERE id > 2' ); my $res = $sth->execute; $sth->bind_columns (\my ($id, $content)); while ($sth->fetch) { print "id: $id, content: $content\n"; } print "Toatal ", $res, "\n";
DBD::Crate is a DBI driver for Crate DB, DBD::Crate is still in early development so any feedback is much appreciated.
If you haven't heard of Crate I suggest you to give it a try, it's a is a distributed data store With SQL query support, and fulltext seach based on Elasticsearch, please read this overview
use DBI; my $dbh = DBI->connect(DBI:Crate:"); #<- [localhost:4200] defaults my $dbh = DBI->connect("DBI:Crate:localhost:5000"); #<-- localhost port 5000 my $dbh = DBI->connect("DBI:Crate:", '', '', { utf8 => [0|1] });
IP:PORT address of Crate server to which you need to connect to, if not available, localhost and default port [4200] will be used.
Crate does't have builtin user permissions or ACL concept, but some times you may use it behind a proxy with user authintication
my $dsn = DBI->connect("DBI:Crate:123.99.76.3:5000", 'user', 'pass');
This will issue a request with Basic-style user:pass authintication, in the example folder you can find a plack proxy example with basic authintication, you can also read Crate post on how to set a proxy behind ngix here
DBD::Crate has a simple fail over setup with multi servers
my $dbh = DBI->connect("DBI:Crate:[localhost:4200, localhost:42001, ...]");
Since Crate DB will handle distributing the job to the best available server for you, we only implement a simple fail over process, it check if the first connection failed, we will try the next one and append the failed host to the end of the list.
Sometimes you need to get raw json data, maybe to send as jsonp response
$sth->raw;
The returned data will be of json format as the following
{"cols" : ["id","content"], "duration" : 0, "rows" : [ [1, "content"], ... ], "rowcount" : 2}
Crate includes support to store binary large objects (BLOBS)
$dbh->crate_blob_insert("blobtable", [sh1-digest], data);
crate_blob_insert accepts three arguments, blob table name, sha1 hex digest of data, and data tp store. sha1 hex digest argument is optional in which DBD::Crate will create the digest for you
crate_blob_insert
my $digest = $dbh->crate_blob_insert("blobtable", data);
crate_blob_insert returns the sha1 hex digest of the data stored on success or undef on failure and set $dbh->errstr & $dbh->err
$dbh->errstr
$dbh->err
$dbh->crate_blob_get("blobtable", "sha1 digest");
returns stored data, in blobtable with sha1 digest previously used to store data, on error returns undef, and set $dbh->errstr & $dbh->err
blobtable
sha1 digest
$dbh->crate_blob_delete("blobtable", "sha1 digest");
Delete blob, returns true on success, undef on failure and set $dbh->errstr & $dbh->err
my $tables = $dbh->crate_tables_list(); my $table = $dbh->crate_table_info("tablename"); my $columns = $dbh->crate_table_columns("tablename");
$dbh->crate_tables_list(schema);
Accepts table schema argument [optional] if not provided will fetch tables from default doc schema, to get blobs tables list, use blob schema
doc
blob
$dbh->crate_tables_list("blob");
return a list of tables under schema with information
[ { 'number_of_replicas' => '0-all', 'partitioned_by' => undef, 'blobs_path' => undef, 'schema_name' => 'doc', 'table_name' => 'articles', 'number_of_shards' => 5, 'clustered_by' => 'id' }, { ... } ];
$dbh->crate_table_info("tablename");
Same as crate_tables_list but returns single hash ref reult for the tablename
crate_tables_list
tablename
my $columns = $dbh->crate_table_columns("tablename");
returns list of table columns
[ { 'data_type' => 'string', 'column_name' => 'content', 'ordinal_position' => 1 }, { 'data_type' => 'string', 'column_name' => 'description', 'ordinal_position' => 2 }, { 'data_type' => 'integer', 'column_name' => 'id', 'ordinal_position' => 3 } ]
Crate doesn't auto generate primary keys, you need to provide a unique key by your self and it must be **ehem** UNIQUE :)
Well, there is also no way to get the last inserted id, for the obvious reason mentioned above I guess, but you can query that if you want in a new statement
To install this module, run the following commands:
perl Makefile.PL make make test make install
OR
cpan install DBD-Crate
If you want to run the complete test suite, you need to have Crate DB installed and running, then set environment variable CRATE_HOST to crate "ip:port"
on windows
$ set CRATE_HOST=127.0.0.1:4200
on linux
$ export CRATE_HOST=127.0.0.1:4200
Mamod A. Mehyar, <mamod.mehyar@gmail.com>
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself
To install DBD::Crate, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBD::Crate
CPAN shell
perl -MCPAN -e shell install DBD::Crate
For more information on module installation, please visit the detailed CPAN module installation guide.