src/dirbs/listgen/generator.py
def _populate_new_notifications_triplets_single_shard(self, virt_imei_range_start, virt_imei_range_end, executor):
        """Function to create and generate the new table of unique IMEI/IMSI/MSISDN notifications triplets."""
        with create_db_connection(self._config.db_config) as conn, conn.cursor() as cursor, CodeProfiler() as cp:
            notifications_imeis_shard = \
                partition_utils.imei_shard_name(base_name=self._notifications_imei_new_tblname,
                                                virt_imei_range_start=virt_imei_range_start,
                                                virt_imei_range_end=virt_imei_range_end)
            notifications_triplets_shard = \
                partition_utils.imei_shard_name(base_name=self._notifications_triplets_new_tblname,
                                                virt_imei_range_start=virt_imei_range_start,
                                                virt_imei_range_end=virt_imei_range_end)
            pairing_list_shard = partition_utils.imei_shard_name(base_name='historic_pairing_list',
                                                                 virt_imei_range_start=virt_imei_range_start,
                                                                 virt_imei_range_end=virt_imei_range_end)

            # Note: IMSI can't be NULL in pairing list as it is constrained in table DDL
            #
            # 20170504: Re: discussion with Daniel it was decided to exclude triplets with a NULL IMSI or MSISDN
            #           from notifications. Without an MSISDN there is no easy way for an operator to contact the
            #           subscriber. Without an IMSI we can't determine accurately whether they were already paired.
            #           We expect both NULL IMSI and MSISDN to be transient, weird events that do not consistently
            #           happen for the same subscriber. Therefore they will be notified anyway based on the non-NULL
            #           IMSI/MSISDN that is expected to be seen either on the same day or at some point during the
            #           configured lookback window.

            # check weather to notify the IMSI change or not, default behavior is to not however can be altered
            if self._notify_imsi_change:
                imsi_change_filter = sql.SQL('AND imsi = network_triplets.imsi')
            else:
                imsi_change_filter = sql.SQL('AND imsi = network_triplets.imsi OR msisdn = network_triplets.msisdn')

            query = sql.SQL(
                """INSERT INTO {notifications_triplets_shard}(imei_norm,
                                                              virt_imei_shard,
                                                              imsi,
                                                              msisdn,
                                                              block_date,
                                                              reasons,
                                                              is_valid,
                                                              amnesty_granted,
                                                              imei_norm_with_check_digit,
                                                              home_operator,
                                                              fallback_operators)
                        SELECT imei_norm,
                               FIRST(network_triplets.virt_imei_shard),
                               imsi,
                               msisdn,
                               FIRST(block_date),
                               FIRST(reasons),
                               FIRST(is_valid),
                               FIRST(amnesty_granted),
                               FIRST(imei_norm_with_check_digit),
                               FIRST(home_network_tbl.operator_id) AS home_operator,
                               array_agg(DISTINCT network_triplets.operator_id)
                                         filter(WHERE network_triplets.operator_id IS NOT NULL) AS fallback_operators
                          FROM {notifications_imeis_shard}
                    INNER JOIN monthly_network_triplets_per_mno network_triplets
                         USING (imei_norm)
                     LEFT JOIN LATERAL ({home_network_query}) home_network_tbl
                               ON TRUE
                         WHERE NOT EXISTS (SELECT 1
                                             FROM {pairing_list_shard}
                                            WHERE end_date IS NULL
                                              AND imei_norm = network_triplets.imei_norm
                                                  {notify_filter})
                           AND imei_norm IS NOT NULL
                           AND imsi IS NOT NULL
                           AND msisdn IS NOT NULL
                           AND last_seen >= %(lookback_start_date)s
                           AND first_seen < %(lookback_end_date)s
                           AND network_triplets.virt_imei_shard >= %(virt_imei_range_start)s
                           AND network_triplets.virt_imei_shard < %(virt_imei_range_end)s
                      GROUP BY imei_norm,
                               imsi,
                               msisdn
                """).format(notifications_triplets_shard=sql.Identifier(notifications_triplets_shard),  # noqa: Q447
                            notifications_imeis_shard=sql.Identifier(notifications_imeis_shard),
                            pairing_list_shard=sql.Identifier(pairing_list_shard),
                            home_network_query=self._home_network_query,
                            notify_filter=imsi_change_filter)

            lookback_end_date = compute_analysis_end_date(conn, self._curr_date)
            lookback_start_date = lookback_end_date - datetime.timedelta(days=self._lookback_days)
            logger = logging.getLogger('dirbs.listgen')
            log_analysis_window(logger, lookback_start_date, lookback_end_date,
                                start_message='Notifications lists using lookback window')
            cursor.execute(query, {'lookback_start_date': lookback_start_date,
                                   'lookback_end_date': lookback_end_date,
                                   'virt_imei_range_start': virt_imei_range_start,
                                   'virt_imei_range_end': virt_imei_range_end})
            num_records = cursor.rowcount
            self._add_pk(conn, tblname=notifications_triplets_shard, pk_columns=['imei_norm', 'imsi', 'msisdn'])

        return num_records, cp.duration
Function to create and generate the new table of unique IMEI/IMSI/MSISDN notifications triplets.
Function to create and generate the new table of unique IMEI/IMSI/MSISDN notifications triplets.
Function to create and generate the new table of unique IMEI / IMSI / MSISDN notifications triplets .
def _populate_new_notifications_triplets_single_shard(self, virt_imei_range_start, virt_imei_range_end, executor):
        with create_db_connection(self._config.db_config) as conn, conn.cursor() as cursor, CodeProfiler() as cp:
            notifications_imeis_shard = \
                partition_utils.imei_shard_name(base_name=self._notifications_imei_new_tblname,
                                                virt_imei_range_start=virt_imei_range_start,
                                                virt_imei_range_end=virt_imei_range_end)
            notifications_triplets_shard = \
                partition_utils.imei_shard_name(base_name=self._notifications_triplets_new_tblname,
                                                virt_imei_range_start=virt_imei_range_start,
                                                virt_imei_range_end=virt_imei_range_end)
            pairing_list_shard = partition_utils.imei_shard_name(base_name='historic_pairing_list',
                                                                 virt_imei_range_start=virt_imei_range_start,
                                                                 virt_imei_range_end=virt_imei_range_end)
             20170504: Re: discussion with Daniel it was decided to exclude triplets with a NULL IMSI or MSISDN
                       from notifications. Without an MSISDN there is no easy way for an operator to contact the
                       subscriber. Without an IMSI we can't determine accurately whether they were already paired.
                       We expect both NULL IMSI and MSISDN to be transient, weird events that do not consistently
                       happen for the same subscriber. Therefore they will be notified anyway based on the non-NULL
                       IMSI/MSISDN that is expected to be seen either on the same day or at some point during the
                       configured lookback window.
             check weather to notify the IMSI change or not, default behavior is to not however can be altered
            if self._notify_imsi_change:
                imsi_change_filter = sql.SQL('AND imsi = network_triplets.imsi')
            else:
                imsi_change_filter = sql.SQL('AND imsi = network_triplets.imsi OR msisdn = network_triplets.msisdn')
            query = sql.SQL(
                """INSERT INTO {notifications_triplets_shard}(imei_norm,
                                                              virt_imei_shard,
                                                              imsi,
                                                              msisdn,
                                                              block_date,
                                                              reasons,
                                                              is_valid,
                                                              amnesty_granted,
                                                              imei_norm_with_check_digit,
                                                              home_operator,
                                                              fallback_operators)
                        SELECT imei_norm,
                               FIRST(network_triplets.virt_imei_shard),
                               imsi,
                               msisdn,
                               FIRST(block_date),
                               FIRST(reasons),
                               FIRST(is_valid),
                               FIRST(amnesty_granted),
                               FIRST(imei_norm_with_check_digit),
                               FIRST(home_network_tbl.operator_id) AS home_operator,
                               array_agg(DISTINCT network_triplets.operator_id)
                                         filter(WHERE network_triplets.operator_id IS NOT NULL) AS fallback_operators
                          FROM {notifications_imeis_shard}
                    INNER JOIN monthly_network_triplets_per_mno network_triplets
                         USING (imei_norm)
                     LEFT JOIN LATERAL ({home_network_query}) home_network_tbl
                               ON TRUE
                         WHERE NOT EXISTS (SELECT 1
                                             FROM {pairing_list_shard}
                                            WHERE end_date IS NULL
                                              AND imei_norm = network_triplets.imei_norm
                                                  {notify_filter})
                           AND imei_norm IS NOT NULL
                           AND imsi IS NOT NULL
                           AND msisdn IS NOT NULL
                           AND last_seen >= %(lookback_start_date)s
                           AND first_seen < %(lookback_end_date)s
                           AND network_triplets.virt_imei_shard >= %(virt_imei_range_start)s
                           AND network_triplets.virt_imei_shard < %(virt_imei_range_end)s
                      GROUP BY imei_norm,
                               imsi,
                               msisdn
                """).format(notifications_triplets_shard=sql.Identifier(notifications_triplets_shard),   noqa: Q447
                            notifications_imeis_shard=sql.Identifier(notifications_imeis_shard),
                            pairing_list_shard=sql.Identifier(pairing_list_shard),
                            home_network_query=self._home_network_query,
                            notify_filter=imsi_change_filter)
            lookback_end_date = compute_analysis_end_date(conn, self._curr_date)
            lookback_start_date = lookback_end_date - datetime.timedelta(days=self._lookback_days)
            logger = logging.getLogger('dirbs.listgen')
            log_analysis_window(logger, lookback_start_date, lookback_end_date,
                                start_message='Notifications lists using lookback window')
            cursor.execute(query, {'lookback_start_date': lookback_start_date,
                                   'lookback_end_date': lookback_end_date,
                                   'virt_imei_range_start': virt_imei_range_start,
                                   'virt_imei_range_end': virt_imei_range_end})
            num_records = cursor.rowcount
            self._add_pk(conn, tblname=notifications_triplets_shard, pk_columns=['imei_norm', 'imsi', 'msisdn'])
        return num_records, cp.duration
