[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /**
   4   * Upgrade YOURLS and DB schema
   5   *
   6   * Note to devs : prefer update function names using the SQL version, eg yourls_update_to_506(),
   7   * rather than using the YOURLS version number, eg yourls_update_to_18(). This is to allow having
   8   * multiple SQL update during the dev cycle of the same YOURLS version
   9   *
  10   * @param string|int $step
  11   * @param string $oldver
  12   * @param string $newver
  13   * @param string|int $oldsql
  14   * @param string|int $newsql
  15   * @return void
  16   */
  17  function yourls_upgrade($step, $oldver, $newver, $oldsql, $newsql ) {
  18  
  19      /**
  20       *  Sanitize input. Two notes :
  21       *  - they should already be sanitized in the caller, eg admin/upgrade.php
  22       *    (but hey, let's make sure)
  23       *  - some vars may not be used at the moment
  24       *    (and this is ok, they are here in case a future upgrade procedure needs them)
  25       */
  26      $step   = intval($step);
  27      $oldsql = intval($oldsql);
  28      $newsql = intval($newsql);
  29      $oldver = yourls_sanitize_version($oldver);
  30      $newver = yourls_sanitize_version($newver);
  31  
  32      yourls_maintenance_mode(true);
  33  
  34      // special case for 1.3: the upgrade is a multi step procedure
  35      if( $oldsql == 100 ) {
  36          yourls_upgrade_to_14( $step );
  37      }
  38  
  39      // other upgrades which are done in a single pass
  40      switch( $step ) {
  41  
  42      case 1:
  43      case 2:
  44          if( $oldsql < 210 )
  45              yourls_upgrade_to_141();
  46  
  47          if( $oldsql < 220 )
  48              yourls_upgrade_to_143();
  49  
  50          if( $oldsql < 250 )
  51              yourls_upgrade_to_15();
  52  
  53          if( $oldsql < 482 )
  54              yourls_upgrade_482(); // that was somewhere 1.5 and 1.5.1 ...
  55  
  56          if( $oldsql < 506 ) {
  57              /**
  58               * 505 was the botched update with the wrong collation, see #2766
  59               * 506 is the updated collation.
  60               * We want :
  61               *      people on 505 to update to 506
  62               *      people before 505 to update to the FIXED complete upgrade
  63               */
  64              if( $oldsql == 505 ) {
  65                  yourls_upgrade_505_to_506();
  66              } else {
  67                  yourls_upgrade_to_506();
  68              }
  69          }
  70  
  71          if( $oldsql < 507 ) {
  72              yourls_upgrade_to_507();
  73          }
  74  
  75          yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=3" ) );
  76  
  77          break;
  78  
  79      case 3:
  80          // Update options to reflect latest version
  81          yourls_update_option( 'version', YOURLS_VERSION );
  82          yourls_update_option( 'db_version', YOURLS_DB_VERSION );
  83          yourls_maintenance_mode(false);
  84          break;
  85      }
  86  }
  87  
  88  /************************** 1.6 -> 1.8 **************************/
  89  
  90  /**
  91   * Add sort index for fast URL lookups.
  92   * DB version 507.
  93   */
  94  function yourls_upgrade_to_507() {
  95      echo "<p>Adding index for url column. Please wait...</p>";
  96  
  97      $table = YOURLS_DB_TABLE_URL;
  98  
  99      $query = sprintf("ALTER TABLE `%s` ADD INDEX `url_idx` (`url`(50));", $table);
 100  
 101      try {
 102          yourls_get_db()->perform($query);
 103      } catch (\Exception $e) {
 104          echo "<p class='error'>Unable to update the DB.</p>";
 105          echo "<p>Could not index urls. You will have to fix things manually :(. The error was
 106          <pre>";
 107          echo $e->getMessage();
 108          echo "\n</pre>";
 109          die();
 110      }
 111  
 112      echo "<p class='success'>OK!</p>";
 113  }
 114  
 115  /**
 116   * Update to 506, just the fix for people who had updated to master on 1.7.10
 117   *
 118   */
 119  function yourls_upgrade_505_to_506() {
 120      echo "<p>Updating DB. Please wait...</p>";
 121      // Fix collation which was wrongly set at first to utf8mb4_unicode_ci
 122      $query = sprintf('ALTER TABLE `%s` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;', YOURLS_DB_TABLE_URL);
 123  
 124      try {
 125          yourls_get_db()->perform($query);
 126      } catch (\Exception $e) {
 127          echo "<p class='error'>Unable to update the DB.</p>";
 128          echo "<p>Could not change collation. You will have to fix things manually :(. The error was
 129          <pre>";
 130          echo $e->getMessage();
 131          echo "\n</pre>";
 132          die();
 133      }
 134  
 135      echo "<p class='success'>OK!</p>";
 136  }
 137  
 138  /**
 139   * Update to 506
 140   *
 141   */
 142  function yourls_upgrade_to_506() {
 143      $ydb = yourls_get_db();
 144      $error_msg = [];
 145  
 146      echo "<p>Updating DB. Please wait...</p>";
 147  
 148      $queries = array(
 149          'database charset'     => sprintf('ALTER DATABASE `%s` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;', YOURLS_DB_NAME),
 150          'options charset'      => sprintf('ALTER TABLE `%s` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;', YOURLS_DB_TABLE_OPTIONS),
 151          'short URL varchar'    => sprintf("ALTER TABLE `%s` CHANGE `keyword` `keyword` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';", YOURLS_DB_TABLE_URL),
 152          'short URL type url'   => sprintf("ALTER TABLE `%s` CHANGE `url` `url` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;", YOURLS_DB_TABLE_URL),
 153          'short URL type title' => sprintf("ALTER TABLE `%s` CHANGE `title` `title` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci", YOURLS_DB_TABLE_URL),
 154          'short URL charset'    => sprintf('ALTER TABLE `%s` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;', YOURLS_DB_TABLE_URL),
 155      );
 156  
 157      foreach($queries as $what => $query) {
 158          try {
 159              $ydb->perform($query);
 160          } catch (\Exception $e) {
 161              $error_msg[] = $e->getMessage();
 162          }
 163      }
 164  
 165      if( $error_msg ) {
 166          echo "<p class='error'>Unable to update the DB.</p>";
 167          echo "<p>You will have to manually fix things, sorry for the inconvenience :(</p>";
 168          echo "<p>The errors were:
 169          <pre>";
 170          foreach( $error_msg as $error ) {
 171              echo "$error\n";
 172          }
 173          echo "</pre>";
 174          die();
 175      }
 176  
 177      echo "<p class='success'>OK!</p>";
 178  }
 179  
 180  /************************** 1.5 -> 1.6 **************************/
 181  
 182  /**
 183   * Upgrade r482
 184   *
 185   */
 186  function yourls_upgrade_482() {
 187      // Change URL title charset to UTF8
 188      $table_url = YOURLS_DB_TABLE_URL;
 189      $sql = "ALTER TABLE `$table_url` CHANGE `title` `title` TEXT CHARACTER SET utf8;";
 190      yourls_get_db()->perform( $sql );
 191      echo "<p>Updating table structure. Please wait...</p>";
 192  }
 193  
 194  /************************** 1.4.3 -> 1.5 **************************/
 195  
 196  /**
 197   * Main func for upgrade from 1.4.3 to 1.5
 198   *
 199   */
 200  function yourls_upgrade_to_15( ) {
 201      // Create empty 'active_plugins' entry in the option if needed
 202      if( yourls_get_option( 'active_plugins' ) === false )
 203          yourls_add_option( 'active_plugins', array() );
 204      echo "<p>Enabling the plugin API. Please wait...</p>";
 205  
 206      // Alter URL table to store titles
 207      $table_url = YOURLS_DB_TABLE_URL;
 208      $sql = "ALTER TABLE `$table_url` ADD `title` TEXT AFTER `url`;";
 209      yourls_get_db()->perform( $sql );
 210      echo "<p>Updating table structure. Please wait...</p>";
 211  
 212      // Update .htaccess
 213      yourls_create_htaccess();
 214      echo "<p>Updating .htaccess file. Please wait...</p>";
 215  }
 216  
 217  /************************** 1.4.1 -> 1.4.3 **************************/
 218  
 219  /**
 220   * Main func for upgrade from 1.4.1 to 1.4.3
 221   *
 222   */
 223  function yourls_upgrade_to_143( ) {
 224      // Check if we have 'keyword' (borked install) or 'shorturl' (ok install)
 225      $ydb = yourls_get_db();
 226      $table_log = YOURLS_DB_TABLE_LOG;
 227      $sql = "SHOW COLUMNS FROM `$table_log`";
 228      $cols = $ydb->fetchObjects( $sql );
 229      if ( $cols[2]->Field == 'keyword' ) {
 230          $sql = "ALTER TABLE `$table_log` CHANGE `keyword` `shorturl` VARCHAR( 200 ) BINARY;";
 231          $ydb->query( $sql );
 232      }
 233      echo "<p>Structure of existing tables updated. Please wait...</p>";
 234  }
 235  
 236  /************************** 1.4 -> 1.4.1 **************************/
 237  
 238  /**
 239   * Main func for upgrade from 1.4 to 1.4.1
 240   *
 241   */
 242  function yourls_upgrade_to_141( ) {
 243      // Kill old cookies from 1.3 and prior
 244      setcookie('yourls_username', '', time() - 3600 );
 245      setcookie('yourls_password', '', time() - 3600 );
 246      // alter table URL
 247      yourls_alter_url_table_to_141();
 248      // recreate the htaccess file if needed
 249      yourls_create_htaccess();
 250  }
 251  
 252  /**
 253   * Alter table URL to 1.4.1
 254   *
 255   */
 256  function yourls_alter_url_table_to_141() {
 257      $table_url = YOURLS_DB_TABLE_URL;
 258      $alter = "ALTER TABLE `$table_url` CHANGE `keyword` `keyword` VARCHAR( 200 ) BINARY, CHANGE `url` `url` TEXT BINARY ";
 259      yourls_get_db()->perform( $alter );
 260      echo "<p>Structure of existing tables updated. Please wait...</p>";
 261  }
 262  
 263  
 264  /************************** 1.3 -> 1.4 **************************/
 265  
 266  /**
 267   * Main func for upgrade from 1.3-RC1 to 1.4
 268   *
 269   */
 270  function yourls_upgrade_to_14( $step ) {
 271  
 272      switch( $step ) {
 273      case 1:
 274          // create table log & table options
 275          // update table url structure
 276          // update .htaccess
 277          yourls_create_tables_for_14(); // no value returned, assuming it went OK
 278          yourls_alter_url_table_to_14(); // no value returned, assuming it went OK
 279          $clean = yourls_clean_htaccess_for_14(); // returns bool
 280          $create = yourls_create_htaccess(); // returns bool
 281          if ( !$create )
 282              echo "<p class='warning'>Please create your <tt>.htaccess</tt> file (I could not do it for you). Please refer to <a href='http://yourls.org/htaccess'>http://yourls.org/htaccess</a>.";
 283          yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=2&oldver=1.3&newver=1.4&oldsql=100&newsql=200" ), $create );
 284          break;
 285  
 286      case 2:
 287          // convert each link in table url
 288          yourls_update_table_to_14();
 289          break;
 290  
 291      case 3:
 292          // update table url structure part 2: recreate indexes
 293          yourls_alter_url_table_to_14_part_two();
 294          // update version & db_version & next_id in the option table
 295          // attempt to drop YOURLS_DB_TABLE_NEXTDEC
 296          yourls_update_options_to_14();
 297          // Now upgrade to 1.4.1
 298          yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=1&oldver=1.4&newver=1.4.1&oldsql=200&newsql=210" ) );
 299          break;
 300      }
 301  }
 302  
 303  /**
 304   * Update options to reflect new version
 305   *
 306   */
 307  function yourls_update_options_to_14() {
 308      yourls_update_option( 'version', '1.4' );
 309      yourls_update_option( 'db_version', '200' );
 310  
 311      if( defined('YOURLS_DB_TABLE_NEXTDEC') ) {
 312          $table = YOURLS_DB_TABLE_NEXTDEC;
 313          $next_id = yourls_get_db()->fetchValue("SELECT `next_id` FROM `$table`");
 314          yourls_update_option( 'next_id', $next_id );
 315          yourls_get_db()->perform( "DROP TABLE `$table`" );
 316      } else {
 317          yourls_update_option( 'next_id', 1 ); // In case someone mistakenly deleted the next_id constant or table too early
 318      }
 319  }
 320  
 321  /**
 322   * Create new tables for YOURLS 1.4: options & log
 323   *
 324   */
 325  function yourls_create_tables_for_14() {
 326      $ydb = yourls_get_db();
 327  
 328      $queries = array();
 329  
 330      $queries[YOURLS_DB_TABLE_OPTIONS] =
 331          'CREATE TABLE IF NOT EXISTS `'.YOURLS_DB_TABLE_OPTIONS.'` ('.
 332          '`option_id` int(11) unsigned NOT NULL auto_increment,'.
 333          '`option_name` varchar(64) NOT NULL default "",'.
 334          '`option_value` longtext NOT NULL,'.
 335          'PRIMARY KEY (`option_id`,`option_name`),'.
 336          'KEY `option_name` (`option_name`)'.
 337          ');';
 338  
 339      $queries[YOURLS_DB_TABLE_LOG] =
 340          'CREATE TABLE IF NOT EXISTS `'.YOURLS_DB_TABLE_LOG.'` ('.
 341          '`click_id` int(11) NOT NULL auto_increment,'.
 342          '`click_time` datetime NOT NULL,'.
 343          '`shorturl` varchar(200) NOT NULL,'.
 344          '`referrer` varchar(200) NOT NULL,'.
 345          '`user_agent` varchar(255) NOT NULL,'.
 346          '`ip_address` varchar(41) NOT NULL,'.
 347          '`country_code` char(2) NOT NULL,'.
 348          'PRIMARY KEY (`click_id`),'.
 349          'KEY `shorturl` (`shorturl`)'.
 350          ');';
 351  
 352      foreach( $queries as $query ) {
 353          $ydb->perform( $query ); // There's no result to be returned to check if table was created (except making another query to check table existence, which we'll avoid)
 354      }
 355  
 356      echo "<p>New tables created. Please wait...</p>";
 357  
 358  }
 359  
 360  /**
 361   * Alter table structure, part 1 (change schema, drop index)
 362   *
 363   */
 364  function yourls_alter_url_table_to_14() {
 365      $ydb = yourls_get_db();
 366      $table = YOURLS_DB_TABLE_URL;
 367  
 368      $alters = array();
 369      $results = array();
 370      $alters[] = "ALTER TABLE `$table` CHANGE `id` `keyword` VARCHAR( 200 ) NOT NULL";
 371      $alters[] = "ALTER TABLE `$table` CHANGE `url` `url` TEXT NOT NULL";
 372      $alters[] = "ALTER TABLE `$table` DROP PRIMARY KEY";
 373  
 374      foreach ( $alters as $query ) {
 375          $ydb->perform( $query );
 376      }
 377  
 378      echo "<p>Structure of existing tables updated. Please wait...</p>";
 379  }
 380  
 381  /**
 382   * Alter table structure, part 2 (recreate indexes after the table is up to date)
 383   *
 384   */
 385  function yourls_alter_url_table_to_14_part_two() {
 386      $ydb = yourls_get_db();
 387      $table = YOURLS_DB_TABLE_URL;
 388  
 389      $alters = array();
 390      $alters[] = "ALTER TABLE `$table` ADD PRIMARY KEY ( `keyword` )";
 391      $alters[] = "ALTER TABLE `$table` ADD INDEX ( `ip` )";
 392      $alters[] = "ALTER TABLE `$table` ADD INDEX ( `timestamp` )";
 393  
 394      foreach ( $alters as $query ) {
 395          $ydb->perform( $query );
 396      }
 397  
 398      echo "<p>New table index created</p>";
 399  }
 400  
 401  /**
 402   * Convert each link from 1.3 (id) to 1.4 (keyword) structure
 403   *
 404   */
 405  function yourls_update_table_to_14() {
 406      $ydb = yourls_get_db();
 407      $table = YOURLS_DB_TABLE_URL;
 408  
 409      // Modify each link to reflect new structure
 410      $chunk = 45;
 411      $from = isset($_GET['from']) ? intval( $_GET['from'] ) : 0 ;
 412      $total = yourls_get_db_stats();
 413      $total = $total['total_links'];
 414  
 415      $sql = "SELECT `keyword`,`url` FROM `$table` WHERE 1=1 ORDER BY `url` ASC LIMIT $from, $chunk ;";
 416  
 417      $rows = $ydb->fetchObjects($sql);
 418  
 419      $count = 0;
 420      $queries = 0;
 421      foreach( $rows as $row ) {
 422          $keyword = $row->keyword;
 423          $url = $row->url;
 424          $newkeyword = yourls_int2string( $keyword );
 425          if( true === $ydb->perform("UPDATE `$table` SET `keyword` = '$newkeyword' WHERE `url` = '$url';") ) {
 426              $queries++;
 427          } else {
 428              echo "<p>Huho... Could not update rown with url='$url', from keyword '$keyword' to keyword '$newkeyword'</p>"; // Find what went wrong :/
 429          }
 430          $count++;
 431      }
 432  
 433      // All done for this chunk of queries, did it all go as expected?
 434      $success = true;
 435      if( $count != $queries ) {
 436          $success = false;
 437          $num = $count - $queries;
 438          echo "<p>$num error(s) occurred while updating the URL table :(</p>";
 439      }
 440  
 441      if ( $count == $chunk ) {
 442          // there are probably other rows to convert
 443          $from = $from + $chunk;
 444          $remain = $total - $from;
 445          echo "<p>Converted $chunk database rows ($remain remaining). Continuing... Please do not close this window until it's finished!</p>";
 446          yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=2&oldver=1.3&newver=1.4&oldsql=100&newsql=200&from=$from" ), $success );
 447      } else {
 448          // All done
 449          echo '<p>All rows converted! Please wait...</p>';
 450          yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=3&oldver=1.3&newver=1.4&oldsql=100&newsql=200" ), $success );
 451      }
 452  
 453  }
 454  
 455  /**
 456   * Clean .htaccess as it existed before 1.4. Returns boolean
 457   *
 458   */
 459  function yourls_clean_htaccess_for_14() {
 460      $filename = YOURLS_ABSPATH.'/.htaccess';
 461  
 462      $result = false;
 463      if( is_writeable( $filename ) ) {
 464          $contents = implode( '', file( $filename ) );
 465          // remove "ShortURL" block
 466          $contents = preg_replace( '/# BEGIN ShortURL.*# END ShortURL/s', '', $contents );
 467          // comment out deprecated RewriteRule
 468          $find = 'RewriteRule .* - [E=REMOTE_USER:%{HTTP:Authorization},L]';
 469          $replace = "# You can safely remove this 5 lines block -- it's no longer used in YOURLS\n".
 470                  "# $find";
 471          $contents = str_replace( $find, $replace, $contents );
 472  
 473          // Write cleaned file
 474          $f = fopen( $filename, 'w' );
 475          fwrite( $f, $contents );
 476          fclose( $f );
 477  
 478          $result = true;
 479      }
 480  
 481      return $result;
 482  }