Chapter 6 -- Creating Databases

After you ve designed your database, you can build the database through the interface or with the language. You might want to add existing tables to your database, and then modify them to take advantage of the data dictionary features in Visual FoxPro. If you are working within a project in the Project Manager, you can add the tables as you create them.

For more information about creating a database for a multiple-user environment, see Chapter 17, Programming for Shared Access.

This chapter discusses:

• Creating a Database
• Viewing and Modifying Database Architecture
• Managing a Database
• Referencing Multiple Databases
• Handling Database Errors

Creating a Database

When you create a database, you gather tables together into one collection and gain the benefit of data dictionary features.

A data dictionary gives you greater flexibility in designing and modifying the database, and frees you from having to write code to create field-level and row-level validation or to ensure the uniqueness of values within primary key fields. The Visual FoxPro data dictionary enables you to create or specify:

• Primary and candidate keys.
• Persistent relationships between database tables.
• Long names for tables and fields.
• Captions on fields that display in Browse windows and Grid columns as headers.
• Default values on fields.
• The default control class used in forms.
• Input masks and display formats for fields.
• Field-level rules and record-level rules.
• Triggers.
• Stored procedures.
• Connections to remote data sources.
• Local and remote views.
• Comments for each field, table, and database.

Some data dictionary features, such as long field names, primary and candidate keys, default values, field-level and record-level rules, and triggers, are stored in the .dbc file but are created as part of the process of building a table or view. For information about these features, see Chapter 7, Working with Tables, and Chapter 8, Creating Views.

Collecting Tables into a Database

To collect tables into a database, you need to create a database container to hold all of the objects such as views, connections, and stored procedures associated with the tables that make up your database.

To create a new database

• In the Project Manager, select the Data tab, then select Databases from the list and choose New.

  -or-

• Use the CREATE DATABASE command.

For example, the following code creates and exclusively opens a new database called Sample:

CREATE DATABASE Sample 

When you create a new database, it is empty, containing no associated tables or other objects. Adding a table creates links between the table file and the database container. The link information stored in the database about a table is a forward link. The link information stored in the table about the database container is the backlink.

Links specify the associations between a database container and tables.

You can use the following commands and functions to work with a database and its objects programmatically.

Commands and functions that manipulate databases and database objects

ADATABASES( )	CREATE VIEW	MODIFY CONNECTION
ADBOBJECTS( )	DBC( )	MODIFY DATABASE
ADD TABLE	DBGETPROP( )	MODIFY PROCEDURE
ALTER TABLE	DBSETPROP( )	MODIFY STRUCTURE
APPEND PROCEDURES	DELETE CONNECTION	MODIFY VIEW
CLOSE DATABASE	DELETE DATABASE	OPEN DATABASE
COPY PROCEDURES	DELETE VIEW	PACK DATABASE
CREATE CONNECTION	DISPLAY DATABASE	RENAME TABLE
CREATE DATABASE	DROP TABLE	REMOVE TABLE
CREATE SQL VIEW	INDBC( )	SET DATABASE
CREATE TABLE	LIST DATABASE	VALIDATE DATABASE

Adding Tables to a Database

Each Visual FoxPro table can exist in one of two states: either as a free table, which is a .dbf file that is not associated with any database, or as a database table, which is a .dbf file that is associated with a database. Tables associated with a database can have properties that tables outside a database do not have, such as field-level and record-level rules, triggers, and persistent relationships.

You associate tables with a database by creating them within an open database, or by adding existing tables to a database. For information about creating new tables, see Chapter 7, Working with Tables.

To add a free table to a database

• In the Project Manager, select Tables from the All tab or the Data tab, then choose Add.

  -or-

• Use the ADD TABLE command.

For example, the following code opens the testdata database and adds the orditems table:

OPEN DATABASE testdata ADD TABLE orditems 

You must explicitly add an existing free table to a database to make it a part of a database. Modifying the structure of a free table does not cause Visual FoxPro to add the free table to a database, even if a database is open when you issue the MODIFY STRUCTURE command.

Using Free Tables

You can associate a given table with only one database. However, you can use the data in an existing .dbf file without incorporating it into your database.

To access a table in another database

• Create a view in your database that references the table.

  -or-

• Access the table with the USE command and the ! symbol.

Use the ! symbol to refer to a table in a database other than the current database. For example, if you want to browse the orditems table in the testdata database, you can type:

USE testdata!orditems BROWSE 

In the previous example, the testdata database is opened automatically for you when you issue the USE command, but Visual FoxPro does not set testdata as the current database. A database opened automatically, as in the previous example, is automatically closed when the table is closed, unless you open the database explicitly before closing the table.

