ITrack/Enterprise/Search Modules

Search Module Specifications and Guidlines.

This document should explain the purpose and use of the different functions of the ITrack Enterprise search module system. This should be used by ITrack employees as well as any third party developers who wish to extend their search capabilities.

Tables Used

searchmodule	This table will store all the system's search modules. The moduleid is just a AUTO_INCREMENT. Name is a user-visible name of the module seen on the search screen. Module is the text of the module itself (syntax of these queries is explained later). Isdefault is whether the module should be selected by default for a user who doesn't have a saved set yet.
searchmoduledefault	This table stores user-based defaults for module selections. You can save a current set of modules as your default module set by hitting the Save Modules as Default button on the advanced page.

Explanation

The search screen, when there is text in the smart search field, will get the query text for all selected search modules from the database. All these queries will take the form of "SELECT partnum, store FROM ....". All these queries are combined with a UNION DISTINCT. This is built to be a subquery that returns a set of partnum, stores which is joined, as a table, to the regular search query. This has the effect of dynamically adding query chunks that can change the result set of the search query. Also, all other fields on the search screen are built into the where clause as usual. In order for the developer to build useful subqueries, certain variables must be made available. These are all data that is derived from the smart search field contents in some way. All the developer needs to do is insert $VARIABLE_NAME into the query, and it will be replaced with the registered variable's value. Available variables will be listed and explained below. It should be noted that in this process of replacement, single quotes (')'s are not added to strings. Therefore, it is the responsibility of the developer to put them in their module query in whatever places that can possibly contain a string (to avoid sql insertion attacks and sql query failures).

Variables

In the following values, LEFT represents the part of the string before the first delimeter (default delimeters are: " -.,_") or everything if one is not found. Right represents everything after the first delimeter, or nothing if no delimeter is found.

$LEFTNUMERIC	This value should be left half as a numeric value (everything but "-.0123456789" are stripped out)
$RIGHTNUMERIC	Same as above, but right half
$ALLNUMERIC	Same as above but whole string
$LEFTNOALPHA	This is the left half exactly if it contains nothing but "0123456789" (we may want to add '-' and '.'?), otherwise it is 0
$RIGHTNOALPHA	Same as above but right half
$ALLNOALPHA	Same as above, but entire string
$LEFT	Just the left half
$RIGHTINT	This is the right half treated as an interchange value. This means that if we're not doing an exact interchange search, everything but "0123456789*?" (numbers and wildcards) are stripped out and a * is added to the end.
$RIGHTSTRIPPED	Just the right half, but with all remaining delimeters stripped out
$RIGHT	Just the right half
$ALLSTRIPPED	Entire string stripped of delimeters
$ALL	Entire string, unmodified
$VENDORNUM	Takes the LEFT half, treats it as a vendorcode, and searches the vendor table for a corresponding vendornum. If a match was found, that value is used, otherwise 0 is used.

Notes and considerations

The NUMERIC and NOALPHA values should be completely safe to use without any single quotes, but the others could contain elements of strings, even if they are expected to contain numbers. I currently didn't do a LEFTSTRIPPED, because it is unnecessary, and there is no LEFTINT because I didn't see a call for one, but it could be easily included. Also, all strings are escaped before being put in the string, so you shouldn't have to worry about sql insertion attacks from that angle.

Also, I wonder if it might be a good idea to have the 'invalid' values be -1 instead of 0, because I noticed that both NULL and 0 existed for certain records for vendornum or typenum, and this could create false positives. I compensated for this in my string by saying that $VENDORNUM != 0 inside the clause that checks for that (so that no records with that will be returned).

Comparison operators

In order to account for wildcards, something special had to be implemented. One cannot fix a = or LIKE into their subquery without knowing if the user will use a wildcard search or not (since = ignores wildcards and LIKE takes more time and I think it might not be able to use indices). So, if you have a value that allows wildcards, you have to use a specific query syntax to signify it.

$COMP(VALUE)

This will tell ITrack to take a look at VALUE. It will replace all * with % and ? with _. If at least one was replaced, this part of the subquery will be replaced with LIKE VALUE. Otherwise, it will be replaced with "= VALUE". If you are comparing to a numerical value, or you don't want to allow wildcards in a subquery, simply write "= $VARIABLE" instead of "$COMP($VARIABLE)". Also, it is ok if there are close parenthesis inside the value that $VARIABLE is replaced with, because ITrack looks for the first close parenthesis after the second unescaped single quote (if a first was found).

$LIKE(VALUE)

Similar to above, but LIKE is always used, even if no wildcards were found in the string. This has the added benefit of being able to force a case-insensitive search.

And, of course, =, >, >=, !=, <, and <= can all be writted in directly (as they aren't compatible with wildcards anyway).

Examples

Cross Reference:

SELECT DISTINCT partnum, store 
FROM interchange 
JOIN inventory ON (inventory.inventoryvendornum = interchange.typenum AND inventory.partid = interchange.model) 
WHERE CrossRef $COMP('$ALL') AND IntType = 'XREF'

Model Lookup:

SELECT DISTINCT partnum, store 
FROM interchange 
JOIN inventory ON (inventory.typenum = interchange.typenum AND inventory.make = interchange.make AND inventory.model = interchange.model) 
WHERE interchange.Model $COMP('$ALL')

Part/Tag/Int/Vendor:

SELECT DISTINCT partnum, store 
FROM inventory 
WHERE ($LEFTNUMERIC != 0 AND typenum = $LEFTNUMERIC AND interchangenum $COMP('$RIGHTINT')) 
     OR partid $COMP('$ALLSTRIPPED') 
     OR partnum $COMP('$ALLSTRIPPED') 
     OR tagnum $COMP('$ALL') 
     OR ($VENDORNUM != 0 
     AND partid $COMP('$RIGHTSTRIPPED') 
     AND (inventoryvendornum = $VENDORNUM OR purchasevendornum = $VENDORNUM))