FrontBase™ release 2 Highlights
FrontBase release 2 represents a new milestone when it comes to affordable enterprise level database servers. Many new features and performance improvements have been added to FrontBase release 2, making it a truly scalable database server for the internet.
The list of new features is as long as it is impressive:
- Stored procedures and functions
- Server side cursors
- Replication
- Cluster
- Query plan information
- Multiple non-rectangular result sets
- Optimized index handling for large tables
- Server side auto commit
- VARCHAR/CHAR hashing
- Dynamic SQL
- Schema synchronization
- Bitwise AND operator
- Legacy style outer joins
- ON DELETE/ON UPDATE FOREIGN KEY constraint support
- Increased STOP/DELETE database security
- Static port number for the FrontBase server
- Client side keyword list
and many other new features.
FrontBase release 2 provides also support for and includes drivers for JDBC 2.0, PHP3 and PHP4.
1. Stored procedures and functions
FrontBase release 2 supports stored procedures and functions as defined in the Persistent Stored Modules addition to the official SQL 92 standard. The support includes overloading, OUT and INOUT parameters, allowing for powerful usage of the stored procedure concept.
A stored procedure or function is considered a schema object on the same level as e.g. a table and can thus be defined and referenced by using the normal visibility rules and qualified names.
Examples:
-- The first example shows how to call a stored procedure that
-- takes one IN parameter and one OUT parameter. It also shown
-- how the OUT parameter can be returned to a client by means of
-- a normal query (table expression).
CREATE PROCEDURE LOOKUP_WORD(IN :W VARCHAR(64), INOUT :WID INT)
BEGIN
SET :WID = CAST(NULL AS INT);
SELECT WORDID INTO :WID FROM WORD_TABLE WHERE WORD = :W;
IF :WID IS NULL THEN
SET :WID = SELECT UNIQUE FROM WORD_TABLE;
INSERT INTO WORD_TABLE VALUES(:WID, :W);
END IF;
END;
DECLARE WID INT;
CALL LOOKUP_WORD('Smith', :WID);
VALUES(:WID);
-- The next example shows an identical functionality, but this time
-- by using a stored function
CREATE FUNCTION LOOKUP_WORD(IN :W VARCHAR(64)) RETURNS INTBEGIN DECLARE WID INT;
SET :WID = CAST(NULL AS INT);
SELECT WORDID INTO :WID FROM WORD_TABLE WHERE WORD = :W;
IF :WID IS NULL THEN
SET :WID = SELECT UNIQUE FROM WORD_TABLE;
INSERT INTO WORD_TABLE VALUES(:WID, :W);
END IF;
RETURN :WID;
END;
VALUES(LOOKUP_WORD('Smith'));
2. Server side cursors
Server side cursors are a part of the SQL 92 standard and provide for an efficient way of handling large result sets, i.e. no need for copying all rows of the result set to the client side. Cursors can be created as scrolling as well as updatable.
Example:
-- First we declare a cursor DECLARE X FOR SELECT WORDID, WORD FROM WORD_TABLE WHERE WORDID < 100000 AND WORDID > 999 ORDER BY WORDID; -- then the cursor needs to be opened OPEN X; -- and now we can fetch the first row FETCH NEXT FROM X; -- Returns one row to the client as a normal SELECT FETCH NEXT FROM X INTO :V0, :V1; -- Fetches the next row and stores the columnm values -- into the two variables -- FETCH will return an error when there are no more rows to fetch. -- Cursurs can be closed CLOSE X; -- and can be dropped DROP X; -- If a cursor is not dropped, it will stay available until the -- session is closed.
3. Replication
Replication is a feature that allows you to maintain one master database, which receives all modifications, and N read-only copies. New read-only copies can be added on the fly. If a read-only client is offline it will automatically be synchronized with the master when it becomes online again.
4. Cluster
Clusters is a feature that allows you to have N databases that are always synchronized, i.e. they have the same content. Any of the N databases can receive modifications and a modified database will upon COMMIT time synchronize with the other databases in the cluster.
5. Query plan information
Query plan information details how the server will execute a given query including which indexes will be used. To turn on the feature, simply execute:
SET DESCRIBE SELECT TRUE;
Subsequent SELECT statements will, as part of the returned meta data, contain the query plan information. Currently, only the FBWebManager is able to display the query plan information as properly formatted data.
The feature can be turned off by executing:
SET DESCRIBE SELECT FALSE;
6. Multiple non-rectangular result sets
Multiple result sets are a way to have the server prepare, so to speak, result sets for multiple SELECT statement in advance, thus saving potentially costly round trips to the server. Stored procedures are a good example of how to execute multiple SELECT statements before returning to the client.
7. Optimized index handling for large tables
The standard way for FrontBase to handle indexes is very memory efficient, the average overhead of a multi-column index is less than 5 bytes per row. To facilitate as fast an access as possible to large tables (> 1 million rows), FrontBase release 2 introduces a new index mode, which offers dramatic performance improvements while consuming more memory.
To switch modes, you only need to execute a single SQL statement:
ALTER TABLE <table name> SET INDEX PRESERVE TIME;
-- Good for large tables
ALTER TABLE <table name> SET INDEX PRESERVE SPACE;
-- Good for smaller tables
8. Server side auto commit
It is now possible to instruct FrontBase to automatically execute a COMMIT after each statement. This can save a number of round-trips to the server, but can also degrade performance pending on the actual SQL statements executed. Typically it will improve performance to include as many as possible SQL statements (INSERT, UPDATE, DELETE, ...) in a single transaction. For other types of statements, e.g. SELECTs, and assuming a proper configuration of caches etc., the auto commit feature can make a difference.
To enable the auto commit feature, you need only to execute a single SQL statement:
SET COMMIT TRUE;
To disable the auto commit feature:
SET COMMIT FALSE;
9. VARCHAR/CHAR hashing
Storing identical strings in a table normally means redundant data, which could mean larger than required use of disk space. Typically this problem is handled by creating e.g a WORD table and then a RELation table:
CREATE TABLE WORD(
WID INT,
WORD VARCHAR(64));
CREATE TABLE REL(
DOCID INT,
WID INT);
SELECT DOCID FROM WORD, REL
WHERE WORD = 'word'
AND
WORD.WID = REL.WID;
In FrontBase this can, without incurring larger disk space overhead, be handled much more efficiently:
CREATE TABLE REL(
DOCID INT,
WORD VARCHAR(64));
SELECT DOCID FROM REL
WHERE WORD = 'word';
The above SELECT is typically also faster since a join between two tables has been saved.
10. Dynamic SQL
Dynamic SQL is a way to have SQL statements prepared by the server, i.e. the statements are sent to the server once and can then be referenced for multiple uses. The prepared statements are stored, as part of the session data, on the server side in a compiled form.
Statements are prepared by prefixing them with the PREPARE keyword:
PREPARE INSERT INTO WORD VALUES(?, ?);
-- The returned meta data contains a socalled statement
-- handle which is to be used when referencing the
-- prepared statement
Prepared statements are executed by means of the EXECUTE statement:
EXECUTE <stmt handle> WITH(99, 'Prepare');
Prepared statements are automatically dropped when a session is terminated, but can also be dropped explicitly:
DROP <statement handle>;
11. Schema synchronization
FrontBase now offers the possibility to change the datatype of a column. The new datatype can be a simple length adjustment or it can be a completely different datatype.
ALTER COLUMN <table name>.<column name> TO [<datatype>] [COLLATE <collation>];
The COLLATE option is an easy way to change the collation of a column, i.e. from no collation to a e.g. case insensitive collation.
ALTER TABLE WORD.WORD TO COLLATE CASE_INSENSITIVE;
When changing the datatype to a type that can not be casted straigth forward, a best fit will occur possibly by replacing the original value with a NULL.
12. Bitwise AND operator
The bitwise AND operator (&) works on values of INT and SMALLINT datatypes. The resulting value is still an INT (or SMALLINT) value. The operator is a convenient way to test whether a given bit in a column value is set.
SELECT * FROM WORD WHERE WID & 16 > 0;
13. Legacy style outer joins
FrontBase is SQL 92 compliant and thus offers the full suite of OUTER joins as defined by the SQL 92 standard. For mainly portability reasons and to ensure better integration with Apple's WebObjects, a legacy style outer joins are now offered:
*= Left outer join =* Right outer join *=* Full outer join
Example:
SELECT * FROM T0, T1 WHERE T0.c0 *= T1.D1;
14. ON DELETE/ON UPDATE FOREIGN KEY constraint support
FrontBase release 2 completes the support for FOREIGN KEY constraints and now offers triggers detailing what should happen when a row is deleted or updated.
ALTER TABLE <table name>
[CONSTRAINT <constraint name> ]
ADD FOREIGN KEY ( <refing columns> ) REFERENCES ( <refd. columns> )
[ ON DELETE { NO ACTION | CASCADE | SET DEFAULT | SET NULL } ]
[ ON UPDATE { NO ACTION | CASCADE | SET DEFAULT | SET NULL } ];
ON DELETE:
If a row in a table T0 is deleted, all tables which have FOREIGN KEYs referencing T0 are checked:
NO ACTION: Nothing is done which typically means that a constraint error is raised, i.e. the referencing table has one or more rows that references a row in T0 that has been deleted.
CASCADE: Referencing rows in other tables are deleted as well. This causes a recursive action.
SET DEFAULT: The referencing columns in the rows matching in the referencing tables are set to their default values (NULL if no default is defined). This causes a recursive action.
SET NULL: The referencing columns in the rows matching in the referencing tables are set to NULL. This causes a recursive action.
ON UPDATE:
If a row in a table T0 is updated, all tables which have FOREIGN KEYs referencing T0 are checked. The check is only done if the updated columns are included in PRIMARY KEY or UNIQUE constraints.
NO ACTION: Nothing is done which typically means that a constraint error is raised, i.e. the referencing table has one or more rows that references a row in T0 that has been updated.
CASCADE: Referencing rows in other tables are updated as well. This causes a recursive action.
SET DEFAULT: The referencing columns in the rows matching in the referencing tables are set to their default values (NULL if no default is defined). This causes a recursive action.
SET NULL: The referencing columns in the rows matching in the referencing tables are set to NULL. This causes a recursive action.
15. Increased STOP/DELETE database security
Databases can now only be stopped/deleted if a connection to the database as user _SYSTEM succeeds.
16. Static port number for the FrontBase server
Normally the FrontBase server will dynamically allocate a port number from the host operating system. To enable a smoother integration with e.g. environments using firewalls, the FrontBase server can now be instructed to use a specific port number.
A command line invocation of the FrontBase server is given to illustrate how to use the port number feature:
<install path>/bin/FrontBase -port=<port number> <database name>
17. Client side keyword list
As SQL92 includes a long list of keywords, a mechanism is offered by which client side software can obtain a list containing all the keywords used by the FrontBase server. The FBWebEnabler is a good example of how this feature can be used to convey more information to the developer.