DDL Parsing

wcboyd has asked for the wisdom of the Perl Monks concerning the following question:

Hello All,

Is there already a perl script that will parse a single large DDL file into several smaller ones and name the smaller ones based on the name of the object being created / altered?

For example, suppose I have a large DDL file that contains a PO system database. I run the script and get the following files produced from it:

CUSTOMER.ddl
PK_CUST_INDEX.ddl
PK_CUST_CONSTRAINT.ddl
PURCHASE_ORDER.ddl
PK_PURCHASE_ORDER_INDEX.ddl
PK_PURCHASE_ORDER_CONSTRAINT.ddl
etc...
[download]

My reason for this is version control. I need to put each object under version control and copying, pasting, naming my own files is way too tedious.

I would be VERY surprised if this script does not already exist so if someone could kindly point it out I would be most grateful.

Thanks, Craigbert

Comment on DDL Parsing Download Code

Replies are listed 'Best First'.
Re: DDL Parsing by Anonymous Monk on Aug 04, 2011 at 06:18 UTC
Is there already a perl script that ... Probably not Links of interest http://search.cpan.org/dist/DDL-Oracle/ SQL::Translator SQL::Statement	[reply]
Re: DDL Parsing by gsiems (Deacon) on Aug 05, 2011 at 03:40 UTC
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.	[reply]