4.3. ID Generation Methods

Contents

4.3.1. Introduction

4.3.2. Database Engine ID Generation

4.3.3. Business Object ID Generation

4.3.4. Sequence Table ID Generation

4.3.5. ID Generator Objects

4.3.6. Choosing an ID Generation Method

4.3.7. The ID Generation Form

4.3.1 Introduction

Persistent objects must be identifiable by a unique object ID. An object ID needs only to be unique within a business class (i.e. two objects can have the same ID as long as they belong to different business classes). VBSF supports object IDs of any data type, and multi-attribute object IDs that span multiple columns. The object ID must be mapped to one or more columns in the database table and its value must be unique within the table. This is accomplished by defining the object ID column as the table's primary key, or by defining a unique index for it. This section discusses the different ways in which an object ID can be defined and generated.

VBSF supports object IDs that are generated by any of the following sources:

1. The database engine.
2. The business object itself.
3. ID tables.
4. An ID generator object.

4.3.2. Database Engine ID Generation

VBSF supports object IDs that are generated by the database engine using any of the methods below:

1. Counter Columns
2. Sequences
3. Stored Procedures
4. Database Triggers
5. Database Functions

Counter Columns

Counter columns are integer fields that are automatically assigned a sequential value by the database engine upon row insertion. Many databases including Access, MS SQL Server, Sybase, Informix, and Paradox support this type of field (referred to as an auto-increment, identity, or serial). The counter field should be defined as the primary key of the table.

VBSF provides support for counter fields by automatically retrieving the counter value from the database and assigning it to the object ID attribute. This support can be enabled by setting the ID method to AUTO.

Sequences

The Oracle, Postgres and InterBase databases provide a sequential number generator mechanism named Sequences. VBSF can automatically use the sequence generator to assign the value of the object ID column during row insertion. This support can be enabled by setting the ID method to SEQUENCE, and supplying the name of the sequence.

Please note the following points about sequences:

1. Sequences are only supported for the Oracle, Postgres and InterBase databases.
2. If a sequence generator is activated in an INSERT trigger, do not use this method.
3. For InterBase, the GEN_ID function is automatically invoked with a step value of 1. If another step value is required, use a Stored Procedure to wrap the function instead.

Stored Procedures

VBSF can also utilize stored procedures that generate a sequential or unique value to assign the object ID prior to row insertion. Solid, for example, provides a sequence generator that is invoked within a stored procedure. VBSF executes the stored procedure, retrieves the output value, and assigns it to the object ID before saving any new objects to database. The stored procedure must not take any input parameters and must return only one output parameter of a type compatible with the type of the ID attribute.

This support can be enabled by setting the ID method to STORED PROCEDURE and supplying the name of the stored procedure. For example if the procedure is declared in the database as shown below:

CREATE PROCEDURE NEXTCUSTOMERID ( OUT ID )

then simply enter NEXTCUSTOMERID as the procedure name and VBSF will call the stored procedure using the following syntax:

{CALL NEXTCUSTOMERID(?)}

Stored procedures can also be used to wrap database functions that generate unique IDs, such as MS SQL Server's NEWID() or Oracle's SYS_GUID(). For example, in MS SQL Server 7.0 and above, you can generate a Universal Unique Identifier (UUID) value mapped to a VBSF BINARY attribute type and a universalidentifier column data type using the NEWID() database function. In the example below, a stored procedure is used to wrap a call to this function in order to retrieve the next UUID value:

create procedure gen_id @@nextid uniqueidentifier OUTPUT as
select @@nextid = NEWID()

In the event a driver does not support the standard JDBC stored procedure escape syntax, or you need to specify another call syntax, you can specify the full call syntax. You may also specify the use of a different parameter marker in the call syntax.

By default VBSF retrieves the ID value using an output parameter. In some databases such as Informix it is not possible to define a stored procedure that returns a value as a JDBC output parameter, so the value must instead be returned in a result set. In this case, you can force VBSF to retrieve the ID value from a result set by specifying the full call syntax and using the dollar sign ($) as the first character in the procedure name field. Below is an example:

${? = call GET_NEXT_ID()}

Database Triggers

Sometimes the value of a primary key field is automatically generated by a database trigger. VBSF can automatically read the ID value from the database after every row insertion. This support can be enabled by setting the ID method to TRIGGER.

