使用 Oracle 进行数据库源代码控制

Database source control with Oracle

提问by borjab

I have been looking during hours for a way to check in a database into source control. My first idea was a program for calculating database diffs and ask all the developers to imlement their changes as new diff scripts. Now, I find that if I can dump a database into a file I cound check it in and use it as just antother type of file.

我一直在寻找一种将数据库签入源代码管理的方法。我的第一个想法是一个用于计算数据库差异的程序，并要求所有开发人员将他们的更改实现为新的差异脚本。现在，我发现如果我可以将数据库转储到文件中，我可以将其检入并将其用作另一种类型的文件。

The main conditions are:

主要条件是：

• Works for Oracle 9R2
• Human readable so we can use diff to see the diferences. (.dmp files doesn't seem readable)
• All tables in a batch. We have more than 200 tables.
• It stores BOTH STRUCTURE AND DATA
• It supports CLOB and RAW Types.
• It stores Procedures, Packages and its bodies, functions, tables, views, indexes, contraints, Secuences and synonims.
• It can be turned into an executable script to rebuild the database into a clean machine.
• Not limitated to really small databases (Supports least 200.000 rows)

• 适用于 Oracle 9R2
• 人类可读，因此我们可以使用 diff 来查看差异。（.dmp 文件似乎不可读）
• 批处理中的所有表。我们有200多张桌子。
• 它存储结构和数据
• 它支持 CLOB 和 RAW 类型。
• 它存储过程、包及其主体、函数、表、视图、索引、约束、Secuences 和同义词。
• 它可以变成一个可执行脚本，将数据库重建成干净的机器。
• 不限于非常小的数据库（支持至少 200.000 行）

It is not easy. I have downloaded a lot of demos that does fail in one way or another.

这不简单。我已经下载了很多以某种方式失败的演示。

EDIT: I wouldn't mind alternatives aproaches provided that they allows us to check a working system against our release DATABASE STRUCTURE AND OBJECTS + DATA in a batch mode.

编辑：我不介意替代方法，前提是它们允许我们以批处理模式根据我们的发布数据库结构和对象 + 数据检查工作系统。

By the way. Our project has been developed for years. Some aproaches can be easily implemented when you make a fresh start but seem hard at this point.

顺便一提。我们的项目已经开发多年。有些方法可以在您重新开始时轻松实施，但此时似乎很难。

EDIT: To understand better the problem let's say that some users can sometimes do changes to the config data in the production eviroment. Or developers might create a new field or alter a view without notice in the realease branch. I need to be aware of this changes or it will be complicated to merge the changes into production.

编辑：为了更好地理解问题，假设某些用户有时可以更改生产环境中的配置数据。或者开发人员可能会在 realease 分支中创建一个新字段或更改视图而不通知。我需要了解这些更改，否则将更改合并到生产中会很复杂。

回答by Stephen ODonnell

So many people try to do this sort of thing (diff schemas). My opinion is

很多人试图做这种事情（差异模式）。我的意见是

• Source code goes into a version control tool (Subversion, CSV, GIT, Perforce ...). Treat it as if it was Java or C code, its really no different. You should have an install process that checks it out and applies it to the database.
• DDL IS SOURCE CODE. It goes into the version control tool too.
• Data is a grey area - lookup tables maybe should be in a version control tool. Application generated data certainly should not.

• 源代码进入版本控制工具（Subversion、CSV、GIT、Perforce ...）。把它当作 Java 或 C 代码一样对待，它真的没有什么不同。您应该有一个安装过程来检查它并将其应用于数据库。
• DDL 是源代码。它也进入版本控制工具。
• 数据是一个灰色区域 - 查找表可能应该在版本控制工具中。应用程序生成的数据当然不应该。

The way I do things these days is to create migration scripts similar to Ruby on Rails migrations. Put your DDL into scripts and run them to move the database between versions. Group changes for a release into a single file or set of files. Then you have a script that moves your application from version x to version y.

这些天我做事的方式是创建类似于 Ruby on Rails 迁移的迁移脚本。将您的 DDL 放入脚本并运行它们以在版本之间移动数据库。将发布的更改分组到单个文件或一组文件中。然后您有一个脚本，可以将您的应用程序从版本 x 移动到版本 y。

One thing I never ever do anymore (and I used to do it until I learned better) is use any GUI tools to create database objects in my development environment. Write the DDL scripts from day 1 - you will need them anyway to promote the code to test, production etc. I have seen so many people who use the GUIs to create all the objects and come release time there is a scrabble to attempt to produce scripts to create/migrate the schema correctly that are often not tested and fail!

我再也不会做的一件事（在我学得更好之前我曾经这样做过）是使用任何 GUI 工具在我的开发环境中创建数据库对象。从第 1 天开始编写 DDL 脚本 - 无论如何，您将需要它们来将代码推广到测试、生产等。我见过很多人使用 GUI 创建所有对象并在发布时拼命尝试生产用于正确创建/迁移架构的脚本，这些脚本通常未经测试并失败！

Everyone will have their own preference to how to do this, but I have seen a lot of it done badly over the years which formed my opinions above.

每个人都会对如何做这件事有自己的偏好，但多年来我看到很多事情做得很糟糕，这形成了我的观点。

回答by Adam Hawkes

Oracle SQL Developer has a "Database Export" function. It can produce a single file which contains all DDL and data.

Oracle SQL Developer 具有“数据库导出”功能。它可以生成包含所有 DDL 和数据的单个文件。

回答by hminaya

I use PL/SQL developer with a VCS Plug-in that integrates into Team Foundation Server, but it only has support for database objects, and not with the data itself, which usually is left out of source control anyways.

我使用带有集成到 Team Foundation Server 的 VCS 插件的 PL/SQL 开发人员，但它只支持数据库对象，而不支持数据本身，数据本身通常被排除在源代码控制之外。

Here is the link: http://www.allroundautomations.com/bodyplsqldev.html

这是链接：http: //www.allroundautomations.com/bodyplsqldev.html

回答by Stephanie Page

I think this is a case of,

我认为这是一个案例，

• You're trying to solve a problem
• You've come up with a solution
• You don't know how to implement the solution
• so now you're asking for help on how to implement the solution

• 你正在努力解决一个问题
• 你想出了一个解决方案
• 你不知道如何实施解决方案
• 所以现在您正在寻求有关如何实施解决方案的帮助

The better way to get help,

获得帮助的更好方式，

• Tell us what the problem is
• ask for ideas for solving the problem
• pick the best solution

• 告诉我们问题是什么
• 征求解决问题的想法
• 选择最佳解决方案

I can't tell what the problem you're trying to solve is. Sometimes it's obvious from the question, this one certainly isn't. But I can tell you that this 'solution' will turn into its own maintenance nightmare. If you think developing the database and the app that uses it is hard. This idea of versioning the entire database in a human readable form is nothing short of insane.

我不知道你要解决的问题是什么。有时从问题中可以明显看出，这个肯定不是。但我可以告诉你，这个“解决方案”会变成它自己的维护噩梦。如果您认为开发数据库和使用它的应用程序很困难。这种以人类可读的形式对整个数据库进行版本控制的想法简直是疯狂的。

回答by René Nyffenegger

Have you tried Oracle's Workspace Manager? Not that I have any experience with it in a production database, but I found some toy experiments with it promising.

您是否尝试过Oracle 的工作区管理器？并不是说我在生产数据库中对它有任何经验，但我发现一些玩具实验很有希望。

回答by Nick

It may not be as slick as detecting the diffs, however we use a simple ant build file. In our current CVS branch, we'll have the "base" database code broken out into the ddl for tables and triggers and such. We'll also have the delta folder, broken out in the same manner. Starting from scratch, you can run "base" + "delta" and get the current state of the database. When you go to production, you'll simply run the "delta" build and be done. This model doesn't work uber-well if you have a huge schema and you are changing it rapidly. (Note: At least among database objects like tables, indexes and the like. For packages, procedures, functions and triggers, it works well.) Here is a sample ant task:

它可能不像检测差异那么灵巧，但是我们使用了一个简单的 ant 构建文件。在我们当前的 CVS 分支中，我们会将“基本”数据库代码分解为表​​和触发器等的 ddl。我们还将有 delta 文件夹，以相同的方式分解。从头开始，您可以运行“base”+“delta”并获取数据库的当前状态。当您进入生产环境时，您只需运行“delta”构建即可完成。如果您有一个庞大的架构并且您正在快速更改它，则此模型不能很好地工作。（注意：至少在表、索引等数据库对象中。对于包、过程、函数和触发器，它工作得很好。）这是一个示例 ant 任务：

    <target name="buildTables" description="Build Tables with primary keys and sequences">
<sql driver="${conn.jdbc.driver}" password="${conn.user.password}"
    url="${conn.jdbc.url}" userid="${conn.user.name}"
    classpath="${app.base}/lib/${jdbc.jar.name}">
    <fileset dir="${db.dir}/ddl">
        <include name="*.sql"/>
    </fileset>
</sql>
</target>

回答by J-16 SDiZ

Don't try to diff the data. Just write a trigger to store whatever-you-want-to-get when the data is changed.

不要尝试比较数据。只需编写一个触发器来存储数据更改时想要获取的任何内容。

回答by Craig Trader

Expensive though it may be, a tool like TOAD for Oraclecan be ideal for solving this sort of problem.

尽管可能很昂贵，但像TOAD for Oracle这样的工具非常适合解决此类问题。

That said, my preferred solution is to start with all of the DDL (including Stored Procedure definitions) as text, managed under version control, and write scripts that will create a functioning database from source. If someone wants to modify the schema, they must, must, must commit those changes to the repository, not just modify the database directly. No exceptions! That way, if you need to build scripts that reflect updates between versions, it's a matter of taking all of the committed changes, and then adding whatever DML you need to massage any existing data to meet the changes (adding default values for new columns for existing rows, etc.) With all of the DDL (and prepopulated data) as text, collecting differences is as simple as diffing two source trees.

也就是说，我的首选解决方案是将所有 DDL（包括存储过程定义）作为文本开始，在版本控制下进行管理，并编写将从源代码创建功能数据库的脚本。如果有人想要修改架构，他们必须，必须，必须将这些更改提交到存储库，而不仅仅是直接修改数据库。没有例外！这样，如果您需要构建反映版本之间更新的脚本，只需获取所有已提交的更改，然后添加您需要的任何 DML 来处理任何现有数据以满足更改（为新列添加默认值）现有行等）将所有 DDL（和预填充数据）作为文本，收集差异就像比较两个源树一样简单。

At my last job, I had NAnt scripts that would restore test databases, run all of the upgrade scripts that were needed, based upon the version of the database, and then dump the end result to DDL and DML. I would do the same for an empty database (to create one from scratch) and then compare the results. If the two were significantly different (the dump program wasn't perfect) I could tell immediately what changes needed to be made to the update / creation DDL and DML. While I did use database comparison tools like TOAD, they weren't as useful as hand-written SQL when I needed to produce general scripts for massaging data. (Machine-generated code can be remarkably brittle.)

在我的上一份工作中，我使用 NAnt 脚本来恢复测试数据库，根据数据库版本运行所有需要的升级脚本，然后将最终结果转储到 DDL 和 DML。我会对一个空数据库做同样的事情（从头开始创建一个），然后比较结果。如果两者显着不同（转储程序并不完美），我可以立即知道需要对更新/创建 DDL 和 DML 进行哪些更改。虽然我确实使用了像 TOAD 这样的数据库比较工具，但当我需要生成用于处理数据的通用脚本时，它们不如手写 SQL 有用。（机器生成的代码可能非常脆弱。）

回答by Aram Paronikyan

Try RedGate's Source Control for Oracle. I've never tried the Oracle version, but the MSSQL version is really great.

试试 RedGate 的Oracle 源代码控制。我从未尝试过 Oracle 版本，但 MSSQL 版本确实很棒。