InterBase 6

Problem/Question/Abstract:

More Than Open Source

Answer:

The feature of InterBase 6 that has received the most attention is its change to being an open source product. However, InterBase 6 also provides a host of important new features for database developers. This article provides a brief description of many of the new features of InterBase 6, including: large exact numerics, new administrative tools, replication, a more powerful ALTER TABLE command, and much more.

Large Exact Numerics

Perhaps the most important new feature for business application developers is called large exact numerics. In prior versions, InterBase implemented the Numeric and Decimal data types in an unusual way. Numeric and Decimal are fixed-decimal data types. When you declare them, you must specify two numbers called precision and scale. Precision is the total number of digits the column can contain; scale is the number of digits to the right of the decimal point. Precision is limited to 18 digits. For example, a column declared as NUMERIC(15,2) would hold a total of 15 digits, and two of those 15 digits would be to the right of the decimal point. The difference between the Numeric type and the Decimal type is that the Numeric type always stores precision digits and the Decimal type stores precision digits or more.

The SQL standard defines the Numeric and Decimal data types primarily to provide precise storage for money amounts, but they are equally useful for any amount that requires a fractional part to be stored accurately. This contrasts with floating point data types, which store fractional amounts approximately. This imprecision can lead to errors that are unacceptable in financial applications. For example, if you store monetary amounts in floating-point format, it's possible to add a large number of values and have the result be off by a penny. In prior versions of InterBase, Numeric and Decimal columns whose precision was nine or less were stored as integers; however, if the precision was greater than nine they were stored in floating-point format, with the resulting lack of precision in the fractional part.

InterBase 6 stores all Numeric and Decimal values as scaled integers, so the numbers are always stored precisely. Columns with a precision up to four are stored as 16-bit integers. Those with a precision of five through nine are stored as 32-bit integers and columns whose precision is greater than nine are stored as 64-bit integers. Another important use of the Numeric and Decimal data types in InterBase 6 is for primary keys, because generators now return a 64-bit integer value instead of the 32-bit integer returned in prior versions. Since there is no 64-bit integer data type you must use Numeric or Decimal to store integer values beyond the range of a 32-bit integer.

Dialects

InterBase 6 introduces the concept of dialects as a way to implement new features required by the SQL 92 standard that conflict with features in previous versions of InterBase. InterBase 6 implements three dialects. Dialect one is compatible with older versions of InterBase; dialect two is a diagnostic mode; and dialect three introduces support for quoted identifiers, large exact numerics, and the SQL Date, Time, and TimeStamp data types.

In dialect one a string constant in a SQL statement can be delimited by single or double quotes. In dialect three, string constants must be enclosed in single quotes. Double quotes are reserved for the names of database objects which are reserved words, contain spaces, contain non-ASCII characters, or are case-sensitive. Although quoted identifiers can include spaces followed by other characters, they cannot include trailing spaces.

Until now, the Date data type, which contains both date and time information, was the only way to store date and time information in InterBase. Dialect three introduces the SQL 92 Date, Time, and TimeStamp types. Date fields store date information only, Time fields store time information only, and TimeStamp is equivalent to the dialect one Date type and stores both the date and time. If you back up a version 5 or older database and restore it in version 6, all Date columns and domains will be converted to TimeStamp. InterBase 6 also introduces the CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP functions to retrieve the current date, time, or date and time, respectively. Also new is the EXTRACT function for extracting information from the data types that contain date and/or time information. For example:

EXTRACT(YEAR FROM SomeDate)

would return the year portion of the date from a Date or TimeStamp value.

InterBase Express

InterBase Express for InterBase 6 adds a new InterBase Admin tab to the Component palette. The Admin tab contains 11 new components that provide an interface to the new InterBase Service, Install, and Licensing APIs. With these components, you can install InterBase or the InterBase client, and administer every aspect of your InterBase server and database.