Database Functions 

Similar to sequence generators, some databases provide sequencer functions that generate sequential values. IBM's DB2, for example, has a sequencer function named GENERATE_UNIQUE. The function can return only one output value. The output of this function is placed directly in the VALUES clause of an INSERT statement in the location where the ID value would go. This support can be enabled by setting the ID method to FUNCTION and supplying the function's full call syntax, including any parenthesis.

For example, to specify that IBM's DB2 sequencer function GENERATE_UNIQUE be used to generate ID values, you would enter

GENERATE_UNIQUE()

as the call syntax and the following INSERT statement would be generated by VBSF each time a new row is inserted in the database:

INSERT INTO TableName (IDColumnName,.....) VALUES (GENERATE_UNIQUE(),....)

If the function takes input parameters, they must also be specified in the function call. For example, Borland's InterBase requires that the name of the generator and a step value be provided as arguments to their ID generator function GEN_ID. Assuming the generator is named 'cutomer_no_generator', and the step value is 5, you would specify the call syntax as shown below:

GEN_ID(cutomer_no_generator,5)

After every row insertion VBSF will automatically read the ID value from the database and assign it to the object ID attribute.

4.3.3. Business Object ID Generation

Although this approach is not recommended, some applications generate the object ID according to a business rule. For example, a customer ID could be generated by concatenating the first three letters of the first name with the last name of the customer. In this case, the burden of generating the object ID falls on the business object itself. Failure of an object to generate unique object IDs could cause unpredictable results. This support can be enabled by setting the ID method to EXTERNAL. A business class with a multi-attribute object ID must have its ID method set as EXTERNAL, since none of the automatic ID generation options discussed in this section work on multiple values.

A new business object using this setting should set the value of its ID attribute(s) before the object is inserted in the database. It is strongly recommended that the corresponding ID column(s) be defined as a primary key (or at least with a unique index) so the database can ensure ID uniqueness, and to speed up queries.

4.3.4. Sequence Table ID Generation

Some database engines such as CA-OpenIngres cannot automatically generate the value of the object ID column using any of the methods described above. In that case VBSF can still generate an ID value using a database sequence table. A sequence or ID table is a one row table with one or more integer type columns. Each column holds the next ID value to be assigned to the object ID of an instance of a persistent class. When a new object is saved to the database, VBSF automatically reads the next ID value from the ID table, assigns it to the object ID, and increments the ID value in the ID table. ID tables are also useful for generating IDs in applications that must run against multiple target RDBMS systems, because this method is generic and works on all databases.

ID tables must be manually created and populated with initial values. There are two approaches to creating sequence tables:

1. Create a one row, one integer column table for each persistent class.
2. Create a one row, multiple integer column table, where each column holds the object ID of a persistent class. In this case one ID table holds ID values for multiple persistent classes.

The first approach may reduce contention and is recommended for applications with heavy insertions. Note that you may also use a combination of both approaches.

It is also possible (and sometimes necessary in order to support heterogeneous collections) to assign the same ID table and column to multiple persistent classes. Doing this will generate IDs that are unique across all the classes that share the ID table. See the vposddemo and vshapesdemo sample applications for examples on multiple classes that share the same ID table.

Below is an example of two ID tables that use the first approach to hold the next customer and vendor IDs:

ID table support can be enabled by setting the ID method to ID TABLE and supplying the ID table and column name.

4.3.5. ID Generator Objects

VBSF also supports the ability to plug-in your own ID generator object. This can be useful when none of the methods above are suitable. Only ID generator objects that create IDs of type long and String are supported. An ID generator class must implement either the BOLongIDGenerator or the BOStringIDGenerator interface depending on the type of ID being generated. The BOLongIDGenerator interface defines the two methods below:

public void setDatabase( Database db ) ;
public long createID( String className ) ;

The BOStringIDGenerator interface defines the two methods below:

public void setDatabase( Database db ) ;
public String createID( String className ) ;

