[#3] Implement Variable Pointers into Octo DDL
This commit changes the DDL exporter to create extra columns for variable pointer fileman fields.
It works like this, using the ORDER1 file OBJECT_OF_ORDER field as an example:
- OBJECT_OF_ORDER field is unchanged. It still exists.
- For each variable pointer, create a new field OBJECT_OF_ORDER_V_{FILENAME}. In this case, OBJECT_OF_ORDER_V_PATIENT and OBJECT_OF_ORDER_V_REFERRAL_PATIENT.
- You can now directly query OBJECT_OF_ORDER_V_PATIENT. It will only return data if there is a valid patient pointer. Otherwise, it will return NULL.
- It's possible (but rare) for a variable pointer to have multiple pointers to the same file, but with a Fileman screen. In this case, we expose the field only once. There is only one field in FOIA VistA where this was found.
Note that since the variable pointer data is obtained using a computed expression, it is not indexable. This is tracked as issue YDBOcto#876.
Some sample data:
sam=# select REACTANT, GMR_ALLERGY, GMR_ALLERGY_V_GMR_ALLERGIES, GMR_ALLERGY_V_VA_GENERIC, GMR_ALLERGY_V_DRUG, GMR_ALLERGY_V_DRUG_INGREDIENTS, GMR_ALLERGY_V_VA_DRUG_CLASS from PATIENT_ALLERGIES;
reactant | gmr_allergy | gmr_allergy_v_gmr_allergies | gmr_allergy_v_va_generic | gmr_allergy_v_drug | gmr_allergy_v_drug_ingredients | gmr_allergy_v_va_drug_class
---------------------------------------------------------------+------------------+-----------------------------+--------------------------+--------------------+--------------------------------+-----------------------------
MILK, ICE CREAM, YOGURT ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
SULFA DRUGS | 1;GMRD(120.82, | 1 | | | |
PENICILLIN | 16;PSNDF(50.6, | | 16 | | |
ASPIRIN | 1;GMRD(120.82, | 1 | | | |
SULFA DRUGS ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
ASCORBIC ACID DERIVATIVES | 1;GMRD(120.82, | 1 | | | |
SULFADIAZINE TABLETS 500MG | 1908;PSDRUG( | | | 1908 | |
SULFA DRUGS ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
ASCORBIC ACID DERIVATIVES ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
SUGAR & MILK | 1;GMRD(120.82, | 1 | | | |
BRONX TEST ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
PENICILLIN VK 250MG TAB | 1531;PSDRUG( | | | 1531 | |
BRONX TEST ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
PENICILLIN BRONX ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
PENICILLAMINE 250MG TAB | 892;PSDRUG( | | | 892 | |
ASPIRIN BUFFERED 325MG TAB | 280;PSDRUG( | | | 280 | |
SULFASALAZINE 500MG S.T. | 1583;PSDRUG( | | | 1583 | |
CODEINE 30MG & ASPIRIN C.T. | 1830;PSDRUG( | | | 1830 | |
SULFAPYRIDINE TABLETS 500MG | 1909;PSDRUG( | | | 1909 | |
SULFA DRUGS ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
ASPIRIN | 964;PSNDF(50.6, | | 964 | | |
PENICILLIN WEST HAVEN ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
PEN-G POTASSIUM 5MIL.UNIT VIAL | 1527;PSDRUG( | | | 1527 | |
PEN-G PROCAINE 1.2 M/U TUBEX | 1528;PSDRUG( | | | 1528 | |
PENICILLIN VK 250MG TAB | 1531;PSDRUG( | | | 1531 | |
PEN-V POTASSIUM 250MG/5ML SUSP. | 1532;PSDRUG( | | | 1532 | |
PEN-G PROCAINE 2.4 MIL UNITS | 2523;PSDRUG( | | | 2523 | |
SULFA DRUGS ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
PENICILLIN | 16;PSNDF(50.6, | | 16 | | |
TRANSFUSION REACTION | 49;GMRD(120.82, | 49 | | | |
TRANSFUSION REACTION | 49;GMRD(120.82, | 49 | | | |
ASPIRIN | 129;PS(50.416, | | | | 129 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
VANNA | 50;GMRD(120.82, | 50 | | | |
VANNA | 50;GMRD(120.82, | 50 | | | |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
IRON | 2046;PSNDF(50.6, | | 2046 | | |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
TEST ALLERGY (PATCH #5) | 50;GMRD(120.82, | 50 | | | |
UNKNOWN | 51;GMRD(120.82, | 51 | | | |
RADIOLOGY CONTRAST DYE ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
MILK | 29;GMRD(120.82, | 29 | | | |
MAYONASE ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
BANANA | 17;GMRD(120.82, | 17 | | | |
TUNAFISH ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
PEANUT BUTTER ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
JELOO ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
EGGS | 7;GMRD(120.82, | 7 | | | |
FISH | 26;GMRD(120.82, | 26 | | | |
SHELL FISH | 5;GMRD(120.82, | 5 | | | |
BANANA | 17;GMRD(120.82, | 17 | | | |
TUNA FISH ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
MEATLOAF ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
ADHESIVE TAPE | 8;GMRD(120.82, | 8 | | | |
BREAD ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
BREAD ( FREE TEXT ) | 1;GMRD(120.82, | 1 | | | |select REACTANT, GMR_ALLERGY, GMR_ALLERGY_V_GMR_ALLERGIES, GMR_ALLERGY_V_VA_GENERIC, GMR_ALLERGY_V_DRUG, GMR_ALLERGY_V_DRUG_INGREDIENTS, GMR_ALLERGY_V_VA_DRUG_CLASS from PATIENT_ALLERGIES where gmr_allergy_v_gmr_allergies IS NULL;
reactant | gmr_allergy | gmr_allergy_v_gmr_allergies | gmr_allergy_v_va_generic | gmr_allergy_v_drug | gmr_allergy_v_drug_ingredients | gmr_allergy_v_va_drug_class
------------------------------------------------------+-----------------+-----------------------------+--------------------------+--------------------+--------------------------------+-----------------------------
ASPIRIN | 129;PS(50.416, | | | | 129 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
AMPICILLIN | 138;PS(50.416, | | | | 138 |
LIDOCAINE | 860;PS(50.416, | | | | 860 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
CYCLOPHOSPHAMIDE | 711;PS(50.416, | | | | 711 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
TOBRAMYCIN | 1194;PS(50.416, | | | | 1194 |
AMPICILLIN | 138;PS(50.416, | | | | 138 |
ASAFETIDA/CAPSICUM/CASCARA SAGRADA/GINGER/NUX VOMICA | 1448;PS(50.416, | | | | 1448 |
LIDOCAINE | 860;PS(50.416, | | | | 860 |
CIMETIDINE | 1448;PS(50.416, | | | | 1448 |
ACETAMINOPHEN | 105;PS(50.416, | | | | 105 |
CIMETIDINE | 1448;PS(50.416, | | | | 1448 |
ACETAMINOPHEN | 105;PS(50.416, | | | | 105 |
ACETAMINOPHEN | 105;PS(50.416, | | | | 105 |
CORN SYRUP | 2733;PS(50.416, | | | | 2733 |
AMPICILLIN | 138;PS(50.416, | | | | 138 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
LIDOCAINE | 860;PS(50.416, | | | | 860 |
PENICILLIN | 2505;PS(50.416, | | | | 2505 |
AMPICILLIN | 138;PS(50.416, | | | | 138 |
LIDOCAINE | 860;PS(50.416, | | | | 860 |
ACETOHEXAMIDE | 95;PS(50.416, | | | | 95 |select o.OBJECT_OF_ORDER_V_PATIENT, p.NAME from ORDER1 o, PATIENT p WHERE o.OBJECT_OF_ORDER_V_PATIENT = p.PATIENT_ID;
object_of_order_v_patient | name
---------------------------+--------------------------------
274 | ZZZRETSIXFIFTYFIVE,PATIENT
239 | ONEHUNDREDTEN,PATIENT
239 | ONEHUNDREDTEN,PATIENT
41 | ZZZRETSIXFORTYTWO,PATIENT
41 | ZZZRETSIXFORTYTWO,PATIENTEdited by Sam Habiel