def autocomplete2():
query = request.args.get('q')
results = {'suggestions': []}
# Libri
books = db.query_db(
'SELECT DISTINCT records.title FROM records WHERE title LIKE "%{}%" LIMIT 4'
.format(query))
for b in books:
results['suggestions'].append({
'value': b['title'],
'data': {
'category': 'Libri'
}
})
# Autori
authors = db.query_db(
'SELECT DISTINCT records.creator FROM records WHERE creator LIKE "%{}%" LIMIT 4'
.format(query))
for a in authors:
results['suggestions'].append({
'value': a['creator'],
'data': {
'category': 'Autori'
}
})
# Soggetti
added = []
subjects = db.query_db(
'SELECT DISTINCT records.subject FROM records WHERE subject LIKE "%{}%" LIMIT 4'
.format(query))
for s in subjects:
d = s['subject'].split(' - ')
for e in d:
if query.lower() in e.lower() and e not in added:
added.append(e)
results['suggestions'].append({
'value': e,
'data': {
'category': 'Soggetti'
}
})
# Argomenti
args = db.query_db(
'SELECT DISTINCT title FROM entities WHERE title LIKE "%{}%" LIMIT 4'.
format(query))
for a in args:
if a['title'] not in added:
results['suggestions'].append({
'value': a['title'],
'data': {
'category': 'Argomenti'
}
})
return jsonify(results)
def autocomplete():
query = request.args.get('q')
results = {'suggestions': []}
# Luoghi di pubblicazione
places = db.query_db(
'SELECT DISTINCT places.name FROM places WHERE name LIKE "%{}%" LIMIT 4'
.format(query))
for p in places:
results['suggestions'].append({
'value': p['name'],
'data': {
'category': 'Luoghi di pubblicazione'
}
})
# Autori
authors = db.query_db(
'SELECT DISTINCT records.creator FROM records WHERE creator LIKE "%{}%" LIMIT 4'
.format(query))
for a in authors:
results['suggestions'].append({
'value': a['creator'],
'data': {
'category': 'Autori'
}
})
# Soggetti
added = []
subjects = db.query_db(
'SELECT DISTINCT records.subject FROM records WHERE subject LIKE "%{}%" LIMIT 4'
.format(query))
for s in subjects:
d = s['subject'].split(' - ')
for e in d:
if query.lower() in e.lower() and e not in added:
added.append(e)
results['suggestions'].append({
'value': e,
'data': {
'category': 'Soggetti'
}
})
# Opere
books = db.query_db(
'SELECT DISTINCT records.title FROM records WHERE title LIKE "%{}%" LIMIT 4'
.format(query))
for b in books:
results['suggestions'].append({
'value': b['title'],
'data': {
'category': 'Libri'
}
})
return jsonify(results)
def get_geo_entities_for_record():
record_id = request.args.get('record_id')
geo_entities = db.query_db(
'SELECT en.* FROM entity_for_record e, entities en\
WHERE e.entity_id = en.entity_id AND en.coords != "" AND e.record_id = {}'
.format(record_id))
return jsonify(geo_entities)
def get_full_record():
record_id = request.args.get('record_id')
record = db.query_db(
'SELECT r.*, p.name as luogo_pubblicazione, p.coords as published_in_coords, e.*, b.name as biblio_name, b.coords as biblio_coords, b.info as biblio_info '
'FROM records r, expanded_records e, places p, biblios b '
'WHERE e.id = r.id and r.published_in = p.id and b.id = r.biblio and r.id = {}'
.format(record_id))
result = {
'id': record[0]['id'],
'title': record[0]['title'],
'subject': record[0]['subject'],
'creator': record[0]['creator'],
'contributor': record[0]['contributor'],
'date': record[0]['date'],
'description': record[0]['description'],
'language': record[0]['language'],
'publisher': record[0]['publisher'],
'type': record[0]['type'],
'format': record[0]['format'],
'relation': record[0]['relation'],
'published_in': record[0]['luogo_pubblicazione'],
'published_in_coords': record[0]['published_in_coords'],
'link': record[0]['link'],
'viaf_id': record[0]['viaf_id'],
'author_other_works': record[0]['author_other_works'],
'author_wiki_page': record[0]['author_wiki_page'],
'author_wiki_info': record[0]['author_wiki_info'],
'biblio_name': record[0]['biblio_name'],
'biblio_coords': record[0]['biblio_coords'],
'biblio_info': record[0]['biblio_info'],
'entities': []
}
entities = db.query_db(
'SELECT en.* FROM entity_for_record e, entities en '
'WHERE e.entity_id = en.entity_id and e.record_id = {}'.format(
record_id))
for e in entities:
result['entities'].append({
'id': e['entity_id'],
'title': e['title'],
'abstract': e['abstract'],
'image_url': e['image_url'],
'uri': e['uri'],
'coords': e['coords']
})
return jsonify(result)
def get_expanded_record():
id = request.args.get('id')
records = db.query_db(
"SELECT DISTINCT e.id AS record_id, e.viaf_id, e.author_other_works, e.author_wiki_page, e.author_wiki_info,\
en.entity_id, en.title, en.abstract, en.coords, en.image_url, en.uri\
FROM expanded_records as e, entities as en, entity_for_record as ent\
WHERE e.id = ent.record_id and en.entity_id = ent.entity_id and e.id = {}"
.format(id))
# record senza entità
if len(records) == 0:
records = db.query_db(
"SELECT e.id AS record_id, e.viaf_id, e.author_other_works, e.author_wiki_page, e.author_wiki_info\
FROM expanded_records as e WHERE e.id = {}".
format(id))
data = {
'id': records[0]['record_id'],
'viaf_id': records[0]['viaf_id'],
'author_wiki_info': records[0]['author_wiki_info'],
'author_wiki_page': records[0]['author_wiki_page'],
'author_other_works': records[0]['author_other_works'],
'entities': ''
}
return jsonify(data)
else:
data = {
'id': records[0]['record_id'],
'viaf_id': records[0]['viaf_id'],
'author_wiki_info': records[0]['author_wiki_info'],
'author_wiki_page': records[0]['author_wiki_page'],
'author_other_works': records[0]['author_other_works'],
'entities': []
}
for r in records:
data['entities'].append({
'entity_id': r['entity_id'],
'title': r['title'],
'abstract': r['abstract'],
'image': r['image_url'],
'coords': r['coords'],
'uri': r['uri']
})
return jsonify(data)
def related():
author = request.args.get('author')
publisher = request.args.get('publisher')
biblio = request.args.get('biblio')
pub = request.args.get('pub')
# date = request.args.get('date')
dates = request.args.get('dates')
arg_id = request.args.get('arg_id')
query = 'SELECT DISTINCT r.id, b.name as biblio_name, b.coords as biblio_coords, b.id as biblio_id, b.info as biblio_info, r.title, r.creator, r.date, r.contributor, r.publisher, p.name as place_name, p.coords ' \
'FROM records r, places p, biblios b ' \
'WHERE r.published_in = p.id AND b.id = r.biblio'
# Autore
if author is not None and author != '':
query += " AND (r.creator LIKE '{q}%' OR r.contributor LIKE '{q}%')".format(
q=author)
# Editore
if publisher is not None and publisher != '':
query += " AND r.publisher LIKE '%{q}%'".format(q=publisher)
# Biblioteca
if biblio is not None and biblio != '':
query += " AND biblio_name LIKE '%{q}%'".format(q=biblio)
# Luogo di pubblicazione
if pub is not None and pub != '':
query += " AND place_name LIKE '%{q}%'".format(q=pub)
# Data
# if date is not None and date != '':
# query += " AND r.date='{q}'".format(q=date)
# Data (da, a)
if dates is not None and len(dates.split(',')) == 2:
query += " AND r.date>={q1} AND r.date<={q2}".format(
q1=dates.split(',')[0], q2=dates.split(',')[1])
# Argomento
if arg_id is not None and arg_id != '':
query += " AND r.id IN (SELECT DISTINCT en.record_id FROM entity_for_record en WHERE en.entity_id='{q}')".format(
q=arg_id)
query += ' ORDER BY r.title'
res = db.query_db(query)
return jsonify(res)
def get_records():
records = db.query_db(
'SELECT r.*, p.name AS place FROM records r, places p WHERE r.published_in = p.id'
)
return jsonify(records)
def search2():
query = request.args.get('q')
response = []
# search query in records
records = db.query_db(
'SELECT DISTINCT r.id AS record_id, p.name as place_name, r.title, r.creator, r.description, r.subject, r.contributor, r.publisher, r.date, b.id AS biblio_id '
'FROM records r, biblios b, places p, entities e, entity_for_record en '
'WHERE r.biblio = b.id AND r.published_in = p.id AND en.record_id = r.id AND en.entity_id = e.entity_id '
'AND (r.title LIKE "%{q}%" OR r.subject LIKE "%{q}%" OR r.description LIKE "%{q}%" OR r.creator LIKE "%{q}%" OR r.contributor LIKE "%{q}%" OR r.publisher LIKE "%{q}%" OR e.title LIKE "%{q}%")'
.format(q=query))
biblios = []
for r in records:
if r['biblio_id'] != '' and r['biblio_id'] not in biblios:
biblios.append(r['biblio_id'])
for biblio in biblios:
query_biblio = db.query_db(
'SELECT name, coords, info from biblios WHERE id="{}"'.format(
biblio))
p_records = [r for r in records if r['biblio_id'] == biblio]
e_records = []
for r in p_records:
e_records.append({
'record_id': r['record_id'],
'title': r['title'],
'date': r['date'],
'publisher': r['publisher'],
'contributor': r['contributor'],
'description': r['description'],
'subject': r['subject'],
'creator': r['creator'],
'place_name': r['place_name']
})
if len(query_biblio) > 0 and len(e_records) > 0:
e = [
r for r in response
if 'biblio_id' in r and r['biblio_id'] == biblio
]
if len(e) > 0:
# if biblio exists, append and remove duplicates
e[0]['records'] = [
dict(t) for t in
{tuple(d.items())
for d in (e[0]['records'] + e_records)}
]
else:
# or else just append
response.append({
'type': 'biblio',
'biblio_id': biblio,
'biblio_coords': query_biblio[0]['coords'],
'biblio_name': query_biblio[0]['name'],
'biblio_info': query_biblio[0]['info'],
'records': e_records
})
return jsonify(response)
def search():
query = request.args.get('q')
exp = request.args.get('expansions') == 'true'
response = []
# search query in entities
if exp:
entities = db.query_db(
'SELECT * FROM entities WHERE title LIKE "{q} %" OR title LIKE "% {q}" OR title LIKE "% {q} %" OR title LIKE "{q}"'
.format(q=query))
for e in entities:
if e['coords'] != '':
response.append({
'type': 'entity',
'entity_id': e['entity_id'],
'coords': e['coords'],
'title': e['title'],
'abstract': e['abstract'],
'image_url': e['image_url'],
'uri': e['uri']
})
# search query in places
places = db.query_db(
'SELECT * from places WHERE name LIKE "%{}%"'.format(query))
for p in places:
records = db.query_db(
'SELECT r.id AS record_id, r.title FROM records r, places p WHERE r.published_in = p.id AND p.id={}'
.format(p['id']))
if len(records) > 0:
response.append({
'type': 'place',
'place_id': p['id'],
'coords': p['coords'],
'name': p['name'],
'records': records
})
# search query in records
records = db.query_db(
'SELECT id AS record_id, title, published_in from records WHERE title LIKE "%{q}%" OR subject LIKE "%{q}%" OR description LIKE "%{q}%" OR creator LIKE "%{q}%" OR contributor LIKE "%{q}%" OR publisher LIKE "%{q}%"'
.format(q=query))
records_places = []
for r in records:
if r['published_in'] != '' and r['published_in'] not in records_places:
records_places.append(r['published_in'])
for place in records_places:
query_place = db.query_db(
'SELECT places.name, places.coords from places WHERE places.id={}'.
format(place))
p_records = [r for r in records if r['published_in'] == place]
e_records = []
for r in p_records:
e_records.append({
'record_id': r['record_id'],
'title': r['title']
})
if len(query_place) > 0 and len(e_records) > 0:
e = [
r for r in response
if 'place_id' in r and r['place_id'] == place
]
if len(e) > 0:
# if place exists, append and remove duplicates
e[0]['records'] = [
dict(t) for t in
{tuple(d.items())
for d in (e[0]['records'] + e_records)}
]
else:
# or else just append
response.append({
'type': 'place',
'place_id': place,
'coords': query_place[0]['coords'],
'name': query_place[0]['name'],
'records': e_records
})
return jsonify(response)