Spider Object Database Programmer's Guide

Developer’s Guide Version 2

Table of Contents

What is an object-oriented database?

Design rational behind the Spider Object Database
    Native Object Pascal
    Simplicity
    Database independence

Spider Object Database's Architecture

Database design guidelines with Spider Object Database

Making a class persistent
    Streaming Class Hierarchies
    Class Registration
    Class Versioning

Creating the Object-Relational Mapping
    Field Types and Indices
    Defining Indices
    Using Autoincrement Fields

Using a TObjectDatabase Instance
Using Transactions

Choosing a Driver
    Database Architecture
    SQL or ISAM?
    Performance
    Size of the database
    Number of simultaneous users
    Robustness
    Comparison Table

Using the TObjectFile Class
    Traversing
    Searching
    Inserting
    Updating
    Deleting

Using the TObjectUpdate Class
    Inserting
    Updating
    Deleting

Using the TObjectQuery Class
    Specifying the query
    Traversing
    Object lookup

Creating a Driver

Introduction

The purpose of this guide is to provide the developer with the fundamental skills in order to be proficient with the Spider Object Database. Creating databases using objects as the basic storage unit is a very different approach than traditional relational databases. Although some of the concepts to relational databases, such as normalization and avoiding data redundancy still apply, the developer will need to acquire very solid object-oriented modeling skills. This is essential, as the entire database will be built upon the classes created within the programming language. There lies the fundamental difference between traditional database systems; the information stored in an object-oriented database does not require any conversion to be used within the native programming language. This guide does not discuss object-oriented analysis, design, and programming topics. There are many articles and books on the subject.

For a detailed reference of the library, consult the online help files.

What is an object-oriented database?

An object-oriented database is a database management system that handles the persistent storage and retrieval of objects. Unlike a relational database, whose characteristics may be easily expressed using mathematical set theory, it is very hard to describe an object-oriented database system using mathematical equations. Some objects may be as simple as one dimensional records in a relational database, but many objects are complex in nature. Objects are often inherited from other objects, and can recursively contain other objects.

Object-oriented databases evolved in academic circles during the 1980's. Since the 90's, commercial acceptance has continuously grown because of the need to store complex data types (objects). Often cited examples are CAD/CAM (computer assisted design/manufacturing), engineering, and multimedia related programs. For examples, data structures used by 3D CAD programs are very hard to express using the row/column approach in relational database. This situation is referred to as an impedance mismatch.

The term impedance mismatch has its roots in electrical circuit theory. This term is often used in object-oriented circles to advocate object-oriented databases (OODB). OODB's are based on persistent objects; entire databases are composed of objects, instead of one dimensional records. So what is this concept of impedance mismatch? In electrical terms, when two circuits are coupled together, in order to get the maximum amount of power transfer from one to the other, the output impedance of one circuit must the same as the input impedance of the other. When the impedance is not the same, the maximum power transfer is not obtained. So what does this have to do with objects? The analogy is really between the efficiency of the power transfer and the software development efficiency.

A good example is in Delphi itself. Forms are created at design time by dropping components on the form. As you probably know, the design time information is stored in a file with the DFM extension. This file is actually a persistent version of the form that was created at design time. The form's properties, and the components on the form are streamed to the DFM file. Forms can be very complex, with many nested components. Imagine if this information would be maintained in relational tables? There would be an impedance mismatch; the form information would have to be converted into a different format, using tables and rows.

Design rational behind the Spider Object Database

Native Object Pascal

Most existing OODBM systems focus on C++, Java, or SmallTalk languages. Object Pascal has a very well designed object model, so Spider Object Database's goal is to provide an object-oriented database system that directly supports Object Pascal. Once a class is written in Object Pascal, instances of the class can be stored in a database without any binding or conversion code!

Simplicity

Component or modular based software is evolving at a phenomenal rate. This is a great concept, system requirements are forever more demanding, so software construction must become more and more component or modular based. This allows software development to maintain a reasonable pace to meet the demand. On paper this approach seems simple. In practice, the interaction of multiple software layers leads to side effects. As software leaders keep pushing new component standards, the complexity of deploying component software, especially in networked environments, is often overlooked. Network administrators constantly deal with version and deployment problems related to external DLL, OLE, OCX, ActiveX, ... software modules. Delphi, especially with its internal (native) VCL component framework, encourages simplicity. Spider Object Database continues along those lines, providing a class library that does not impose any additional external software modules.

Database independence

