async def find_by_url_or_id(
cls, url_or_id: str) -> Optional["BaseORMModelType"]:
if is_uuid(url_or_id):
statement = select(cls).where(cls.id == url_or_id)
else:
statement = select(cls).where(cls.url == url_or_id)
async with db_session() as session:
try:
result = await session.exec(statement)
except StatementError:
return None
return result.one_or_none()
def find_domain_invitations_statement(self) -> Select:
from joj.horse import models
statement = select(models.DomainInvitation).where(
models.DomainInvitation.domain_id == self.id
)
return statement
def find_domain_roles_statement(self) -> Select:
from joj.horse import models
statement = select(models.DomainRole).where(
models.DomainRole.domain_id == self.id
)
return statement
async def delete_like(
path: VideoLikeUnlike,
user: User = Depends(cognito_signed_in),
db_session: AsyncSession = Depends(yield_db_session),
) -> Any:
"""
Remove like to a video
"""
video_statement = (
select(Video)
.where(
and_(
Video.path == path.path,
Video.likes.any(name=user.name), # type: ignore
)
)
.options(selectinload(Video.likes))
)
result = await db_session.exec(video_statement) # type: ignore
video = result.one_or_none()
if not video:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail='Video not found.',
)
video.likes.remove(user)
db_session.add(video)
await db_session.commit()
await db_session.refresh(video)
return await fetch_one_or_none_video(video_path=video.path, db_session=db_session)
def find_problems_statement(self, include_hidden: bool) -> Select:
from joj.horse import models
statement = select(models.Problem).where(models.Problem.domain_id == self.id)
if not include_hidden:
statement = statement.where(models.Problem.hidden != true())
return statement
async def datasets(for_research: Optional[str],
session: Session = Depends(get_session)):
expr = select(Dataset)
if for_research:
expr = expr.where(Dataset.parent_research == for_research)
fitms = session.exec(expr).all()
return fitms
def get_previous_social() -> Social:
with Session(engine) as session:
statement = select(Social).order_by(
Social.id.desc()).offset(1).limit(1)
result = session.exec(statement).one_or_none()
logging.info(f"SELECT previous social row: {result}")
return result
def update_user(self, user_id: int, user: schemas.UserUpdate) -> Any:
""" Update User"""
try:
with session_scope() as db:
statement = select(
models.User).where(models.User.id == user_id)
results = db.exec(statement)
db_user = results.one()
db_user.first_name = user.first_name
db_user.last_name = user.last_name
db_user.full_name = user.full_name
db_user.city = user.city
db_user.country = user.country
db_user.is_active = user.is_active
db_user.is_superuser = user.is_superuser
db_user.is_admin = user.is_admin
db_user.modified_by_userid = user.modified_by_userid
db_user.modified_timestamp = datetime.utcnow()
db.add(db_user)
db.commit()
db.refresh(db_user)
return db_user
except SQLAlchemyError as e:
fastapi_logger.exception("update_user")
return None
def get_count(self, db: Session) -> int:
"""
Get total number of objects in database.
:param db: Database Session to be used
"""
# return db.query(self.model).count()
return db.scalar(select(func.count()).select_from(self.model))
def set_posting_as_sent(self, sha: str):
with Session(engine) as session:
statement = select(Posting).where(Posting.sha == sha)
posting = session.exec(statement).first()
posting.sent = True
session.add(posting)
session.commit()
async def create_or_update(oauth_name: str, token: Dict[str, Any],
profile: OAuth2Profile) -> "UserOAuthAccount":
access_token = token["access_token"]
refresh_token = token.get("refresh_token", None)
expires_at = token.get("expires_at", None)
async with db_session() as session:
statement = (select(UserOAuthAccount).where(
UserOAuthAccount.oauth_name == oauth_name).where(
UserOAuthAccount.account_id == profile.account_id))
results = await session.exec(statement)
oauth_account: Optional[UserOAuthAccount] = results.one_or_none()
if oauth_account:
oauth_account.access_token = access_token
oauth_account.refresh_token = refresh_token
oauth_account.expires_at = expires_at
oauth_account.account_name = profile.account_name
else:
oauth_account = UserOAuthAccount(
oauth_name=oauth_name,
access_token=access_token,
refresh_token=refresh_token,
expires_at=expires_at,
account_id=profile.account_id,
account_name=profile.account_name,
account_email=profile.account_email,
)
session.sync_session.add(oauth_account)
await session.commit()
await session.refresh(oauth_account)
return oauth_account
async def delete_file(
path: DeletedFileResponse,
boto_session: AioBaseClient = Depends(get_boto),
user: User = Depends(cognito_signed_in),
db_session: AsyncSession = Depends(yield_db_session),
) -> dict[str, str]:
"""
Delete file with filename
"""
video_statement = select(Video).where(and_(Video.path == path.path, Video.user_id == user.id))
db_result = await db_session.exec(video_statement) # type: ignore
video = db_result.one_or_none()
if not video:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail='File not found. Ensure you own the file, and that the file already exist.',
)
await boto_session.delete_object(Bucket=settings.S3_BUCKET_URL, Key=video.path)
if video.thumbnail_uri:
await boto_session.delete_object(
Bucket=settings.S3_BUCKET_URL, Key=video.thumbnail_uri.split('https://gg.klepp.me/')[1]
)
await db_session.delete(video)
await db_session.commit()
return {'path': path.path}
def read_users(
*,
session: Session = Depends(get_session),
offset: int = 0,
limit: int = Query(default=100, lte=100),
):
users = session.exec(select(User).offset(offset).limit(limit)).all()
return users
def store_parent(ses, k, v):
for kk in v.keys():
statement = select(GeoLabel).where(GeoLabel.id == str(kk))
results = session.exec(statement)
labl = results.one()
labl.parent = kk
ses.add(labl)
store_parent(ses, kk, v[kk])
def read_topics(
*,
session: Session = Depends(get_session),
offset: int = 0,
limit: int = Query(default=100, lte=100),
):
topics = session.exec(select(Topic).offset(offset).limit(limit)).all()
return topics
def read_courses(*,
session: Session = Depends(get_session),
offset: int = 0,
limit: int = Query(default=100, lte=100),
current_user: User = Depends(deps.get_current_user)):
courses = session.exec(
select(Course).filter(Course.user_id == current_user.id).offset(
offset).limit(limit)).all()
return courses
def find_domain_users_statement(self) -> Select:
from joj.horse import models
statement = (
select(models.DomainUser, models.User)
.where(models.DomainUser.domain_id == self.id)
.where(models.DomainUser.user_id == models.User.id)
)
return statement
def select_heroes():
with Session(engine) as session:
heroes = session.exec(
select(Hero).where(
or_(col(Hero.name) == "Black Lion",
col(Hero.age) == 25))
# .where(Hero.age == 48)
).all()
print(heroes)
def select_heroes():
with Session(engine) as session:
statement = select(Team).where(Team.name == "Preventers")
result = session.exec(statement)
team_preventers = result.one()
print("Preventers heroes:", team_preventers.heroes)
team_new = TeamCapability(**team_preventers.dict())
team_new.capability = mode
print(team_new)
def read_assignments(*,
session: Session = Depends(get_session),
course_id: int,
offset: int = 0,
limit: int = Query(default=100, lte=100),
current_user: User = Depends(deps.get_current_user)):
assignments = session.exec(
select(Assignment).where(Assignment.course_id == course_id).offset(
offset).limit(limit)).all()
return assignments
def apply_filtering(
__base_orm_model_cls__: Type["BaseORMModelType"],
__statement__: Union[Select, Update, Delete],
**kwargs: Any,
) -> Union[Select, Update, Delete]:
statement = select(__base_orm_model_cls__)
for k, v in kwargs.items():
statement = statement.where(
getattr(__base_orm_model_cls__, k) == v)
return statement
async def patch_video(
video_patch: VideoPatch,
db_session: AsyncSession = Depends(yield_db_session),
user: User = Depends(cognito_signed_in),
) -> Any:
"""
Partially update a video.
"""
excluded = video_patch.dict(exclude_unset=True)
[
excluded.pop(x)
for x in [key for key, value in excluded.items() if value is None]
]
query_video = (select(Video).where(
and_(Video.path == video_patch.path,
Video.user_id == user.id)).options(selectinload(Video.tags)))
db_result = await db_session.exec(query_video) # type: ignore
video = db_result.one_or_none()
if not video:
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail=
'File not found. Ensure you own the file, and that the file already exist.',
)
if excluded_tags := excluded.get('tags'):
# They want to update tags, fetch available tags first
list_tag = [tag['name'] for tag in excluded_tags]
query_tags = select(Tag).where(Tag.name.in_(list_tag)) # type: ignore
tag_result = await db_session.exec(query_tags) # type: ignore
tags: list[Tag] = tag_result.all()
if len(list_tag) != len(tags):
db_list_tag = [tag.name for tag in tags]
not_found_tags = [
f'`{tag}`' for tag in list_tag if tag not in db_list_tag
]
raise HTTPException(
status_code=status.HTTP_404_NOT_FOUND,
detail=f'Tag {", ".join(not_found_tags)} not found.',
)
video.tags = tags
excluded.pop('tags')
async def indicators(session: Session = Depends(get_session)):
areas = session.exec(select(GeoLabel)).all()
trees = json.load(open('geonames.tree'))
l = copy.deepcopy(years)
l.reverse()
return {
"years": l,
"topics": topics,
"areas": areas,
"trees": trees,
}
def get_user_password(email: str) -> Any:
""" Get User Password based on email"""
try:
with session_scope() as db:
statement = select(models.User).where(models.User.email == email)
results = db.exec(statement)
data = results.one()
return data
except SQLAlchemyError as e:
fastapi_logger.exception("get_user")
return None
def get_bom(self, page_uid=None):
if page_uid is None:
page_uid = self.page.eva.onshape.uid
stmt = (
select(db.BOMTable, db.BOMItem)
.where(db.BOMItem.bom_table_id == db.BOMTable.id)
.where(db.BOMTable.name == page_uid)
)
first = self.session.exec(stmt).first()
if first:
return first[0]
async def count_domain_user(
cls, domain_id: UUID, role: Union[str, DefaultRole]
) -> int:
role = str(role)
statement = (
select(DomainUser.id)
.where(DomainUser.domain_id == domain_id)
.where(DomainUser.role == role)
.with_only_columns(count())
)
user_count = (await DomainUser.session_exec(statement)).one_or_none()
return 0 if user_count is None else user_count
def check_active_session(self, session_id: str):
""" check for active session """
try:
with session_scope() as db:
statement = select(models.UsersLoginAttempt).where(
models.UsersLoginAttempt.session_id == session_id)
results = db.exec(statement)
data = results.one()
return data
except SQLAlchemyError as e:
fastapi_logger.exception("check_active_session")
return None
def get_user_id(self, id: int) -> Any:
""" Get User Data based on id"""
try:
with session_scope() as db:
statement = select(models.User).where(
models.User.id == id).options(defer('password'))
results = db.exec(statement)
data = results.one()
return data
except SQLAlchemyError as e:
fastapi_logger.exception("get_user_id")
return None
def get_article(self, article_id: str):
""" Get A Single article """
try:
with session_scope() as db:
statement = select(models.Article).where(
models.Article.article_id == article_id)
results = db.exec(statement)
data = results.one()
return data
except SQLAlchemyError as e:
fastapi_logger.exception("get_article")
return None
def last_time_thing_created(session: Session,
authority_id: str) -> Optional[datetime.datetime]:
# A bit of a hack to work around postgres perf issues. Limit the number of records to examine by including a
# time created date in the qualifier.
one_year_ago = datetime.datetime(year=datetime.date.today().year - 1,
month=1,
day=1)
created_select = (select(
Thing.tcreated).filter(Thing.authority_id == authority_id).filter(
Thing.tcreated >= one_year_ago).limit(1).order_by(
Thing.tcreated.desc()))
result = session.exec(created_select).first()
return result
