Further work on the fault analyser (PrioXRef) - beware of linked files

I mentioned the other day a 270 line Priority procedure that I used as a test for the cross referencer. This procedure was at the proof of concept stage: it did what it was supposed to do but made several assumptions. I improved it to be more general, a conceptual change that required many changes throughout the procedure, that amongst other things builds a new purchase part with specific characteristics. At the end of the procedure, a purchase order is created that includes this new part. As such, the part needs to have a price in the supplier's price list based on the base part of the new part - whilst creation of the purchase order was part of the POC procedure, addition of the part to the price list wasn't.

After adding the appropriate code for the price list, I saw that it wasn't working, leading to a painful debugging session before the penny dropped. In the query that retrieved the price of the base part, I was using the table PART and this was what was causing the problem - PART was linked and of cause the actual part that I was using wasn't in the linked table. What made this error particularly galling was that I had already documented this problem.

As happens frequently these days, I woke up in the middle of the night. After dealing with my bladder, the first thought that popped into my head was that PrioXRef doesn't deal with 'UPDATE' and 'DELETE' statements; the second thought was how I could get the program to deal with the linked table problem. Unfortunately, this problem occupied my mind for quite some time and I had to take cognitive steps to banish it so that I could resume sleeping.

At the moment, I don't think that there is a way of showing this as a fault. How is PrioXRef supposed to know that the part that I am using as a parameter is not in the linked table? Of course, the problem would not exist if I had the internal part number of :SEARCHNAME and then I wouldn't have to include the PART table in this query. 

/* Get price from supplier's pricelist */                          
SELECT SPARTPRICE.PRICE, SUPPRICELIST.SUPPLDATE, 
SUPPRICELIST.SUPPLIST 
INTO :PRICE, :SDATE, :SLIST                                                                                       
FROM SUPPRICELIST, SPARTPRICE, PART, SUPPLIERS                      
AND SUPPRICELIST.SUP = SUPPLIERS.SUP 
WHERE SUPPRICELIST.SUPPLIST = SPARTPRICE.SUPPLIST                   
AND SPARTPRICE.PART = PART.PART                                     
AND SUPPLIERS.SUPNAME = :SUPNAME                                    
AND PART.PARTNAME = :SEARCHNAME 
ORDER BY 2 DESC;  

The only idea that I have at the moment is to display a warning like "[line number] warning: PART is linked". In fact, whilst PrioXRef knows that on line X that table PART is linked and is then unlinked on line Y, it can't use that information as it ignores SELECT statements. That's not exactly true: SELECT is not a keyword that triggers some handling, but INTO and FROM are such keywords. I should add some code to the FROM handling that checks whether any of the following tables are stored in the binary tree of identifiers. The FROM code should continue to be in effect until the next keyword is found (there may not be a WHERE clause, ORDER BY or GROUP BY in the query). PrioXRef has to check that the token following each part is not an alias (the above problem was solved by aliasing PART which causes the real table to be joined).

This might require a rewrite of the tokeniser as at the moment it does not save the token terminator (this would be a comma in the above example) and so getting the alias may cause the next table name to be obscured. On the other hand, there is code that pushes the next token back into the current text line to be parsed if it's not the expected token. Rewriting the alias detection code should be rewritten and changed to be a function so that it can be called at several different places.

A few people have received PrioXRef, but I have yet to receive any comment bar 'Thanks' and 'Nice idea'.