Brief Description
To create a library of User Defined Functions (UDF) to be included with the shipping version of InterBase version 5.0.
Purpose
The primary purpose is to expand the function offerings of InterBase. However, there is a secondary, less evident purpose. That is, one of the existing issues with the super server architecture is that if a UDF is ill behaved and tends to crash the server, it will crash the entire server process terminating all connections. In the past (classic architecture) only those connection actually using the UDF would have their individual server process terminate. By providing a set of standard UDF libraries we will expand the standard functionality of the server, and prevent some users from attempting or needing to write their own, possibly ill behaved, UDF library. In addition to the creation of this library, it is suggested that the creation and use of UDF libraries be clearly documented.
Feature Description
The functionality of the InterBase product would need two changes. First, we to ensure that the server will be able to load this new UDF. That is, the current mechanism for loading external libraries is dependent on environment variable such as LD_LIBRARY_PATH on SOLARIS, SHLIB_PATH on HP and PATH on Windows. Since we do not want to have to modify the users environment, we will change the loading mechanism to first let the operating system look for the library, if it is not found, we will look in the ‘lib’ directory bellow the InterBase installation directory. This will be the location of any external libraries which we will provide. (Note: for the time being this is the only one, but if we ever wish to provide more, we now have a place for them. Second, we need to be able to unload a UDF. This work has already been done by Charlie, and will affect both UDFs and Filters.
A set of functions will need to be coded and added to the library. The functionality of the proposed library is briefly described in the following tables. Note, only those functions marked with x in the first column are going to be present in InterBase 5.0, the others should be added at a later time.
| Math Functions | Description | |
| x | abs(n) | returns the absolute value of n |
| x | acos(n) | returns the angle (in radians) whose Cosine is n |
| x | asin(n) | Returns the angle (in radians) whose sine is n |
| x | atan(n) | returns the angle (in radians) whose tangent is n |
| x | atan2(n1,n2) | returns the angle (in radians) whose tangent is n1/n2 |
| base(n,base) | Converts a number from decimal to base base | |
| x | bin_and(n,m) | bit-wise AND |
| x | bin_or(n,m) | bit-wise OR |
| x | bin_xor(n,m) | bit-wise exclusive OR |
| bin_comp(n,m) | bit-wise compare | |
| x | ceiling(n) | returns the smallest integer greater than or equal to n |
| cond(expr1, expr2,expr3) | Same functionality as C language expr1 ? expr2: expr3 | |
| x | cos(n) | returns the cosine of n (in radians) |
| x | cosh(n) | returns the hyperbolic cosine of n (n is in radians) |
| x | cot(n) | returns the cotangent of n (in radians) |
| degrees(n) | Converts radians to degrees | |
| x | div(n,m) | Integer divide. |
| x | exp(n) | returns (e=2.718281..) raised to the nth power |
| x | floor(n) | returns largest integer equal or less than n |
| is_null(expr) | returns 1 or 0 depending if expr is null. | |
| x | ln(n) | returns natural logarithm of n (n>0) |
| x | log(n,m) | returns logarithm base n of m |
| x | log10(n) | returns the base 10 logarithm of n |
| x | mod(n,m) | returns remainder of n divided by m |
| x | pi() | returns the constant 3.145926535897936 |
| x | power(n,m) | returns n raised to the mth power |
| radians(n) | Converts degrees to radians | |
| x | rand() | returns a random float between 0 and |
| round(n,m) | returns n rounded to m decimal places | |
| x | sign(n) | returns {-1, 0, 1} if {n>0, n==0, n<0} respectively |
| x | sin(n) | returns sine of n (n in radians) |
| x | sinh(n) | returns the hyperbolic sine of n (n is in radians) |
| x | sqrt(n) | returns the square root of n |
| x | tan(n) | returns tangent of n (n in radians) |
| x | tanh(n) | returns the hyperbolic tangent of n (n in radians) |
| String Functions | Description | |
| x | ascii_val(c) | returns ASCII code for first character in c |
| x | ascii_char(n) | converts a single-byte integer to a character value |
| Charindex(c1, c2) | returns position of c1 in c2 SQL-92 equivalent is position(c1 IN c2) | |
| difference(exp1,exp2) | returns an integer representing the difference between two soundex values | |
| insert(str1,start,n,str2) | replaces n chars from str1 with str2 beginning at start | |
| x | strlen(c) | returns length of character expr c |
| x | lower(c) | returns character string in lower case |
| match(c,rexpr) | matches c according to the regular expression rexpr | |
| proper(c) | uppercase 1st letter of each word in c is initcap(c) | |
| replicate(c,n) | returns character expression c repeated n times | |
| reverse(c) | returns the reverse of character expr c | |
| x | substr(c, n,m) | returns a sub-string of c strting at n for m characters |
| soundex(c) | returns a 4-char "soundex" code for a character string | |
| space(n) | returns a string of n spaces | |
| x | rtrim(string) | removes blanks from the end of string |
| x | ltrim(string) | removes leading blanks from string |
| DateTime Functions | Description | |
| current_time([precision]) | returns current system time at precision decimal places (SQL-92) | |
| datepart(datepart,date) | returns part of date SQL-92 equivalent is specified by datepart EXTRACT(dpart FROM date) | |
| datediff(datepart,d1,d2) | returns d2-d1 measured in the specified date datepart | |
| dateadd(datepart,n,d) | returns date d added to n specified dateparts | |
| timepart(date) | returns a string representation of the time part of date (convenience) |
Implementation
Affected Modules
Unloading of UDF/filter change:
Modules changed in jrd: jrd.c, flu.c, fun.e, met.e, jrd.h.
New modules in jrd: flu.h
Default UDF location:
Modules changed in jrd: gds.c, gds_proto.h, gds32.bind, flu.c, why.c, met.e,
lan.c, intl.c, jrd.c, isc_file.c, isc.c, fun.e, blf.e, flu_proto.h, common.h
Modules changed in other components: remotemslan.c, intldtest.c,
buildsbind_gds.generic, buildsbind_gds.hp, buildsbind_gds.win_nt
UDF creation:
New component: extlib
New modules in extlib: ib_udf.c, ib_udf.h, ib_udf.sql, makefile.mak
New modules in builds: sfx.extlib
Changed modules in builds: prefix.solaris, prefix.hp10, setup_dirs, refresh,
refresh.all, set_prot, maketar, sfx.interbase
New modules in build_win32: refresh.ksh, setup_dirs.ksh, build_lib.bat,
make.jrd, make.example4
Proposed Code changes:
Unloading UDF/filter library:
FLU.C (see FLU_modules) keeps a linked list of dynamically loaded library modules (see FLU.H) that are currently loaded by the process. These module structures are reference counted once by each attached database that has dynamically linked one or more functions or filters from the library module. Each database maintains a linked list stack of modules (see JRD.H- ddb_modules) that it is using.
FUN.E and MET.E (FUN_lookup_function() and MET_lookup_filter()) call FLU_lookup_module() to check if the module name is currently referenced by that database and add it to the linked list stack if necessary. Since ISC_lookup_entrypoint() has been modified to increment the module reference count on every function lookup, it is necessary for the database to decrement the count if it finds the module was already in the database linked list stack.
There is a subtle reason why ISC_lookup_entrypoint() increments the module reference count on every function lookup. There are contexts in which ISC_lookup_entrypoint() is called that are unrelated to any particular database. If a user bundled all external functions and filters with extensible functions related to internationalization or encryption in the same library module, a database might accidently unload it because it would be unaware of the usage of the module by other components of the engine.
When the last attachment from a database is detached, the reference counts for all library modules used by that database are decremented (see FUN.E - FUN_fini()). If the the reference count falls to zero then the library module is considered not in use and it is unloaded from the process address space (see FLU.C - FLU_unregister_module().)
Caveats:
The dynamic loader for the OS platform must perform its own reference counting for the shared object. This is to guard against the situation where a shared library object has multiple aliases. For example, consider if one database lists a module path in RDB$FUNCTIONS with an absolute path and another database lists a path for the same module that includes UNIX symbolic links.
Since the FLU_lookup_module() performs lookup by module name as recorded in RDB$FUNCTIONS.RDB$MODULE_NAME, there will be two module structures in the list that have opened a handle to the same shared library object.
When one of the databases tries to unload the shared library known by one alias, the OS dynamic loader has to know that there is still a reference to it by another handle and prevent the library from being unloaded.