def test_insert1(db: DB):
async def insert_fn():
result = await db.table('users').insert(
{
'email': '*****@*****.**',
'password': '******',
'admin': None
}, {
'email': '*****@*****.**',
'password': '******',
'admin': 'DEFAULT'
}).insert({
'email': '*****@*****.**',
'password': '******',
'admin': 'DEFAULT'
}).returning('id', 'email e')
return result
rows = loop.run_until_complete(insert_fn())
assert len(rows) == 3
assert rows[0]['e'] == '*****@*****.**'
assert rows[1]['e'] == '*****@*****.**'
assert rows[2]['e'] == '*****@*****.**'
loop.run_until_complete(
db.table('users').where('id', rows[0]['id']).delete())
loop.run_until_complete(
db.table('users').where('id', rows[1]['id']).delete())
loop.run_until_complete(
db.table('users').where('id', rows[2]['id']).delete())
def test_insert_uuid(db: DB):
async def insert_fn():
results = await db.table('tasks_uuid_pkey').insert({
'name': 'test'
}).returning('id')
return results
async def insert_fn2(rid):
results = await db.table('tasks_uuid_pkey').insert({
'id': rid,
'name': 'test'
}).returning('id')
return results
rows = loop.run_until_complete(insert_fn())
assert len(rows) == 1
insertedId = rows[0]['id']
assert isinstance(insertedId, asyncpg.pgproto.pgproto.UUID)
rows = loop.run_until_complete(
db.table('tasks_uuid_pkey').where('id',
insertedId).delete().returning())
assert len(rows) == 1
rows = loop.run_until_complete(insert_fn2(insertedId))
assert len(rows) == 1
insertedId2 = rows[0]['id']
assert isinstance(insertedId2, asyncpg.pgproto.pgproto.UUID)
assert str(insertedId2) == str(insertedId)
rows = loop.run_until_complete(
db.table('tasks_uuid_pkey').where('id',
insertedId2).delete().returning())
assert len(rows) == 1
def test_rrule_rdate(db: DB):
rows = loop.run_until_complete(
db.rrule('rrule1', {
'rdate': '20210503T100000Z'
}).table('rrule1').select())
assert len(rows) == 1
assert rows[0]['rrule'] == datetime.datetime(2021,
5,
3,
10,
0,
tzinfo=datetime.timezone.utc)
rows = loop.run_until_complete(
db.rrule('rrule1', {
'rdate': ['20210503T100000Z', '20210603T100000Z']
}).table('rrule1').select())
assert len(rows) == 2
assert rows[0]['rrule'] == datetime.datetime(2021,
5,
3,
10,
0,
tzinfo=datetime.timezone.utc)
assert rows[1]['rrule'] == datetime.datetime(2021,
6,
3,
10,
0,
tzinfo=datetime.timezone.utc)
def test_update_contain_empty_text(db: DB):
createdAt = datetime.datetime(
2021, 5, 31, 22, 11, tzinfo=datetime.timezone.utc)
async def insert_fn():
results = ['started', 'finished']
results = await db.table('task_results').insert({
'task_id': 173,
'created_at': createdAt,
'result': '\t'.join(results)
}).returning('id')
return results
async def update_fn(taskID):
results = await db.table('task_results').update({
'created_at': createdAt,
'result': '',
'task_id': 137,
}).where('id', taskID)
return results
rows = loop.run_until_complete(insert_fn())
assert len(rows) == 1
insertedId = rows[0]['id']
loop.run_until_complete(update_fn(insertedId))
rows = loop.run_until_complete(
db.table('task_results').where('id', insertedId).select())
assert len(rows) == 1
assert rows[0]['result'] == ''
rows = loop.run_until_complete(
db.table('task_results').where('id', insertedId).delete().returning())
assert len(rows) == 1
assert rows[0]['id'] == insertedId
def test_insert_newline(db: DB):
createdAt = datetime.datetime(2021,
3,
8,
23,
50,
tzinfo=datetime.timezone.utc)
async def insert_fn():
results = ['started', 'finished']
results = await db.table('task_results').insert({
'task_id':
100,
'created_at':
createdAt,
'result':
'\n'.join(results)
}).returning('id')
return results
rows = loop.run_until_complete(insert_fn())
assert len(rows) == 1
insertedId = rows[0]['id']
rows = loop.run_until_complete(
db.table('task_results').where('id', rows[0]['id']).select())
assert len(rows) == 1
assert '\n' in rows[0]['result']
rows = loop.run_until_complete(
db.table('task_results').where('id',
rows[0]['id']).delete().returning())
assert len(rows) == 1
assert rows[0]['id'] == insertedId
def test_rrule_slice(db: DB):
rruleStr3 = """
DTSTART:20210203T100000Z
RRULE:FREQ=DAILY
"""
rows = loop.run_until_complete(db.rrule('task_rrules', {'rrule': rruleStr3, 'rrule_slice': slice(
3)}).table('task_rrules').select('task_rrules.rrule'))
assert len(rows) == 3
assert rows[0]['rrule'] == datetime.datetime(
2021, 2, 3, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[1]['rrule'] == datetime.datetime(
2021, 2, 4, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[2]['rrule'] == datetime.datetime(
2021, 2, 5, 10, 0, tzinfo=datetime.timezone.utc)
rows = loop.run_until_complete(db.rrule('task_rrules', {'rrule': rruleStr3, 'rrule_slice': slice(
10, 20, 2)}).table('task_rrules').select('task_rrules.rrule'))
assert len(rows) == 5
assert rows[0]['rrule'] == datetime.datetime(
2021, 2, 13, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[1]['rrule'] == datetime.datetime(
2021, 2, 15, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[2]['rrule'] == datetime.datetime(
2021, 2, 17, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[3]['rrule'] == datetime.datetime(
2021, 2, 19, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[4]['rrule'] == datetime.datetime(
2021, 2, 21, 10, 0, tzinfo=datetime.timezone.utc)
def test_7drop(db: DB):
async def drop_fn():
# drop index
await db.schema('INDEX CONCURRENTLY test1.idx_users_email').drop()
# drop index 2
await db.schema('INDEX IF EXISTS test1.idx_users_email, idx_json2'
).drop('CASCADE')
# drop table
await db.schema('TABLE test_table').drop()
# drop table if exists
await db.schema(
'TABLE IF EXISTS test_table, test_table2, test_table3, test1.test_users2'
).drop('CASCADE')
loop.run_until_complete(drop_fn())
rows = loop.run_until_complete(
db.table('pg_indexes').where('tablename', 'test_users2').where(
'indexname', 'idx_users_email').select())
assert len(rows) == 0
rows = loop.run_until_complete(
db.table('pg_indexes').where('tablename', 'test_table').where(
'indexname', 'idx_json2').select())
assert len(rows) == 0
rows = loop.run_until_complete(
db.table('information_schema.tables').where('table_name',
'test_users2').select())
assert len(rows) == 0
rows = loop.run_until_complete(
db.table('information_schema.tables').where('table_name',
'test_table').select())
assert len(rows) == 0
def test_update_newline(db: DB):
createdAt = datetime.datetime(
2021, 5, 31, 15, 25, tzinfo=datetime.timezone.utc)
async def insert_fn():
results = ['started', 'finished']
results = await db.table('task_results').insert({
'task_id': 171,
'created_at': createdAt,
'result': '\n'.join(results)
}).returning('id')
return results
async def update_fn(taskID):
results = ['started2', 'finished2']
results = await db.table('task_results').update({
'result': '\n'.join(results)
}).where('id', taskID)
return results
rows = loop.run_until_complete(insert_fn())
assert len(rows) == 1
insertedId = rows[0]['id']
loop.run_until_complete(update_fn(insertedId))
rows = loop.run_until_complete(
db.table('task_results').where('id', insertedId).select())
assert len(rows) == 1
assert '\nfinished2' in rows[0]['result']
rows = loop.run_until_complete(
db.table('task_results').where('id', insertedId).delete().returning())
assert len(rows) == 1
assert rows[0]['id'] == insertedId
def test_where_with_uuid(db: DB):
async def insert_fn():
results = await db.table('tasks_uuid_pkey').insert({
'name': 'test'
}).returning('id')
return results
rows = loop.run_until_complete(insert_fn())
assert len(rows) == 1
insertedId1 = rows[0]['id']
assert isinstance(insertedId1, asyncpg.pgproto.pgproto.UUID)
rows = loop.run_until_complete(insert_fn())
assert len(rows) == 1
insertedId2 = rows[0]['id']
assert isinstance(insertedId2, asyncpg.pgproto.pgproto.UUID)
rows = loop.run_until_complete(
db.table('tasks_uuid_pkey').select().where("id", insertedId1))
assert len(rows) == 1
rows = loop.run_until_complete(
db.table('tasks_uuid_pkey').select().where("id = ? OR id = ?",
insertedId1, insertedId2))
assert len(rows) == 2
rows = loop.run_until_complete(
db.table('tasks_uuid_pkey').where('id',
insertedId1).delete().returning())
assert len(rows) == 1
rows = loop.run_until_complete(
db.table('tasks_uuid_pkey').where('id',
insertedId2).delete().returning())
assert len(rows) == 1
def test_5create_index(db: DB):
async def index_fun():
# create index
await db.schema('INDEX idx_users_email ON test1.test_users2').create(
'email_address',
'user_token',
)
# create partial index
await db.schema('UNIQUE INDEX unique_num912 ON test_table').create(
'num9',
'num12',
).where('num9', '>', 1)
# create gin index
await db.schema('INDEX IF NOT EXISTS idx_json2 ON test_table USING GIN'
).create('(json2->firstname)', )
loop.run_until_complete(index_fun())
rows = loop.run_until_complete(
db.table('pg_indexes').where('tablename', 'test_users2').where(
'indexname', 'idx_users_email').select())
assert len(rows) == 1
rows = loop.run_until_complete(
db.table('pg_indexes').where('tablename', 'test_table').where(
'indexname', 'unique_num912').select())
assert len(rows) == 1
assert 'WHERE (num9 > 1)' in rows[0]['indexdef']
rows = loop.run_until_complete(
db.table('pg_indexes').where('tablename', 'test_table').where(
'indexname', 'idx_json2').select())
assert len(rows) == 1
def test_3modify(db: DB):
async def modify_fn():
# alter table set schema
await db.schema('TABLE IF EXISTS test_users2').alter(
'SET SCHEMA test1', )
# alter table add column
await db.schema('TABLE IF EXISTS test1.test_users2').alter(
'ADD user_token integer GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 2)',
)
# alter table add column 2
await db.schema('TABLE IF EXISTS test1.test_users2').alter(
'ADD COLUMN IF NOT EXISTS user_token2 integer GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10)',
)
loop.run_until_complete(modify_fn())
rows = loop.run_until_complete(
db.table('information_schema.columns').where(
'table_name', 'test_users2').select().order_by('ordinal_position'))
assert len(rows) == 6
assert rows[1]['column_name'] == 'email_address'
assert rows[1]['table_schema'] == 'test1'
assert rows[2]['column_name'] == 'password'
assert rows[3]['column_name'] == 'is_admin'
assert rows[4]['column_name'] == 'user_token'
assert rows[5]['column_name'] == 'user_token2'
rows = loop.run_until_complete(
db.table('information_schema.table_constraints').where(
'table_name', 'test_table').where('constraint_name',
'sixeight_num').select())
assert len(rows) == 1
def test_2rename(db: DB):
async def rename_fn():
# alter table rename
await db.schema('TABLE IF EXISTS test_users').alter(
'RENAME TO test_users2')
# alter table column name
await db.schema('TABLE IF EXISTS test_users2').alter(
'RENAME COLUMN admin TO is_admin')
# alter table column name
await db.schema('TABLE IF EXISTS ONLY test_users2').alter(
'RENAME email TO email_address')
# alter table rename constraint
await db.schema('TABLE IF EXISTS test_table').alter(
'RENAME CONSTRAINT sixeight TO sixeight_num', )
loop.run_until_complete(rename_fn())
rows = loop.run_until_complete(
db.table('information_schema.columns').where(
'table_name', 'test_users2').select().order_by('ordinal_position'))
assert len(rows) == 4
assert rows[1]['column_name'] == 'email_address'
assert rows[2]['column_name'] == 'password'
assert rows[3]['column_name'] == 'is_admin'
rows = loop.run_until_complete(
db.table('information_schema.table_constraints').where(
'table_name', 'test_table').where('constraint_name',
'sixeight_num').select())
assert len(rows) == 1
def test_rrule_no_results(db: DB):
with pytest.raises(RruleNoResults) as excinfo:
loop.run_until_complete(
db.rrule('rrule1', {'rrule': rruleStr1, 'rrule_slice': slice(1000, 1001)}, ).table('rrule1').select())
assert type(excinfo.value) is RruleNoResults
# it should reset the build after an exception is raised
rows = loop.run_until_complete(
db.rrule('rrule1', {'rrule': rruleStr1, 'rrule_slice': slice(0, 1)}, ).table('rrule1').select())
assert len(rows) == 1
def test_rrule_exdate(db: DB):
rows = loop.run_until_complete(
db.rrule('rrule1', {
'rrule': rruleStr1,
'exdate': '20210304T100000Z'
}).table('rrule1').select())
assert len(rows) == 4
assert rows[0]['rrule'] == datetime.datetime(2021,
3,
3,
10,
0,
tzinfo=datetime.timezone.utc)
assert rows[1]['rrule'] == datetime.datetime(2021,
3,
5,
10,
0,
tzinfo=datetime.timezone.utc)
assert rows[2]['rrule'] == datetime.datetime(2021,
3,
6,
10,
0,
tzinfo=datetime.timezone.utc)
assert rows[3]['rrule'] == datetime.datetime(2021,
3,
7,
10,
0,
tzinfo=datetime.timezone.utc)
rows = loop.run_until_complete(
db.rrule('rrule1', {
'rrule': rruleStr1,
'exdate': ['20210304T100000Z', '20210306T100000Z']
}).table('rrule1').select())
assert len(rows) == 3
assert rows[0]['rrule'] == datetime.datetime(2021,
3,
3,
10,
0,
tzinfo=datetime.timezone.utc)
assert rows[1]['rrule'] == datetime.datetime(2021,
3,
5,
10,
0,
tzinfo=datetime.timezone.utc)
assert rows[2]['rrule'] == datetime.datetime(2021,
3,
7,
10,
0,
tzinfo=datetime.timezone.utc)
def test_rrule_raw(db: DB):
rows = loop.run_until_complete(
db.rrule('task_rrules', {
'task_id': 1,
'rrule': rruleStr1
}, {
'task_id': 3,
'rrule': rruleStr2
}).raw(
"""
INSERT INTO task_results
(task_id, result)
SELECT task_id, 'pending' from task_rrules
WHERE rrule > $1
RETURNING id, task_id
""", datetime.datetime(2021,
3,
20,
10,
0,
tzinfo=datetime.timezone.utc)))
assert len(rows) == 3
assert rows[0]['task_id'] == 3
assert rows[1]['task_id'] == 3
assert rows[2]['task_id'] == 3
rows = loop.run_until_complete(
db.rrule('task_rrules', {
'task_id': 1,
'rrule': rruleStr1
}, {
'task_id': 3,
'rrule': rruleStr2
}).raw(
"""
DELETE FROM task_results
WHERE EXISTS(
SELECT 1 FROM task_rrules
WHERE
task_id = task_results.task_id AND
rrule > $1
)
RETURNING id, task_id
""", datetime.datetime(2021,
3,
20,
10,
0,
tzinfo=datetime.timezone.utc)))
assert len(rows) == 3
assert rows[0]['task_id'] == 3
assert rows[1]['task_id'] == 3
assert rows[2]['task_id'] == 3
def test_delete(db: DB):
rows = loop.run_until_complete(
db.table('users').insert({
'email': '*****@*****.**',
'password': '******'
}).returning())
assert rows[0]['email'] == '*****@*****.**'
loop.run_until_complete(
db.table('users').where('id', rows[0]['id']).delete())
rows = loop.run_until_complete(
db.table('users').select().where('id', rows[0]['id']))
assert len(rows) == 0
def test_drop_nonexists(db: DB):
# create a simple table and test DROP on it
loop.run_until_complete(
db.schema('TABLE users_tmp').create('name text not null'))
rows1 = loop.run_until_complete(
db.table('information_schema.columns').select('column_name').where(
'table_schema', 'public').where('table_name', 'users_tmp'))
loop.run_until_complete(db.schema('TABLE IF EXISTS users_tmp').drop())
rows2 = loop.run_until_complete(
db.table('information_schema.columns').select('column_name').where(
'table_schema', 'public').where('table_name', 'users_tmp'))
assert len(rows1) == 1
assert len(rows2) == 0
def test_rrule_join(db: DB):
rows = loop.run_until_complete(
db.rrule('task_rrules', {
'task_id': 1, 'rrule': rruleStr1
}, {
'task_id': 2, 'rrule': rruleStr2
}).table('task_rrules').
join('tasks', 'tasks.id', '=', 'task_rrules.task_id').
where('rrule > ? AND rrule <= ?',
datetime.datetime(2021, 3, 5, 10, 0,
tzinfo=datetime.timezone.utc),
datetime.datetime(2021, 3, 8, 10, 0,
tzinfo=datetime.timezone.utc),
).
select('task_rrules.rrule', 'tasks.name'))
assert len(rows) == 3
assert rows[0]['rrule'] == datetime.datetime(
2021, 3, 6, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[0]['name'] == 'tax return'
assert rows[1]['rrule'] == datetime.datetime(
2021, 3, 7, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[1]['name'] == 'tax return'
assert rows[2]['rrule'] == datetime.datetime(
2021, 3, 8, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[2]['name'] == 'pick up kids'
def test_rrule_update(db: DB):
loop.run_until_complete(
db.rrule('task_rrules', {
'task_id': 1, 'rrule': rruleStr1
}, {
'task_id': 2, 'rrule': rruleStr2
}).table('task_results').update("result = 'finished'").
from_table('task_rrules').
where('task_rrules.task_id = task_results.task_id').
where('rrule > ?', datetime.datetime(2021, 3, 20, 10, 0,
tzinfo=datetime.timezone.utc)))
rows = loop.run_until_complete(
db.table('task_results').where('result', 'finished').select())
assert len(rows) == 1
assert rows[0]['task_id'] == 2
def db(config):
app_db = DB()
async def init_db():
return await app_db.connect('db_test', {
'host': config.DB_HOST,
'port': config.DB_PORT,
'database': config.DB_TEST,
'username': config.DB_USER,
'password': config.DB_PASS
},
default=True)
asyncio.get_event_loop().run_until_complete(init_db())
yield app_db
asyncio.get_event_loop().run_until_complete(app_db.stop())
def test_insert_jsonb(db: DB):
async def insert_jsonb(test_id):
return await db.table('cards').insert({
'id':
test_id,
'board_id':
random.randint(1, 100),
'data': {
'name': f'I am {test_id}',
'address': {
'city': 'Chicago',
'state': 'IL'
}
}
})
async def get_jsonb(test_id):
return await db.table('cards').select('data->>name AS name').where(
'id', test_id)
test_id = random.randint(10000, 90000)
loop.run_until_complete(insert_jsonb(test_id))
rows = loop.run_until_complete(get_jsonb(test_id))
loop.run_until_complete(db.table('cards').where('id', test_id).delete())
assert rows[0]['name'] == f'I am {test_id}'
def test_raw_multi_queries(db: DB):
sql = 'SELECT 1 as one; SELECT 2 as two; SELECT 3 as three'
try:
loop.run_until_complete(db.raw(sql))
except Exception as exc:
assert False, f"'test_raw_multi_queries' raised an exception {exc}"
def test_rrule_where2(db: DB):
rows = loop.run_until_complete(
db.rrule('rrule2', {'rrule': rruleStr2}).table('rrule2').where('rrule < ?', datetime.datetime(2021, 3, 13, 10, 0, tzinfo=datetime.timezone.utc)).select())
assert len(rows) == 2
assert rows[0]['rrule'] == datetime.datetime(
2021, 3, 3, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[1]['rrule'] == datetime.datetime(
2021, 3, 8, 10, 0, tzinfo=datetime.timezone.utc)
def test_1create_table(db: DB):
async def create_fn():
# create table like
await db.schema('TABLE test_users').create('like users')
loop.run_until_complete(create_fn())
rows = loop.run_until_complete(
db.table('information_schema.columns').where(
'table_name', 'test_users').select().order_by('ordinal_position'))
assert len(rows) == 4
assert rows[1]['column_name'] == 'email'
assert rows[2]['column_name'] == 'password'
assert rows[3]['column_name'] == 'admin'
rows = loop.run_until_complete(
db.table('information_schema.table_constraints').where(
'table_name', 'test_users').select())
assert len(rows) == 1
def test_rrule_exrule(db: DB):
rows = loop.run_until_complete(
db.rrule('rrule1', {'rrule': rruleStr1, 'exrule': exruleStr1}).table('rrule1').select())
assert len(rows) == 3
assert rows[0]['rrule'] == datetime.datetime(
2021, 3, 3, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[1]['rrule'] == datetime.datetime(
2021, 3, 4, 10, 0, tzinfo=datetime.timezone.utc)
assert rows[2]['rrule'] == datetime.datetime(
2021, 3, 5, 10, 0, tzinfo=datetime.timezone.utc)
def test_insert_raw(db: DB):
user_id = 29998
location = ''.join(random.choice(string.ascii_letters) for i in range(6))
# test inserRaw with a rather complex query
sql = 'INSERT INTO boards ("user_id", "location") SELECT $1, $2 WHERE NOT EXISTS (SELECT "user_id" FROM boards WHERE "user_id" = $1)'
loop.run_until_complete(db.raw(sql, user_id, location))
# insert it again that has no new row gets inserted
loop.run_until_complete(db.raw(sql, user_id, location))
# verify that only 1 row was inserted
rows = loop.run_until_complete(
db.table('boards').select().where('user_id', user_id))
loop.run_until_complete(
db.table('boards').where('user_id', user_id).delete())
assert len(rows) == 1
assert rows[0]['user_id'] == user_id
assert rows[0]['location'] == location
def test_alter_unique_index(db: DB):
indexname = 'boards_user_id_location_idx'
async def add_unique():
return await db.schema(f'UNIQUE INDEX {indexname} ON boards').create(
'user_id', 'location'
)
async def drop_unique():
return await db.schema(f'INDEX {indexname}').drop()
loop.run_until_complete(add_unique())
row = {'user_id': 1399, 'location': 'northwest'}
loop.run_until_complete(db.table('boards').insert(row))
# inserting 2nd time violates unique constraint
with pytest.raises(asyncpg.exceptions.UniqueViolationError) as excinfo:
loop.run_until_complete(db.table('boards').insert(row))
assert type(excinfo.value) is asyncpg.exceptions.UniqueViolationError
loop.run_until_complete(db.table('boards').where('user_id', 1399).delete())
loop.run_until_complete(drop_unique())
def test_add_column(db: DB):
async def add_col():
await db.schema('TABLE users').alter(
"ADD COLUMN test_col varchar(50) NOT NULL DEFAULT 'test_col_default'",
)
async def drop_col():
return await db.schema('TABLE users').alter(
'drop COLUMN test_col',
)
loop.run_until_complete(add_col())
rows = loop.run_until_complete(db.table('users').insert({
'email': '*****@*****.**',
'password': '******'
}).returning())
assert rows[0]['test_col'] == 'test_col_default'
loop.run_until_complete(drop_col())
rows = loop.run_until_complete(db.table('users').select())
assert not hasattr(rows[0], 'test_col')
async def init_db(host, port, database, username, password):
db = DB()
await db.connect(database, {
'host': host,
'port': port,
'database': database,
'username': username,
'password': password,
},
default=True,
min_size=1,
max_size=1)
return db
def test_6alter_table(db: DB):
async def alter_fn():
# alter table alter column type
await db.schema('TABLE test_table').alter(
'alter num1 TYPE bigint',
'alter COLUMN num10 SET DATA TYPE bigint',
'alter text5 TYPE integer using text5::integer',
'alter text4 SET DEFAULT \'abc\'',
'alter COLUMN num3 DROP DEFAULT',
'alter "my geo3" SET NOT NULL',
'alter num3 DROP NOT NULL',
'alter column num12 ADD GENERATED ALWAYS AS IDENTITY (INCREMENT BY 2)',
'alter num11 SET GENERATED ALWAYS',
'alter num11 SET START WITH 6',
'alter num11 SET INCREMENT BY 3',
'alter num11 RESTART WITH 9',
'alter COLUMN num11 SET GENERATED BY DEFAULT SET START WITH 2 SET INCREMENT BY 8 RESTART WITH 10',
'alter num12 drop identity if exists',
'alter num1 SET STATISTICS 100',
'alter num1 SET STATISTICS -1',
'alter text1 set storage plain',
'add CONSTRAINT num7constr check(num7 > 6 and num7 > 8)',
'add UNIQUE(text1, text2) WITH (fillfactor=70)',
'add FOREIGN KEY (num12) REFERENCES cards (id) ON DELETE SET NULL',
'drop CONSTRAINT IF EXISTS sixeight CASCADE ',
)
loop.run_until_complete(alter_fn())
rows = loop.run_until_complete(
db.table('information_schema.columns').where(
'table_name', 'test_table').select().order_by('ordinal_position'))
assert len(rows) == 56
assert rows[0]['data_type'] == 'bigint'
assert rows[16]['data_type'] == 'integer'
rows = loop.run_until_complete(
db.table('information_schema.table_constraints').where(
'table_name', 'test_table').select())
assert len(rows) == 20