The IBBackupService and IBRestoreService components allow you to back up and restore InterBase databases, on a client machine or on the database server. Using the IBConfigService component you can start and stop the InterBase service and determine if the service is running. You can also shut down a database, or bring a database on line. IBConfigService also provides methods to set all of the parameters of a database, including read-only, the SQL dialect, the number of page buffers, the sweep interval, the asynchronous write mode, and whether space for record versions is automatically reserved in the database pages.

Use the IBValidationService component to validate your database and recover any transactions that are in limbo. The IBStatisticalService reports various statistics about your database. The IBLogService is a handy diagnostic tool that retrieves the contents of the InterBase log file from the server. If you need to add or delete users, or make changes to the security settings for a user, use the IBSecurityService component. The IBLicensingService component lets you add license certificates, and the IBServerProperties component reports server licensing and configuration information. Finally, the IBInstall and IBUninstall components let you easily install or uninstall InterBase from your application.

IBConsole

The most noticeable change in InterBase 6 is that Server Manager and WISQL have been replaced by IBConsole. IBConsole, shown in Figure 1, is a Windows application you can use to administer InterBase servers and databases on any platform. The IBConsole user interface is divided into a tree pane, on the left, and a work pane, on the right. The tree pane provides a view of your InterBase servers, databases, and database objects. Figure 1 shows two servers, each with one database, with all nodes in the tree expanded. The tree pane lets you select and work with servers, databases, backups, license certificates, the server log, and users. For each database you have access to domains, tables, views, stored procedures, external functions (also called user-defined functions), generators, exceptions, BLOB filters, and roles.


Figure 1: The tree pane and work pane of the IBConsole user interface.

You can work with servers and databases in two ways. The easiest way is to right-click the server or database, and make a choice from the context menu. The alternative is to select a server or database, and use the main menu. Before you can work with a server, you must right-click on InterBase Servers, the root entry in the tree pane, and choose Register to register the server. Figure 2 shows the Register Server and Connect dialog box. Simply enter the server's name, choose a network protocol, enter an alias for the server (which will appear in the tree view) and enter a user name and password. Click OK and the server will appear in the tree pane. Right-click on the server to register an existing database or create a new one.


Figure 2: The Register Server and Connect dialog box.

What appears in the work pane depends on what you select in the tree pane. Figure 3 shows the work pane with a database selected. The items in the work pane offer most of the same options you will see on the context menu, if you right-click the database in the tree pane. To perform any task in the work pane, simply double-click it.


Figure 3: The work pane with a database selected.

When you expand a database you can double-click on Domains, Tables, Views, Stored Procedures, External Functions, Generators, Exceptions, Blob Filters, or Roles. Figure 4 shows the result of double-clicking the Customer table. The Properties tab lets you see the structure of the table, and the Metadata tab shows the CREATE TABLE statement for the table. Permissions lists all users with access to the table and their rights. Using the Data tab you can browse the table's data, and Dependencies lists any objects in the database that depend on the table. Double-clicking on other database objects displays similar information.


Figure 4: The properties of a table.

Unfortunately, most of the information displayed in IBConsole is read-only. To make changes you must still write your own SQL. Click the SQL toolbar button, or choose Tools | Interactive SQL from the menu to open the SQL Editor shown in Figure 5. The top pane shows your SQL script. You can enter your SQL from the keyboard, or load an existing SQL script and execute it. Any SQL you enter can also be saved as a script file. The bottom pane shows the result of executing the script, or, if the script includes a SELECT statement, the bottom pane displays the returned data.


Figure 5: The SQL editor.

Replication

With InterBase 6 you can replicate a source database to any number of target databases. The targets can have different table structures and different column names. You can replicate different tables, rows, and columns to each target database.

Replication can be initiated by a request from a client application, at timed intervals configured in the replication scheduler, or by an InterBase event. You can also use synchronous replication to propagate each change to the target database(s) as soon as it occurs.