For information about using a view to access information outside your database, see Chapter 8, Creating Views.

Removing a Table from a Database

When you add a table to a database, Visual FoxPro modifies the table file s header record to document the path and file name for the database that now owns the table. This path and file name information is called a backlink, because it links the table back to the owning database. The process of removing a table from a database not only removes the table and associated data dictionary information from the database file, but also updates the backlink information to reflect the table s new status as a free table.

You can remove a table from a database through the interface or with the REMOVE TABLE command. As you remove the table from the database, you can also choose to physically delete the table file from the disk.

To remove a table from a database

• In the Project Manager, select the table name, then choose Remove.

  -or-

• From the Database Designer, select the table and choose Remove from the Database menu.

  -or-

• Use the REMOVE TABLE command.

For example, the following code opens the testdata database and removes the orditems table:

OPEN DATABASE testdata REMOVE TABLE orditems 

Removing a table from a database does not automatically delete the table file. If you want to both remove the table from the database and delete the table s .dbf file from the disk, use the DELETE clause of the REMOVE TABLE command or the DROP TABLE command. For example, the following code opens the testdata database and deletes the orditems table from disk:

OPEN DATABASE testdata REMOVE TABLE orditems DELETE 

The following code also opens the testdata database, then deletes the orditems table without moving a copy to the Windows Recycle bin:

OPEN DATABASE testdata DROP TABLE orditems NORECYCLE 

Updating Table and Database Links

If you move database files (.dbc, .dct, and .dcx), or a table associated with a database, the relative paths change and might break the backlinks and forward links that Visual FoxPro uses to associate database and table files:

• The backlink links the table back to the table s owning database. It consists of the relative path and file name for the .dbc file associated with the table, and is stored in the header of the Visual FoxPro table file (.dbf).
• The forward link tells the database which tables belong to it. Forward links are stored in the database file (.dbc), and consist of the relative path and file name for each associated table file.

You can reestablish links and update the relative path information to reflect the new file location.

To update links after moving a table or a database

• Use the RECOVER clause of the VALIDATE DATABASE command.

For example, the following code opens the testdata database and displays dialog boxes that allow you to locate tables that are not in the locations contained in the database:

OPEN DATABASE testdata VALIDATE DATABASE RECOVER 

Tip   If you want to use a table without taking time to reestablish the links for all tables in the database, you can open the table with the USE command. Visual FoxPro displays the Open dialog box to allow you to locate the owning database or delete the links.

For information on removing the backlink from a table whose owning database has been deleted accidentally from disk, see FREE TABLE.

Creating Persistent Relationships

You can create persistent relationships between tables in a database. Persistent relationships are relationships between database tables that are stored in the database file and are:

• Automatically used as default join conditions in the Query and View Designers.
• Displayed in the Database Designer as lines relating table indexes.
• Displayed in the Data Environment Designer as default relationships for forms and reports.
• Used to store referential integrity information.

Unlike temporary relationships created with the SET RELATION command, persistent relationships do not need to be re-created each time you use tables. However, because persistent relationships do not control the relationship between record pointers in tables, you will use both temporary SET RELATION relationships and persistent relationships when developing Visual FoxPro applications.

In Visual FoxPro, you use indexes to establish persistent relationships between tables in a database. You create a persistent relationship between indexes rather than between fields, which enables you to relate tables based on a simple or a complex index expression.

To create a persistent relationship between tables

• In the Database Designer, choose the index name you want to relate, then drag it to the index name of the related table.

  -or-

• Use the FOREIGN KEY clause with the CREATE TABLE or ALTER TABLE commands.

For example, the following command adds a one-to-many persistent relationship between the customer and orders table, based on the primary cust_id key in the customer table, and a new foreign key, cust_id, in the orders table:

ALTER TABLE orders;    ADD FOREIGN KEY cust_id TAG ;       cust_id REFERENCES customer 

If you were to then examine the database schema in the Database Designer, you would see a line joining orders and customer, representing the new persistent relationship.

Indexes provide the basis for persistent relationships

The type of index tag or key determines the type of persistent relationship you can create. You must use a primary or candidate index tag or key for the one side of a one-to-many relationship; for the many side, you must use a regular index tag or key. For more information on index types and creating indexes, see Chapter 7, Working with Tables.

To delete a persistent relationship between tables

1. In the Database Designer, click the relationship line between the two tables.

   The width of the relationship line increases to indicate that you have selected the relationship.

2. Press the DELETE key

   -or-

   Use the DROP FOREIGN KEY clause with the ALTER TABLE command.

