src/dirbs/listgen/generator.py
def _populate_new_blacklist_or_notifications_imei_table(self, conn, tblname, *, is_blacklist):
        """Helper function to DRY out populating either an IMEI table for blacklist or notifications."""
        is_valid_query, imei_norm_with_check_digit_query = self._is_valid_and_check_digit_queries

        if is_blacklist:
            block_date_filter = sql.SQL('block_date <= %s')
            exclude_blacklisted_imeis_query = sql.SQL('')
            include_amnesty_column = sql.SQL('')

        else:
            block_date_filter = sql.SQL('block_date > %s')
            exclude_blacklisted_imeis_query = \
                sql.SQL("""AND NOT EXISTS (SELECT 1
                                             FROM {blacklist_tblname}
                                            WHERE imei_norm = cs.imei_norm)""").format(
                    blacklist_tblname=sql.Identifier(self._blacklist_new_tblname))
            include_amnesty_column = sql.SQL(', amnesty_granted')

        # Populate table
        query = sql.SQL("""INSERT INTO {tblname}(imei_norm,
                                                 virt_imei_shard,
                                                 block_date,
                                                 reasons,
                                                 is_valid,
                                                 imei_norm_with_check_digit
                                                 {include_amnesty_column})
                                SELECT imei_norm,
                                       virt_imei_shard,
                                       min_block_date,
                                       reasons,
                                       is_valid,
                                       imei_norm_with_check_digit
                                       {include_amnesty_column}
                                  FROM (SELECT imei_norm,
                                               FIRST(virt_imei_shard) AS virt_imei_shard,
                                               bool_and(amnesty_granted) AS amnesty_granted,
                                               MIN(block_date) AS min_block_date,
                                               array_agg(DISTINCT reason ORDER BY reason) AS reasons
                                          FROM (SELECT *
                                                  FROM classification_state
                                                  JOIN {blocking_conditions_tblname}
                                                 USING (cond_name)
                                                 WHERE end_date IS NULL
                                                   AND block_date IS NOT NULL
                                                   AND {block_date_filter}) cs
                                         WHERE NOT EXISTS(SELECT 1
                                                            FROM golden_list gl
                                                           WHERE hashed_imei_norm = md5(cs.imei_norm)::UUID)
                                               {exclude_blacklisted_imeis_query}
                                      GROUP BY imei_norm) bl_imeis,
                                       LATERAL ({is_valid_query}) is_valid_tbl,
                                       LATERAL ({imei_norm_with_check_digit_query}) check_digit_tbl
                        """).format(tblname=sql.Identifier(tblname),  # noqa: Q447
                                    include_amnesty_column=include_amnesty_column,
                                    is_valid_query=is_valid_query,
                                    imei_norm_with_check_digit_query=imei_norm_with_check_digit_query,
                                    blocking_conditions_tblname=sql.Identifier(self._blocking_conditions_new_tblname),
                                    block_date_filter=block_date_filter,
                                    exclude_blacklisted_imeis_query=exclude_blacklisted_imeis_query)

        # Get current date for the purposes of blacklisting. Most of the time, this will be NULL and use the date
        # of the run
        if self._curr_date is not None:
            curr_date = self._curr_date
        else:
            # Query for metadata date for this run_id
            job_start_time = metadata.job_start_time_by_run_id(self._metadata_conn, self._run_id)
            assert job_start_time is not None
            curr_date = job_start_time.date()

        with conn.cursor() as cursor:
            cursor.execute(query, [curr_date])
            num_records = cursor.rowcount
            self._add_pk(conn, tblname=tblname, pk_columns=['imei_norm'])
            self._analyze_helper(cursor, tblname)
            return num_records
Helper function to DRY out populating either an IMEI table for blacklist or notifications.
Helper function to DRY out populating either an IMEI table for blacklist or notifications.
Helper function to DRY out populating either an IMEI table for blacklist or notifications .
def _populate_new_blacklist_or_notifications_imei_table(self, conn, tblname, *, is_blacklist):
        is_valid_query, imei_norm_with_check_digit_query = self._is_valid_and_check_digit_queries
        if is_blacklist:
            block_date_filter = sql.SQL('block_date <= %s')
            exclude_blacklisted_imeis_query = sql.SQL('')
            include_amnesty_column = sql.SQL('')
        else:
            block_date_filter = sql.SQL('block_date > %s')
            exclude_blacklisted_imeis_query = \
                sql.SQL("""AND NOT EXISTS (SELECT 1
                                             FROM {blacklist_tblname}
                                            WHERE imei_norm = cs.imei_norm)""").format(
                    blacklist_tblname=sql.Identifier(self._blacklist_new_tblname))
            include_amnesty_column = sql.SQL(', amnesty_granted')
        query = sql.SQL("""INSERT INTO {tblname}(imei_norm,
                                                 virt_imei_shard,
                                                 block_date,
                                                 reasons,
                                                 is_valid,
                                                 imei_norm_with_check_digit
                                                 {include_amnesty_column})
                                SELECT imei_norm,
                                       virt_imei_shard,
                                       min_block_date,
                                       reasons,
                                       is_valid,
                                       imei_norm_with_check_digit
                                       {include_amnesty_column}
                                  FROM (SELECT imei_norm,
                                               FIRST(virt_imei_shard) AS virt_imei_shard,
                                               bool_and(amnesty_granted) AS amnesty_granted,
                                               MIN(block_date) AS min_block_date,
                                               array_agg(DISTINCT reason ORDER BY reason) AS reasons
                                          FROM (SELECT *
                                                  FROM classification_state
                                                  JOIN {blocking_conditions_tblname}
                                                 USING (cond_name)
                                                 WHERE end_date IS NULL
                                                   AND block_date IS NOT NULL
                                                   AND {block_date_filter}) cs
                                         WHERE NOT EXISTS(SELECT 1
                                                            FROM golden_list gl
                                                           WHERE hashed_imei_norm = md5(cs.imei_norm)::UUID)
                                               {exclude_blacklisted_imeis_query}
                                      GROUP BY imei_norm) bl_imeis,
                                       LATERAL ({is_valid_query}) is_valid_tbl,
                                       LATERAL ({imei_norm_with_check_digit_query}) check_digit_tbl
                        """).format(tblname=sql.Identifier(tblname),  
                                    include_amnesty_column=include_amnesty_column,
                                    is_valid_query=is_valid_query,
                                    imei_norm_with_check_digit_query=imei_norm_with_check_digit_query,
                                    blocking_conditions_tblname=sql.Identifier(self._blocking_conditions_new_tblname),
                                    block_date_filter=block_date_filter,
                                    exclude_blacklisted_imeis_query=exclude_blacklisted_imeis_query)
        if self._curr_date is not None:
            curr_date = self._curr_date
        else:
            job_start_time = metadata.job_start_time_by_run_id(self._metadata_conn, self._run_id)
            assert job_start_time is not None
            curr_date = job_start_time.date()
        with conn.cursor() as cursor:
            cursor.execute(query, [curr_date])
            num_records = cursor.rowcount
            self._add_pk(conn, tblname=tblname, pk_columns=['imei_norm'])
            self._analyze_helper(cursor, tblname)
            return num_records
