DBMS, July 1996
When you acquire the full Delphi application development environment from Borland International Inc., you get a "little" relational database management system called Interbase as part of the package. This "little" RDBMS supports the relational data model well and has some very interesting features.
I have known of Interbase since 1988. At that time, I was lecturing on database systems and database theory in the Computer Science department at a local university. I was using proprietary products such as DG/SQL and DG/DBMS to illustrate a small part of the theory I was teaching. The local distributor of a relatively unknown 4GL that I used as part of the course (one of the company directors was an ex-student of said university) twisted my arm to look at a relational product called Interbase. He said it would put "more relational power" under the 4GL applications I was developing as part of the course.
Back then, Interbase was engineered, developed, and distributed by Interbase Inc., headed by Jim Starkey, an ex-DEC employee who developed DSRI for Rdb. Rumor has it that Starkey wanted to incorporate more relational features into Rdb but wasn't given the go-ahead by DEC, so he engineered all his good ideas into Interbase. At that time Interbase was also distributed by Cognos Inc. as StarBase, the relational database under its PowerHouse 4GL. Shortly thereafter, Ashton-Tate bought Interbase as its offering in the midrange market and also as a more powerful departmental server behind dBASE. Then Borland bought Ashton-Tate, with Interbase included in the deal. Today Borland almost gives away Interbase with its Delphi application development environment.
But enough product history ... great was my surprise when I investigated Interbase more closely. Even at that early stage it had some "very relational" features and a number of interesting facilities. As Interbase wasn't marketed very well at that stage, I thought it was one of the best-kept secrets in the database world. This product gave new life to the database courses I was teaching -- to the extent that I left the university a few years later for the greener pastures of database consulting, starting on potential Interbase customers. Today, I'm still evaluating and lecturing on RDBMS products, and Interbase is still one of the best kept secrets in the database industry.
Any data model consists of structures (to store the data), operations (to manipulate the data), and integrity constraints (to ensure that the data is always consistent and correct). (See E.F. Codd, A Relational Model of Data for Large Shared Data Bases, Comm. of the ACM, Vol. 13, No 6, 1970.) The relational model fits well into this definition. It has domains and relations (tables) as structures, languages such as SQL (which act on data stored in relational databases), and, of course, domain, key, column, referential, and user-defined constraints. In addition to the formal definition of the relational data model, there are various characteristics you need from an RDBMS, such as orthogonality and updatable views. Interbase supports the relational data model quite well, much better than most products that call themselves relational just because they can represent and process data in tables.
Structures. Interbase is one of very few RDBMSs that support domains properly. In Interbase, you can define a domain as a named data type. You can then use any number of domain definitions in a relation's (table's) column definition. Everything you define for the domain is then applied to each column whose data type is that user-defined domain.
For example, in Interbase you can define a domain and then use it in a number of table definitions:
create domain CourseNo as varchar(6)
check (value like '__-____');
create domain Year as date
check (value >= cast('1900' as date) and
value <= cast('2500' as date));
create table COURSES
(CourseNo CourseNo not null,
CourseYear Year not null;
...
primary key (CourseNo));
For every column in which the CourseNo and Year domains are used, even if it is for three columns in the same table, the data type and constraints defined for the domain are enforced.
Interbase supports most SQL data types, but it does not directly support date, time, and timestamp. However, it does support blob, char(n), character(n), date, decimal (precision, scale), double precision, float, integer, numeric (precision, scale), smallint,varchar(n), varying char(n), and varying character(n).
The date data type includes time information as well. You can use the double precision data type to store values from 1.7 ý 10-308 to 1.7 ý 10308, with 15 digits of precision. You can use the float data type to store values from 3.4 ý 10-38 to 3.4 ý 1038, with seven digits of precision. You can use the integer data type to store values from ý2,147,483,648 to 2,147,483,648. And you can use the smallint data type to store values from ý32,768 to 32,767.
Interbase was one of the first RDBMSs to support binary large objects, or BLOBs. Interbase is used to store large nontabular data objects, such as graphics, text, and digitized voice. BLOBs are built up from structural units called segments. A BLOB is returned from an Interbase database to a 3GL program, for example, as a sequence of segments. A BLOB can have a subtype that describes the BLOB's contents and the structure of its segments.
Interbase is also one of the few RDBMSs to support arrays. You can define any column as a multidimensional array of a specified data type or of a specified domain. You can also specify the minimum and maximum range of each dimension.
For example, you can create a table containing a matrix as a column:
create domain ProductNo varchar(7);
create domain Sales decimal(15,2);
create table QUARTERLY_SALES
(ProductNo ProductNo not null,
Sales Sales[1980:2010, 1:4],
/* Sales per quarter of each year between 1980 and 2010 */
primary key (ProductNo));
Operations. In its earlier releases, Interbase supported two languages: SQL, for those who wanted to stick to the ANSI SQL standard, and GDML, for those who didn't care about SQL and wanted better relational processing. Unfortunately, GDML has gone the way of the dinosaur, and Interbase users are stuck with the SQL language, with a few extensions inherited from GDML. Like most other RDBMSs, Interbase currently supports only SQL and its product-specific extensions. Interbase SQL has the following extensions for queries:
* conversion functions between data types, such as the cast function
* a system-generated number function, called gen_id
* various relational extensions to the where clause, such as:
SINGULAR (* the specification of access plans for the query to the optimizer) [NOT] CONTAINING [NOT] STARTING [WITH]
Interbase supports union, inner join, left outer, right outer, and full outer join operations.
Constraints. Because of its good implementation of domains, Interbase supports proper domain-based integrity constraints. You can define check constraints for each domain; these constraints are enforced for each table column in which the domain is utilized. However, you cannot change the messages returned by these constraints.
Interbase also supports key constraints, column constraints, and basic declarative referential-integrity constraints. These are specified using the create table and alter table statements, using entry-level ANSI SQL-92 syntax. The only option available when a referential-integrity constraint is violated is the default action: Restrict the operation. Unfortunately, you cannot customize the messages returned by Interbase when these constraints are violated.
Alternatively, you can code your own constraints using triggers. Interbase has a powerful and modular implementation of triggers. (See "Trigger Happy," DBMS, May 1996, page 89.) You can define multiple triggers per table, before and after the operations "firing" the triggers. You can specify the trigger-firing sequence for multiple triggers defined for the same operation on the same table. The pre-operation triggers are useful and efficient to check whether an operation will violate a constraint before the operation is actually performed. This aborts the operation before the DBMS performs any significant work.
Orthogonality. Orthogonality means that when a feature is supported in one context, it should be supported in every other similar context in which it can be interpreted sensibly and unambiguously. (See E.F. Codd, The Relational Model for Database Management: Version 2, Addison-Wesley, 1990.) For example, wherever a constant can appear in a query, it can be replaced by an expression that yields a value that is type-compatible with the constant. Another well-known example is that there shouldn't be any significant difference between tables and views in operations and queries -- both should be considered relations and should be processed as such.
Interbase previously implemented this concept in its GDML language, which didn't make any distinctions between tables and views. The most interesting orthogonal feature of Interbase is that its triggers can be defined on tables and views. This is how Dr. Codd proposed that the relational data model should work -- a relation is a relation; it should not matter whether it is implemented as a physical table or virtual view.
With triggers defined on views, you can implement integrity-checking triggers on a view, which may, for example, be the union of two or more tables. This is useful for implementing specialization and generalization hierarchies.
Updatable Views. Most RDBMSs are primitive in their support of updatable views. Most RDBMSs do not allow updates to complex views (for example, a view defined as a join between two tables -- or two relations, be it tables or views, to be exact). In this context, "update" means the generic case, namely the equivalent of SQL insert, update, or delete operations. Because the developers of these RDBMSs haven't bothered to implement the complex mappings from operations on the view to operations on the underlying tables or views, they simply do not allow any operations on complex views.
Interbase, however, gives you a way out. By providing triggers on views, you can implement fully updatable complex views. When you define a complex view, Interbase does not map the operations on the view to operations on the underlying tables or views. Instead, through the triggers you can define on the view, you can implement the mappings of the operations on the view as corresponding operations on the underlying tables or views. You can even implement the triggers on a complex view in such a way that a theoretically non-updatable view becomes physically updatable, if you perform enough processing and value generation in the view's triggers. Note, however, that this is not a trivial exercise. It requires a thorough understanding of updatable and non-updatable views, as well as the mappings between complex views and the underlying tables or views. (See the chapter on updating views in Relational Database: Selected Writings, C.J. Date, Addison-Wesley, 1986.)
Interbase has some interesting features that very few other RDBMSs have. These include optimistic concurrency control, a black-box physical database file, and automatic table restructuring.
Optimistic Concurrency Control. While the developers and users of most other RDBMSs are debating page-level versus row-level locking, Interbase has taken a completely different approach to concurrency control. Interbase was positioned as a server suited to OLCP -- online complex processing. This was partly to advertise Interbase's BLOB and array data types. Concerning concurrency control, Interbase argued that in OLTP systems, and in OLCP systems in particular, you often have the requirement that short, fast, online screen-based transactions must coexist with longer, slower reports to query, extract, and format the data. In the traditional locking-based systems, locking conflicts are often experienced between application components serving these two widely differing requirements.
Interbase overcomes the throughput problems encountered in locking-based systems by using an optimistic version-based concurrency control mechanism. Whenever a transaction updates a row, a new version of that row is created, with a new timestamp. The transaction can then modify its private version of the row without affecting other transactions. In the meantime, when a much older report-extraction transaction reaches the updated row towards the end of its run, it reads the previous version of the row, which is still available. The older version of the row represents the state of the database at the time the report extraction program was started. At transaction commit time, the update transaction checks whether another transaction updated the same row; if not, it can commit its version and make it available as the latest version. Old and unused versions are automatically removed when they become obsolete. In the event of a conflict, one of the transactions must be re-executed, similar to the scenario in a locking-based scheme. The advantage of this approach is that read and write operations on the tables crucial to the application do not conflict as in a locking-based system. The effect is that OLCP applications can run concurrently and with optimal throughput.
Black-Box Physical Database File. Although some RDBMS products, such as CA-OpenIngres, create a separate disk file per table and per index, Interbase uses the "black box" approach. When you create an Interbase database, it creates a single file on the specified disk location. If you specify multiple disk locations for the database, it creates a single file on each of the disk locations. Thus, instead of seeing a multitude of files, you see only a single file per separate disk location. Interbase transparently organizes the storage of the tables, indexes, and all of the other database objects inside the single database file or files. Unlike other RDBMSs, such as CA-OpenIngres, Oracle, and Sybase SQL Server, with Interbase you do not have any influence over where Interbase places specific database objects.
On the one hand, this approach is very convenient as it does not require much monitoring and database management from a skilled DBA. On the other hand, this frustrates skilled DBAs, because they cannot tune the physical database in any way. For example, as a DBA, you have no control over where Interbase physically places the tables you create. You cannot force Interbase to place two frequently accessed tables on two separate disks and you cannot instruct Interbase to store a table and its indexes on separate physical disks.
Automatic Table Restructuring. Internally, Interbase automatically performs table restructuring and garbage collection. It deletes unused rows and old unused versions of rows, usually when the newest versions of the rows are accessed subsequently. This can have some unexpected side effects. For example, if a big report query is executed a short while after a big update operation is executed, the report query may run much longer than it does usually, as it has to perform the garbage collection of the update operation. This implies that it is difficult to obtain stable performance statistics for your queries -- but it depends on what happened before the query.
This feature, however, together with the server-less architecture and the black box database file, makes Interbase a convenient RDBMS for packaged applications that run on a relational database. The user does have to be concerned with server processes that must be monitored and managed, separate disk locations for different tables, table restructuring to reclaim the space left by deleted rows, or reindexing to rebalance indexes. This is very much in line with the direction many DBMS vendors take to develop DBMSs with self-managing and self-organizing capabilities, as well as support for other monitoring tools and interfaces, such as SNMP.
In my opinion Interbase is the product that best supports the relational data model as defined by Codd, Date, and others. Its extensive use of domains is a relational feature, actually a relational requirement, which other RDBMS products would do well to copy. Although still far from complete, its updatable views and its orthogonal treatment of tables and views in trigger definitions are steps in the right direction toward properly supporting the relational data model.
Interbase has some interesting and unusual features, such as optimistic concurrency control, a server-less architecture, a black box database file, and automatic database restructuring. These features are not only interesting from an academic point of view, but they are convenient and appropriate in some situations (for example, for packaged database applications).
One necessary observation, however, is that Interbase has not changed much lately. Apart from advances in its SQL, internationalization, and ODBC support, Interbase release 4 doesn't seem to have many new major features. The features I have described in this article have been in the product for a number of years. There was a time in Interbase's life when it was technologically streets ahead of the other RDBMS products -- at that stage it was probably too far ahead for the database buyers to comprehend. It seems a pity that it has not kept that technological advantage through the various takeovers and mergers. In addition, it seems to grow more and be used more on the smaller, single-user PC-type platforms than on the midrange-type machines on which it originated. I wonder when we are going to see Interbase develop and advance again. When will it grow in the midrange server market again? Or will it just stay the database industry's best kept "little" secret?