def init_app (app):
""" Init the Flask app. """
app.config.val = None
app.config.set_cover_rg_id = None
with app.config.dba.engine.begin () as conn:
try:
res = execute (conn, """
SELECT bk_id
FROM books
WHERE book = :book
""", { 'book' : app.config['BOOK'] })
bk_id = res.fetchone ()[0]
res = execute (conn, """
SELECT rg_id
FROM ranges
WHERE bk_id = :bk_id AND range = 'All'
""", { 'bk_id' : bk_id })
rg_id = res.fetchone ()[0]
app.config.set_cover_rg_id = rg_id
except:
pass # FIXME
def __init__(self, conn, passage_or_id):
""" Initialize from passage or passage id. """
self.conn = conn
self.pass_id, self.start, self.end, self.bk_id, self.chapter = 0, 0, 0, 0, 0
start, end = self.fix(str(passage_or_id))
if int(start) > 10000000:
res = execute(
conn, """
SELECT pass_id, begadr, endadr, adr2bk_id (begadr), adr2chapter (begadr)
FROM passages
WHERE begadr = :begadr AND endadr = :endadr
""", dict(parameters, begadr=start, endadr=end))
else:
res = execute(
conn, """
SELECT pass_id, begadr, endadr, adr2bk_id (begadr), adr2chapter (begadr)
FROM passages
WHERE pass_id = :pass_id
""", dict(parameters, pass_id=start))
row = res.fetchone()
if row is not None:
self.pass_id, self.start, self.end, self.bk_id, self.chapter = row
def import_genealogical_fdw (dbsrc, dbdest, parameters):
"""Import genealogical tables from mysql.
Import the (28 * 3) mysql tables to 3 tables in the postgres database.
"""
if not config.get ('MYSQL_VG_DB'):
return
dbsrc_meta = sqlalchemy.schema.MetaData (bind = dbsrc.engine)
dbsrc_meta.reflect ()
with dbdest.engine.begin () as dest:
if config.get ('MYSQL_LOCSTEM_TABLES'):
log (logging.INFO, " Importing mysql locstem tables ...")
concat_tables_fdw (dest, dbsrc_meta, 'original_locstemed', 'var_fdw', config['MYSQL_LOCSTEM_TABLES'])
with dbdest.engine.begin () as dest:
if config.get ('MYSQL_RDG_TABLES'):
log (logging.INFO, " Importing mysql rdg tables ...")
concat_tables_fdw (dest, dbsrc_meta, 'original_rdg', 'var_fdw', config['MYSQL_RDG_TABLES'])
with dbdest.engine.begin () as dest:
if config.get ('MYSQL_VAR_TABLES'):
log (logging.INFO, " Importing mysql var tables ...")
concat_tables_fdw (dest, dbsrc_meta, 'original_var', 'var_fdw', config['MYSQL_VAR_TABLES'])
with dbdest.engine.begin () as dest:
if config.get ('MYSQL_MEMO_TABLE'):
log (logging.INFO, " Importing mysql memo table ...")
copy_table_fdw (dest, 'original_memo', 'var_fdw', config['MYSQL_MEMO_TABLE'])
execute (dest, """
ALTER TABLE original_memo RENAME COLUMN anfadr TO begadr;
""", parameters)
def import_att_fdw (dbsrc, dbdest, parameters):
"""Import att and lac tables from mysql.
Import the (28 * 2) mysql tables to 2 tables in the postgres database.
"""
log (logging.INFO, " Importing mysql att tables ...")
dbsrc_meta = sqlalchemy.schema.MetaData (bind = dbsrc.engine)
dbsrc_meta.reflect ()
with dbdest.engine.begin () as dest:
concat_tables_fdw (dest, dbsrc_meta, 'original_att', 'app_fdw', config['MYSQL_ATT_TABLES'])
with dbdest.engine.begin () as dest:
if config.get ('MYSQL_LAC_TABLES'):
log (logging.INFO, " Importing mysql lac tables ...")
concat_tables_fdw (dest, dbsrc_meta, 'original_lac', 'app_fdw', config['MYSQL_LAC_TABLES'])
else:
# no lacuna tables provided (eg. John)
execute (dest, """
DROP TABLE IF EXISTS original_lac;
CREATE TABLE original_lac (LIKE original_att);
""", parameters)
with dbdest.engine.begin () as dest:
execute (dest, """
ALTER TABLE original_att RENAME COLUMN anfadr TO begadr;
ALTER TABLE original_lac RENAME COLUMN anfadr TO begadr;
""", parameters)
def import_att_fdw(dbsrc, dbdest, parameters):
"""Import att and lac tables from mysql.
Import the (28 * 2) mysql tables to 2 tables in the postgres database.
"""
log(logging.INFO, " Importing mysql att tables ...")
dbsrc_meta = sqlalchemy.schema.MetaData(bind=dbsrc.engine)
dbsrc_meta.reflect()
with dbdest.engine.begin() as dest:
concat_tables_fdw(dest, dbsrc_meta, 'original_att', 'app_fdw',
config['MYSQL_ATT_TABLES'])
with dbdest.engine.begin() as dest:
if config.get('MYSQL_LAC_TABLES'):
log(logging.INFO, " Importing mysql lac tables ...")
concat_tables_fdw(dest, dbsrc_meta, 'original_lac', 'app_fdw',
config['MYSQL_LAC_TABLES'])
else:
# no lacuna tables provided (eg. John)
execute(
dest, """
DROP TABLE IF EXISTS original_lac;
CREATE TABLE original_lac (LIKE original_att);
""", parameters)
with dbdest.engine.begin() as dest:
execute(
dest, """
ALTER TABLE original_att RENAME COLUMN anfadr TO begadr;
ALTER TABLE original_lac RENAME COLUMN anfadr TO begadr;
""", parameters)
def init_app(app):
""" Initialize the flask app. """
app.config.bk_id = None
app.config.rg_id_all = None
with app.config.dba.engine.begin() as conn:
try:
res = execute(
conn, """
SELECT bk_id
FROM books
WHERE book = :book
""", {'book': app.config['BOOK']})
app.config.bk_id = res.fetchone()[0]
res = execute(
conn, """
SELECT rg_id
FROM ranges
WHERE bk_id = :bk_id AND range = 'All'
""", {'bk_id': app.config.bk_id})
rg_id = res.fetchone()[0]
app.config.rg_id_all = rg_id
except:
pass # FIXME
def notes_txt(passage_or_id):
"""Read or write the editor notes for a passage
"""
private_auth()
with current_app.config.dba.engine.begin() as conn:
passage = Passage(conn, passage_or_id)
if request.method == 'PUT':
edit_auth()
json = request.get_json()
res = execute(
conn, """
SET LOCAL ntg.user_id = :user_id;
""", dict(parameters, user_id=flask_login.current_user.id))
# check for edit conflicts
res = execute(
conn, """
SELECT * FROM notes
WHERE pass_id = :pass_id AND note != :old_note
""",
dict(parameters,
pass_id=passage.pass_id,
old_note=json['original']))
for row in res:
return make_json_response(
status=409,
message='Cannot save. The note was edited by another user.'
)
# save
res = execute(
conn, """
INSERT INTO notes AS n (pass_id, note)
VALUES (:pass_id, :note)
ON CONFLICT (pass_id) DO
UPDATE
SET note = :note
WHERE n.pass_id = EXCLUDED.pass_id
""", dict(parameters, pass_id=passage.pass_id, note=json['note']))
return make_json_response(message='Note saved.')
res = execute(
conn, """
SELECT note
FROM notes
WHERE pass_id = :pass_id
""", dict(parameters, pass_id=passage.pass_id))
if res.rowcount > 0:
return make_text_response(res.fetchone()[0])
return make_text_response('')
def copy_table_fdw (conn, dest_table, fdw, source_table):
"""Copy a table. """
execute (conn, """
DROP TABLE IF EXISTS {dest_table};
""", dict (parameters, dest_table = dest_table))
execute (conn, """
SELECT * INTO {dest_table} FROM {fdw}."{source_table}"
""", dict (parameters, fdw = fdw, dest_table = dest_table, source_table = source_table))
def manuscript_full_json (passage_or_id, hs_hsnr_id):
"""Endpoint. Serve information about a manuscript.
:param string hs_hsnr_id: The hs, hsnr or id of the manuscript.
"""
auth ()
hs_hsnr_id = request.args.get ('ms_id') or hs_hsnr_id
chapter = request.args.get ('range') or 'All'
with current_app.config.dba.engine.begin () as conn:
passage = Passage (conn, passage_or_id)
ms = Manuscript (conn, hs_hsnr_id)
rg_id = passage.range_id (chapter)
json = ms.to_json ()
json['length'] = ms.get_length (passage, chapter)
# Get the attestation(s) of the manuscript (may be uncertain eg. a/b/c)
res = execute (conn, """
SELECT labez, clique, labez_clique
FROM apparatus_view_agg
WHERE ms_id = :ms_id AND pass_id = :pass_id
""", dict (parameters, ms_id = ms.ms_id, pass_id = passage.pass_id))
json['labez'], json['clique'], json['labez_clique'] = res.fetchone ()
# Get the affinity of the manuscript to all manuscripts
res = execute (conn, """
SELECT avg (a.affinity) as aa,
percentile_cont(0.5) WITHIN GROUP (ORDER BY a.affinity) as ma
FROM affinity a
WHERE a.ms_id1 = :ms_id1 AND a.rg_id = :rg_id
""", dict (parameters, ms_id1 = ms.ms_id, rg_id = rg_id))
json['aa'], json['ma'] = res.fetchone ()
# Get the affinity of the manuscript to MT
#
# For a description of mt and mtp see the comment in
# ActsMsListValPh3.pl and
# http://intf.uni-muenster.de/cbgm/actsPh3/guide_en.html#Ancestors
json['mt'], json['mtp'] = 0.0, 0.0
res = execute (conn, """
SELECT a.affinity as mt, a.equal::float / c.length as mtp
FROM affinity a
JOIN ms_ranges c
ON (a.ms_id1, a.rg_id) = (c.ms_id, c.rg_id)
WHERE a.ms_id1 = :ms_id1 AND a.ms_id2 = 2 AND a.rg_id = :rg_id
""", dict (parameters, ms_id1 = ms.ms_id, rg_id = rg_id))
if res.rowcount > 0:
json['mt'], json['mtp'] = res.fetchone ()
return make_json_response (json)
def init(db):
""" Do some preparative calculations and cache the results. """
val = CBGM_Params()
with db.engine.begin() as conn:
# get max number of different cliques in any one passage
res = execute(
conn, """
SELECT MAX (c)
FROM (
SELECT COUNT ((labez, clique)) AS c
FROM locstem
WHERE labez !~ '^z'
GROUP BY pass_id
) AS foo
""", {})
n_cliques = res.fetchone()[0]
# see that the bitmask fits into uint64
# one bit is reserved for 'unknown' derivation
assert n_cliques < 64
# load all attestations into one big numpy array
create_labez_matrix(db, {}, val)
# build a mask of all readings of all mss.
# every labez_clique gets an id (in the range 1..63)
# Matrix mss x passages containing the bitmask of all manuscripts readings
val.mask_matrix = np.zeros((val.n_mss, val.n_passages),
dtype=np.uint64)
res = execute(
conn, """
WITH rn AS (
{with}
)
SELECT msq.ms_id, msq.pass_id, rn1.rn
FROM ms_cliques AS msq
JOIN (select * from rn) as rn1
USING (pass_id, labez, clique)
""", {'with': WITH_SELECT})
mask_row = collections.namedtuple('Mask_Row', 'ms_id, pass_id, shift')
for r in res:
mask = mask_row._make(r)
val.mask_matrix[mask.ms_id - 1,
mask.pass_id - 1] = np.uint64(mask.shift)
return val
def copy_table_fdw(conn, dest_table, fdw, source_table):
"""Copy a table. """
execute(conn, """
DROP TABLE IF EXISTS {dest_table};
""", dict(parameters, dest_table=dest_table))
execute(
conn, """
SELECT * INTO {dest_table} FROM {fdw}."{source_table}"
""",
dict(parameters,
fdw=fdw,
dest_table=dest_table,
source_table=source_table))
def comparison_detail ():
"""Output comparison of 2 witnesses, chapter detail.
Outputs a detail of the differences between 2 manuscripts in one chapter.
"""
with current_app.config.dba.engine.begin () as conn:
ms1 = Manuscript (conn, request.args.get ('ms1') or 'A')
ms2 = Manuscript (conn, request.args.get ('ms2') or 'A')
range_ = request.args.get ('range') or 'All'
res = execute (conn, """
SELECT p.pass_id, p.begadr, p.endadr, v1.labez_clique, v1.lesart,
v2.labez_clique, v2.lesart,
is_p_older (p.pass_id, v1.labez, v1.clique, v2.labez, v2.clique) AS older,
is_p_older (p.pass_id, v2.labez, v2.clique, v1.labez, v1.clique) AS newer,
is_p_unclear (p.pass_id, v1.labez, v1.clique) OR
is_p_unclear (p.pass_id, v2.labez, v2.clique) AS unclear
FROM (SELECT * FROM ranges WHERE range = :range_) r
JOIN passages p ON (r.passage @> p.passage )
JOIN apparatus_cliques_view v1 USING (pass_id)
JOIN apparatus_cliques_view v2 USING (pass_id)
WHERE v1.ms_id = :ms1 AND v2.ms_id = :ms2
AND v1.labez != v2.labez AND v1.labez !~ '^z' AND v2.labez !~ '^z'
AND v1.cbgm AND v2.cbgm
ORDER BY p.pass_id
""", dict (parameters, ms1 = ms1.ms_id, ms2 = ms2.ms_id, range_ = range_))
return list (map (_ComparisonDetailRowCalcFields._make, res))
def comparison_detail ():
"""Output comparison of 2 witnesses, chapter detail.
Outputs a detail of the differences between 2 manuscripts in one chapter.
"""
with current_app.config.dba.engine.begin () as conn:
ms1 = Manuscript (conn, request.args.get ('ms1') or 'A')
ms2 = Manuscript (conn, request.args.get ('ms2') or 'A')
range_ = request.args.get ('range') or 'All'
res = execute (conn, """
SELECT p.pass_id, p.begadr, p.endadr, v1.labez_clique, v1.lesart,
v2.labez_clique, v2.lesart,
is_p_older (p.pass_id, v1.labez, v1.clique, v2.labez, v2.clique) AS older,
is_p_older (p.pass_id, v2.labez, v2.clique, v1.labez, v1.clique) AS newer,
is_p_unclear (p.pass_id, v1.labez, v1.clique) OR
is_p_unclear (p.pass_id, v2.labez, v2.clique) AS unclear
FROM (SELECT * FROM ranges WHERE range = :range_) r
JOIN passages p ON (r.passage @> p.passage )
JOIN apparatus_cliques_view v1 USING (pass_id)
JOIN apparatus_cliques_view v2 USING (pass_id)
WHERE v1.ms_id = :ms1 AND v2.ms_id = :ms2
AND v1.labez != v2.labez AND v1.labez !~ '^z' AND v2.labez !~ '^z'
AND v1.cbgm AND v2.cbgm
ORDER BY p.pass_id
""", dict (parameters, ms1 = ms1.ms_id, ms2 = ms2.ms_id, range_ = range_))
return list (map (_ComparisonDetailRowCalcFields._make, res))
def __init__ (self, conn, manuscript_id_or_hs_or_hsnr):
""" Initialize from manuscript id or hs or hsnr. """
self.conn = conn
self.ms_id = self.hs = self.hsnr = None
param = manuscript_id_or_hs_or_hsnr
if Manuscript.RE_HSNR.search (param):
where = 'hsnr = :param'
param = int (param)
elif Manuscript.RE_MSID.search (param):
where = 'ms_id = :param'
param = int (param[2:])
elif Manuscript.RE_HS.search (param):
where = 'hs = :param'
else:
return
res = execute (conn, """
SELECT ms_id, hs, hsnr
FROM manuscripts
WHERE {where}
""", dict (parameters, where = where, param = param))
self.ms_id, self.hs, self.hsnr = res.first ()
def __init__(self, conn, manuscript_id_or_hs_or_hsnr):
""" Initialize from manuscript id or hs or hsnr. """
self.conn = conn
self.ms_id = self.hs = self.hsnr = None
if Manuscript.RE_HSNR.search(manuscript_id_or_hs_or_hsnr):
where = 'hsnr'
param = int(manuscript_id_or_hs_or_hsnr)
elif Manuscript.RE_MSID.search(manuscript_id_or_hs_or_hsnr):
where = 'ms_id'
param = int(manuscript_id_or_hs_or_hsnr[2:])
elif Manuscript.RE_HS.search(manuscript_id_or_hs_or_hsnr):
where = 'hs'
param = manuscript_id_or_hs_or_hsnr
else:
return
res = execute(
conn, """
SELECT ms_id, hs, hsnr
FROM manuscripts
WHERE {where} = :param
""", dict(parameters, where=where, param=param))
row = res.fetchone()
if row is not None:
self.ms_id, self.hs, self.hsnr = row
def readings (self, prefix = None, suffix = None, delete = None):
# Get a list of all readings for this passage
prefix = prefix or []
suffix = suffix or []
delete = delete or []
res = execute (self.conn, """
SELECT labez
FROM readings
WHERE pass_id = :pass_id AND labez != 'zz'
ORDER BY labez
""", dict (parameters, pass_id = self.pass_id))
d = collections.OrderedDict ()
for p in prefix:
d[p] = p
for row in res:
d[row[0]] = row[0]
for s in suffix:
d[s] = s
for dd in delete:
if dd in d:
del d[dd]
for k in d.keys ():
d[k] = LABEZ_I18N.get (d[k], d[k])
Readings = collections.namedtuple ('Readings', 'labez labez_i18n')
return [ Readings._make (r)._asdict () for r in d.items () ]
def ranges_json (passage_or_id):
"""Endpoint. Serve a list of ranges.
Serves a list of the configured ranges that are contained inside a book in
the NT.
:param string passage_or_id: The passage id.
:param integer bk_id: The id of the book.
"""
auth ()
passage_or_id = request.args.get ('pass_id') or passage_or_id or '0'
with current_app.config.dba.engine.begin () as conn:
passage = Passage (conn, passage_or_id)
bk_id = request.args.get ('bk_id') or passage.bk_id
res = execute (conn, """
SELECT DISTINCT range, range, lower (ch.passage) as begadr, upper (ch.passage) as endadr
FROM ranges ch
WHERE bk_id = :bk_id
ORDER BY begadr, endadr DESC
""", dict (parameters, bk_id = bk_id))
Ranges = collections.namedtuple ('Ranges', 'range, value, begadr, endadr')
# ranges = list (map (Ranges._make, res))
ranges = [ Ranges._make (r)._asdict () for r in res ]
return make_json_response (ranges)
def leitzeile_json (passage_or_id):
"""Endpoint. Serve the leitzeile for the verse containing passage_or_id. """
auth ()
with current_app.config.dba.engine.begin () as conn:
passage = Passage (conn, passage_or_id)
verse_start = (passage.start // 1000) * 1000
verse_end = verse_start + 999
res = execute (conn, """
SELECT l.begadr, l.endadr, l.lemma, ARRAY_AGG (p.pass_id)
FROM nestle l
LEFT JOIN passages p ON (p.passage @> l.passage)
WHERE int4range (:start, :end + 1) @> l.passage
GROUP BY l.begadr, l.endadr, l.lemma
UNION -- get the insertions
SELECT p.begadr, p.endadr, '', ARRAY_AGG (p.pass_id)
FROM passages_view p
WHERE int4range (:start, :end + 1) @> p.passage AND (begadr % 2) = 1
GROUP BY p.begadr, p.endadr
ORDER BY begadr, endadr DESC
""", dict (parameters, start = verse_start, end = verse_end))
Leitzeile = collections.namedtuple (
'Leitzeile', 'begadr, endadr, lemma, pass_ids')
leitzeile = [ Leitzeile._make (r)._asdict () for r in res ]
return make_json_response (leitzeile)
def readings(self, prefix=None, suffix=None, delete=None):
# Get a list of all readings for this passage
prefix = prefix or []
suffix = suffix or []
delete = delete or []
res = execute(
self.conn, """
SELECT labez
FROM readings
WHERE pass_id = :pass_id AND labez != 'zz'
ORDER BY labez
""", dict(parameters, pass_id=self.pass_id))
d = collections.OrderedDict()
for p in prefix:
d[p] = p
for row in res:
d[row[0]] = row[0]
for s in suffix:
d[s] = s
for dd in delete:
if dd in d:
del d[dd]
for k in d.keys():
d[k] = LABEZ_I18N.get(d[k], d[k])
Readings = collections.namedtuple('Readings', 'labez labez_i18n')
return [Readings._make(r)._asdict() for r in d.items()]
def leitzeile_json(passage_or_id):
"""Endpoint. Serve the leitzeile for the verse containing passage_or_id. """
auth()
with current_app.config.dba.engine.begin() as conn:
passage = Passage(conn, passage_or_id)
verse_start = (passage.start // 1000) * 1000
verse_end = verse_start + 999
res = execute(
conn, """
SELECT l.begadr, l.endadr, l.lemma, ARRAY_AGG (p.pass_id)
FROM nestle l
LEFT JOIN passages p ON (p.passage @> l.passage)
WHERE int4range (:start, :end + 1) @> l.passage
GROUP BY l.begadr, l.endadr, l.lemma
UNION -- get the insertions
SELECT p.begadr, p.endadr, '', ARRAY_AGG (p.pass_id)
FROM passages_view p
WHERE int4range (:start, :end + 1) @> p.passage AND (begadr % 2) = 1
GROUP BY p.begadr, p.endadr
ORDER BY begadr, endadr DESC
""", dict(parameters, start=verse_start, end=verse_end))
Leitzeile = collections.namedtuple('Leitzeile',
'begadr, endadr, lemma, pass_ids')
leitzeile = [Leitzeile._make(r)._asdict() for r in res]
return make_json_response(leitzeile)
def notes_json(range_id):
"""Endpoint. Get a list of all editor notes."""
private_auth()
with current_app.config.dba.engine.begin() as conn:
res = execute(
conn, """
SELECT pass_id, begadr, endadr, note
FROM passages_view p
JOIN ranges rg
ON (rg.passage @> p.passage)
JOIN notes
USING (pass_id)
WHERE rg.rg_id = :range_id
ORDER BY pass_id
""", dict(parameters, range_id=range_id))
Notes = collections.namedtuple('Notes',
'pass_id, begadr, endadr, note')
notes = []
for r in res:
note = Notes._make(r)._asdict()
note['hr'] = Passage.static_to_hr(note['begadr'], note['endadr'])
notes.append(note)
return make_json_response(notes)
def init (db):
""" Do some preparative calculations and cache the results. """
val = CBGM_Params ()
with db.engine.begin () as conn:
# get max number of different cliques in any one passage
res = execute (conn, """
SELECT MAX (c)
FROM (
SELECT COUNT ((labez, clique)) AS c
FROM locstem
WHERE labez !~ '^z'
GROUP BY pass_id
) AS foo
""", {})
n_cliques = res.fetchone ()[0]
# see that the bitmask fits into uint64
# one bit is reserved for 'unknown' derivation
assert n_cliques < 64
# load all attestations into one big numpy array
create_labez_matrix (db, {}, val)
# build a mask of all readings of all mss.
# every labez_clique gets an id (in the range 1..63)
# Matrix mss x passages containing the bitmask of all manuscripts readings
val.mask_matrix = np.zeros ((val.n_mss, val.n_passages), dtype = np.uint64)
res = execute (conn, """
WITH rn AS (
{with}
)
SELECT msq.ms_id, msq.pass_id, rn1.rn
FROM ms_cliques AS msq
JOIN (select * from rn) as rn1
USING (pass_id, labez, clique)
""", { 'with' : WITH_SELECT })
mask_row = collections.namedtuple ('Mask_Row', 'ms_id, pass_id, shift')
for r in res:
mask = mask_row._make (r)
val.mask_matrix[mask.ms_id - 1, mask.pass_id - 1] = np.uint64 (mask.shift)
return val
def comparison_summary ():
"""Output comparison of 2 witnesses, chapter summary.
Outputs a summary of the differences between 2 manuscripts, one summary row
per chapters.
"""
with current_app.config.dba.engine.begin () as conn:
ms1 = Manuscript (conn, request.args.get ('ms1') or 'A')
ms2 = Manuscript (conn, request.args.get ('ms2') or 'A')
res = execute (conn, """
(WITH ranks AS (
SELECT ms_id1, ms_id2, rg_id, rank () OVER (PARTITION BY rg_id ORDER BY affinity DESC) AS rank, affinity
FROM affinity aff
WHERE ms_id1 = :ms_id1
AND {prefix}newer > {prefix}older
ORDER BY affinity DESC
)
SELECT a.rg_id, a.range, a.common, a.equal,
a.older, a.newer, a.unclear, a.affinity, r.rank, ms1_length, ms2_length
FROM {view} a
JOIN ranks r USING (rg_id, ms_id1, ms_id2)
WHERE a.ms_id1 = :ms_id1 AND a.ms_id2 = :ms_id2
)
UNION
(WITH ranks2 AS (
SELECT ms_id1, ms_id2, rg_id, rank () OVER (PARTITION BY rg_id ORDER BY affinity DESC) AS rank, affinity
FROM affinity aff
WHERE ms_id2 = :ms_id2
AND {prefix}newer < {prefix}older
ORDER BY affinity DESC
)
SELECT a.rg_id, a.range, a.common, a.equal,
a.older, a.newer, a.unclear, a.affinity, r.rank, ms1_length, ms2_length
FROM {view} a
JOIN ranks2 r USING (rg_id, ms_id1, ms_id2)
WHERE a.ms_id1 = :ms_id1 AND a.ms_id2 = :ms_id2
)
UNION
SELECT a.rg_id, a.range, a.common, a.equal,
a.older, a.newer, a.unclear, a.affinity, NULL, ms1_length, ms2_length
FROM {view} a
WHERE a.ms_id1 = :ms_id1 AND a.ms_id2 = :ms_id2 AND a.newer = a.older
ORDER BY rg_id
""", dict (parameters, ms_id1 = ms1.ms_id, ms_id2 = ms2.ms_id,
view = 'affinity_p_view', prefix = 'p_'))
return list (map (_ComparisonRowCalcFields._make, res))
def comparison_summary ():
"""Output comparison of 2 witnesses, chapter summary.
Outputs a summary of the differences between 2 manuscripts, one summary row
per chapters.
"""
with current_app.config.dba.engine.begin () as conn:
ms1 = Manuscript (conn, request.args.get ('ms1') or 'A')
ms2 = Manuscript (conn, request.args.get ('ms2') or 'A')
res = execute (conn, """
(WITH ranks AS (
SELECT ms_id1, ms_id2, rg_id, rank () OVER (PARTITION BY rg_id ORDER BY affinity DESC) AS rank, affinity
FROM affinity aff
WHERE ms_id1 = :ms_id1
AND {prefix}newer > {prefix}older
ORDER BY affinity DESC
)
SELECT a.rg_id, a.range, a.common, a.equal,
a.older, a.newer, a.unclear, a.affinity, r.rank, ms1_length, ms2_length
FROM {view} a
JOIN ranks r USING (rg_id, ms_id1, ms_id2)
WHERE a.ms_id1 = :ms_id1 AND a.ms_id2 = :ms_id2
)
UNION
(WITH ranks2 AS (
SELECT ms_id1, ms_id2, rg_id, rank () OVER (PARTITION BY rg_id ORDER BY affinity DESC) AS rank, affinity
FROM affinity aff
WHERE ms_id2 = :ms_id2
AND {prefix}newer < {prefix}older
ORDER BY affinity DESC
)
SELECT a.rg_id, a.range, a.common, a.equal,
a.older, a.newer, a.unclear, a.affinity, r.rank, ms1_length, ms2_length
FROM {view} a
JOIN ranks2 r USING (rg_id, ms_id1, ms_id2)
WHERE a.ms_id1 = :ms_id1 AND a.ms_id2 = :ms_id2
)
UNION
SELECT a.rg_id, a.range, a.common, a.equal,
a.older, a.newer, a.unclear, a.affinity, NULL, ms1_length, ms2_length
FROM {view} a
WHERE a.ms_id1 = :ms_id1 AND a.ms_id2 = :ms_id2 AND a.newer = a.older
ORDER BY rg_id
""", dict (parameters, ms_id1 = ms1.ms_id, ms_id2 = ms2.ms_id,
view = 'affinity_p_view', prefix = 'p_'))
return list (map (_ComparisonRowCalcFields._make, res))
def import_genealogical_fdw(dbsrc, dbdest, parameters):
"""Import genealogical tables from mysql.
Import the (28 * 3) mysql tables to 3 tables in the postgres database.
This function is relevant only for Acts, where we had to import genealogical
data from a previous implementation of the CBGM. It is not used for new
projects.
"""
if not config.get('MYSQL_VG_DB'):
return
dbsrc_meta = sqlalchemy.schema.MetaData(bind=dbsrc.engine)
dbsrc_meta.reflect()
with dbdest.engine.begin() as dest:
if config.get('MYSQL_LOCSTEM_TABLES'):
log(logging.INFO, " Importing mysql locstem tables ...")
concat_tables_fdw(dest, dbsrc_meta, 'original_locstemed',
'var_fdw', config['MYSQL_LOCSTEM_TABLES'])
with dbdest.engine.begin() as dest:
if config.get('MYSQL_RDG_TABLES'):
log(logging.INFO, " Importing mysql rdg tables ...")
concat_tables_fdw(dest, dbsrc_meta, 'original_rdg', 'var_fdw',
config['MYSQL_RDG_TABLES'])
with dbdest.engine.begin() as dest:
if config.get('MYSQL_VAR_TABLES'):
log(logging.INFO, " Importing mysql var tables ...")
concat_tables_fdw(dest, dbsrc_meta, 'original_var', 'var_fdw',
config['MYSQL_VAR_TABLES'])
with dbdest.engine.begin() as dest:
if config.get('MYSQL_MEMO_TABLE'):
log(logging.INFO, " Importing mysql memo table ...")
copy_table_fdw(dest, 'original_memo', 'var_fdw',
config['MYSQL_MEMO_TABLE'])
execute(
dest, """
ALTER TABLE original_memo RENAME COLUMN anfadr TO begadr;
""", parameters)
def build_explain_matrix(conn, val, ms_id):
"""Build the explain matrix.
A matrix of 1 x n_passages containing the bitmask of all those readings that
would explain the reading in the manuscript under scrutiny.
Bit 1 means: the reading stems from an unknown source.
Bit 2..64 are the bitmask of all cliques.
"""
explain_matrix = np.zeros(val.n_passages, dtype=np.uint64)
res = execute(
conn, """
WITH RECURSIVE
rn AS (
{with}
),
lsrn AS (
SELECT ls.pass_id, ls.labez, ls.clique,
-- set 1 as flag for unknown derivation
CASE WHEN ls.source_labez = '?' THEN rn1.rn | 1 ELSE rn1.rn END AS rn1,
rn2.rn AS rn2
FROM locstem ls
JOIN rn as rn1
USING (pass_id, labez, clique)
LEFT JOIN rn as rn2
ON (ls.pass_id, ls.source_labez, ls.source_clique) = (rn2.pass_id, rn2.labez, rn2.clique)
),
lsrec (pass_id, rn1, rn2) AS (
SELECT lsrn.pass_id, lsrn.rn1, lsrn.rn2
FROM ms_cliques AS msq
JOIN lsrn USING (pass_id, labez, clique)
WHERE ms_id = :ms_id
UNION
SELECT lsrn.pass_id, lsrn.rn1, lsrn.rn2
FROM lsrec
JOIN lsrn
ON (lsrn.pass_id = lsrec.pass_id AND (lsrn.rn1 & ~B'1'::integer) = lsrec.rn2)
)
SELECT pass_id, BIT_OR (rn1) AS rn
FROM lsrec
GROUP BY pass_id
ORDER BY pass_id;
""", {
'with': WITH_SELECT,
'ms_id': ms_id
})
explain_row = collections.namedtuple('Explain_Row', 'pass_id, mask')
for r in res:
mask = explain_row._make(r)
explain_matrix[mask.pass_id - 1] = np.uint64(mask.mask)
return explain_matrix
def get_length (self, passage, range_ = '0'):
# Get the length of the manuscript, ie. the no. of existing passages
res = execute (self.conn, """
SELECT length
FROM ms_ranges_view
WHERE ms_id = :ms_id AND bk_id = :bk_id AND range = :range_
""", dict (parameters, ms_id = self.ms_id, bk_id = passage.bk_id, range_ = range_))
return res.fetchone ()[0]
def get_length(self, rg_id):
# Get the length of the manuscript, ie. the no. of existing passages
res = execute(
self.conn, """
SELECT length
FROM ms_ranges
WHERE ms_id = :ms_id AND rg_id = :rg_id
""", dict(parameters, ms_id=self.ms_id, rg_id=rg_id))
return res.fetchone()[0]
def apparatus_json(passage_or_id):
""" The contents of the apparatus table. """
auth()
with current_app.config.dba.engine.begin() as conn:
passage = Passage(conn, passage_or_id)
# list of labez => lesart
res = execute(
conn, """
SELECT labez, reading (labez, lesart)
FROM readings
WHERE pass_id = :pass_id
ORDER BY labez
""", dict(parameters, pass_id=passage.pass_id))
Readings = collections.namedtuple('Readings', 'labez lesart')
readings = [Readings._make(r)._asdict() for r in res]
# list of labez_clique => manuscripts
res = execute(
conn, """
SELECT labez, clique, labez_clique, labezsuf, reading (labez, lesart), ms_id, hs, hsnr, certainty
FROM apparatus_view_agg
WHERE pass_id = :pass_id
ORDER BY hsnr, labez, clique
""", dict(parameters, pass_id=passage.pass_id))
Manuscripts = collections.namedtuple(
'Manuscripts',
'labez clique labez_clique labezsuf lesart ms_id hs hsnr certainty'
)
manuscripts = [Manuscripts._make(r)._asdict() for r in res]
return make_json_response({
'readings': readings,
'manuscripts': manuscripts,
})
return 'Error'
def range_id (self, range_ = None):
""" Return the id of the range containing this passage. """
range_ = range_ or self.chapter
res = execute (self.conn, """
SELECT rg_id
FROM ranges_view
WHERE bk_id = :bk_id AND range = :range_
""", dict (parameters, bk_id = self.bk_id, range_ = range_))
return res.fetchone ()[0]
def build_explain_matrix (conn, val, ms_id):
"""Build the explain matrix.
A matrix of 1 x n_passages containing the bitmask of all those readings that
would explain the reading in the manuscript under scrutiny.
Bit 1 means: the reading stems from an unknown source.
Bit 2..64 are the bitmask of all cliques.
"""
explain_matrix = np.zeros (val.n_passages, dtype = np.uint64)
res = execute (conn, """
WITH RECURSIVE
rn AS (
{with}
),
lsrn AS (
SELECT ls.pass_id, ls.labez, ls.clique,
-- set 1 as flag for unknown derivation
CASE WHEN (ls.source_labez IS NULL) AND NOT ls.original THEN rn1.rn | 1 ELSE rn1.rn END AS rn1,
rn2.rn AS rn2
FROM locstem ls
JOIN rn as rn1
USING (pass_id, labez, clique)
LEFT JOIN rn as rn2
ON (ls.pass_id, ls.source_labez, ls.source_clique) = (rn2.pass_id, rn2.labez, rn2.clique)
),
lsrec (pass_id, rn1, rn2) AS (
SELECT lsrn.pass_id, lsrn.rn1, lsrn.rn2
FROM ms_cliques AS msq
JOIN lsrn USING (pass_id, labez, clique)
WHERE ms_id = :ms_id
UNION
SELECT lsrn.pass_id, lsrn.rn1, lsrn.rn2
FROM lsrec
JOIN lsrn
ON (lsrn.pass_id = lsrec.pass_id AND (lsrn.rn1 & ~B'1'::integer) = lsrec.rn2)
)
SELECT pass_id, BIT_OR (rn1) AS rn
FROM lsrec
GROUP BY pass_id
ORDER BY pass_id;
""", { 'with' : WITH_SELECT, 'ms_id' : ms_id })
explain_row = collections.namedtuple ('Explain_Row', 'pass_id, mask')
for r in res:
mask = explain_row._make (r)
explain_matrix[mask.pass_id - 1] = np.uint64 (mask.mask)
return explain_matrix
def apparatus_json (passage_or_id):
""" The contents of the apparatus table. """
auth ()
with current_app.config.dba.engine.begin () as conn:
passage = Passage (conn, passage_or_id)
# list of labez => lesart
res = execute (conn, """
SELECT labez, reading (labez, lesart)
FROM readings
WHERE pass_id = :pass_id
ORDER BY labez
""", dict (parameters, pass_id = passage.pass_id))
Readings = collections.namedtuple ('Readings', 'labez lesart')
readings = [ Readings._make (r)._asdict () for r in res ]
# list of labez_clique => manuscripts
res = execute (conn, """
SELECT labez, clique, labez_clique, labezsuf, reading (labez, lesart), ms_id, hs, hsnr, certainty
FROM apparatus_cliques_view
WHERE pass_id = :pass_id
ORDER BY hsnr, labez, clique
""", dict (parameters, pass_id = passage.pass_id))
Manuscripts = collections.namedtuple (
'Manuscripts',
'labez clique labez_clique labezsuf lesart ms_id hs hsnr certainty'
)
manuscripts = [ Manuscripts._make (r)._asdict () for r in res ]
return make_json_response ({
'readings' : readings,
'manuscripts' : manuscripts,
})
return 'Error'
def __init__ (self, conn, passage_or_id):
""" Initialize from passage or passage id. """
self.conn = conn
self.pass_id, self.start, self.end, self.bk_id, self.chapter = 0, 0, 0, 0, 0
start, end = self.fix (str (passage_or_id))
if int (start) > 10000000:
res = execute (conn, """
SELECT pass_id, begadr, endadr, adr2bk_id (begadr), adr2chapter (begadr)
FROM passages
WHERE begadr = :begadr AND endadr = :endadr
""", dict (parameters, begadr = start, endadr = end))
else:
res = execute (conn, """
SELECT pass_id, begadr, endadr, adr2bk_id (begadr), adr2chapter (begadr)
FROM passages
WHERE pass_id = :pass_id
""", dict (parameters, pass_id = start))
res = res.first ()
if res is not None:
self.pass_id, self.start, self.end, self.bk_id, self.chapter = res
def range_id(self, range_=None):
""" Return the id of the range containing this passage. """
range_ = range_ or str(self.chapter)
res = execute(
self.conn, """
SELECT rg_id
FROM ranges_view
WHERE bk_id = :bk_id AND range = :range_
""", dict(parameters, bk_id=self.bk_id, range_=range_))
row = res.fetchone()
return row[0] if row is not None else None
def notes_txt (passage_or_id):
"""Get the editor notes for a passage
"""
if not flask_login.current_user.has_role ('editor'):
raise PrivilegeError ('You don\'t have editor privilege.')
with current_app.config.dba.engine.begin () as conn:
passage = Passage (conn, passage_or_id)
if request.method == 'PUT':
res = execute (conn, """
SET LOCAL ntg.user_id = :user_id;
""", dict (parameters, user_id = flask_login.current_user.id))
res = execute (conn, """
INSERT INTO notes AS n (pass_id, note)
VALUES (:pass_id, :note)
ON CONFLICT (pass_id) DO
UPDATE
SET note = :note
WHERE n.pass_id = EXCLUDED.pass_id
""", dict (parameters,
pass_id = passage.pass_id,
note = request.get_json ()['remarks']))
return make_json_response (message = 'Notes saved.')
res = execute (conn, """
SELECT note
FROM notes
WHERE pass_id = :pass_id
""", dict (parameters, pass_id = passage.pass_id))
if res.rowcount > 0:
return make_text_response (res.fetchone ()[0])
return make_text_response ('')
def cliques (self, prefix = None, suffix = None, delete = None):
# Get a list of all cliques for this passage
prefix = prefix or []
suffix = suffix or []
delete = delete or []
res = execute (self.conn, """
SELECT labez, clique, labez_clique (labez, clique) AS labez_clique
FROM cliques
WHERE pass_id = :pass_id
ORDER BY labez, clique
""", dict (parameters, pass_id = self.pass_id))
Cliques = collections.namedtuple ('Cliques', 'labez clique labez_clique')
return [ Cliques._make (r)._asdict ()
for r in prefix + list (res.fetchall ()) + suffix if r not in delete ]
def get_ancestors(conn, rg_id, ms_id):
""" Get all ancestors of ms. """
mode = 'sim'
view = 'affinity_view' if mode == 'rec' else 'affinity_p_view'
res = execute(
conn, """
SELECT aff.ms_id2 as ms_id
FROM
{view} aff
WHERE aff.ms_id1 = :ms_id1 AND aff.rg_id = :rg_id
AND aff.common > 0 AND aff.older < aff.newer
ORDER BY affinity DESC, newer DESC, older DESC
""", dict(ms_id1=ms_id, rg_id=rg_id, view=view))
return frozenset([r[0] for r in res])
def attesting_csv (passage_or_id, labez):
""" Serve all relatives of all mss. attesting labez at passage. """
auth ()
with current_app.config.dba.engine.begin () as conn:
passage = Passage (conn, passage_or_id)
res = execute (conn, """
SELECT ms_id, hs, hsnr
FROM apparatus_view
WHERE pass_id = :pass_id AND labez = :labez
ORDER BY hsnr
""", dict (parameters, pass_id = passage.pass_id, labez = labez))
Attesting = collections.namedtuple ('Attesting', 'ms_id hs hsnr')
return csvify (Attesting._fields, list (map (Attesting._make, res)))
def get_ancestors (conn, rg_id, ms_id):
""" Get all ancestors of ms. """
mode = 'sim'
view = 'affinity_view' if mode == 'rec' else 'affinity_p_view'
res = execute (conn, """
SELECT aff.ms_id2 as ms_id
FROM
{view} aff
WHERE aff.ms_id1 = :ms_id1 AND aff.rg_id = :rg_id
AND aff.common > 0 AND aff.older < aff.newer
ORDER BY affinity DESC, newer DESC, older DESC
""", dict (ms_id1 = ms_id,
rg_id = rg_id,
view = view))
return frozenset ([r[0] for r in res])
def optimal_substemma_detail_csv():
"""Report details about one combination of ancestors.
"""
if current_app.config.val is None:
current_app.config.val = init(current_app.config.dba)
val = current_app.config.val
with current_app.config.dba.engine.begin() as conn:
# the manuscript to explain
ms = Manuscript(conn, request.args.get('ms'))
# get the selected set of ancestors
selected = [
Manuscript(conn, anc_id)
for anc_id in (request.args.get('selection') or '').split()
]
combinations = [Combination(selected, 0)]
explain_matrix = build_explain_matrix(conn, val, ms.ms_id)
_optimal_substemma(ms.ms_id,
explain_matrix,
combinations,
mode='detail')
res = execute(
conn, """
SELECT 'unknown' as type, p.pass_id, p.begadr, p.endadr, v.labez_clique, v.lesart
FROM passages p
JOIN apparatus_cliques_view v USING (pass_id)
WHERE v.ms_id = :ms_id AND pass_id IN :unknown_pass_ids
UNION
SELECT 'open' as type, p.pass_id, p.begadr, p.endadr, v.labez_clique, v.lesart
FROM passages p
JOIN apparatus_cliques_view v USING (pass_id)
WHERE v.ms_id = :ms_id AND pass_id IN :open_pass_ids
""",
dict(ms_id=ms.ms_id,
unknown_pass_ids=combinations[0].unknown_indices or (-1, ),
open_pass_ids=combinations[0].open_indices or (-1, )))
return csvify(
_OptimalSubstemmaDetailRowCalcFields._fields,
list(map(_OptimalSubstemmaDetailRowCalcFields._make, res)))
def attesting_csv(passage_or_id, labez):
""" Serve all relatives of all mss. attesting labez at passage. """
auth()
with current_app.config.dba.engine.begin() as conn:
passage = Passage(conn, passage_or_id)
res = execute(
conn, """
SELECT ms_id, hs, hsnr
FROM apparatus_view
WHERE pass_id = :pass_id AND labez = :labez
ORDER BY hsnr
""", dict(parameters, pass_id=passage.pass_id, labez=labez))
Attesting = collections.namedtuple('Attesting', 'ms_id hs hsnr')
return csvify(Attesting._fields, list(map(Attesting._make, res)))
def cliques(self, prefix=None, suffix=None, delete=None):
# Get a list of all cliques for this passage
prefix = prefix or []
suffix = suffix or []
delete = delete or []
res = execute(
self.conn, """
SELECT labez, clique, labez_clique (labez, clique) AS labez_clique
FROM cliques
WHERE pass_id = :pass_id
ORDER BY labez, clique
""", dict(parameters, pass_id=self.pass_id))
Cliques = collections.namedtuple('Cliques',
'labez clique labez_clique')
return [
Cliques._make(r)._asdict()
for r in prefix + list(res.fetchall()) + suffix if r not in delete
]
def get_excluded_ms_ids (conn, include):
"""Get the ms_ids of manuscripts to exclude.
Helps to implement the buttons "A", "MT", and "F" in the toolbar.
"""
exclude = set (EXCLUDE_REGEX_MAP.keys ()) - set (include)
if not exclude:
return tuple ([-1]) # a non-existing id to avoid an SQL syntax error
exclude = [ EXCLUDE_REGEX_MAP[x] for x in exclude]
# get ids of nodes to exclude
res = execute (conn, """
SELECT ms_id
FROM manuscripts
WHERE hs ~ '^({exclude})$'
ORDER BY ms_id
""", dict (parameters, exclude = '|'.join (exclude)))
return tuple ([ row[0] for row in res ] or [ -1 ])
def get_excluded_ms_ids(conn, include):
"""Get the ms_ids of manuscripts to exclude.
Helps to implement the buttons "A", "MT", and "F" in the toolbar.
"""
exclude = set(EXCLUDE_REGEX_MAP.keys()) - set(include)
if not exclude:
return tuple([-1]) # a non-existing id to avoid an SQL syntax error
exclude = [EXCLUDE_REGEX_MAP[x] for x in exclude]
# get ids of nodes to exclude
res = execute(
conn, """
SELECT ms_id
FROM manuscripts
WHERE hs ~ '^({exclude})$'
ORDER BY ms_id
""", dict(parameters, exclude='|'.join(exclude)))
return tuple([row[0] for row in res] or [-1])
def notes_json ():
"""Endpoint. Get a list of all editor notes."""
if not flask_login.current_user.has_role ('editor'):
raise PrivilegeError ('You don\'t have editor privilege.')
with current_app.config.dba.engine.begin () as conn:
res = execute (conn, """
SELECT pass_id, begadr, endadr, note
FROM passages_view
JOIN notes
USING (pass_id)
""", dict (parameters))
Notes = collections.namedtuple ('Notes', 'pass_id, begadr, endadr, note')
notes = []
for r in res:
note = Notes._make (r)._asdict ()
note['hr'] = Passage.static_to_hr (note['begadr'], note['endadr'])
notes.append (note)
return make_json_response (notes)
def attestation_json (passage_or_id):
auth ()
with current_app.config.dba.engine.begin () as conn:
passage = Passage (conn, passage_or_id)
res = execute (conn, """
SELECT ms_id, labez
FROM apparatus
WHERE pass_id = :pass_id
ORDER BY ms_id
""", dict (parameters, pass_id = passage.pass_id))
attestations = {}
for row in res:
ms_id, labez = row
attestations[str (ms_id)] = labez
return make_json_response ({
'attestations': attestations
})
def optimal_substemma_detail_csv ():
"""Report details about one combination of ancestors.
"""
if current_app.config.val is None:
current_app.config.val = init (current_app.config.dba)
val = current_app.config.val
with current_app.config.dba.engine.begin () as conn:
# the manuscript to explain
ms = Manuscript (conn, request.args.get ('ms'))
# get the selected set of ancestors
selected = [ Manuscript (conn, anc_id)
for anc_id in (request.args.get ('selection') or '').split () ]
combinations = [Combination (selected, 0)]
explain_matrix = build_explain_matrix (conn, val, ms.ms_id)
_optimal_substemma (ms.ms_id, explain_matrix, combinations, mode = 'detail')
res = execute (conn, """
SELECT 'unknown' as type, p.pass_id, p.begadr, p.endadr, v.labez_clique, v.lesart
FROM passages p
JOIN apparatus_cliques_view v USING (pass_id)
WHERE v.ms_id = :ms_id AND pass_id IN :unknown_pass_ids
UNION
SELECT 'open' as type, p.pass_id, p.begadr, p.endadr, v.labez_clique, v.lesart
FROM passages p
JOIN apparatus_cliques_view v USING (pass_id)
WHERE v.ms_id = :ms_id AND pass_id IN :open_pass_ids
""", dict (
ms_id = ms.ms_id,
unknown_pass_ids = combinations[0].unknown_indices,
open_pass_ids = combinations[0].open_indices
))
return csvify (_OptimalSubstemmaDetailRowCalcFields._fields,
list (map (_OptimalSubstemmaDetailRowCalcFields._make, res)))
def attestation_json(passage_or_id):
"""Answer with a list of the attestations of all manuscripts at one specified
passage."""
auth()
with current_app.config.dba.engine.begin() as conn:
passage = Passage(conn, passage_or_id)
res = execute(
conn, """
SELECT ms_id, labez
FROM apparatus
WHERE pass_id = :pass_id
ORDER BY ms_id
""", dict(parameters, pass_id=passage.pass_id))
attestations = {}
for row in res:
ms_id, labez = row
attestations[str(ms_id)] = labez
return make_json_response({'attestations': attestations})
def ranges_json():
"""Endpoint. Serve a list of ranges.
Serves a list of the configured ranges that are contained inside a book in
the NT.
"""
conf = current_app.config
with conf.dba.engine.begin() as conn:
res = execute(
conn, """
SELECT DISTINCT bk_id, book, rg_id, range, lower (rg.passage) as begadr, upper (rg.passage) as endadr
FROM ranges_view rg
WHERE bk_id = :bk_id
ORDER BY begadr, endadr DESC
""", dict(parameters, bk_id=conf.bk_id))
Ranges = collections.namedtuple(
'Ranges', 'bk_id, book, rg_id, range, begadr, endadr')
ranges = [Ranges._make(r)._asdict() for r in res]
return cache(make_json_response(ranges))
def build_A_text(dba, parameters):
"""Build the 'A' text
The editors' reconstruction of the archetype is recorded in the locstem
table. This functions generates a virtual manuscript 'A' from those choices.
The designation of a passage as 'Fehlvers' is an editorial decision that the
verse is not original, so we set 'zu'.
If the editors came to no final decision, no 'original' reading will be
found in locstem. In this case we set 'A' to 'zz' and there will be a gap
in the reconstructed text.
The Lesart of 'A' is always NULL, because it is a virtual manuscript.
"""
with dba.engine.begin() as conn:
execute(
conn, """
DELETE FROM ms_cliques WHERE ms_id = :ms_id;
DELETE FROM ms_cliques_tts WHERE ms_id = :ms_id;
DELETE FROM apparatus WHERE ms_id = :ms_id;
""", dict(parameters, ms_id=MS_ID_A))
# Fill with the original reading in locstem or 'zz' if none found
execute(
conn, """
INSERT INTO apparatus_cliques_view (ms_id, pass_id, labez, clique, cbgm, origin, lesart)
SELECT :ms_id, p.pass_id, COALESCE (l.labez, 'zz'), COALESCE (l.clique, '1'), true, 'LOC', NULL
FROM passages p
LEFT JOIN locstem l ON (l.pass_id, l.source_labez) = (p.pass_id, '*')
WHERE NOT p.fehlvers
""", dict(parameters, ms_id=MS_ID_A))
# Fill Fehlverse with labez 'zu'
execute(
conn, """
INSERT INTO apparatus_cliques_view (ms_id, pass_id, labez, clique, cbgm, origin, lesart)
SELECT :ms_id, p.pass_id, 'zu', '1', true, 'LOC', NULL
FROM passages p
WHERE p.fehlvers
""", dict(parameters, ms_id=MS_ID_A))
def build_A_text (dba, parameters):
"""Build the 'A' text
The editors' reconstruction of the archetype is recorded in the locstem
table. This functions generates a virtual manuscript 'A' from those choices.
The designation of a passage as 'Fehlvers' is an editorial decision that the
verse is not original, so we set 'zu'.
If the editors came to no final decision, no 'original' reading will be
found in locstem. In this case we set 'A' to 'zz' and there will be a gap
in the reconstructed text.
The Lesart of 'A' is always NULL, because it is a virtual manuscript.
"""
with dba.engine.begin () as conn:
execute (conn, """
DELETE FROM ms_cliques WHERE ms_id = :ms_id;
DELETE FROM ms_cliques_tts WHERE ms_id = :ms_id;
DELETE FROM apparatus WHERE ms_id = :ms_id;
""", dict (parameters, ms_id = MS_ID_A))
# Fill with the original reading in locstem or 'zz' if none found
execute (conn, """
INSERT INTO apparatus_cliques_view (ms_id, pass_id, labez, clique, cbgm, origin, lesart)
SELECT :ms_id, p.pass_id, COALESCE (l.labez, 'zz'), COALESCE (l.clique, '1'), true, 'LOC', NULL
FROM passages p
LEFT JOIN locstem l ON (l.pass_id, l.original) = (p.pass_id, true)
WHERE NOT p.fehlvers
""", dict (parameters, ms_id = MS_ID_A))
# Fill Fehlverse with labez 'zu'
execute (conn, """
INSERT INTO apparatus_cliques_view (ms_id, pass_id, labez, clique, cbgm, origin, lesart)
SELECT :ms_id, p.pass_id, 'zu', '1', true, 'LOC', NULL
FROM passages p
WHERE p.fehlvers
""", dict (parameters, ms_id = MS_ID_A))
db = db_tools.PostgreSQLEngine (**config)
tree = lxml.etree.parse (args.input if args.input != '-' else sys.stdin)
with db.engine.begin () as conn:
db_tools.truncate_editor_tables (conn)
log (logging.INFO, "Loading cliques ...")
with db.engine.begin () as conn:
values = []
for row in tree.xpath ('/sql/export_cliques/row'):
values.append ({ e.tag : e.text for e in row })
execute (conn, """
TRUNCATE import_cliques;
""", parameters)
executemany (conn, """
INSERT INTO import_cliques (passage, labez, clique,
sys_period, user_id_start, user_id_stop)
VALUES (:passage, :labez, :clique,
:sys_period, :user_id_start, :user_id_stop)
""", parameters, values)
execute (conn, """
UPDATE import_cliques u
SET pass_id = r.pass_id
FROM readings_view r
WHERE (u.passage, u.labez) = (r.passage, r.labez)
""", parameters)
db_tools.init_default_cliques(conn)
log(logging.INFO, "Build default ms_cliques ...")
db_tools.init_default_ms_cliques(conn)
log(logging.INFO, "Build default locstem ...")
db_tools.init_default_locstem(conn)
# default notes is an empty table
log(logging.INFO, "Loading cliques ...")
with db.engine.begin() as conn:
values = []
for row in tree.xpath('/sql/export_cliques/row'):
values.append({e.tag: e.text for e in row})
execute(conn, """
TRUNCATE import_cliques;
""", parameters)
executemany(
conn, """
INSERT INTO import_cliques (passage, labez, clique,
sys_period, user_id_start, user_id_stop)
VALUES (:passage, :labez, :clique,
:sys_period, :user_id_start, :user_id_stop)
""", parameters, values)
execute(
conn, """
UPDATE import_cliques u
SET pass_id = r.pass_id
FROM readings_view r
init_logging(
args,
logging.StreamHandler(), # stderr
logging.FileHandler('mk_users.log'))
dba = db_tools.PostgreSQLEngine(**config)
db.Base3.metadata.drop_all(dba.engine)
db.Base3.metadata.create_all(dba.engine)
pwd_context = CryptContext(schemes=[config['USER_PASSWORD_HASH']])
with dba.engine.begin() as src:
# create the basic roles
execute(
src,
"INSERT INTO role (id, name, description) VALUES (1, 'admin', 'Administrator')",
{})
execute(
src,
"INSERT INTO role (id, name, description) VALUES (2, 'editor', 'Editor')",
{})
# create the admin user
if args.email:
params = {
"username": args.username,
"email": args.email,
"password":
pwd_context.hash(args.password) if args.password else '',
"active": True,
"confirmed_at": datetime.datetime.now()
def relatives_csv(passage_or_id, hs_hsnr_id):
"""Output a table of the nearest relatives of a manuscript.
Output a table of the nearest relatives/ancestors/descendants of a
manuscript and what they attest.
"""
auth()
type_ = request.args.get('type') or 'rel'
limit = int(request.args.get('limit') or 0)
labez = request.args.get('labez') or 'all'
mode = request.args.get('mode') or 'sim'
include = request.args.getlist('include[]') or []
fragments = request.args.getlist('fragments[]') or []
view = 'affinity_view' if mode == 'rec' else 'affinity_p_view'
where = ''
if type_ == 'anc':
where = ' AND older < newer'
if type_ == 'des':
where = ' AND older >= newer'
if labez == 'all':
where += " AND labez !~ '^z'"
elif labez == 'all+lac':
pass
else:
where += " AND labez = '%s'" % labez
if 'fragments' in fragments:
frag_where = ''
else:
frag_where = 'AND aff.common > aff.ms1_length / 2'
limit = '' if limit == 0 else ' LIMIT %d' % limit
with current_app.config.dba.engine.begin() as conn:
passage = Passage(conn, passage_or_id)
ms = Manuscript(conn, hs_hsnr_id)
rg_id = passage.request_rg_id(request)
exclude = get_excluded_ms_ids(conn, include)
# Get the X most similar manuscripts and their attestations
res = execute(
conn, """
/* get the LIMIT closest ancestors for this node */
WITH ranks AS (
SELECT ms_id1, ms_id2,
rank () OVER (ORDER BY affinity DESC, common, older, newer DESC, ms_id2) AS rank,
affinity
FROM {view} aff
WHERE ms_id1 = :ms_id1 AND aff.rg_id = :rg_id AND ms_id2 NOT IN :exclude
AND newer > older {frag_where}
ORDER BY affinity DESC
)
SELECT r.rank,
aff.ms_id2 as ms_id,
ms.hs,
ms.hsnr,
aff.ms2_length,
aff.common,
aff.equal,
aff.older,
aff.newer,
aff.unclear,
aff.common - aff.equal - aff.older - aff.newer - aff.unclear as norel,
CASE WHEN aff.newer < aff.older THEN ''
WHEN aff.newer = aff.older THEN '-'
ELSE '>'
END as direction,
aff.affinity,
a.labez,
a.certainty
FROM
{view} aff
JOIN apparatus_view_agg a
ON aff.ms_id2 = a.ms_id
JOIN manuscripts ms
ON aff.ms_id2 = ms.ms_id
LEFT JOIN ranks r
ON r.ms_id2 = aff.ms_id2
WHERE aff.ms_id2 NOT IN :exclude AND aff.ms_id1 = :ms_id1
AND aff.rg_id = :rg_id AND aff.common > 0
AND a.pass_id = :pass_id {where} {frag_where}
ORDER BY affinity DESC, r.rank, newer DESC, older DESC, hsnr
{limit}
""",
dict(parameters,
where=where,
frag_where=frag_where,
ms_id1=ms.ms_id,
hsnr=ms.hsnr,
pass_id=passage.pass_id,
rg_id=rg_id,
limit=limit,
view=view,
exclude=exclude))
Relatives = collections.namedtuple(
'Relatives',
'rank ms_id hs hsnr length common equal older newer unclear norel direction affinity labez certainty'
)
return csvify(Relatives._fields, list(map(Relatives._make, res)))
db_tools.init_default_cliques (conn)
log (logging.INFO, "Build default ms_cliques ...")
db_tools.init_default_ms_cliques (conn)
log (logging.INFO, "Build default locstem ...")
db_tools.init_default_locstem (conn)
# default notes is an empty table
log (logging.INFO, "Loading cliques ...")
with db.engine.begin () as conn:
values = []
for row in tree.xpath ('/sql/export_cliques/row'):
values.append ({ e.tag : e.text for e in row })
execute (conn, """
TRUNCATE import_cliques;
""", parameters)
executemany (conn, """
INSERT INTO import_cliques (passage, labez, clique,
sys_period, user_id_start, user_id_stop)
VALUES (:passage, :labez, :clique,
:sys_period, :user_id_start, :user_id_stop)
""", parameters, values)
execute (conn, """
UPDATE import_cliques u
SET pass_id = r.pass_id
FROM readings_view r
WHERE (u.passage, u.labez) = (r.passage, r.labez)
""", parameters)
def concat_tables_fdw (conn, meta, dest_table, fdw, table_mask):
"""Concatenate multiple tables into one."""
table_mask = re.compile ('^%s$' % table_mask)
# find the set of fields common to all input tables. check types also. it
# is ridiculous that we have to do this but the table structures are highly
# inconsistent even between chapters of the same book.
source_table = None
column_set = collections.OrderedDict ()
for t in sorted (meta.tables.keys ()):
if table_mask.match (t):
source_model = sqlalchemy.Table (t, meta, autoload = True)
if source_table is None:
source_table = t
for c in source_model.columns:
column_set[c.name] = c.type.python_type
else:
col_set = { c.name : c.type.python_type for c in source_model.columns }
for name, type_ in list (column_set.items ()):
if col_set.get (name, '') != type_:
del column_set[name]
# create a table with those fields common to all input tables, lowercase the
# field names
execute (conn, """
DROP TABLE IF EXISTS {dest_table}
""", dict (parameters, dest_table = dest_table))
execute (conn, """
CREATE TABLE {dest_table} ( LIKE {fdw}."{source_table}" )
""", dict (parameters, dest_table = dest_table, source_table = source_table, fdw = fdw))
source_model = sqlalchemy.Table (source_table, meta, autoload = True)
cols = [column.name for column in source_model.columns]
for column in cols:
if column in column_set:
if column != column.lower ():
execute (conn, 'ALTER TABLE {dest_table} RENAME COLUMN "{source_column}" TO "{dest_column}"',
dict (parameters, dest_table = dest_table, source_column = column, dest_column = column.lower ()))
else:
execute (conn, 'ALTER TABLE {dest_table} DROP COLUMN "{source_column}"',
dict (parameters, dest_table = dest_table, source_column = column, dest_column = column.lower ()))
execute (conn, """COMMIT""", parameters);
# concat the input tables
for source_table in sorted (meta.tables.keys ()):
if not table_mask.match (source_table):
continue
log (logging.DEBUG, " Copying table %s" % source_table)
source_columns = ['"' + column + '"' for column in column_set.keys ()]
dest_columns = ['"' + column.lower () + '"' for column in column_set.keys ()]
execute (conn, """
INSERT INTO {dest_table} ({dest_columns})
SELECT {source_columns}
FROM {fdw}."{source_table}"
""", dict (parameters, source_table = source_table, dest_table = dest_table, fdw = fdw,
source_columns = ', '.join (source_columns),
dest_columns = ', '.join (dest_columns)))
def suggest_json ():
"""Endpoint. The suggestion drop-downs in the navigator.
Serves a list of books, chapters, verses, or words that the user can enter
in the navigation gadget. It suggests only entities that are actually in
the database.
"""
auth ()
# the name of the current field
field = request.args.get ('currentfield')
# the term the user entered in the current field
term = request.args.get ('term') or ''
term = '^' + re.escape (term.split ('-')[0])
# terms entered in previous fields
siglum = request.args.get ('siglum') or ''
chapter = request.args.get ('chapter') or 'All'
verse = request.args.get ('verse') or '1'
Words = collections.namedtuple (
'Words', 'kapanf, versanf, wortanf, kapend, versend, wortend, lemma')
res = []
with current_app.config.dba.engine.begin () as conn:
if field == 'siglum':
# only show those books that actually are in the database
res = execute (conn, """
SELECT DISTINCT siglum, siglum, book, bk_id
FROM passages_view b
WHERE siglum ~ :term OR book ~ :term
ORDER BY bk_id
""", dict (parameters, term = term))
return flask.json.jsonify (
[ { 'value' : r[0], 'label' : r[1], 'description' : r[2] } for r in res ])
if field == 'chapter':
res = execute (conn, """
SELECT range, range
FROM ranges_view
WHERE siglum = :siglum AND range ~ '[1-9][0-9]*' AND range ~ :term
ORDER BY range::integer
""", dict (parameters, siglum = siglum, term = term))
return flask.json.jsonify ([ { 'value' : r[0], 'label' : r[1] } for r in res ])
if field == 'verse':
res = execute (conn, """
SELECT DISTINCT verse, verse
FROM passages_view
WHERE variant AND siglum = :siglum AND chapter = :chapter AND verse::varchar ~ :term
ORDER BY verse
""", dict (parameters, siglum = siglum, chapter = chapter, term = term))
return flask.json.jsonify ([ { 'value' : r[0], 'label' : r[1] } for r in res ])
if field == 'word':
res = execute (conn, """
SELECT chapter, verse, word,
adr2chapter (p.endadr), adr2verse (p.endadr), adr2word (p.endadr),
COALESCE (string_agg (n.lemma, ' ' ORDER BY n.begadr), '') as lemma
FROM passages_view p
LEFT JOIN nestle n
ON (p.passage @> n.passage)
WHERE variant AND siglum = :siglum AND chapter = :chapter AND verse = :verse AND word::varchar ~ :term
GROUP BY chapter, verse, word, p.endadr
ORDER BY word, adr2verse (p.endadr), adr2word (p.endadr)
""", dict (parameters, siglum = siglum, chapter = chapter, verse = verse, term = term))
res = map (Words._make, res)
res = map (_f_map_word, res)
return flask.json.jsonify (
[ { 'value' : r[0], 'label' : r[1], 'description' : r[2] } for r in res ])
return flask.json.jsonify ([])
db = db_tools.PostgreSQLEngine (**config)
log (logging.INFO, "Saving changes ...")
if args.output == '-':
fp = sys.stdout
else:
fp = open (args.output, 'w', encoding='utf-8')
with db.engine.begin () as conn:
fp.write ('<?xml version="1.0" encoding="utf-8" ?>\n\n')
fp.write ('<sql profile="%s">\n' % args.profile)
res = execute (conn, """
SELECT (table_to_xml ('export_cliques', true, false, ''))
""", parameters)
fp.write (res.fetchone ()[0])
fp.write ('\n')
res = execute (conn, """
SELECT (table_to_xml ('export_ms_cliques', true, false, ''))
""", parameters)
fp.write (res.fetchone ()[0])
fp.write ('\n')
res = execute (conn, """
SELECT (table_to_xml ('export_locstem', true, false, ''))
""", parameters)
def relatives_csv (passage_or_id, hs_hsnr_id):
"""Output a table of the nearest relatives of a manuscript.
Output a table of the nearest relatives/ancestors/descendants of a
manuscript and what they attest.
"""
auth ()
type_ = request.args.get ('type') or 'rel'
chapter = request.args.get ('range') or 'All'
limit = int (request.args.get ('limit') or 0)
labez = request.args.get ('labez') or 'all'
mode = request.args.get ('mode') or 'sim'
include = request.args.getlist ('include[]') or []
fragments = request.args.getlist ('fragments[]') or []
view = 'affinity_view' if mode == 'rec' else 'affinity_p_view'
where = ''
if type_ == 'anc':
where = ' AND older < newer'
if type_ == 'des':
where = ' AND older >= newer'
if labez == 'all':
where += " AND labez !~ '^z'"
elif labez == 'all+lac':
pass
else:
where += " AND labez = '%s'" % labez
if 'fragments' in fragments:
frag_where = ''
else:
frag_where = 'AND aff.common > aff.ms1_length / 2'
limit = '' if limit == 0 else ' LIMIT %d' % limit
with current_app.config.dba.engine.begin () as conn:
passage = Passage (conn, passage_or_id)
ms = Manuscript (conn, hs_hsnr_id)
rg_id = passage.range_id (chapter)
exclude = get_excluded_ms_ids (conn, include)
# Get the X most similar manuscripts and their attestations
res = execute (conn, """
/* get the LIMIT closest ancestors for this node */
WITH ranks AS (
SELECT ms_id1, ms_id2,
rank () OVER (ORDER BY affinity DESC, common, older, newer DESC, ms_id2) AS rank,
affinity
FROM {view} aff
WHERE ms_id1 = :ms_id1 AND aff.rg_id = :rg_id AND ms_id2 NOT IN :exclude
AND newer > older {frag_where}
ORDER BY affinity DESC
)
SELECT r.rank,
aff.ms_id2 as ms_id,
ms.hs,
ms.hsnr,
aff.ms2_length,
aff.common,
aff.equal,
aff.older,
aff.newer,
aff.unclear,
aff.common - aff.equal - aff.older - aff.newer - aff.unclear as norel,
CASE WHEN aff.newer < aff.older THEN ''
WHEN aff.newer = aff.older THEN '-'
ELSE '>'
END as direction,
aff.affinity,
a.labez
FROM
{view} aff
JOIN apparatus_view_agg a
ON aff.ms_id2 = a.ms_id
JOIN manuscripts ms
ON aff.ms_id2 = ms.ms_id
LEFT JOIN ranks r
ON r.ms_id2 = aff.ms_id2
WHERE aff.ms_id2 NOT IN :exclude AND aff.ms_id1 = :ms_id1
AND aff.rg_id = :rg_id AND aff.common > 0
AND a.pass_id = :pass_id {where} {frag_where}
ORDER BY affinity DESC, r.rank, newer DESC, older DESC, hsnr
{limit}
""", dict (parameters, where = where, frag_where = frag_where,
ms_id1 = ms.ms_id, hsnr = ms.hsnr,
pass_id = passage.pass_id, rg_id = rg_id, limit = limit,
view = view, exclude = exclude))
Relatives = collections.namedtuple (
'Relatives',
'rank ms_id hs hsnr length common equal older newer unclear norel direction affinity labez'
)
return csvify (Relatives._fields, list (map (Relatives._make, res)))
