Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Obtaining SQL updates for application to another database

by rinceWind (Monsignor)
on Apr 05, 2005 at 09:26 UTC ( [id://444913]=perlquestion: print w/replies, xml ) Need Help??

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

Folks,

I am looking for the best option here. I am supporting a legacy application that occasionally gets duff data in its database. I have written a reconciler perl script, using Class::DBI, which reports any inconsistencies.

I have included an update option, which corrects the data; I have also put this in a transaction which can be rolled back at the end.

My client's change management procedures require that the script is run by a DBA - supplying a write permitted username and password. During the testing phase, the DBA has asked me whether I can show him the SQL.

I showed him DBI_TRACE, but even level 1 gives a copious amount of output, and it was a needle in a haystack finding the updates. Implementation of this particular data fix has been agreed according to the logic I have provided.

What he is really looking for is a way that my script could emit the SQL updates, so that these can be taken into production and run in isolation - without needing to run my script in production.

I am looking for a way of doing this - generically. I have several options:

  1. I could hook DBI, possibly by taking the DBI_TRACE output and munging it into freestanding SQL.
  2. I could hook Class::DBI, Ima::DBI or DBIx::ContextualFetch to grab the SQL before it gets executed.
  3. I could build the logic into the application as Class::DBI triggers, or validate_column_values methods. This would then involve hand-crafting some update SQL.

I'm not sure which way to go on this one. Any suggestions?

--
I'm Not Just Another Perl Hacker

Replies are listed 'Best First'.
Re: Obtaining SQL updates for application to another database
by bpphillips (Friar) on Apr 05, 2005 at 12:21 UTC
    Presumably, you have a development environment that you're working with. Does the database you're using provide any sort of "update logs"? The company I work for uses MySQL's binary logs and query logs in our Sarbanes-Oxley compliance. The former logs anything that changes data while the latter logs pretty much everything. If the DB you're using does provide something like this, you could just pull all the relevant SQLs from the log files that were generated when you ran tests against your development server (you'd probably only be interested in the binary logs) and hand that off to the DBA for perusal.

    Update: I was just looking at the DBI_TRACE documentation and you can send all trace information to a file (i.e. DBI->trace(1,"/path/to/log/file")). It should be possible then to parse that file for any update statements your application executed althought it would take quite a bit of regex hackery.
Re: Obtaining SQL updates for application to another database
by jZed (Prior) on Apr 05, 2005 at 13:00 UTC
    The DBI docs say:
    Curently the DBI only defines two trace flags:
    
      ALL - turn on all DBI and driver flags (not recommended)
      SQL - trace SQL statements executed (not yet implemented)
    
    So perhaps write to the dbi-users@perl.org and see what progress has been made or where you can jump in to patch a pure-SQL trace method for DBI. It's something generally useful enough that it seems like it should belong in DBI.
Re: Obtaining SQL updates for application to another database
by adrianh (Chancellor) on Apr 05, 2005 at 16:59 UTC

    You might look at DBD::Mock. You can probably munge the DBD::Mock::StatementTrack objects into something useful:

    For example:

    my $dbh = DBI->connect( 'DBI:Mock:', '', '' ) || die "connect: $DBI::e +rrstr"; # [ call stuff that uses $dbh ] print join "\n", map { $_->to_string } ( @{ $dbh->{mock_all_history} } + );
Re: Obtaining SQL updates for application to another database
by eXile (Priest) on Apr 05, 2005 at 14:50 UTC
    Maybe you could (mis)use the DBI profiler (DBI::ProfileDumper / dbiprof) for this. I've only used it for profiling, but I imagine that the raw data that is produced by profiling might contain all SQL queries, and it looks like DBI::ProfileData is a programmatic interface to this raw data.
Re: Obtaining SQL updates for application to another database
by ryantate (Friar) on Apr 05, 2005 at 17:58 UTC

    Fast and dirty solution:

    It probably wouldn't be that hard to programatically extract the SQL bits from a DBI->trace dump, so he doesn't have to find the needle amid the haystack. Below is an excerpt from a DBI->trace(1, $tracefile) dump I did the other day from Class::DBI code:

    <- connect= DBIx::ContextualFetch::db=HASH(0x573ae0) T <- STORE('dbi_connect_closure' CODE(0x573b88))= 1 at DBI.pm line 6 +62 T <- prepare_cached('SELECT id, location, membership_level, im_servi +ce, last_name, password, im_handle, url, registration_date, profile_u +pdated, priv_email, email, email_bouncing_p, first_names FROM dashboard_user WHERE id = ? ')= ( DBIx::ContextualFetch::st=HASH(0x5674a4) ) [1 items] at DBI.pm l +ine 391 T <- FETCH('Active')= '' at DBI.pm line 1124 T <- FETCH('Taint')= 1 at ContextualFetch.pm line 49 <- STORE('Taint' 0)= 1 at ContextualFetch.pm line 50 <- execute(1)= 1 at ContextualFetch.pm line 51 T <- STORE('Taint' 1)= 1 at ContextualFetch.pm line 52

    Depending on the specifics of your configuration, you might be able to get away with looping through the tracefile with a regex like (untested):

    /^ T  <- prepare(_cached)?\('(.+?)'\)= \( DBIx::ContextualFetch::st=HASH\(/

    ... where the SQL would be stored in $2.

    (Incidentally, it looks like my tainting is being turned off on every execute! I had never noticed that before ...)

Re: Obtaining SQL updates for application to another database
by rinceWind (Monsignor) on Apr 06, 2005 at 10:05 UTC

    Thanks for all your suggestions. It seems that the consensus is to go with option 1 i.e. hooking DBI (trace). A problem I have found with DBI_TRACE is that it is truncating the SQL, hence is not usable in this case.

    jZed metioned the SQL flag bit in the DBI_TRACE level - something I was not aware of. Even if this is not available cross-platform, there is no reason why it could not be implemented for a given DBD - in my case DBD::Sybase.

    I know that the SQL_TRACE environment variable does a similar job for Roguewave (object persistence layer). This has worked well for me diagnosing problems with said legacy app, which uses Roguewave dbtools. I will pursue this option with the sybperl list (which is used to support DBD::Sybase).

    --
    I'm Not Just Another Perl Hacker

      I haven't worked on implementing the specifics of the DBI_TRACE bits in DBD::Sybase, so that may not work for you.

      There are a couple of Sybase-specific alternatives: You could use Ribo to capture incoming SQL requests, you could set up auditing on the server and extract the appropriate SQL requests, and you could also use things like the Sybase Replicator (the java light weight replication system included in ASE 12.5.x) to keep a remote table in sync.

      Update Having re-read the original question I see that the "replicator" option is not at all useful here. However, the other two (and in particular Ribo) could be used fairly easily.

      Michael

Re: Obtaining SQL updates for application to another database
by r34d0nl1 (Pilgrim) on Apr 05, 2005 at 11:28 UTC
    Why not just warning or printing to somewhere the statements that are updates?
    It's in your script, just echo it.
    then the DBA can just copy and paste then or execute as an sql script.
    maybe i didn't get the correct picture of what you intend to do... thx

      The script uses Class::DBI. Updates to individual columns consist of method calls. The whole idea behind Class::DBI is that it is an object persistence layer, and enables the developer to avoid writing SQL.

      If the script were using plain vanilla DBI, it would contain SQL, but probably be at least twice as long.

      --
      I'm Not Just Another Perl Hacker

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://444913]
Approved by EdwardG
Front-paged by tlm
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (10)
As of 2024-03-28 12:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found