3 // Upgrade YOURLS and DB schema
\r
4 function yourls_upgrade( $step, $oldver, $newver, $oldsql, $newsql ) {
\r
5 // As of now, there's no other upgrading possibility.
\r
6 // In the future this function may contain tests to upgrade
\r
7 // from any release to the latest
\r
8 yourls_upgrade_to_14( $step );
\r
12 // Upgrade DB Schema from 1.3-RC1 or prior to 1.4
\r
13 function yourls_upgrade_to_14( $step ) {
\r
17 // create table log & table options
\r
18 // update table url structure
\r
19 yourls_create_tables_for_14();
\r
20 yourls_alter_url_table_to_14();
\r
21 yourls_redirect_javascript( YOURLS_SITE."/admin/upgrade.php?step=2&oldver=1.3&newver=1.4&oldsql=100&newsql=200" );
\r
25 // convert each link in table url
\r
26 yourls_update_table_to_14();
\r
30 // update table url structure part 2: recreate indexes
\r
31 yourls_alter_url_table_to_14_part_two();
\r
32 yourls_redirect_javascript( YOURLS_SITE."/admin/upgrade.php?step=4&oldver=1.3&newver=1.4&oldsql=100&newsql=200" );
\r
36 // update version & db_version & next_id in the option table
\r
37 // attempt to drop YOURLS_DB_TABLE_NEXTDEC
\r
38 yourls_update_options_to_14();
\r
42 // Update options to reflect new version
\r
43 function yourls_update_options_to_14() {
\r
44 yourls_update_option( 'version', YOURLS_VERSION );
\r
45 yourls_update_option( 'db_version', YOURLS_DB_VERSION );
\r
48 $table = YOURLS_DB_TABLE_NEXTDEC;
\r
49 $next_id = $ydb->get_var("SELECT `next_id` FROM `$table`");
\r
50 yourls_update_option( 'next_id', $next_id );
\r
51 @$ydb->query( "DROP TABLE `$table`" );
\r
54 // Create new tables for YOURLS 1.4: options & log
\r
55 function yourls_create_tables_for_14() {
\r
60 $queries[YOURLS_DB_TABLE_OPTIONS] =
\r
61 'CREATE TABLE IF NOT EXISTS `'.YOURLS_DB_TABLE_OPTIONS.'` ('.
\r
62 '`option_id` int(11) unsigned NOT NULL auto_increment,'.
\r
63 '`option_name` varchar(64) NOT NULL default "",'.
\r
64 '`option_value` longtext NOT NULL,'.
\r
65 'PRIMARY KEY (`option_id`,`option_name`),'.
\r
66 'KEY `option_name` (`option_name`)'.
\r
69 $queries[YOURLS_DB_TABLE_LOG] =
\r
70 'CREATE TABLE IF NOT EXISTS `'.YOURLS_DB_TABLE_LOG.'` ('.
\r
71 '`click_id` int(11) NOT NULL auto_increment,'.
\r
72 '`click_time` datetime NOT NULL,'.
\r
73 '`shorturl` varchar(200) NOT NULL,'.
\r
74 '`referrer` varchar(200) NOT NULL,'.
\r
75 '`user_agent` varchar(255) NOT NULL,'.
\r
76 '`ip_address` varchar(41) NOT NULL,'.
\r
77 '`country_code` char(2) NOT NULL,'.
\r
78 'PRIMARY KEY (`click_id`),'.
\r
79 'KEY `shorturl` (`shorturl`,`referrer`,`country_code`)'.
\r
82 foreach( $queries as $query ) {
\r
83 $ydb->query( $query );
\r
86 echo "<p>New tables created. Please wait...</p>";
\r
90 // Alter table structure, part 1 (change schema, drop index)
\r
91 function yourls_alter_url_table_to_14() {
\r
93 $table = YOURLS_DB_TABLE_URL;
\r
97 $alters[] = "ALTER TABLE `$table` CHANGE `id` `keyword` VARCHAR( 200 ) NOT NULL";
\r
98 $alters[] = "ALTER TABLE `$table` DROP PRIMARY KEY";
\r
100 foreach ( $alters as $query ) {
\r
101 $ydb->query( $query );
\r
104 echo "<p>Structure of existing tables updated. Please wait...</p>";
\r
107 // Alter table structure, part 2 (recreate index after the table is up to date)
\r
108 function yourls_alter_url_table_to_14_part_two() {
\r
110 $table = YOURLS_DB_TABLE_URL;
\r
113 $alters[] = "ALTER TABLE `$table` ADD PRIMARY KEY ( `keyword` )";
\r
115 foreach ( $alters as $query ) {
\r
116 $ydb->query( $query );
\r
119 echo "<p>New table index created</p>";
\r
122 // Convert each link from 1.3 (id) to 1.4 (keyword) structure
\r
123 function yourls_update_table_to_14() {
\r
125 $table = YOURLS_DB_TABLE_URL;
\r
127 // Modify each link to reflect new structure
\r
129 $from = isset($_GET['from']) ? intval( $_GET['from'] ) : 0 ;
\r
130 $total = yourls_get_db_stats();
\r
131 $total = $total['total_links'];
\r
133 $sql = "SELECT `keyword`,`url` FROM `$table` WHERE 1=1 ORDER BY `url` ASC LIMIT $from, $chunk ;";
\r
135 $rows = $ydb->get_results($sql);
\r
138 foreach( $rows as $row ) {
\r
139 $keyword = $row->keyword;
\r
141 $newkeyword = yourls_int2string( $keyword );
\r
142 $result = $ydb->query("UPDATE `$table` SET `keyword` = '$newkeyword' WHERE `url` = '$url';");
\r
146 if ( $count == $chunk ) {
\r
147 // there are probably other rows to convert
\r
148 $from = $from + $chunk;
\r
149 $remain = $total - $from;
\r
150 echo "<p>Converted $chunk database rows ($remain remaining). Continuing... Please do not close this window until it's finished!</p>";
\r
151 yourls_redirect_javascript( YOURLS_SITE."/admin/upgrade.php?step=2&oldver=1.3&newver=1.4&oldsql=100&newsql=200&from=$from" );
\r
154 echo '<p>All rows converted! Please wait...</p>';
\r
155 yourls_redirect_javascript( YOURLS_SITE."/admin/upgrade.php?step=3&oldver=1.3&newver=1.4&oldsql=100&newsql=200" );
\r