Clojure clj-record library and Postgresql

I came across a problem when using the clj-record library with Postgresql. When creating a new record in a table, a java.lang.IllegalArgumentException is thrown, despite the fact that the record was successfully created. The "create" function inserts and retrieves the inserted record, so it looked like the problem happened when fetching the newly inserted record. By looking at the source code for the "create" function, I found out that the "insert" function called by "create" calls "id-query-for", which is a multimethod. For postgresql, the function is :

(defmethod id-query-for "postgresql" [_ table-name]
  (str "SELECT currval(pg_get_serial_sequence('" table-name "','id'))"))

Calling Postgresql's pg_get_serial_sequence means that the id column must be called "id", and that it must be "serial". My table layout looked like that :

CREATE TABLE users
(
  id integer NOT NULL DEFAULT nextval('seq_users'::regclass),
  "login" character varying(10),
  first_name character varying(20),
  last_name character varying(20),
  "password" character varying(10),
  email_address character varying(50),
  CONSTRAINT pk_users PRIMARY KEY (id)
)

The column id was properly named "id", but its type was not serial, which was the problem. pg_get_serial_sequence returned null, so the record could not be fetched. I changed my table layout to look like this :

CREATE TABLE users
(
  id serial NOT NULL,
  "login" character varying(10),
  first_name character varying(20),
  last_name character varying(20),
  "password" character varying(10),
  email_address character varying(50),
  CONSTRAINT pk_users PRIMARY KEY (id)
)

This worked as expected. I don't quite like the fact that the column id is fixed to "id", nor the logic to fetch the inserted record...

Using Clojure in Vim

There are already a few plugins for well known editors to use Clojure. Anybody familiar with Vim will want to use it with Clojure. My environment is not complete yet, but here is what I am using so far :

• The VimClojure plugin, for syntax highlighting.
• The TagList plugin. Very useful plugin which shows the content of tags files.
• Exuberant Ctags, used by the TagList plugin.

I'm currently using Vim 7.3, TagList 4.5 and Exuberant Ctags 5.8.

VimClojure plugin

The installation is straight forward, and is fully explained in a readme file in the archive. The following settings has to added in your .vimrc (_vimrc) :

syntax on
filetype plugin indent on

I've also added the following two settings:

let vimclojure#HighlightBuiltins=1 
let vimclojure#ParenRainbow=1

The rainbow parenthesis might be distracting, but I quite like it this way.


I'm leaving the plugin in offline mode, so I didn't configure the Nailgun server. This allows to use nice features like dynamic documentation lookup and Repl running in a Vim buffer, but I'll configure that when I get more familiar with Clojure. For the moment, I use Repl in another window and load the file I'm editing using (load "filename").

(UPDATE: the explanations for configuring the Nailgun client and server are here)

Exuberant Ctags

Nothing more than downloading the latest archive, extracting it, and setting the directory where the ctags executable is into the path.

TagList plugin

One problem: the taglist plugin relies on the filetype detected by Vim and passes the filetype to the exuberant ctags utility to parse the tags. With the VimClojure plugin on, the filetype is set to "clojure", which is not recognized by the taglist plugin. In order to tell the plugin to use the Lisp syntax, the following line should be added to the .vimrc (_vimrc) file :

let tlist_clojure_settings = 'lisp;f:function'

A useful setting is "let Tlist_Exit_OnlyWindow=1", which will close the TagList window if the last file was closed.

In Vim, the ":Tlist" command shows the tag list in a new window.

That's it. With a couple of plugins, using Clojure in Vim becomes much more fun.To generate the help documentation of both plugins, just type ":helptags ~/vimfiles/doc"(or another path where the docs are) in Vim. Take some time to go through the help and check useful settings can be used.

Using Exuberant Ctags on the command line with Clojure files

The following steps are not necessary in order to use the TagList plugin. It should be done only if you want to generate the tags file for Clojure files from the command line.

I downloaded the latest version (5.8). Unfortunately, Clojure is not supported yet. But lisp is. Exuberant Ctags autodetects a file type by looking at its file extension. We can for it to use a different language by using the --language-force option (--language-force=lisp), but it's annoying to do it all the time. What can we do then ? It is possible to override the file mappings via the --langmap option. Let's see how it's done:

Check the supported languages

ctags --list-languages

Ant
Asm
Asp
Awk
Basic
BETA
C
C++
C#
Cobol
DosBatch
...

Clojure is not there.

Check the file mappings

ctags --list-maps
...
Lisp     *.cl *.clisp *.el *.l *.lisp *.lsp
...

The extension above are associated to Lisp.

Adding Clojure file extensions to the Lisp mapping

New extensions can be added via the --langmap option:

