src/dirbs/listgen/generator.py
def _populate_delta_notifications_list(self, operator_id, executor):
        """Function to populate the old notifications list for a given operator id."""
        with create_db_connection(self._config.db_config) as conn, conn.cursor() as cursor, CodeProfiler() as cp:
            delta_tbl_name = self._notifications_lists_delta_part_tblname(operator_id)
            delta_tbl = sql.Identifier(delta_tbl_name)
            old_tbl = sql.Identifier(self._notifications_lists_old_part_tblname(operator_id))
            new_tbl = sql.Identifier(self._notifications_lists_new_part_tblname(operator_id))
            # First, generate a delta for any 'new' triplets (new adds)
            cursor.execute(sql.SQL("""INSERT INTO {delta_tbl}(operator_id,
                                                              imei_norm,
                                                              virt_imei_shard,
                                                              imsi,
                                                              msisdn,
                                                              block_date,
                                                              reasons,
                                                              amnesty_granted,
                                                              start_run_id,
                                                              end_run_id,
                                                              delta_reason)
                                           SELECT %s, imei_norm, virt_imei_shard, imsi, msisdn, block_date, reasons,
                                                  amnesty_granted, %s, NULL, 'new'
                                             FROM {new_tbl} nt
                                            WHERE NOT EXISTS(SELECT 1
                                                               FROM {old_tbl}
                                                              WHERE imei_norm = nt.imei_norm
                                                                AND virt_imei_shard = nt.virt_imei_shard
                                                                AND imsi = nt.imsi
                                                                AND msisdn = nt.msisdn)
                                   """).format(delta_tbl=delta_tbl, old_tbl=old_tbl, new_tbl=new_tbl),  # noqa: Q449
                           [operator_id, self._run_id])
            num_records = cursor.rowcount

            # weather to notify the IMSI change or not, default behaviour is to not however it can be
            # altered in the config file variable notify_imsi_change
            if self._notify_imsi_change:
                imsi_change_filter = sql.SQL('AND imsi = ot.imsi')
            else:
                imsi_change_filter = sql.SQL('AND imsi = ot.imsi OR msisdn = ot.msisdn')

            # Next, generate a delta for any 'resolved' and 'blacklisted' triplets (new removals)
            blacklist_tbl = sql.Identifier(self._blacklist_new_tblname)
            notifications_imei_tbl = sql.Identifier(self._notifications_imei_new_tblname)
            pairings_tbl = sql.Identifier(self._pairings_imei_imsi_new_tblname)
            cursor.execute(sql.SQL(
                """INSERT INTO {delta_tbl}(operator_id,
                                           imei_norm,
                                           virt_imei_shard,
                                           imsi,
                                           msisdn,
                                           block_date,
                                           reasons,
                                           amnesty_granted,
                                           start_run_id,
                                           end_run_id,
                                           delta_reason)
                        SELECT %s,
                               imei_norm,
                               virt_imei_shard,
                               imsi,
                               msisdn,
                               block_date,
                               reasons,
                               amnesty_granted,
                               %s,
                               NULL,
                               CASE WHEN is_paired THEN 'resolved' -- If paired, definitely resolved
                                    WHEN is_blocked THEN 'blacklisted' -- If blacklisted, definitely blacklisted
                                    WHEN is_notified THEN 'no_longer_seen' -- If IMEI still notified, no longer seen
                                    ELSE 'resolved' -- If IMEI no longer notified, is resolved
                               END
                          FROM {old_tbl} ot,
                               LATERAL (SELECT EXISTS (SELECT 1
                                                         FROM {blacklist_tbl}
                                                        WHERE imei_norm = ot.imei_norm
                                                          AND virt_imei_shard = ot.virt_imei_shard) AS is_blocked
                                                      ) blocked_tbl,
                               LATERAL (SELECT EXISTS (SELECT 1
                                                         FROM {notifications_imei_tbl}
                                                        WHERE imei_norm = ot.imei_norm
                                                          AND virt_imei_shard = ot.virt_imei_shard) AS is_notified
                                                      ) notify_tbl,
                               LATERAL (SELECT EXISTS (SELECT 1
                                                         FROM {pairings_tbl}
                                                        WHERE imei_norm = ot.imei_norm
                                                          AND virt_imei_shard = ot.virt_imei_shard
                                                              {notify_filter}) AS is_paired) pairing_tbl
                         WHERE NOT EXISTS(SELECT 1
                                            FROM {new_tbl}
                                           WHERE imei_norm = ot.imei_norm
                                             AND virt_imei_shard = ot.virt_imei_shard
                                             AND imsi = ot.imsi
                                             AND msisdn = ot.msisdn)
                                   """).format(delta_tbl=delta_tbl, old_tbl=old_tbl,   # noqa: Q447, Q449
                                               new_tbl=new_tbl,
                                               blacklist_tbl=blacklist_tbl,
                                               notifications_imei_tbl=notifications_imei_tbl,
                                               pairings_tbl=pairings_tbl,
                                               notify_filter=imsi_change_filter),
                [operator_id, self._run_id])
            num_records += cursor.rowcount
            # Finally, generate a delta for any triplets where the reasons/date have changed
            cursor.execute(sql.SQL("""INSERT INTO {delta_tbl}(operator_id,
                                                              imei_norm,
                                                              virt_imei_shard,
                                                              imsi,
                                                              msisdn,
                                                              block_date,
                                                              reasons,
                                                              amnesty_granted,
                                                              start_run_id,
                                                              end_run_id,
                                                              delta_reason)
                                           SELECT %s,
                                                  nt.imei_norm,
                                                  nt.virt_imei_shard,
                                                  nt.imsi,
                                                  nt.msisdn,
                                                  nt.block_date,
                                                  nt.reasons,
                                                  nt.amnesty_granted, %s,
                                                  NULL,
                                                  'changed'
                                             FROM {new_tbl} nt
                                             JOIN {old_tbl} ot
                                            USING (imei_norm, imsi, msisdn)
                                            WHERE ot.block_date <> nt.block_date
                                               OR ot.reasons <> nt.reasons
                                   """).format(delta_tbl=delta_tbl, old_tbl=old_tbl, new_tbl=new_tbl),
                           [operator_id, self._run_id])
            num_records += cursor.rowcount

        return num_records, cp.duration
