Specialised SQL Statements

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

Specialised SQL Statements

Dave Sunerton-Burl
I'm trying to think through an issue I have and I'm looking for some
ideas and/or help. I'm using dbUnit as a mechanism for managing data
exports/imports for an application which uses MySql. The issue I'm
having is that some of the tables contain spatial data. I have this
working using a new dbUnit data type with the spatial column coming
through as binary data. Creating the XML file is relatively fast (about
2 mins on a really big dataset), but importing the file using a REFRESH
operation is *very* slow - in excess of 2 hours sometimes. If I exclude
the spatial column from the data it works fast so I'm assuming the
binary data is slowing things down enormously.

What I'd like to be able to do is alter how the SQL statement is
generated so I can take advantage of a built-in MySQL function. I'm
assuming that, ultimately, dbUnit creates a prepared statement which
would look something like this...

INSERT INTO MyTable(id, name, geom) VALUES(?,?,?)

... and binds the values, including binary data for the "geom" column.
MySql allows you to pass spatial data as simple text using a built-in
function, so it would look like this...

INSERT INTO MyTable(id, name, geom) VALUES(?,?,GeomFromText(?))

...and you'd bind a string to the 3rd parameter (e.g. "POINT(10 20)").
MySql then does all the work and this would avoid having to use binary
data which would, presumably, speed things up a lot.

Is this possible within the existing framework? I can change the new
data type/factory to consume and produce the correct String value.

Or is there another way of speeding up the import anyone can think of?
All ideas would be most welcome!

Thanks
Dave S-B
Lledr Solutions


------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Specialised SQL Statements

Davis Ford-2
Hi Dave.  I have built similar things using Oracle Spatial -- which
also has spatial stuff that does not lend itself to being loaded in
via dbunit XML files.

See this page I wrote:
http://zenoconsulting.wikidot.com/blog:8/comments/show#post-494674  --
specifically under "How Do I Test It".  I use Spring and DbUnit
here...but what I've done in practice is pull this up into my own
AbstractDatabaseTestCase which subclasses Spring's
AbstractTransactionalDataSourceSpringContextTests.
AbstractDatabaseTestCase has one abstract method:

/** get the name of the spring bean under test */
protected String getBeanName();

So, in a real test that subclasses this, I'll just do this in the
constructor -- the superclass loads it from the spring context:

public MyDao dao;

public SomeDaoTest() { dao = (MyDao) super.getBean(); }

It has two subclass variations:

1. AbstractDbUnitDatabaseTestCase with one abstract method (I use this
for all my non-spatial tables):

/** get the name of the DbUnit XML file -- which is loaded before the test */
protected String getXmlFileName();

2. AbstractSQLDatabaseTestCase with one abstract method:

/** get a list of SQL to execute before the test */
protected String[] getSqlStatements();

So the latter test class is what I use for my spatial tables.  You can
build the SQL up by hand in the test itself, or read it in from a
file, and then use Spring's JdbcTemplate to do a batch insert before
your test case runs.  Here, I don't use DbUnit at all, but just use
JdbcTemplate to batch insert custom SQL that your test case provides.

I hope that makes sense.  It wasn't clear to me whether you were
trying to figure a good way to test with pre-populated spatial data,
or whether you were trying to use DbUnit as a batch import process.  I
wouldn't really recommend the latter.  When I write DB tests, I
typically don't need to load more than a handful of rows.  If you need
a LOT of rows, I would re-think the test.  If it is a performance
test, I think there may be other ways to address this.

Regards,
Davis

On Sun, Jun 7, 2009 at 1:37 PM, Dave S-B<[hidden email]> wrote:

> Thanks - here's a reminder...!
>
> Dave S-B
>
> Davis Ford wrote:
>>
>> Dave I have a good solution 4 u but I am in an airport typing this on an
>> iPhone.  Email me and remind me to detail it 4 u
>>
>> Sent from my iPhone
>>
>> On Jun 5, 2009, at 8:58 AM, Dave Sunerton-Burl
>> <[hidden email]> wrote:
>>
>>> I'm trying to think through an issue I have and I'm looking for some
>>> ideas and/or help. I'm using dbUnit as a mechanism for managing data
>>> exports/imports for an application which uses MySql. The issue I'm
>>> having is that some of the tables contain spatial data. I have this
>>> working using a new dbUnit data type with the spatial column coming
>>> through as binary data. Creating the XML file is relatively fast (about
>>> 2 mins on a really big dataset), but importing the file using a REFRESH
>>> operation is *very* slow - in excess of 2 hours sometimes. If I exclude
>>> the spatial column from the data it works fast so I'm assuming the
>>> binary data is slowing things down enormously.
>>>
>>> What I'd like to be able to do is alter how the SQL statement is
>>> generated so I can take advantage of a built-in MySQL function. I'm
>>> assuming that, ultimately, dbUnit creates a prepared statement which
>>> would look something like this...
>>>
>>> INSERT INTO MyTable(id, name, geom) VALUES(?,?,?)
>>>
>>> ... and binds the values, including binary data for the "geom" column.
>>> MySql allows you to pass spatial data as simple text using a built-in
>>> function, so it would look like this...
>>>
>>> INSERT INTO MyTable(id, name, geom) VALUES(?,?,GeomFromText(?))
>>>
>>> ...and you'd bind a string to the 3rd parameter (e.g. "POINT(10 20)").
>>> MySql then does all the work and this would avoid having to use binary
>>> data which would, presumably, speed things up a lot.
>>>
>>> Is this possible within the existing framework? I can change the new
>>> data type/factory to consume and produce the correct String value.
>>>
>>> Or is there another way of speeding up the import anyone can think of?
>>> All ideas would be most welcome!
>>>
>>> Thanks
>>> Dave S-B
>>> Lledr Solutions
>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> OpenSolaris 2009.06 is a cutting edge operating system for enterprises
>>> looking to deploy the next generation of Solaris that includes the latest
>>> innovations from Sun and the OpenSource community. Download a copy and
>>> enjoy capabilities such as Networking, Storage and Virtualization.
>>> Go to: http://p.sf.net/sfu/opensolaris-get
>>> _______________________________________________
>>> dbunit-user mailing list
>>> [hidden email]
>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>
>> ------------------------------------------------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database:
>> 270.12.53/2156 - Release Date: 06/05/09 06:24:00
>>
>>
>
>



--
Zeno Consulting, Inc.
home: http://www.zenoconsulting.biz
blog: http://zenoconsulting.wikidot.com
p: 248.894.4922
f: 313.884.2977

------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user