Have you ever wondered "what are all these files in my directory and what do they do?"  There are thirteen different file extensions used for files created by Alpha.  If you have a file to save customer information called customers, you may have up to six files with the file name customers in the directory with six different file extensions.  What is being stored in these files and are they really needed?

A related area is terminology.  What is the difference between a table and a database?  Some people use the terms interchangeably, but in Alpha Five they mean very different things.  And what are these things called "data dictionaries"? The Alpha Five User's Manual gives some information on these issues, including a definition of terms and a general description of how a relational database works.  We will expand on that information and take a more detailed look "under the hood" at each structural element.  Some of the following may seem fairly basic to experienced users, but a little review is always helpful.

The basic building block of any data storage method is what is called a table.  It is exactly what its name implies, just a table made of columns and rows that contains information or data.  Each column represents one type of data and is called a field.  Each row represents a single record.  Alpha uses the file extension .dbf to identify the main table file, which is actually a fairly standard file structure originally used in Dbase and Fox Pro.  For instance, if we create a table to store customer information and want to name it customers, the main table file will be named customers.dbf.  The field names created during design such as firstname and lastname will become the column titles in the table.  Each column will also have a specific format such as character, numeric, and date, which limits the type of information that can be stored.

Unfortunately, this basic dbf file structure does have some built in limitations.  The extra files created by Alpha in the directory are designed to overcome these restrictions.

Each column or field must be a fixed length, which is established during design.  The widest column allowed is 255 characters.  Sometimes we have information that contains more than 255 characters and does not fit into a fixed width column.   The basic dbf table has records in no particular order.  A new record is just added to the next row at the bottom of the table.   Field names can only be 10 characters long and contain no spaces.  Yet, the Alpha User's Guide states a field name can be 32 characters long.  How this trick is achieved will be discussed a little later.   The only way to actually view the data is to look at it in the original table format.   This format is what is called the browse view.  This is not always desired.

To beat the column width limitation, a memo field type is used.  A memo field takes alphanumeric data of any length and is limited only by available disk space.   Since the basic dbf structure will not accept a non-fixed length field, a new file is created, the .fpt file.  This file does not have a table structure like the dbf but stores the information in a more freeform manner called "blob" data with each new memo added after existing memos.  A 10-character field in the dbf file is created which contains only information to access a location in .fpt file.  This location in the .fpt file contains all of the data in the actual memo that is connected to the record in the table.  If our customers table has a memo field defined, we will have a file called customers.fpt. If there are no memo fields defined, there will not be a .fpt file for the table.   The dbf and fpt are linked and therefore must be kept together.  Any changes made to the dbf file will effect the pointer to the memo in the fpt file.   They really should be considered two parts of a single table structure.  When data is backed up, these are the files that must be saved.

Normally, we want to put our records in some sort of rational order.  We may want to sort the data in a number of ways in our customer data file, such as by last name or zip code or city.  Indexes are created to create lists in the order desired.  Alpha Five creates index files with a .cdx extension.  If any indexes are defined for our customer table, there will be a file created named customers.cdx.  One of the interesting limitations of the cdx file format is that, like field names, index names can only be 10 characters long and contain no spaces.  Once again, Alpha will permit the user to actually store a name of more than 10 characters though a little bit of trickery.

Sometimes there is confusion about what is contained in the index file.  First, each index definition is stored, including the index expression and any filters.   Then, the index expression is evaluated for each record and a list is created to put the result in the index order.  A list is created and stored for each index defined.  For example, if an index is built to sort our customers by last name, there will be a list of last names in the order we defined.  Each entry in the list has a pointer to identify the original record.  When we search by index, Alpha finds the correct entry in the index file and then opens the corresponding record in the dbf file.  The table .cdx file is normally included in a data backup, if only to save the index definitions.

When an index is rebuilt, all of these index lists are recreated from the saved definitions.  Therefore, it is not really required to save the .cdx file as part of a data backup, but it is more convenient.  If the .dbf and .fpt file data only is restored, the existing .cdx file indexes will have lists built from the old data and will be incorrect.  Updating the indexes will build new lists from the restored data, which may take a little time.

Part 2 - Data Dictionaries

