Glossary.

Active tables: a concept applied in IB to system tables. They are active in the sense that a modification on them triggers the logical and physical actions associated with those modifications. For example, adding an index and its segments to the respective system tables causes that pages be allocated and initialized to hold index structures.

Alice, burp, dudley and other utilities: see the document about IB utilities in the bin directory that explains the vast majority of them. Also, gpre and qli are defined below in this page.

Alternate key: this is a unique key that's not chosen as the primary key. Because it can be used instead of the current primary key for its properties, it's referred as alternate.

BDE: it stands for Borland Database Engine. Originally designed as the engine to deal with both Dbase and Paradox, it was extended to serve as middleware to remote connectivity with relational databases through a set of libraries known as Borland SQL Links. Given that, BDE supports both record-oriented commands and SQL syntax. The name BDE is used to refer to the package that consists of the core technology (that includes the IDAPI Infrastructure and the common Query engine) plus the three IDAPI drivers/engines (for Paradox, dBASE and Text formats) plus the ODBC Socket that makes any ODBC driver into an IDAPI driver for BDE applications. It's important to say that because BDE connects to several engines through SQL Links, it cannot recognize or take advantage of every feature in each specific relational engine, so the support for some IB features is limited or it doesn't exist. Also, BDE cannot take advantage of IB version 6, because it has not been updated yet in January 2001.

BLOB: short for Binary Large Object. Interbase invented the BLOB fields several years ago and Boeing took advantage of them to keep recorded sounds in the database. AFAIK, IB offered the first BLOB implementation in the relational market. In IB, BLOBs can have different sub types. Note, however, that the original word BLOB seems to mean nothing and probably comes from a movie.

BLR: it means Binary Language Representation. Within the engine, there is no SQL, no GDML, no QUEL - no relational language designed to be read and written by people. Instead, queries are represented in a binary notation that is a super set of the relational languages we knew. Gpre and QLI both parse SQL and GDML into BLR. When you successfully compile a stored procedure or a trigger, its compiled representation is kept in a BLOB sub type field, in the BLR format. Also, The current DSQL interface to the server translates the queries into BLR. You can use the command-line tool isql to watch the BLR representation of triggers, stored procedures, check constraints, defaults and views if you do the command SET BLOB ALL and then use a select statement to get the appropriate BLR fields from the system tables.

Character set: programming languages usually use no more than two type of character sets, the ASCII and the UNICODE ones. The first set is limited to 256 possibilities (control sequences included) whereas the latter has enough room for 65536 possibilities. The problem is that databases need to store characters avoiding excessive overhead and also, they need to pass them to clients that expect to display the correct image for each stored code representing a character. Not all code tables use the same numeric values for representing the same characters and also, some alphabets are totally different from others or have a bunch of special characters. So, it becomes important to define the character set according to the "code page" that the application will use. Interbase supports a default character set per database and an explicit character set for each char/varchar field. There's no character set available for all the engine. If not specified, the system default is NONE, that means characters are stored as they are and no translation is attempted between the server and the client.

Collation: that's related to the operations on a given character set. A collation is in plain words, a comparison. The collation defines how a SORT instruction orders the results, how the UPPER function works and how fields are compared in the WHERE and HAVING clauses of a SELECT statement.

DDL: it's the part of SQL that deals with the definition of data, so it manages metadata and hence the name Data Definition Language.

DML: it's the part of SQL that deals with the data and hence the name Data Manipulation Language.

DPB: this is the Database Parameter Buffer, a char array used to communicate parameters and their respective values to the engine when using API calls. A DPB includes a first byte with its version and a cluster of bytes for each parameter, where each cluster has one byte for the parameter type, the length of the cluster after the type and the bytes themselves that carry the information, depending on the parameter type.

DSQL: Dynamic SQL. When you read in the new documentation "available in DSQL", it means the instruction is available to programs that submit SQL statements created in run time to the server, with or without parameters. It's the way a typical application made in Delphi or BCB works.

DSRI: Digital Standard Relational Interface. Used by Rdb/VMS, Rdb/ELN, and a couple of DEC interface products.

