Commits

Kirill Simonov committed 5b9d672

Completed the port to MS SQL Server.

Comments (0)

Files changed (21)

src/htsql/tr/assemble.py

                     LiteralPhrase, TruePhrase, CastPhrase,
                     ColumnPhrase, ReferencePhrase, EmbeddingPhrase,
                     FormulaPhrase, Anchor, LeadingAnchor)
-from .signature import (Signature, IsEqualSig, IsTotallyEqualSig, IsNullSig,
-                        NullIfSig, IfNullSig, AndSig)
+from .signature import (Signature, IsEqualSig, IsTotallyEqualSig, IsInSig,
+                        IsNullSig, NullIfSig, IfNullSig, CompareSig,
+                        AndSig, OrSig, NotSig, ToPredicateSig,
+                        FromPredicateSig)
 
 
 class Claim(Comparable, Printable):
         phrase = evaluate()
         # Restore the original gate.
         self.pop_gate()
-        # Return the generate phrase.
+        # Return the generated phrase.
         return phrase
 
     def demand(self, claim):
         # Save the phrase.
         self.phrase_by_claim[claim] = phrase
 
+    def to_predicate(self, phrase):
+        return FormulaPhrase(ToPredicateSig(), phrase.domain,
+                             phrase.is_nullable, phrase.expression, op=phrase)
+
+    def from_predicate(self, phrase):
+        return FormulaPhrase(FromPredicateSig(), phrase.domain,
+                             phrase.is_nullable, phrase.expression, op=phrase)
+
 
 class Assemble(Adapter):
     """
         # Assemble a `WHERE` clause.
         # Evaluate the `filter` expression (we expect all its claims
         # to be already satisfied).
-        return self.state.evaluate(self.term.filter,
-                                   router=self.term.kid)
+        phrase = self.state.evaluate(self.term.filter,
+                                     router=self.term.kid)
+        return self.state.to_predicate(phrase)
 
 
 class AssembleOrder(Assemble):
                                       ops=equalities)
         elif self.term.is_left or self.term.is_right:
             condition = TruePhrase(self.term.expression)
+            condition = self.state.to_predicate(condition)
         # Generate a `JOIN` clause for the second subframe.
         ranchor = Anchor(rframe, condition,
                          self.term.is_left, self.term.is_right)
                              **arguments)
 
 
-class EvaluateIsTotallyEqual(EvaluateBySignature):
+class EvaluateIsEqualBase(EvaluateBySignature):
+
+    adapts_many(IsEqualSig, IsInSig, CompareSig)
+
+    def __call__(self):
+        phrase = super(EvaluateIsEqualBase, self).__call__()
+        return self.state.from_predicate(phrase)
+
+
+class EvaluateIsTotallyEqualBase(EvaluateBySignature):
     """
     Evaluates the total equality (``==``) operator.
     """
 
-    adapts(IsTotallyEqualSig)
+    adapts_many(IsTotallyEqualSig, IsNullSig)
 
     def __call__(self):
         # Override the default implementation since the total equality
         # operator is not null-regular, and, in fact, always not nullable.
         arguments = self.arguments.map(self.state.evaluate)
-        return FormulaPhrase(self.signature, self.domain,
-                             False, self.code, **arguments)
-
-
-class EvaluateIsNull(EvaluateBySignature):
-    """
-    Evaluates the ``is_null()`` operator.
-    """
-
-    adapts(IsNullSig)
-
-    def __call__(self):
-        # Override the default implementation since the `is_null()`
-        # operator is not null-regular, and, in fact, always not nullable.
-        arguments = self.arguments.map(self.state.evaluate)
-        return FormulaPhrase(self.signature, self.domain,
-                             False, self.code, **arguments)
+        phrase = FormulaPhrase(self.signature, self.domain,
+                               False, self.code, **arguments)
+        return self.state.from_predicate(phrase)
 
 
 class EvaluateNullIf(EvaluateBySignature):
                              is_nullable, self.code, **arguments)
 
 
+class EvaluateAndOrNot(EvaluateBySignature):
+
+    adapts_many(AndSig, OrSig, NotSig)
+
+    def __call__(self):
+        arguments = (self.arguments.map(self.state.evaluate)
+                                   .map(self.state.to_predicate))
+        is_nullable = any(cell.is_nullable for cell in arguments.cells())
+        phrase = FormulaPhrase(self.signature,
+                               self.domain,
+                               is_nullable,
+                               self.code,
+                               **arguments)
+        return self.state.from_predicate(phrase)
+
+
 class EvaluateUnit(Evaluate):
     """
     Evaluates a unit.

src/htsql/tr/dump.py

                     FormulaPhrase, Anchor, LeadingAnchor)
 from .signature import (Signature, isformula, IsEqualSig, IsTotallyEqualSig,
                         IsInSig, IsNullSig, IfNullSig, NullIfSig, CompareSig,
-                        AndSig, OrSig, NotSig)
+                        AndSig, OrSig, NotSig, ToPredicateSig,
+                        FromPredicateSig)
 from .plan import Plan
 import StringIO
 import re
                     self.arguments, self.signature)
 
 
