SQL Functions

Robert's Simple SQL Parser

Documentation In Progress

The Parser/Tokenizer is a relatively simple state machine that scans through a SQL WHERE clause type string extracting a token at a time (where a token is:

  VALUE -> quoted string or number
  AGGREGator -> AND or OR
  KEYWORD -> quoted string or single word
  OPerator -> =,!=,LIKE,etc..
  PARENthesis -> open or close.

And that stream of tokens is passed through the "machine" in order to build up a structure that looks like:

       KEY OP VALUE
  AND  KEY OP VALUE
  OR   KEY OP VALUE

That also deals with parenthesis for nesting. (The parentheses are just handed off the SearchBuilder)

ClausesToSQL

FromSQL

Convert a RT-SQL string into a set of SearchBuilder restrictions.

Returns (1, 'Status message') on success and (0, 'Error Message') on failure.

Exceptions

Most of the RT code does not use Exceptions (die/eval) but it is used in the TicketSQL code for simplicity and historical reasons. Lest you be worried that the dies will trigger user visible errors, all are trapped via evals.

99% of the dies fall in subroutines called via FromSQL and then parse. (This includes all of the _FooLimit routines in Tickets_Overlay.pm.) The other 1% or so are via _ProcessRestrictions.

All dies are trapped by eval {}s, and will be logged at the 'error' log level. The general failure mode is to not display any tickets.

General Flow

Legacy Layer:

   Legacy LimitFoo routines build up a RestrictionsHash
   _ProcessRestrictions converts the Restrictions to Clauses
   ([key,op,val,rest]).
   Clauses are converted to RT-SQL (TicketSQL)

New RT-SQL Layer:

   FromSQL calls the parser
   The parser calls the _FooLimit routines to do DBIx::SearchBuilder
   limits.

And then the normal SearchBuilder/Ticket routines are used for display/navigation.