Microsoft Access and informative id keys

(Programmers usually don’t use Microsoft Access in multi user apps, but it can be handy if you quickly need to setup some single user personal administration database.)

A thing that I find very cumbersome in Access, is the use of surrogate keys (e.g. autonumbering IDs instead of business keys like “name”), because autonumbering keys are not very informative. This blog post tells you how you can show informative field while at the same time keep using your existing surrogate keys.

Tested with Microsoft Access 2007.

Example of the problem

Here an example of the problem: you have a STUDENT table and a COLLEGE table, where a student has only one college, and a college can have multiple students. (A one-to-many relation.)

Now when you want to select/assign a college to a student, you don’t want to select it using NON-informative college_id’s like this:

instead you want select college_id’s using informative college names like :

And after after selecting the college_id, you want the informative college fields to stay visible in the list, like this:

while at the same time having a transparant relationship using the numbered surrogate id’s as foreign keys.

Composed of two subproblems

The problem consists of two sub problems:

  1. selecting non-informative foreign key id’s transparently using informative fields.
  2. showing (single or multiple) informative fields in the list instead of a non-informative foreign id key

Solution using Lookup Wizard

The easiest way to accomplish this is to use the “Lookup Wizard”. This wizard creates a (new) relationship between the 2 tables, so you have to make sure that a relation doesn’t exist yet.
In this case, college_id will be the foreign key and thus the lookup column.

There are two ways to access the Lookup Wizard:

  1. via the “Lookup Column” button. This creates a new column, so college_id shoudn’t exist yet. Or
  2. by selecting “Lookup Wizard” as “Data Type” for college_id.

The wizard is self-explanatory. When selecting the column in this wizard, make sure you select BOTH the id, AND the columns that you want to be visible. You also have an option to hide the ID, which can be enabled if you prefer.

After finishing the wizard, you’ll see that you can now use informative fields instead of non-informative id’s, while transparently maintaining a foreign key relation using the non-informative id’s.

Manual solution

If you don’t to use the Lookup wizards, you can do it manually yourself in the field properties of college_id:

  1. change the “Display Control” to “List Box” or “Combo Box”
  2. set “Row Source Type” to “Table/Query”
  3. set “Row Source” to a query that contain BOTH the foreign key (college_id) AND the data that you want to be visible as value (cname in this case). The id column MUST be the first column, even if you don’t want it to be visible.
  4. set “Column Count” to the number of columns of the query from step 3
  5. set “Column Widths” for the columns. The ugly part: it is important that you set 0 as width for the id, so that it will be skipped and not be visible in the row, and thus the second column will be visible, which is our informative column.

Multiple informative field in the list

The text above only partially solves subproblem 2; it shows ONLY 1 informative field, even when you selected multiple informative field in the wizard.
For example, it shows

where only “MyFirstCollege” is visible instead of also the value “Rotterdam”. Instead we want:

where the “location” field of the COLLEGE table is also visible.

A manual solution to show multiple informative field in a row (like the last picture), is to concatenate the fields. This can be done by going to the field properties of college_id in the STUDENT table, and alter the SQL query:
SELECT COLLEGE.ID, COLLEGE.cname, COLLEGE.location FROM COLLEGE;
to
SELECT COLLEGE.ID, COLLEGE.cname & ‘-‘ & COLLEGE.location FROM COLLEGE;

Grails & Jetty with multiple contexts

A webapp usually contains both dynamic resources (scripts, config files, html files) and static resources (jpg, zip files, data etc). These resources can be deployed all together as one package, but when you have a lot of static resources, you usually want them to be seperate from the dynamic resources. Reasons might be that you have that much static resources that it will slow down deployment or testing when packaged as a single unit, or you might want static resources to be served by a seperate service to improve performance, or you might want them to be in seperate filesytem hierarchy because the static resources might also be needed outside the webapp. In this post I describe two methods to solve the problem of seperating dynamic from static resources when using Grails and Jetty.

