def upgrade(self, db_conn):
"""Overrides AbstractMigrator upgrade method."""
logger = logging.getLogger('dirbs.db')
with db_conn.cursor() as cursor:
logger.info('Creating historic tables...')
cursor.execute(
sql.SQL("""CREATE TABLE historic_stolen_list (
imei_norm text NOT NULL,
reporting_date DATE DEFAULT NULL,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP DEFAULT NULL
);
CREATE UNIQUE INDEX
ON historic_stolen_list
USING btree (imei_norm)
WHERE (end_date IS NULL);
CREATE TABLE historic_pairing_list (
imei_norm text NOT NULL,
imsi text NOT NULL,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP DEFAULT NULL
);
CREATE UNIQUE INDEX
ON historic_pairing_list
USING btree (imei_norm, imsi)
WHERE (end_date IS NULL);
CREATE TABLE historic_golden_list (
hashed_imei_norm UUID NOT NULL,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP DEFAULT NULL
);
CREATE UNIQUE INDEX
ON historic_golden_list
USING btree (hashed_imei_norm)
WHERE (end_date IS NULL);
CREATE TABLE historic_registration_list (
imei_norm text NOT NULL,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP DEFAULT NULL
);
CREATE UNIQUE INDEX
ON historic_registration_list
USING btree (imei_norm)
WHERE (end_date IS NULL);"""))
logger.info('Created historic tables')
logger.info('Start migrating import tables to historic tables...')
logger.info(
'Migrating stolen_list table to historic_stolen_list table...')
stolen_job_start_time = most_recent_job_start_time_by_command(
db_conn,
'dirbs-import',
subcommand='stolen_list',
successful_only=True)
if not stolen_job_start_time:
stolen_job_start_time = datetime.datetime.now()
cursor.execute(
sql.SQL(
"""INSERT INTO historic_stolen_list(imei_norm, reporting_date, start_date, end_date)
SELECT imei_norm, reporting_date, %s, NULL
FROM stolen_list;"""),
[stolen_job_start_time])
logger.info(
'Migrating pairing_list table to historic_pairing_list table...'
)
pairing_job_start_time = most_recent_job_start_time_by_command(
db_conn,
'dirbs-import',
subcommand='pairing_list',
successful_only=True)
if not pairing_job_start_time:
pairing_job_start_time = datetime.datetime.now()
cursor.execute(
sql.SQL(
"""INSERT INTO historic_pairing_list(imei_norm, imsi, start_date, end_date)
SELECT imei_norm, imsi, %s, NULL
FROM pairing_list;"""),
[pairing_job_start_time])
logger.info(
'Migrating registration_list table to historic_registration_list table...'
)
registration_job_start_time = most_recent_job_start_time_by_command(
db_conn,
'dirbs-import',
subcommand='registration_list',
successful_only=True)
if not registration_job_start_time:
registration_job_start_time = datetime.datetime.now()
cursor.execute(
sql.SQL(
"""INSERT INTO historic_registration_list(imei_norm, start_date, end_date)
SELECT imei_norm, %s, NULL
FROM registration_list;"""),
[registration_job_start_time])
logger.info(
'Migrating golden_list table to historic_golden_list table...')
golden_job_start_time = most_recent_job_start_time_by_command(
db_conn,
'dirbs-import',
subcommand='golden_list',
successful_only=True)
if not golden_job_start_time:
golden_job_start_time = datetime.datetime.now()
cursor.execute(
sql.SQL(
"""INSERT INTO historic_golden_list(hashed_imei_norm, start_date, end_date)
SELECT hashed_imei_norm, %s, NULL
FROM golden_list;"""),
[golden_job_start_time])
logger.info('Migrated all the import tables to historic tables')
logger.info('Dropping old import tables...')
cursor.execute(
sql.SQL("""DROP TABLE pairing_list;
DROP TABLE stolen_list;
DROP TABLE golden_list;
DROP TABLE registration_list;"""))
logger.info('Dropped old import tables')
logger.info(
'Creating views to keep a compatibility with the previous importers ...'
)
cursor.execute(
sql.SQL("""CREATE VIEW pairing_list AS
SELECT imei_norm, imsi
FROM historic_pairing_list
WHERE end_date IS NULL WITH CHECK OPTION;
CREATE VIEW stolen_list AS
SELECT imei_norm, reporting_date
FROM historic_stolen_list
WHERE end_date IS NULL WITH CHECK OPTION;
CREATE VIEW golden_list AS
SELECT hashed_imei_norm
FROM historic_golden_list
WHERE end_date IS NULL WITH CHECK OPTION;
CREATE VIEW registration_list AS
SELECT imei_norm
FROM historic_registration_list
WHERE end_date IS NULL WITH CHECK OPTION;"""
))
logger.info('Created views')
logger.info('Granting privileges on views and historic tables...')
cursor.execute(
sql.SQL("""GRANT SELECT ON historic_pairing_list TO
dirbs_core_listgen,
dirbs_core_report,
dirbs_core_api;
GRANT SELECT ON pairing_list TO
dirbs_core_listgen,
dirbs_core_report,
dirbs_core_api,
dirbs_core_import_pairing_list;
GRANT SELECT, INSERT, UPDATE ON historic_pairing_list TO
dirbs_core_import_pairing_list;
GRANT SELECT ON historic_stolen_list TO dirbs_core_classify;
GRANT SELECT ON stolen_list TO
dirbs_core_classify,
dirbs_core_import_stolen_list;
GRANT SELECT, INSERT, UPDATE ON historic_stolen_list TO
dirbs_core_import_stolen_list;
GRANT SELECT ON historic_golden_list TO dirbs_core_listgen;
GRANT SELECT ON golden_list TO
dirbs_core_listgen,
dirbs_core_import_golden_list;
GRANT SELECT, INSERT, UPDATE ON historic_golden_list TO
dirbs_core_import_golden_list;
GRANT SELECT ON historic_registration_list TO
dirbs_core_classify,
dirbs_core_api;
GRANT SELECT ON registration_list TO
dirbs_core_classify,
dirbs_core_api,
dirbs_core_import_registration_list;
GRANT SELECT, INSERT, UPDATE ON historic_registration_list TO
dirbs_core_import_registration_list;"""
))
logger.info('Granted privileges')
def upgrade(self, db_conn):
"""Overrides AbstractMigrator upgrade method."""
logger = logging.getLogger('dirbs.db')
with db_conn.cursor() as cursor:
logger.info('Creating GSMA historic table...')
cursor.execute(
sql.SQL("""CREATE TABLE historic_gsma_data (
tac character varying(8) NOT NULL,
manufacturer character varying(128),
bands character varying(4096),
allocation_date date,
model_name character varying(1024),
device_type TEXT,
optional_fields jsonb,
rat_bitmask INTEGER,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP DEFAULT NULL
);
CREATE UNIQUE INDEX
ON historic_gsma_data
USING btree (tac)
WHERE (end_date IS NULL)"""))
logger.info('Created historic table')
logger.info('Migrating GSMA table to historic GSMA table...')
gsma_job_start_time = most_recent_job_start_time_by_command(
db_conn,
'dirbs-import',
subcommand='gsma_tac',
successful_only=True)
if not gsma_job_start_time:
gsma_job_start_time = datetime.datetime.now()
cursor.execute(
sql.SQL("""INSERT INTO historic_gsma_data(tac,
manufacturer,
bands,
allocation_date,
model_name,
device_type,
optional_fields,
rat_bitmask,
start_date,
end_date)
SELECT tac, manufacturer, bands, allocation_date, model_name, device_type,
optional_fields, rat_bitmask, %s, NULL
FROM gsma_data;"""),
[gsma_job_start_time])
logger.info('Migrated import tables to historic table')
logger.info('Dropping old GSMA import table...')
cursor.execute(sql.SQL("""DROP TABLE gsma_data;"""))
logger.info('Dropped old GSMA import table')
logger.info(
'Creating GSMA materialized view to keep a compatibility with the previous importers ...'
)
cursor.execute(
sql.SQL("""CREATE MATERIALIZED VIEW gsma_data AS
SELECT tac, manufacturer, bands, allocation_date, model_name,
device_type, optional_fields, rat_bitmask
FROM historic_gsma_data
WHERE end_date IS NULL WITH DATA;
CREATE UNIQUE INDEX
ON gsma_data
USING btree (tac)
""")) # noqa Q441
cursor.execute(
sql.SQL(
"""ALTER MATERIALIZED VIEW gsma_data OWNER TO dirbs_core_import_gsma"""
))
logger.info(
'Created GSMA view to keep a compatibility with the previous importers...'
)
logger.info(
'Granting privileges on GSMA view and historic table...')
cursor.execute(
sql.SQL("""GRANT SELECT ON historic_gsma_data TO
dirbs_core_import_operator, dirbs_core_classify, dirbs_core_api,
dirbs_core_report;
GRANT SELECT ON gsma_data TO
dirbs_core_import_operator, dirbs_core_classify, dirbs_core_api,
dirbs_core_report, dirbs_core_import_gsma;
GRANT SELECT, INSERT, UPDATE ON historic_gsma_data TO
dirbs_core_import_gsma;"""))
logger.info('Granted privileges')