def GetOrAdd(
session: sql.orm.session.Session,
model,
defaults: typing.Dict[str, object] = None,
**kwargs,
):
"""Instantiate a mapped database object.
If the object is not in the database,
add it. Note that no change is written to disk until commit() is called on the
session.
Args:
session: The database session.
model: The database table class.
defaults: Default values for mapped objects.
kwargs: The values for the table row.
Returns:
An instance of the model class, with the values specified.
"""
instance = session.query(model).filter_by(**kwargs).first()
if not instance:
params = {
k: v
for k, v in kwargs.items()
if not isinstance(v, sql.sql.expression.ClauseElement)
}
params.update(defaults or {})
instance = model(**params)
session.add(instance)
return instance
def _parse_args(
config: Config, session: sqlalchemy.orm.session.Session, args: argparse.Namespace
):
"""Parses the given commandline arguments.
Args:
config: Configuration in use.
session: Current db session.
args: Commandline arguments to parse.
Raises:
SystemExit: Query returned no tracks.
"""
if args.album:
query_type = "album"
elif args.extra:
query_type = "extra"
else:
query_type = "track"
items = query.query(args.query, session, query_type=query_type)
if not items:
raise SystemExit(1)
for item in items:
log.info(f"Removing '{item}' from the library.")
session.delete(item)
def bootstrap(sess: sa.orm.session.Session,
delete_first: bool = True) -> Dict[str, List[Base]]:
tables: List[Type[Base]] = [
CovidCategory,
CovidMeasurement,
CovidUnit,
CovidDemographic,
CovidProvider,
Location,
CovidVariable,
]
if sess.bind is None:
raise ValueError("Session must be bound to an engine or connection")
# drop in reverse order to avoid constraint issues
if delete_first:
for t in tables[::-1]:
# first delete from table
sess.execute(t.__table__.delete())
sess.commit()
components = {}
for t in tables:
ins, rows = _bootstrap_csv_to_orm(t, sess.bind)
components[t.__tablename__] = rows
ins.execute()
return components
def _highscores_helper(
s: sqlalchemy.orm.session.Session,
mapped_class: sqlalchemy.ext.declarative.api.DeclarativeMeta,
game_column: sqlalchemy.orm.attributes.InstrumentedAttribute
) -> Sequence[Game]:
"""Generic function to find highscores against arbitrary foreign keys.
Parameters:
mapped_class: the foreign key table's class
game_column: the foreign key's column in Games table
Returns:
Array of results
"""
results = []
q = s.query(Game)
for i in s.query(mapped_class).filter(
# error: Type[Any] has no attribute "playable"
mapped_class.playable == sqlalchemy.true()).order_by(
mapped_class.name).all():
result = q.filter(game_column == i).order_by(
Game.score.desc()).limit(1).first()
if result:
results.append(result)
return results
def _assert_db_contents(
project_id: str,
postgres_session: sa.orm.session.Session,
fake_workbench_payload: Dict[str, Any],
fake_workbench_adjacency_list: Dict[str, Any],
check_outputs: bool,
):
# pylint: disable=no-member
pipeline_db = (postgres_session.query(comp_pipeline).filter(
comp_pipeline.c.project_id == project_id).one())
assert pipeline_db.project_id == project_id
assert pipeline_db.dag_adjacency_list == fake_workbench_adjacency_list
# check db comp_tasks
tasks_db = (postgres_session.query(comp_tasks).filter(
comp_tasks.c.project_id == project_id).all())
mock_pipeline = fake_workbench_payload
assert len(tasks_db) == len(mock_pipeline)
for task_db in tasks_db:
assert task_db.project_id == project_id
assert task_db.node_id in mock_pipeline.keys()
assert task_db.inputs == mock_pipeline[task_db.node_id].get("inputs")
if check_outputs:
assert task_db.outputs == mock_pipeline[task_db.node_id].get(
"outputs")
assert task_db.image["name"] == mock_pipeline[task_db.node_id]["key"]
assert task_db.image["tag"] == mock_pipeline[
task_db.node_id]["version"]
def get_or_create(
cls,
session: sqlalchemy.orm.session.Session,
object: Base,
organization: Organization,
object_role: str,
organization_role: str,
):
"""
Creates a new role for a given organization and a given object.
"""
obj_role = (
session.query(ObjectRole)
.filter(
ObjectRole.organization_id == organization.id,
ObjectRole.object_id == object.id,
ObjectRole.object_type == object.type,
ObjectRole.object_role == object_role,
ObjectRole.organization_role == organization_role,
ObjectRole.deleted_at == None,
)
.one_or_none()
)
if obj_role is None:
obj_role = ObjectRole(
organization_id=organization.id,
object_id=object.id,
object_type=object.type,
object_role=object_role,
organization_role=organization_role,
)
session.add(obj_role)
return obj_role
def save_logfile_progress(s: sqlalchemy.orm.session.Session, logfile: str,
pos: int) -> None:
"""Save the position for a logfile."""
log = get_logfile_progress(s, logfile)
log.bytes_parsed = pos
s.add(log)
s.commit()
def list_users(guild_id: str,
session: sqlalchemy.orm.session.Session = db.session):
"""List all registered users on guild.
Args:
guild_id (str):
Discord GuildID.
session (sqlalchemy.orm.session.Session, optional):
DB session to operate.
Default: db.session.
Returns:
List[Users]:
registered users list.
"""
all_users = None
try:
all_users = session.query(Users).filter(
Users.guild_id == str(guild_id)).all()
except Exception as ex:
logger.exception(ex)
finally:
session.close()
return all_users
def _create_query(session: sqlalchemy.orm.session.Session, terms: List[str],
query_type: str) -> sqlalchemy.orm.query.Query:
"""Creates a query statement.
Args:
session: Current db session.
terms: Query terms to parse.
query_type: Type of query. Should be one of "album", "extra", or "track"
Returns:
Sqlalchemy query statement.
Raises:
QueryError: Invalid query terms or type.
"""
if query_type == "track":
library_query = session.query(Track).join(Album)
elif query_type == "album":
library_query = session.query(Album).join(Track)
elif query_type == "extra":
library_query = session.query(Extra).join(Album).join(Track)
else:
raise QueryError(f"Invalid query type: {query_type}")
# only join Extras if the table is not empty
if session.query(Extra).all() and query_type in {"album", "track"}:
library_query = library_query.join(Extra)
for term in terms:
library_query = library_query.filter(
_create_expression(_parse_term(term)))
return library_query
def add_event(s: sqlalchemy.orm.session.Session, data: dict) -> None:
"""Normalise and add a milestone event.
XXX: DOES NOT COMMIT YOU MUST COMMIT (For speedy reasons)"""
data["gid"] = "%s:%s:%s" % (data["name"], data["src_abbr"], data["start"])
if data["type"] == "begin":
_new_game(s, data)
elif data["type"] == "death.final":
_end_game(s, data)
branch = get_branch(s, data["br"])
m = {
"gid": data["gid"],
"xl": data["xl"],
"place_id": get_place(s, branch, data["lvl"]).id,
"oplace_id": get_place_from_string(s, data["oplace"]).id,
"god_id": get_god(s, data["god"]).id,
"turn": data["turn"],
"dur": data["dur"],
"runes": data["runes"],
"time": modelutils.crawl_date_to_datetime(data["time"]),
"potionsused": data["potionsused"],
"scrollsused": data["scrollsused"],
"skill_id": get_skill(s, data["sk"]).id,
"sklev": data["sklev"],
"verb_id": get_verb(s, data["type"]).id,
"msg": data["milestone"]
}
s.add(Milestone(**m))
def add_data(
session: sqlalchemy.orm.session.Session,
data_list: List[Dict[str, str]]
) -> None:
"""Add the data into database"""
for data in data_list:
new_data = User(**data)
session.add(new_data)
def setup_skills(s: sqlalchemy.orm.session.Session) -> None:
"""Load skill data into the database."""
new = []
for sk in const.SKILLS:
if not s.query(Skill).filter(Skill.name == sk).first():
logging.info("Adding skill '%s'" % sk)
new.append({"name": sk})
s.bulk_insert_mappings(Skill, new)
s.commit()
def setup_verbs(s: sqlalchemy.orm.session.Session) -> None:
"""Load verb data into the database."""
new = []
for verb in const.VERBS:
if not s.query(Verb).filter(Verb.name == verb).first():
logging.info("Adding verb '%s'" % verb)
new.append({"name": verb})
s.bulk_insert_mappings(Verb, new)
s.commit()
def setup_species(s: sqlalchemy.orm.session.Session) -> None:
"""Load species data into the database."""
new = []
for sp in const.SPECIES:
if not s.query(Species).filter(Species.short == sp.short).first():
logging.info("Adding species '%s'" % sp.full)
new.append({"short": sp.short, "name": sp.full})
s.bulk_insert_mappings(Species, new)
s.commit()
def setup_ktyps(s: sqlalchemy.orm.session.Session) -> None:
"""Load ktyp data into the database."""
new = []
for ktyp in const.KTYPS:
if not s.query(Ktyp).filter(Ktyp.name == ktyp).first():
logging.info("Adding ktyp '%s'" % ktyp)
new.append({"name": ktyp})
s.bulk_insert_mappings(Ktyp, new)
s.commit()
def setup_gods(s: sqlalchemy.orm.session.Session) -> None:
"""Load god data into the database."""
new = []
for god in const.GODS:
if not s.query(God).filter(God.name == god.name).first():
logging.info("Adding god '%s'" % god.name)
new.append({"name": god.name})
s.bulk_insert_mappings(God, new)
s.commit()
def get_version(s: sqlalchemy.orm.session.Session, v: str) -> Version:
"""Get a version, creating it if needed."""
version = s.query(Version).filter(Version.v == v).first()
if version:
return version
else:
version = Version(v=v)
s.add(version)
s.commit()
return version
def get_data(
session: sqlalchemy.orm.session.Session,
ascending: bool = True, **kwargs: Union[int, str]
) -> List[User]:
"""Get a list of data"""
direction = asc if ascending else desc
if kwargs:
return session.query(User).filter_by(**kwargs).\
order_by(direction("id")).all()
return session.query(User).order_by(direction("id")).all()
def get_server(s: sqlalchemy.orm.session.Session, name: str) -> Server:
"""Get a server, creating it if needed."""
server = s.query(Server).filter(Server.name == name).first()
if server:
return server
else:
server = Server(name=name)
s.add(server)
s.commit()
return server
def setup_backgrounds(s: sqlalchemy.orm.session.Session) -> None:
"""Load background data into the database."""
new = []
for bg in const.BACKGROUNDS:
if not s.query(Background).filter(
Background.short == bg.short).first():
logging.info("Adding background '%s'" % bg.full)
new.append({"short": bg.short, "name": bg.full})
s.bulk_insert_mappings(Background, new)
s.commit()
def get_logfile_progress(s: sqlalchemy.orm.session.Session,
url: str) -> Logfile:
"""Get a logfile progress records, creating it if needed."""
log = (s.query(Logfile).filter(Logfile.source_url == url).one_or_none())
if log:
return log
else:
log = Logfile(source_url=url)
s.add(log)
s.commit()
return log
def stamp_edit(request_user_id: int, reference_obj: models_alch.Reference,
session: sqlalchemy.orm.session.Session) -> None:
""" Updates when the Reference-object was last edited and by whom.
Called by manage_relationships_post() """
log.debug('starting stamp_edit()')
edit = models_alch.ReferenceEdit(reference_id=reference_obj.id,
user_id=request_user_id,
timestamp=datetime.datetime.utcnow())
session.add(edit)
session.commit()
return
def load_tracks(tracks: typing.List[typing.Dict[str, typing.Any]], *,
session: sqlalchemy.orm.session.Session) -> None:
"""Load tracks into table in the database."""
inserts = ({
key: value if key not in {
'creationTimestamp', 'lastModifiedTimestamp', 'recentTimestamp',
'recentTimestamp'
} else value.datetime
for key, value in track.items()
} for track in tracks)
session.bulk_insert_mappings(tables.trackdb.NewTracks, inserts)
def get_skill(s: sqlalchemy.orm.session.Session, name: str) -> Ktyp:
"""Get a verb/type by name, creating it if needed."""
skill = s.query(Skill).filter(Skill.name == name).first()
if skill:
return skill
else:
skill = Skill(name=name)
s.add(skill)
s.commit()
logging.warning("Found new skill %s, please add me to constants.py" %
name)
return skill
def get_branch(s: sqlalchemy.orm.session.Session, br: str) -> Branch:
"""Get a branch by short name, creating it if needed."""
branch = s.query(Branch).filter(Branch.short == br).first()
if branch:
return branch
else:
branch = Branch(short=br, name=br, multilevel=True)
s.add(branch)
s.commit()
logging.warning("Found new branch %s, please add me to constants.py"
" and update the database." % br)
return branch
def get_verb(s: sqlalchemy.orm.session.Session, name: str) -> Ktyp:
"""Get a verb/type by name, creating it if needed."""
verb = s.query(Verb).filter(Verb.name == name).first()
if verb:
return verb
else:
verb = Verb(name=name)
s.add(verb)
s.commit()
logging.warning("Found new verb %s, please add me to constants.py" %
name)
return verb
def get_ktyp(s: sqlalchemy.orm.session.Session, name: str) -> Ktyp:
"""Get a ktyp by name, creating it if needed."""
ktyp = s.query(Ktyp).filter(Ktyp.name == name).first()
if ktyp:
return ktyp
else:
ktyp = Ktyp(name=name)
s.add(ktyp)
s.commit()
logging.warning("Found new ktyp %s, please add me to constants.py" %
name)
return ktyp
def get_god(s: sqlalchemy.orm.session.Session, name: str) -> God:
"""Get a god by name, creating it if needed."""
god = s.query(God).filter(God.name == name).first()
if god:
return god
else:
god = God(name=name)
s.add(god)
s.commit()
logging.warning("Found new god %s, please add me to constants.py"
" and update the database." % name)
return god
def get_species(s: sqlalchemy.orm.session.Session, sp: str) -> Species:
"""Get a species by short code, creating it if needed."""
species = s.query(Species).filter(Species.short == sp).first()
if species:
return species
else:
species = Species(short=sp, name=sp)
s.add(species)
s.commit()
logging.warning("Found new species %s, please add me to constants.py"
" and update the database." % sp)
return species
def get_place(s: sqlalchemy.orm.session.Session, branch: Branch,
lvl: int) -> Place:
"""Get a place, creating it if needed."""
place = s.query(Place).filter(Place.branch == branch,
Place.level == lvl).first()
if place:
return place
else:
place = Place(branch=branch, level=lvl)
s.add(place)
s.commit()
return place
