Icon Customizing the Engine

Introduction
As already discussed in the DBISAM Architecture topic, the TDBISAMEngine component represents the engine in DBISAM. The following information will show how to customize the engine in an application. Some of the customizations can be made for the engine when it is acting as a local engine or server engine, while other customizations are only intended for the server engine. The TDBISAMEngine EngineType property controls whether the engine is behaving as a local engine or a server engine.

Engine Signature
The TDBISAMEngine EngineSignature property controls the engine signature for the engine. The default engine signature is "'DBISAM_SIG". The engine signature in DBISAM is used to "stamp" all tables, backup files, and streams created by the engine so that only an engine with the same signature can open them or access them afterwards. If an engine does attempt to access an existing table, backup file, or stream with a different signature than that of the table, backup file, stream, an EDBISAMEngineError exception will be raised. The error code that is returned when the access fails due to an invalid engine signature is 12036 and is defined as DBISAM_BADSIGNATURE in the dbisamcn unit (Delphi and Kylix) or dbisamcn header file (C++Builder).

Also, if the EngineType property is set to etClient, the engine signature is used to stamp all requests sent from a remote session to a database server. If the database server is not using the same engine signature then the requests will be treated as invalid and rejected by the database server. If the EngineType property is set to etServer, the engine signature is used to stamp all responses sent from the database server to any remote session. If the remote session is not using the same engine signature then the requests will be treated as invalid and rejected by the database server. In summary, both the remote sessions and the database server must be using the same engine signature or else communications between the two will be impossible.

Triggers
Triggers can be implemented for a local or server engine by using the TDBISAMEngine StartTransactionTrigger, CommitTrigger, RollbackTrigger, BeforeInsertTrigger, AfterInsertTrigger, BeforeUpdateTrigger, AfterUpdateTrigger, BeforeDeleteTrigger, and AfterDeleteTrigger events. These events are fired whenever a transaction is started, committed, or rolled back, and whenever a record is inserted, updated, or deleted via navigational methods or via SQL. However, these events are not triggered during any system processing such as Creating and Altering Tables or Optimizing Tables. This allows for the freedom to change the table metadata without having to worry about causing any errors due to constraints that may be enforced via the triggers.

Information These events can be called from multiple threads concurrently, so it is very important that you observe the rules of multi-threading with DBISAM. The TDBISAMSession and TDBISAMDatabase components are created automatically by the engine and passed as parameters to these events, so if you create any TDBISAMTable or TDBISAMQuery components in an event handler for one or more of these events, you need to make sure to assign the SessionName and DatabaseName properties to that of these passed TDBISAMSession and TDBISAMDatabase components. Please see the Multi-Threaded Applications topic for more information.

The TDBISAMEngine triggers events can be used for audit logging, referential integrity, replication, hot backups, etc. There really is no limit to what can be coded in an event handler attached to one or more of these events. The following is an example of a BeforeDelete trigger that executes a query in order to determine whether to permit the deletion or raise an exception:

procedure TMyForm.EngineBeforeDeleteTrigger(Sender: TObject;
  TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase;
  const TableName: String; CurrentRecord: TDBISAMRecord);
var
   OrdersQuery: TDBISAMQuery;
begin
   if (AnsiCompareText(TableName,'customer')=0) then
      begin
      OrdersQuery:=TDBISAMQuery.Create(nil);
      try
         with OrdersQuery do
            begin
            SessionName:=TriggerDatabase.SessionName;
            DatabaseName:=TriggerDatabase.DatabaseName;
            RequestLive:=True;
            SQL.Text:='SELECT * FROM Orders '+
                      'WHERE CustNo=:CustNo AND '+
                      'AmountPaid < ItemsTotal';
            ParamByName('CustNo').AsFloat:=
                   CurrentRecord.FieldByName('CustNo').AsFloat;
            Open;
            try
               if (RecordCount > 0) then
                  raise Exception.Create('Cannot delete this '+
                                         'customer, there are still '+
                                         IntToStr(RecordCount)+' active '+
                                         'orders present for this '+
                                         'customer');
            finally
               Close;
            end;
            end;
      finally
         OrdersQuery.Free;
      end;
      end;
end;

