async def create_table(self, engine):
"""
创建表
"""
from sqlalchemy.sql.ddl import CreateTable
async with engine.acquire() as conn:
async with conn.begin() as transaction:
try:
if await self.exists_table(conn, self._tables[0].name):
await self.drop_table(conn)
for table in self._tables:
await conn.execute(CreateTable(table))
except Exception:
await transaction.close()
def test_create_table_custom_name(self):
base = get_declarative_base()
class TestTable(base):
__tablename__ = 'testtable'
x = Column(types.Int32, primary_key=True)
y = Column(types.String)
__table_args__ = (engines.Memory(), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE testtable (x Int32, y String) ENGINE = Memory')
def startDatabase(self):
self.engine = create_engine(
# sqlite in-memory
#"sqlite://", reactor=reactor, strategy=TWISTED_STRATEGY
# sqlite on filesystem
"sqlite:////tmp/kotori.sqlite", reactor=reactor, strategy=TWISTED_STRATEGY
# mysql... todo
)
# Create the table
yield self.engine.execute(CreateTable(self.telemetry))
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)
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_multiple_columns_partition_by(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
__table_args__ = (engines.MergeTree(partition_by=(date, x),
order_by='date'), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32, y String) '
'ENGINE = MergeTree() PARTITION BY (date, x) '
'ORDER BY date')
def test_create_table_column_all_defaults(self):
table = Table(
't1', self.metadata(), Column('x', types.Int8),
Column('dt',
types.DateTime,
server_default=func.now(),
clickhouse_materialized=func.now(),
clickhouse_alias=func.now()), engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 ('
'x Int8, '
'dt DateTime DEFAULT now()) '
'ENGINE = Memory')
def compile_sql(dialect, meta):
r = {}
table = tables_for_meta(meta)
index = indices_for_table_dict(table)
query = queries_for_table_dict(table)
for t in table.values():
r['create_' + t.name] = CreateTable(t).compile(dialect=dialect)
for (tab, idx) in index.items():
r['index_' + tab] = CreateIndex(idx).compile(dialect=dialect)
for (name, q) in query.items():
r[name] = q.compile(dialect=dialect)
return r
def test_text_engine_columns_declarative(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
__table_args__ = (engines.MergeTree('date', ('date', 'x')), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32, y String) '
'ENGINE = MergeTree() '
'PARTITION BY date '
'ORDER BY (date, x)')
def test_memory(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
__table_args__ = (
engines.Memory(),
)
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32) '
'ENGINE = Memory'
)
def test_graphite_merge_tree(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
__table_args__ = (engines.GraphiteMergeTree(
'config_section', date, (date, x)), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
"CREATE TABLE test_table (date Date, x Int32) "
"ENGINE = GraphiteMergeTree('config_section') "
"PARTITION BY date "
"ORDER BY (date, x)")
def test_text_engine_columns(self):
table = Table(
't1',
self.metadata(),
Column('date', types.Date, primary_key=True),
Column('x', types.Int32),
Column('y', types.String),
engines.MergeTree('date', ('date', 'x')),
)
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE t1 (date Date, x Int32, y String) '
'ENGINE = MergeTree(date, (date, x), 8192)')
def test_buffer(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
__table_args__ = (engines.Buffer('db', 'table'), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32, y String) '
'ENGINE = Buffer('
'db, table, 16, 10, 100, 10000, 1000000, 10000000, 100000000'
')')
def test_if_not_exists_create_table(self):
expected_result = re.sub(
r'\s+', '',
"CREATE TABLE IF NOT EXISTS testtable (col1 INTEGER)").strip()
engine = create_engine('redshift+psycopg2://')
table_model = Table("testtable", MetaData(), Column("col1", Integer))
ddl_statement = re.sub(
r'\s+', '',
str(
CreateTable(table_model).compile(
engine, compile_kwargs={"if_not_exists": True})).strip())
self.assertEqual(expected_result, ddl_statement)
def main(argv=sys.argv):
if len(argv) < 2:
usage(argv)
config_uri = argv[1]
request = init_websauna(config_uri)
engine = request.dbsession.get_bind()
for name, cls in Base._decl_class_registry.items():
if name == "_sa_module_registry":
continue
print(CreateTable(cls.__table__, bind=engine))
def test_summing_merge_tree(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.Int32)
__table_args__ = (engines.SummingMergeTree((y, ), date,
(date, x)), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32, y Int32) '
'ENGINE = SummingMergeTree(y) '
'PARTITION BY date '
'ORDER BY (date, x)')
def test_merge_tree_index_granularity(self):
base = get_declarative_base()
class TestTable(base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
__table_args__ = (engines.MergeTree(date, (date, x),
index_granularity=4096), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32, y String) '
'ENGINE = MergeTree(date, (date, x), 4096)')
def test_summing_merge_tree(self):
base = get_declarative_base()
class TestTable(base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.Int32)
__table_args__ = (engines.SummingMergeTree(date, (date, x),
(y, )), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32, y Int32) '
'ENGINE = SummingMergeTree(date, (date, x), 8192, (y))')
def source(test_dataset, connection):
table = Table(
f"{test_dataset}.source",
metadata,
Column("s1", String),
Column("s2", Date)
)
logger.info(f"Creating table {table}")
connection.execute(CreateTable(table))
yield table
logger.info(f"Dropping table {table}")
connection.execute(DropTable(table))
def test_collapsing_merge_tree(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
sign = Column(types.Int8)
__table_args__ = (engines.CollapsingMergeTree(
date, (date, x), sign), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table '
'(date Date, x Int32, y String, sign Int8) '
'ENGINE = CollapsingMergeTree(date, (date, x), 8192, sign)')
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'
)
def test_replicated_merge_tree(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
__table_args__ = (engines.ReplicatedMergeTree(
'/table/path', 'name', 'date', ('date', 'x')), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
"CREATE TABLE test_table (date Date, x Int32, y String) "
"ENGINE = ReplicatedMergeTree("
"'/table/path', 'name', date, (date, x), 8192"
")")
def test_distributed(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
__table_args__ = (
engines.Distributed(
'cluster', 'test', 'merge_distributed1', 'rand()'
),
)
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32) '
'ENGINE = Distributed(cluster, test, merge_distributed1, rand())'
)
def test_func_engine_columns(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
__table_args__ = (engines.MergeTree('date',
('date', func.intHash32(x)),
sampling=func.intHash32(x)), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32, y String) '
'ENGINE = MergeTree('
'date, intHash32(x), (date, intHash32(x)), 8192'
')')
def test_replacing_merge_tree(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
version = Column(types.Int32)
__table_args__ = (engines.ReplacingMergeTree(
'date', ('date', 'x'), 'version'), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
"CREATE TABLE test_table ("
"date Date, x Int32, y String, version Int32"
") "
"ENGINE = ReplacingMergeTree(date, (date, x), 8192, version)")
def test_buffer(self):
base = get_declarative_base()
class TestTable(base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
__table_args__ = (engines.Buffer('db', 'table'), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
"CREATE TABLE test_table (date Date, x Int32, y String) "
"ENGINE = Buffer("
"db, table, 16, 10, 100, 10000, 1000000, 10000000, 100000000"
")")
def make_create_ddl(self, metadata: MetaData) -> DdlString:
if not self.dialect:
raise ValueError(
"Dialect must be specified to use default metadata creation function"
)
ddl = []
if metadata.schema:
schema_ddl = str(
CreateSchema(metadata.schema).compile(dialect=self.dialect))
ddl.append(schema_ddl)
for table_obj in metadata.tables.values():
table_ddl = str(
CreateTable(table_obj).compile(dialect=self.dialect))
ddl.append(table_ddl)
return ";\n".join(d for d in ddl) + ";\n"
def test_replicated(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.Int32)
__table_args__ = (engines.ReplicatedAggregatingMergeTree(
'/table/path', 'name', partition_by=date,
order_by=(date, x)), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
"CREATE TABLE test_table (date Date, x Int32, y Int32) "
"ENGINE = ReplicatedAggregatingMergeTree('/table/path', 'name') "
"PARTITION BY date "
"ORDER BY (date, x)")
def test_basic(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.Int32)
__table_args__ = (engines.AggregatingMergeTree(partition_by=date,
order_by=(date,
x)), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table (date Date, x Int32, y Int32) '
'ENGINE = AggregatingMergeTree() '
'PARTITION BY date '
'ORDER BY (date, x)')
def test_text_engine_columns(self):
table = Table(
't1',
self.metadata(),
Column('date', types.Date, primary_key=True),
Column('x', types.Int32),
Column('y', types.String),
engines.MergeTree(partition_by='date', order_by=('date', 'x')),
)
self.assertEqual(
self.compile(CreateTable(table)),
'CREATE TABLE t1 (date Date, x Int32, y String) '
'ENGINE = MergeTree() '
'PARTITION BY date '
'ORDER BY (date, x)')
