-
Alessandro Vullo authorede27f241d
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
patch_55_56_c.sql 12.26 KiB
# patch_55_56_c.sql
#
# title: oligo tables and xrefs
#
# description:
# Drop oligo tables and remove association xref schema data
-- Delete all AFFY object_xrefs
-- Do this on external_db_ids and dbname, to be safer i.e. we do not want to corrupt and custom data
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id>=3000 and edb.external_db_id<3200 and edb.db_name like 'AFFY\_%' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
-- And xrefs
DELETE x from external_db edb, xref x where edb.external_db_id>=3000 and edb.external_db_id<3200 and edb.db_name like 'AFFY\_%' and edb.external_db_id=x.external_db_id;
-- And external_dbs
DELETE from external_db where external_db_id>=3000 and external_db_id<3200 and db_name like 'AFFY\_%';
-- Species specific xrefs, to do this safely have to do these on a per db basis due to specific names
-- aedes_aegypti_core_55_1d
-- oligo_array_id parent_array_id probe_setsize name type external_db_id
-- 3 NULL 1 ARRAY_ND_TIGRTC_9_6K_v1 OLIGO 3207
-- 2 NULL 1 ARRAY_LIV_AEGDETOX_0_25K_v1 OLIGO 3206
-- 4 NULL 1 ARRAY_UCR_GillMgMT_0_2K_v2 OLIGO 3208
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3207 and edb.db_name='ARRAY_ND_TIGRTC_9_6K_v1' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3206 and edb.db_name='ARRAY_LIV_AEGDETOX_0_25K_v1' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3208 and edb.db_name='ARRAY_UCR_GillMgMT_0_2K_v2' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
-- And xrefs
DELETE x from external_db edb, xref x where edb.external_db_id=3207 and edb.db_name='ARRAY_ND_TIGRTC_9_6K_v1' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3206 and edb.db_name='ARRAY_LIV_AEGDETOX_0_25K_v1' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3208 and edb.db_name='ARRAY_UCR_GillMgMT_0_2K_v2' and edb.external_db_id=x.external_db_id ;
-- And external_dbs
DELETE from external_db where external_db_id=3207 and db_name='ARRAY_ND_TIGRTC_9_6K_v1';
DELETE from external_db where external_db_id=3206 and db_name='ARRAY_LIV_AEGDETOX_0_25K_v1';
DELETE from external_db where external_db_id=3209 and db_name='ARRAY_UCR_GillMgMT_0_2K_v2';
-- anopheles_gambiae_core_55_3k
-- oligo_array_id parent_array_id probe_setsize name type external_db_id
-- 3 NULL 1 LIV_GAMDETOX_0.25k_v1 OLIGO 3204
-- 4 NULL 1 LIV_GAMDETOX_0.25k_v2 OLIGO 3210
-- 5 NULL 1 EMBL_MMC1_20k_v1 OLIGO 3201
-- 6 NULL 1 EMBL_MMC2_12k_v1 OLIGO 3202
-- 7 NULL 1 JHSPH_AG_GAMBER_15k_v1 OLIGO 3209
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3204 and edb.db_name='ARRAY_LIV_GAMDETOX_0.25k_v1' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3210 and edb.db_name='ARRAY_LIV_GAMDETOX_0.25k_v2' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3201 and edb.db_name='ARRAY_EMBL_MMC1_20k_v1' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3202 and edb.db_name='ARRAY_EMBL_MMC2_12k_v1' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3209 and edb.db_name='ARRAY_JHSPH_AG_GAMBER_15k_v1' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE x from external_db edb, xref x where edb.external_db_id=3204 and edb.db_name='ARRAY_LIV_GAMDETOX_0.25k_v1' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3210 and edb.db_name='ARRAY_LIV_GAMDETOX_0.25k_v2' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3201 and edb.db_name='ARRAY_EMBL_MMC1_20k_v1' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3202 and edb.db_name='ARRAY_EMBL_MMC2_12k_v1' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3209 and edb.db_name='ARRAY_JHSPH_AG_GAMBER_15k_v1' and edb.external_db_id=x.external_db_id ;
DELETE from external_db where external_db_id=3204 and db_name='ARRAY_LIV_GAMDETOX_0.25k_v1';DELETE from external_db where external_db_id=3210 and db_name='ARRAY_LIV_GAMDETOX_0.25k_v2';
DELETE from external_db where external_db_id=3201 and db_name='ARRAY_EMBL_MMC1_20k_v1';
DELETE from external_db where external_db_id=3202 and db_name='ARRAY_EMBL_MMC2_12k_v1';
DELETE from external_db where external_db_id=3200 and db_name='ARRAY_JHSPH_AG_GAMBER_15k_v1';
-- danio
-- oligo_array_id parent_array_id probe_setsize name type external_db_id
-- 2 NULL 1 AGILENT_G2518A OLIGO 3251
-- 3 NULL 1 AGILENT_G2519F OLIGO 3252
-- 4 NULL 1 LEIDEN2 OLIGO 3260
-- 5 NULL 1 LEIDEN3 OLIGO 3261
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3251 and edb.db_name='AGILENT_G2518A' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3252 and edb.db_name='AGILENT_G2519F' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3260 and edb.db_name='ARRAY_ZFISH_LEIDEN2' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3261 and edb.db_name='ARRAY_ZFISH_LEIDEN3' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE x from external_db edb, xref x where edb.external_db_id=3251 and edb.db_name='AGILENT_G2518A' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3252 and edb.db_name='AGILENT_G2519F' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3260 and edb.db_name='ARRAY_ZFISH_LEIDEN2' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3261 and edb.db_name='ARRAY_ZFISH_LEIDEN3' and edb.external_db_id=x.external_db_id ;
DELETE from external_db where external_db_id=3251 and db_name='AGILENT_G2518A';
DELETE from external_db where external_db_id=3252 and db_name='AGILENT_G2519F';
DELETE from external_db where external_db_id=3260 and db_name='ARRAY_ZFISH_LEIDEN2';
DELETE from external_db where external_db_id=3261 and db_name='ARRAY_ZFISH_LEIDEN3';
-- platypus
-- platypus_exon? Is done as affy for oligos, but no for xrefs.
-- Platypus_exon 3299
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3299 and edb.db_name='Platypus_exon' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE x from external_db edb, xref x where edb.external_db_id=3299 and edb.db_name='Platypus_exon' and edb.external_db_id=x.external_db_id ;
DELETE from external_db where external_db_id=3299 and db_name='Platypus_exon';
-- Also have:
-- VB_array 3200
-- ARRAY_JHU_GAM3_0_21k_v1 3203
-- ARRAY_JHU_AEG1_0_20k_v1 3205
-- ARRAY_LIV_AEGDETOX_0_25k_v2 3211
-- Seeking confirmation from Dan Lawson wrt removing these
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3203 and edb.db_name='ARRAY_JHU_GAM3_0_21k_v1' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3205 and edb.db_name='ARRAY_JHU_AEG1_0_20k_v1' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3211 and edb.db_name='ARRAY_LIV_AEGDETOX_0_25k_v2' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE x from external_db edb, xref x where edb.external_db_id=3203 and edb.db_name='ARRAY_JHU_GAM3_0_21k_v1' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3205 and edb.db_name='ARRAY_JHU_AEG1_0_20k_v1' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3211 and edb.db_name='ARRAY_LIV_AEGDETOX_0_25k_v2' and edb.external_db_id=x.external_db_id ;
DELETE from external_db where external_db_id=3203 and db_name='ARRAY_JHU_GAM3_0_21k_v1';
DELETE from external_db where external_db_id=3205 and db_name='ARRAY_JHU_AEG1_0_20k_v1';
DELETE from external_db where external_db_id=3211 and db_name='ARRAY_LIV_AEGDETOX_0_25k_v2';
-- Also need to remove xref only array data for db_name like AGILENT, Illumina, CODELINK
-- +----------------+-------------+
-- | external_db_id | db_name |
-- +----------------+-------------+
-- | 3250 | Illumina |
-- | 3255 | Illumina_V1 |
-- | 3256 | Illumina_V2 |
-- | 4300 | AgilentProbe |
-- | 4305 | AgilentCGH |
-- | 6000 | Codelink |
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3250 and edb.db_name='Illumina' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3255 and edb.db_name='Illumina_V1' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=3256 and edb.db_name='Illumina_V2' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=4300 and edb.db_name='AgilentProbe' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=4306 and edb.db_name='AgilentCGH' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE ox from external_db edb, xref x, object_xref ox where edb.external_db_id=6000 and edb.db_name='Codelink' and edb.external_db_id=x.external_db_id and x.xref_id=ox.xref_id;
DELETE x from external_db edb, xref x where edb.external_db_id=3250 and edb.db_name='Illumina' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3255 and edb.db_name='Illumina_V1' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=3256 and edb.db_name='Illumina_V2' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=4300 and edb.db_name='AgilentProbe' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=4305 and edb.db_name='AgilentCGH' and edb.external_db_id=x.external_db_id ;
DELETE x from external_db edb, xref x where edb.external_db_id=6000 and edb.db_name='Codelink' and edb.external_db_id=x.external_db_id ;
DELETE from external_db where external_db_id=3250 and db_name='Illumina';
DELETE from external_db where external_db_id=3255 and db_name='Illumina_V1';
DELETE from external_db where external_db_id=3256 and db_name='Illumina_V2';
DELETE from external_db where external_db_id=4300 and db_name='AgilentProbe';
DELETE from external_db where external_db_id=4305 and db_name='AgilentCGH';
DELETE from external_db where external_db_id=6000 and db_name='Codelink';
-- Delete unmapped_reasons
DELETE ur from unmapped_reason ur, unmapped_object uo where uo.type='probe2transcript' and uo.unmapped_reason_id=ur.unmapped_reason_id;
-- Delete unmapped_objects, separately just in case of orhpans
DELETE from unmapped_object where type='probe2transcript';
-- Drop oligo tables last so we do not get errors, should we need to rerun
DROP table oligo_array;
DROP table oligo_probe;
DROP table oligo_feature;
INSERT INTO meta (species_id, meta_key, meta_value) VALUES (NULL, 'patch', 'patch_55_56_c.sql|drop_oligo_tables_and_xrefs');