You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@xmlbeans.apache.org by Philip Mark Donaghy <ph...@yahoo.com> on 2004/12/03 23:25:37 UTC
Creating a Jakarta Commons SQL datamodel using XmlBeans and Velocity
Greetings everyone,
I'm starting to get some pretty good results from my
sample xmlbeans app. I'm posting my documentation for
anyone who may be interested. How should I go about
contributing it?
I tried "cvs diff -u > sample.patch" but that resulted
in a two line file,
? sample.patch
? samples/sql
Doc follows. -Phil
About Vxsdb
-----------
Vxsdb is a sample XmlBeans application which derives a
Jakarta Commons SQL data model from an xml schema. It
was conceived from things I have learned from using
XmlBeans and talking to people at the ASF conference.
Vxsdb uses Jakarta Velocity as its templating engine.
At runtime a SchemaTypeSystem XmlBeans object is
created from compiling an xml schema. This object is
placed in the Velocity context. A template processes
the objects global elements, global types and derives
tables and columns which become a Jakarta Commons SQL
datamodel.
Getting Started
---------------
The basic utilization compiles the sample and produces
a database schema for a given xml schema.
Execute the command �ant�
This compiles the sample.
Execute the command �run�
This creates the Jakarta Commons SQL datamodel.xml and
creates the create.sql ddl.
Configuration
-------------
Modifying the command line parameters to specify
another template, output or xml schema.
run -Dtemplate=datamodel.vm -Doutput=database.xml
-Dschema=easypo.xsd
Generating database schema
--------------------------
Included in the sample run.xml is an Ant target which
generates the ddl used to create the database tables.
The default database is PostgreSQL.
ant -f run.xml create-sql
To change the target database use,
ant -f run.xml create-sql -DtargetDatabase=oracle
Determining Tables, Columns, and Table relationships
----------------------------------------------------
Tables are created from all global elements and most
global types.
For each schema property the sample application
determines if it is a column, an exported key (foreign
key to another table), or an imported foreign key
(another entity who's table contains a foreign back to
this entity).
If a schema property (attribute or element) is a
simple type without restriction a column is created
and one of the following Commons SQL values is
assigned to the property. DATE, DECIMAL, FLOAT,
INTEGER, TIME, TIMESTAMP, or VARCHAR.
If a schema property is a simple type with a
restriction then a column is created and one of the
above values is assigned with a size attribute.
If a schema property is a complex type with no
maxOccurs or minOccurs (meaning that it is required)
then a foreign key column is entered into the database
table descriptor and the required attribute is set to
true.
If a schema property is a complex type with maxOccurs
equal to one then a foreign key column is entered into
the database table.
If a schema property is a complex type with maxOccurs
greater than one or unbounded then a imported key
(foreign key from this type's table) is required. This
presents a problem. The Velocity template may have
already encountered this complex type and created its
table descriptor without even knowing about this
relationship. This is known as the Imported Key
Question.
Solving the Imported Key Question
---------------------------------
Iterate the SchemaTypeSystem in the Java class to
determine imported keys.
Cliff told me that ms has a solution to this which may
be included in the xml schema.
Additional ideas
----------------
Combining XmlBeans and Velocity templates can result
in other interesting xml schema related technologies.
Using a similar approach to this sample XmlBeans and
Velocity one can harness the xml schema and generate
documentation, a relational database model, or even
data access objects.
Sample datamodel.xml
--------------------
<?xml version="1.0" encoding="UTF-8"?>
<database name="sample">
<!-- Global Elements -->
<table name="PURCHASE_ORDER">
<column name="PK" type="INTEGER"
required="true" primaryKey="true"/>
<!-- T=customer@http://openuri.org/easypo -->
<column name="FK_CUSTOMER" required="true"
type="INTEGER" />
<foreign-key foreignTable="CUSTOMER">
<reference local="FK_CUSTOMER"
foreign="PK"/>
</foreign-key>
<!--
T=dateTime@http://www.w3.org/2001/XMLSchema -->
<column name="DATE" type="TIMESTAMP" />
<!-- Foreign key imported from table named
LINE_ITEM -->
<!-- T=shipper@http://openuri.org/easypo -->
<column name="FK_SHIPPER" type="INTEGER" />
<foreign-key foreignTable="SHIPPER">
<reference local="FK_SHIPPER"
foreign="PK"/>
</foreign-key>
</table>
<!-- Global Types -->
<table name="CUSTOMER">
<column name="PK" type="INTEGER"
required="true" primaryKey="true"/>
<!-- T=string@http://www.w3.org/2001/XMLSchema
-->
<column name="NAME" type="VARCHAR" />
<!-- T=string@http://www.w3.org/2001/XMLSchema
-->
<column name="ADDRESS" type="VARCHAR" />
<!-- T=int@http://www.w3.org/2001/XMLSchema
-->
<column name="AGE" type="TINYINT" />
<!-- T=int@http://www.w3.org/2001/XMLSchema
-->
<column name="MOO" type="TINYINT" />
<!-- T=int@http://www.w3.org/2001/XMLSchema
-->
<column name="POO" type="TINYINT" />
</table>
<table name="LINE_ITEM">
<column name="PK" type="INTEGER"
required="true" primaryKey="true"/>
<!-- T=string@http://www.w3.org/2001/XMLSchema
-->
<column name="DESCRIPTION" type="VARCHAR" />
<!--
T=decimal@http://www.w3.org/2001/XMLSchema -->
<column name="PER_UNIT_OUNCES" type="DECIMAL"
/>
<!--
T=decimal@http://www.w3.org/2001/XMLSchema -->
<column name="PRICE" type="DECIMAL" />
<!--
T=integer@http://www.w3.org/2001/XMLSchema -->
<column name="QUANTITY" type="INTEGER" />
<!-- Exported key -->
<column name="FK_PURCHASE_ORDER"
required="true" type="INTEGER" />
<foreign-key foreignTable="PURCHASE_ORDER">
<reference local="FK_PURCHASE_ORDER"
foreign="PK"/>
</foreign-key>
</table>
<table name="SHIPPER">
<column name="PK" type="INTEGER"
required="true" primaryKey="true"/>
<!-- T=string@http://www.w3.org/2001/XMLSchema
-->
<column name="NAME" type="VARCHAR" />
<!--
T=decimal@http://www.w3.org/2001/XMLSchema -->
<column name="PER_OUNCE_RATE" type="DECIMAL"
/>
</table>
</database>
Sample create.sql
-----------------
drop table SHIPPER;
drop table LINE_ITEM;
drop table CUSTOMER;
drop table PURCHASE_ORDER;
--
-----------------------------------------------------------------------
-- PURCHASE_ORDER
--
-----------------------------------------------------------------------
create table PURCHASE_ORDER
(
PK INTEGER NOT NULL ,
FK_CUSTOMER INTEGER NOT NULL ,
DATE TIMESTAMP NULL ,
FK_SHIPPER INTEGER NULL ,
PRIMARY KEY (PK)
);
ALTER TABLE PURCHASE_ORDER
ADD CONSTRAINT PURCHASE_ORDER_FK_1 FOREIGN KEY
(FK_CUSTOMER)
REFERENCES CUSTOMER (PK)
;
ALTER TABLE PURCHASE_ORDER
ADD CONSTRAINT PURCHASE_ORDER_FK_2 FOREIGN KEY
(FK_SHIPPER)
REFERENCES SHIPPER (PK)
;
--
-----------------------------------------------------------------------
-- CUSTOMER
--
-----------------------------------------------------------------------
create table CUSTOMER
(
PK INTEGER NOT NULL ,
NAME VARCHAR NULL ,
ADDRESS VARCHAR NULL ,
AGE TINYINT NULL ,
MOO TINYINT NULL ,
POO TINYINT NULL ,
PRIMARY KEY (PK)
);
--
-----------------------------------------------------------------------
-- LINE_ITEM
--
-----------------------------------------------------------------------
create table LINE_ITEM
(
PK INTEGER NOT NULL ,
DESCRIPTION VARCHAR NULL ,
PER_UNIT_OUNCES DECIMAL NULL ,
PRICE DECIMAL NULL ,
QUANTITY INTEGER NULL ,
FK_PURCHASE_ORDER INTEGER NOT NULL ,
PRIMARY KEY (PK)
);
ALTER TABLE LINE_ITEM
ADD CONSTRAINT LINE_ITEM_FK_1 FOREIGN KEY
(FK_PURCHASE_ORDER)
REFERENCES PURCHASE_ORDER (PK)
;
--
-----------------------------------------------------------------------
-- SHIPPER
--
-----------------------------------------------------------------------
create table SHIPPER
(
PK INTEGER NOT NULL ,
NAME VARCHAR NULL ,
PER_OUNCE_RATE DECIMAL NULL ,
PRIMARY KEY (PK)
);
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@xmlbeans.apache.org
For additional commands, e-mail: dev-help@xmlbeans.apache.org
Re: Creating a Jakarta Commons SQL datamodel using XmlBeans and Velocity
Posted by Jim McLaughlin <me...@yahoo.com>.
Did you post this application yet?
I have some interest in this area as well.
I have utilized the Axgen and AndroMDA approaches but they have problems with models that are based on packages like XMLBeans.
Thanks in advance.
Philip Mark Donaghy <ph...@yahoo.com> wrote:
Greetings everyone,
I'm starting to get some pretty good results from my
sample xmlbeans app. I'm posting my documentation for
anyone who may be interested. How should I go about
contributing it?
I tried "cvs diff -u > sample.patch" but that resulted
in a two line file,
? sample.patch
? samples/sql
Doc follows. -Phil
About Vxsdb
-----------
Vxsdb is a sample XmlBeans application which derives a
Jakarta Commons SQL data model from an xml schema. It
was conceived from things I have learned from using
XmlBeans and talking to people at the ASF conference.
Vxsdb uses Jakarta Velocity as its templating engine.
At runtime a SchemaTypeSystem XmlBeans object is
created from compiling an xml schema. This object is
placed in the Velocity context. A template processes
the objects global elements, global types and derives
tables and columns which become a Jakarta Commons SQL
datamodel.
Getting Started
---------------
The basic utilization compiles the sample and produces
a database schema for a given xml schema.
Execute the command �ant�
This compiles the sample.
Execute the command �run�
This creates the Jakarta Commons SQL datamodel.xml and
creates the create.sql ddl.
Configuration
-------------
Modifying the command line parameters to specify
another template, output or xml schema.
run -Dtemplate=datamodel.vm -Doutput=database.xml
-Dschema=easypo.xsd
Generating database schema
--------------------------
Included in the sample run.xml is an Ant target which
generates the ddl used to create the database tables.
The default database is PostgreSQL.
ant -f run.xml create-sql
To change the target database use,
ant -f run.xml create-sql -DtargetDatabase=oracle
Determining Tables, Columns, and Table relationships
----------------------------------------------------
Tables are created from all global elements and most
global types.
For each schema property the sample application
determines if it is a column, an exported key (foreign
key to another table), or an imported foreign key
(another entity who's table contains a foreign back to
this entity).
If a schema property (attribute or element) is a
simple type without restriction a column is created
and one of the following Commons SQL values is
assigned to the property. DATE, DECIMAL, FLOAT,
INTEGER, TIME, TIMESTAMP, or VARCHAR.
If a schema property is a simple type with a
restriction then a column is created and one of the
above values is assigned with a size attribute.
If a schema property is a complex type with no
maxOccurs or minOccurs (meaning that it is required)
then a foreign key column is entered into the database
table descriptor and the required attribute is set to
true.
If a schema property is a complex type with maxOccurs
equal to one then a foreign key column is entered into
the database table.
If a schema property is a complex type with maxOccurs
greater than one or unbounded then a imported key
(foreign key from this type's table) is required. This
presents a problem. The Velocity template may have
already encountered this complex type and created its
table descriptor without even knowing about this
relationship. This is known as the Imported Key
Question.
Solving the Imported Key Question
---------------------------------
Iterate the SchemaTypeSystem in the Java class to
determine imported keys.
Cliff told me that ms has a solution to this which may
be included in the xml schema.
Additional ideas
----------------
Combining XmlBeans and Velocity templates can result
in other interesting xml schema related technologies.
Using a similar approach to this sample XmlBeans and
Velocity one can harness the xml schema and generate
documentation, a relational database model, or even
data access objects.
Sample datamodel.xml
--------------------
required="true" primaryKey="true"/>
type="INTEGER" />
foreign="PK"/>
T=dateTime@http://www.w3.org/2001/XMLSchema -->
LINE_ITEM -->
foreign="PK"/>
required="true" primaryKey="true"/>
-->
-->
-->
-->
-->
required="true" primaryKey="true"/>
-->
T=decimal@http://www.w3.org/2001/XMLSchema -->
/>
T=decimal@http://www.w3.org/2001/XMLSchema -->
T=integer@http://www.w3.org/2001/XMLSchema -->
required="true" type="INTEGER" />
foreign="PK"/>
required="true" primaryKey="true"/>
-->
T=decimal@http://www.w3.org/2001/XMLSchema -->
/>
Sample create.sql
-----------------
drop table SHIPPER;
drop table LINE_ITEM;
drop table CUSTOMER;
drop table PURCHASE_ORDER;
--
-----------------------------------------------------------------------
-- PURCHASE_ORDER
--
-----------------------------------------------------------------------
create table PURCHASE_ORDER
(
PK INTEGER NOT NULL ,
FK_CUSTOMER INTEGER NOT NULL ,
DATE TIMESTAMP NULL ,
FK_SHIPPER INTEGER NULL ,
PRIMARY KEY (PK)
);
ALTER TABLE PURCHASE_ORDER
ADD CONSTRAINT PURCHASE_ORDER_FK_1 FOREIGN KEY
(FK_CUSTOMER)
REFERENCES CUSTOMER (PK)
;
ALTER TABLE PURCHASE_ORDER
ADD CONSTRAINT PURCHASE_ORDER_FK_2 FOREIGN KEY
(FK_SHIPPER)
REFERENCES SHIPPER (PK)
;
--
-----------------------------------------------------------------------
-- CUSTOMER
--
-----------------------------------------------------------------------
create table CUSTOMER
(
PK INTEGER NOT NULL ,
NAME VARCHAR NULL ,
ADDRESS VARCHAR NULL ,
AGE TINYINT NULL ,
MOO TINYINT NULL ,
POO TINYINT NULL ,
PRIMARY KEY (PK)
);
--
-----------------------------------------------------------------------
-- LINE_ITEM
--
-----------------------------------------------------------------------
create table LINE_ITEM
(
PK INTEGER NOT NULL ,
DESCRIPTION VARCHAR NULL ,
PER_UNIT_OUNCES DECIMAL NULL ,
PRICE DECIMAL NULL ,
QUANTITY INTEGER NULL ,
FK_PURCHASE_ORDER INTEGER NOT NULL ,
PRIMARY KEY (PK)
);
ALTER TABLE LINE_ITEM
ADD CONSTRAINT LINE_ITEM_FK_1 FOREIGN KEY
(FK_PURCHASE_ORDER)
REFERENCES PURCHASE_ORDER (PK)
;
--
-----------------------------------------------------------------------
-- SHIPPER
--
-----------------------------------------------------------------------
create table SHIPPER
(
PK INTEGER NOT NULL ,
NAME VARCHAR NULL ,
PER_OUNCE_RATE DECIMAL NULL ,
PRIMARY KEY (PK)
);
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@xmlbeans.apache.org
For additional commands, e-mail: dev-help@xmlbeans.apache.org
---------------------------------
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.