The methods are tested using:

  • Grails 1.0.3
  • Jetty 6.1.4 (default Jetty for Grails 1.0.3)
  • Static Resources 0.5 plugin

Method 1: Static Resources plugin & symbolic links

The Grails Static-Resources plugin is designed to separate dynamic from static resources.

Install:

  1. open a cmd/shell prompt and go into the directory of your Grails webapp project. I will call this directory <myapp-grails-project-base>.
  2. type: grails install-plugin static-resources
    The plugin will now be installed for the current webapp only.

I’ll show by example how the Static-Resources plugin works. Suppose you have a website called example.com and a webapp called myapp, then the webapp is available at http://example.com:8080/myapp/. When you have the plugin installed, everything under the /static/ (filesystem directory) will be available under http://example.com:8088/ when run in dev environment. Notice that port 8088 is used instead of port 8080. The plugin does this by starting a NanoHTTPD, which is a lightweight single class Java HTTP server. The plugin calls this the resource server.
When you go to http://example.com:8088/, you will get a directory listing of all the files located in the static directory, so you can start using them and linking to them, but there is a better way (read on).

If you put a pdf file at <myapp-grails-project-base>/static/archive/catalog2005.pdf (filesystem) then it will be available automatically and directly at http://example.com:8088/archive/catalog2005.pdf in a dev env.
You can hardcode links to http://example.com:8080/archive/catalog2005.pdf manually in the view, but you can also use the plugins dedicated resourceLinkTo tag for this. For the pdf link, this tag is used as: <g:resourceLinkTo dir=”archive” file=”catalog2005.pdf”/>

When running in prod env instead of dev, the resourceLinkTo tag will link to the URI /resources/archive/catalog2005.pdf instead of http://example.com:8080/archive/catalog2005.pdf. And this time it won’t start a NanoHTTPD server, it only changes the link. So in a prod env, you have to map /resources/ to <myapp-grails-project-base>/static/ yourself, in whatever production HTTP server you are using. You could do this in Tomcat 6 by putting something like <Context docBase=” <myapp-grails-project-base>/static/” path=”/resources”/> in the host element of server.xml, or better, in context.xml.

If you don’t want your resources to be located in the <myapp-grails-project-base>/static/ directory, you can use symbolic links in the static directory, and map them to elsewhere. Note that symbolic links are not only available on POSIX systems (Linux etc), but also on Windows systems using special tools. (I’m not talking about Windows Shortcuts, but Windows symbolic links, hard links or junctions.)

Another way is to change the static directory location by modifying <myapp-grails-project-base>\plugins\static-resources-0.5\grails-app\services\ExternalResourceServerService.groovy.

Method 2: a second Jetty webapp context

The static resources method (described above) doesn’t have a complete built-in solution for a production environment; you have to make/map the /resources/ URI yourself.

If want you to use Jetty in both dev and prod env, or if you want to have a separate static resources directory without using the Static-Resources plugin, then read on:

1) Open %GRAILS_HOME%\scripts\RunApp.groovy
2) change

    target( configureHttpServer : &quot;Returns a jetty server configured with an HTTP connector&quot;) {
    def server = new Server()
    grailsServer = server
    def connectors = [new SelectChannelConnector()]
    connectors[0].setPort(serverPort)
    server.setConnectors( (Connector [])connectors )
    setupWebContext()
    server.setHandler( webContext )
    event(&quot;ConfigureJetty&quot;, [server])
    return server
}

to

    target( configureHttpServer : &quot;Returns a jetty server configured with an HTTP connector&quot;) {
    def server = new Server()
    grailsServer = server
    def connectors = [new SelectChannelConnector()]
    connectors[0].setPort(serverPort)
    server.setConnectors( (Connector [])connectors )
    setupWebContext()

    server.addHandler( webContext )
    WebAppContext resourceContext = new WebAppContext(&quot;D:/myapp-grails-project-base/static&quot;, &quot;/resources&quot;)
    server.addHandler(resourceContext);

    event(&quot;ConfigureJetty&quot;, [server])
    return server
}

