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

[B Hart]

Thanks for your explanation. Where you say "(those can be placed into a
separate file and included into
  the ODB compilation with the --odb-epilogue option)." do you mean the
xsd-generated classes with pragmas?

So hypothetically, if I decided to create a whole new DB (based on the XML
Schema and using ODB), how would I best use the XSD-generated classes with
ODB to do this?  Will ODB create a complete DB from the XSD-generated
output?  I understand that ODB doesnt' support MS SQL currently so I am
assuming the use MySQL in this case.

-Brian



On Tue, Jul 26, 2011 at 6:37 AM, Boris Kolpackov <boris at codesynthesis.com>wrote:

> 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
>