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 __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
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