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

[Boris Kolpackov]

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