跳到主要内容

综合与深入:初步分析 Discourse

在完成了初步的学习之后，我们已经具备了管理基本关系型数据的能力，在这篇文章中，我们将对 Discourse 的一些数据库操作行为展开分析，它是一款非常优秀的社区应用程序，世界上一些最为卓越的企业们选择使用这款程序来提供他们的服务（比如：OpenAI 公司使用 Discourse 程序创建了它们的开发者社区）。

openai

（OpenAI 开发者社区截图）

Discourse 基本架构理解​

自 Discourse 的视角而言，PostgreSQL 在 Discourse 社区服务中承担着存储数据的职责（“Our main data store is in Postgres.”），帮助社区管理着最为重要的数据，因此对 Discourse 中与 PostgreSQL 的交互行为展开分析，自然而然可以帮助我们更为深入的理解 PostgreSQL。

提示

MVC 架构简介​

作为一款流行的社区应用程序，Discourse 很大程度上借鉴了业界中非常经典的 MVC 架构，用以指导自身的工作逻辑。

mvc

在 MVC 架构中，软件的模块从整体上被划分为“Model“，”View”，“Controller” 三个层次，而作为 PostgreSQL 内核开发者的我们，更多的是和 Model 层与 Controller 层打交道，以一篇社区文章举例来说，Model 决定了文章将以怎样的数据表的形式存储于数据库中，而 Controller 层则决定在什么情况下面，网站将会要求数据库对于文章数据展开某种工作（如删除文章对应 DELETE，增加文章对应 INSERT）。

不过这种架构也会使得 SQL 查询的传递，更多地会是逐步拼接起来的一种情况（把用户的数据同系统的配置逐步结合，以达到最终结果），这就需要我们对具体的业务逻辑，工作逻辑，有一个把握与了解。

下面，我们将会挑选一些 Discourse 程序中所涉及到的场景，理解它们将会大大促进你对于 SQL 这门语言的理解。

Discourse 程序如何记录文章当中的附件？​

互联网的一项巨大进步，体现在我们发表的文章里面，不再只有简单的文字，而是可以有更多其它的内容，比如图片，比如表情，或者是我们自己上传的文档，代码，压缩包等，这些数据，在社区应用程序当中，就被统称为“附件”，并常常会被统一到某种数据表中进行管理（如果我们删除文章的时候，不删除对应的附件，无疑会造成极大的空间浪费）。

而在 Discourse 中，post_uploads 表就专门用于存储这一方面的数据，现在就让我们阅读文章附件记录时的 SQL，了解这一方面的流程与原理吧！

execute "INSERT INTO post_uploads (upload_id, post_id)
        SELECT u.id, #{post_id}
        FROM uploads u
        WHERE u.url = '#{url}'
        AND NOT EXISTS (SELECT 1 FROM post_uploads WHERE upload_id = u.id AND post_id = #{post_id})"

可以注意到，Discourse 所使用的 SQL 语句虽然也运行在 PostgreSQL 上面，但是与我们之前所见过的都有所不一样，在这里，我们展开渐进式的分析，揭晓其中的奥妙。

  • execute 是 Discourse 程序执行 PostgreSQL 具体交互的指令
    正如我们依托 psql 将我们的 SQL 查询传递给 PostgreSQL Server 一样，Discoure 将会依托某种 PostgreSQL 客户端接口程序，将某些特定的查询发送到 PostgreSQL Server 去。
  • Discourse 程序中的 SQL 是动态构造的
    我们之前所书写的插入语句，删除语句的代码，都是按照某种已知的数据来做安排的（比如要插入什么书籍，都是我们自己事先设计好的），但是在实际的情况里面，用户输入的内容往往是动态变化的，是需要具体情况具体分析的，这就需要我们采取灵活的手段构建可以随情况而变化的 SQL 语句。
    而在这里，Discourse 程序选择动态填写 （文章 id）, url（附件 url），id（附件 id）三个数据项目（原理：在传递给 PostgreSQL 之前，这三个部分的内容会被替换为具体的变量值），进而使得最后构造出来的内容，适应具体业务的需要
  • 子查询被应用到了 SQL 之中
    除了动态化与传递方式的不同以外，这一次所应用到的 SQL 更为复杂，使用了子查询（即在某条查询之中，再嵌套一条 SQL 查询），分析它们的办法也非常简单，就是从内向外分析。
    比如这里，我们就先从 SELECT 的部分开始，可以发现，它的基本目标，就是将附件 id（u.id）与文章 id （post_id）从附件表（uploads）中提取出来，而限制条件就是附件url与传递入的url匹配，并且对应的记录不存在于 post_uploads 中已经存在有的记录当中（避免重复），然后再分析上层的 INSERT，就会发现这实际上就是一个“先筛选，后插入”的过程，最后的结果就是把一定的数据插入到附件表中。
提示

子查询简介

子查询，同样也被成为内部查询或者内部选择，对应的概念是外部查询与外部选择。
它们嵌套于 SELECT, INSERT, UPDATE, DELETE 语句之中，或者是其它的子查询之中，进而对于最终的查询结果展开某种影响（如上文中的子查询有去重和自其它表中提取特定数据的作用）。
理解子查询对于书写高效的 SQL 代码具有重大意义。

Discourse 程序如何查询用户的头像？​

现在我们把目光放在另外一个场景上面，即 Discourse 如何查询用户的头像，参考如下的代码：

avatars = query(>>~SQL)
      SELECT id, avatar_upload_id
        FROM users
       WHERE avatar_upload_id IS NOT NULL
       ORDER BY id
    SQL
提示

Ruby 中的 Heredoc 简介
Discourse 实际上是在 Ruby 程序中书写了 SQL 代码，此处的 >>~SQL 和 SQL 实际上表示要求 Ruby 解释器保留字符串查询的原始特征（即不做任何修改，即 Heredoc）。
这样 PostgreSQL 最终所接收到的 SQL 代码，就是同代码中所写的一样。

言归正传，这里的 SQL 同上面的与我们之前所接触到的一样，也有了一些新的情况与新的变化：

  • IS NOT NULL 代表不希望数据为空
    在之前的文章当中，我们提及，对于那些插入数据时没有填写内容的属性，PostgreSQL 一般而言会将其写为 NULL（代表数据为空）。
    而在查询的时候，通过 WHERE 子句，我们可以书写 IS NOT NULL 这条指令，限制为空的数据进入到我们查询的最终结果之中，以此确保很多场景下软件工作的正确性（如此处，如果查询出来的是空头像，那么显示则可能不正常）。
  • ORDER BY 代表希望最终的结果根据某条属性进行排序
    SQL 查询的结果是可以排列的，具体则通过 ORDER BY 指令进行实施，通过它我们可以为软件带来很多很有意义的特性，如将文章按照时间顺序从新向旧排列，将文章按照阅读量由大到小排序。

通过这两个场景，我们也就对先前的内容做了一个简单的总结与拓展，也对未来的学习做了一个展望，那就是 SQL 语言可以通过不同指令的加入来影响最终的行为，而这些指令往往需要我们通过具体场景的实践以及具体情况的分析来加以掌握，死记硬背是没有用的。

提示

psql \h 指令可以帮助我们了解对应查询语句的形式
psql 工具作为 PostgreSQL 官方发行的客户端，内置了 SQL 查询的对应文档，通过 \h + 对应指令的形式，我们可以非常轻易地将查询所可应用的指令，给提取出来，参考如下：

psql

可以发现，查询指令的变式纷繁复杂，依靠死记硬背根本就记不住，因此掌握随查随用的本领，以及不断积累场景所对应的指令的能力，非常重要。