Added new keywords and functions to SQL scanner (see #221, thanks to Joshua Galvez).

4 files changed, 517 insertions, 3 deletions

diff --git a/Changes.textile b/Changes.textile
index 09c236a..deddb27 100644
--- a/Changes.textile
+++ b/Changes.textile
@@ -47,9 +47,15 @@ h3. @Scanners::Scheme@
  
 * *CHANGED*: Does use @:opertor@ instead of @:opertor_fat@ now.
 
+h3. @Scanners::SQL@
+ 
+* *IMPROVED*: Extended list of keywords and functions (thanks to Joshua Galvez).
+  See "#221":http://redmine.rubychan.de/issues/221.
+
 h3. @Styles::Alpha@
  
 * *NEW* A style that uses transparent HSLA colors as defined in CSS 3.
+  See "#199":http://redmine.rubychan.de/issues/199.
 
 h3. @FileType@
  
@@ -61,7 +67,6 @@ h3. @FileType@
 h3. Internal API changes
  
 * *FIXED* @Encoders::HTML#token@'s second parameter is no longer optional.
-  See "#119":http://redmine.rubychan.de/issues/199.
 
 
 h2. Changes in 0.9.2
diff --git a/lib/coderay/scanners/sql.rb b/lib/coderay/scanners/sql.rb
index 993afd5..3aeea77 100644
--- a/lib/coderay/scanners/sql.rb
+++ b/lib/coderay/scanners/sql.rb
@@ -11,7 +11,7 @@ module CodeRay module Scanners
       for foreign from group if inner is join key
       like not on or order outer primary references replace
       then to trigger union using values when where
-      left
+      left right distinct
     )
     
     OBJECTS = %w(
@@ -31,7 +31,7 @@ module CodeRay module Scanners
       bool boolean hex bin oct
     )
     
-    PREDEFINED_FUNCTIONS = %w( sum cast substring abs pi count min max avg )
+    PREDEFINED_FUNCTIONS = %w( sum cast substring abs pi count min max avg now )
     
     DIRECTIVES = %w( auto_increment unique default charset )
     
@@ -86,6 +86,7 @@ module CodeRay module Scanners
             kind = :delimiter
             
           elsif match = scan(/ @? [A-Za-z_][A-Za-z_0-9]* /x)
+            # FIXME: Don't match keywords after "."
             kind = match[0] == ?@ ? :variable : IDENT_KIND[match.downcase]
             
           elsif scan(/0[xX][0-9A-Fa-f]+/)
