Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: DDL Parsing

by gsiems (Chaplain)
on Aug 05, 2011 at 03:40 UTC ( #918674=note: print w/ replies, xml ) Need Help??


in reply to DDL Parsing

I wasn't aware of any back when I had the question/problem... so, many years ago, I rolled my own. I'm still not aware of any other scripts so I've been continuing to use the home-rolled solution. FWIW, I recently revisited and re-wrote portions of it to use git for tracking the changes-- this allowed me to throw out much of the original custom code.

While it isn't the most elegant code I've ever written, it works well enough and it isn't so ugly as to make my eyes bleed looking at it. The current incarnation is built around the dbms_metadata package, sqlplus, git, perl, shell, and cron.

The work flow is more-or-less as follows (for each database of interest, with each database having it's own git repository):

  • delete the results (if any) of the previous DDL extract (but NOT the .git directory)
  • use sqlplus and the dbms_metadata package to extract the DDL from the database (this is the oldest surviving piece, written back in the day)
  • parse the resulting file (using perl) and writing the object DDL out in a file structure of the form "db_name/ddl/schema_name/object_type/object_name.sql" (Note that, since the parser was written against the dbms_metadata output, it may not work well, or at all, when run against DDL generated by other means-- YMMV)
  • `git commit` the results
  • `git clone` some of the exported databases to the gitweb data directory so the developers can peruse the DDL history

This runs once a week from cron on a linux box (could run it more often but weekly is sufficient for our purposes).

I'm pretty pleased so far with using git to track the changes-- so pleased I've even gone so far as to twist git to document the DDL differences between our production, test, and development databases. The work flow for documenting the differences operates along the lines of (for each pair of databases to compare):

  • delete the previous DDL comparison project (including the .git directory)
  • `git init` a new comparison project
  • copy the already extracted DDL directory from the "from database" to the new project dir
  • `git add` and `git commit` the project dir
  • delete the extracted DDL from the project dir
  • copy the extracted DDL directory from the "to database" to the new project dir
  • `git add` and `git commit` the project dir
  • `git clone` the results to the gitweb data directory

...at this point the comparison git repository has but two commits, the "from database" and the "to database" and git kindly indicates the new, deleted, moved and changed db object DDL files.

I don't have this anywhere out on the net but you can drop me a line if you're interested in looking at it.


Comment on Re: DDL Parsing

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://918674]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (10)
As of 2014-12-22 13:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (117 votes), past polls