ADS Supported Table Types

The Advantage Database Server supports three different table formats: Clipper and FoxPro DBF formats (compatible with dBASE III + format), and a proprietary ADT format. The two DBF formats are useful for developers who want to maintain backward compatibility with legacy applications, particularly those still running in DOS. In this book, the ADS-supported formats are referred to collectively as ADS tables.

The ADT format, on the other hand, is the preferred ADS format for ADS-based applications that no longer need to maintain backward compatibility. This is because the ADT format provides superior features, flexibility, and performance.

While there are still some legacy Clipper and FoxPro applications out there that can benefit from the stability and performance offered by ADS, most developers using ADS should use the ADT format.

DBF Formats

DBF format databases employ tables in DBF format. These tables use indexes in either FoxPro IDX or CDX formats, or the Clipper NTX format. Similarly, memo fields employ the FPT (FoxPro) and DBT (Clipper) memo field files. Because the index and memo files are associated with either FoxPro or Clipper, which format you use depends on the Advantage driver you are employing. For example, you use the Advantage NTX driver to create and work with NTX indexes. By comparison, you use the Advantage CDX driver to use IDX and CDX indexes.

Both the FoxPro and Clipper formats have well-known characteristics and limitations. For example, the maximum field name length is 10 characters, deleted records are merely marked for deletion and can still be accessed up until a table is packed, unique indexes do not enforce record uniqueness, and memo files can include large amounts of wasted space.

In short, except for legacy application support, the use of the DBF file formats is discouraged. In fact, if you are building a new application with no need for backward compatibility with existing Clipper or FoxPro applications, you are much better off using the ADS proprietary format. For more information on Clipper and FoxPro DBF formats, including their characteristics, refer to the ADS help or a third-party book on Clipper or FoxPro.

The Advantage Proprietary (ADT) Format

As noted in the preceding section, the traditional DBF format has well-documented limitations, making its use less attractive than many of the alternatives available today. In response to these limitations, Extended Systems introduced a new, proprietary format in 1998. This format is referred to as the ADT format.

Tables of the ADT format employ the .adt file extension, with the associated memo file and structural index having the same filename, but with the .adm and .adi file extensions, respectively. Among the enhancements introduced with the ADT format are long field names, superior memo file space utilization, improved index options, and more flexible table structures. Also, whereas FoxPro and Clipper field names are limited to alpha characters, 0–9, and underscore (_), ADS table field names can include all but null values, semicolons, and commas.

Table 2-1 lists some of the basic ADT table specifications. For more information, refer to the Advantage documentation, which is available on the CD-ROM that accompanies this book.

Table 2-1: Characteristics of the ADT Table Format

Table Characteristic	Specification
Maximum field name length	128 characters.
Maximum table size	4 gigabytes (16 exabytes using Windows NT/2000/XP/2003 NTFS or NetWare NSS file systems; 8 exabytes using Linux).
Maximum number of records per table	2.2 billion.
Maximum record length	64 kilobytes.
Maximum record filter expression length	64 kilobytes.
Maximum index file size	4 gigabytes (35 terabytes in Windows NT/2000/XP/2003 NTFS, NetWare NSS file systems, or Linux).
Maximum memo file size	4 gigabytes (4 terabytes in Windows NT/2000/XP/2003 NTFS, NetWare NSS file systems, or Linux).
Maximum number of fields per record	Dependent on the average field name length. For example, if the average field name length is 10 characters, you can have 3256 fields. If the average field name is 40 characters, you can have 1302 fields per table.

Free Tables Versus Database Tables

There is a second major distinction between the various ADS table types, but this is not related to table format. Instead, it is associated with how ADS interacts with the tables. There are two data access mechanisms. Tables can either be accessed directly, or they can be accessed through a data dictionary. When tables are accessed directly, they are referred to as free tables. Tables accessed using a data dictionary are referred to as database tables.

Free tables provide far fewer features than database tables. Because ADS accesses free tables directly, the only features available with free tables are those associated with the tables, indexes, and memo files. Security is provided by the free table’s encryption (if encrypted), and validation is provided for by the table’s structure, that is, the data types of the columns of the table. If any other security or validation features are desired, you must program those manually into your client application.

Database tables are those ADS tables that are bound to a data dictionary. By binding a table to a data dictionary, the properties defined in the data dictionary are used by ADS when you access the table, which is how some of ADS’s more advanced features are implemented. For example, a data dictionary permits you to define constraints, which further define what data can be posted to a record. For example, you can use a constraint to prevent a negative number from being posted to an integer field. With a free table, by comparison, if you want to prevent a negative number from being posted to an integer field, you must add code to your client application.

When an ADT table is bound to a data dictionary, additional information is written into its header, and that information prevents the ADT from being accessed directly. This restriction ensures that the properties you set for the data dictionary to which the table is bound are respected.

Unlike an ADT database table, which can only be accessed through the data dictionary to which it is bound, there is no way to attach custom information to a DBF format table that would prevent ADS from accessing the database table directly. As a result, even though a DBF table may be bound to a data dictionary, ADS as well as legacy applications can continue to access that table directly. This can compromise the integrity of your data since data dictionary definitions will not necessarily be enforced. Here again is yet another argument in favor of using ADT tables over DBF format tables.

The remainder of this chapter discusses how to create ADT tables. These operations apply to both free tables and database tables, unless noted. Creating a data dictionary, and binding ADT tables to it, is discussed in detail in Chapter 4.