In FileFlex, data is stored in what we call databases (each database corresponding to a single .DBF file). What we call databases are often called "tables". A table is basically an ordered sequence of data represented by some number of like records, each broken down into like fields.
As created, each table is its own entity. It's not connected to other tables and actions in one table don't effect actions in another. But, often, you may want tables to work together, to relate in some way to each other.
To many database scholars, this set of tables, working together based on defined relations, is what they call a "database". In other words, to many database experts, the entire set of data files, along with all their relations, is the complete database. Don't worry about it too much, it'll all make sense in a moment.
In FileFlex, each individual .DBF file is what we call the database. And we use the terms database and table interchangeably. When you use FileFlex to relate tables together, so that a group of .DBF files work together, we call the result a "virtual, combined database". It's called this because the files are not physically connected, but related logically. Hence the term "virtual". Because the databases work together to create what almost seems like a bigger, super, meta database, the term "combined" is appropriate. Together, it becomes a virtual, combined database.
Let's look at a quick example. Suppose you had a customer table. The customer table had the name, address, and other mailing/contact information, including a unique customer number for each individual customer. Now, suppose you had an invoices table. In this case, you have one record for each invoice. And on each invoice, you also had a customer number. You'd create a virtual, combined table is where you relate each invoice to each customer entry using the customer number as the common key.
In this chapter, you'll learn how to use the FileFlex 3 relational commands to create and manage your own virtual, combined database. We should warn you: relations are pretty complex coding: if you're not a strong programmer, some of this might be a bit difficult to understand.
Creating a Relation Tree
Let's start with the basics. To create a relation, you'll need two or more databases (the .DBF files), and some number of indexes. Relations are organized in a tree structure. There's a top node. Then a relation can hang off that node, creating a branch. You can relate a second table to the top node, creating two branches, or you can relate a table to the table that's already hanging off the top table, creating a branch with a branch.
But no matter how you structure your relational tree, the first thing you have to do is define your topmost table. To do this, you use DBSelect to choose the database you want to be the top node (what we call the parent relation). Then you'll call DBSetParentRelation to create a new relation and to make the currently selected database the topmost node.
You can do it all in two lines (of course, normally you'd check result codes):
put DBSelect(customerID) into DBResult put DBSetParentRelation() into topID
By doing this, you've allocated memory and created the very top node of the relation.
When you create a relation, you'll get an ID returned, very much like when you do when you use a database with DBUse. To select a relation, you'll use the DBSelectRelation function, passing it the relation ID.
Recall for a moment the example above with a customer table and an invoice table. Think about it. For each customer, you'd have one or more invoices. For example, Johnson Company might place an order a week. Whereas PowerFlubber Industries might only place one order each year. This would be a "one-to-many relation": one customer to many invoices.
Now, let's assume you had an employee database and a salary database. The employee database would have the employee's name, address, hire date, home address, etc. It would also have an employee number. The salary database would also have an employee number and would have the amount the employee gets paid. In this cause, you'd only have one salary record in the salary database for each employee record in the employee database. This would be called a "one-to-one relation".
In FileFlex, when you add a relation to the relation tree, you'll need to decide if you're adding a "many" relation (i.e., a one-to-many or a many-to-many relation) or you're adding an "exact" relation (i.e., a one-to-one relation).
If you're going to add a "many" relation, you'll need to use the FileFlex DBAddManyRelation function. If you're going to add an "exact" relation to the relation tree, you'll need to use DBAddExactRelation. The syntax of both are identical.
Before you add a relation, you'll need the following information:
Obviously, this means you'll need to have created a tag that indexes the child's key field.
Once you've got all that, you're ready to rock. Here's what you'll do:
Let's look at a short example code chunk (again, don't forget to check DBResult for errors):
put DBSelect(ZIPCODE_ID) into DBResult put DBSelectRelation(PARENT_ID) into DBResult put DBAddManyRelation("NAME","BYCITY") into DBResult
Once you've executed the DBAddManyRelation or DBAddExactRelation call, one of three things will happen:
Building a Relational Query Expression
The ability to perform a relational query is one of FileFlex 3's most powerful new features. FileFlex uses some extremely advanced database technology to perform accelerated queries. In FoxPro, the technology to do relational queries is called "Rushmore". FileFlex has a technology very similar in nature to Rushmore.
The idea behind relational queries is very exciting: you can do a dynamic search, and traverse the database, in its virtual, combined structure. You can do searches across databases, looking for information in one table and relating that information to other tables.
You perform a FileFlex relational query using the function DBRelationalQuery. The first thing you need to tell DBRelationalQuery is the query expression. This is very similar to an expression you would have created in an index, or might have been created in FileFlex 2's DBQuery function. For example, you can look for all salaries greater than $30,000. Such an expression might be "SALARY > 30000".
Let's go back to the employee database. Let's assume you wanted to see all employees whose salaries were greater than $30,000 and who were located in New Jersey. The problem is, the information about location is stored in the EMPLOYEE table, while the salary data is in the SALARY table. How do you tell FileFlex what to search?
This is where the "arrow notation" comes into play. The arrow is the dash/greater-than-sign syntax represented by "->" (without the quotes). To specify across tables, you specify the filename the table, followed by the arrow, followed by the field name. Here are two examples:
So, to create a query that tells us about the salary over $30,000 for employees in NJ, you'd create a query expression like this:
(SALARY->PAYRATE > 30000) .AND. (EMPLOYEE->STATE = 'NJ')
In this instance, the bounding parenthesis are not required, but they tend to make the code easier to read and parse. You can learn more about how to set up expressions in the next chapter.
Accelerating a Relational Query
The next parameter to DBRelationalQuery is the "Force Acceleration" flag. Only certain queries can be accelerated. FileFlex query acceleration works by tapping into the power of pre-defined indexes and using those indexes to narrow down the query. For a query to function, the parent's table index files must be open. In fact, the more active index files, the better chance you'll be able to accelerate a query.
Accelerated queries also only work when you're doing a comparison against the topmost parent table.
Finally, accelerated queries must involve, at least partially, a query of a tag key from the topmost parent compared to a constant.
FileFlex 3 recognizes that not all queries will meet these requirements. It allows you to run acceleration in an "as available" mode or in an "acceleration required" mode. When you tell FileFlex to run in "as available" mode, you're saying "Try to do an accelerated query, but if you can't, just run a normal query". This means that FileFlex will try to accelerate the query, and if it doesn't meet the stringent query acceleration requirements, FileFlex will still perform the query.
With the second option, the "acceleration required" mode, FileFlex will not execute the query unless it can be accelerated. If the query cannot be accelerated, FileFlex will return a -840 error code.
The second parameter to DBRelationalQuery, the "Force Acceleration" flag, must be "true" to force acceleration. If it's false, FileFlex will attempt to accelerate, but will run the query even if it can't be accelerated.
One nice design use of this is how you can relate query information to your users. You might create a DBRelationalQuery call where you force acceleration. If it returns a -840, you can tell your user that the query will be slower, and ask the user to confirm whether or not the slower query should be run. If the user wants the query to run anyway, again execute DBRelationalQuery, this time with Force Acceleration set to "false".
Sorting the Query Results
The DBRelationalQuery function has a third, optional, parameter: sort order. With this expression (which is specified like you'd specify any tag), you can determine the order that the query results are returned.
Let's take a look at the following example:
put DBRelationalQuery("STAFF->SALARY > OUTSERV->SALARY", "TRUE", "STAFF->LAST") into DBResult
In this example, we're looking for all employed staff members whose salaries are greater than outside service people salaries. We're forcing acceleration. And we're returning the results in order by the last name of the staff member.
Relations and Navigation
As you now know, having an active index impacts the behavior of DBTop, DBBottom, and DBSkip. The same is true of relations. If you have an active relation, these three functions will operate in the order of the relation.
If you've created a virtual, combined table, but have not yet defined a query, DBSkip will happily move through all valid, combined records. If you've executed a relational query, then DBSkip will move through only those records found in the query. DBTop and DBBottom will move, respectively, to the first and last matching record.
Closing the Relation
If you no longer need a relation and want to clear it out of memory (always something you should do when you're done with FileFlex), you should call DBCloseRelation. It takes no parameters and completely clears out relational memory. It also returns DBSkip, DBTop, and DBBottom back to their natural behavior.
Discuss this chapter on the FileFlex Boards.