First of all, we will design the database model. (If a database model already existed, then we could skip this task and simply re-use the existing model). DbForms has no special requirements for the database model. Here is a simple model which addresses the requirements defined above.
The following database model will be used in this tutorial.
Here is the SQL code for creating the database model shown in the preceeding section. The example is for a MySQL database.
Example 7.1. SQL code for creating the database tables
CREATE TABLE services
(
id int NOT NULL PRIMARY KEY,
name char (30) NULL,
description char (255) NULL
);
CREATE TABLE customers
(
id int NOT NULL PRIMARY KEY,
firstname char (50) NULL,
lastname char (50) NULL,
address char (30) NULL,
pcode char (10) NULL,
city char (40) NULL
);
CREATE TABLE orders
(
order_id int AUTO_INCREMENT,
customer_id int NOT NULL,
service_id int NULL,
orderdate char (20) NULL,
PRIMARY KEY ( order_id, customer_id )
);
CREATE TABLE complaints
(
complaint_id int AUTO_INCREMENT,
customer_id int NOT NULL,
usermessage char (255) NULL,
incomingdate char (20) NULL,
priority int NULL,
PRIMARY KEY ( complaint_id, customer_id )
);
CREATE TABLE priorities
(
id int NOT NULL PRIMARY KEY,
shortname char (12) NULL,
description char (100) NULL
);
Remarks
It would be desirable to add referential integrity, by adding foreign keys and/or triggers) but to keep things simple for this tutorial on DbForms capabilities, we have not included them.
The next step is to create the XML-based description of this model, as follows.
Example 7.2. Defining tables in dbforms-config.xml
<?xml version="1.0" encoding="ISO-8859-1" ?>
<dbforms-config>
<table name="services">
<field name="id" fieldType="int" isKey="true" />
<field name="name" fieldType="char" />
<field name="description" fieldType="char" />
</table>
<table name="customers">
<field name="id" fieldType="int" isKey="true" />
<field name="firstname" fieldType="char" />
<field name="lastname" fieldType="char" />
<field name="address" fieldType="char" />
<field name="pcode" fieldType="char" />
<field name="city" fieldType="char" />
</table>
<table name="orders">
<field name="order_id" fieldType="int" isKey="true"
autoInc="true" />
<field name="customer_id" fieldType="int" isKey="true" />
<field name="service_id" fieldType="char" sortable="true"/>
<field name="orderdate" fieldType="char" sortable="true"/>
</table>
<table name="complaints">
<field name="complaint_id" fieldType="int" isKey="true"
autoInc="true" />
<field name="customer_id" fieldType="int" isKey="true" />
<field name="usermessage" fieldType="char" />
<field name="incomingdate" fieldType="char" sortable="true"/>
<field name="priority" fieldType="int" sortable="true"/>
</table>
<table name="priorities">
<field name="id" fieldType="int" isKey="true" />
<field name="shortname" fieldType="char" />
<field name="description" fieldType="char" />
</table>
<!--
<dbconnection
name = "java:comp/env/jdbc/tutorial"
isJndi = "true"
/>
-->
<dbconnection
isPow2 = "true"
connectionProviderClass =
"org.dbforms.conprovider.SingleConnectionProvider"
name =
"jdbc:hsqldb:$(SERVLETCONTEXT_REALPATH)/WEB-INF/db_hsql/tutorial"
isJndi = "false"
conClass = "org.hsqldb.jdbcDriver"
username = "sa"
password = ""
/>
</dbforms-config>
Remarks
There exists a DbForms tool which does all the painful work of transferring a database schema into XML format. See Chapter 5, DevGui for more information.
This example includes samples of two
db:dbconnection
elements. Refer to
Chapter 2, DbForms Concepts
for a discussion of the dbconnection element. The example
that is not commented out uses an in-memory HSQL database that
requires no setup time to run.