Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://918674]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (3)
As of 2018-01-24 02:59 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (255 votes). Check out past polls.