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

1 files changed, 54 insertions, 0 deletions

diff --git a/test/sql/test_tablesample.py b/test/sql/test_tablesample.py
new file mode 100644
index 000000000..b2dddaf8c
--- /dev/null
+++ b/test/sql/test_tablesample.py
@@ -0,0 +1,54 @@
+from sqlalchemy.testing import fixtures
+from sqlalchemy.testing import AssertsCompiledSQL, assert_raises_message
+from sqlalchemy.sql import select, func, text
+from sqlalchemy.engine import default
+from sqlalchemy import exc
+from sqlalchemy import Table, Integer, String, Column
+from sqlalchemy import tablesample
+
+
+class TableSampleTest(fixtures.TablesTest, AssertsCompiledSQL):
+    __dialect__ = default.DefaultDialect(supports_native_boolean=True)
+
+    run_setup_bind = None
+
+    run_create_tables = None
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table('people', metadata,
+              Column('people_id', Integer, primary_key=True),
+              Column('age', Integer),
+              Column('name', String(30)))
+
+    def test_standalone(self):
+        table1 = self.tables.people
+
+        # no special alias handling even though clause is not in the
+        # context of a FROM clause
+        self.assert_compile(
+            tablesample(table1, 1, name='alias'),
+            'people AS alias TABLESAMPLE system(:system_1)'
+        )
+
+        self.assert_compile(
+            table1.tablesample(1, name='alias'),
+            'people AS alias TABLESAMPLE system(:system_1)'
+        )
+
+        self.assert_compile(
+            tablesample(table1, func.bernoulli(1), name='alias',
+                        seed=func.random()),
+            'people AS alias TABLESAMPLE bernoulli(:bernoulli_1) '
+            'REPEATABLE (random())'
+        )
+
+    def test_select_from(self):
+        table1 = self.tables.people
+
+        self.assert_compile(
+            select([table1.tablesample(text('1'), name='alias').c.people_id]),
+            'SELECT alias.people_id FROM '
+            'people AS alias TABLESAMPLE system(1)'
+        )
+