ctags --langmap=lisp:+.clj --list-maps
...
Lisp     *.cl *.clisp *.el *.l *.lisp *.lsp *.clj
...

To set it permanently, we have to set this option in the CTAGS environment variable (e.g. set CTAGS=--langmap=lisp:+.clj), or in one of the setting files used by Exuberant Tags (e.g. $HOME/.ctags)

After everything was set, looking at a tags file generated by "ctags *.clj" will confirm that the Clojure extension is properly recognized.

Oracle ROWNUM : What it is and what it's not

What is ROWNUM ?

In Oracle, ROWNUM is a pseudocolumn added by Oracle after the data has been selected. The ROWNUM will be incremented only if the selected row satisfies the condition clause. A pseudocolumn does not physically exist, but it behaves like any table column. The first row returned by a query has a ROWNUM of 1, the second row has a ROWNUM of 2, etc... The ROWNUM can be used to retrieve the top n records of a query. For example, to return a maximum of ten cats from an animal table, the query would look like:

SELECT * from animals WHERE type='cat' and ROWNUM < 11

Pitfall 1: Ordered results

The preceding query returned ten cats. But it doesn't mean that the same ten cats will be returned each time we execute that query, because the fetched records are not ordered. However, adding a simple ORDER BY does not work:

SELECT * from animals WHERE type='cat' and ROWNUM < 11 ORDER BY name

because the results will be first fetched and then ordered. To get the first ten results of an ordered query, we have to fetch records and order them first :

SELECT * from ( SELECT * from animals WHERE type='cat' ORDER BY name) WHERE ROWNUM < 11

Pitfall 2: Getting the nth record

We may think that using the ROWNUM allows to fetch the nth record of a query. For example, getting the second cat of the list :

SELECT * from animals WHERE type='cat' and ROWNUM = 2

Unfortunatly, this doesn't work. In the first paragraph of this article, you might have noticed that some words are in bold : the ROWNUM is set after records were fetched and will be incremented only if the selected row satisfies the condition clause. What's the problem with that ? Imagine that there are ten cats in the animals table. After fetching ten cats, oracle will try to assign a ROWNUM to the first record. The first record ROWNUM is 1. But the query fetches only the record whose ROWNUM is 2. The first record will be skipped, but the ROWNUM will still be 1. The ROWNUM will not be incremented. Thus, the second record ROWNUM is 1. And again, the "ROWNUM = 2" condition is not satisfied. Finally, the ROWNUM will never be incremented, and no records will be fetched. The equality condition will only work with the first record (ROWNUM = 1).

Pitfall 3: Getting all records after the nth record

Well, the problem is the same as with getting the nth record. The condition will never be met. Having a condition looking like "WHERE ROWNUM > 10" will never return anything. The first record whose ROWNUM is 1 will not satisfy the condition, so 1 will be used again for the next record, and so on.

Execution plans

It's interesting to look at the difference of execution plan between "ROWNUM < 11" and "ROWNUM = 2". The first condition uses a "COUNT STOPKEY" operation, which means that Oracle performs a count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause. Even if there are one billion records in the animals table, only the first ten records will be read.

SELECT * from animals WHERE ROWNUM < 11

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    10 |   850 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| ANIMALS |    10 |   850 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM>10)

On the other hand, the erronous "ROWNUM = 2" condition produces the following plan:

SELECT * from animals WHERE ROWNUM = 2

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    80 |  6800 |     3   (0)| 00:00:01 |
|   1 |  COUNT              |         |       |       |            |          |
|*  2 |   FILTER            |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL| ANIMALS |    80 |  6800 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=2)

We can see that Oracle goes through all records and tries to filter them using "ROWNUM = 2".

References

• Oracle Sql Language Reference : 3 Pseudocolumns, ROWNUM Pseudocolumn
• Ask Tom

Oracle : Primary Keys and Non-Unique Indexes

I was asked by a colleague recently about "Unique and Nonunique Indexes" in Oracle Concepts guide :

Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE INDEX. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.

Well, I was as puzzled as she was, by the following two facts:

1. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index.
2. The index they create is not guaranteed to be a unique index.

Looking at the "PRIMARY KEY Integrity Constraints" of the same guide, it says :

Oracle Database enforces all PRIMARY KEY constraints using indexes. The primary key constraint created for a column is enforced by the implicit creation of:

• A unique index on that column
• A NOT NULL constraint for that column

Composite primary key constraints are limited to 32 columns, which is the same limitation imposed on composite indexes. The name of the index is the same as the name of the constraint. Also, you can specify the storage options for the index by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement used to create the constraint. If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one.