+class DumpToPredicate(DumpBySignature):
+
+    adapts(ToPredicateSig)
+
+    def __call__(self):
+        return self.state.dump(self.phrase.op)
+
+
+class DumpFromPredicate(DumpBySignature):
+
+    adapts(FromPredicateSig)
+
+    def __call__(self):
+        return self.state.dump(self.phrase.op)
+
+
 def serialize(clause, state=None):
     """
     Translates a clause node to SQL.

src/htsql/tr/fn/assemble.py

 from ...adapter import adapts, adapts_none
 from ..assemble import EvaluateBySignature
 from ..frame import FormulaPhrase
-from .signature import ConcatenateSig, ExistsSig, CountSig
+from .signature import (ConcatenateSig, ExistsSig, CountSig, ContainsSig,
+                        LikeSig, IfSig, SwitchSig)
 
 
 class EvaluateFunction(EvaluateBySignature):
 
     is_null_regular = True
     is_nullable = True
+    is_predicate = False
 
     def __call__(self):
         arguments = self.arguments.map(self.state.evaluate)
             is_nullable = any(cell.is_nullable for cell in arguments.cells())
         else:
             is_nullable = self.is_nullable
-        return FormulaPhrase(self.signature,
-                             self.domain,
-                             is_nullable,
-                             self.code,
-                             **arguments)
+        phrase = FormulaPhrase(self.signature,
+                               self.domain,
+                               is_nullable,
+                               self.code,
+                               **arguments)
+        if self.is_predicate:
+            phrase = self.state.from_predicate(phrase)
+        return phrase
 
 
 class EvaluateWrapExists(EvaluateFunction):
     adapts(ExistsSig)
     is_null_regular = False
     is_nullable = False
+    is_predicate = True
 
 
 class EvaluateTakeCount(EvaluateFunction):
     is_nullable = False
 
 
+class EvaluateContains(EvaluateFunction):
+
+    adapts(ContainsSig)
+    is_predicate = True
+
+
+class EvalutateLike(EvaluateFunction):
+
+    adapts(LikeSig)
+    is_predicate = True
+
+
+class EvaluateIf(EvaluateFunction):
+
+    adapts(IfSig)
+
+    def __call__(self):
+        arguments = self.arguments.map(self.state.evaluate)
+        predicates = arguments['predicates']
+        consequents = arguments['consequents']
+        alternative = arguments['alternative']
+        predicates = [self.state.to_predicate(cell) for cell in predicates]
+        is_nullable = any(cell.is_nullable for cell in consequents)
+        if alternative is None or alternative.is_nullable:
+            is_nullable = True
+        phrase = FormulaPhrase(self.signature, self.domain, is_nullable,
+                               self.code, predicates=predicates,
+                               consequents=consequents,
+                               alternative=alternative)
+        return phrase
+
+
+class EvaluateSwitch(EvaluateFunction):
+
+    adapts(SwitchSig)
+
+    def __call__(self):
+        arguments = self.arguments.map(self.state.evaluate)
+        consequents = arguments['consequents']
+        alternative = arguments['alternative']
+        is_nullable = any(cell.is_nullable for cell in consequents)
+        if alternative is None or alternative.is_nullable:
+            is_nullable = True
+        phrase = FormulaPhrase(self.signature, self.domain, is_nullable,
+                               self.code, **arguments)
+        return phrase
+
+
+
+

src/htsql/tr/fn/rewrite.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+from ...adapter import adapts, adapts_none
+from ..code import LiteralCode, FormulaCode
+from ..rewrite import RewriteBySignature
+from .signature import SubstringSig
+
+
+class RewriteFunction(RewriteBySignature):
+
+    adapts_none()
+    is_null_regular = False
+
+    def __call__(self):
+        arguments = self.arguments.map(self.state.rewrite)
+        if self.is_null_regular:
+            for cell in arguments.cells():
+                if isinstance(cell, LiteralCode) and cell.value is None:
+                    return LiteralCode(None, self.domain, self.code.binding)
+        return FormulaCode(self.signature, self.domain,
+                           self.code.binding, **arguments)
+
+
+class RewriteSubstring(RewriteFunction):
+
+    adapts(SubstringSig)
+    is_null_regular = True
+
+

src/htsql/tr/reduce.py

 from ..domain import BooleanDomain, StringDomain
 from .coerce import coerce
 from .compile import ordering
+from .term import PermanentTerm
 from .frame import (Clause, Frame, ScalarFrame, BranchFrame, NestedFrame,
                     QueryFrame, Phrase, LiteralPhrase, NullPhrase,
                     TruePhrase, FalsePhrase, CastPhrase, FormulaPhrase,
                     ExportPhrase, ReferencePhrase, Anchor, LeadingAnchor)
 from .signature import (Signature, isformula, IsEqualSig, IsTotallyEqualSig,
                         IsInSig, IsNullSig, IfNullSig, NullIfSig,
-                        AndSig, OrSig, NotSig)
+                        AndSig, OrSig, NotSig, FromPredicateSig,
+                        ToPredicateSig)
 
 
 class ReducingState(object):
         collapse = Collapse(frame, self)
         return collapse()
 
+    def to_predicate(self, phrase):
+        phrase = FormulaPhrase(ToPredicateSig(), phrase.domain,
+                               phrase.is_nullable, phrase.expression,
+                               op=phrase)
+        return self.reduce(phrase)
+
+    def from_predicate(self, phrase):
+        phrase = FormulaPhrase(FromPredicateSig(), phrase.domain,
+                               phrase.is_nullable, phrase.expression,
+                               op=phrase)
+        return self.reduce(phrase)
+
 
 class Reduce(Adapter):
     """
         # The rest of the `FROM` clause.
         tail = self.frame.include[1:]
 
