WValenti
commented
11 months ago So...DELUSIONS is in both 3.0/B and 3.0/B+, and is similar but still very different from i10940, so we need to remove it from the equivalence_group (and variable_name_equivalence_pairs, which is how it got into equivalence_groups). We won't regen equivalence_groups from VNEPs at this time as that's slow and will need close watching because just like now, I've been directly patching both tables and need to be sure I didn't screw things up.
The next step is a bit tricksier as I need to rename DELUSIONS in the DI-PAD because it is just one source compared to two, and 3.0/B and B+ were here first.
DELETE FROM variable_name_equivalence_pair_support where equivalence_pair_id IN (SELECT equivalence_pair_id FROM variable_name_equivalence_pairs WHERE variable_name_b = 'DELUSIONS');
DELETE FROM variable_name_equivalence_pairs WHERE variable_name_b = 'DELUSIONS'; -- similar but different
UPDATE equivalence_groups SET first_member = 'DELUSIONS' where variable_name = 'DELUSIONS'; -- only grouped with self for now
INSERT INTO variable_name_equivalence_pairs VALUES (11065, 'I10940', 'DIPAD_DELUSIONS', NULL, 'Changed from DELUSIONS due to 3.0/B conflict');
INSERT INTO variable_name_equivalence_pair_support VALUES (11065, 21);
INSERT INTO equivalence_groups SELECT 'DIPAD_DELUSIONS', first_member, NULL, NULL, cardinality, min_value, max_value, top_3_responses, width, observations FROM equivalence_groups WHERE variable_name = 'I10940';
-- On to renaming the raw variable. Can't explain the various data types other than that is what I was given.
INSERT INTO raw_variable_name_renames VALUES (121,'delusions','dipad_delusions',NOW(),'Not the same as DELUSIONS from predecessor 3.0/B(+)');
ALTER TABLE DIGS_raw.076_20140724_study76_77_724_sas7bdat CHANGE delusions dipad_delusions VARCHAR(20);
ALTER TABLE DIGS_raw.76_ALL_GPC_SZ_PROBAND_AND_FAMILY_2016_2_1v3_phen_UTF8 CHANGE delusions dipad_delusions VARCHAR(4);
ALTER TABLE DIGS_raw.study77_phen_v2_UTF8 CHANGE delusions dipad_delusions TINYINT(1);
UPDATE variables SET variable_name = 'DIPAD_DELUSIONS' WHERE variable_name = 'DELUSIONS' AND table_id IN (223,224,225);
SET FOREIGN_KEY_CHECKS=0; -- because DIGS_questions and DIGS_instruments are incestuous
UPDATE DIGS_questions SET variable_name = 'DIPAD_DELUSIONS' WHERE version = 'DI-PAD' AND variable_name = 'DELUSIONS';
UPDATE DIGS_instruments SET variable_name = 'DIPAD_DELUSIONS' WHERE version = 'DI-PAD' AND variable_name = 'DELUSIONS';
SET FOREIGN_KEY_CHECKS=1;Now test with:
CALL summarize_cleaned_kibble('DELUSIONS','+'); -- and DIPAD_DELUSIONS, I10940
The DELUSIONS harmonization is a mess anyhow, e.g. 3.0/B contributes BOTH the variable DELUSIONS and DELUSIONS_EVER. The whole thing needs cleaning-up, and the most awkward part will be doing a raw-table rename of 3.0/B's DELUSIONS to something specific to the instrument so it won't be name harmonized.