Commits

Kirill Simonov committed 554cc0c

Added is_null(), if_null(), null_if() functions.

Comments (0)

Files changed (4)

 =======================================================
 
 HTSQL ("Hyper Text Structured Query Language") is a schema-driven URI-to-SQL
-translator that takes a request over HTTP, converts it to a SQL query, executes
-the query against a database, and returns the results in a format best suited
-for the user agent (CSV, HTML, etc.).
+translator that takes a request over HTTP, converts it to a SQL query,
+executes the query against a database, and returns the results in a format
+best suited for the user agent (CSV, HTML, etc.).
 
 HTSQL is copyright by Prometheus Research, LLC.  See the file ``LICENSE``
 for details.  HTSQL is written by Clark C. Evans <cce@clarkevans.com> and

src/htsql/tr/fn/function.py

 EvaluateConcatenation = GenericEvaluate.factory(AdditionOperator,
         ConcatenationExpression, ConcatenationPhrase,
         is_null_regular=False, is_nullable=False)
-SerializeConcatenation = GenericSerialize.factory(AdditionOperator,
-        ConcatenationPhrase,
-        "(COALESCE(%(left)s, '') || COALESCE(%(right)s, ''))")
+
+
+class SerializeConcatenation(Serialize):
+
+    adapts(ConcatenationPhrase, Serializer)
+
+    def serialize(self):
+        left = self.serializer.serialize(self.phrase.left)
+        if self.phrase.left.is_nullable:
+            left = self.format.concat_wrapper(left)
+        right = self.serializer.serialize(self.phrase.right)
+        if self.phrase.right.is_nullable:
+            right = self.format.concat_wrapper(right)
+        return self.format.concat_op(left, right)
 
 
 class Concatenate(Add):
     adapts(UntypedDomain, UntypedDomain)
 
 
+class IsNullFunction(ProperFunction):
+
+    adapts(named['is_null'])
+
+    parameters = [
+            Parameter('expression'),
+    ]
+
+    def correlate(self, expression, syntax, parent):
+        domain = self.binder.coerce(expression.domain)
+        if domain is None:
+            raise InvalidArgumentError("unexpected domain",
+                                       expression.mark)
+        expression = self.binder.cast(expression, domain)
+        yield IsNullBinding(parent, BooleanDomain(), syntax,
+                            expression=expression)
+
+
+IsNullBinding = GenericBinding.factory(IsNullFunction)
+IsNullExpression = GenericExpression.factory(IsNullFunction)
+IsNullPhrase = GenericPhrase.factory(IsNullFunction)
+
+
+EncodeIsNull = GenericEncode.factory(IsNullFunction,
+        IsNullBinding, IsNullExpression)
+EvaluateIsNull = GenericEvaluate.factory(IsNullFunction,
+        IsNullExpression, IsNullPhrase,
+        is_null_regular=False, is_nullable=False)
+SerializeIsNull = GenericSerialize.factory(IsNullFunction,
+        IsNullPhrase, "(%(expression)s IS NULL)")
+
+
+class NullIfMethod(ProperMethod):
+
+    adapts(named['null_if'])
+
+    parameters = [
+            Parameter('this'),
+            Parameter('expressions', is_list=True),
+    ]
+
+    def correlate(self, this, expressions, syntax, parent):
+        domain = this.domain
+        for expression in expressions:
+            domain = self.binder.coerce(domain, expression.domain)
+            if domain is None:
+                raise InvalidArgumentError("unexpected domain",
+                                           expression.mark)
+        domain = self.binder.coerce(domain)
+        if domain is None:
+            raise InvalidArgumentError("inexpected domain",
+                                       this.mark)
+        this = self.binder.cast(this, domain)
+        expressions = [self.binder.cast(expression, domain)
+                       for expression in expressions]
+        yield NullIfBinding(parent, domain, syntax,
+                            this=this, expressions=expressions)
+
+
+NullIfBinding = GenericBinding.factory(NullIfMethod)
+NullIfExpression = GenericExpression.factory(NullIfMethod)
+NullIfPhrase = GenericPhrase.factory(NullIfMethod)
+
+
+EncodeNullIf = GenericEncode.factory(NullIfMethod,
+        NullIfBinding, NullIfExpression)
+EvaluateNullIf = GenericEvaluate.factory(NullIfMethod,
+        NullIfExpression, NullIfPhrase,
+        is_null_regular=False)
+
+
+class SerializeNullIf(Serialize):
+
+    adapts(NullIfPhrase, Serializer)
+
+    def serialize(self):
+        left = self.serializer.serialize(self.phrase.this)
+        for expression in self.phrase.expressions:
+            right = self.serializer.serialize(expression)
+            left = self.format.nullif_fn(left, right)
+        return left
+
+
+class IfNullMethod(ProperMethod):
+
+    adapts(named['if_null'])
+
+    parameters = [
+            Parameter('this'),
+            Parameter('expressions', is_list=True),
+    ]
+
+    def correlate(self, this, expressions, syntax, parent):
+        domain = this.domain
+        for expression in expressions:
+            domain = self.binder.coerce(domain, expression.domain)
+            if domain is None:
+                raise InvalidArgumentError("unexpected domain",
+                                           expression.mark)
+        domain = self.binder.coerce(domain)
+        if domain is None:
+            raise InvalidArgumentError("inexpected domain",
+                                       this.mark)
+        this = self.binder.cast(this, domain)
+        expressions = [self.binder.cast(expression, domain)
+                       for expression in expressions]
+        yield IfNullBinding(parent, domain, syntax,
+                            this=this, expressions=expressions)
+
+
+IfNullBinding = GenericBinding.factory(IfNullMethod)
+IfNullExpression = GenericExpression.factory(IfNullMethod)
+IfNullPhrase = GenericPhrase.factory(IfNullMethod)
+
+
+EncodeIfNull = GenericEncode.factory(IfNullMethod,
+        IfNullBinding, IfNullExpression)
+EvaluateIfNull = GenericEvaluate.factory(IfNullMethod,
+        IfNullExpression, IfNullPhrase)
+
+
+class SerializeIfNull(Serialize):
+
+    adapts(IfNullPhrase, Serializer)
+
+    def serialize(self):
+        arguments = [self.serializer.serialize(self.phrase.this)]
+        for expression in self.phrase.expressions:
+            arguments.append(self.serializer.serialize(expression))
+        return self.format.coalesce_fn(arguments)
+
+
 class FormatFunctions(Format):
 
     weights(0)
     def concat_op(self, left, right):
         return "(%s || %s)" % (left, right)
 
