[odb-users] Re: Can one use ODB with Classes generated by XSD?

[Boris Kolpackov]

Hi Brian,

B Hart <bhartsb at gmail.com> writes:

> I have a large Schema that I compiled with XSD (tree).  This allows me to
> nicely read in corresponding XML data.  Now I'm faced with the task of
> populating an existing DB with the data.  However, their are difficulties:
> 1) There are new elements that are not currently stored in the DB, so tables
> and columns will have to be manually added, 2) there is not a nice mapping
> between all of the Schema elements and the corresponding DB
> table/column...i.e.. some of the element data may have to be modified or
> combined to go into a DB field, and the document that specifies the mappings
> is incomplete (this means I have to look through hundreds of tables to
> "figure out" where data goes, for hundreds of elements).  3) Once I figure
> out the mapping I have to then add the code manually to populate the DB with
> the data.

Yes, I think this is a fairly common problem when trying to import data
from an XML vocabulary to a relational database, unless the XML vocabulary
was specifically designed with that conversion in mind.


> The DB is a MS SQL DB and right now ODB doesn't support MS SQL. However, it
> might be worth switching to MySQL if it were possible and reasonable to run
> ODB against the classes that XSD creates in order to create a correspond
> MySQL DB Schema and the code to populate it.  Then I could read in the XML
> dataset with the XSD generated code and populate and work with the DB with
> ODB generated code.

The problem with automatically storing XSD-generated object model in
a relational database using ODB is that the conversion is not well
define and in fact is not always possible. It is not clear whether,
say, a nested element should be mapped to a column (value type) or
a reference to another table (object) with the contents of this
element stored in that table. Some elements can be stored as either 
composite value types or as objects. Those that have more than two
levels of sequence containment can only be stored as objects.


> What are your thoughts.

I see two possible approaches here, depending on how closely the
XML vocabulary models the database representation.

1. If XML and database models are very different (as in the case you
   described above), then the best approach would probably be to have two
   object models (sets of C++ classes): the first is for XML (generated
   by the XSD compiler) and the second is for the database (hand-written
   or auto-generated by ODB SQL-to-C++ compiler from the existing schema,
   something that is on our TODO list). Once you have the two models, you
   manually write the code that convert between the two, such as
   performing merging and splitting of members, etc.

2. If XML models the database pretty closely, then you can take the
   XSD-generated model and map it (using ODB pragmas) to the database
   tables (those can be placed into a separate file and included into
   the ODB compilation with the --odb-epilogue option). Once that is
   done, you can just load the classes from XML and store them into
   the RDBMS.

Boris