For example, the following command deletes a persistent relationship between the customer and orders table, based on the primary cust_id key in the customer table, and a foreign key, cust_id, in the orders table:

ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE 

Building Referential Integrity

Establishing referential integrity involves building a set of rules to preserve the defined relationships between tables when you enter or delete records.

If you enforce referential integrity, Visual FoxPro prevents you from:

• Adding records to a related table when there is no associated record in the primary table.
• Changing values in a primary table that would result in orphan records in a related table.
• Deleting records from a primary table when there are matching related records.

You can choose to write your own triggers and stored procedure code to enforce referential integrity. However, the Visual FoxPro Referential Integrity (RI) Builder enables you to determine the types of rules you want to enforce, the tables on which you want the rules enforced, and the system events that will cause Visual FoxPro to check referential integrity rules.

The RI Builder handles multiple levels of cascading for cascade deletes and updates and is recommended as a tool for building referential integrity.

To open the RI Builder

1. Open the Database Designer.
   
   
2. From the Database menu, choose Referential Integrity.

When you use the RI Builder to build rules for your database, Visual FoxPro generates code to enforce relational integrity rules, then saves it as triggers that reference stored procedures. You can view this code by opening the stored procedure text editor for your database. For information about creating triggers programmatically, see Using Triggers in Chapter 7, Working with Tables.

Caution   When you make changes to the design of a database, such as modifying database tables or altering indexes used in a persistent relationship, you should rerun the RI Builder before you use the database. Rerunning the RI Builder revises the stored procedure code and table triggers used to enforce referential integrity so that they reflect the new design. If you don t rerun the RI Builder, you might have unexpected results, because the stored procedures and triggers are not rewritten to reflect your changes.

Creating Stored Procedures

You can create stored procedures for the tables in your database. A stored procedure is Visual FoxPro code that is stored in the .dbc file. Stored procedures are code procedures that operate specifically on the data in the database. Stored procedures can improve performance because they are loaded into memory when a database is opened.

To create, modify, or remove a stored procedure

• In the Project Manager, select a database and select Stored Procedures, then choose New, Modify, or Remove.

  -or-

• In the Database Designer, choose Edit Stored Procedures from the Database menu.

  -or-

• In the Command window, use the MODIFY PROCEDURE command.

Each of these options opens the Visual FoxPro text editor, allowing you to create, modify, or remove stored procedures in the current database.

You can use stored procedures for creating user-defined functions that you reference in field- and record-level validation rules. When you save a user-defined function as a stored procedure in your database, the code for the function is saved in the .dbc file and automatically moves with the database if you relocate the database. Using stored procedures also makes your application more portable because you don t have to manage user-defined function files separately from your database file.

Viewing and Setting Database Properties

Each Visual FoxPro database has Version and Comment properties. You can view and set these properties with the DBGETPROP( ) and DBSETPROP( ) functions.

For example, the following code displays the version number of the testdata database:

? DBGETPROP('testdata', 'database', 'version') 

The value returned represents the Visual FoxPro .dbc version number, and is read-only. Using the same function, you can view the comment, if one exists for the database:

? DBGETPROP('testdata', 'database', 'comment') 

Unlike the Version property, the Comment property can be set. Use the DBSETPROP( ) function to enter a description or other text that you want to store with the database.

To set the comment property on the current database

• In the Database Designer, choose Properties from the Database menu and type a comment in the Comment box.

  -or-

• Use the comment option of the DBSETPROP( ) function.

For example, the following code changes the comment for the testdata database:

? DBSETPROP('testdata', 'database', 'comment', ;   'TestData is included with Visual FoxPro') 

You can also use the DBGETPROP( ) and DBSETPROP( ) functions to view and set properties on other database objects such as connections and views.

Viewing and Modifying Database Architecture

When you create a database, Visual FoxPro creates and exclusively opens a .dbc (DataBase Container) file. The .dbc file stores all the information about the database, including the names of files and objects associated with it. The .dbc file does not physically contain any top-level objects such as tables or fields. Rather, Visual FoxPro stores file path pointers to the tables in the .dbc file.

To examine the architecture of your database, you can browse the database file, view the schema, browse the database objects, validate the database, and even extend the .dbc file.

Viewing the Database Schema

The database schema is a visual representation of the table structures and persistent relationships established in your database. The Database Designer window displays the schema of the open database.

To view the database schema

• Use the MODIFY DATABASE command.

For example, the following code opens the testdata database and displays the schema in the Database Designer:

MODIFY DATABASE testdata 

A database schema is a representation of the objects in a database.

From the Database Designer, you can use the Database toolbar to create a new table, add an existing table to the database, remove a table from the database, or modify the structure of a table. You can also create connections and edit stored procedures.

