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
Example #2
0
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