This is an Eloquent and Query builder support for Crate.io. Extends the original Laravel API with Crate PDO driver.
Crate is a distributed SQL Database based on Elasticsearch, Lucene and other goodies. See their official page on Crate.io for more info.
Crate.io published a PDO and DBAL driver for easy access to the crate DB server. Laravel-crate.io project uses those adapters when connecting to Crate DB.
!!!! WARNING: There's a breaking change in version 11. See changelog below.
Laravel-crate.io is used in our internal projects. We did a bunch of unit tests and the driver seems ok. We use id as a caching layer in front of our DB servers. Crate is insanely fast (Elasticsearch) and offloads our DB servers a lot.
if you find any bugs, please open an issue ticket.
Laravel 5.5, 6, 7, 8, 9 and 10 are supported.
Add a require to your composer.json :
composer require ratkor/laravel-crate.io[!NOTE] Allways use tagged version in composer.json. Latest (master) version of laravel-crate.io works only with latest laravel. Latest laravel has a bunch of different function parameters in query parsing and you'll get parse errors if you'll try to use master branch with older laravel.
Laravel 5.5+ uses Package Auto-Discovery, so doesn't require you to manually add the ServiceProvider.
You'll have to install crate.io server, of course. See installation instructions on their site.
Version 12 supports PHP 8.1.
Version 10.1 supports PHP 8.
We run tests on ubuntu with PHP 8.0, crate-dbal 3 and phpunit 9.5. All tests passed ok.
Versions tagged with 9 and higher require php 7.2.5!
Open config/database.php and add new crate database connection (with
config values to match your setup):
'crate' => array(
'driver' => 'crate',
'host' => 'localhost',
'database' => 'doc',
'port' => 4200,
),Next, change default database connection to "crate".
'default' => 'crate',This driver supports standard CrateDB auth methods trust and password via
HTTP Basic Auth. To enable this, edit the crate config you created in the
previous step and add username and password with appropriate values for your system.
For more information on CrateDB auth methods, see CrateDB's documentation about Authentication Methods.
'crate' => array(
'driver' => 'crate',
'host' => 'localhost',
'database' => 'doc',
'port' => 4200,
'username' => 'MyUsername',
'password' => 'MyPassword',
),This driver can handle conenctions to multiple crate hosts. To use them, write
host config parameter as a comma delimited list of hosts. Like:
'crate' => array(
'driver' => 'crate',
'host' => 'localhost,10.0.0.1,10.0.0.2',
'database' => 'doc',
'port' => 4200,
),The DSN created in this case looks like:
'crate:localhost:4200,10.0.0.1:4200,10.0.0.2:4200'If you need to specify different ports, add them to the host param like:
'host' => 'localhost:4201,10.0.0.1:4300,10.0.0.2',which will create a DSN like:
'crate:localhost:4201,10.0.0.1:4300,10.0.0.2:4200'Randomization
crate-pdo takes the first host
from list of hosts. To overcome this we randomize all hosts
so that connections to multiple crate servers are distributed. If you don't want
randomization, add a randomHosts parameter and set it to false:
'crate' => array(
'driver' => 'crate',
'host' => 'localhost,10.0.0.1,10.0.0.2',
'database' => 'doc',
'port' => 4200,
'randomHosts' => false,
),Crate.io supports many of the SQL statements, but not all of them. Be sure to take a look at their site if you're in doubt.
We're throwing an RatkoR\Crate\NotImplementedException for those statements that you
might wrongly try to use. We tried to cover all of them, but if we missed any
you'll get Exception from Crate DB.
Big things that are not supported are:
Crate specific stuff that were added are:
Also, Article::truncate() has been changed to silently use delete from article;
Note, that Crate.io does not support uppercase letters in table or schema names. See this and other restrictions here.
Migration and schema are supported. You can use artisan migrate commands
to create or drop tables.
Crate has only a subset of field types (and some new ones), so choose appropriate.
Crate types:
Some SQL types are silently linked to crate types. For example, bigInteger is
linked to long, text, mediumtext, longtext, enum are linked to string, ...
An example of schema in migration file would be:
Schema::create('article', function(Blueprint $table)
{
$table->integer('id');
$table->string('title')->index('plain');
$table->mediumText('summary');
$table->text('internal_Comment')->index('off');
$table->text('body')->index('fulltext:english');
$table->bigInteger('nb_views');
$table->timestamp('published_on');
$table->arrayField('images','object as (id integer, title string');
$table->objectField('author','(dynamic) as (id integer, name string)');
$table->timestamps();
$table->primary('id');
});Creating (and dropping) blob tables is also supported. Blob tables don't have arbitrary colums, just digest and last_moified. And even these are created automatically.
An example of create blob schema is:
Schema::createBlob('myblob');There is no need for the callback parameter (the second parameter in createBlob() which defines fields). If you pass it it will be silently ignored.
To drop a table in schema do:
Schema::dropBlob('myblob');Fulltext index on a single field can be added as:
$table->index('field1','fulltext');or
$table->string('field1')->index('fulltext');Fulltext index on multiple fields:
$table->index(['field1','field2'],'fulltext');Fulltext index with english analyzer on multiple fields:
$table->index(['field1','field2'],'fulltext:english');Primary key on single field:
$table->primary('field1');Primary key on multiple fields:
$table->primary(['f_id','f2_id']);To not include a field in default index
$table->string('not_important_field')->index('off');A PLAIN index (the default index)
$table->string('field')->index('plain');or
$table->string('field')->index();or just leave it out, crate will index it.
To drop a table in migration scripts:
Schema::drop('article');To add an 'object' field use:
$table->objectField('field_name', 'object parameters');where object parameters can be any parameters that crate excepts for an object.
See their documentation
for objects. Examples would be:
$table->objectField('my_object_1','as (f_date timestamp)');
$table->objectField('my_object_2','(dynamic) as (name string, birthday timestamp)');Add an 'array' field:
Arrays are added with ->arrayField('name', 'array parameters'). As is with
object type, array paramters can have any property that crate allows
for arrays. See their documentation.
Examples for array of dynamic objects:
$table->arrayField('f_array','object as (age integer, name string');With crate DB connection, you can do simple and even more complex queries. Some examples are:
$articles = DB::select('select * from article where id = ?', array(1));
$user = DB::table('user')->where('email','some@example.com')->first();
$users = DB::table('user')->get();To use Eloquent you'll need to use Crate Eloquent model.
use RatkoR\Crate\Eloquent\Model AS Eloquent;
class Article extends Eloquent {}You can use (almost) all eloquent goodies as with the original eloquent model.
To use different table name, use:
protected $table = 'myArticles';etc...
Instead of adding
use RatkoR\Crate\Eloquent\Model AS Eloquent;to all your eloquent classes, you can add an alias to alias array in config/app.php:
'CrateEloquent' => 'RatkoR\Crate\Eloquent\Model'This will allow you to shorten the class definition to:
use CrateEloquent;
class Article extends CrateEloquent {}It can be used mostly the same as an original Laravel eloquent model.
$articles = Article::all();$article = Article::find(1);$articles = Article::where('name','LIKE','Star%')->where('views','>',100)->get();$articles = Article::where('name','LIKE','Star%')->take(10)->get();$articles = Article::whereIn('id',[1,2,3])->get();$article = Article::select('id','name')->where('id',1)->first();$nb = Article::where('views','>',100)->count();$articles = Article::where('id','=',3)->orWhere(function($query)
{
$query->where('title', 'Star Wars 7')
->orWhere('title', 'none');
})->get();etc...
$new = Article::create([
'id' => 1, // don't forget, there is no auto increment
'title' => 'John Doe and friends',
'summary' => '...',
'array_of_strings' => ['one', 'two'],
'object_field' => ['author' => 'Someone', 'title' => 'Editpr']
]);$article = Article::find(1);
$article->title = 'Brand new title';
$article->array_of_strings = ['tree', 'four'];
$article->object_field = ['author' => 'Someone Else', 'title' => 'Administrator'];
$article->save();Note: when you update array or object field, whatever is in that field will be replaced with whatever you give. You cannot append or change just one value.
$article->object_field = ['crated_by' => 'Third Person'];would not append 'created_by' field to the fields that are already existing, but would overwrite and leave only 'created_by' value in 'object_field'. To fix this, do an update like:
$newValues = $article->object_field;
$newValues['created_by'] = 'Third Person';
$article->object_field = $newValues;$article = Article::find(1);
$article->delete();Support for laravel 10.
Support for laravel 9 (thanks to Julian Martin)
BREAKING CHANGE
Version 11 changes fetch method from PDO::FETCH_ASSOC to laravel's default
PDO::FETCH_OBJ.
PHP 8 support added. All existing tests pass when run on machine with php 8 installed. No changes were needed in code (only composer.json was updated).
Updated project to work with laravel 8.
Note: crate-pdo now allows saving of objects into string fields.
If you have a User object with name field set as string and if you do:
$foo = new stdClass();
$foo->bar = 'test';
User::create(['id'=>1,'name'=> $foo]);it will do an insert with 'name' field set to object {"bar": "test"}. I don't know if it's a feature or a bug.. I'll leave it to the developers to use it or not.
Connection::recordsHaveBeenModified() is now properly called for all laravels that have it defined.
Support for table patitioning and generated tables (pull #34). Updated crate-dbal to version 2.0 (pull #33).
Thanks to Julian Martin this project now works with laravel 6.X. PHP 7.2 is now mandatory (it is mandatory for Laravel 6 also).
Updated project to work with laravel 5.8 No new functionalities, only fixes compatibility issues with changes in L 5.8.
Reworked test cases to work with phpunit 8 and 9.
Updated project to work with laravel 5.7 No new functionalities, only fixes compatibility issues with changes in L 5.7.
Updated project to work with laravel 5.6
Updated project to work with laravel 5.5. The new 5.0 version works
only with laravel 5.5.X.
Updated and synced with laravel 5.4. The runQueryCallback method is
different in 5.4 as it was in 5.3. So you'll have to use 4.0 with laravel
5.4.
This is an internal change, you work with laravel and crate as you did before. It's just a notice that 4.0 is not compatible with <5.3 laravels.
Version 3.1. brings custom QueryException. This means that in case of SQL errors you'll be able to see SQL and it's parameters even if some of them are objects or arrays.
$foo = new stdClass();
$foo->bar = 'test';
User::create(['id'=>1,'name'=> $foo,'email'=>'user1@example.com']);Throws:
QueryException:
SQLActionException [Validation failed for name: cannot cast {bar=test} to string] (SQL: insert into users (id, name, email) values (1, {"bar":"test"}, "user1@example.com"))Connection to crate can take table prefix.
Add prefix key to your crate configuration to use it.
'crate' => array(
'driver' => 'crate',
'host' => 'localhost',
'database' => 'doc',
'port' => 4200,
'prefix' => 'sys',
),See commit and official crate docs
There are two kinds of tests:
Lexical tests check if SQL statements produced by Query builder are semantically correct.
These tests are executed relatively fast. They check that all common SQLs are unaffected by code changes.
Data tests connect to real Crate.io server and try to manage data there. Selecting, inserting, updating, deleting queries, all are tested.
These tests take longer to finish. We found that queriying for a record immediatelly
after it has been inserted can produce negative results. Crate
needs some time between insert (or delete, or update) requests and
all next selects that query for this changes. So we have couple of
sleep(1) statements in test code.
Running data tests for the first time will probably fail as migration
table will not exist yet. Try rerunning test and it will proceed ok. Oh, and
you'll have to change $table->increment('id') to $table->integer('id'); in
DatabaseMigrationRepository::createRepository or it will break as
increments are not supported.
Conenction properties for tests are in tests/DataTests/Config/database.php
file and can be changed for your setup.
Data tests will create:
t_migration table for test table migrations,t_users table for some dummy user data.