+    def concat_wrapper(self, expr):
+        return "COALESCE(%s, '')"
+
     def count_fn(self, condition):
         return "COUNT(NULLIF(%s, FALSE))" % condition
 
     def count_wrapper(self, aggregate):
         return "COALESCE(%s, 0)" % aggregate
 
+    def nullif_fn(self, left, right):
+        return "NULLIF(%s, %s)" % (left, right)
+
+    def coalesce_fn(self, arguments):
+        return "COALESCE(%s)" % ", ".join(arguments)
+
 
 class CountFunction(ProperFunction):
 

test/input/pgsql.yaml

                  null()&false(),null()&true(),null()&null()}
         # Auto-cast of arguments (false,true).
         - uri: /{string('')&string('X'),0&1}
-        # Auto-cast of NULL values (null,false,false)
+        # Auto-cast of NULL values (null,false,false).
         - uri: /{null()&null(),integer(null())&null(),integer(null())&integer(null())}
         # The OR operator.
         - uri: /{false()|false(),false()|true(),true()|false(),true()|true(),
         - uri: /{!true(),!false(),!null()}
         # Auto-cast of arguments (true,false,false,true).
         - uri: /{!string(''),!string('X'),!1,!integer(null())}
+        # Is NULL function (null => true, otherwise => false).
+        - uri: /{is_null(null()),is_null(true()),is_null(''),is_null(0)}
+        # Null If method (`this` is equal to one of the arguments => null, otherwise => `this`).
+        - uri: /{'Y'.null_if('X'),'Y'.null_if('Y'),
+                 (5).null_if(1,2,3),(5).null_if(1,2,3,4,5,6,7,8,9,10),
+                 null().null_if(null()),null().null_if('X')}
+        # If Null method (`this` is null => the first non-null argument, otherwise => `this`).
+        - uri: /{'X'.if_null('Y'),null().if_null('X'),null().if_null(null()),
+                 null().if_null(null(),null(),null()),
+                 null().if_null(null(),null(),0),
+                 null().if_null(0,1,2,3,null())}
 
   # Simple (non-aggregate) filters.
   - title: Simple filters

test/output/pgsql.yaml

 
          ----
          /{'HT'+'SQL'}
-         SELECT (COALESCE('HT', '') || COALESCE('SQL', ''))
+         SELECT ('HT' || 'SQL')
     - id: literals
       tests:
       - uri: "/{'','HTSQL','O''Reilly','\u03BB\u03CC\u03B3\u03BF\u03C2', '%01%02%03%04%05%06%07%08%09%0A%0B%0C%0D%0E%0F',
              ----
              /{!string(''),!string('X'),!1,!integer(null())}
              SELECT (NOT (NULLIF('', '') IS NOT NULL)), (NOT (NULLIF('X', '') IS NOT NULL)), (NOT (1 IS NOT NULL)), (NOT (NULL IS NOT NULL))
+        - uri: /{is_null(null()),is_null(true()),is_null(''),is_null(0)}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | /{is_null(null()),is_null(true()),is_null(''),is_null(0)}    |
+            -+--------------------------------------------------------------+-
+             | is_null(null()) | is_null(true()) | is_null('') | is_null(0) |
+            -+-----------------+-----------------+-------------+------------+-
+             | true            | false           | false       | false      |
+                                                                      (1 row)
+
+             ----
+             /{is_null(null()),is_null(true()),is_null(''),is_null(0)}
+             SELECT (NULL IS NULL), (TRUE IS NULL), ('' IS NULL), (0 IS NULL)
+        - uri: /{'Y'.null_if('X'),'Y'.null_if('Y'), (5).null_if(1,2,3),(5).null_if(1,2,3,4,5,6,7,8,9,10),
+            null().null_if(null()),null().null_if('X')}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | /{'Y'.null_if('X'),'Y'.null_if('Y'),(5).null_if(1,2,3),(5).null_if(1,2,3,4,5,6,7,8,9,10),null().null_if(null()),null().null_if('X')}        |
+            -+---------------------------------------------------------------------------------------------------------------------------------------------+-
+             | 'Y'.null_if('X') | 'Y'.null_if('Y') | (5).null_if(1,2,3) | (5).null_if(1,2,3,4,5,6,7,8,9,10) | null().null_if(null()) | null().null_if('X') |
+            -+------------------+------------------+--------------------+-----------------------------------+------------------------+---------------------+-
+             | Y                |                  |                  5 |                                   |                        |                     |
+                                                                                                                                                     (1 row)
+
+             ----
+             /{'Y'.null_if('X'),'Y'.null_if('Y'),(5).null_if(1,2,3),(5).null_if(1,2,3,4,5,6,7,8,9,10),null().null_if(null()),null().null_if('X')}
+             SELECT NULLIF('Y', 'X'), NULLIF('Y', 'Y'), NULLIF(NULLIF(NULLIF(5, 1), 2), 3), NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(5, 1), 2), 3), 4), 5), 6), 7), 8), 9), 10), NULLIF(NULL, NULL), NULLIF(NULL, 'X')
+        - uri: /{'X'.if_null('Y'),null().if_null('X'),null().if_null(null()), null().if_null(null(),null(),null()),
+            null().if_null(null(),null(),0), null().if_null(0,1,2,3,null())}
+          status: 200 OK
+          headers:
+          - [Content-Type, text/plain; charset=UTF-8]
+          body: |2
+             | /{'X'.if_null('Y'),null().if_null('X'),null().if_null(null()),null().if_null(null(),null(),null()),null().if_null(null(),null(),0),null().if_null(0,1,2,3,null())}        |
+            -+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-
+             | 'X'.if_null('Y') | null().if_null('X') | null().if_null(null()) | null().if_null(null(),null(),null()) | null().if_null(null(),null(),0) | null().if_null(0,1,2,3,null()) |
+            -+------------------+---------------------+------------------------+--------------------------------------+---------------------------------+--------------------------------+-
+             | X                | X                   |                        |                                      |                               0 |                              0 |
+                                                                                                                                                                                   (1 row)
+
+             ----
+             /{'X'.if_null('Y'),null().if_null('X'),null().if_null(null()),null().if_null(null(),null(),null()),null().if_null(null(),null(),0),null().if_null(0,1,2,3,null())}
+             SELECT COALESCE('X', 'Y'), COALESCE(NULL, 'X'), COALESCE(NULL, NULL), COALESCE(NULL, NULL, NULL, NULL), COALESCE(NULL, NULL, NULL, 0), COALESCE(NULL, 0, 1, 2, 3, NULL)
   - id: simple-filters
     tests:
     - uri: /school?code='ns'
 
          ----
          /department{school.name+' - '+name}
-         SELECT (COALESCE((COALESCE("school"."name", '') || COALESCE(' - ', '')), '') || COALESCE("department"."name", '')) FROM "ad"."department" AS "department" INNER JOIN "ad"."school" AS "school" ON (("department"."school" = "school"."code")) ORDER BY "department"."code" ASC
+         SELECT (("school"."name" || ' - ') || "department"."name") FROM "ad"."department" AS "department" INNER JOIN "ad"."school" AS "school" ON (("department"."school" = "school"."code")) ORDER BY "department"."code" ASC
   - id: aggregates
     tests:
     - uri: /{count(school),count(department),count(course)}