FireBird Offline Processing

Offline Processing
Creating a monitoring daemon
Without the chest-thumping
Compensating transactions
Process scheduling
One last thing

Offline Processing

I use the term offline processing in the context of a database to refer to processes that execute outside of the regular business transaction. These processes may be looking for trends in data, looking for suspicious content, doing extra consistency checks, doing periodical maintenance, etc.

Let's see an example of when we might make use of offline processing.

Most modern database management systems (DBMSs) do have very good support for database constraints. These constraints essentially are constraints that involve multiple tables. The closest thing to this is the foreign constraint, and figuring out the right dependencies can be quite tricky. Sometimes it's easier to let client applications do as much checking as they can, but run extra checks 'just in case'. We will be working off the sample database created in the Database Constraints article.

Creating a monitoring daemon

First, let's suppose we want to have a process that runs and checks the procedures. I'm using Visual C++ here, but I'm pretty confident this should be fairly easy to adapt to other platforms. Let's start by the build file; you can build this from the VS command console in any directory with the code file, and then copy fbclient_ms.dll to where the executable will run from to make it available (in my case, I just dumped it together with the code and the binary).

File: build-checker.cmd

@echo off

rem GS enables security checks.
rem I includes a directory for includes.
rem Zi enables debugging information
rem W4 sets warning level 4
rem /link sets flags for the linker

cl /GS /Zi /W4 /I"%programfiles%\Firebird\Firebird_1_5\include" ^
  delayed-checker.c ^
  /link "%programfiles%\Firebird\Firebird_1_5\lib\fbclient_ms.lib"

Now, the real deal. Hopefully the code will be easy to follow.

File: delayed-checker.c

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "ibase.h"

#define FAIL(MSG) \
  printf(MSG); \
  printf("\r\n"); \
  isc_print_status(status); \
  result = 1; \
  goto exit;

static char database_name[] = "localhost:c:/db-constraints.gdb";
static char username[] = "SYSDBA";
static char password[] = "masterkey";
static char query[] = "EXECUTE PROCEDURE CHECK_ORDERS";

const short dpb_overhead = 1 + 1 + 1;

// Remember to copy fbclient_ms.dll over.

void add_string(char** writer, char parameter_type, char* value)
{
  int value_length;

  value_length = strlen(value);
  **writer = parameter_type;
  (*writer)++;
  **writer = (char)value_length;
  (*writer)++;
  memcpy(*writer, value, value_length);
  (*writer) += value_length;
}

int main()
{
  isc_db_handle database;     // Database handle.
  isc_tr_handle transaction;  // Transaction handle.
  isc_stmt_handle statement;  // Statement handle.
  ISC_STATUS_ARRAY status;    // Return status.
  char* dpb;
  char* dpb_writer;
  short dpb_length;
  int result;

  database = 0;
  transaction = 0;
  statement = 0;
  dpb = 0;
  result = 0;

  // Allocate memory for the database parameter block.
  dpb_length = (short)(strlen(username) + strlen(password) + dpb_overhead);
  dpb = (char*)malloc(dpb_length);
  if (!dpb)
  {
    printf("Out of memory.\r\n");
    result = 1;
    goto exit;
  }

  // Set up a pointer to write into the block and populate
  // it with data.
  dpb_writer = dpb;
  *dpb_writer++ = isc_dpb_version1;
  add_string(&dpb_writer, isc_dpb_user_name, username);
  add_string(&dpb_writer, isc_dpb_password, password);

  if (isc_attach_database(status, 0, database_name, &database, dpb_length, dpb))
  {
    FAIL("Unable to connect to database.");
  }

  if (isc_start_transaction(status, &transaction, 1, &database, 0, NULL))
  {
    FAIL("Unable to start a new transaction.");
  }

  // Allocate and prepare a statement.
  if (isc_dsql_allocate_statement(status, &database, &statement))
  {
    FAIL("Unable to allocate a statement.");
  }
  if (isc_dsql_prepare(status, &transaction, &statement, 0, query, 0, NULL))
  {
    FAIL("Unable to prepare statement.");
  }
  if (isc_dsql_execute(status, &transaction, &statement, 1, NULL))
  {
      FAIL("Unable to execute CHECK_ORDERS successfully.");
  }

  // Commit the transaction.
  printf("Successful execution. Commiting transaction...");
  isc_dsql_free_statement(status, &statement, DSQL_close);
  statement = 0;
  if (isc_commit_transaction(status, &transaction))
  {
    FAIL("Unable to commit transaction.");
  }
  transaction = 0;
  printf("Transaction commited.");

exit:
  if (statement) isc_dsql_free_statement(status, &statement, DSQL_close);
  if (transaction) isc_rollback_transaction(status, &transaction);
  if (database) isc_detach_database(status, &database);
  if (dpb) free(dpb);
  return result;
}

As you can see, we return non-zero for failure, zero for success. You can then use your platform's cron or at utilities to schedule this to run regularly.

If there is more interest in this article, I will examine how you can use compensating transactions when finding problems, and how to interface with the daemon to report the patch-up activity going on in the database.

Without the chest-thumping

Now, unless your process is actually going to do something interesting with the data, I would not recommend the approach given above. isql has everything you need to simply execute a procedure and pipe out the results.

File: isql-checks.cmd

@echo off
echo Running database consistency checks...
if not exist "%ProgramFiles%\Firebird\Firebird_1_5\bin\isql.exe" (
  echo Cannot find isql file for Firebird 1.5.
  goto :eof
)

rem You probably want to include this in a separate file,
rem rather than building it on the fly.
echo CONNECT 'c:/db-constraints.gdb' USER 'SYSDBA' PASSWORD 'masterkey'; > check.sql
echo EXECUTE PROCEDURE CHECK_ORDERS; >> check.sql

"%ProgramFiles%\Firebird\Firebird_1_5\bin\isql.exe" -input check.sql > results.txt 2> errors.txt

Now, after running this command file, the results.txt file will hold any output returned from procedures or queries (none in this case), and errors will hold any errors found while processing the file. If errors.txt has any content, we know that there was a problem in our database. With a more sophisticated procedure, we might also be able to make use of the first file.

This scripting-oriented approach works best in Posix environments where there are multitude of command-line tools. In Windows, this is best complemented by VBScript or JScript files. And, of course, you can also resort to plain C and go as far as you need to with the approach shown in the previous section.

FireBird Offline Processing

Offline Processing

Creating a monitoring daemon

Without the chest-thumping

Compensating transactions

Process scheduling

One last thing