Introduction to Databases

Tom Rettig

Rettig Micro Corporation,

The Visual FoxPro database implementation is very powerful and brings Visual FoxPro into the mainstream relational world, both in client-server and LAN capabilities. Visual FoxPro is not just a “real” database, it’s a solid generation ahead of the competition.

A database is a collection of tables and descriptions. Tables in a Visual FoxPro database are individual .DBF files. The database describes the tables’ properties, rules, triggers, relations, views, connections, and stored procedures. The Visual FoxPro database container is implemented in an ordinary .DBF file but with a .DBC extension.

The database container does not contain a full description of the table that allows you to recreate the table and its indexes from the database. Rather, it contains additional information beyond the tables’ structure and indexes. The database requires that the tables and indexes exist when they are added to the database. From the Visual FoxPro viewpoint, the .DBF file describes (and is) the table’s structure, and the .CDX file describes (and is) the index tag structures. Think of the database as an extension and container rather than a metadata description.

The database must be open when any of its tables are, and Visual FoxPro opens the database automatically when any of its tables are used. Thus, the database always controls and protects the tables regardless of how the tables are accessed.

You can open more than one database, and you select the current one with the database ComboBox on the Standard toolbar or with SET DATABASE TO.

Database Designer

The Database Designer displays a database’s “schema” as a visual representation. A schema is a collection of all the database’s information about the objects it contains. Everything you can do programmatically with a database, you can do visually in the Database Designer.

In the Database Designer, you can create, add, modify, and remove tables, relations, views, connections, rules, triggers, and stored procedures from the toolbar, menu, or by right clicking on the Designer window or one of its objects. From here, you can also access the “RI Builder” to specify referential integrity rules.

Stored Procedures

All code used by the database should be “encapsulated” in the database as stored procedures. This assures that all the required code is always compiled and in memory whenever a table is accessed, even from outside any application. Code used by the database includes user defined functions in rules, triggers, and index expressions.

Stored procedures should have no external dependencies. Although they can access external PUBLIC or PRIVATE (in scope) variables, they should not do so and should be fully functional by themselves. Think of them as your database’s sole guardian.

Language Extensions

Tables

Tables are “free” when they are created without a selected open database, otherwise they are automatically added to the database.

Tables can belong to only one database. The table header stores a “backlink” to the database to which it belongs in the form of a relative path. When you open a database table and the database is not already open, Visual FoxPro will open it automatically. Forcing the database to be open assures that all the rules and triggers are properly checked regardless of how the table is accessed.

Tables in a database can have alias names up to 128 characters long.

The number of work areas is 32,767.

Table Designer

When the table belongs to a database, the Table Designer lets you set field and row data validation rules, field default values, and event triggers. Everything you can do programmatically with a table, you can do visually in the Table Designer.

Language Extensions

CREATE/ALTER TABLE Options

Clause	Action
DEFAULT1	Assign a default value to a field when a new record is added
NULL | NOT NULL	Can a field accept null values?
CHECK lValid [ERROR cText]	Data validation rule and error message, both row and field level
PRIMARY KEY	Create/remove the table’s primary key tag
FOREIGN KEY	Create/remove a foreign key tag
REFERENCES	Specify the foreign key table for a persistent relation
NOVALIDATE	Allow this change when current data is in violation of it

¹DEFAULT can be any Visual FoxPro expression and may be, or include, a user-defined function.

New Data Types

Data types and null support are available in both free and database tables.

DateTime

The DateTime data type uses 14 bytes and presents the data in the format, Date + Time. The actual format depends on the settings of SET DATE, SET CENTURY, SET MARK, SET HOURS, SET SECONDS, the settings of the International options in the Windows Control Panel, and the settings of the Regional option in the Visual FoxPro Options dialog. Only whole seconds are stored, not fractions as returned by the SECONDS() function.

Currency

The currency data type uses 8 bytes and stores all values with four decimal places.

Double

The double data type uses 8 bytes and is primarily for compatibility with other databases. It also offers greater precision and less storage space over numeric and float types. No conversion is needed among these three types.

Picture

The picture data type is for backward compatibility with FoxBASE Mac only. Use General with OLE instead.

Binary

