def schema_selection(db_alias: str):
"""Asynchronously computes the list of schemas with foreign key constraints"""
schemas_with_fk_constraints = schemas_with_foreign_key_constraints(db_alias)
if not schemas_with_fk_constraints or len(schemas_with_fk_constraints) == 0:
return str(_.i['No schemas with foreign key constraints found'])
return ''.join(xml.render([
[_.div(class_='form-check form-check-inline')[
_.label(class_='form-check-label')[
_.input(class_="form-check-input schema-checkbox", type="checkbox", value=schema_name)[
''], ' ', schema_name]]
for schema_name in sorted(schemas_with_fk_constraints)],
'   ',
_.div(class_='form-check form-check-inline')[
_.label(class_='form-check-label')[
_.input(class_="form-check-input", id='hide-columns-checkbox', type="checkbox")[
''], ' ', 'hide columns']],
'   ',
_.div(class_='form-check form-check-inline')[
_.label(class_='form-check-label')[
'graphviz engine ',
_.select(id='engine', style='border:none;background-color:white;')[
[_.option(value=engine)[engine] for engine in ['neato', 'dot', 'twopi', 'fdp']]
]]],
_.script['''
var schemaPage = SchemaPage("''' + flask.url_for('mara_db.index_page', db_alias=db_alias) + '''", "''' + db_alias + '''");
''']]))
def schema_selection(db_alias: str):
"""Asynchronously computes the list of schemas with foreign key constraints"""
schemas_with_fk_constraints = []
with mara_db.postgresql.postgres_cursor_context(db_alias) as cursor:
cursor.execute('''
SELECT
array_cat(array_agg(DISTINCT constrained_table_schema.nspname), array_agg(DISTINCT referenced_table_schema.nspname))
FROM pg_constraint
JOIN pg_class constrained_table ON constrained_table.oid = pg_constraint.conrelid
JOIN pg_namespace constrained_table_schema ON constrained_table.relnamespace = constrained_table_schema.oid
JOIN pg_class referenced_table ON referenced_table.oid = pg_constraint.confrelid
JOIN pg_namespace referenced_table_schema ON referenced_table.relnamespace = referenced_table_schema.oid'''
)
result = cursor.fetchone()
if result != (None, ):
schemas_with_fk_constraints = sorted(list(set(result[0])))
if len(schemas_with_fk_constraints) == 0:
return str(_.i['No schemas with foreign key constraints found'])
return ''.join(
xml.render(
[[
_.div(class_='form-check form-check-inline')[_.label(
class_='form-check-label')[
_.input(class_="form-check-input schema-checkbox",
type="checkbox",
value=schema_name)[''], ' ', schema_name]]
for schema_name in sorted(schemas_with_fk_constraints)
], '   ',
_.div(class_='form-check form-check-inline')[_.label(
class_='form-check-label')[_.input(class_="form-check-input",
id='hide-columns-checkbox',
type="checkbox")[''], ' ',
'hide columns']], '   ',
_.div(class_='form-check form-check-inline')[_.label(
class_='form-check-label'
)['graphviz engine ',
_.select(id='engine',
style='border:none;background-color:white;')[[
_.option(value=engine)[engine]
for engine in ['neato', 'dot', 'twopi', 'fdp']
]]]], _.script['''
var schemaPage = SchemaPage("''' + flask.url_for('mara_db.index_page',
db_alias=db_alias) +
'''", "''' + db_alias + '''");
''']]))
def data_set_page(id: str) -> response.Response:
"""Renders the pages for individual data sets"""
from ..config import data_sets
data_set = next(
(data_set for data_set in data_sets() if data_set.id() == id), None)
if not data_set:
flask.flash(f'Could not find data set "{id}"', category='warning')
return flask.redirect(flask.url_for('mara_schema.index_page'))
base_url = flask.url_for('mara_schema.data_set_sql_query',
id=data_set.id())
def attribute_rows(data_set: DataSet) -> []:
rows = []
for path, attributes in data_set.connected_attributes().items():
if path:
rows.append(_.tr[_.td(
colspan=3, style='border-top:none; padding-top: 20px;'
)[[[
'→ ',
_.a(
href=data_set_url(entity.data_set)
)[link_title] if entity.data_set else link_title, ' '
] for entity, link_title in [(
entity_link.target_entity,
entity_link.prefix or entity_link.target_entity.name)
for entity_link in path]],
[' ', _.
i[path[-1].description]] if path[-1].description else '']])
for prefixed_name, attribute in attributes.items():
rows.append(_.tr[_.td[escape(prefixed_name)], _.td[_.i[escape(
attribute.description
)]], _.td[_.tt[escape(
f'{path[-1].target_entity.table_name + "." if path else ""}{attribute.column_name}'
)]]])
return rows
return response.Response(
html=[
bootstrap.card(
header_left=_.i[escape(data_set.entity.description)],
body=[
_.p['Entity table: ', _.code[escape(
f'{data_set.entity.schema_name}.{data_set.entity.table_name}'
)]],
html.asynchronous_content(
flask.url_for('mara_schema.data_set_graph',
id=data_set.id())),
]),
bootstrap.card(
header_left='Metrics',
body=[
html.asynchronous_content(
flask.url_for('mara_schema.metrics_graph',
id=data_set.id())),
bootstrap.table(['Name', 'Description', 'Computation'], [[
_.tr[_.td[escape(metric.name)],
_.td[_.i[escape(metric.description)]],
_.td[_.code[escape(metric.display_formula())]]]
for metric in data_set.metrics.values()
]]),
]),
bootstrap.card(header_left='Attributes',
body=bootstrap.table(
["Name", "Description", "Column name"],
attribute_rows(data_set))),
bootstrap.card(
header_left=[
'Data set sql query: ',
[
_.div(class_='form-check form-check-inline')
[" ",
_.label(class_='form-check-label')[
_.input(class_="form-check-input param-checkbox",
type="checkbox",
value=param)[''], ' ', param]]
for param in [
'human readable columns',
'pre-computed metrics',
'star schema',
'personal data',
'high cardinality attributes',
]
]
],
body=[
_.div(id='sql-container')[html.asynchronous_content(
base_url, 'sql-container')], _.script['''
document.addEventListener('DOMContentLoaded', function() {
DataSetSqlQuery("''' + base_url + '''");
});
''']
])
],
title=f'Data set "{data_set.name}"',
js_files=[
flask.url_for('mara_schema.static',
filename='data-set-sql-query.js')
],
)