Spider Object Database's architecture provides complete isolation from the underlying physical database. This is very important as databases vary tremendously with respect to performance, size, and robustness. Any database system, object-oriented or not, should not impose a particular physical database. The choice of the underlying database must be determined from the requirements of the application itself. Almost all "front end" relational development tools, including Delphi, have adequately achieved database independence. The same should apply to object-oriented database systems.

Spider Object Database's Architecture

The object-oriented database abstraction provided by Spider Object Database is divided into five primary classes. A TObjectDatabase instance is created to perform database level operations such as opening/closing sessions and transaction control (commit/rollback).

A TObjectFile instance is analogous to a TTable instance in a relational database. It allows reading and modifying objects in an object file.

A TObjectQuery allows reading objects from an object file based on an SQL query. A TObjectUpdate instance allows adding, updating, or deleting objects in an object file.

SQL based engines will use the TObjectQuery and TObjectUpdate classes. ISAM (indexed sequential access method) based databases will use the TObjectFile class to read and modify objects. Some databases, such as Turbo Power's Flash Filer and Elevate Software's DBISAM allow both ISAM and SQL access.

The TOdbDriver instance provides the link with the underlying database. The database independence is achieved by deriving new classes from this abstract class. This class defines the interface which TObjectDatabase, TObjectFile, TObjectQuery, and TObjectUpdate instances use to communicate with the underlying database.

Database design guidelines with Spider Object Database

In order for any database to be usable, the data contained must be structured in a meaningful manner. This structure is defined by a data model. Databases have been successfully created with different models, such as hierarchical, network, and relational. Hierarchical and network databases have been used extensively in the mainframe world, but the most popular model since the 1980's has been the relational model. The object-oriented data model has evolved from object-oriented programming languages, which wanted to store objects directly into a database without having to convert objects into a different format.

Object-oriented database system are generally divided into two broad categories; pure and hybrid. Hybrid systems are built by extending a relational database system to support objects. Pure systems are built from the ground up, concentrating strictly on an object-oriented approach without any constraints imposed by an existing model. These systems often use a proprietary data format.

A hybrid approach is used with Spider Object Database to take advantage of numerous existing database systems, in order to achieve database independence. The relational model is also very well established with a proven model. Some concepts in the relational model may be argued to be universal; a relation establishes a conceptual link between two entities, independent of the object model.

Spider Object Database makes a distinction between two types of relations, external and internal. An external relationship is borrowed from the relational model. An internal relationship is managed by the object itself. It is the internal relationships which provides support for complex objects. Internal relationships are created using container classes.

Here is an example of a typical of a customer invoicing database using the relational model.

In this relational model four tables are required. The three relations are external.

Let’s analyze this example with the Spider Object Database approach. This requires identifying the object identities. In a relational model, all one-to-many relations must be expressed with relations between tables. With an object-oriented approach, many one-to-many relations may be eliminated, as an object can contain a list of other objects, by using aggregated container objects. Many container classes all with built-in persistent support, including as lists, stacks, queues, and arrays, are available to allow creating internal relations.

An invoice object contains a list of invoice items. This immediately eliminates the external relation between invoice and invoice items.

A product is an autonomous object. The one-to-many relation between products and invoice items remains external, as invoice items are already contained within an invoice.

The one-to-many relationship between Customer and Invoice objects is interesting. If the same rational as the relationship between Invoice and Invoice Items is used, it would be immediately apparent that the relationship should be internal, as a customer can be modelled as "containing" a list of invoices . This brings up the issue of lock granularity. In a multi-user environment, objects should always be locked before being updated. If the Customer object is to have an internal relationship with the Invoice object, this has the implication that only one invoice for a customer can be created at the same time. The decision whether or not this relationship should be external or internal is dependent on the application requirements. Here is the same customer invoicing database using the Spider Object Database model, using an internal relationship between Customer and Invoice objects.

This model yields only two object files, as opposed to four in the relational model.

Making a class persistent

Persistent objects are objects that persist beyond their normal life span. All objects that are created in a Delphi application are only accessible during the execution of the application. This is the normal life span for a non persistent object. The idea of persistent objects is to give the ability to objects to store themselves on some persistent stream (usually files), so that they may later be reconstructed. The action of storing an object on a persistent stream is called streaming, which is why persistent objects are also known as streamable objects.

