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.

Isha Kandaswamy

Dec. 08, 21 · Tutorial

Likes (1)

Comment

Save

4.5K Views

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.

Database sql Drops (app) Release (computing) JavaScript Convert (command) Extract, transform, load House (operating system) Workaround

Opinions expressed by DZone contributors are their own.

Related

Trending