建议在管理多个数据库时将所有liquibase变更集记录在一个数据库中吗? [英] Recommended to record all liquibase changesets in one database when managing multiple databases?

问题描述

我正在使用一个现有应用程序，该应用程序使用两个不同的数据库以及一堆预先存在的SQL语句.数据库是:

1. 应用
2. 记录

它们不共享架构，但是它们都在应用程序运行时使用.

问题:是否可以将所有变更集记录在一个数据库中?

这里是一些背景.当我第一次针对这些测试 liquibase 时，我试图在运行时选择数据库，并且遇到错误，再加上，它正在将变更集记录在系统数据库中.

SQL语句都捆绑在几个文件中，因此我开始按数据库拆分它们，并在每次看到新的USE [foo]

时插入--changeset条目

一切都看起来不错，我认为这就是我必须要做的.这为我提供了每个数据库的变更日志，如果您一开始就很干净，那可能是理想的选择.

但是，我无意中错过了Logging数据库中的一条语句，该语句将记录插入到App数据库中；因此，我在两个数据库中都有重复的插入语句，但是插入内容明确表示:

INSERT INTO [app].[dbo].[tableA] values ('1', 'one')

因此，当我针对两个数据库运行更新时，它对于App成功，但在记录时失败.有趣的是，如果我先将删除"放在日志记录"下，那么尽管URL指向日志记录"数据库，它还是成功了.

这让我有些惊讶，但是现在让我怀疑是否不能只在URL中列出Logging数据库并将所有更改放在一起? App变更集和Logging变更集将仅针对Logging DB记录.

我知道这可以防止将它们拆分开来，但是考虑到所有SQL语句已经混合在一起，因此一开始似乎要容易得多.

解决方案

我会说答案是取决于".我的一般建议是，尽管每个应用程序都应使用一个数据库更改日志，但是如果您有很多独立的模块，则按模块将其分解通常是有意义的.但是，这些模块必须完全独立，没有跨对象引用.

在您的情况下，听起来好像您有单独的独立模块，但是除非您有充分的理由，否则它仍然是一个简单的数据库更改日志表，该表管理两个模式/数据库中所应用的内容，这可能最简单且出错率更低.这只是模块化与简单性之间的权衡.

有了一个单独的变更日志文件和databasechangelog表，您就可以在大多数操作中使用"catalogName"属性来指定应对其进行更改的数据库.

Liquibase仅使用一个连接，因此使用单个changelog表将只允许您运行一次liquibase，但是作为连接的用户将需要对两个数据库的权限.

I am working with an existing application that has two different databases that it uses, and a bunch of pre-existing SQL statements. The databases are:

1. App
2. Logging

They do not share a schema but they both are used while the app is running.

QUESTION: Is it OK, preferred, or frowned upon to record all change sets under one of the databases?

Here is some background. When I first started testing liquibase against these, I was trying to select the database at runtime and was running into errors, plus, it was recording the changesets in the system database.

The SQL statements are all bundled in a few files, so I began splitting them by database, and inserting --changeset entries every time I saw a new USE [foo]

Everything was looking good, and I thought that was how I had to go about it. That gives me a changelog per database, which is probably ideal if you were starting clean.

However, I accidentally missed a statement in the Logging database that inserted records into the App database; so, I had duplicate insert statements in both databases, but the insert explicitly said:

INSERT INTO [app].[dbo].[tableA] values ('1', 'one')

So, when I ran update against both databases, it succeed for App but failed on Logging. The interesting thing though is that if I put in a DELETE first under Logging, it then succeeded, despite the URL pointing to the Logging database instead.

That surprised me a bit, but now makes me wonder if I couldn't just list the Logging database in the URL and leave all of the changes together; the App change sets and the Logging change sets would be recorded against only the Logging DB.

I know that could prevent splitting them down the road, but it seems like it would be much easier to implement in the beginning, given that all of the SQL statements are already mixed.

解决方案

I'd say the answer is "It Depends". My general advice is that you should use a single databasechangelog per application although if you have a lot of independent modules it often makes sense to break it up by modules. The modules need to be completely independent, though, with no cross-object references.

In your case, it sounds like you have separate independent modules, but unless you have a good reason it is probably easiest and less error prone to still have a single databasechangelog table that manages what has been applied to both schemas/databases. It is just a trade-off in modularity vs. simplicity.

With a single changelog file and databasechangelog table, You are able to use the "catalogName" attribue on most operations to specify the database that they changes should be made in.

Liquibase uses just one connection, so using a single changelog table will allow you to only need to run liquibase once, but the user you connect as will need permissions to both databases.

这篇关于建议在管理多个数据库时将所有liquibase变更集记录在一个数据库中吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！