Re: SQL dumps for version control

On Aug 8, 4:24 am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA..._at_demogracia.com> wrote:
> I’d need to generate SQL dumps to store in a version control system. I’m
> not asking for advanced features like upgrade/downgrade scripts or the
> like. My requirements are something like this:
>
> - I need to store the structure of specific tables (not all); providing
> the table name should automatically backup all related objects:
> constraints, indexes, triggers, sequences…
> - I need to store the data of specific tables (not all, and not
> necessarily the previous ones).
> - Dumps should be similar when the database has not changed so diff
> tools are meaningful.
>
> Ideally, I’d have a script (*.bat, *.php or whatever) with two
> configuration files where I specify the table names for either case
> (e.g. structure.txt and data.txt), but it’s okay if the process has to
> be done manually.
>
> I’m coding a PHP web site in a Windows 2003 Server box that accesses a
> remote Oracle 10g server. I’ve installed TOAD Free and Oracle SQL
> Developer but I’m open to other tools you may suggest. The version
> control system is Subversion.
>
> I've tried SQL Developer's "Database Export" and the dumps look quite
> good (although it's hard to say because it puts everything in a single
> huge file) but I have to specify all the parameters every time (it
> doesn't even remember the last used directory) what looks too error prone.
>
> Do you have any suggestion? Thank you in advance.
>
> --
> --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain
> -- Mi sitio sobre programación web:http://bits.demogracia.com
> -- Mi web de humor al baño María:http://www.demogracia.com
> --

Trying to find a tool to selectively process the data and information just as you want is unlikely and trying to manipulate the tool into doing what you want would probably be more time consuming that just writing scripts.

The expdp utility could easily be used to generate the DDL and/or data for database objects. To add more flexibility you could use pl/sql to generate the script based on control information you insert into a table and control runing of the script via a dbms_scheduler task.

Another approach would be to use PHP or Perl scripts with the DBI and to use dbms_metadata to generate the DDL and select of concatenated column lists to place data into sqlldr format files.

I hope this information gives you some things to consider in determine you approach to this problem.
-- Mark D Powell -- Received on Sat Aug 09 2008 - 11:11:29 CDT