| 1 |
<?php |
| 2 |
|
| 3 |
/** |
| 4 |
* plugin initialization class |
| 5 |
* |
| 6 |
* handles installation, activation, deactivation, deletion, updates |
| 7 |
* |
| 8 |
* @version 2.6 |
| 9 |
* The way db updates will work is we will first set the "fresh install" db |
| 10 |
* initialization to the latest version's structure. Then, we add the update |
| 11 |
* queries to the series of upgrade steps that follow. Whichever version the |
| 12 |
* plugin comes in with when activated, it will jump into the series at that |
| 13 |
* point and complete the series to bring the database up to date. |
| 14 |
* |
| 15 |
*/ |
| 16 |
defined( 'ABSPATH' ) || exit; |
| 17 |
|
| 18 |
class PDb_Init { |
| 19 |
|
| 20 |
// arrays for building default field set |
| 21 |
public static $internal_fields; |
| 22 |
public static $main_fields; |
| 23 |
public static $admin_fields; |
| 24 |
public static $personal_fields; |
| 25 |
public static $field_groups; |
| 26 |
|
| 27 |
// |
| 28 |
|
| 29 |
/** |
| 30 |
* |
| 31 |
* @param string $mode the initialization mode |
| 32 |
* @param mixed $arg passed-in argument |
| 33 |
*/ |
| 34 |
function __construct( $mode = false, $arg = false ) |
| 35 |
{ |
| 36 |
if ( !$mode ) |
| 37 |
wp_die( 'class must be called on the activation hooks', 'object not correctly instantiated' ); |
| 38 |
|
| 39 |
switch ( $mode ) { |
| 40 |
case 'activate' : |
| 41 |
$this->_activate(); |
| 42 |
break; |
| 43 |
|
| 44 |
case 'network_activate' : |
| 45 |
$this->_network_activate(); |
| 46 |
break; |
| 47 |
|
| 48 |
case 'deactivate' : |
| 49 |
$this->_deactivate(); |
| 50 |
break; |
| 51 |
|
| 52 |
case 'uninstall' : |
| 53 |
$this->_uninstall(); |
| 54 |
break; |
| 55 |
|
| 56 |
case 'network_uninstall' : |
| 57 |
$this->_network_uninstall(); |
| 58 |
break; |
| 59 |
|
| 60 |
case 'new_blog': |
| 61 |
$this->_new_blog( $arg ); |
| 62 |
break; |
| 63 |
|
| 64 |
case 'delete_blog': |
| 65 |
$this->_delete_blog( $arg ); |
| 66 |
break; |
| 67 |
} |
| 68 |
} |
| 69 |
|
| 70 |
/** |
| 71 |
* set up database, defaults |
| 72 |
* |
| 73 |
* @param bool $networkwide |
| 74 |
*/ |
| 75 |
public static function on_activate( $networkwide = false ) |
| 76 |
{ |
| 77 |
$mode = $networkwide && self::is_network() ? 'network_activate' : 'activate'; |
| 78 |
new PDb_Init( $mode ); |
| 79 |
} |
| 80 |
|
| 81 |
/** |
| 82 |
* |
| 83 |
*/ |
| 84 |
public static function on_deactivate() |
| 85 |
{ |
| 86 |
new PDb_Init( 'deactivate' ); |
| 87 |
} |
| 88 |
|
| 89 |
/** |
| 90 |
* remove all plugin settings and database tables |
| 91 |
*/ |
| 92 |
public static function on_uninstall() |
| 93 |
{ |
| 94 |
$mode = self::is_network() ? 'network_uninstall' : 'uninstall'; |
| 95 |
new PDb_Init( $mode ); |
| 96 |
} |
| 97 |
|
| 98 |
/** |
| 99 |
* handle creating a new blog on a network |
| 100 |
*/ |
| 101 |
public static function new_blog( $blog_id ) |
| 102 |
{ |
| 103 |
new PDb_Init( 'new_blog', $blog_id ); |
| 104 |
} |
| 105 |
|
| 106 |
/** |
| 107 |
* handle deleting a blog on a network |
| 108 |
* |
| 109 |
* @param int $blog_id of the blog to delete |
| 110 |
* @param bool $drop if true, delete tables |
| 111 |
*/ |
| 112 |
public static function delete_blog( $blog_id, $drop ) |
| 113 |
{ |
| 114 |
if ( $drop ) { |
| 115 |
new PDb_Init( 'delete_blog', $blog_id ); |
| 116 |
} |
| 117 |
} |
| 118 |
|
| 119 |
/** |
| 120 |
* performs the activation |
| 121 |
* |
| 122 |
* @global wpdb $wpdb |
| 123 |
*/ |
| 124 |
private function _activate() |
| 125 |
{ |
| 126 |
global $wpdb; |
| 127 |
|
| 128 |
Participants_Db::setup_source_names(); |
| 129 |
|
| 130 |
// install the db tables if needed |
| 131 |
$this->maybe_install(); |
| 132 |
|
| 133 |
// check for the need to run the fix for issue #2039 |
| 134 |
$check = $wpdb->get_results('SELECT count(*) FROM `' . Participants_Db::$fields_table . '` WHERE `group` = ""'); |
| 135 |
if ( $check > 0 ) { |
| 136 |
$fields = array( "id","private_id"); |
| 137 |
foreach( $fields as $fieldname ) { |
| 138 |
$wpdb->update( Participants_Db::$fields_table, array('group' => 'internal', 'form_element' => 'text-line'), array('name' => $fieldname) ); |
| 139 |
} |
| 140 |
$fields = array( "date_recorded","date_updated","last_accessed"); |
| 141 |
foreach( $fields as $fieldname ) { |
| 142 |
$wpdb->update( Participants_Db::$fields_table, array('group' => 'internal', 'form_element' => 'timestamp'), array('name' => $fieldname) ); |
| 143 |
} |
| 144 |
} |
| 145 |
|
| 146 |
do_action( 'pdb-plugin_activation' ); |
| 147 |
|
| 148 |
error_log( Participants_Db::PLUGIN_NAME . ' plugin activated' ); |
| 149 |
} |
| 150 |
|
| 151 |
/** |
| 152 |
* performs the activation on a network |
| 153 |
* |
| 154 |
*/ |
| 155 |
private function _network_activate() |
| 156 |
{ |
| 157 |
self::do_network_operation( array($this, 'maybe_install') ); |
| 158 |
|
| 159 |
do_action( 'pdb-plugin_network_activation' ); |
| 160 |
|
| 161 |
error_log( Participants_Db::PLUGIN_NAME . ' plugin activated' ); |
| 162 |
} |
| 163 |
|
| 164 |
/** |
| 165 |
* install tables when a new blog is created |
| 166 |
* |
| 167 |
* @global wpdb $wpdb |
| 168 |
* @param int $blog_id the id of the new blog |
| 169 |
*/ |
| 170 |
private function _new_blog( $blog_id ) |
| 171 |
{ |
| 172 |
global $wpdb; |
| 173 |
|
| 174 |
if ( is_plugin_active_for_network( 'participants-database/participants-database.php' ) ) { |
| 175 |
$current_blog = $wpdb->blogid; |
| 176 |
switch_to_blog( $blog_id ); |
| 177 |
$this->maybe_install(); |
| 178 |
switch_to_blog( $current_blog ); |
| 179 |
} |
| 180 |
} |
| 181 |
|
| 182 |
/** |
| 183 |
* deletes a blog's PDB tables |
| 184 |
* |
| 185 |
* @global wpdb $wpdb |
| 186 |
* @param int $blog_id |
| 187 |
* |
| 188 |
*/ |
| 189 |
private function _delete_blog( $blog_id ) |
| 190 |
{ |
| 191 |
global $wpdb; |
| 192 |
|
| 193 |
// store the currently active blog id |
| 194 |
$current_blog = $wpdb->blogid; |
| 195 |
switch_to_blog( $blog_id ); |
| 196 |
Participants_Db::setup_source_names(); |
| 197 |
|
| 198 |
|
| 199 |
// delete tables |
| 200 |
$sql = 'DROP TABLE IF EXISTS `' . Participants_Db::$fields_table . '`, `' . Participants_Db::$participants_table . '`, `' . Participants_Db::$groups_table . '`;'; |
| 201 |
$wpdb->query( $sql ); |
| 202 |
|
| 203 |
// return to the current blog selection |
| 204 |
switch_to_blog( $current_blog ); |
| 205 |
} |
| 206 |
|
| 207 |
/** |
| 208 |
* triggers a database install if needed |
| 209 |
* |
| 210 |
* @global wpdb $wpdb |
| 211 |
*/ |
| 212 |
public function maybe_install() |
| 213 |
{ |
| 214 |
global $wpdb; |
| 215 |
Participants_Db::setup_source_names(); |
| 216 |
|
| 217 |
if ( $this->needs_install() ) { |
| 218 |
$this->_install_database(); |
| 219 |
} |
| 220 |
} |
| 221 |
|
| 222 |
/** |
| 223 |
* checks for the need to install the tables |
| 224 |
* |
| 225 |
* @global wpdb $wpdb |
| 226 |
* @return bool true if the tables do not exist |
| 227 |
*/ |
| 228 |
private function needs_install() |
| 229 |
{ |
| 230 |
global $wpdb; |
| 231 |
return $wpdb->get_var( 'SHOW TABLES LIKE "' . Participants_Db::$participants_table . '"' ) != Participants_Db::$participants_table; |
| 232 |
} |
| 233 |
|
| 234 |
/** |
| 235 |
* checks if multisite is active |
| 236 |
* |
| 237 |
*/ |
| 238 |
public static function is_network() |
| 239 |
{ |
| 240 |
return function_exists( 'is_multisite' ) && is_multisite(); |
| 241 |
} |
| 242 |
|
| 243 |
/** |
| 244 |
* deactivates the plugin; does a litle housekeeping |
| 245 |
*/ |
| 246 |
private function _deactivate() |
| 247 |
{ |
| 248 |
error_log( Participants_Db::PLUGIN_NAME . ' plugin deactivated' ); |
| 249 |
|
| 250 |
do_action( 'participants_database_deactivated' ); |
| 251 |
} |
| 252 |
|
| 253 |
/** |
| 254 |
* uninstalls the plugin network-wide |
| 255 |
* |
| 256 |
* @global wpdb $wpdb |
| 257 |
*/ |
| 258 |
private function _network_uninstall() |
| 259 |
{ |
| 260 |
self::do_network_operation( array($this, '_uninstall') ); |
| 261 |
|
| 262 |
do_action( 'pdb-plugin_network_uninstall' ); |
| 263 |
} |
| 264 |
|
| 265 |
/** |
| 266 |
* performs a network-wide operation |
| 267 |
* |
| 268 |
* @global wpdb $wpdb |
| 269 |
* @param array|string $callback a PHP callable to execute on each blog |
| 270 |
*/ |
| 271 |
public static function do_network_operation( $callback ) |
| 272 |
{ |
| 273 |
add_action( 'switch_blog', 'PDb_Init::switch_blog', 10, 2 ); |
| 274 |
|
| 275 |
global $wpdb; |
| 276 |
|
| 277 |
// store the currently active blog id |
| 278 |
$current_blog = $wpdb->blogid; |
| 279 |
|
| 280 |
// Get all blog ids |
| 281 |
$blogids = $wpdb->get_col( "SELECT blog_id FROM $wpdb->blogs" ); |
| 282 |
// cycle through all the blogs and make the callback on each one |
| 283 |
foreach ( $blogids as $blog_id ) { |
| 284 |
switch_to_blog( $blog_id ); |
| 285 |
call_user_func( $callback ); |
| 286 |
} |
| 287 |
// return to the current blog selection |
| 288 |
switch_to_blog( $current_blog ); |
| 289 |
} |
| 290 |
|
| 291 |
/** |
| 292 |
* handles switching the blog |
| 293 |
* |
| 294 |
* |
| 295 |
* @param int $new_blog id |
| 296 |
* @param int $current_blog id |
| 297 |
*/ |
| 298 |
public static function switch_blog( $new_blog, $current_blog ) |
| 299 |
{ |
| 300 |
if ( $new_blog != $current_blog ) { |
| 301 |
Participants_Db::setup_source_names(); |
| 302 |
} |
| 303 |
} |
| 304 |
|
| 305 |
/** |
| 306 |
* deletes all plugin tables, options and transients |
| 307 |
* |
| 308 |
* @global wpdb $wpdb |
| 309 |
*/ |
| 310 |
private function _uninstall() |
| 311 |
{ |
| 312 |
Participants_Db::initialize( false ); |
| 313 |
Participants_Db::setup_source_names(); |
| 314 |
|
| 315 |
do_action( 'participants_database_uninstall' ); |
| 316 |
do_action( 'uninstall_participants-database' ); |
| 317 |
|
| 318 |
global $wpdb; |
| 319 |
|
| 320 |
// delete tables |
| 321 |
$sql = 'DROP TABLE IF EXISTS `' . Participants_Db::$fields_table . '`, `' . Participants_Db::$participants_table . '`, `' . Participants_Db::$groups_table . '`;'; |
| 322 |
$wpdb->query( $sql ); |
| 323 |
|
| 324 |
// remove options |
| 325 |
delete_option( Participants_Db::$participants_db_options ); |
| 326 |
delete_option( Participants_Db::$db_version_option ); |
| 327 |
delete_option( Participants_Db::$default_options ); |
| 328 |
delete_option( Participants_Db::one_time_notice_flag ); |
| 329 |
delete_option( Participants_Db::$prefix . 'csv_import_params' ); |
| 330 |
|
| 331 |
// clear user options |
| 332 |
$delete_keys = array( |
| 333 |
Participants_Db::$prefix . PDb_List_Admin::$user_setting_name . '%', |
| 334 |
Participants_Db::$prefix . PDb_admin_list\filter::$filter_option . '%', |
| 335 |
); |
| 336 |
$sql = 'SELECT `option_name` FROM ' . $wpdb->prefix . 'options WHERE `option_name` LIKE "' . join( '" OR `option_name` LIKE "', $delete_keys ) . '"'; |
| 337 |
$options = $wpdb->get_col( $sql ); |
| 338 |
foreach ( $options as $name ) { |
| 339 |
delete_option( $name ); |
| 340 |
} |
| 341 |
|
| 342 |
// clear transients |
| 343 |
delete_transient( Participants_Db::$last_record ); |
| 344 |
$delete_keys = array( |
| 345 |
'%' . PDb_List_Admin::$user_setting_name . '%', |
| 346 |
'%' . Participants_Db::$prefix . 'captcha_key', |
| 347 |
'%' . Participants_Db::$prefix . 'signup-email-sent', |
| 348 |
'%' . PDb_Aux_Plugin::throttler . '%', |
| 349 |
'%' . \PDb_fields\calculated_field::keycache . '%', |
| 350 |
); |
| 351 |
$sql = 'SELECT `option_name` FROM ' . $wpdb->prefix . 'options WHERE `option_name` LIKE "' . join( '" OR `option_name` LIKE "', $delete_keys ) . '"'; |
| 352 |
$transients = $wpdb->get_col( $sql ); |
| 353 |
foreach ( $transients as $name ) { |
| 354 |
delete_transient( $name ); |
| 355 |
} |
| 356 |
|
| 357 |
error_log( Participants_Db::PLUGIN_NAME . ' plugin uninstalled' ); |
| 358 |
} |
| 359 |
|
| 360 |
/** |
| 361 |
* deletes all sessions created by the WP_Session class |
| 362 |
* |
| 363 |
* this could potentially interfere with another plugin that might use WP_Sessions, |
| 364 |
* but they're meant to be temporary, so it would only result in some minor disruption |
| 365 |
* |
| 366 |
* @global wpdb $wpdb |
| 367 |
*/ |
| 368 |
public static function delete_user_sessions() |
| 369 |
{ |
| 370 |
global $wpdb; |
| 371 |
// clear session entries |
| 372 |
$sql = 'SELECT `option_name` FROM ' . $wpdb->prefix . 'options WHERE ( `option_name` LIKE "_wp_session_%" AND `option_name` NOT LIKE "_wp_session_expires_%" )'; |
| 373 |
$sessions = $wpdb->get_col( $sql ); |
| 374 |
foreach ( $sessions as $name ) { |
| 375 |
delete_option( $name ); |
| 376 |
} |
| 377 |
} |
| 378 |
|
| 379 |
/** |
| 380 |
* installs the plugin database tables and default fields |
| 381 |
* |
| 382 |
* @global wpdb $wpdb |
| 383 |
*/ |
| 384 |
private function _install_database() |
| 385 |
{ |
| 386 |
global $wpdb; |
| 387 |
|
| 388 |
// define the arrays for loading the initial db records |
| 389 |
self::_define_init_arrays(); |
| 390 |
|
| 391 |
// create the field values table |
| 392 |
$sql = 'CREATE TABLE ' . Participants_Db::$fields_table . ' ( |
| 393 |
`id` INT(3) NOT NULL AUTO_INCREMENT, |
| 394 |
`order` INT(3) NOT NULL DEFAULT 0, |
| 395 |
`name` VARCHAR(64) NOT NULL, |
| 396 |
`title` TINYTEXT NOT NULL, |
| 397 |
`default` TEXT NULL, |
| 398 |
`group` VARCHAR(64) NOT NULL, |
| 399 |
`form_element` TINYTEXT NULL, |
| 400 |
`options` LONGTEXT NULL, |
| 401 |
`attributes` TEXT NULL, |
| 402 |
`validation` TINYTEXT NULL, |
| 403 |
`validation_message` TEXT NULL, |
| 404 |
`help_text` TEXT NULL, |
| 405 |
`display_column` INT(3) DEFAULT 0, |
| 406 |
`admin_column` INT(3) DEFAULT 0, |
| 407 |
`sortable` BOOLEAN DEFAULT 0, |
| 408 |
`CSV` BOOLEAN DEFAULT 0, |
| 409 |
`persistent` BOOLEAN DEFAULT 0, |
| 410 |
`signup` BOOLEAN DEFAULT 0, |
| 411 |
`readonly` BOOLEAN DEFAULT 0, |
| 412 |
UNIQUE KEY ( `name` ), |
| 413 |
INDEX ( `order` ), |
| 414 |
INDEX ( `group` ), |
| 415 |
PRIMARY KEY ( `id` ) |
| 416 |
) |
| 417 |
DEFAULT CHARACTER SET utf8mb4 |
| 418 |
COLLATE utf8mb4_unicode_ci |
| 419 |
AUTO_INCREMENT = 0 |
| 420 |
'; |
| 421 |
$wpdb->query( $sql ); |
| 422 |
|
| 423 |
// create the groups table |
| 424 |
$sql = 'CREATE TABLE ' . Participants_Db::$groups_table . ' ( |
| 425 |
`id` INT(3) NOT NULL AUTO_INCREMENT, |
| 426 |
`order` INT(3) NOT NULL DEFAULT 0, |
| 427 |
`mode` VARCHAR(64) NOT NULL, |
| 428 |
`display` BOOLEAN DEFAULT 1, |
| 429 |
`admin` BOOLEAN DEFAULT 0, |
| 430 |
`title` TINYTEXT NOT NULL, |
| 431 |
`name` VARCHAR(64) NOT NULL, |
| 432 |
`description` TEXT NULL, |
| 433 |
UNIQUE KEY ( `name` ), |
| 434 |
PRIMARY KEY ( `id` ) |
| 435 |
) |
| 436 |
DEFAULT CHARACTER SET utf8mb4 |
| 437 |
COLLATE utf8mb4_unicode_ci |
| 438 |
AUTO_INCREMENT = 1 |
| 439 |
'; |
| 440 |
$wpdb->query( $sql ); |
| 441 |
|
| 442 |
// create the main data table |
| 443 |
$sql = 'CREATE TABLE ' . Participants_Db::$participants_table . ' ( |
| 444 |
`id` int(6) NOT NULL AUTO_INCREMENT, |
| 445 |
`private_id` VARCHAR(9) NULL, |
| 446 |
'; |
| 447 |
foreach ( array_keys( self::$field_groups ) as $group ) { |
| 448 |
|
| 449 |
// these are not added to the sql in the loop |
| 450 |
if ( $group == 'internal' ) |
| 451 |
continue; |
| 452 |
|
| 453 |
foreach ( self::${$group . '_fields'} as $name => &$defaults ) { |
| 454 |
|
| 455 |
if ( !isset( $defaults['form_element'] ) ) |
| 456 |
$defaults['form_element'] = 'text-line'; |
| 457 |
|
| 458 |
$datatype = PDb_FormElement::get_datatype( $defaults['form_element'] ); |
| 459 |
|
| 460 |
$sql .= '`' . $name . '` ' . $datatype . ' NULL, '; |
| 461 |
} |
| 462 |
} |
| 463 |
|
| 464 |
$sql .= '`date_recorded` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| 465 |
`date_updated` TIMESTAMP NULL DEFAULT NULL, |
| 466 |
`last_accessed` TIMESTAMP NULL DEFAULT NULL, |
| 467 |
PRIMARY KEY (`id`) |
| 468 |
) |
| 469 |
DEFAULT CHARACTER SET utf8mb4 |
| 470 |
COLLATE utf8mb4_unicode_ci |
| 471 |
;'; |
| 472 |
|
| 473 |
$wpdb->query( $sql ); |
| 474 |
|
| 475 |
// save the db version |
| 476 |
update_option( Participants_Db::$db_version_option, Participants_Db::$db_version ); |
| 477 |
|
| 478 |
// now load the default values into the database |
| 479 |
$i = 0; |
| 480 |
unset( $defaults ); |
| 481 |
foreach ( array_keys( self::$field_groups ) as $group ) { |
| 482 |
|
| 483 |
foreach ( self::${$group . '_fields'} as $name => $defaults ) { |
| 484 |
|
| 485 |
$defaults['name'] = $name; |
| 486 |
$defaults['group'] = $group; |
| 487 |
$defaults['order'] = $i; |
| 488 |
$defaults['validation'] = isset( $defaults['validation'] ) ? $defaults['validation'] : 'no'; |
| 489 |
|
| 490 |
if ( isset( $defaults['options'] ) && is_array( $defaults['options'] ) ) { |
| 491 |
|
| 492 |
$defaults['options'] = serialize( $defaults['options'] ); |
| 493 |
} |
| 494 |
|
| 495 |
$wpdb->insert( Participants_Db::$fields_table, $defaults ); |
| 496 |
|
| 497 |
$i++; |
| 498 |
} |
| 499 |
} |
| 500 |
|
| 501 |
// put in the default groups |
| 502 |
$i = 1; |
| 503 |
$defaults = array(); |
| 504 |
foreach ( self::$field_groups as $group => $title ) { |
| 505 |
$defaults['name'] = $group; |
| 506 |
$defaults['title'] = $title; |
| 507 |
$defaults['mode'] = ( in_array( $group, array('internal', 'admin', 'source') ) ? 'admin' : 'public' ); |
| 508 |
$defaults['admin'] = ( in_array( $group, array('internal', 'admin', 'source') ) ? '1' : '0' ); |
| 509 |
$defaults['display'] = ( in_array( $group, array('internal', 'admin', 'source') ) ? '0' : '1' ); |
| 510 |
$defaults['order'] = $i; |
| 511 |
|
| 512 |
$wpdb->insert( Participants_Db::$groups_table, $defaults ); |
| 513 |
|
| 514 |
$i++; |
| 515 |
} |
| 516 |
} |
| 517 |
|
| 518 |
/** |
| 519 |
* performs an update to the database if needed |
| 520 |
* |
| 521 |
* @global wpdb $wpdb |
| 522 |
*/ |
| 523 |
public static function on_update() |
| 524 |
{ |
| 525 |
global $wpdb; |
| 526 |
|
| 527 |
// determine the actual version status of the database |
| 528 |
self::set_database_real_version(); |
| 529 |
|
| 530 |
$db_version = get_option( Participants_Db::$db_version_option ); |
| 531 |
|
| 532 |
Participants_Db::debug_log( 'participants database db version determined to be: ' . $db_version ); |
| 533 |
|
| 534 |
$success = true; |
| 535 |
|
| 536 |
if ( false === $db_version || '0.1' == $db_version ) { |
| 537 |
|
| 538 |
/* |
| 539 |
* updates version 0.1 database to 0.2 |
| 540 |
* |
| 541 |
* adding a new column "display_column" and renaming "column" to |
| 542 |
* "admin_column" to accommodate the new frontend display shortcode |
| 543 |
*/ |
| 544 |
|
| 545 |
$sql = "ALTER TABLE " . Participants_Db::$fields_table . " ADD COLUMN `display_column` INT(3) DEFAULT 0 AFTER `validation`,"; |
| 546 |
|
| 547 |
$sql .= "CHANGE COLUMN `column` `admin_column` INT(3)"; |
| 548 |
|
| 549 |
if ( false !== $wpdb->query( $sql ) ) { |
| 550 |
|
| 551 |
// in case the option doesn't exist |
| 552 |
add_option( Participants_Db::$db_version_option ); |
| 553 |
|
| 554 |
// set the version number this step brings the db to |
| 555 |
$db_version = '0.2'; |
| 556 |
} |
| 557 |
|
| 558 |
// load some preset values into new column |
| 559 |
$values = array( |
| 560 |
'first_name' => 1, |
| 561 |
'last_name' => 2, |
| 562 |
'city' => 3, |
| 563 |
'state' => 4 |
| 564 |
); |
| 565 |
foreach ( $values as $field => $value ) { |
| 566 |
$wpdb->update( |
| 567 |
Participants_Db::$fields_table, array('display_column' => $value), array('name' => $field) |
| 568 |
); |
| 569 |
} |
| 570 |
} |
| 571 |
|
| 572 |
if ( '0.2' == $db_version ) { |
| 573 |
|
| 574 |
/* |
| 575 |
* updates version 0.2 database to 0.3 |
| 576 |
* |
| 577 |
* modifying the 'values' column of the fields table to allow for larger |
| 578 |
* select option lists |
| 579 |
*/ |
| 580 |
|
| 581 |
$sql = "ALTER TABLE " . Participants_Db::$fields_table . " MODIFY COLUMN `values` LONGTEXT NULL DEFAULT NULL"; |
| 582 |
|
| 583 |
if ( false !== $wpdb->query( $sql ) ) { |
| 584 |
|
| 585 |
// set the version number this step brings the db to |
| 586 |
$db_version = '0.3'; |
| 587 |
} |
| 588 |
} |
| 589 |
|
| 590 |
if ( '0.3' == $db_version ) { |
| 591 |
|
| 592 |
/* |
| 593 |
* updates version 0.3 database to 0.4 |
| 594 |
* |
| 595 |
* changing the 'when' field to a date field |
| 596 |
* exchanging all the PHP string date values to UNIX timestamps in all form_element = 'date' fields |
| 597 |
* |
| 598 |
*/ |
| 599 |
|
| 600 |
// change the 'when' field to a date field |
| 601 |
$wpdb->update( Participants_Db::$fields_table, array('form_element' => 'date'), array('name' => 'when', 'form_element' => 'text-line') ); |
| 602 |
|
| 603 |
// |
| 604 |
$date_fields = $wpdb->get_results( 'SELECT f.name FROM ' . Participants_Db::$fields_table . ' f WHERE f.form_element = "date"', ARRAY_A ); |
| 605 |
|
| 606 |
$df_string = ''; |
| 607 |
|
| 608 |
foreach ( $date_fields as $date_field ) { |
| 609 |
|
| 610 |
if ( !in_array( $date_field['name'], array('date_recorded', 'date_updated') ) ) |
| 611 |
$df_string .= ',`' . $date_field['name'] . '` '; |
| 612 |
} |
| 613 |
|
| 614 |
// skip updating the Db if there's nothing to update |
| 615 |
if ( !empty( $df_string ) ) : |
| 616 |
|
| 617 |
$query = ' |
| 618 |
|
| 619 |
SELECT `id`' . $df_string . ' |
| 620 |
FROM ' . Participants_Db::$participants_table; |
| 621 |
|
| 622 |
$fields = $wpdb->get_results( $query, ARRAY_A ); |
| 623 |
|
| 624 |
|
| 625 |
// now that we have all the date field values, convert them to N=UNIX timestamps |
| 626 |
foreach ( $fields as $row ) { |
| 627 |
|
| 628 |
$id = $row['id']; |
| 629 |
unset( $row['id'] ); |
| 630 |
|
| 631 |
$update_row = array(); |
| 632 |
|
| 633 |
foreach ( $row as $field => $original_value ) { |
| 634 |
|
| 635 |
if ( empty( $original_value ) ) |
| 636 |
continue 2; |
| 637 |
|
| 638 |
// if it's already a timestamp, we don't try to convert |
| 639 |
$value = preg_match( '#^[0-9]+$#', $original_value ) > 0 ? $original_value : strtotime( $original_value ); |
| 640 |
|
| 641 |
// if strtotime fails, revert to original value |
| 642 |
$update_row[$field] = ( false === $value ? $original_value : $value ); |
| 643 |
} |
| 644 |
|
| 645 |
$wpdb->update( |
| 646 |
Participants_Db::$participants_table, $update_row, array('id' => $id) |
| 647 |
); |
| 648 |
} |
| 649 |
|
| 650 |
endif; |
| 651 |
|
| 652 |
// set the version number this step brings the db to |
| 653 |
$db_version = '0.4'; |
| 654 |
} |
| 655 |
|
| 656 |
if ( '0.4' == $db_version ) { |
| 657 |
|
| 658 |
/* |
| 659 |
* updates version 0.4 database to 0.5 |
| 660 |
* |
| 661 |
* modifying the "import" column to be named more appropriately "CSV" |
| 662 |
*/ |
| 663 |
|
| 664 |
$sql = "ALTER TABLE " . Participants_Db::$fields_table . " CHANGE COLUMN `import` `CSV` TINYINT(1)"; |
| 665 |
|
| 666 |
if ( false !== $wpdb->query( $sql ) ) { |
| 667 |
|
| 668 |
// set the version number this step brings the db to |
| 669 |
$db_version = '0.5'; |
| 670 |
} |
| 671 |
} |
| 672 |
|
| 673 |
/* this fixes an error I made in the 0.5 DB update |
| 674 |
*/ |
| 675 |
if ( '0.5' == $db_version && false === Participants_Db::get_participant() ) { |
| 676 |
|
| 677 |
// define the arrays for loading the initial db records |
| 678 |
self::_define_init_arrays(); |
| 679 |
|
| 680 |
// load the default values into the database |
| 681 |
$i = 0; |
| 682 |
unset( $defaults ); |
| 683 |
foreach ( array_keys( self::$field_groups ) as $group ) { |
| 684 |
|
| 685 |
foreach ( self::${$group . '_fields'} as $name => $defaults ) { |
| 686 |
|
| 687 |
$defaults['name'] = $name; |
| 688 |
$defaults['group'] = $group; |
| 689 |
$defaults['CSV'] = 'main' == $group ? 1 : 0; |
| 690 |
$defaults['order'] = $i; |
| 691 |
$defaults['validation'] = isset( $defaults['validation'] ) ? $defaults['validation'] : 'no'; |
| 692 |
|
| 693 |
if ( isset( $defaults['options'] ) && is_array( $defaults['options'] ) ) { |
| 694 |
|
| 695 |
$defaults['options'] = serialize( $defaults['options'] ); |
| 696 |
} |
| 697 |
|
| 698 |
$wpdb->insert( Participants_Db::$fields_table, $defaults ); |
| 699 |
|
| 700 |
$i++; |
| 701 |
} |
| 702 |
} |
| 703 |
// set the version number this step brings the db to |
| 704 |
$db_version = '0.5.1'; |
| 705 |
} |
| 706 |
|
| 707 |
/* |
| 708 |
* this is to fix a problem with the timestamp having it's datatype |
| 709 |
* changed when the field attributes are edited |
| 710 |
*/ |
| 711 |
if ( '0.51' == $db_version ) { |
| 712 |
|
| 713 |
$sql = "SHOW FIELDS FROM " . Participants_Db::$participants_table . " WHERE `field` IN ('date_recorded','date_updated')"; |
| 714 |
$field_info = $wpdb->get_results( $sql ); |
| 715 |
|
| 716 |
foreach ( $field_info as $field ) { |
| 717 |
|
| 718 |
if ( $field->Type !== 'TIMESTAMP' ) { |
| 719 |
|
| 720 |
switch ( $field->Field ) { |
| 721 |
|
| 722 |
case 'date_recorded': |
| 723 |
|
| 724 |
$column_definition = '`date_recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP'; |
| 725 |
break; |
| 726 |
|
| 727 |
case 'date_updated': |
| 728 |
|
| 729 |
$column_definition = '`date_updated` TIMESTAMP NOT NULL DEFAULT 0'; |
| 730 |
break; |
| 731 |
|
| 732 |
default: |
| 733 |
|
| 734 |
$column_definition = false; |
| 735 |
} |
| 736 |
|
| 737 |
if ( false !== $column_definition ) { |
| 738 |
|
| 739 |
$sql = "ALTER TABLE " . Participants_Db::$participants_table . " MODIFY COLUMN " . $column_definition; |
| 740 |
|
| 741 |
$result = $wpdb->get_results( $sql ); |
| 742 |
} |
| 743 |
} |
| 744 |
} |
| 745 |
|
| 746 |
// delete the default record |
| 747 |
$wpdb->query( $wpdb->prepare( "DELETE FROM " . Participants_Db::$participants_table . " WHERE private_id = '%s'", 'RPNE2' ) ); |
| 748 |
|
| 749 |
// add the new private ID admin column setting because we eliminated the redundant special setting |
| 750 |
$options = get_option( Participants_Db::$participants_db_options ); |
| 751 |
if ( $options['show_pid'] ) { |
| 752 |
$wpdb->update( Participants_Db::$fields_table, array('admin_column' => 90), array('name' => 'private_id') ); |
| 753 |
} |
| 754 |
|
| 755 |
/* |
| 756 |
* add the "read-only" column |
| 757 |
*/ |
| 758 |
$sql = "ALTER TABLE " . Participants_Db::$fields_table . " ADD COLUMN `readonly` BOOLEAN DEFAULT 0 AFTER `signup`"; |
| 759 |
|
| 760 |
$wpdb->query( $sql ); |
| 761 |
|
| 762 |
/* |
| 763 |
* change the old 'textarea' field type to the new 'text-area' |
| 764 |
*/ |
| 765 |
$sql = " |
| 766 |
UPDATE " . Participants_Db::$fields_table . " |
| 767 |
SET `form_element` = replace(`form_element`, \"textarea\", \"text-area\")"; |
| 768 |
$wpdb->query( $sql ); |
| 769 |
$sql = " |
| 770 |
UPDATE " . Participants_Db::$fields_table . " |
| 771 |
SET `form_element` = replace(`form_element`, \"text-field\", \"text-line\") "; |
| 772 |
|
| 773 |
|
| 774 |
if ( false !== $wpdb->query( $sql ) ) { |
| 775 |
|
| 776 |
// update the stored DB version number |
| 777 |
$db_version = '0.55'; |
| 778 |
} |
| 779 |
} |
| 780 |
|
| 781 |
/* |
| 782 |
* this database version adds the "last_accessed" column to the main database |
| 783 |
* |
| 784 |
*/ |
| 785 |
if ( '0.55' == $db_version ) { |
| 786 |
|
| 787 |
/* |
| 788 |
* add the "last_accessed" column |
| 789 |
*/ |
| 790 |
$sql = "ALTER TABLE " . Participants_Db::$participants_table . " ADD COLUMN `last_accessed` TIMESTAMP NOT NULL AFTER `date_updated`"; |
| 791 |
|
| 792 |
$wpdb->query( $sql ); |
| 793 |
|
| 794 |
/* |
| 795 |
* add the new field to the fields table |
| 796 |
*/ |
| 797 |
$data = array( |
| 798 |
'order' => '20', |
| 799 |
'name' => 'last_accessed', |
| 800 |
'title' => 'Last Accessed', |
| 801 |
'group' => 'internal', |
| 802 |
'sortable' => '1', |
| 803 |
'form_element' => 'date', |
| 804 |
); |
| 805 |
|
| 806 |
if ( false !== $wpdb->insert( Participants_Db::$fields_table, $data ) ) { |
| 807 |
|
| 808 |
// update the stored DB version number |
| 809 |
$db_version = '0.6'; |
| 810 |
} |
| 811 |
} |
| 812 |
if ( '0.6' == $db_version ) { |
| 813 |
/* |
| 814 |
* this database version changes the internal timestamp fields from "date" |
| 815 |
* type to "timestamp" type fields, also sets the 'readonly' flag of internal |
| 816 |
* fields so we don't have to treat them as a special case any more. |
| 817 |
* |
| 818 |
* set the field type of internal timestamp fields to 'timestamp' |
| 819 |
*/ |
| 820 |
$sql = "UPDATE " . Participants_Db::$fields_table . " p SET p.form_element = 'timestamp', p.readonly = '1' WHERE p.name IN ('date_recorded','last_accessed','date_updated')"; |
| 821 |
if ( $wpdb->query( $sql ) !== false ) { |
| 822 |
// update the stored DB version number |
| 823 |
$db_version = '0.65'; |
| 824 |
} else { |
| 825 |
self::post_failure_notice( $wpdb->last_error, '0.65' ); |
| 826 |
} |
| 827 |
} |
| 828 |
if ( '0.65' == $db_version ) { |
| 829 |
/* |
| 830 |
* adds a new column to the goups database so a group cna be designated as a "admin" group |
| 831 |
*/ |
| 832 |
$sql = "ALTER TABLE " . Participants_Db::$groups_table . " ADD COLUMN `admin` BOOLEAN NOT NULL DEFAULT 0 AFTER `order`"; |
| 833 |
|
| 834 |
if ( $wpdb->query( $sql ) !== false ) { |
| 835 |
// update the stored DB version number |
| 836 |
$db_version = '0.7'; |
| 837 |
} else { |
| 838 |
self::post_failure_notice( $wpdb->last_error, '0.7' ); |
| 839 |
} |
| 840 |
|
| 841 |
$sql = "UPDATE " . Participants_Db::$groups_table . " g SET g.admin = '1' WHERE g.name ='internal'"; |
| 842 |
$wpdb->query( $sql ); |
| 843 |
} |
| 844 |
if ( '0.7' == $db_version ) { |
| 845 |
/* |
| 846 |
* changes all date fields' datatype to BIGINT unless the user has modified the datatype |
| 847 |
*/ |
| 848 |
$sql = 'SELECT f.name FROM ' . Participants_Db::$fields_table . ' f INNER JOIN INFORMATION_SCHEMA.COLUMNS AS c ON TABLE_NAME = "' . Participants_Db::$participants_table . '" AND c.column_name = f.name COLLATE utf8_general_ci AND data_type = "TINYTEXT" WHERE f.form_element = "date"'; |
| 849 |
|
| 850 |
$results = $wpdb->get_results( $sql, ARRAY_A ); |
| 851 |
$fields = array(); |
| 852 |
foreach ( $results as $result ) { |
| 853 |
$fields[] = $result['name']; |
| 854 |
} |
| 855 |
|
| 856 |
if ( count( $fields ) === 0 ) { |
| 857 |
|
| 858 |
// nothing to change, update the version number |
| 859 |
$db_version = '0.8'; |
| 860 |
} else { |
| 861 |
|
| 862 |
$results = $wpdb->get_results( "SHOW COLUMNS FROM `" . Participants_Db::$participants_table . "`" ); |
| 863 |
$columns = array(); |
| 864 |
foreach ( $results as $result ) { |
| 865 |
$columns[] = $result->Field; |
| 866 |
} |
| 867 |
$fields = array_intersect( $columns, $fields ); |
| 868 |
$sql = 'ALTER TABLE ' . Participants_Db::$participants_table . ' MODIFY COLUMN `' . implode( '` BIGINT NULL, MODIFY COLUMN `', $fields ) . '` BIGINT NULL'; |
| 869 |
|
| 870 |
if ( false !== $wpdb->query( $sql ) ) { |
| 871 |
// set the version number this step brings the db to |
| 872 |
$db_version = '0.8'; |
| 873 |
} else { |
| 874 |
self::post_failure_notice( $wpdb->last_error, '0.8' ); |
| 875 |
} |
| 876 |
} |
| 877 |
} |
| 878 |
if ( '0.8' == $db_version ) { |
| 879 |
/* |
| 880 |
* all field and group names need more storage space, changing the datatype to VARCHAR(64) |
| 881 |
*/ |
| 882 |
$sql = "ALTER TABLE " . Participants_Db::$fields_table . " MODIFY `name` VARCHAR(64) NOT NULL, MODIFY `group` VARCHAR(64) NOT NULL"; |
| 883 |
|
| 884 |
if ( false !== $wpdb->query( $sql ) ) { |
| 885 |
|
| 886 |
$sql = "ALTER TABLE " . Participants_Db::$groups_table . " MODIFY `name` VARCHAR(64) NOT NULL"; |
| 887 |
|
| 888 |
if ( false !== $wpdb->query( $sql ) ) { |
| 889 |
// set the version number this step brings the db to |
| 890 |
$db_version = '0.9'; |
| 891 |
} else { |
| 892 |
self::post_failure_notice( $wpdb->last_error, '0.9' ); |
| 893 |
} |
| 894 |
} |
| 895 |
} |
| 896 |
|
| 897 |
if ( '0.9' == $db_version ) { |
| 898 |
/* |
| 899 |
* set TIMESTAMP fields to allow NULL and set the default to NULL |
| 900 |
*/ |
| 901 |
$success = $wpdb->query( "ALTER TABLE `" . Participants_Db::$participants_table . "` MODIFY COLUMN `date_updated` TIMESTAMP NULL DEFAULT NULL" ); |
| 902 |
if ( $success !== false ) |
| 903 |
$success = $wpdb->query( "ALTER TABLE `" . Participants_Db::$participants_table . "` MODIFY COLUMN `last_accessed` TIMESTAMP NULL DEFAULT NULL" ); |
| 904 |
/* |
| 905 |
* set other "not null" columns to NULL so the empty default value won't trigger an error |
| 906 |
*/ |
| 907 |
if ( $success !== false ) |
| 908 |
$success = $wpdb->query( "ALTER TABLE `" . Participants_Db::$participants_table . "` MODIFY COLUMN `private_id` VARCHAR(6) NULL" ); |
| 909 |
if ( $success !== false ) |
| 910 |
$success = $wpdb->query( "ALTER TABLE `" . Participants_Db::$fields_table . "` MODIFY COLUMN `name` VARCHAR(64) NULL" ); |
| 911 |
if ( $success !== false ) |
| 912 |
$success = $wpdb->query( "ALTER TABLE `" . Participants_Db::$fields_table . "` MODIFY COLUMN `title` TINYTEXT NULL" ); |
| 913 |
if ( $success !== false ) |
| 914 |
$success = $wpdb->query( "ALTER TABLE `" . Participants_Db::$fields_table . "` MODIFY COLUMN `group` VARCHAR(64) NULL" ); |
| 915 |
if ( $success !== false ) |
| 916 |
$success = $wpdb->query( "ALTER TABLE `" . Participants_Db::$groups_table . "` MODIFY COLUMN `name` VARCHAR(64) NULL" ); |
| 917 |
if ( $success !== false ) |
| 918 |
$success = $wpdb->query( "ALTER TABLE `" . Participants_Db::$groups_table . "` MODIFY COLUMN `title` TINYTEXT NULL" ); |
| 919 |
// |
| 920 |
if ( $success !== false ) { |
| 921 |
$table_status = $wpdb->get_results( "SHOW TABLE STATUS WHERE `name` = '" . Participants_Db::$participants_table . "'" ); |
| 922 |
if ( current( $table_status )->Collation !== 'utf8_unicode_ci' ) { |
| 923 |
if ( $success !== false ) |
| 924 |
$success = $wpdb->query( "alter table `" . Participants_Db::$participants_table . "` convert to character set utf8 collate utf8_unicode_ci" ); |
| 925 |
if ( $success !== false ) |
| 926 |
$success = $wpdb->query( "alter table `" . Participants_Db::$fields_table . "` convert to character set utf8 collate utf8_unicode_ci" ); |
| 927 |
if ( $success !== false ) |
| 928 |
$success = $wpdb->query( "alter table `" . Participants_Db::$groups_table . "` convert to character set utf8 collate utf8_unicode_ci" ); |
| 929 |
} |
| 930 |
} |
| 931 |
|
| 932 |
if ( $success === false ) { |
| 933 |
self::post_failure_notice( $wpdb->last_error, '1.0' ); |
| 934 |
} else { |
| 935 |
$db_version = '1.0'; |
| 936 |
} |
| 937 |
} |
| 938 |
|
| 939 |
// update from 1.0 to 1.1 |
| 940 |
if ( '1.0' == $db_version ) { |
| 941 |
|
| 942 |
$success = $wpdb->query( "ALTER TABLE " . Participants_Db::$fields_table . " ADD COLUMN `options` LONGTEXT NULL AFTER `values`" ); |
| 943 |
if ( $success !== false ) |
| 944 |
$success = $wpdb->query( "ALTER TABLE " . Participants_Db::$fields_table . " ADD COLUMN `attributes` TEXT NULL AFTER `options`" ); |
| 945 |
if ( $success !== false ) |
| 946 |
$success = $wpdb->query( "ALTER TABLE " . Participants_Db::$fields_table . " ADD COLUMN `validation_message` TEXT NULL AFTER `validation`" ); |
| 947 |
if ( $success !== false ) |
| 948 |
$success = $wpdb->query( "ALTER TABLE " . Participants_Db::$groups_table . " ADD COLUMN `mode` VARCHAR(64) NULL AFTER `order`" ); |
| 949 |
|
| 950 |
|
| 951 |
if ( $success === false ) { |
| 952 |
self::post_failure_notice( $wpdb->last_error, '1.1' ); |
| 953 |
} else { |
| 954 |
self::set_mode_column_values(); |
| 955 |
self::update_field_def_values(); |
| 956 |
$db_version = '1.1'; |
| 957 |
} |
| 958 |
} |
| 959 |
|
| 960 |
// update from 1.1 to 1.2 |
| 961 |
if ( '1.1' == $db_version ) { |
| 962 |
|
| 963 |
// give the default parameter more space #2703 |
| 964 |
$success = $wpdb->query( "ALTER TABLE " . Participants_Db::$fields_table . " MODIFY COLUMN `default` TEXT NULL" ); |
| 965 |
|
| 966 |
if ( $success === false ) { |
| 967 |
self::post_failure_notice( $wpdb->last_error, '1.2' ); |
| 968 |
} else { |
| 969 |
$db_version = '1.2'; |
| 970 |
} |
| 971 |
} |
| 972 |
|
| 973 |
// update 1.2 to 1.3 |
| 974 |
if ( '1.2' == $db_version ) |
| 975 |
{ |
| 976 |
PDb_Manage_Fields_Updates::repair_internal_fields(); |
| 977 |
$success = true; |
| 978 |
$db_version = '1.3'; |
| 979 |
} |
| 980 |
|
| 981 |
update_option( Participants_Db::$db_version_option, $db_version ); |
| 982 |
|
| 983 |
if ( $success ) { |
| 984 |
$update_message = 'plugin updated to database version %s'; |
| 985 |
} else { |
| 986 |
$update_message = 'plugin failed to update database; version remains at %s'; |
| 987 |
} |
| 988 |
Participants_Db::debug_log( Participants_Db::PLUGIN_NAME . ' ' . sprintf( $update_message, $db_version ) ); |
| 989 |
} |
| 990 |
|
| 991 |
/** |
| 992 |
* notifies the user in case of a database update failure |
| 993 |
* |
| 994 |
* @param string $db_error the database error message |
| 995 |
* @param string $error_message the message to show |
| 996 |
*/ |
| 997 |
public static function post_failure_notice( $db_error, $db_version, $error_message = false ) |
| 998 |
{ |
| 999 |
if ( $error_message === false ) { |
| 1000 |
$error_message = __('The database update to version %s failed with error: %s ', 'participants-database' ); |
| 1001 |
} |
| 1002 |
|
| 1003 |
PDb_Admin_Notices::post_error( sprintf( $error_message, $db_version, $db_error ), '', true ); |
| 1004 |
|
| 1005 |
Participants_Db::debug_log( Participants_Db::PLUGIN_NAME . ': ' . sprintf( $error_message, $db_version, $db_error ) ); |
| 1006 |
} |
| 1007 |
|
| 1008 |
/** |
| 1009 |
* performs a series of tests on the database to determine it's actual version |
| 1010 |
* |
| 1011 |
* this is because it is apparently possible for the database version option |
| 1012 |
* to be incorrect or missing. This way, we know with some certainty which version |
| 1013 |
* the database really is. Every time we create a new database version, we add |
| 1014 |
* a test for it here. |
| 1015 |
* |
| 1016 |
* @global object $wpdb |
| 1017 |
* @return null |
| 1018 |
*/ |
| 1019 |
private static function set_database_real_version() |
| 1020 |
{ |
| 1021 |
|
| 1022 |
global $wpdb; |
| 1023 |
$current_version = '0.1'; |
| 1024 |
|
| 1025 |
// set up the option starting with the first version |
| 1026 |
add_option( Participants_Db::$db_version_option, $current_version ); |
| 1027 |
|
| 1028 |
// check to see if the update to 0.2 has been performed |
| 1029 |
$column_test = $wpdb->get_results( 'SHOW COLUMNS FROM ' . Participants_Db::$fields_table . ' LIKE "column"' ); |
| 1030 |
if ( empty( $column_test ) ) { |
| 1031 |
$current_version = '0.2'; |
| 1032 |
} |
| 1033 |
|
| 1034 |
// check for version 0.4 |
| 1035 |
$column_test = $wpdb->get_results( 'SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "' . $wpdb->dbname . '" AND table_name = "' . Participants_Db::$fields_table . '" AND COLUMN_NAME = "values"' ); |
| 1036 |
if ( is_object(current($column_test)) && strtolower( current($column_test)->DATA_TYPE ) == 'longtext' ) { |
| 1037 |
// we're skipping update 3 because all it does is insert default values |
| 1038 |
$current_version = '0.4'; |
| 1039 |
} |
| 1040 |
|
| 1041 |
// check for version 0.51 |
| 1042 |
$column_test = $wpdb->get_results( 'SHOW COLUMNS FROM ' . Participants_Db::$fields_table . ' LIKE "import"' ); |
| 1043 |
if ( empty( $column_test ) ) { |
| 1044 |
$current_version = '0.51'; |
| 1045 |
} |
| 1046 |
// check for version 0.55 |
| 1047 |
$column_test = $wpdb->get_results( 'SHOW COLUMNS FROM ' . Participants_Db::$fields_table . ' LIKE "readonly"' ); |
| 1048 |
if ( !empty( $column_test ) ) { |
| 1049 |
$current_version = '0.55'; |
| 1050 |
} |
| 1051 |
|
| 1052 |
// check for version 0.6 |
| 1053 |
$column_test = $wpdb->get_results( 'SHOW COLUMNS FROM ' . Participants_Db::$participants_table . ' LIKE "last_accessed"' ); |
| 1054 |
if ( !empty( $column_test ) ) { |
| 1055 |
$current_version = '0.6'; |
| 1056 |
} |
| 1057 |
|
| 1058 |
// check for version 0.65 |
| 1059 |
$value_test = $wpdb->get_var( 'SELECT `form_element` FROM ' . Participants_Db::$fields_table . ' WHERE `name` = "date_recorded"' ); |
| 1060 |
if ( $value_test == 'timestamp' ) { |
| 1061 |
$current_version = '0.65'; |
| 1062 |
} |
| 1063 |
|
| 1064 |
// check for version 0.7 |
| 1065 |
$column_test = $wpdb->get_results( 'SHOW COLUMNS FROM ' . Participants_Db::$groups_table . ' LIKE "admin"' ); |
| 1066 |
if ( !empty( $column_test ) ) { |
| 1067 |
$current_version = '0.7'; |
| 1068 |
} |
| 1069 |
|
| 1070 |
// check for version 0.9 |
| 1071 |
$column_test = $wpdb->get_results( 'SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = "' . Participants_Db::$fields_table . '" AND COLUMN_NAME = "name"' ); |
| 1072 |
if ( $column_test[0]->CHARACTER_MAXIMUM_LENGTH === '64' ) { |
| 1073 |
$current_version = '0.9'; |
| 1074 |
} |
| 1075 |
|
| 1076 |
// check for version 1.0 |
| 1077 |
$table_status = $wpdb->get_results( "SHOW TABLE STATUS WHERE `name` = '" . Participants_Db::$participants_table . "'" ); |
| 1078 |
if ( current( $table_status )->Collation == 'utf8_unicode_ci' ) { |
| 1079 |
$current_version = '1.0'; |
| 1080 |
} |
| 1081 |
|
| 1082 |
// check for version 1.1 |
| 1083 |
$column_test = $wpdb->get_results( 'SHOW COLUMNS FROM ' . Participants_Db::$groups_table . ' LIKE "mode"' ); |
| 1084 |
if ( !empty( $column_test ) ) { |
| 1085 |
$current_version = '1.1'; |
| 1086 |
} |
| 1087 |
|
| 1088 |
// check for version 1.2 |
| 1089 |
$column_test = $wpdb->get_results( "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" . $wpdb->dbname . "' AND TABLE_NAME = '" . Participants_Db::$fields_table . "' AND COLUMN_NAME = 'default'"); |
| 1090 |
|
| 1091 |
if ( strtolower( current($column_test)->DATA_TYPE ) === 'text' ) { |
| 1092 |
$current_version = '1.2'; |
| 1093 |
} |
| 1094 |
|
| 1095 |
update_option( Participants_Db::$db_version_option, $current_version ); |
| 1096 |
|
| 1097 |
return; |
| 1098 |
} |
| 1099 |
|
| 1100 |
/** |
| 1101 |
* fills the new group mode column with values based on the old data |
| 1102 |
* |
| 1103 |
* @global wpdb $wpdb |
| 1104 |
*/ |
| 1105 |
private static function set_mode_column_values() |
| 1106 |
{ |
| 1107 |
global $wpdb; |
| 1108 |
|
| 1109 |
$group_data_list = $wpdb->get_results( 'SELECT * FROM ' . Participants_Db::$groups_table ); |
| 1110 |
|
| 1111 |
if ( !is_array( $group_data_list ) ) { |
| 1112 |
error_log( __METHOD__ . ' groups data not obtained' ); |
| 1113 |
return; |
| 1114 |
} |
| 1115 |
|
| 1116 |
$update = array(); |
| 1117 |
foreach ( $group_data_list as $group ) { |
| 1118 |
if ( !empty( $group->mode ) ) { |
| 1119 |
continue 1; |
| 1120 |
} |
| 1121 |
switch ( true ) { |
| 1122 |
case $group->display == '0' && $group->admin == '1': |
| 1123 |
case $group->name === 'admin': |
| 1124 |
case $group->name === 'internal': |
| 1125 |
$mode = 'admin'; |
| 1126 |
break; |
| 1127 |
case $group->display == '1' && $group->admin == '1': |
| 1128 |
case $group->display == '1' && $group->admin == '0': |
| 1129 |
case $group->display == '0' && $group->admin == '0': |
| 1130 |
$mode = 'public'; |
| 1131 |
break; |
| 1132 |
} |
| 1133 |
$wpdb->update( Participants_Db::$groups_table, array('mode' => $mode), array('id' => $group->id) ); |
| 1134 |
} |
| 1135 |
} |
| 1136 |
|
| 1137 |
/** |
| 1138 |
* updates the field definitions to use the new columns as of db version 1.1 |
| 1139 |
* |
| 1140 |
* value set field now use the "options" column to hold the options values, other |
| 1141 |
* fields use only the attributes column |
| 1142 |
* |
| 1143 |
* @global wpdb $wpdb |
| 1144 |
*/ |
| 1145 |
public static function update_field_def_values() |
| 1146 |
{ |
| 1147 |
global $wpdb; |
| 1148 |
$field_def_list = $wpdb->get_results( 'SELECT v.* |
| 1149 |
FROM ' . Participants_Db::$fields_table . ' v |
| 1150 |
ORDER BY v.order' ); |
| 1151 |
|
| 1152 |
foreach ( $field_def_list as $field_def ) { |
| 1153 |
if ( Participants_Db::$fields[$field_def->name]->is_value_set() ) { |
| 1154 |
$update = array( 'values' => NULL ); |
| 1155 |
if ( empty( $field_def->options ) ) { |
| 1156 |
$update['options'] = $field_def->values; |
| 1157 |
} |
| 1158 |
$wpdb->update( Participants_Db::$fields_table, |
| 1159 |
$update, |
| 1160 |
array( |
| 1161 |
'id' => $field_def->id |
| 1162 |
) ); |
| 1163 |
} elseif ( empty( $field_def->attributes ) && ! empty( $field_def->values ) ) { |
| 1164 |
$wpdb->update( Participants_Db::$fields_table, |
| 1165 |
array( |
| 1166 |
'attributes' => $field_def->values, |
| 1167 |
'values' => NULL |
| 1168 |
), |
| 1169 |
array( |
| 1170 |
'id' => $field_def->id |
| 1171 |
) ); |
| 1172 |
} |
| 1173 |
} |
| 1174 |
} |
| 1175 |
|
| 1176 |
/** |
| 1177 |
* finds and removes orphan columns in the main table |
| 1178 |
* |
| 1179 |
* DANGER: this will delete data |
| 1180 |
* |
| 1181 |
*/ |
| 1182 |
public static function delete_orphan_columns() |
| 1183 |
{ |
| 1184 |
global $wpdb; |
| 1185 |
|
| 1186 |
$exceptions = array( |
| 1187 |
'record_slug', |
| 1188 |
); |
| 1189 |
|
| 1190 |
$field_list = $wpdb->get_col('SELECT f.name FROM ' . Participants_Db::$fields_table . ' f' ); |
| 1191 |
|
| 1192 |
// this query does not include fields installed by plugins |
| 1193 |
$column_query = 'SHOW COLUMNS FROM ' . Participants_Db::$participants_table . ' WHERE field NOT IN ("' . implode( '","', $field_list ) . '") AND field NOT LIKE "pdb%" AND field NOT IN ("' . implode( '","', $exceptions ) . '")'; |
| 1194 |
|
| 1195 |
$columns = $wpdb->get_results( $column_query ); |
| 1196 |
|
| 1197 |
// build the list of orphans |
| 1198 |
$delete_list = array(); |
| 1199 |
foreach( $columns as $column ) { |
| 1200 |
$delete_list[] = 'DROP COLUMN IF EXISTS `' . $column->Field . '`'; |
| 1201 |
} |
| 1202 |
|
| 1203 |
$drop_query = 'ALTER TABLE ' . Participants_Db::$participants_table . " \n " . implode( ", \n" , $delete_list ) . ';'; |
| 1204 |
|
| 1205 |
$wpdb->query($drop_query); |
| 1206 |
} |
| 1207 |
|
| 1208 |
/** |
| 1209 |
* defines arrays containing a starting set of fields, groups, etc. |
| 1210 |
* |
| 1211 |
* @return void |
| 1212 |
*/ |
| 1213 |
private static function _define_init_arrays() |
| 1214 |
{ |
| 1215 |
|
| 1216 |
// define the default field groups |
| 1217 |
self::$field_groups = array( |
| 1218 |
'main' => __( 'Participant Info', 'participants-database' ), |
| 1219 |
'personal' => __( 'Personal Info', 'participants-database' ), |
| 1220 |
'admin' => __( 'Administrative Info', 'participants-database' ), |
| 1221 |
'internal' => __( 'Record Info', 'participants-database' ), |
| 1222 |
); |
| 1223 |
|
| 1224 |
// fields for keeping track of records; not manually edited, but they can be displayed |
| 1225 |
self::$internal_fields = array( |
| 1226 |
'id' => array( |
| 1227 |
'title' => 'Record ID', |
| 1228 |
'signup' => 1, |
| 1229 |
'form_element' => 'text-line', |
| 1230 |
'CSV' => 1, |
| 1231 |
'readonly' => 1, |
| 1232 |
), |
| 1233 |
'private_id' => array( |
| 1234 |
'title' => 'Private ID', |
| 1235 |
'signup' => 1, |
| 1236 |
'form_element' => 'text', |
| 1237 |
'admin_column' => 90, |
| 1238 |
'default' => '', |
| 1239 |
'readonly' => 1, |
| 1240 |
), |
| 1241 |
'date_recorded' => array( |
| 1242 |
'title' => 'Date Recorded', |
| 1243 |
'form_element' => 'timestamp', |
| 1244 |
'admin_column' => 100, |
| 1245 |
'sortable' => 1, |
| 1246 |
'readonly' => 1, |
| 1247 |
), |
| 1248 |
'date_updated' => array( |
| 1249 |
'title' => 'Date Updated', |
| 1250 |
'form_element' => 'timestamp', |
| 1251 |
'sortable' => 1, |
| 1252 |
'readonly' => 1, |
| 1253 |
), |
| 1254 |
'last_accessed' => array( |
| 1255 |
'title' => 'Last Accessed', |
| 1256 |
'form_element' => 'timestamp', |
| 1257 |
'sortable' => 1, |
| 1258 |
'readonly' => 1, |
| 1259 |
), |
| 1260 |
); |
| 1261 |
|
| 1262 |
|
| 1263 |
/* |
| 1264 |
* these are some fields just to get things started |
| 1265 |
* in the released plugin, these will be defined by the user |
| 1266 |
* |
| 1267 |
* the key is the id slug of the field |
| 1268 |
* the fields in the array are: |
| 1269 |
* title - a display title |
| 1270 |
* help_text - help text to appear on the form |
| 1271 |
* default - a default value |
| 1272 |
* sortable - a listing can be sorted by this value if set |
| 1273 |
* column - column in the list view and order (missing or 0 for not used) |
| 1274 |
* persistent - is the field persistent from one entry to the next (for |
| 1275 |
* convenience while entering multiple records) |
| 1276 |
* CSV - is the field one to be imported or exported |
| 1277 |
* validation - if the field needs to be validated, use this regex or just |
| 1278 |
* yes for a value that must be filled in |
| 1279 |
* form_element - the element to use in the form--defaults to |
| 1280 |
* input, Could be text-line (input), text-field (textarea), |
| 1281 |
* radio, dropdown (option) or checkbox, also select-other |
| 1282 |
* multi-checkbox and asmselect.(http: *www.ryancramer.com/journal/entries/select_multiple/) |
| 1283 |
* The mysql data type is determined by this. |
| 1284 |
* values array title=>value pairs for checkboxes, radio buttons, dropdowns |
| 1285 |
* for checkbox, first item is visible option, if value |
| 1286 |
* matches 'default' value then it defaults checked |
| 1287 |
*/ |
| 1288 |
self::$main_fields = array( |
| 1289 |
'first_name' => array( |
| 1290 |
'title' => 'First Name', |
| 1291 |
'form_element' => 'text-line', |
| 1292 |
'validation' => 'yes', |
| 1293 |
'sortable' => 1, |
| 1294 |
'admin_column' => 2, |
| 1295 |
'display_column' => 1, |
| 1296 |
'signup' => 1, |
| 1297 |
'CSV' => 1, |
| 1298 |
), |
| 1299 |
'last_name' => array( |
| 1300 |
'title' => 'Last Name', |
| 1301 |
'form_element' => 'text-line', |
| 1302 |
'validation' => 'yes', |
| 1303 |
'sortable' => 1, |
| 1304 |
'admin_column' => 3, |
| 1305 |
'display_column' => 2, |
| 1306 |
'signup' => 1, |
| 1307 |
'CSV' => 1, |
| 1308 |
), |
| 1309 |
'address' => array( |
| 1310 |
'title' => 'Address', |
| 1311 |
'form_element' => 'text-line', |
| 1312 |
'CSV' => 1, |
| 1313 |
), |
| 1314 |
'city' => array( |
| 1315 |
'title' => 'City', |
| 1316 |
'sortable' => 1, |
| 1317 |
'persistent' => 1, |
| 1318 |
'form_element' => 'text-line', |
| 1319 |
'admin_column' => 0, |
| 1320 |
'display_column' => 3, |
| 1321 |
'CSV' => 1, |
| 1322 |
), |
| 1323 |
'state' => array( |
| 1324 |
'title' => 'State', |
| 1325 |
'sortable' => 1, |
| 1326 |
'persistent' => 1, |
| 1327 |
'form_element' => 'text-line', |
| 1328 |
'display_column' => 4, |
| 1329 |
'CSV' => 1, |
| 1330 |
), |
| 1331 |
'country' => array( |
| 1332 |
'title' => 'Country', |
| 1333 |
'sortable' => 1, |
| 1334 |
'persistent' => 1, |
| 1335 |
'form_element' => 'text-line', |
| 1336 |
'CSV' => 1, |
| 1337 |
), |
| 1338 |
'zip' => array( |
| 1339 |
'title' => 'Zip Code', |
| 1340 |
'sortable' => 1, |
| 1341 |
'persistent' => 1, |
| 1342 |
'form_element' => 'text-line', |
| 1343 |
'CSV' => 1, |
| 1344 |
), |
| 1345 |
'phone' => array( |
| 1346 |
'title' => 'Phone', |
| 1347 |
'help_text' => 'Your primary contact number', |
| 1348 |
'form_element' => 'text-line', |
| 1349 |
'CSV' => 1, |
| 1350 |
), |
| 1351 |
'email' => array( |
| 1352 |
'title' => 'Email', |
| 1353 |
'form_element' => 'text-line', |
| 1354 |
'admin_column' => 4, |
| 1355 |
'validation' => 'email-regex', |
| 1356 |
'signup' => 1, |
| 1357 |
'CSV' => 1, |
| 1358 |
), |
| 1359 |
'mailing_list' => array( |
| 1360 |
'title' => 'Mailing List', |
| 1361 |
'help_text' => 'do you want to receive our newsletter and occasional announcements?', |
| 1362 |
'sortable' => 1, |
| 1363 |
'signup' => 1, |
| 1364 |
'form_element' => 'checkbox', |
| 1365 |
'CSV' => 1, |
| 1366 |
'default' => 'Yes', |
| 1367 |
'options' => array( |
| 1368 |
'Yes', |
| 1369 |
'No', |
| 1370 |
), |
| 1371 |
), |
| 1372 |
); |
| 1373 |
self::$personal_fields = array( |
| 1374 |
'photo' => array( |
| 1375 |
'title' => 'Photo', |
| 1376 |
'help_text' => 'Upload a photo of yourself. 300 pixels maximum width or height.', |
| 1377 |
'form_element' => 'image-upload', |
| 1378 |
), |
| 1379 |
'website' => array( |
| 1380 |
'title' => 'Website, Blog or Social Media Link', |
| 1381 |
'form_element' => 'link', |
| 1382 |
'help_text' => 'Put the URL in the left box and the link text that will be shown on the right', |
| 1383 |
), |
| 1384 |
'interests' => array( |
| 1385 |
'title' => 'Interests or Hobbies', |
| 1386 |
'form_element' => 'multi-select-other', |
| 1387 |
'options' => array( |
| 1388 |
'Sports' => 'sports', |
| 1389 |
'Photography' => 'photography', |
| 1390 |
'Art/Crafts' => 'crafts', |
| 1391 |
'Outdoors' => 'outdoors', |
| 1392 |
'Yoga' => 'yoga', |
| 1393 |
'Music' => 'music', |
| 1394 |
'Cuisine' => 'cuisine', |
| 1395 |
), |
| 1396 |
), |
| 1397 |
); |
| 1398 |
self::$admin_fields = array( |
| 1399 |
'approved' => array( |
| 1400 |
'title' => 'Approved', |
| 1401 |
'sortable' => 1, |
| 1402 |
'form_element' => 'checkbox', |
| 1403 |
'default' => 'no', |
| 1404 |
'options' => array( |
| 1405 |
'yes', |
| 1406 |
'no', |
| 1407 |
), |
| 1408 |
), |
| 1409 |
); |
| 1410 |
} |
| 1411 |
|
| 1412 |
} |
| 1413 |
|
