This is a much-updated version of some code first published in 2019. Sorry, I couldn't figure out how to update the original posting.
Alpha5 DBF database structures are known to lose indexes from time to time. One of the reasons for lost indexes is discussed later in this document. Lost indexes cause issues for Alpha5 applications that use and expect specific index files to be present.
This package provides an Index dictionary for your application. It allows the end user to rebuild missing indexes. It enables your application to intelligently deal with indexes that may be missing, allowing the end user to rebuild them without your intervention.
This package also assists the developer in keeping track of the indexes required and the frequency of their use. The IDX dictionary will even scan your code to verify index usage.
This software has only been tested in version 12 of Alpha Anywhere. Most of it should work in the earlier version, but I suspect the progress() class used for the status bar during IDXrebuild may fail. Some stuff was changed with classes between Alpha 5 versions 10, 11 and 12. Sorry if you run into problems with the status bar, you are on your own, but it should not be that hard to remove the dependence on the progress() class.
Installation
------------
The ZIP consists of a single text file idx_XX.txt file, where XX is the version number. The idx_XX.txt file contains four functions and a single class. The ZIP file also includes this readme file.
Right, Click on an empty area of the Code Tab to deselect any selected script and select Import from the pop-up menu.
Import the IDX.txt file, selecting 4 Functions and Class listed below.
From the Interactive Window, enter the following to build the required table, form an object, and open the IDX form used to administer the Index Dictionary.
idx()
Most of the time, the function will create the table and form without issue. You may need to try the IDX() function a second time when it does not. In the worst case, delete the IDX.DDF from the Table tab of the control panel. Exit Alpha Fice and delete any remaining IDX.* support files from the database folder. Then restart Alpha Five and try again.
When the form is first displayed, a blank record will be created. It should be deleted after populating the dictionary using the <Build Dictionary> button.
After initially building the dictionary, use the <Update Dictionary> to keep all indexes from the dictionary that may have gone missing since the last rebuild.
IDX form is not intended for use by the developer; I would not recommend you give the end user access to it. However, the end user should be given access to the IDXrebuild() function.
FUNCTIONS
---------
IDX() is a single function that provides all the code run from the buttons on the IDX form. IDX() also builds the DBF table and form.
IDXPUT() is a function that should be used when selecting an index in your code. It works with the Index Dictionary (IDX.DBF) to accomplish the following;
- Selects the index using the full name specified, unlike the equivalent A5 methods for index selection
- If the index is missing, build a temporary sort order if possible and return a pointer to it
- Flag tables that require index rebuild if an index is missing. These tables are pre-selected when the IDXREBUILD() function is used.
- Gather statistics on the index's usage.
See the comments in the IDXPUT() function for details on its use.
IDXREBUILD() will rebuild indexes. It can be called from your application and used by the end user. See the comments at the start of this function for details on its use.
IDXSET() is a function to set the primary index for a table or browse. It uses IDXPUT() and will display any error messages.
PROGRESS() is a Class that provides a progress dialogue used by the Reindex function.
A5 Index Issues
---------------
The introduction indicates that Alpha5 can lose indexes all on its own. One cause of this is indexing expressions that cannot be evaluated. When Alpha5 saves a change to a record, it next has to update indexes for that table. Suppose an index expression cannot be evaluated. In that case, Alpha5 cannot back out of the save operation (it can't do Atomic transactions), and the index is no longer valid, so it is dropped. The index MAY reappear on the subsequent table opening if the expression is valid once again.
The issue is easily demonstrated by defining a global variable with a value and using it in an indexing expression. Update some records that use the index. Next, delete the Global variable and update a record in the table, and you will see the index be deleted.
Alpha Five will build many indexes on its own. Their names are easily recognized, and some are not added to the Index Dictionary. Look at the IDX() function to see a list of indices excluded from the Index Dictionary.
Other indices built by Alpha Five are required for linking tables in sets. These will have unique names based on the fields involved, and these will be included in the index dictionary.
Alpha Five will, at times, build indexes that contain commas in the Index Name. The Alpha Five routines this package uses will build indexes named with commas. Some of the index functions provided by Alpha Five will not accept Index names with commas. Indexes can be renamed in the index dictionary, removing the commas. The indexs can then be rebuilt using <Rebuild Index> button on the IDX form, removing the indexs names with commas.
Before building an index for its internal use, Alpha5 will see if an index with the correct definition exists. If one does, it will be used. So, if an index that Alpha5 would build with commas in the name is created or recreated under a different name, it will be used, and the issue of index names with commas will be avoided.
Alpha5 DBF database structures are known to lose indexes from time to time. One of the reasons for lost indexes is discussed later in this document. Lost indexes cause issues for Alpha5 applications that use and expect specific index files to be present.
This package provides an Index dictionary for your application. It allows the end user to rebuild missing indexes. It enables your application to intelligently deal with indexes that may be missing, allowing the end user to rebuild them without your intervention.
This package also assists the developer in keeping track of the indexes required and the frequency of their use. The IDX dictionary will even scan your code to verify index usage.
This software has only been tested in version 12 of Alpha Anywhere. Most of it should work in the earlier version, but I suspect the progress() class used for the status bar during IDXrebuild may fail. Some stuff was changed with classes between Alpha 5 versions 10, 11 and 12. Sorry if you run into problems with the status bar, you are on your own, but it should not be that hard to remove the dependence on the progress() class.
Installation
------------
The ZIP consists of a single text file idx_XX.txt file, where XX is the version number. The idx_XX.txt file contains four functions and a single class. The ZIP file also includes this readme file.
Right, Click on an empty area of the Code Tab to deselect any selected script and select Import from the pop-up menu.
Import the IDX.txt file, selecting 4 Functions and Class listed below.
From the Interactive Window, enter the following to build the required table, form an object, and open the IDX form used to administer the Index Dictionary.
idx()
Most of the time, the function will create the table and form without issue. You may need to try the IDX() function a second time when it does not. In the worst case, delete the IDX.DDF from the Table tab of the control panel. Exit Alpha Fice and delete any remaining IDX.* support files from the database folder. Then restart Alpha Five and try again.
When the form is first displayed, a blank record will be created. It should be deleted after populating the dictionary using the <Build Dictionary> button.
After initially building the dictionary, use the <Update Dictionary> to keep all indexes from the dictionary that may have gone missing since the last rebuild.
IDX form is not intended for use by the developer; I would not recommend you give the end user access to it. However, the end user should be given access to the IDXrebuild() function.
FUNCTIONS
---------
IDX() is a single function that provides all the code run from the buttons on the IDX form. IDX() also builds the DBF table and form.
IDXPUT() is a function that should be used when selecting an index in your code. It works with the Index Dictionary (IDX.DBF) to accomplish the following;
- Selects the index using the full name specified, unlike the equivalent A5 methods for index selection
- If the index is missing, build a temporary sort order if possible and return a pointer to it
- Flag tables that require index rebuild if an index is missing. These tables are pre-selected when the IDXREBUILD() function is used.
- Gather statistics on the index's usage.
See the comments in the IDXPUT() function for details on its use.
IDXREBUILD() will rebuild indexes. It can be called from your application and used by the end user. See the comments at the start of this function for details on its use.
IDXSET() is a function to set the primary index for a table or browse. It uses IDXPUT() and will display any error messages.
PROGRESS() is a Class that provides a progress dialogue used by the Reindex function.
A5 Index Issues
---------------
The introduction indicates that Alpha5 can lose indexes all on its own. One cause of this is indexing expressions that cannot be evaluated. When Alpha5 saves a change to a record, it next has to update indexes for that table. Suppose an index expression cannot be evaluated. In that case, Alpha5 cannot back out of the save operation (it can't do Atomic transactions), and the index is no longer valid, so it is dropped. The index MAY reappear on the subsequent table opening if the expression is valid once again.
The issue is easily demonstrated by defining a global variable with a value and using it in an indexing expression. Update some records that use the index. Next, delete the Global variable and update a record in the table, and you will see the index be deleted.
Alpha Five will build many indexes on its own. Their names are easily recognized, and some are not added to the Index Dictionary. Look at the IDX() function to see a list of indices excluded from the Index Dictionary.
Other indices built by Alpha Five are required for linking tables in sets. These will have unique names based on the fields involved, and these will be included in the index dictionary.
Alpha Five will, at times, build indexes that contain commas in the Index Name. The Alpha Five routines this package uses will build indexes named with commas. Some of the index functions provided by Alpha Five will not accept Index names with commas. Indexes can be renamed in the index dictionary, removing the commas. The indexs can then be rebuilt using <Rebuild Index> button on the IDX form, removing the indexs names with commas.
Before building an index for its internal use, Alpha5 will see if an index with the correct definition exists. If one does, it will be used. So, if an index that Alpha5 would build with commas in the name is created or recreated under a different name, it will be used, and the issue of index names with commas will be avoided.
Comment