管理数据库更改

Question

我正在将更多的逻辑移到数据库中，使用触发器、视图、函数、CTE等。当postgres支持plv8/json时，我可以看到我会在其中放很多逻辑。

我在sequel和activerecord中遇到了“标准”的数据库迁移方式问题。Sequel和activerecord都允许您将任意sql代码放入带有时间戳的文件中。每个文件运行时，一个schema_versions表会更新文件名（或文件名中的时间戳），这保留了哪些迁移已应用于当前数据库的记录。

如果在数据库级别进行了大量编码，这意味着对现有视图、函数等的修改将遵循以下模式： 迁移1 定义一个函数和使用该函数的视图。

-- Migration 1
create function calculate(x int) returns int as $$                              
  return x + 1;                                                                 
$$ language sql;                                                                

create view foos as (                                                           
  select something, calculate(something) from a_table                           
);  

需求会发生变化，我需要更改一个函数类型。在迁移2中，我必须删除所有依赖于foo的对象，并通过复制它们的整个主体来重新创建它们——即使大部分其他代码没有任何更改！

-- Migration 2                                                              

-- Have to drop all views and functions that depend on the                  
-- `calculate(int)` function.                                               
drop view foos;                                                             
create or replace calculate(x bigint) returns bigint as $$                  
  return x + 1;                                                             
$$ language sql;                                                            

-- I could do `drop function calculate(int) cascade`,                       
-- but I might accidentally drop some objects that wouldn't get recreated below.

-- Now I have to recreate foo.                                              
create view foos as (                                                       
  select something, calculate(something) from a_table                       
);

如果我基于视图和函数以及触发器构建系统，那么我的迁移将充满重复的代码，并且很难找到最新版本的代码。你可能会说“不要那样做！”，但是对于我的目的（电子商务、运输、交易），通过在数据库内部执行逻辑并确保数据完整性，这样做要容易得多且更快速。

当然，您可以转储当前的数据库模式（其中包括所有代码定义），但我认为您会丢失注释。而且，通常不希望编辑包含整个模式的巨大文件。

有什么解决这个问题的想法吗？我最好的想法是将 SQL 代码包含到它们自己的规范文件中（例如 app/sql/orders/shipping.sql，app/sql/orders/creation.sql 等）。每个人都是直接在这些文件上进行开发的。每当需要发布新版本时，就需要创建一个新的迁移文件，查看与上一个版本相比的所有更改的代码，确定需要删除和重新创建的数据库对象的依赖关系链，然后将 SQL 从规范 SQL 文件复制到新的 sequel/activerecord 迁移文件中。但这很痛苦。 :/

非常欢迎分享您的想法。我希望我已经足够清楚地解释了这一点，因为我减少了咖啡因的摄入量，现在有点昏昏欲睡。

哦，我在 Stack Overflow 上问了一个类似的问题：更改其他视图中使用的列的类型。答案是一个函数，让我传递：

• 要运行的 SQL 代码
• 要删除和重新创建的数据库视图

该函数将检索视图定义，删除视图，运行 SQL 代码，然后重新创建视图定义（以删除的相反顺序）。也许这样一套函数系统可以帮助解决必须将 SQL 代码复制/粘贴到迁移文件中的问题。

- Joe Van Dyk

---

https://github.com/nkiraly/DBSteward 看起来是解决这个问题的一个有趣方法。 - Joe Van Dyk

嗨，我是DBSteward的共同维护者。我知道你提出这个问题已经将近4年了，但我们仍在积极开发DBSteward。我很想知道你是否最终使用了DBSteward，以及你的使用体验如何，或者为什么它没有解决你的问题。 - Austin Hyde

Answer 1

我建议使用Liquibase。

你可以创建文件来跟踪数据库的更改，这些文件将按正确的迁移顺序运行到数据库中。

- Mark O'Connor

Answer 2

你可能会发现Dave Wheeler的博客文章很有趣，从这里开始阅读：

http://justatheory.com/computers/databases/simple-sql-change-management.html

我的数据库更改速率相当小，但我倾向于粗心大意地直接对架构进行微小的更改，因此我不得不想出相当多的基础设施来捕捉我这样做时的情况。基本元素如下：

1. 一个可以从头开始重建开发数据库的 makefile
2. 一组模块化的模式文件（lookups_schema.sql、lookup_data.sql）
3. 一组过渡从一个版本到下一个版本的更新文件
4. 我通常没有相应的降级脚本，有些人会有
5. 用合理数量的测试数据填充我的数据库的脚本
6. 至关重要的是，通过 pgTAP 进行的测试套件，检查我的各种函数、视图和升级脚本。升级测试也可以针对实时数据库运行。

如果您有一个单独的 PostgreSQL 实例设置为关闭 fsync / 开启 ramdisk 等，则重建整个数据库并填充它可能只需几秒钟（如果您没有太多的测试数据）。

从 #1、#2 开始，然后添加 #6（pgTAP非常酷），再加上其他内容。关键是一个检查您的数据库代码的测试套件。

有一些工具可以尝试自动化模式更改，但它们只适用于向表中添加新列之类的操作。一旦您在数据库中编写了代码，它们就没有太大帮助。

- Richard Huxton