def test_create_table_nested_types(self):
table = Table('t1', self.metadata(),
Column('x', types.Int32, primary_key=True),
Column('y', types.Array(types.String)), engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 '
'(x Int32, y Array(String)) '
'ENGINE = Memory')
table = Table('t1', self.metadata(),
Column('x', types.Int32, primary_key=True),
Column('y', types.Array(types.Array(types.String))),
engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 '
'(x Int32, y Array(Array(String))) '
'ENGINE = Memory')
table = Table('t1', self.metadata(),
Column('x', types.Int32, primary_key=True),
Column('y', types.Array(types.Array(types.String))),
engines.Memory())
self.assertEqual(
self.compile(CreateTable(table)), 'CREATE TABLE t1 '
'(x Int32, y Array(Array(String))) '
'ENGINE = Memory')
async def create_table(conn):
await conn.execute('DROP TABLE IF EXISTS cars')
await conn.execute('DROP TABLE IF EXISTS users')
await conn.execute('DROP TYPE IF EXISTS s_enum CASCADE')
await conn.execute("CREATE TYPE s_enum AS ENUM ('f', 's')")
await conn.execute(CreateTable(users))
await conn.execute(CreateTable(cars))
async def db_prepair(engine):
async with engine.acquire() as conn:
print("Recreate tables in db")
await conn.execute('DROP TABLE IF EXISTS reports')
await conn.execute('DROP TABLE IF EXISTS devices')
await conn.execute(CreateTable(devices))
await conn.execute(CreateTable(reports))
async def create_tables(conn):
await conn.execute('DROP TABLE IF EXISTS orders')
await conn.execute('DROP TABLE IF EXISTS customers')
await conn.execute('DROP TABLE IF EXISTS products')
await conn.execute(CreateTable(customers))
await conn.execute(CreateTable(products))
await conn.execute(CreateTable(orders))
async def _execute_pyrogram_ddl(self, conn):
""" Create initial structures """
await conn.execute(str(CreateTable(self._t_session)))
await conn.execute(str(CreateTable(self._t_peers)))
await conn.execute(str(CreateIndex(self._i_peers_username)))
await conn.execute(str(CreateIndex(self._i_peers_phone_number)))
await conn.execute(
insert(self._t_session).values([2, None, None, 0, None, None]))
async def db_create_tables(dsn):
async with create_engine(dsn) as engine:
async with engine.acquire() as conn:
await conn.execute(
"DROP TABLE IF EXISTS stores, shelves, shelf_items CASCADE"
)
await conn.execute(CreateTable(stores_table))
await conn.execute(CreateTable(shelves_table))
await conn.execute(CreateTable(shelf_items_table))
def create_tables(app: web.Application) -> None:
cfg = app['config']['postgres']
engine = sa.create_engine(f'postgresql://{cfg["user"]}:{cfg["password"]}@'
f'{cfg["host"]}:{cfg["port"]}/{cfg["database"]}')
try:
engine.execute(CreateTable(section))
engine.execute(CreateTable(post))
engine.execute(CreateTable(comment))
except Exception as e:
print(e)
def _rename_and_delete_columns(engine, table_name,
column_renames: t.List[t.Tuple[str, str]],
column_deletes: t.List[str]):
temp_table_name = table_name + '_temp'
table = db.Model.metadata.tables[table_name]
tmp_ddl = CreateTable(table).compile(engine).string.replace(
table_name, temp_table_name)
map2old = {t[1]: t[0] for t in column_renames}
new_column_table = [
k for k in table.columns.keys() if k not in column_deletes
]
old_column_table = [map2old.get(item, item) for item in new_column_table]
with engine.connect() as connection:
old_c_s = [f'"{c}"' for c in old_column_table]
new_c_s = [f'"{c}"' for c in new_column_table]
connection.execute(tmp_ddl)
try:
connection.execute(
f'INSERT INTO {temp_table_name}({", ".join(new_c_s)}) '
f'SELECT {", ".join(old_c_s)} FROM {table_name}')
except:
connection.execute(f"DROP TABLE {temp_table_name}")
raise
else:
connection.execute(f"DROP TABLE {table_name}")
connection.execute(
f"ALTER TABLE {temp_table_name} RENAME TO {table_name}")
def _delete_columns(engine, table_name, column_deletes: t.List[str]):
temp_table_name = table_name + '_temp'
table = db.Model.metadata.tables[table_name]
tmp_ddl = CreateTable(table).compile(engine).string.replace(
table_name, temp_table_name)
new_column_table = [
k for k in table.columns.keys() if k not in column_deletes
]
with engine.connect() as connection:
new_c_s = [f'"{c}"' for c in new_column_table]
connection.execute(tmp_ddl)
try:
connection.execute(
f'INSERT INTO {temp_table_name}({", ".join(new_c_s)}) '
f'SELECT {", ".join(new_c_s)} FROM {table_name}')
except:
connection.execute(f"DROP TABLE {temp_table_name}")
raise
else:
connection.execute(f"DROP TABLE {table_name}")
connection.execute(
f"ALTER TABLE {temp_table_name} RENAME TO {table_name}")
def renderCreateTable(self, table, createIndexes=True):
'''
Render the CREATE TABLE statement for the given table.
Arguments:
table (sqlalchemy.sql.schema.Table:): The SQLAlchemy Table object to
render
createIndexes (bool): Whether or not it should create table indexes
Returns:
string: The compiled CREATE TABLE statement
'''
ddl = []
separator = '' if self.minified else '\n'
if not self.minified:
ddl.append('--\n-- Structure for table {}\n--\n'.format(
table.name))
ddl.append(str(CreateTable(table).compile(dialect=self._dialect)))
if len(table._data):
ddl.append(separator + self.renderInserts(table))
if len(table.foreign_keys) and self._dialect.supports_alter:
ddl.append(separator + self.renderTableConstraints(table))
if createIndexes and len(table.indexes):
ddl.append(separator + self.renderTableIndexes(table))
return separator.join(ddl)
async def prepare_tables(pg):
tables = Base.metadata.tables.values()
await delete_tables(pg, tables)
async with pg.acquire() as conn:
for table in tables:
create_expr = CreateTable(table)
await conn.execute(create_expr)
def test_replicated(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
sign = Column(types.Int8)
version = Column(types.Int8)
__table_args__ = (engines.ReplicatedVersionedCollapsingMergeTree(
'/table/path',
'name',
sign,
version,
partition_by=date,
order_by=(date, x),
primary_key=(x, y)), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
"CREATE TABLE test_table "
"(date Date, x Int32, y String, sign Int8, version Int8) "
"ENGINE = ReplicatedVersionedCollapsingMergeTree"
"('/table/path', 'name', sign, version) "
"PARTITION BY date "
"ORDER BY (date, x) "
"PRIMARY KEY (x, y)")
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'
)
def test_basic(self):
class TestTable(self.base):
date = Column(types.Date, primary_key=True)
x = Column(types.Int32)
y = Column(types.String)
sign = Column(types.Int8)
version = Column(types.Int8)
__table_args__ = (engines.VersionedCollapsingMergeTree(
sign,
version,
partition_by=date,
order_by=(date, x),
primary_key=(x, y),
sample_by=func.random(),
key='value'), )
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
'CREATE TABLE test_table '
'(date Date, x Int32, y String, sign Int8, version Int8) '
'ENGINE = VersionedCollapsingMergeTree(sign, version) '
'PARTITION BY date '
'ORDER BY (date, x) '
'PRIMARY KEY (x, y) '
'SAMPLE BY random() '
'SETTINGS key=value')
def test_replicated(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.ReplicatedReplacingMergeTree(
'/table/path', 'name',
version='version',
partition_by='date',
order_by=('date', 'x')
),
)
self.assertEqual(
self.compile(CreateTable(TestTable.__table__)),
"CREATE TABLE test_table "
"(date Date, x Int32, y String, version Int32) "
"ENGINE = ReplicatedReplacingMergeTree("
"'/table/path', 'name', version) "
"PARTITION BY date "
"ORDER BY (date, x)"
)
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_all_settings(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,
order_by=(date, x),
primary_key=(x, y),
sample_by=func.hashFunc(x),
setting1=2,
setting2=5
),
)
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) '
'PRIMARY KEY (x, y) '
'SAMPLE BY hashFunc(x) '
'SETTINGS setting1=2, setting2=5'
)
async def check_and_create_table(conn, table, table_name):
"""Check if table exist and creates it if none"""
sql = f"SELECT to_regclass('public.{table_name}')"
check_sql = await conn.execute(sql)
check = await check_sql.fetchone()
if check[0] is None:
logging.info(f"Creating table {table_name}")
await conn.execute(CreateTable(table))
def start():
engine = yield from make_engine()
with (yield from engine) as conn:
yield from conn.execute('DROP TABLE IF EXISTS sa_tbl4')
sql = CreateTable(tbl)
yield from conn.execute(sql)
engine.release(conn)
return engine
def test_compile_create_table_ddl():
create_statement = CreateTable(ddl_test_table)
result, params = connection.compile_query(create_statement)
assert result == (
'\nCREATE TABLE ddl_test_table (\n\tint_col'
' INTEGER, \n\tstr_col VARCHAR\n)\n\n'
)
assert len(params) == 0
async def initialize_database(connection,
stage2_only=False,
stage1_only=False):
if not stage2_only:
await connection.execute(
"CREATE TYPE extension_type AS ENUM('SIMPLE', 'MULTIRING', 'GROUP', 'EXTERNAL', "
"'TRUNK')")
await connection.execute(
"CREATE TYPE forwarding_mode AS ENUM('DISABLED', 'ENABLED', 'ON_BUSY', "
"'ON_UNAVAILABLE')")
await connection.execute(
"CREATE TYPE fork_rank_mode AS ENUM('DEFAULT', 'NEXT', 'DROP')")
await connection.execute(
"CREATE TYPE fork_rankmember_type AS ENUM('DEFAULT', 'AUXILIARY', 'PERSISTENT')"
)
if not stage1_only:
await connection.execute(
"CREATE TYPE dect_displaymode AS ENUM('NUMBER', 'NUMBER_AND_NAME', 'NAME')"
)
if not stage2_only:
await connection.execute(CreateTable(Yate.table))
await connection.execute(CreateTable(Extension.table))
await connection.execute(CreateTable(ForkRank.table))
await connection.execute(CreateTable(ForkRank.member_table))
if not stage1_only:
await connection.execute(CreateTable(User.table))
await connection.execute(CreateTable(Registration.table))
await connection.execute(CreateTable(ActiveCall.table))
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_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 test_create_table_without_engine(self):
no_engine_table = Table('t1', self.metadata(),
Column('x', types.Int32, primary_key=True),
Column('y', types.String))
with self.assertRaises(exc.CompileError) as ex:
self.compile(CreateTable(no_engine_table))
self.assertEqual(str(ex.exception), "No engine for table 't1'")
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')
async def start():
engine = await make_engine()
with (await engine) as conn:
await conn.execute('DROP TABLE IF EXISTS sa_tbl4')
await conn.execute('DROP SEQUENCE IF EXISTS id_sequence_seq')
await conn.execute(CreateTable(tbl))
await conn.execute('CREATE SEQUENCE id_sequence_seq')
return engine
def start():
engine = yield from make_engine()
with (yield from engine) as conn:
yield from conn.execute('DROP TABLE IF EXISTS sa_tbl4')
yield from conn.execute('DROP SEQUENCE IF EXISTS id_sequence_seq')
yield from conn.execute(CreateTable(tbl))
yield from conn.execute('CREATE SEQUENCE id_sequence_seq')
return engine
async def create_data_base(self, drop_table=False):
"""
Create tables if they not exist
:return:
"""
if drop_table:
async with self.engine.acquire() as conn:
await conn.execute('DROP TABLE IF EXISTS user_changes CASCADE')
await conn.execute('DROP TABLE IF EXISTS users CASCADE')
await conn.execute('DROP TABLE IF EXISTS user_changes')
await conn.execute('DROP TABLE IF EXISTS session_list CASCADE')
await conn.execute('DROP TABLE IF EXISTS files_info CASCADE')
await conn.execute('DROP TABLE IF EXISTS messages CASCADE')
await conn.execute(CreateTable(self.UserCharges))
await conn.execute(CreateTable(self.Users))
await conn.execute(CreateTable(self.Sessions))
await conn.execute(CreateTable(self.FilesInfo))
await conn.execute(CreateTable(self.Message))
async def setup_db(db_dsn, *, loop):
async with aiopg.sa.create_engine(db_dsn, loop=loop) as db_engine:
async with db_engine.acquire() as conn:
await conn.execute(CreateTable(character_table))
await conn.execute(CreateTable(actor_table))
await conn.execute(character_table.insert().values([
dict(id=1, name='James T. Kirk', species='Human'),
dict(id=2, name='Spock', species='Vulcan/Human'),
dict(id=3, name='Leonard McCoy', species='Human'),
]))
await conn.execute(actor_table.insert().values([
dict(id=1, character_id=1, name='William Shatner'),
dict(id=2, character_id=2, name='Leonard Nimoy'),
dict(id=3, character_id=3, name='DeForest Kelley'),
dict(id=4, character_id=1, name='Chris Pine'),
dict(id=5, character_id=2, name='Zachary Quinto'),
dict(id=6, character_id=3, name='Karl Urban'),
]))
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')