Object streaming in Delphi's VCL library is very component centric. The documentation indicates that classes derived from TPersistent may be stored on streams. In reality, the methods of TStream, WriteComponent and ReadComponent, clearly indicate that the streaming architecture in Delphi is focus primarily on component streaming. In addition, the streaming mechanism in Delphi is virtually an undocumented feature.

The Spider Object Database uses a streaming architecture that is easy to comprehend and non-intrusive. It is not required to derive all streamable objects from a common base class. This is very important for object-oriented languages that support single inheritance. This means that existing class hierarchies need not be changed to implement persistent objects. All that is required to make an object streamable is to write two routines, and to register the class.

To make a class persistent two procedures must be created; one to read the class's data members, and another to write the class's data members. Then the class must be registered, usually in the initialization section of a unit. If the class changes over time, multiple versions may be required.

The read and write procedures must have the same signature as specified by the TObjectReadProc type definition and the TObjectWriteProc type definition respectively.

TObjectReadProc = procedure(
const Obj : TObject;
const Reader : TObjectReader );

TObjectWriteProc = procedure(
const Obj : TObject;
const Writer : TObjectWriter );

The object to be read or written is passed in the Obj parameter. The TObjectReader and TObjectWriter objects provide methods to stream various database types. Below is a table showing the various methods of the TObjectReader/TObjectWriter classes used to stream the data belonging to Obj.

Method Purpose

ReadInteger / WriteInteger Read and write data members of types such as Byte, Word, Integer, and Cardinal.

ReadFloat / WriteFloat Read and write data members of types such as Single, Double, and Extended.

ReadString / WriteString Read and write data members of type String.

Read / Write Read and write raw data in binary form.

ReadChildObject / WriteChildObject Read and write object data members.

It is generally easier to first create the write procedure. After the write procedure is created, the read procedure has the same structure as the write procedure, with the obvious exception the data is read instead of written.

Here is a simple template for read and write procedures:

unit ExampleObject;

interface

uses
ObjPerst; { TObjectReader, TObjectWriter }

TExampleObject = class(TObject)
    { basic types }
    Data1 : String;
    Data2 : Double;

    { binary data }
    BufSize : Integer;
    pBuffer : ^TByteArray;

{ child objects }
ChildObject : TChildObject;

constructor Create;
destructor Destroy; override;
end;

procedure WriteExampleObject(
const Obj : TObject;
const Writer : TObjectWriter );

procedure ReadExampleObject(
const Obj : TObject;
const Reader : TObjectReader );

implementation

constructor TExampleObject.Create;
begin
inherited Create;

BufSize := 4096;
GetMem(pBuffer, BufSize);

ChildObject := TChildObject.Create;
end;

destructor TExampleObject.Destroy;
begin
ChildObject.Free;
FreeMem(pBuffer, BufSize);

inherited Destroy;
end;

procedure WriteExampleObject(
    const Obj : TObject;
    const Writer : TObjectWriter );
var
    ExampleObject : TExampleObject;

begin
ExampleObject := Obj as TExampleObject;

    { basic types }
    Writer.WriteString(ExampleObject.Data1);
    Writer.WriteFloat(ExampleObject.Data2);

    { binary data }
    Writer.WriteInteger(ExampleObject.BufSize);
    Writer.Write(ExampleObject.pBuffer^, BufSize);

{ child objects }
Writer.WriteChildObject(ExampleObject.ChildObject);
end;

procedure ReadExampleObject(
    const Obj : TObject;
    const Reader : TObjectReader );
var
    ExampleObject : TExampleObject;

begin
ExampleObject := Obj as TExampleObject;

    { basic types }
    ExampleObject.Data1 := Reader.ReadString;
    ExampleObject.Data2 := Reader.ReadFloat;

    { binary data }
    ExampleObject.BufSize := Reader.ReadInteger;
    GetMem(ExampleObject.pBuffer, ExampleObject.BufSize);
    Reader.Read(ExampleObject.pBuffer^, ExampleObject.BufSize);

{ child objects }
ExampleObject.ChildObject := Reader.ReadChildObject;
end;

initialization

RegisterStreamable(
    TExampleObject,
    [TObjectReadProc(@ReadExampleObject)],
    [TObjectWriteProc(@WriteExampleObject)]
    );

end.

Notice that if the class has dynamically allocated data members other than objects, the read procedure is responsible to allocate memory, just like it is done in the constructor. For child objects, the memory is allocated automatically by the streaming mechanism in the ReadChildObject method.

Some Delphi versions have problems with the array of TProcedure style of parameters. It may be necessary to typecast as shown in the RegisterStreamable calls above.