Character and memo fields have an additional “binary” type that prevents these fields from being code-page translated. They are the field-level versions of SET NOCPTRANS, a command that is not necessary anymore. You can select Character(binary) or Memo(binary) in the Table Designer or specify field-level NOCPTRANS in CREATE/ALTER TABLE.

Null Value

The null value is not a data type. Any data type may contain a null value except a table’s field that is part of a primary, candidate, or compound index tag expression.

Think of null as meaning, “I don’t know what value this is” or “no value has been assigned.” If a value is unknown or not yet assigned, it obviously cannot be compared to other real values of the same data type without the result being, “I don’t know what this result is.” The same logic applies to why a null value is invalid as a unique identifier in a primary, candidate, or compound key tag.

EMPTY() and ISBLANK() always return false when testing a null value. Null is neither equal to nor not equal to a data value and must always be tested with ISNULL() or NVL().

STORE "Some String" TO cChar1, cChar2
cChar2 = .NULL.
? TYPE("cChar1"), TYPE("cChar2")  && both character type
lResult = (cChar1 = cChar2)
? TYPE("lResult"), lResult     && logical type contains .NULL.
? ISNULL(cChar2), ISNULL(lResult) && both true
? ISBLANK(cChar2) EMPTY(cChar2)  && both false
? NVL(cChar2, cChar1)       && cChar1 value of "Some String"

Indexes

In Visual FoxPro, indexes are more than just ways of ordering and accessing data. They are the relational keys that implement persistent relations between tables in a database. This is a very powerful new technique.

Index tags can be designated as a “candidate” key and, if the table belongs to a database, a “primary” key. You set persistent relations from one table’s primary or candidate tag to another table’s tag, which is the “foreign” key.

Table Designer

When the table belongs to a database, the Table Designer lets you set one index tag to be the primary key.

Language Extensions

¹UNIQUE means candidate in SQL terminology. Do not confuse this with the UNIQUE keyword in the INDEX command and the Table Designer. See Unique below.

Candidate

Candidate keys are so named because they are “candidates” to become the primary key. They are also called “alternate keys” in relational theory.

Candidate keys cannot contain nulls or duplicate data.

Both free and database tables can have candidate key tags, and each table can have more than one.

Candidate keys are ideal for preventing duplicate entries and eliminate the need to write duplicate checking routines in row-level data validation rules.

Primary

A primary key tag has the same rules as candidate except that the table must belong to a database, and each table can have only one primary key tag. Primary tags are assigned with CREATE/ALTER TABLE or the Table Designer and are not available from the INDEX command.

Unique

This is the old style index that takes just the first occurrence but does not prevent duplicate entries as candidate and primary tags do. This is included in the Table Designer and INDEX command only for backward compatibility and should not be used. Above all, do not confuse the name with truly unique tags like primary and candidate. Note that the SQL commands, CREATE/ALTER TABLE, use UNIQUE to mean candidate.

Filtered

All index tags can be filtered on a logical condition. For example, if you filter your primary key tag on NOT DELETED(), your deleted records can contain duplicate primary key values that would otherwise be forbidden. This is valuable for maintaining “reusable” records instead of having to periodically PACK the table.

If you want Rushmore to always be optimized, you must also provide an unfiltered tag on the same expression as the filtered tag.

Deleted

You can add a tag on DELETED() to speed up access when using the DELETED() condition in local SQL commands. The existence of this tag enables Rushmore optimization on the expression.

SELECT * FROM MyTable WHERE NOT DELETED()
LOCATE FOR cMyPrimaryExp = " " AND DELETED() && reusable record

Relations

The two types of relations are “persistent” and “temporal.”

Temporal relations are temporary, the same as SET RELATION TO. They establish a runtime link between two tables and may or may not correspond to existing persistent relations. See Use in Forms below. The rest of this section discusses persistent relations.

Persistent relations are set from one table’s primary or candidate tag to another table’s tag, which is the “foreign” key. Both tables must belong to the same database. Persistent relations are used to enforce relational integrity rules. Persistent relations can be “self-referential,” which means that they are set from one tag to another in the same table.

One table can be involved with several relations into several tables, but rarely will it be involved in more than one relation with each table.

