Some projects at work use a Crate database
as kind of caching layer for aggregated, unnormalized data that
helps reducing requests from frontend servers.
Because of its eventual consistency
model, filling data into it
has some issues.
Eventual consistency means that at some point in the future - not now - data will be consistent. You write into Crate, but it is not guranteed that the next read will contain the data written earlier, as described in its documentation.
The following PHP code uses Laravel's Eloquent with the Crate adapter, and is pretty standard in the sloppy and inefficient world of nice-to-look-at ORMs:
$obj = Model::firstOrCreate(['id' => $id]); $obj->fill($transformer->transform($data)); $obj->save();
That code handles both fresh inserts and updates in only three lines, which is nice. But it makes three SQL queries for new data:
- SELECT for checking if the row exists
- INSERT to create the row (saving the ID only)
- UPDATE to actually write the new data
With Crate, the UPDATE will fail because the row inserted in step 2 is not available in step 3.
Eloquent's firstOrCreate method fortunately supports a second parameter that may contain additional data to be inserted:
$newdata = $transformer->transform($data); $obj = Model::firstOrCreate(['id' => $id], $newdata); $obj->fill($newdata); $obj->save();
This code works with Crate, even if it is terrible inefficient - when inserting a new row, it sends the same data again in the UPDATE.
Eventual consistency
will also bite you when using TRUNCATE
and then immediately inserting data.
Create has a
REFRESH TABLE
command that ought to support you with such cases, but in Crate 1.0.1
we had issues with that, and had to resort to a sleep(1) call in
our import script :/