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

[B Hart]

Hi Boris,

Sorry about the late response, I was put on to some other tasks.

I looked over the two XSD/XML examples you provided.  In the first example,
there is a catalog of books where (potentially) a book can have may authors
and an author many books (many to many, but in this case with attribute
"recommends" it is unidirectional 1:1).  In the second example it is a
catalog of both books and authors (a little strange), with the same
attribute "recommends" expressing a unidirectional 1:1 relationship.
(BTW: In the first example, shouldn't "recommends" be an element (minOccurs
= 0 maxOccurs = unbounded), since this really isn't Metadata?  )

The XML schema I'm working with seems closer to your second example.  The
DB Schema auto-generated by XMLSpy is definitely not perfect, but with a
little minor cleanup should be 3N form.  There is a Patient Care record
(1patient = 1 record), and each record is divided into a number of
sections, and the relationships between elements are hierarchical in one
direction (down the tree) with some 1 to many relationships.    I've tried
to see where there might be relationships between elements in different
sections, but there seem to be few.  Data inserted is immutable.

So maybe I just got lucky or I haven't explored deeply enough yet.  I do
know that it was very quick for me to generate the tables and then use
Mapforce to create an extraction and load (not much transforming).  I'd
like to use ODB, but it seems like it is going to take a lot of time to
create all the mappings.

I understand it might be be difficult to auto-generate an acceptable DB
Schema in the majority of instances, but would even a poor DB Schema and
mapping (automatically generated) be a better starting point than none at
all (especially when there are going to be many tables)???  What do you
think about the idea of having a pragma that could be used to remove the
mappings in sections of XSD generated classes that weren't found to auto
generate correctly a portion of the DB schema???

>>Yes, wrappers and the NULL value semantics are supported.

Can you point me to any examples showing how wrappers are used?

Thanks.




On Thu, May 3, 2012 at 7:19 AM, Boris Kolpackov <boris at codesynthesis.com>wrote:

