Migrating from Sakila-MySQL to Couchbase - Part 4: Triggers

Read Part 1: Migrating From Sakila-MySQL to Couchbase - ETL

Read Part 2: Migrating from Sakila-MySQL to Couchbase - Part 2: Views and User-Defined Functions

Read Part 3: Migrating from Sakila-MySQL to Couchbase - Part 3: Stored Procedures

Note: For the source of the examples from Sakila MySQL DB, please refer to this. 

In SQL, a trigger is a database object which fires when an event (usually a change such as an insert, update or delete) occurs in a database. 

The Couchbase Eventing Service is a framework to operate on changes to data in real-time. Events are changes to data in the Couchbase cluster triggered by inserts, updates, and deletes. In couchbase both insert and update events are clubbed into the OnUpdate function call. 

Now let's look at a couple of examples and map triggers in MySQL to Couchbase eventing functions. 

Insert and Update Triggers and Eventing Functions

In order to insert and update values in a particular table, in mysql they have created 2 triggers - an insert trigger and an update trigger. In the insert trigger, the old values are taken and these same values are inserted into the new table. For the update trigger, for updates on every row in the film_old table, the fim_new table is updated with the same changed values. 

Insert and update triggers are actually grouped into one eventing function in couchbase called OnUpdate which works more like an upsert action (on update and on insert). This single-function maps to 2 separate triggers in mysql as seen in the example.

SQL Trigger for Sakila

DELIMITER ;; 
CREATE TRIGGER `insert_duplicate`   
AFTER INSERT ON `film_old`   
FOR EACH ROW BEGIN     
     INSERT INTO film_new (film_id, title, description)         
     VALUES (new.film_id, new.title, new.description);   
END;;    
     
CREATE TRIGGER `upd_duplicate` 
AFTER UPDATE ON `film_old` 
FOR EACH ROW BEGIN     
     IF (old.title != new.title) 
     OR (old.description != new.description)      
     OR (old.film_id != new.film_id)     
     THEN         
        UPDATE film_new             
        SET title=new.title,                 
        description=new.description,                 
        film_id=new.film_id         
        WHERE film_id=old.film_id;     
     END IF;   
END;;

Couchbase Eventing Functions

Create 2 bucket aliases for src_bucket as read-only and dst_bucket1 as read and write. 

The OnUpdate function either inserts or updates the values for each document depending on if the values already exist or not, from the source bucket to the destination bucket,  using the upsert operation. 

function OnUpdate(doc, meta) {
    log("Doc created/updated", meta.id);
    try{
        var dst_doc = {};
        dst_doc["film_id"] = doc["film_id"];
        dst_doc["title"] = doc["title"];
        dst_doc[“description”] = doc[“description”];
        var result1= couchbase.upsert(dst_bucket,meta,dst_doc);
        log(result1);
    }catch(e){
        log("error:",e);
    }
}

Delete Triggers and Eventing Functions

The delete_duplicate trigger duplicates all DELETE operations on the film_new after those corresponding rows are deleted on the film_old table.  Similarly in Couchbase eventing, as the document is deleted in the source bucket, the eventing function is triggered for the destination bucket. 

SQL Trigger for Sakila

CREATE TRIGGER `delete_duplicate` 
AFTER DELETE ON `film_old` 
FOR EACH ROW BEGIN
    DELETE 
    FROM film_new 
    WHERE film_id = old.film_id;
END;;

Couchbase Eventing Functions

function OnDelete(){
    log("Doc deleted", meta.id);
    try {
            var this_film_id = meta.id;   
            var del = delete from `film_text` where film_id =
                     TONUMBER($this_film_id);
            del.execQuery();
            log('Deleted entry: ', this_film_id);
    }catch(e){
        log("error:",e);
    }
}

Date Change Triggers

There are 3 date change triggers in the MySQL Sakila examples. The customer_create_date trigger, the rental_date trigger, and the payment_date trigger. All of these set a create date column with the current time, date, and timestamp-based on when the row was inserted. Let's look at one of them as an example here. 

SQL Trigger for Sakila

CREATE TRIGGER `customer_create`
 AFTER INSERT ON `customer`
 FOR EACH ROW BEGIN
    SET new.create_date = NOW();
  END;;

In couchbase we create a new Date() using the new Date() function. 

Couchbase Eventing Functions

function OnUpdate(doc, meta) {
    log("Doc created/updated", meta.id);
    try {
        src_bucket["create_date"] = new Date();
    } catch(e) {
        log("Error: ",e);
    }
}

As we can see, moving from a relational database to Couchbase is quite easy given the various services Couchbase offers - N1QL, Indexing, Eventing, and Import/Export. 

This marks the end of the 4 part series that discusses migrating from Sakila DB in MySQL to Couchbase.