<commit_before>import sqlalchemy as alc
from sqlalchemy import create_engine, MetaData
from rdrf.dynamic_data import DynamicDataWrapper
import explorer
from explorer.utils import DatabaseUtils
from rdrf.utils import timed

import logging
logger = logging.getLogger("registry_log")

from django.conf import settings


def temporary_table_name(query_model, user):
    return "report_%s_%s" % (query_model.id, user.pk)


class ReportingTableGenerator(object):
    DEMOGRAPHIC_FIELDS = [('family_name', 'todo', alc.String),
                          ('given_names', 'todo', alc.String),
                          ('home_phone', 'todo', alc.String),
                          ('email', 'todo', alc.String)]
    TYPE_MAP = {"float": alc.Float,
                "calculated": alc.String, #todo fix this - some calculated field are strings some numbers
                "integer": alc.Integer,
                "int": alc.Integer,
                "string": alc.String,
                }

    def __init__(self, user, registry_model, multisection_unroller, humaniser, type_overrides={}):
        self.user = user
        self.counter = 0
        self.col_map = {}
        self.registry_model = registry_model
        self.type_overrides = type_overrides
        self.engine = self._create_engine()
        self.columns = set([])
        self.table_name = ""
        self.table = None
        self.reverse_map = {}
        self.multisection_column_map = {}
        self.multisection_unroller = multisection_unroller
        self.humaniser = humaniser

    def _create_engine(self):
        report_db_data = settings.DATABASES["reporting"]
        db_user = report_db_data["USER"]
        db_pass = report_db_data["PASSWORD"]
        database = report_db_data["NAME"]
        host = report_db_data["HOST"]
        port = report_db_data["PORT"]
        connection_string = "postgresql://{0}:{1}@{2}:{3}/{4}".format(db_user,
                                                                      db_pass,
                                                                      host,
                                                                      port,
                                                                      database)

        logger.debug("connection_string = %s" % connection_string)
        return create_engine(connection_string)

    @timed
    def create_table(self):
        self.table.create()
        logger.debug("created table based on schema")

    @timed
    def drop_table(self):
        drop_table_sql = "DROP TABLE %s" % self.table_name
        conn = self.engine.connect()
        try:
            conn.execute(drop_table_sql)
            logger.debug("dropped temp table %s" % self.table_name)
        except:
            pass
        conn.close()

    def set_table_name(self, obj):
        logger.debug("setting table name from %s %s" % (self.user.username, obj))
        self.table_name = temporary_table_name(obj, self.user)
        logger.info("table name = %s" % self.table_name)

    def _add_reverse_mapping(self, key, value):
        self.reverse_map[key] = value

    def _create_must_exist_columns(self):
        # These columns will always appear
        self.columns.add(self._create_column("context_id", alc.Integer))
        self._add_reverse_mapping("context_id", "context_id")
        logger.debug("COLUMN: context_id")


        self.columns.add(self._create_column("timestamp", alc.String))
        self._add_reverse_mapping("timestamp", "timestamp")
        logger.debug("COLUMN: timestamp")

        self.columns.add(self._create_column("snapshot", alc.Boolean))
        self._add_reverse_mapping("snapshot", "snapshot")
        logger.debug("COLUMN: snapshot")
        

    def _create_sql_columns(self, sql_metadata):
        # Columns from sql query
        for i, column_metadata in enumerate(sql_metadata):
            column_from_sql = self._create_column_from_sql(column_metadata)
            self.columns.add(column_from_sql)
            #to get from tuple
            # map the column's index in tuple to the column name in the dict
            self._add_reverse_mapping(i, column_metadata["name"])

    @timed
    def create_columns(self, sql_metadata, mongo_metadata):
        self.columns = set([])
        self._create_must_exist_columns()
        self._create_sql_columns(sql_metadata)
        self._create_mongo_columns(mongo_metadata)

    def _create_mongo_columns(self, mongo_metadata):
        # FH-22 ...
        # given triples of form_model, section_model and cde_models + a column name
        # create the correctly typed column in postgres
        # If the section is multiple , create ITEM_MAX columns to for each CDE in th
        # multisection   ( as opposed to "unrolling" the data )

        # e.g.:
        # if secA is non-multiple with CDEs P, Q and R
        # and secM is multiple with CDEs X and Y
        # we create columns:
        # |secA P|secA Q|secA R|secM X_1|secM Y_1|secM X_2|secM X_3|secM Y_3| ...|secM X_ITEM_MAX|secM Y_ITEM_MAX| 
        # If ITEM_MAX is too small we could miss out some data ; we could calculate it but that's another query .. 
        column_map = mongo_metadata["multisection_column_map"]
        multisection_map = {}
        current_form = None
        current_section = None
        column_operations = []

        class ColumnOp(object):
            def __init__(self, columns, form_model, section_model, cde_model, column_name):
                self.columns = columns
                self.form_model = form_model
                self.section_model = section_model
                self.cde_model = cde_model
                self.column_name = column_name
                self.section_order = 
                self.column_index = None
                self.has_run = False
            def run(self):
                column = self._create_column()
                self.columns.add(column)
                self.has_run = True

            def _create_column(self):
                pass


        class ColumnOps(object):
            def __init__(self, max_items):
                self.max_items = max_items
                self.column_ops = []

            def add(self, column_op):
                self.column_ops.append(column_op)

            def run(self):
                self._create_multisection_columns()
                self._reorder()
                self._create_columns()
                

        column_ops = ColumnOps(max_items=5)
        
        for ( (form_model, section_model, cde_model), column_name) in column_map.items():
            column_op = ColumnOp(self.columns,
                                 form_model,
                                 section_model,
                                 cde_model,
                                 column_name)

            column_ops.add(column_op)

        column_ops.

                

    def _create_column_from_sql(self, column_metadata):
        column_name = column_metadata["name"]
        type_name = column_metadata["type_name"]
        # Not sure if this good idea ...
        # these type_names are coming from introspection on the sql cursor
        if type_name == "varchar":
            datatype = alc.String
        elif type_name == "bool":
            datatype = alc.Boolean
        elif "float" in type_name:
            datatype = alc.FLOAT
        elif "int" in type_name:
            datatype = alc.INTEGER
        elif "date" in type_name:
            datatype = alc.DATE
        else:
            datatype = alc.String

        logger.debug("COLUMN: %s" % column_name)
        return self._create_column(column_name, datatype)

    def _create_column_from_mongo(self, column_name, form_model, section_model, cde_model):
        column_data_type = self._get_sql_alchemy_datatype(cde_model)
        self._add_reverse_mapping((form_model, section_model, cde_model), column_name)
        if section_model.allow_multiple:
            # This map is used when we unroll/"denormalise" the multisection data
            if section_model.code in self.multisection_column_map:
                self.multisection_column_map[section_model.code].append(column_name)<commit_msg>FH-22 column ops for horizontal multisections<commit_after>import sqlalchemy as alc
