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