Monday, August 19, 2013

Northwind DB revisited with MP 4 OX

Minuteproject (0.8.4) now supports ms-sqlserver.
It has been tested with MS Northwind DB on sqlserver 2014.
Here are the steps to follow to get an OpenXava application from the northwind DB.

Goal:
Get a working Northwind Openxava application in couple of seconds.
For hungry minds please find the resulting code on googlecode.

Northwind DB

Northwind database is a sample DB provided by Microsoft.
It can be found at http://northwinddatabase.codeplex.com/releases/view/71634

The sql provided to create the schema has DB objects (tables/views) containing space.
This sql has been revisited to remove those spaces a version can be found here.

  • Install SQLServer
  • Create an account
  • Run the script.

MinuteProject with SQLSERVER

Minuteproject
  • uses com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc driver
  • associates 'identity' as a primary key strategy by default on the console
  • associates org.hibernate.dialect.SQLServer2008Dialect for hibernate dialect
  • proposes maven artifact-id=sqljdbc4; group-id=com.microsoft.sqlserver; version=4.0 for pom configuration
Furthermore when retrieving the metadata of your model, set 'dbo' of the schema node.

Minuteproject configuration



<!DOCTYPE root>
<generator-config>
 <configuration>
  <model name="nortewind" version="1.0" package-root="net.sf.mp.demo">
   <data-model>
    <driver name="sqlserver" version="4.0" groupId="com.microsoft.sqlserver"
     artifactId="sqljdbc4"></driver>
    <dataSource>
     <driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
     <url>jdbc:sqlserver://localhost:1433;databaseName=northwind</url>
     <username>sqlserver</username>
     <password>xxxxxxxx</password>
    </dataSource>
    <!-- for Oracle and DB2 please set the schema <schema> </schema> -->
    <schema>dbo</schema>
    <primaryKeyPolicy oneGlobal="true">
     <primaryKeyPolicyPattern name="identityPattern"></primaryKeyPolicyPattern>
    </primaryKeyPolicy>
   </data-model>
   <business-model>
    <business-package default="business">
        <condition type="package" database-object-type="VIEW" result="review"></condition>   
    </business-package>
    <enrichment>
     <conventions>
          <view-primary-key-convention 
            type="apply-default-primary-key-otherwise-first-one" 
            default-primary-key-names="ID" /> 
         <column-naming-convention
       type="apply-fix-primary-key-column-name-when-no-ambiguity-and-not-natural"
       default-value="ID" />
         <entity-naming-convention type="apply-field-alias-based-on-camelcase"/>
         <column-naming-convention type="apply-field-alias-based-on-camelcase"/>

      <reference-naming-convention
       type="apply-referenced-alias-when-no-ambiguity" is-to-plurialize="false" />
      <reference-naming-convention type="apply-many-to-many-aliasing" is-to-plurialize="false"/>
     </conventions>
    </enrichment>
   </business-model>

  </model>
<!-- -->    <targets catalog-entry="OpenXava">
  </targets>  
<!--    <targets catalog-entry="JPA2" >
  </targets>   -->
 </configuration>
</generator-config>


This configuration will allow you to

  • retrieve sqlserver information for the model northwind (do not forget to specify 'dbo' in schema)
  • assign identity as primary key strategy
  • separate table package from view package. (tables go in package business, views in package review)
  • apply convention
    • use camel case for field and entity (table/view)
    • associate a primary key if not present. The primary key would then be attribute to the field ID if present otherwise the first found.
    • have clean name (here not plurialized because northwind already use plural in DB object name) when possible (i.e. there is no variable name collisition - this occurs when you have more than one relationship between two objects).
  • use the track OpenXava from the catalog.

Steps

Copy this configuration to /mywork/config as northwind-OX.xml 
From a command line run model-generation.cmd/sh northwind-OX.xml 
The result will go in /mywork/output/nortewind/OpenXava

Open a prompt in /mywork/output/nortewind/OpenXava
set OX_HOME and MP_HOME (where you install Openxava and Minuteproject)
  • set OX_HOME= path-to-Openxava // export OX_HOME in linux
  • set MP_HOME= path-to-Minuteproject // export MP_HOME in linux

Run build-nortewind.cmd/sh

This should be enough to get a

Enjoy!

Screenshots

You have Openxava CRUD on all tables and selection on views

Menu entries


Select Order

 Region details
List of products (view)

MinuteProject with console

The code can also be generated with Minuteproject console, it is faster since you do not have to write any configuration. Meanwhile it is more limited since not all the convention are available on the console.

click /bin/start-console.sh/cmd
apply the following parameters



Click on generate and the output will go to /output/northwind/Openxava

You can them process with the same steps as with the configuration.


Conclusion

Minuteproject 0.8.4 offers sqlserver generation and simplifies the configuration of the tracks.
Feel free to pick up others by picking them from the drop down list or by changing the attribute catalog-entry of the node targets by one of the following value:

JPA2
JPA2-ABL
BSLA-JPA2
REST-JEE
REST-SpringMVC
REST-CXF-Spring
REST-CXF-JEE
WS-JEE
JOOQ
Primefaces-Spring
Primefaces-JEE
FitNesse
Solr
OpenXava
Grails
Play
Vaadin
Roo
Maven Spring Hibernate
Maven Spring JPA/Hibernate
SpringService

Remark some tracks are under construction.