Function to populate the old notifications list for a given operator id.
Function to populate the old notifications list for a given operator id.
Function to populate the old notifications list for a given operator id .
def _populate_delta_notifications_list(self, operator_id, executor):
        with create_db_connection(self._config.db_config) as conn, conn.cursor() as cursor, CodeProfiler() as cp:
            delta_tbl_name = self._notifications_lists_delta_part_tblname(operator_id)
            delta_tbl = sql.Identifier(delta_tbl_name)
            old_tbl = sql.Identifier(self._notifications_lists_old_part_tblname(operator_id))
            new_tbl = sql.Identifier(self._notifications_lists_new_part_tblname(operator_id))
            cursor.execute(sql.SQL("""INSERT INTO {delta_tbl}(operator_id,
                                                              imei_norm,
                                                              virt_imei_shard,
                                                              imsi,
                                                              msisdn,
                                                              block_date,
                                                              reasons,
                                                              amnesty_granted,
                                                              start_run_id,
                                                              end_run_id,
                                                              delta_reason)
                                           SELECT %s, imei_norm, virt_imei_shard, imsi, msisdn, block_date, reasons,
                                                  amnesty_granted, %s, NULL, 'new'
                                             FROM {new_tbl} nt
                                            WHERE NOT EXISTS(SELECT 1
                                                               FROM {old_tbl}
                                                              WHERE imei_norm = nt.imei_norm
                                                                AND virt_imei_shard = nt.virt_imei_shard
                                                                AND imsi = nt.imsi
                                                                AND msisdn = nt.msisdn)
                                   """).format(delta_tbl=delta_tbl, old_tbl=old_tbl, new_tbl=new_tbl),  
                           [operator_id, self._run_id])
            num_records = cursor.rowcount
            if self._notify_imsi_change:
                imsi_change_filter = sql.SQL('AND imsi = ot.imsi')
            else:
                imsi_change_filter = sql.SQL('AND imsi = ot.imsi OR msisdn = ot.msisdn')
            blacklist_tbl = sql.Identifier(self._blacklist_new_tblname)
            notifications_imei_tbl = sql.Identifier(self._notifications_imei_new_tblname)
            pairings_tbl = sql.Identifier(self._pairings_imei_imsi_new_tblname)
            cursor.execute(sql.SQL(
                """INSERT INTO {delta_tbl}(operator_id,
                                           imei_norm,
                                           virt_imei_shard,
                                           imsi,
                                           msisdn,
                                           block_date,
                                           reasons,
                                           amnesty_granted,
                                           start_run_id,
                                           end_run_id,
                                           delta_reason)
                        SELECT %s,
                               imei_norm,
                               virt_imei_shard,
                               imsi,
                               msisdn,
                               block_date,
                               reasons,
                               amnesty_granted,
                               %s,
                               NULL,
                               CASE WHEN is_paired THEN 'resolved' -- If paired, definitely resolved
                                    WHEN is_blocked THEN 'blacklisted' -- If blacklisted, definitely blacklisted
                                    WHEN is_notified THEN 'no_longer_seen' -- If IMEI still notified, no longer seen
                                    ELSE 'resolved' -- If IMEI no longer notified, is resolved
                               END
                          FROM {old_tbl} ot,
                               LATERAL (SELECT EXISTS (SELECT 1
                                                         FROM {blacklist_tbl}
                                                        WHERE imei_norm = ot.imei_norm
                                                          AND virt_imei_shard = ot.virt_imei_shard) AS is_blocked
                                                      ) blocked_tbl,
                               LATERAL (SELECT EXISTS (SELECT 1
                                                         FROM {notifications_imei_tbl}
                                                        WHERE imei_norm = ot.imei_norm
                                                          AND virt_imei_shard = ot.virt_imei_shard) AS is_notified
                                                      ) notify_tbl,
                               LATERAL (SELECT EXISTS (SELECT 1
                                                         FROM {pairings_tbl}
                                                        WHERE imei_norm = ot.imei_norm
                                                          AND virt_imei_shard = ot.virt_imei_shard
                                                              {notify_filter}) AS is_paired) pairing_tbl
                         WHERE NOT EXISTS(SELECT 1
                                            FROM {new_tbl}
                                           WHERE imei_norm = ot.imei_norm
                                             AND virt_imei_shard = ot.virt_imei_shard
                                             AND imsi = ot.imsi
                                             AND msisdn = ot.msisdn)
                                   """).format(delta_tbl=delta_tbl, old_tbl=old_tbl,   
                                               new_tbl=new_tbl,
                                               blacklist_tbl=blacklist_tbl,
                                               notifications_imei_tbl=notifications_imei_tbl,
                                               pairings_tbl=pairings_tbl,
                                               notify_filter=imsi_change_filter),
                [operator_id, self._run_id])
            num_records += cursor.rowcount
            cursor.execute(sql.SQL("""INSERT INTO {delta_tbl}(operator_id,
                                                              imei_norm,
                                                              virt_imei_shard,
                                                              imsi,
                                                              msisdn,
                                                              block_date,
                                                              reasons,
                                                              amnesty_granted,
                                                              start_run_id,
                                                              end_run_id,
                                                              delta_reason)
                                           SELECT %s,
                                                  nt.imei_norm,
                                                  nt.virt_imei_shard,
                                                  nt.imsi,
                                                  nt.msisdn,
                                                  nt.block_date,
                                                  nt.reasons,
                                                  nt.amnesty_granted, %s,
                                                  NULL,
                                                  'changed'
                                             FROM {new_tbl} nt
                                             JOIN {old_tbl} ot
                                            USING (imei_norm, imsi, msisdn)
                                            WHERE ot.block_date <> nt.block_date
                                               OR ot.reasons <> nt.reasons
                                   """).format(delta_tbl=delta_tbl, old_tbl=old_tbl, new_tbl=new_tbl),
                           [operator_id, self._run_id])
            num_records += cursor.rowcount
        return num_records, cp.duration
