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