src/dirbs/schema_migrators/v85_upgrade.py
def _migrate_device_association_list(self, logger, conn):
        """Method to migrate barred imeis list."""
        with conn.cursor() as cursor:
            cursor.execute(sql.SQL("""CREATE TABLE historic_device_association_list (
                                          imei_norm text NOT NULL,
                                          uid text NOT NULL,
                                          start_date TIMESTAMP NOT NULL,
                                          end_date TIMESTAMP,
                                          virt_imei_shard SMALLINT NOT NULL
                                    )
                                    PARTITION BY RANGE (virt_imei_shard);"""))

            num_shards = part_utils.num_physical_imei_shards(conn)
            logger.debug('Granting permissions to barred_list partitions...')
            part_utils._grant_perms_barred_list(conn, part_name='historic_device_association_list')
            logger.debug('Creating barred_list child partitions...')
            part_utils.create_imei_shard_partitions(conn, tbl_name='historic_device_association_list',
                                                    num_physical_shards=num_shards,
                                                    perms_func=part_utils._grant_perms_association_list,
                                                    fillfactor=80)

            # Add indexes to each partition
            idx_metadata = [part_utils.IndexMetadatum(idx_cols=['uid', 'imei_norm'],
                                                      is_unique=True,
                                                      partial_sql='WHERE end_date IS NULL')]
            part_utils.add_indices(conn, tbl_name='historic_device_association_list', idx_metadata=idx_metadata)

            # Creating view to historic_barred_list
            cursor.execute("""CREATE OR REPLACE VIEW device_association_list AS
                                   SELECT uid, imei_norm, virt_imei_shard
                                     FROM historic_device_association_list
                                    WHERE end_date IS NULL WITH CHECK OPTION""")  # noqa: Q440
            cursor.execute("""GRANT SELECT ON device_association_list
                                      TO dirbs_core_classify, dirbs_core_api,
                                      dirbs_core_import_device_association_list""")

            # Creating insert trigger function
            cursor.execute("""CREATE FUNCTION device_association_list_staging_data_insert_trigger_fn() RETURNS TRIGGER
                                  LANGUAGE plpgsql
                                  AS $$
                              BEGIN
                                  NEW.uid = NULLIF(TRIM(NEW.uid), '');
                                  NEW.imei_norm = normalize_imei(NULLIF(TRIM(NEW.imei), ''));
                                  RETURN NEW;
                              END
                              $$;

                              ALTER FUNCTION device_association_list_staging_data_insert_trigger_fn()
                                OWNER TO dirbs_core_power_user;
                            """)
            logger.debug('Granting create permission to dirbs_core_import_device_association_list...')
            cursor.execute('GRANT CREATE ON SCHEMA core TO dirbs_core_import_device_association_list')
Method to migrate barred imeis list.
Method to migrate barred imeis list.
Method to migrate barred imeis list .
def _migrate_device_association_list(self, logger, conn):
        with conn.cursor() as cursor:
            cursor.execute(sql.SQL("""CREATE TABLE historic_device_association_list (
                                          imei_norm text NOT NULL,
                                          uid text NOT NULL,
                                          start_date TIMESTAMP NOT NULL,
                                          end_date TIMESTAMP,
                                          virt_imei_shard SMALLINT NOT NULL
                                    )
                                    PARTITION BY RANGE (virt_imei_shard);"""))
            num_shards = part_utils.num_physical_imei_shards(conn)
            logger.debug('Granting permissions to barred_list partitions...')
            part_utils._grant_perms_barred_list(conn, part_name='historic_device_association_list')
            logger.debug('Creating barred_list child partitions...')
            part_utils.create_imei_shard_partitions(conn, tbl_name='historic_device_association_list',
                                                    num_physical_shards=num_shards,
                                                    perms_func=part_utils._grant_perms_association_list,
                                                    fillfactor=80)
            idx_metadata = [part_utils.IndexMetadatum(idx_cols=['uid', 'imei_norm'],
                                                      is_unique=True,
                                                      partial_sql='WHERE end_date IS NULL')]
            part_utils.add_indices(conn, tbl_name='historic_device_association_list', idx_metadata=idx_metadata)
            cursor.execute("""CREATE OR REPLACE VIEW device_association_list AS
                                   SELECT uid, imei_norm, virt_imei_shard
                                     FROM historic_device_association_list
                                    WHERE end_date IS NULL WITH CHECK OPTION""")  
            cursor.execute("""GRANT SELECT ON device_association_list
                                      TO dirbs_core_classify, dirbs_core_api,
                                      dirbs_core_import_device_association_list""")
            cursor.execute("""CREATE FUNCTION device_association_list_staging_data_insert_trigger_fn() RETURNS TRIGGER
                                  LANGUAGE plpgsql
                                  AS $$
                              BEGIN
                                  NEW.uid = NULLIF(TRIM(NEW.uid), '');
                                  NEW.imei_norm = normalize_imei(NULLIF(TRIM(NEW.imei), ''));
                                  RETURN NEW;
                              END
                              $$;
                              ALTER FUNCTION device_association_list_staging_data_insert_trigger_fn()
                                OWNER TO dirbs_core_power_user;
                            """)
            logger.debug('Granting create permission to dirbs_core_import_device_association_list...')
            cursor.execute('GRANT CREATE ON SCHEMA core TO dirbs_core_import_device_association_list')