Replication would be simple if the source database was the only one being changed. However, if both the source and target databases are being changed, some mechanism must be provided to resolve conflicts. Conflict resolution can be configured to any of three modes. Using priority-based resolution, the database with the highest priority takes precedence. Suppose the source database has the highest priority. In this case, an update to a record that doesn't exist in the target is automatically converted to an insert. An insert where the target already contains a record with the same primary key is converted to an update. A delete where the record doesn't exist in the target is ignored. Using time-stamped resolution, the change with the latest timestamp has precedence. Using master/slave resolution, the source database takes precedence.

For each source/target database pair you must create a configuration database using the Replication Manager, as shown in Figure 6. While this is a bit more work if the replication and structure is identical for all of the targets, it provides virtually infinite flexibility if they are not. The next step is to add the source and target databases using the Databases tab of the Replication Manager's main form (see Figure 7).


Figure 6: The Replication Manager's Configuration databases form.


Figure 7: Adding databases in the Replication Manager.

Use the Replication Manager's Replications page, shown in Figure 8, to create the replication schemata. The schemata identifies the source and target databases and the tables, rows, and columns that will be replicated. The final step is to double-click the Create system objects icon in the Replication Manager's control panel at the right side of the form. This step automatically creates the log table and stored procedures required for replication in the source database. Note that no changes are made to the target database.


Figure 8: The Replications page of the Replication Manager.

Read-only Databases

InterBase 6 databases have two modes: read-write and read-only. All databases are created in read-write mode, but can be changed to read-only using gbak, gfix, or IBConsole. Once a database has been changed to read-only mode you can copy it to a CD-ROM, or any other read-only media, and access the data. The only restrictions are that you cannot change the data or metadata, and you can only access generators to get their current value. For example:

SELECT GEN_ID(EMP_NO_GEN, 0) FROM EMPLOYEE

will work, and will return the current value of the EMP_NO_GEN generator. However:

SELECT GEN_ID(EMP_NO_GEN, 1) FROM EMPLOYEE

will fail because the generator cannot be incremented. To make a database read-only using IBConsole, right-click the database and choose Properties from the context menu to display the Database Properties dialog box shown in Figure 9. Click Read Only, then click the drop-down arrow and choose True. Finally, click the Apply button to change the mode to read-only. To change the mode with gbak, back up the database, then restore it in read-only mode using the following command:

gbak -create -mode read-only employee.gbk employee.gdb

To change the mode using gfix use the following command:

gfix -mode read_only employee.gdb

To change the mode to read-only or read-write, you must be the owner of the database or sysdba, and you must have exclusive use of the database.


Figure 9: The Database Properties dialog box.

ALTER TABLE and ALTER DOMAIN

The SQL ALTER TABLE statement now allows you to change the name, data type, or position of an existing column in a table. For example:

ALTER TABLE CUSTOMER
ALTER POSTAL_CODE TO ZIP_CODE

will change the name of the POSTAL_CODE field to ZIP_CODE, and:

ALTER TABLE CUSTOMER
ALTER POSTAL_CODE TYPE VARCHAR(14)

will change the data type to VARCHAR(14). In InterBase 6, ALTER DOMAIN also lets you change the type of a domain.

GBAK

The gbak utility now includes the functionality provided by gsplit in InterBase 5, allowing you to back up to multiple files in a single step. The new -service switch lets you run a back up on the server, without copying the data across the network to the client. This can mean substantially faster backups with less network traffic load.

Conclusion

InterBase 6 is a major upgrade. With the addition of large exact numerics, replication, and the InterBase Express Admin tools, you now have a full-featured SQL database server - available at no cost - that lets you create BDE-free database applications that support any requirement from a single user, with a modest amount of data to hundreds of users with many gigabytes of data distributed across multiple databases. It's even a great database for over-the-counter software that requires a large database on a CD-ROM.

InterBase 6 will be available from http://www.interbase.com/. Packaged CD editions will follow shortly thereafter. Editions with printed documentation will cost more.