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))