In this article Barry Mavin, CEO and Chief Software Architect for Recital, gives details on Working with user-defined Functions in the Recital Database Server.

Overview

User-defined functions (UDFs) are collections of statements written in the Recital 4GL (compatible with Visual FoxPro) stored under a name and saved in a Database. User-defined functions are just-in-time compiled by the Recital database engine. User-defined functions can be used in SQL statements to extend the power and flexibility of the inbuilt functions. Using the Database Administrator in Recital Enterprise Studio, you can easily create, view, modify, and test Stored Procedures, Triggers, and user-defined functions.

Tip

You can also extend the Recital Database Server with C Extension Libraries and use the functions defined within that library also.

Creating and Editing user-defined functions

To create a new User-defined function,  right-click the Procedures node in the Databases tree of the Project Explorer and choose Create. To modify an existing User-defined function select the User-defined function in the Databases Tree in the Project Explorer by double-clicking on it or selecting Modify from the context menu. By convertion we recommend that you name your User-defined functions beginning with "f_xxx_", where xxx is the name of the table that they are associated with.

Testing the user-defined function

To test run the user-defined function, select it in the Databases Tree in the Project Explorer by double-clicking on it. Once the Database Administrator is displayed, click the Run button to run it.

Example

Example: user-defined function "f_order_details_total".

////////////////////////////////////////////////////////////////////////
// example user-defined function
function f_order_details_total(pUnitprice, pQuantity, pDiscount)
    return (pUnitprice + pQuantity + pDiscount) > 0
endfunc

Example: using the user-defined function in a SQL SELECT statement.

////////////////////////////////////////////////////////////////////////
// sample code to use a user-defined function in a SQL SELECT statement
select * from customers where f_order_details_total(Unitprice, Quantity, Discount)

Using user-defined function libraries with the Recital Database Server

You can place all of the user-defined functions associated with a particular table into a procedure library. You then define an Open Trigger for the table that opens up the procedure library whenever the table is accessed. This is a much faster way of using user-defined functions as it reduces the amount of file open/close operations during a query and also simplifies development and maintenance.

By convertion we recommend that you should name the library using the convention "lib_xxx", where xxx is the name of the table that the library is associated with.

Example: procedure library in lib_order_details.

////////////////////////////////////////////////////////////////////////
// example user-defined functions
function f_order_details_total(pUnitprice, pQuantity, pDiscount)
    return (pUnitprice * pQuantity - pDiscount) > 0
endfunc

function f_order_details_diff(pUnitprice, pQuantity, pDiscount, pValue)
    return f_order_details_total(pUnitprice, pQuantity, pDiscount) - pValue
endfunc

Example: Open Trigger in dt_order_details_open.

////////////////////////////////////////////////////////////////////////
// This trigger will open up the procedure library when the table is opened
set procedure to lib_order_details additive

Example: Close Trigger in dt_order_details_close.

////////////////////////////////////////////////////////////////////////
// This trigger will close the procedure library when the table is closed
close procedure lib_order_details

Example: using the user-defined function in a SQL SELECT statement.

////////////////////////////////////////////////////////////////////////
// sample code to use a user-defined function in a SQL SELECT statement
select * from customers where f_order_details_total(Unitprice, Quantity, Discount)

User-defined functions can also be used with any of the Client Drivers that work with the Recital Database Server.

DRBD:
DRBD (Distributed Replicated Block Device) forms the storage redundancy portition of a HA cluster setup. Explained in basic terms DRBD provides a means of achieving RAID 1 behavoir over a network, where whole block devices are mirrored accross the network.

To start off you will need 2 indentically sized raw drives or partitions. Many how-to's on the internet assume the use of whole drives, of course this will be better performance, but if you are simply getting familar with the technology you can repartition existing drives to allow for two eqaully sized raw partitions, one on each of the systems you will be using.

There are 3 DRBD replication modes:
• Protocol A: Write I/O is reported as completed as soon as it reached local disk and local TCP send buffer
• Protocol B: Write I/O is reported as completed as soon as it reached local disk and remote TCP buffer cache
• Protocol C: Write I/O is reported as completed as soon as it reached both local and remote disks.

If we were installing the HA cluster on a slow LAN or if the geogrphical seperation of the systems involved was great, then I recommend you opt for asyncronous mirroring (Protocol A) where the notifcation of a completed write operation occurs as soon as the local disk write is performed. This will greatly improve performance.

