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.

Requirements: Table: searchmodule (moduleid, name, module, isdefault) 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.

Table: searchmoduledefault (moduleid, userid) 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 (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 (but I predict it to be mostly unused).

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

Here are the three original queries I wrote for the system that should serve as examples or templates:

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))