Persistent relations can exist only between base tables in the database and cannot be used with view cursors.

Relations are one-to-one if the foreign key tag is primary or candidate, otherwise they are one-to-many. In many-to-many relations, each table must set a one-to-many relation into another “junction” table. This table creates the many-to-many relation between its participant tables.

Edit Relationship Dialog

This dialog appears when you create or modify a persistent relation in the Database Designer.

Language Extensions

Relational Integrity

Relational integrity comprises both “entity” and “referential” rules. Entity rules affect which data is allowed in the table, and are enforced through Visual FoxPro Rules. Referential rules affect related tables and are enforced through Visual FoxPro Triggers. Use the RI Builder to specify referential integrity rules.

See Rules and Triggers below.

Relational Key Link

There is much debate over whether the relational key link should be internally generated or not. Relational theory states that the primary key should be “meaningful” and thereby describe the database’s schema. To prevent difficulties with compound key expressions and non-unique data, unique identifying numbers can be assigned. These would include keys like Vendor number, Invoice number, and Part number.

As a practical matter, any part of the primary key that has meaning can change and duplicate entries can result from entry errors, mergers, and fraud. Although assigning a unique internal number on which to link relations can’t eliminate the possibility of duplicate entries, it does reduce maintenance by not having to cascade eventual corrections through all persistent relations.

Whether to use meaningful or assigned numbers to link relations is a design decision we must all make. This developer prefers internally assigned numbers, but Visual FoxPro will let you do it any way you choose.

Rules

Rules can be set for each field and for an entire record. A rule is a logical expression, which if false, prevents the field or row change from occurring and displays the validation text. The rule may be implemented as a function in stored procedures that returns a logical value.

Tables must be in a database container to use rules. If the rule expression is a function, store it in the database’s stored procedures so it is always available regardless of how the table is accessed.

Use rules to enforce “entity” or “data” integrity (what is allowed in the table) at the field or row level.

Use NOT ISNULL() checking to require a value in fields that accept nulls or NOT EMPTY() in fields that do not. In either case, you must provide a default value so the rule isn’t violated when a new record is added.

You can use the Update trigger to allow invalid values to be inserted, e.g., a blank record added without defaults, and not be tested until the record is committed.

To prevent duplicates, use a candidate index tag instead of data validation rules.

Modifying anything in the table from a rule is not allowed and causes an “Illegal recursion” error. Rules simply tell any source that tries to change something whether or not the change is valid. If you have a form, it can check rule violations in its Error event and control its behavior accordingly. Whatever you use to access the table (Forms, BROWSE, SQL, ODBC), it’s up to that access device to behave according to the messages it receives from the database’s rules.

Field rules must not conflict with record rules, and neither may conflict with triggers or defaults.

Field rules fire before record rules, and record rules fire before triggers. If buffering is used (see Buffering below), both field and record rules fire on the buffered record, but triggers don’t fire until you commit the change.

Use AERROR() in your form’s Error event to respond to rule errors with specific messages and behavior.

See the example in Transactions below.

Table Designer

When the table belongs to a database, the Table Designer lets you set field and row data validation rules and text.

Language Extensions

Field-Level Rules

Field rules are activated when the field value changes. This can occur when you add or change a record in the language or when you move off a changed field in a form or browse. The rule is not checked if no changes are made.

Record-Level Rules

Record rules are activated when the record pointer moves off the record or attempt to commit the changes with END TRANSACTION or TABLEUPDATE(). The record pointer moves in a form, browse, or from language commands like COUNT, INSERT, LOCATE, and SKIP. The rule is not checked if no changes are made.

Make sure your rule functions do not move the record pointer in their attempt to validate the data. Use SQL SELECT to retrieve information without moving the record pointer.

Triggers

Triggers are events that occur on an Insert, Update, or Delete attempt. You can store a different trigger expression for each event. Insert triggers fire when a record is added or a deleted record is recalled. Delete triggers fire when a record is deleted. Update triggers fire when a record is changed. Like record-level rules, triggers activate when you move off the record or attempt to commit the changes with END TRANSACTION or TABLEUPDATE().

