4 * Upgrade YOURLS and DB schema
7 function yourls_upgrade( $step, $oldver, $newver, $oldsql, $newsql ) {
8 // special case for 1.3: the upgrade is a multi step procedure
10 yourls_upgrade_to_14( $step );
13 // other upgrades which are done in a single pass
19 yourls_upgrade_to_141();
22 yourls_upgrade_to_143();
25 yourls_upgrade_to_15();
30 yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=3" ) );
35 // Update options to reflect latest version
36 yourls_update_option( 'version', YOURLS_VERSION );
37 yourls_update_option( 'db_version', YOURLS_DB_VERSION );
46 function yourls_upgrade_482() {
47 // Change URL title charset to UTF8
49 $table_url = YOURLS_DB_TABLE_URL;
50 $sql = "ALTER TABLE `$table_url` CHANGE `title` `title` TEXT CHARACTER SET utf8;";
52 echo "<p>Updating table structure. Please wait...</p>";
55 /************************** 1.4.3 -> 1.5 **************************/
58 * Main func for upgrade from 1.4.3 to 1.5
61 function yourls_upgrade_to_15( ) {
62 // Create empty 'active_plugins' entry in the option if needed
63 if( yourls_get_option( 'active_plugins' ) === false )
64 yourls_add_option( 'active_plugins', array() );
65 echo "<p>Enabling the plugin API. Please wait...</p>";
67 // Alter URL table to store titles
69 $table_url = YOURLS_DB_TABLE_URL;
70 $sql = "ALTER TABLE `$table_url` ADD `title` TEXT AFTER `url`;";
72 echo "<p>Updating table structure. Please wait...</p>";
75 yourls_create_htaccess();
76 echo "<p>Updating .htaccess file. Please wait...</p>";
79 /************************** 1.4.1 -> 1.4.3 **************************/
82 * Main func for upgrade from 1.4.1 to 1.4.3
85 function yourls_upgrade_to_143( ) {
86 // Check if we have 'keyword' (borked install) or 'shorturl' (ok install)
88 $table_log = YOURLS_DB_TABLE_LOG;
89 $sql = "SHOW COLUMNS FROM `$table_log`";
90 $cols = $ydb->get_results( $sql );
91 if ( $cols[2]->Field == 'keyword' ) {
92 $sql = "ALTER TABLE `$table_log` CHANGE `keyword` `shorturl` VARCHAR( 200 ) BINARY;";
95 echo "<p>Structure of existing tables updated. Please wait...</p>";
98 /************************** 1.4 -> 1.4.1 **************************/
101 * Main func for upgrade from 1.4 to 1.4.1
104 function yourls_upgrade_to_141( ) {
105 // Kill old cookies from 1.3 and prior
106 setcookie('yourls_username', null, time() - 3600 );
107 setcookie('yourls_password', null, time() - 3600 );
109 yourls_alter_url_table_to_141();
110 // recreate the htaccess file if needed
111 yourls_create_htaccess();
115 * Alter table URL to 1.4.1
118 function yourls_alter_url_table_to_141() {
120 $table_url = YOURLS_DB_TABLE_URL;
121 $alter = "ALTER TABLE `$table_url` CHANGE `keyword` `keyword` VARCHAR( 200 ) BINARY, CHANGE `url` `url` TEXT BINARY ";
122 $ydb->query( $alter );
123 echo "<p>Structure of existing tables updated. Please wait...</p>";
127 /************************** 1.3 -> 1.4 **************************/
130 * Main func for upgrade from 1.3-RC1 to 1.4
133 function yourls_upgrade_to_14( $step ) {
137 // create table log & table options
138 // update table url structure
140 yourls_create_tables_for_14(); // no value returned, assuming it went OK
141 yourls_alter_url_table_to_14(); // no value returned, assuming it went OK
142 $clean = yourls_clean_htaccess_for_14(); // returns bool
143 $create = yourls_create_htaccess(); // returns bool
145 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>.";
146 yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=2&oldver=1.3&newver=1.4&oldsql=100&newsql=200" ), $create );
150 // convert each link in table url
151 yourls_update_table_to_14();
155 // update table url structure part 2: recreate indexes
156 yourls_alter_url_table_to_14_part_two();
157 // update version & db_version & next_id in the option table
158 // attempt to drop YOURLS_DB_TABLE_NEXTDEC
159 yourls_update_options_to_14();
160 // Now upgrade to 1.4.1
161 yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=1&oldver=1.4&newver=1.4.1&oldsql=200&newsql=210" ) );
167 * Update options to reflect new version
170 function yourls_update_options_to_14() {
171 yourls_update_option( 'version', '1.4' );
172 yourls_update_option( 'db_version', '200' );
174 if( defined('YOURLS_DB_TABLE_NEXTDEC') ) {
176 $table = YOURLS_DB_TABLE_NEXTDEC;
177 $next_id = $ydb->get_var("SELECT `next_id` FROM `$table`");
178 yourls_update_option( 'next_id', $next_id );
179 @$ydb->query( "DROP TABLE `$table`" );
181 yourls_update_option( 'next_id', 1 ); // In case someone mistakenly deleted the next_id constant or table too early
186 * Create new tables for YOURLS 1.4: options & log
189 function yourls_create_tables_for_14() {
194 $queries[YOURLS_DB_TABLE_OPTIONS] =
195 'CREATE TABLE IF NOT EXISTS `'.YOURLS_DB_TABLE_OPTIONS.'` ('.
196 '`option_id` int(11) unsigned NOT NULL auto_increment,'.
197 '`option_name` varchar(64) NOT NULL default "",'.
198 '`option_value` longtext NOT NULL,'.
199 'PRIMARY KEY (`option_id`,`option_name`),'.
200 'KEY `option_name` (`option_name`)'.
203 $queries[YOURLS_DB_TABLE_LOG] =
204 'CREATE TABLE IF NOT EXISTS `'.YOURLS_DB_TABLE_LOG.'` ('.
205 '`click_id` int(11) NOT NULL auto_increment,'.
206 '`click_time` datetime NOT NULL,'.
207 '`shorturl` varchar(200) NOT NULL,'.
208 '`referrer` varchar(200) NOT NULL,'.
209 '`user_agent` varchar(255) NOT NULL,'.
210 '`ip_address` varchar(41) NOT NULL,'.
211 '`country_code` char(2) NOT NULL,'.
212 'PRIMARY KEY (`click_id`),'.
213 'KEY `shorturl` (`shorturl`)'.
216 foreach( $queries as $query ) {
217 $ydb->query( $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)
220 echo "<p>New tables created. Please wait...</p>";
225 * Alter table structure, part 1 (change schema, drop index)
228 function yourls_alter_url_table_to_14() {
230 $table = YOURLS_DB_TABLE_URL;
234 $alters[] = "ALTER TABLE `$table` CHANGE `id` `keyword` VARCHAR( 200 ) NOT NULL";
235 $alters[] = "ALTER TABLE `$table` CHANGE `url` `url` TEXT NOT NULL";
236 $alters[] = "ALTER TABLE `$table` DROP PRIMARY KEY";
238 foreach ( $alters as $query ) {
239 $ydb->query( $query );
242 echo "<p>Structure of existing tables updated. Please wait...</p>";
246 * Alter table structure, part 2 (recreate indexes after the table is up to date)
249 function yourls_alter_url_table_to_14_part_two() {
251 $table = YOURLS_DB_TABLE_URL;
254 $alters[] = "ALTER TABLE `$table` ADD PRIMARY KEY ( `keyword` )";
255 $alters[] = "ALTER TABLE `$table` ADD INDEX ( `ip` )";
256 $alters[] = "ALTER TABLE `$table` ADD INDEX ( `timestamp` )";
258 foreach ( $alters as $query ) {
259 $ydb->query( $query );
262 echo "<p>New table index created</p>";
266 * Convert each link from 1.3 (id) to 1.4 (keyword) structure
269 function yourls_update_table_to_14() {
271 $table = YOURLS_DB_TABLE_URL;
273 // Modify each link to reflect new structure
275 $from = isset($_GET['from']) ? intval( $_GET['from'] ) : 0 ;
276 $total = yourls_get_db_stats();
277 $total = $total['total_links'];
279 $sql = "SELECT `keyword`,`url` FROM `$table` WHERE 1=1 ORDER BY `url` ASC LIMIT $from, $chunk ;";
281 $rows = $ydb->get_results($sql);
285 foreach( $rows as $row ) {
286 $keyword = $row->keyword;
288 $newkeyword = yourls_int2string( $keyword );
289 $ydb->query("UPDATE `$table` SET `keyword` = '$newkeyword' WHERE `url` = '$url';");
290 if( $ydb->result === true ) {
293 echo "<p>Huho... Could not update rown with url='$url', from keyword '$keyword' to keyword '$newkeyword'</p>"; // Find what went wrong :/
298 // All done for this chunk of queries, did it all go as expected?
300 if( $count != $queries ) {
302 $num = $count - $queries;
303 echo "<p>$num error(s) occured while updating the URL table :(</p>";
306 if ( $count == $chunk ) {
307 // there are probably other rows to convert
308 $from = $from + $chunk;
309 $remain = $total - $from;
310 echo "<p>Converted $chunk database rows ($remain remaining). Continuing... Please do not close this window until it's finished!</p>";
311 yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=2&oldver=1.3&newver=1.4&oldsql=100&newsql=200&from=$from" ), $success );
314 echo '<p>All rows converted! Please wait...</p>';
315 yourls_redirect_javascript( yourls_admin_url( "upgrade.php?step=3&oldver=1.3&newver=1.4&oldsql=100&newsql=200" ), $success );
321 * Clean .htaccess as it existed before 1.4. Returns boolean
324 function yourls_clean_htaccess_for_14() {
325 $filename = YOURLS_ABSPATH.'/.htaccess';
328 if( is_writeable( $filename ) ) {
329 $contents = implode( '', file( $filename ) );
330 // remove "ShortURL" block
331 $contents = preg_replace( '/# BEGIN ShortURL.*# END ShortURL/s', '', $contents );
332 // comment out deprecated RewriteRule
333 $find = 'RewriteRule .* - [E=REMOTE_USER:%{HTTP:Authorization},L]';
334 $replace = "# You can safely remove this 5 lines block -- it's no longer used in YOURLS\n".
336 $contents = str_replace( $find, $replace, $contents );
338 // Write cleaned file
339 $f = fopen( $filename, 'w' );
340 fwrite( $f, $contents );