def insert_score(p: Print) -> int:
""" Vloží záznamy score(name, genre, key, incipit, year) do tabulky."""
score = p.score()
score.incipit = "".join(
score.incipit) # TODO: Jenom hack –> upravit formát 'incipit' pořádně
incipit_lookup = 'incipit IS NULL' if not score.incipit else 'incipit=?'
year_lookup = 'year IS NULL' if not score.year else 'year=?'
# Vyhledávání v databázi v závislosti na přítomných parametrech
get_score_id = f"SELECT id FROM score WHERE name=? AND genre=? AND key=? AND {incipit_lookup} AND {year_lookup}"
sql_params = (score.name, score.genre, score.key)
if score.incipit:
sql_params += (score.incipit, )
if score.year:
sql_params += (score.year, )
cursor.execute(get_score_id, sql_params)
results = cursor.fetchall()
if results:
for result in results:
if compare_authors(score, result[0]) and compare_voices(
score, result[0]):
# Existující -> vrátit ID
return result[0]
# Chybí záznam -> přidat do tabulky
cursor.execute(
'INSERT INTO score(name, genre, key, incipit, year) VALUES(?, ?, ?, ?, ?)',
(score.name, score.genre, score.key, score.incipit, score.year))
db_connection.commit()
score_id = cursor.lastrowid
for voice in score.voices:
# Vložit hlasy do 'voice' + commit
cursor.execute(
'INSERT INTO voice(number, score, range, name) VALUES(?, ?, ?, ?)',
(
voice.number,
score_id,
voice.range,
voice.name,
))
db_connection.commit()
for author in score.authors:
# Vložit autory do 'score_authors' + commit
if not author.name:
# Bezejmenný autor
continue
cursor.execute('SELECT id FROM person WHERE name=?',
(author.name, )) # Najít ID autora
result = cursor.fetchone()
cursor.execute(
'INSERT INTO score_author(score, composer) VALUES(?, ?)',
(score_id, result[0])) # Junction
db_connection.commit()
return score_id
def insert_edition(p: Print, score_id: int) -> int:
""" Vloží záznamy edition(score, name, year) do tabulky."""
edition = p.edition
name_lookup = 'name IS NULL' if not edition.name else 'name=?'
year_lookup = 'year IS NULL' if not p.score().year else 'year=?'
# Vyhledávání v databázi v závislosti na přítomných parametrech
get_edition_id = f"SELECT id FROM edition WHERE score=? AND {name_lookup} AND {year_lookup}"
sql_params = (score_id, )
if edition.name:
sql_params += (edition.name, )
if p.score().year:
sql_params += (p.score().year, )
cursor.execute(get_edition_id, sql_params)
results = cursor.fetchall()
if results:
for result in results:
if compare_editors(edition, result[0]):
# Existující -> vrátit ID
return result[0]
# Chybí záznam -> přidat do tabulky
cursor.execute('INSERT INTO edition(score, name, year) VALUES(?, ?, ?)',
(score_id, edition.name, p.score().year))
db_connection.commit()
edition_id = cursor.lastrowid
for editor in edition.authors:
# Vložit autory do 'edition_author' + commit
cursor.execute('SELECT id FROM person WHERE name=?', (editor.name, ))
result = cursor.fetchone()
cursor.execute(
'INSERT INTO edition_author(edition, editor) VALUES(?, ?)', (
edition_id,
result[0],
)) # Junction
db_connection.commit()
return edition_id