Upsizing Paradox Databases to InterBase

by James Arias-La Rheir

Introduction

Moving from a desktop database to a full-featured, SQL database server has caused many DBA's to reconsider that job at the local five and dime. But I'm hear to tell you that it's not all that bad! With forward planning and building on your database experience, you can move a relatively complex desktop database to the league of SQL database servers.

InterBase brings intuitive, easy to use database structure and management to the table for all to take advantage of. The Paradox table structure allows for a reasonably logical medium to maintain data in a localized application. This complimentary combination makes the data migration an attractive option over scraping the old and starting anew. This presentation will cover issues such as:

  • The real reasons for upsizing
  • Using already-built tools to migrate the data
  • Mapping Paradox table properties to InterBase database features
  • Paradox features that make SQL database access easier
  • Keeping your original Paradox front-end application alive
  • How will the client application communicate to the database server?

Culture Shock!

Changing your Paradox perspective of what the InterBase database might look like is the first hurdle. Fortunately, a good portion of your current database application design still functions perfectly in the InterBase implementation. The client/server paradigm that InterBase employs entails new concepts that the Paradox developer can (or will learn to) appreciate.

First, the database’s structure is the most obvious difference between InterBase and Paradox databases. InterBase databases usually consist of one file (for example, employee.gdb) that contain definitions and values for tables, indexes, data validation, triggers, procedures and so on. Paradox database definitions are distributed amongst several files, such as indexes (employee.xg0), validity checks (employee.val) and BLOBs (employee.mb), which are all linked to a particular table (employee.db).

The next significant difference is the separation of data and code. In Paradox, there is the concept of one or more working directories where the data files (Paradox tables), reports, forms and scripts reside. These reside on either a workstation or dedicated file server. Given the client/server nature of InterBase, only the tables themselves are located on the server machine; all of the other application files reside on the client. Since you can’t specify a remote server’s directory as a Paradox working directory, connecting to an InterBase database with a Paradox application requires that you precede the InterBase table name with a Borland Database Engine (BDE) alias.

There is also the concept of business rules (or sometimes called integrity constraints). Paradox and InterBase use similar business rules, although through slightly different implementations (see Phase 3: Build in Business Rules later in this presentation). Each DBMS supports referential integrity, primary keys, required fields and default field values. InterBase has the added functionality of triggers and stored procedures that move the bulk of the data manipulation code off of the client application (as with Paradox) and to the server’s database.

Lastly, there is the concept of live data. Paradox tables support a pessimistic locking scheme, which forces applications to obtain a record lock before any changes can be attempted (hence the need for a pdoxusrs.net file). InterBase supports an optimistic locking scheme, which enables clients to start changing a record – from a snapshot of the current state of the database - without locking it. This snapshot controls the locking of the data through a transaction stamp, which is managed by the Multi-Generational Architecture of InterBase.

Reasons for Upsizing

Using the InterBase database server provides several advantages over desktop databases:

  • You may develop in any software package that can use ODBC drivers or make direct InterBase API calls.
  • In all cases, there are increased limits (or no limit at all) on database and record size, number of columns per table, number of concurrent connections, etc.
  • The locking mechanism is more sophisticated; say goodbye to pdoxusrs.net problems.
  • There are several platforms and operating systems versions available as well as portability across those platforms.
  • Transactional processing, which provides support for rollback and commit operations, is managed using disk-based writing to provide a more stable environment. Paradox performs all read and write operations in memory in conjunction with the pdoxusrs.net file. When a situation occurs, such as a power outage or system crash, which abnormally interrupts Paradox, data loss or corruption may result.
  • The Multi-Generational Architecture of InterBase supports a record versioning scheme that reduces data contention. Paradox realizes only one instance of a record and manages it through a lock file (pdoxusrs.lck).
  • InterBase reliably manages up to 200+ concurrent user-connections.
  • Some database structure changes (such as adding and dropping columns) can be made on the fly; Paradox requires exclusive access to the tables.
  • InterBase requires little additional DBA experience. As with application design, your current database knowledge is still valid and easily expands to include the concepts of a database server.
  • All of this and InterBase maintains a small footprint (10MB required for server install, 2MB required for the client’s driver and libraries).

