-
Notifications
You must be signed in to change notification settings - Fork 19
/
0005.sql
136 lines (131 loc) · 12.5 KB
/
0005.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
/* 5PM MDT 31 March 2015
This column needs to be added to enable efficient metadata pulldown. Without
it, we cannot easily tell what the column should be called in the pulldown TSV.
- Adam Robbins-Pianka
*/
ALTER TABLE ag.survey_question ADD question_shortname varchar(100);
COMMENT ON COLUMN ag.survey_question.question_shortname IS 'A QIIME mapping file-compatible header that describes the question and can be used for metadata pulldown';
-- lots of inserts
UPDATE ag.survey_question set question_shortname = 'DIET_TYPE' WHERE survey_question_id = 1;
UPDATE ag.survey_question set question_shortname = 'MULTIVITAMIN' WHERE survey_question_id = 2;
UPDATE ag.survey_question set question_shortname = 'PROBIOTIC_FREQUENCY' WHERE survey_question_id = 3;
UPDATE ag.survey_question set question_shortname = 'VITAMIN_B_SUPPLEMENT_FREQUENCY' WHERE survey_question_id = 4;
UPDATE ag.survey_question set question_shortname = 'VITAMIN_D_SUPPLEMENT_FREQUENCY' WHERE survey_question_id = 5;
UPDATE ag.survey_question set question_shortname = 'OTHER_SUPPLEMENT_FREQUENCY' WHERE survey_question_id = 6;
UPDATE ag.survey_question set question_shortname = 'LACTOSE' WHERE survey_question_id = 7;
UPDATE ag.survey_question set question_shortname = 'GLUTEN' WHERE survey_question_id = 8;
UPDATE ag.survey_question set question_shortname = 'ALLERGIC_TO' WHERE survey_question_id = 9;
UPDATE ag.survey_question set question_shortname = 'LOWGRAIN_DIET_TYPE' WHERE survey_question_id = 10;
UPDATE ag.survey_question set question_shortname = 'CONSUME_ANIMAL_PRODUCTS_ABX' WHERE survey_question_id = 11;
UPDATE ag.survey_question set question_shortname = 'SPECIAL_RESTRICTIONS' WHERE survey_question_id = 12;
UPDATE ag.survey_question set question_shortname = 'DRINKING_WATER_SOURCE' WHERE survey_question_id = 13;
UPDATE ag.survey_question set question_shortname = 'RACE' WHERE survey_question_id = 14;
UPDATE ag.survey_question set question_shortname = 'LAST_MOVE' WHERE survey_question_id = 15;
UPDATE ag.survey_question set question_shortname = 'LAST_TRAVEL' WHERE survey_question_id = 16;
UPDATE ag.survey_question set question_shortname = 'ROOMMATES' WHERE survey_question_id = 17;
UPDATE ag.survey_question set question_shortname = 'ROOMMATES_IN_STUDY' WHERE survey_question_id = 18;
UPDATE ag.survey_question set question_shortname = 'LIVINGWITH' WHERE survey_question_id = 19;
UPDATE ag.survey_question set question_shortname = 'DOG' WHERE survey_question_id = 20;
UPDATE ag.survey_question set question_shortname = 'CAT' WHERE survey_question_id = 21;
UPDATE ag.survey_question set question_shortname = 'HAND' WHERE survey_question_id = 22;
UPDATE ag.survey_question set question_shortname = 'LEVEL_OF_EDUCATION' WHERE survey_question_id = 23;
UPDATE ag.survey_question set question_shortname = 'EXERCISE_FREQUENCY' WHERE survey_question_id = 24;
UPDATE ag.survey_question set question_shortname = 'EXERCISE_LOCATION' WHERE survey_question_id = 25;
UPDATE ag.survey_question set question_shortname = 'NAIL' WHERE survey_question_id = 26;
UPDATE ag.survey_question set question_shortname = 'POOL_FREQUENCY' WHERE survey_question_id = 27;
UPDATE ag.survey_question set question_shortname = 'SMOKING_FREQUENCY' WHERE survey_question_id = 28;
UPDATE ag.survey_question set question_shortname = 'ALCOHOL_FREQUENCY' WHERE survey_question_id = 29;
UPDATE ag.survey_question set question_shortname = 'ALCOHOL_TYPES' WHERE survey_question_id = 30;
UPDATE ag.survey_question set question_shortname = 'TEETHBRUSHING_FREQUENCY' WHERE survey_question_id = 31;
UPDATE ag.survey_question set question_shortname = 'FLOSSING_FREQUENCY' WHERE survey_question_id = 32;
UPDATE ag.survey_question set question_shortname = 'COSMETICS_FREQUENCY' WHERE survey_question_id = 33;
UPDATE ag.survey_question set question_shortname = 'DEODORANT_USE' WHERE survey_question_id = 34;
UPDATE ag.survey_question set question_shortname = 'SLEEP_DURATION' WHERE survey_question_id = 35;
UPDATE ag.survey_question set question_shortname = 'SOFTENER' WHERE survey_question_id = 36;
UPDATE ag.survey_question set question_shortname = 'BOWEL_MOVEMENT_FREQUENCY' WHERE survey_question_id = 37;
UPDATE ag.survey_question set question_shortname = 'BOWEL_MOVEMENT_QUALITY' WHERE survey_question_id = 38;
UPDATE ag.survey_question set question_shortname = 'ANTIBIOTIC_SELECT' WHERE survey_question_id = 39;
UPDATE ag.survey_question set question_shortname = 'FLU_VACCINE_DATE' WHERE survey_question_id = 40;
UPDATE ag.survey_question set question_shortname = 'CONTRACEPTIVE' WHERE survey_question_id = 41;
UPDATE ag.survey_question set question_shortname = 'PREGNANT' WHERE survey_question_id = 42;
UPDATE ag.survey_question set question_shortname = 'WEIGHT_CHANGE' WHERE survey_question_id = 43;
UPDATE ag.survey_question set question_shortname = 'TONSILS_REMOVED' WHERE survey_question_id = 44;
UPDATE ag.survey_question set question_shortname = 'APPENDIX_REMOVED' WHERE survey_question_id = 45;
UPDATE ag.survey_question set question_shortname = 'CHICKENPOX' WHERE survey_question_id = 46;
UPDATE ag.survey_question set question_shortname = 'ACNE_MEDICATION' WHERE survey_question_id = 47;
UPDATE ag.survey_question set question_shortname = 'ACNE_MEDICATION_OTC' WHERE survey_question_id = 48;
UPDATE ag.survey_question set question_shortname = 'CONDITIONS_MEDICATION' WHERE survey_question_id = 49;
UPDATE ag.survey_question set question_shortname = 'CSECTION' WHERE survey_question_id = 50;
UPDATE ag.survey_question set question_shortname = 'FED_AS_INFANT' WHERE survey_question_id = 51;
UPDATE ag.survey_question set question_shortname = 'WILLING_TO_BE_CONTACTED' WHERE survey_question_id = 52;
UPDATE ag.survey_question set question_shortname = 'SEASONAL_ALLERGIES' WHERE survey_question_id = 53;
UPDATE ag.survey_question set question_shortname = 'NON_FOOD_ALLERGIES' WHERE survey_question_id = 54;
UPDATE ag.survey_question set question_shortname = 'BREASTMILK_FORMULA_ENSURE' WHERE survey_question_id = 55;
UPDATE ag.survey_question set question_shortname = 'MEAT_EGGS_FREQUENCY' WHERE survey_question_id = 56;
UPDATE ag.survey_question set question_shortname = 'HOMECOOKED_MEALS_FREQUENCY' WHERE survey_question_id = 57;
UPDATE ag.survey_question set question_shortname = 'READY_TO_EAT_MEALS_FREQUENCY' WHERE survey_question_id = 58;
UPDATE ag.survey_question set question_shortname = 'PREPARED_MEALS_FREQUENCY' WHERE survey_question_id = 59;
UPDATE ag.survey_question set question_shortname = 'KIDNEY_DISEASE' WHERE survey_question_id = 60;
UPDATE ag.survey_question set question_shortname = 'FRUIT_FREQUENCY' WHERE survey_question_id = 61;
UPDATE ag.survey_question set question_shortname = 'VEGETABLE_FREQUENCY' WHERE survey_question_id = 62;
UPDATE ag.survey_question set question_shortname = 'FERMENTED_PLANT_FREQUENCY' WHERE survey_question_id = 63;
UPDATE ag.survey_question set question_shortname = 'MILK_CHEESE_FREQUENCY' WHERE survey_question_id = 64;
UPDATE ag.survey_question set question_shortname = 'MILK_SUBSTITUTE_FREQUENCY' WHERE survey_question_id = 65;
UPDATE ag.survey_question set question_shortname = 'FROZEN_DESERT_FREQUENCY' WHERE survey_question_id = 66;
UPDATE ag.survey_question set question_shortname = 'RED_MEAT_FREQUENCY' WHERE survey_question_id = 67;
UPDATE ag.survey_question set question_shortname = 'HIGH_FAT_RED_MEAT_FREQUENCY' WHERE survey_question_id = 68;
UPDATE ag.survey_question set question_shortname = 'POULTRY_FREQUENCY' WHERE survey_question_id = 69;
UPDATE ag.survey_question set question_shortname = 'SEAFOOD_FREQUENCY' WHERE survey_question_id = 70;
UPDATE ag.survey_question set question_shortname = 'SALTED_SNACKS_FREQUENCY' WHERE survey_question_id = 71;
UPDATE ag.survey_question set question_shortname = 'SUGARY_SWEETS_FREQUENCY' WHERE survey_question_id = 72;
UPDATE ag.survey_question set question_shortname = 'OLIVE_OIL' WHERE survey_question_id = 73;
UPDATE ag.survey_question set question_shortname = 'WHOLE_EGGS' WHERE survey_question_id = 74;
UPDATE ag.survey_question set question_shortname = 'SUGAR_SWEETENED_DRINK_FREQUENCY' WHERE survey_question_id = 75;
UPDATE ag.survey_question set question_shortname = 'ONE_LITER_OF_WATER_A_DAY_FREQUENCY' WHERE survey_question_id = 76;
UPDATE ag.survey_question set question_shortname = 'ASD' WHERE survey_question_id = 77;
UPDATE ag.survey_question set question_shortname = 'SIBO' WHERE survey_question_id = 78;
UPDATE ag.survey_question set question_shortname = 'IBS' WHERE survey_question_id = 79;
UPDATE ag.survey_question set question_shortname = 'CDIFF' WHERE survey_question_id = 80;
UPDATE ag.survey_question set question_shortname = 'CLINICAL_CONDITION' WHERE survey_question_id = 81;
UPDATE ag.survey_question set question_shortname = 'DIABETES' WHERE survey_question_id = 82;
UPDATE ag.survey_question set question_shortname = 'IBD' WHERE survey_question_id = 83;
UPDATE ag.survey_question set question_shortname = 'ALZHEIMERS' WHERE survey_question_id = 84;
UPDATE ag.survey_question set question_shortname = 'ADD_ADHD' WHERE survey_question_id = 85;
UPDATE ag.survey_question set question_shortname = 'LIVER_DISEASE' WHERE survey_question_id = 86;
UPDATE ag.survey_question set question_shortname = 'AUTOIMMUNE' WHERE survey_question_id = 87;
UPDATE ag.survey_question set question_shortname = 'SKIN_CONDITION' WHERE survey_question_id = 88;
UPDATE ag.survey_question set question_shortname = 'CARDIOVASCULAR_DISEASE' WHERE survey_question_id = 89;
UPDATE ag.survey_question set question_shortname = 'EPILEPSY_OR_SEIZURE_DISORDER' WHERE survey_question_id = 90;
UPDATE ag.survey_question set question_shortname = 'WHOLE_GRAIN_FREQUENCY' WHERE survey_question_id = 91;
UPDATE ag.survey_question set question_shortname = 'MIGRAINE' WHERE survey_question_id = 92;
UPDATE ag.survey_question set question_shortname = 'LUNG_DISEASE' WHERE survey_question_id = 93;
UPDATE ag.survey_question set question_shortname = 'PKU' WHERE survey_question_id = 94;
UPDATE ag.survey_question set question_shortname = 'FUNGAL_OVERGROWTH' WHERE survey_question_id = 95;
UPDATE ag.survey_question set question_shortname = 'THYROID' WHERE survey_question_id = 96;
UPDATE ag.survey_question set question_shortname = 'DEPRESSION_BIPOLAR_SCHIZOPHRENIA' WHERE survey_question_id = 97;
UPDATE ag.survey_question set question_shortname = 'PREGNANT_DUE_DATE' WHERE survey_question_id = 98;
UPDATE ag.survey_question set question_shortname = 'MEDICATION_LIST' WHERE survey_question_id = 99;
UPDATE ag.survey_question set question_shortname = 'DOG_LOCATION' WHERE survey_question_id = 101;
UPDATE ag.survey_question set question_shortname = 'RACE_OTHER' WHERE survey_question_id = 103;
UPDATE ag.survey_question set question_shortname = 'SUPPLEMENTS' WHERE survey_question_id = 104;
UPDATE ag.survey_question set question_shortname = 'DOG_CONTACT' WHERE survey_question_id = 105;
UPDATE ag.survey_question set question_shortname = 'OTHER_CONDTITIONS_LIST' WHERE survey_question_id = 106;
UPDATE ag.survey_question set question_shortname = 'GENDER' WHERE survey_question_id = 107;
UPDATE ag.survey_question set question_shortname = 'HEIGHT_CM' WHERE survey_question_id = 108;
UPDATE ag.survey_question set question_shortname = 'HEIGHT_UNITS' WHERE survey_question_id = 109;
UPDATE ag.survey_question set question_shortname = 'COUNTRY_OF_BIRTH' WHERE survey_question_id = 110;
UPDATE ag.survey_question set question_shortname = 'BIRTH_MONTH' WHERE survey_question_id = 111;
UPDATE ag.survey_question set question_shortname = 'BIRTH_YEAR' WHERE survey_question_id = 112;
UPDATE ag.survey_question set question_shortname = 'WEIGHT_KG' WHERE survey_question_id = 113;
UPDATE ag.survey_question set question_shortname = 'WEIGHT_UNITS' WHERE survey_question_id = 114;
UPDATE ag.survey_question set question_shortname = 'ZIP_CODE' WHERE survey_question_id = 115;
UPDATE ag.survey_question set question_shortname = 'ABOUT_YOURSELF_TEXT' WHERE survey_question_id = 116;
UPDATE ag.survey_question set question_shortname = 'CAT_LOCATION' WHERE survey_question_id = 117;
UPDATE ag.survey_question set question_shortname = 'DIET_RESTRICTIONS_LIST' WHERE survey_question_id = 118;
UPDATE ag.survey_question set question_shortname = 'TRAVEL_LOCATIONS_LIST' WHERE survey_question_id = 119;
UPDATE ag.survey_question set question_shortname = 'RELATIONSHIPS_WITH_OTHERS_IN_STUDY' WHERE survey_question_id = 120;
UPDATE ag.survey_question set question_shortname = 'CAT_CONTACT' WHERE survey_question_id = 122;
UPDATE ag.survey_question set question_shortname = 'ANTIBIOTIC_MED' WHERE survey_question_id = 124;
UPDATE ag.survey_question set question_shortname = 'ANTIBIOTIC_CONDITION' WHERE survey_question_id = 126;
ALTER TABLE ag.survey_question ALTER COLUMN question_shortname SET NOT NULL;
ALTER TABLE ag.survey_question ADD CONSTRAINT idx_survey_question_1 UNIQUE ( question_shortname ) ;