from sqlalchemy import create_engine, MetaData
from rdrf.dynamic_data import DynamicDataWrapper
import explorer
from explorer.utils import DatabaseUtils
from rdrf.utils import timed

import logging
logger = logging.getLogger("registry_log")

from django.conf import settings


def temporary_table_name(query_model, user):
    return "report_%s_%s" % (query_model.id, user.pk)


class ReportingTableGenerator(object):
    DEMOGRAPHIC_FIELDS = [('family_name', 'todo', alc.String),
                          ('given_names', 'todo', alc.String),
                          ('home_phone', 'todo', alc.String),
                          ('email', 'todo', alc.String)]
    TYPE_MAP = {"float": alc.Float,
                "calculated": alc.String, #todo fix this - some calculated field are strings some numbers
                "integer": alc.Integer,
                "int": alc.Integer,
                "string": alc.String,
                }

    def __init__(self, user, registry_model, multisection_unroller, humaniser, type_overrides={}):
        self.user = user
        self.counter = 0
        self.col_map = {}
        self.registry_model = registry_model
        self.type_overrides = type_overrides
        self.engine = self._create_engine()
        self.columns = set([])
        self.table_name = ""
        self.table = None
        self.reverse_map = {}
        self.multisection_column_map = {}
        self.multisection_unroller = multisection_unroller
        self.humaniser = humaniser

    def _create_engine(self):
        report_db_data = settings.DATABASES["reporting"]
        db_user = report_db_data["USER"]
        db_pass = report_db_data["PASSWORD"]
        database = report_db_data["NAME"]
        host = report_db_data["HOST"]
        port = report_db_data["PORT"]
        connection_string = "postgresql://{0}:{1}@{2}:{3}/{4}".format(db_user,
                                                                      db_pass,
                                                                      host,
                                                                      port,
                                                                      database)

        logger.debug("connection_string = %s" % connection_string)
        return create_engine(connection_string)

    @timed
    def create_table(self):
        self.table.create()
        logger.debug("created table based on schema")

    @timed
    def drop_table(self):
        drop_table_sql = "DROP TABLE %s" % self.table_name
        conn = self.engine.connect()
        try:
            conn.execute(drop_table_sql)
            logger.debug("dropped temp table %s" % self.table_name)
        except:
            pass
        conn.close()

    def set_table_name(self, obj):
        logger.debug("setting table name from %s %s" % (self.user.username, obj))
        self.table_name = temporary_table_name(obj, self.user)
        logger.info("table name = %s" % self.table_name)

    def _add_reverse_mapping(self, key, value):
        self.reverse_map[key] = value

    def _create_must_exist_columns(self):
        # These columns will always appear
        self.columns.add(self._create_column("context_id", alc.Integer))
        self._add_reverse_mapping("context_id", "context_id")
        logger.debug("COLUMN: context_id")


        self.columns.add(self._create_column("timestamp", alc.String))
        self._add_reverse_mapping("timestamp", "timestamp")
        logger.debug("COLUMN: timestamp")

        self.columns.add(self._create_column("snapshot", alc.Boolean))
        self._add_reverse_mapping("snapshot", "snapshot")
        logger.debug("COLUMN: snapshot")
        

    def _create_sql_columns(self, sql_metadata):
        # Columns from sql query
        for i, column_metadata in enumerate(sql_metadata):
            column_from_sql = self._create_column_from_sql(column_metadata)
            self.columns.add(column_from_sql)
            #to get from tuple
            # map the column's index in tuple to the column name in the dict
            self._add_reverse_mapping(i, column_metadata["name"])

    @timed
    def create_columns(self, sql_metadata, mongo_metadata):
        self.columns = set([])
        self._create_must_exist_columns()
        self._create_sql_columns(sql_metadata)
        self._create_mongo_columns(mongo_metadata)

    def _create_mongo_columns(self, mongo_metadata):
        # FH-22 ...
        # given triples of form_model, section_model and cde_models + a column name
        # create the correctly typed column in postgres
        # If the section is multiple , create ITEM_MAX columns to for each CDE in th
        # multisection   ( as opposed to "unrolling" the data )

        # e.g.:
        # if secA is non-multiple with CDEs P, Q and R
        # and secM is multiple with CDEs X and Y
        # we create columns:
        # |secA P|secA Q|secA R|secM X_1|secM Y_1|secM X_2|secM X_3|secM Y_3| ...|secM X_ITEM_MAX|secM Y_ITEM_MAX| 
        # If ITEM_MAX is too small we could miss out some data ; we could calculate it but that's another query .. 
        column_map = mongo_metadata["multisection_column_map"]
        multisection_map = {}
        current_form = None
        current_section = None
        column_operations = []

        class ColumnOp(object):
            def __init__(self, rtg, columns, form_model, section_model, cde_model, column_name):
                self.rtg = rtg
                self.columns = columns
                self.form_model = form_model
                self.section_model = section_model
                self.cde_model = cde_model
                self.column_name = column_name
                self.section_order = (form_model, section_model)
                self.column_index = None
                self.has_run = False
            def run(self):
                column = self._create_column()
                self.columns.add(column)
                self.has_run = True

            def _create_column(self):
                if self.column_index is None:
                    column = self.rtg._create_column_from_mongo(self.column_name,
                                                       self.form_model,
                                                       self.section_model,
                                                       self.cde_model)
                else:
                    column_name = "%_%" % (self.column_name, self.column_index)
                    column = self.rtg._create_column_from_mongo(column_name,
                                                       self.form_model,
                                                       self.section_model,
                                                       self.cde_model)
                    
                    

        class ColumnOps(object):
            def __init__(self, max_items):
                self.max_items = max_items
                self.column_ops = []

            def add(self, column_op):
                self.column_ops.append(column_op)

            def run(self):
                self._create_multisection_columns()
                self._reorder()
                self._create_columns()

            def _create_multisection_columns(self):
                new_ops = []
                for op in self.column_ops:
                    if op.section_model.allow_multiple:
                        for i in range(1, self.max_items + 1):
                            pass
                            

                

        column_ops = ColumnOps(max_items=5)
        
        for ( (form_model, section_model, cde_model), column_name) in column_map.items():
            column_op = ColumnOp(self,
                                 self.columns,
                                 form_model,
                                 section_model,
                                 cde_model,
                                 column_name)

            column_ops.add(column_op)

        column_ops.run()

                

    def _create_column_from_sql(self, column_metadata):
        column_name = column_metadata["name"]
        type_name = column_metadata["type_name"]
        # Not sure if this good idea ...
        # these type_names are coming from introspection on the sql cursor
        if type_name == "varchar":
            datatype = alc.String
        elif type_name == "bool":
            datatype = alc.Boolean
        elif "float" in type_name:
            datatype = alc.FLOAT
        elif "int" in type_name:
            datatype = alc.INTEGER
        elif "date" in type_name:
            datatype = alc.DATE
        else:
            datatype = alc.String

        logger.debug("COLUMN: %s" % column_name)
        return self._create_column(column_name, datatype)

    def _create_column_from_mongo(self, column_name, form_model, section_model, cde_model):
        column_data_type = self._get_sql_alchemy_datatype(cde_model)
        self._add_reverse_mapping((form_model, section_model, cde_model), column_name)
        if section_model.allow_multiple:
            # This map is used when we unroll/"denormalise" the multisection data
            if section_model.code in self.multisection_column_map:
                self.multisection_column_map[section_model.code].append(column_name)
{'fraction_non_alphanumeric': 0.08393681929634488, 'fraction_numerical': 0.0018247134629640188, 'mean_word_length': 1.81780205655527, 'pattern_counts': {'":': 14, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 45, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1755257', 'n_tokens_mistral': 4853, 'n_tokens_neox': 4704, 'n_words': 1065}