Trigger expressions are generally functions in stored procedures that test the current attempt and return a logical true if okay or false if not. True allows the event to take place, and false prevents it and fires the error “Trigger failed.” To show a different message, trap this error in your error handler.

Tables must be in a database container to use triggers. Store function code in the database’s stored procedures so it is always available regardless of how the table is accessed.

Use triggers to enforce “referential” integrity (how the event affects or is affected by related (referenced) tables). Referential rules are properties of a persistent relation, and are set with the RI Builder.

Triggers must not conflict with field and record rules or field defaults.

As with Rules, modifying anything in the table from a trigger is not allowed and causes an “Illegal recursion” error.

Both field and record rules fire before triggers. If buffering is used (see Buffering below), both field and record rules fire on the buffered record, but triggers don’t fire until you commit the change.

Use AERROR() in your form’s Error event to respond to trigger errors with specific messages and behavior.

See the example in Transactions below.

Table Designer

When the table belongs to a database, the Table Designer lets you set Insert, Update, and Delete trigger expressions.

Language Extensions

Buffering

Visual FoxPro has two kinds of buffering, row and table. Use row buffering when you deal with one record at a time, and use table buffering when you deal with multiple records. Buffering eliminates the need to store field values to temporary variables or arrays for editing.

Buffering works with both free and database tables as well as cursors.

Buffering is most effective when used within a transaction (see below), but it can also be used alone.

Language Extensions

Buffered Locking

The two kinds of buffered locking are optimistic and pessimistic.

Optimistic locking lets you edit all the buffered records and doesn’t attempt to lock them until you commit the changes. CURVAL() and OLDVAL() work only when optimistic buffering is in effect and are used to detect whether another user changed anything that you did.

Pessimistic locking locks the records at the time they are buffered and retains the locks until you commit or roll back your changes.

TABLEUPDATE() releases automatic record locks. Explicit locks made with LOCK() are not released.

Transactions

Tables must be in a database container to use transactions.

Transactions can be nested five levels deep.

All changes made after a BEGIN TRANSACTION are not “committed” to the table until an END TRANSACTION is executed. All changes made during a transaction are discarded if a ROLLBACK is executed instead of END TRANSACTION.

ROLLBACK and END TRANSACTION can occur from anywhere in the application, such as in an error routine. Unlike all other Visual FoxPro “structured” commands, they do not have to appear in the same program, procedure, or class method. ROLLBACK and END TRANSACTION always terminate the most recently issued BEGIN TRANSACTION, that is, the current transaction level and release all automatic locks but not manual locks.

If the system crashes or the power goes down during an incomplete transaction, it is the same as issuing ROLLBACK, that is, no changes will appear in the tables involved in the transaction.

Transactions are very useful in a client-server environment because they can roll back the local cursor if there is an error updating the server.

Language Extensions

Example

ON ERROR DO MyErrorHandler && or call from a form's Error method
BEGIN TRANSACTION
	INSERT INTO MyTable (cField) VALUES ("Whatever")
	BEGIN TRANSACTION
		INSERT INTO AnotherTable (cField) VALUES (MyTable.cField)
		REPLACE AnotherTable.nNumber WITH 123
	END TRANSACTION
	REPLACE MyTable.nNumber WITH AnotherTable.nNumber
END TRANSACTION
ON ERROR
PROCEDURE MyErrorHandler
	LOCAL laError[1], llRollback
	=AERROR(laError)
	DO CASE
		CASE INLIST(laError[1], 1581, 1582, 1884)
			* 1581: Field doesn't accept NULL
			* 1582: Field rule violated
			* 1884: Unique index violation
			llRollback = .T.
			=MESSAGEBOX(laError[2]+" "+laError[3]) && include field or tag name
		CASE INLIST(laError[1], 1583, 1585, 1700)
			* 1583: Record rule violated
			* 1585: Record changed by another
			* 1700: Record in use by another
			llRollback = .T.
			=MESSAGEBOX(laError[2])
		CASE laError[1] == 1539  && Trigger failed
			llRollback = .T.
			DO CASE
				CASE laError[5] == 1
					=MESSAGEBOX("Insert "+laError[2])
				CASE laError[5] == 2
					=MESSAGEBOX("Update "+laError[2])
				CASE laError[5] == 3
					=MESSAGEBOX("Delete "+laError[2])
			ENDCASE
	ENDCASE
	IF llRollback
		DO WHILE TXNLEVEL() > 0
			ROLLBACK  && all
		ENDDO
	ENDIF