The important part is "If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one". I was under the false impression that set a PRIMARY KEY constraint on a table would create a unique index. Well, no, not necessarily. Oracle will reuse (thus not even create) a non-unique index for the constraint, when the columns on which the index is are the same as those for the primary key. This is also true for indexes having more columns than the primary key. As long as the columns used for the primary are the same as the first columns used for the index, the index will be reused. Unique or not.

Another thing to know is "Deferred Constraint Checking". From the Concepts guide :

You can defer checking constraints for validity until the end of the transaction.

• A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to undo.
• If a constraint is immediate (not deferred), then it is checked at the end of each statement. If it is violated, the statement is rolled back immediately.

What happens when a PRIMARY KEY is created as being DEFERRABLE ? Until the transaction is committed, it is allowed to have duplicate values. So a unique index cannot be used. Oracle has to use a non-unique index. Again, a primary key constraint is not necessarily ensured by a unique index.

The fun part is that :

• If a unique index was used by a primary key constraint and that constraint is dropped, the index will also be dropped !
• If a non-unique index was used by a primary key constraint and that constraint is dropped, the index will not be dropped.

References

• Primary Keys and Non-Unique Indexes (What's Really Happening ?) by Richard Foote
• What is the difference between a unique index and a unique constraint?
• Oracle documentation

Experimenting with JPA 2.0 and Spring 3.0.x. A libraries nightmare.

I was making JPA samples while I was reading Spring Persistence with Hibernate. I started to make everything from scratch. The book uses Maven to manage the sample project, but I'm not a Maven person, so I decided to leave Maven and do it the good old way : compile, see a ClassDefNotFoundException raised, find out which library I have to add to the classpath, download it, extract it, sometimes figure out which extracted libraries are not necessary, adding the necessary ones to my project... It sounds like Daft Punk's Technologic ! (compile it, break it, find it, get it, extract it, guess it, add it...) I finally made it, but I can't remember how long it took me to do all these steps. Too long for sure.

Which is why I started studying Maven ! Although I knew about Maven's dependencies management, I never actually used it. There are some good online books at Sonatype. I went straight to the Maven by Example book. This is a good book. I discovered that Maven is much more than a dependencies management tool !

From there, I made a new JPA/Spring project from scratch, using Eclipse's Maven plugin, and while looking at Spring Persistence with Hibernate's xml, I added the necessary dependencies. No effort. Maven did it all.

Here is my pom.xml, using Hibernate JPA, Spring 3.0.5(using transactions without aspectj) and H2Database. It looks big, but it's just a bunch of dependencies declarations, which are made even easier via Eclipse's plugin.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>springwithmaven</groupId>
  <artifactId>springwithmaven</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springwithmaven</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

    <repositories>
        <repository>
            <id>JBoss Repo</id>
            <url>http://repository.jboss.com/maven2</url>
            <name>JBoss Repo</name>
        </repository>
        <repository>
            <id>ibiblio mirror</id>
            <url>http://mirrors.ibiblio.org/pub/mirrors/maven2/</url>
        </repository>
        <repository>
            <id>jboss-public-repository-group</id>
            <name>JBoss Public Maven Repository Group</name>
            <url>https://repository.jboss.org/nexus/content/groups/public/</url>
            <layout>default</layout>
            <releases>
                <enabled>true</enabled>
                <updatePolicy>never</updatePolicy>
            </releases>
            <snapshots>
                <enabled>true</enabled>
                <updatePolicy>never</updatePolicy>
            </snapshots>
        </repository>
    </repositories>

  <dependencies>
 <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-core</artifactId>
     <version>3.0.5.RELEASE</version>
 </dependency>  
 <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-context</artifactId>
     <version>3.0.5.RELEASE</version>
 </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-entitymanager</artifactId>
      <version>3.4.0.GA</version>
    </dependency>
    
    <dependency>
     <groupId>org.hibernate.javax.persistence</groupId>
     <artifactId>hibernate-jpa-2.0-api</artifactId>
     <version>1.0.0.Final</version>
    </dependency>
    <dependency>
     <groupId>com.h2database</groupId>
     <artifactId>h2</artifactId>
     <version>1.3.149</version>
    </dependency>
    <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-tx</artifactId>
     <version>3.0.5.RELEASE</version>
     <type>jar</type>
     <scope>compile</scope>
    </dependency>
    <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-orm</artifactId>
     <version>3.0.5.RELEASE</version>
     <type>jar</type>
     <scope>compile</scope>
    </dependency>
    <dependency>
     <groupId>commons-dbcp</groupId>
     <artifactId>commons-dbcp</artifactId>
     <version>1.3</version>
     <type>jar</type>
     <scope>compile</scope>
    </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>1.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.6.0</version>
        </dependency>
        <dependency>
            <groupId>net.sf.ehcache</groupId>
            <artifactId>ehcache</artifactId>
            <version>1.6.2</version>
        </dependency>    
  </dependencies>
</project>

Book review: Pro Git

Pro Git

I've been using both CVS and SVN at work, and never had the chance to try Git. I decided to learn about Git, so I looked for a book. I found Pro Git's homepage, with the book fully readable online, in several languages. We don't often have the chance to read free quality books online, but this one is 100% free. After spending some time on the train enjoying the first three chapters of the book, I decided to buy it. It's well worth it. The author did a great job presenting Git's functionalities, and the major differences with other version control systems.

The main chapters on Git basics and branching are very easy to follow. The author explains all the major commands, showing the command line and the execution result. You can read the book without even trying anything (although I would recommend to download Git and try as much as possible). The chapter on distributed workflows is particularly interesting. It shows you how Git is used in a project, depending on the size (and other factors) of the project. Finally, the author presents the major Git tools, explains how to customize Git, how to use it with Subversion (would you do that?), and how Git works internally, which is very instructive.

The book is thin, and easy to read, so anybody can finish it in a short time. If you want to get started with Git, you can pick up this book without hesitation.

I have one minor complaint : the book size is a bit different from other Apress books I have (may depend on the print ?). It breaks the balance of my beloved book shelf :)

