NAME

DBD::Crate - DBI driver for Crate db

SYNOPSIS

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";

DESCRIPTION

DBD::Crate is a DBI driver for Crate DB, DBD::Crate is still in early development so any feedback is much appreciated.

ABOUT CRATE

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

Methods

connect

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.

raw

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}

BLOB Methods

Crate includes support to store binary large objects (BLOBS)

crate_blob_insert

$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

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

crate_blob_get

$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

crate_blob_delete

$dbh->crate_blob_delete("blobtable", "sha1 digest");

Delete blob, returns true on success, undef on failure and set $dbh->errstr & $dbh->err

Table Info Method

my $tables = $dbh->crate_tables_list();
my $table = $dbh->crate_table_info("tablename");
my $columns = $dbh->crate_table_columns("tablename");

crate_tables_list

$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

$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'
    },
    {
        ...
    }
];

crate_table_info

$dbh->crate_table_info("tablename");

Same as crate_tables_list but returns single hash ref reult for the tablename

crate_table_columns

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
  }
]

IMPORTANT NOTES

Pimary keys

Crate doesn't auto generate primary keys, you need to provide a unique key by your self and it must be **ehem** UNIQUE :)

last inserted id

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

INSTALLATION & TEST

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

See Also

Crate.io

Crate Docs

AUTHOR

Mamod A. Mehyar, <[email protected]>

LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself