Oracle / Spring Webflow Template Code
Status: Pre-Alpha
Brought to you by:
andrewah
Menu
▾
▴
Tree [r374] / History
| File | Date | Author | Commit |
|---|---|---|---|
| .settings | 2009-07-26 | andrewah | [r14] Add reference data validation |
| src | 2010-05-07 | andrewah | [r374] |
| target | 2010-01-20 | andrewah | [r212] |
| .classpath | 2010-02-07 | andrewah | [r270] |
| .project | 2009-12-13 | andrewah | [r131] |
| mvn | 2009-07-14 | andrewah | [r1] Committing first major release of the oracle / ... |
| pom.xml | 2010-02-19 | andrewah | [r318] |
| readme.html | 2009-07-29 | andrewah | [r21] Removed Oracle Drivers from release WAR and add... |
Read Me
<html>
<head>
<title>Oracle / Spring Webflow Template Application Instructions</title>
<head>
<body>
<div id="container">
<div id="maintitle">
<h1><span>Oracle / Spring Webflow Template Application</span></h1>
<h4>Documentation: v 0.1.</h4>
</div>
<div id="aboutme">
<h2><span>About Me</span></h2>
<p>I am a software engineer who has mainly been involved in the development of large database systems during his career.</p>
<p>The purpose of this project was to give me a vehicle to help learn the Java programming language - something which I have always used sporadically, but nonetheless something which I cannot claim any expertise in.</p>
</div>
<div id="synopsis">
<h2><span>Synopsis</span></h2>
<p>It would seem that a web-based approach has almost become the de-facto choice when faced with the prospect of developing new a application.</p>
<p>Whilst benefits have been conferred onto the development process by this approach, unfortunately, beneficial and important knowledge has been lost along the way. The knowledge that has become increasingly scarce centres around good usage of the SQL database products available on the market.</p>
<p>Some of these points will inevitably be contentious, but specifically, the cult of database agnosticism, the proliferation of object relational mapping tools and the <a href="http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/app_devs.htm">'one big application user'</a> model have led to a situation where: </p>
<ul>
<li>Security of the data is provided solely by the application, whilst the database is powerless to restrict access to the information which resides in it.</li>
<li>Knowledge about the mechanics of databases (such as indexing, exotic storage structures such as clusters) and SQL has decayed in the development community, and in some quarters is actively derided due to a belief in the value of database agnosticism.</li>
<li>The fad for 'objecty' versions of SQL such as <a href="http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html">HQL</a> have dragged the set based processing provided by SQL databases down to the level of a procedural language, lowering the level of abstraction at which data manipulation is done; whilst not providing exhaustive support for all of their (especially vendor specific) features, which can only lead to sub-optimal solutions being produced. Sadly, they result in a bastard mix of a language which doesn't sufficiently differentiate itself from what it attempts to replace.</li>
<li>The database is often the first element of an application which is called (sometimes rightly, often not) to task if performance becomes a contentious issue. Unfortunately, the decay in database skills, coupled with a decreasing awareness of tools such as <a href="http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm#8344">SQL Trace and TKPROF</a> means that it is both more likely that performance problems have been built in and that it will be difficult for teams to address these problems.</li>
</ul>
</div>
<div id="philosophy">
<h2><span>Design Philosophy</span></h2>
</div>
<div id="features">
<h2><span>Features</span></h2>
<p>What this project has attempted to do is produce a small web application which implements (some of the) functionality required by almost every application. The key point is that this implementation has been careful to take full advantage of database specific functionality (Oracle in this case). The implemented functionality includes:</p>
<ul>
<li>Logon Facility</li>
<li>User Managment Facility</li>
<li>User Privilege Management Facility</li>
<li>Reporting Framework Subsystem</li>
</ul>
<p>Two advantages of using vendor specific database features have led to:</p>
<ul>
<li>A rejection of the one big application user model. Instead an Oracle user per physical user is used. This allows us to leverage the security mechanisms provided by the database. The benefits of connection pooling are also retained via the use of <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21575905259251">proxy users</a>.</li>
<li>The ability to capture the database activity triggered by any given user via SQL Trace. This can be switched on and off via the application.</li>
<li>The creation of a PL/SQL API (I realise this will not be seen as a positive by all!!!) which abstracts the details of the data model away from the application, provides a reusable mechanism through which other applications/products can interact with the data, and guards against <a href="http://www.oracle.com/technology/tech/pl_sql/pdf/how_to_write_injection_proof_plsql.pdf">SQL Injection</a>.</li>
</ul>
<p>One repercussion of rejecting the "one big application user" model, is that it adds an extra complexity if your application has a requirement to perform tasks involving a database, but without a physical user logged in to perform that task - for example, you may have a facility allowing new users to register themselves.</p>
</div>
<div id="requirements">
<h2><span>Requirements</span></h2>
<p>This application has been developed using the following:</p>
<ul>
<li><a href="http://tomcat.apache.org/">Apache Tomcat 6.0.20</a></li>
<li><a href="http://java.sun.com/javase/6/">Java 1.6.0_07</a> (required)</li>
<li><a href="http://www.springsource.org/webflow">Spring Webflow 2.0.7</a> (required)</li>
<li><a href="http://www.oracle.com/technology/products/database/xe/index.html">Oracle XE 10G</a> (required)</li>
</ul>
<p>Each of the items on this list will need to be set up and installed in your development environment.</p>
</div>
<div id="subsystems">
<h2><span>Subsystems</span></h2>
<p>The first thing to know is that all of application's data is stored in a single database schema, called APP_CORE. (Password: APP_CORE). Therefore, all components of each of these subsystems will be found in this schema.</p>
<p>What follows are instructions on how to extend the application.</p>
<h3>Database Connection</h3>
<p>The application has been designed and developed to access its database using the standard JNDI facility provided by Apache Tomcat. So, as such, JNDI resources need to be configured in the <em>src\main\webapp\META-INF\context.xml</em> file. As an example:</p>
<pre>
<Resource name="jdbc/standard"
auth="Container"
type="oracle.jdbc.pool.OracleDataSource"
driverClassName="oracle.jdbc.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
url="jdbc:oracle:thin:@127.0.0.1:1521:xe"
maxActive="8"
maxIdle="4"
maxWait="-1"
/>
</pre>
<p>The entries provided by the template application will have to be modified in such a manner that they become appropriate to your development environment. However, in all probability, the "url" property will be the one factor most prone to change.</p>
<p>When the template application is loaded by Tomcat, a servlet (<em>net.sourceforge.webflowtemplate.config.web.servlet.DBConnectionConfigInit</em>) parses the file located at src\main\resource\dbConnections.xml. It then gets and caches a datasource (see the <em>net.sourceforge.webflowtemplate.db.connection.AbstractDBConnectionFactory</em> class)- this has the advantage of allowing us to avoid a JNDI resource lookup each time that we have to obtain a connection. This file contains information about the JNDI resources that provide the application access to the database. As an example:</p>
<pre>
<connection>
<key>STANDARD</key>
<dbconnectiontype>JNDI</dbconnectiontype>
<dbproduct>ORACLE</dbproduct>
<jndiresourcename>jdbc/standard</jndiresourcename>
<jndienvironmentkey>java:/comp/env</jndienvironmentkey>
</connection>
</pre>
<p>Please note that:</p>
<ul>
<li>The <em>"Key"</em> element defines a string (key) which will be used the application to the JNDI datasource with a JNDI Datasource name of "jndiresourcename". Every legal value of "key" must be matched by an entry in the <em>net.sourceforge.webflowtemplate.constants.db.DBResource</em> ENUM class.</li>
<li>The <em>"dbconnectiontype"</em> allows us to dictate what method of connecting to the database will be used. Every legal value of "dbconnectiontype" must be matched by an entry in the <em>net.sourceforge.webflowtemplate.constants.db.DBConnectionType</em>. The two values supported at the moment are "JNDI" and "JNDIPROXIED" (for use with Oracle Proxy users)</li>
<li>The <em>"dbproduct"</em> element defines a string identifying the actual product which this datasource refers to. Every legal value of "dbproduct" must be matched by an entry in the <em>net.sourceforge.webflowtemplate.constants.db.DBProduct</em> ENUM class.</li>
<li>The <em>"jndiresourcename"</em> element should match the name of a JNDI resource defined in the <em>src\main\webapp\META-INF\context.xml</em> file.</li>
</ul>
<h3>Reporting</h3>
<p>The key idea of the reporting system is that any given report may or may not be accessible by any user. Therefore, in order to integrate a report into the database, a single package, housing one function (returning a REF CURSOR) will need to be able to be created in order to provide access to the report's data. In addition, a single database ROLE will need to be created (and registered in the "db_role" table) in order to allow access to the report's data. Finally, you wil need to register the report in the "report_category" table.</p>
<p>After this, it is a case of extending the application so that it can retrieve and display the information made accessible by the report.</p>
</div>
<div id="use>
<p>The installation scripts create a superuser which has access to all functionality. Username: SUPERUSER, password: SUPERUSER</p>
</div>
<div id="installation">
<h2><span>Installation</span></h2>
<div id="installdatabase">
<h3><span>Database</span></h3>
<p>Alter the file <em>"<path to project directory>\src\main\database\oracle\install.sql"</em> and set the values of the following variables appropriately:</p>
<ul>
<li>PATH_TO_THIS_FILE</li>
<li>TNSNAMES_ALIAS</li>
</ul>
<p>Alter the file <em>"<path to project directory>\src\main\database\oracle\define_schema.sql"</em> and set the values of the following variables appropriately:</p>
<ul>
<li>SYS_PASSWORD</li>
<li>SYSTEM_PASSWORD</li>
</ul>
<p>Run the following SQL* Plus command: @<path to project directory>\src\main\database\oracle\install</p>
</div>
<div id="installapp">
<h3><span>Precompiled WAR</span></h3>
<p>There is a .WAR file containing everything required at <a href="https://springwebflowte.svn.sourceforge.net/svnroot/springwebflowte/war/secure.war">https://springwebflowte.svn.sourceforge.net/svnroot/springwebflowte/war/secure.war</a>. You may need to alter the contents of the <em>context.xml</em> file to something more appropriate.</p>
<h3><span>Required JARs</span></h3>
<p>The following is a list of JARs that <a href="http://maven.apache.org/">Maven</a> has pulled into this project.</p>
<p>*Note* In addition to the items on this list, you will also need the <a href="http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html">Oracle JDBC drivers</a> which I can't redistribute. I recommend the latest (11g release 1 at the time of writing)</p>
<ul>
<li>aopalliance-1.0.jar</li>
<li>com.springsource.org.aopalliance-1.0.0.jar</li>
<li>com.springsource.org.apache.commons.logging-1.1.1.jar</li>
<li>com.springsource.org.jboss.el-2.0.0.GA.jar</li>
<li>commons-beanutils-1.8.0.jar</li>
<li>commons-codec-1.3.jar</li>
<li>commons-collections-3.2.1.jar</li>
<li>commons-digester-2.0.jar</li>
<li>jcl-over-slf4j-1.5.6.jar</li>
<li>jstl-1.1.2.jar</li>
<li>log4j-1.2.15.jar</li>
<li>org.springframework.aop-2.5.6.A.jar</li>
<li>org.springframework.beans-2.5.6.A.jar</li>
<li>org.springframework.context-2.5.6.A.jar</li>
<li>org.springframework.core-2.5.6.A.jar</li>
<li>org.springframework.jdbc-2.5.6.A.jar</li>
<li>org.springframework.transaction-2.5.6.A.jar</li>
<li>security-core-2.0.4.jar</li>
<li>slf4j-api-1.5.6.jar</li>
<li>slf4j-log4j12-1.5.6.jar</li>
<li>spring-2.5.6.jar</li>
<li>spring-aop-2.0.8.jar</li>
<li>spring-beans-2.5.6.jar</li>
<li>spring-binding-2.0.7.jar</li>
<li>spring-context-2.5.6.jar</li>
<li>spring-context-support-2.5.6.jar</li>
<li>spring-core-2.5.6.jar</li>
<li>spring-dao-2.0.8.jar</li>
<li>spring-jdbc-2.0.8.jar</li>
<li>spring-js-2.0.7.jar</li>
<li>spring-security-acl-2.0.4.jar</li>
<li>spring-security-core-2.0.4.jar</li>
<li>spring-security-taglibs-2.0.4.jar</li>
<li>spring-web-2.5.6.jar</li>
<li>spring-webflow-2.0.7.jar</li>
<li>spring-webmvc-2.5.6.jar</li>
<li>standard-1.1.2.jar</li>
<li>tiles-api-2.0.6.jar</li>
<li>tiles-core-2.0.6.jar</li>
<li>tiles-jsp-2.0.6.jar</li>
<li>tiles-portlet-2.1.2.jar</li>
<li>tiles-servlet-2.1.2.jar</li>
</ul>
</div>
</div>
</body>
</html>
