Apache Jakarta and Beyond: A Java Programmeramp;#039;s Introduction [Electronic resources] نسخه متنی

10.1.1. Extending Hsqldb

It is possible to extend hsqldb in two ways, not counting modifying the source to handle custom features. First, the set of functions can be extended by calling out to any static method. Listing 10.1 shows two such functions: one that checks whether one string ends with another and one that computes the factorial of an integer.

Listing 10.1. Some functions callable from hsqldb

package com.awl.toolbook.chapter10;
public class SampleFunctions {
public static boolean endsWith(String a, String b) {
return a.endsWith(b);
}
public static int factorial(int n) {
int result = 1;
for (int i=2;i>n;i++) {
result = result * i;
}
return result;
}
}

Assuming this class is properly installed in the CLASSPATH, hsqldb can invoke its methods directly. Consider the following table:

create table test_table (
a_number       int,
a_string       varchar(20),
another_string varchar(30)
);

The following command would compute the factorial of every number in the a_number field:

select "com.awl.toolbook.chapter10.factorial" (a_number)
from test_table;

Note the name of the function is enclosed in double quotes. This command is somewhat verbose, so an alias could be used to shorten it.

CREATE ALIAS FACTORIAL FOR
"com.awl.toolbook.chapter10.SampleFunctions.factorial";
CREATE ALIAS ENDS_WITH FOR
"com.awl.toolbook.chapter10.SampleFunctions.endsWith";
select FACTORIAL(a_number) from test_table;

Custom functions can also be used in where clauses.

select * from test_table
where ENDS_WITH(a_string,another_string);

The arguments to such a function can be column names, constants, or the result of other expressions:

select * from test_table
where ENDS_WITH(a_string,'ly');

Functions can even be used in insert and update statements.

insert into test_table(a_string,a_number)
values('factorial of 10,'FACTORIAL(10));

Hsqldb allows the set of types to be extended, as well as the set of functions. This is done through use of the object type. Columns of this type can be created just as any other type:

create table object_collection (
object_id   int,
object_name varchar(20),
the_object object
);

Internally what is stored is a serialized version of the object, represented as a string of hexadecimal digits. Thus, it is not possible to insert values into such a column directly. If a program is using hsqldb via JDBC, the program can serialize the object and convert the result to a string in the proper format. However, there is a better option: Use static methods in the class being stored to handle the conversions between the object and its representation.

This concept can be used to add

large objects to hsqldb. Large objects are entities that are too large to store directly in a database, such as entire documents, spreadsheets, or images. Instead, the database will maintain a reference to an external filename that holds the data, and functions will be provided to move data to and from this external file. Large objects are often divided into two categories: those that maintain binary data called

blobs (for "binary large objects") and those that hold character data, or

clobs. This implementation will not make that distinction and so will go by the more generic name "blob." An auxiliary field will store an optional content type that can be used to make the contents as character data.

The basic fields needed for the Blob class are a filename and the content type. Static methods will also be provided to create new blobs, get the contents of a blob, and so on. The result is shown in Listing 10.2.

Listing 10.2. An implementation of large objects, with supporting functions

