管理数据库更改

Question

我开始将更多的逻辑转移到数据库中，使用触发器、视图、函数、CTE等等。当plv8 8/json推出postgres时，我可以看到自己将大量的逻辑放入其中。

对于在续集和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/order/shipping.sql、app/sql/orders/creation.sql等)中。每个人都是在这些基础上发展的。无论何时发布，您都需要创建一个新的迁移文件，查看自上一版本以来更改的所有代码，找出需要删除和重新创建的数据库对象的依赖链，然后将sql从规范sql文件复制到一个新的续集/activerecord迁移文件中。但这很痛苦。:/

思想是非常受欢迎的。我希望我解释得够清楚，我正在减少咖啡因的摄入量，我是一个有点昏昏欲睡的自动取款机。

哦，我问了一个类似的关于堆栈溢出的问题：更改其他视图中使用的列的类型 --答案是一个让我传入的函数：

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

该函数将检索视图定义，删除视图，运行sql代码，然后重新创建视图定义(按删除的相反顺序)。这样的函数系统可能有助于解决将sql代码复制/粘贴到迁移文件中的问题。

Answer 1

我推荐液基。

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

Answer 2

你可能会发现Dave的博客-从这里开始的有趣的文章：

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

我的数据库更改率相当小，但我倾向于粗心大意，直接对模式进行小的更改，因此我必须想出相当多的基础设施，以便在完成此操作时捕捉到。基本要素是：

1. 可以从头开始重建开发数据库的makefile
2. 一组模式文件，分隔为“模块”(lookups_schema.sql，lookup_data.sql)。
3. 从一个版本转换到下一个版本的一组更新文件。
4. 我通常没有相应的降级脚本，有些人这样做
5. 用合理数量的测试数据填充数据库的脚本
6. 关键是，一个通过pgTAP来检查我的各种功能、视图和升级脚本的测试套件。升级测试也可以在活动数据库上运行。

如果您使用fsync关闭/打开ramdisk等设置了一个单独的PostgreSQL实例，那么重新构建整个DB并填充它可能需要几秒钟时间(如果您没有太多的测试数据)。

从#1，#2开始，然后添加#6 (pgTAP非常酷)，然后是其余的。关键是一个测试套件来检查你在数据库中的代码。

有些工具试图为您自动化模式更改，但它们实际上只擅长向表中添加新列之类的东西。一旦数据库中有了代码，它们就没有多大帮助了。