Indexes removed and renamed

[tiago]

During the normal use of the application, some indexes are removed.

1-What can cause the indexes delete?

2-Is there a procedure to work with indexes?

3-Also some indexes are automatic renamed, and that cause an error when the index is called by the name.
Is there a way to solve the rename problem?

4- To try to deal with the delete and rename problem, I created a script to check indexes and create again if it is needed.
This script is called in autoexec, but we have problems to run this script in the shadow.
This is a high level function using indexes, first I tried a low level procedure using tbl.index_tag_add.
Anyone use a similar approach for this kind of problems?

' Reference: http://wiki.alphasoftware.com/~alphafiv/INDEXES_MATCH_DEFSTRING+Function
 
dim indexDef as C
dim matchIndex as P
 
' 1- Check indexes in subsc_charged_items table
 
'indexDef = get_index_definitions("subsc_charged_items") 
indexDef = <<%txt%
AUTO_NO|AUTO_NO||
AUTO_NO1|AUTO_NO||U
INV_REC|INV_REC+CHARGE_TYPE||
SUB_AUTO_N|SUB_AUTO_NO+CHARGE_TYPE+THIS_CHAR||
SUB_REF|SUB_REF|ISBLANK("INV_REC")|
SUB_REF1|SUB_REF+THIS_CHAR+CHARGE_TYPE||
SUB_REF2|SUB_REF||D
SUB_REF3|SUB_REF||
SUB_REF4|SUB_REF+CHARGE_TYPE+THIS_CHAR||
THIS_CHAR|SUB_REF+CHARGE_TYPE+THIS_CHAR+SERV_TYPE||D
__A5_RECORD__|RECNO()||
%txt%
 
matchIndex=indexes_match_defstring("subsc_charged_items", indexDef)
if matchIndex.Error=.F. .or. matchIndex.AdditionalIndexTags<>"" .or.  matchIndex.MissingIndexTags<>""
            Create_Indexes("subsc_charged_items", indexDef) 
end if

[Stan Mathews]

Re: Indexes removed and renamed

What can cause the indexes delete?

If Alpha can not evaluate the expression used in the index, the index will be dropped.

[Stan Mathews]

Re: Indexes removed and renamed

Is there a procedure to work with indexes?

Question is unclear.

[Tom Cone Jr]

Re: Indexes removed and renamed

An indextag will be deleted automatically if it cannot be evaluated at run time.

I avoid indextag names that end in a number. If Alpha needs to create an index for your application it will often create a tag name that ends in a number. I don't know for certain whether this could create a naming conflict, but I see the potential and avoid it.

[Stan Mathews]

Re: Indexes removed and renamed

Also some indexes are automatic renamed, and that cause an error when the index is called by the name.
Is there a way to solve the rename problem?

Unlikely any indexes are renamed. More probable that an index is dropped as mentioned earlier and a new one is created automatically as needed for a set linkage, etc.

[Stan Mathews]

Re: Indexes removed and renamed

One suggestion might be to try your script in the onactivate event for your forms (or maybe an ontimer event) but instead of

Create_Indexes("subsc_charged_items", indexDef)

simply throw up a message box alerting the user that the indexes are corrupt and have them note what they were doing immediately prior to seeing the message.

Might help you track down the cause.

[CraigSchumacker]

Re: Indexes removed and renamed

This was problematic for me in a very large application with a part information table. It would happen sporatically EVEN when there was no reason for it go belly up. (There was no reason for the evaluation to fail.)

Because the application was so large, and used by 30 users a day, I was never able to track down the root cause, if there actually was one on my end.

As the only solution possible, I created a script that used .index_name_get(), .index_create_begin() and .index_add() to recreate any missing indexes.

The downside is you must have FILE_RW_EXCLUSIVE set when opening the table before the script will proceed.

Frankly, I would rather see an error message telling the reason the index failed, rather than a random restructuring. It a little like an error in a script erasing the script.

From version 9 on, I resolved never to use the suite of LOOKUP() functions for that very reason.

[CraigSchumacker]

Re: Indexes removed and renamed

Stan:

Do you know if create_indexes() works when the table is open??? Usually, the table must be closed when new indexes are created...

Craig

[tiago]

Re: Indexes removed and renamed

Craig, I tested the script with a open table and it created the indexes.

[CraigSchumacker]

Re: Indexes removed and renamed

Wow! That's great!

Thanks!

[tiago]

Re: Indexes removed and renamed

Is there a procedure to work with indexes?

We are using 3 indexes that we have given a custom name.
We are only having problems with one of them.
I wonder what is different.

It is there a way to do a lookup without using indexes (even if it is slower) ?

[CraigSchumacker]

Re: Indexes removed and renamed

I most commonly open the table, and set a query. Definitely slower, but more stable.

That will probably change, now, based on this thread.

Of course, rebuilding indexes at run time may also slow things down, but only if that has to happen.

It says create_indexes() came out with version 6. Somehow, I missed the possibility that you might no longer need exclusive access...

ex:

payments_tbl = table.open("payments")

query.order = "Paydate"
query.filter = "paydate >= var->begin_date_d_v .and. paydate <= var->end_date_d_v" query.flags = ""
query.flags = ""

indx1 = payments_tbl.query_create()

record_count_1 = indx1.records_get()

etc...

[CraigSchumacker]

Re: Indexes removed and renamed

Turns out create_indexes() isn't in the local copy of version 9's help file. At least I don't feel so bad, now.

[martinwcole]

Re: Indexes removed and renamed

my experience is that when a table is open I cannot recreate indexes
a number of years ago Selwyn can up with a script that would read all the current indexes and save it to a text file - and then a way to read the text file and recreate the indexes - I modified and keep the indexes in a memo field in a table - works great

The issue Stan mentioned - that where you have an expression (as when creating a query) and Alpha cannot create the query using the expression, it will delete the respective indexes - has been around since 1998, at least.

Unfortunately, I know of no way to automatically determine what expression is causing the problem - extensive testing in a development copy would work, but be very time consuming - like run one of you scripts or query's, then see if it is still there.

[Stan Mathews]

Re: Indexes removed and renamed

I tested the same and it does not create the indexes. It doesn't throw an error message, just fails.

list = Get_Index_Definitions("aa")
? list
= ID|ID||U
TEST|NAME||
__A5_RECORD__|RECNO()||
'go to the table, delete the indexes, save, open a browse of the table
? create_indexes("aa",list)
= ErrorText = ""
HasError = .T.