You can use the TDBISAMEngine OnInsertError, OnUpdateError, and OnDeleteError events to trap any errors that may occur during an insert, update, or delete, and reverse any action that may have been initiated in a Before*Trigger event handler. For example, if you start a transaction in a BeforeDeleteTrigger event, you should be sure to rollback the transaction in an OnDeleteError event handler or else you will inadvertently leave an active transaction hanging around.

The TriggerSession CurrentServerUser property can be referenced from within a trigger that is being executed when the TDBISAMEngine EngineType property is set to etServer in order to retrieve the current user name.

Information If any exception is raised in any trigger event handler, the exception will be converted into an EDBISAMEngineError exception object with an error code of DBISAM_TRIGGERERROR. The original exception's error message will be assigned to the ErrorMessage property of the EDBISAMEngineError exception object, as well as be included as part of the error message in the EDBISAMEngineError exception object itself.

Custom SQL and Filter Functions
Custom SQL and filter functions can be implemented for a local or server engine by using the TDBISAMEngine Functions property in conjunction with the OnCustomFunction event. The Functions property is a TDBISAMFunctions object, and the easiest way to add new functions is to use the Functions property's CreateFunction method, which will create a new TDBISAMFunction object, add it to the Functions property, and return a reference to the new function. You can then use this function reference to add the parameters to the function using the TDBISAMFunction Params property. The Params property is a TDBISAMFunctionParams object, and the easiest way to add new function parameters is to use the Params property's CreateFunctionParam method, which will create a new TDBISAMFunctionParam object, add it to the Params property, and return a reference to the new function parameter. You can then use this function parameter reference to specify the data type of the parameters to the custom function. All custom function result and parameter data types use the TFieldType type. Please see the Data Types and NULL Support topic for more information.

The following example shows how you would use the CreateFunction method to create a function called "DaysBetween" that returns the number of days between two date parameters as an integer:

begin
   { We'll just use the default Engine global function
     for this example }
   with Engine do
      begin
      with Functions.CreateFunction(ftInteger,'DaysBetween').Params do
         begin
         CreateFunctionParam(ftDate);
         CreateFunctionParam(ftDate);
         end;
      end;
end;

Information Adding a custom function while the engine is active will result in the engine triggering an exception. You should define all custom functions before activating the engine.

Once you have defined the custom function using the TDBISAMEngine Functions property, you must then proceed to implement the function using an event handler assigned to the TDBISAMEngine OnCustomFunction event. When DBISAM encounters a function name in a filter or SQL expression that does not match that of a pre-defined function in DBISAM, the OnCustomFunction event is triggered with the name of the function, the parameters to the function defined as a TDBISAMParams object, and a parameter for returning the function result as a variant variable. Inside of the OnCustomFunction event handler you must conditionally process each function using the name of the function passed to the event handler. The following example implements the "DaysBetween" function that we defined previously in the above example:

procedure MyForm.CustomFunction(Sender: TObject;
  const FunctionName: String; FunctionParams: TDBISAMParams;
  var Result: Variant);
var
   Stamp1: TTimeStamp;
   Stamp2: TTimeStamp;
begin
   if (AnsiCompareText(FunctionName,'DaysBetween')=0) then
      begin
      { Notice that the function parameters are accessed
        in a 0-based manner }
      Stamp1:=DateTimeToTimeStamp(FunctionParams[0].AsDateTime);
      Stamp2:=DateTimeToTimeStamp(FunctionParams[1].AsDateTime);
      Result:=Trunc((Stamp2.Date-Stamp1.Date)+
                   (((((Stamp2.Time-Stamp1.Time)/1000)/60)/60)/24));
      end;
end;

Information The name of the parameters sent to the OnCustomFunction event handler will be:

"Param" plus an underscore (_) plus the position of the parameter (0-based)

for constants or expressions, and:

Table name plus underscore (_) plus column name plus (_) plus the position of the parameter (0-based)

for table columns. This allows you to identify which column from which table was passed to a custom function.

Memory Buffer Customizations
The TDBISAMEngine MaxTableDataBufferCount, MaxTableDataBufferSize, MaxTableIndexBufferCount, MaxTableIndexBufferSize, MaxTableBlobBufferCount, and MaxTableBlobBufferSize properties allow you to control how much memory is used for buffering the data records, index pages, and BLOB blocks for each physical table opened in a given session in the engine. The *Size properties dictate how much memory, in bytes, to allocate. The *Count properties dictate the maximum number of data records, index pages, and BLOB blocks that can be allocated regardless of the amount of memory available. This is to ensure that the buffering architecture in DBISAM does not get overwhelmed by buffering too many small records, etc.

