从 Sakila-MySQL 迁移到 Couchbase，第 5 部分：JavaScript 用户定义函数

使用 Couchbase 上的新 JavaScript 函数探索与 Sakila DB-MySQL 用户定义函数和存储过程的比较。

随着 7.1 的发布，Couchbase N1QL 增加了对JavaScript 用户定义函数的支持。最初如第 2部分：视图和 UDF和第 3 部分：存储过程中所见，我们使用 N1QL UDF 来映射 MySQL UDF 和存储过程。使用 N1QL UDF，我们只能对每个函数执行一次查询或操作。这意味着我们在 Couchbase 中翻译 UDF 有点麻烦。但是，随着 JS UDF 的添加，按原样映射SQL函数变得更加简单。 

现在让我们看看各个函数和存储过程以及它们的直接映射。

获取客户余额

这将返回指定客户帐户当前所欠的总金额（请参阅上面链接的本系列的第 2 部分以了解 MySQL 函数）。

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;

}

如您所见，我们创建了 1 个 JS UDF，而不是使用 N1QL UDF 创建 4 个函数。

客户职能部门持有的库存

此函数返回customer id从库存中租用特定项目的那个（再次，请参阅 MySQL 函数系列的第 2 部分）。

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;

}

这类似于 N1QL UDF 函数，因为我们只需要在函数中执行单个操作/查询。在这种情况下，使用 N1QL UDF 与 JS UDF 相比可能更直观。

库存入库功能

这个函数告诉我们一个特定的项目是否有库存（参见第 2 部分的 MySQL 函数）。

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;

}

同样在这里，由于使用了多个查询来计算我们的预期值，因此使用 JavaScript 函数可以为我们提供更精确的映射。此外，当我们与 N1QL UDF 进行比较时，我们可以看到，我们使用 JavaScript 本身来执行很多 if-else 循环，而不是使用 N1QL CASE WHEN/THEN 语句。

现在我们已经了解了函数，让我们也看看存储过程。 

胶片库存存储程序

这个过程告诉我们给定电影 ID 的电影拷贝数在特定商店的库存中。（有关 N1QL UDF 和MySQL 过程，请参阅上面链接的系列的第 3 部分。） 

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;

}

正如您在 JS UDF 中看到的那样，我们能够准确地处理我们想要显示的数据的哪些方面。在这种情况下，我们使用 JavaScript 内置数组length函数来为我们提供满足输入约束的行数（文档）。使用 N1QL UDF，我们必须使用另一个查询来返回计数。

胶片无库存存储过程

这个程序告诉我们是否有任何电影拷贝已在特定商店出租。（有关 N1QL UDF 和MySQL 过程的链接，请参阅该系列的第 3 部分。） 

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;

}

奖励报告程序

这会生成指定月份的顶级客户的自定义列表（有关 MySQL 过程，请参阅第 3 部分）。这是一个非常有趣的例子。（对于原始存储过程，请参阅此处的结构，以及此处的实际过程。）

该过程创建一个临时表来存储满足基于给定约束的日期检查的客户 ID。在最初的 N1QL UDF 文章中，我提到我们需要手动（可能我在 UI 上单独添加）创建一个临时集合以在相同的存储桶和 _default 范围下使用。这超出了 N1QL UDF 的范围。但是使用 JS UDF，我们可以通过编程方式执行此操作。此外，我们可以在 JS 中使用原生日期时间支持来进行日期操作。正如您在下面的示例中所见，我们在该集合上创建一个集合和一个索引以映射到MySQL 中的临时表，使用它来存储我们的查询结果，对该集合执行 JOIN以获得我们的最终列表客户，然后将计数与客户列表一起返回。 

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;

}

通过上面使用 JavaScript UDF 在 MySQL 函数和过程与 N1QL 函数之间转换的示例，我们可以看到使用 JavaScript UDF 是多么容易和强大。给开发者更多的权力！

原文地址：https://dzone.com/articles/migrating-from-sakila-mysql-to-couchbase-part-5-ja

原文作者： Isha Kandaswamy

原文标题：Migrating From Sakila-MySQL to Couchbase, Part 5: JavaScript User-Defined Functions