+        # A frame corresponding to a permanent term is never collapsed down.
+        if isinstance(head.term, PermanentTerm):
+            return self.frame
+
         # Any subframe, including the head frame, is one of theses:
         # - a scalar frame, we could just discard it;
         # - a table frame, there is nothing we can do;
         if isinstance(lop, NullPhrase) and isinstance(rop, NullPhrase):
             # Reduce:
             #   null()=null(), null()!=null() => null()
-            return NullPhrase(self.phrase.domain, self.phrase.expression)
+            return self.state.to_predicate(
+                    NullPhrase(self.phrase.domain, self.phrase.expression))
 
         # Now suppose one of the operands (`rop`) is `NULL`.
         if isinstance(lop, NullPhrase):
             # aggregate expression).  So we only do that when `lop` is a
             # literal.
             if isinstance(lop, LiteralPhrase):
-                return NullPhrase(self.phrase.domain, self.phrase.expression)
+                return self.state.to_predicate(
+                        NullPhrase(self.phrase.domain, self.phrase.expression))
 
         # Check if both arguments are boolean literals.
         if (isinstance(lop, (TruePhrase, FalsePhrase)) and
             if lop.value != rop.value:
                 polarity = -polarity
             if polarity > 0:
-                return TruePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        TruePhrase(self.phrase.expression))
             else:
-                return FalsePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        FalsePhrase(self.phrase.expression))
 
         # None of specific optimizations were applied, just return
         # the same operator with reduced operands.  Update the `is_nullable`
             # Reduce:
             #   null()==null() => true, null()!==null() => false()
             if polarity > 0:
-                return TruePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        TruePhrase(self.phrase.expression))
             else:
-                return FalsePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        FalsePhrase(self.phrase.expression))
 
         # Now suppose one of the operands (`rop`) is `NULL`.
         if isinstance(lop, NullPhrase):
             # optimization when `lop` is a literal.
             if isinstance(lop, LiteralPhrase):
                 if polarity > 0:
-                    return FalsePhrase(self.phrase.expression)
+                    return self.state.to_predicate(
+                            FalsePhrase(self.phrase.expression))
                 else:
-                    return TruePhrase(self.phrase.expression)
+                    return self.state.to_predicate(
+                            TruePhrase(self.phrase.expression))
             return FormulaPhrase(IsNullSig(polarity), self.domain,
                                  False, self.phrase.expression, op=lop)
 
             # arbitrary type because we cannot precisely replicate the
             # database equality operator.
             if polarity * (-1 if (lop.value != rop.value) else +1):
-                return TruePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        TruePhrase(self.phrase.expression))
             else:
-                return FalsePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        FalsePhrase(self.phrase.expression))
 
         # When both operands are not nullable, we could replace a total
         # comparison operator with a regular one.
         # on the right are literals.
         if isinstance(lop, NullPhrase):
             if all(isinstance(rop, LiteralPhrase) for rop in rops):
-                return NullPhrase(self.domain, self.phrase.expression)
+                return self.state.to_predicate(
+                        NullPhrase(self.domain, self.phrase.expression))
         # Similarly, reduce:
         #   x={null(),null(),...}
         if all(isinstance(rop, NullPhrase) for rop in rops):
             if isinstance(lop, LiteralPhrase):
-                return NullPhrase(self.domain, self.phrase.expression)
+                return self.state.to_predicate(
+                        NullPhrase(self.domain, self.phrase.expression))
 
         # Reduce:
         #   x={y} => x=y
         #   !is_null(null()) => false()
         if isinstance(op, NullPhrase):
             if self.signature.polarity > 0:
-                return TruePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        TruePhrase(self.phrase.expression))
             else:
-                return FalsePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        FalsePhrase(self.phrase.expression))
         # If the operand is not nullable, we could reduce the operator
         # to a `TRUE` or a `FALSE` clause.  However it is only safe
         # to do for a literal operand.
         if isinstance(op, LiteralPhrase):
             if self.signature.polarity > 0:
-                return FalsePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        FalsePhrase(self.phrase.expression))
             else:
-                return TruePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        TruePhrase(self.phrase.expression))
 
         # Return the same operator with a reduced operand.
         return self.phrase.clone(op=op)
         # Reduce:
         #   "&"() => true()
         if not ops:
-            return TruePhrase(self.phrase.expression)
+            return self.state.to_predicate(TruePhrase(self.phrase.expression))
         # Reduce:
         #   "&"(op) => op
         if len(ops) == 1:
         # do that when all operands are literals.
         if any(isinstance(op, FalsePhrase) for op in ops):
             if all(isinstance(op, LiteralPhrase) for op in ops):
-                return FalsePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        FalsePhrase(self.phrase.expression))
 
         # Return the same operator with reduced operands.  Update
         # the `is_nullable` status since it could change after reducing
         # Reduce:
         #   "|"() => false()
         if not ops:
-            return FalsePhrase(self.phrase.expression)
+            return self.state.to_predicate(FalsePhrase(self.phrase.expression))
         # Reduce:
         #   "|"(op) => op
         if len(ops) == 1:
         # do that when all operands are literals.
         if any(isinstance(op, TruePhrase) for op in ops):
             if all(isinstance(op, LiteralPhrase) for op in ops):
-                return TruePhrase(self.phrase.expression)
+                return self.state.to_predicate(
+                        TruePhrase(self.phrase.expression))
 
         return self.phrase.clone(ops=ops)
         # Return the same operator with reduced operands.  Update
         #   !true() => false()
         #   !false() => true()
         if isinstance(op, NullPhrase):