def _migrate_device_association_list ( self , logger , conn ) : with conn . cursor ( ) as cursor : cursor . execute ( sql . SQL ( """CREATE TABLE historic_device_association_list (
                                          imei_norm text NOT NULL,
                                          uid text NOT NULL,
                                          start_date TIMESTAMP NOT NULL,
                                          end_date TIMESTAMP,
                                          virt_imei_shard SMALLINT NOT NULL
                                    )
                                    PARTITION BY RANGE (virt_imei_shard);""" ) ) num_shards = part_utils . num_physical_imei_shards ( conn ) logger . debug ( 'Granting permissions to barred_list partitions...' ) part_utils . _grant_perms_barred_list ( conn , part_name = 'historic_device_association_list' ) logger . debug ( 'Creating barred_list child partitions...' ) part_utils . create_imei_shard_partitions ( conn , tbl_name = 'historic_device_association_list' , num_physical_shards = num_shards , perms_func = part_utils . _grant_perms_association_list , fillfactor = 80 ) idx_metadata = [ part_utils . IndexMetadatum ( idx_cols = [ 'uid' , 'imei_norm' ] , is_unique = True , partial_sql = 'WHERE end_date IS NULL' ) ] part_utils . add_indices ( conn , tbl_name = 'historic_device_association_list' , idx_metadata = idx_metadata ) cursor . execute ( """CREATE OR REPLACE VIEW device_association_list AS
                                   SELECT uid, imei_norm, virt_imei_shard
                                     FROM historic_device_association_list
                                    WHERE end_date IS NULL WITH CHECK OPTION""" ) cursor . execute ( """GRANT SELECT ON device_association_list
                                      TO dirbs_core_classify, dirbs_core_api,
                                      dirbs_core_import_device_association_list""" ) cursor . execute ( """CREATE FUNCTION device_association_list_staging_data_insert_trigger_fn() RETURNS TRIGGER
                                  LANGUAGE plpgsql
                                  AS $$
                              BEGIN
                                  NEW.uid = NULLIF(TRIM(NEW.uid), '');
                                  NEW.imei_norm = normalize_imei(NULLIF(TRIM(NEW.imei), ''));
                                  RETURN NEW;
                              END
                              $$;

                              ALTER FUNCTION device_association_list_staging_data_insert_trigger_fn()
                                OWNER TO dirbs_core_power_user;
                            """ ) logger . debug ( 'Granting create permission to dirbs_core_import_device_association_list...' ) cursor . execute ( 'GRANT CREATE ON SCHEMA core TO dirbs_core_import_device_association_list' )
Method to migrate barred imeis list.
Method to migrate barred imeis list .
"""Method to migrate barred imeis list.""" # Add indexes to each partition # Creating view to historic_barred_list # noqa: Q440 # Creating insert trigger function
self
logger
conn