diff --git a/test/scanners/sql/mysql-long-queries.expected.raydebug b/test/scanners/sql/mysql-long-queries.expected.raydebug
new file mode 100644
index 0000000..a0d0886
--- /dev/null
+++ b/test/scanners/sql/mysql-long-queries.expected.raydebug
@@ -0,0 +1,254 @@
+comment(/* This is a modified copy of the
+  query linked above to test other keywords: */) 
+class(SELECT)   ident(sd)operator(.)ident(qbclass)operator(,)                   comment(-- Comments Test)
+         predefined(Sum)operator(()ident(sd)operator(.)ident(amount)operator(\))   reserved(AS) ident(invoiceamount)operator(,)          comment(# Comments Test)
+         predefined(Sum)operator(()ident(scd1)operator(.)ident(amount)operator(\)) reserved(AS) ident(paymentsperiod1)operator(,) 
+         predefined(Sum)operator(()ident(scd2)operator(.)ident(amount)operator(\)) reserved(AS) ident(paymentsperiod2)operator(,) 
+         predefined(Sum)operator(()ident(scd3)operator(.)ident(amount)operator(\)) reserved(AS) ident(paymentsperiod3) 
+reserved(FROM)     ident(studentdebit) reserved(AS) ident(sd) 
+         reserved(LEFT) reserved(JOIN) operator(()class(SELECT) ident(studentcreditdetail)operator(.)ident(studentdebitid)operator(,) 
+                           ident(studentcreditdetail)operator(.)ident(amount)operator(,) 
+                           ident(studentcredit)operator(.)pre_type(date)operator(,) 
+                           ident(credittype)operator(.)ident(credittype) 
+                    reserved(FROM)   ident(studentcreditdetail) 
+                           reserved(INNER) reserved(JOIN) ident(studentcredit) 
+                             reserved(ON) ident(studentcreditdetail)operator(.)ident(studentcreditid) operator(=) ident(studentcredit)operator(.)ident(studentcreditid) 
+                                reserved(AND) ident(studentcredit)operator(.)ident(obsolete) operator(=) integer(0) comment(/* Not Deleted */) 
+                                reserved(AND) ident(studentcredit)operator(.)ident(status) operator(=) integer(1)   comment(/* Successful  */) 
+                                comment(/* PERIOD 1 */) 
+                                reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(<) predefined(Now)operator(()operator(\)) 
+                                comment(/* PERIOD 1 */) 
+                                reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(1) ident(MONTH) 
+                           reserved(LEFT) reserved(JOIN) ident(credittype) 
+                             reserved(USING)operator(()ident(credittypeid)operator(\))operator(\)) reserved(AS) ident(scd1) 
+           reserved(ON) ident(sd)operator(.)ident(studentdebitid) operator(=) ident(scd1)operator(.)ident(studentdebitid) 
+         reserved(LEFT) reserved(JOIN) operator(()class(SELECT) ident(studentcreditdetail)operator(.)ident(studentdebitid)operator(,) 
+                           ident(studentcreditdetail)operator(.)ident(amount)operator(,) 
+                           ident(studentcredit)operator(.)pre_type(date)operator(,) 
+                           ident(credittype)operator(.)ident(credittype) 
+                    reserved(FROM)   ident(studentcreditdetail) 
+                           reserved(INNER) reserved(JOIN) ident(studentcredit) 
+                             reserved(ON) ident(studentcreditdetail)operator(.)ident(studentcreditid) operator(=) ident(studentcredit)operator(.)ident(studentcreditid) 
+                                reserved(AND) ident(studentcredit)operator(.)ident(obsolete) operator(=) integer(0) comment(/* Not Deleted */) 
+                                reserved(AND) ident(studentcredit)operator(.)ident(status) operator(=) integer(1)   comment(/* Successful  */) 
+                                comment(/* PERIOD 2 */) 
+                                reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(<) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(1) ident(MONTH) 
+                                comment(/* PERIOD 2 */) 
+                                reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(2) ident(MONTH) 
+                           reserved(LEFT) reserved(OUTER) reserved(JOIN) ident(credittype) 
+                             reserved(USING)operator(()ident(credittypeid)operator(\))operator(\)) reserved(AS) ident(scd2) 
+           reserved(ON) ident(sd)operator(.)ident(studentdebitid) operator(=) ident(scd2)operator(.)ident(studentdebitid) 
+         reserved(RIGHT) reserved(JOIN) operator(()class(SELECT) ident(studentcreditdetail)operator(.)ident(studentdebitid)operator(,) 
+                           ident(studentcreditdetail)operator(.)ident(amount)operator(,) 
+                           ident(studentcredit)operator(.)pre_type(date)operator(,) 
+                           ident(credittype)operator(.)ident(credittype) 
+                    reserved(FROM)   ident(studentcreditdetail) 
+                           reserved(INNER) reserved(JOIN) ident(studentcredit) 
+                             reserved(ON) ident(studentcreditdetail)operator(.)ident(studentcreditid) operator(=) ident(studentcredit)operator(.)ident(studentcreditid) 
+                                reserved(AND) ident(studentcredit)operator(.)ident(obsolete) operator(=) integer(0) comment(/* Not Deleted */) 
+                                reserved(AND) ident(studentcredit)operator(.)ident(status) operator(=) integer(1)   comment(/* Successful  */) 
+                                comment(/* PERIOD 3 */) 
+                                reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(<) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(2) ident(MONTH) 
+                                comment(/* PERIOD 3 */) 
+                                reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(3) ident(MONTH) 
+                           reserved(LEFT) reserved(JOIN) ident(credittype) 
+                             reserved(USING)operator(()ident(credittypeid)operator(\))operator(\)) reserved(AS) ident(scd3) 
+           reserved(ON) ident(sd)operator(.)ident(studentdebitid) operator(=) ident(scd3)operator(.)ident(studentdebitid) 
+reserved(WHERE)    ident(sd)operator(.)ident(obsolete) operator(=) integer(0)   comment(/* Not Deleted */) 
+         reserved(AND) ident(sd)operator(.)ident(status) operator(=) integer(0) comment(/* Normal      */) 
+         comment(/* Exclude Voided Invoices */) 
+         reserved(AND) ident(sd)operator(.)ident(adjustsdebitid) reserved(IS) pre_constant(NULL) 
+         reserved(AND) ident(sd)operator(.)ident(studentdebitid) reserved(NOT) ident(IN) operator(()class(SELECT) ident(adjustsdebitid) 
+                                       reserved(FROM)   ident(studentdebit) 
+                                       reserved(WHERE)  ident(adjustsdebitid) reserved(IS) reserved(NOT) pre_constant(NULL)operator(\)) 
+         comment(/* FULL PERIOD */) 
+         reserved(AND) ident(sd)operator(.)pre_type(DATE) operator(<) predefined(Now)operator(()operator(\)) 
+         comment(/* FULL PERIOD */) 
+         reserved(AND) ident(sd)operator(.)pre_type(DATE) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(3) ident(MONTH) 
+reserved(GROUP) reserved(BY) ident(sd)operator(.)ident(qbclass) 
+comment(/* Formatting only */) 
+reserved(UNION) reserved(ALL) 
+class(SELECT) string<delimiter(')content(---)delimiter(')>operator(,) 
+       string<delimiter(')content(---)delimiter(')>operator(,) 
+       string<delimiter(')content(---)delimiter(')>operator(,) 
+       string<delimiter(')content(---)delimiter(')>operator(,) 
+       string<delimiter(')content(---)delimiter(')> 
+comment(/* Payment Types Summary */) 
+reserved(UNION) reserved(DISTINCT) 
+class(SELECT)   ident(credittype)operator(,) 
+         ident(invoiceamount)operator(,) 
+         predefined(Sum)operator(()ident(paymentsperiod1)operator(\))operator(,) 
+         predefined(Sum)operator(()ident(paymentsperiod2)operator(\))operator(,) 
+         predefined(Sum)operator(()ident(paymentsperiod3)operator(\)) 
+reserved(FROM)     operator(()class(SELECT)   ident(scd)operator(.)ident(credittype)operator(,) 
+                   string<delimiter(')delimiter(')>              reserved(AS) ident(invoiceamount)operator(,) 
+                   predefined(Sum)operator(()ident(scd)operator(.)ident(amount)operator(\)) reserved(AS) ident(paymentsperiod1)operator(,) 
+                   string<delimiter(')delimiter(')>              reserved(AS) ident(paymentsperiod2)operator(,) 
+                   string<delimiter(')delimiter(')>              reserved(AS) ident(paymentsperiod3) 
+          reserved(FROM)     ident(studentdebit) reserved(AS) ident(sd) 
+                   reserved(INNER) reserved(JOIN) operator(()class(SELECT) ident(studentcreditdetail)operator(.)ident(studentdebitid)operator(,) 
+                                      ident(studentcreditdetail)operator(.)ident(amount)operator(,) 
+                                      ident(studentcredit)operator(.)pre_type(date)operator(,) 
+                                      ident(credittype)operator(.)ident(credittype) 
+                               reserved(FROM)   ident(studentcreditdetail) 
+                                      reserved(INNER) reserved(JOIN) ident(studentcredit) 
+                                        reserved(ON) ident(studentcreditdetail)operator(.)ident(studentcreditid) operator(=) ident(studentcredit)operator(.)ident(studentcreditid) 
+                                           reserved(AND) ident(studentcredit)operator(.)ident(obsolete) operator(=) integer(0) comment(/* Not Deleted */) 
+                                           reserved(AND) ident(studentcredit)operator(.)ident(status) operator(=) integer(1)   comment(/* Successful  */) 
+                                           comment(/* PERIOD 1 */) 
+                                           reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(<) predefined(Now)operator(()operator(\)) 
+                                           comment(/* PERIOD 1 */) 
+                                           reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(1) ident(MONTH) 
+                                      reserved(LEFT) reserved(JOIN) ident(credittype) 
+                                        reserved(USING)operator(()ident(credittypeid)operator(\))operator(\)) reserved(AS) ident(scd) 
+                     reserved(ON) ident(sd)operator(.)ident(studentdebitid) operator(=) ident(scd)operator(.)ident(studentdebitid) 
+          reserved(WHERE)    ident(sd)operator(.)ident(obsolete) operator(=) integer(0)   comment(/* Not Deleted */) 
+                   reserved(AND) ident(sd)operator(.)ident(status) operator(=) integer(0) comment(/* Normal      */) 
+                   comment(/* Exclude Voided Invoices */) 
+                   reserved(AND) ident(sd)operator(.)ident(adjustsdebitid) reserved(IS) pre_constant(NULL) 
+                   reserved(AND) ident(sd)operator(.)ident(studentdebitid) reserved(NOT) ident(IN) operator(()class(SELECT) ident(adjustsdebitid) 
+                                                 reserved(FROM)   ident(studentdebit) 
+                                                 reserved(WHERE)  ident(adjustsdebitid) reserved(IS) reserved(NOT) pre_constant(NULL)operator(\)) 
+                   reserved(AND) ident(sd)operator(.)pre_type(DATE) operator(<) predefined(Now)operator(()operator(\)) 
+                   reserved(AND) ident(sd)operator(.)pre_type(DATE) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(3) ident(MONTH) 
+          reserved(GROUP) reserved(BY) ident(scd)operator(.)ident(credittype) 
+          reserved(UNION) reserved(ALL) 
+          class(SELECT)   ident(scd)operator(.)ident(credittype)operator(,) 
+                   string<delimiter(')delimiter(')>              reserved(AS) ident(invoiceamount)operator(,) 
+                   string<delimiter(')delimiter(')>              reserved(AS) ident(paymentsperiod1)operator(,) 
+                   predefined(Sum)operator(()ident(scd)operator(.)ident(amount)operator(\)) reserved(AS) ident(paymentsperiod2)operator(,) 
+                   string<delimiter(')delimiter(')>              reserved(AS) ident(paymentsperiod3) 
+          reserved(FROM)     ident(studentdebit) reserved(AS) ident(sd) 
+                   reserved(INNER) reserved(JOIN) operator(()class(SELECT) ident(studentcreditdetail)operator(.)ident(studentdebitid)operator(,) 
+                                      ident(studentcreditdetail)operator(.)ident(amount)operator(,) 
+                                      ident(studentcredit)operator(.)pre_type(date)operator(,) 
+                                      ident(credittype)operator(.)ident(credittype) 
+                               reserved(FROM)   ident(studentcreditdetail) 
+                                      reserved(INNER) reserved(JOIN) ident(studentcredit) 
+                                        reserved(ON) ident(studentcreditdetail)operator(.)ident(studentcreditid) operator(=) ident(studentcredit)operator(.)ident(studentcreditid) 
+                                           reserved(AND) ident(studentcredit)operator(.)ident(obsolete) operator(=) integer(0) comment(/* Not Deleted */) 
+                                           reserved(AND) ident(studentcredit)operator(.)ident(status) operator(=) integer(1)   comment(/* Successful  */) 
+                                           comment(/* PERIOD 2 */) 
+                                           reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(<) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(1) ident(MONTH) 
+                                           comment(/* PERIOD 2 */) 
+                                           reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(2) ident(MONTH) 
+                                      reserved(LEFT) reserved(JOIN) ident(credittype) 
+                                        reserved(USING)operator(()ident(credittypeid)operator(\))operator(\)) reserved(AS) ident(scd) 
+                     reserved(ON) ident(sd)operator(.)ident(studentdebitid) operator(=) ident(scd)operator(.)ident(studentdebitid) 
+          reserved(WHERE)    ident(sd)operator(.)ident(obsolete) operator(=) integer(0)   comment(/* Not Deleted */) 
+                   reserved(AND) ident(sd)operator(.)ident(status) operator(=) integer(0) comment(/* Normal      */) 
+                   comment(/* Exclude Voided Invoices */) 
+                   reserved(AND) ident(sd)operator(.)ident(adjustsdebitid) reserved(IS) pre_constant(NULL) 
+                   reserved(AND) ident(sd)operator(.)ident(studentdebitid) reserved(NOT) ident(IN) operator(()class(SELECT) ident(adjustsdebitid) 
+                                                 reserved(FROM)   ident(studentdebit) 
+                                                 reserved(WHERE)  ident(adjustsdebitid) reserved(IS) reserved(NOT) pre_constant(NULL)operator(\)) 
+                   reserved(AND) ident(sd)operator(.)pre_type(DATE) operator(<) predefined(Now)operator(()operator(\)) 
+                   reserved(AND) ident(sd)operator(.)pre_type(DATE) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(3) ident(MONTH) 
+          reserved(GROUP) reserved(BY) ident(scd)operator(.)ident(credittype) 
+          reserved(UNION) reserved(ALL) 
+          class(SELECT)   ident(scd)operator(.)ident(credittype)operator(,) 
+                   string<delimiter(')delimiter(')>              reserved(AS) ident(invoiceamount)operator(,) 
+                   string<delimiter(')delimiter(')>              reserved(AS) ident(paymentsperiod1)operator(,) 
+                   string<delimiter(')delimiter(')>              reserved(AS) ident(paymentsperiod2)operator(,) 
+                   predefined(Sum)operator(()ident(scd)operator(.)ident(amount)operator(\)) reserved(AS) ident(paymentsperiod3) 
+          reserved(FROM)     ident(studentdebit) reserved(AS) ident(sd) 
+                   reserved(INNER) reserved(JOIN) operator(()class(SELECT) ident(studentcreditdetail)operator(.)ident(studentdebitid)operator(,) 
+                                      ident(studentcreditdetail)operator(.)ident(amount)operator(,) 
+                                      ident(studentcredit)operator(.)pre_type(date)operator(,) 
+                                      ident(credittype)operator(.)ident(credittype) 
+                               reserved(FROM)   ident(studentcreditdetail) 
+                                      reserved(INNER) reserved(JOIN) ident(studentcredit) 
+                                        reserved(ON) ident(studentcreditdetail)operator(.)ident(studentcreditid) operator(=) ident(studentcredit)operator(.)ident(studentcreditid) 
+                                           reserved(AND) ident(studentcredit)operator(.)ident(obsolete) operator(=) integer(0) comment(/* Not Deleted */) 
+                                           reserved(AND) ident(studentcredit)operator(.)ident(status) operator(=) integer(1)   comment(/* Successful  */) 
+                                           comment(/* PERIOD 3 */) 
+                                           reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(<) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(2) ident(MONTH) 
+                                           comment(/* PERIOD 3 */) 
+                                           reserved(AND) ident(studentcredit)operator(.)pre_type(date) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(3) ident(MONTH) 
+                                      reserved(LEFT) reserved(JOIN) ident(credittype) 
+                                        reserved(USING)operator(()ident(credittypeid)operator(\))operator(\)) reserved(AS) ident(scd) 
+                     reserved(ON) ident(sd)operator(.)ident(studentdebitid) operator(=) ident(scd)operator(.)ident(studentdebitid) 
+          reserved(WHERE)    ident(sd)operator(.)ident(obsolete) operator(=) integer(0)   comment(/* Not Deleted */) 
+                   reserved(AND) ident(sd)operator(.)ident(status) operator(=) integer(0) comment(/* Normal      */) 
+                   comment(/* Exclude Voided Invoices */) 
+                   reserved(AND) ident(sd)operator(.)ident(adjustsdebitid) reserved(IS) pre_constant(NULL) 
+                   reserved(AND) ident(sd)operator(.)ident(studentdebitid) reserved(NOT) ident(IN) operator(()class(SELECT) ident(adjustsdebitid) 
+                                                 reserved(FROM)   ident(studentdebit) 
+                                                 reserved(WHERE)  ident(adjustsdebitid) reserved(IS) reserved(NOT) pre_constant(NULL)operator(\)) 
+                   reserved(AND) ident(sd)operator(.)pre_type(date) operator(<) predefined(Now)operator(()operator(\)) 
+                   reserved(AND) ident(sd)operator(.)pre_type(date) operator(>) predefined(Now)operator(()operator(\)) operator(-) ident(INTERVAL) integer(3) ident(MONTH) 
+          reserved(GROUP) reserved(BY) ident(scd)operator(.)ident(credittype)operator(\)) reserved(AS) ident(ct) 
+reserved(GROUP) reserved(BY) ident(ct)operator(.)ident(credittype)
+
+class(SELECT) string<delimiter(')content(mediaid)delimiter(')>             reserved(AS) string<delimiter(`)content(idtype)delimiter(`)>operator(,) 
+       string<delimiter(`)content(m)delimiter(`)>operator(.)string<delimiter(`)content(mediaid)delimiter(`)>         reserved(AS) string<delimiter(`)content(id)delimiter(`)>operator(,) 
+       string<delimiter(`)content(m)delimiter(`)>operator(.)string<delimiter(`)content(title)delimiter(`)>           reserved(AS) string<delimiter(`)content(title)delimiter(`)>operator(,) 
+       string<delimiter(`)content(m)delimiter(`)>operator(.)string<delimiter(`)content(description)delimiter(`)>     reserved(AS) string<delimiter(`)content(description)delimiter(`)>operator(,) 
+       string<delimiter(`)content(m)delimiter(`)>operator(.)string<delimiter(`)content(source)delimiter(`)>          reserved(AS) string<delimiter(`)content(source)delimiter(`)>operator(,) 
+       string<delimiter(`)content(m)delimiter(`)>operator(.)string<delimiter(`)content(date)delimiter(`)>            reserved(AS) string<delimiter(`)content(startdate)delimiter(`)>operator(,) 
+       string<delimiter(`)content(m)delimiter(`)>operator(.)string<delimiter(`)content(date)delimiter(`)>            reserved(AS) string<delimiter(`)content(enddate)delimiter(`)>operator(,) 
+       string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(class)delimiter(`)>           reserved(AS) string<delimiter(`)content(class)delimiter(`)>operator(,) 
+       string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(classname)delimiter(`)>       reserved(AS) string<delimiter(`)content(classname)delimiter(`)>operator(,) 
+       string<delimiter(`)content(per)delimiter(`)>operator(.)string<delimiter(`)content(firstname)delimiter(`)>     reserved(AS) string<delimiter(`)content(firstname)delimiter(`)>operator(,) 
+       string<delimiter(`)content(per)delimiter(`)>operator(.)string<delimiter(`)content(lastname)delimiter(`)>      reserved(AS) string<delimiter(`)content(lastname)delimiter(`)>operator(,) 
+       string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(description)delimiter(`)>     reserved(AS) string<delimiter(`)content(classdesc)delimiter(`)>operator(,) 
+       string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(programid)delimiter(`)>       reserved(AS) string<delimiter(`)content(programid)delimiter(`)>operator(,) 
+       reserved(If)operator(()operator(()string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(subprogramof)delimiter(`)> reserved(IS) reserved(NOT) pre_constant(NULL)operator(\))operator(,)string<delimiter(`)content(mp)delimiter(`)>operator(.)string<delimiter(`)content(programname)delimiter(`)>operator(,) 
+          string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(programname)delimiter(`)>operator(\)) reserved(AS) string<delimiter(`)content(programname)delimiter(`)>operator(,) 
+       string<delimiter(')content(Recorded)delimiter(')>            reserved(AS) string<delimiter(`)content(longname)delimiter(`)> 
+reserved(FROM)   operator(()operator(()operator(()operator(()operator(()operator(()operator(()operator(()string<delimiter(`)content(media)delimiter(`)> string<delimiter(`)content(m)delimiter(`)> 
+               reserved(JOIN) string<delimiter(`)content(mediaaudience)delimiter(`)> string<delimiter(`)content(ma)delimiter(`)> 
+                 reserved(ON) operator(()operator(()operator(()string<delimiter(`)content(m)delimiter(`)>operator(.)string<delimiter(`)content(mediaid)delimiter(`)> operator(=) string<delimiter(`)content(ma)delimiter(`)>operator(.)string<delimiter(`)content(mediaid)delimiter(`)>operator(\)) 
+                      reserved(AND) operator(()string<delimiter(`)content(ma)delimiter(`)>operator(.)string<delimiter(`)content(audiencetype)delimiter(`)> operator(=) string<delimiter(')content(Public)delimiter(')>operator(\)) 
+                      reserved(AND) operator(()operator(()string<delimiter(`)content(ma)delimiter(`)>operator(.)string<delimiter(`)content(enddate)delimiter(`)> operator(<) predefined(Now)operator(()operator(\))operator(\)) 
+                            reserved(OR) ident(Isnull)operator(()string<delimiter(`)content(ma)delimiter(`)>operator(.)string<delimiter(`)content(enddate)delimiter(`)>operator(\))operator(\))operator(\))operator(\))operator(\)) 
+              reserved(LEFT) reserved(JOIN) string<delimiter(`)content(mediapresenter)delimiter(`)> string<delimiter(`)content(mpp)delimiter(`)> 
+                reserved(ON) operator(()operator(()string<delimiter(`)content(m)delimiter(`)>operator(.)string<delimiter(`)content(mediaid)delimiter(`)> operator(=) string<delimiter(`)content(mpp)delimiter(`)>operator(.)string<delimiter(`)content(mediaid)delimiter(`)>operator(\))operator(\))operator(\)) 
+             reserved(LEFT) reserved(JOIN) string<delimiter(`)content(person)delimiter(`)> string<delimiter(`)content(per)delimiter(`)> 
+               reserved(ON) operator(()operator(()string<delimiter(`)content(mpp)delimiter(`)>operator(.)string<delimiter(`)content(personid)delimiter(`)> operator(=) string<delimiter(`)content(per)delimiter(`)>operator(.)string<delimiter(`)content(personid)delimiter(`)>operator(\))operator(\))operator(\)) 
+            reserved(LEFT) reserved(JOIN) string<delimiter(`)content(mediaaudience)delimiter(`)> string<delimiter(`)content(mad)delimiter(`)> 
+              reserved(ON) operator(()operator(()operator(()string<delimiter(`)content(ma)delimiter(`)>operator(.)string<delimiter(`)content(mediaid)delimiter(`)> operator(=) string<delimiter(`)content(mad)delimiter(`)>operator(.)string<delimiter(`)content(mediaid)delimiter(`)>operator(\)) 
+                   reserved(AND) operator(()string<delimiter(`)content(mad)delimiter(`)>operator(.)string<delimiter(`)content(audiencetype)delimiter(`)> operator(=) string<delimiter(')content(classid)delimiter(')>operator(\))operator(\))operator(\))operator(\)) 
+           reserved(LEFT) reserved(JOIN) string<delimiter(`)content(class)delimiter(`)> string<delimiter(`)content(c)delimiter(`)> 
+             reserved(ON) operator(()operator(()string<delimiter(`)content(mad)delimiter(`)>operator(.)string<delimiter(`)content(audienceid)delimiter(`)> operator(=) string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(classid)delimiter(`)>operator(\))operator(\))operator(\)) 
+          reserved(LEFT) reserved(JOIN) string<delimiter(`)content(program_class)delimiter(`)> string<delimiter(`)content(pc)delimiter(`)> 
+            reserved(ON) operator(()operator(()string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(classid)delimiter(`)> operator(=) string<delimiter(`)content(pc)delimiter(`)>operator(.)string<delimiter(`)content(classid)delimiter(`)>operator(\))operator(\))operator(\)) 
+         reserved(LEFT) reserved(JOIN) string<delimiter(`)content(program)delimiter(`)> string<delimiter(`)content(p)delimiter(`)> 
+           reserved(ON) operator(()operator(()string<delimiter(`)content(pc)delimiter(`)>operator(.)string<delimiter(`)content(programid)delimiter(`)> operator(=) string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(programid)delimiter(`)>operator(\))operator(\))operator(\)) 
+        reserved(LEFT) reserved(JOIN) string<delimiter(`)content(program)delimiter(`)> string<delimiter(`)content(mp)delimiter(`)> 
+          reserved(ON) operator(()operator(()string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(subprogramof)delimiter(`)> operator(=) string<delimiter(`)content(mp)delimiter(`)>operator(.)string<delimiter(`)content(programid)delimiter(`)>operator(\))operator(\))operator(\)) 
+reserved(UNION) 
+class(SELECT) string<delimiter(')content(sectionid)delimiter(')>           reserved(AS) string<delimiter(`)content(idtype)delimiter(`)>operator(,) 
+       string<delimiter(`)content(cc)delimiter(`)>operator(.)string<delimiter(`)content(sectionid)delimiter(`)>      reserved(AS) string<delimiter(`)content(id)delimiter(`)>operator(,) 
+       string<delimiter(`)content(cc)delimiter(`)>operator(.)string<delimiter(`)content(title)delimiter(`)>          reserved(AS) string<delimiter(`)content(title)delimiter(`)>operator(,) 
+       string<delimiter(`)content(cc)delimiter(`)>operator(.)string<delimiter(`)content(description)delimiter(`)>    reserved(AS) string<delimiter(`)content(description)delimiter(`)>operator(,) 
+       string<delimiter(`)content(l)delimiter(`)>operator(.)string<delimiter(`)content(mapurl)delimiter(`)>          reserved(AS) string<delimiter(`)content(mapurl)delimiter(`)>operator(,) 
+       string<delimiter(`)content(cc)delimiter(`)>operator(.)string<delimiter(`)content(starttime)delimiter(`)>      reserved(AS) string<delimiter(`)content(startdate)delimiter(`)>operator(,) 
+       string<delimiter(`)content(cc)delimiter(`)>operator(.)string<delimiter(`)content(endtime)delimiter(`)>        reserved(AS) string<delimiter(`)content(enddate)delimiter(`)>operator(,) 
+       string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(class)delimiter(`)>           reserved(AS) string<delimiter(`)content(class)delimiter(`)>operator(,) 
+       string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(classname)delimiter(`)>       reserved(AS) string<delimiter(`)content(classname)delimiter(`)>operator(,) 
+       string<delimiter(`)content(per)delimiter(`)>operator(.)string<delimiter(`)content(firstname)delimiter(`)>     reserved(AS) string<delimiter(`)content(firstname)delimiter(`)>operator(,) 
+       string<delimiter(`)content(per)delimiter(`)>operator(.)string<delimiter(`)content(lastname)delimiter(`)>      reserved(AS) string<delimiter(`)content(lastname)delimiter(`)>operator(,) 
+       string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(description)delimiter(`)>     reserved(AS) string<delimiter(`)content(classdesc)delimiter(`)>operator(,) 
+       string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(programid)delimiter(`)>       reserved(AS) string<delimiter(`)content(programid)delimiter(`)>operator(,) 
+       reserved(If)operator(()operator(()string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(subprogramof)delimiter(`)> reserved(IS) reserved(NOT) pre_constant(NULL)operator(\))operator(,)string<delimiter(`)content(mp)delimiter(`)>operator(.)string<delimiter(`)content(programname)delimiter(`)>operator(,) 
+          string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(programname)delimiter(`)>operator(\)) reserved(AS) string<delimiter(`)content(programname)delimiter(`)>operator(,) 
+       string<delimiter(`)content(d)delimiter(`)>operator(.)string<delimiter(`)content(longname)delimiter(`)>        reserved(AS) string<delimiter(`)content(longname)delimiter(`)> 
+reserved(FROM)   operator(()operator(()operator(()operator(()operator(()operator(()operator(()operator(()string<delimiter(`)content(calendarcache)delimiter(`)> string<delimiter(`)content(cc)delimiter(`)> 
+               reserved(JOIN) string<delimiter(`)content(section)delimiter(`)> string<delimiter(`)content(s)delimiter(`)> 
+                 reserved(ON) operator(()operator(()string<delimiter(`)content(cc)delimiter(`)>operator(.)string<delimiter(`)content(sectionid)delimiter(`)> operator(=) string<delimiter(`)content(s)delimiter(`)>operator(.)string<delimiter(`)content(sectionid)delimiter(`)>operator(\))operator(\))operator(\)) 
+              reserved(LEFT) reserved(JOIN) string<delimiter(`)content(person)delimiter(`)> string<delimiter(`)content(per)delimiter(`)> 
+                reserved(ON) operator(()operator(()string<delimiter(`)content(s)delimiter(`)>operator(.)string<delimiter(`)content(teacherid)delimiter(`)> operator(=) string<delimiter(`)content(per)delimiter(`)>operator(.)string<delimiter(`)content(personid)delimiter(`)>operator(\))operator(\))operator(\)) 
+             reserved(LEFT) reserved(JOIN) string<delimiter(`)content(division)delimiter(`)> string<delimiter(`)content(d)delimiter(`)> 
+               reserved(ON) operator(()operator(()string<delimiter(`)content(s)delimiter(`)>operator(.)string<delimiter(`)content(divisionid)delimiter(`)> operator(=) string<delimiter(`)content(d)delimiter(`)>operator(.)string<delimiter(`)content(divisionid)delimiter(`)>operator(\))operator(\))operator(\)) 
+            reserved(LEFT) reserved(JOIN) string<delimiter(`)content(location)delimiter(`)> string<delimiter(`)content(l)delimiter(`)> 
+              reserved(ON) operator(()operator(()string<delimiter(`)content(s)delimiter(`)>operator(.)string<delimiter(`)content(locationid)delimiter(`)> operator(=) string<delimiter(`)content(l)delimiter(`)>operator(.)string<delimiter(`)content(locationid)delimiter(`)>operator(\))operator(\))operator(\)) 
+           reserved(LEFT) reserved(JOIN) string<delimiter(`)content(class)delimiter(`)> string<delimiter(`)content(c)delimiter(`)> 
+             reserved(ON) operator(()operator(()string<delimiter(`)content(s)delimiter(`)>operator(.)string<delimiter(`)content(classid)delimiter(`)> operator(=) string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(classid)delimiter(`)>operator(\))operator(\))operator(\)) 
+          reserved(LEFT) reserved(JOIN) string<delimiter(`)content(program_class)delimiter(`)> string<delimiter(`)content(pc)delimiter(`)> 
+            reserved(ON) operator(()operator(()string<delimiter(`)content(c)delimiter(`)>operator(.)string<delimiter(`)content(classid)delimiter(`)> operator(=) string<delimiter(`)content(pc)delimiter(`)>operator(.)string<delimiter(`)content(classid)delimiter(`)>operator(\))operator(\))operator(\)) 
+         reserved(LEFT) reserved(JOIN) string<delimiter(`)content(program)delimiter(`)> string<delimiter(`)content(p)delimiter(`)> 
+           reserved(ON) operator(()operator(()string<delimiter(`)content(pc)delimiter(`)>operator(.)string<delimiter(`)content(programid)delimiter(`)> operator(=) string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(programid)delimiter(`)>operator(\))operator(\))operator(\)) 
+        reserved(LEFT) reserved(JOIN) string<delimiter(`)content(program)delimiter(`)> string<delimiter(`)content(mp)delimiter(`)> 
+          reserved(ON) operator(()operator(()string<delimiter(`)content(p)delimiter(`)>operator(.)string<delimiter(`)content(subprogramof)delimiter(`)> operator(=) string<delimiter(`)content(mp)delimiter(`)>operator(.)string<delimiter(`)content(programid)delimiter(`)>operator(\))operator(\))operator(\)) 
+reserved(WHERE)  operator(()reserved(NOT) operator(()operator(()string<delimiter(`)content(cc)delimiter(`)>operator(.)string<delimiter(`)content(description)delimiter(`)> reserved(LIKE) string<delimiter(')content(%{cs}%)delimiter(')>operator(\))operator(\))operator(\))
\ No newline at end of file
diff --git a/test/scanners/sql/mysql-long-queries.in.sql b/test/scanners/sql/mysql-long-queries.in.sql
new file mode 100644
index 0000000..5704d6b
--- /dev/null
+++ b/test/scanners/sql/mysql-long-queries.in.sql
@@ -0,0 +1,254 @@
+/* This is a modified copy of the
+  query linked above to test other keywords: */ 
+SELECT   sd.qbclass,                   -- Comments Test
+         Sum(sd.amount)   AS invoiceamount,          # Comments Test
+         Sum(scd1.amount) AS paymentsperiod1, 
+         Sum(scd2.amount) AS paymentsperiod2, 
+         Sum(scd3.amount) AS paymentsperiod3 
+FROM     studentdebit AS sd 
+         LEFT JOIN (SELECT studentcreditdetail.studentdebitid, 
+                           studentcreditdetail.amount, 
+                           studentcredit.date, 
+                           credittype.credittype 
+                    FROM   studentcreditdetail 
+                           INNER JOIN studentcredit 
+                             ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
+                                AND studentcredit.obsolete = 0 /* Not Deleted */ 
+                                AND studentcredit.status = 1   /* Successful  */ 
+                                /* PERIOD 1 */ 
+                                AND studentcredit.date < Now() 
+                                /* PERIOD 1 */ 
+                                AND studentcredit.date > Now() - INTERVAL 1 MONTH 
+                           LEFT JOIN credittype 
+                             USING(credittypeid)) AS scd1 
+           ON sd.studentdebitid = scd1.studentdebitid 
+         LEFT JOIN (SELECT studentcreditdetail.studentdebitid, 
+                           studentcreditdetail.amount, 
+                           studentcredit.date, 
+                           credittype.credittype 
+                    FROM   studentcreditdetail 
+                           INNER JOIN studentcredit 
+                             ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
+                                AND studentcredit.obsolete = 0 /* Not Deleted */ 
+                                AND studentcredit.status = 1   /* Successful  */ 
+                                /* PERIOD 2 */ 
+                                AND studentcredit.date < Now() - INTERVAL 1 MONTH 
+                                /* PERIOD 2 */ 
+                                AND studentcredit.date > Now() - INTERVAL 2 MONTH 
+                           LEFT OUTER JOIN credittype 
+                             USING(credittypeid)) AS scd2 
+           ON sd.studentdebitid = scd2.studentdebitid 
+         RIGHT JOIN (SELECT studentcreditdetail.studentdebitid, 
+                           studentcreditdetail.amount, 
+                           studentcredit.date, 
+                           credittype.credittype 
+                    FROM   studentcreditdetail 
+                           INNER JOIN studentcredit 
+                             ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
+                                AND studentcredit.obsolete = 0 /* Not Deleted */ 
+                                AND studentcredit.status = 1   /* Successful  */ 
+                                /* PERIOD 3 */ 
+                                AND studentcredit.date < Now() - INTERVAL 2 MONTH 
+                                /* PERIOD 3 */ 
+                                AND studentcredit.date > Now() - INTERVAL 3 MONTH 
+                           LEFT JOIN credittype 
+                             USING(credittypeid)) AS scd3 
+           ON sd.studentdebitid = scd3.studentdebitid 
+WHERE    sd.obsolete = 0   /* Not Deleted */ 
+         AND sd.status = 0 /* Normal      */ 
+         /* Exclude Voided Invoices */ 
+         AND sd.adjustsdebitid IS NULL 
+         AND sd.studentdebitid NOT IN (SELECT adjustsdebitid 
+                                       FROM   studentdebit 
+                                       WHERE  adjustsdebitid IS NOT NULL) 
+         /* FULL PERIOD */ 
+         AND sd.DATE < Now() 
+         /* FULL PERIOD */ 
+         AND sd.DATE > Now() - INTERVAL 3 MONTH 
+GROUP BY sd.qbclass 
+/* Formatting only */ 
+UNION ALL 
+SELECT '---', 
+       '---', 
+       '---', 
+       '---', 
+       '---' 
+/* Payment Types Summary */ 
+UNION DISTINCT 
+SELECT   credittype, 
+         invoiceamount, 
+         Sum(paymentsperiod1), 
+         Sum(paymentsperiod2), 
+         Sum(paymentsperiod3) 
+FROM     (SELECT   scd.credittype, 
+                   ''              AS invoiceamount, 
+                   Sum(scd.amount) AS paymentsperiod1, 
+                   ''              AS paymentsperiod2, 
+                   ''              AS paymentsperiod3 
+          FROM     studentdebit AS sd 
+                   INNER JOIN (SELECT studentcreditdetail.studentdebitid, 
+                                      studentcreditdetail.amount, 
+                                      studentcredit.date, 
+                                      credittype.credittype 
+                               FROM   studentcreditdetail 
+                                      INNER JOIN studentcredit 
+                                        ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
+                                           AND studentcredit.obsolete = 0 /* Not Deleted */ 
+                                           AND studentcredit.status = 1   /* Successful  */ 
+                                           /* PERIOD 1 */ 
+                                           AND studentcredit.date < Now() 
+                                           /* PERIOD 1 */ 
+                                           AND studentcredit.date > Now() - INTERVAL 1 MONTH 
+                                      LEFT JOIN credittype 
+                                        USING(credittypeid)) AS scd 
+                     ON sd.studentdebitid = scd.studentdebitid 
+          WHERE    sd.obsolete = 0   /* Not Deleted */ 
+                   AND sd.status = 0 /* Normal      */ 
+                   /* Exclude Voided Invoices */ 
+                   AND sd.adjustsdebitid IS NULL 
+                   AND sd.studentdebitid NOT IN (SELECT adjustsdebitid 
+                                                 FROM   studentdebit 
+                                                 WHERE  adjustsdebitid IS NOT NULL) 
+                   AND sd.DATE < Now() 
+                   AND sd.DATE > Now() - INTERVAL 3 MONTH 
+          GROUP BY scd.credittype 
+          UNION ALL 
+          SELECT   scd.credittype, 
+                   ''              AS invoiceamount, 
+                   ''              AS paymentsperiod1, 
+                   Sum(scd.amount) AS paymentsperiod2, 
+                   ''              AS paymentsperiod3 
+          FROM     studentdebit AS sd 
+                   INNER JOIN (SELECT studentcreditdetail.studentdebitid, 
+                                      studentcreditdetail.amount, 
+                                      studentcredit.date, 
+                                      credittype.credittype 
+                               FROM   studentcreditdetail 
+                                      INNER JOIN studentcredit 
+                                        ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
+                                           AND studentcredit.obsolete = 0 /* Not Deleted */ 
+                                           AND studentcredit.status = 1   /* Successful  */ 
+                                           /* PERIOD 2 */ 
+                                           AND studentcredit.date < Now() - INTERVAL 1 MONTH 
+                                           /* PERIOD 2 */ 
+                                           AND studentcredit.date > Now() - INTERVAL 2 MONTH 
+                                      LEFT JOIN credittype 
+                                        USING(credittypeid)) AS scd 
+                     ON sd.studentdebitid = scd.studentdebitid 
+          WHERE    sd.obsolete = 0   /* Not Deleted */ 
+                   AND sd.status = 0 /* Normal      */ 
+                   /* Exclude Voided Invoices */ 
+                   AND sd.adjustsdebitid IS NULL 
+                   AND sd.studentdebitid NOT IN (SELECT adjustsdebitid 
+                                                 FROM   studentdebit 
+                                                 WHERE  adjustsdebitid IS NOT NULL) 
+                   AND sd.DATE < Now() 
+                   AND sd.DATE > Now() - INTERVAL 3 MONTH 
+          GROUP BY scd.credittype 
+          UNION ALL 
+          SELECT   scd.credittype, 
+                   ''              AS invoiceamount, 
+                   ''              AS paymentsperiod1, 
+                   ''              AS paymentsperiod2, 
+                   Sum(scd.amount) AS paymentsperiod3 
+          FROM     studentdebit AS sd 
+                   INNER JOIN (SELECT studentcreditdetail.studentdebitid, 
+                                      studentcreditdetail.amount, 
+                                      studentcredit.date, 
+                                      credittype.credittype 
+                               FROM   studentcreditdetail 
+                                      INNER JOIN studentcredit 
+                                        ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
+                                           AND studentcredit.obsolete = 0 /* Not Deleted */ 
+                                           AND studentcredit.status = 1   /* Successful  */ 
+                                           /* PERIOD 3 */ 
+                                           AND studentcredit.date < Now() - INTERVAL 2 MONTH 
+                                           /* PERIOD 3 */ 
+                                           AND studentcredit.date > Now() - INTERVAL 3 MONTH 
+                                      LEFT JOIN credittype 
+                                        USING(credittypeid)) AS scd 
+                     ON sd.studentdebitid = scd.studentdebitid 
+          WHERE    sd.obsolete = 0   /* Not Deleted */ 
+                   AND sd.status = 0 /* Normal      */ 
+                   /* Exclude Voided Invoices */ 
+                   AND sd.adjustsdebitid IS NULL 
+                   AND sd.studentdebitid NOT IN (SELECT adjustsdebitid 
+                                                 FROM   studentdebit 
+                                                 WHERE  adjustsdebitid IS NOT NULL) 
+                   AND sd.date < Now() 
+                   AND sd.date > Now() - INTERVAL 3 MONTH 
+          GROUP BY scd.credittype) AS ct 
+GROUP BY ct.credittype
+
+SELECT 'mediaid'             AS `idtype`, 
+       `m`.`mediaid`         AS `id`, 
+       `m`.`title`           AS `title`, 
+       `m`.`description`     AS `description`, 
+       `m`.`source`          AS `source`, 
+       `m`.`date`            AS `startdate`, 
+       `m`.`date`            AS `enddate`, 
+       `c`.`class`           AS `class`, 
+       `c`.`classname`       AS `classname`, 
+       `per`.`firstname`     AS `firstname`, 
+       `per`.`lastname`      AS `lastname`, 
+       `c`.`description`     AS `classdesc`, 
+       `p`.`programid`       AS `programid`, 
+       If((`p`.`subprogramof` IS NOT NULL),`mp`.`programname`, 
+          `p`.`programname`) AS `programname`, 
+       'Recorded'            AS `longname` 
+FROM   ((((((((`media` `m` 
+               JOIN `mediaaudience` `ma` 
+                 ON (((`m`.`mediaid` = `ma`.`mediaid`) 
+                      AND (`ma`.`audiencetype` = 'Public') 
+                      AND ((`ma`.`enddate` < Now()) 
+                            OR Isnull(`ma`.`enddate`))))) 
+              LEFT JOIN `mediapresenter` `mpp` 
+                ON ((`m`.`mediaid` = `mpp`.`mediaid`))) 
+             LEFT JOIN `person` `per` 
+               ON ((`mpp`.`personid` = `per`.`personid`))) 
+            LEFT JOIN `mediaaudience` `mad` 
+              ON (((`ma`.`mediaid` = `mad`.`mediaid`) 
+                   AND (`mad`.`audiencetype` = 'classid')))) 
+           LEFT JOIN `class` `c` 
+             ON ((`mad`.`audienceid` = `c`.`classid`))) 
+          LEFT JOIN `program_class` `pc` 
+            ON ((`c`.`classid` = `pc`.`classid`))) 
+         LEFT JOIN `program` `p` 
+           ON ((`pc`.`programid` = `p`.`programid`))) 
+        LEFT JOIN `program` `mp` 
+          ON ((`p`.`subprogramof` = `mp`.`programid`))) 
+UNION 
+SELECT 'sectionid'           AS `idtype`, 
+       `cc`.`sectionid`      AS `id`, 
+       `cc`.`title`          AS `title`, 
+       `cc`.`description`    AS `description`, 
+       `l`.`mapurl`          AS `mapurl`, 
+       `cc`.`starttime`      AS `startdate`, 
+       `cc`.`endtime`        AS `enddate`, 
+       `c`.`class`           AS `class`, 
+       `c`.`classname`       AS `classname`, 
+       `per`.`firstname`     AS `firstname`, 
+       `per`.`lastname`      AS `lastname`, 
+       `c`.`description`     AS `classdesc`, 
+       `p`.`programid`       AS `programid`, 
+       If((`p`.`subprogramof` IS NOT NULL),`mp`.`programname`, 
+          `p`.`programname`) AS `programname`, 
+       `d`.`longname`        AS `longname` 
+FROM   ((((((((`calendarcache` `cc` 
+               JOIN `section` `s` 
+                 ON ((`cc`.`sectionid` = `s`.`sectionid`))) 
+              LEFT JOIN `person` `per` 
+                ON ((`s`.`teacherid` = `per`.`personid`))) 
+             LEFT JOIN `division` `d` 
+               ON ((`s`.`divisionid` = `d`.`divisionid`))) 
+            LEFT JOIN `location` `l` 
+              ON ((`s`.`locationid` = `l`.`locationid`))) 
+           LEFT JOIN `class` `c` 
+             ON ((`s`.`classid` = `c`.`classid`))) 
+          LEFT JOIN `program_class` `pc` 
+            ON ((`c`.`classid` = `pc`.`classid`))) 
+         LEFT JOIN `program` `p` 
+           ON ((`pc`.`programid` = `p`.`programid`))) 
+        LEFT JOIN `program` `mp` 
+          ON ((`p`.`subprogramof` = `mp`.`programid`))) 
+WHERE  (NOT ((`cc`.`description` LIKE '%{cs}%')))
\ No newline at end of file