- added pyodbc for sybase driver.

- generalized the "freetds" / "unicode statements" behavior of MS-SQL/pyodbc
into the base Pyodbc connector, as this seems to apply to Sybase as well.
- generalized the python-sybase "use autocommit for DDL" into the pyodbc
connector.  With pyodbc, the "autocommit" flag on connection is used,
as Pyodbc seems to have more database conversation than python-sybase that
can't otherwise be suppressed.
- Some platforms will now interpret certain literal values
as non-bind parameters, rendered literally into the SQL
statement.   This to support strict SQL-92 rules that are
enforced by some platforms including MS-SQL and Sybase.
In this model, bind parameters aren't allowed in the
columns clause of a SELECT, nor are certain ambiguous
expressions like "?=?".  When this mode is enabled, the base
compiler will render the binds as inline literals, but only across
strings and numeric values.  Other types such as dates
will raise an error, unless the dialect subclass defines
a literal rendering function for those.  The bind parameter
must have an embedded literal value already or an error
is raised (i.e. won't work with straight bindparam('x')).
Dialects can also expand upon the areas where binds are not
accepted, such as within argument lists of functions
(which don't work on MS-SQL when native SQL binding is used).

8 files changed, 80 insertions, 39 deletions

diff --git a/lib/sqlalchemy/dialects/access/base.py b/lib/sqlalchemy/dialects/access/base.py
index c10e77011..7dfb3153e 100644
--- a/lib/sqlalchemy/dialects/access/base.py
+++ b/lib/sqlalchemy/dialects/access/base.py
@@ -371,9 +371,9 @@ class AccessCompiler(compiler.SQLCompiler):
         return (self.process(join.left, asfrom=True) + (join.isouter and " LEFT OUTER JOIN " or " INNER JOIN ") + \
             self.process(join.right, asfrom=True) + " ON " + self.process(join.onclause))
 
-    def visit_extract(self, extract):
+    def visit_extract(self, extract, **kw):
         field = self.extract_map.get(extract.field, extract.field)
-        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr))
+        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr, **kw))
 
 class AccessDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
diff --git a/lib/sqlalchemy/dialects/maxdb/base.py b/lib/sqlalchemy/dialects/maxdb/base.py
index 504c31209..758cfaf05 100644
--- a/lib/sqlalchemy/dialects/maxdb/base.py
+++ b/lib/sqlalchemy/dialects/maxdb/base.py
@@ -558,8 +558,8 @@ class MaxDBCompiler(compiler.SQLCompiler):
 
         return labels
 
-    def order_by_clause(self, select):
-        order_by = self.process(select._order_by_clause)
+    def order_by_clause(self, select, **kw):
+        order_by = self.process(select._order_by_clause, **kw)
 
         # ORDER BY clauses in DISTINCT queries must reference aliased
         # inner columns by alias name, not true column name.
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 254aa54fd..4d697854f 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -843,8 +843,9 @@ class MSExecutionContext(default.DefaultExecutionContext):
 class MSSQLCompiler(compiler.SQLCompiler):
     returning_precedes_values = True
     
-    extract_map = compiler.SQLCompiler.extract_map.copy()
-    extract_map.update ({
+    extract_map = util.update_copy(
+        compiler.SQLCompiler.extract_map,
+        {
         'doy': 'dayofyear',
         'dow': 'weekday',
         'milliseconds': 'millisecond',
@@ -937,9 +938,9 @@ class MSSQLCompiler(compiler.SQLCompiler):
         kwargs['mssql_aliased'] = True
         return super(MSSQLCompiler, self).visit_alias(alias, **kwargs)
 
-    def visit_extract(self, extract):
+    def visit_extract(self, extract, **kw):
         field = self.extract_map.get(extract.field, extract.field)
-        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr))
+        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr, **kw))
 
     def visit_rollback_to_savepoint(self, savepoint_stmt):
         return "ROLLBACK TRANSACTION %s" % self.preparer.format_savepoint(savepoint_stmt)
