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

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

Stored procedures are a set of SQL statements stored together under a given name so as to be reused by multiple queries. Stored procedures by themselves are not supported in N1QL. (PROCEDURAL SQL - proc: BEGIN END LEAVE PROC) but as a workaround, we can implement them as functions. 

One thing to note is that since we don't have temp table support, creating temp tables is hard to map. However, we can create a temporary collection and use that or create a temp bucket that can house multiple temp tables mapped as collections.

Let's quickly look at some examples that convert a SQL stored procedure into N1QL UDFs: 

rewards_report 

The rewards_report stored procedure generates a customizable list of the top customers for the previous month. 

rewards_report(min_monthly_purchases, min_dollar_amt_purchased)  

This returns count_rewardees and needs to be broken down into several steps when translating to N1QL. 

Step 1 - Date Manipulation 

last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) 

last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),
                                      '-',
                                      MONTH(last_month_start),'-01')
                               ,'%Y-%m-%d');

It becomes the following when translated to N1QL:

DATE_FORMAT_STR( DATE_TRUNC_STR( DATE_ADD_STR( 
                                              CLOCK_STR(),-1,"month")
                               ,'month'),
                 '1111-11-11');

last_month_end = LAST_DAY(last_month_start);

select DATE_ADD_STR(DATE_ADD_STR('2021-02-01',1,'month'), -1,'day');

Step 2 - The next step is to manually create a collection tmpCustomer - customer_id under Sakila bucket, new scope metad, and then do the following:

INSERT INTO Sakila.metad.tmpCustomer (KEY p.customer_id, Value p.customer_id)

SELECT p 
FROM Sakila._default.payment as p 
WHERE p.payment_date BETWEEN $last_month_start AND $last_month_end
GROUP BY p.customer_id
HAVING SUM(p.amount) > min_dollar_amount_purchased
AND COUNT(customer_id) > min_monthly_purchases;

SELECT COUNT(*) as count_rewardees FROM Sakila.metad.tmpCustomer;

SELECT c.* 
FROM Sakila.metad.tmpCustomer as t 
INNER JOIN Sakila._default.customer as c 
ON t.customer_id = c.customer_id; 

Step 3 - Drop collection TMP customer and then drop scope metad. 

This can be done using the UI itself. 

Film_in_stock Stored Procedure

The film_in_stock stored procedure determines whether any copies of a given film are in stock at a given store.

CREATE FUNCTION film_stock(p_film_id,p_store_id) 
{(SELECT RAW inventory_id
     FROM sakila._default.inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND inventory_in_stock(inventory_id))
 };

CREATE FUNCTION film_count(p_film_if,p_store_id) 
{(SELECT RAW COUNT(*)
     FROM sakila._default.inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND inventory_in_stock(inventory_id))[0]
};

CREATE FUNCTION film_in_stock(p_film_id,p_store_id,countval) 
{ (select RAW case when countval 
                   then film_count(p_film_id,p_store_id) 
                   else film_stock(p_film_id,p_store_id) end)
};

Film_not_in_stock Stored Procedure 

The film_not_in_stock stored procedure determines whether there are any copies of a given film not in stock (rented out) at a given store.

CREATE FUNCTION film_not_in_stock(p_film_id,p_store_id) 
{(SELECT RAW inventory_id
     FROM sakila._default.inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT inventory_in_stock(inventory_id))
};

CREATE FUNCTION film_count_not_in(p_film_if,p_store_id) 
{(SELECT RAW COUNT(*)
     FROM sakila._default.inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT  inventory_in_stock(inventory_id))[0]
};

CREATE FUNCTION film_not_in_stock(p_film_id,p_store_id,countval) 
{ (select RAW case when countval 
                   then film_count_not_in(p_film_id,p_store_id) 
                   else film_not_in_stock(p_film_id,p_store_id) end)
};
                   

With the addition of Javascript UDFs in the upcoming release, the mapping of SQL functions and procedures should become easier. In Part 4 we will cover mapping triggers to eventing functions.