2 -- Comment Meta Language Constructs:
5 -- argument: table_name
6 -- behavior: if the table_name does not exist, the block will be executed
9 -- argument: table_name
10 -- behavior: if the table_name does exist, the block will be executed
13 -- arguments: table_name colname
14 -- behavior: if the table exists but the column does not, the block will be executed
17 -- arguments: table_name colname value
18 -- 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
21 -- arguments: table_name colname value
22 -- behavior: If the table table_name does not have a row where colname = value, the block will be executed.
25 -- arguments: table_name colname value colname2 value2
26 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
29 -- arguments: table_name colname value colname2 value2 colname3 value3
30 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
33 -- arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
34 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2 AND colname3 = value3 AND colname4 = value4, the block will be executed.
37 -- desc: This is a very specialized function to allow adding items to the list_options table to avoid both redundant option_id and title in each element.
38 -- arguments: table_name colname value colname2 value2 colname3 value3
39 -- behavior: The block will be executed if both statements below are true:
40 -- 1) The table table_name does not have a row where colname = value AND colname2 = value2.
41 -- 2) The table table_name does not have a row where colname = value AND colname3 = value3.
44 -- arguments: table_name colname value colname2 value2
45 -- behavior: If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
48 -- desc: This function is most often used for dropping of indexes/keys.
49 -- arguments: table_name colname
50 -- behavior: If the table and index exist the relevant statements are executed, otherwise not.
53 -- desc: This function will allow adding of indexes/keys.
54 -- arguments: table_name colname
55 -- behavior: If the index does not exist, it will be created
58 -- all blocks are terminated with a #EndIf statement.
60 #IfNotTable report_results
61 CREATE TABLE `report_results` (
62 `report_id` bigint(20) NOT NULL,
63 `field_id` varchar(31) NOT NULL default '',
65 PRIMARY KEY (`report_id`,`field_id`)
69 #IfMissingColumn version v_acl
70 ALTER TABLE `version` ADD COLUMN `v_acl` int(11) NOT NULL DEFAULT 0;
73 #IfMissingColumn documents_legal_detail dld_moved
74 ALTER TABLE `documents_legal_detail` ADD COLUMN `dld_moved` tinyint(4) NOT NULL DEFAULT '0';
77 #IfMissingColumn documents_legal_detail dld_patient_comments
78 ALTER TABLE `documents_legal_detail` ADD COLUMN `dld_patient_comments` text COMMENT 'Patient comments stored here';
81 #IfMissingColumn documents_legal_master dlm_upload_type
82 ALTER TABLE `documents_legal_master` ADD COLUMN `dlm_upload_type` tinyint(4) DEFAULT '0' COMMENT '0-Provider Uploaded,1-Patient Uploaded';
85 #IfMissingColumn list_options codes
86 ALTER TABLE `list_options` ADD COLUMN `codes` varchar(255) NOT NULL DEFAULT '';
87 UPDATE list_options SET `codes`='SNOMED-CT:449868002' WHERE list_id='smoking_status' AND option_id='1' AND title='Current every day smoker';
88 UPDATE list_options SET `codes`='SNOMED-CT:428041000124106' WHERE list_id='smoking_status' AND option_id='2' AND title='Current some day smoker';
89 UPDATE list_options SET `codes`='SNOMED-CT:8517006' WHERE list_id='smoking_status' AND option_id='3' AND title='Former smoker';
90 UPDATE list_options SET `codes`='SNOMED-CT:266919005' WHERE list_id='smoking_status' AND option_id='4' AND title='Never smoker';
91 UPDATE list_options SET `codes`='SNOMED-CT:77176002' WHERE list_id='smoking_status' AND option_id='5' AND title='Smoker, current status unknown';
92 UPDATE list_options SET `codes`='SNOMED-CT:266927001' WHERE list_id='smoking_status' AND option_id='9' AND title='Unknown if ever smoked';
95 #IfNotRow2Dx2 list_options list_id smoking_status option_id 15 title Heavy tobacco smoker
96 INSERT INTO list_options ( list_id, option_id, title, seq, is_default, codes ) VALUES ('smoking_status', '15', 'Heavy tobacco smoker', 70, 0, "SNOMED-CT:428071000124103");
99 #IfNotRow2Dx2 list_options list_id smoking_status option_id 16 title Light tobacco smoker
100 INSERT INTO list_options ( list_id, option_id, title, seq, is_default, codes ) VALUES ('smoking_status', '16', 'Light tobacco smoker', 80, 0, "SNOMED-CT:428061000124105");
103 #IfMissingColumn code_types ct_term
104 ALTER TABLE `code_types` ADD COLUMN ct_term tinyint(1) NOT NULL default 0 COMMENT '1 if this is a clinical term';
107 #IfNotRow code_types ct_key SNOMED-CT
108 DROP TABLE IF EXISTS `temp_table_one`;
109 CREATE TABLE `temp_table_one` (
110 `id` int(11) NOT NULL DEFAULT '0',
111 `seq` int(11) NOT NULL DEFAULT '0'
113 INSERT INTO `temp_table_one` (`id`, `seq`) VALUES ( IF( ((SELECT MAX(`ct_id`) FROM `code_types`)>=100), ((SELECT MAX(`ct_id`) FROM `code_types`) + 1), 100 ) , IF( ((SELECT MAX(`ct_seq`) FROM `code_types`)>=100), ((SELECT MAX(`ct_seq`) FROM `code_types`) + 1), 100 ) );
114 INSERT INTO code_types (ct_key, ct_id, ct_seq, ct_mod, ct_just, ct_fee, ct_rel, ct_nofs, ct_diag, ct_active, ct_label, ct_external, ct_claim, ct_proc, ct_term ) VALUES ('SNOMED-CT' , (SELECT MAX(`id`) FROM `temp_table_one`), (SELECT MAX(`seq`) FROM `temp_table_one`), 0, '', 0, 0, 1, 0, 0, 'SNOMED Clinical Term', 7, 0, 0, 1);
115 DROP TABLE `temp_table_one`;
118 #IfNotColumnType codes code varchar(25)
119 ALTER TABLE `codes` CHANGE `code` `code` varchar(25) NOT NULL default '';
122 #IfNotColumnType billing code varchar(20)
123 ALTER TABLE `billing` CHANGE `code` `code` varchar(20) default NULL;
126 #IfNotColumnType ar_activity code varchar(20)
127 ALTER TABLE `ar_activity` CHANGE `code` `code` varchar(20) NOT NULL COMMENT 'empty means claim level';
130 #IfNotTable procedure_questions
131 CREATE TABLE `procedure_questions` (
132 `lab_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references users.id to identify the lab',
133 `procedure_code` varchar(31) NOT NULL DEFAULT '' COMMENT 'references procedure_type.procedure_code to identify this order type',
134 `question_code` varchar(31) NOT NULL DEFAULT '' COMMENT 'code identifying this question',
135 `seq` int(11) NOT NULL default 0 COMMENT 'sequence number for ordering',
136 `question_text` varchar(255) NOT NULL DEFAULT '' COMMENT 'descriptive text for question_code',
137 `required` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = required, 0 = not',
138 `maxsize` int NOT NULL DEFAULT 0 COMMENT 'maximum length if text input field',
139 `fldtype` char(1) NOT NULL DEFAULT 'T' COMMENT 'Text, Number, Select, Multiselect, Date, Gestational-age',
140 `options` text NOT NULL DEFAULT '' COMMENT 'choices for fldtype S and T',
141 `activity` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = active, 0 = inactive',
142 PRIMARY KEY (`lab_id`, `procedure_code`, `question_code`)
146 #IfMissingColumn procedure_type activity
147 ALTER TABLE `procedure_type` ADD COLUMN `activity` tinyint(1) NOT NULL default 1;
150 #IfMissingColumn procedure_type notes
151 ALTER TABLE `procedure_type` ADD COLUMN `notes` varchar(255) NOT NULL default '';
154 #IfNotTable procedure_answers
155 CREATE TABLE `procedure_answers` (
156 `procedure_order_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references procedure_order.procedure_order_id',
157 `procedure_order_seq` int(11) NOT NULL DEFAULT 1 COMMENT 'references procedure_order_code.seq',
158 `question_code` varchar(31) NOT NULL DEFAULT '' COMMENT 'references procedure_questions.question_code',
159 `answer_seq` int(11) NOT NULL AUTO_INCREMENT COMMENT 'supports multiple-choice questions',
160 `answer` varchar(255) NOT NULL DEFAULT '' COMMENT 'answer data',
161 PRIMARY KEY (`procedure_order_id`, `procedure_order_seq`, `question_code`, `answer_seq`)
165 #IfNotTable procedure_providers
166 CREATE TABLE `procedure_providers` (
167 `ppid` bigint(20) NOT NULL auto_increment,
168 `name` varchar(255) NOT NULL DEFAULT '',
169 `npi` varchar(15) NOT NULL DEFAULT '',
170 `protocol` varchar(15) NOT NULL DEFAULT 'DL',
171 `login` varchar(255) NOT NULL DEFAULT '',
172 `password` varchar(255) NOT NULL DEFAULT '',
173 `orders_path` varchar(255) NOT NULL DEFAULT '',
174 `results_path` varchar(255) NOT NULL DEFAULT '',
175 `notes` text NOT NULL DEFAULT '',
180 #IfNotTable procedure_order_code
181 CREATE TABLE `procedure_order_code` (
182 `procedure_order_id` bigint(20) NOT NULL,
183 `procedure_order_seq` int(11) NOT NULL AUTO_INCREMENT COMMENT 'supports multiple tests per order',
184 `procedure_type_id` bigint(20) NOT NULL COMMENT 'references procedure_type.procedure_type_id',
185 `procedure_code` varchar(31) NOT NULL DEFAULT '' COMMENT 'copy of procedure_type.procedure_code',
186 PRIMARY KEY (`procedure_order_id`, `procedure_order_seq`)
188 INSERT INTO procedure_order_code
189 SELECT po.procedure_order_id, 1, po.procedure_type_id, pt.procedure_code
190 FROM procedure_order AS po
191 LEFT JOIN procedure_type AS pt ON pt.procedure_type_id = po.procedure_type_id;
192 ALTER TABLE `procedure_order`
193 DROP COLUMN `procedure_type_id`;
196 #IfMissingColumn procedure_order lab_id
197 ALTER TABLE `procedure_order`
198 ADD COLUMN `lab_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references procedure_providers.ppid',
199 ADD COLUMN `specimen_type` varchar(31) NOT NULL DEFAULT '' COMMENT 'from the Specimen_Type list',
200 ADD COLUMN `specimen_location` varchar(31) NOT NULL DEFAULT '' COMMENT 'from the Specimen_Location list',
201 ADD COLUMN `specimen_volume` varchar(30) NOT NULL DEFAULT '' COMMENT 'from a text input field';
202 UPDATE procedure_order AS po, procedure_order_code AS pc, procedure_type AS pt
203 SET po.lab_id = pt.lab_id WHERE
205 pc.procedure_order_id = po.procedure_order_id AND
206 pt.procedure_type_id = pc.procedure_type_id AND
210 #IfMissingColumn procedure_report procedure_order_seq
211 ALTER TABLE procedure_report
212 ADD COLUMN `procedure_order_seq` int(11) NOT NULL DEFAULT 1 COMMENT 'references procedure_order_code.procedure_order_seq';
215 #IfMissingColumn procedure_order diagnoses
216 ALTER TABLE `procedure_order`
217 ADD COLUMN `diagnoses` text NOT NULL DEFAULT '' COMMENT 'diagnoses and maybe other coding (e.g. ICD9:111.11)';
220 #IfMissingColumn procedure_providers remote_host
221 ALTER TABLE `procedure_providers`
222 ADD COLUMN `remote_host` varchar(255) NOT NULL DEFAULT '' COMMENT 'IP or hostname of remote server',
223 ADD COLUMN `send_app_id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Sending application ID (MSH-3.1)',
224 ADD COLUMN `send_fac_id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Sending facility ID (MSH-4.1)',
225 ADD COLUMN `recv_app_id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Receiving application ID (MSH-5.1)',
226 ADD COLUMN `recv_fac_id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Receiving facility ID (MSH-6.1)',
227 ADD COLUMN `DorP` char(1) NOT NULL DEFAULT 'D' COMMENT 'Debugging or Production (MSH-11)';
230 #IfMissingColumn procedure_order_code procedure_source
231 ALTER TABLE `procedure_order_code`
232 ADD COLUMN `procedure_source` char(1) NOT NULL DEFAULT '1' COMMENT '1=original order, 2=added after order sent';
235 #IfMissingColumn procedure_result result_code
236 ALTER TABLE `procedure_result`
237 ADD COLUMN `result_data_type` char(1) NOT NULL DEFAULT 'S' COMMENT
238 'N=Numeric, S=String, F=Formatted, E=External, L=Long text as first line of comments',
239 ADD COLUMN `result_code` varchar(31) NOT NULL DEFAULT '' COMMENT
240 'LOINC code, might match a procedure_type.procedure_code',
241 ADD COLUMN `result_text` varchar(255) NOT NULL DEFAULT '' COMMENT
242 'Description of result_code';
243 # This severs the link between procedure_result and procedure_type:
244 UPDATE procedure_result AS ps, procedure_type AS pt
245 SET ps.result_code = pt.procedure_code, ps.result_text = pt.description
246 WHERE pt.procedure_type_id = ps.procedure_type_id;
247 ALTER TABLE `procedure_result` DROP COLUMN procedure_type_id;
250 #IfMissingColumn procedure_questions tips
251 ALTER TABLE `procedure_questions`
252 ADD COLUMN `tips` varchar(255) NOT NULL DEFAULT '' COMMENT 'Additional instructions for answering the question';
255 #IfMissingColumn procedure_order_code procedure_name
256 ALTER TABLE `procedure_order_code`
257 ADD COLUMN `procedure_name` varchar(255) NOT NULL DEFAULT '' COMMENT
258 'Descriptive name of procedure_code';
259 # This severs the link between procedure_order_code and procedure_type:
260 UPDATE procedure_order_code AS pc, procedure_order AS po, procedure_type AS pt
261 SET pc.procedure_name = pt.name
262 WHERE po.procedure_order_id = pc.procedure_order_id AND
263 pt.lab_id = po.lab_id AND
264 pt.procedure_code = pc.procedure_code;
265 ALTER TABLE `procedure_order_code` DROP COLUMN procedure_type_id;
268 #IfMissingColumn procedure_report report_notes
269 ALTER TABLE procedure_report
270 ADD COLUMN `report_notes` text NOT NULL DEFAULT '' COMMENT 'Notes from the lab';
273 #IfNotRow code_types ct_key SNOMED-PR
274 DROP TABLE IF EXISTS `temp_table_one`;
275 CREATE TABLE `temp_table_one` (
276 `id` int(11) NOT NULL DEFAULT '0',
277 `seq` int(11) NOT NULL DEFAULT '0'
279 INSERT INTO `temp_table_one` (`id`, `seq`) VALUES ( IF( ((SELECT MAX(`ct_id`) FROM `code_types`)>=100), ((SELECT MAX(`ct_id`) FROM `code_types`) + 1), 100 ) , IF( ((SELECT MAX(`ct_seq`) FROM `code_types`)>=100), ((SELECT MAX(`ct_seq`) FROM `code_types`) + 1), 100 ) );
280 INSERT INTO code_types (ct_key, ct_id, ct_seq, ct_mod, ct_just, ct_fee, ct_rel, ct_nofs, ct_diag, ct_active, ct_label, ct_external, ct_claim, ct_proc, ct_term ) VALUES ('SNOMED-PR' , (SELECT MAX(`id`) FROM `temp_table_one`), (SELECT MAX(`seq`) FROM `temp_table_one`), 0, 'SNOMED', 1, 0, 0, 0, 0, 'SNOMED Procedure', 9, 1, 1, 0);
281 DROP TABLE `temp_table_one`;