Streaming Class Hierarchies

Often classes are derived from other classes to extended the functionality of an object. Derived classes should make use of the read and write procedures of the ancestor class to avoid rewriting code. Following is a unit containing a class derived from the TExampleObject class.

unit DerivedObject;

interface

uses
ExampleObject,
ObjPerst; { TObjectReader, TObjectWriter }

TDerivedObject = class(TExampleObject)
{ new data member added in derived class }
NewDataMember : String;
end;

procedure WriteDerivedObject(
const Obj : TObject;
const Writer : TObjectWriter );

procedure ReadDerivedObject(
const Obj : TObject;
const Reader : TObjectReader );

implementation

procedure WriteDerivedObject(
    const Obj : TObject;
    const Writer : TObjectWriter );
var
    DerivedObject : TDerivedObject;
begin
    DerivedObject := Obj as TDerivedObject;

{ write the data members of the ancestor }
WriteExampleObject(Obj, Writer);

{ write the data members in the current class }
Writer.WriteString(DerivedObject.NewDataMember);
end;

procedure ReadDerivedObject(
    const Obj : TObject;
    const Reader : TObjectReader );
var
    DerivedObject : TDerivedObject;
begin
    DerivedObject := Obj as TEDerivedObject;

{ read the data members of the ancestor }
ReadExampleObject(Obj, Reader);

{ read the data members in the current class }
DerivedObject.NewDataMember := Reader.ReadString;
end;

initialization

{$ifdef Win32}
RegisterStreamable(
    TDerivedObject,
    [TObjectReadProc(@ReadDerivedObject)],
    [TObjectWriteProc(@WriteDerivedObject)]
    );
{$else}
RegisterStreamable(
    TDerivedObject,
    [ReadDerivedObject],
    [WriteDerivedObject]
    );
{$endif}

end.

Class Registration

In the initialization section of both the ExampleObject and DerivedObject units, the classes are registered with the streaming system using the RegisterStreamable procedure. This allows the streaming system to associate classes with their read and write procedures. If multiple classes share the same read and write procedures, all the most derived classes must be registered individual. For example, the WriteGraphic and ReadGraphic procedures found in the ObjPerst unit are used to stream TBitmap, TIcon, and TMetaFile classes. Eventhough all three classes share the TGraphic ancestor, each class must be registered individual for the streaming system to properly recreate the classes when reading the object data from a stream.

The RegisterStreamable procedure requires as parameters the object type, and the read/write procedures. There may be multiple read and write procedures to support class versioning.

Class Versioning

Classes evolve with time. Class versioning allows for the streaming mechanism to function with previous class versions. When a class changes by adding new data members to the class, the new data members must be stored, but the previous versions of the class should still be readable.

The versioning mechanism used by the Spider Object Database, is to create different versions of the read and write procedures for a class. These procedures are registered with RegisterStreamable.

With the following class:

TPerson = class(TObject)
Name : String[50];
Email : String[80];
end;

Initially, the first read and write procedures may have been,

procedure ReadPerson1(
    const Obj : TObject;
    const Reader : TObjectReader );
begin
    (Obj as TPerson).Name := Reader.ReadString;
end;

procedure WritePerson1(
    const Obj : TObject;
    const Writer : TObjectWriter );
begin
    Writer.WriteString((Obj as TPerson).Name);
end;

If the Email data member was added at a later time, new read and write procedures may be written as follows:

procedure ReadPerson2(
    const Obj : TObject;
    const Reader : TObjectReader );
begin
    ReadPerson1(Obj, Reader);
    (Obj as TPerson).Email := Reader.ReadString;
end;

procedure WritePerson2(
    const Obj : TObject;
    const Writer : TObjectWriter );
begin
    WritePerson1(Obj, Writer);
    Writer.WriteString((Obj as TPerson).Email);
end;

Finally, the class is registered,

RegisterStreamable(
    TPerson,
    [ReadPerson1, ReadPerson2],
    [WritePerson1, WritePerson2]
    );

Creating the Object-Relational Mapping

Once a class is persistent, it may stored within streams (file, memory, etc...). The TObjectStream class in the ObjPerst.Pas unit, allows creating a stream of objects. In order to store objects inside a database other considerations are involved. Random access to the objects will be required, and the efficient use of indices will speed up queries. This is the role of the object-relational mapping. The abstract class, TORMapping, provides the interface to create the object-relational mapping.

