Oracle & Wicket Starter Application Code
Brought to you by:
andrewah
Menu
▾
▴
Tree [r94] / History
| File | Date | Author | Commit |
|---|---|---|---|
| .settings | 2010-06-29 | andrewah | [r1] Initial load of the application |
| src | 2010-11-15 | andrewah | [r94] Invalidate the session immediately when the log... |
| .classpath | 2010-11-14 | andrewah | [r69] |
| .project | 2010-09-13 | andrewah | [r55] |
| checkStyleConfig.xml | 2010-11-14 | andrewah | [r69] |
| pom.xml | 2010-11-14 | andrewah | [r69] |
| readme.html | 2010-11-13 | andrewah | [r65] MOD : Mimic apache wicket datetime package stru... |
Read Me
<html>
<head>
<title>Installation and setup instructions</title>
</head>
<body>
<div id="contents">
<h1><span>Contents</span></h1>
<ol>
<li><a href="#introduction">Introduction</a></li>
<li><a href="#prerequisites">Required Software</a></li>
<li><a href="#mavenConfig">Maven Configuration</a></li>
<li><a href="#databaseSetup">Database Setup</a></li>
<li><a href="#deployment">Deployment</a></li>
<li><a href="#design">Design</a>
<ul>
<li><a href="#architecture">Architecture</a></li>
<li><a href="#security">Security</a></li>
</ul>
</li>
<li><a href="#refdatamgr">Reference Data Management</a></li>
<li><a href="#ucp">Universal Connection Pool</a></li>
<li><a href="#html">HTML</a>
<ul>
<li><a href="#layout">Layout</a></li>
</ul>
</li>
</ol>
</div>
<a name="introduction"></a>
<div id="introduction">
<h1><span>Introduction</span></h1>
<p>
This document represents the installation instructions for a starter application developed using <a href="http://wicket.apache.org/">Apache Wicket</a> and Oracle.
The software is database specific, exploits Oracle functionality and is based on Oracle's published recommendations of best practice. It is intended to provide a high quality implementation of basic functionality which would be required of any serious business application.
The foundation which it provides can be customised as projects dictate.
</p>
</div>
<a name="prerequisites"></a>
<div id="prerequisites">
<h1><span>Required Software</span></h1>
<p>Download, install and configure all of the following software:</p>
<ul>
<li><a href="http://tomcat.apache.org/download-60.cgi#6.0.26">Apache Tomcat</a></li>
<li><a href="http://maven.apache.org/download.html">Apache Maven</a></li>
<li><a href="http://java.sun.com/javase/downloads/widget/jdk6.jsp">Java</a></li>
<li><a href="http://www.oracle.com/technology/software/products/database/xe/index.html">Oracle</a></li>
</ul>
<p>Please note that by default Oracle XE will install a web server which runs on port 8080. As a consequence, I've configured Tomcat to run on port 8081. The alternative is to use <a href="http://jetty.codehaus.org/jetty/">Jetty</a> - this will be described later.</p>
</div>
<a name="mavenConfig"></a>
<div id="mavenConfig">
<h1><span>Maven Configuration</span></h1>
<p>The following instructions should be followed after Maven has been successfully installed.</p>
<ol>
<li>Download the latest versions of the <a href="http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_112010.html">Oracle JDBC drivers</a> and the <a href="http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/ucp_112010.html">Universal Connection Pool</a>. They will not be available from the <a href="http://repo1.maven.org/maven2/">Maven Repository</a>.</li>
<li>
Add them to your Maven repository. This can be done by issuing the following on the command line [modify the path to these .jars as appropriate]:
<ol>
<li>mvn install:install-file -DgroupId=oracle -DartifactId=jdbc -Dversion=11.2.0.1.0 -Dpackaging=jar -Dfile=/path/to/file</li>
<li>mvn install:install-file -DgroupId=oracle -DartifactId=ucp -Dversion=11.2.0.1.0 -Dpackaging=jar -Dfile=/path/to/file</li>
</ol>
</li>
<li>
Configure Maven so that it can deploy web applications directly to Tomcat. Do this by adding a file named "settings.xml" to the directory (in my case C:\Users\Andrew\.m2) beneath your local repository containing the following :
<pre>
<settings xmlns="http://maven.apache.org/SETTINGS/1.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/SETTINGS/1.0.0
http://maven.apache.org/xsd/settings-1.0.0.xsd">
<servers>
<server>
<id>LocalTomcat</id>
<username>admin</username>
<password>admin</password>
</server>
</servers>
</settings>
</pre>
Adjust the values of the username and password accordingly.
</li>
</ol>
</div>
<a name="databaseSetup"></a>
<div id="databaseSetup">
<h1><span>Database Setup</span></h1>
<p>The following instructions must be followed after Oracle has been successfully installed.</p>
<ol>
<li>Modify the oracle installation scripts.</li>
<ol>
<li>Adjust the variables in <a href="src/main/database/oracle/install.sql">install.sql</a> in a manner appropriate to your environment. Pay particular attention to the following: </li>
<ol>
<li>PATH_TO_THIS_FILE - the full path to the install.sql file</li>
<li>TNSNAMES_ALIAS - this must match the entry in your TNSNames.ora file for the database on which the application will be resident</li>
<li>SYS_PASSWORD - the password of your database's SYS user</li>
<li>DEFAULT_TABLESPACE - the name of the default tablespace</li>
<li>DEFAULT_TABLESPACE_FILE - the directory path / file name of the default tablespace - the directory must exist (it will not be created by the installation process)</li>
<li>DEFAULT_TEMP_TABLESPACE - the name of the default temporary tablespace</li>
<li>DEFAULT_TEMP_TABLESPACE_FILE - the directory path / file name of the default temporary tablespace - the directory must exist (it will not be created by the installation process)</li>
</ol>
</ol>
<li>Run the oracle installation scripts by executing the following via SQL*Plus: <pre>@<path_to_install.sql></pre> - eg. <pre>@c:\oraclewicket\src\main\database\oracle\install.sql</pre></li>
</ol>
</div>
<a name="deployment"></a>
<div id="deployment">
<h1><span>Deployment</span></h1>
<p>This must only be attempted once all prerequistes have been fulfilled, and once the database has been setup</p>
<p>To use Jetty (which may be appropriate during development):</p>
<ol>
<li>On the command line, navigate to the directory in which the file <a href="pom.xml">pom.xml</a> is located</li>
<li>Execute the follow command : <pre>mvn -Djetty.port=8081 jetty:run</pre></li>
<li>Go to <pre><a href="http://localhost:8081/oracle_wicket_template">http://localhost:8081/oracle_wicket_template</a></pre></li>
<li>Logon using the username : <strong>SUPERUSER</strong> and the password : <strong>sup3r!2</strong></li>
</ol>
<p>To use Tomcat:</p>
<ol>
<li>Ensure that both Apache Tomcat and Oracle are running.</li>
<li>On the command line, navigate to the directory in which the file <a href="pom.xml">pom.xml</a> is located</li>
<li>Execute the follow command : <pre>mvn clean tomcat:redeploy</pre></li>
<li>The Apache Tomcat service may need to be restarted in order that the Universal Connection Pool starts correctly</li>
<li>Go to <pre><a href="http://localhost:8081/oracle_wicket_template">http://localhost:8081/oracle_wicket_template</a></pre></li>
<li>Logon using the username : <strong>SUPERUSER</strong> and the password : <strong>sup3r!2</strong></li>
</ol>
</div>
<a name="design"></a>
<div id="design">
<h1><span>Design</span></h1>
<p>Some notes on the implementation: </p>
<a name="architecture"></a>
<h2><span>Architecture</span></h2>
<ul>
<li>This template application has been very specifically design to use both the Oracle Database and the Apache Wicket framework. It is database specific and exploits all appropriate proprietary features of Oracle. The concept of being database agnostic has been rejected - this application will not port to other databases without significant rework - but the database portion will easily port to other web frameworks and languages (eg PHP, .net) easily!</li>
<li>In contrast to most web based database applications, this one unequivocally rejects the so called <a href="http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/app_devs.htm#i1006169">One Big Application User</a> architecture. Instead all users are represented by Oracle Users - which allows for end to authentication and <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/sqltrace.htm#PFGRF01020">tracing</a>.</li>
<li>As per <a href="http://www.oracle.com/technology/oramag/oracle/08-nov/o68asktom.html">Oracle recommendations</a>, all data structures are stored in a single schema. This has been named 'APP_DATA'. It is intended that the content of this schema be strictly limited to the following database objects
<ul>
<li>Tables</li>
<li>Clusters</li>
<li>Views</li>
<li>Sequences</li>
</ul>
(I.e. all database objects related to the storage of data).
</li>
<li>As per <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:25405782527721">Oracle recommendations</a> the web portion of the application accesses the database via a PL/SQL Transactional API - there is no SQL in the application whatsoever, only calls to PL/SQL packaged functions / procedures which constitute the API. <strong>The application should not be aware of any of the data structures/tables in the database.</strong> This architecture has the desirable secondary effect of <a href="http://www.oracle.com/technology/tech/pl_sql/pdf/how_to_write_injection_proof_plsql.pdf">minimising or even eliminating any vulnerabilities to SQL Injection</a>.</li>
<li>The Transactional API has been written using PL/SQL packages and those packages are divided amongst the following schema - each of which represent logically distinct aspects of the application's functionality:
<ul>
<li>APP_DATA_REGISTRATION - utility packages used to setup data required by the application. Used during installation only.</li>
<li>APP_EXCEPTION - Exception management and raising API.</li>
<li>APP_LOG - Event logging API.</li>
<li>APP_REFDATA - Reference data management API</li>
<li>APP_REPORT - Database reporting API</li>
<li>APP_USER - User Management API</li>
<li>APP_UTILITY - Disparate utilities whose scope extends over the whole application. Will not be called directly from the web application</li>
</ul>
These schema do not contain any database objects related to the storage of data (this is the exclusive remit of the 'APP_DATA' schema). Instead they contain all objects required to implement the database's transactional API - packages, oracle objects etc.
</li>
</ul>
<a name="security"></a>
<h2><span>Security</span></h2>
<p>Because the 'One Big Application User' architecture has been rejected, access to resources (data, PL/SQL) is controlled by Oracle's <a href="http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/authorization.htm#i1007091">role</a> mechanism. Use of the role mechanism along with a transactional API has some important consequences:</p>
<ul>
<li>Oracle provides 2 types of role which have relevance to this application:
<ul>
<li><a href="http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/users.htm#i1006320">Default</a> - these roles are 'enabled' for a user automatically when they connect to the database, giving the user access to all of the resources conferred by the role.</li>
<li>Non-default - these roles are not 'enabled' for a user automatically - the role has to be explicitly activated using the <a href="http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/authorization.htm#BABJCHGD">SET ROLE</a> statement or the <a href="http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10577/d_sessio.htm#i997099">DBMS_SESSION.SET_ROLE</a> procedure. This has been accomplished in my implementation through:
<ul>
<li>The PL/SQL package <a href="src/main/database/oracle/APP_USER/packages/pkSetAppUserRoles.sql">app_user.pkSetAppUserRoles</a></li>
<li>The Java class <a href="src/main/java/com/sourceforge/oraclewicket/db/oracle/dao/AbstractOracleDAO.java">com.sourceforge.oraclewicket.db.oracle.dao.AbstractOracleDAO</a>. The methods 'setRole(String)' and 'setRoles(String[])' invoke the methods in the PL/SQL package app_user.pkSetAppUserRoles. (It is intended that all data access objects extend this base class)</li>
<li>The table <a href="src/main/database/oracle/APP_DATA/tables/table_db_role.sql">app_data.db_role</a> - whose contents represent every database role relevant to this application</li>
<li>The table <a href="src/main/database/oracle/APP_DATA/tables/table_manageable_db_role.sql">app_data.manageable_db_role</a> - whose contents represent every database role relevant to this application which is to be considered a default role.</li>
</ul>
</li>
</ul>
It's important note that whilst Oracle does not limit the amount of roles that can be granted to a user, it only permits a user to have a maximum of <a href="http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/authorization.htm#DBSEG99978">148</a> roles enabled at any one time. So, a user with > 148 default roles will not be able to log on to the database successfully! This should be considered as being a boon rather than a burden as it encourages the developer to consider and enforce the <a href="http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/guidelines.htm#CHDGIEJE">principle of least privilege</a>.</li>
<li>Consider that :
<ul>
<li>This application uses a Transactional Database API - in which each PL/SQL stored procedure used by the (Java) web application is designed to satisfy the work required by a single transaction.</li>
<li>Oracle recommend that all PL/SQL should be placed into packages.</li>
<li>The permission to execute a PL/SQL package (the whole package only - not individual functions/procedures within it) can be granted to a role, which can then be granted to a user.</li>
</ul>
based on this I've adopted the design mantra that:
<ul>
<li>A role should represent the permission to use a discrete (of course this is a subjective term) piece of functionality in the application. For example, using a report; managing users</li>
<li>A package should implement all of the functionality (but no more) required by a discrete piece of functionality in the application</li>
<li>Ergo, there should be a 1:1 correspondance between roles and packages.</li>
</ul>
This design mantra provides the application with a fine grained security model based around functionality (not data/table structures) - which is desirable because it allows roles to be provided to users in a specific and unambigious manner - in this arrangement is is extremely clear what a user can and cannot do.
</li>
<li>Oracle allows roles to be granted to roles (a composite role) - raising the possibility that, for example a 'secretary' role could consist of a role allowing user management, and role allowing use of a reporting subsystem. However, I've rejected this notion on the basis that using composite roles means that a role no longer only represents the permission to invoke a discrete piece of application functionality! The role of roles in the application has been ambiguated!!! However, the ability to group together roles is still useful. The application caters for this by providing the notion of a user type:
<ul>
<li>User types represent a collection of database roles. The contents of the table <a href="src/main/database/oracle/APP_DATA/tables/table_app_user_type.sql">app_data.app_user_type</a> constitutes the collection of user types.</li>
<li>The contents of the table <a href="src/main/database/oracle/APP_DATA/tables/table_app_user_type_role.sql">app_data.app_user_type_role</a> represents the database roles that have been grouped into user types.</li>
</ul>
The contents of these data structures can be managed through the reference data management facility.
</li>
</ul>
<a name="refdatamgr"></a>
<h2><span>Reference Data Management</span></h2>
<p>The application provides a facility which allows reference data to be managed. It caters for several different flavours of reference data. See the comments in the header of the package <a href="src/main/database/oracle/APP_DATA_REGISTRATION/packages/pkReferenceDataSetup.sql">app_data_registration.pkReferenceDataSetup</a> for detailed information about how to integrate with it.</p>
<a name="ucp"></a>
<h2><span>Universal Connection Pool</span></h2>
<p>The application makes use of the <a href="http://download.oracle.com/docs/cd/E11882_01/java.112/e12265/intro.htm">Oracle Universal Connection pool</a> to manage database connections. It makes use of <a href="http://download.oracle.com/docs/cd/E11882_01/java.112/e12265/label.htm">labelled connections</a> and provides a screen through which it can be administered at run time.</p>
</div>
<a name="design"></a>
<div id="html">
<h1><span>HTML</span></h1>
<p>I am not a HTML, CSS or javascript expert so the layout has been designed to be basic, functional, but well designed (through the use of Wicket's <a href="http://wicket.apache.org/examplemarkupinheritance.html">markup inheritance feature</a>).</p>
<a name="layout"></a>
<h2><span>Layout</span></h2>
<p>The <a href="http://search.dojotoolkit.org/">Dojo toolkit</a> has been used to implement the layout. I'm no Javascript expert so am not knowledgeable about the advantages of one Javascript framework over another - but I don't imagine that it would be difficult to use an alternative javascript framework like jQuery.</p>
<p> A <a href="http://doc-staging.dojocampus.org/quickstart/custom-builds.html">custom build of Dojo</a> has been created, and the files required by the build script are located <a href="src\main\dojo-build">here</a>. Modify them appropriately.</p>
</div>
</body>
</html>