ENDPROC

Connections

Connections are a bridge between the Visual FoxPro database and an external ODBC database, and are used by Views (see below) to retrieve a local cursor for manipulation. Views can be updatable, which means that any changes made to the local cursor will be written to the connected table(s).

The external database can be any database for which an ODBC driver is registered.

Connection Designer

The Connection Designer lets you set a connection’s properties including the connected data source, user ID, password, timeout intervals, and type of execution and processing. Everything you can do programmatically with a connection, you can do visually in the Connection Designer.

Language Extensions

¹ Use the Connection Designer or keywords DATASOURCE, USERID, PASSWORD, and CONNSTRING

Execution

Connection execution can be “synchronous” or “asynchronous.” A synchronous connection completes execution before returning control to Visual FoxPro. An asynchronous connection returns control periodically during execution so you have the opportunity to display a progress thermometer or cancel execution that takes too long.

Views

Views are cursors or “virtual tables” that exist in the database by definition only. When you open a view, the cursor is created by retrieving the data in its definition with SQL.

Views can be updatable, which means that any changes made to the local cursor will be written to the view’s table(s).

Views can be set between two tables in the same database or between one table in a database and the other either in another database or a free table. The view itself must belong to a Visual FoxPro database.

Local

A local view is a view of local Visual FoxPro tables, which may be free or belong to a local database. Local views use the local SQL commands, SELECT, INSERT, UPDATE, and DELETE.

Remote

A remote view is a view of tables in a non-FoxPro database that is accessed through an ODBC connection. The external database can be any database for which an ODBC driver is registered. Remote views use SQL pass-through functions (see below).

View Designer

The View Designer lets you set a view’s properties including the tables, fields, order, group, and selection and update criteria. The SQL available in the View Designer is a subset of Visual FoxPro’s SQL language, so although you can do quite a lot from the designer, you can actually do more with the language.

Language Extensions

SQL Pass-Through Functions

Command/Function	Action
SQLCONNECT()	Establish an ODBC connection to a remote data source
SQLSTRINGCONNECT()	Establish an ODBC connection using a connection string
SQLGETPROP()	Get current or default settings of connection, data source, or attached table
SQLSETPROP()	Set current or default settings of connection, data source, or attached table
SQLEXEC() 1	Send a SQL statement to be processed by the remote data source
SQLMORERESULTS() 1	Get another result set if more are available
SQLCANCEL()	Cancel an executing SQL statement
SQLROLLBACK()	Cancel any changes made during the current transaction
SQLCOMMIT()	Commit a transaction
SQLTABLES() 1	Get a data source’s table names into a cursor
SQLCOLUMNS() 1	Get a remote table’s column details into a cursor
SQLDISCONNECT()	Terminate an ODBC connection to a remote data source

¹The four SQL pass-through functions that can operate asynchronously (see Connections for a description) are SQLEXEC(), SQLMORERESULTS(), SQLTABLES(), and SQLCOLUMNS().

Use in Forms

Opening tables and cursors in a form is achieved automatically by “binding” a data source to the form or its controls.

You bind data to forms in the DataEnvironment property, which you access from the toolbar, menu, or by right clicking on the Form Designer window or one of its objects. The DataEnvironment can contain both tables and views, and opens them when the form is run. Any relations set here are temporary or “temporal” relations set at runtime with SET RELATION TO.

You bind data to controls in their source properties, ControlSource, RowSource, RowSourceType, ChildOrder, and LinkMaster.

Multiple session forms give you an unlimited number of work areas for any form because each instance of the form uses its own copy (“data session”) of the work areas populated by the DataEnvironment property.

Extending the Database Container

You can add fields to the DBC, but you cannot add records. You can add index tags, but not to the .DCX production file. If you add an index tag, a .CDX file is created which you must remember to explicitly open whenever you USE the DBC. Because Visual FoxPro updates the DBC, you must always check to make sure that your extensions, however implemented, are current with any Visual FoxPro updates.

