Database Reindex

[Jeff Richards]

I have a button on a "dummy form" that re-indexes my database. However, sometimes if the indexes are badly corrupted, I have to right click on each table to get the indexes fixed. Why is that.
Here is my Script;
'Date Created: 29-Dec-2002 05:47:43 PM
'Last Updated: 29-Sep-2003 08:35:48 PM
'Created By :
'Updated By :
StatusBar.show()
'Update indexes for all of the tables in the Database.
omit_tables = ""

dim table_list as c
table_list = a5.table_enum(12)

if omit_tables <> "" then
tables_to_update = word_subtract(table_list,omit_tables,crlf())
end if

dim count as n
count = w_count(table_list,crlf())
dim i as n
dim tbl as p
for i = 1 to count
statusbar.percent(i,count)
table_i = word(table_list,i,crlf())
tbl = table.open(table_i)
tbl.update_production_index()
tbl.close()
next i
ui_msg_box("Index rebuilding complete","")

[Cheryl Lemire]

Re: Database Reindex

Jeff, how do you know this happens when the indexes are badly corrected, do you get some kind of error message or something when you run your script?

[Stan Mathews]

Re: Database Reindex

table_list = a5.table_enum(12)

should be

table_list = a5.table_enum()
table_list = a5.table_enum(4)
table_list = a5.table_enum(8)

I don't see where 12 can be used.

Flag
Optional. Default = Null.

Null = Table filename

4 = Full path and filename

8 = Quoted full path, and filename.

[Jeff Richards]

Re: Database Reindex

I believe that my script came directly from a script genie.
Can you please explain your answer. I appreciate it.
Thanks.

[Jeff Richards]

Re: Database Reindex

Cheryl,

Two ways I know the indexes are corrupted.
1- The links between the tables get screwed up
2- I will sometimes get a message that says something about corrupted indexes.

[Stan Mathews]

Re: Database Reindex

I believe that my script came directly from a script genie.
Can you please explain your answer. I appreciate it.
Thanks.

Take a look at the documentation for .table_enum(). The entry indicates that three values can be used inside the parentheses as parameters. 12 is not one of them.

Try the statement

? a5.table_enum(12)

in the interactive editor.

The result is gibberish.

[MikeC]

Re: Database Reindex

Jeff,
Are you sure this is all the code?? Besides what Stan pointed out there are some other oddities in it and is why I ask such as:
omit_tables is set to "" so what follows "if omit_tables<>""... will never run.

 
omit_tables = "" 
 
dim table_list as c 
table_list = a5.table_enum(12) 
 
if omit_tables <> "" then 
tables_to_update = word_subtract(table_list,omit_tables,crlf()) 
end if

Even if it did run, there is no mention of "tables_to_update" later on to be used anyway.

If the genie actually created this then I doubt it was intentional on Alpha's part.

[CALocklin]

Re: Database Reindex

Back to the original question:
"However, sometimes if the indexes are badly corrupted, I have to right click on each table to get the indexes fixed. Why is that."

If all the index definitions still exist, you shouldn't have to do that. HOWEVER, I expect your situation is more like this....

If the indexes are corrupted enough that some of them are missing (or the spelling gets messed up - but I've never seen that happen), you will have to rebuild them from scratch. Why? Note the name of the function you are using: update_production_index(). The key word here is "update". When indexes are lost, even the definition for the index is lost and it no longer exists in the production index (.cdx) file. The update_production_index() function doesn't magically know what your index definitions are. It has to read them from the .cdx file. And a result, the updating_production_index routine can only update indexes for the definitions it can find.

There are many ways you can create a "backup" for your index definitions. One way is to make a copy of the .cdx files with the correct definitions and create a routine that will copy them back (or copy them back manually) then run the update_production_index() function to bring them up-to-date. You can also build your own xbasic routine to capture and rebuild the indexes. Before Alpha decided to create some built-in routines to help with this process, I created an addon that will basically do it for you and can be run from any workstation. (Note: Rebuilding the indexes will always require exclusive access. There's nothing anyone can do about that. However, my routine will warn you if it is not able to get exclusive access and then continue on to the next table. Some routines I've seen will simply quit the first time an 'in use' file is encountered.)

FWIW: The update_production_index() function reads the index definitions from the .cdx files and rebuilds whatever it finds. If a previous definition no longer exists, it can't rebuild it. My addon consists of one function that reads your current index definitions and then builds a new function that actually contains your index definitions so it can rebuild (vs. update) them at any time. This new function is stored in your Code tab and is completely editable by you - although I don't recommend that unless you really know what you are doing. (The default name of the new function is Index_rebuild_auto().) The "read and build a new function" part of my addon must be run when the index definitions are all correct - typically right before distributing an update. Then the resulting Index_rebuild_auto() function, which is now part of your application, can be run at any time by users to rebuild the indexes. The button to start the rebuild would have a very complex xbasic script something like this:

Index_rebuild_auto()

If an update requires new index definitions, simply re-run the addon's Create_index_rebld_auto() function and it will rebuild the Index_rebuild_auto() function in your application.

[Jeff Richards]

Re: Database Reindex

Here is what I get in the interactive window
? a5.table_enum(12)
= c:\doc"uments and settings\richie\desktop\rg testing2\customer_vendor totals.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\transmit orders.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\a_rep ware_help.dbf"
\\lapt"op\rep ware\dummy.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\customers.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\products.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\transmit customer orders.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\reports.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\vendor_totals.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\vendorcustno.dbf"
\\lapt"op\rep ware\new customer.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\import.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\orders.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\company info.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\orders by rep group.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\vendors.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\orderdetails.dbf"
c:\doc"uments and settings\richie\desktop\rg testing2\cross tab cust_vend totals.dbf"

Is this incorrect?

[Stan Mathews]

Re: Database Reindex

What do you suppose happens when the other code enounters the first " in

c:\doc"uments and settings\richie\desktop\rg testing2\customer_vendor totals.dbf"

ie the

c:\doc"uments

It appears to me that both the options are being executed with one overwriting the other.


Full path and filename (then)
Quoted full path, and filename

or verse visa.

[CALocklin]

Re: Database Reindex

Incorrect? Depends on how you look at it.

- According to the Help file there are 3 valid arguments:
Null = Table filename
4 = Full path and filename
8 = Quoted full path, and filename.

In that case, using 12 is incorrect.

- The results of using 12 are to stick the quote mark after the 6th character. This makes it pretty useless unless you move the quote mark yourself so I suppose you could say this is incorrect. It's certainly in invalid/useless result. But, A5 doesn't support "12" as a valid argument so we have no reason to expect it to be correct.

I tested it with many different numbers and everything I tested will give one of the four results: The three results A5 specifies and the rather strange one that results from using "12" and some other numbers.

Does "12" return a result? Yes.
Is it a correct result? No.
Is it supposed to be a correct result? No.

This one case where adding the two arguments together does not result in a valid "combined" result.

[Jeff Richards]

Re: Database Reindex

I understand it now. It took me awhile, Thanks everyone for the help.

[CALocklin]

Re: Database Reindex

Hey Jeff, I just re-read my response and now it sounds rather snide - it wasn't intended that way. It just kinda developed that way as I was running my tests at the same time I was adding to the response.

The reason I did all the testing? Sometimes you discover something useful but undocumented as a result of questions like this.