The setDatabase() method is invoked right after VBSF instantiates an ID generator object. A reference to the database object is necessary if the ID generator object must access the database or retrieve other objects to perform its function. If the database object is not used by the ID generator, an empty setDatabase() method may be declared. The createID() method is invoked by VBSF every time a new object of a class assigned to the ID generator object is created. This means objects of a class assigned to an ID generator object are assigned IDs at the time of instantiation, instead of when they are first saved to the database (which is when all other methods described in this section assign the ID). VBSF passes the createID() method the name of the class for which an ID is to be generated. If an ID generator object creates IDs for only one class, this argument may be ignored.

Below is the source code for the ID generator class defined in the viddemo sample application:

public class IDGenerator implements BOLongIDGenerator {

private Database db_ ;
private int lastID_ = 0 ; //to avoid duplicate Ids

IDGenerator() {
    super() ;
}

/**
* Sets the database object this ID generator object can use
* to retrieve persistent objects from the database.
* This is useful in case IDs are generated based on values
* stored in database tables.
* This method is invoked as soon as the setIDGenerator()
* method of BOP_Database/BOMetaData is called.
* @param db database object
*/
public void setDatabase( Database db ) {
    //although in this example the db_ member is
    //not actually utilized by the createID() method,
    //it is implemented for demonstration purposes
    db_ = db ;
}

/**
* Creates and returns a new unique object ID of type long.
* The ID is generated using the Date's getTime() method.
* @param className name of class for which an object ID
* is to be generated
*/
public synchronized long createID( String className ) {
    //because many databases do not support Java
    //long values convert everything to int
    int currID = (int)System.currentTimeMillis();
    while (currID == lastID_) {
        try {
            Thread.sleep(1);
        } catch (InterruptedException e){}
        currID = (int)System.currentTimeMillis();
    }
    lastID_ = currID;
    return (long)currID;
}

}

An ID generator objects is assigned to a persistent class by setting the ID method to ID OBJECT and supplying the fully qualified class name of the ID generator object. Only one ID generator instance is created per ID generator class. This means that when multiple persistent classes are assigned to the same ID generator class, they will be sharing the same ID generator object. In this case the argument to the createID() method can be used to determine for which class the object ID is being created.

4.3.6. Choosing an ID Generation Method

Choosing an appropriate ID generation method depends on the application requirements. We prefer to have IDs generated by the database than by the application itself. However, when the ID is generated by the database the actual ID value is not known until after insertion in the database. If your application requires knowledge of ID values in advance you might want to assign the ID value externally or via an ID Generator object.

The choice of which method to use when IDs are generated by the database is determined by the target database of your application. If your application must run with many different types of databases, then ID Table generation is the best approach because it is database independent. If your application is targeted towards a specific database then we recommend using either a Counter column, a Sequence, or a Stored Procedure, depending on your database capabilities. Use of Triggers and Functions is only recommended as a last resort because there might not be a reliable way of obtaining the value of the ID column after row insertion. If your database can only generate unique IDs via a function, then we recommend that you wrap the function inside a stored procedure and have VBSF invoke the stored procedure instead. This is demonstrated in the MS SQL Server UUID example.

4.3.7. The ID Generation Form

The ID generation method to be used for each persistent class in the schema is specified using the ID Generation form shown below. The default method is AUTO. This form is displayed when the "ID Generation" sub-header of a persistent class is clicked in the tree.

In the Method field you can select from the combo box drop down list the ID generation method to be used. The following table summarizes the ID generation methods already discussed in the previous sections:

If the AUTO, TRIGGER, or EXTERNAL methods are selected no further information needs to be entered and the remaining two text fields remain disabled.

If the SEQUENCE method is selected the second text field will be enabled and its label will prompt you for the name of the sequence.

If the STORED PROCEDURE method is selected the second text field will be enabled and its label will prompt you for the name of the stored procedure. You may enter the name of the procedure or its full call syntax. The third text field will also be enabled to allow you to enter a custom parameter marker. You may leave it blank to use the standard ODBC/JDBC question mark (?) as parameter marker.

If the FUNCTION method is selected the second text field will be enabled and its label will prompt you for the name of the database function. You must enter the function's full call syntax.

If the ID TABLE method is selected the second text field will be enabled and its label will prompt you for the name of the ID table. The third text field will also be enabled and its label will prompt you for the name of the ID table column.

Finally, if the ID OBJECT method is selected the second text field will be enabled and its label will prompt you for the name of the ID generator class. You must enter a fully qualified class name

Next Section 

Return to Table of Contents