5

I have existing oracle database. I want to put it under source control (Subversion). The only solution I know - create 'DROP/CREATE/INSERT' text scripts and store them to SVN.

May be there is better way to manage schema and data? I'm using Oracle SQL Developer and I have seen there Migration/Repository management features. Should I use them? And how to use them?

Igor Konoplyanko
  • 9,176
  • 6
  • 57
  • 100
  • Yes, you need to use the `Versioning` feature in SQL Developer. Also, I would suggest using SVN to control only the source code that is customized (by the Dev team). – Anjan Biswas Aug 14 '12 at 13:30
  • 1
    See my comments originally posted on SO [here][1] Hope that helps [1]: http://stackoverflow.com/questions/9195170/diff-local-package-with-database-package/9210845#9210845 – tbone Aug 14 '12 at 18:11
  • I cant connect sql developer to subversion. This looks like bug, because all other svn clients are working. – Igor Konoplyanko Aug 17 '12 at 09:11

3 Answers3

5

Disclaimer: I work for Red Gate

Source Control for Oracle can link your existing schemas to Subversion (Windows only): http://www.red-gate.com/source-control-for-oracle/

This allows you to check in a baseline of CREATE files, and any changes to those moving forwards. It also allows you to apply changes from source control to your schemas, handling the changes as ALTER statements and thus preserving data when modifying tables. This also allows you to spin up private/dedicated schemas per developer creating a sand-boxed environment (if you are working in a team).

We currently don't support adding your static/reference data to source control, but we plan to.

michristofides
  • 453
  • 5
  • 11
3

I'm not sure if I am really answering your question here or just spewing random stuff :)

To start, I would like to say first I am highly against putting data into source control - your database is where you maintain your data. I use a SCM to keep track of object changes and an archive strategy within the DB to track data changes.

I have not used SQL Developer's Version control, mainly as we have our deployments integrated with our Jenkins CI server, so maybe it does exactly what you need already.

A basic structure is required to organize your code and I think this should conform as close as possible to your actual database.

<database>
    |_____<schema>
    |         |____<DDL>
    |         |____<DML>
    |         |____<PLSQL>
    |         |____<Indexes>
    |         |____<Constraints>
    |____<schema>
    ...

The above mimics the namespace boundaries within Oracle. PLSQL and DDL objects do share the same namespace but I separate them due to PLSQL having business logic.

I host all objects within these folders with the naming schema of OBJECTNAME.TYPEEXTENSION, with the (more or less) standardized file extensions:

Table           .tbl
Package Spec    .pks
Package Body    .pkb
Trigger         .trg
View            .vw
...

The contents of these scripts I find depends on your deployment tool and the object type.

  • Can you create dependencies between the file objects or will be know what to run?
  • Can it handle rerunability or do you need to build this into your scripts?
  • Can the tool rollback to previous versions?

With this in mind you can figure out the structure you will need to have in these files.

Our tool currently can not handle rerunability, i.e. where we do not have CREATE OR REPLACE objects or there are tightly coupled dependencies between User Defined Objects.

For these we have had to write a PLSQL block to check system tables to see, for example, if an index has been added and if so it throws an exception which the tool captures and knows to discard and move on to the next script.

I only look to apply the deltas within the database during our continuous integration, hence why we do not use DROP/CREATE/INSERT scripts. This allows us to track each change independently and, with our rollback logic, revert to a specific build.

John D
  • 2,307
  • 17
  • 28
  • About data - I am agree with you, that data shouldn't be stored in db. But *test data* is very likely to be there among various versions of schema. This will give us reproducibility of db-specific bugs, etc. As deployment tool I have maven. – Igor Konoplyanko Aug 15 '12 at 10:14
  • Hmm, we generate our test data on the fly and it gets removed once the tests are run so I think that is why never had to deal with that - added a DML folder to the layout to accommodate this – John D Aug 15 '12 at 13:54
  • How does the automation work? Do you have a job that just run any script that is added to any of those folders? – ziggy Feb 17 '16 at 16:06
  • We have a set of Jenkins CI jobs that are configured to fire off when a checkin occurs, then a jenkins job will run a script that iterates through all the new files – John D Feb 17 '16 at 20:02
1

Look at oracle-ddl2svn - Set of tools for automatization storing of oracle DDL schema in SVN.

qwazer
  • 7,174
  • 7
  • 44
  • 69