Visual FoxPro only changes the DBC objects Database, Table, and Connection. Any changes made to Field, Index, and View objects, either visually or programmatically, cause existing records to be marked DELETED() and new records added to replace them. For example, when you rename one field, all that table’s field entries in the DBC are deleted and new entries are written for each field, even the ones that did not change. When the database container is packed from the Database menu or with PACK DATABASE, or if you USE it and PACK, all the ObjectID entries are renumbered to match the record number and all the ParentID numbers are renumbered to match their parent’s new ObjectID.

If you extend the DBC by adding fields, you may risk that your extended information will be lost without warning when the database is packed. At the time of this writing, the issue is unresolved. A better method is to use a separate file linked by a unique ID in the User field, which is always copied over when changes occur.

Structure

¹ A table’s file path is a property of the table and is stored as a relative path to the DBC location.

² The User field should be left for the user’s custom use if any. Third party add-on products that extend the DBC should either add fields using the prefix U_, as in U_TRO, or keep extensions in a separate file. A separate file may be preferable due to the way Visual FoxPro handles the DBC.

Index Tags

Object Types, Parents, and Locations

The main Database object is always record number one and has itself as its parent. Other Database objects include TransactionLog, StoredProceduresSource, and StoredProceduresObject, which occupy records 2 through 4 and all have the main Database object (record 1) as their parent.

Tables, Views, and Connections all have the main Database object (record 1) as their parent and are always located after the Database object records.

Indexes have Tables as their parent and are always located after their parent record.

Fields have both Tables and Views as their parent(s) and are always located after their parent record.

DBC Access

Because the Database Container is an ordinary Visual FoxPro table, you can access it directly. Although you should not need to do this since you can get all its information from language extensions, it’s useful to see how the structure and tags facilitate access.

The ObjectID is always the same as the object’s record number, so you can quickly access any object’s parent object with GO nRecordNumber.

	USE MyData.dbc AGAIN IN SELECT(1) ALIAS Parent
USE MyData.dbc AGAIN IN SELECT(1) ALIAS Child ORDER ObjectName
SELECT Child
SCAN
	GO Child.ParentID IN Parent
	? Child.ObjectType, Child.ObjectName
	? "  Parent:", Parent.ObjectType, Parent.ObjectName
ENDSCAN
  
To access children of a parent, use the indexes.
  
USE MyData.dbc AGAIN IN SELECT(1) ALIAS Child ORDER ObjectType
USE MyData.dbc AGAIN IN SELECT(1) ALIAS Parent ORDER ObjectName
SELECT Parent
SCAN
	IF SEEK(STR(Parent.ObjectID), "Child") && assumes SET EXACT OFF
		? Parent.ObjectType, Parent.ObjectName
		DO WHILE Parent.ObjectID == Child.ParentID
			? "  Child:", Child.ObjectType, Child.ObjectName
			SKIP IN Child
		ENDDO
	ENDIF
ENDSCAN

Extended Database Container Class Library

The DevCon source code diskette contains a class library, EdcLib.prg, that you can use to add your own extensions to the DBC.

EdcLib uses a single extended file with separate memo fields for everybody’s individual extensions. This prevents DBC bloat, allows the user to access a different extension with each instance, and allows everybody to access each other’s extensions.

Methods create the extended file and individual extension fields, set and retrieve extended properties and unique identifiers, validate the EDC to the DBC, and analyze the DBC Property field’s data.

All extended properties are fully user-defined, can be assigned to any DBC object, and their values can be any data type except Memo, General, and Picture.

All names, delimiters, and limits are properties that can be changed by subclassing.

All messages are properties that can be subclassed for other languages.

This class library is public domain, subject to a warranty disclaimer, and may be used freely by anyone in any way without obligation to the author other than indemnification according to the disclaimer.

Conclusion

Way cool! There’s nothing you can’t do with Visual FoxPro’s database implementation.

Developers who have never worked with a “real” database may find some difficulty adjusting to relational constraints. For more information on relational theory and implementation, I highly recommend An Introduction to Database Systems by C. J. Date from Addison Wesley.