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