在工作中,对于SQL Server,我们编写模式更改脚本,首先回滚要进行的更改(幂等性,因此即使尚未应用模式更改,回滚部分也可以正常运行),然后是应用更改的部分。在TSQL中,很容易查看系统目录或其他表以查看表/列/索引/行是否已经存在,如果不存在则不执行任何操作。
在PostgreSQL中,您受到更多限制,不能简单地向服务器发送命令-但是另一方面,DDL是事务性的,因此不会发生半应用的模式更改。例如,我已经成功地将我在工作中使用的方案适用于自己的小项目(过度?但即使在这里我也有一个开发/测试数据库和一个“真实”数据库)。
\echo Rolling back schema change #35
BEGIN;
DELETE FROM schema_version WHERE schema_id = 35;
DROP TABLE IF EXISTS location_coordinates;
DROP FUNCTION IF EXISTS location_coordinates_populate();
END;
\echo Applying schema change #35
BEGIN;
INSERT INTO schema_version(schema_id, description) VALUES(35, 'Add location_coordinates table');
CREATE TABLE location_coordinates(
location_id INT PRIMARY KEY REFERENCES location(location_id),
latitude FLOAT NOT NULL,
longitude FLOAT NOT NULL,
earth_coordinates earth NOT NULL,
box_10miles cube NOT NULL
);
GRANT SELECT, INSERT, UPDATE, DELETE ON location_coordinates TO ui;
CREATE FUNCTION location_coordinates_populate() RETURNS TRIGGER LANGUAGE 'plpgsql' AS $$
BEGIN
new.earth_coordinates := ll_to_earth(new.latitude, new.longitude);
new.box_10miles := earth_box(new.earth_coordinates, 10 * 1609.344);
RETURN new;
END
$$;
CREATE TRIGGER location_coordinates_populate BEFORE INSERT OR UPDATE ON location_coordinates
FOR EACH ROW EXECUTE PROCEDURE location_coordinates_populate();
INSERT INTO location_coordinates(location_id, latitude, longitude)
SELECT location_id, latitude, longitude FROM location WHERE latitude IS NOT NULL AND longitude IS NOT NULL;
CREATE INDEX location_coordinates_10miles ON location_coordinates USING gist (box_10miles);
END;
\echo Done
这个脚本可以在数据库上通过 "psql -f schema-changes/35.sql" 运行。仅需剪切到“应用…”消息即可获取回滚命令。而且,如您所见,该更改维护了一个元数据表“schema_version”,因此我可以看到哪些更改已应用。整个更改是作为事务完成的,包括数据迁移。在此,我使用了 DROP 命令的“IF EXISTS”功能,以使回滚部分在更改未应用时也能正常工作。我记得我们在 Oracle 上做的一件事情是将模式更改编写为 PL/SQL——您可以使用 plpgsql 中的一些函数来帮助进行更改吗?
请注意,在上面的更改中,我将“location”的“latitude”和“longitude”列(可为空)迁移到单独的“location_coordinates”关系中(并添加 earthdistance 的内容),但没有删除旧列。我们必须小心谨慎地使模式更改向后兼容,如果可能的话。因此,我可以在更新应用程序使用新表之前应用此模式更改。我将有第二个更改来删除旧列,以在更新应用程序之后应用。在工作中,这些将在两个不同的发布周期中完成,因此在发布 X 期间,我们仍然可以选择将应用程序回退到发布 X-1 而无需首先回退所有模式更改;同时能够在应用程序之前的一个单独窗口部署模式更改。(从技术上讲,我应该编写触发器,以便将旧表的更新同步到新表中,但我没有这样做,因为那太像工作了 :))
我们还有一些东西,比如一个应用程序,它会爬行我们所有的数据库,查看 schema_version
表中的内容,并跟踪更改,这样人们甚至可以在不连接的情况下查看进行了哪些更改,并了解每个更改的历史记录(我们跟踪“在开发中回滚”、“在开发中应用”等)。在工作中,我们的 schema_version 表还包括作者信息等。从版本控制应用版本信息的魔法方式很酷——我们遇到的一个问题是,如果 SC 在 QA 中应用,然后在 Perforce 中更改,可能没有人注意到。因此,一种跟踪模式更改 35 修订版 #4 是否已应用的方法会很好。
需要注意的一件事是,我们的模式更改编号与应用程序版本独立。显然它们是相关的——这是另一件爬行应用程序允许人们输入的事情——但我们尝试进行许多小的更改,而不是巨大的“这里是发布 X 的所有内容”补丁。模式更改也用于添加新索引,因此可能根本不受应用程序驱动。总的来说,模式更改是由开发人员“拥有”的,而不是 DBA——尽管在上面的“创建索引”示例中,DBA 基本上是扮演开发人员角色并拥有模式更改。是的,我们要求开发人员具备高水平的 SQL 能力——尽管公司的其他团队工作方式略有不同,并将更多的工作交给 DB 团队。