Migrating Paradox Tables to an InterBase Database

No two database software packages are the same, although the core database design principles still apply such as table relations, use of indexes, etc. Table 1 lists each property of a Paradox table and its effect in the InterBase scheme.

Table 1. How Paradox Table Properties Migrate
Table Property Migration Issues
Data Types1 Most Paradox datatypes map well to InterBase datatypes. Those that do not transparently translate into InterBase are Auto-increment, separate Time & Date, Logical and BCD.
Picture Statements Using check constraints can simulate table-based picture statements; otherwise, the picture statement can be moved to the data field on the Paradox form.
Table Lookups CHECK constraints can be used to make a column’s entered value conform to another table’s column value.
Referential Integrity By using a combination of CHECK constraints and foreign key designations, you can achieve the same functionality as Paradox referential integrity.
Validity Checks CHECK constraints can be used to specify requirements of a newly entered value.
Keys1 Keys are called PRIMARY KEYs in InterBase and InterBase does not allow a record to be posted where its primary key value is NULL.
Secondary Indexes1 InterBase indexes have the added option of being made inactive before doing large inserts to improve performance. Then the index can be re-activated to rebuild the index. InterBase does not support case-insensitive indexes, but you can use collation sequences to achieve similar results.
Password Security InterBase maintains a security database that is separate from the actual database. A user is given rights to the database using standard SQL GRANT statements (or can be restricted by the REVOKE command). Also, passwords are not ‘additive’ as in Paradox.
Table Language In addition to setting the language (character set) for a particular table, InterBase also enables you to specify a single language for the entire database (default) or for a specific column within a table. You can also specify various character orders (COLLATION SEQUENCE).
[1](1, 2, 3) These properties are automatically transferred when using the Paradox Copy Utility or Data Pump migration techniques described below.

As you’ve seen so far, depending on the complexity of the tables, migrating Paradox tables to an InterBase database may involve considerable work. For example, you will need to learn some basic SQL for database structure (metadata) changes. Database Explorer is a tool that comes with C++ Builder and Delphi. It performs most functions – see Figure 1. The Paradox Restructure utility can’t modify InterBase metadata, but the Paradox SQL File tool enables you to do such modifications. Fortunately, you can leave most of the front-end application’s design intact. You can still use the Paradox Data Model to link tables together in forms and reports, and you can leave ObjectPAL code mostly unchanged (at a minimum).

Phased-Migration Approach

A common upsizing technique used by IT departments is that of a phased implementation strategy in which the steps required for the upsize project are performed and tested incrementally. The goal is to migrate the database and application as seamlessly as possible. Following is a suggested phased-migration strategy. You will want to thoroughly test each aspect of the migration before moving onto the next.

Phase 1: Moving the Data

Of paramount importance to any database is its data and that is why the utmost care should be given to its new home. There are three ways to migrate your Paradox tables into an InterBase database, ranked from easiest to most difficult (please see the appropriate documentation for details):

  • The Paradox Copy utility: The old familiar Tools | Utilities | Copy utility that translates Paradox datatypes and some table properties to the corresponding InterBase datatypes and properties. An InterBase database must first exist and the aliases must be configured in the BDE.
  • Data Pump: The Data Migration Wizard that comes with Delphi and C++Builder that moves data and column structure between different heterogeneous formats. An InterBase database must first exist and the aliases must be configured in the BDE.
  • External Files: InterBase allows you to query/import data from external, fixed-length files to which most Paradox can easily export. BLOB data will need to be programmatically extracted to a separate file. This is a data-only migration, therefore table structure information is not translated.

Phase 2: Build in Security