The primary purpose of the object-relational mapping is to define the indices within a file of objects. To define indices, first the fields participating in the indices must be defined. This leads to two methods in TORMapping, namely, DefineFields and DefineIndices. Here is a typical object-relational mapping class. This is a simplified example from TestObj.Pas:

interface

type

TTestObjectMapping = class(TORMapping)
public
function GetObjectType : TClass; override;

    procedure DefineFields; override;
    procedure DefineIndices; override;
    procedure UpdateFieldsFromObj( const Obj       : TObject ); override;

{ This method is overriden when the table has an autoincrement field }
procedure UpdateObjFromFields( const Obj : TObject ); override;
end;

implementation

function TTestObjectMapping.GetObjectType : TClass;
begin
Result := TTestObject;
end;

procedure TTestObjectMapping.DefineFields;
begin
    AddField('Name', oftString, 41);
    AddField('Age', oftInteger, 0);
    AddField('Weight', oftFloat, 0);
    AddField('Id', oftAutoInc, 0);
end;

procedure TTestObjectMapping.DefineIndices;
begin
    AddIndex( 'Name', [iaPrimary], ['Name']);
    AddIndex( 'NameCI', [iaUnique, iaCaseInsensitive], ['Name']);
    AddIndex( 'AgeName', [], ['Age','Name']);
    AddIndex( 'AgeDesc', [iaDescending], ['Age']);
    AddIndex( 'Weight', [], ['Weight']);
    AddIndex( 'Id', [iaUnique], ['Id']);
end;

procedure TTestObjectMapping.UpdateFieldsFromObj( const Obj     : TObject );
begin
    FieldByName('Name').AsString := (Obj as TTestObject).Name;
    FieldByName('Age').AsInteger := (Obj as TTestObject).Age;
    FieldByName('Weight').AsFloat := (Obj as TTestObject).Weight;
    FieldByName('Id').AsInteger   := (Obj as TTestObject).Id;
end;

procedure TTestObjectMapping.UpdateObjFromFields( const Obj     : TObject );
begin
    { only update autoincrement field }
    (Obj as TTestObject).Id := FieldByName('Id').AsInteger;
end;

The GetObjectType method simply returns the type of the object for which will be handled by the object-relational mapping.

The UpdateFieldsFromObj method has the responsibility to obtain the field values given an object. The UpdateObjFromFields is only overriden when there is an autoincrement field defined. This allows updating an object from an autoincrement field value assigned by the database engine. Note that not all drivers support autoincremented fields.

Field Types and Indices

The DefineFields method defines all the fields that will be used in the indices of the object file. When using SQL databases, fields not participating in indices may also be defined to be used in SQL queries. The AddField method is part of the TORMapping base class. See the online help file for more details.

The example show all field types available with the Spider ODB. The TOdbFieldType type defines which data types may be used to create an index.

TOdbFieldType = (oftString, oftFloat, oftInteger, oftAutoInc);

For simplicity, only three basic types, string, float, and integer are used. This is sufficient to index almost any type of information. For instance date and time values may be indexes as string types. The functions DateToDateStr and TimeToTimeStr can be used to convert Delphi’s TDateTime into sortable strings.

Note that to define string fields, the size passed as a parameter to AddField must include the length byte, thus it is always one more than the maximum length of the string. The field size parameter to AddField only applies to string fields; passing 0 for other fields is recommended.

Defining Indices

All object-relational mappings must define one primary index. This is done by creating an index with the iaPrimary attribute. There may be additional unique indices, which are created using the iaUnique attribute. The primary index is the index used when using the TObjectUpdate class.

The DefineIndices method defines the indices that will be used with the object file. The AddIndex method is part of the TORMapping base class. See the online help file for more details.

Using Autoincrement Fields

Autoincrement fields are used in conjunction with an index definition. An autoincrement field is like an integer field, but its value is automatically incremented by one every time an object is inserted into an object file. The autoincrement is performed by the database engine, so for this reason, the UpdateObjFromFields method of TObjectFile must be overriden to update an inserted object with the value incremented by the database engine. For example, the Id field in the TTestObject class defines an autoincrement field.

procedure TTestObjectMapping.DefineFields;
begin
...
AddField('Id', oftAutoInc, 0);
end;

procedure TTestObjectMapping.DefineIndices;
begin
...
AddIndex( 'Id', [iaUnique], ['Id']);
end;

Using a TObjectDatabase Instance

