def test_sanity(self):
with mocked_engine(self.session) as engine:
table = Table('t1', self.metadata(session=engine.session),
Column('x', types.Int32, primary_key=True),
engines.Memory())
table.drop(if_exists=True)
self.assertEqual(engine.history, ['DROP TABLE IF EXISTS t1'])
def _make_table(self, table_name=None, *columns):
metadata = self.metadata()
columns = ((columns or
(Column('x', types.Int32, primary_key=True), )) +
(engines.Memory(), ))
return Table(table_name or 't1', metadata, *columns)
def test_create_table_decimal_symlink(self):
table = Table('test', TypesTestCase.metadata(),
Column('x', types.Decimal(10, 2)), engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory')
def test_create_table_with_codec(self):
table = Table(
't1', self.metadata(),
Column(
'list',
types.DateTime,
clickhouse_codec=['DoubleDelta', 'ZSTD'],
),
Column(
'tuple',
types.UInt8,
clickhouse_codec=('T64', 'ZSTD(5)'),
),
Column('explicit_none', types.UInt32, clickhouse_codec=None),
Column('str', types.Int8, clickhouse_codec='ZSTD'),
engines.Memory()
)
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE t1 ('
'list DateTime CODEC(DoubleDelta, ZSTD), '
'tuple UInt8 CODEC(T64, ZSTD(5)), '
'explicit_none UInt32, '
'str Int8 CODEC(ZSTD)) '
'ENGINE = Memory'
)
class TestTable(base):
x = Column(types.Int32, primary_key=True)
y = Column(types.String)
__table_args__ = (
engines.Memory(),
)
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
__table_args__ = (
engines.Memory(),
)
class DateTimeCompilationTestCase(CompilationTestCase):
table = Table('test', CompilationTestCase.metadata(),
Column('x', types.DateTime, primary_key=True),
engines.Memory())
def test_create_table(self):
self.assertEqual(self.compile(CreateTable(self.table)),
'CREATE TABLE test (x DateTime) ENGINE = Memory')
def _type_round_trip(self, *types):
metadata = self.metadata()
args = ([Column('t%d' % i, type_)
for i, type_ in enumerate(types)] + [engines.Memory()])
table = Table('t', metadata, *args)
with self.create_table(table):
return [c['type'] for c in inspect(metadata.bind).get_columns('t')]
def test_create_table_tuple(self):
table = Table('t1', self.metadata(),
Column('x', types.Tuple(types.Int8, types.Float32)),
engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 ('
'x Tuple(Int8, Float32)) '
'ENGINE = Memory')
def setUp(self):
super(ClickHouseDialectTestCase, self).setUp()
self.table = Table(
'test_exists_table',
self.metadata(),
Column('x', types.Int32, primary_key=True),
engines.Memory()
)
self.table.drop(self.session.bind, if_exists=True)
def test_not_null(self):
metadata = self.metadata()
table = Table('t', metadata, Column('x', types.Int32, nullable=False),
engines.Memory())
with self.create_table(table):
col = inspect(metadata.bind).get_columns('t')[0]
self.assertIsInstance(col['type'], types.Int32)
self.assertFalse(col['nullable'])
def test_create_table_with_column_comment(self):
table = Table(
't1', self.metadata(session=self.session),
Column('x', types.Int32, primary_key=True, comment='col_comment'),
engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)),
"CREATE TABLE t1 (x Int32 COMMENT 'col_comment') ENGINE = Memory")
def test_create_table_map(self):
table = Table('t1', self.metadata(),
Column('x', types.Map(types.String, types.String)),
engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 ('
'x Map(String, String)) '
'ENGINE = Memory')
def test_create_table_nested_nullable(self):
table = Table('t1', self.metadata(),
Column('x', types.Int32, primary_key=True),
Column('y', types.Array(types.Nullable(types.String))),
engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 '
'(x Int32, y Array(Nullable(String))) '
'ENGINE = Memory')
def test_create_table_column_default(self):
table = Table('t1', self.metadata(), Column('x', types.Int8),
Column('dt', types.DateTime, server_default=func.now()),
engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 ('
'x Int8, '
'dt DateTime DEFAULT now()) '
'ENGINE = Memory')
class Test(Base):
id = Column(types.Int32, primary_key=True)
a = Column(types.String)
b = Column(types.String)
c = Column(types.String)
d = Column(types.String)
__table_args__ = (
engines.Memory(),
)
class NumericTypeTestCase(TypesTestCase):
table = Table(
'test', TypesTestCase.metadata(),
Column('x', Numeric(10, 2)),
engines.Memory()
)
def test_create_table(self):
self.assertEqual(
self.compile(CreateTable(self.table)),
'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory'
)
def test_select_insert(self):
x = Decimal('12345678.12')
with self.create_table(self.table):
self.session.execute(self.table.insert(), [{'x': x}])
self.assertEqual(self.session.query(self.table.c.x).scalar(), x)
def test_insert_truncate(self):
value = Decimal('123.129999')
expected = Decimal('123.12')
with self.create_table(self.table):
self.session.execute(self.table.insert(), [{'x': value}])
self.assertEqual(self.session.query(self.table.c.x).scalar(),
expected)
def test_insert_overflow(self):
value = Decimal('12345678901234567890.1234567890')
with self.create_table(self.table):
with self.assertRaises(DatabaseException) as ex:
self.session.execute(self.table.insert(), [{'x': value}])
# 'Too many digits' is written in the CH response;
# 'out of range' is raised from `struct` within
# `clickhouse_driver`,
# before the query is sent to CH.
self.assertTrue(
'out of range' in str(ex.exception) or
'Too many digits' in str(ex.exception))
def test_create_table_decimal_symlink(self):
table = Table(
'test', TypesTestCase.metadata(),
Column('x', types.Decimal(10, 2)),
engines.Memory()
)
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory'
)
def test_create_table_column_alias(self):
table = Table(
't1', self.metadata(), Column('x', types.Int8),
Column('dt', types.DateTime, clickhouse_alias=func.now()),
engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 ('
'x Int8, '
'dt DateTime ALIAS now()) '
'ENGINE = Memory')
def test_insert_no_templates_after_value(self):
# Optimized non-templating insert test (native protocol only).
table = Table(
't1', self.metadata(),
Column('x', types.Int32),
engines.Memory()
)
self.assertEqual(
self.compile(table.insert()),
'INSERT INTO t1 (x) VALUES'
)
class NumericHttpTestCase(HttpSessionTestCase):
table = Table('test', HttpSessionTestCase.metadata(),
Column('x', Numeric(10, 2)), engines.Memory())
def test_insert_truncate(self):
value = Decimal('123.129999')
with self.create_table(self.table):
with self.assertRaises(DatabaseException) as ex:
self.session.execute(self.table.insert(), [{'x': value}])
self.assertIn('value is too small', str(ex.exception))
def test_rowcount_return(self):
table = Table('test', self.metadata(),
Column('x', types.Int32, primary_key=True),
engines.Memory())
table.drop(if_exists=True)
table.create()
rv = self.session.execute(table.insert(), [{'x': x} for x in range(5)])
self.assertEqual(rv.rowcount, 5)
self.assertEqual(
self.session.query(func.count()).select_from(table).scalar(), 5)
def test_create_table(self):
table = Table('t1', self.metadata(),
Column('x', types.Int32, primary_key=True),
Column('y', types.String), Column('z', types.String(10)),
engines.Memory())
# No NOT NULL. And any PKS.
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE t1 (x Int32, y String, z FixedString(10)) '
'ENGINE = Memory')
class DateTimeTestCase(BaseTestCase):
table = Table('test', BaseTestCase.metadata(),
Column('x', types.DateTime, primary_key=True),
engines.Memory())
def test_select_insert(self):
dt = datetime(2018, 1, 1, 15, 20)
with self.create_table(self.table):
self.session.execute(self.table.insert(), [{'x': dt}])
self.assertEqual(self.session.query(self.table.c.x).scalar(), dt)
def test_escape_binary_mod(self):
query = self.session.query(literal(1) % literal(2))
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT 1 %% 2 AS anon_1')
table = Table('t', self.metadata(),
Column('x', types.Int32, primary_key=True),
engines.Memory())
query = self.session.query(table.c.x % table.c.x)
self.assertEqual(self.compile(query, literal_binds=True),
'SELECT x %% x AS anon_1 FROM t')
class BooleanCompilationTestCase(CompilationTestCase):
table = Table('test', CompilationTestCase.metadata(), Column('x', Boolean),
engines.Memory())
def test_create_table(self):
self.assertEqual(self.compile(CreateTable(self.table)),
'CREATE TABLE test (x UInt8) ENGINE = Memory')
def test_literals(self):
query = self.session.query(self.table.c.x).filter(self.table.c.x)
self.assertEqual(self.compile(query),
'SELECT test.x AS test_x FROM test WHERE test.x = 1')
class NumericNativeTestCase(NativeSessionTestCase):
table = Table('test', NativeSessionTestCase.metadata(),
Column('x', Numeric(10, 2)), engines.Memory())
def test_insert_truncate(self):
value = Decimal('123.129999')
expected = Decimal('123.12')
with self.create_table(self.table):
self.session.execute(self.table.insert(), [{'x': value}])
self.assertEqual(
self.session.query(self.table.c.x).scalar(), expected)
def test_create_all_drop_all(self):
metadata = self.metadata(session=self.session)
Table(
't1',
metadata,
Column('x', types.Int32, primary_key=True),
engines.Memory(),
)
metadata.create_all()
metadata.drop_all()
def test_server_default(self):
metadata = self.metadata()
args = ([
Column('x1', sa_types.String),
Column('x2', sa_types.String, server_default='')
] + [engines.Memory()])
table = Table('t', metadata, *args)
with self.create_table(table):
self.assertEqual([
c['default'] for c in inspect(metadata.bind).get_columns('t')
], [None, "''"])
def test_table_create_on_cluster(self):
table = Table('t1',
self.metadata(),
Column('x', types.Int32, primary_key=True),
engines.Memory(),
clickhouse_cluster='test_cluster')
create_sql = ('CREATE TABLE t1 ON CLUSTER test_cluster '
'(x Int32) ENGINE = Memory')
with mocked_engine() as engine:
table.create()
engine.assert_sql([create_sql])
self.assertEqual(self.compile(CreateTable(table)), create_sql)
class DateTime64CompilationTestCaseTimezone(CompilationTestCase):
table = Table(
'test', CompilationTestCase.metadata(),
Column(
'x',
types.DateTime64(4, 'Pacific/Honolulu'),
primary_key=True,
), engines.Memory())
def test_create_table_precision(self):
self.assertEqual(
self.compile(CreateTable(self.table)), 'CREATE TABLE test ('
'x DateTime64(4, \'Pacific/Honolulu\')'
') ENGINE = Memory')