Lock File Name Customizations
The default lock file name, "dbisam.lck", can be modified using the TDBISAMEngine LockFileName property.

File Extension Customizations
The default file extensions for tables are detailed in the DBISAM Architecture topic. You can modify these default extensions using the following properties:


Information The temporary file extension customizations are useful when you wish to have any temporary tables created by DBISAM use a file extension other than .dat, .idx, or .blb. Recent issues with certain anti-virus software has shown that it may be necessary to change the extensions of the files that make up temporary DBISAM tables in order to prevent the anti-virus software from interfering with DBISAM's ability to create and open temporary tables on a local drive.

Encryption Customizations
By default DBISAM uses the Blowfish block cipher encryption algorithm with 128-bit MD5 hash keys for encryption. However, you may replace the encryption in DBISAM with another 8-byte block cipher algorithm by defining event handlers for the TDBISAMEngine OnCryptoInit, OnEncryptBlock, OnDecryptBlock, and OnCryptoReset events. The OnCryptoInit event is triggered whenever DBISAM needs to initialize the internal block cipher tables using a new key. The OnEncryptBlock event is triggered whenever DBISAM needs to encrypt a block of data, and the OnDecryptBlock event is triggered whenever DBISAM needs to decrypt a block of data. A block of data will always be 8-bytes in length. Finally, the OnCryptoReset event is triggered after every encryption or decryption of a buffer (data record, index page, or BLOB block) in order to reset the cipher data so that it is ready for encrypting or decrypting a new buffer.

Please see the Encryption topic for more information.

Compression Customizations
By default DBISAM uses the ZLIB compression algorithm for compression. However, you may replace the compression in DBISAM with another compression algorithm by defining event handlers for the TDBISAMEngine OnCompress and OnDecompress events. The OnCompress event is triggered whenever DBISAM needs to compress a buffer. The OnDecompress event is triggered whenever DBISAM needs to decompress a buffer.

Please see the Compression topic for more information.

Full Text Indexing Customizations
The full text indexing functionality in DBISAM allows the developer to index the words in string or memo fields for very fast word-based searches. You can define event handlers for the TDBISAMEngine OnTextIndexFilter and OnTextIndexTokenFilter events that allow you to filter the string and memo field data prior to being indexed by DBISAM. The OnTextIndexFilter event is triggered before DBISAM parses any string or memo fields that are included in the full text index for the table into words using the stop words, space characters, and include characters defined for the table. This allows you to filter the raw data, such as stripping out control codes from HTML, RTF, or other types of document formats. On the other hand, the OnTextIndexTokenFilter event is triggered after any string and memo fields are parsed into words using the stop words, space characters, and include characters defined for the table. This allows you to further filter out certain words based upon conditional rules or custom dictionaries that aren't easily expressed using just the static stop words for the table. Please see the Full Text Indexing topic for more information.

Information If you add or modify the OnTextIndexFilter or OnTextIndexTokenFilter event handlers when you have existing tables with full text indexing defined for one or more fields, you must be sure to alter the structure of these tables and turn off the full text indexing for all fields. After you have done this, you can then alter the structure of these tables again to turn back on the full text indexing for the desired fields. Doing this will ensure that any existing text is properly handled with the new event handlers and will eliminate the possibility of confusing results when searching on the fields that are part of the full text index. Please see the Creating and Altering Tables topic for more information.

Reserved Customizations
There are certain customizations in the engine that are only for use in fine-tuning specific issues that you may be having with an application and should not be modified unless instructed to do so by Elevate Software. The TDBISAMEngine TableReadLockRetryCount and TableReadLockWaitTime, TableWriteLockRetryCount and TableWriteLockWaitTime, TableTransLockRetryCount and TableTransLockWaitTime, TableFilterIndexThreshhold properties should only be modified when instructed to by Elevate Software.

Server-Only Customizations
The following customizations are only available when the TDBISAMEngine EngineType property is set to etServer and the engine is behaving as a database server.