Our customer table also has three more files called customers.ddd, customers.ddm, customers.ddx.   These are part of what is called the table data dictionary.   The use of data dictionaries is the means Alpha uses to expand beyond the limitations of the basic dbf format.  The user guide states that the data dictionary "Stores layouts, field rules, ranges, queries, and saved utility settings".  What does this really mean?  Let's take a look at the specific purpose of each file.

First, a list of all of the added elements must be saved.  Elements include form designs, browses, letters, reports, saved operations, field rules, and even the fact that long names are saved for the fields and indexes.  This is how Alpha can "trick" the basic dbf format to use the long names and have complex field rules.   This information is saved in the .ddd file which is actually a table in dbf format.  The "fields" include the name of each added element, a four character type code, a character field, and two memo fields.  Since a dbf format does not handle a memo directly, the data dictionary has a corresponding memo file, the .ddm.   The .ddm file is in the same format as the .fpt file.  There is a single record for each element.  If we created a form called Customer Info, we will find a record in the ddd file with Customer Info in the name field, FRMI in the type field to indicate that the entry is a form, and the actual code for the design elements in a memo field.  Other design elements will have different type codes.  For example, each field rule will be listed with a type of FRUL and the name of the field.   The field rule code is found in the .ddm memo file.

If long names are used, there will be a record entry to indicate that fact, along with a conversion "chart" in the record's memo field.  You may have named an index last name for customer.  The actual name saved in the .cdx file would be either the first 10 letters or the first 9 letters plus a number, such as last_name1.  The memo in the record for long names contains an entry last_name1= last name for customer.  When you look at the index definitions, you only see the converted long name.  The same method is used to save and convert long field names.

Just like the dbf and fpt files are linked and part of a single structure, the same applies to the .ddd and .ddm files since the actual code for the form designs, field rules, and so on are saved in the memo .ddm file.  The .ddx file is an index file for the data dictionary.  Just like a user uses an index to find a specific record, Alpha uses the ddx to find a specific form name or field rule or whatever is needed.  The .ddx file must be kept with the .ddd and .ddm files so Alpha can find all these elements and their pieces.

Part 3 - Sets and the Database

When a set is designed to link two or more tables together, three more new files types are created.  Perhaps we are working in a sales system and we have linked our customer table to a table named sales that has the customer sales records.  This linked set may be named cust_sales.  The three files created will be cust_sales.set, cust_sales.sem, and cust_sales.sex.  A set does not contain any actual information, just a means to link existing tables.  In this case, the files created are just another use of the data dictionary concept.  The .set file is actually a dbf table, which contains a list of all forms, etc. attached to the set as well as a record entry for each linking definition.  The actual forms, reports, and other objects attached to the set have their design information and code saved in memo format in the .sem file, which is in the same format as the table .fpt file and the table data dictionary .ddm file.  The.sex file, as you might imagine by now, is not as exciting as its name, but just an index to tell the program where to find everything.  These three files should always be kept together.

Now that we have described a table and established what most of the files contain, what is defined as the actual database?  The database can be thought of as a container that holds all of the tables, sets, and other objects that must fit together to create a working application.  A table does not stand alone in Alpha Five, but is attached to a database.  Our customers and sales tables, as well as our cust_sales set may be attached to a database named sales.  When we create this database, four new files are made; sales.adb, sales.alb, sales.alm, and sales.alx.  The .adb file is a type of table that contains a list of all of tables attached to the database and their related file names as well as the directory path to each file.  It is not saved in the dbf format.  The other three files are referred to as the database data dictionary files.

Some objects can be used by more than one table.  These include custom toolbars and menus, global user defined functions, and global scripts.  These are saved in the database data dictionary files so they can be used by any table attached to the database.  The database data dictionaries have the same structure as the data dictionaries for the tables.  The list of all objects is stored in a table in dbf format in the .alb file.  A record is made for each object with fields for the object name and type as well as memo fields to save the actual code for the object.  For example, a custom menu called Customer_Form would have that name saved in the name field and MENU in the type field.  The type code for a toolbar is TLBR, the type for script is SCRP, and so on.  Alpha also makes some entries automatically to save global colors and global variable names.  The actual script code, menu definitions, etc. are saved in memo format in the .alm file.  The .alx file is the index used by Alpha to find all of these objects when needed.  Again, these data dictionary files can be thought of as three parts of a single structure and should always be kept together.

You may not need or want to know how each file fits into a complete Alpha database structure.  But this knowledge can be very useful when you are trying to determine what files must be sent to a user after a change has been made.