def delete_titles(self):
for title in self.title:
q = '''DELETE FROM work_title WHERE work_id = $work_id
AND title = $title'''
db.query(q, dict(work_id=self.UUID, title=title))
# now we delete the title if it's not linked to other work
try:
db.delete('title', dict(title=title), where="title=$title")
except BaseException:
pass
def delete_uris(self):
from .identifier import Identifier
for i in self.URI:
uri = i.get('URI') or i.get('uri')
scheme, value = Identifier.split_uri(uri)
q = '''DELETE FROM work_uri WHERE work_id = $work_id
AND uri_scheme = $scheme AND uri_value = $value'''
db.query(q, dict(work_id=self.UUID, scheme=scheme, value=value))
# now we delete the URI if it's not linked to other work
q = '''DELETE FROM uri WHERE
uri_scheme = $scheme AND uri_value = $value'''
try:
db.query(q, dict(scheme=scheme, value=value))
except BaseException:
pass
def report():
"""Generates a statistical report of database coverage compared to
available data
"""
tracks_with_songs = Track.query\
.filter(Track.song_id is not None).count()
songs_with_tracks = db.query(Track.song_id)\
.distinct(Track.song_id).filter(Track.song_id is not None).count()
concerts = Concert.query.count()
trackless = Concert.trackless_concerts(query=True).count()
return {
"artists": Artist.query.count(),
"concerts": concerts,
"trackless_concerts": trackless,
"complete_concerts": concerts - trackless,
"songs": {
"total": Song.query.count(),
"with_tracks": songs_with_tracks
},
"tracks": {
"total": Track.query.count(),
"with_songs": tracks_with_songs
}
}
def aggregate_by_measure_country(clause, params):
try:
q = '''SELECT
measure_uri,
namespace,
source,
type,
version,
country_uri,
country_code,
country_name,
continent_code,
SUM(value) as value
FROM event
INNER JOIN measure USING(measure_uri)
LEFT JOIN country USING(country_uri)
WHERE 1=1 ''' + clause + '''
GROUP BY measure_uri, namespace, source, type, version,
country_uri, country_code, country_name, continent_code
ORDER BY measure_uri, country_uri;'''
result = db.query(q, params)
return result
except (Exception, psycopg2.DatabaseError) as error:
logger.error(error)
raise Error(FATAL)
def get_deduped_tracks(self):
"""Placeholder for resolving Tracks against Songs"""
# tracks = {}
tracknames = db.query(func.lower(Track.name)) \
.filter(self.id == Track.artist_id).all()
for trackname in tracknames:
pass
def get_description(self):
options = dict(uri=self.measure_uri)
q = '''SELECT locale_code, locale_name, description
FROM measure_description INNER JOIN locale USING(locale_code)
WHERE measure_uri = $uri
ORDER BY locale_code;'''
return db.query(q, options)
def save_if_not_exists(self):
try:
option = dict(title=self.title)
q = '''INSERT INTO title VALUES ($title) ON CONFLICT DO NOTHING'''
return db.query(q, option)
except (Exception, psycopg2.DatabaseError) as error:
logger.error(error)
raise Error(FATAL)
def get_from_id(account_id):
params = {'account_id': account_id}
q = '''SELECT * FROM account WHERE account_id = $account_id;'''
try:
return db.query(q, params)
except (Exception, psycopg2.DatabaseError) as error:
logger.error(error)
raise Error(FATAL)
def get_all(clause, params):
try:
q = '''SELECT * FROM event WHERE 1=1 ''' + clause + '''
ORDER BY timestamp;'''
return db.query(q, params)
except (Exception, psycopg2.DatabaseError) as error:
logger.error(error)
raise Error(FATAL)
def save(self):
try:
q = '''INSERT INTO event (event_id, work_uri, measure_uri,
timestamp, value, event_uri, country_uri, uploader_uri)
VALUES ($event_id, $work_uri, $measure_uri, $timestamp,
$value, $event_uri, $country_uri, $uploader_uri)
ON CONFLICT DO NOTHING;'''
vals = dict(event_id=self.event_id,
work_uri=self.work_uri,
measure_uri=self.measure_uri,
timestamp=self.timestamp,
value=self.value,
event_uri=self.event_uri,
country_uri=self.country_uri,
uploader_uri=self.uploader_uri)
db.query(q, vals)
except (Exception, psycopg2.DatabaseError) as error:
logger.debug(error)
raise Error(FATAL)
def aggregate_by_year_measure(clause, params):
try:
q = '''SELECT
to_char(timestamp, 'YYYY') as year,
measure_uri,
namespace,
source,
type,
version,
SUM(value) as value
FROM event
INNER JOIN measure USING(measure_uri)
WHERE 1=1 ''' + clause + '''
GROUP BY year, measure_uri, namespace, source, type, version
ORDER BY year, measure_uri;'''
result = db.query(q, params)
return result
except (Exception, psycopg2.DatabaseError) as error:
logger.error(error)
raise Error(FATAL)
def save(self):
from .title import Title
from .identifier import Identifier
try:
with db.transaction():
q = '''INSERT INTO work (work_id, work_type)
VALUES ($work_id, $work_type) ON CONFLICT DO NOTHING'''
db.query(q, dict(work_id=self.UUID, work_type=self.type))
if not self.exists():
logger.error('Could not save record.')
raise Error(FATAL)
for title in self.title:
t = Title(title)
t.save_if_not_exists()
q = '''INSERT INTO work_title (work_id, title)
VALUES ($work_id, $title) ON CONFLICT DO NOTHING'''
db.query(q, dict(work_id=self.UUID, title=title))
for i in self.URI:
uri = i.get('URI') or i.get('uri')
is_canonical = i['canonical']
scheme, value = Identifier.split_uri(uri)
Identifier.insert_if_not_exist(scheme, value)
q = '''INSERT INTO work_uri (work_id, uri_scheme, uri_value,
canonical) VALUES
($work_id, $uri_scheme, $uri_value, $canonical)
ON CONFLICT DO NOTHING'''
db.query(q, dict(work_id=self.UUID, uri_scheme=scheme,
uri_value=value, canonical=is_canonical))
try:
for c in self.child:
db.insert('work_relation', parent_work_id=self.UUID,
child_work_id=c)
except AttributeError:
pass
try:
for p in self.parent:
db.insert('work_relation', parent_work_id=p,
child_work_id=self.UUID)
except AttributeError:
pass
except (Exception, psycopg2.DatabaseError) as error:
logger.debug(error)
raise Error(FATAL)
def delete(self):
q = '''DELETE FROM work WHERE work_id = $work_id'''
db.query(q, dict(work_id=self.UUID))
def do_query(query, params):
try:
return db.query(query, params)
except (Exception, psycopg2.DatabaseError) as error:
logger.error(error)
raise Error(FATAL)