Browsing the Database File

The database file contains a record for each table, view, index, index tag, persistent relationship, and connection associated with the database, as well as for each table field or view field having extended properties. It also includes a single record that contains all the stored procedures for the database.

For information about the structure of the .dbc file, see Table File Structure.

While the Database Designer provides a conceptual representation of the database schema, sometimes you might need to browse the contents of the database file itself. You can browse a closed database by issuing the USE command on the .dbc file. The following example opens a Browse window displaying the contents of the sales database in table form.

CLOSE DATABASE sales USE sales.dbc EXCLUSIVE BROWSE  

Caution   Don t use the BROWSE command to alter the database file unless you are knowledgeable about the structure of the .dbc file. If you make an error while attempting to change the .dbc file you can invalidate the database and potentially lose data.

Extending Database Files

Each .dbc file contains a Memo field named User that you can use to store your own information about each record in the database. You can also extend a .dbc file to add fields to accommodate your own needs as a developer. Fields must be added to the end of the structure. You must have exclusive access to a .dbc file to modify its structure.

To add a field to a .dbc file

1. Open the .dbc file for exclusive use with the USE command.
   
   
2. Use the MODIFY STRUCTURE command.

For example, the following code opens the Table Designer so you can add a field to the structure of Testdata.dbc:

USE TESTDATA.DBC EXCLUSIVE MODIFY STRUCTURE 

When you add a new field to a database file, begin the field name with U to designate it as a user-defined field. This designation prevents your field from conflicting with any future extensions to the .dbc file.

Caution   Don t change any existing Visual FoxPro- defined fields in a .dbc file. Any changes you make to a .dbc file could affect the integrity of your database.

Validating a Database

Validating a database ensures that the rows of the database are storing accurate representations of the meta-data in the database. You can check the integrity of the current database with the VALIDATE DATABASE command.

To validate a database

• Use the VALIDATE DATABASE command.

For example, the following code uses and validates the .dbc file for the testdata database:

OPEN DATABASE testdata EXCLUSIVE VALIDATE DATABASE 

Managing a Database

After creating a database, you might want to add it to a project if it isn t already part of one. If your database is already part of a project, you can remove it from a project. Also, if you no longer need the database, you can delete it from the disk.

A database in the Project Manager

Adding a Database to a Project

When you create a database with the CREATE DATABASE command, the database is not automatically a part of a project, even if the Project Manager is open. You can add the database to a project to make it easier to organize, view and manipulate database objects through the interface, as well as to simplify the process of building an application. You can add a database to a project only through the Project Manager.

To add a database to a project

• In the Project Manager, select Databases and choose Add.

Removing a Database from a Project

You can remove a database from a project only through the Project Manager.

To remove a database from a project

• In the Project Manager, select the database and choose Remove, and then choose Remove again.

Deleting a Database

You can delete a database from disk using the Project Manager or the DELETE DATABASE command.

To delete a database

• In the Project Manager, select the database and choose Remove, and then choose Delete.

  -or-

• Use the DELETE DATABASE command.

For example, the following code deletes the sample database:

DELETE DATABASE sample 

Always use one of the methods above to delete a database from disk. Using the Project Manager or the DELETE DATABASE command enables Visual FoxPro to remove backlinks to the database from the tables in a database. If you use another file manipulation utility to delete a database file, such as the Windows Explorer, these backlinks are not removed.

Note   The DELETE DATABASE command does not delete the tables associated with a database from the disk; rather, the tables that were associated with the database become free tables. If you want to delete both a database and all its associated tables from disk, use the DELETETABLES clause with the DELETE DATABASE command.

Referencing Multiple Databases

You can have many Visual FoxPro databases on your system to meet organizational needs in a multi-user environment. Multiple databases offer the following advantages:

• Controlling user access to a subset of tables in the overall system.
• Organizing the data to efficiently meet the information needs of various groups using the system.
• Allowing exclusive use of a subset of tables for creating local and remote views at run time.

For example, you might have a sales database that maintains sales information used primarily by the sales force working with customers and another database that maintains inventory information used primarily by the buyers working with suppliers. At times the information needs of these groups will overlap. These databases can be opened at the same time and accessed at will but they contain completely different types of information.

Multiple databases can add flexibility to your system.

You can use multiple databases either by opening more than one database simultaneously or by referencing tables in a closed database. Once multiple databases are open, you can set the current database and select tables in it.

Opening More Than One Database

