Oracle DDL/DML 脚本，源代码管理中的 PL/SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1262534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle DDL/DML script, PL/SQL in Source Control
提问by David North
I'm looking for recommendations/guidance on how best to store DDL/DML and PL/SQL script in Source Control (we're using Microsoft Visual Studio TFS) for an in-house developed SaaS application.
我正在寻找关于如何最好地在源代码管理中存储 DDL/DML 和 PL/SQL 脚本（我们使用 Microsoft Visual Studio TFS）的建议/指导，以用于内部开发的 SaaS 应用程序。
We have a team of up to 7 developers working on a project that is based on a fairly straight-forward Dev/Main branch model. There are dependencies (primarily execution order) between scripts.
我们有一个最多由 7 名开发人员组成的团队，他们致力于一个基于相当直接的 Dev/Main 分支模型的项目。脚本之间存在依赖关系（主要是执行顺序）。
What has worked well under similar circumstances for you?
在类似的情况下，什么对你来说效果很好？
采纳答案by Mac
See my answerto How should you build your database from source control?. It is mainly based on the excellent K. Scott Allenseries on that topic.
请参阅我对如何从源代码控制构建数据库的回答？. 它主要基于关于该主题的优秀K. Scott Allen系列。
回答by justadeveloper
We built a tool that can manage PL/SQL code (or any other object that can be created with the CREATE OR REPLACE command) in the Oracle Database. It hooks Git to the Oracle database.
我们构建了一个工具，可以管理 Oracle 数据库中的 PL/SQL 代码（或可以使用 CREATE OR REPLACE 命令创建的任何其他对象）。它将 Git 挂接到 Oracle 数据库。
You can do basic Git tasks such as commiting, resetting, branching, cloning, merging, pulling etc... and Gitora automatically updates the PL/SQL code in the database.
您可以执行基本的 Git 任务，例如提交、重置、分支、克隆、合并、拉取等……并且 Gitora 会自动更新数据库中的 PL/SQL 代码。
You can download it at www.gitora.com
你可以在www.gitora.com下载
回答by alexs
We kept the DDL scripts under source control and for DML, we've used something similar with this: http://dbdeploy.com/, but ours was written in Perl so it was kinda awkward.
我们将 DDL 脚本保持在源代码控制之下，对于 DML，我们使用了类似的东西：http: //dbdeploy.com/，但我们的脚本是用 Perl 编写的，所以有点尴尬。
回答by Chris Cameron-Mills
A simplified look at a leg in our source control would be:
我们的源代码管理中的一个分支的简化视图是：
\DatabasePatches
\Core
\Data
\DatabaseSource
\Core
\SchemaA
\SchemaB
\SchemaC
DDL for a particular chunk of work is written and checked in under core patches with DML/migration checked in under data patches. Part of the patch label is a sequence number (manually add 10 each time allowing future insertion of patches in between) so a patch might be called "DATAPATCH01530 - migrate of xyz.sql".
特定工作块的 DDL 在核心补丁下编写和签入，DML/迁移在数据补丁下签入。补丁标签的一部分是序列号（每次手动添加 10 以允许将来在两者之间插入补丁），因此补丁可能被称为“DATAPATCH01530 - xyz.sql 的迁移”。
When deploying to a new environment all the core patches are run and then data patches are run. If there is DML important for the next part of a core patch then it may be included in that core patch.
部署到新环境时，会运行所有核心补丁，然后运行数据补丁。如果核心补丁的下一部分有重要的 DML，那么它可能包含在该核心补丁中。
Once a patch has been run for the first time on a new location the file is marked FINAL in the source control (we use PVCS and lock the file with a user called FINAL) to make sure it can't be changed and cause inconsistency. Any additional changes should be included in a seperate patch.
在新位置首次运行补丁后，该文件在源代码管理中被标记为 FINAL（我们使用 PVCS 并使用名为 FINAL 的用户锁定文件）以确保它无法更改并导致不一致。任何其他更改都应包含在单独的补丁中。
Stored procedures, functions, packages etc. are saved and checked in under the DatabaseSource leg. You are unable to guarantee if these objects are promoted before scripts are run so we accommodate for this by creating stubs in our scripts (e.g views would be created as SELECT '1' FROM dual, packages would contain a dummy procedure) that guarantee the object exists and allows you to grant privileges etc. When the actual object gets promoted it replaces the stub and retains privileges.
存储过程、函数、包等在 DatabaseSource 分支下保存和检入。您无法保证在脚本运行之前是否提升了这些对象，因此我们通过在脚本中创建存根来适应这一点（例如，视图将创建为 SELECT '1' FROM dual，包将包含一个虚拟过程）来保证对象存在并允许您授予特权等。当实际对象被提升时，它会替换存根并保留特权。
回答by zmische
You could sniff through LiquiBase (http://en.wikipedia.org/wiki/LiquiBase) LiquiBase is an open source database-independent library for tracking, managing and applying database changes. (XML-based)
您可以通过 LiquiBase ( http://en.wikipedia.org/wiki/LiquiBase)嗅探LiquiBase 是一个开源的独立于数据库的库，用于跟踪、管理和应用数据库更改。（基于 XML）
It has build system, where you are able to order your scripts in your own way. All you need - is just put includes in correct order. I think It should help.
它具有构建系统，您可以在其中以自己的方式订购脚本。您所需要的只是将包含的内容按正确的顺序排列。我认为它应该有帮助。
I'm going to use this tool to track database DML, PLSQL changes with GIT version control.
我将使用这个工具通过 GIT 版本控制来跟踪数据库 DML、PLSQL 更改。
回答by zmische
Why do you want to keep the DDL scripts? You can never use them again. You can't trust them with out comparing them to production. The only reason I would keep DDL (other than it sounds like a good idea) is to be able to compare full base lines by putting each scheme in a single file. This would allow you to do a diff before deployment to see the changes.
为什么要保留 DDL 脚本？你永远不能再使用它们。如果不将它们与生产进行比较，您就无法信任它们。我保留 DDL 的唯一原因（除了听起来是个好主意）是能够通过将每个方案放在一个文件中来比较完整的基线。这将允许您在部署之前进行比较以查看更改。
I have checked in DDL on many projects and have found that by building changes on top of the source code checkout instead of what is in prod. we lost changes.
我已经检查了许多项目的 DDL，并发现通过在源代码检出而不是生产中的内容之上构建更改。我们失去了变化。
DB is different than front-end source code. You have people wit the ability to connect to the database and make changes through sql-plus or toad to fix an emergency problem and you will not get your change into source code control. DBAs could make changes ....
DB 不同于前端源代码。您让人们有能力连接到数据库并通过 sql-plus 或 toad 进行更改以解决紧急问题，而您不会将更改纳入源代码控制。DBA 可以进行更改....
In my opinion it sound good but does not work well in practice.
在我看来，这听起来不错，但在实践中效果不佳。