Now, everything under D:/myapp-grails-project-base/static will be mapped to http://…../resources, in both dev and prod evironment, and will be served seperate from the webapp.

A drawback of placing this in RunApp.groovy is that every Grails app will have a /resources context running next to it.

Note that Jetty 5 and earlier versions of Grails probably needs different code.

Edit:
Grails maintains a temperary directory for scripts in %userhome%\.grails\1.0.3\scriptCache. I you find that modifications aren’t visible, then try deleting that directory.

More info:

Struts2 table annoyance

I was trying out a bit of Struts 2 just to test it out. An annoyance that quickly came up is that the HTML layout of forms are all table based. For example, if you want an input field for your name, then a column-based HTML table will be created automatically for that. The first column contains a HTML label, and the second the input field.

For example, the following Struts2 code:

<s:form action="HelloWorld">
    		<s:textfield name="name" label="Your name"/>
    		<s:submit/>
		</s:form>

will yield this HTML code:

<form id="HelloWorld" name="HelloWorld" onsubmit="return true;" action="/StrutsInAction/chapterTwo/HelloWorld.action" method="post">
	<table class="wwFormTable">
		<tr>
			<td class="tdLabel"><label for="HelloWorld_name" class="label">Your name:</label></td>
			<td><input type="text" name="name" value="" id="HelloWorld_name"/></td>
		</tr>
   		<tr>
			<td colspan="2"><div align="right"><input type="submit" id="HelloWorld_0" value="Submit"/></div></td>
		</tr>
	</table>
</form>

What if you want to work without HTML tables? Or what if you want to put a label on the first row and its input field on the second row, instead of using two columns? You can by using the “simple” theme instead of the default “xhtml” theme. Set it using:

<s:form action="HelloWorld" theme="simple">[/xml]
The generated HTML code will now be:
<form id="HelloWorld" name="HelloWorld" onsubmit="return true;" action="/StrutsInAction/chapterTwo/HelloWorld.action" method="post">
	<input type="text" name="name" value="" id="HelloWorld_name"/>
	<input type="submit" id="HelloWorld_0" value="Submit"/>
</form>

But…. using the simple theme, will drop validation, error reporting, ajax etc……

So if you don’t want those column based HTML tables but do want the default framework functionality (validation etc), you will have to make your own themes……

JSF doesn’t have this problem, there you could just write something like:

<h:form>
	<h:outputLabel for="name">Your name:</h:outputLabel>
	<h:inputText value="#{formAction.name}"/>

This is a bit more coding then using Struts 2, but it gives you the flexibility to place the outputLabel wherever you want, instead of being locked to two HTML columns.

More info:
http://struts.apache.org/2.x/docs/themes-and-templates.html
http://www.vitarara.org/cms/struts_2_cookbook/creating_a_theme

Seam-gen and default ROOT context

If you are using seam-gen to deploy webapps as war-files to a webserver (for example JBoss AS), the root url of your webapp will be something like http://example.com/myproject/. (Where “myproject” is the name that you gave to your webapps when setting it up using seam-gen.) If you want your webapp to be assigned to the root (http://example.com/) (also known as the default ROOT context), then read on.

To change the root context, I perform the following steps. Note, there might be a better way, but this one works for me. If you’re using ejb instead of war, then you probably have to do some additional steps.

Tested using jboss-4.2.2.GA and jboss-seam-2.0.2.SP1.

1) After creating the project, open build.xml. Change

<property name="war.dir" value="exploded-archives/${project.name}.war" />

to

<property name="war.dir" value="exploded-archives/ROOT.war" />

and change

<property name="war.deploy.dir" value="${deploy.dir}/${project.name}.war" />

to

<property name="war.deploy.dir" value="${deploy.dir}/ROOT.war" />

2) Go to the following directory: {your.jboss.home.dir}\server\default\deploy\jboss-web.deployer. And rename ROOT.war to something else. The JBoss manager might then not be available anymore.

3) Now run an explode, and your webapp will be at the ROOT context.

