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