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