Add TABLESAMPLE clause support.

The TABLESAMPLE clause allows randomly selecting an approximate percentage
of rows from a table. At least DB2, Microsoft SQL Server and recent
Postgresql support this standard clause.

Fixes: #3718
Change-Id: I3fb8b9223e12a57100df30876b461884c58d72fa
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/277

4 files changed, 108 insertions, 2 deletions

diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py
index 7f7abacc2..a9b842e1a 100644
--- a/lib/sqlalchemy/sql/__init__.py
+++ b/lib/sqlalchemy/sql/__init__.py
@@ -18,6 +18,7 @@ from .expression import (
     Select,
     Selectable,
     TableClause,
+    TableSample,
     Update,
     alias,
     and_,
@@ -59,6 +60,7 @@ from .expression import (
     select,
     subquery,
     table,
+    tablesample,
     text,
     true,
     True_,
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 94c7db20a..5e537dfdc 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1351,6 +1351,17 @@ class SQLCompiler(Compiled):
         kw['lateral'] = True
         return "LATERAL %s" % self.visit_alias(lateral, **kw)
 
+    def visit_tablesample(self, tablesample, asfrom=False, **kw):
+        text = "%s TABLESAMPLE %s" % (
+            self.visit_alias(tablesample, asfrom=True, **kw),
+            tablesample._get_method()._compiler_dispatch(self, **kw))
+
+        if tablesample.seed is not None:
+            text += " REPEATABLE (%s)" % (
+                tablesample.seed._compiler_dispatch(self, **kw))
+
+        return text
+
     def get_render_as_alias_suffix(self, alias_name_text):
         return " AS " + alias_name_text
 
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 97f74d4e4..cbb123ec6 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -26,7 +26,8 @@ __all__ = [
     'nullslast',
     'or_', 'outparam', 'outerjoin', 'over', 'select', 'subquery',
     'table', 'text',
-    'tuple_', 'type_coerce', 'union', 'union_all', 'update', 'within_group']
+    'tuple_', 'type_coerce', 'union', 'union_all', 'update', 'within_group',
+    'TableSample', 'tablesample']
 
 
 from .visitors import Visitable
@@ -49,7 +50,7 @@ from .base import ColumnCollection, Generative, Executable, \
 from .selectable import Alias, Join, Select, Selectable, TableClause, \
     CompoundSelect, CTE, FromClause, FromGrouping, Lateral, SelectBase, \
     alias, GenerativeSelect, subquery, HasCTE, HasPrefixes, HasSuffixes, \
-    lateral, Exists, ScalarSelect, TextAsFrom
+    lateral, Exists, ScalarSelect, TextAsFrom, TableSample, tablesample
 
 
 from .dml import Insert, Update, Delete, UpdateBase, ValuesBase
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index ac955a60f..e62aa1e8e 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -183,6 +183,51 @@ def lateral(selectable, name=None):
     return selectable.lateral(name=name)
 
 
+def tablesample(selectable, sampling, name=None, seed=None):
+    """Return a :class:`.TableSample` object.
+
+    :class:`.TableSample` is an :class:`.Alias` subclass that represents
+    a table with the TABLESAMPLE clause applied to it.
+    :func:`~.expression.tablesample`
+    is also available from the :class:`.FromClause` class via the
+    :meth:`.FromClause.tablesample` method.
+
+    The TABLESAMPLE clause allows selecting a randomly selected approximate
+    percentage of rows from a table. It supports multiple sampling methods,
+    most commonly BERNOULLI and SYSTEM.
+
+    e.g.::
+
+        from sqlalchemy import func
+
+        selectable = people.tablesample(
+                    func.bernoulli(1),
+                    name='alias',
+                    seed=func.random())
+        stmt = select([selectable.c.people_id])
+
+    Assuming ``people`` with a column ``people_id``, the above
+    statement would render as::
+
+        SELECT alias.people_id FROM
+        people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
+        REPEATABLE (random())
+
+    .. versionadded:: 1.1
+
+    :param sampling: a ``float`` percentage between 0 and 100 or
+        :class:`.functions.Function`.
+
+    :param name: optional alias name
+
+    :param seed: any real-valued SQL expression.  When specified, the
+     REPEATABLE sub-clause is also rendered.
+
+    """
+    return _interpret_as_from(selectable).tablesample(
+        sampling, name=name, seed=seed)
+
+
 class Selectable(ClauseElement):
     """mark a class as being selectable"""
     __visit_name__ = 'selectable'
@@ -474,6 +519,21 @@ class FromClause(Selectable):
         """
         return Lateral(self, name)
 
+    def tablesample(self, sampling, name=None, seed=None):
+        """Return a TABLESAMPLE alias of this :class:`.FromClause`.
+
+        The return value is the :class:`.TableSample` construct also
+        provided by the top-level :func:`~.expression.tablesample` function.
+
+        .. versionadded:: 1.1
+
+        .. seealso::
+
+            :func:`~.expression.tablesample` - usage guidelines and parameters
+
+        """
+        return TableSample(self, sampling, name, seed)
+
     def is_derived_from(self, fromclause):
         """Return True if this FromClause is 'derived' from the given
         FromClause.
@@ -1268,6 +1328,38 @@ class Lateral(Alias):
     __visit_name__ = 'lateral'
 
 
+class TableSample(Alias):
+    """Represent a TABLESAMPLE clause.
+
+    This object is constructed from the :func:`~.expression.tablesample` module
+    level function as well as the :meth:`.FromClause.tablesample` method available
+    on all :class:`.FromClause` subclasses.
+
+    .. versionadded:: 1.1
+
+    .. seealso::
+
+        :func:`~.expression.tablesample`
+
+    """
+
+    __visit_name__ = 'tablesample'
+
+    def __init__(self, selectable, sampling,
+                 name=None,
+                 seed=None):
+        self.sampling = sampling
+        self.seed = seed
+        super(TableSample, self).__init__(selectable, name=name)
+
+    @util.dependencies("sqlalchemy.sql.functions")
+    def _get_method(self, functions):
+        if isinstance(self.sampling, functions.Function):
+            return self.sampling
+        else:
+            return functions.func.system(self.sampling)
+
+
 class CTE(Generative, HasSuffixes, Alias):
     """Represent a Common Table Expression.