# Embedded Database Logic

## 简介

到目前为止，我们都假设所有的业务逻辑都位于应用本身，应用通过与 DBMS 通过多次通信，来达到最终业务目的，如下图所示：

![](https://1008303647-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LMjQD5UezC9P8miypMG%2F-LbaqJU1GBvR9w5ht9Xb%2F-LbarEQ8Ak_Ro8LtVtPs%2FScreen%20Shot%202019-04-04%20at%201.40.26%20PM.jpg?alt=media\&token=2d5660eb-6901-441a-8088-aef864bd6f5c)

这种做法有两个坏处：

* 多个 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 的返回值：

```sql
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 的例子：

```sql
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，如下图所示：

![](https://1008303647-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LMjQD5UezC9P8miypMG%2F-LcsWdYDvr3mxgdSEGbH%2F-LcsaQitOCLaGW8u6Di8%2FScreen%20Shot%202019-04-20%20at%2010.35.52%20AM.jpg?alt=media\&token=d3a7cf4b-a59b-45a6-9841-0dd2d62014eb)

#### 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

举例如下：

```sql
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 表为例：

![](https://1008303647-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LMjQD5UezC9P8miypMG%2F-LcsWdYDvr3mxgdSEGbH%2F-LcsjLIzJULxiV8EaYgg%2FScreen%20Shot%202019-04-20%20at%2011.14.50%20AM.jpg?alt=media\&token=4469d37f-3476-4fdf-8dd8-3979217f483b)

创建 cs\_students View：

```sql
CREATE VIEW cs_students AS
  SELECT sid, name, login
    FROM student
   WHERE login LIKE '%@cs';
```

创建 cs\_gpa View：

```sql
CREATE VIEW cs_gpa AS
  SELECT AVG(gpa) AS avg_gpa
    FROM student
   WHERE login LIKE '%@cs';
```

### Materialized Views

View 对应的查询在 View 每次被使用时都会被执行一次，如果我们希望 View 实体化，提高查询效率，可以使用 Materialized Views，后者的数据会随着底层数据改变而被自动更新，举例如下：

```sql
CREATE MATERIALIZED VIEW cs_gpa AS
  SELECT AVG(gpa) AS avg_gpa
    FROM student
   WHERE login LIKE '%@cs';
```

### Conclusion

将应用逻辑放入 DBMS 中的各有利弊：

* Pros
  * 减少 RTT，提高效率
  * 不同应用之间实现逻辑重用
* Cons
  * 迁移性差
  * 运维成本高

### 参考

[slides](https://15445.courses.cs.cmu.edu/fall2018/slides/15-embeddedlogic.pdf)