-            return NullPhrase(self.phrase.domain, self.phrase.expression)
+            return self.state.to_predicate(
+                    NullPhrase(self.phrase.domain, self.phrase.expression))
         if isinstance(op, TruePhrase):
-            return FalsePhrase(self.phrase.expression)
+            return self.state.to_predicate(FalsePhrase(self.phrase.expression))
         if isinstance(op, FalsePhrase):
-            return TruePhrase(self.phrase.expression)
+            return self.state.to_predicate(TruePhrase(self.phrase.expression))
         # Reverse polarity of equality operators:
         #   !(lop=rop) => lop!=rop
         #   ...
         return self.phrase.clone(is_nullable=op.is_nullable, op=op)
 
 
+class ReduceFromPredicate(ReduceBySignature):
+
+    adapts(FromPredicateSig)
+
+    def __call__(self):
+        #op = self.state.reduce(self.phrase.op)
+        #if isformula(op, ToPredicateSig):
+        #    return op.op
+        #return self.phrase.clone(is_nullable=op.is_nullable, op=op)
+        return self.state.reduce(self.phrase.op)
+
+
+class ReduceToPredicate(ReduceBySignature):
+
+    adapts(ToPredicateSig)
+
+    def __call__(self):
+        #op = self.state.reduce(self.phrase.op)
+        #if isformula(op, FromPredicateSig):
+        #    return op.op
+        #return self.phrase.clone(is_nullable=op.is_nullable, op=op)
+        return self.state.reduce(self.phrase.op)
+
+
 class ReduceExport(Reduce):
     """
     Reduces an export phrase.

src/htsql/tr/rewrite.py

                    QuotientSpace, FilteredSpace, OrderedSpace,
                    Code, LiteralCode, CastCode, FormulaCode, Unit, ScalarUnit,
                    AggregateUnitBase, KernelUnit, ComplementUnit)
+from .signature import Signature
 
 
 class RewritingState(object):
     adapts(FormulaCode)
 
     def __call__(self):
-        arguments = self.code.arguments.map(self.state.rewrite)
-        return FormulaCode(self.code.signature,
-                           self.code.domain,
+        rewrite = RewriteBySignature(self.code, self.state)
+        return rewrite()
+
+
+class RewriteBySignature(Adapter):
+
+    adapts(Signature)
+
+    @classmethod
+    def dispatch(interface, code, *args, **kwds):
+        assert isinstance(code, FormulaCode)
+        return (type(code.signature),)
+
+    def __init__(self, code, state):
+        assert isinstance(code, FormulaCode)
+        assert isinstance(state, RewritingState)
+        self.code = code
+        self.state = state
+        self.signature = code.signature
+        self.domain = code.domain
+        self.arguments = code.arguments
+
+    def __call__(self):
+        arguments = self.arguments.map(self.state.rewrite)
+        return FormulaCode(self.signature, self.domain,
                            self.code.binding, **arguments)
 
 

src/htsql/tr/signature.py

     """
 
 
+class ToPredicateSig(UnarySig):
+    pass
+
+
+class FromPredicateSig(UnarySig):
+    pass
+
+

src/htsql/tr/term.py

         return "(%s)" % self.kid
 
 