Licensed Connections
You can specify that a maximum number of licensed connections be used for the database server by modifying the TDBISAMEngine ServerLicensedConnections property. The default is 65,535 connections. Setting this property to a lower figure will allow no more than the specified number of connections to be configured as the maximum number of connections for the database server in addition to actually preventing any more than the specified number of connections active on the database server at the same time.

Notification Events
You can define event handlers for the following TDBISAMEngine events to respond to various server conditions:

EventDescription
OnServerStartThis event will be triggered whenever the server starts listening for incoming normal data connections. The server is started via the TDBISAMEngine StartMainServer method or remotely via the TDBISAMSession StartRemoteServer method.
OnServerStopThis event will be triggered whenever the server stops listening for incoming noraml data connections. The server is stopped via the TDBISAMEngine StopMainServer method or remotely via the TDBISAMSession StopRemoteServer method.
OnServerConnectThis event will be triggered whenever a normal data connection is established.
OnServerReconnectThis event will be triggered whenever a normal data connection is re-established by an automatic reconnection by the remote session.
OnServerLoginThis event will be triggered whenever a user logs in on a normal data connection.
OnServerLogoutThis event will be triggered whenever a user logs out on a normal data connection.
OnServerDisconnectThis event will be triggered whenever a normal data connection is closed.

Logging Events
DBISAM abstracts all server logging functionality so that you may choose to log server events in any manner that you wish. The default server project that ships with DBISAM uses these events to store the log records in a binary file. You can define event handlers for the following TDBISAMEngine events to customize the logging functionality:

EventDescription
OnServerLogEventThis event is triggered whenever the server needs to log an event. The log record that is passed to the event handler is defined as a TLogRecord type.
OnServerLogCountThis event is triggered whenever the server needs to get a count of the number of log records in the current log. This event is triggered whenever the TDBISAMEngine GetServerLogCount method is called or the TDBISAMSession GetRemoteLogCount method is called by a remote session.
OnServerLogRecordThis event is triggered whenever the server needs to get a specific log record from the current log. This event is triggered whenever the TDBISAMEngine GetServerLogRecord method is called or the TDBISAMSession GetRemoteLogRecord method is called by a remote session.

Scheduled Events
DBISAM allows the definition of scheduled events for a database server. Scheduled events are stored in the configuration file for the server and are implemented via the TDBISAMEngine OnServerScheduledEvent event. Scheduled events will simply do nothing unless they are actually implemented in the database server via an event handler assigned to this event. Scheduled events are executed in a separate thread in the server, one thread for each currently-executing scheduled event. If you have three scheduled events that are scheduled for the same time, then the server will create three threads, one for each scheduled event. Any database access within the thread must be done according to the rules for using DBISAM in a multi-threaded application. Please see the Multi-Threaded Applications topic for more information. Also, scheduled events are run as if they are using a local engine accessing databases and tables directly and cannot directly use database names that are defined in the database server configuration. You must use the methods available in the TDBISAMEngine component for retrieving database information for databases for retrieving the information necessary to access server databases and tables in the scheduled event (see the example below).

The following is an example of a scheduled event called "DailyBackup" that calls the TDBISAMDatabase Backup method to backup a databse every day at a certain time:

procedure TMyForm.ServerScheduledEvent(Sender: TObject;
  const EventName: String; var Completed: Boolean);
var
   TempSession: TDBISAMSession;
   TempDatabase: TDBISAMDatabase;
   TempDescription: string;
   TempPath: string;
   BackupFiles: TStrings;
begin
   TempDescription:='';
   TempPath:='';
   if (AnsiCompareText(EventName,'DailyBackup')=0) then
      begin
      { Create a new session component, remembering
        the multi-threading requirements of DBISAM
        for session names }
      TempSession:=TDBISAMSession.Create(Self);
      try
         with TempSession do
            begin
            SessionName:='DailyBackup'+IntToStr(GetCurrentThreadID);
            Active:=True;
            end;
         { Create a new database component }
         TempDatabase:=TDBISAMDatabase.Create(Self);
         try
            with TempDatabase do
               begin
               SessionName:=TempSession.SessionName;
               DatabaseName:='DailyBackup';
               { Get the actual local path for the Main
                 database }
               ServerEngine.GetServerDatabase('Main',
                                              TempDescription,
                                              TempPath);
               Directory:=TempPath;
               Connected:=True;
               BackupFiles:=TStringList.Create;
               try
                  TempSession.GetTableNames(DatabaseName,BackupFiles);
                  Completed:=Backup(
                    IncludeTrailingBackslash(TempPath)+'backup'+
                    StringReplace(DateToStr(Date),'/',
                                  '',[rfReplaceAll])+'.bkp',
                    'Daily Backup for '+DateToStr(Date),6,BackupFiles);
               finally
                  BackupFiles.Free;
               end;
               Connected:=False;
               end;
         finally
            TempDatabase.Free;
         end;
      finally
         TempSession.Free;
      end;
      end
   else
      Completed:=True;
