async def _create_user(conn, name: str, group: RowProxy) -> RowProxy:
result = await conn.execute(users.insert().values(**random_user(
name=name)).returning(literal_column("*")))
user = await result.fetchone()
result = await conn.execute(user_to_groups.insert().values(uid=user.id,
gid=group.gid))
return user
async def engine(pg_engine: Engine):
async with pg_engine.acquire() as conn:
await conn.execute(users.insert().values(**random_user(name="A")))
await conn.execute(users.insert().values(**random_user()))
await conn.execute(users.insert().values(**random_user()))
await conn.execute(
projects.insert().values(**random_project(prj_owner=1)))
await conn.execute(
projects.insert().values(**random_project(prj_owner=2)))
await conn.execute(
projects.insert().values(**random_project(prj_owner=3)))
with pytest.raises(ForeignKeyViolation):
await conn.execute(
projects.insert().values(**random_project(prj_owner=4)))
yield pg_engine
async def test_insert_user(engine):
async with engine.acquire() as conn:
# execute + scalar
res: ResultProxy = await conn.execute(
users.insert().values(**random_user(name="FOO")))
assert res.returns_rows
assert res.rowcount == 1
assert res.keys() == ("id", )
user_id = await res.scalar()
assert isinstance(user_id, int)
assert user_id > 0
# only scalar
user2_id: int = await conn.scalar(
users.insert().values(**random_user(name="BAR")))
assert isinstance(user2_id, int)
assert user2_id == user_id + 1
# query result
res: ResultProxy = await conn.execute(
users.select().where(users.c.id == user2_id))
assert res.returns_rows
assert res.rowcount == 1
assert len(res.keys()) > 1
# DIFFERENT betwen .first() and fetchone()
user2: RowProxy = await res.first()
# Fetch the first row and then close the result set unconditionally.
assert res.closed
res: ResultProxy = await conn.execute(
users.select().where(users.c.id == user2_id))
user2a: RowProxy = await res.fetchone()
# If rows are present, the cursor remains open after this is called.
assert not res.closed
assert user2 == user2a
user2b: RowProxy = await res.fetchone()
# If no more rows, the cursor is automatically closed and None is returned
assert user2b is None
assert res.closed
async def user_id(pg_engine: Engine) -> int:
async with pg_engine.acquire() as conn:
# a 'me' user
uid = await conn.scalar(
users.insert().values(**(random_user())).returning(users.c.id))
yield uid
# cleanup
async with pg_engine.acquire() as conn:
# a 'me' user
uid = await conn.execute(users.delete().where(users.c.id == uid))
async def engine(pg_engine: Engine):
# injects ...
async with pg_engine.acquire() as conn:
# a 'me' user
user_id = await conn.scalar(users.insert().values(**random_user(
name=USERNAME)).returning(users.c.id))
# has a project 'parent'
await conn.execute(projects.insert().values(
**random_project(prj_owner=user_id, name=PARENT_PROJECT_NAME)))
yield pg_engine
async def user(pg_engine: Engine) -> RowProxy:
# some user
async with pg_engine.acquire() as conn:
result: Optional[ResultProxy] = await conn.execute(
users.insert().values(**random_user(
name=USERNAME)).returning(users))
assert result.rowcount == 1
_user: Optional[RowProxy] = await result.first()
assert _user
assert _user.name == USERNAME
return _user
async def test_all_group(make_engine):
engine = await make_engine()
sync_engine = make_engine(is_async=False)
metadata.drop_all(sync_engine)
metadata.create_all(sync_engine)
async with engine.acquire() as conn:
# now check the only available group is the all group
groups_count = await conn.scalar(
select([func.count()]).select_from(groups))
assert groups_count == 1
result = await conn.execute(
groups.select().where(groups.c.type == GroupType.EVERYONE))
all_group_gid = (await result.fetchone()).gid
assert all_group_gid == 1 # it's the first group so it gets a 1
# try removing the all group
with pytest.raises(RaiseException):
await conn.execute(
groups.delete().where(groups.c.gid == all_group_gid))
# check adding a user is automatically added to the all group
result = await conn.execute(users.insert().values(
**random_user()).returning(literal_column("*")))
user: RowProxy = await result.fetchone()
result = await conn.execute(user_to_groups.select().where(
user_to_groups.c.gid == all_group_gid))
user_to_groups_row: RowProxy = await result.fetchone()
assert user_to_groups_row.uid == user.id
assert user_to_groups_row.gid == all_group_gid
# try removing the all group
with pytest.raises(RaiseException):
await conn.execute(
groups.delete().where(groups.c.gid == all_group_gid))
# remove the user now
await conn.execute(users.delete().where(users.c.id == user.id))
users_count = await conn.scalar(
select([func.count()]).select_from(users))
assert users_count == 0
# check the all group still exists
groups_count = await conn.scalar(
select([func.count()]).select_from(groups))
assert groups_count == 1
result = await conn.execute(
groups.select().where(groups.c.type == GroupType.EVERYONE))
all_group_gid = (await result.fetchone()).gid
assert all_group_gid == 1 # it's the first group so it gets a 1
async def test_own_group(make_engine):
engine = await make_engine()
sync_engine = make_engine(is_async=False)
metadata.drop_all(sync_engine)
metadata.create_all(sync_engine)
async with engine.acquire() as conn:
result = await conn.execute(users.insert().values(
**random_user()).returning(literal_column("*")))
user: RowProxy = await result.fetchone()
assert not user.primary_gid
# now fetch the same user that shall have a primary group set by the db
result = await conn.execute(users.select().where(users.c.id == user.id)
)
user: RowProxy = await result.fetchone()
assert user.primary_gid
# now check there is a primary group
result = await conn.execute(
groups.select().where(groups.c.type == GroupType.PRIMARY))
primary_group: RowProxy = await result.fetchone()
assert primary_group.gid == user.primary_gid
groups_count = await conn.scalar(
select([func.count(groups.c.gid)
]).where(groups.c.gid == user.primary_gid))
assert groups_count == 1
relations_count = await conn.scalar(
select([func.count()]).select_from(user_to_groups))
assert relations_count == 2 # own group + all group
# try removing the primary group
with pytest.raises(ForeignKeyViolation):
await conn.execute(
groups.delete().where(groups.c.gid == user.primary_gid))
# now remove the users should remove the primary group
await conn.execute(users.delete().where(users.c.id == user.id))
users_count = await conn.scalar(
select([func.count()]).select_from(users))
assert users_count == 0
groups_count = await conn.scalar(
select([func.count()]).select_from(groups))
assert groups_count == 1 # the all group is still around
relations_count = await conn.scalar(
select([func.count()]).select_from(user_to_groups))
assert relations_count == (users_count + users_count)
async def engine(loop, postgres_db: sa.engine.Engine):
# pylint: disable=no-value-for-parameter
async with aiopg.sa.create_engine(str(postgres_db.url)) as pg_engine:
# injects TABLES ...
async with pg_engine.acquire() as conn:
# a 'me' user
user_id = await conn.scalar(users.insert().values(**random_user(
name=USERNAME)).returning(users.c.id))
# has a project 'parent'
await conn.execute(projects.insert().values(**random_project(
prj_owner=user_id, name=PARENT_PROJECT_NAME,
uuid=PROJECT_UUID)))
# has a project 'another'
await conn.execute(projects.insert().values(**random_project(
prj_owner=user_id, name="another", uuid=ANOTHER_UUID)))
yield pg_engine
def user_id(postgres_engine: sa.engine.Engine) -> Iterable[int]:
# inject user in db
# NOTE: Ideally this (and next fixture) should be done via webserver API but at this point
# in time, the webserver service would bring more dependencies to other services
# which would turn this test too complex.
# pylint: disable=no-value-for-parameter
stmt = users.insert().values(**random_user(name="test")).returning(users.c.id)
print(str(stmt))
with postgres_engine.connect() as conn:
result = conn.execute(stmt)
[usr_id] = result.fetchone()
yield usr_id
with postgres_engine.connect() as conn:
conn.execute(users.delete().where(users.c.id == usr_id))
async def user_id(postgres_engine: Engine) -> Iterable[int]:
# inject a random user in db
# NOTE: Ideally this (and next fixture) should be done via webserver API but at this point
# in time, the webserver service would bring more dependencies to other services
# which would turn this test too complex.
# pylint: disable=no-value-for-parameter
stmt = users.insert().values(**random_user(name="test")).returning(
users.c.id)
print(str(stmt))
async with postgres_engine.acquire() as conn:
result = await conn.execute(stmt)
row = await result.fetchone()
assert isinstance(row.id, int)
yield row.id
async with postgres_engine.acquire() as conn:
await conn.execute(users.delete().where(users.c.id == row.id))