Confirming Quartz Trigger firing times (especially cron expressions)

The Quartz Scheduler is very useful when it comes to scheduling jobs. It offers a wide range of trigger options, including cron-like expressions. Cron-like expressions are a bit obscure to read, unless you use them often. These expressions are explained in the CronExpression and CronTrigger classes API, and in the online tutorial. I sometimes need to make sure that the triggers will get fired at the proper time. The TriggerUtils class allows to output all firing dates of a trigger between two dates : TriggerUtils#computeFireTimesBetween(Trigger, Calendar, Date, Date). The Calendar parameter here is not a java.util.Calendar, but a org.quartz.Calendar, which can be annoying when using both java.util.Calendar and org.quartz.Calendar in the same class.

Making a sample program to output all firing times between two dates is rather simple :

import java.text.ParseException;
import java.util.List;

import org.quartz.CronTrigger;
import org.quartz.Trigger;
import org.quartz.TriggerUtils;
import org.quartz.impl.calendar.AnnualCalendar;


public class ConfirmTriggerFireTimes {

 public static void main(String[] args) throws ParseException {
  new ConfirmTriggerFireTimes().showTriggerFireTimes();
 }
 
 public void showTriggerFireTimes() throws ParseException {
  CronTrigger trigger = new CronTrigger();
  // Fire at 10:15am every Monday, Tuesday, Wednesday, and Thursday 
  trigger.setCronExpression("0 15 10 ? * MON-THU");
  java.util.Calendar startDate = java.util.Calendar.getInstance();
  startDate.set(2011,java.util.Calendar.JANUARY, 1);
  java.util.Calendar endDate = java.util.Calendar.getInstance();
  endDate.set(2011,java.util.Calendar.JANUARY, 31);
  outputFireTimeList(trigger, startDate, endDate);
 }
 
 @SuppressWarnings("rawtypes")
 private void outputFireTimeList(Trigger trigger, java.util.Calendar from, java.util.Calendar to) {
  List fireTimeList = TriggerUtils.computeFireTimesBetween(trigger, null, from.getTime(), to.getTime());
  for ( int i = 0; i < fireTimeList.size(); i++ ) {
   System.out.println(fireTimeList.get(i));
  }
 }
}

In this example, I want to confirm the firing dates of a trigger set to fire at 10:15am every Monday, Tuesday, Wednesday, and Thursday in January. The resulting output is :

Mon Jan 03 10:15:00 JST 2011
Tue Jan 04 10:15:00 JST 2011
Wed Jan 05 10:15:00 JST 2011
Thu Jan 06 10:15:00 JST 2011
Mon Jan 10 10:15:00 JST 2011
Tue Jan 11 10:15:00 JST 2011
Wed Jan 12 10:15:00 JST 2011
Thu Jan 13 10:15:00 JST 2011
Mon Jan 17 10:15:00 JST 2011
Tue Jan 18 10:15:00 JST 2011
Wed Jan 19 10:15:00 JST 2011
Thu Jan 20 10:15:00 JST 2011
Mon Jan 24 10:15:00 JST 2011
Tue Jan 25 10:15:00 JST 2011
Wed Jan 26 10:15:00 JST 2011
Thu Jan 27 10:15:00 JST 2011
Mon Jan 31 10:15:00 JST 2011

This looks good. We can see that any jobs associated to this trigger will be executed at 10:15am every Monday, Tuesday, Wednesday, and Thursday.