BMLT Root Server
db_connect.php
Go to the documentation of this file.
1 <?php
2 /*
3  This file is part of the Basic Meeting List Toolbox (BMLT).
4 
5  Find out more at: https://bmlt.app
6 
7  BMLT is free software: you can redistribute it and/or modify
8  it under the terms of the MIT License.
9 
10  BMLT is distributed in the hope that it will be useful,
11  but WITHOUT ANY WARRANTY; without even the implied warranty of
12  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13  MIT License for more details.
14 
15  You should have received a copy of the MIT License along with this code.
16  If not, see <https://opensource.org/licenses/MIT>.
17 */
18 /**
19  \brief This function checks to make sure the database is correct for the current version.
20 */
22 {
23  include(dirname(__FILE__)."/../server/config/get-config.php");
24 
25  if (!isset($dbType)) {
26  $dbType = 'mysql';
27  }
28 
29  if (!isset($dbServer)) {
30  $dbServer = 'localhost';
31  }
32 
33  if (!isset($dbPrefix)) {
34  $dbPrefix = 'na';
35  }
36 
37  c_comdef_dbsingleton::init($dbType, $dbServer, $dbName, $dbUser, $dbPassword, 'utf8');
38 
39  // Make sure version table exists
40  $versionTableName = $dbPrefix . "_comdef_db_version";
41  $sql = "SELECT COUNT(*) as count FROM information_schema.tables WHERE TABLE_SCHEMA = '$dbName' AND TABLE_NAME = '$versionTableName'";
43  if (is_array($rows) && count($rows)) {
44  $row = $rows[0];
45  if (intval($row['count']) == 0) {
46  $sql = "CREATE TABLE $versionTableName (version INT)";
48  $sql = "INSERT INTO $versionTableName VALUES(0)";
50  }
51  }
52 
53  // Get current db version
54  $sql = "SELECT version FROM $versionTableName";
56  $dbVersion = intval($rows[0]['version']);
57 
58  // New-style migrations
59  $dbMigrations = array(
60  // Add database migrations here. The first item is the database version, the second item is a function to run.
61  // Version 1 will never run, and is just an example. The first actual migration will begin with version 2.
62  array(1, function () {
63  $dbPrefix = $GLOBALS['dbPrefix'];
64  try {
65  // Version 1.3 added 1 column to the main meeting table.
66  $table = "$dbPrefix" . "_comdef_meetings_main";
67 
68  // We start with default 1, to set the existing records, then we change to 0 for future records.
69  $alter_sql = "ALTER TABLE $table ADD published TINYINT NOT NULL DEFAULT 1";
71  $alter_sql = "ALTER TABLE $table ALTER COLUMN published SET DEFAULT 0";
73  // Make sure we can look it up quickly.
74  $alter_sql = "CREATE INDEX published ON $table (published)";
76  } catch (Exception $e) {
77  // We don't die if the thing already exists. We just mosey on along as if nothing happened.
78  }
79 
80  try {
81  // Version 1.3.4 added 1 column to the service body table.
82  $table = "$dbPrefix" . "_comdef_service_bodies";
83 
84  $alter_sql = "ALTER TABLE $table ADD sb_meeting_email VARCHAR(255) DEFAULT NULL";
86  $alter_sql = "CREATE INDEX sb_meeting_email ON $table (sb_meeting_email)";
88  } catch (Exception $e) {
89  // We don't die if the thing already exists. We just mosey on along as if nothing happened.
90  }
91 
92  try {
93  // Version 1.3.6 added 1 column to the main meeting table for meeting-specific email contact.
94  $table = "$dbPrefix" . "_comdef_meetings_main";
95 
96  $alter_sql = "ALTER TABLE $table ADD email_contact VARCHAR(255) DEFAULT NULL";
98  $alter_sql = "CREATE INDEX email_contact ON $table (email_contact)";
100  } catch (Exception $e) {
101  // We don't die if the thing already exists. We just mosey on along as if nothing happened.
102  }
103 
104  try {
105  // Version 1.3.6 added 1 column to the meeting data tables for visibility.
106  $table = "$dbPrefix" . "_comdef_meetings_data";
107  $alter_sql = "ALTER TABLE `$table` ADD `visibility` INT( 1 ) NULL DEFAULT NULL AFTER `lang_enum`";
109  $alter_sql = "CREATE INDEX visibility ON $table (visibility)";
111  $table = "$dbPrefix" . "_comdef_meetings_longdata";
112  $alter_sql = "ALTER TABLE `$table` ADD `visibility` INT( 1 ) NULL DEFAULT NULL AFTER `lang_enum`";
114  $alter_sql = "CREATE INDEX visibility ON $table (visibility)";
116  } catch (Exception $e) {
117  // We don't die if the thing already exists. We just mosey on along as if nothing happened.
118  }
119 
120  try {
121  // Version 2.11.0 added 1 column to the users table for user ownership.
122  $table = "$dbPrefix" . "_comdef_users";
123  $check_column_sql = "SELECT COUNT(*) AS count FROM information_schema.columns WHERE TABLE_SCHEMA = '$dbName' AND TABLE_NAME = '$table' AND COLUMN_NAME='owner_id_bigint'";
124  $rows = c_comdef_dbsingleton::preparedQuery($check_column_sql);
125  if (is_array($rows) && count($rows)) {
126  $row = $rows[0];
127  if (intval($row['count']) == 0) {
128  // Some databases in the wild have an invalid default datetime for the last_access_datetime column, which could cause this migration to fail
129  $alter_sql = "ALTER TABLE `$table` MODIFY COLUMN `last_access_datetime` datetime NOT NULL DEFAULT '1970-01-01 00:00:00'";
131  $alter_sql = "ALTER TABLE `$table` ADD `owner_id_bigint` BIGINT NOT NULL DEFAULT -1 AFTER `lang_enum`";
133  $alter_sql = "CREATE INDEX owner_id_bigint ON $table (owner_id_bigint)";
135  }
136  }
137  } catch (Exception $e) {
138  // We don't die if the thing already exists. We just mosey on along as if nothing happened.
139  }
140 
141  try {
142  // Version 2.13.1, update format_enum_type for the default open/closed formats.
143  $table = "$dbPrefix" . "_comdef_formats";
144  $check_column_sql = "SELECT COUNT(*) AS count FROM `$table` WHERE `shared_id_bigint`=4 AND `key_string`='C' AND `format_type_enum`='FC3'";
145  $rows = c_comdef_dbsingleton::preparedQuery($check_column_sql);
146  if (is_array($rows) && count($rows)) {
147  $row = $rows[0];
148  if (intval($row['count']) != 0) {
149  $update_sql = "UPDATE `$table` SET `format_type_enum`='O' WHERE `shared_id_bigint`=4 AND `key_string`='C'";
151  }
152  }
153  } catch (Exception $e) {
154  }
155 
156  try {
157  // Version 2.13.1, update format_enum_type for the default open/closed formats.
158  $table = "$dbPrefix" . "_comdef_formats";
159  $check_column_sql = "SELECT COUNT(*) AS count FROM `$table` WHERE `shared_id_bigint`=17 AND `key_string`='O' AND `format_type_enum`='FC3'";
160  $rows = c_comdef_dbsingleton::preparedQuery($check_column_sql);
161  if (is_array($rows) && count($rows)) {
162  $row = $rows[0];
163  if (intval($row['count']) != 0) {
164  $update_sql = "UPDATE `$table` SET `format_type_enum`='O' WHERE `shared_id_bigint`=17 AND `key_string`='O'";
166  }
167  }
168  } catch (Exception $e) {
169  }
170  }),
171  array(3, function () {
172  $dbPrefix = $GLOBALS['dbPrefix'];
173  $table = "$dbPrefix" . "_comdef_formats";
174  $updateSqlTemplate = "UPDATE `$table` SET `worldid_mixed` = '%s' WHERE `shared_id_bigint` = %s AND `key_string` = '%s' AND `lang_enum` = 'en' AND `name_string` = '%s' AND `format_type_enum` = '%s'";
175  $updateSql = sprintf($updateSqlTemplate, 'CH', '5', 'CH', 'Closed Holidays', 'FC3');
177  $updateSql = sprintf($updateSqlTemplate, 'NC', '16', 'NC', 'No Children', 'FC3');
179  $updateSql = sprintf($updateSqlTemplate, 'LC', '51', 'LC', 'Living Clean', 'FC1');
181  }),
182  array(4, function () {
183  $dbPrefix = $GLOBALS['dbPrefix'];
184  $table = "$dbPrefix" . "_comdef_formats";
185  $updateSqlTemplate = "UPDATE `$table` SET `worldid_mixed` = '%s' WHERE `shared_id_bigint` = %s AND `key_string` = '%s' AND `lang_enum` = 'en' AND `name_string` = '%s' AND `format_type_enum` = '%s'";
186  $updateSql = sprintf($updateSqlTemplate, 'NS', '37', 'NS', 'No Smoking', 'FC1');
188  }),
189  array(5, function () {
190  return;
191  }),
192  array(6, function () {
193  return;
194  }),
195  array(7, function () {
196  return;
197  }),
198  array(8, function () {
199  $dbPrefix = $GLOBALS['dbPrefix'];
200  $table = "$dbPrefix" . "_comdef_meetings_data";
201  $check_column_sql = "SELECT COUNT(*) AS count FROM `$table` WHERE `key`='phone_meeting_number' AND meetingid_bigint = 0";
202  $rows = c_comdef_dbsingleton::preparedQuery($check_column_sql);
203  if (is_array($rows) && count($rows)) {
204  $row = $rows[0];
205  if (intval($row['count']) == 0) {
206  $sql = "INSERT INTO `$table` (`meetingid_bigint`, `key`, `field_prompt`, `lang_enum`, `visibility`, `data_string`, `data_bigint`, `data_double`) VALUES (0, 'phone_meeting_number', 'Phone Meeting Dial-in Number', 'en', 0, 'Phone Meeting Dial-in Number', NULL, NULL)";
208  }
209  }
210  }),
211  array(9, function () {
212  $dbPrefix = $GLOBALS['dbPrefix'];
213  $table = "$dbPrefix" . "_comdef_meetings_data";
214  $check_column_sql = "SELECT COUNT(*) AS count FROM `$table` WHERE `key`='virtual_meeting_link' AND meetingid_bigint = 0";
215  $rows = c_comdef_dbsingleton::preparedQuery($check_column_sql);
216  if (is_array($rows) && count($rows)) {
217  $row = $rows[0];
218  if (intval($row['count']) == 0) {
219  $sql = "INSERT INTO `$table` (`meetingid_bigint`, `key`, `field_prompt`, `lang_enum`, `visibility`, `data_string`, `data_bigint`, `data_double`) VALUES (0, 'virtual_meeting_link', 'Virtual Meeting Link', 'en', 0, 'Virtual Meeting Link', NULL, NULL)";
221  }
222  }
223  }),
224  array(10, function () {
225  $dbPrefix = $GLOBALS['dbPrefix'];
226  $table = "$dbPrefix" . "_comdef_formats";
227  $check = "SELECT COUNT(*) AS count FROM `$table` WHERE `key_string` = 'VM'";
228  $check = c_comdef_dbsingleton::preparedQuery($check);
229  if (is_array($check) && count($check)) {
230  $check = $check[0];
231  if (intval($check['count']) == 0) {
232  $next_id = "SELECT MAX(shared_id_bigint) + 1 AS next_id FROM `$table`";
233  $next_id = c_comdef_dbsingleton::preparedQuery($next_id);
234  $next_id = $next_id[0];
235  $next_id = $next_id['next_id'];
236  $langs = array("en", "es", "fa", "fr", "it", "pl", "pt", "sv");
237  foreach ($langs as $lang) {
238  $sql = "INSERT INTO `$table` (`shared_id_bigint`, `key_string`, `icon_blob`, `worldid_mixed`, `lang_enum`,`name_string`, `description_string`, `format_type_enum`) VALUES ($next_id, 'VM', NULL, NULL, '$lang', 'Virtual Meeting', 'Meets Virtually', 'FC2')";
240  }
241  } else {
242  $sql = "UPDATE `$table` SET `description_string` = 'Meets Virtually' WHERE `key_string` = 'VM'";
244  }
245  }
246  }),
247  array(11, function () {
248  $dbPrefix = $GLOBALS['dbPrefix'];
249  $table = "$dbPrefix" . "_comdef_formats";
250  $check = "SELECT COUNT(*) AS count FROM `$table` WHERE `key_string` = 'TC'";
251  $check = c_comdef_dbsingleton::preparedQuery($check);
252  if (is_array($check) && count($check)) {
253  $check = $check[0];
254  if (intval($check['count']) == 0) {
255  $next_id = "SELECT MAX(shared_id_bigint) + 1 AS next_id FROM `$table`";
256  $next_id = c_comdef_dbsingleton::preparedQuery($next_id);
257  $next_id = $next_id[0];
258  $next_id = $next_id['next_id'];
259  $langs = array("en", "es", "fa", "fr", "it", "pl", "pt", "sv");
260  foreach ($langs as $lang) {
261  $sql = "INSERT INTO `$table` (`shared_id_bigint`, `key_string`, `icon_blob`, `worldid_mixed`, `lang_enum`,`name_string`, `description_string`, `format_type_enum`) VALUES ($next_id, 'TC', NULL, NULL, '$lang', 'Temporarily Closed', 'Facility is Temporarily Closed', 'O')";
263  }
264  } else {
265  $sql = "UPDATE `$table` SET `description_string` = 'Facility is Temporarily Closed' WHERE `key_string` = 'TC'";
267  }
268  }
269  }),
270  array(12, function () {
271  $dbPrefix = $GLOBALS['dbPrefix'];
272  $table = "$dbPrefix" . "_comdef_meetings_data";
273  $check_column_sql = "SELECT COUNT(*) AS count FROM `$table` WHERE `key`='virtual_meeting_additional_info' AND meetingid_bigint = 0";
274  $rows = c_comdef_dbsingleton::preparedQuery($check_column_sql);
275  if (is_array($rows) && count($rows)) {
276  $row = $rows[0];
277  if (intval($row['count']) == 0) {
278  $sql = "INSERT INTO `$table` (`meetingid_bigint`, `key`, `field_prompt`, `lang_enum`, `visibility`, `data_string`, `data_bigint`, `data_double`) VALUES (0, 'virtual_meeting_additional_info', 'Virtual Meeting Additional Info', 'en', 0, 'Virtual Meeting Additional Info', NULL, NULL)";
280  }
281  }
282  }),
283  array(13, function () {
284  // Add HY (hybrid) as an officially supported format, and map it to its NAWS format
285  $dbPrefix = $GLOBALS['dbPrefix'];
286  $table = "$dbPrefix" . "_comdef_formats";
287  $check = "SELECT COUNT(*) AS count FROM `$table` WHERE `key_string` = 'HY'";
288  $check = c_comdef_dbsingleton::preparedQuery($check);
289  if (is_array($check) && count($check)) {
290  $check = $check[0];
291  if (intval($check['count']) == 0) {
292  $next_id = "SELECT MAX(shared_id_bigint) + 1 AS next_id FROM `$table`";
293  $next_id = c_comdef_dbsingleton::preparedQuery($next_id);
294  $next_id = $next_id[0];
295  $next_id = $next_id['next_id'];
296  $langs = array("en", "es", "fa", "fr", "it", "pl", "pt", "ru", "sv");
297  foreach ($langs as $lang) {
298  $sql = "INSERT INTO `$table` (`shared_id_bigint`, `key_string`, `icon_blob`, `worldid_mixed`, `lang_enum`,`name_string`, `description_string`, `format_type_enum`) VALUES ($next_id, 'HY', NULL, 'HYBR', '$lang', 'Hybrid Meeting', 'Meets Virtually and In-person', 'FC2')";
300  }
301  } else {
302  $sql = "UPDATE `$table` SET `worldid_mixed` = 'HYBR' WHERE `key_string` = 'HY'";
304  }
305  }
306  }),
307  array(14, function () {
308  // Map existing VM formats to the new NAWS format
309  $dbPrefix = $GLOBALS['dbPrefix'];
310  $table = "$dbPrefix" . "_comdef_formats";
311  $id = "SELECT `shared_id_bigint` FROM `$table` WHERE `key_string` = 'VM' AND `lang_enum` = 'en'";
313  if (is_array($id) && count($id)) {
314  $id = $id[0];
315  $id = $id['shared_id_bigint'];
316  $sql = "UPDATE `$table` SET `worldid_mixed` = 'VM' WHERE `shared_id_bigint` = $id";
318  }
319  }),
320  array(15, function () {
321  // Map existing TC formats to the new NAWS format
322  $dbPrefix = $GLOBALS['dbPrefix'];
323  $table = "$dbPrefix" . "_comdef_formats";
324  $id = "SELECT `shared_id_bigint` FROM `$table` WHERE `key_string` = 'TC' AND `lang_enum` = 'en'";
326  if (is_array($id) && count($id)) {
327  $id = $id[0];
328  $id = $id['shared_id_bigint'];
329  $sql = "UPDATE `$table` SET `worldid_mixed` = 'TC' WHERE `shared_id_bigint` = $id";
331  }
332  }),
333  array(16, function () {
334  $dbPrefix = $GLOBALS['dbPrefix'];
335  $table = "$dbPrefix" . "_comdef_meetings_main";
336  $alter_sql = "ALTER TABLE $table ADD COLUMN `time_zone` VARCHAR(40) DEFAULT NULL AFTER `duration_time`";
338  $create_sql = "CREATE INDEX `time_zone` ON $table (`time_zone`)";
340  }),
341  array(17, function () {
342  // This migration subsumed by 18 (which also adds an additional check that every format for the virtual location
343  // types HY, TC, and VM has a key in every language, in addition to the checks that were in this migration).
344  return;
345  }),
346  array(18, function () {
347  // Ensure that the database includes the 3 virtual location format types HY, TC, and VM, which are now used
348  // for the venue type radio buttons. If they aren't there, add them; and also make sure they map to the
349  // correct NAWS formats and format types. If there are multiple formats named say VM (which would be weird
350  // but possible), this code will only update the one with the smallest shared id. Also make sure that every
351  // existing format for HY, TC, and VM has a key.
352  function fix_formats($key, $naws, $name, $descr, $type)
353  {
354  $dbPrefix = $GLOBALS['dbPrefix'];
355  $table = "$dbPrefix" . "_comdef_formats";
356  $langs = array('en', 'dk', 'de', 'es', 'fa', 'fr', 'it', 'pl', 'pt', 'ru', 'sv');
357  // if there are additional languages, fix those formats as well
358  if (array_key_exists('format_lang_names', $GLOBALS)) {
359  $langs = array_merge($langs, array_keys($GLOBALS['format_lang_names']));
360  }
361  $id = null; // this will be the shared_id of the format we're fixing or adding
362  // first see if there is an English language version of the format with the correct key (take the one with the smallest shared id if more than one)
363  $q1 = "SELECT `shared_id_bigint` FROM `$table` WHERE `key_string` = '$key' AND `lang_enum` = 'en' ORDER BY `shared_id_bigint`";
364  $result1 = c_comdef_dbsingleton::preparedQuery($q1);
365  if (is_array($result1) && count($result1)) {
366  // English version of the format found
367  $id = $result1[0]['shared_id_bigint'];
368  }
369  if (!$id) {
370  // No English language version of the format found. See if there is a format with the correct NAWS key; if so, use
371  // that. (This seems safe -- there should not be a format to do something different that would map to that NAWS format.)
372  $q2 = "SELECT `shared_id_bigint` FROM `$table` WHERE `worldid_mixed` = '$naws' ORDER BY `shared_id_bigint`";
373  $result2 = c_comdef_dbsingleton::preparedQuery($q2);
374  if (is_array($result2) && count($result2)) {
375  $id = $result2[0]['shared_id_bigint'];
376  }
377  }
378  if (!$id) {
379  // No format with the correct NAWS key either. Next see if there is a format in some other language with the given key; if so, use
380  // that. This is a bit riskier -- although it is unlikely there would be a format with the correct key that does something different.
381  $q3 = "SELECT `shared_id_bigint` FROM `$table` WHERE `key_string` = '$key' ORDER BY `shared_id_bigint`";
382  $result3 = c_comdef_dbsingleton::preparedQuery($q3);
383  if (is_array($result3) && count($result3)) {
384  $id = $result3[0]['shared_id_bigint'];
385  }
386  }
387  if (!$id) {
388  // No luck finding an existing format, period. Get a new shared ID.
389  $next_id = "SELECT MAX(shared_id_bigint) + 1 AS next_id FROM `$table`";
390  $next_id = c_comdef_dbsingleton::preparedQuery($next_id);
391  $next_id = $next_id[0];
392  $id = $next_id['next_id'];
393  }
394  // $id will be the shared ID for the desired format. First update any existing formats with this $id.
395  $sql = "UPDATE `$table` SET `worldid_mixed` = '$naws', `format_type_enum` = '$type' WHERE `shared_id_bigint` = $id";
397  // Make sure the key is correct for the English version of this format, and that the key is filled in for all versions.
398  // If the key is missing for some language, use the English key.
399  $sql = "UPDATE `$table` SET `key_string` = '$key' WHERE `shared_id_bigint` = '$id' AND (`lang_enum` = 'en' OR TRIM(`key_string`)='')";
401  // For each language, add a format for that language if there isn't one already.
402  // It will be in English - the server admin can translate it later.
403  foreach ($langs as $lang) {
404  $q4 = "SELECT * FROM `$table` WHERE `shared_id_bigint` = '$id' AND `lang_enum` = '$lang'";
405  $result4 = c_comdef_dbsingleton::preparedQuery($q4);
406  if (!is_array($result4) || count($result4) == 0) {
407  $sql = "INSERT INTO `$table` (`shared_id_bigint`, `key_string`, `icon_blob`, `worldid_mixed`, `lang_enum`,`name_string`, `description_string`, `format_type_enum`) VALUES ($id, '$key', NULL, '$naws', '$lang', '$name', '$descr', '$type')";
409  }
410  }
411  }
412  fix_formats('VM', 'VM', 'Virtual Meeting', 'Meets Virtually', 'FC2');
413  fix_formats('HY', 'HYBR', 'Hybrid Meeting', 'Meets Virtually and In-person', 'FC2');
414  fix_formats('TC', 'TC', 'Temporarily Closed', 'Facility is Temporarily Closed', 'FC2');
415  })
416  );
417  // WHEN ADDING A NEW DATABASE MIGRATION, REMEMBER TO BUMP THE VERSION IN local_server/install_wizard/sql_files/initialDbVersionData.sql
418 
419  foreach ($dbMigrations as $dbMigration) {
420  $version = $dbMigration[0];
421  if ($dbVersion >= $version) {
422  continue;
423  }
424 
426 
427  try {
428  $func = $dbMigration[1];
429  call_user_func($func);
430  $sql = "UPDATE $versionTableName SET version = $version";
432  } catch (Exception $e) {
434  echo $e->getMessage();
435  die();
436  }
437 
439  }
440 }
static preparedExec($sql, $params=array())
Wrapper for preparing and executing a PDOStatement that does not return a resultset e...
DB_Connect_and_Upgrade()
This function checks to make sure the database is correct for the current version.
Definition: db_connect.php:21
function sprintf()
Definition: installer.js:873
static preparedQuery($sql, $params=array(), $fetchKeyPair=false)
Wrapper for preparing and executing a PDOStatement that returns a resultset e.g. SELECT SQL statement...
static init($driver, $host, $database, $user=null, $password=null, $charset=null)
Initializes connection param class members.