Index Tip?

[SNusa]

Re: Index Tip?

Thanks Martin;

Your code got me to thinking about index names and testing for truncation. This made me wonder whether it might even be a good idea to standardize the length of the index name across all user defined indexes? (ie: pad all index names with an underscore out to either 8 or 10 characters.) ~ This would allow you to add code to test for truncations & even "regenerate" (& save) the correct index names automatically in the unlikely event one becomes truncated.)

Also, in the first script, usage of the "|" (pipe) characters are throwing me off.... example: "__A5_RECOR|RECNO()||"
(I'm assuming that example above represents one of several ways the a5 generated indexes are represented/stored?)

[martinwcole]

Re: Index Tip?

Thanks Martin;

Your code got me to thinking about index names and testing for truncation. This made me wonder whether it might even be a good idea to standardize the length of the index name across all user defined indexes? (ie: pad all index names with an underscore out to either 8 or 10 characters.) ~ This would allow you to add code to test for truncations & even "regenerate" (& save) the correct index names automatically in the unlikely event one becomes truncated.)

the rebuild function DOES rebuild to the original name, thats the whole point of the function

[SNusa]

Re: Index Tip?

the rebuild function DOES rebuild to the original name, thats the whole point of the function

Hi Martin;

Yes, I hadn't gotten that far parsing the code on the second function. ~ I was thinking about the initial function as it generates the "backup table" of indexes. (and got hung up on usage of the "|" character) ~ I was thinking in terms of being able to automatically "catch" an unexpected, preexisting truncation and fix it (automatically) during the initial save.

Without knowing the length of the index name, the first function is "blindly" assuming the state of the existing user generated indexes are fine, correct?
(Hence making sure the indexes are not already truncated requires manual inspection without some sort of universal length "standardization".)

[martinwcole]

Re: Index Tip?

PS:

while I don't recommend using the --- with padlefet, if you did you would want to pad right, not left - and they truncate after 10 characters, not 8

[SNusa]

Re: Index Tip?

PS:

while I don't recommend using the --- with padlefet, if you did you would want to pad right, not left - and they truncate after 10 characters, not 8

Yes & thank you. For quite some time, I have adopted padding right with two underscores after using only the field name syllables. (Field "keyfob" becomes index "kyfb__".) ~ But I also had read elsewhere on this forum that some other issues regarding indexes can occur past the 8'th character. ~ I believe it was Ted Giles who made reference to experiencing this.

[Mark Williams]

Re: Index Tip?

Martin,

I set these functions up in my database and they work great until I try to access my database from a shadow. When trying to run it from a shadow, it gives an error on startup, 'tbl.tablename subelement not found' and will not load the database.

If I run the database directly from the server, it runs fine.

I've tried running the 'indexes_rebuild' directly from the server and running it from the shadow copy and still receive the above error either way.

I did this with a backup copy, so no harm has been done but would like to be able to utilize this.

Thanks

[martinwcole]

Re: Index Tip?

hmmm - not sure what the issue is but I have been using that script for several years. It could be some of your tables are not in the same folder, (or in a subfolder.)

[Mark Williams]

Re: Index Tip?

hmmm - not sure what the issue is but I have been using that script for several years. It could be some of your tables are not in the same folder, (or in a subfolder.)

I've got it narrowed down to one table that is generating the error. I will try to figure out what it doesn't like and report back.

Thanks

[johngtatp]

Re: Index Tip?

Martin, how do you accomplish the auto rebuild. Do not all users have to be out of the system? Is this setup on a ontimer event on an open form, or some other method?

[martinwcole]

Re: Index Tip?

I put it as an option for some or all users, in a button
it will run whether others have the app open or not - and give a list of tables it could not rebuild

but mainly, I have a maintenance adb (in the same folder) that runs on the server from a windows task scheduler, that does various maintenance tasks, and then closes Alpha.

[Mark Williams]

Re: Index Tip?

Ok... Here's what I have figured out while trying to implement these functions into my database...

If I run the application directly from the server, i can run the indexes_create() and indexes_rebuild() functions and everything works fine.

I can then create a shadow copy and it runs fine.

However, as soon as I rerun the indexes_rebuild() function, either directly from the server copy or from the shadow copy, my shadow copy is missing multiple indexes from multiple tables.

I can still load the app directly from the server and all of the indexes are in tact but the shadow copy is missing multiple indexes.

The only way I can get the shadow copy to run again is to delete it and recreate a new shadow. It will again run fine until I run the indexes_rebuild() again.

Make any sense?

[MoGrace]

Re: Index Tip?

Hi Mark,
There shouldn't BE any index files (*.CDX) in the shadow folder.

[CALocklin]

Re: Index Tip?

Some years ago I found that indexes were occasionally getting put on the workstation for certain clients. I don't recall the specific circumstances now but the exact same routine worked for must customers while causing a problem for one or two others. This is why the AIMS Index Rebuild addon now includes a routine to automatically remove any cdx files from the workstation unless the matching dbf file is not shadowed. The routine was originally created back in A5v5 so I have no idea what year or version the problems occurred. All I know is I haven't had any complaints since.

You might also want to change the test for __A5_Record__ indexes. I've often seen other names like __A5_Reco0, __A5_Reco1, etc. (Or maybe it was __A5_Rec01 and __A5_Rec02 - I'm not sure.) In fact, I once saw this go all the way up to __A5_Rec15. Also, that index should always be created. Again, I don't recall the issue but I've seen issues when that index was not rebuilt - rare but it has happened.

[Mark Williams]

Re: Index Tip?

Hi Mark,
There shouldn't BE any index files (*.CDX) in the shadow folder.

That is the problem. When the indexes_rebuild() function is run from a shadow copy, it is writing the cdx files into the shadow folder instead of the main server folder.

If I manually move the cdx files from the shadow folder into the server folder, all is well again.

[CALocklin]

Re: Index Tip?

I think the problem is that the create_indexes routine is not opening the table on the server. In my routine, I explicitly open the main data table - not the shadowed copy. Maybe the create_indexes() routine is supposed to handle the shadow issue internally but that doesn't seem to be happening. I know that plain a5.table_enum() does not include the path and I'm betting that a5.table_enum(1) would include the workstation path instead of the server path.

In fact, as I think about this more, that might be how I solved my original problem - by specifically opening the main data (server) table. The "remove indexes in the shadow folder" feature may have been a 'just in case'.

I notice now that the routine gets "mpath" and "datapath" but does nothing with them. That's kind of suspicious - and why two different variable names?
I use this in a LOT of scripts:
DIM datapath as C
IF a5.Get_Master_Path() = ""
datapath = a5.Get_Path()+chr(92)
ELSE
datapath = a5.Get_Master_Path()+chr(92)
END IF

Without copying the whole code and debugging it I'm not sure what happens but you should be able to make sure the full path and file name is included. If it still doesn't work, maybe there's an issue in the create_indexes() function that is ignoring the path.