SQL 代码生成

近一两年，低代码、无代码的概念大肆其行，只要说自己是低代码或无代码平台，就能引来多数人关注。低代码也好、无代码也罢，本质是利用配置信息来预先编译、或者动态解析来提高组件或已有代码的利用率、减少重复劳动。那么，前提是程序可以被模式化、组件化、参数化。代码生成对于长期从事编程工作的技术人员来说，并不是什么新鲜事。

在 SQL 实践中，代码生成很早就被引入。SQL 本身属于动态脚本，在运行时要预先编译，这就给动态拼接 SQL 语句创造可能。后来，由于优化的需要，又引入了绑定变量技术。在信息管理管理，SQL也是由 ORM库自动生成，在 java 应用开发中广泛使用的 mybatis，通过模板技术，还支持条件模板，来生成SQL。在数据类应用或者平台中，大量的 ETL 程序是包含 SQL 的存储过程，部分存储过程是由脚本或者程序所生成。

本文谈谈这些利用模板生成代码的技术。

模板技术生成代码的原理 #

模板技术生成代码，是将目标代码文件的格式模板化与数据格式化，通过更换格式化数据结合固定模板来批量生成程序，或者通过更换模板与固定的格式化数据来生成多个结构的程序的方法。如果你开发过前端网页页面，就能理解这个过程类似于通过查询数据库得到的数据，渲染成表单和CRUD页面。本站也是使用模板构建的。

为了使用模板手段来辅助代码生成，需要满足这些要求：

1. 目标代码可以分离固定出 结构模板 与 数据
2. 代码中的数据可格式化

什么是模板 #

在 java、python 中的字符串类型对象，有这样一个函数 format，可用来做字符串格式化。

# python
>>> print("hello , {}".format("youwu.today"))
hello , youwu.today
>>>
>>> a = "youwu.today"
>>> f"hello {a}"
'hello youwu.today'

// java
String hello = String.format("Hello, %s", "youwu.today");  

// javascript
qjs > site = "youwu.today";
"youwu.today"
qjs > console.log(`hello ${site}`);
hello youwu.today

上面各例是语言上对字符串格式化的支持。定义一个字符串模板，并在模板中将可替换值定义为参数，通过输出不同的值来生成不同的结果字符串。这是字符串级别的。当然，结果字符串也可以使用拼接的形式来生成，当然也可以多行。

当字符串的生成逻辑比较复杂时，比较带条件判断、带数据迭代，那么仅用拼接和上面的 format 代入的方式就很难实现了。模板引擎应运而生。

什么是数据可格式化 #

假设某个程序中，需要用到本文的标题、最后编辑时间、文章标签这些信息，那么需要将这些信息以某种格式保存起来，模板引擎读取到后可正确解析，将渲染到模板中。

比如在 markdown中，使用 yaml 格式定义 front matter 可以写成：

title: "SQL 代码生成"
lastmod: 2022-06-25T12:07:19+08:00
tags: [SQL 实践, 有悟方法, 代码生成]

编写程序时，使用配置文件也是一种保存程序数据的手段。不过当数据较多时，配置文件就不够用了，因为它通常只是 key=value的形式保存了系统的某些参数。

又如，我们需要在程序中使用到表 T，也它的10字段的名称 c1,…,c10。 数据以 json 格式化为:

