Extract test data - filter by query and only related rows in dependent tables

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Extract test data - filter by query and only related rows in dependent tables

James Brook
Can anyone tell me whether the following is possible please? I want to extract a test data set from a snapshot of my production database. The set I want to get is products that belong to a particular customer and then the rows in the dependent tables that are related to the product rows by foreign key, then their related rows in deeper tables, etc. The graph is effectively a tree. I would like to limit the edges that I traverse with a whitelist of the tables that the foreign key comes from. I do not want all the rows in the dependent table but only those owned by the top level products that I have filtered out with my query. Is it possible to do this with the high level DBUnit abstractions or do I need to write custom code?

I have seen a project called Jailer which seems to be able to achieve this but I would love to be able to do this with some DBUnit code. I am just not sure if it is possible without things becoming complicated.

Any help would be much appreciated.


James

------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Extract test data - filter by query and only related rows in dependent tables

Mike Kienenberger
I do this with large numbers of hand-maintained export statements using dbunit ant tasks.

    <target name="downloadData" description="download data...">

           <dbunit
            driver="${jdbc.driver.source}"
            classpath="${jdbc.lib.source}"
            url="${jdbc.url.source}"
            schema="${jdbc.schema.source}"
            userid="${jdbc.user.source}"
            password="${jdbc.password.source}">


            <export dest="${snapshot.directory}/TABLE1-MIN_${target.partitioning_key}.xml" format="flat">
                <query name="SCHEMA.TABLE2" sql="SELECT t2.* FROM SCHEMA.TABLE2 t2 join SCHEMA.TABLE1 t1 on t2.id = t1.id
 where t1.partitioning_key = ${target.partitioning_key} order by t2.id"/>
            </export>

            [...repeat <export...]

I'd be interested in hearing a better way to do it, but if your schema doesn't change too frequently this can work.


On Mon, Mar 23, 2015 at 4:03 PM, James Brook <[hidden email]> wrote:
Can anyone tell me whether the following is possible please? I want to extract a test data set from a snapshot of my production database. The set I want to get is products that belong to a particular customer and then the rows in the dependent tables that are related to the product rows by foreign key, then their related rows in deeper tables, etc. The graph is effectively a tree. I would like to limit the edges that I traverse with a whitelist of the tables that the foreign key comes from. I do not want all the rows in the dependent table but only those owned by the top level products that I have filtered out with my query. Is it possible to do this with the high level DBUnit abstractions or do I need to write custom code?

I have seen a project called Jailer which seems to be able to achieve this but I would love to be able to do this with some DBUnit code. I am just not sure if it is possible without things becoming complicated.

Any help would be much appreciated.


James

------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user