SQL select to web application in couple of seconds

Maybe you are not aware but if you just know how to write one sql select statement, you're just couple of seconds from having an web application and couple of minutes from enjoying it as a Liferay portlet.
The key ingredients for this recipe are: Openxava, Liferay and ... Minuteproject.

The equation is:
sql select statement (containing one field considered as unique) = Openxava (Webapp) + Liferay (Portlet)
in less than 5 minutes.

Prerequisites

• Download Openxava (4.2.2), Liferay, Minuteproject (0.5.7.1+)
• Set environment variables OX_HOME and MP_HOME to Openxava and Minuteproject home directories respectively.
• Use Java 6

Create your statement

• Create a sql select statement
• Ensure that one of the return field can be considered as unique (if not add one)
  
• Create a view from your statement
• Ex: CREATE VIEW abc as

Remark:
Why is it necessary to have a unique field?
It is to ensure that MP can place a @Id JPA2 annotation on one field of the generated entity.
It has to be unique to avoid issue dealing with first level cache.

Start Minuteproject console

• Point to your model and fill your model name (ex: efg)
• select your view abc (in customisation tab)
  
• add the unique field as 'virtual' primary key. Your unique field is not a PK but it will acts as to uniquely identify a record entry.
  
• Choose target openxava and click generate.

Release your application

• go to the generated code
• check environment variables (OX_HOME, MP_HOME) are set.
• execute from a command prompt: build-efg.cmd/sh
• depending on the OS from couple of seconds to a dozen of seconds and a browser should open to the address http://localhost:8080/egf/xava/homeMenu.jsp

Deploy on Liferay

• go to OX_HOME/workspace.dist/efg.dist/efg.war
• add ejb.jar and jdbc driver jar in LIFERAY_HOME/tomcat/lib
• start Liferay
• Upload your portlet
• Install your portlet
• Enjoy

Remark:
Whether on the standalone tomcat app or on the Liferay deployed portlet, Openxava is only offering the user selection. It is normal since the DB object is a view, Minuteproject 4 Openxava suppress the Create/Update/Delete, just the Select functionality is kept.

---

Example

As model I take the lazuly model project in version 0.1 it contains 13 tables.
The sql to create the schema on mysql can be downloaded at http://code.google.com/p/lazuly/source/browse/trunk/conference-db/conference-db-mysql-0.1.sql

Create your view from your sql select statement


Start MinuteProject console


Scope your model


Generate
Choose your technology: here Openxava, and click on Generate.
The code will be generated in MP_HOME/output/conference/OpenXava

Make a standalone Openxava app
Go to the generated code
Open a prompt
Check or set OX_HOME and MP_HOME
execute build-conference.cmd/sh
Couple of seconds later a browser should open to the model (conference) menu.
Report tab has one entry 'my conference cool report'.
When clicking on it here is a sample screen with data that you could see:



Deploy on Liferay

