Indexes are the often overlooked workhorses of an Alpha Five database application. An index determines the order and inclusion of records within a given view, and provides a tremendous speed boost to an application. Rather than having to search through an entire table or set to find records that match a given criteria, Alpha Five can use indexes which already know which records meet the definition built into the index. Indexes are updated 'on the fly' as a user enters, edits or deletes records within a table and hence should always up to date. Because the index is pre-built and up to date, when the user wants to view a given sub-set of records, based on a given index, the Alpha Five system can almost instantaneously present them.

Alpha Five provides a wide array of tools to build, use, and manipulate indexes. Three new functions, added in build 1549-2035 released January 4, 2005, allow you to take your Alpha Five applications to a new level of professionalism. These three functions, Get_Index_Definitions(),Create_Indexes(), and Indexes_Match_DefString() provide the functionality to be able to check for the existence of indexes, make sure the index definitions are exactly what you, as the developer expect, and create or redefine the indexes if necessary.

Take the following scenerio: You develop an application. As part of your preparation for its delivery, you use the new Get_Index_Definitions() function and create a file of the index definitions you have created and deem necessary to the application. After the application is in the hands of your client, they use their copy of the full version of Alpha Five to modify one of your defined indexes. As part of the autoexec script, you have used the new Indexes_Match_DefString() function to check the current indexes. This function finds the index definition that the end user has changed or deleted. Your routine then uses the Create_Index() function to re-create the missing or modified index.

Instead of an error or slow record retrieval, your application has 'self-healed' itself and the robustness of your application has been greatly improved, not to mention the lack of an emergency support call to you the developer!

This month Selwyn Rabins, co-owner of Alpha Software, brings to the Alpha community two Xbasic routines to make sure your indexes exist. The first script takes a 'snapshot' of your index definitions in a given database and stores the definitions in a text file within your database directory. This file's name will be the same as your database name with an extension of: '.TableInformation'.

The second Xbasic script that Selwyn gives us is a routine that can be run in your in-use database that checks all the table indexes against this text file created by the first script. If there are missing indexes or indexes where the definition has been changed, this script reconstructs or repairs the index.

This script could be launched and run manually from a utilities menu or run automatically from an autoexec script or any other event that makes sense.

Please be aware that these scripts are dependant upon several new functions that were added to Alpha Five version 6 in build 1549-2035 that was released January 4, 2005. The new functions are:

Get_Index_Definitions() Function - Returns a CRLF string of the definitions of all indexes (i.e. index tags) for a table.

Create_Indexes() Function - Creates indexes for a table based on a CRLF definition string that defines the indexes to be created. This function is a wrapper around the low level .Index_Create_Begin(), .Index_Add() and .Index_Create_End() functions.

Indexes_Match_DefString() - Checks the indexes in a table and compares them against a previously created index definition string. Allows you to tell if index tags are missing, or if index tags have been added. Used in conjunction with Get_Index_Defintions(), and Create_Indexes(), this function allows you to re-create the indexes for a table if they no longer match the required indexes.

(You can either copy and paste the scripts directly from this page or click HERE to download a text file containing the two scripts)

The following two scripts allow a user to take a snapshot of all of the indexes in all of the tables in their application, and then check the actual indexes against the index definitions stored in the snapshot.

Script 1 loops through all of the table in a database and stores the index definitions in a text file on disk. The text file is the same as the Database name but it has a ".TableInformation" extension.

'get a list of all tables
dim tables as c
tables = a5.Table_Enum()
dim td as p
'create an array to hold the index information for each table
dim count as n
count = line_count(tables)
dim td.TableInfo[count] as p
'initialize the array
td.TableInfo.initialize_properties("TableName",tables)

'put up a progress dialog box.
DIM pDlg1 as {waitdialog}
pDlg1.Create(3,"Percent")
pDlg1.Set_Title("Please Wait")
pDlg1.Set_Message("Storing index information. Please wait...")

dim i as n
dim table_i as c
dim indexInfo_i as c
dim sourceType_i as c
for i = 1 to count
    table_i = td.TableInfo[i].tablename
    'check the table type - we don't want to bother with shadow or linked tables - only care about Native tables
    sourceType_i = table.get_source_type(table.filename_get(table_i))
    td.TableInfo[i].sourceType = sourceType_i
    pDlg1.Set_Bottom_Message(table_i)
    if sourceType_i = "Native" then
        td.TableInfo[i].indexDefinitions = get_index_definitions(table_i)
    else
        td.TableInfo[i].indexDefinitions = ""
    end if
next i

'close the progress dialog
pDlg1.close()

dim fn as c
fn = file.filename_parse(a5.Get_Name(),"dpn") + ".TableInformation"

'save the variable that contains the information to the file on disk.
'before you can save the variable, you have to convert it to a string value
'using property_to_string()
save_to_file(property_to_string(td),fn)
 

Script 2 is used to check the indexes in the tables against the saved index definitions:

dim fn as c
fn = file.filename_parse(a5.Get_Name(),"dpn") + ".TableInformation"
if file.exists(fn) = .f. then
ui_msg_box("Error","Saved index information file not found.")
goto skip
end if

dim txt as c
txt = get_from_file(fn)
dim td as p
'restore the array that contains the index information from the information stored on disk.
property_from_string(td,txt)


dim i as n
dim count as n
count = td.TableInfo.first_empty()-1

'put up a progress dialog box.
DIM pDlg1 as {waitdialog}
pDlg1.Create(3,"Percent")
pDlg1.Set_Title("Please Wait")
pDlg1.Set_Message("Checking indexes. Please wait...")

dim tablesWithErrors as c = ""

dim index_info_i as c
dim table_i as c
dim p as p
for i = 1 to count
    index_info_i = td.TableInfo[i].indexDefinitions
    table_i = td.TableInfo[i].tablename
    pDlg1.Set_Bottom_Message(table_i)
    'only restore indexes if the table type is a native table
    if td.TableInfo[i].sourceType = "Native" then
        P = indexes_match_defstring(table_i , index_info_i)
        if p.missingIndexTags <> "" then
            tablesWithErrors = tablesWithErrors + table_i + crlf()
            Create_Indexes(table_i,index_info_i)
        end if
    end if
next i

'close the progress dialog
pDlg1.close()


if tablesWithErrors <> "" then
    ui_msg_box("Notice","Missing indexes were re-created in the following tables: " + crlf()+ tablesWithErrors)
else
    ui_msg_box("Notice","No missing indexes were found")
end if
skip: