Hi Al

<snip>

>>Limiting this discussion to the scenario I outlined, how can having locate/SQL compare the index values against the records and seek (which only uses the index) compare the index entry against the actual data be opening up a can of worms? Except for a small performance hit, there seems to be little risk.
>>
>
>I was clearly reading too much into "SET INTEGRITY" - maybe it should be named "SET DETECT_ORPHAN_INDEX_ENTRIES" instead. If we limit it strictly to the problem your code outlines then sure, such a setting could alert users of an index problem.
>
>What I was pointing out was not the case of a filtered index, where we can expect table records to not appear in the index, but rather the case where a table has a record added but the index doesn't get updated. Most often due to WS, network or server hiccups and the fact that local and network file systems are not transactional. While AFAIK I have never encountered your error, I've hit mine far more often than I'd like. This is the "false negative" counterpart to your "false positive" example. I was thinking that anything named "SET INTEGRITY" should naturally handle both problems.
>

I can't see any way to handle that. User A adds a record to a table with index tags. Before the indexes are updated, User A's PC freezes/crashes. User A reboots. The only way I've ever found to deal with that is to do the following from the command window

1) open the table manually
2) navigate to the record
3) do a scatter/gather, which updates the indexes.

>>>
>>>2. Philosophically, I tend to think once you get corrupted files, then all bets are off. Your case plus the one I outlined above are only 2 of many different possible corruption scenarios. Just how good should we expect SET INTEGRITY ON to be?
>>
>>Corruption of the index can happen anywhere in the file, potentially leaving most of it intact. In that case, most bets are still on. The treatment is still the same, detect the corruption and rebuild the index. We have no way to detect the corruption of the index. Scanning the entire index is slower than rebuilding the index.
>
>By "all bets are off", I meant that even in the case where VFP can still open the index, a "mostly OK" index is still unreliable, and that means it's really no good at all.
>
>I was under the impression you would want to get VFP to delete the orphan index entry if detected, rather than rebuild the index entirely. While this could be done in a multi-user environment without exclusive use of the table/index, I agree that as soon as *any* corruption is detected, a complete index rebuild is preferable.
>

Actually, VFP could update the orphan index entry by reprocessing the index expression on that record. *That* might lead to a can of worms! I've thought of a refinement. SET INTEGRITY TO 0 means do what we have now, SET INTEGRITY TO 1 means just report an error. SET INTEGRITY TO 2 means attempt to replace the orphan index entry with a new one. UDFs in the index expression may make this an impossible request, but in that case, the developer would have to use SET INTEGRITY TO 0/1.

>>
>>
>>
>>>
>>>3. What about multi-user scenarios? Is it possible spurious errors might crop up because one station is adding or deleting records while another is using a search function with SET INTEGRITY ON?
>>
>>Good questions.
>>
<snip>
>>The repro code shows that record "C" which is in the restored corrupt.cdx is never displayed by LOCATE / SQL. I can't see how we could catch index corruption before the bad records are dropped. Only the VFP Team could do that.
>
>I mainly threw this question out to point out that network file systems are complex, and that someone (probably on the Fox team) would need to go through all possible lock and cache scenarios to make sure false positives wouldn't occur.

Agreed.