end;

Information If a scheduled event is not marked as completed by this event handler, it will continue to be executed every minute by the database server until the time range for which it was scheduled is up. For example, if the above scheduled event was scheduled to run every day between 11:00pm and 11:30pm, the database server will attempt to execute the scheduled event until it is either completed or the time exceeds 11:30pm. Also, if an error occurs during the scheduled event execution, the database server will consider the scheduled event not completed. Any time the database server encounters an error in the scheduled event or detects that the scheduled event did not complete it will log this information in the current log.

Server Procedures
DBISAM allows the definition of server-side procedures for a database server. Server-side procedures are stored in the configuration file for the server and are implemented via the TDBISAMEngine OnServerProcedure event. Server-side procedures will simply do nothing unless they are actually implemented in the database server via an event handler assigned to this event. Server-side procedures are executed in the context of the session thread currently running for the remote session that is calling the server-side procedure. Any database access within the server-side procedure must be done according to the rules for using DBISAM in a multi-threaded application. Please see the Multi-Threaded Applications topic for more information. However, unlike scheduled events (see above), server-side procedures are passed a TDBISAMSession component for use in the procedure for retrieving parameters passed in from the remote session and for populating the result parameters that are passed back to the remote session after the procedure is done, as well as sending progress information back to the calling session. This TDBISAMSession component is automatically created and assigned a unique SessionName property to ensure that it can be safely be used in a multi-threaded manner. This session name consists of the user name plus an underscore (_) plus the session ID. Also, server-side procedures are run as if they are using a local engine accessing databases and tables directly and cannot directly use database names that are defined in the database server configuration. You must use the methods available in the TDBISAMEngine component for retrieving database information for databases for retrieving the information necessary to access server databases and tables in the server-side procedure.

The TDBISAMSession RemoteParams property is used both to pass the parameters to the server-side procedure and to return any results to the remote session that called the server-side procedure. The RemoteParams property is a TDBISAMParams object. Be sure to always clear the parameters using the RemoteParams' Clear method before leaving the server-side procedure. Otherwise, the same parameters that were passed to the server-side procedure will be returned to the remote session as results. You can add new results to the RemoteParams property for return to the remote session using the RemoteParams' CreateParam method.

The following is an example of a server-side procedure called "TextFile" that sends a text file back to the remote session that requested it:

procedure TMyForm.ServerProcedure(Sender: TObject;
  ServerSession: TDBISAMSession; const ProcedureName: String);
var
   TempFileName: string;
begin
   if (AnsiCompareText(ProcedureName,'TextFile')=0) then
      begin
      with ServerSession do
         begin
         TempFileName:=RemoteParams.ParamByName('FileName').AsString;
         { Now clear the parameters for use in populating
           the result parameters }
         RemoteParams.Clear;
         if FileExists(TempFileName) then
            begin
            { If the file exists, use the TDBISAMParam
              LoadFromFile method to load the file
              data into the parameter }
            with RemoteParams.CreateParam(ftMemo,'FileContents') do
               LoadFromFile(TempFileName,ftMemo);
            end
         else
            { If the file doesn't exist, just create a NULL
              parameter with the correct result name }
            RemoteParams.CreateParam(ftMemo,'FileContents');
         end;
      end;
end;

The ServerSession CurrentServerUser property can be referenced from within a trigger that is being executed when the TDBISAMEngine EngineType property is set to etServer in order to retrieve the current user name.

Information If a server-side procedure raises any type of exception at all, the database server will send the exception back to the remote session that called it as if the exception occurred in the remote session.

To report progress information back to the calling session during the server-side procedure, use the SendProcedureProgress method of the TDBISAMSession component passed as a parameter to the OnServerProcedure event handler.
Image