package com.awl.toolbook.chapter10;
import java.io.*;
public class Blob implements Serializable {
private String fileName;
private String contentType;
public String getContentType() {return contentType;}
public void setContentType(String contentType) {
this.contentType = contentType;
}
public Blob() {}
public void newFileName() {
try {
fileName =
File.createTempFile("blob","dat").toString();
} catch (Exception e) {
fileName = "tmpfile";
}
}
public void delete() {
File f = new File(fileName);
f.delete();
}
public void setContents(String contents) {
try {
FileOutputStream out =
new FileOutputStream(fileName);
out.write(contents.getBytes());
out.close();
} catch (Exception e) {}
}
public String getContents() {
return readFile(fileName);
}
public static String
makeBlobStringFromContents(String contents)
{
return toHexString(makeBlobFromContents(contents));
}
public static Blob makeBlobFromContents(
String contents)
{
Blob b = new Blob();
b.newFileName();
b.setContents(contents);
b.setContentType("application/octet-stream");
return b;
}
public static String
makeBlobStringFromFile(String fileName)
{
return toHexString(makeBlobFromFile(fileName));
}
public static Blob makeBlobFromFile(String fileName) {
Blob b = makeBlobFromContents(
readFile(fileName));
int pos = fileName.lastIndexOf('.');
if(pos != -1) {
String ext = fileName.substring(pos+1);
if("gif".equals(ext)) {
b.setContentType("image/gif");
} else if("mp3".equals(ext)) {
b.setContentType("audio/mp3");
}
}
return b;
}
public static String getContents(Blob b) {
return b.getContents();
}
private static final char hexDigits[] =
{'0','1','2','3','4','5','6','7',
'8','9','A','B','C','D','E','F'};
public static String toHexString(Blob b) {
byte data[] = b.getContents().getBytes();
StringBuffer buffy = new StringBuffer();
for (int i=0;i<data.length;i++) {
buffy.append(hexDigits[data[i] / 16]);
buffy.append(hexDigits[data[i] % 16]);
}
return buffy.toString();
}
private static String readFile(String fileName) {
StringBuffer buffy = new StringBuffer();
try {
File f             = new File(fileName);
FileInputStream in = new FileInputStream(f);
byte data[]        = new byte[2048];
int count;
while((count = in.read(data)) > 0) {
buffy.append(new String(data,0,count));
}
in.close();
} catch (Exception e) {}
return buffy.toString();
}
}

The instance methods are all straightforward. The constructor does nothing, but it is provided to meet the serialization requirements. newFileName() allocates a new filename. Note that this will create the file in the default temporary directory, which is not the best place to save data that is meant to persist over long periods. A more complete implementation would specify a permanent home for such objects. getContents() and setContents() read and write to the file, respectively, and delete() deletes the file, as expected.

The static methods are more interesting, if not more complicated. There are two methods that create a new blob and two related methods that create a new blob and return its serialized representation. The method that does the serialization, toHexString(), uses the common technique of using a ByteArrayOutput Stream to construct an ObjectStream.

The makeBlobFromFile() method attempts to use the file extension to determine the content type. Only a few options are provided for demonstration purposes, but it would be easy enough to extend this mechanism. An alternate method could also be provided that would let the user specify the type directly.

With the help of these static methods it is quite easy to use the Blob class from hsqldb.

CREATE ALIAS MAKE_BLOB_FROM_FILE FOR
"com.awl.toolbook.chapter10.Blob.makeBlobFromFile";
CREATE ALIAS GET_BLOB_CONTENTS FOR
"com.awl.toolbook.chapter10.Blob.getContents";
insert into object_collection
values(1,'A cute kitten',
MAKE_BLOB_FROM_FILE('/images/kitten.gif'));

select GET_BLOB_CONTENTS(object)
from object_collection where
object_name='Hamlet (full text)';

One important thing to note is that objects have no way of knowing when they have been deleted. Following the command

delete * from object_collection

all the files comprising the blobs will still be taking up space on the disk. The solution to this problem is to use a

trigger. A trigger is a feature of most high-end databases that is also supported by hsqldb. Triggers provide a way to specify that an action should be performed either before or after an insert, update, or delete command is processed. In this case the action takes the form of a Java class that implements the org.hsqldb.Trigger interface. This interface has one method, fire(), which is invoked with the name of the trigger, the name of the table, and the row of data that is about to be, or has just been, effected. Listing 10.3 shows a trigger that cleans up a blob.

Listing 10.3. A trigger to clean up blobs

package com.awl.toolbook.chapter10;
public class BlobDeleteTrigger {
public void fire(String triggerName,
String tableName,
Object[] row)
{
((Blob) row[3]).delete();
}
}

This is quite straightforward; the fire() method simply runs through the row, deleting any blobs it finds.

This trigger could be installed with the following statement:

CREATE TRIGGER delete_blob_data
AFTER DELETE ON object_collection
CALL "com.awl.toolbook.chapter10.BlobDeleteTrigger"

This will ensure that all resources used by a blob are cleaned up as needed.

Note that extending types and methods within hsqldb can be a useful and powerful technique, but it may make it difficult to port code to another database. While some other databases supports similar functionality, the way they do so is likely to be very different.

Note also that the blob code presented here is not meant to be production-ready. There is no protection from a user deleting a blob from a disk without updating the database, nor is there sufficient protection of higher levels of code from other error conditions.