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_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_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_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_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_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_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_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_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_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_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_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_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')
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
def test_insert3(db: DB):
insertId = 1000
email = '*****@*****.**'
# first insert a record
async def insert_fn():
results = await db.table('users').insert({
'id': insertId,
'email': email,
'password': '******',
'admin': None
}).returning('id', 'email')
return results
rows = loop.run_until_complete(insert_fn())
assert len(rows) == 1
assert rows[0]['email'] == email
# insert the same record with on conflic do nothing
async def insert_fn2():
results = await db.table('users').insert({
'id': insertId,
'email': f'{email} x2',
'password': '******',
'admin': None
}).on_conflict('(id)', 'DO NOTHING').returning('id', 'email')
return results
rows = loop.run_until_complete(insert_fn2())
assert len(rows) == 0
# insert the same record with on conflic do update
async def insert_fn3():
results = await db.table('users AS u').insert({
'id': insertId,
'email': f'{email} x3',
'password': '******',
'admin': None
}).on_conflict('ON CONSTRAINT users_pkey', "DO UPDATE SET email = EXCLUDED.email || ' (formerly ' || u.email || ')'").\
returning('id', 'email')
return results
rows = loop.run_until_complete(insert_fn3())
assert len(rows) == 1
assert rows[0]['email'] == f'{email} x3 (formerly {email})'
rows = loop.run_until_complete(
db.table('users').where('id', insertId).delete().returning())
assert len(rows) == 1
assert rows[0]['id'] == insertId
def test_group_by(db: DB):
async def create_table():
return await db.schema('TABLE cards_tmp').create(
'id serial PRIMARY KEY',
'board_id integer not null',
)
async def drop_table():
return await db.schema('TABLE cards_tmp').drop()
loop.run_until_complete(create_table())
loop.run_until_complete(
db.table('cards_tmp').insert(
{'board_id': 1},
{'board_id': 1},
{'board_id': 2},
))
rows = loop.run_until_complete(
db.table('cards_tmp').select('board_id').group_by('board_id'))
loop.run_until_complete(drop_table())
assert len(rows) == 2
rows.sort(key=lambda x: x['board_id'])
assert rows[0]['board_id'] == 1
assert rows[1]['board_id'] == 2
def test_alter_primary_key(db: DB):
pkname = 'id_board_id_pky'
async def add_primary():
return await db.schema('TABLE cards_copy').alter(
f'add CONSTRAINT {pkname} PRIMARY KEY (id, board_id)',
)
async def drop_primary():
await db.schema('TABLE cards_copy').alter(
f'DROP CONSTRAINT {pkname}'
)
# pkey is (id, board_id)
loop.run_until_complete(add_primary())
rows = loop.run_until_complete(
db.table('pg_indexes').select().where('tablename', 'cards_copy'))
assert rows[0]['indexdef'].find('(id, board_id)') != -1
# pkey is deleted
loop.run_until_complete(drop_primary())
rows = loop.run_until_complete(
db.table('pg_indexes').select().where('tablename', 'cards_copy'))
assert len(rows) == 0
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 test_alter_index(db: DB):
indexname = 'boards_user_id_location_idx'
async def add_index():
return await db.schema(f'UNIQUE INDEX {indexname} ON boards').create(
'user_id', 'location'
)
async def drop_index():
return await db.schema(f'INDEX {indexname}').drop()
loop.run_until_complete(add_index())
rows = loop.run_until_complete(
db.table('pg_indexes').select().where('indexname', indexname))
assert len(rows) == 1
loop.run_until_complete(drop_index())
def test_4drop_column(db: DB):
async def drop_fun():
# alter table drop column
await db.schema('TABLE IF EXISTS test1.test_users2').alter(
'drop user_token2', )
# alter table drop column 2
await db.schema('TABLE IF EXISTS test1.test_users2').alter(
'drop COLUMN IF EXISTS password RESTRICT', )
loop.run_until_complete(drop_fun())
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'] == 'is_admin'
assert rows[3]['column_name'] == 'user_token'
def test_create_jsonb(db: DB):
async def create_jsonb():
return await db.schema('TABLE cards_tmp').create(
'id integer not null',
'board_id integer not null',
'data jsonb',
)
async def drop_table():
return await db.schema('TABLE cards_tmp').drop()
loop.run_until_complete(create_jsonb())
rows = loop.run_until_complete(
db.table('information_schema.columns').select('data_type').where(
'table_schema', 'public').where('table_name', 'cards_tmp'))
loop.run_until_complete(drop_table())
assert len(rows) == 3
assert rows[2]['data_type'] == 'jsonb'
def test_update_returning(db: DB):
createdAt = datetime.datetime(
2021, 6, 19, 19, 46, tzinfo=datetime.timezone.utc)
async def insert_fn():
results = ['add', 'missing', 'update', 'returning']
results = await db.table('task_results').insert({
'task_id': 179,
'created_at': createdAt,
'result': '\n'.join(results)
}).returning('id')
return results
async def update_fn(taskID):
results = await db.table('task_results').update({
'created_at': createdAt,
'result': 'update returning',
'task_id': 129,
}).where('id', taskID).returning()
return results
async def update_fn2(taskID):
results = await db.table('task_results').update({
'result': '',
'task_id': 139,
}).where('id', taskID).returning('id', 'task_id')
return results
rows = loop.run_until_complete(insert_fn())
assert len(rows) == 1
insertedId = rows[0]['id']
rows = loop.run_until_complete(update_fn(insertedId))
assert len(rows) == 1
assert rows[0]['result'] == 'update returning'
assert rows[0]['task_id'] == 129
rows = loop.run_until_complete(update_fn2(insertedId))
assert len(rows) == 1
assert 'result' not in rows[0]
assert rows[0]['task_id'] == 139
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_user(db: DB):
async def insert_user(email1, email2):
return await db.table('users').insert(
{'email': email1, 'password': '******'},
{'email': email2, 'password': '******'}
)
async def get_user(email):
return await db.table('users').select().where('email', email)
email1 = ''.join(random.choice(string.ascii_letters) for i in range(6))
email2 = ''.join(random.choice(string.ascii_letters) for i in range(6))
loop.run_until_complete(insert_user(email1, email2))
rows1 = loop.run_until_complete(get_user(email1))
rows2 = loop.run_until_complete(get_user(email2))
loop.run_until_complete(db.table('users').where('email', email1).delete())
loop.run_until_complete(db.table('users').where('email', email2).delete())
assert rows1[0]['email'] == email1
assert rows2[0]['email'] == email2
def test_create_primary_key(db: DB):
async def create_user_tmp():
return await db.schema('TABLE users_tmp').create(
'name text not null',
'email text not null',
'PRIMARY KEY(name, email)',
'password text not null',
'registered_on timestamp not null DEFAULT NOW()',
'admin boolean not null default false',
)
async def drop_table():
return await db.schema('TABLE users_tmp').drop()
loop.run_until_complete(create_user_tmp())
rows = loop.run_until_complete(
db.table('pg_indexes').select().where('indexname', 'users_tmp_pkey'))
loop.run_until_complete(drop_table())
assert rows[0]['indexdef'].find('(name, email)') != -1
def test_create_user_tmp(db: DB):
async def create_user_tmp():
return await db.schema('TABLE users_tmp').create(
'id serial PRIMARY KEY',
'email text not null unique',
'password text not null',
'registered_on timestamp not null DEFAULT NOW()',
'admin boolean not null default false',
)
async def drop_table():
return await db.schema('TABLE users_tmp').drop()
loop.run_until_complete(create_user_tmp())
rows = loop.run_until_complete(
db.table('information_schema.columns').select(
'column_name', 'column_default', 'is_nullable',
'data_type').where('table_schema',
'public').where('table_name', 'users_tmp'))
loop.run_until_complete(drop_table())
# verify each column
assert len(rows) == 5
assert rows[0]['column_name'] == 'id'
assert rows[0]['column_default'].find('nextval') != -1
assert rows[0]['is_nullable'] == 'NO'
assert rows[0]['data_type'] == 'integer'
assert rows[1]['column_name'] == 'email'
assert rows[1]['column_default'] is None
assert rows[1]['is_nullable'] == 'NO'
assert rows[1]['data_type'] == 'text'
assert rows[2]['column_name'] == 'password'
assert rows[2]['column_default'] is None
assert rows[2]['is_nullable'] == 'NO'
assert rows[2]['data_type'] == 'text'
assert rows[3]['column_name'] == 'registered_on'
assert rows[3]['column_default'] == 'now()'
assert rows[3]['is_nullable'] == 'NO'
assert rows[3]['data_type'] == 'timestamp without time zone'
assert rows[4]['column_name'] == 'admin'
assert rows[4]['column_default'] == 'false'
assert rows[4]['is_nullable'] == 'NO'
assert rows[4]['data_type'] == 'boolean'
def test_with_values_uuid(db: DB):
async def insert_fn():
results = await db.table('tasks_uuid_pkey').insert({
'name': 'test123'
}).returning('id')
results = await db.with_values('uuid_vals', *results).\
table('tasks_uuid_pkey').\
select('tasks_uuid_pkey.name', 'tasks_uuid_pkey.id').\
join('uuid_vals', 'uuid_vals.id = tasks_uuid_pkey.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
