making DBI writes no-ops

geoff on 2006-12-13T13:58:35

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 :)

---

What about Mocking?

jk2addict on 2006-12-13T15:22:49

How about DBD::Mock or Test::MockDBI?

Re:What about Mocking?

geoff on 2006-12-13T15:44:11

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

ChrisDolan on 2006-12-13T15:51:26

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 one. This was brittle because it required parsing SQL, so I only implemented for the small subset of SQL that the client was using.

UID

Abigail on 2006-12-13T16:03:32

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:UID

geoff on 2006-12-13T16:47:14

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 ?

renodino on 2006-12-13T18:53:04

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/user/darnold/DBIx-Chart-0.05/) 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.