{
	"t1": ["c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "c9", "c10",]
}

也可使用 csv 的格式：

t1, c1
t1, c2
t1, c3
t1, c4
t1, c5
t1, c6
t1, c7
t1, c8
t1, c9
t1, c10

数据可格式化的目标在于，模板引擎可识别这些需要渲染到模板中插入到变量数据的值。

模板引擎介绍 #

模板本身是一个语言解析器（已经涉及编译原理使用），几乎每一种编程语言都第三方模板库，只要它被用来用于 web 应用。golang 甚至在标准库中包含了 text/template 模板库。

不用担心 😓，我们学习模板技术不是为了再造一个轮子。

模板库的功能均差不多：

1. 模板是文本形式存在
2. 模板嵌套
3. 在模板文本中的任意位置可添加片段，这个片段以外的文本字符不做处理
4. 使用上下文传递变量
5. 支持条件语句
6. 支持循环迭代

一个使用模板例子 #

python: 3.9.13
jinja2: 3.1.2

STEP1. 创建虚拟环境，并安装 jinja 库。

> python -m venv codegen
> source codegen/bin/activate
> pip install jinja2

STEP2. 编写模板

-- template.sql
create table {{ table_name }}
(
    {% for col in columns %}
    {%- if not loop.first -%}, {% endif -%}
    {{- col.name }} {{ col.datatype }}
    {% endfor %}
);

模板语言参考 jinja2 文档 ，本例使用了 {% for %}{% endfor %} 循环块，loop 迭代变量，上下文变量传参 {{ 变量名 }}，{% if %}{% end %} 条件语句块。

在语句块以外的符号，全部被保留。这种方式相比与字符串硬接，是不是更具维护性呢？当然，若仅仅是一两行的模板，还是用编程语言中的字符串模板为好，因为足够简单。

STEP3. 编写 python 脚本

# # codegen.py
from jinja2 import Environment, FileSystemLoader, select_autoescape
env = Environment(
    loader=FileSystemLoader([".", "./templates"]),
    autoescape=select_autoescape()
)


table_name = "hello"
columns = [
    {"name": "id", "datatype": "int"},
    {"name": "col2", "datatype": "varchar(20)"},
    {"name": "col3", "datatype": "int"},
]


template = env.get_template("template.sql")

sql = template.render(table_name=table_name, columns=columns)

print(sql)

FileSystemLoader 是 jinja2 的加载器，它负责在本地目录下寻找模板文件。它还有其它策略的加载器。脚本过程的核心先创建 jinja2 环境，创建模板对象，将变量值（数据）作为上下文传递给模板并渲染模板。

template = env.get_template("template.sql")

sql = template.render(table_name=table_name, columns=columns)

模板渲染后的结果可以保存在局部变量中，也可以打印到标准终端、保存到本地文件等等，视具体需要而定。

STEP4. 运行 python 脚本得到SQL

❯ python codegen.py
create table hello
(
    id int
    , col2 varchar(20)
    , col3 int

);

⚠️ 开卷：

1. 你的工作中，有哪些交付物是可以通过模板技术来辅助实现的？

较有名的模板引擎 #

SQL 代码生成的场景 #

前面通过一个 python3 脚本的例子，演示了如何利用模板生成创建表结构的 SQL 语句。但在该例中，并没有展示如何进行准备数据。

一般地，根据模板引擎的两个要素，模板、数据来准备即可。

根据目标代码的格式与模板引擎的语句来定制模板；而数据则非常灵活易变。有些数据维护在 excel，有些数据被保存在数据库中。还是需要根据实际情况下定制数据的读取器，只要在模板渲染前可获取到，并解析成模板中引用的数据对象形式或结构，那么数据被按什么方式存储就不重要了。

回到 《ETL 程序模式》 ，通常 etl 单元程序的 源->目标 之间的映射维护在可读性比较强的电子表格中，正如该文中所说，ETL 程序的代码生成，只是为了辅助开发，不是全替代。有些复杂的映射关系使用二维表格表示可读性非常差，也很难格式化。除此之外，某一对一、二对一这种简单关系映射，其映射文档非常整齐，这时使用模板来生成代码既高效，又准确，特别是在当出现字段变更时，使用代码生成器重新生成，可以保证无须过多测试就能得到正确代码。用过的都知道有多香。

当前，在数据开发领域，模板技术辅助代码生成的主要被用来大批量生成建表语句、部分关系简单的ETL程序过程，大大减少手工编写所需的时间。如数据加载、数据卸载等比较形式简单的程序，使用商业ETL程序的GUI designer 无法与这种批量生成脚本的方式比拟。生产力杠杠滴。

最后的建议 #

模板引擎有很多实现版本，但基本功能均错不太多。主要还是看团队中使用的技术堆栈，再选择该编程栈中较热门的模板引擎。注意，我们使用模板引擎是为了辅助代码生成，不为提供在线实时服务，因此引擎的运行效率不是选型的要点。

在你的项目里面使用代码生成吧，去别人面前炫耀、故弄玄虚、让领导表扬表扬。普通的数据开发、特别是做数据可视化的技术人员，并没有方面的知识，平常编程少，也很难将 ETL程序开发联系到代码生成。

不要尝试设计一套标准的录入文档格式，特别是 etl 程序的映射关系，本来它的目的是便于阅读，并非程序代码生成，两者的要求与严谨程序并不相同。有些映射关系非常难以在二维表格中表示，只解决大部分问题即可。

多留意，大批量的重复格式化内容出现，意味很大容易可以通过模板的方式来辅助生成。