Idea for DBIx module: Creating mass test databases based on existing ones

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

We did a performance test of an Oracle database client and needed a way to inflate several existing tables to simulate realistic data sizes in a production environment. The database has a complex structure and lots of primary key, foreign key, uniqueness and check constraints. I came up with an Oracle procedure that analyzes all constraints and automatically adds records to tables until they reach a specified target size. The data in the new records is meaningful (values come e.g. from tables referenced in foreign key relations) and randomly chosen from the corresponding sources and one can also restrict the number of random choices so that the procedure performs reasonably.

The procedure is very useful for us for testing and I wondered if instead of restricting it to Oracle, one could re-implement it as a database agnostic Perl module.

Now there is a huge number of DBIx modules available, so while I could not find something similar to this in CPAN, in particular the DBIx modules, I may have overlooked it. Therefore I have two questions to the community:

(1) Has CPAN already something in that direction?
(2) Do you think it could be useful to have such a (DBIx?) module?

Comment on Idea for DBIx module: Creating mass test databases based on existing ones

Replies are listed 'Best First'.
Re: Idea for DBIx module: Creating mass test databases based on existing ones by Your Mother (Archbishop) on Jul 24, 2012 at 17:25 UTC
I would start looking at these. This problem space is non-trivial (triggers, special constraints, series PKs, etc, etc) but these go a long way. DBIx::Class::Fixtures DBIx::Class::DeploymentHandler Test::DBIx::Class DBIx::Class::Schema->deploy DBIx::Class::Schema::Versioned DBIx::Class::Migration dbicadmin	[reply]
Re^2: Idea for DBIx module: Creating mass test databases based on existing ones by jds17 (Pilgrim) on Jul 24, 2012 at 20:01 UTC
Thank you for your pointers, the modules you have listed are very useful modules to keep in mind. Since I am quite proficient on the DBMS side, but have only done basic things with Perl DBI, I really need to take a good look at these and others first before starting to program.	[reply]
Re: Idea for DBIx module: Creating mass test databases based on existing ones by technojosh (Priest) on Jul 24, 2012 at 16:03 UTC
It would absolutely be useful to the testing community I haven't seen anything like this on CPAN, but I also have never done an extensive search for such a thing DBI::ODBC version plz I could give my 2 cents on the state of the databases often used in test efforts, but that may be out of scope. This would be a useful tool, although it looks (at first glance) like it would be a lot of work to get it functional across a decent swath of DBIx flavors -TJ	[reply]
Re^2: Idea for DBIx module: Creating mass test databases based on existing ones by jds17 (Pilgrim) on Jul 24, 2012 at 19:37 UTC
Thank you for the incentive and your comments. I know there is still a long way to go to make things work, especially across different DBMSs. But I find it worth the effort, it would be an improvement over having to create anonymized client databases or writing ad-hoc SQL to pump up tables.	[reply]