Monday Jan 08, 2007

Derby's Identity column trivia

In a previous post, I described the SQL command to create a table with an auto increment column in the Derby database. It turns out that the 'GENERATED' keyword can be used in a couple of way.
  • GENERATED ALWAYS AS IDENTITY
  • GENERATED BY DEFAULT AS IDENTITY
For details see the relevant section of the Derby manual here. Essentially the distinguishing fact being - by using 'BY DEFAULT' the user has the ability to specify a value for the identity column as part of a (insert) SQL statement, whereas with 'ALWAYS', the column can never be explicitly edited by the user. Also 'ALWAYS' guarantees uniqueness, but 'BY DEFAULT' does not (use the UNIQUE/PRIMARY KEY attribute to achieve the same effect though)

So, the better suited SQL statement for creating a table in Derby in a RoR application would be

CREATE TABLE ${table-name} ( 
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    ${other-columns-and-constraints} )
Running a simple RoR application with the underlying identifier created via either option works without any issues. However, if you want to run the tests that comes in built with Rails framework the difference is detrimental (since the test framework seems to explicitly want to insert a value for the identifier column).

Wednesday Dec 06, 2006

RoR in GlassFish with Derby as the database

In my previous post I wrote about getting a RoR application to work on GlassFish as a web application. Since the Derby database is embedded in GlassFish why not try to use it as the backend datastore for the RoR-web-application.

Getting a Ruby on Rails application to work with a Derby database should be relatively straightforward. Well it is, but only after you've jumped through a few hoops trying to figure things out for the first time.

1. Modify config/database.yml to something like:

development:
 adapter: jdbc
 driver: org.apache.derby.jdbc.ClientDriver
 url: jdbc:derby://${database host}:${database port}/${database name}
 username: ${username}
 password: ${password}
Derby has the concept of 'database name' and 'schema'. If 'schema' is unspecified it defaults to 'username'

2. Create table(s) in Derby. Points to note here are:
   2.1 Use upper case for all table/column names
   2.2 To create the auto increment identifier column use the following SQL statement

CREATE TABLE ${table-name} ( ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ${other-columns-and-constraints} )


I'm not sure what the SQL standard is on auto increment column types but this was bit of a pain to figure out. Also remember that 'DESC' is a reserved work in SQL terminology so do not use that as a column name.

The Derby jdbc driver should by default be in the classpath of GlassFish application server so no tweaking needed on that account. However, if you are running the RoR application on JRuby/WEBrick combo you'll need to add the derby-jdbc-driver library to the classpath.

That's pretty much all there is to it. Give it a shot !

About

whacko

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today