Migrating From Sakila-MySQL to Couchbase, Part 5: JavaScript User-Defined Functions
Explore this comparison with Sakila DB-MySQL user-defined functions and stored procedures using the new JavaScript functions on Couchbase.
Join the DZone community and get the full member experience.
Join For FreeWith the release of 7.1, Couchbase N1QL adds support for JavaScript user-defined functions. Originally as seen in Part2: Views and UDFs and Part3: Stored Procedures, we used N1QL UDFs to map the MySQL UDFs and stored procedures. With N1QL UDFs, we can do only a single query or operation per function. This meant our translation to UDFs in Couchbase was a bit cumbersome. However, with the addition of the JS UDFs, mapping an SQL function as is becomes much simpler.
Now let's look at the individual functions and stored procedures and their direct mappings.
Get Customer Balance
This returns the total amount owed at the current time for a specified customer's account (refer to Part 2 of this series linked above for the MySQL function).
function get_customer_balance(p_customer_id,p_effective_date) {
var q1 = SELECT RAW ROUND(IFMISSINGORNULL(SUM(film.rental_rate),0),2) FROM sakila._default.film as film JOIN sakila._default.inventory as inventory ON film.film_id = inventory.film_id JOIN sakila._default.rental as rental ON inventory.inventory_id = rental.inventory_id WHERE MILLIS(rental.rental_date) <= MILLIS($p_effective_date) AND rental.customer_id = $p_customer_id;
let res4rentfees = [];
for (const row of q1) {
res4rentfees.push(row);
}
q1.close();
var q2 = SELECT RAW IFMISSINGORNULL(SUM(CASE WHEN date_diff_str(rental.return_date,rental.rental_date,"day") > film.rental_duration THEN date_diff_str(rental.return_date,rental.rental_date,"day") - film.rental_duration ELSE 0 END),0) from sakila._default.rental as rental join sakila._default.inventory as inventory ON inventory.inventory_id = rental.inventory_id JOIN sakila._default.film as film ON film.film_id = inventory.film_id WHERE MILLIS(rental.rental_date) <= MILLIS($p_effective_date) AND rental.customer_id = $p_customer_id;
let res4overfees = [];
for (const row of q2) {
res4overfees.push(row);
}
q2.close();
var q3 = SELECT RAW ROUND(IFMISSINGORNULL(SUM(payment.amount),0),2) FROM sakila._default.payment as payment WHERE MILLIS(payment.payment_date) <= MILLIS($p_effective_date) AND payment.customer_id = $p_customer_id;
let res4payments = [];
for (const row of q3) {
res4payments.push(row);
}
q3.close();
var finalres = res4rentfees[0] + res4overfees[0] - res4payments[0];
return finalres;
}
As you can see, instead of creating 4 functions using N1QL UDFs we have created 1 JS UDF.
Inventory Held by Customer Function
This function returns the customer id that leased a specific item from the inventory (again, refer to Part 2 of the series for the MySQL function).
function inventory_held_by_customer(p_inventory_id) {
var q = SELECT RAW IFMISSINGORNULL(customer_id,null) as v_customer_id FROM sakila._default.rental WHERE return_date IS NULL AND inventory_id=$p_inventory_id;
let res = [];
for (const row of q) {
res.push(row);
}
q.close();
return res;
}This is similar to the N1QL UDF function in that we only had to perform a single op/query within the function. In such cases, it might be more intuitive to use the N1QL UDF vs the JS UDFs.
Inventory In-Stock Function
This function tells us if a specific item is in stock or not (refer to Part 2 for MySQL function).
function inventory_in_stock(p_inventory_id) {
let v_rentals = 0;
let v_out = 0;
var q = SELECT RAW COUNT(*) from sakila._default.rental where inventory_id = $p_inventory_id;
for (const row of q) {
v_rentals = row;
break;
}
q.close();
if (v_rentals == 0) {
return true;
}
var q = SELECT RAW COUNT(rental.rental_id)
FROM sakila._default.inventory as inventory
LEFT JOIN sakila._default.rental as rental ON inventory.inventory_id = rental.inventory_id
WHERE inventory.inventory_id = $p_inventory_id
AND rental.return_date is null;
for (const row of q) {
v_out = row;
break;
}
q.close();
if (v_out > 0) {
return 0;
}
return 1;
}Again here, since multiple queries were used to compute our expected value, using a JavaScript function gives us a more exact mapping. Also as we can see when we compare to the N1QL UDF, we use JavaScript itself to do a lot of the if-else loops instead of using the N1QL CASE WHEN/THEN statement.
Now that we have looked at the functions, let's also take a look at the stored procedures.
Film In-Stock Stored Procedure
This procedure tells us the number of film copies for a given film id are in stock at a particular store. (Refer to Part 3 of the series as linked above for the N1QL UDF and here for the MySQL procedure.)
function film_in_stock(p_film_id,p_store_id) {
var q1 = SELECT inventory_id
FROM sakila._default.inventory
WHERE film_id = $p_film_id
AND store_id = $p_store_id
AND default:inventory_in_stock(inventory_id);
let film_stock = [];
for (const row of q1) {
film_stock.push(row);
}
q1.close();
film_stock.push({"count":film_stock.length});
return film_stock;
}length function to give us the count of rows (documents) that satisfy the input constraint. With the N1QL UDF, we had to use another query to return the count.
Film Not In-Stock Stored Procedure
This procedure tells us if any film copies have been rented out at a particular store. (Refer to Part 3 of the series for the N1QL UDF and this link for MySQL procedure.)
function film_not_in_stock(p_film_id,p_store_id) {
var q1 = SELECT inventory_id
FROM sakila._default.inventory
WHERE film_id = $p_film_id
AND store_id = $p_store_id
AND NOT default:inventory_in_stock(inventory_id);
let film_no_stock = [];
for (const row of q1) {
film_no_stock.push(row);
}
q1.close();
film_no_stock.push({"count":film_no_stock.length})
return film_no_stock;
}Rewards Report Procedure
This generates a customized list of top customers for the specified month (refer to Part 3 for MySQL procedure). This is a very interesting example. (For the original stored procedure, refer here for structure, and the actual procedure here.)
The procedure creates a temp table to store customer ids that satisfy a date check based on given constraints. In the original N1QL UDF article, I mentioned that we need to manually (might I add separately on the UI) create a temporary collection to use under the same bucket and _default scope. This was outside the scope of the N1QL UDF. But with the JS UDF, we can do this operation programmatically. Also, we can use native date time support within JS to do date manipulation. As you can see in the example below, we create a collection and an index on that collection to map to a temporary table in MySQL, use that to store the results of our query, perform a JOIN on that collection to get our final list of customers, and then return the count along with the list of customers.
function rewards_report(min_monthly_purchases, min_dollar_amt_purchased) {
var last_month_start = new Date();
var last_month_end = new Date();
var count_rewardees = [];
if (min_monthly_purchases == 0) {
return 'Minimum monthly purchases parameter must be > 0';
}
if (min_dollar_amt_purchased == 0.00) {
return 'Minimum monthly dollar amount purchased parameter must be > $0.00';
}
var q1 = select RAW DATE_FORMAT_STR( DATE_TRUNC_STR( DATE_ADD_STR(
CLOCK_STR(),-1,"month")
,'month'),
'1111-11-11');
for (const row of q1) {
last_month_start = row;
}
q1.close();
var q2 = select raw DATE_ADD_STR(DATE_ADD_STR($last_month_start,1,'month'), -1,'day');
for (const row of q2) {
last_month_end = row;
}
q2.close();
//last_month_end = "2005-08-30";
//last_month_start = "2005-08-01";
var q3 = create collection sakila._default.tmpCustomer;
var start = new Date().getTime();
var end = start;
while(end < start + 100) {
end = new Date().getTime();
}
var q7 = create primary index on default:sakila._default.tmpCustomer;
while(end < start + 100) {
end = new Date().getTime();
}
var q4 = INSERT INTO default:sakila._default.tmpCustomer (KEY TO_STRING(customer_id), Value {"customer_id":customer_id})
SELECT p.customer_id FROM sakila._default.payment p WHERE p.customer_id is not missing AND
p.customer_id is not null and p.payment_date BETWEEN $last_month_start AND $last_month_end GROUP BY p.customer_id
HAVING SUM(p.amount) > $min_dollar_amt_purchased AND COUNT(p.customer_id) > $min_monthly_purchases;
var q5 = SELECT RAW COUNT(*) FROM sakila._default.tmpCustomer;
for (const row of q5) {
count_rewardees.push(row)
}
q5.close();
var q6 = SELECT * FROM default:sakila._default.tmpCustomer as t INNER JOIN sakila._default.customer as c ON t.customer_id = c.customer_id;
for (const row of q6) {
count_rewardees.push(row)
}
q6.close();
var q7 = DROP COLLECTION sakila._default.tmpCustomer;
while(end < start + 100) {
end = new Date().getTime();
}
return count_rewardees;
}With the above example conversions between MySQL functions and procedures and N1QL functions using JavaScript UDFs, we can see how easy and powerful it is to use JavaScript UDFs. More power to the developer!
Opinions expressed by DZone contributors are their own.
Comments