As we are setting up our HA cluster connected via a fast LAN, we will be using DRBD in fully syncronous mode, protocol C.
Protocol C involves the file system on the active node only being notified that the write operation was finished when the block is written to both disks of the cluster. Protocol C is the most commonly used mode of DRBD.

/etc/drbd.conf

global { usage-count yes; }
common { syncer { rate 10M; } }
resource r0 {
protocol C;
net {
max-buffers 2048;
ko-count 4;
}
on bailey {
device    /dev/drbd0;
disk      /dev/sda4;
address   192.168.1.125:7789;
meta-disk internal;
}
on giskard {
device    /dev/drbd0;
disk      /dev/sda3;
address   192.168.1.127:7789;
meta-disk internal;
}
}

drbd.conf explained:

Global section, usage-count. The DRBD project keeps statistics about the usage of DRBD versions. They do this by contacting a HTTP server each time a new DRBD version is installed on a system. This can be disabled by setting usage-count no;.

The common seciton contains configurations inhereted by all resources defined.
Setting the syncronisation rate, this is accoimplished by going to the syncer section and then assigning a value to the rate setting. The syncronisation rate refers to rate in which the data is being mirrored in the background. The best setting for the syncronsation rate is related to the speed of the network with which the DRBD systems are communicating on. 100Mbps ethernet supports around 12MBps, Giggabit ethernet somewhere around 125MBps.

in the configuration above, we have a resource defined as r0, the nodes are configured in the "on" host subsections.
"Device" configures the path of the logical block device that will be created by DRBD
"Disk" configures the block device that will be used to store the data.
"Address" configures the IP address and port number of the host that will hold this DRBD device.
"Meta-disk" configures the location where the metadata about the DRBD device will be stored.
You can set this to internal and DRBD will use the physical block device to store the information, by recording the metadata within the last sections of the disk.
Once you have created your configuration file, you must conduct the following steps on both the nodes.

Create device metadata.

$ drbdadm create-md r0
v08 Magic number not found
Writing meta data...
initialising activity log
NOT initialized bitmap
New drbd meta data block sucessfully created.
success

Attach the backing device.
$ drbdadm attach r0

Set the syncronisation parameters.
$ drbdadm syncer r0

Connect it to the peer.
$ drbdadm connect r0

Run the service.
$ service drbd start

Heartbeat:

Heartbeat provides the IP redundancy and the service HA functionailty.
On the failure of the primary node the VIP is assigned to the secondary node and the services configured to be HA are started on the secondary node.

Heartbeat configuration:

/etc/ha/ha.conf

## /etc/ha.d/ha.cf on node1
## This configuration is to be the same on both machines
## This example is made for version 2, comment out crm if using version 1
// replace the node variables with the names of your nodes.

crm no
keepalive 1
deadtime 5
warntime 3
initdead 20
bcast eth0
auto_failback yes
node bailey
node giskard

/etc/ha.d/authkeys
// The configuration below set authentication off, and encryption off for the authentication of nodes and their packets.
//Note make sure the authkeys file has the correct permisisions chmod 600

## /etc/ha.d/authkeys
auth 1
1 crc

/etc/ha.d/haresources
//192.168.1.40 is the VIP (Virtual IP) assigned to the cluster.
//the "smb" in the configuration line represents the service we wish to make HA
// /devdrbd0 represents the resource name you configured in the drbd.conf

## /etc/ha.d/haresources
## This configuration is to be the same on both nodes

bailey 192.168.1.40 drbddisk Filesystem::/dev/drbd0::/drbdData::ext3 smb

Recital 10 introduced the REQUIRE() and REQUIRE_ONCE() statement.

The REQUIRE() statement includes and executes the contents of the specified file at the current program execution level.

When a file is included, the code it contains inherits the variable scope of the line on which the include occurs. Any variables, procedures, functions or classes declared in the included file will be available at the current program execution level.

The REQUIRE_ONCE() statement is identical to the REQUIRE() statement except that Recital will check to see if the file as already been included and if so ignore the command.

The full syntax is:

REQUIRE( expC )
REQUIRE_ONCE( expC )

e.g.

REQUIRE_ONCE( "myapp/myglobals.prg" )

An extremely useful article that describes some firefox undocumented features that allow you to install Firefox XPI And JAR Firefox Add-ons And Themes.