External Code Modules - Firebird

Firebird can extend its capabilities by accessing user-defined routines that are written in a host language and compiled in external shared libraries. This section provides an overview of some of the issues and techniques concerned with writing external functions (UDFs) and BLOB filters.

  • External functions:Firebird “travels light” with respect to built-in functions. Instead of a vast library of esoteric functions to weigh down the server, Firebird provides for developers to select—and, if necessary, to define —their own libraries of external functions to suit the calculation and expression requirements in their databases. User-defined functions, known to all as UDFs, add great flexibility to your database environment. UDFs are server -side extensions to the Firebird server that are declared to databases and executed in the context of the server process.

Like the standard, built-in SQL functions, UDFs can be designed to do conversions or calculations that are either complex or impossible to do with the SQL language. Possibilities include statistical, string, date, and mathematical functions.

In Chapter Expressions and Predicates the section “External Functions (UDFs)” explains in detail how to locate, declare, and use external functions, and how to configure their file-system location to avoid some of the security and integrity risks inherent in running external code from within the server engine. The filesystem configuration defaults and options are also discussed earlier in this chapter, under “UdfAccess” (v.1.5) and “external_file_directory” (v.1.0.x).

  • BLOB filters: The Firebird engine uses several internally defined routines to convert byte streams from one format to another. These routines are known as BLOB filters. The SQL engine surfaces them as BLOB subtypes in DDL and metadata. It is also possible to write your own BLOB filters for converting BLOB data from one format to another, compatible format. For example, a BLOB filter could convert XML to rich text or a bitmap image to a JPEG.

Developing Your Own UDFs

UDF libraries are compiled as standard shared libraries to run on the server where the database resides. The libraries are dynamically loaded by the database at runtime when the library is referenced in an SQL expression. You can create UDF libraries on any platform that is supported by Firebird. To use the same set of UDFs with databases running on different platforms, create and compile separate libraries for each platform where the databases reside.

A library, in this context, is a shared object that typically has a .dll extension on Windows platforms, an .so extension on UNIX and Solaris, and an .sl extension on HP-UX. It can contain one or many entry points for user-defined functions.

Creating and implementing a UDF is a four-step process:

  1. Write the function in any programming language that can create a shared library. A function can take a limited number of entry parameters, but it must return one and only one result. Functions written in Java are not supported.
  2. Compile the function and link it to a dynamically linked library or shared object library, as appropriate to the platform.
  3. Place the library and any symbolic links required into the appropriate disk locations on the server machine so that the server can locate it—the default /UDF directory or an alternative location you have configured for external function libraries.
  4. Use DECLARE EXTERNAL FUNCTION to declare each individual UDF to each database in which you need to use it.

Writing a Function Module

In the C language, a UDF is written like any standard function. The UDF can provide for up to ten input parameters, and must return one and only one C data value as its result.

A source code module can define one or more functions. If you include the Firebird ibase.h header file provided in your Firebird /include directory in the compilation, your C or C++ module can use the typedefs defined there. Translations exist for other languages, including several for Delphi. For example, the source kit for FreeUDFLib, by Gregory Deatz, includes ibase.pas.

Specifying Parameters

Non-BLOB, non-array parameters are passed to and from the UDF either by reference, using host language data types that are capable of being mapped to corresponding Firebird data types, or by descriptor, using a predefined structure that describes a Firebird data type to the host language. Up to ten parameters can be accepted, corresponding to any Firebird data type except an array type or element. If a UDF returns a BLOB, the number of input parameters is restricted to nine.

As an example of passing parameters by reference, the C function declaration for FN_ABS() accepts one parameter of the C type double. When FN_ABS() is called, it should be passed a parameter of the SQL data type DOUBLE PRECISION.

Passing parameters by descriptor is a capability surfaced in Firebird 1.0 that allows native Firebird data types to be passed. For some external functions, it simplifies the handling of NULL parameters and enables declaration overloading. The structure of a parameter descriptor can be found in ibase.h:

