def queue_calc_imeis_jobs(self, executor, app_config, run_id, curr_date):
"""
Method to queue jobs to calculate the IMEIs that are met by this condition.
Arguments:
executor: instance of the python executor class, to submit back the results
app_config: dirbs app current configuration, to extract various configs required for the job
run_id: run id of the current classification job
curr_date: current date of the system
"""
with create_db_connection(app_config.db_config) as conn, conn.cursor() as cursor:
cursor.execute(sql.SQL("""CREATE UNLOGGED TABLE {intermediate_tbl} (
imei_norm TEXT NOT NULL,
virt_imei_shard SMALLINT NOT NULL
)
PARTITION BY RANGE (virt_imei_shard)""")
.format(intermediate_tbl=self.intermediate_tbl_id(run_id)))
partition_utils.create_imei_shard_partitions(conn, tbl_name=self.intermediate_tbl_name(run_id),
unlogged=True)
parallel_shards = partition_utils.num_physical_imei_shards(conn)
# Done with connection -- temp tables should now be committed
virt_imei_shard_ranges = partition_utils.virt_imei_shard_bounds(parallel_shards)
for virt_imei_range_start, virt_imei_range_end in virt_imei_shard_ranges:
yield executor.submit(self._calc_imeis_job,
app_config,
run_id,
curr_date,
virt_imei_range_start,
virt_imei_range_end)
def partition_registration_list(self, conn, *, num_physical_shards):
"""Method to repartition registration_list for v47 upgrade."""
with conn.cursor() as cursor, utils.db_role_setter(
conn, role_name='dirbs_core_power_user'):
# Create parent partition
cursor.execute("""CREATE TABLE historic_registration_list_new (
LIKE historic_registration_list INCLUDING DEFAULTS
INCLUDING IDENTITY
INCLUDING CONSTRAINTS
INCLUDING STORAGE
INCLUDING COMMENTS
)
PARTITION BY RANGE (virt_imei_shard)
""")
part_utils._grant_perms_registration_list(
conn, part_name='historic_registration_list_new')
# Create child partitions
part_utils.create_imei_shard_partitions(
conn,
tbl_name='historic_registration_list_new',
num_physical_shards=num_physical_shards,
perms_func=part_utils._grant_perms_registration_list,
fillfactor=80)
# Insert data from original partition
cursor.execute("""INSERT INTO historic_registration_list_new
SELECT *
FROM historic_registration_list""")
# Add in indexes to each partition
idx_metadata = [
part_utils.IndexMetadatum(idx_cols=['imei_norm'],
is_unique=True,
partial_sql='WHERE end_date IS NULL')
]
part_utils.add_indices(conn,
tbl_name='historic_registration_list_new',
idx_metadata=idx_metadata)
# Drop old view + table, rename tables, indexes and constraints
cursor.execute('DROP VIEW registration_list')
cursor.execute('DROP TABLE historic_registration_list CASCADE')
part_utils.rename_table_and_indices(
conn,
old_tbl_name='historic_registration_list_new',
new_tbl_name='historic_registration_list',
idx_metadata=idx_metadata)
cursor.execute("""CREATE OR REPLACE VIEW registration_list AS
SELECT imei_norm, make, model, status, virt_imei_shard
FROM historic_registration_list
WHERE end_date IS NULL WITH CHECK OPTION"""
)
cursor.execute("""GRANT SELECT ON registration_list
TO dirbs_core_classify, dirbs_core_api, dirbs_core_import_registration_list"""
)
def _init_staging_table_shards(self):
"""Method to create IMEI shards in the staging table if supported by the importer."""
assert self._supports_imei_shards
with db_role_setter(self._conn, role_name=self._owner_role_name):
tbl_name = self._staging_tbl_name
partition_utils.create_imei_shard_partitions(self._conn,
tbl_name=tbl_name,
unlogged=True)
for name, rstart, rend in partition_utils.physical_imei_shards(
self._conn, tbl_name=tbl_name):
self._on_staging_table_shard_creation(name, rstart, rend)
def queue_calc_imeis_jobs(self, executor, app_config, run_id, curr_date):
"""Method to queue jobs to calculate the IMEIs that are met by this condition."""
with create_db_connection(
app_config.db_config) as conn, conn.cursor() as cursor:
cursor.execute(
sql.SQL("""CREATE UNLOGGED TABLE {intermediate_tbl} (
imei_norm TEXT NOT NULL,
virt_imei_shard SMALLINT NOT NULL
)
PARTITION BY RANGE (virt_imei_shard)""").
format(intermediate_tbl=self.intermediate_tbl_id(run_id)))
partition_utils.create_imei_shard_partitions(
conn,
tbl_name=self.intermediate_tbl_name(run_id),
unlogged=True)
parallel_shards = partition_utils.num_physical_imei_shards(conn)
# Done with connection -- temp tables should now be committed
virt_imei_shard_ranges = partition_utils.virt_imei_shard_bounds(
parallel_shards)
for virt_imei_range_start, virt_imei_range_end in virt_imei_shard_ranges:
yield executor.submit(self._calc_imeis_job, app_config, run_id,
curr_date, virt_imei_range_start,
virt_imei_range_end)
def upgrade(self, conn):
"""Overrides AbstractMigrator upgrade method."""
logger = logging.getLogger('dirbs.db')
logger.info('Creating historic_whitelist table...')
with conn.cursor() as cur:
# create historic table for whitelist
cur.execute(
sql.SQL("""CREATE TABLE historic_whitelist (
imei_norm text NOT NULL,
associated BOOLEAN DEFAULT FALSE,
eir_id text DEFAULT NULL,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP DEFAULT NULL,
virt_imei_shard SMALLINT NOT NULL
)
PARTITION BY RANGE (virt_imei_shard)"""))
num_shards = part_utils.num_physical_imei_shards(conn)
logger.debug('Creating Whitelist child partitions...')
part_utils.create_imei_shard_partitions(
conn,
tbl_name='historic_whitelist',
num_physical_shards=num_shards,
fillfactor=80)
# Add indices to each partition
idx_metadata = [
part_utils.IndexMetadatum(idx_cols=['imei_norm'],
is_unique=True,
partial_sql='WHERE end_date IS NULL')
]
part_utils.add_indices(conn,
tbl_name='historic_whitelist',
idx_metadata=idx_metadata)
# creating views to historic_whitelist
cur.execute("""CREATE VIEW whitelist AS
SELECT imei_norm, associated, eir_id, virt_imei_shard
FROM historic_whitelist
WHERE end_date IS NULL WITH CHECK OPTION""")
# create view for imeis that are not associated yet
cur.execute("""CREATE VIEW available_whitelist AS
SELECT imei_norm, virt_imei_shard
FROM historic_whitelist
WHERE associated IS FALSE
AND end_date IS NULL WITH CHECK OPTION""")
# create insert & update trigger on historic_registration_list to update whitelist
# on update and insert
cur.execute(
"""CREATE OR REPLACE FUNCTION insert_whitelist() RETURNS TRIGGER AS
$BODY$
BEGIN
IF new.status = 'whitelist' OR new.status IS NULL THEN
INSERT INTO
historic_whitelist (imei_norm, start_date, end_date, virt_imei_shard)
VALUES (new.imei_norm, new.start_date, new.end_date, new.virt_imei_shard);
END IF;
RETURN new;
END;
$BODY$
LANGUAGE plpgsql;
-- update function
CREATE OR REPLACE FUNCTION update_whitelist() RETURNS TRIGGER AS
$BODY$
BEGIN
UPDATE historic_whitelist
SET end_date = new.end_date
WHERE imei_norm = new.imei_norm
AND new.end_date IS NOT NULL;
RETURN new;
END;
$BODY$
LANGUAGE plpgsql;
-- triggers
CREATE TRIGGER wl_insert_trigger AFTER INSERT ON historic_registration_list
FOR EACH ROW
EXECUTE PROCEDURE insert_whitelist();
CREATE TRIGGER wl_update_trigger AFTER UPDATE ON historic_registration_list
FOR EACH ROW
EXECUTE PROCEDURE update_whitelist();
ALTER TYPE job_command_type RENAME TO job_command_type_old;
--
-- Create type for command
--
CREATE TYPE job_command_type AS ENUM (
'dirbs-catalog',
'dirbs-classify',
'dirbs-db',
'dirbs-import',
'dirbs-listgen',
'dirbs-prune',
'dirbs-report',
'dirbs-whitelist'
);
ALTER TABLE job_metadata ALTER COLUMN command TYPE job_command_type
USING command::TEXT::job_command_type;
DROP TYPE job_command_type_old;
--
-- Whitelist notification triggers
--
CREATE FUNCTION notify_insert_distributor() RETURNS TRIGGER AS
$BODY$
BEGIN
IF new.associated IS FALSE AND new.eir_id IS NULL THEN
PERFORM pg_notify('distributor_updates', row_to_json(NEW)::text);
END IF;
RETURN new;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;
CREATE FUNCTION notify_remove_distributor() RETURNS TRIGGER AS
$BODY$
BEGIN
IF new.end_date IS NOT NULL THEN
PERFORM pg_notify('distributor_updates', row_to_json(NEW)::text);
END IF;
RETURN new;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;
CREATE TRIGGER notify_insert_trigger AFTER INSERT ON historic_whitelist
FOR EACH ROW
EXECUTE PROCEDURE notify_insert_distributor();
CREATE TRIGGER notify_remove_trigger AFTER UPDATE ON historic_whitelist
FOR EACH ROW
EXECUTE PROCEDURE notify_remove_distributor();
GRANT SELECT ON historic_whitelist TO dirbs_core_import_registration_list;
GRANT UPDATE ON historic_whitelist TO dirbs_core_import_registration_list;
GRANT INSERT ON historic_whitelist TO dirbs_core_import_registration_list;
GRANT INSERT ON historic_whitelist TO dirbs_core_white_list;
GRANT UPDATE ON historic_whitelist TO dirbs_core_white_list;
GRANT SELECT ON historic_whitelist TO dirbs_core_white_list;
GRANT DELETE ON historic_whitelist TO dirbs_core_white_list;
""") # noqa: Q440, Q449, Q441, Q447
def _repartition_pairing_list(self, conn, *, num_physical_shards):
"""Repartition pairing list to implement change in structure."""
with conn.cursor() as cursor, utils.db_role_setter(
conn, role_name='dirbs_core_power_user'):
cursor.execute("""CREATE TABLE historic_pairing_list_new (
LIKE historic_pairing_list INCLUDING DEFAULTS
INCLUDING IDENTITY
INCLUDING CONSTRAINTS
INCLUDING STORAGE
INCLUDING COMMENTS
)
PARTITION BY RANGE (virt_imei_shard)
""")
cursor.execute(
"""ALTER TABLE historic_pairing_list_new ADD COLUMN msisdn TEXT NOT NULL"""
)
# grant permissions
part_utils._grant_perms_pairing_list(
conn, part_name='historic_pairing_list_new')
# create child partitions
part_utils.create_imei_shard_partitions(
conn,
tbl_name='historic_pairing_list_new',
num_physical_shards=num_physical_shards,
perms_func=part_utils._grant_perms_pairing_list,
fillfactor=80)
# copy data from original partition
cursor.execute("""INSERT INTO historic_pairing_list_new
SELECT p.imei_norm, p.imsi, p.start_date, p.end_date, p.virt_imei_shard, m.msisdn
FROM historic_pairing_list p
INNER JOIN monthly_network_triplets_country m ON p.imsi = m.imsi"""
)
# add indexes
idx_metadata = [
part_utils.IndexMetadatum(
idx_cols=['imei_norm', 'imsi', 'msisdn'],
is_unique=True,
partial_sql='WHERE end_date IS NULL')
]
part_utils.add_indices(conn,
tbl_name='historic_pairing_list_new',
idx_metadata=idx_metadata)
# drop old views, tables, indexes and constraints
cursor.execute('DROP VIEW pairing_list')
cursor.execute('DROP TABLE historic_pairing_list CASCADE')
part_utils.rename_table_and_indices(
conn,
old_tbl_name='historic_pairing_list_new',
new_tbl_name='historic_pairing_list',
idx_metadata=idx_metadata)
# create new view and grant permissions
cursor.execute("""CREATE VIEW pairing_list AS
SELECT imei_norm, imsi, msisdn, virt_imei_shard
FROM historic_pairing_list
WHERE end_date IS NULL WITH CHECK OPTION"""
)
cursor.execute("""GRANT SELECT ON pairing_list
TO dirbs_core_listgen, dirbs_core_report, dirbs_core_api, dirbs_core_import_pairing_list
""")
# drop and recreate staging data insert trigger
cursor.execute("""
DROP FUNCTION pairing_list_staging_data_insert_trigger_fn() CASCADE;
CREATE FUNCTION pairing_list_staging_data_insert_trigger_fn() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
-- Clean/normalize data before inserting
NEW.imei_norm = normalize_imei(NULLIF(TRIM(NEW.imei), ''));
NEW.imsi = NULLIF(TRIM(new.imsi), '');
NEW.msisdn = NULLIF(TRIM(new.msisdn), '');
RETURN NEW;
END
$$;
""")
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'
)