Hi Brian,
Post by B HartAs 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.
Post by B HartI 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.
Post by B HartAlso, I'm wondering if item #2 below has been implemented?
Yes, wrappers and the NULL value semantics are supported.
Boris