def _populate_delta_notifications_list ( self , operator_id , executor ) : with create_db_connection ( self . _config . db_config ) as conn , conn . cursor ( ) as cursor , CodeProfiler ( ) as cp : delta_tbl_name = self . _notifications_lists_delta_part_tblname ( operator_id ) delta_tbl = sql . Identifier ( delta_tbl_name ) old_tbl = sql . Identifier ( self . _notifications_lists_old_part_tblname ( operator_id ) ) new_tbl = sql . Identifier ( self . _notifications_lists_new_part_tblname ( operator_id ) ) cursor . execute ( sql . SQL ( """INSERT INTO {delta_tbl}(operator_id,
                                                              imei_norm,
                                                              virt_imei_shard,
                                                              imsi,
                                                              msisdn,
                                                              block_date,
                                                              reasons,
                                                              amnesty_granted,
                                                              start_run_id,
                                                              end_run_id,
                                                              delta_reason)
                                           SELECT %s, imei_norm, virt_imei_shard, imsi, msisdn, block_date, reasons,
                                                  amnesty_granted, %s, NULL, 'new'
                                             FROM {new_tbl} nt
                                            WHERE NOT EXISTS(SELECT 1
                                                               FROM {old_tbl}
                                                              WHERE imei_norm = nt.imei_norm
                                                                AND virt_imei_shard = nt.virt_imei_shard
                                                                AND imsi = nt.imsi
                                                                AND msisdn = nt.msisdn)
                                   """ ) . format ( delta_tbl = delta_tbl , old_tbl = old_tbl , new_tbl = new_tbl ) , [ operator_id , self . _run_id ] ) num_records = cursor . rowcount if self . _notify_imsi_change : imsi_change_filter = sql . SQL ( 'AND imsi = ot.imsi' ) else : imsi_change_filter = sql . SQL ( 'AND imsi = ot.imsi OR msisdn = ot.msisdn' ) blacklist_tbl = sql . Identifier ( self . _blacklist_new_tblname ) notifications_imei_tbl = sql . Identifier ( self . _notifications_imei_new_tblname ) pairings_tbl = sql . Identifier ( self . _pairings_imei_imsi_new_tblname ) cursor . execute ( sql . SQL ( """INSERT INTO {delta_tbl}(operator_id,
                                           imei_norm,
                                           virt_imei_shard,
                                           imsi,
                                           msisdn,
                                           block_date,
                                           reasons,
                                           amnesty_granted,
                                           start_run_id,
                                           end_run_id,
                                           delta_reason)
                        SELECT %s,
                               imei_norm,
                               virt_imei_shard,
                               imsi,
                               msisdn,
                               block_date,
                               reasons,
                               amnesty_granted,
                               %s,
                               NULL,
                               CASE WHEN is_paired THEN 'resolved' -- If paired, definitely resolved
                                    WHEN is_blocked THEN 'blacklisted' -- If blacklisted, definitely blacklisted
                                    WHEN is_notified THEN 'no_longer_seen' -- If IMEI still notified, no longer seen
                                    ELSE 'resolved' -- If IMEI no longer notified, is resolved
                               END
                          FROM {old_tbl} ot,
                               LATERAL (SELECT EXISTS (SELECT 1
                                                         FROM {blacklist_tbl}
                                                        WHERE imei_norm = ot.imei_norm
                                                          AND virt_imei_shard = ot.virt_imei_shard) AS is_blocked
                                                      ) blocked_tbl,
                               LATERAL (SELECT EXISTS (SELECT 1
                                                         FROM {notifications_imei_tbl}
                                                        WHERE imei_norm = ot.imei_norm
                                                          AND virt_imei_shard = ot.virt_imei_shard) AS is_notified
                                                      ) notify_tbl,
                               LATERAL (SELECT EXISTS (SELECT 1
                                                         FROM {pairings_tbl}
                                                        WHERE imei_norm = ot.imei_norm
                                                          AND virt_imei_shard = ot.virt_imei_shard
                                                              {notify_filter}) AS is_paired) pairing_tbl
                         WHERE NOT EXISTS(SELECT 1
                                            FROM {new_tbl}
                                           WHERE imei_norm = ot.imei_norm
                                             AND virt_imei_shard = ot.virt_imei_shard
                                             AND imsi = ot.imsi
                                             AND msisdn = ot.msisdn)
                                   """ ) . format ( delta_tbl = delta_tbl , old_tbl = old_tbl , new_tbl = new_tbl , blacklist_tbl = blacklist_tbl , notifications_imei_tbl = notifications_imei_tbl , pairings_tbl = pairings_tbl , notify_filter = imsi_change_filter ) , [ operator_id , self . _run_id ] ) num_records += cursor . rowcount cursor . execute ( sql . SQL ( """INSERT INTO {delta_tbl}(operator_id,
                                                              imei_norm,
                                                              virt_imei_shard,
                                                              imsi,
                                                              msisdn,
                                                              block_date,
                                                              reasons,
                                                              amnesty_granted,
                                                              start_run_id,
                                                              end_run_id,
                                                              delta_reason)
                                           SELECT %s,
                                                  nt.imei_norm,
                                                  nt.virt_imei_shard,
                                                  nt.imsi,
                                                  nt.msisdn,
                                                  nt.block_date,
                                                  nt.reasons,
                                                  nt.amnesty_granted, %s,
                                                  NULL,
                                                  'changed'
                                             FROM {new_tbl} nt
                                             JOIN {old_tbl} ot
                                            USING (imei_norm, imsi, msisdn)
                                            WHERE ot.block_date <> nt.block_date
                                               OR ot.reasons <> nt.reasons
                                   """ ) . format ( delta_tbl = delta_tbl , old_tbl = old_tbl , new_tbl = new_tbl ) , [ operator_id , self . _run_id ] ) num_records += cursor . rowcount return num_records , cp . duration
Function to populate the old notifications list for a given operator id.
Function to populate the old notifications list for a given operator id .
"""Function to populate the old notifications list for a given operator id.""" # First, generate a delta for any 'new' triplets (new adds) # noqa: Q449 # weather to notify the IMSI change or not, default behaviour is to not however it can be # altered in the config file variable notify_imsi_change # Next, generate a delta for any 'resolved' and 'blacklisted' triplets (new removals) # noqa: Q447, Q449 # Finally, generate a delta for any triplets where the reasons/date have changed
self
operator_id
executor