Commits

Kirill Simonov committed c18b5fa

Contains (`~`) operator to use `ILIKE` when the right operand is a literal.

  • Participants
  • Parent commits 5a4b7b8

Comments (0)

Files changed (2)

src/htsql/tr/fn/function.py

 from ..assemble import Evaluate
 from ..reduce import Reduce
 from ..frame import (FunctionPhrase, IsNullPhrase, NullIfPhrase, IfNullPhrase,
-                     LiteralPhrase, TruePhrase, FalsePhrase)
+                     LiteralPhrase, TruePhrase, FalsePhrase, NullPhrase)
 from ..serializer import Serializer, Format, Serialize
 from ..coerce import coerce
 from ..lookup import lookup
         ContainsExpression, ContainsPhrase)
 ReduceContains = GenericReduce.factory(ContainsOperator,
         ContainsPhrase)
-SerializeContains = GenericSerialize.factory(ContainsOperator,
-        ContainsPhrase, "(POSITION(LOWER(%(right)s) IN LOWER(%(left)s)) > 0)")
+
+
+class SerializeContains(Serialize):
+
+    adapts(ContainsPhrase, Serializer)
+
+    def serialize(self):
+        left = self.serializer.serialize(self.phrase.left)
+        if isinstance(self.phrase.right, NullPhrase):
+            return self.serializer.serialize(self.phrase.right)
+        if isinstance(self.phrase.right, LiteralPhrase):
+            value = self.phrase.right.value
+            value = value.replace('%', '\\%').replace('_', '\\_')
+            value = "%%%s%%" % value
+            right = LiteralPhrase(value, self.phrase.right.domain,
+                                  self.phrase.right.expression)
+            right = self.serializer.serialize(right)
+            return self.format.ilike(left, right)
+        else:
+            right = self.serializer.serialize(self.phrase.right)
+            return self.format.contains(left, right)
 
 
 class FormatFunctions(Format):
     def not_among(self, lop, rops):
         return "(%s NOT IN (%s))" % (lop, ", ".join(rops))
 
+    def contains(self, left, right):
+        return "(POSITION(LOWER(%(right)s) IN LOWER(%(left)s)) > 0)" \
+                % (left, right)
+
+    def ilike(self, left, right):
+        return "(%s ILIKE %s)" % (left, right)
+
     def round_fn(self, value, digits=None):
         if digits is None:
             return "ROUND(%s)" % value

test/output/pgsql.yaml

 
              ----
              /{'HTSQL'~'sql','HTSQL'~'HTTP','HTSQL'~'','HTSQL'~null(),null()~'HTSQL',null()~null()}
-             SELECT (POSITION(LOWER('sql') IN LOWER('HTSQL')) > 0), (POSITION(LOWER('HTTP') IN LOWER('HTSQL')) > 0), (POSITION(LOWER('') IN LOWER('HTSQL')) > 0), (POSITION(LOWER(NULL) IN LOWER('HTSQL')) > 0), (POSITION(LOWER('HTSQL') IN LOWER(NULL)) > 0), (POSITION(LOWER(NULL) IN LOWER(NULL)) > 0)
+             SELECT ('HTSQL' ILIKE '%sql%'), ('HTSQL' ILIKE '%HTTP%'), ('HTSQL' ILIKE '%%'), NULL, (NULL ILIKE '%HTSQL%'), NULL
       - id: date-functions-and-operators
         tests:
         - uri: /{date('2010-07-28'),date(string('2010-07-28'))}
 
          ----
          /school{code,name,this(){code}?name!~'art',root().school{code}?name!~'art'}
-         SELECT "school_1"."code", "school_1"."name", "school_2"."code", "school_2"."code" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT "school"."code" FROM "ad"."school" AS "school" WHERE (NOT (POSITION(LOWER('art') IN LOWER("school"."name")) > 0))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY 1 ASC
+         SELECT "school_1"."code", "school_1"."name", "school_2"."code", "school_2"."code" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT "school"."code" FROM "ad"."school" AS "school" WHERE (NOT ("school"."name" ILIKE '%art%'))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY 1 ASC
     - uri: /school{name,count(department),this(){count(department)}?name!~'art'}
       status: 200 OK
       headers:
 
          ----
          /school{name,count(department),this(){count(department)}?name!~'art'}
-         SELECT "school_1"."name", COALESCE("department"."!", 0), "school_2"."!" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "!", "department"."school" FROM "ad"."department" AS "department" GROUP BY 2) AS "department" ON (("school_1"."code" = "department"."school")) LEFT OUTER JOIN (SELECT COALESCE("department"."!", 0) AS "!", "school"."code" FROM "ad"."school" AS "school" LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "!", "department"."school" FROM "ad"."department" AS "department" GROUP BY 2) AS "department" ON (("school"."code" = "department"."school")) WHERE (NOT (POSITION(LOWER('art') IN LOWER("school"."name")) > 0))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY "school_1"."code" ASC
+         SELECT "school_1"."name", COALESCE("department"."!", 0), "school_2"."!" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "!", "department"."school" FROM "ad"."department" AS "department" GROUP BY 2) AS "department" ON (("school_1"."code" = "department"."school")) LEFT OUTER JOIN (SELECT COALESCE("department"."!", 0) AS "!", "school"."code" FROM "ad"."school" AS "school" LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "!", "department"."school" FROM "ad"."department" AS "department" GROUP BY 2) AS "department" ON (("school"."code" = "department"."school")) WHERE (NOT ("school"."name" ILIKE '%art%'))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY "school_1"."code" ASC
     - uri: /school{name,exists(department),this(){exists(department)}?name!~'art'}
       status: 200 OK
       headers:
 
          ----
          /school{name,exists(department),this(){exists(department)}?name!~'art'}
-         SELECT "school_1"."name", EXISTS((SELECT TRUE AS "!" FROM "ad"."department" AS "department" WHERE ("school_1"."code" = "department"."school"))), "school_2"."!" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT EXISTS((SELECT TRUE AS "!" FROM "ad"."department" AS "department" WHERE ("school"."code" = "department"."school"))) AS "!", "school"."code" FROM "ad"."school" AS "school" WHERE (NOT (POSITION(LOWER('art') IN LOWER("school"."name")) > 0))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY "school_1"."code" ASC
+         SELECT "school_1"."name", EXISTS((SELECT TRUE AS "!" FROM "ad"."department" AS "department" WHERE ("school_1"."code" = "department"."school"))), "school_2"."!" FROM "ad"."school" AS "school_1" LEFT OUTER JOIN (SELECT EXISTS((SELECT TRUE AS "!" FROM "ad"."department" AS "department" WHERE ("school"."code" = "department"."school"))) AS "!", "school"."code" FROM "ad"."school" AS "school" WHERE (NOT ("school"."name" ILIKE '%art%'))) AS "school_2" ON (("school_1"."code" = "school_2"."code")) ORDER BY "school_1"."code" ASC
   - id: table-expressions
     tests:
     - uri: /(school?code='art').department
 
          ----
          /course?title~'lab'
-         SELECT "course"."department", "course"."number", "course"."title", "course"."credits", "course"."description" FROM "ad"."course" AS "course" WHERE (POSITION(LOWER('lab') IN LOWER("course"."title")) > 0) ORDER BY 1 ASC, 2 ASC
+         SELECT "course"."department", "course"."number", "course"."title", "course"."credits", "course"."description" FROM "ad"."course" AS "course" WHERE ("course"."title" ILIKE '%lab%') ORDER BY 1 ASC, 2 ASC
     - uri: /course?title!= 'Organic Chemistry Laboratory I'
       status: 200 OK
       headers: