Delete, DELETED(), Pack, Recall, SET DELETED, Set(“Deleted”)

These commands and functions control deletion of records from a table. Deleting a record in FoxPro (and in any other Xbase language) is a dance—a two-step—because it takes two steps to really and truly make a record go away. First, you “mark” the record for deletion. You can permanently throw away all the marked records when you want. Until you do so, you can “unmark” the record. It’s like deleting files using the Windows Recycle Bin; the file’s not really deleted from the disk until you empty the bin. Come to think of it, it’s not so different from real life. You put something in the trash, but you can retrieve it (and uncrumple it) until the trash can is emptied.

The first byte of every record in each table is a deletion flag. That’s why the total length shown in the Table Designer is always at least one more than the sum of the field lengths (null fields can further increase the record length; see “Nulls” in “DBF, FPT, CDX, DBC—Hike!”) This flag field contains an asterisk (“*”) when the record is marked for deletion and a space when it’s not marked. You can see this by opening up a table using a hex editor—that’s how we figured it out.

Usage

DELETE [ Scope ]
       [ FOR lForExpression ]
       [ WHILE lWhileExpression ]
       [ IN cAlias | nWorkArea ]
       [ NOOPTIMIZE ]
RECALL [ Scope ]
       [ FOR lForExpression ]
       [ WHILE lWhileExpression ]
       [ IN cAlias | nWorkArea ]
       [ NOOPTIMIZE ]

DELETE marks one or more records for deletion. The command accepts a Scope, a FOR and a WHILE. Its default scope is NEXT 1. (Good choice for a destructive command. It limits the damage if you make a mistake.) RECALL unmarks one or more records. Again, the default scope is NEXT 1.

Both DELETE and RECALL allow you to specify another work area using IN. We’ve changed our minds on using this clause and now recommend that you always do so. That way, you’re sure what table you’re working on. In addition, always DELETE the fewest records that make sense. In many cases, you should ask the user to confirm the deletion at least once; in extreme cases, maybe even twice depending on the reason for the deletion. DELETE and RECALL both accept the NOOPTIMIZE keyword, but we’ve never been able to think of a reason to use it. If you really think you need it, see SET OPTIMIZE.

With Visual FoxPro’s Referential Integrity Builder, you’ll need to write a lot less deletion code than before. If you set deletion to cascade in appropriate one-to-many relationships, you need to delete only the record on the one side, and the RI code handles all the many records.

Visual FoxPro actually offers two ways to delete records (though only one to recall them). DELETE-SQL has pretty much the same functionality as the traditional Xbase DELETE. However, Xbase’s DELETE pays attention to the current deletion status of records and therefore seems to be faster when some records are already deleted.

Example

* This might be in the Click event of a Delete button
#INCLUDE FoxPro.H
IF MESSAGEBOX("Do you really want to delete this record",;
     MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2, ;
     "Delete current record?") = ID_YES
   DELETE NEXT 1
ENDIF

Usage

PACK [ MEMO | DBF ] [ TableName ] [ IN cAlias | nWorkArea ]

PACK permanently removes all marked records. What it actually does is copy all the unmarked records into a new file and then rename the new file. (The table is also reindexed.) Because PACK does this behind the scenes, there is a small chance for data loss if your system crashes during a PACK.

Use PACK with caution. Before VFP, the standard advice was to do the same steps yourself, using COPY TO, DELETE FILE and RENAME. That’s still your best bet for free tables. Either way, you need exclusive access to the file to perform this maintenance. Also, in either case, you should SET ORDER TO the tag you use most often before starting the process. That way, the records in the resulting file will be in the order in which they are most often accessed, resulting in better performance. Here’s an example to get you started:

USE MyTable ORDER BestOrder EXCLUSIVE
COPY TO MyNewTable FOR NOT DELETED()
 
IF FILE("MyNewTable.DBF") AND FILE("MyNewTable.FPT")
   * Only go on if the new version was created
   USE
   DELETE FILE MyTable.DBF
   DELETE FILE MyTable.FPT
   DELETE FILE MyTable.CDX
   RENAME MyNewTable.DBF TO MyTable.DBF
   RENAME MyNewTable.FPT TO MyTable.FPT
 
   * Now re-create the indexes
ELSE
   WAIT WINDOW "Trouble in Paradise" NOWAIT
ENDIF

For tables in a database, things are a little more difficult. Using the COPY TO approach, you lose all the database information for the table. In particular, your relations get trashed. Your best bet is to get a good database maintenance tool (like Stonefield Database Toolkit) to handle these chores.

Memo files (FPT) can accumulate a lot of wasted space because replacement of existing data doesn’t reuse the same disk space. Over time, this can really add up, especially if those humongous general field OLE objects are used and edited. PACK MEMO cleans up this wasted space without removing the records marked for deletion. There’s also a PACK DBF version, but it’s another of those items that seems to have been added for completeness, not because it was useful. It removes deleted records without compacting memo space. (If you find a reason to use it, let us know.)

VFP 7 makes it a little easier to use PACK. First, it acquires an IN clause, so you can specify which table you mean and not depend on being in the right work area. More interestingly, the new TableName parameter means that you can pack a table without having to explicitly open it first. To use it, the table must not be open anywhere, since PACK needs exclusive access. Among other things, this means that you can’t combine the TableName and IN clauses, because having the table open in a work area prevents it from being opened exclusively for packing. Like a good citizen, VFP cleans up after itself and the table is closed after it’s packed. Would that all our code was as careful.

Usage

lIsDeleted = DELETED( [ cAlias | nWorkArea ] )

DELETED() tells you whether a record has been marked for deletion. If so, it returns .T. By default, DELETED() refers to the current work area, but you can pass it an alias or work area number to check the status of the current record in another work area.

DELETED() can be very handy in FOR clauses, letting you do things like:

BROWSE FOR NOT DELETED()

to see all the current records in a table.

Usage

SET DELETED ON | OFF
cDeletedSetting = SET("DELETED")

The final members of this group are the most confusing. SET DELETED is extremely useful, but equally counter-intuitive. This command tells other commands whether or not to pay attention to records marked for deletion. SET DELETED ON indicates that marked records should be ignored (that is, turn off the deleted records). SET DELETED OFF says to include the marked records (that is, turn them on). It’s only recently that we’ve been able to use this command without looking it up first. (Even the Microsoft/Fox technical writers have trouble with this one. The FoxPro 2.5 Language Reference contained an error that made both options sound like SET DELETED ON.)

Setting DELETED ON is similar to setting a filter. It’s as if the deleted records don’t exist. You can issue commands with a scope of ALL and have the deleted records ignored. Many applications operate with DELETED ON all the time.

For a long time, the prevailing advice was that working with SET DELETED ON meant that every table should have an index tag with a key of DELETED(). Rushmore uses the tag to optimize elimination of deleted records. Otherwise, result sets have to be checked sequentially for records marked for deletion. It doesn’t matter whether or not any records are actually deleted. Only the SET DELETED choice was thought to matter. (See “Faster than a Speeding Bullet” for more on this.)

However, an article by Chris Probst in the May 1999 issue of FoxPro Advisor showed that, in some network situations, a tag on DELETED() can slow things down rather than speed them up. The issue is bringing the index information across the network to figure out which records you want.

SET(“DELETED”) returns either “ON” or “OFF” to indicate the current setting. It’s handy for saving the old setting when you need to change it.

Example

cOldDeleted=SET("DELETED")
SET DELETED OFF
* Find a deleted record, perhaps to recycle it
LOCATE FOR DELETED()
RECALL NEXT 1
* Stuff some data into it
GATHER MEMVAR
* Now clean up
SET DELETED &cOldDeleted

See Also

Delete-SQL, Reindex, Set, Set Optimize