Extreme 8

How to Use PSMs to Extend Capabilities of
Empress Database with 3rd Party Libraries

Introduction

Empress Persistent Stored Modules (PSM) give users the ability to extend the capabilities of Database engine by writing user-defined functions, operators, stored procedures and triggers and storing them in the database.

Furtheremore, Empress PSM technology gives users the capability to extend the intelligence of the database even more. The functionality stored in system libraries or 3rd party products which are written as C libraries can be also utilized via Empress PSM technology. This feature becomes very significant nowdays in the presence of plethora of different products available via the Internet.

How it works?

In order to describe the process of how to extend capabilities of Empress RDBMS with 3rd party libraries we downloaded from the Internet a C library "Str" which is an extension of regular "string" C library. The "Str" library is written by Ralf S. Engelschall.

Description of Str Library

The Str library is a generic string library written in ANSI C which provides functions for handling, matching, parsing, searching and formatting of C strings. So it can be considered as a superset of POSIX string(3), but its main intention is to provide a more convenient and compact API plus a more generalized functionality.

For the sake of our example we used str_compare function. The synopsis of this function is:

int str_compare(const char *s, const char *t, str_size_t n, int mode);

This performs a lexicographical comparison of the two strings s and t (but never compares more than n characters of them) and returns one of three return values: a value lower than 0 if s is lexicographically lower than t, a value of exactly 0 if s and t are equal and a value greater than 0 if s is lexicographically higher than t. Per default (mode is 0) the comparison is case-sensitive, but if STR_NOCASE is used for mode the comparison is done in a case-insensitive way.

Empress user has to know the synopsis of the functions included in the library in order to correctly define them in Empress module.

For example:

                                                    

                                                        
CREATE MODULE module_str
       FUNCTION str_compare( GENERIC CHAR, GENERIC CHAR,
       GENERIC INTEGER, GENERIC INTEGER)
       RETURNS GENERIC INTEGER
       EXTERNAL NAME str_compare;
END MODULE

UPDATE MODULE module_str
USING './str-0.9.4/to/str/lib/libstr.so.9.0.24';
                                                    
                                                

We use "USING" keyword in "UPDATE MODULE" command in order to specify the external 3rd party library. In this way Empress RDBMS will not store the library inside the database, but only use its functionality.

The example shows an SQL JOIN operation on the two following tables, "vendors" and "hardware" with the help of "str_compare" function.

Table: vendors  id name 1 SUN 2 IBM 3 SGI

Table : hardware id name 1 Sun Sparcserver 670MP 2 sunNetra Internet Server 3 SUN 3/50 4 SG Irix Onyx

                                                    

                                                        
SELECT 'v'.id, 'v'.name, 'h'.id, 'h'.name
FROM vendors 'v', hardware 'h'
WHERE str_compare('v'.name, 'h'.name, 3, 4) = 0;

         id  name           id  name

          1  SUN             1  Sun Sparcserver 670MP
          1  SUN             2  sunNetra Internet Server
          1  SUN             3  SUN 3/50

                                                    
                                                

where the 3rd parameter (value = 3) is the number of characters to compare; and the 4th parameter (value = STR_NOCASE = 4) designates a "case insensitive" operation.

Note: The library "Str" is used here to verify the proof of concept. Some other functions in the library might not be applicable for Empress usage.

APPENDIX

The following script can be executed with EMPRESS V8.60 installed on Intel x86 machine running Linux OS Release 2.0 with libc.so.6 (ELF) in order to acquire the above described functionality. The script would require changes in order to be executed on some other platforms, such as MS Windows.

#!/bin/sh

empmkdb exampleDB

empbatch exampleDB <<EOM
CREATE MODULE module_str
       FUNCTION str_compare( GENERIC CHAR, GENERIC CHAR,
       GENERIC INTEGER, GENERIC INTEGER)
       RETURNS GENERIC INTEGER
       EXTERNAL NAME str_compare;
END MODULE

UPDATE MODULE module_str
USING './str-0.9.4/to/str/lib/libstr.so.9.0.24';


CREATE TABLE vendors (id longinteger, name char(3));
INSERT INTO TABLE vendors VALUES (1, "SUN");
INSERT INTO TABLE vendors VALUES (2, "IBM");
INSERT INTO TABLE vendors VALUES (3, "SGI");

CREATE TABLE hardware (id longinteger, name char);
INSERT INTO TABLE hardware VALUES (1, "Sun Sparcserver 670MP");
INSERT INTO TABLE hardware VALUES (2, "sunNetra Internet Server");
INSERT INTO TABLE hardware VALUES (3, "SUN 3/50");
INSERT INTO TABLE hardware VALUES (4, "SG Irix Onyx");

SELECT 'v'.id, 'v'.name, 'h'.id, 'h'.name
FROM vendors 'v', hardware 'h' WHERE str_compare('v'.name, 'h'.name, 3, 4) = 0;
EOM