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 and column exist, the block will be executed
17 -- arguments: table_name colname
18 -- behavior: if the table exists but the column does not, the block will be executed
21 -- arguments: table_name colname value
22 -- 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
24 -- #IfNotColumnTypeDefault
25 -- arguments: table_name colname value value2
26 -- behavior: If the table table_name does not have a column colname with a data type equal to value and a default equal to value2, then the block will be executed
29 -- arguments: table_name colname value
30 -- behavior: If the table table_name does not have a row where colname = value, the block will be executed.
33 -- arguments: table_name colname value colname2 value2
34 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
37 -- arguments: table_name colname value colname2 value2 colname3 value3
38 -- 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.
41 -- arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
42 -- 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.
45 -- 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.
46 -- arguments: table_name colname value colname2 value2 colname3 value3
47 -- behavior: The block will be executed if both statements below are true:
48 -- 1) The table table_name does not have a row where colname = value AND colname2 = value2.
49 -- 2) The table table_name does not have a row where colname = value AND colname3 = value3.
52 -- arguments: table_name colname value
53 -- behavior: If the table table_name does have a row where colname = value, the block will be executed.
56 -- arguments: table_name colname value colname2 value2
57 -- behavior: If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
60 -- arguments: table_name colname value colname2 value2 colname3 value3
61 -- behavior: If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
64 -- arguments: table_name colname
65 -- behavior: If the table table_name does have a row where colname is null, the block will be executed.
68 -- desc: This function is most often used for dropping of indexes/keys.
69 -- arguments: table_name colname
70 -- behavior: If the table and index exist the relevant statements are executed, otherwise not.
73 -- desc: This function will allow adding of indexes/keys.
74 -- arguments: table_name colname
75 -- behavior: If the index does not exist, it will be created
78 -- all blocks are terminated with a #EndIf statement.
81 -- Custom function for creating Reaction List
83 -- #IfNotListOccupation
84 -- Custom function for creating Occupation List
86 -- #IfTextNullFixNeeded
87 -- desc: convert all text fields without default null to have default null.
91 -- desc: Execute SQL if the table has been created with given engine specified.
92 -- arguments: table_name engine
93 -- behavior: Use when engine conversion requires more than one ALTER TABLE
95 -- #IfInnoDBMigrationNeeded
96 -- desc: find all MyISAM tables and convert them to InnoDB.
98 -- behavior: can take a long time.
100 -- #IfDocumentNamingNeeded
101 -- desc: populate name field with document names.
104 -- #IfUpdateEditOptionsNeeded
105 -- desc: Change Layout edit options.
106 -- arguments: mode(add or remove) layout_form_id the_edit_option comma_separated_list_of_field_ids
108 -- #IfVitalsDatesNeeded
109 -- desc: Change date from zeroes to date of vitals form creation.
112 #IfTable pma_bookmark
113 DROP TABLE IF EXISTS `pma_bookmark`;
116 #IfTable pma_column_info
117 DROP TABLE IF EXISTS `pma_column_info`;
121 DROP TABLE IF EXISTS `pma_history`;
124 #IfTable pma_pdf_pages
125 DROP TABLE IF EXISTS `pma_pdf_pages`;
128 #IfTable pma_relation
129 DROP TABLE IF EXISTS `pma_relation`;
132 #IfTable pma_table_coords
133 DROP TABLE IF EXISTS `pma_table_coords`;
136 #IfTable pma_table_info
137 DROP TABLE IF EXISTS `pma_table_info`;
140 #IfMissingColumn x12_partners x12_submitter_id
141 ALTER TABLE `x12_partners` ADD COLUMN `x12_submitter_id` smallint(6) DEFAULT NULL;
144 #IfNotRow2D list_options list_id abook_type option_id bill_svc
145 INSERT INTO list_options (list_id, option_id, title, seq, option_value) VALUES ('abook_type', 'bill_svc', 'Billing Service', 125, 3);
148 #IfMissingColumn users_secure last_login_fail
149 ALTER TABLE `users_secure` ADD `last_login_fail` datetime DEFAULT NULL;
152 #IfMissingColumn users_secure total_login_fail_counter
153 ALTER TABLE `users_secure` ADD `total_login_fail_counter` bigint DEFAULT 0;
156 #IfMissingColumn users_secure auto_block_emailed
157 ALTER TABLE `users_secure` ADD `auto_block_emailed` tinyint DEFAULT 0;
160 #IfNotRow globals gl_name time_reset_password_max_failed_logins
161 UPDATE `globals` SET `gl_value` = 20 WHERE `gl_name` = 'password_max_failed_logins' AND `gl_value` = 0;
164 #IfNotTable ip_tracking
165 CREATE TABLE `ip_tracking` (
166 `id` bigint NOT NULL auto_increment,
167 `ip_string` varchar(255) DEFAULT '',
168 `total_ip_login_fail_counter` bigint DEFAULT 0,
169 `ip_login_fail_counter` bigint DEFAULT 0,
170 `ip_last_login_fail` datetime DEFAULT NULL,
171 `ip_auto_block_emailed` tinyint DEFAULT 0,
172 `ip_force_block` tinyint DEFAULT 0,
173 `ip_no_prevent_timing_attack` tinyint DEFAULT 0,
175 UNIQUE KEY `ip_string` (`ip_string`)
176 ) ENGINE=InnoDb AUTO_INCREMENT=1;
179 #IfNotTable email_queue
180 CREATE TABLE `email_queue` (
181 `id` bigint NOT NULL auto_increment,
182 `sender` varchar(255) DEFAULT '',
183 `recipient` varchar(255) DEFAULT '',
184 `subject` varchar(255) DEFAULT '',
186 `datetime_queued` datetime default NULL,
187 `sent` tinyint DEFAULT 0,
188 `datetime_sent` datetime default NULL,
189 `error` tinyint DEFAULT 0,
190 `error_message` text,
191 `datetime_error` datetime default NULL,
194 ) ENGINE=InnoDb AUTO_INCREMENT=1;
197 #IfNotRow background_services name Email_Service
198 INSERT INTO `background_services` (`name`, `title`, `active`, `running`, `next_run`, `execute_interval`, `function`, `require_once`, `sort_order`) VALUES
199 ('Email_Service', 'Email Service', 1, 0, '2021-01-18 11:25:10', 2, 'emailServiceRun', '/library/email_service_run.php', 100);
202 #IfNotTable patient_settings
203 CREATE TABLE `patient_settings` (
204 `setting_patient` bigint(20) NOT NULL DEFAULT 0,
205 `setting_label` varchar(100) NOT NULL,
206 `setting_value` varchar(255) NOT NULL DEFAULT '',
207 PRIMARY KEY (`setting_patient`, `setting_label`)
211 #IfMissingColumn facility inactive
212 ALTER TABLE `facility` ADD COLUMN `inactive` tinyint(1) NOT NULL DEFAULT '0';
215 #IfNotRow2D list_options list_id Document_Template_Categories option_id notification_template
216 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`) VALUES ('Document_Template_Categories','notification_template','Notification Template',105,0,0,'','','',0,0,1);
219 #IfNotRow2D list_options list_id lists option_id default_open_tabs
220 -- Create new list Default Open Tabs
221 INSERT INTO `list_options` (`list_id`, `option_id`, `title`) VALUES ('lists', 'default_open_tabs', 'Default Open Tabs');
222 -- Populate list with sensible defaults based on previous optiosn in globals, assume no tabs are actually active
223 INSERT INTO `list_options` (`list_id`, `notes`, `title`, `seq`, `option_id`, `activity`) VALUES ('default_open_tabs', 'interface/main/main_info.php', 'Calendar', 10, 'cal', '0');
224 INSERT INTO `list_options` (`list_id`, `notes`, `title`, `seq`, `option_id`, `activity`) VALUES ('default_open_tabs', 'interface/new/new.php', 'Patient Search / Add', 20, 'pat', '0');
225 INSERT INTO `list_options` (`list_id`, `notes`, `title`, `seq`, `option_id`, `activity`) VALUES ('default_open_tabs', 'interface/main/finder/dynamic_finder.php', 'Patient Finder', 30, 'fin', '0');
226 INSERT INTO `list_options` (`list_id`, `notes`, `title`, `seq`, `option_id`, `activity`) VALUES ('default_open_tabs', 'interface/patient_tracker/patient_tracker.php?skip_timeout_reset=1', 'Flow Board', 40, 'flb', '0');
227 INSERT INTO `list_options` (`list_id`, `notes`, `title`, `seq`, `option_id`, `activity`) VALUES ('default_open_tabs', 'interface/main/messages/messages.php?form_active=1', 'Message Inbox', 50, 'msg', '0');
228 -- Activate the 2 list options that were the previous default and second pane settings
229 -- note need the second line for when upgrading in a version before the default_second_tab was added, which for example will impact the official demos
230 UPDATE `list_options` lo INNER JOIN globals g ON lo.notes LIKE CONCAT('%', g.gl_value) AND lo.list_id = 'default_open_tabs' SET lo.activity = 1 WHERE g.gl_name = 'default_top_pane' OR g.gl_name = 'default_second_tab';
231 UPDATE `list_options` SET `activity` = 1 WHERE `list_id` = 'default_open_tabs' AND `option_id` = 'msg' AND NOT EXISTS (SELECT `gl_value` FROM `globals` WHERE `gl_name` = 'default_second_tab');
234 #IfNotRow2D list_options list_id lists option_id recent_patient_columns
235 INSERT INTO `list_options` (`list_id`, `option_id`, `title`) VALUES ('lists', 'recent_patient_columns', 'Recent Patient Columns');
236 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`) VALUES ('recent_patient_columns', 'fname', 'First Name', '10');
237 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`) VALUES ('recent_patient_columns', 'mname', 'Middle Name', '20');
238 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`) VALUES ('recent_patient_columns', 'lname', 'Last Name', '30');
239 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`) VALUES ('recent_patient_columns', 'dob', 'Date of Birth', '40');
242 #IfNotTable recent_patients
243 CREATE TABLE recent_patients (
244 user_id varchar(40) NOT NULL,
246 PRIMARY KEY (user_id)
250 #IfMissingColumn oauth_clients skip_ehr_launch_authorization_flow
251 ALTER TABLE `oauth_clients` ADD COLUMN `skip_ehr_launch_authorization_flow` tinyint(1) NOT NULL DEFAULT '0';
254 #IfMissingColumn document_template_profiles notify_trigger
255 ALTER TABLE `document_template_profiles` ADD `notify_trigger` VARCHAR(31) NOT NULL;
256 ALTER TABLE `document_template_profiles` ADD `notify_period` INT(4) NOT NULL;
259 #IfNotRow2D layout_options form_id DEM field_id preferred_name
260 SET @group_id = (SELECT `group_id` FROM layout_options WHERE field_id='suffix' AND form_id='DEM');
262 UPDATE `layout_options` SET `seq` = (@seq_start := @seq_start+1)*10 WHERE group_id = @group_id AND form_id='DEM' ORDER BY `seq`;
263 SET @seq_add_to = (SELECT seq FROM layout_options WHERE group_id = @group_id AND field_id='suffix' AND form_id='DEM');
264 INSERT INTO `layout_options` (`form_id`, `field_id`, `group_id`, `title`, `seq`, `data_type`, `uor`, `fld_length`, `max_length`, `list_id`, `titlecols`, `datacols`, `default_value`, `edit_options`, `description`, `fld_rows`) VALUES ('DEM','preferred_name',@group_id,'Preferred Name',@seq_add_to+5,2,1,32,64,'',1,3,'','[\"J\",\"DAP\"]','Patient preferred name or name patient is commonly known.',0);
265 ALTER TABLE `patient_data` ADD `preferred_name` TINYTEXT;
268 #IfNotColumn email_queue template_name
269 ALTER TABLE `email_queue` ADD `template_name` VARCHAR(255) DEFAULT NULL COMMENT 'The folder prefix and base filename (w/o extension) of the twig template file to use for this email';