@@ -1011,8 +1012,8 @@ class MSSQLCompiler(compiler.SQLCompiler):
         # "FOR UPDATE" is only allowed on "DECLARE CURSOR" which SQLAlchemy doesn't use
         return ''
 
-    def order_by_clause(self, select):
-        order_by = self.process(select._order_by_clause)
+    def order_by_clause(self, select, **kw):
+        order_by = self.process(select._order_by_clause, **kw)
 
         # MSSQL only allows ORDER BY in subqueries if there is a LIMIT
         if order_by and (not self.is_subquery() or select._limit):
@@ -1020,6 +1021,37 @@ class MSSQLCompiler(compiler.SQLCompiler):
         else:
             return ""
 
+class MSSQLStrictCompiler(MSSQLCompiler):
+    """A subclass of MSSQLCompiler which disables the usage of bind
+    parameters where not allowed natively by MS-SQL.
+    
+    A dialect may use this compiler on a platform where native
+    binds are used.
+    
+    """
+    ansi_bind_rules = True
+
+    def visit_in_op(self, binary, **kw):
+        kw['literal_binds'] = True
+        return "%s IN %s" % (
+                                self.process(binary.left, **kw), 
+                                self.process(binary.right, **kw)
+            )
+
+    def visit_notin_op(self, binary, **kw):
+        kw['literal_binds'] = True
+        return "%s NOT IN %s" % (
+                                self.process(binary.left, **kw), 
+                                self.process(binary.right, **kw)
+            )
+
+    def visit_function(self, func, **kw):
+        kw['literal_binds'] = True
+        return super(MSSQLStrictCompiler, self).visit_function(func, **kw)
+
+    #def render_literal_value(self, value):
+        # TODO! use mxODBC's literal quoting services here
+
 
 class MSDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
diff --git a/lib/sqlalchemy/dialects/mssql/mxodbc.py b/lib/sqlalchemy/dialects/mssql/mxodbc.py
index bf14601b8..59cf65d63 100644
--- a/lib/sqlalchemy/dialects/mssql/mxodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/mxodbc.py
@@ -4,9 +4,10 @@ import sys
 from sqlalchemy import types as sqltypes
 from sqlalchemy.connectors.mxodbc import MxODBCConnector
 from sqlalchemy.dialects.mssql.pyodbc import MSExecutionContext_pyodbc
-from sqlalchemy.dialects.mssql.base import MSExecutionContext, MSDialect
-
+from sqlalchemy.dialects.mssql.base import MSExecutionContext, MSDialect, \
+                                            MSSQLCompiler, MSSQLStrictCompiler
 