• Add the datasource (the snippet for tomcat is in OX_HOME/workspace/conference/OpenXava/openxava/other/tomcat/snippet/conference_jndi_for_tomcat_snippet.txt is to be inserted into LIFERAY_HOME/tomcat/conf/context.xml)
• Upload the portlet war (OX_HOME/workspace.dist/conference.dist/conference.war
• Position it
• Enjoy.

---

Other information

You can also find other information regarding Minuteproject 4 Openxava generation at

• http://minuteproject.wikispaces.com/OpenXava
• http://minuteproject.wikispaces.com/OpenXavaSmartRE
• http://minuteproject.blogspot.com/2011/06/minuteproject-4-openxava-lazuly.html

---

Conclusion

• Your model is your technology tutorial
• Productivity can be greatly improved
• Data Quality and troubleshooting operations are quickly available
• Keep on improving send feed-back to minuteproject@gmail.com or tweet @minuteproject

MinuteProject 4 Openxava: Lazuly showcase

Intro
The goal of this article is to have a working Openxava on top of a data model by writting 0 (yes ZERO) lines of code. This application has to run as a standalone webapp or as portlets in a portal.

The model used is the Lazuly-db which is a opensource database hosted on googlecode that represents the structure of a database holding conference related informations.
Lazuly-db will be used as a showcase of various MinuteProject technology tracks.
In version 0.1 it has 13 tables, 2 views.

This article explains the different steps to quickly have a create an Openxava application.
It is furthermore a tutorial on how to enrich the database model to get customized Openxava artifacts providing specific behaviour.
The purpose is to give to user enough material to customize it and go on extending your model.

If you want to see the result quickly, I encourage you to see the videos (downwards on this page).

OpenXava
Is an opensource product that makes CRUD RIA modal application in a very quick way.
It enforces the DRY principle with a comprehensive set of annotations.

Openxava is a generic framework that dynamically generates the presentation layer based on custom annotation.

MinuteProject 4 Openxava offers a reverse-engineering solution targeting OX architecture.

Here are the set of artifacts MinuteProject will generate:

• JPA2 and OpenXava annotated entities;
• associated metamodel;
• drop down list artifacts for enumeration;
• Navigation menu;
• build script (windows and unix);
• tomcat datasouce configuration snippet and context.xml;
• internationalisation.

Operating principle

MinuteProject works with a configuration file.
This file holds the information regarding the model and how to enrich it. It also specifies the target bundle of templates for a technology track (here Openxava).

Datasource
Lazuly DB main entities: The schema below resumes the lazuly DB main entities (tables/views) and theirs relationships.


Configuration

This part summarizes the different configurations used:

<!DOCTYPE root>
<generator-config>
<configuration>
<model name="conference" version="1.0" package-root="net.sf.mp.demo">
<data-model>
<driver name="mysql" version="5.1.16" groupId="mysql" artifactId="mysql-connector-java"></driver>
<dataSource>
<driverClassName>org.gjt.mm.mysql.Driver</driverClassName>
<url>jdbc:mysql://127.0.0.1:3306/conference</url>
<username></username>
<password></password>
</dataSource>
<!--
for Oracle and DB2 please set the schema <schema> </schema>
-->
<primaryKeyPolicy oneGlobal="true">
<primaryKeyPolicyPattern name="autoincrementPattern"></primaryKeyPolicyPattern>
</primaryKeyPolicy>
</data-model>
<business-model>
<!--
<generation-condition> <condition type="exclude"
startsWith="DUAL"></condition> </generation-condition>
-->
<business-package default="conference">
<condition type="package" startsWith="STAT" result="statistics"></condition>
<condition type="package" startsWith="COUNTRY" result="admin"></condition>
<condition type="package" startsWith="ROLE" result="admin"></condition>
</business-package>
<enrichment>
<conventions>
<column-naming-convention type="apply-strip-column-name-suffix"
pattern-to-strip="_ID" />
<reference-naming-convention
type="apply-referenced-alias-when-no-ambiguity" is-to-plurialize="true" />
</conventions>

<entity name="COUNTRY" content-type="reference-data">
<semantic-reference>
<sql-path path="NAME" />
</semantic-reference>
</entity>
<entity name="CONFERENCE_MEMBER">
<semantic-reference>
<sql-path path="FIRST_NAME" />
<sql-path path="LAST_NAME" />
</semantic-reference>
<field name="STATUS">
<property tag="checkconstraint" alias="conference_member_status">
<property name="PENDING" value="PENDING" />
<property name="ACTIVE" value="ACTIVE" />
</property>
</field>
<field name="EMAIL">
<stereotype stereotype="EMAIL" />
</field>
</entity>
<entity name="SPEAKER">
<field name="BIO">
<stereotype stereotype="HTML_TEXT" />
</field>
<field name="PHOTO">
<stereotype stereotype="PHOTO" />
</field>
<field name="WEB_SITE_URL">
<stereotype stereotype="WEBURL" />
</field>
</entity>
<entity name="PRESENTATION">
<field name="STATUS">
<property tag="checkconstraint" alias="presentation_status">
<property name="PROPOSAL" value="PROPOSAL" />
<property name="ACTIVE" value="ACTIVE" />
</property>
</field>
</entity>
<entity name="SPONSOR">
<field name="STATUS">
<property tag="checkconstraint" alias="sponsor_status">
<property name="PENDING" value="PENDING" />
<property name="ACTIVE" value="ACTIVE" />
</property>
</field>
<field name="PRIVILEGE_TYPE">
<property tag="checkconstraint" alias="sponsor_privilege">
<property name="GOLDEN" value="Golden" />
<property name="SILVER" value="Silver" />
<property name="BRONZE" value="Bronze" />
</property>
</field>
</entity>
<!-- views -->
<entity name="stat_mb_per_ctry_conf" alias="MEMBER_PER_COUNTRY_AND_CONFERENCE">
<virtual-primary-key isRealPrimaryKey="true">
<property name="virtualPrimaryKey" value="ID" />
</virtual-primary-key>
</entity>
<entity name="stat_mb_by_role" alias="MEMBER_PER_ROLE_COUNTRY_AND_CONFERENCE">
<virtual-primary-key isRealPrimaryKey="true">
<property name="virtualPrimaryKey" value="id" />
</virtual-primary-key>
<field name="stat_mb_per_ctry_conf_ID" linkToTargetEntity="stat_mb_per_ctry_conf"
linkToTargetField="id"></field>
</entity>
</enrichment>
</business-model>
</model>
<targets>
<!-- openxava -->
<target refname="OpenXava" name="OpenXava"
fileName="mp-template-config-openxava-last-features.xml"
outputdir-root="../../DEV/output/openxava/conference"
templatedir-root="../../template/framework/openxava">
</target>

<target refname="JPA2-LIB" fileName="mp-template-config-JPA2-LIB.xml"
templatedir-root="../../template/framework/jpa">
</target>

<target refname="BSLA-LIB" fileName="mp-template-config-bsla-LIB-features.xml"
templatedir-root="../../template/framework/bsla">
</target>

</targets>
</configuration>
</generator-config>

Errata

Minuteproject and OpenXava are in constant evolution and at some stage there might be some temporary fix to apply in the configuration

This version works for MP version 0.5.x
For version above 0.6 add

   <target refname="CACHE-LIB" 
      fileName="mp-template-config-CACHE-LIB.xml" 
      templatedir-root="../../template/framework/cache">
   </target>

 
On MP 0.8.2 the semantic reference node on 'CONFERENCE_MEMBER' entity is to comment.

Configuration explained

If you have read the minuteproject 4 JPA2 lazuly showcase article on this blog, you would notice that the configuration is similar to the one for JPA2 track. It is the case for everything regarding the model location (same model) and the enrichment, meanwhile it defers regarding the target, since it uses OpenXava.

The configuration provides:

• A model name: conference, version and package root;
• the driver as maven reference;
• connection parameters;
• primarykey policy (autoincrement)
• business packages: entities starting with stat go to 'statistics'...;role, countries goes in administration package. Those will be converted into distinct menu tabs.
• convention: strip-column-name-suffix _ID (by convention in the database, I have column naming ending with '_ID' for foreign key. Here the Java naming convention can defer from the DB naming
• convention: apply-referenced-alias-when-no-ambiguity indicating that when a table has a one2many relationship with other (i.e. has child), if it has not 2 children with the same other side table (no ambiguity) than the name of the variable is based on the other side table, and plurialized (english) collections
• Entity enrichment: name alias (can have a distinct Java class name than the one derived from the DB);
• Field enrichment: provide a check constraint that becomes an Openxava drop down list
• Entity (views) enrichment by providing them a primary key (create a unique key) by adding foreignkey. In this way we can have an alternative navigation model on top of some BI views.
• Tag field content-type="reference-data" apply to ROLE and COUNTRY entry will trigger the following navigation behavior: you cannot navigation from a role entry to children CONFERENCE_MEMBER in the application, but form CONFERENCE_MEMBER you can associate roles.
• stereotype="EMAIL" will be converted in its associated email stereotype on Openxava triggering email format validation, same for WEB_SITE_URL
• stereotype="HTML_TEXT" applied to SPEAKER.BIO field will be converted in its associated html_text stereotype on Openxava offering a Rich text format facility
• stereotype="PHOTO" applied to SPEAKER.PHOTO field will be converted in file upload facility by Openxava

Additional information on how to enrich the model can be found at

• http://minuteproject.wikispaces.com/OpenXava
• http://minuteproject.wikispaces.com/OpenXavaSmartRE

Sources and installation
Lazuly project
Svn the source as describe on http://code.google.com/p/lazuly/source/checkout

Mysql
Install mysql
run conference-db.0.1.sql on schema conference

Minuteproject
Download and install minuteproject 0.5.5. Minuteproject is installed in MP_HOME.

Openxava

Download and install openxava 4.1.2. Openxava is installed in OX_HOME.
Remark
This demo is based on OX 4.1.2, if you are using a more recent version there may not be foreward compatibility. Release (MP 0.5.6 will be compliant with OX 4.2.1). In fact there is one artifact generated home.jsp that is based on OX module.jsp. If this artifact changes between OX release, there is potentially an error.

Generation

• Copy the file http://code.google.com/p/lazuly/source/browse/trunk/lazuly-openxava/mp-config-LAZULY-OPENXAVA.xml into %MP_HOME%/mywork/config
• Adapt the connection parameters in mp-config-LAZULY-OPENXAVA.xml
• from a command line execute: generate-model.(sh/cmd) mp-config-LAZULY-OPENXAVA.xml

The resulting generated code goes to %MP_HOME%/DEV/output/openxava/conference

Structure of the Openxava project
Here the structure open with as an eclipse project in the openxava workspace

Execution

• check mysql.jar is in tomcat/lib
• check that the system variables: ox_home, mp_home are set to Openxava and MinuteProject installation directories.

Open a prompt on /DEV/output/openxava/conference, and execute build-conference.cmd.

It will

• Create an Openxava project
• Build openxava web app
• Build openxava portlets (to be deployed on a portal such as Liferay)
• Copy context.xml (take care that it overwrite the existing one)
• Start tomcat
• Deploy the application
• Start you default browser to the Conference Home menu.

The enjoy the navigation.

Videos

Video starting from scratch to deployed application

Video of the resulting application