# Embedded Database Logic

## 简介

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

![](/files/-LbarEQ8Ak_Ro8LtVtPs)

这种做法有两个坏处：

* 多个 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，如下图所示：

![](/files/-LcsaQitOCLaGW8u6Di8)

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

![](/files/-LcsjLIzJULxiV8EaYgg)

创建 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)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://zhenghe.gitbook.io/open-courses/cmu-15-445-645-database-systems/embedded-database-logic.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.