Stories
Slash Boxes
Comments

NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

use Perl

All the Perl that's Practical to Extract and Report

use Perl Log In

[ Create a new account ]

geoff (2013)

geoff
{geoff} {at} {modperlcookbook.org}
http://www.modperlcookbook.org/

see http://www.modperlcookbook.org/~geoff/ [modperlcookbook.org] for personal information, links to presentations, GPG key, and so on.

Journal of geoff (2013)

Wednesday December 13, 2006

08:58 AM

making DBI writes no-ops

[ #31897 ]

one of our databases consists of mysql myisam tables, which don't have transactions so you can't rollback. this makes debugging and 'dry-run' modes very difficult. one of my coworkers suggested DBD::NullP but that doesn't quite fit the bill - I want to pull data, inspect it, but no commit changes...

what I really need is some DBD driver that intercepts write statements (UPDATE, REPLACE, etc) and turns them in to no-ops. DBD::Proxy and/or DBD::ProxyServer seems to almost get me there, but I don't feel like I should have to go through a complex acl-style process for all my code - I should be able to swap out the driver and it should all just work. so, has anyone done anything like this? I'm contemplating writing something like DBD::NullWrites, which should be relatively simple once I get my head around the DBD API. and after figuring out whether I can make it generic to apply to any read-front-end (DBD::Oracle for reads, DBD::NullWrites for writes, etc). pointers from folks who have ventured into DBD:: space appreciated :)

List all Journal entries

making DBI writes no-ops More | Login | Reply

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Without JavaScript enabled, you might want to use the classic discussion system instead. If you login, you can remember this preference.

What about Mocking? (Score:1)

by jk2addict (4946)

How about DBD::Mock or Test::MockDBI?
- Re: (Score:2)
  
  by geoff (2013)
  
  nope, those won't do - reads really need to work so I can watch the program iterating over the record set and doing something to it. I just don't want the commits to be effective in an environment where I can't naturally undo them. so, mocking the DBI objects to just see that the sql is properly formed does't fit the bill...
Two not-so-good solutions (Score:1)

by ChrisDolan (2855)

I have used the following two heavy-handed solutions in the past. Neither of them are good solutions for big databases, but they can get the job done where disk space is not a concern.

1) Once per day, replicate the database to a dev copy. Run all tests on the dev database, destructively

2) Copy-on-write. I wrote a DBI facade which intercepted SQL prepares and, upon seeing an insert or update, copied the table to a new table and thereafter altered all SQL to replace the name of the old table with the new o
UID (Score:1)

by Abigail (26)

You might want to run your queries as a user that only has read permission - and ignore any insert/delete errors caused by insufficient permission.
- Re: (Score:2)
  
  by geoff (2013)
  
  yeah, that's an option I've used. but it's a bit clumsy and somewhat dangerous as you need to remember to change the uid. in my situation, I want to offer a --dry-run option to all our scripts across the board, which works fine except for the databases that don't support transactions...
Perhaps a DBI subclass ? (Score:1)

by renodino (6856)

If you want to preserve the connection to the orignal DBMS, but just filter out INSERT/UPDATE/DELETE[/CREATE/ALTER/DROP], maybe a DBI subclass is the solution ? e.g., "DBIx::ReadOnly" ? DBIx::Chart (http://search.cpan.org/~darnold/DBIx-Chart-0.05/ [cpan.org]) might be useful as a template (tho overbuilt for your purposes).
BTW: If you're using some DBI wrapper (DBIXC, CDBI, etc), you'll likely need to specify the subclass via the RootClass connect() attribute.