+        
 class MSExecutionContext_mxodbc(MSExecutionContext_pyodbc):
     """
     The pyodbc execution context is useful for enabling
@@ -20,7 +21,11 @@ class MSExecutionContext_mxodbc(MSExecutionContext_pyodbc):
 class MSDialect_mxodbc(MxODBCConnector, MSDialect):
 
     execution_ctx_cls = MSExecutionContext_mxodbc
-
+    
+    # TODO: may want to use this only if FreeTDS is not in use,
+    # since FreeTDS doesn't seem to use native binds.
+    statement_compiler = MSSQLStrictCompiler
+    
     def __init__(self, description_encoding='latin-1', **params):
         super(MSDialect_mxodbc, self).__init__(**params)
         self.description_encoding = description_encoding
diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index 54b43320a..34050271f 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -1,3 +1,16 @@
+"""
+Support for MS-SQL via pyodbc.
+
+http://pypi.python.org/pypi/pyodbc/
+
+Connect strings are of the form::
+
+    mssql+pyodbc://<username>:<password>@<dsn>/
+    mssql+pyodbc://<username>:<password>@<host>/<database>
+
+
+"""
+
 from sqlalchemy.dialects.mssql.base import MSExecutionContext, MSDialect
 from sqlalchemy.connectors.pyodbc import PyODBCConnector
 from sqlalchemy import types as sqltypes
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 37e63fbc1..98df8d0cb 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -218,10 +218,10 @@ class SQLiteCompiler(compiler.SQLCompiler):
         else:
             return self.process(cast.clause)
 
-    def visit_extract(self, extract):
+    def visit_extract(self, extract, **kw):
         try:
             return "CAST(STRFTIME('%s', %s) AS INTEGER)" % (
-                self.extract_map[extract.field], self.process(extract.expr))
+                self.extract_map[extract.field], self.process(extract.expr, **kw))
         except KeyError:
             raise exc.ArgumentError(
                 "%s is not a valid extract argument." % extract.field)
diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py
index 5d20faaf9..c440015d0 100644
--- a/lib/sqlalchemy/dialects/sybase/base.py
+++ b/lib/sqlalchemy/dialects/sybase/base.py
@@ -236,9 +236,11 @@ class SybaseExecutionContext(default.DefaultExecutionContext):
         return lastrowid
 
 class SybaseSQLCompiler(compiler.SQLCompiler):
+    ansi_bind_rules = True
 
-    extract_map = compiler.SQLCompiler.extract_map.copy()
-    extract_map.update ({
+    extract_map = util.update_copy(
+        compiler.SQLCompiler.extract_map,
+        {
         'doy': 'dayofyear',
         'dow': 'weekday',
         'milliseconds': 'millisecond'
@@ -267,33 +269,17 @@ class SybaseSQLCompiler(compiler.SQLCompiler):
         # Limit in sybase is after the select keyword
         return ""
 
-    def dont_visit_binary(self, binary):
-        """Move bind parameters to the right-hand side of an operator, where possible."""
-        if isinstance(binary.left, expression._BindParamClause) and binary.operator == operator.eq:
-            return self.process(expression._BinaryExpression(binary.right, binary.left, binary.operator))
-        else:
-            return super(SybaseSQLCompiler, self).visit_binary(binary)
-
-    def dont_label_select_column(self, select, column, asfrom):
-        if isinstance(column, expression.Function):
-            return column.label(None)
-        else:
-            return super(SybaseSQLCompiler, self).label_select_column(select, column, asfrom)
-
-#    def visit_getdate_func(self, fn, **kw):
-         # TODO: need to cast? something ?
-#        pass
-
-    def visit_extract(self, extract):
+    def visit_extract(self, extract, **kw):
         field = self.extract_map.get(extract.field, extract.field)
-        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr))
+        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr, **kw))
 
     def for_update_clause(self, select):
         # "FOR UPDATE" is only allowed on "DECLARE CURSOR" which SQLAlchemy doesn't use
         return ''
 
-    def order_by_clause(self, select):
-        order_by = self.process(select._order_by_clause)
+    def order_by_clause(self, select, **kw):
+        kw['literal_binds'] = True
+        order_by = self.process(select._order_by_clause, **kw)
 
         # SybaseSQL only allows ORDER BY in subqueries if there is a LIMIT
         if order_by and (not self.is_subquery() or select._limit):
diff --git a/lib/sqlalchemy/dialects/sybase/pyodbc.py b/lib/sqlalchemy/dialects/sybase/pyodbc.py
index 4f89fe334..1bfdb6151 100644
--- a/lib/sqlalchemy/dialects/sybase/pyodbc.py
+++ b/lib/sqlalchemy/dialects/sybase/pyodbc.py
@@ -1,7 +1,12 @@
 """
 Support for Sybase via pyodbc.
 
-This dialect is a stub only and is likely non functional at this time.
+http://pypi.python.org/pypi/pyodbc/
+
+Connect strings are of the form::
+
+    sybase+pyodbc://<username>:<password>@<dsn>/
+    sybase+pyodbc://<username>:<password>@<host>/<database>
 
 
 """