Database Application Database Application
Using NetKernel with a DB to create an Address book application
Home > Books > Tutorials and Training Guides > Web > Database Application

Rate this page:
Really useful
Satisfactory
Not helpful
Confusing
Incorrect
Unsure
Extra comments:


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.

  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. 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>

  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>
    </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).

  4. 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>
  5. 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>
  6. 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

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&amp;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]
© 2003-2007, 1060 Research Limited. 1060 registered trademark, NetKernel trademark of 1060 Research Limited.