Wednesday, February 25, 2009

sqlpython 1.5.3, with version control

There are several barriers to use of version control tools for Oracle DDL (Data Definition Language: table structures, etc.)
  • Tools from Oracle, Quest, etc. may have VC capabilities, but you never know whether you'll have the tool available in any particular situation, and you never know when the workings of the tools will change beyond recognition. (I'm looking at you, Oracle.)
  • The tools are separate from the mainstream of version control in software development - you're learning quirky specialty tools instead of widely-known industry standards.
  • They lack some of the modern capabilities of distributed version control.

We're better off using standard software development VC.

But, of course, those tools are meant for text files, so there needs to be a handy way to get this stuff from DDL inside the database to version-controlled text files.

It's easy with sqlpython 1.5.3


The new sqlpython commands svn, bzr, and hg all do the following:
  1. Create or update a directory tree, beginning at your current working directory, containing text files with the DDL for all the objects in your schema
  2. Put these files under version control and commit
If you don't want all the DDL, you can limit the dump using the same arguments the ls command takes.

For example,

testschema@orcl> !pwd
/home/catherine/oracle_vc
testschema@orcl> ls

NAME
--------------
INDEX/XPK_PLAY
TABLE/PLAY

2 rows selected.

testschema@orcl> bzr
added testschema
added testschema/index
added testschema/index/xpk_play.sql
added testschema/table
added testschema/table/play.sql
Committing to: /home/catherine/oracle_vc/
added testschema
added testschema/index
added testschema/table
added testschema/index/xpk_play.sql
added testschema/table/play.sql
Committed revision 1.
testschema@orcl> alter table play add (opening_night DATE);

Executed

testschema@orcl> bzr
bzr: ERROR: Already a branch: ".".
Committing to: /home/catherine/oracle_vc/
modified testschema/table/play.sql
Committed revision 2.

testschema@orcl> alter table play add (performances NUMBER(5,0));

Executed

testschema@orcl> create index xif1_play on play (opening_night);

Executed

testschema@orcl> bzr index/
bzr: ERROR: Already a branch: ".".
added testschema/index/xif1_play.sql
Committing to: /home/catherine/oracle_vc/
added testschema/index/xif1_play.sql
Committed revision 3.

2 comments:

Paolo Marzucco said...

Hi Catherine, it looks very interesting!
I am already using Bazaar for Oracle development on Windows and I'd like to try it.

Do you know by any chance if it's possible to use sqlpython with the Python libraries installed by the Bazaar installer for Windows or if I should have a separate Python installation as well?

Thanks,
Paolo

Catherine said...

Hi, Paolo!

Actually, I've never tried the Bazaar installer. I don't think it should need a separate Python installation, though. Try this:

1. Get setuptools / easy_install
2. Verify that easy_install is in your PATH.
3. Type 'easy_install sqlpython'

Let me know if it works/doesn't work!