> Hi Brian,
>
> B Hart <bhartsb at gmail.com> writes:
>
> > As an evaluation exercise I generated a DB schema from the XSDs using
> > Altova's XMLSpy.  It generated a set of tables very reflective of the
> > organization of the XML Schemas as well as the element constraints. I'm
> > wondering if I similarly relied on ODB to generate the tables if it would
> > produce a similar DB schema, as well as the constraints based on the
> > element types?  Haven't tried it yet.
>
> ODB will generate a database schema according to how you map XSD-
> generated classes to objects, values, relationships, containers, etc.
> In fact, XML schemas that I normally see (hierarchical, deeply nested,
> container-in-container-in-container-... kind) don't match the canonical
> relational model (i.e., a model that an experienced DBA would design)
> very well. So I am quite surprised you are happy with a database schema
> generated by XMLSpy without any "mapping" input from your side. And
> that's also why I am quite skeptical that we can support a fully-
> automatic XSD->C++->DB mapping, without any user input.
>
> To illustrate my point, consider this fairly typical XML and schema
> (based on the library example from XSD):
>
> XML:
>
> <catalog>
>  <book id="MM">
>    <title>The Master and Margarita</title>
>    <author recommends="WP">
>      <name>
>        <first>Mikhail</first>
>        <last>Bulgakov</last>
>      </name>
>    </author>
>  </book>
>
>  <book id="WP">
>    <title>War and Peace</title>
>    <author recommends="MM">
>      <name>
>        <first>Leo</first>
>        <last>Tolstoy</last>
>      </name>
>    </author>
>  </book>
> </catalog>
>
>
> Schema:
>
>  <complexType name="name">
>    <sequence>
>      <element name="first" type="string"/>
>      <element name="last" type="string"/>
>    </sequence>
>  </complexType>
>
>  <complexType name="author">
>    <sequence>
>      <element name="name" type="lib:name"/>
>    </sequence>
>    <attribute name="recommends" type="IDREF"/>
>  </complexType>
>
>  <complexType name="book">
>    <sequence>
>      <element name="title" type="string"/>
>      <element name="author" type="lib:author" maxOccurs="unbounded"/>
>    </sequence>
>    <attribute name="id" type="ID" use="required"/>
>  </complexType>
>
>  <complexType name="catalog">
>    <sequence>
>      <element name="book" type="lib:book" maxOccurs="unbounded"/>
>    </sequence>
>  </complexType>
>
>  <element name="catalog" type="lib:catalog"/>
>
> How would we map something like this to a database? Is 'name' an object
> or a value (i.e., do names get their own table or are part of another
> table)? In case of a name, it is probably a value type. Answering the
> same question for 'author' is trickier (seeing that there could be
> multiple books by the same author, it should probably be an object).
> 'book' is most definitely an object. And 'catalog' probably doesn't
> have any representation in the database at all!
>
> Here is the database schema that I would design for this object model:
>
> CREATE TABLE author (
>  first_name VARCHAR(255) NOT NULL,
>  last_name VARCHAR(255) NOT NULL,
>  recommends VARCHAR(255) NULL,
>
>  PRIMARY KEY (first_name, last_name),
>  CONSTRAINT recommends_fk FOREIGN KEY (recommends) REFERENCES book (id)));
>
> CREATE TABLE book (
>  id VARCHAR(255) NOT NULL PRIMARY KEY,
>  title TEXT NOT NULL);
>
> CREATE TABLE book_author (
>  book_id VARCHAR(255) NOT NULL,
>  author_first_name VARCHAR(255) NOT NULL,
>  author_last_name VARCHAR(255) NOT NULL,
>
>  CONSTRAINT book_fk FOREIGN KEY (book_id) REFERENCES book (id)),
>  CONSTRAINT author_fk
>    FOREIGN KEY (author_first_name, author_last_name)
>    REFERENCES author (first_name, last_name)));
>
> Does it resemble the XML schema? Not really. In fact, XML and schema that
> would resemble this database schema more closely would look along these
> lines:
>
> XML:
>
> <catalog>
>
>  <authors>
>    <author id="MB" recommends="WP">
>      <name>
>        <first>Mikhail</first>
>        <last>Bulgakov</last>
>      </name>
>    </author>
>
>    <author id="LT" recommends="MM">
>      <name>
>        <first>Leo</first>
>        <last>Tolstoy</last>
>      </name>
>    </author>
>  </authors>
>
>  <books>
>    <book id="MM">
>      <title>The Master and Margarita</title>
>      <author>MB</author>
>    </book>
>
>    <book id="WP">
>      <title>War and Peace</title>
>      <author>LT</author>
>    </book>
>  </books>
>
> </catalog>
>
> Schema:
>
>  <complexType name="name">
>    <sequence>
>      <element name="first" type="string"/>
>      <element name="last" type="string"/>
>    </sequence>
>  </complexType>
>
>  <complexType name="author">
>    <sequence>
>      <element name="name" type="lib:name"/>
>    </sequence>
>    <attribute name="id" type="ID" use="required"/>
>    <attribute name="recommends" type="IDREF"/>
>  </complexType>
>
>  <complexType name="book">
>    <sequence>
>      <element name="title" type="string"/>
>      <element name="author" type="IDREF" maxOccurs="unbounded"/>
>    </sequence>
>    <attribute name="id" type="ID" use="required"/>
>  </complexType>
>
>  <complexType name="catalog">
>    <sequence>
>
>      <element name="authors">
>        <complexType>
>          <sequence>
>            <element name="author" type="lib:author" maxOccurs="unbounded"/>
>          </sequence>
>        </complexType>
>      </element>
>
>      <element name="books">
>        <complexType>
>          <sequence>
>            <element name="book" type="lib:book" maxOccurs="unbounded"/>
>          </sequence>
>        </complexType>
>      </element>
>
>    </sequence>
>  </complexType>
>
>  <element name="catalog" type="lib:catalog"/>
>
> I see schemas like the first one all the time and like the second one --
> not much.
>
> > I have written a program that with excellent help from XSD generated
> classes
> > reads in patient records in an XML file, validates the XML, and checks
> > various business rules and generates a report.  At the point after
> > validation has occurred and Business Rules are checked and pass, the
> data is
> > ready to put into the DB.  It would be nice if I could use ODB to
> generate
> > the Schema and make it happen with just a few lines of code (similar to
> how
> > easy it is with XSD to read in a complex schema and serialize it out
> > again.).
>
> The point of the above exercise is to show that I don't think we can come
> up with an auto-magical solution which will take an XML schema, generate
> C++ classes, and map them to the database, all without your DBA swearing
> at you in the end (for the all the right reasons) ;-).
>
> Instead, the generated C++ classes will have to manually and carefully
> be mapped to the database.
>
> > Also, I'm wondering if item #2 below has been implemented?
>
> Yes, wrappers and the NULL value semantics are supported.
>
> Boris
>