Introduction
The mod-db module provides several accessors for querying and updating relational databases. In this example we will create an
address book web-application. In building the application we will explore the RDBMS accessors and also
show RESTful stateless application design. We will also demonstrate the XRL language runtime and show how it can be
used to decouple an external web-address space from the internal implementation address space with fine-grained caching - a step-by-step
introduction to XRL is provided here you should review this first.
Installation
To save you work the application is pre-installed in the front-end-fulcrum on port 8080.
All you need to do is run setup
and specify a
directory to use for storing the database. You can then try the application here
.
Specification
We will create an address book application. The address book will store surname, firstname, address, multiple telephone
numbers and multiple email addresses. The address book will provide a new entry form, search and browse.
Step1: Create and configure a module
Here we will create and configure a module to host the application. We import the modules
which we will use in our application. We create rewrite rules which map external requests to the XRL mapper accessor.
-
Create a new module with the new module wizard
. Call it "app_address_book". Import
the module into the frontend fulcrum and make sure the external path is /addressbook/
, keep the default internal
address path. Include DPML and entrypoints but disable the Accessor option.
-
Edit the module.xml and add the following module library imports
to the mapping section.
<mapping>
...
<import>
<uri>urn:org:ten60:netkernel:ext:layer1</uri>
</import>
<import>
<uri>urn:org:ten60:netkernel:ext:xml:core</uri>
</import>
<import>
<uri>urn:org:ten60:netkernel:ext:xml:ura</uri>
</import>
<import>
<uri>urn:org:ten60:netkernel:ext:xquery</uri>
</import>
<import>
<uri>urn:org:ten60:netkernel:ext:sys</uri>
</import>
<import>
<uri>urn:org:ten60:netkernel:mod:db</uri>
</import>
<import>
<uri>urn:org:ten60:netkernel:ext:xrl</uri>
</import>
<import>
<uri>urn:org:ten60:netkernel:ext:dpml</uri>
</import>
<import>
<uri>urn:org:ten60:netkernel:ext:script</uri>
</import>
<import>
<uri>urn:org:ten60:netkernel:lib:xlib</uri>
</import>
...
</mapping>
-
Delete the default rewrite rules for the module. Replace with the following
<rewrite>
<rule>
<match>(ffcpl:/addressbook/.*)</match>
<to>active:mapper+operand@$1+operator@ffcpl:/links.xml</to>
</rule>
</rewrite>
This rule takes all requests for resources in the ffcpl:/addressbook/ path and makes them active:mapper
requests. Mapper
is an XRL accessor - it uses a links document to map from an external URI to an internal URI in doing this it decouples the external addresses
from the internal addresses (its use will become clear shortly).
-
Create a links.xml document (below) in the root of your module. This document will be used by the mapper accessor to resolve internal
and external links. This links file contains a mapping from the external REST path /addressbook/test to an internal active URI active:dpml... which executes
the index.idoc using the DPML runtime.
<links basepath="ffcpl:/addressbook/">
<link>
<name>test</name>
<ext>/test</ext>
<int>active:dpml+operand@ffcpl:/resources/index.idoc</int>
</link>
</links>
-
Register the links.xml document in the internal address space of the module. Do this by adding a
<this>
declaration (below) to the end of the mapping section of the module definition. While we're at it we've added the etc/ path as well - we'll
use this as the path for the Database accessor configuration in the next section.
<this>
<match>ffcpl:/(links.xml|etc/.*)</match>
</this>
-
Test your configuration. Cold restart
NetKernel to pick up the changes you've made to the module definition.
To test your config try http://localhost:8080/addressbook/test
. You should see a confirmation
that the module is configured correctly. If you have problems the complete module definition is available here
.
Step 2: Creating the Database
Demo Note:
The finished app uses the embedded HSQLDB and will run out of the box with zero configuration. It is recommended to install the completed module
to avoid the pain of the DB Administration!
JDBC Driver Note:
You must supply a suitable JDBC driver for your database if you are not using the shipped HSQLDB.
Please see the mod-db
guide for information on how to install this.
For this example, to keep things simple, we will create a single table database - a better approach would be to have multiple
tables for user, addresses, phone numbers, emails but this isn't a course in how to be a DBA! This example shows the
settings for a MySQL database - it should be straightforward to modify this for another RDBMS.
Run the SQL statements below as root in your RDBMS. We have assumed that the database is on the same box
as your NetKernel installation - if not you'll have to set the privileges and alter the JDBC config appropriately (below).
########################
#Set up AddressBook Database
#Run as user root in mysql database
########################
CREATE DATABASE IF NOT EXISTS addressbook;
########################
#Grant access to user addressbook with password changeme
#Change password to something more secure!
########################
GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES
ON addressbook.* TO addressbook@localhost IDENTIFIED BY 'changeme';
GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES
ON addressbook.* TO addressbook@localhost.localdomain IDENTIFIED BY 'changeme';
FLUSH PRIVILEGES;
#######################
#Set up Table
#######################
CREATE TABLE IF NOT EXISTS addressbook.entries (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),
firstname VARCHAR(100),
lastname VARCHAR(100),
add1 VARCHAR(200),
add2 VARCHAR(200),
add3 VARCHAR(200),
add4 VARCHAR(200),
region VARCHAR(100),
country VARCHAR(100),
zip VARCHAR(100),
phone1 VARCHAR(100),
phone2 VARCHAR(100),
phone3 VARCHAR(100),
fax VARCHAR(100),
email1 VARCHAR(200),
email2 VARCHAR(200),
email3 VARCHAR(200),
web1 VARCHAR(200),
web2 VARCHAR(200),
notes TEXT
);
Now we need to setup the mod_db configuration file - this will be used by the RDBMS accessors for their configuration. Create a directory
etc/ in the root of your module (we mapped this to the internal address space earlier). Add a file ConfigRDBMS.xml
<config>
<rdbms>
<jdbcDriver>com.mysql.jdbc.Driver</jdbcDriver>
<jdbcConnection>jdbc:mysql://localhost/addressbook?user=addressbook&password=changeme</jdbcConnection>
</rdbms>
</config>
Testing your DB Connection
We now need to test the configuration of your Database. Change the resources/index.idoc to the following...
<idoc>
<seq>
<instr>
<type>sqlQuery</type>
<operand>
<sql>SHOW COLUMNS FROM entries;</sql>
</operand>
<target>this:response</target>
</instr>
<instr>
<type>cast</type>
<operator>
<cast>
<mimetype>text/xml</mimetype>
</cast>
</operator>
<operand>this:response</operand>
<target>this:response</target>
</instr>
</seq>
</idoc>
Try the test link again http://localhost:8080/addressbook/test
- you should see the result set containing column descriptions in XML form.
The configuration is now complete - the application is now pretty simple to build.
[Part 2]