JSF doesn’t validate empty forms

Error:

Caused by org.hibernate.validator.InvalidStateException with message: “validation failed for: MyHibernateEntity”

Are you getting this error while trying to validate a form with empty fields using Seam, JSF and Hibernate? Then read on.

Seam allows you to define validation conditions on a single place: on the Hibernate / JPA entities. And then use this condition in the view without duplicating them.
Sounds nice, but when you try it in practice, you can easily run into the “validation failed” error above.

The problem is caused by JSF. It sounds idiot (and it is), but JSF validators aren’t invoked on empty fields. So the following code already causes the error when submitting an empty comment field

<h:inputTextarea id="comment" value="#{user.comment}" cols="10" rows="3">
   <s:validate/>
</h:inputTextarea>
<h:message for="comment" />
@Entity
public class User {
  // ....

  @NotNull
  private String comment;

  // ....
}

Fortunately a solution is simple, set the required attribute of the input field element to true. Like this:

<h:inputTextarea id="comment" value="#{user.comment}" cols="10" rows="3" required="true">

More info:
http://jamiemcilroy.wordpress.com/2006/10/10/not-quite-what-i-expectedjsf-validation/
http://forum.java.sun.com/thread.jspa?threadID=5055877
http://www.jboss.com/index.html?module=bb&op=viewtopic&t=127793

Seam and URL rewriting with parameters and forms

Making pretty bookmarkable RESTful URLs in JSF is hard. Fortunately the Seam framework solves this problem by supporting HTTP parameters, advanced page navigation, and can use the help of UrlRewriteFilter. But making bookmarkable parameter URLs in combination with a POST form can still be a bit tricky. This blog post shows an example how to solve this problem.

Note: The code snippets used are only fragments. I also assume that UrlRewriteFilter is already installed etc. Seam-gen also contains UrlRewriteFilter, but you might have to add it to the deployment and configure web.xml. Use /rewrite-status to see if it’s working.

Example

We have a website that shows products of a given catagory. In our case, we use the catagory ‘audio’. We want bookmarkable URLs, like http://example.com/products.jsf?catagory=audio instead of http://example.com/products.jsf. And we want pretty URLs that hide technology implementation and parameter names, so in the end we want only URLs like http://example.com/products/audio.

Also we want a HTML POST form on the page that allows us to add products to a catagory (‘audio’ in this case). After submitting this form, we want the URLs to still be pretty, so also here we only want URLs like /products/audio.

Making bookmarkable URLs for first HTTP requests

A URL is bookmarkable when the URL by itself contains all parameters needed to retrieve the page, like
/products.jsf?catagory=audio.

JSF is unable to do this because it cannot handle URL parameters. JSF depends on a HTTP POST (the postback request) to retrieve the page, so the user first has to click a button, after which JSF will show the page with audio products.
The Seam framework solves this problem by handling URL parameters, so we use Seam. (This is just one reason to use Seam around JSF. There are many more.)

Because we are using Seam around JSF, the URL becomes /products.seam?catagory=audio.
To make these parameters, we add the following to pages.xml:

<page view-id="/products.seam">
    <param name="catagory" value="#{productsManager.catagory}" />
</page>

In the code above, productsManager is a backingbean and catagory is a property that will be set to ‘audio’ automatically when the url /products.seam?catagory=audio is requested.

Making pretty URLs for first HTTP requests

After enabling parameters for our URLs, we want them to be pretty. We want /products/audio instead of /products.seam?catagory=audio.
This is done by using UrlRewriteFilter. We add the following code to urlrewrite.xml:

<rule>
    <from>/products/(.*)</from>
    <to>/products.seam?catagory=$1</to>
</rule>

Bookmarkable URLs after a form submit

By default, with the code used above, when we use the form at /products/audio to add a product the catagory ‘audio’, the form will send us to the URL /products.seam without parameters, which is not bookmarkable! To make it bookmarkable, change the code that we have added to pages.xml to:

