Monday, October 18, 2004
How Does Foxfire! Build Joins
The build_from method generates a list of all tables involved in the request from filters, data items and more.
FFBUILDJ is responsible for actually generating the joins. Here's how it does it:
It does this by looking for each used table in the JOINS table and noting its Join Level. (searching by alias1)
If it can't find a join with alias1, it searches for alias2 and increasing the join level by one.
This is important because the JOIN LEVEL is what will be used to identify what is the driving table for the request. (the lowest one usually wins)
The maximum weight of all joins is stored in lnMaxPref.
** Why not just look at the max weight of the joins in the request's tables?
Ensure that all tables have join records in the system
Creates two arrays for tracking which tables have joins and which ones don't.
It identifies the Start table by looking for the first table in the unjoined tables array (which identifies all the tables used) and looks for a matching record with any of the other tables.
(this can be critical because if your request starts with a little used table but there is a jOIN in the array, it will START with that as the primary table for the join)
Update: this has been revised to look for all direct relationships right away once it knows the Start table. it does this by looking for relationships in order of the level in the array. So if your Customer file is at the top, it starts with THAT file and looks for relationships below it. This loop takes place right away and removes entries from the laUnJoined array.
NOTE: Put an exit in the search for empty line tables - it doesn't need to search through everything again. Time waster
If, by this time, the relationship hasn't been figured out.
The Start table is added as the first element of array laJoined here.
Now go through the weights listed here, seeing which tables can be DIRECTLY linked to the START table. (once again, if the WRONG start table is chosen, this could be a problem) In most cases, the lnMaxPref would be 1 so this loop would only be gone through once.
How do weights work? Well it starts at the lowest number (1) and looks for a relationship between any table in the Unjoined array and a table in the joined array that is LESS or equal to that number. If one isn't found, it then moves up to the next weight.
This is done in reverse order in the unjoined array.
If this uses Advanced SQL (??? How is this defined ??? )
it identifies the new join by calling j_gen_instjoin with no parameters
sf_alias is a separate array that ?????
This join is now added to a cursor called FFCJOINS
This is repeated for all the tables involved and the table is removed from the unjoined array.
At this stage, the unjoined array should be empty. If it's not, a warning is posted and the request is stopped.
In addition, a variable named lcJoinstring now contains essentially the WHERE clause if there were no outer joins being used here.
The FFCJOINS table is now sorted by Join Level (from the JOIN table) and the Alias 1.
This lets FF! know what the highest join level is.
*!*!*!!*!* BUG: Don't use ALLTRIM(STR() use PADL(joinlevel, 3) - this sets the WRONG level.
Do we still want to support nested Joins vs. sequential?
Sequential Version
The Nested
posted by andrew macneill at 7:16 am
Comment
FFBUILDJ is responsible for actually generating the joins. Here's how it does it:
It does this by looking for each used table in the JOINS table and noting its Join Level. (searching by alias1)
If it can't find a join with alias1, it searches for alias2 and increasing the join level by one.
This is important because the JOIN LEVEL is what will be used to identify what is the driving table for the request. (the lowest one usually wins)
The maximum weight of all joins is stored in lnMaxPref.
** Why not just look at the max weight of the joins in the request's tables?
Ensure that all tables have join records in the system
Creates two arrays for tracking which tables have joins and which ones don't.
It identifies the Start table by looking for the first table in the unjoined tables array (which identifies all the tables used) and looks for a matching record with any of the other tables.
(this can be critical because if your request starts with a little used table but there is a jOIN in the array, it will START with that as the primary table for the join)
Update: this has been revised to look for all direct relationships right away once it knows the Start table. it does this by looking for relationships in order of the level in the array. So if your Customer file is at the top, it starts with THAT file and looks for relationships below it. This loop takes place right away and removes entries from the laUnJoined array.
NOTE: Put an exit in the search for empty line tables - it doesn't need to search through everything again. Time waster
If, by this time, the relationship hasn't been figured out.
The Start table is added as the first element of array laJoined here.
Now go through the weights listed here, seeing which tables can be DIRECTLY linked to the START table. (once again, if the WRONG start table is chosen, this could be a problem) In most cases, the lnMaxPref would be 1 so this loop would only be gone through once.
How do weights work? Well it starts at the lowest number (1) and looks for a relationship between any table in the Unjoined array and a table in the joined array that is LESS or equal to that number. If one isn't found, it then moves up to the next weight.
This is done in reverse order in the unjoined array.
If this uses Advanced SQL (??? How is this defined ??? )
it identifies the new join by calling j_gen_instjoin with no parameters
sf_alias is a separate array that ?????
This join is now added to a cursor called FFCJOINS
This is repeated for all the tables involved and the table is removed from the unjoined array.
At this stage, the unjoined array should be empty. If it's not, a warning is posted and the request is stopped.
In addition, a variable named lcJoinstring now contains essentially the WHERE clause if there were no outer joins being used here.
The FFCJOINS table is now sorted by Join Level (from the JOIN table) and the Alias 1.
This lets FF! know what the highest join level is.
*!*!*!!*!* BUG: Don't use ALLTRIM(STR() use PADL(joinlevel, 3) - this sets the WRONG level.
Do we still want to support nested Joins vs. sequential?
Sequential Version
The Nested
posted by andrew macneill at 7:16 am
Comment