Before accessing any object file with the Spider ODB classes, a TObjectDatabase must be created to establish a connection with the database. The constructor of the TObjectFile, TObjectQuery, and TObjectUpdate classes require a TObjectDatabase instance.

The TObjectDatabase instance may be created in the OnCreate handler of the main form and destroyed in the OnDestroy handler. Below is typical example on how to create an object database and object file within the main form of an application.

type

TMainForm = class(TForm)
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
private
    { Private declarations }
    CompanyDatabase : TObjectDatabase;
    EmployeeFile    : TPersonFile;

public
{ Public declarations }
end;

procedure TMainForm.FormCreate(Sender: TObject);
var
    ServerName, UserName, Password : String;
begin
    CompanyDatabase := TObjectDatabase.Create(
        TBdeOdbDriver,
        ‘ABC_Supplies’ );

    ServerName := ‘’;
    UserName := ‘’;
    Password := ‘’;
    CompanyDatabase.Open(ServerName, UserName, Password);

    EmployeeFile := TPersonFile.Create(
        CompanyDatabase, ‘Employee’, TPerson );
    EmployeeFile.Open;
end;

procedure TMainForm.FormDestroy(Sender: TObject);
begin
EmployeeFile.Close;
EmployeeFile.Free;

CompanyDatabase.Close;
CompanyDatabase.Free;
end;

The TObjectDatabase constructor requires two parameters; a driver class, and the name of the database.The driver class is a class derived from TOdbDriver. The database name is the name of the database to connect to. This may be an alias, or a directory name depending on which driver is employed.

The connection to the database is initiated when the Open method of the TObjectDatabase object is invoked. It requires the ServerName, UserName, and Password parameters. These parameters must be set as required by the underlying driver.

Using Transactions

There are three methods in the TObjectDatabase class used for transaction processing; StartTransaction, Commit, and RollBack. Transactions are used for "all or nothing" changes to a database.

A transaction begins by invoking StartTransaction and must later be terminated with the Commit method to accept the changes, or the RollBack method to ignore all changes.

The classical example to explain the transaction concept is the transfer of money from one bank account to another. Two operations must be performed to accomplish this task. The first is to withdraw the amount from the first account, and the second is to deposit the amount into the second account. Without a transaction, if a failure occurs after the withdrawal, the balance of the second account will not be correct. Below is the pseudo code to properly use transactions in this example.

Database.StartTransaction;
try
    Account1.Subtract(Amount);
    Account2.Add(Amount);
    Database.Commit;
except
    Database.Rollback;
    raise;
end;

Choosing a Driver

The choice of the driver is very important when deploying an application. Here are some factors to consider.

Database Architecture

There are three main types of database architectures, single-user, shared file multi-user, and client/server multi-user. Single user is fine for desktop applications where the data is only access by one application at a time. In the shared file multi-user architecture, every application has direct access to the data and is responsible to make changes to the shared data. In the client/server approach, only the server has access to the shared data, and the applications (clients) send requests using a network protocol to the server in order to access the data.

SQL or ISAM?

SQL (structured query language) is "the" standard for database access. Most database servers are SQL based. The Spider ODB supports SQL backends, and requires a minimal knowledge of SQL. All modifications to a database are done with the TObjectUpdate class, where the SQL statements are created automatically for the user. The queries in the database are done with the TObjectQuery class, and requires the user to be familiar with the SQL select statement.

ISAM access (indexed sequential access method) often provides a more intuitive access to the data. All ISAM access, both modifications and queries are performed with the TObjectFile class.The primitives act directly on an entire object file, and are straight foward; insert, delete, update, first, next, last, previous. ISAM access is often associated with desktop databases, but surprisingly, there are some ISAM based database that offer robust client/server operation. For example, Btrieve, now part of Pervasive.SQL, is used in critical applications using an ISAM access.

Some drivers support both SQL and ISAM access.

Performance

How quickly can objects be read/written to the database? Most database engines offer good performance especially with modern operating systems. Most operating systems generally perform extensive disk caching, allowing most drivers to achieve a good performance. Some drivers may suffer abnormal performance degradation as the size or the number of simultaneous users increases. It is important to test the driver and underlying database engine under the appropriate conditions. As the number of users increase beyond a certain threshold (somewhere between 10-30 users), only the client/server architecture will provide adequate performance.

Size of the database

How many objects will be stored in the database? All drivers will support thousands of objects, but when dealing with millions or even more objects, this is an important consideration. Many database engines, especially engines with originated on the desktop, are limited to 2 or 4 gigabyte file sizes. Some database engines also store all tables into one file, limiting the total database size. The Spider Object Database makes extensive use of BLOB fields, so a database which stores BLOB’s separately from the primary file may be considered.

