class SQLGenerator:
def __init__(self, dburl, hqmf_schema, result_schema, measure, inline_preference=True):
self.db = DBConnection(dburl, hqmf_schema)
self.result_util = HQMFResultUtil(result_schema)
self.measure = measure
self.symbol_table = ExtendedSymbolTable(measure.symbol_table, measure.non_stratifier_data_criteria(), self.db)
self.symbol_table.set_inline_preference(inline_preference)
self.symbol_table.measure_name = self.measure_name()
self.symbol_table.sql_generator = self
def create_empty_table(self, name_suffix, base_cols):
table_name = 'measure_' + self.measure_name() + '_' + name_suffix
cols = []
for c in base_cols:
cols.append(Column(c.name, c.type))
table = Table(table_name,
self.db.meta,
*cols,
schema=self.result_util.get_schema())
print(str(CreateTable(table, bind=self.db.engine)))
print(self.result_util.statement_terminator())
return table
def populate_table_from_select(self, table, sel):
if table is None or sel is None:
raise ValueError("table or select not set")
print(sql_to_string(table.insert().from_select(sel.columns, sel), self.pretty))
print(self.result_util.statement_terminator())
for c in table.columns:
bc = c.base_columns
if bc != None and len(bc) > 0:
bname = list(bc)[0].name
else:
bname = c.name
if bname.endswith(QDMConstants.PATIENT_ID_COL):
ixname = 'ix_' + table.name + '_' + QDMConstants.PATIENT_ID_COL
print(str(CreateIndex(Index(ixname, c), bind=self.db.engine)))
print(self.result_util.statement_terminator())
return table
def create_table_from_select(self, name_suffix, sel):
if sel is None:
raise ValueError("sel is None")
table = self.create_empty_table(name_suffix, sel.columns)
return self.populate_table_from_select(table, sel)
def measure_name(self):
return self.measure.get_measure_name()
def viewname_all(self, popnum):
return 'measure_' + self.measure_name() + "_" + str(popnum) + '_all'
def print_debug(self):
dc_set = set()
self.symbol_table.create_base_query(self)
for dc in self.symbol_table.get_data_criteria().values():
print("<hr>")
dc.dc.print_summary(self.symbol_table.raw_symbol_table, dc_set, dc_verbose=True)
print("<p>")
ds = dc.new_selectable(self.symbol_table)
if hasattr(ds, 'create_selectable'):
sql = ds.create_selectable()
if sql is not None:
print(html.escape(sql_to_string(sql)))
print("</p>")
def generate_sql(self, pretty=True, skip_exceptions=False):
self.pretty = pretty
self.symbol_table.create_base_query(self)
popnum = 0
for pcsection in self.measure.populations:
print("---found pcsection " + str(popnum))
popnames = []
cols=self.symbol_table.base_select.columns.values()
for pclist in pcsection.population_criteria.values():
if isinstance(pclist, list):
pass
# for pccrit in pclist:
# pop = ExtendedPopulationCriterion(pccrit)
# psql = pop.to_sql(self.symbol_table).label(pop.population_name())
# cols.append(psql)
else:
pop = ExtendedPopulationCriterion(pclist)
if skip_exceptions and pop.population_name() in ['denominatorExceptions', 'denominatorExclusions']:
print("--skipping {popname}".format(popname = pop.population_name()))
psql = None
else:
psql = pop.to_sql(self.symbol_table)
if psql != None:
cols.append(psql.label(pop.population_name()))
popnames.append(pop.population_name())
if len(cols) > 0:
query = select(cols)
# print(sql_to_string(query))
print(self.result_util.create_view(self.viewname_all(popnum), query, pretty))
self.create_patient_summary_table(popnames, popnum)
index_name = self.find_index_colname(cols)
if index_name != None:
self.create_event_summary_table(popnames, popnum, index_name)
popnum = popnum+1
def find_index_colname(self, cols):
# quick and dirty version -- should really do this while building measure population
index_col = None
for c in cols:
if c.name.endswith('audit_key_value'):
if index_col is not None:
print("--multiple possibilities for index col")
return None
index_col = c
if index_col is None:
return None
return index_col.name
def create_patient_summary_table(self, popnames, popnum):
self.create_summary_table(popnames, popnum, '_patient_summary', 'base_patient_id', 'patient_id')
def create_event_summary_table(self, popnames, popnum, index_name):
self.create_summary_table(popnames, popnum, '_event_summary', index_name, 'event_id')
def create_summary_table(self, popnames, popnum, view_suffix, window_colname, window_label):
result_table = Table('measure_' + self.measure_name() + '_' + str(popnum) + view_suffix, self.db.meta,
Column(window_label, Integer),
Column('effective_ipp', Boolean),
Column('effective_denom', Boolean),
Column('effective_denex', Boolean),
Column('effective_numer', Boolean),
Column('effective_denexcep', Boolean),
schema=self.result_util.get_schema())
print(str(CreateTable(result_table, bind=self.db.engine)))
print(self.result_util.statement_terminator())
popquery = self.make_popquery(popnames, popnum, view_suffix, window_colname, window_label)
ins = result_table.insert().from_select([
window_label,
'effective_ipp',
'effective_denom',
'effective_denex',
'effective_numer',
'effective_denexcep'], popquery)
print(sql_to_string(ins))
print(self.result_util.statement_terminator())
def constant_string(self, str):
return cast(str, String(256))
def make_popquery(self, popnames, popnum, view_suffix, window_colname, window_label):
cdict = dict()
self.make_ipp_col(popnames, cdict)
self.make_denom_col(popnames, cdict)
self.make_denex_col(popnames, cdict)
self.make_numer_col(popnames, cdict)
self.make_denexcep_col(popnames, cdict)
fromview = text(self.result_util.qualified_artifact_name(self.viewname_all(popnum)))
basename = self.measure_name() + '_' + str(popnum)
viewname = basename + view_suffix
window_col = column(window_colname)
order_cols=[]
for key in ['denominatorExclusions', 'numerator', 'denominatorExceptions', 'denominator']:
col = cdict.get(key)
if col is None:
cdict[key] = cast(null(), Boolean)
else:
order_cols.append(col.desc())
if cdict.get('STRAT') is None:
cdict['STRAT'] = cast(null(), Boolean)
summary_cols = [
window_col.label(window_label),
cdict.get('initialPopulation').label('effective_ipp'),
cdict.get('denominator').label('effective_denom'),
cdict.get('denominatorExclusions').label('effective_denex'),
cdict.get('numerator').label('effective_numer'),
cdict.get('denominatorExceptions').label('effective_denexcep'),
]
rowcol = func.rank().over(partition_by=window_col, order_by = order_cols)
summary_cols.append(rowcol.label('rank'))
base = select(summary_cols).select_from(fromview).where(cdict.get('initialPopulation')).alias()
print("---window_colname is " + str(window_colname))
query=select([
base.c.get(window_label),
base.c.effective_ipp,
base.c.effective_denom,
base.c.effective_denex,
base.c.effective_numer,
base.c.effective_denexcep]).where(base.c.rank == 1).distinct()
return(query.alias())
def make_ipp_col(self, popnames, cdict):
cdict['initialPopulation'] = column('initialPopulation')
def make_denom_col(self, popnames, cdict):
sname = 'denominator'
ipp = cdict.get('initialPopulation')
if sname in popnames:
denom = column(sname)
cdict['denominator'] = and_(ipp, denom)
else:
cdict['denominator'] = ipp
def make_denex_col(self, popnames, cdict):
sname = 'denominatorExclusions'
if sname in popnames:
denex = column(sname)
denom = cdict.get('denominator')
cdict[sname] = and_(denom, denex)
def make_numer_col(self, popnames, cdict):
sname = 'numerator'
if sname in popnames:
numer = column(sname)
denom = cdict.get('denominator')
denex = cdict.get('denominatorExclusions')
if denex is not None:
numer = and_(denom, not_(denex), numer)
else:
numer = and_(denom, numer)
cdict['numerator'] = numer
else:
raise ValueError("No numerator in population")
def make_denexcep_col(self, popnames, cdict):
sname = 'denominatorExceptions'
if sname in popnames:
denexcep = column(sname)
numer = cdict.get('numerator')
denom = cdict.get('denominator')
denex = cdict.get('denominatorExclusions')
if denex is not None:
denexcep = and_(denom, not_(denex), not_(numer), denexcep)
else:
denexcep = and_(denom, not_(numer), denexcep)
cdict[sname] = denexcep
class SQLGenerator:
def __init__(self,
dburl,
hqmf_schema,
result_schema,
measure,
inline_preference=True):
self.db = DBConnection(dburl, hqmf_schema)
self.result_util = HQMFResultUtil(result_schema)
self.measure = measure
self.symbol_table = ExtendedSymbolTable(
measure.symbol_table, measure.non_stratifier_data_criteria(),
self.db)
self.symbol_table.set_inline_preference(inline_preference)
self.symbol_table.measure_name = self.measure_name()
self.symbol_table.sql_generator = self
def create_empty_table(self, name_suffix, base_cols):
table_name = 'measure_' + self.measure_name() + '_' + name_suffix
cols = []
for c in base_cols:
cols.append(Column(c.name, c.type))
table = Table(table_name,
self.db.meta,
*cols,
schema=self.result_util.get_schema())
print(str(CreateTable(table, bind=self.db.engine)))
print(self.result_util.statement_terminator())
return table
def populate_table_from_select(self, table, sel):
if table is None or sel is None:
raise ValueError("table or select not set")
print(
sql_to_string(table.insert().from_select(sel.columns, sel),
self.pretty))
print(self.result_util.statement_terminator())
for c in table.columns:
bc = c.base_columns
if bc != None and len(bc) > 0:
bname = list(bc)[0].name
else:
bname = c.name
if bname.endswith(QDMConstants.PATIENT_ID_COL):
ixname = 'ix_' + table.name + '_' + QDMConstants.PATIENT_ID_COL
print(str(CreateIndex(Index(ixname, c), bind=self.db.engine)))
print(self.result_util.statement_terminator())
return table
def create_table_from_select(self, name_suffix, sel):
if sel is None:
raise ValueError("sel is None")
table = self.create_empty_table(name_suffix, sel.columns)
return self.populate_table_from_select(table, sel)
def measure_name(self):
return self.measure.get_measure_name()
def viewname_all(self, popnum):
return 'measure_' + self.measure_name() + "_" + str(popnum) + '_all'
def print_debug(self):
dc_set = set()
self.symbol_table.create_base_query(self)
for dc in self.symbol_table.get_data_criteria().values():
print("<hr>")
dc.dc.print_summary(self.symbol_table.raw_symbol_table,
dc_set,
dc_verbose=True)
print("<p>")
ds = dc.new_selectable(self.symbol_table)
if hasattr(ds, 'create_selectable'):
sql = ds.create_selectable()
if sql is not None:
print(html.escape(sql_to_string(sql)))
print("</p>")
def generate_sql(self, pretty=True, skip_exceptions=False):
self.pretty = pretty
self.symbol_table.create_base_query(self)
popnum = 0
for pcsection in self.measure.populations:
print("---found pcsection " + str(popnum))
popnames = []
cols = self.symbol_table.base_select.columns.values()
for pclist in pcsection.population_criteria.values():
if isinstance(pclist, list):
pass
# for pccrit in pclist:
# pop = ExtendedPopulationCriterion(pccrit)
# psql = pop.to_sql(self.symbol_table).label(pop.population_name())
# cols.append(psql)
else:
pop = ExtendedPopulationCriterion(pclist)
if skip_exceptions and pop.population_name() in [
'denominatorExceptions', 'denominatorExclusions'
]:
print("--skipping {popname}".format(
popname=pop.population_name()))
psql = None
else:
psql = pop.to_sql(self.symbol_table)
if psql != None:
cols.append(psql.label(pop.population_name()))
popnames.append(pop.population_name())
if len(cols) > 0:
query = select(cols)
# print(sql_to_string(query))
print(
self.result_util.create_view(self.viewname_all(popnum),
query, pretty))
self.create_patient_summary_table(popnames, popnum)
index_name = self.find_index_colname(cols)
if index_name != None:
self.create_event_summary_table(popnames, popnum,
index_name)
popnum = popnum + 1
def find_index_colname(self, cols):
# quick and dirty version -- should really do this while building measure population
index_col = None
for c in cols:
if c.name.endswith('audit_key_value'):
if index_col is not None:
print("--multiple possibilities for index col")
return None
index_col = c
if index_col is None:
return None
return index_col.name
def create_patient_summary_table(self, popnames, popnum):
self.create_summary_table(popnames, popnum, '_patient_summary',
'base_patient_id', 'patient_id')
def create_event_summary_table(self, popnames, popnum, index_name):
self.create_summary_table(popnames, popnum, '_event_summary',
index_name, 'event_id')
def create_summary_table(self, popnames, popnum, view_suffix,
window_colname, window_label):
result_table = Table('measure_' + self.measure_name() + '_' +
str(popnum) + view_suffix,
self.db.meta,
Column(window_label, Integer),
Column('effective_ipp', Boolean),
Column('effective_denom', Boolean),
Column('effective_denex', Boolean),
Column('effective_numer', Boolean),
Column('effective_denexcep', Boolean),
schema=self.result_util.get_schema())
print(str(CreateTable(result_table, bind=self.db.engine)))
print(self.result_util.statement_terminator())
popquery = self.make_popquery(popnames, popnum, view_suffix,
window_colname, window_label)
ins = result_table.insert().from_select([
window_label, 'effective_ipp', 'effective_denom',
'effective_denex', 'effective_numer', 'effective_denexcep'
], popquery)
print(sql_to_string(ins))
print(self.result_util.statement_terminator())
def constant_string(self, str):
return cast(str, String(256))
def make_popquery(self, popnames, popnum, view_suffix, window_colname,
window_label):
cdict = dict()
self.make_ipp_col(popnames, cdict)
self.make_denom_col(popnames, cdict)
self.make_denex_col(popnames, cdict)
self.make_numer_col(popnames, cdict)
self.make_denexcep_col(popnames, cdict)
fromview = text(
self.result_util.qualified_artifact_name(
self.viewname_all(popnum)))
basename = self.measure_name() + '_' + str(popnum)
viewname = basename + view_suffix
window_col = column(window_colname)
order_cols = []
for key in [
'denominatorExclusions', 'numerator', 'denominatorExceptions',
'denominator'
]:
col = cdict.get(key)
if col is None:
cdict[key] = cast(null(), Boolean)
else:
order_cols.append(col.desc())
if cdict.get('STRAT') is None:
cdict['STRAT'] = cast(null(), Boolean)
summary_cols = [
window_col.label(window_label),
cdict.get('initialPopulation').label('effective_ipp'),
cdict.get('denominator').label('effective_denom'),
cdict.get('denominatorExclusions').label('effective_denex'),
cdict.get('numerator').label('effective_numer'),
cdict.get('denominatorExceptions').label('effective_denexcep'),
]
rowcol = func.rank().over(partition_by=window_col, order_by=order_cols)
summary_cols.append(rowcol.label('rank'))
base = select(summary_cols).select_from(fromview).where(
cdict.get('initialPopulation')).alias()
print("---window_colname is " + str(window_colname))
query = select([
base.c.get(window_label), base.c.effective_ipp,
base.c.effective_denom, base.c.effective_denex,
base.c.effective_numer, base.c.effective_denexcep
]).where(base.c.rank == 1).distinct()
return (query.alias())
def make_ipp_col(self, popnames, cdict):
cdict['initialPopulation'] = column('initialPopulation')
def make_denom_col(self, popnames, cdict):
sname = 'denominator'
ipp = cdict.get('initialPopulation')
if sname in popnames:
denom = column(sname)
cdict['denominator'] = and_(ipp, denom)
else:
cdict['denominator'] = ipp
def make_denex_col(self, popnames, cdict):
sname = 'denominatorExclusions'
if sname in popnames:
denex = column(sname)
denom = cdict.get('denominator')
cdict[sname] = and_(denom, denex)
def make_numer_col(self, popnames, cdict):
sname = 'numerator'
if sname in popnames:
numer = column(sname)
denom = cdict.get('denominator')
denex = cdict.get('denominatorExclusions')
if denex is not None:
numer = and_(denom, not_(denex), numer)
else:
numer = and_(denom, numer)
cdict['numerator'] = numer
else:
raise ValueError("No numerator in population")
def make_denexcep_col(self, popnames, cdict):
sname = 'denominatorExceptions'
if sname in popnames:
denexcep = column(sname)
numer = cdict.get('numerator')
denom = cdict.get('denominator')
denex = cdict.get('denominatorExclusions')
if denex is not None:
denexcep = and_(denom, not_(denex), not_(numer), denexcep)
else:
denexcep = and_(denom, not_(numer), denexcep)
cdict[sname] = denexcep