CODEWITHSUNDEEP
oracle-databaseversion-controlscripting
David North
David North

Reputation: 1406

Oracle DDL/DML script, PL/SQL in Source Control

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.

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.

What has worked well under similar circumstances for you?

Upvotes: 3

Views: 7533

Answers (6)

justadeveloper
justadeveloper

Reputation: 198

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.

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.

You can download it at www.gitora.com

Upvotes: 1

Tom Clark AaiCanHelp.com
Tom Clark AaiCanHelp.com

Reputation:

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.

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.

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 ....

In my opinion it sound good but does not work well in practice.

Upvotes: 0

zmische
zmische

Reputation: 849

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)

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.

Upvotes: 0

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

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".

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.

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.

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.

Upvotes: 0

alexs
alexs

Reputation: 406

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.

Upvotes: 0

Mac
Mac

Reputation: 8339

See my answer to How should you build your database from source control?. It is mainly based on the excellent K. Scott Allen series on that topic.

Upvotes: 0

Related Questions