2 -- Comment Meta Language for sql upgrades:
4 -- Each section within an upgrade sql file is enveloped with an #If*/#EndIf block. At first glance, these appear to be standard mysql
5 -- comments meant to be cryptic hints to -other developers about the sql goodness contained therein. However, were you to rely on such basic premises,
6 -- you would find yourself grossly decieved. Indeed, without the knowledge that these comments are, in fact a sneakily embedded meta langauge derived
7 -- for a purpose none-other than to aid in the protection of the database during upgrades, you would no doubt be subject to much ridicule and public
8 -- beratement at the hands of the very developers who envisioned such a crafty use of comments. -jwallace
10 -- While these lines are as enigmatic as they are functional, there is a method to the madness. Let's take a moment to briefly go over proper comment meta language use.
12 -- The #If* sections have the behavior of functions and come complete with arguments supplied command-line style
14 -- Your Comment meta language lines cannot contain any other comment styles such as the nefarious double dashes "--" lest your lines be skipped and
15 -- the blocks automatcially executed with out regard to the existing database state.
17 -- Comment Meta Language Constructs:
20 -- argument: table_name
21 -- behavior: if the table_name does not exist, the block will be executed
24 -- argument: table_name
25 -- behavior: if the table_name does exist, the block will be executed
28 -- arguments: table_name colname
29 -- behavior: if the colname in the table_name table does not exist, the block will be executed
32 -- arguments: table_name colname value
33 -- behavior: If the table table_name does not have a column colname with a data type equal to value, then the block will be executed
36 -- arguments: table_name colname value
37 -- behavior: If the table table_name does not have a row where colname = value, the block will be executed.
40 -- arguments: table_name colname value colname2 value2
41 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
44 -- all blocks are terminated with and #EndIf statement.
46 #IfNotRow2D list_options list_id language option_id armenian
47 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'armenian', 'Armenian', 10, 0);
48 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'chinese', 'Chinese', 20, 0);
49 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'danish', 'Danish', 30, 0);
50 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'deaf', 'Deaf', 40, 0);
51 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'farsi', 'Farsi', 60, 0);
52 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'french', 'French', 70, 0);
53 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'german', 'German', 80, 0);
54 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'greek', 'Greek', 90, 0);
55 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'hmong', 'Hmong', 100, 0);
56 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'italian', 'Italian', 110, 0);
57 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'japanese', 'Japanese', 120, 0);
58 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'korean', 'Korean', 130, 0);
59 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'laotian', 'Laotian', 140, 0);
60 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'mien', 'Mien', 150, 0);
61 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'norwegian', 'Norwegian', 160, 0);
62 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'othrs', 'Others', 170, 0);
63 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'portuguese', 'Portuguese', 180, 0);
64 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'punjabi', 'Punjabi', 190, 0);
65 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'russian', 'Russian', 200, 0);
66 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'tagalog', 'Tagalog', 220, 0);
67 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'turkish', 'Turkish', 230, 0);
68 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'vietnamese', 'Vietnamese', 240, 0);
69 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'yiddish', 'Yiddish', 250, 0);
70 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('language', 'zulu', 'Zulu', 260, 0);
72 update list_options set seq = 50 where list_id = 'language' and option_id = 'English';
73 update list_options set seq = 210 where list_id = 'language' and option_id = 'Spanish';
76 #IfNotRow2D list_options list_id ethrace option_id aleut
77 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'aleut', 'ALEUT', 10, 0);
78 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'amer_indian', 'American Indian', 20, 0);
79 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'cambodian', 'Cambodian', 50, 0);
80 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'cs_american', 'Central/South American', 70, 0);
81 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'chinese', 'Chinese', 80, 0);
82 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'cuban', 'Cuban', 90, 0);
83 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'eskimo', 'Eskimo', 100, 0);
84 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'filipino', 'Filipino', 110, 0);
85 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'guamanian', 'Guamanian', 120, 0);
86 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'hawaiian', 'Hawaiian', 130, 0);
87 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'othr_us', 'Hispanic - Other (Born in US)', 150, 0);
88 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'othr_non_us', 'Hispanic - Other (Born outside US)', 160, 0);
89 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'hmong', 'Hmong', 170, 0);
90 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'indian', 'Indian', 180, 0);
91 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'japanese', 'Japanese', 190, 0);
92 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'korean', 'Korean', 200, 0);
93 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'laotian', 'Laotian', 210, 0);
94 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'mexican', 'Mexican/MexAmer/Chicano', 220, 0);
95 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'mlt-race', 'Multiracial', 230, 0);
96 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'othr', 'Other', 240, 0);
97 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'othr_spec', 'Other - Specified', 250, 0);
98 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'pac_island', 'Pacific Islander', 260, 0);
99 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'puerto_rican', 'Puerto Rican', 270, 0);
100 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'refused', 'Refused To State', 280, 0);
101 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'samoan', 'Samoan', 290, 0);
102 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'spec', 'Specified', 300, 0);
103 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'thai', 'Thai', 310, 0);
104 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'unknown', 'Unknown', 320, 0);
105 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'unspec', 'Unspecified', 330, 0);
106 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'vietnamese', 'Vietnamese', 340, 0);
107 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'white', 'White', 350, 0);
108 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ethrace', 'withheld', 'Withheld', 360, 0);
111 update list_options set seq = 60 where list_id = 'ethrace' and option_id = 'Caucasian';
112 update list_options set seq = 30 where list_id = 'ethrace' and option_id = 'Asian';
113 update list_options set seq = 40 where list_id = 'ethrace' and option_id = 'Black';
114 update list_options set seq = 140 where list_id = 'ethrace' and option_id = 'Hispanic';
116 #IfNotRow2D list_options list_id lists option_id eligibility
117 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists', 'eligibility', 'Eligibility', 47, 0);
118 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('eligibility', 'eligible', 'Eligible', 10, 0);
119 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('eligibility', 'ineligible', 'Ineligible', 20, 0);
122 #IfNotRow2D layout_options form_id DEM field_id vfc
123 INSERT INTO `layout_options` VALUES ('DEM', 'vfc', '5Stats', 'VFC', 12, 1, 1, 20, 0, 'eligibility', 1, 1, '', '', 'Eligibility status for Vaccine for Children supplied vaccine');
126 #IfNotRow2D layout_options form_id DEM field_id mothersname
127 INSERT INTO `layout_options` VALUES ('DEM', 'mothersname', '2Contact', 'Mother''s Name', 6, 2, 1, 20, 63, '', 1, 1, '', '', '');
130 #IfNotRow2D layout_options form_id DEM field_id guardiansname
131 INSERT INTO `layout_options` VALUES ('DEM', 'guardiansname', '2Contact', 'Guardian''s Name', 7, 2, 1, 20, 63, '', 1, 1, '', '', '');
134 #IfNotRow2D layout_options form_id DEM field_id allow_imm_reg_use
135 INSERT INTO `layout_options` VALUES ('DEM', 'allow_imm_reg_use', '3Choices', 'Allow Immunization Registry Use', 9, 1, 1, 0, 0, 'yesno', 1, 1, '', '', '');
138 #IfNotRow2D layout_options form_id DEM field_id allow_imm_info_share
139 INSERT INTO `layout_options` VALUES ('DEM', 'allow_imm_info_share', '3Choices', 'Allow Immunization Info Sharing', 10, 1, 1, 0, 0, 'yesno', 1, 1, '', '', '');
142 #IfNotRow2D layout_options form_id DEM field_id allow_health_info_ex
143 INSERT INTO `layout_options` VALUES ('DEM', 'allow_health_info_ex', '3Choices', 'Allow Health Information Exchange', 11, 1, 1, 0, 0, 'yesno', 1, 1, '', '', '');
146 #IfMissingColumn patient_data vfc
147 ALTER TABlE patient_data
148 ADD vfc varchar(255) NOT NULL DEFAULT '',
149 ADD mothersname varchar(255) NOT NULL DEFAULT '',
150 ADD guardiansname varchar(255) NOT NULL DEFAULT '',
151 ADD allow_imm_reg_use varchar(255) NOT NULL DEFAULT '',
152 ADD allow_imm_info_share varchar(255) NOT NULL DEFAULT '',
153 ADD allow_health_info_ex varchar(255) NOT NULL DEFAULT '';
156 #IfNotRow categories name Advance Directive
157 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Advance Directive', '', 1, rght, rght + 7 from categories where name = 'Categories';
158 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Do Not Resuscitate Order', '', (select id from categories where name = 'Advance Directive'), rght + 1, rght + 2 from categories where name = 'Categories';
159 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Durable Power of Attorney', '', (select id from categories where name = 'Advance Directive'), rght + 3, rght + 4 from categories where name = 'Categories';
160 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Living Will', '', (select id from categories where name = 'Advance Directive'), rght + 5, rght + 6 from categories where name = 'Categories';
161 UPDATE categories SET rght = rght + 8 WHERE name = 'Categories';
162 UPDATE categories_seq SET id = (select MAX(id) from categories);
165 #IfMissingColumn patient_data completed_ad
166 ALTER TABLE patient_data
167 ADD completed_ad VARCHAR(3) NOT NULL DEFAULT 'NO',
168 ADD ad_reviewed date DEFAULT NULL;
171 #IfNotRow2D list_options list_id lists option_id apptstat
172 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists' ,'apptstat','Appointment Statuses', 1,0);
173 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','-' ,'- None' , 5,0);
174 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','*' ,'* Reminder done' ,10,0);
175 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','+' ,'+ Chart pulled' ,15,0);
176 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','x' ,'x Canceled' ,20,0);
177 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','?' ,'? No show' ,25,0);
178 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','@' ,'@ Arrived' ,30,0);
179 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','~' ,'~ Arrived late' ,35,0);
180 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','!' ,'! Left w/o visit' ,40,0);
181 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','#' ,'# Ins/fin issue' ,45,0);
182 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','<' ,'< In exam room' ,50,0);
183 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','>' ,'> Checked out' ,55,0);
184 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','$' ,'$ Coding done' ,60,0);
185 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','%' ,'% Canceled < 24h' ,65,0);
186 ALTER TABLE openemr_postcalendar_events CHANGE pc_apptstatus pc_apptstatus varchar(15) NOT NULL DEFAULT '-';
189 #IfNotRow2D layout_options form_id DEM field_id referral_source
190 INSERT INTO `layout_options` VALUES ('DEM', 'referral_source', '5Stats', 'Referral Source',10, 26, 1, 0, 0, 'refsource', 1, 1, '', '', 'How did they hear about us');
193 #IfMissingColumn list_options notes
194 ALTER TABLE list_options
195 CHANGE mapping mapping varchar(31) NOT NULL DEFAULT '',
196 ADD notes varchar(255) NOT NULL DEFAULT '';
199 #IfNotRow2D list_options list_id lists option_id warehouse
200 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','warehouse','Warehouses',21,0);
201 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('warehouse','onsite','On Site', 5,0);
204 #IfNotRow2D list_options list_id lists option_id abook_type
205 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','abook_type' ,'Address Book Types' , 1,0);
206 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','ord_img','Imaging Service' , 5,0);
207 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','ord_imm','Immunization Service',10,0);
208 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','ord_lab','Lab Service' ,15,0);
209 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','spe' ,'Specialist' ,20,0);
210 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','vendor' ,'Vendor' ,25,0);
211 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','oth' ,'Other' ,95,0);
214 #IfMissingColumn users abook_type
216 ADD abook_type varchar(31) NOT NULL DEFAULT '';
219 #IfNotRow2D list_options list_id lists option_id proc_type
220 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_type','Procedure Types', 1,0);
221 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_type','grp','Group' ,10,0);
222 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_type','ord','Procedure Order',20,0);
223 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_type','res','Discrete Result',30,0);
224 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_type','rec','Recommendation' ,40,0);
227 #IfNotRow2D list_options list_id lists option_id proc_body_site
228 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_body_site','Procedure Body Sites', 1,0);
229 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_body_site','arm' ,'Arm' ,10,0);
230 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_body_site','buttock','Buttock',20,0);
231 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_body_site','oth' ,'Other' ,90,0);
234 #IfNotRow2D list_options list_id lists option_id proc_specimen
235 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_specimen','Procedure Specimen Types', 1,0);
236 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_specimen','blood' ,'Blood' ,10,0);
237 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_specimen','saliva','Saliva',20,0);
238 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_specimen','urine' ,'Urine' ,30,0);
239 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_specimen','oth' ,'Other' ,90,0);
242 #IfNotRow2D list_options list_id lists option_id proc_route
243 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_route','Procedure Routes', 1,0);
244 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_route','inj' ,'Injection',10,0);
245 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_route','oral','Oral' ,20,0);
246 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_route','oth' ,'Other' ,90,0);
249 #IfNotRow2D list_options list_id lists option_id proc_lat
250 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_lat','Procedure Lateralities', 1,0);
251 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_lat','left' ,'Left' ,10,0);
252 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_lat','right','Right' ,20,0);
253 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_lat','bilat','Bilateral',30,0);
256 #IfNotRow2D list_options list_id lists option_id proc_unit
257 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('lists','proc_unit','Procedure Units', 1);
258 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','bool' ,'Boolean' , 5);
259 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','cu_mm' ,'CU.MM' , 10);
260 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','fl' ,'FL' , 20);
261 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','g_dl' ,'G/DL' , 30);
262 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','gm_dl' ,'GM/DL' , 40);
263 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','hmol_l' ,'HMOL/L' , 50);
264 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','iu_l' ,'IU/L' , 60);
265 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','mg_dl' ,'MG/DL' , 70);
266 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','mil_cu_mm' ,'Mil/CU.MM' , 80);
267 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','percent' ,'Percent' , 90);
268 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','percentile' ,'Percentile' ,100);
269 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','pg' ,'PG' ,110);
270 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','ratio' ,'Ratio' ,120);
271 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','thous_cu_mm','Thous/CU.MM',130);
272 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','units' ,'Units' ,140);
273 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','units_l' ,'Units/L' ,150);
274 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','days' ,'Days' ,600);
275 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','weeks' ,'Weeks' ,610);
276 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','months' ,'Months' ,620);
277 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','oth' ,'Other' ,990);
280 #IfNotRow2D list_options list_id lists option_id ord_priority
281 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','ord_priority','Order Priorities', 1,0);
282 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_priority','high' ,'High' ,10,0);
283 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_priority','normal','Normal',20,0);
286 #IfNotRow2D list_options list_id lists option_id ord_status
287 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','ord_status','Order Statuses', 1,0);
288 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_status','pending' ,'Pending' ,10,0);
289 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_status','routed' ,'Routed' ,20,0);
290 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_status','complete','Complete',30,0);
291 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_status','canceled','Canceled',40,0);
294 #IfNotRow2D list_options list_id lists option_id proc_rep_status
295 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_rep_status','Procedure Report Statuses', 1,0);
296 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','final' ,'Final' ,10,0);
297 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','review' ,'Reviewed' ,20,0);
298 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','prelim' ,'Preliminary',30,0);
299 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','cancel' ,'Canceled' ,40,0);
300 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','error' ,'Error' ,50,0);
301 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','correct','Corrected' ,60,0);
304 #IfNotRow2D list_options list_id lists option_id proc_res_abnormal
305 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_res_abnormal','Procedure Result Abnormal', 1,0);
306 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_abnormal','no' ,'No' ,10,0);
307 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_abnormal','yes' ,'Yes' ,20,0);
308 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_abnormal','high','High',30,0);
309 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_abnormal','low' ,'Low' ,40,0);
312 #IfNotRow2D list_options list_id lists option_id proc_res_status
313 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_res_status','Procedure Result Statuses', 1,0);
314 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','final' ,'Final' ,10,0);
315 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','prelim' ,'Preliminary',20,0);
316 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','cancel' ,'Canceled' ,30,0);
317 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','error' ,'Error' ,40,0);
318 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','correct' ,'Corrected' ,50,0);
319 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','incomplete','Incomplete' ,60,0);
322 #IfNotRow2D list_options list_id lists option_id proc_res_bool
323 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_res_bool','Procedure Boolean Results', 1,0);
324 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_bool','neg' ,'Negative',10,0);
325 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_bool','pos' ,'Positive',20,0);
328 #IfNotTable procedure_type
329 CREATE TABLE `procedure_type` (
330 `procedure_type_id` bigint(20) NOT NULL AUTO_INCREMENT,
331 `parent` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references procedure_type.procedure_type_id',
332 `name` varchar(63) NOT NULL DEFAULT '' COMMENT 'name for this category, procedure or result type',
333 `lab_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references users.id, 0 means default to parent',
334 `procedure_code` varchar(31) NOT NULL DEFAULT '' COMMENT 'code identifying this procedure',
335 `procedure_type` varchar(31) NOT NULL DEFAULT '' COMMENT 'see list proc_type',
336 `body_site` varchar(31) NOT NULL DEFAULT '' COMMENT 'where to do injection, e.g. arm, buttok',
337 `specimen` varchar(31) NOT NULL DEFAULT '' COMMENT 'blood, urine, saliva, etc.',
338 `route_admin` varchar(31) NOT NULL DEFAULT '' COMMENT 'oral, injection',
339 `laterality` varchar(31) NOT NULL DEFAULT '' COMMENT 'left, right, ...',
340 `description` varchar(255) NOT NULL DEFAULT '' COMMENT 'descriptive text for procedure_code',
341 `standard_code` varchar(255) NOT NULL DEFAULT '' COMMENT 'industry standard code type and code (e.g. CPT4:12345)',
342 `related_code` varchar(255) NOT NULL DEFAULT '' COMMENT 'suggested code(s) for followup services if result is abnormal',
343 `units` varchar(31) NOT NULL DEFAULT '' COMMENT 'default for procedure_result.units',
344 `range` varchar(255) NOT NULL DEFAULT '' COMMENT 'default for procedure_result.range',
345 `seq` int(11) NOT NULL default 0 COMMENT 'sequence number for ordering',
346 PRIMARY KEY (`procedure_type_id`),
351 #IfNotTable procedure_order
352 CREATE TABLE `procedure_order` (
353 `procedure_order_id` bigint(20) NOT NULL AUTO_INCREMENT,
354 `procedure_type_id` bigint(20) NOT NULL COMMENT 'references procedure_type.procedure_type_id',
355 `provider_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references users.id',
356 `patient_id` bigint(20) NOT NULL COMMENT 'references patient_data.pid',
357 `encounter_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references form_encounter.encounter',
358 `date_collected` datetime DEFAULT NULL COMMENT 'time specimen collected',
359 `date_ordered` date DEFAULT NULL,
360 `order_priority` varchar(31) NOT NULL DEFAULT '',
361 `order_status` varchar(31) NOT NULL DEFAULT '' COMMENT 'pending,routed,complete,canceled',
362 `patient_instructions` text NOT NULL DEFAULT '',
363 `activity` tinyint(1) NOT NULL DEFAULT 1 COMMENT '0 if deleted',
364 `control_id` bigint(20) NOT NULL COMMENT 'This is the CONTROL ID that is sent back from lab',
365 PRIMARY KEY (`procedure_order_id`),
366 KEY datepid (date_ordered, patient_id)
370 #IfNotTable procedure_report
371 CREATE TABLE `procedure_report` (
372 `procedure_report_id` bigint(20) NOT NULL AUTO_INCREMENT,
373 `procedure_order_id` bigint(20) DEFAULT NULL COMMENT 'references procedure_order.procedure_order_id',
374 `date_collected` datetime DEFAULT NULL,
375 `date_report` date DEFAULT NULL,
376 `source` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references users.id, who entered this data',
377 `specimen_num` varchar(63) NOT NULL DEFAULT '',
378 `report_status` varchar(31) NOT NULL DEFAULT '' COMMENT 'received,complete,error',
379 `review_status` varchar(31) NOT NULL DEFAULT 'received' COMMENT 'panding reivew status: received,reviewed',
380 PRIMARY KEY (`procedure_report_id`),
381 KEY procedure_order_id (procedure_order_id)
385 #IfNotTable procedure_result
386 CREATE TABLE `procedure_result` (
387 `procedure_result_id` bigint(20) NOT NULL AUTO_INCREMENT,
388 `procedure_report_id` bigint(20) NOT NULL COMMENT 'references procedure_report.procedure_report_id',
389 `procedure_type_id` bigint(20) NOT NULL COMMENT 'references procedure_type.procedure_type_id',
390 `date` datetime DEFAULT NULL COMMENT 'lab-provided date specific to this result',
391 `facility` varchar(255) NOT NULL DEFAULT '' COMMENT 'lab-provided testing facility ID',
392 `units` varchar(31) NOT NULL DEFAULT '',
393 `result` varchar(255) NOT NULL DEFAULT '',
394 `range` varchar(255) NOT NULL DEFAULT '',
395 `abnormal` varchar(31) NOT NULL DEFAULT '' COMMENT 'no,yes,high,low',
396 `comments` text NOT NULL DEFAULT '' COMMENT 'comments from the lab',
397 `document_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references documents.id if this result is a document',
398 `result_status` varchar(31) NOT NULL DEFAULT '' COMMENT 'preliminary, cannot be done, final, corrected, incompete...etc.',
399 PRIMARY KEY (`procedure_result_id`),
400 KEY procedure_report_id (procedure_report_id)
404 #IfMissingColumn history_data recreational_drugs
405 ALTER TABLE history_data ADD recreational_drugs longtext;
408 update layout_options set seq = 1, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'coffee';
409 update layout_options set seq = 6, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'exercise_patterns';
410 update layout_options set seq = 2, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'tobacco';
411 update layout_options set seq = 3, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'alcohol';
412 update layout_options set seq = 5, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'counseling';
413 update layout_options set seq = 7, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'hazardous_activities';
414 update layout_options set seq = 8, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'sleep_patterns';
415 update layout_options set seq = 9, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'seatbelt_use';
417 #IfNotRow2D layout_options form_id HIS field_id recreational_drugs
418 INSERT INTO layout_options (form_id, field_id, group_name, title, seq, data_type, uor, fld_length, max_length, list_id, titlecols, datacols, default_value, edit_options, description) VALUES ('HIS','recreational_drugs','4Lifestyle','Recreational Drugs',4,28,1,20,255,'',1,3,'','' ,'Recreational drugs use');
421 #IfMissingColumn users pwd_expiration_date
422 ALTER TABLE users ADD pwd_expiration_date date DEFAULT NULL;
425 #IfMissingColumn users pwd_history1
426 ALTER TABLE users ADD pwd_history1 longtext DEFAULT NULL;
429 #IfMissingColumn users pwd_history2
430 ALTER TABLE users ADD pwd_history2 longtext DEFAULT NULL;
433 #IfMissingColumn drug_inventory warehouse_id
434 ALTER TABLE drug_inventory
435 ADD warehouse_id varchar(31) NOT NULL DEFAULT '';
438 #IfMissingColumn drug_inventory vendor_id
439 ALTER TABLE drug_inventory
440 ADD vendor_id bigint(20) NOT NULL DEFAULT 0;
443 #IfMissingColumn drug_sales xfer_inventory_id
444 ALTER TABLE drug_sales
445 ADD xfer_inventory_id int(11) NOT NULL DEFAULT 0;
448 #IfMissingColumn drugs allow_combining
450 ADD allow_combining tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = allow filling an order from multiple lots',
451 ADD allow_multiple tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = allow multiple lots at one warehouse';
454 #IfNotRow registry directory procedure_order
455 INSERT INTO `registry` VALUES ('Procedure Order', 1, 'procedure_order', NULL, 1, 1, '2010-02-25 00:00:00', 0, 'Administrative', '');
458 UPDATE registry SET category = 'Administrative' WHERE category = 'category' AND directory = 'fee_sheet';
459 UPDATE registry SET category = 'Administrative' WHERE category = 'category' AND directory = 'procedure_order';
460 UPDATE registry SET category = 'Administrative' WHERE category = 'category' AND directory = 'newpatient';
461 UPDATE registry SET category = 'Administrative' WHERE category = 'category' AND directory = 'misc_billing_options';
462 UPDATE registry SET category = 'Clinical' WHERE category = 'category';
464 #IfMissingColumn log patient_id
465 ALTER TABLE log ADD patient_id bigint(20) DEFAULT NULL;
468 #IfMissingColumn log success
469 ALTER TABLE log ADD success tinyint(1) DEFAULT 1;
472 #IfMissingColumn log checksum
473 ALTER TABLE log ADD checksum longtext DEFAULT NULL;
476 #IfMissingColumn log crt_user
477 ALTER TABLE log ADD crt_user varchar(255) DEFAULT NULL;
480 #IfMissingColumn users default_warehouse
481 ALTER TABLE users ADD default_warehouse varchar(31) NOT NULL DEFAULT '';
484 UPDATE layout_options SET edit_options = 'N' WHERE form_id = 'DEM' AND field_id = 'title' AND edit_options = '';
485 UPDATE layout_options SET edit_options = 'CD' WHERE form_id = 'DEM' AND field_id = 'fname' AND edit_options = 'C';
486 UPDATE layout_options SET edit_options = 'CD' WHERE form_id = 'DEM' AND field_id = 'lname' AND edit_options = 'C';
487 UPDATE layout_options SET edit_options = 'ND' WHERE form_id = 'DEM' AND field_id = 'pubpid' AND edit_options = '';
488 UPDATE layout_options SET edit_options = 'N' WHERE form_id = 'DEM' AND field_id = 'sex' AND edit_options = '';
490 #IfNotRow2D list_options list_id lists option_id message_status
491 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists' ,'message_status','Message Status',45,0);
492 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('message_status','Done' ,'Done' , 5,0);
493 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('message_status','Forwarded' ,'Forwarded' ,10,0);
494 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('message_status','New' ,'New' ,15,0);
495 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('message_status','Read' ,'Read' ,20,0);
498 #IfNotRow2D list_options list_id note_type option_id Lab Results
499 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('note_type','Lab Results' ,'Lab Results', 15,0);
501 #IfNotRow2D list_options list_id note_type option_id New Orders
502 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('note_type','New Orders' ,'New Orders', 20,0);
504 #IfNotRow2D list_options list_id note_type option_id Patient Reminders
505 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('note_type','Patient Reminders' ,'Patient Reminders', 25,0);
508 #IfMissingColumn pnotes message_status
510 ADD message_status VARCHAR(20) NOT NULL DEFAULT 'New';
514 CREATE TABLE `globals` (
515 `gl_name` varchar(63) NOT NULL,
516 `gl_index` int(11) NOT NULL DEFAULT 0,
517 `gl_value` varchar(255) NOT NULL DEFAULT '',
518 PRIMARY KEY (`gl_name`, `gl_index`)
522 #IfNotTable lang_custom
523 CREATE TABLE lang_custom (
524 `lang_description` varchar(100) NOT NULL default '',
525 `lang_code` char(2) NOT NULL default '',
526 `constant_name` varchar(255) NOT NULL default '',
527 `definition` mediumtext NOT NULL default ''
531 #IfNotRow2D list_options list_id lists option_id irnpool
532 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists' ,'irnpool','Invoice Reference Number Pools', 1,0);
533 INSERT INTO list_options ( list_id, option_id, title, seq, is_default, notes ) VALUES ('irnpool','main','Main',1,1,'000001');
536 #IfMissingColumn users irnpool
537 ALTER TABLE users ADD irnpool varchar(31) NOT NULL DEFAULT '';
540 #IfMissingColumn form_encounter invoice_refno
541 ALTER TABLE form_encounter ADD invoice_refno varchar(31) NOT NULL DEFAULT '';
544 #IfMissingColumn drug_sales notes
545 ALTER TABLE drug_sales
546 ADD notes varchar(255) NOT NULL DEFAULT '';
549 #IfNotTable code_types
550 CREATE TABLE code_types (
551 ct_key varchar(15) NOT NULL COMMENT 'short alphanumeric name',
552 ct_id int(11) UNIQUE NOT NULL COMMENT 'numeric identifier',
553 ct_seq int(11) NOT NULL DEFAULT 0 COMMENT 'sort order',
554 ct_mod int(11) NOT NULL DEFAULT 0 COMMENT 'length of modifier field',
555 ct_just varchar(15) NOT NULL DEFAULT ''COMMENT 'ct_key of justify type, if any',
556 ct_mask varchar(9) NOT NULL DEFAULT ''COMMENT 'formatting mask for code values',
557 ct_fee tinyint(1) NOT NULL default 0 COMMENT '1 if fees are used',
558 ct_rel tinyint(1) NOT NULL default 0 COMMENT '1 if can relate to other code types',
559 ct_nofs tinyint(1) NOT NULL default 0 COMMENT '1 if to be hidden in the fee sheet',
560 ct_diag tinyint(1) NOT NULL default 0 COMMENT '1 if this is a diagnosis type',
563 INSERT INTO code_types (ct_key, ct_id, ct_seq, ct_mod, ct_just, ct_fee, ct_rel, ct_nofs, ct_diag ) VALUES ('ICD9' , 2, 1, 2, '' , 0, 0, 0, 1);
564 INSERT INTO code_types (ct_key, ct_id, ct_seq, ct_mod, ct_just, ct_fee, ct_rel, ct_nofs, ct_diag ) VALUES ('CPT4' , 1, 2, 2, 'ICD9', 1, 0, 0, 0);
565 INSERT INTO code_types (ct_key, ct_id, ct_seq, ct_mod, ct_just, ct_fee, ct_rel, ct_nofs, ct_diag ) VALUES ('HCPCS', 3, 3, 2, 'ICD9', 1, 0, 0, 0);
568 #IfNotRow2D list_options list_id lists option_id code_types
569 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('lists', 'code_types', 'Code Types', 1);