When a database is open, the tables and relationships between tables are controlled by the information stored in the open database. You can have more than one database open at a time. For example, you might use multiple open databases when you run multiple applications, each based on a different database. You might also want to open multiple databases to use information, such as custom controls, stored in a database that is separate from your application s database.

To open more than one database

• In the Project Manager, select a database and choose Modify or Open.

  -or-

• Use the OPEN DATABASE command.

Opening a new database does not close any databases you ve opened previously. Other open databases remain open, and the newly opened database becomes the current database.

Setting the Current Database

When you open multiple databases, Visual FoxPro sets the most recently opened database as the current database. Any tables or other objects you create or add to the database become a part of the current database by default. Commands and functions that manipulate open databases, such as ADD TABLE and DBC( ), operate on the current database.

You can choose a different database as the current database through the interface or with the SET DATABASE command.

To set the current database

• On the standard toolbar, select a database from the Databases box.

  -or-

• Use the SET DATABASE command.

For example, the following code opens three databases, sets the first database as the current database, then uses the DBC( ) function to display the name of the current database:

OPEN DATABASE testdata OPEN DATABASE tastrade OPEN DATABASE sample SET DATABASE TO testdata ? DBC( ) 

Tip   Visual FoxPro might open one or more databases automatically when you execute a query or a form that requires the databases to be open. To be sure you are operating on the correct database, set the current database explicitly before issuing any commands that operate on the current database.

Selecting Tables in the Current Database

You can choose from a list of tables in the current database with the USE command.

To choose a table from the current database

• Issue the USE command with a ? symbol.

  The Use dialog box displays so that you can select a table to open.

For example, the following code opens the sales database and prompts you to select a table from the list of tables in the database.

OPEN DATABASE SALES USE ? 

If you want to select a table that is not associated with the open database, you can choose Other in the Use dialog box.

Closing a Database

You can close an open database from the Project Manager or with the CLOSE DATABASE command.

To close a database

• From the Project Manager, select the database and choose Close.

  -or-

• Use the CLOSE DATABASE command.

For example, the following code closes the testdata database:

SET DATABASE TO testdata CLOSE DATABASE 

Both options close the database automatically. You can also close databases and all other open objects with the ALL clause of the CLOSE command.

Issuing the CLOSE DATABASE command from the Command window does not close a database if the database was opened by:

• The Project Manager when you expanded the outline to view the contents of a database.
• A form that is running in its own data session.

In these circumstances, the database remains open until the Project Manager closes the database, or until the form using the database is closed.

Scope Resolution

Visual FoxPro uses the current database as the primary scope for named objects, such as tables. When a database is open, Visual FoxPro first searches within the open database for any objects you request, such as tables, views, connections, and so on. If the object is not in the database, Visual FoxPro looks in the default search path.

For example, if the customer table is associated with the sales database, Visual FoxPro would always find the customer table in the database when you issue the following commands:

OPEN DATABASE SALES ADD TABLE F:\SOURCE\CUSTOMER.DBF USE CUSTOMER 

If you issue the following command, Visual FoxPro will first look in the current database for the products table.

USE PRODUCTS 

If products is not in the current database, Visual FoxPro will look outside the database, using the default search path.

Note   You can specify the full path for a table if you want to be able to access it inside or outside a database for example, if you anticipate a change in the location of a table. However, you increase performance when you reference only the table name, because Visual FoxPro accesses database table names more quickly than names specified with a full path.

Handling Database Errors

Database errors, also called engine errors, occur when run-time errors occur in record-level event code. For example, a database error occurs when a user attempts to store a null value to a field that doesn t allow null values.

When a database error occurs, the underlying database engine that detects the error typically posts an error message. However, the exact nature of the error message depends on what database is being accessed for example, the error messages produced by a remote database server (such as Microsoft SQL Server) will probably be different from those produced if a database error occurs in a local Visual FoxPro table.

In addition, engine-level errors are sometimes very generic, because the database engine has no information about the context in which a record is being updated. As a consequence, error messages produced by a database engine are often less useful to the end user of a Visual FoxPro application.

To handle database errors in a more application-specific way, you can create triggers using the CREATE TRIGGER command. The trigger is called when a record update is attempted (delete, insert, or update). Your custom trigger code can then look for application-specific error conditions and report these.

If you handle database errors using triggers, you should turn buffering on. That way, when a record is updated your trigger is called, but the record is not immediately sent to the underlying database. You therefore avoid the possibility of producing two error messages: one from your trigger, and another from the underlying database engine.

To create custom error messages using triggers

1. Inside a user-defined function or stored procedure, write your own message text.
   
   
2. Enable buffering with the CURSORSETPROP( ) function to display your custom text. If buffering is off, the user will see both your custom text and the engine error message.