FAQ
I am looking at a way to export and import data from a Derby
database. Built into Derby itself is a tool which exports CSV or tab
delimited data, but this has a range of problems with binary data and
with compatibility across platforms and different databases, as well
as across upgrades of the schema.

Another option is to use Cayenne itself to perform the migration,
with an XML dataNode as the repository for the data. I know that EOF
does this sort of thing natively, but I don't believe XML datanodes
are available in Cayenne. Has anyone attempted something similar with
one of the JDBC-XML drivers (for example: https://xlsql.dev.java.net/)?

Speed is likely to be awful, but that is OK for this purpose. It also
comes to mind that this approach might be a great way to write unit
tests for our system, testing the code against a standard set of
sample data.


Thanks
Ari Maniatis




-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A

Search Discussions

  • Ahmed Mohombe at Mar 9, 2007 at 10:30 am
    I am looking at a way to export and import data from a Derby database.
    Take a look at DDLUtils:
    http://db.apache.org/ddlutils/

    Even if not perfect, from all the solutions, it seems so far to be the best
    inter- and intra- DB data migration tool.

    Ahmed.
  • Aristedes Maniatis at Mar 9, 2007 at 11:21 am

    On 09/03/2007, at 9:29 PM, Ahmed Mohombe wrote:

    I am looking at a way to export and import data from a Derby
    database.
    Take a look at DDLUtils:
    http://db.apache.org/ddlutils/

    Even if not perfect, from all the solutions, it seems so far to be
    the best
    inter- and intra- DB data migration tool.

    Ahmed.
    I did look at that, but it designed as a schema export/import tool,
    not a data import/export tool. Yes, it has some similar
    functionality, but it is really very primitive. Has anyone done
    anything like this into XML or a set of SQL statements?

    Ari





    -------------------------->
    Aristedes Maniatis
    phone +61 2 9660 9700
    PGP fingerprint 08 57 20 4B 80 69 59 E2 A9 BF 2D 48 C2 20 0C C8
  • Ahmed Mohombe at Mar 9, 2007 at 11:32 am

    I am looking at a way to export and import data from a Derby database.
    Take a look at DDLUtils:
    http://db.apache.org/ddlutils/

    Even if not perfect, from all the solutions, it seems so far to be the
    best
    inter- and intra- DB data migration tool.

    Ahmed.
    I did look at that, but it designed as a schema export/import tool, not
    a data import/export tool.
    Data too.
    Than try(not just look) the example ANT tasks -> first the DDLs if there's no target schema,
    and than the data.
    Yes, it has some similar functionality, but
    it is really very primitive.
    It's not primitive - it's just simple and it works.
    It's just an API and a set of ANT tasks to do the job - no GUI (yet).

    Why should it be complicated? Using the examples I was able to adapt them my project
    needs in just 10 minutes.

    Ahmed.
  • Adrian Wiesmann at Mar 9, 2007 at 11:43 am

    I did look at that, but it designed as a schema export/import tool,
    not a data import/export tool. Yes, it has some similar
    functionality, but it is really very primitive. Has anyone done
    anything like this into XML or a set of SQL statements?
    I did something like that with Cayenne for our SOBF Tool. I am using the
    Cayenne XML Import/Export features. Every table gets exported in a
    separate xml file. All these files together get merged into a single zip
    together with a manifest file. Like that you can have versionised data
    dumps. As long as the schema does not change you simply read and write
    those files with the Cayenne functionality. If the version changes you
    first run a transformation to structure the data as expected and
    afterwards use the Cayenne functionality.

    hth,
    Adrian
  • Peter Karich at Mar 10, 2007 at 2:17 pm
    Hallo!

    I think xstream is very easy to use and it works fine for me.

    E.g. use:

    XStream xs = new XStream(new DomDriver());
    //Xpp instead dom would be faster ...

    OutputStream os;

    try {
    os = new FileOutputStream(xmlFile);
    xs.toXML(obj, os);
    } finally {
    os.close();
    }

    For deep object dependency I run into memory problems, but
    for simple property based object and one or two relations it is very
    good and fast.

    Bye, Peter.





    ___________________________________________________________
    Der frühe Vogel fängt den Wurm. Hier gelangen Sie zum neuen Yahoo! Mail: http://mail.yahoo.de
  • Aristedes Maniatis at Mar 11, 2007 at 1:12 am
    For the mail list archives, here are some approaches suggested (thank
    you!) that we will be investigating further:

    * Connect Cayenne to an XML database source [1] and using a Cayenne
    script called cdataport move data [2] between the main SQL database
    and the XML 'database'. Looks like we'd need to write a Cayenne db
    adapter to do this. Pretty neat though and other options such as
    Excel files might also be options.

    * Write some code using Cayenne's XML import/export features [3].
    Lots of control over the output. But a bit more work upfront.

    * xstream is a project designed to move java objects to XML and back
    [4]. Very simple and it produces nice looking XML. Plenty of nice
    features like being able to write Converters which understand how to
    deal with specific field types. Somewhat similar is Apache Xmlbeans. [5]

    * dbunit [6] This is a toolset specifically designed for testing -
    taking a JDBC data source, moving it into XML and then being able to
    restore it on demand. Then it can tie into junit to allow tests to be
    performed on the system with the database in a known state, checking
    to the see if the final state of the database is as expected. Pretty
    nice idea. This is the one option which would effectively bypass
    Cayenne and make direct JDBC connections of its own.

    Thanks for everyone's suggestions.

    Ari Maniatis


    [1] http://xlsql.dev.java.net
    [2] http://thread.gmane.org/gmane.comp.apache.db.derby.user/2655/
    focus=2739
    [3] http://somap.svn.sourceforge.net/viewvc/somap/sobf_tool/src/org/
    somap/sobf/database/backuprestore/Backup.java?revision=175&view=markup
    [4] http://xstream.codehaus.org
    [5] http://xmlbeans.apache.org
    [6] http://dbunit.sourceforge.net/

    -------------------------->
    Aristedes Maniatis
    phone +61 2 9660 9700
    PGP fingerprint 08 57 20 4B 80 69 59 E2 A9 BF 2D 48 C2 20 0C C8

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedMar 9, '07 at 10:25a
activeMar 11, '07 at 1:12a
posts7
users5
websitecayenne.apache.org

People

Translate

site design / logo © 2022 Grokbase