by Ken Henderson
Today you'll become quite acquainted with tables and queries. Here's what you can expect to learn today:
Though IntraBuilder doesn't have a separate table object (tables are accessed by using Query objects), you can still readily access tables from a variety of Database Management System (DBMS) platforms. For example, you can access dBASE and Paradox tables because the Borland Database Engine (BDE) supports them natively. You can also access DBMSs for which you have BDE SQL Links drivers. SQL Links drivers are included with the Professional and Client/Server versions of IntraBuilder. IntraBuilder Professional includes SQL Links drivers for Microsoft SQL Server and Borland InterBase. IntraBuilder Client/Server includes these two plus drivers for Oracle, Sybase, DB2, and Informix.
IntraBuilder Client/Server also includes support for ODBC drivers.
Thanks to the ubiquity of ODBC, this means that you can access
virtually any type of DBMS. ODBC drivers are available for everything
from flat text files to IBM AS/400 databases. You can get to Microsoft's
Excel workbooks, as well as Microsoft's own DBMS, Access. There
are very few popular DBMSs for which you can't get an ODBC driver.
After you have the appropriate driver, all you need is IntraBuilder
Client/Server to access your data.
 |
Remember that IntraBuilder works only with 32-bit ODBC drivers. You can't use 16-bit drivers (which are readily available) with IntraBuilder because it's a 32-bit tool. Make sure you have 32-bit ODBC drivers before attempting to access databases by using ODBC from IntraBuilder. |
Even if you eventually intend to place the tables an IntraBuilder application references on an SQL Server, you might want to start off developing the application using local tables. This is simpler, quicker, and easier to manage from a development standpoint. Whether you intend to merely use local tables as precursors to remote ones or you plan to actually build applications around local tables, it's important to know what the different local DBMS formats offer you and what their strengths and weaknesses are.
dBASE tables have the extension .DBF. dBASE is by far
the most popular local DBMS format, having engendered an entire
cottage industry of products seeking to copy and enhance it. It
is the native table format for Borland Visual dBASE, Microsoft
Visual FoxPro, and Computer Associates Clipper. The dBASE format
initially gained widespread use through the popularity of dBASE
III Plus in the early 1980s and has continued to evolve through
the many incarnations that dBASE has taken since then. IntraBuilder
supports level 3, 4, and 5 dBASE tables. Table 7.1 summarizes
the support for dBASE tables by level. Level 6 tables are in development
and may include some of the data integrity options currently available
for Paradox tables.
| Base Product | IntraBuilder Support | |
| Vulcan | None. Not a publicly released format. | |
| dBASE II | None. Not supported by BDE. | |
| dBASE III Plus | DBF table and DBT memo files. NDX index files are not supported. | |
| dBASE IV | Full support for DBF table, DBT memo and production MDX index tags. Non-production MDX index tags are not supported. | |
| Visual dBASE | Full support for DBF table, DBT memo, and production MDX index tags. Automatic conversion for OLE and binary fields. |
Some of the many advantages of using the dBASE table format are described in the next few sections. Despite the technical differences between dBASE and other local DBMS formats, the decision is largely a personal one. What do you feel most comfortable using? Is it dBASE? Paradox? Maybe Access? People tend to get a little testy when you try to swap DBMSs on them. Choose what works for you and stick with it. All of the major players provide sufficient features, speed, and reliability to make excellent candidates for most local DBMS tasks.
Of the two formats native to the BDE-dBASE and Paradox-dBASE is the faster of the two. To an extent, this is due to the fact that dBASE is also less capable than Paradox, but nevertheless, if speed is all you care about, the nod has to go to dBASE. See Day 10, "Querying for the Quickest Searches," for more information on table performance.
Because it's been around so long and been supported on so many platforms, dBASE tables are extremely portable. Many third-party utilities will work directly with dBASE tables. Consider, for example, Microsoft Excel. It includes drivers that let you edit dBASE .DBF files as though they were Excel worksheets. If you intend to access your databases from a number of third-party packages (especially non-Borland ones), the dBASE format is the best choice.
 |