def _populate_new_notifications_triplets_single_shard ( self , virt_imei_range_start , virt_imei_range_end , executor ) : with create_db_connection ( self . _config . db_config ) as conn , conn . cursor ( ) as cursor , CodeProfiler ( ) as cp : notifications_imeis_shard = partition_utils . imei_shard_name ( base_name = self . _notifications_imei_new_tblname , virt_imei_range_start = virt_imei_range_start , virt_imei_range_end = virt_imei_range_end ) notifications_triplets_shard = partition_utils . imei_shard_name ( base_name = self . _notifications_triplets_new_tblname , virt_imei_range_start = virt_imei_range_start , virt_imei_range_end = virt_imei_range_end ) pairing_list_shard = partition_utils . imei_shard_name ( base_name = 'historic_pairing_list' , virt_imei_range_start = virt_imei_range_start , virt_imei_range_end = virt_imei_range_end ) if self . _notify_imsi_change : imsi_change_filter = sql . SQL ( 'AND imsi = network_triplets.imsi' ) else : imsi_change_filter = sql . SQL ( 'AND imsi = network_triplets.imsi OR msisdn = network_triplets.msisdn' ) query = sql . SQL ( """INSERT INTO {notifications_triplets_shard}(imei_norm,
                                                              virt_imei_shard,
                                                              imsi,
                                                              msisdn,
                                                              block_date,
                                                              reasons,
                                                              is_valid,
                                                              amnesty_granted,
                                                              imei_norm_with_check_digit,
                                                              home_operator,
                                                              fallback_operators)
                        SELECT imei_norm,
                               FIRST(network_triplets.virt_imei_shard),
                               imsi,
                               msisdn,
                               FIRST(block_date),
                               FIRST(reasons),
                               FIRST(is_valid),
                               FIRST(amnesty_granted),
                               FIRST(imei_norm_with_check_digit),
                               FIRST(home_network_tbl.operator_id) AS home_operator,
                               array_agg(DISTINCT network_triplets.operator_id)
                                         filter(WHERE network_triplets.operator_id IS NOT NULL) AS fallback_operators
                          FROM {notifications_imeis_shard}
                    INNER JOIN monthly_network_triplets_per_mno network_triplets
                         USING (imei_norm)
                     LEFT JOIN LATERAL ({home_network_query}) home_network_tbl
                               ON TRUE
                         WHERE NOT EXISTS (SELECT 1
                                             FROM {pairing_list_shard}
                                            WHERE end_date IS NULL
                                              AND imei_norm = network_triplets.imei_norm
                                                  {notify_filter})
                           AND imei_norm IS NOT NULL
                           AND imsi IS NOT NULL
                           AND msisdn IS NOT NULL
                           AND last_seen >= %(lookback_start_date)s
                           AND first_seen < %(lookback_end_date)s
                           AND network_triplets.virt_imei_shard >= %(virt_imei_range_start)s
                           AND network_triplets.virt_imei_shard < %(virt_imei_range_end)s
                      GROUP BY imei_norm,
                               imsi,
                               msisdn
                """ ) . format ( notifications_triplets_shard = sql . Identifier ( notifications_triplets_shard ) , notifications_imeis_shard = sql . Identifier ( notifications_imeis_shard ) , pairing_list_shard = sql . Identifier ( pairing_list_shard ) , home_network_query = self . _home_network_query , notify_filter = imsi_change_filter ) lookback_end_date = compute_analysis_end_date ( conn , self . _curr_date ) lookback_start_date = lookback_end_date - datetime . timedelta ( days = self . _lookback_days ) logger = logging . getLogger ( 'dirbs.listgen' ) log_analysis_window ( logger , lookback_start_date , lookback_end_date , start_message = 'Notifications lists using lookback window' ) cursor . execute ( query , { 'lookback_start_date' : lookback_start_date , 'lookback_end_date' : lookback_end_date , 'virt_imei_range_start' : virt_imei_range_start , 'virt_imei_range_end' : virt_imei_range_end } ) num_records = cursor . rowcount self . _add_pk ( conn , tblname = notifications_triplets_shard , pk_columns = [ 'imei_norm' , 'imsi' , 'msisdn' ] ) return num_records , cp . duration
Function to create and generate the new table of unique IMEI/IMSI/MSISDN notifications triplets.
Function to create and generate the new table of unique IMEI / IMSI / MSISDN notifications triplets .
"""Function to create and generate the new table of unique IMEI/IMSI/MSISDN notifications triplets.""" # Note: IMSI can't be NULL in pairing list as it is constrained in table DDL # # 20170504: Re: discussion with Daniel it was decided to exclude triplets with a NULL IMSI or MSISDN #           from notifications. Without an MSISDN there is no easy way for an operator to contact the #           subscriber. Without an IMSI we can't determine accurately whether they were already paired. #           We expect both NULL IMSI and MSISDN to be transient, weird events that do not consistently #           happen for the same subscriber. Therefore they will be notified anyway based on the non-NULL #           IMSI/MSISDN that is expected to be seen either on the same day or at some point during the #           configured lookback window. # check weather to notify the IMSI change or not, default behavior is to not however can be altered # noqa: Q447
self
virt_imei_range_start
virt_imei_range_end
executor