Database-backed Application

Introduction

The mod_db module provides several XML-to-RDBMS accessors. In this example we will create a web-based address book application. In building the application we will explore the RDBMS accessors and also show the use of server-side XForm processing. We will also demonstrate the XRL pull runtime and show how it can be used to decouple an external web-address space from the internal implementation address space.

This trailmap is a step-by-step guide. If at any time you get out of step or find things not working you can download and install the completed application using the install wizard and examine it's source code after unzipping it - you will have to perform the Database admin steps outlined below to set up your backend address database.

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 mod_db, session and xrl modules which we will use in our application. We create rewrite rules which map external requests to the XRL mapper accessor and add/reconnect a session resource to every request. We create and register a SessionPolicy and links definition.

1. 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.
2. Import the ext_session, ext_xrl, and mod_xforms module into your new module. Edit the module.xml and add the following imports to the mapping section.
   <mapping> ...
     <import>
       <uri>urn:org:ten60:netkernel:ext:session</uri>
     </import>
     <import>
       <uri>urn:org:ten60:netkernel:ext:xquery</uri>
     </import>
     <import>
       <uri>urn:org:ten60:netkernel:ext:xrl</uri>
     </import>
     <import>
       <uri>urn:org:ten60:netkernel:mod:xforms</uri>
     </import>
   </mapping>
3. 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>
     <rule>
       <match>(active:mapper.*)</match>
       <to>sessionmapper:$1</to>
     </rule>
   </rewrite>

   The first 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 (it's use will become clear shortly).

   The second rule applies a session resource to all requests. It matches the results of the first rule and maps to the sessionmapper. The sessionmapper adds/reconnects a session to a request and then reissues the original request, in this case it attaches a session and then makes the request to the mapper.

4. Create a SessionPolicy.xml document (shown below) in the root of your module. This document is the Session policy read by the session mapper - it declares one or more session zones that will be mapped to sessions, in this case a session will be applied to the /addressbook/ path
   <SessionPolicy>
     <zone>
       <match>.*/addressbook/.*</match>
       <path>/addressbook/</path>
       <token>cookie</token>
       <type>
         <simple />
       </type>
     </zone>
   </SessionPolicy>
5. 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>
6. Register the SessionPolicy.xml document and 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:/(SessionPolicy.xml|links.xml|etc/.*)</match>
   </this>
   Lastly add a <super/> declaration as the last item in the mapping section - this ensures we can go up the request super stack (we'll need this later to request the HTTPredirect accesor supplied from the tpt_http module in the frontend fulcrum).
7. Test your configuration. Cold restartNetKernel 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

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! We will assume you have a MySQL 4.x database though it should be straigtforward to port the SQL to other RDBMS. Run these SQL statements as root in your MySQL DB. 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

Testing your DB Connection

We now need to test the configuration of your Database. Change the resources/index.idoc to the following...

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.