Expression indexes are indexes whose keys consist of expressions, not just field references. These expressions are then evaluated and processed at runtime, giving the BDE interpreter-like characteristics. |
The dBASE format is also the only local format to support expression-based
indexes. You can do all sorts of interesting things using this
expression support, including things that have nothing to do with
building or searching indexes. The following columns list the
dBASE functions and logical operators that the BDE supports in
expression indexes:
| ABS() | ACOS() | ALIAS() | .AND. |
| ANSI() | ASC() | ASIN() | AT() |
| ATAN() | ATN2() | BITAND() | BITLSHIFT() |
| BITOR() | BITRSHIFT() | BITSET() | BITXOR() |
| CEILING() | CENTER() | CHR() | COS() |
| CTOD() | DATABASE() | DATE() | DAY() |
| DBF() | DELETED() | DIFFERENCE() | DOW() |
| DTOC() | DTOR() | DTOS() | ELAPSED() |
| EMPTY() | EXP() | FCOUNT() | FIELD() |
| FIXED() | FLDCOUNT() | FLOAT() | FLOOR() |
| FV() | HTOI() | ID() | IIF() |
| INT() | ISALPHA() | ISBLANK() | ISLOWER() |
| ISUPPER() | ITOH() | LEFT() | LEN() |
| LENNUM() | LIKE() | LOG() | LOG10() |
| LOWER() | LTRIM() | MAX() | MEMLINES() |
| MIN() | MLINE() | MOD() | MONTH() |
| .NOT. | OEM() | .OR. | OS() |
| PAYMENT() | PI() | PROPER() | PV() |
| RANDOM() | RAT() | RECNO() | RECSIZE() |
| REPLICATE() | RIGHT() | ROUND() | RTOD() |
| RTRIM() | SECONDS() | SIGN() | SIN() |
| SOUNDEX() | SPACE() | SQRT() | STR() |
| STUFF() | SUBSTR() | TAN() | TIME() |
| TRIM() | UPPER() | VAL() | VERSION() |
| YEAR() |
The dBASE table format supports a full-featured security system. If you want to set up complex or extensive permission levels for tables accessed by your IntraBuilder apps, dBASE tables are your best choice.
dBASE tables support table and field level security. Table level security is implemented by using data encryption. With protected systems, data is encrypted so that it cannot be read until it's decrypted. Encrypted tables contain data that has been scrambled to the extent that it's unreadable. IntraBuilder automatically encrypts/decrypts tables, indexes, and memo files after the required password information is entered.
Beyond table-level access, dBASE tables also allow you to specify which fields a user can access. You can define the level of access-read, read/write, or full. Again, if you have need of a robust security system for local tables, the dBASE format is the way to go.
As you might expect, there are drawbacks that go right along with the benefits of using dBASE tables. Again, the local format you choose is very much a judgment call.
The biggest drawback to using dBASE tables is that there is no data dictionary. You can't set up a validation mask at the database engine level. Many of the field attributes that users have come to expect from SQL databases are missing from the dBASE format.
Without a data dictionary, database integrity becomes the responsibility of the application developer. IntraBuilder provides a robust set of database classes that a programmer can use to ensure data validation and relational integrity.
Another limitation results from the types of fields you can create in dBASE tables. IntraBuilder supports the eight dBASE field types available for level 5 dBASE tables. Paradox, Access, and most SQL tables offer a wider selection of field types. Figure 7.1 shows the field types supported by dBASE tables. The IntraBuilder Server automatically converts images stored in dBASE binary and OLE fields to Web-compatible formats.
Figure 7.1 : Selecting a dBASE field type in the Table Designer.
Paradox has many advanced features such as referential integrity, lookup fields, and a wide selection of field types. Because of its support for these advanced features, porting Paradox tables to a SQL database can be easier than porting from dBASE tables.
Paradox has a number of strengths that make it a good choice for small projects and for prototyping large ones. In the next few sections, I've listed a few of its features that stand out more than others, but, again, selecting a local table type is largely a matter of personal choice.
Paradox tables support column validation constraints. You can
define default values for each field. Some field types allow you
to set maximum and minimum constraints. All the Paradox field
types support the required constraint. The database engine will
not commit required fields that contain a null value.
|
IntraBuilder ignores column constraints that are not enforced by the database engine. For example, the picture attribute has no effect on data entry within IntraBuilder. The picture field mask is only enforced when editing data within Paradox for Windows. Unsupported constraints do not appear in the Table Designer's Field Inspector. |
Paradox tables also support referential integrity relationships. For example, you can specify that values entered into TableA must be contained in TableB. You can also specify which of a table's fields are its primary key and which ones must be unique. This support for referential and entity integrity makes it easy to migrate data to a client/server database.
As illustrated by Figure 7.2, Paradox tables support a wide array of field types. The list of supported types compares quite well with supported field types in SQL databases.
Figure 7.2 : Paradox tables support a wide variety of field types.
All the benefits of the Paradox format don't come without a price. Usually, more features means more complexity. The question you have to answer is, "What are my needs?" If you need referential integrity at the database engine level, Paradox is the obvious choice. On the other hand, if you need in-depth security support, dBASE would be better.
The biggest hassle with Paradox tables is the management of the NET DIR parameter and its associated PDOXUSRS.NET network control file. To access a Paradox table that's located on a network drive, the NET DIR parameter must be set and must point to a network drive. It cannot point to a local drive.
Even though the current NET DIR setting is stored in the PARADOX driver section of the BDE configuration file, it can also be found in a number of other places. You might find one in the system Registry or in a 16-bit BDE Configuration file. The variety of locations in which you might find NET DIR settings is due to the evolution of the BDE and of Paradox over the years. Progress, at least in this case, has its price.
Although you might find NET DIR settings in several different places, the one in the BDE Configuration Utility's PARADOX driver section has precedence. When NET DIR has been set in the BDE Configuration Utility's PARADOX driver section, settings found in other locations have no effect. This can make properly configuring the Paradox NET DIR confusing, especially for the uninitiated.
Unlike dBASE, Paradox lacks a sophisticated access control system. Paradox doesn't support internal users or user groups; those are left to the domain of the network. You can control table-level access with network access rights, but there's no provision for field-level access control in IntraBuilder. If you need security features beyond the most basic, the Paradox table format is not your best choice.
The extra field validation and masking that you can apply to a Paradox table is stored in a separate file with an extension of .VAL. There are two problems with this approach. First, the .VAL file can be deleted, effectively removing all validity checking for the fields in the table. Second, the file can be viewed, and possibly manipulated, outside your apps. The design employed here by the Paradox database format makes your field-level constraints vulnerable to possible tampering or deletion.
The Borland SQL Link drivers are special DLLs that allow the BDE to access SQL databases. There are SQL Links drivers for Sybase, InterBase, Oracle, Informix, DB2, and Microsoft SQL Server. As I mentioned earlier today, you get SQL Links drivers only with the Professional and Client/Server versions of IntraBuilder; none come with the Standard version. The InterBase and Microsoft SQL Server drivers come with IntraBuilder Professional. IntraBuilder Client/Server includes these two, plus drivers for Sybase, Oracle, DB2, and Informix. SQL Links drivers are highly tuned for their respective back-ends and are usually more fully featured and faster than their ODBC counterparts.
Of course, moving to a client/server DBMS brings with it many of the benefits typically touted by client/server aficionados. You should get better performance when working with large sets of data. Also, you should see greater reliability and recoverability after errors. You should see improved transaction management, and so forth. Naturally, these benefits are passed on to your apps from your SQL Link drivers. Though (when compared with local DBMS drivers) the SQL Links drivers perform a relatively small portion of the work, you have to pretend they do more for the sake of comparison with local drivers. That is, when you're trying to make a decision on whether to go with dBASE tables or InterBase tables, you have to look at the big picture. You have to weigh everything from the database drivers themselves to the format, speed, and integrity of the underlying DBMSs. These elements amount to a sum-total of the reasons you should or shouldn't choose a particular platform.
The bottom line is that a comparison between the BDE's native formats (dBASE and Paradox) and SQL Links drivers must include not only a discussion of the client-side features of a given driver, but also of the server-side features of its back-end DBMS. The comparison isn't so much one of local drivers versus SQL Links drivers as it is the dBASE and Paradox table formats versus client/server DBMSs.
Because objects can reside on an entirely different computer or set of computers, client/server DBMSs definitely have the upper hand as far as security is concerned. In contrast to dBASE and Paradox tables, it is often virtually impossible to modify SQL Server-based objects except through the server program itself. Client/server DBMSs typically support their own logins and passwords and force access to be explicitly granted to user accounts.
Because they normally support complex transaction logs and log management functions, SQL DBMSs are usually more reliable than local table-based systems. It's easy enough to corrupt dBASE indexes by turning off your machine while building them. However, SQL databases provide a number of recovery processes and checks against data corruption. They also support advanced features such as replication, backup servers, and clustered servers, so usually you can rely more heavily on them than you can on local formats such as dBASE and Paradox.
The benefits of using the client/server DBMSs to which the SQL Links drivers provide access are not without their costs. To begin with, having your databases on a separate computer or computers adds a level of complexity in itself. Other drawbacks include the configuration headaches that can occur with client/server DBMSs and their reliance on networks and network bandwidth.
Because you're locating database objects on a separate database server, you automatically double your machine administrative overhead. Not only do you have the client machine to consider, now you have to think about a server as well. If you upgrade the OS on one, maybe you should look at upgrading it on the other. If you come up with a backup solution for one, you've got a second backup solution to think about, as well. Of course, this increase in overhead isn't exponential. It applies only when you first add your database server. As you add additional clients to your server, you won't see repeated doubling of your administrative overhead.
In addition to server machine complexities, client/server DBMSs also bring with them client machine complications. Fortunately, IntraBuilder's server-based approach to database access alleviates the need to make a number of changes to client machines simply to run database apps. Your database access happens on the server; all a client needs is a compatible browser. On the other hand, it's not as though you'll access only client/server DBMSs with IntraBuilder apps, even if the purpose of the server is to support IntraBuilder apps exclusively. This is because you'll need to manage the server itself. It will have to be backed up. It will have to be administrated. You'll probably do at least some of this from machines other than the server. If this is the case, your client machines will have to have the proper database and network libraries and drivers installed on them. This adds a level of complexity that is a constant source of headaches for database administrators.
A final area that is more complicated with SQL databases than with local ones is that of network access. Many client/server DBMSs place unusual demands on their host networks. A client/server DBMS might, for example, insist on using a given protocol on the network. It might require TCP/IP and refuse to use IPX. It might require support of Out of Bound Data (OOBD) or multiple connections between the server and its clients. A good client/server database administrator usually ends up receiving a thorough, if not painful, introduction to network management. You have to weigh these additional complexities when deciding whether a given client/server platform is for you.
Microsoft's Open Database Connectivity (ODBC) specification is by far the most popular of the database access middleware specs in existence. There are ODBC drivers available for just about everything. The drivers you can't get from DBMS vendors themselves can usually be acquired from a third-party such as Intersolv. To an extent, ODBC suffers from the "jack of all trades, master of none" syndrome, but things are improving. Gradually, the spec has been enhanced to support increasingly more advanced features, and vendors continue to enhance their own drivers to support proprietary innovations.
ODBC's most significant benefit is its ubiquity. It seems that you find a new ODBC driver under every rock these days. PC software and mainframe vendors alike have adopted the standard and include ODBC drivers with their products. Beyond that, about the only other real benefit to using ODBC is that Microsoft backs it. Due to Microsoft's huge market presence, the fact that it backs a technology is reason in itself to consider using the technology. You can probably count on the technology, whether it's ODBC or something else, being around for awhile.
If you're a Microsoft Access user, you'll have to use ODBC to
make use of your tables within IntraBuilder. Microsoft hasn't
published the internal specifications of the Access database format.
If using Access is important to you, you'll definitely need ODBC.
| NOTE |
If you've installed Microsoft Office Professional, you might already have an ODBC driver for Access databases. You can install the driver when installing any of the Microsoft Office products. |
As I've mentioned, not only does ODBC provide access to Microsoft products, it's also supported by most other vendors. You can usually find an ODBC driver for just about any popular DBMS or file format. ODBC provides a generic, vendor-independent interface to databases and files of all kinds. By providing support for ODBC, the Borland Database Engine has ensured that it is as compatible with as many platforms as possible.
The Borland SQL Links drivers that come with the Professional
and Client/Server versions of IntraBuilder are faster and generally
more feature-rich than their ODBC counterparts. Because full support
for ODBC is provided only by the Client/Server version of IntraBuilder,
you should resort to ODBC only if you don't already have an SQL
Links driver for your particular DBMS. That is, if you buy IntraBuilder
Client/Server, you automatically receive SQL Links drivers for
Sybase, Oracle, InterBase, DB2, Informix, and Microsoft SQL Server.
As a rule, don't bother with ODBC drivers if you're using one
of these platforms. Likewise, you can get by just fine without
ODBC support if you're using either InterBase or Microsoft SQL
Server because IntraBuilder Professional includes SQL Links drivers
for both of them.
| NOTE |
Every rule has an exception, and so does my recommendation regarding not using ODBC when an equivalent SQL Links driver is available. I've had a variety of problems with the Microsoft SQL Server SQL Links driver. Of particular concern was a recurrent problem with one connection from my application blocking another. Performance was also a major problem with the SQL Links driver. It appeared painfully slow at times. Switching to the ODBC driver cured these ills every time. If you run into a similar situation with the Microsoft SQL Server SQL Links driver, you might give the ODBC driver (which is included with the server software) a try. |
One disadvantage of using ODBC in comparison with SQL Links drivers is the lack of performance. You'll often see a perceptible speed degradation with ODBC drivers. This isn't always the case. There are, in fact, some rather speedy ODBC drivers available. However, the general consensus is that ODBC drivers are not highly tuned for speed. The one-size-fits-all approach that ODBC takes to database access prevents it from taking advantage of some vendor-specific performance enhancements. SQL Links drivers, on the other hand, usually provide complete access to their back-end DBMSs. They even provide the API calls and data structures necessary to make vendor-specific client library calls. This results in better performance and a richer feature set than similar ODBC drivers.
Like their SQL Links counterparts, ODBC drivers also involve configuration issues that are outside the Borland Database Engine. You have vendor-supplied client libraries that might need to be installed (if you're accessing a remote SQL database), you have network issues to consider, and you have the additional machine management issues that I mentioned earlier in the "Choosing a Table Type" section. In a sense, ODBC gives you the worst of both worlds: You get the complexities of SQL Links drivers, but slower performance and a least-common-denominator feature set not unlike local tables. This isn't to say that you'll have problems configuring or using ODBC drivers. Most people don't have serious problems using ODBC in their apps. Just keep in mind that many of the same complexities that you face with SQL Links drivers apply equally well to ODBC drivers.
Now that you've learned a little about the background and feature set of the various table formats and database drivers, the next task is learning how to access them from IntraBuilder. I'll take you first through the process of creating tables and then through referencing them from IntraBuilder. We'll finish up today by exploring IntraBuilder's database administration facilities.
As with forms and reports, IntraBuilder includes a wizard facility for creating tables. To invoke it, click the New button on the IntraBuilder toolbar and select Table from the pop-up menu. After the New Table dialog box appears on-screen, click the Expert button.
As illustrated by Figure 7.3, the first step in the two-step Table Expert process is to select fields from a list of sample tables that you want to include in your new table. You can include fields from several different sample tables in the table you're building. Clicking a table in the samples list causes the fields it contains to be listed in the From Sample Table list. Click the arrow buttons to add fields from the sample tables to your new table definition. Select a variety of fields so that you have a wide range of field types in your new table.
After you've selected the fields you want in your new table, click
the Next button to proceed.
| TIP |
Don't fret if you can't find a needed field definition in one of the sample tables using the Table Expert. Go ahead and create the new table anyway. After the Table Expert has created the table, you can modify it using the Table Designer and add any missing field definitions. |
The next, and final, step of the Table Expert is to select the table type you want to create and either "run" the table (open it), or modify it in the Table Designer. For now, choose the Paradox table type. Figure 7.4 shows the Table Expert.
Figure 7.4 : You choose the type of table to create in the Table Expert's final dialog box.
Now that you've selected a table type, click the Run Table button to open your new table. Before the table actually runs, you'll be asked to save the new table. Supply a name for the new table and click the Save button. Figure 7.5 shows what your new table might look like at runtime.
Figure 7.5 : You can open new tables in a simple form that IntraBuilder creates for you.
| NOTE |
When you run a table, IntraBuilder provides buttons to add, delete, search, and navigate the table's rows. You don't need to build a special form to do these things; you can perform most basic data navigation and modification tasks by simply running the table. |
Now that you've created and run your new table, it's easy enough to modify the table by using the Table Designer. With your new table still running, click the Table Design button on the IntraBuilder toolbar. Figure 7.6 shows what you should see.
Figure 7.6 : The new table as it might appear in the Table Designer.
| NOTE |
You can rearrange the columns represented in the Table Designer by simply dragging them to new locations. To do this, position the mouse pointer over the heading of the column you want to move (the pointer should change to a hand), and drag it to a new location. A rectangle should appear around the column as you drag it. |
You can add a field to the end of your table definition by clicking just below the last field or by clicking the Add Field button on the IntraBuilder toolbar. Add a Number field now to the end of those already listed. Figure 7.7 illustrates adding a field in the Table Designer.
Figure 7.7 : Adding a field to your new table is easy in the Table Designer.
| TIP |
As an alternative to clicking the buttons on the IntraBuilder toolbar, you can right-click the header area of the Table Designer (not the field list) and select the Add Field, Insert Field, or Delete Current Field options from the shortcut menu. |
You might have noticed that a Field Inspector automatically appeared
when you began editing your table in the Table Designer. The Field
Inspector enables you to define a default, maximum, and minimum
value for each field. It also enables you to flag whether a field
is required. Table 7.2 shows you the properties you should enter
for the new field you just created.
| NOTE |
You'll only see the Field Inspector when working with Paradox tables. Level 5 dBASE tables do not support declared default, maximum, or minimum values, so you won't see a Field Inspector when working with them. |
| Property | Value |
| default | 1 |
| maximum | 100 |
| minimum | 0 |
| required | true |
You can establish these same four properties for every field in the table, if you like. (Provided they apply, of course; you can't, for example, define value properties for BLOB or OLE fields.) These field properties are built into the Paradox database format.
Now that you've fully set up the new field, click the Run button on the toolbar to run the table again. You'll then be prompted to save the changes you've made to the table. Click the Yes button. You should then see your new field in the running table. If you click the Add Row button, the default value you defined for the new field should appear in it, as shown in Figure 7.8.
Figure 7.8 : Adding a new row with the new field.
| TIP |
You can rearrange the order of a table's field definitions by simply dragging them to new locations. To do this, position the mouse pointer over the field number of the definition you want to move (the mouse pointer should change to a hand) and drag the field definition to a new location. You should see a rectangle around the definition as you drag it. |
Deleting a field from a table definition is equally simple. With your new table still running, click the Table Design button on the toolbar. Next, select one of the fields in your table and click the Delete Current Field button on the toolbar. Figure 7.9 shows how to delete a field using the toolbar.
| NOTE |
In addition to the buttons on the toolbar, you can also manipulate field definitions by using the options on the Structure menu. |
There might be times that you want to add a new field definition somewhere besides the tail end of your existing table structure. If you'd rather insert the new field into the middle of your field list somewhere, click or highlight a field, and then click the Insert Field button. The new field will be inserted before the highlighted field. Figure 7.10 illustrates inserting a new field.
Figure 7.10 : You can use the Insert Field button to insert new field definitions.
After you've clicked the Insert Field button, the field on which you were positioned should be shifted down and a blank field definition should appear in its place. You can then key in your new field's name, length, and other attributes, as shown in Figure 7.11.
Figure 7.11 : After clicking the Insert Field button, key in your new field's attributes.
After you're finished editing your table definition, click the
Save button to save your table structure to disk.
| TIP |
IntraBuilder provides keyboard shortcuts for a number of operations, including several that are available in the Table Designer. Table 7.3 lists some that help with editing table definitions. |
| Operation | |
| Add Field | |
| Insert Field | |
| Delete Field | |
| Goto Field |
A primary key is the field or set of fields in a table that can be used to uniquely identify individual records. Examples of primary keys include customer number fields from customer tables, invoice number fields from invoice tables, and so on. A table can only have one primary key, although it can have more than one set of unique fields.
Now that you've learned to add, insert, or delete fields, let's define the table's primary key. Selecting a primary key ensures that each row in a table is uniquely identified. It also allows the table to participate in foreign key references from other tables. You set up primary keys by selecting Structure|Define Primary Key. Click it now.
After the Define Primary Key dialog box is displayed, select a field or two as your table's primary key and click OK. The fields you select must be consecutive beginning with the first field in the table (the Paradox table format imposes this restriction). Figure 7.12 shows a primary key definition.
Figure 7.12 : Use the Define Primary Key dialog box to select your table's primary key fields.
| NOTE |
As with other extended column attributes, you can't define primary keys for level 5 dBASE tables. If you want to experiment with primary key definitions, be sure you're using Paradox tables or some other format that supports primary keys. |
| TIP |
You can configure the appearance of the Table Designer. Right-click its header area and select Table Designer Properties from the shortcut menu. The Table Designer Properties dialog box allows you to configure whether horizontal and/or vertical lines are displayed between rows and columns in the Table Designer. |
After you've finished defining your new table's primary key, resave your table definition. You can do this either by clicking the Save button on the IntraBuilder toolbar, by pressing Ctrl+S, or by selecting File|Save.
Now that your table has a primary key, you're ready to define additional indexes for it. Secondary indexes can help speed access to your table's data. Suppose you have a table with the following fields: Last_name, First_name, Phone, Email, and Level. The table's primary key might be the Last_name and First_name fields. However, you might often need to search the table using the Level field as well. The table's Level field, then, would be a good candidate as the key field for a secondary index. If you create an index for the Level field, searches against the table using the field could be sped up by the index-meaning that they could make use of it. In terms of searching for data, secondary indexes can dramatically improve access time.
With this in mind, it might seem reasonable to create secondary indexes over every remaining field in a table after the primary key is defined. This brings up the one downside to creating additional indexes: They cause additional overhead when inserting or updating data. As you add rows to a table, its indexes must be updated to reflect the key values introduced by the new rows. Indexes also must be updated when you delete rows from a table or modify the fields that make up their keys. In short, indexes speed data access, but slow data modification.
The trick with wise index selection is in indexing enough fields to give you the query performance you need without losing the data insertion/update performance you require. Knowing what to index and what not to index is a balancing act between speed when accessing data and speed when inserting or updating it. Often, this varies from application to application and can be determined only by trial and error.
Keep in mind that index searches work left to right in a composite
index key. This means that you might not need as many indexes
as you think. For example, if you have an index whose key fields
are the Last_name and First_name fields from
a table, you don't need an index on Last_name alone in
order to search using only the Last_name field; your
composite key will service any queries against Last_name
just fine.
|
A composite index key is an index key that consists of multiple fields. You can use multiple fields in both a table's primary key and in its secondary index keys. |
Define a secondary index by selecting Structure|Manage Indexes. Click it now. Immediately, you should see the index created by your primary key definition listed. Figure 7.13 shows what you should see.
Let's add a new index definition to your table. Click the dialog box's New button to display the Define Index dialog box. Click the arrow buttons to select a field or two as the key field(s) for your new index. If you selected multiple fields as your index key, supply a name for the index as well. After you're done, click OK. Figure 7.14 illustrates the addition of an index.
Figure 7.14 : Create new indexes using the Define Index dialog box.
| TIP |
You can also double-click fields to move them between the Available Fields and Fields of Index Key lists. |
After you return to the Manage Indexes dialog box, you should see your new index listed. IntraBuilder names single field indexes after their key fields. Composite key index names are blank by default, though you can (and should) name them. Figure 7.15 shows what your Manage Indexes dialog box might look like now.
Figure 7.15 : Your new index is listed in the Manage Indexes dialog box.
|
Even though IntraBuilder doesn't force you to, be sure to name composite key indexes. If you fail to, IntraBuilder will not provide a default name for them. Unnamed indexes cannot be immediately deleted or edited using the Manage Indexes dialog box. You'll have to exit and re-enter the dialog box in order to edit or delete them. |
Click OK to leave the Manage Indexes dialog box and save your revised table definition to disk. After you've saved your table definition to disk, close the Table Designer.
Because we're currently dealing with Paradox tables, I'll begin discussing IntraBuilder's database administration as it applies to Paradox tables. Later, we'll cover database administration with dBASE tables, as well.
As far as IntraBuilder is concerned, there are two areas of database
administration-referential integrity constraint definition and
table security. Defining referential integrity constraints amounts
to defining relationships between tables. Setting up table security
consists of defining a password that's required to access a given
table. There are no provisions in the Paradox file format for
users, groups, user or group rights, and so on. You might say
that Paradox's administration facilities are a bit weak.
|
Referential integrity ensures that references between tables are valid. That is, if TableA references a row in TableB, referential integrity constraints help ensure that this relationship is respected. Referential integrity prevents the row in TableB from being deleted as long as TableA references it. Likewise, it forces new entries in TableA to have corresponding entries in TableB. In this sense, the referential integrity constraint is bidirectional; it restricts, or constrains, both tables involved in a table-to-table relationship. |
You set up both of these items by selecting File|Database Administration. Change the Table Type to Paradox. Figure 7.16 shows the Database Administration dialog box.
Click the Referential Integrity button to bring up the Referential Integrity Rules dialog box. Here you can define relationships between tables. Click the New button to begin defining a new referential integrity rule. Figure 7.17 shows the dialog box you should see.
Figure 7.17 : Define table relationships in the New Referential Integrity Rule dialog box.
You can use tables from the CD-ROM Day 7 folder to set up a relationship between the Levels table and the People table. To set up a referential integrity relationship between two tables, load the New Referential Integrity Rule dialog box and follow these steps:
|
A one to one relationship is one in which there's a maximum of one row in the child table for each row in the parent table. A one to many relationship is one in which one or more rows exist in the child table for each row in the parent. Referential relationships are usually one to many in nature. |
Figure 7.18 shows the newly defined relation. You can modify relations from this dialog.
Figure 7.18 : A sample referential integrity rule definition as it might look when completed.
You can click Cancel now to exit the rule definition dialog box. Next, click Close to exit the Referential Integrity Rules dialog box.
You should again see the Database Administration dialog box. This time, click Security. You should then see the Security dialog box. Select the new table you created earlier and click Edit Table as shown in Figure 7.19.
Figure 7.19 : Use the Security dialog box to specify passwords for Paradox tables.
You should next see the Master Password dialog box. Enter the password you want users to enter to access the table and then enter it a second time for confirmation. Asterisks will be displayed in place of the characters you type in order to keep them from being visible. After you've typed the password twice, click Set to set the password. Figure 7.20 shows the Master Password dialog.
Figure 7.20 : Enter Paradox table passwords in the Master Password dialog box.
After you're back in the Security dialog box, click Close to return
to the Database Administration dialog box.
|
After you set a table access password, you'll have to supply it in order to access the table. This includes changing the password by using the Security dialog box. You'll have to supply the old password to change or delete it. It's a good idea to record in a safe place passwords you use. |
You have a couple of options available to you for providing access to Paradox tables that require passwords. The first is simply to let IntraBuilder display a default login form, which prompts the user for the required password. You don't need to do anything to utilize this facility-it's automatic. If a form attempts to open a password-protected table, the built-in password form is displayed automatically. For Paradox tables, this form is PASS_PDX.JFM. It's located by default in the C:\Program Files\Borland\IntraBuilder\Bin folder.
The second option available to you is to hard-wire passwords by supplying them with JavaScript code. Passwords for Paradox tables are handled by using Session objects and are supplied with the addPassword method. The following is some sample JavaScript code to supply a Paradox table password:
_sys.databases[0].session.addPassword("MyPassword");
| NOTE |
Referring to _sys in JavaScript code isn't the preferred method of adding Paradox passwords. Instead, call the query component's addPassword method from its canOpen event. This fires before PASS_PDX.jfm. For example, here is the query's canOpen: function myquery1_canOpen() |
You could place code like this strategically so that it preempts the automatic password form. This way, users are not bothered with repeatedly typing a minimal access level password.
As I mentioned previously, dBASE supports a much richer security system than Paradox does. dBASE tables support user-level access, group-level access, table-level access, and field-level access. I'll give you a brief tour of the dBASE security system and how to set it up.
Each time you click the Security button in the Database Administration dialog box when you're working with dBASE tables, you'll be prompted for the Administrator account's password. The first time you do this, you'll actually set up the password. You'll be prompted to enter it a second time for confirmation, and whatever you type will be displayed as asterisks. Be sure to record the password in a safe place, because you'll need it to access the dBASE security system in the future.
The second time, and all subsequent times, you enter the security system, you'll be required to type the Administrator password before proceeding. After you're in, you'll be able to define users and groups and their access rights.
If you haven't done so already, change the Database Administration dialog box's Table Type to DBASE and click Security. Key in the Administrator password as necessary and proceed to the next dialog box. Figure 7.21 shows what you should see.
Figure 7.21 : The dBASE Security dialog box is where you define users and groups.
The first order of business is to define your groups. After the groups are set up, you'll add users to them. Each table in your database(s) can belong to only one group, so it's important to plan ahead. You'll probably want to organize groups similarly to your organization. For example, you might organize them by department or by sales area. Click New to begin simultaneously defining a new user and group.
For our purposes, you can come up with your own user and group name. You can leave the Access Level entry box at its default of 1. Click OK when you're finished. Figure 7.22 shows an example of the completed New User dialog box.
Figure 7.22 : You add new users and groups by using the New User dialog box.
| NOTE |
Though a user can belong to more than one group, a user must be logged into each group separately. |
You might be wondering what the function of the Access Level setting is. Access levels range from 1 to 8. Lower numbers provide greater access; higher numbers provide lesser access. Establishing access levels within groups enables you to give different users different rights to tables and their fields. Apart from the rights they provide, access levels themselves are conceptual in nature; they have no actual value or meaning.
Typically, you assign the more powerful levels (such as 1-3) to fewer users than you do the less powerful levels. If you want to secure your system, assign only small numbers of users access levels with greater rights. Keep in mind that you can assign as many users as you want to each access level. Note that there's no reason to alter a user's default access level if you don't intend to modify the access levels of the tables belonging to his or her group. User access levels and table/field access levels work hand-in-hand; you don't need one without the other.
Now that your user and group are defined, click the Tables tab in the Security dialog box. Select a dBASE table from the list and click Edit Table. Figure 7.23 shows the Edit Table Privileges dialog box.
Figure 7.23 : You use the Edit Table Privileges to set up dBASE table and field security.
Table access can be divided into four distinct categories: read, update, extend (append), and delete. By default, users with an access of eight (that is, anybody) can perform all these tasks. You can limit who can perform a given task by changing the access level associated with the task. Users with an access level higher than that associated with a given task will not be able to perform the task. For example, if you set the Delete access level to 5, users with access levels between 1 and 5 will be able to delete records; however, those with levels of 6 or higher will not.
Field level security works very similarly. Each access level has three privileges that it can have associated with it: Full, Read-Only, and None. So, for each field, you can control which tasks each access level can perform. You might, for example, decide that you want only users of access level 2 or higher to be able to update the Notes field in the Product table and that users below level 5 shouldn't even see the field. In this scenario, you'd click the Notes field and set the privileges of access levels 3, 4, and 5 to Read-Only and levels 6 through 8 to None. Click Cancel when you're done experimenting with dBASE table and field security.
You have two choices for providing access to encrypted dBASE tables. The first is to allow IntraBuilder to automatically display the default dBASE login form, which prompts the user for the required information. If a form attempts to open an encrypted dBASE table, the built-in password form is displayed automatically. For dBASE tables, this form is PASS_DBF.JFM. It's located by default in the C:\Program Files\Borland\IntraBuilder\Bin folder.
The second option is to hard-wire passwords with JavaScript code. Login information for dBASE tables is set up using the Session object's login method. The following is some sample JavaScript code to supply dBASE login parameters:
_sys.databases[0].session.login("FBI", "DCOOPER", "COOP1") ;
|
When working with encrypted dBASE tables, a file called dbsystem.db is created (which is not a Paradox table). This file holds all encryption keys. If this file is deleted or damaged to the point of being inaccessible, all encrypted files will no longer be accessible. If you are no longer working with encrypted tables, you can erase the dbsystem.db and forget your master password. If the dbsystem.db file is not found, IntraBuilder will not prompt you to log in during startup. |
You could strategically place code like this so that it preempts the automatic login form. You might, for example, encrypt all tables on your Web server in order to protect them from intruders and hard-code a read-only access user account to provide access to the general public with your IntraBuilder apps. User accounts with greater privileges could be defined for staff members and others who need additional rights.
You can now exit completely out of the Database Administration dialog box.
As with SQL and Paradox tables, you have two choices for providing access to SQL tables. The first is to allow IntraBuilder to automatically display the default SQL login form, which prompts the user for the required information. If a form attempts to open an SQL table, the built-in password form is displayed automatically. For SQL tables, this form is PASS_SQL.JFM. It's located by default in the C:\Program Files\Borland\IntraBuilder\Bin folder.
Alternatively, you can hard-wire passwords with JavaScript code. Login information for SQL tables is set up using the Database object's loginString property. You can set this property using the Inspector while editing your forms in the Form Designer. The property takes the form user/password, like so:
DCOOPER/COOP1
| NOTE |
Sharing a Database object among multiple forms can create more problems than it solves. Until you feel comfortable with the IntraBuilder dynamic object model, set up your forms so that each one has its own Database object. Each Database object should then have the appropriate loginString property value for its particular form. |
Tables are accessed in IntraBuilder through Query objects. Query objects are driven by SQL SELECT statements, which reference the tables you want to include on a given form. For example, you might build an SQL SELECT statement like
SELECT * FROM "PRODUCT.DBF"
to include the PRODUCT.DBF table on a form. Conversely, you might construct a much more complicated SQL statement to join multiple tables together or restrict the rows returned using a complex WHERE or HAVING clause.
The quickest way to add a table (and thereby a Query object) to a form is simply to drag the table from the Tables page of the IntraBuilder Explorer and drop it onto a form or report. IntraBuilder will automatically set up and open a corresponding Query component. If you drag a table from a BDE alias (Sybase, Oracle, and so on), the database object is also created.
If you need something a little more sophisticated than a simple SELECT * against a single table, you might want to drop a Query component onto your form or report "manually." You'll then need to set its sql property to the SQL code that you want to execute and set its active property to true.
As an alternative to keying in your SQL "by hand," you can use IntraBuilder's Visual Query Builder to create the code for you. To do this, follow these steps:
You can use the SQL button (which appears as a pair of spectacles) on the Visual Query Builder toolbar to view the SQL being built for you. Figure 7.24 shows an example of what a query might look like in the Visual Query Builder.
Figure 7.24 : You can build complex SQL queries using the Visual Query Builder.
| NOTE |
When you furnish SQL to the Query object's sql property using either SQL script files or Query Builder .QRY files, IntraBuilder places the name of the external file itself in the sql property, prefixed with the @ character. |
In this chapter you learned the advantages and disadvantages of the Paradox and dBASE table formats and the advantages and disadvantages of using SQL Links and ODBC drivers. You learned how to create tables, to build indexes, and to modify table definitions. You also saw how to define primary keys and establish referential integrity relationships. You finished the day by learning how to set up Paradox table passwords, how to set up the dBASE security system, and how to build queries using SQL and the Visual Query Builder.
| Q: | If I'm wanting to prototype applications with local tables that I eventually plan to move to a client/server DBMS, what's the best local table format for me? |
| A: | I would pick Paradox because of its support for declarative referential integrity and its rich palette of supported field types. |
| Q: | What's the best local table type in terms of security? |
| A: | That would definitely be the dBASE table type. |
| Q: | What options are available for prompting users for encrypted table passwords? |
| A: | You can let the automatic password form display or force passwords yourself using either JavaScript and the Session object, or the Database object and its loginString property. |
| Q: | Which is better-ODBC or SQL Links? |
| A: | That's a difficult question, and the answer could be a book unto itself. The short answer is that I prefer SQL Links. The long answer is, it depends on your needs, and keep in mind that they aren't mutually exclusive. You can use ODBC drivers with the Borland Database Engine in the same apps where you're using SQL Links drivers. |
The Workshop section provides questions and exercises to help you get a better feel for the material you learned today. Try to answer the questions and at least think about the exercises before moving on to tomorrow's lesson. You'll find the answers to the questions in Appendix A, "Answers to Quiz Questions."
