Last time we left with a very raw, straight forward import of SQL tables in Couchbase, with one document per table row. But there is still work to do. Primary keys have changed in the process so we need to fix that. And a SQL database does not contain just tables. There are other structures and functions that carry business logic and that we will have to move to the application layer.
The first thing I want to do after import is run JOIN on documents, which can be done the same way with a SQL database because it’s essentially an exact mapping. It’s not as simple as running the N1QL query directly. JOIN in N1QL only works on a document’s key, and we changed them at import to avoid collision. So we need to change the different foreign keys. The good news is that it’s really easy to do with N1QL Data Manipulation Language. There is support for UPDATE, MERGE, INSERT or UPSERT.
cbq> UPDATE default d SET language_id = "language::" || TOSTRING(d.language_id) WHERE _tableName = "film";
This query will change the field language_id of every document in the bucket default that contains a _tableName field set to “film”. The language_id will go from being a Number type field to a String. If you had 1, you now have “language::1”. This will correspond to the actual key of the document, because that’s what was added by the RowMapper.
The ‘||’ operator is used for String concatenation. And since language_id was a Number, you need to use the toString method to convert it. You’ll need to run this query for every foreign key in your database. After that you should be able to run N1QL queries with JOIN, NEST, and UNNEST.
cbq> SELECT * FROM `default` AS a JOIN `default` AS b ON KEYS a.language_id;
cbq> SELECT * FROM `default` AS a NEST `default` AS b ON KEYS a.language_id;
Sequence, View, Trigger, Domain, and Function
There are some things you can’t really migrate directly from a SQL database to Couchbase. More specifically, all the business logic you had on your SQL database will have to move to your application layer. Some are more straightfoward to move than others. You can decide if having business logic in the data layer is a good thing or not. While I don’t personally like it, I understand that many different apps can use the same database and so constraint expressed on the db level can be a good thing. I prefer to think those apps should rely on a service instead of getting straight to the database.
A sequence can be seen as an Atomic Counter, and is something straightforward to create:
JsonLongDocument rv = bucket.counter(key, 20, 100);
LOGGER.info("Delta=20, Initial=100. Current value is: " + rv.content());
rv = bucket.counter(key, 1);
LOGGER.info("Delta=1. Current value is: " + rv.content());
A SQL view can be seen as a dynamic table resulting from a SQL query. In Couchbase, a View is an index (think of an index as a two column table) created dynamically using an incremental map/reduce function, and mostly depends on the complexity of your View. A view is used to present data in a different context. In NoSQL, when you want to do that you start denormalizing your data, which does duplicate it. So be careful about what you are doing. If this data needs to be modified often, denormalizing it might not be a good idea (although things might change with the SubDocument API we are working on). Here's an example of View togive you an idea:
A SQL domain is a new type (based on an existing one) with integrated constraints. In my SQL sample, a new domain called type is defined like this:
CREATE DOMAIN public.year
CONSTRAINT year_check CHECK (VALUE >= 1901 AND VALUE <= 2155);
ALTER DOMAIN public.year
OWNER TO postgres;
It defines a new type called year from the existing integer type. It adds a constraint saying the value of year should be between 1901 and 2155. To express this constraint in Java, you can use a validation framework like Hibernate Validator. Your year type would look like this:
private int year;
Triggers are SQL functions executed when something particular happens in the database, like a specific INSERT, DELETE, or UPDATE. It’s a great way to enforce the integrity of your data or automating operations. There is no equivalent on Couchbase Server, so this will have to move to your application layer. For example, you can reproduce this using an application event bus. This could probably done using Couchbase's DCP
Most SQL databases allow you to define your own functions. This is not currently possible with N1QL. All the logic you put in that function should be put in your application layer.
You should now have a decent idea on how to migrate simply from SQL databases to Couchbase. I can't stress enough how this is the simple path to migration. There is no data modeling involved here. A full migration would involve taking a good look at your data model and see what you can denormalize.
Let us know what you think in the coments bellow. I would be happy to know if for instance you already did an SQL migration and how you did it 🙂