Number of simultaneous users

As stated previously, there are two choices for multi-user databases; the shared file and client/server architectures. The shared file model is simpler to deploy, but beyond a certain number of users (about 10-30), its performance will degrade considerably. This is because each application must access the shared data directly, created a large amount of network traffic. The client/server approach becomes the only alternative when performance degrades due to a high number of simultaneous users.

Robustness

If the database must be available 24 hours/day, 7 days/week, the database must be very robust. Robustness is often associated with file corruption. Database files sometimes become corrupted after the abnormal termination of applications. Single user and shared file multi-user databases are more subject to file corruption, as the applications themselves make changes to the database. For example, a system crash while updating an object may cause file corruption. Client/server systems are less subject to file corruption, as only the server performs changes to the database, and the server is often designed in a very robust manner in order to recuperate by rolling back partial changes even after a system failure.

Comparison Table

Driver Multi-user
shared file Multi-user
client/server Transaction
support ISAM SQL Comments

TBdeOdbDriver, Borland Database Engine (Paradox and Dbase) Yes No Yes (32 bit only) Not very robust with Paradox and DBase Yes Yes Included with Delphi

TBtrieveOdbDriver, by Pervasive Software Yes Yes Yes Yes No

TQdbOdbDriver, Quick Database components by Robert R. Marsh No No No Yes No Lightweight driver which compiles directly in executable file. Supports only one index of type string.

TDbIsamOdbDriver, by Elevevate Software Yes No Yes Yes Yes Compiles directly in executable file. Client/server may be available in future releases.

TAdvantageOdbDriver, by Extended Systems Yes Yes Yes Yes Yes

FlashFiler, by Turbo Power No Yes Yes Yes Yes

Interbase Express, by Borland No Yes Yes No Yes

Using the TObjectFile Class

This section contains sample code constructs often used with the TObjectFile class.

Traversing

This fragment may be used to traverse all the objects of an object file.

var
Person : TPerson;

if PersonFile.FindFirst then
repeat
Person := PersonFile.CurrentObj as TPerson;
…
until not PersonFile.FindNext;

Searching

This example shows how to search for a person called ‘John Smith’ in a TPersonFile.

var
Person : TPerson;

if not PersonFile.IsEmpty then
begin
    PersonFile.IndexName := 'LastFirstName';
    Person := (PersonFile.CurrentObj as TPerson);
    Person.LastName := 'Smith';
    Person.FirstName := 'Joe';

    if PersonFile.FindExact then
    begin
        Person := (PersonFile.CurrentObj as TPerson);
        …
    end;
end;

Inserting

This example shows how to insert a new person into a TPersonFile.

var
Person : TPerson;

Person := TPerson.Create;
try
    Person.LastName := 'Smith';
    Person.FirstName := 'John';
    Person.SocialInsuranceNo := '111-111-111';
    Person.Email := 'jsmith@servername.com';
    Person.BirthDate := EncodeDate(1965, 12, 31);

    PersonFile.CurrentObj := Person;
    PersonFile.Insert;
finally
    Person.Free;
end;

Updating

An update operation must be performed after positioning the object file with a find operation.

var
Person : TPerson;

if not PersonFile.IsEmpty then
begin
    PersonFile.IndexName := 'LastFirstName';
    Person := (PersonFile.CurrentObj as TPerson);
    Person.LastName := 'Smith';
    Person.FirstName := 'Joe';

    if PersonFile.FindExact then
    begin
        Person := (PersonFile.CurrentObj as TPerson);
        Person.Email := := 'jsmith@newservername.com';
        PersonFile.Update;
    end;
end;

Deleting

This example deletes the first object in a TPersonFile. After the deletion, the object file is positioned to the next object.

if PersonFile.FindFirst then
PersonFile.Delete;

Using the TObjectUpdate Class

This section contains sample code constructs to be used with the TObjectUpdate class.

Inserting

This example shows how to insert a new person using a TObjectUpdate.

var
Person : TPerson;

PersonUpdate.Insert(Person);
finally
Person.Free;
end;

Updating

In order to peform an update, the object to update must be identified by the primary key defined in the object-relational mapping.

var
Person : TPerson;