As Table 1 above eludes to, InterBase and Paradox invoke two, distinctively different security techniques. The InterBase security database, ISC4.GDB, manages all aspects of how users access the server's databases. For starters, even a first-time user/owner of a database must log in to the InterBase server before accessing it in any fashion. Albeit most first-time InterBase developers create databases using the default username/password -- documented publicly -- for testing purposes, suffice it to say that it is wise to change at least the password portion before the database goes into production. Note that InterBase passwords can be applied to any database on a specific server; they are not database-dependent. They are also encrypted so the exact password string is never sent over the network. Privileges are made by using the gsec.exe command-line tool included with InterBase or by issuing SQL GRANT and REVOKE commands through a SQL interface. Paradox, on the other hand, maintains the encrypted password within each table file (*.db). This means that there is no centralized security database and multiple passwords (possibly the same) need to be maintained.

One approach of migrating the Paradox security to InterBase is to use a privilege comparison sheet as a guide (see Table 2). Given the added layers of security that InterBase offers (ROLES, stored procedures, triggers, views, roles, etc.), it may be a good idea to re-evaluate the Paradox security layout to better fit into the InterBase scheme.

Table 2. Privilege Comparison2
Paradox InterBase Notes
Master Password Administrator/Owner Password When the database is first created, the creator owns it and only they have privileges to it.
Auxiliary Password Any other password Not equivalent to Administrator
Table Rights | All GRANT ALL Composite of INSERT, DELETE, SELECT, UPDATE, EXECUTE and REFERENCES
Table Rights | Insert & Delete

GRANT INSERT

GRANT DELETE

 These are two separate privileges in InterBase
Table Rights | Data Entry

GRANT SELECT +

GRANT INSERT +

GRANT UPDATE

 Given the higher granularity of InterBase security, use all three privileges.
Table Rights | Update GRANT UPDATE  
Table Rights | Read Only GRANT SELECT  

Field Rights | All

Field Rights | Read Only

Field Rights | None

GRANT UPDATE (<column>)

or

GRANT REFERNCES (<column>)

 Column-level privileges are best obtained through views or data blocking/hiding within the client application.
N/A GRANT EXECUTE Required to execute a stored procedure.
N/A GRANT REFERENCES Required to access columns of a foreign key.
N/A GRANT ROLE Used to grant privileges to a group of users.
[2]For Paradox tables, privileges are set for each table; InterBase maintains one centralized security database.

Another method of restricting access to a table is through views; a virtual table based on one or more underlying tables that yield a subset of columns and data. Furthermore, views can either be updateable or read-only.

Phase 3: Build in Business Rules

What really makes a SQL database server shine is its ability to manage the business rules (data manipulation code and validity constraints) that are transparent to the client. Except for those table properties listed in Table 1, a Paradox database requires the developer to implement the bulk of the data manipulation routines within the Paradox client application. Putting these data manipulation routines on the client makes for an application that needs to be recompiled every time a rule or constraint is changed or added. Then the client application will need to be re-deployed, taking care to manage its various versions. Business rules coded into the client application will require additional 'hits' against the database, thereby reducing overall performance. Table 3 explains those InterBase features that put the rules and constraints on the database thereby creating a lean, mean client machine!