def _populate_new_blacklist_or_notifications_imei_table ( self , conn , tblname , * , is_blacklist ) : is_valid_query , imei_norm_with_check_digit_query = self . _is_valid_and_check_digit_queries if is_blacklist : block_date_filter = sql . SQL ( 'block_date <= %s' ) exclude_blacklisted_imeis_query = sql . SQL ( '' ) include_amnesty_column = sql . SQL ( '' ) else : block_date_filter = sql . SQL ( 'block_date > %s' ) exclude_blacklisted_imeis_query = sql . SQL ( """AND NOT EXISTS (SELECT 1
                                             FROM {blacklist_tblname}
                                            WHERE imei_norm = cs.imei_norm)""" ) . format ( blacklist_tblname = sql . Identifier ( self . _blacklist_new_tblname ) ) include_amnesty_column = sql . SQL ( ', amnesty_granted' ) query = sql . SQL ( """INSERT INTO {tblname}(imei_norm,
                                                 virt_imei_shard,
                                                 block_date,
                                                 reasons,
                                                 is_valid,
                                                 imei_norm_with_check_digit
                                                 {include_amnesty_column})
                                SELECT imei_norm,
                                       virt_imei_shard,
                                       min_block_date,
                                       reasons,
                                       is_valid,
                                       imei_norm_with_check_digit
                                       {include_amnesty_column}
                                  FROM (SELECT imei_norm,
                                               FIRST(virt_imei_shard) AS virt_imei_shard,
                                               bool_and(amnesty_granted) AS amnesty_granted,
                                               MIN(block_date) AS min_block_date,
                                               array_agg(DISTINCT reason ORDER BY reason) AS reasons
                                          FROM (SELECT *
                                                  FROM classification_state
                                                  JOIN {blocking_conditions_tblname}
                                                 USING (cond_name)
                                                 WHERE end_date IS NULL
                                                   AND block_date IS NOT NULL
                                                   AND {block_date_filter}) cs
                                         WHERE NOT EXISTS(SELECT 1
                                                            FROM golden_list gl
                                                           WHERE hashed_imei_norm = md5(cs.imei_norm)::UUID)
                                               {exclude_blacklisted_imeis_query}
                                      GROUP BY imei_norm) bl_imeis,
                                       LATERAL ({is_valid_query}) is_valid_tbl,
                                       LATERAL ({imei_norm_with_check_digit_query}) check_digit_tbl
                        """ ) . format ( tblname = sql . Identifier ( tblname ) , include_amnesty_column = include_amnesty_column , is_valid_query = is_valid_query , imei_norm_with_check_digit_query = imei_norm_with_check_digit_query , blocking_conditions_tblname = sql . Identifier ( self . _blocking_conditions_new_tblname ) , block_date_filter = block_date_filter , exclude_blacklisted_imeis_query = exclude_blacklisted_imeis_query ) if self . _curr_date is not None : curr_date = self . _curr_date else : job_start_time = metadata . job_start_time_by_run_id ( self . _metadata_conn , self . _run_id ) assert job_start_time is not None curr_date = job_start_time . date ( ) with conn . cursor ( ) as cursor : cursor . execute ( query , [ curr_date ] ) num_records = cursor . rowcount self . _add_pk ( conn , tblname = tblname , pk_columns = [ 'imei_norm' ] ) self . _analyze_helper ( cursor , tblname ) return num_records
Helper function to DRY out populating either an IMEI table for blacklist or notifications.
Helper function to DRY out populating either an IMEI table for blacklist or notifications .
"""Helper function to DRY out populating either an IMEI table for blacklist or notifications.""" # Populate table # noqa: Q447 # Get current date for the purposes of blacklisting. Most of the time, this will be NULL and use the date # of the run # Query for metadata date for this run_id
self
conn
tblname
is_blacklist