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