DDF Builder Concepts
This section contains conceptual information about working with DDF Builder. The following sections are included:
•
Terminology Review
•
Security
•
Previous Database and DDF Versions
•
DDF Builder Error Detection and Correction
•
Saving Original and Modified Definitions
•
Table Definition Editor Pages
•
Legacy Nulls in DDF Builder
•
True Nulls in DDF Builder
Terminology Review
Before you begin working with DDF Builder, you should review the terminology used throughout the manual. Some of the terms used are specific to the relational database model, whereas some of the information is from the transactional database methodology. Because DDF Builder is designed to create relational table definitions from transactional data, the utility brings together these two models in one place. This information is useful because terminology used in the transactional model may be referred to differently in the relational model. The following table lists the terms used throughout this manual.
Terms
Definition
Database
A collection of files that includes a set of DDFs with table definitions, a set of data files and a DSN.
File
Table
The Btrieve physical file or SQL table name.
Dictionary
Data Dictionary
System Catalog
System Tables
A set of DDF files that can contain table definitions of the data (metadata).
Table Definition
Entry in the DDFs that corresponds to a Btrieve file.
DDFs
System Tables
System Objects
Files with a .DDF extension. DDFs are system tables that provide the means for defining files within the constraints of a relational database (the metadata).
Key
Index
Specified data used to sort a table or file in a particular order and to optimize searches on particular values.
Security
When using DDF Builder with security-enabled databases, it is recommended that you turn off all security prior to opening the files in DDF Builder. Failure to turn off security results in DDF Builder sending secure login and password requests each time a call is executed. This could become excessive and counterproductive, depending on the number of calls needed to create or modify the table definition.
Previous Database and DDF Versions
DDF Builder is supported on PSQL v9 and later versions. DDFs created with Scalable SQL v4.00 and later are supported along with Btrieve data files v6.x and later.
Scalable SQL v3.xx and Earlier
If you have DDFs that were created with Scalable SQL v3.xx or earlier, you must convert your DDFs before you can open them with DDF Builder.
For step-by-step instructions, see Lesson 1 – Working with v3.00 DDFs.
Btrieve v5.x and Earlier
DDF Builder supports version 6.x files but does not support files created prior to version 6.x. If you have Btrieve data files that were created with Btrieve v5.x or earlier, you must rebuild the files before you can open them with DDF Builder.
For step-by-step instructions, see Lesson 2 – Working with Pre v6.x File Formats.
DDF Builder Error Detection and Correction
When you open an existing table definition associated with a Btrieve file, DDF Builder goes through a set of comparisons and checks. From these comparisons and checks, DDF Builder may make certain adjustments to the table definition, if errors are detected. Any changes made to the table definition are recorded in the Definition Errors window.
*Note: DDF Builder always retains the original table definition, so the changes made by DDF Builder are never automatically saved to your table definition. You may save either the modified or the original table definition with a different name to retain both definitions.
So that you can understand the assessments and potential changes DDF Builder makes, take a moment to look at the steps DDF Builder goes through.
First, DDF Builder reviews the Btrieve file and examines the pertinent details, such as record length, indexes, index segments, and so forth. Next, DDF Builder opens the existing DDFs and looks at the information contained in the DDFs in comparison to the Btrieve file. These two sets of details are then systematically compared to each other for matches and mismatches.
DDF Builder uses the details found in the Btrieve file as the basis for detecting and fixing any errors or problems. The following examples help clarify.
Example 1 - Index Inconsistencies
You have a Btrieve file that contains a key. The table definition does not contain a corresponding index. Since DDF Builder cannot alter your Btrieve file, the key defined in the Btrieve file is carried over to your table definition as an index.
For information on dealing with this type of situation, refer to Lesson 6 – Index Inconsistencies.
Example 2 - Record Length Mismatch
Your Btrieve file contains records defined with a total length of 120 bytes, yet your table definition only defines a total of 100 bytes. Since DDF Builder cannot alter the Btrieve record, a column is added to the table definition for the 20 bytes unaccounted for in the definition.
For information on dealing with this type of situation, refer to Lesson 8 – Record Length Mismatch.
Example 3 - Flag Inconsistencies
The Btrieve file contains a flag on a key that does not match the flags set on the corresponding SQL index in the table definition. Again, because DDF Builder cannot change the Btrieve file, the flag setting on the SQL index in the table definition is changed to reflect the setting in the Btrieve file.
For information on dealing with this type of situation, refer to Lesson 5 – File/Field Flag Inconsistencies.
*Note: Chapter 3, DDF Builder Tutorials, provides step-by-step instructions on dealing with the various situations you may encounter when working with DDF Builder.
DDF Builder cannot repair every problem. For a complete list of the issues that DDF Builder detects and repairs, see Table Definition Errors.
Saving Original and Modified Definitions
Although DDF Builder makes recommended changes to your existing table definitions, you are not required to keep the changes, nor are you required to discard your original table definition if you accept the changes.
You can save both your modified and original table definitions by saving one of the definitions with a different name. If you accept the changes made by DDF Builder, you may save either the modified or the original table definition with a different name.
If you reject the changes made by DDF Builder, your original table definition is retained with the original name.
Table Definition Editor Pages
The Table Definition Editor is made up of several pages of information that you use for viewing, creating, and modifying table definitions. The Table Definition Editor contains the following pages:
•
Table Page
•
Indexes Page
•
Preview Page
•
Statistics Page
•
SQL View Page
Table Page
The Table page is where you do most of the work when creating or modifying table definitions. The Table page includes the raw data view and the grid data view.
Raw Data View
The raw data view provides a look at the data in your Btrieve file in a combined ASCII and Hexadecimal view. Record length, offset and field sizes are also illustrated in this view.
The raw data view also provides you with the visual indicators you may need to determine columns, null indicators, as well as identify unknown fields and bytes.
*Tip: For information on the visual indicators used in the raw data view, refer to Field Attributes in Raw Data View.
Grid Data View
The grid data view is similar in feature and functionality to the grid window view in the SQL Editor of PSQL Control Center.
Fields used in indexes cannot have their size or type modified; only the field name can be modified in the grid data view. To add indexes, drop indexes, or change other index information, you would need to modify the Btrieve file directly using the Btrieve Maintenance Utility.
*Tip: For information on the visual indicators used in the grid data view, refer to Field Attributes in Grid Data View.
Indexes Page
The Indexes page allows you to see the indexes and index segments that DDF Builder detected in the Btrieve file. You may not add or modify any indexes or index segments from this page. Only index name changes are allowed on the Indexes page. If you change an indexed field’s name on the Table page, the name is updated in real-time on the Indexes page.
*Note: For the steps needed to name an index, see Name an Index.
Alternate Collating Sequence (ACS) Files
When working with Btrieve files that use alternate collating sequence (ACS) files, the ACS file must reside in the same directory as the Btrieve file and must have an .ALT extension (for example, UPPER.ALT and LOWER.ALT).
Preview Page
The Preview page provides a look at a file’s data formatted using the current table definition. As you make changes to the table definition, the preview changes accordingly.
The Preview page is read-only; you may not edit any of the information displayed on this page. Although this page is read-only, you may move through the records by using the arrow buttons located at the bottom of the page.
Statistics Page
The Statistics page displays the Btrieve file statistics for any file open in the Table Definition Editor. The information presented on this page would be the same information reported if a statistics report were generated on the file using the Btrieve Maintenance utility.
SQL View Page
The SQL View page displays the underlying SQL statements that apply to the creation or modification of your table definitions. As you modify your table definition, the underlying SQL statements are updated immediately on the SQL View page.
The information on this page is read-only and cannot be altered on this page. You may select and copy the statements to use elsewhere, if needed.
*Caution: Do not reuse any statement that contains a reference to any of the PSQL dictionary system objects. These objects are easily identified by their X$<tablename> and include a proprietary comment prohibiting reuse.
Legacy Nulls in DDF Builder
Versions of PSQL prior to Pervasive.SQL 2000 only provided support for legacy nulls. Files that use legacy nulls are recognized by DDF Builder, but no action is required to deal with this type of null when working with DDF Builder.
True Nulls in DDF Builder
In Pervasive.SQL 2000, support was added for true nulls. With true nulls, a leading byte, known as a null indicator byte, is included to designate the corresponding column as having a null or non-null value
Working with True Nulls
When defining record fields within DDF Builder, it is important to know the fields in the record that are nullable, since marking a section of the record as nullable adds an extra byte to the field. This is because in a Btrieve file, nullable portions of the record are designated as such by the use of the null indicator byte. In the raw data view of the Table Definition Editor, the null indicator byte is indicated in the byte immediately preceding the field or column. When the Null check box is selected, the null indicator byte becomes active and the size of the field is automatically reduced by one byte to accommodate for that byte.
*Note: If you are working with files created prior to Pervasive.SQL 2000, nulls are most likely not an issue.
Creating Nullable Fields
Creating nullable fields in DDF Builder requires that you consider the size of the field and the one byte needed for the null indicator. This means that a nullable field intended to be 25 bytes would actually be defined as 26, reserving one byte for the null indicator. Once you designate a field as nullable, the size is automatically reduced in DDF Builder by one.
*Note: For examples of working with nullable and non-nullable columns, see the examples in Tutorial 1, Create a Nullable Column in the raw data view and Change a Non-Nullable Column to Nullable in the grid data view.