1<?php 2/** 3 * webtrees: online genealogy 4 * Copyright (C) 2018 webtrees development team 5 * This program is free software: you can redistribute it and/or modify 6 * it under the terms of the GNU General Public License as published by 7 * the Free Software Foundation, either version 3 of the License, or 8 * (at your option) any later version. 9 * This program is distributed in the hope that it will be useful, 10 * but WITHOUT ANY WARRANTY; without even the implied warranty of 11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 * GNU General Public License for more details. 13 * You should have received a copy of the GNU General Public License 14 * along with this program. If not, see <http://www.gnu.org/licenses/>. 15 */ 16declare(strict_types=1); 17 18namespace Fisharebest\Webtrees\Schema; 19 20use Fisharebest\Webtrees\Database; 21use Fisharebest\Webtrees\Module; 22use Fisharebest\Webtrees\Site; 23 24/** 25 * Upgrade the database schema from version 0 (empty database) to version 1. 26 */ 27class Migration0 implements MigrationInterface 28{ 29 /** 30 * Upgrade to to the next version 31 * 32 * @return void 33 */ 34 public function upgrade() 35 { 36 Database::exec( 37 "CREATE TABLE IF NOT EXISTS `##gedcom` (" . 38 " gedcom_id INTEGER AUTO_INCREMENT NOT NULL," . 39 " gedcom_name VARCHAR(255) NOT NULL," . 40 " sort_order INTEGER NOT NULL DEFAULT 0," . 41 " PRIMARY KEY (gedcom_id)," . 42 " UNIQUE KEY `##gedcom_ix1` (gedcom_name)," . 43 " KEY `##gedcom_ix2` (sort_order)" . 44 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 45 ); 46 Database::exec( 47 "CREATE TABLE IF NOT EXISTS `##site_setting` (" . 48 " setting_name VARCHAR(32) NOT NULL," . 49 " setting_value VARCHAR(255) NOT NULL," . 50 " PRIMARY KEY (setting_name)" . 51 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 52 ); 53 Database::exec( 54 "CREATE TABLE IF NOT EXISTS `##gedcom_setting` (" . 55 " gedcom_id INTEGER NOT NULL," . 56 " setting_name VARCHAR(32) NOT NULL," . 57 " setting_value VARCHAR(255) NOT NULL," . 58 " PRIMARY KEY (gedcom_id, setting_name)," . 59 " FOREIGN KEY `##gedcom_setting_fk1` (gedcom_id) REFERENCES `##gedcom` (gedcom_id) /* ON DELETE CASCADE */" . 60 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 61 ); 62 Database::exec( 63 "CREATE TABLE IF NOT EXISTS `##user` (" . 64 " user_id INTEGER AUTO_INCREMENT NOT NULL," . 65 " user_name VARCHAR(32) NOT NULL," . 66 " real_name VARCHAR(64) NOT NULL," . 67 " email VARCHAR(64) NOT NULL," . 68 " password VARCHAR(128) NOT NULL," . 69 " PRIMARY KEY (user_id)," . 70 " UNIQUE KEY `##user_ix1` (user_name)," . 71 " UNIQUE KEY `##user_ix2` (email)" . 72 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 73 ); 74 Database::exec( 75 "CREATE TABLE IF NOT EXISTS `##user_setting` (" . 76 " user_id INTEGER NOT NULL," . 77 " setting_name VARCHAR(32) NOT NULL," . 78 " setting_value VARCHAR(255) NOT NULL," . 79 " PRIMARY KEY (user_id, setting_name)," . 80 " FOREIGN KEY `##user_setting_fk1` (user_id) REFERENCES `##user` (user_id) /* ON DELETE CASCADE */" . 81 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 82 ); 83 Database::exec( 84 "CREATE TABLE IF NOT EXISTS `##user_gedcom_setting` (" . 85 " user_id INTEGER NOT NULL," . 86 " gedcom_id INTEGER NOT NULL," . 87 " setting_name VARCHAR(32) NOT NULL," . 88 " setting_value VARCHAR(255) NOT NULL," . 89 " PRIMARY KEY (user_id, gedcom_id, setting_name)," . 90 " FOREIGN KEY `##user_gedcom_setting_fk1` (user_id) REFERENCES `##user` (user_id) /* ON DELETE CASCADE */," . 91 " FOREIGN KEY `##user_gedcom_setting_fk2` (gedcom_id) REFERENCES `##gedcom` (gedcom_id) /* ON DELETE CASCADE */" . 92 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 93 ); 94 Database::exec( 95 "CREATE TABLE IF NOT EXISTS `##log` (" . 96 " log_id INTEGER AUTO_INCREMENT NOT NULL," . 97 " log_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," . 98 " log_type ENUM('auth', 'config', 'debug', 'edit', 'error', 'media', 'search') NOT NULL," . 99 " log_message TEXT NOT NULL," . 100 " ip_address VARCHAR(40) NOT NULL," . 101 " user_id INTEGER NULL," . 102 " gedcom_id INTEGER NULL," . 103 " PRIMARY KEY (log_id)," . 104 " KEY `##log_ix1` (log_time)," . 105 " KEY `##log_ix2` (log_type)," . 106 " KEY `##log_ix3` (ip_address)," . 107 " FOREIGN KEY `##log_fk1` (user_id) REFERENCES `##user`(user_id) /* ON DELETE SET NULL */," . 108 " FOREIGN KEY `##log_fk2` (gedcom_id) REFERENCES `##gedcom` (gedcom_id) /* ON DELETE SET NULL */" . 109 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 110 ); 111 Database::exec( 112 "CREATE TABLE IF NOT EXISTS `##change` (" . 113 " change_id INTEGER AUTO_INCREMENT NOT NULL," . 114 " change_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," . 115 " status ENUM('accepted', 'pending', 'rejected') NOT NULL DEFAULT 'pending'," . 116 " gedcom_id INTEGER NOT NULL," . 117 " xref VARCHAR(20) NOT NULL," . 118 " old_gedcom MEDIUMTEXT NOT NULL," . 119 " new_gedcom MEDIUMTEXT NOT NULL," . 120 " user_id INTEGER NOT NULL," . 121 " PRIMARY KEY (change_id)," . 122 " KEY `##change_ix1` (gedcom_id, status, xref)," . 123 " FOREIGN KEY `##change_fk1` (user_id) REFERENCES `##user` (user_id) /* ON DELETE RESTRICT */," . 124 " FOREIGN KEY `##change_fk2` (gedcom_id) REFERENCES `##gedcom` (gedcom_id) /* ON DELETE CASCADE */" . 125 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 126 ); 127 Database::exec( 128 "CREATE TABLE IF NOT EXISTS `##message` (" . 129 " message_id INTEGER AUTO_INCREMENT NOT NULL," . 130 " sender VARCHAR(64) NOT NULL," . // username or email address 131 " ip_address VARCHAR(40) NOT NULL," . // long enough for IPv6 132 " user_id INTEGER NOT NULL," . 133 " subject VARCHAR(255) NOT NULL," . 134 " body TEXT NOT NULL," . 135 " created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," . 136 " PRIMARY KEY (message_id)," . 137 " FOREIGN KEY `##message_fk1` (user_id) REFERENCES `##user` (user_id) /* ON DELETE RESTRICT */" . 138 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 139 ); 140 Database::exec( 141 "CREATE TABLE IF NOT EXISTS `##default_resn` (" . 142 " default_resn_id INTEGER AUTO_INCREMENT NOT NULL," . 143 " gedcom_id INTEGER NOT NULL," . 144 " xref VARCHAR(20) NULL," . 145 " tag_type VARCHAR(15) NULL," . 146 " resn ENUM ('none', 'privacy', 'confidential', 'hidden') NOT NULL," . 147 " comment VARCHAR(255) NULL," . 148 " updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," . 149 " PRIMARY KEY (default_resn_id)," . 150 " UNIQUE KEY `##default_resn_ix1` (gedcom_id, xref, tag_type)," . 151 " FOREIGN KEY `##default_resn_fk1` (gedcom_id) REFERENCES `##gedcom` (gedcom_id)" . 152 ") ENGINE=InnoDB COLLATE=utf8_unicode_ci" 153 ); 154 Database::exec( 155 "CREATE TABLE IF NOT EXISTS `##individuals` (" . 156 " i_id VARCHAR(20) NOT NULL," . 157 " i_file INTEGER NOT NULL," . 158 " i_rin VARCHAR(20) NOT NULL," . 159 " i_sex ENUM('U', 'M', 'F') NOT NULL," . 160 " i_gedcom MEDIUMTEXT NOT NULL," . 161 " PRIMARY KEY (i_id, i_file)," . 162 " UNIQUE KEY `##individuals_ix1` (i_file, i_id)" . 163 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 164 ); 165 Database::exec( 166 "CREATE TABLE IF NOT EXISTS `##families` (" . 167 " f_id VARCHAR(20) NOT NULL," . 168 " f_file INTEGER NOT NULL," . 169 " f_husb VARCHAR(20) NULL," . 170 " f_wife VARCHAR(20) NULL," . 171 " f_gedcom MEDIUMTEXT NOT NULL," . 172 " f_numchil INTEGER NOT NULL," . 173 " PRIMARY KEY (f_id, f_file)," . 174 " UNIQUE KEY `##families_ix1` (f_file, f_id)," . 175 " KEY `##families_ix2` (f_husb)," . 176 " KEY `##families_ix3` (f_wife)" . 177 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 178 ); 179 Database::exec( 180 "CREATE TABLE IF NOT EXISTS `##places` (" . 181 " p_id INTEGER AUTO_INCREMENT NOT NULL," . 182 " p_place VARCHAR(150) NULL," . 183 " p_parent_id INTEGER NULL," . 184 " p_file INTEGER NOT NULL," . 185 " p_std_soundex TEXT NULL," . 186 " p_dm_soundex TEXT NULL," . 187 " PRIMARY KEY (p_id)," . 188 " KEY `##places_ix1` (p_file, p_place)," . 189 " UNIQUE KEY `##places_ix2` (p_parent_id, p_file, p_place)" . 190 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 191 ); 192 Database::exec( 193 "CREATE TABLE IF NOT EXISTS `##placelinks` (" . 194 " pl_p_id INTEGER NOT NULL," . 195 " pl_gid VARCHAR(20) NOT NULL," . 196 " pl_file INTEGER NOT NULL," . 197 " PRIMARY KEY (pl_p_id, pl_gid, pl_file)," . 198 " KEY `##placelinks_ix1` (pl_p_id)," . 199 " KEY `##placelinks_ix2` (pl_gid)," . 200 " KEY `##placelinks_ix3` (pl_file)" . 201 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 202 ); 203 Database::exec( 204 "CREATE TABLE IF NOT EXISTS `##dates` (" . 205 " d_day TINYINT NOT NULL," . 206 " d_month CHAR(5) NULL," . 207 " d_mon TINYINT NOT NULL," . 208 " d_year SMALLINT NOT NULL," . 209 " d_julianday1 MEDIUMINT NOT NULL," . 210 " d_julianday2 MEDIUMINT NOT NULL," . 211 " d_fact VARCHAR(15) NOT NULL," . 212 " d_gid VARCHAR(20) NOT NULL," . 213 " d_file INTEGER NOT NULL," . 214 " d_type ENUM ('@#DGREGORIAN@', '@#DJULIAN@', '@#DHEBREW@', '@#DFRENCH R@', '@#DHIJRI@', '@#DROMAN@', '@#DJALALI@') NOT NULL," . 215 " KEY `##dates_ix1` (d_day)," . 216 " KEY `##dates_ix2` (d_month)," . 217 " KEY `##dates_ix3` (d_mon)," . 218 " KEY `##dates_ix4` (d_year)," . 219 " KEY `##dates_ix5` (d_julianday1)," . 220 " KEY `##dates_ix6` (d_julianday2)," . 221 " KEY `##dates_ix7` (d_gid)," . 222 " KEY `##dates_ix8` (d_file)," . 223 " KEY `##dates_ix9` (d_type)," . 224 " KEY `##dates_ix10` (d_fact, d_gid)" . 225 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 226 ); 227 Database::exec( 228 "CREATE TABLE IF NOT EXISTS `##media` (" . 229 " m_id VARCHAR(20) NOT NULL," . 230 " m_ext VARCHAR(6) NULL," . 231 " m_type VARCHAR(20) NULL," . 232 " m_titl VARCHAR(255) NULL," . 233 " m_filename VARCHAR(512) NULL," . 234 " m_file INTEGER NOT NULL," . 235 " m_gedcom MEDIUMTEXT NULL," . 236 " PRIMARY KEY (m_file, m_id)," . 237 " UNIQUE KEY `##media_ix1` (m_id, m_file)," . 238 " KEY `##media_ix2` (m_ext, m_type)," . 239 " KEY `##media_ix3` (m_titl)" . 240 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 241 ); 242 Database::exec( 243 "CREATE TABLE IF NOT EXISTS `##next_id` (" . 244 " gedcom_id INTEGER NOT NULL," . 245 " record_type VARCHAR(15) NOT NULL," . 246 " next_id DECIMAL(20) NOT NULL," . 247 " PRIMARY KEY (gedcom_id, record_type)," . 248 " FOREIGN KEY `##next_id_fk1` (gedcom_id) REFERENCES `##gedcom` (gedcom_id) /* ON DELETE CASCADE */" . 249 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 250 ); 251 Database::exec( 252 "CREATE TABLE IF NOT EXISTS `##other` (" . 253 " o_id VARCHAR(20) NOT NULL," . 254 " o_file INTEGER NOT NULL," . 255 " o_type VARCHAR(15) NOT NULL," . 256 " o_gedcom MEDIUMTEXT NULL," . 257 " PRIMARY KEY (o_id, o_file)," . 258 " UNIQUE KEY `##other_ix1` (o_file, o_id)" . 259 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 260 ); 261 Database::exec( 262 "CREATE TABLE IF NOT EXISTS `##sources` (" . 263 " s_id VARCHAR(20) NOT NULL," . 264 " s_file INTEGER NOT NULL," . 265 " s_name VARCHAR(255) NOT NULL," . 266 " s_gedcom MEDIUMTEXT NOT NULL," . 267 " PRIMARY KEY (s_id, s_file)," . 268 " UNIQUE KEY `##sources_ix1` (s_file, s_id)," . 269 " KEY `##sources_ix2` (s_name)" . 270 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 271 ); 272 Database::exec( 273 "CREATE TABLE IF NOT EXISTS `##link` (" . 274 " l_file INTEGER NOT NULL," . 275 " l_from VARCHAR(20) NOT NULL," . 276 " l_type VARCHAR(15) NOT NULL," . 277 " l_to VARCHAR(20) NOT NULL," . 278 " PRIMARY KEY (l_from, l_file, l_type, l_to)," . 279 " UNIQUE KEY `##link_ix1` (l_to, l_file, l_type, l_from)" . 280 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 281 ); 282 Database::exec( 283 "CREATE TABLE IF NOT EXISTS `##name` (" . 284 " n_file INTEGER NOT NULL," . 285 " n_id VARCHAR(20) NOT NULL," . 286 " n_num INTEGER NOT NULL," . 287 " n_type VARCHAR(15) NOT NULL," . 288 " n_sort VARCHAR(255) NOT NULL," . // e.g. “GOGH,VINCENT WILLEM” 289 " n_full VARCHAR(255) NOT NULL," . // e.g. “Vincent Willem van GOGH” 290 // These fields are only used for INDI records 291 " n_surname VARCHAR(255) NULL," . // e.g. “van GOGH” 292 " n_surn VARCHAR(255) NULL," . // e.g. “GOGH” 293 " n_givn VARCHAR(255) NULL," . // e.g. “Vincent Willem” 294 " n_soundex_givn_std VARCHAR(255) NULL," . 295 " n_soundex_surn_std VARCHAR(255) NULL," . 296 " n_soundex_givn_dm VARCHAR(255) NULL," . 297 " n_soundex_surn_dm VARCHAR(255) NULL," . 298 " PRIMARY KEY (n_id, n_file, n_num)," . 299 " KEY `##name_ix1` (n_full, n_id, n_file)," . 300 " KEY `##name_ix2` (n_surn, n_file, n_type, n_id)," . 301 " KEY `##name_ix3` (n_givn, n_file, n_type, n_id)" . 302 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 303 ); 304 Database::exec( 305 "CREATE TABLE IF NOT EXISTS `##module` (" . 306 " module_name VARCHAR(32) NOT NULL," . 307 " status ENUM('enabled', 'disabled') NOT NULL DEFAULT 'enabled'," . 308 " tab_order INTEGER NULL, " . 309 " menu_order INTEGER NULL, " . 310 " sidebar_order INTEGER NULL," . 311 " PRIMARY KEY (module_name)" . 312 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 313 ); 314 Database::exec( 315 "CREATE TABLE IF NOT EXISTS `##module_setting` (" . 316 " module_name VARCHAR(32) NOT NULL," . 317 " setting_name VARCHAR(32) NOT NULL," . 318 " setting_value MEDIUMTEXT NOT NULL," . 319 " PRIMARY KEY (module_name, setting_name)," . 320 " FOREIGN KEY `##module_setting_fk1` (module_name) REFERENCES `##module` (module_name) /* ON DELETE CASCADE */" . 321 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 322 ); 323 Database::exec( 324 "CREATE TABLE IF NOT EXISTS `##module_privacy` (" . 325 " module_name VARCHAR(32) NOT NULL," . 326 " gedcom_id INTEGER NOT NULL," . 327 " component ENUM('block', 'chart', 'menu', 'report', 'sidebar', 'tab', 'theme') NOT NULL," . 328 " access_level TINYINT NOT NULL," . 329 " PRIMARY KEY (module_name, gedcom_id, component)," . 330 " FOREIGN KEY `##module_privacy_fk1` (module_name) REFERENCES `##module` (module_name) /* ON DELETE CASCADE */," . 331 " FOREIGN KEY `##module_privacy_fk2` (gedcom_id) REFERENCES `##gedcom` (gedcom_id) /* ON DELETE CASCADE */" . 332 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 333 ); 334 Database::exec( 335 "CREATE TABLE IF NOT EXISTS `##block` (" . 336 " block_id INTEGER AUTO_INCREMENT NOT NULL," . 337 " gedcom_id INTEGER NULL," . 338 " user_id INTEGER NULL," . 339 " xref VARCHAR(20) NULL," . 340 " location ENUM('main', 'side') NULL," . 341 " block_order INTEGER NOT NULL," . 342 " module_name VARCHAR(32) NOT NULL," . 343 " PRIMARY KEY (block_id)," . 344 " FOREIGN KEY `##block_fk1` (gedcom_id) REFERENCES `##gedcom` (gedcom_id), /* ON DELETE CASCADE */" . 345 " FOREIGN KEY `##block_fk2` (user_id) REFERENCES `##user` (user_id), /* ON DELETE CASCADE */" . 346 " FOREIGN KEY `##block_fk3` (module_name) REFERENCES `##module` (module_name) /* ON DELETE CASCADE */" . 347 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 348 ); 349 Database::exec( 350 "CREATE TABLE IF NOT EXISTS `##block_setting` (" . 351 " block_id INTEGER NOT NULL," . 352 " setting_name VARCHAR(32) NOT NULL," . 353 " setting_value TEXT NOT NULL," . 354 " PRIMARY KEY (block_id, setting_name)," . 355 " FOREIGN KEY `##block_setting_fk1` (block_id) REFERENCES `##block` (block_id) /* ON DELETE CASCADE */" . 356 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 357 ); 358 Database::exec( 359 "CREATE TABLE IF NOT EXISTS `##hit_counter` (" . 360 " gedcom_id INTEGER NOT NULL," . 361 " page_name VARCHAR(32) NOT NULL," . 362 " page_parameter VARCHAR(32) NOT NULL," . 363 " page_count INTEGER NOT NULL," . 364 " PRIMARY KEY (gedcom_id, page_name, page_parameter)," . 365 " FOREIGN KEY `##hit_counter_fk1` (gedcom_id) REFERENCES `##gedcom` (gedcom_id) /* ON DELETE CASCADE */" . 366 ") COLLATE utf8_unicode_ci ENGINE=InnoDB" 367 ); 368 369 // Set the default site preferences 370 Site::setPreference('INDEX_DIRECTORY', 'data/'); 371 Site::setPreference('USE_REGISTRATION_MODULE', '1'); 372 Site::setPreference('ALLOW_USER_THEMES', '1'); 373 Site::setPreference('ALLOW_CHANGE_GEDCOM', '1'); 374 Site::setPreference('SMTP_ACTIVE', 'internal'); 375 Site::setPreference('SMTP_HOST', 'localhost'); 376 Site::setPreference('SMTP_PORT', '25'); 377 Site::setPreference('SMTP_AUTH', '1'); 378 Site::setPreference('SMTP_SSL', 'none'); 379 Site::setPreference('SMTP_HELO', $_SERVER['SERVER_NAME']); 380 Site::setPreference('SMTP_FROM_NAME', $_SERVER['SERVER_NAME']); 381 382 // Search for all installed modules, and enable them. 383 Module::getInstalledModules('enabled'); 384 } 385} 386