+class PermanentTerm(WrapperTerm):
+    """
+    Represents a no-op operation.
+
+    A permanent term is never collapsed with the outer term.
+    """
+
+    def __str__(self):
+        # Display:
+        #   (!<kid>!)
+        return "(!%s!)" % self.kid
+
+
 class SegmentTerm(UnaryTerm):
     """
     Represents a segment term.

src/htsql_mssql/tr/__init__.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+"""
+:mod:`htsql_mssql.tr`
+=====================
+
+This package adapts the HTSQL-to-SQL translator for MS SQL Server.
+"""
+
+

src/htsql_mssql/tr/compile.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+from htsql.domain import BooleanDomain, IntegerDomain
+from htsql.tr.term import PermanentTerm, FilterTerm, OrderTerm
+from htsql.tr.code import LiteralCode, FormulaCode, ScalarUnit
+from htsql.tr.coerce import coerce
+from htsql.tr.signature import CompareSig, AndSig
+from htsql.tr.fn.signature import SortDirectionSig
+from .signature import RowNumberSig
+from htsql.tr.compile import CompileOrdered, ordering, spread
+
+
+class MSSQLCompileOrdered(CompileOrdered):
+
+    def __call__(self):
+        if self.space.offset is None:
+            return super(MSSQLCompileOrdered, self).__call__()
+        kid = self.state.compile(self.space.base,
+                                  baseline=self.state.scalar,
+                                  mask=self.state.scalar)
+        order = ordering(self.space)
+        codes = [code for code, direction in order]
+        kid = self.state.inject(kid, [code for code, direction in order])
+        ops = []
+        for code, direction in order:
+            op = FormulaCode(SortDirectionSig(direction=direction),
+                             code.domain, code.binding, base=code)
+            ops.append(op)
+        row_number_code = FormulaCode(RowNumberSig(), coerce(IntegerDomain()),
+                                      self.space.binding, ops=ops)
+        row_number_unit = ScalarUnit(row_number_code, self.space.base,
+                                     self.space.binding)
+        tag = self.state.tag()
+        routes = kid.routes.copy()
+        routes[row_number_unit] = tag
+        kid = PermanentTerm(tag, kid, kid.space, kid.baseline, routes)
+        left_limit = self.space.offset+1
+        right_limit = None
+        if self.space.limit is not None:
+            right_limit = self.space.limit+self.space.offset+1
+        left_limit_code = LiteralCode(left_limit, coerce(IntegerDomain()),
+                                      self.space.binding)
+        right_limit_code = None
+        if right_limit is not None:
+            right_limit_code = LiteralCode(right_limit, coerce(IntegerDomain()),
+                                           self.space.binding)
+        left_filter = FormulaCode(CompareSig('>='), coerce(BooleanDomain()),
+                                  self.space.binding,
+                                  lop=row_number_unit, rop=left_limit_code)
+        right_filter = None
+        if right_limit_code is not None:
+            right_filter = FormulaCode(CompareSig('<'),
+                                       coerce(BooleanDomain()),
+                                       self.space.binding,
+                                       lop=row_number_unit,
+                                       rop=right_limit_code)
+        filter = left_filter
+        if right_filter is not None:
+            filter = FormulaCode(AndSig(), coerce(BooleanDomain()),
+                                 self.space.binding,
+                                 ops=[left_filter, right_filter])
+        routes = kid.routes.copy()
+        for unit in spread(self.space):
+            routes[unit.clone(space=self.space)] = routes[unit]
+        return FilterTerm(self.state.tag(), kid, filter,
+                          self.space, kid.baseline, routes)
+
+
+

src/htsql_mssql/tr/dump.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+"""
+:mod:`htsql_mssql.tr.dump`
+==========================
+
+This module adapts the SQL serializer for MS SQL Server.
+"""
+
+
+from htsql.adapter import adapts
+from htsql.domain import (BooleanDomain, StringDomain, IntegerDomain,
+                          DecimalDomain, DateDomain)
+from htsql.tr.error import SerializeError
+from htsql.tr.frame import ColumnPhrase, ReferencePhrase, LiteralPhrase
+from htsql.tr.dump import (FormatName, DumpBranch, DumpBySignature,
+                           DumpFromPredicate, DumpToPredicate,
+                           DumpIsTotallyEqual, DumpBoolean, DumpInteger,
+                           DumpDecimal, DumpFloat, DumpDate, DumpToInteger,
+                           DumpToFloat, DumpToDecimal, DumpToString,
+                           DumpToDate)
+from htsql.tr.fn.dump import (DumpRound, DumpRoundTo, DumpLength,
+                              DumpConcatenate, DumpSubstring, DumpTrim,
+                              DumpToday, DumpExtractYear, DumpExtractMonth,
+                              DumpExtractDay, DumpMakeDate,
+                              DumpDateIncrement, DumpDateDecrement,
+                              DumpDateDifference)
+from htsql.tr.signature import FromPredicateSig, ToPredicateSig
+from htsql.tr.fn.signature import SortDirectionSig
+from .signature import RowNumberSig
+
+
+class MSSQLFormatName(FormatName):
+
+    def __call__(self):
+        self.stream.write("[%s]" % self.value.replace("]", "]]"))
+
+
+class MSSQLDumpBranch(DumpBranch):
+
+    def dump_select(self):
+        aliases = self.state.select_aliases_by_tag[self.frame.tag]
+        self.write("SELECT ")
+        self.indent()
+        if self.frame.limit is not None:
+            self.write("TOP "+str(self.frame.limit))
+            self.newline()
+        for index, phrase in enumerate(self.frame.select):
+            alias = None
+            if self.state.hook.with_aliases:
+                alias = aliases[index]
+                if isinstance(phrase, ColumnPhrase):
+                    if alias == phrase.column.name:
+                        alias = None
+                if isinstance(phrase, ReferencePhrase):
+                    target_alias = (self.state.select_aliases_by_tag
+                                            [phrase.tag][phrase.index])
+                    if alias == target_alias:
+                        alias = None
+            if alias is not None:
+                self.format("{selection} AS {alias:name}",
+                            selection=phrase, alias=alias)
+            else:
+                self.format("{selection}",
+                            selection=phrase)
+            if index < len(self.frame.select)-1:
+                self.write(",")
+                self.newline()
+        self.dedent()
+
+    def dump_group(self):
+        if not self.frame.group:
+            return
+        self.newline()
+        self.write("GROUP BY ")
+        for index, phrase in enumerate(self.frame.group):
+            self.format("{kernel}", kernel=phrase)
+            if index < len(self.frame.group)-1:
+                self.write(", ")
+
+    def dump_limit(self):
+        assert self.frame.offset is None
+
+
+class MSSQLDumpFromPredicate(DumpFromPredicate):
+
+    def __call__(self):
+        if self.phrase.is_nullable:
+            self.format("(CASE WHEN {op} THEN 1 WHEN NOT {op} THEN 0 END)",
+                        self.arguments)
+        else:
+            self.format("(CASE WHEN {op} THEN 1 ELSE 0 END)",
+                        self.arguments)
+
+
+class MSSQLDumpToPredicate(DumpToPredicate):
+
+    def __call__(self):
+        self.format("({op} <> 0)", self.arguments)
+
+
+class MSSQLDumpRowNumber(DumpBySignature):
+
+    adapts(RowNumberSig)
+
+    def __call__(self):
+        self.format("ROW_NUMBER() OVER (ORDER BY {ops:union{, }})",
+                    self.arguments)
+
+
+class MSSQLDumpSortDirection(DumpBySignature):
+
+    adapts(SortDirectionSig)
+
+    def __call__(self):
+        self.format("{base} {direction:switch{ASC|DESC}}",
+                    self.arguments, self.signature)
+
+
+class MSSQLDumpBoolean(DumpBoolean):
+
+    def __call__(self):
+        if self.value is True:
+            self.write("1")
+        if self.value is False:
+            self.write("0")
+
+
+class MSSQLDumpInteger(DumpInteger):
+
+    def __call__(self):
+        if not (-2**63 <= self.value < 2**63):
+            raise SerializeError("invalid integer value",
+                                 self.phrase.mark)
+        if abs(self.value) < 2**31:
+            self.write(str(self.value))
+        else:
+            self.write("CAST(%s AS BIGINT)" % self.value)
+
+
+class MySQLDumpFloat(DumpFloat):
+
+    def __call__(self):
+        assert str(self.value) not in ['inf', '-inf', 'nan']
+        value = repr(self.value)
+        if 'e' not in value and 'E' not in value:
+            value = value+'e0'
+        self.write(value)
+
+
+class MySQLDumpDecimal(DumpDecimal):
+
+    def __call__(self):
+        assert self.value.is_finite()
+        value = str(self.value)
+        if 'E' in value:
+            value = "CAST(%s AS DECIMAL(38,19))" % value
+        elif '.' not in value:
+            value = "%s." % value
+        self.write(value)
+
+
+class MSSQLDumpDate(DumpDate):
+
+    def __call__(self):
+        self.format("CAST({value:literal} AS DATETIME)",
+                    value=str(self.value))
+
+
+class MSSQLDumpToInteger(DumpToInteger):
+
+    def __call__(self):
+        self.format("CAST({base} AS INT)", base=self.base)
+
+
+class MSSQLDumpToFloat(DumpToFloat):
+
+    def __call__(self):
+        self.format("CAST({base} AS FLOAT)", base=self.base)
+
+
+class MSSQLDumpToDecimal(DumpToDecimal):
+
+    def __call__(self):
+        self.format("CAST({base} AS DECIMAL(38,19))", base=self.base)
+
+
+class MSSQLDumpIntegerToDecimal(MSSQLDumpToDecimal):
+
+    adapts(IntegerDomain, DecimalDomain)
+
+    def __call__(self):
+        self.format("CAST({base} AS DECIMAL(38))", base=self.base)
+
+
+class MSSQLDumpToString(DumpToString):
+
+    def __call__(self):
+        self.format("CAST({base} AS VARCHAR(MAX))", base=self.base)
+
+
+class MSSQLDumpBooleanToString(DumpToString):
+
+    adapts(BooleanDomain, StringDomain)
+
+    def __call__(self):
+        if self.base.is_nullable:
+            self.format("(CASE WHEN {base} <> 0 THEN 'true'"
+                        " WHEN NOT {base} = 0 THEN 'false' END)",
+                        base=self.base)
+        else:
+            self.format("(CASE WHEN {base} <> 0 THEN 'true' ELSE 'false' END)",
+                        base=self.base)
+
+
+class MSSQLDumpDateToString(DumpToString):
+
+    adapts(DateDomain, StringDomain)
+
+    def __call__(self):
+        self.format("SUBSTRING(CONVERT(VARCHAR, {base}, 20), 1, 10)",
+                    base=self.base)
+
+
+class MSSQLDumpToDate(DumpToDate):
+
+    def __call__(self):
+        self.format("CAST({base} AS DATETIME)", base=self.base)
+
+
+class MSSQLDumpIsTotallyEqual(DumpIsTotallyEqual):
+
+    def __call__(self):
+        self.format("((CASE WHEN ({lop} = {rop}) OR"
+                    " ({lop} IS NULL AND {rop} IS NULL)"
+                    " THEN 1 ELSE 0 END) {polarity:switch{<>|=}} 0)",
+                    self.arguments, self.signature)
+
+
+class MSSQLDumpRound(DumpRound):
+
+    def __call__(self):
+        if isinstance(self.phrase.domain, DecimalDomain):
+            self.format("CAST(ROUND({op}, 0) AS DECIMAL(38))", self.arguments)
+        else:
+            self.format("ROUND({op}, 0)", self.arguments)
+
+
+class MSSQLDumpRoundTo(DumpRoundTo):
+
+    def __call__(self):
+        scale = None
+        if (isinstance(self.phrase.precision, LiteralPhrase) and
+            self.phrase.precision.value is not None):
+            scale = self.phrase.precision.value
+            if scale < 0:
+                scale = 0
+        if scale is not None:
+            self.format("CAST(ROUND({op}, {precision})"
+                        " AS DECIMAL(38,{scale:pass}))",
+                        self.arguments, scale=str(scale))
+        else:
+            self.format("ROUND({op}, {precision})", self.arguments)
+
+
+class MSSQLDumpLength(DumpLength):
+
+    template = "LEN({op})"
+
+
+class MSSQLDumpConcatenate(DumpConcatenate):
+
+    template = "({lop} + {rop})"
+
+
+class MSSQLDumpSubstring(DumpSubstring):
+
+    def __call__(self):
+        if self.phrase.length is None:
+            self.format("SUBSTRING({op}, {start}, LEN({op}))", self.phrase)
+        else:
+            self.format("SUBSTRING({op}, {start}, {length})", self.phrase)
+
+
+class MSSQLDumpTrim(DumpTrim):
+
+    def __call__(self):
+        if self.signature.is_left and not self.signature.is_right:
+            self.format("LTRIM({op})", self.arguments)
+        elif not self.signature.is_left and self.signature.is_right:
+            self.format("RTRIM({op})", self.arguments)
+        else:
+            self.format("LTRIM(RTRIM({op}))", self.arguments)
+
+
+class MSSQLDumpToday(DumpToday):
+
+    template = "CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)"
+
+
+class MSSQLDumpExtractYear(DumpExtractYear):
+
+    template = "DATEPART(YEAR, {op})"
+
+
+class MSSQLDumpExtractMonth(DumpExtractMonth):
+
+    template = "DATEPART(MONTH, {op})"
+
+
+class MSSQLDumpExtractDay(DumpExtractDay):
+
+    template = "DATEPART(DAY, {op})"
+
+
+class MSSQLDumpMakeDate(DumpMakeDate):
+
+    template = ("DATEADD(DAY, {day} - 1,"
+                " DATEADD(MONTH, {month} - 1,"
+                " DATEADD(YEAR, {year} - 2001,"
+                " CAST('2001-01-01' AS DATETIME))))")
+
+
+class MSSQLDumpDateIncrement(DumpDateIncrement):
+
+    template = "DATEADD(DAY, {rop}, {lop})"
+
+
+class MSSQLDumpDateDecrement(DumpDateDecrement):
+
+    template = "DATEADD(DAY, -{rop}, {lop})"
+
+
+class MSSQLDumpDateDifference(DumpDateDifference):
+
+    template = "DATEDIFF(DAY, {rop}, {lop})"
+
+

src/htsql_mssql/tr/encode.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+from htsql.tr.code import LiteralCode, FormulaCode
+from htsql.tr.fn.signature import ReplaceSig, ConcatenateSig, LikeSig
+from htsql.tr.fn.encode import EncodeContains
+
+
+class MSSQLEncodeContains(EncodeContains):
+
+    def __call__(self):
+        lop = self.state.encode(self.binding.lop)
+        rop = self.state.encode(self.binding.rop)
+        if isinstance(rop, LiteralCode):
+            if rop.value is not None:
+                value = ("%" + rop.value.replace("\\", "\\\\")
+                                        .replace("[", "\\[")
+                                        .replace("]", "\\]")
+                                        .replace("%", "\\%")
+                                        .replace("_", "\\_") + "%")
+                rop = rop.clone(value=value)
+        else:
+            backslash_literal = LiteralCode("\\", rop.domain, self.binding)
+            xbackslash_literal = LiteralCode("\\\\", rop.domain, self.binding)
+            lbracket_literal = LiteralCode("[", rop.domain, self.binding)
+            xlbracket_literal = LiteralCode("\\[", rop.domain, self.binding)
+            rbracket_literal = LiteralCode("]", rop.domain, self.binding)
+            xrbracket_literal = LiteralCode("\\]", rop.domain, self.binding)
+            percent_literal = LiteralCode("%", rop.domain, self.binding)
+            xpercent_literal = LiteralCode("\\%", rop.domain, self.binding)
+            underscore_literal = LiteralCode("_", rop.domain, self.binding)
+            xunderscore_literal = LiteralCode("\\_", rop.domain, self.binding)
+            rop = FormulaCode(ReplaceSig(), rop.domain, self.binding,
+                              op=rop, old=backslash_literal,
+                              new=xbackslash_literal)
+            rop = FormulaCode(ReplaceSig(), rop.domain, self.binding,
+                              op=rop, old=lbracket_literal,
+                              new=xlbracket_literal)
+            rop = FormulaCode(ReplaceSig(), rop.domain, self.binding,
+                              op=rop, old=rbracket_literal,
+                              new=xrbracket_literal)
+            rop = FormulaCode(ReplaceSig(), rop.domain, self.binding,
+                              op=rop, old=percent_literal,
+                              new=xpercent_literal)
+            rop = FormulaCode(ReplaceSig(), rop.domain, self.binding,
+                              op=rop, old=underscore_literal,
+                              new=xunderscore_literal)
+            rop = FormulaCode(ConcatenateSig(), rop.domain, self.binding,
+                              lop=percent_literal, rop=rop)
+            rop = FormulaCode(ConcatenateSig(), rop.domain, self.binding,
+                              lop=rop, rop=percent_literal)
+        return FormulaCode(self.signature.clone_to(LikeSig),
+                           self.domain, self.binding, lop=lop, rop=rop)
+
+

src/htsql_mssql/tr/reduce.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+from htsql.tr.signature import isformula, FromPredicateSig, ToPredicateSig
+from htsql.tr.reduce import ReduceFromPredicate, ReduceToPredicate
+
+
+class MSSQLReduceFromPredicate(ReduceFromPredicate):
+
+    def __call__(self):
+        op = self.state.reduce(self.phrase.op)
+        if isformula(op, ToPredicateSig):
+            return op.op
+        return self.phrase.clone(is_nullable=op.is_nullable, op=op)
+
+
+class MSSQLReduceToPredicate(ReduceToPredicate):
+
+    def __call__(self):
+        op = self.state.reduce(self.phrase.op)
+        if isformula(op, FromPredicateSig):
+            return op.op
+        return self.phrase.clone(is_nullable=op.is_nullable, op=op)
+
+

src/htsql_mssql/tr/signature.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+from htsql.tr.signature import ConnectiveSig
+
+
+class RowNumberSig(ConnectiveSig):
+    pass
+
+

test/regress/input/library.yaml

   - uri: /{'', 'HTSQL', 'O''Reilly',
            '%ce%bb%cf%8c%ce%b3%ce%bf%cf%82',
            '$-b \pm \sqrt{b^2 - 4ac} \over 2a$'}
+    ifndef: mssql
+  # The regression database for MS SQL Server uses Latin1 locale and therefore
+  # is unable to represent greek characters.
+  - uri: /{'', 'HTSQL', 'O''Reilly', 'zo%c3%b6logy',
+           '$-b \pm \sqrt{b^2 - 4ac} \over 2a$'}
+    ifdef: mssql
   - uri: /{string('832040')}
 
   # Integer values
   - uri: /{decimal('1E-10')}
   # Arbitrary length
   - uri: /{4154781481226426191177580544000000.808017424794512875886459904961710757005754368000000000}
+    ifndef: mssql
   # Invalid decimal literals
   - uri: /{decimal('vingt-cinq')}
     expect: 400
   - uri: /{integer(string('cinq'))}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifdef: [pgsql, mssql]
   # Integer overflow
   - uri: /{integer(4294967296.0)}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifdef: [pgsql, mssql]
   - uri: /{integer(1.8446744073709552e+19)}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifdef: [pgsql, mssql]
   - uri: /{integer(4294967296.0),
            integer(1.8446744073709552e+19)}
     ifdef: [sqlite, mysql]
   - uri: /{7*2147483647}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifdef: [pgsql, mssql]
   - uri: /{9223372036854775807+1}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifdef: [pgsql, mssql]
   - uri: /{7*2147483647, 9223372036854775807+1}
     ifdef: [sqlite, mysql]
   # Division by zero
   - uri: /{7/0}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifdef: [pgsql, mssql]
   - uri: /{7/0.0}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifdef: [pgsql, mssql]
   - uri: /{7/0e0}
     expect: 409
     ignore: true
-    ifdef: pgsql
+    ifdef: [pgsql, mssql]
   - uri: /{7/0, 7/0.0, 7/0e0}
     ifdef: [sqlite, mysql]
 
   - uri: /{round(3272.78125), round(271828e-5)}
   # Coercion
   - uri: /{round(9973)}
+  # Bug in MSSQL 2008R2, see
+  # https://connect.microsoft.com/SQLServer/feedback/details/646516/bad-example-for-round
   - uri: /{round(9973,-2)}
-    ifndef: sqlite
+    ifndef: [sqlite, mssql]
   # Inadmissible operand
   - uri: /{round(271828e-5,2)}
     expect: 400
   # Contains
   - uri: /{'OMGWTFBBQ'~'wtf', 'OMGWTFBBQ'!~'LOL'}
   - uri: /{'OMGWTFBBQ'!~'wtf', 'OMGWTFBBQ'~'LOL'}
+  # Bug in MSSQL 2005, `SELECT (CASE WHEN ('LOL' LIKE NULL) THEN 1 END)`
+  # generates [<1>].
   - uri: /{null()~'LOL', 'LOL'~null(), null()~null()}
+    ifndef: mssql
+  - uri: /{null()~'LOL', null()~null()}
+    ifdef: mssql
+  - uri: /this(){true()}?'LOL'~null()
+    ifdef: mssql
 
   # Slicing
   - uri: /{head('OMGWTFBBQ'), head('OMGWTFBBQ',3), head('OMGWTFBBQ',-3)}
   - uri: /{replace('OMGWTFBBQ','WTF','LOL')}
   - uri: /{replace('OMGWTFBBQ','wtf','LOL'),
            replace('OMGWTFBBQ','WTF','lol')}
+    ifndef: mssql
+  # `REPLACE` in MSSQL respects the database collation, which is
+  # case-insensitive for the regression database.
+  - uri: /{replace('OMGWTFBBQ','wtf','LOL')}
+    ifdef: mssql
+  - uri: /{replace('OMGWTFBBQ','WTF','lol')}
+    ifdef: mssql
   - uri: /{replace('floccinaucinihilipilification','ili','LOL')}
   - uri: /{replace('OMGWTFBBQ','','LOL'),
            replace('OMGWTFBBQ','WTF','')}

test/regress/input/mssql.yaml

   tests:
   - connect: *admin-connect
     sql: !environ |
-        CREATE DATABASE [${MSSQL_DATABASE}] COLLATE Latin1_General_CS_AI;
+        CREATE DATABASE [${MSSQL_DATABASE}] COLLATE Latin1_General_CI_AI;
         CREATE LOGIN [${MSSQL_USERNAME}] WITH PASSWORD = '${MSSQL_PASSWORD}', CHECK_POLICY = OFF;
         USE [${MSSQL_DATABASE}];
         CREATE USER [${MSSQL_USERNAME}] FOR LOGIN [${MSSQL_USERNAME}];
 
 - title: Run the test collection
   id: test-mssql
-  skip: true
   tests:
   - define: mssql
   - db: *connect

test/regress/input/translation.yaml

     - uri: /school?!exists(department)
     - uri: /school{*,count(department)}
     - uri: /school{*,count(department?exists(course))}
+    # MSSQL does not allow the argument of an aggregate to contain
+    # a subquery.
     - uri: /school{*,count(department.exists(course))}
+      ifndef: mssql
     - uri: /school{code,count(department),count(program)}
     - uri: /school{code,exists(department),exists(program)}
       skip: true # broken until `reduce` is implemented