Table 3. InterBase Features to Thin Out the Client
Feature Explanation
Character Sets A character set defines the symbols that can be entered as text in a column. A character set can be defined on the database as a whole, a column of a table or when the client connection is established. A collation order specifies how the characters are sorted and ordered. InterBase provides collation orders to support Paradox.
Check Constraints A check constraint can be part of a column definition and is used to enforce that a value inserted or updated falls within a specified range or is within a list of values. The enforced condition can also be based on data in the same row or in a different table.
Computed Columns A computed column is one whose value is calculated each time the column is accessed at runtime. The resulting datatype is calculated if one is not provided. Paradox accomplishes this through 'calculated fields' within each form or report.
Domains A domain serves as a column template when creating tables that use a similar column (for example, "ID"). Some of the inherited properties of a domain can be overridden by a local column definition.
Exceptions An exception is a named error message that is raised from within a stored procedure or a trigger. When an exception is raised, the error message is returned and the procedure or trigger is terminated.
Generators A generator is a global mechanism that creates a unique, sequential number. The uniqueness of a generator is based on the name of the generator, not on a table as with Paradox, so you can use multiple generators per table. In conjunction with a trigger, a generator can be used to insert a unique value for a row when the row is about to be inserted or updated. The Paradox datatype autoincrement is similar to the generator, except that a Paradox table can only use one autoincrement-generated value; additional unique values would have to be generated through code in the client application.
Referential Integrity InterBase referential integrity works similar to that feature in Paradox: foreign keys are defined to ensure that rows in the referencing table have corresponding rows in the referenced table. One key difference between the two implementations is that InterBase referential integrity supports cascading deletes; in the Paradox case it would have to be coded into the client application. InterBase referential integrity can also be restricted to just updates or deletes and the value can be a default or NULL.
Security As explained in Phase 2 above, InterBase privileges are established using a combination of SQL GRANT and REVOKE commands. Privileges can be placed on a table, view , a column of a table or view, or on a stored procedure. InterBase security is pessimistic: only an object's creator (owner) has access to it and the owner must GRANT privileges to others before they can access the object.
Stored Procedures A stored procedure is a program written in the InterBase stored procedure and trigger language (includes SQL commands, control loops and error handling routines) that is global to the database. It can be called directly from the client application. Stored procedures have several advantages: Performance (executes on the server), Easier Maintenance (updates are reflected throughout), Modular Design (shared amongst applications).
Triggers A trigger is a routine, also written in the InterBase stored procedure and trigger language, associated with a table or view that automatically performs an action when a row is inserted, updated or deleted.
User-Defined Functions A user-defined function (UDF) is a program written in a language like C, Delphi or Perl for performing customized, often-used tasks. UDFs are commonly used to perform actions that may be more efficient or not possible using the InterBase stored procedure and trigger language. UDFs are called through the client application or through a SQL command against the database and reside on the database server.
Views A view specifies a subset of columns and data from which a client application can query against. Paradox simulates this through client-side filtering and/or reporting. Views can be based on one or more tables, or another view and can be made read-only. Views are not copies of the data.

Because of the SQL foundation InterBase maintains, it is also a good idea to familiarize yourself with SQL and the InterBase extensions to it. There are several good books on SQL; just be sure to purchase one that covers SQL92, as InterBase is fully SQL92 entry level compliant. See the Resources section for a couple of book recommendations.

Phase 4: Build in a Maintenance System

Here's how it typically goes.. The database is running like a champ. Dozens of users are committing transactions left and right. People are smiling. You, as the DBA, are loving life and are about to close up shop early and head out to the mountains. And then.. "Aaahhh, my system crashed!!" No worries. You've followed the steps in this section to a "T" and have everything under control. Well, in case you didn't, let's go through the process of ensuring the lifespan of your database.

The most important, and seemingly least implemented, safeguard is backing up the database. Paradox DBAs would typically copy the Paradox tables to another location, only after making sure everyone is 'off of the system'. This would be done at night or at some other idle period. InterBase provides a backup utility -- commonly referred to as "GBAK" -- that performs a transactional-state copy of the database to a (by default) compressed file. GBAK'ing a database can be done on a 'live' database, because it itself is a transaction. GBAK'ing a database is done by either using the command-line tool gbak.exe or through the Windows Server Manager program. You can GBAK to either your local machine or to a remote server. All that is required is that InterBase Server be running on your local machine or the remote server.

After backing up, the next critical task for database management is database validation. Paradox DBA's made use of the Table Repair Utility. Validating an InterBase database's integrity is done by either using the command-line tool gfix.exe or through the Windows Server Manager.