DYN: is yet another byte encoded language for describing data definition statements. The DSQL subsystem in IB passes parsed DDL statements to a component that emits DYN and then to the Y-Valve that interprets DYN and modify directly system tables that are "active" by design.

ESQL: Embedded SQL. When you read in the new documentation "available in ESQL", it means the instruction is available to applications that have embedded static SQL commands in BLR form. See GPRE for more information.

FIB: short for Free Interbase Components. This is the work of Greg Deatz, the same person who created the FreeUDFLib (Delphi/Windows) and the FreeUDFLibC (C/Sun). FIB allows you to connect directly to IB without the BDE. It's the foundation for IBX and since IBX appeared, FIB is no longer in development.

FIBPlus: Serge Buzadzhy thought FIB was good enough to build a new package on top of it and included a bunch of new features like support for IB6, for example. FIBPlus is available for free.

Foreign key: this is a special non-unique key used to enforce automatically referential integrity. In IB, when you declare a FK, the underlying index is created automatically and it always is in ascending order. Its reference field in the master table must be a PK or UK.

gbak, gdef, gfix, gpre, gsec, gstat, iblockpr and qli: see the document about IB utilities in the bin directory that explains the vast majority of them. Also, gpre and qli are defined below in this page.

GDB: the extension used by convention for Interbase databases. In fact, Interbase can use any extension for a database. This is a remembrance of the days when Interbase Corp. was Groton Database Systems.

GDDL: it stands for Groton Data Definition Language. There was a tool that parsed it and generated calls that defined databases. It's the equivalent of DDL in SQL.

GDML: it stands for Groton Data Manipulation Language. It's a relational language designed to be written and read by people, like SQL. GDML is the original language of Interbase and still it's supported through an utility known as QLI. It's the equivalent of DML in SQL, although it exhibits some capabilities of DDL.

GPre: it's the Interbase Preprocessor. Depending on the platform, it supports different languages to write embedded SQL applications and generates BLR from the SQL instructions. The most common supported language is C but depending on the platform, you can find support for COBOL and ADA. When you read in the new documentation "available in GPRE", it means the instruction is available to programs with static SQL that are preprocessed by GPRE and then passed to the native language compiler (C++, for example) to create the executable.

Hierarchical database: the first design of databases. Each object (except the root itself) must have a parent and only one parent. IBM used this design before 1970.

IBX: short for IB Express. Starting with D5, Borland decided to make two "express" products: ADO Express and IBX. The latter is a package of components that make direct connections to IB using the API and not the BDE. It was built on the foundation of FIB and integrates in the abstract hierarchy of datasets in the VCL.

IBO: short for IB Objects. Jason Wharton created this commercial third party package from scratch to access IB using the API and so bypassing the BDE. IBO is database centric in the sense it tries to follow a C/S model, relying on native IB capabilities to manage data and transactions.

IDAPI: stands for Integrated Database API. It unifies ISAM type data access and Query oriented (SQL or QBE) data access into a consistent cursor model. It appeared in mid 1994 and incorporated some of the work done by the IDAPI technical committee (consisting of Borland, IBM, Novell and Wordperfect). It was known as the BDE version 2 (there was not BDE v1 because it was called ODAPI). The term IDAPI is used to not only refer to the API portion but also to refer to the overall technology and it supercedes the ODAPI term that was used with earlier incarnations of this technology.

Identity key: although not a strict definition, an identity is usually a surrogate key made with an integer field that's filled automatically by the engine and may imply the implicit creation of an underlying index. For example, Paradox and SqlServer both have this kind of special field. IB doesn't support this field directly, but the same result can be achieved with more work but having more flexibility by means of an integer field, a generator and a before insert trigger that calls gen_id to get the next value of the generator. The increment doesn't have to be 1 as in Paradox's automatic identity columns.

ISC: the error messages in Interbase start with ISC and people often speak about ISC. This is no more than Interbase SW Corporation, a semi independent company owned by Borland that finally was absorbed by Borland in 1998. Ultimately, ISC is going to relive with the Open Source project for Interbase.

