Sybase iAnywhere SQL AAnywhere Mobile and Embedded Database

I'd rather play golf


Thoughts on data management, autonomic computing, and self-managing database systems.

header image

Hibernate and schema generation

June 21st, 2008 · No Comments

I’ve spent some time over the past week rewriting the SQL Anywhere Hibernate dialect routine, which is part of the Hibernate 3.2 distribution. The original dialect routine, inaccurately named SybaseAnywhere.java, inherited virtually all of its functionality from the Sybase11Dialect.java routine (supporting ASE), thereby omitting a variety of language functionality provided by SQL Anywhere: support for SELECT TOP N, GUID support, and support for ANSI join syntax, to name a few.

One of the Hibernate 3.2 features is the ability to generate a relational schema from a mapping file. I would expect most Hibernate applications would be developed for already-extant database instances, so a schema generation function would be rarely used, but for perhaps test databases. This was confirmed in a conversation I had last week with Steve Ebersole, the manager of the Hibernate project at JBoss (Redhat). Hibernate’s support for foreign key generation, as an example, is quite basic. Here’s the code from the base class, Dialect.java:

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
31
32
33
34
35
36
	/**
	 * The syntax used to add a foreign key constraint to a table.
	 *
	 * @param constraintName The FK constraint name.
	 * @param foreignKey The names of the columns comprising the FK
	 * @param referencedTable The table referenced by the FK
	 * @param primaryKey The explicit columns in the referencedTable referenced
	 * by this FK.
	 * @param referencesPrimaryKey if false, constraint should be
	 * explicit about which column names the constraint refers to
	 *
	 * @return the "add FK" fragment
	 */
	public String getAddForeignKeyConstraintString(
			String constraintName,
			String[] foreignKey,
			String referencedTable,
			String[] primaryKey,
			BOOLEAN referencesPrimaryKey) {
		StringBuffer res = NEW StringBuffer( 30 );
 
		res.append( " add constraint " )
				.append( constraintName )
				.append( " foreign key (" )
				.append( StringHelper.JOIN( ", ", foreignKey ) )
				.append( ") references " )
				.append( referencedTable );
 
		IF ( !referencesPrimaryKey ) {
			res.append( " (" )
					.append( StringHelper.JOIN( ", ", primaryKey ) )
					.append( ')' );
		}
 
		RETURN res.toString();
	}

While the above will correctly create a foreign key – the syntax generated will be of the form:

1
[ALTER TABLE <tablename>] ADD CONSTRAINT <constraintname> FOREIGN KEY (fk-COLUMN list) REFERENCES <primary table> [(pk-COLUMN list)]

it isn’t at all flexible. In the new SQL Anywhere 11 release, one can specify referential integrity constraint options that include:

  • how to handle NULL values with multi-column foreign keys by specifying either MATCH SIMPLE or MATCH FULL;
  • whether or not the foreign key is CLUSTERED;
  • whether or not the foreign key should be enforced only at the end of a transaction (CHECK ON COMMIT);
  • what referential actions to enforce (the default is ON UPDATE RESTRICT and ON DELETE RESTRICT);
  • specifying ASC or DESC to the FOREIGN KEY column specification, so that the index generated for the foreign key may be in ascending or descending sequence;
  • the ability to specify UNIQUE, so as to generate a foreign key that embodies a strict 1:1 relationship without the need for an additional UNIQUE constraint (and index);
  • the ability to specify a column ordering for the foreign key that does not match the primary key, enabling complete customization of the index constructed for the foreign key.

Application developers should realize that the flexibility of SQL Anywhere’s support for referential integrity constraints simply isn’t available through the Hibernate functionality.

I hope to have a new SQL Anywhere Hibernate dialect driver ready for beta testing sometime over the summer, which will be available under GPL and included with any forthcoming Hibernate distribution from JBoss.

Tags: Database interfaces and persistent objects · Hibernate · SQL Anywhere · Sybase ASE