Person := TPerson.Create;
try
    Person.LastName := 'Smith';
    Person.FirstName := 'John';
    Person.SocialInsuranceNo := '111-111-111';
    Person.Email := 'jsmith@newdomain.com';
    Person.BirthDate := EncodeDate(1965, 12, 31);

PersonUpdate.Update(Person);
finally
Person.Free;
end;

An update will completely the existing object matching the values of the primary. Generally, the current object should have been read with a TObjectQuery before updating it.

Deleting

In order to delete an object, the object to delete must be identified by the primary key defined in the object-relational mapping.

var
Person : TPerson;

Person := TPerson.Create;
try
Person.LastName := 'Smith';
Person.FirstName := 'John';

PersonUpdate.Delete(Person);
finally
Person.Free;
end;

Using the TObjectQuery Class

This section contains sample code constructs to be used with the TObjectQuery class.

Specifying the query

An SQL query needs to be specified to retrieve objects using the TObjectQuery class. The select section of the statement must include at minimum the "object" field name from the object-relational mapping that was used to create the TObjectQuery instance. The query must be supplied in the Select property.

For example, this is simplest select statement for an object file called PSonFile:

select object
from PSonFile

Fields defined by the object-relational mapping may be used to specify the where clause. The fields used in the where clause must be included in the select part of the statement. For example, to select all persons having reached 18 years of age:

PersonQuery.Select.Clear;
PersonQuery.Select.Add('select object, age');
PersonQuery.Select.Add('from PSonFile');
PersonQuery.Select.Add('where age >= 18');

PersonQuery.Open;

Traversing

Once a query has been specified and opened, it is possible to navigate through the objects of the query using the FindFirst, FindNext, FindLast, and FindPrior methods. For example to traverse all the objects in a query:

var
Person : TPerson;

if PersonQuery.FindFirst then
repeat
Person := PersonQuery.CurrentObj as TPerson;
…
until not PersonQuery.FindNext;

Object lookup

It is often needed to lookup an object based on its primary key. The TObjectQuery class allows searching for a unique object based on the primary key, without having to contruct a query statement. The Search method will search for an object based on the primary key fields from the object. For example, to lookup the information on 'John Smith':

var
    Key    : TPerson;
    Person : TPerson;
begin
    Key := TPerson.Create;
    try
        Key.LastName := 'Smith';
        Key.FirstName := 'John';
        Person := PersonQuery.Search(Key);
        if Person <> nil then
        begin
            ...
        end;
    finally
         Key.Free;
    end;
end;

Creating a Driver

The Spider Object Database is designed with an open architecture. New drivers may created to access other databases, or even create multi-tier solutions, where the driver could send requests to a middle tier rather than to a database engine directly.

The TOdbDriver class provides abstract methods which must be overriden when creating a new driver. The online help file under the TOdbDriver entry details all the methods which must be overriden. A good example is OdbDbIsm unit which implements a driver for DBISAM Database Engine which supports both ISAM and SQL access.

During the development of a driver, the automated tests in DriverTest should be used to test the correct operation of the driver. A good example, is to review the TDbIsamOdbDriverTest class in the OdbDbIsm unit. The test cases must be registered in the RegTests unit.

Method	Purpose
ReadInteger / WriteInteger	Read and write data members of types such as Byte, Word, Integer, and Cardinal.
ReadFloat / WriteFloat	Read and write data members of types such as Single, Double, and Extended.
ReadString / WriteString	Read and write data members of type String.
Read / Write	Read and write raw data in binary form.
ReadChildObject / WriteChildObject	Read and write object data members.

Driver	Multi-user shared file	Multi-user client/server	Transaction support	ISAM	SQL	Comments
TBdeOdbDriver, Borland Database Engine (Paradox and Dbase)	Yes	No	Yes (32 bit only) Not very robust with Paradox and DBase	Yes	Yes	Included with Delphi
TBtrieveOdbDriver, by Pervasive Software	Yes	Yes	Yes	Yes	No
TQdbOdbDriver, Quick Database components by Robert R. Marsh	No	No	No	Yes	No	Lightweight driver which compiles directly in executable file. Supports only one index of type string.
TDbIsamOdbDriver, by Elevevate Software	Yes	No	Yes	Yes	Yes	Compiles directly in executable file. Client/server may be available in future releases.
TAdvantageOdbDriver, by Extended Systems	Yes	Yes	Yes	Yes	Yes
FlashFiler, by Turbo Power	No	Yes	Yes	Yes	Yes
Interbase Express, by Borland	No	Yes	Yes	No	Yes