Another safeguard that some InterBase DBAs employ is the use of shadows. A database shadow is an identical physical copy of the database, at a specific transactional-state. The InterBase Server engine duplicates the main database's transactions automatically; this is not a separate process. If the database should fail, simply renaming the shadow database is all that is required to recover. You create a shadow by issuing a CREATE SHADOW command in Windows ISQL (a SQL utility that comes with InterBase).

The last safeguard that you may want to employ is a scheduling routine. You can write an application to schedule a backup or database validation that 'fires' at certain times and parse the output for errors. You can also tell your maintenance application to parse the interbase.log file, which logs critical errors that the server encounters. An easy way to schedule these check routines is to write a batch file and use the Windows NT AT command or the UNIX cron command. Connecting to an InterBase Database

Another advantage InterBase has is the multitude of ways the client application has to communicate with the database server. Following is a brief list:

  • Use a BDE alias through an ODBC driver. You will get the ODBC driver with the InterBase Server.
  • Use a BDE alias through an SQL Links driver. You will get the SQL Links driver with one of the Borland Professional or Client/Server suites.
  • Use a programming language, like C++ Builder to make InterBase API calls. For a web server-based connection, you can use the InterClient driver (an all-Java, thin-client JDBC driver); there are InterBase ‘hooks’ to simplify development. This makes for a very fast connection.
  • Use a programming language to make ODBC driver API calls, which will in turn make InterBase API calls.

The most commonly used connection method is a BDE alias through an ODBC driver. When creating a BDE alias through an ODBC driver to establish a connection, there are only a few parameters of concern:

Database Name:
The full path and file name of the InterBase database (for example, C:INTERBASEXAMPLESEMPLOYEE.GDB). Note that the ‘Path Name’ that was previously used for Paradox tables must be blank for InterBase databases.
User Name:
The login name for the database (for employee.gdb, it would be ‘sysdba’)
ODBC DSN:
The name of the ODBC data source to which this alias will connect (for example, IB_Visigenic (InterBase 4.x)). In BDE Administrator, you can select from a drop-down list.

Of course, the BDE will need to know about the OBDC driver, which you can do by creating a BDE driver alias that references the OBDC driver (please refer to the online help for additional information). To test the connection, run Paradox, select File | Open | Table and choose the alias just created. You will be prompted for a password and, presuming no errors have occurred, you are connected to the database and will be presented with a list of tables.

Migration Checklist

Following is a recommended list of items that someone migrating Paradox tables to an InterBase database may consider. Furthermore, each database application will have special attributes that will require additional checks. The items below are ordered in regards to implementation.

  • Is the column name, type and length correct? Is a primary key specified?
  • Are all indexes accountable and 'in use'? You can test this by turning on the SHOW PLAN option in the InterBase Windows InterBase SQL tool and running the following query: select * from <table_name> where <index_column>=<some_value>
  • Can the database's Administrator user connect? Can a typical user connect? How about a user through a ROLE (if applicable)? Can all of the preceding users execute relevant procedures and make required (meta)data changes?
  • Are referential integrity relationships working? n Are check constraints working?
  • Is the character set/collation sequence for the database, table and column in place?
  • Do triggers fire at appropriate events?
  • Do stored procedures return the correct results and/or work properly? Does the client application handle a failure gracefully?
  • Does the client interface (GUI) make sense to the new [InterBase] paradigm? For example, does an "endEdit" command equate to a database commit?
  • Does the client application's code handle server-generated errors well?
  • Can you backup and restore the database without errors?
  • Can you validate (GFIX) the database without errors?
  • Does your maintenance routine account for all scenarios?
  • During the test cycle, were there any anomalies in the interbase.log file?
  • TEST: WORKS UNDER LOAD ON TEST SYSTEM
  • TEST: WORKS ON OTHER PLATFORMS (IF APPLICABLE)
  • TEST: WORKS WITH DIFFERENT NET PROTOCOLS (IF APPLICABLE)