Isolation level: this is a characteristic of a transaction and it mandates how one transaction must interact with other transactions accessing the same database, in terms of visibility and locking. There are three levels commonly used: Dirty Read, Read Committed and Repeatable Read. The first is the only mode in Paradox and it's supported by a few RDBMS products. The second is the default for almost any relational engine. The third is the default for Interbase and it's done without bringing the engine to its knees (not the case with other engines). In addition, Interbase offers a higher level, known as Repeatable Read with Stability that can be used for special and short transactions. Thanks to the multi version capability, Interbase also allows explicit control over locking management (avoid, wait or give an error) that doesn't have sense on other "classic" engines. See the full description for more information.

ISQL: Interactive SQL utility. When you read in the new documentation "available in ISQL", it means the instruction is available to the user tool to submit commands to the engine and see results of queries.

JRD: the inner parts of Interbase. The kernel owes its name to Jim Starkey, the creator of JRD, that was the basis for Interbase. It stands for Jim's Relational Database, to distinguish it from the parallel (official) development of RDB at DEC.

Key: while the characteristic of a set is it cannot have duplicated elements, sets themselves do not suggest in theory how to avoid duplicated. For relational theory, it's important to be able to identify a record univocally and to give it a distinctive signal. Also, because a set doesn't have an inherent order but ordering is a way to help discarding duplicates, improve performance and group tuples, there are different types of keys: unique, primary, alternate, surrogate, identity, non-unique and foreign are the most known names.

LIBS: it means Local Interbase Server. Back in the time when IB was a closed, commercial product, there was two types of licenses: local (allowing only connections from the same computer) and remote (allowing connections from a remote computer). The versions that came with Delphi and BCB were local and only licensed for development. Since IB6 has no commercial limits of usage nor licenses, LIBS has disappeared. It never was another code, but the same code that imposed connection restrictions based on the licenses that could be purchased and installed.

MGA: it stands for Multi Generational Architecture. You can think of it as another name for the multi versioning engine that enables IB to avoid locking and at the same time, recover quickly in case of failure (server crash, power outage, etc.) without using a transaction log.

Natural scan: when possible and it makes sense, the engine looks for a record using an index when a SQL sentence involves a WHERE or GROUP BY part. When it decides to traverse the table from the first to the last record in storage order, looking for the desired values, it's said to be using a natural scan. Note sometimes the IB optimizer is right when it decides that a natural scan is faster than an index.

Network database: second design of databases after hierarchical ones. Each object can have several pointers to other objects to denote relationships. This creates a mesh of relationships and hence the name "network".

Non-unique key: a key that serves to identify a range of records, because many records can have the same value in that key. It serves for ordering and enforcing referential integrity. In IB, you cannot declare a NUK as part of the table declaration, you must create the underlying index directly instead unless you are defining a FK in which case the NUK is created for you behind the scenes.

Null value: really, it should be named Null state. It's not a value per se; it only denotes that a value was not specified for a field, so the contents of this field are undefined. It's different than zero or an empty (zero length) string, because these are well defined values. Operations against NULL return either NULL or UNKNOWN and it's a source of confusion for newcomers to relational databases, more even that testing for NULL in a query is done with the operator IS NULL but making a field NULL in an update is done by assigning NULL to the desired field.

Object Oriented database: a new type of database that has been in research for a couple of years. It stores really objects internally. It doesn't require normalization. The schema in the db follows the data structures in the program and vice versa. Relationships are represented as pointers. This idea resembles network databases, but with more elaborated concepts, because objects themselves are responsible for some housekeeping tasks. Despite many efforts to raise market share for OO engines, they remained in a niche.

ODAPI: it means Open Database API, it was started in 1990 by Borland and appeared the first time with Quattro Pro for Windows 1.0 in Sept 92. It was the beginning of the BDE project.

ODBC: short for Open Database Connectivity. It's is a call-level interface that allows applications to access data in any database for which there is an ODBC driver. Using ODBC, you can create database applications with access to any database for which your end-user has an ODBC driver. ODBC provides an API that allows your application to be independent of the source database engine. Currently, Interbase v5 has a driver provided by Visigenic and a newer one provided by Intersolv. Sadly, none of them offer complete functionality and both have bugs and are no longer in development. A new group of drivers for IB6 has appeared at the end of year 2000.

ODS: it stands for On-Disk Structure. It's the level of the internal structure of an IB database. For IB4.0, it was 8; for IB4.2, it was 8.2; for IB5.X, it was 9 and for IB6.0 it's 10. See the ODS table for more details.

OLAP: On-Line Analytical Processing. Data volume has grown so large that nobody taking decisions would peruse individual data. Management needs to derive trends, watch an historical perspective, play with what-if cases, etc. For producing those aggregate reports, db engines need crunching power and enough resources to read many MBs almost as it if were a batch process, but returning a result to the interactive client in an acceptable time.

OLE/DB: an standard developed by MS that has decided to include the "OLE" term in several of its technologies. It's a low level specification for accessing both relational and non-relational data. It's supposed to be more efficient than ODBC for relational data. In 2001, it still works only in Windows. ADO is layered on top of OLE/DB.

OLTP: On-Line Transaction Processing. This is the classical requirement a db engine should fulfill: clients doing transactions in real time. Most of the cases involve short transactions with very specific actions, like updating a customer's record. Longer operations are reporting tasks, but they usually don't change data.

Primary key: this is a unique key that's chosen to identify uniquely each record in a table and serve as foreign key in dependant tables. It may be composed or one or more fields. In IB, when you declare a PK, the underlying index is created automatically and it always is in ascending order.

PSQL: When you read in the new documentation "available in PSQL", it means the instruction is available to stored procedures and triggers in the engine. The differences between procedures and triggers are minimal: procedures can't use the special OLD and NEW variables whereas triggers don't have parameters and can't use the EXIT statement. You cannot invoke a trigger explicitly from DSQL or from another trigger or procedure.

QLI: it stands for Query Language Interpreter. It's the interactive data retrieval and manipulation interface to databases managed by Interbase. QLI supports significant subsets of GDML, SQL, and GDEF's data
definition language (DDL).

QUEL: it was an academic relational language which preceded SQL. Quel is much less procedural than SQL. Ingres & the Britten-Lee machine were originally Quel engines. (Definition by Ann Harrison.)

RDB: it's easy to guess it stands for Relational Database. This was the first attempt at DEC to build a database using such design and at the same time, the starting point for Interbase that prefixes its system tables with RDB$ for documentation purposes.

RDB$DB_KEY: usually referred as DB_KEY, it's one of the mysteries in Interbase and give people some untamed power in dynamic SQL as well as inside stored procedures.

Referential integrity: in database theory, a concept that in practice can be explained saying that if A depends upon B and an attempt is made to delete B or change it in a way that some of its changed attributes break the dependency A relies on, either the action should be rejected or A should be updated to be in sync with the changed B or -if B is deleted- A should be made to depend elsewhere or deleted. In an implementation, it's the automatic mechanism in a db engine that allows it to enforce referential integrity (referential constraint rules) as specified by the user among the options detailed in the SQL standard that are recognized and implemented by the particular db engine being used.

Relational database: the design of databases as we see it implemented in products like Interbase, Oracle and Sybase. Backed by a strong theoretical work done by E. F. Codd and Chris Date, the model follows the set concept in mathematics and relationships are represented by "link" attributes. Speaking rigorously, a database schema (structure of the objects) must be "normalized", passing through all "normal forms", namely, 1st, 2nd, 3rd, Boyce-Codd and 5st. However, in practice, a database in third normal form is accepted as normalized. One of the subjects that cause controversy until now is the representation and use of null values.

Special system tables: these are two system tables that fall outside transaction control. Unlike other user and system tables, changes to these two special entities are seen immediately by any user transaction without the need to commit or rollback. They are rdb$formats and rdb$pages. You can read Language Reference to see what they contain. Compilers and database engines are typical cases of code that relies on its own metadata to describe data and metadata, but the cycle should be broken at some point or they would fall in a chicken and egg trap.

SQL: the standard language to talk to relational engines. Short for Structured Query Language, it's a declarative language, because conversely to procedural languages like C and Pascal, SQL specifies the things that must be done by the engine in terms of the expected results but not how it must do them. However, there's added functionality to take some control over how things are accessed in the engine through the explicit use of plans. You can trace its origins to the work of IBM in the 1960 decade.

[Available in] SQL, DSQL and isql: if you read IB manuals, you can see "Available in SQL, DSQL, and isql." when the commands are explained. SQL means Embedded SQL, namely, IB commands that you write in a host language -C in this case- and pass it to the preprocessor (GPRE) to generate a source C file to use in your application. These are static SQL commands. DSQL means Dynamic SQL, namely, the SQL commands you can create and submit to IB in real time, they don't need to be compiled before the application is ran. Lastly, isql means Interactive SQL and it refers to the tools available to work with IB writing command and watching results. There are two native isql tools: the command line isql.exe and in Windows, the graphical wisql.exe that exists up to version 5.6. Update: in the new documentation for IB6, the names will be ESQL, DSQL, PSQL and isql.

SQL Links: The BDE can be augmented with optional native IDAPI SQL drivers that provide transparent and connectivity that bypass ODBC to widely used SQL Servers. For example, native SQL Drivers are available for Interbase, Oracle, Sybase, MsSql and Informix servers. These SQL drivers are called SQL Links because they link the BDE to remote database engines.

Surrogate key: when a field or combination of fields cannot be made unique for each record, a contrived or "artificial" key must be used. Usually this is a random generated value (like GUIDs) or a monotonically ascendant value. It's used also by people who think that PKs should be based on generated fields that aren't part of the natural attributes of the data being modeled.

Sweeping: in Interbase, the process that collects and frees older and unneeded versions of each record in one database when a threshold (known as Sweeping Interval) is reached. This is due to the multi versioning engine and doesn't happen on other commercial relational databases. Sweeping can be invoked explicitly through the Interbase utilities, too. It's a garbage collection process applied to every table in a database.

System tables: relational engines are self contained. It means data about the structure of user tables is kept in tables, too. These tables that keep data-about-data (metadata or db schema) are created automatically and are called system tables. They keep information about themselves, too, so it's like trying to trace who was first: the egg or the hen. By convention, system tables and all their fields (columns) are prefixed with RDB$. However, what really distinguishes system objects is a flag that's kept in a field in the system tables that maintain information about the different objects (tables, procedures, generators, etc.) recognized by Interbase. Be sure to avoid mental loops when reading this.

Transaction: a logical unit of work, comprising one or many commands sent to the database engine. A transaction is an atomic action: it's either finished completely or it is abandoned completely and all its changes are discarded.

Transaction log: a typical relational database (and some OO databases) uses a separated file where it keeps the history of transactions. When a crash happens, the engine reads that file on startup and determines the changes the need to be confirmed and the changes that need to be undone. IB doesn't use such facility, because if a crash happens, the MGA will discard internally the record versions that pertain to uncommitted transactions, the next time the database is accessed.

Transaction zero: all user transactions only can see committed changes or at most, report an error if the newest version of a record has been created by another txn but not committed yet. However, the system runs the txn zero that's always in pre-committed state, so it can see all changes, whether pending or confirmed and all record versions. This is necessary, for example, to enforce referential integrity and to maintain indexes (because indexes keep track of all versions of all fields that they span).

UDF: short for User Defined Function. IB has only a few built-in functions that are SQL standard. For extensible functionality, it allows the developer to write functions callable from the engine that can be used like built-in ones. There's already a FreeUDFLib that serves as a demonstration as well as provides bunch of very useful and commonly requested functions.

Unique key: a value that identifies each record (tuple) in a table and distinguishes it from other records. Therefore, one value of a unique key is used only by one record. The simplest type of unique key is a field whose values cannot be repeated, like an employee identity inside a company. Often, one single field doesn't suffice for creating unique values, so a combination of fields must be used. If this still doesn't suffice, then a surrogate key is used. In IB, when you declare an UK, the underlying index is created automatically and it always is in ascending order.

Y-Valve: Internally InterBase has several possible "engines", and when attaching to a database it must decide which engine it is to use. The logic for deciding which engine to use is called the Y-valve. (Steve Tendon's definition.) Among other things, the y-valve should decide whether to use a direct access to the db (local database) or to connect as a client to a remote IB server (in IB classic) and what server version to use for reading a given ODS (for the cases where the same IB is able to read different ODS versions).

This page was last updated on 2001-02-16 00:29:54