typedef struct paramdsc {
unsigned char dsc_dtype;
signed char dsc_scale;
ISC_USHORT dsc_length;
short dsc_sub_type;
ISC_USHORT dsc_flags;
unsigned char *dsc_address;

The most important field in the structure, of course, is dsc_dtype, because it is responsible for translating native Firebird data types to the host language types.

BLOB Parameters

UDFs that accept BLOB parameters require special data structure for processing. A BLOB is passed by reference to a BLOB control structure, described in the section “Writing a BLOB Function.”

Specifying a Return Value

The same data type restrictions apply to return values as to input parameters: Host types must be able to correspond to a Firebird type. For example, the C function declaration for FN_ABS() returns a value of type double, which corresponds to the Firebird data type DOUBLE PRECISION.

By default, return values are passed by reference. Numeric values can also be returned by value, although it is a deprecated method and not highly recommended. To return a numeric parameter by value, include the optional BY VALUE keyword after the return value when declaring the UDF to a database.

A UDF that returns a BLOB does not actually define a return value. Instead, a pointer to a structure describing the BLOB to return must be passed as the last input parameter to the UDF.

Character Data Types

UDFs need to use host-language data types for both their input and return values. Firebird must be able to translate between the declared type and the SQL data type. In the case of strings, the input to a string UDF is a CSTRING type of a specified maximum length in bytes. CSTRING is used to translate an input of CHAR or VARCHAR type into a null -terminated C string for processing, and to return a variable-length, null terminated C string to Firebird for automatic conversion to CHAR or VARCHAR. With other host languages, ensure that your function returns null-terminated strings.

When declaring a UDF that returns a C string, CHAR or VARCHAR, the keyword FREE_IT must be included in the declaration in order to free the memory used by the return value if it was allocated with ib_util_malloc.

Calling Conventions

The calling convention determines how a function is called and how the parameters are passed. The function receiving the function call must be compatible with the CDECL calling convention used by Firebird. With C functions using the CDECL calling convention, the__cdecl reserved word must be added to the function declaration. With Pascal, use cdecl.

Here is an example in C that specifies CDECL:

ISC_TIMESTAMP* __cdecl addmonth(ISC_TIMESTAMP *preTime)
// body of function here

Threading Issues

In Superserver implementations of Firebird, the server runs as a single multi-threaded process. This means that you must take some care in the way you allocate and release memory when coding UDFs, and also in the way you declare UDFs. Several issues need to be considered when handling memory in the single-process, multi-thread architecture:

  • UDFs must allocate memory using the ib _util _malloc function,in the ib_util library, rather than static arrays.
  • Memory allocated dynamically is not automatically released, since the process does not end. You must use the reserved word FREE_IT when you declare the UDF to the database (DECLARE EXTERNAL FUNCTION).
  • Static variables are not thread-safe. Users running the same UDFs concurrently will conflict when they step on the same static memory space. It would not be wise to use static variables unless you could guarantee that only one user at a time would be accessing the function.

If you cannot avoid returning a pointer to static data, you must not use FREE_IT.

The ib_util Library

The ib _util _malloc function is in your Firebird /lib directory, in the shared library ib_util.so on POSIX, ib_util.dll on Windows, and ib_util.sl on HP-UX. The function prototype, for C and Pascal, is provided in the /include directory as ib_util.h and ib_util.pas , respectively.

Pointer Variables with Classic Server

For non -threaded use with Classic server, you can return a global pointer. In the following example for the UDF FN_LOWER(), the array must be global to avoid going out of context:

char buffer[256];
char *fn_lower(char *ups)
return (buffer);

Making UDFs Leakproof

The procedure for allocating and freeing memory for return values in a fashion that is both thread-safe and compiler independent is as follows:

Use the ib _util _malloc( ) function in your host code to allocate memory for return values. If you use it, use the FREE_IT keyword in the RETURNS clause when declaring a function that returns dynamically allocated objects.

In the following example, the Firebird engine will free the buffer if the UDF is declared using the FREE_IT reserved word. Notice that this example uses Firebird’s ib_util_malloc( ) function to allocate memory:

char *fn_lower(char *ups)
char *buffer = (char *) ib_util_malloc(256);
return (buffer);

This is the declaration:

ENTRY POINT 'fn_lower' MODULE_NAME 'ib_udf';

Notes About Compiling and Linking

When a UDF module is ready, compile it in a normal fashion into object or library format.

Include ibase.h or its equivalent if you use typedefs defined in it. If linking statically, link to the Firebird client library if you are calling any Firebird library functions. For Microsoft Visual C/C++ the typelibs fbclient_ms.lib and ib_util_ms.lib can be found in the Firebird /lib directory.

Modifying a Function Library

To add a UDF to an existing external function module, add the file containing the object code for the new UDF and recompile as usual. Some platforms allow you to add object files directly to existing libraries. For more information, consult the platform-specific compiler and linker documentation.

To remove a function, follow the linker’s instructions for removing an object from a library. Deleting a function from a library does not remove its declaration from the database—use DROP EXTERNAL FUNCTION to do that.

Writing a BLOB Function

A BLOB function differs from other external functions, because pointers to BLOB control structures are passed to the function instead of references to actual data. The function cannot open or close a BLOB, but instead invokes API functions to perform BLOB access.

Creating a BLOB Control Structure

A BLOB control structure is a C struct, declared within a function module as a typedef. Programmers must provide the control structure definition that, in C, should be defined as follows:

typedef struct blob {
short (*blob_get_segment) ();
isc_blob_handle blob_handle;
long number_segments;
long max_seglen;
long total_size;
void (*blob_put_segment) ();
} *Blob;

Table describes the fields in the BLOB control structure.

Fields in the BLOB Control Structure

Fields in the BLOB Control Structure

Declaring a BLOB Function

A BLOB function is declared to the database using DECLARE EXTERNAL FUNCTION, with the difference that its type declaration is placed before the keyword RETURNS as the last argument of the parameter list, instead of following it as a return value. For the RETURNS argument, use the keyword PARAMETER and the ordinal number of the last parameter. For example, the following statement declares a BLOB function, blob_plus_blob, in an external function module named MyExtLib:

ENTRY_POINT 'blob_concat' MODULE_NAME 'MyExtLib';

More Information

A number of tutorial papers about writing external functions are available from Firebird community websites. The knowledge bases and search engines will yield plenty of good, explanatory articles.

BLOB Filters

In Chapter BLOBs and Arrays, we touched on this special type of external function that could be used by Firebird to customize the conversion of BLOBs between two formats capable of representing compatible data. A BLOB filter is a server-based, user-defined utility routine—a specialized UDF, in fact—that is capable of taking a BLOB in one format, converting it, and returning it as a BLOB in a second format. Once compiled and declared to a database, the BLOB filter can be actioned using regular DML statements from client applications, stored procedures and triggers, and isql.

In Firebird SQL, a BLOB filter is recognized and referred to by its SUB_TYPE. You are already familiar with two subtypes: 0 (for BLOB of any format and 1 (for unformatted or minimally formatted text). They are pre-defined internally, along with a number of others, all positive numbers, that Firebird uses internally for metadata and parsing. Other BLOB filters are user defined and can have any negative numbers as subtypes.

The Firebird engine has no “internal knowledge” of what is (or should be) stored in a BLOB of a given subtype numbered zero or less. It is up to the application to ensure that inputs and outputs are appropriate to the purpose of the subtype and any BLOB filters written to handle them.

Pairs of BLOB filters can be used to manage a useful range of regular conversions required by your applications, for example:

  • Compressing and decompressing data. One subtype stores compressed data while the other handles it when it is decompressed. The BLOB filter might be designed to take a SUB_TYPE 0 (unknown format) BLOB and convert it to a compressed format (zip or rar, for example) and expand it for handling as SUB_TYPE 0.
  • You might have one subtype to store generic application code and others to hold system-specific code, with a BLOB filter to add the necessary system -specific variations to the generic code when it is requested in a query.
  • You might have a subtype to store XML-formatted text and BLOB filters to transform it into to specific output formats—HTML, rich text, Portable Document Format(PDF), UNIX ‘man’ files, word-processor formats —for output as another subtype.

Firebird’s BLOB filter capability enables it to relegate the “bloat” associated with data converters to external processors. The transformation code in a filter routine can be as simple or complex as it needs to be. It can call other modules, if necessary, thus putting at your disposal the ability to incorporate existing converters into your server’s operations. Since the engine itself is concerned only with the outputs and inputs, while the filter code is maintained externally, your filters can be kept in step with the latest innovations of the technology without impacting the server.

Writing BLOB Filters

Writing BLOB filters involves the same care with memory and threading and similar steps to those for other external functions, namely

  1. Write the filters and compile them into object code.
  2. Create a shared filter library (shared object or DLL).
  3. Configure the Firebird server to know where to find the library at runtime.
  4. Use DECLARE FILTER to declare the filters to the database.
  5. Define columns in tables to store BLOBs of the subtypes “known” to the filter.
  6. Write applications or PSQL modules that request the filtering.

Declaring BLOB Filters

A filter is recognized at database level by way of a metadata declaration using this syntax:

DECLARE FILTER <filter-name>
INPUT_TYPE <sub-type> /* identifies subtype of object to be converted */
OUTPUT_TYPE <sub-type> /* identifies subtype of object to be created */
ENTRY_POINT '<entry-point-name>' /* name of exported function */
MODULE_NAME '<external-library-name>'; /* name of BLOB filter library */

Invoking BLOB Filters

The conversion from INPUT_TYPE to OUTPUT_TYPE is invoked automatically when MODULE_NAME is called with parameters that have been defined with the correct subtype numbers.

Suppose, for example, you create a library named filters.so or filters.dll that is in a legal location for BLOB filter libraries on the server. In it, you have a function named xml_to_rtf that takes a BLOB consisting of XML marked-up text, passes it to a program that converts it to a rich text document, and finally passes the result back in a new BLOB. You store XML documents in a BLOB SUB_TYPE –10 and rich text in a BLOB SUB_TYPE –15.

First, you would declare the BLOB filter to the database:

DECLARE FILTER XML2RTF /* your choice of name */
INPUT_TYPE -10 /* XML marked-up text */
OUTPUT_TYPE -15 /* rich text, formatted to company rules */
ENTRY_POINT 'xml_to_rtf' /* first stop for the conversion routine */
MODULE_NAME 'filters'; /* name of BLOB filter library */

Now, in your SQL or PSQL, all you need to invoke the conversion automatically is a variable or database column, declared as BLOB SUB_TYPE –10, containing your good, valid XML document and a destination variable or column of SUB_TYPE –15 to receive the converted document.

Tools for Writing Filters

The Firebird API has a group of macro functions beginning with isc_blob_filter_ that are the “programmer’s toolbox” for writing BLOB filters. A number of how-to documents n be located at community websites with little searching.

The Firebird API also has structures—with headers for them in ibase.h—for passing and reading information about BLOBs. If your curiosity has been whetted, look for BLOB descriptors and the BLOB parameter buffer (BPB). A group of related functions can be used in your BLOB filter code and also for requesting BLOB filters directly from your application code.

International Language Modules

Firebird is distributed with an ever-growing library of support for international languages and collation sequences. All but the four “basic” languages are distributed in the library fbintl (on POSIX) or fbintl.dll (on Windows). Firebird expects to find language libraries in the installation directory /intl.

It is possible to write your own character sets and collations, and have the Firebird engine load them from a shared library, which should be named fbintl2 in order to be recognized and linked. David Brookestone Schnepper, developer of the fbintl2 plug-in, makes a do-it-yourself language kit freely available.

From Firebird 1.5 onward, it is also possible to implement custom character sets or collations using external functions. Because the fbintl2 kit includes lucid instructions for creating character sets, it is also a useful reference if you plan to use the external function approach to implement a custom character set.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics