Packing Your Table

One of the most common needs for database programmers is to purge deleted records from their databases. Some DBMS (such as dBase) do not actually remove deleted rows in tables. They simply flag them as deleted, and hide them from your programs. But as time passes, the proportion of space taken by deleted records will cause performance degradation.

However, purging deleted records is something one must be very careful about. For one thing, it usually cannot be done while anyone else is using the table. For another, an error during the process can cause damage.

From a design point of view, it is wise to consider which tables in a system have high activity. Those tables may be able to be placed on the client workstation because they are personal to the client, in which case exclusive access is easy to accomplish, and finding strategic points where packing is safe is also easy. On the other hand, if the table must be shared, it is wise to either put packing in the hands of the system administrator or some other entity which can be sure of blocking access to the database while packing is being done.

From your program's point of view, it may be impossible to determine whether the underlying DBMS requires packing. SQL does not provide a PACK statement, and none of the standard C++ Builder database components support it either. Thus, again, it is perhaps best to avoid packing in an application and leave it for an administrative utility.

However, if you have a table which sees so much activity that it must be packed on the fly, then you must identify key points where packing can seem appropriate to the user. For instance, if the table is cleared, you may wish to pack at that time. Or you may wish to keep a count of deleted records, and pack when that number becomes too large.

The first solution to the packing problem is to use a third party database component which supports it. The Infopower++ components are an example. If you use this, then you will need simply to establish exclusive access to the table and call the Pack method.

Table->Active = false;
Table->Exclusive = true;

try
{
   Table->Active = true;
   Table->Pack();
   Table->Active = false;
   Table->Exclusive = false;
   Table->Active = true;
}
catch (EDatabaseError &DatabaseError)
{
   // Can't get exclusive access or can't pack
};

If you have to create your own Pack, you can use a similar method, in conjunction with TBatchMove. TBatchMove lets you copy a table in a single operation. We need two copies, and EmptyTable() and two tables.

The following is the interface for a lightweight test program that performs this operation

whose .dfm is

object MainForm: TMainForm
   Left = 2
   Top = 155
   Width = 696
   Height = 310
   Caption = 'PackTest'
   Color = clBtnFace
   Font.Charset = DEFAULT_CHARSET
   Font.Color = clWindowText
   Font.Height = -13
   Font.Name = 'MS Sans Serif'
   Font.Style = []
   OldCreateOrder = False
   PixelsPerInch = 120
   TextHeight = 16
   object PackFromGrid: TDBGrid
      Left = 0
      Top = 0
      Width = 329
      Height = 241
      DataSource = PackFromSource
      TabOrder = 0
      TitleFont.Charset = DEFAULT_CHARSET
      TitleFont.Color = clWindowText
      TitleFont.Height = -13
      TitleFont.Name = 'MS Sans Serif'
      TitleFont.Style = []
   end
   object DBGrid1: TDBGrid
      Left = 352
      Top = 0
      Width = 329
      Height = 241
      DataSource = PackToSource
      TabOrder = 1
      TitleFont.Charset = DEFAULT_CHARSET
      TitleFont.Color = clWindowText
      TitleFont.Height = -13
      TitleFont.Name = 'MS Sans Serif'
      TitleFont.Style = []
   end
   object PackButton: TButton
      Left = 304
      Top = 248
      Width = 75
      Height = 25
      Caption = 'Pack'
      TabOrder = 2
      OnClick = PackButtonClick
   end
   object PackFrom: TTable
      DatabaseName = 'YourAlias'
      Exclusive = True
      TableName = 'PackFrom.DBF'
      Left = 16
      Top = 248
   end
   object PackFromSource: TDataSource
      DataSet = PackFrom
      Left = 56
      Top = 248
   end
   object PackTo: TTable
      DatabaseName = 'YourAlias'
      Exclusive = True
      Left = 144
      Top = 248
   end
   object PackToSource: TDataSource
      DataSet = PackTo
      Left = 192
      Top = 248
   end
end

Note that the name of the destination table is not supplied here. The code for the button click is:

//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop

#include "MainFormUnit.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TMainForm *MainForm;
//---------------------------------------------------------------------------
__fastcall TMainForm::TMainForm(TComponent* Owner)
   : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TMainForm::PackButtonClick(TObject *Sender)
{
    PackButton->Enabled = false;
    Screen->Cursor = crHourGlass;

    PackTo->DatabaseName = PackFrom->DatabaseName;
    PackTo->TableName = "Pack"+PackFrom->TableName;
    PackTo->Exclusive = true;
    // PackTo is not made active since it may not exist until the copy operation

    PackFrom->Exclusive = true;
    PackFrom->Active = false;

    TBatchMove *Pack = new TBatchMove(NULL);
         
    Pack->Source = PackFrom;
    Pack->Destination = PackTo;
    Pack->Mode = batCopy;
    Pack->Execute();

    PackFrom->EmptyTable(); // This preserves the fields and indexes

    Pack->Source = Pack->Destination;
    Pack->Destination = PackFrom;
    Pack->Mode = batAppend; // This preserves the fields and indexes
    Pack->Execute();
         
    PackFrom->Exclusive = false;
    PackFrom->Active = true;
    PackTo->Active = true; // show in grid

    // Do what you want with the PackTo, then

    delete Pack;

    PackButton->Enabled = true;
    Screen->Cursor = crDefault;
}
//---------------------------------------------------------------------------

Please note that you can also use the BatchMove method of TTable to perform the same functions shown above with the TBatchMove object. However, in all cases, make sure to have removed / disabled any filters prior to performing the operation.

Please note that there will be no indexes on the destination table. However, they should survive in the original after the return copy.

The batCopy mode does not copy indexes. While this example uses an EmptyTable() combined with batAppend to ensure preservation of the indexes, you can try a batCopy and a set of AddIndex statements. However, note that some field definitions were found in tests fo the above in that mode to have been damaged.