Embedded Database Logic

简介
到目前为止，我们都假设所有的业务逻辑都位于应用本身，应用通过与 DBMS 通过多次通信，来达到最终业务目的，如下图所示：
这种做法有两个坏处：
多个 RTT，更多延迟
不同的应用无法复用查询逻辑
如果能将部分业务逻辑转移到 DBMS 中，就能够在以上两个方面得到优化。本节将介绍将业务逻辑转移到 DBMS 中的几种方法：
User-defined Functions
Stored Procedures
Triggers
Change Notifications
User-defined Types
Views
注意：将业务逻辑嵌入 DBMS 中也有坏处，比如不同版本的应用依赖于不同版本的 Stored Procedures 等，后期将增加 DBMS 的运维成本，因此这种做法也有其劣势，要具体问题具体分析。
User-Defined Functions (UDF)
UDF 允许应用开发者在 DB 自定义函数，根据返回值类型可以分为：
Scalar Functions：返回单个数值
Table Functions：返回一张数据表
UDF 函数计算的定义可以通过两种方式：
SQL Functions
External Programming Languages
SQL Functions
SQL Functions 包含一列 SQL 语句，DBMS 按顺序执行这些语句，以最后一条语句的返回值作为整个 Function 的返回值：
CREATE FUNCTION get_foo(int) RETURNS foo AS $
  SELECT * FROM foo WHERE foo.id = $1;
$ LANGUAGE SQL;
External Programming Language
一些 DBMSs 支持使用非 SQL 定义 UDF：
SQL Standard：SQL/PSM
Oracle/DBS：PL/SQL
Postgres：PL/pgSQL
MySQL/Sybase：Transact-SQL
以下是 PL/pgSQL 的例子：
CREATE OR REPLACE FUNCTION sum_foo(i int) RETURN int AS $
  DECLARE foo_rec RECORD;
  DECLARE out INT;
  BEGIN
    out := 0
    FOR foo_rec IN SELECT id FROM foo
                    WHERE id > i LOOP
      out := out + foo_rec.id;
    END LOOP;
    RETURN out;
  END;
$ LANGUAGE plpgsql;
Stored Procedures
Stored Procedure 同样允许应用开发者自定义复杂逻辑，它的主要特点是：
可以有多个输入和输出值
可以修改数据表及数据结构
通常不在 SQL 查询中调用
通常应用程序会直接调用 Stored Procedures，如下图所示：
Stored Procedures 与 UDF 的区别
抛开具体特征，从语义出发：
UDF: perform a subset of a read-only computation within a query
Stored Procedure: perform a complete computation that is independent of a query
Database Triggers
Trigger 通常被用来连接事件与 UDF：当某个 DB 事情发生时，监听相关事件的 trigger 负责调用对应的 UDF。
通常开发者需要定义：
触发的事件类型: INSERT, UPDATE, DELETE, TRUNCATE, CREATE, ALTER, DROP
事件的定义域: TABLE, DATABASE, VIEW, SYSTEM
触发的时机：
before the statement executes
after the statement executes
before each row that the statement affects
instead of the statement
举例如下：
CREATE TABLE foo (
  id INT PRIMARY KEY,
  val VARCHAR(16)
);
​
CREATE TABLE foo_audit (
  id SERIAL PRIMARY KEY,
  foo_id INT REFERENCES foo (id),
  orig_val VARCHAR,
  cdate TIMESTAMP
);
​
CREATE OR REPLACE FUNCTION log_foo_updates() RETURNS trigger AS $
  BEGIN
    IF NEW.val <> OLD.val THEN
      INSERT INTO foo_audit (foo_id, orig_val, cdate)
        VALUES(OLD.id, OLD.val, NOW());
    END IF
    RETURN NEW
  END
$ LANGUAGE plpgsql;
​
CREATE TRIGGER foo_updates BEFORE UPDATE ON foo FOR EACH ROW
  EXECUTE PROCEDURE log_foo_updates();
Change Notifications
change notification 与 trigger 的功能类似，但前者触发的是外部函数。
User-Defined Types (UDT)
尽管 DBMSs 支持所有基本的原始数据类型，但如果我们想存储组合数据类型，如 struct，该如何做？就已有的知识，我们能想到两种方法：
Attribute Splitting：即将组合数据类型单独作为一张表 (pros&cons)
Application Serialization：即将组合数据序列化 (pros&cons)
除此之外，DBMS 通常还提供额外的 API，方便用户自定义数据，即 UDT：
Oracle supports PL/SQL
DB2 supports creating types based on build-in types
MySQL/Postgres only support type definition using external languages
Views
可以将 View 理解成一张虚拟表，这张表是一个只读查询的结果集，可以被其它查询引用。通常 View 的用途包括：
简化查询语句
对某些用户选择性隐藏数据
以下面这张 student 表为例：
创建 cs_students View：
CREATE VIEW cs_students AS
  SELECT sid, name, login
    FROM student
   WHERE login LIKE '%@cs';
创建 cs_gpa View：
CREATE VIEW cs_gpa AS
  SELECT AVG(gpa) AS avg_gpa
    FROM student
   WHERE login LIKE '%@cs';
Materialized Views
View 对应的查询在 View 每次被使用时都会被执行一次，如果我们希望 View 实体化，提高查询效率，可以使用 Materialized Views，后者的数据会随着底层数据改变而被自动更新，举例如下：
CREATE MATERIALIZED VIEW cs_gpa AS
  SELECT AVG(gpa) AS avg_gpa
    FROM student
   WHERE login LIKE '%@cs';
Conclusion
将应用逻辑放入 DBMS 中的各有利弊：
Pros
减少 RTT，提高效率
不同应用之间实现逻辑重用
Cons
迁移性差
运维成本高
参考
​slides​
​

CMU 15-445/645 Database Systems - Previous

Parallel Execution

Next - CMU 15-445/645 Database Systems

Concurrency Control Theory

Last modified 3yr ago

Copy link

Outline

简介

User-Defined Functions (UDF)

Stored Procedures

Database Triggers

Change Notifications

User-Defined Types (UDT)