<page view-id="/products.seam">
    <param name="catagory" value="#{productsManager.catagory}" />
    <navigation from-action="#{productsManager.addProduct}">
        <redirect view-id="/products.seam"/>
    </navigation>
</page>

Where addProduct is an action method bound to the h:commandButton of the form.

When the form is now submitted, the user will be redirected to /products.seam?catagory=audio&cid=1, which is bookmarkable.
Notice that a cid parameter is added by Seam, which Seam uses to track conversations. (To remove it, read further.)

Pretty URLs after a form submit

After submitting the form, we have /products.seam?catagory=audio&cid=1. To make it pretty again (/products/audio), we use outbound urlrewriting. Add the following code to urlrewrite.xml:

<outbound-rule>
    <from>^/products.seam\?catagory=(.*)</from>
    <to>/products/$1</to>
</outbound-rule>

After a form submit, the URL will now be: /products/audio?cid=1. The URL is now prettier, but not completely, we still have the cid parameter. If you don’t need to maintain conversations, the cid parameter can be removed with by changing the outbound rule to:

<outbound-rule>
    <from>^/products.seam\?catagory=(\w*)&amp;cid=\d*</from>
    <to>/products/$1</to>
</outbound-rule>

Disadvantage of the solution

1) Extra delay:
If you would make a HTML form without JSF, you could immediately specify the action URL (attribute of the HTML form), so you could immediately specify to URL you want to send form data to, without all the rewriting. When the browser then submits the POST data (immediately to the pretty bookmarkable URL), the server will immediately return the page with results. So only one HTTP request/response is needed. But with Seam/JSF two request/reponses are needed if you want pretty bookmarkable URLs, which is an extra unwanted delay, just to get the job done in these frameworks.

The following steps happen in the background in our example:

  1. The user enters /products/audio in the browser.
  2. The server responds with the page containing a HTML form like: <form method="post" action="/products.seam">. Notice that it refers to /products.seam without parameters.
  3. The user enters form data and submits it. The browser will generate a HTTP POST request to /products.seam.
  4. The server processes the form data using productsManager.addProduct(), and responds with a HTTP REDIRECT to /products/audio. (A HTTP redirect doesn’t contain a page.)
  5. The browser receives the redirect, and immediately requests for /products/audio, using a HTTP GET.
  6. The server receives the HTTP GET and gives a HTTP OK reponse containing the page the user already was on.

Without the Seam/JSF framework, these steps would be shorter and faster, saving an extra client-server-client roundtrip. Because you could then specify the URL to submit to in the HTML form, and thus won’t need the HTTP redirect. But these extra step can also be used as an advantage; it allow serversided navigation rules. But it would be nice if both options were possible, allowing the developer to choose.

2) SEO:
Google recently announced that they are going to crawl through HTML forms to find URLs.
Because the HTML form is using /products.seam in the HTML code instead of the pretty bookmarkable url, Google might index URLs you don’t want to be indexed. (This can be stopped using robots.txt, but other crawler maybe might not use that file.) 

3) Doesn’t work for validation:
When a form is not validated because the form fields are not passing the specified field conditions, the JSF life cycle will end and thus skip the Invoke Application phase, and thus the navigation rules above will not be executed. I don’t know if there’s an easy solution for this.

Notes:

1)
When using UrlRewriteFilter, make sure you encode XML entities, so a & becomes & in urls that have a querystring.

2)
The querystring of an URL is everything behind the questionmark, like /products.seam?catagory=audio&login=true. In UrlRewriteFilter, like in mod_rewrite, querystrings are handled separatly from the URL. In UrlRewriteFilter you’ll have to use the <condition> element when using a <rule> element for an URL containing a querystring. For example:

<rule>
<condition type="query-string">catagory=(\w*)&amp;login=true</condition>
<from>/products.seam</from>
<to type="redirect">/products/%1</to>
</rule>

Also note that you’ll have to use %1 instead of $1 when using conditions.
You don’t have to use the <condition> element when using querystrings with the <outbound-rule> element.