Migrating from Sakila-MySQL to Couchbase - Part 3: Stored Procedures
This is part 3 of migrating from Sakila to Couchbase series. Today, we will cover stored procedures that we can implement as 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.
Opinions expressed by DZone contributors are their own.