1<?php 2/* 3 * vim:set softtabstop=4 shiftwidth=4 expandtab: 4 * 5 * LICENSE: GNU Affero General Public License, version 3 (AGPL-3.0-or-later) 6 * Copyright 2001 - 2020 Ampache.org 7 * 8 * This program is free software: you can redistribute it and/or modify 9 * it under the terms of the GNU Affero General Public License as published by 10 * the Free Software Foundation, either version 3 of the License, or 11 * (at your option) any later version. 12 * 13 * This program is distributed in the hope that it will be useful, 14 * but WITHOUT ANY WARRANTY; without even the implied warranty of 15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 16 * GNU Affero General Public License for more details. 17 * 18 * You should have received a copy of the GNU Affero General Public License 19 * along with this program. If not, see <https://www.gnu.org/licenses/>. 20 * 21 */ 22 23declare(strict_types=0); 24 25namespace Ampache\Module\System; 26 27use Ampache\Config\AmpConfig; 28use Ampache\Repository\Model\User; 29 30/** 31 * Update Class 32 * 33 * This class mainly handles schema updates for the database. 34 * Versions are a monotonically increasing integer: First column(s) are the 35 * major version, followed by a single column for the minor version and four 36 * columns for the build number. 3.6 build 1 is 360000; 10.9 build 17 is 37 * 1090017. 38 */ 39class Update 40{ 41 public $key; 42 public $value; 43 public static $versions; // array containing version information 44 45 /** 46 * get_version 47 * 48 * This checks to see what version you are currently running. 49 * Because we may not have the update_info table we have to check 50 * for its existence first. 51 * @return string 52 */ 53 public static function get_version() 54 { 55 $version = ""; 56 /* Make sure that update_info exits */ 57 $sql = "SHOW TABLES LIKE 'update_info'"; 58 $db_results = Dba::read($sql); 59 if (!Dba::dbh()) { 60 header("Location: test.php"); 61 } 62 63 // If no table 64 if (!Dba::num_rows($db_results)) { 65 // They can't upgrade, they are too old 66 header("Location: test.php"); 67 } else { 68 // If we've found the update_info table, let's get the version from it 69 $sql = "SELECT * FROM `update_info` WHERE `key`='db_version'"; 70 $db_results = Dba::read($sql); 71 $results = Dba::fetch_assoc($db_results); 72 $version = $results['value']; 73 } 74 75 return $version; 76 } // get_version 77 78 /** 79 * format_version 80 * 81 * Make the version number pretty. 82 * @param string $data 83 * @return string 84 */ 85 public static function format_version($data) 86 { 87 return substr($data, 0, strlen((string)$data) - 5) . '.' . substr($data, strlen((string)$data) - 5, 88 1) . ' Build:' . substr($data, strlen((string)$data) - 4, strlen((string)$data)); 89 } 90 91 /** 92 * need_update 93 * 94 * Checks to see if we need to update ampache at all. 95 * @return boolean 96 */ 97 public static function need_update() 98 { 99 $current_version = self::get_version(); 100 101 if (!is_array(self::$versions)) { 102 self::$versions = self::populate_version(); 103 } 104 105 // Iterate through the versions and see if we need to apply any updates 106 foreach (self::$versions as $update) { 107 if ($update['version'] > $current_version) { 108 return true; 109 } 110 } 111 112 return false; 113 } 114 115 /** 116 * populate_version 117 * just sets an array the current differences 118 * that require an update 119 * @return array 120 */ 121 public static function populate_version() 122 { 123 /* Define the array */ 124 $version = array(); 125 126 $update_string = "- Add manual update flag on artist.<br />"; 127 $version[] = array('version' => '380005', 'description' => $update_string); 128 129 $update_string = "- Add library item context menu option.<br />"; 130 $version[] = array('version' => '380006', 'description' => $update_string); 131 132 $update_string = "- Add upload rename pattern and ignore duplicate options.<br />"; 133 $version[] = array('version' => '380007', 'description' => $update_string); 134 135 $update_string = "- Add browse filter and light sidebar options.<br />"; 136 $version[] = array('version' => '380008', 'description' => $update_string); 137 138 $update_string = "- Add update date to playlist.<br />"; 139 $version[] = array('version' => '380009', 'description' => $update_string); 140 141 $update_string = "- Add custom blank album/video default image and alphabet browsing options.<br />"; 142 $version[] = array('version' => '380010', 'description' => $update_string); 143 144 $update_string = "- Fix username max size to be the same one across all tables.<br />"; 145 $version[] = array('version' => '380011', 'description' => $update_string); 146 147 $update_string = "- Fix change in <a href='https://github.com/ampache/ampache/commit/0c26c336269624d75985e46d324e2bc8108576ee'>this commit</a>, that left the userbase with an inconsistent database, if users updated or installed Ampache before 28 Apr 2015<br />"; 148 $version[] = array('version' => '380012', 'description' => $update_string); 149 150 $update_string = "* Enable better podcast defaults<br />* Increase copyright column size to fix issue #1861<br />* Add name_track, name_artist, name_album to user_activity<br />* Add mbid_track, mbid_artist, mbid_album to user_activity<br />* Insert some decent SmartLists for a better default experience<br />* Delete plex preferences from the server<br />"; 151 $version[] = array('version' => '400000', 'description' => $update_string); 152 153 $update_string = "* Update preferences for older users to match current subcategory items<br /> (~3.6 introduced subcategories but didn't include updates for existing users.<br /> This is a cosmetic update and does not affect any operation)<br />"; 154 $version[] = array('version' => '400001', 'description' => $update_string); 155 156 $update_string = "**IMPORTANT UPDATE NOTES**<br /><br /> This is part of a major update to how Ampache handles Albums, Artists and data migration during tag updates.<br /><br />* Update album disk support to allow 1 instead of 0 by default.<br />* Add barcode catalog_number and original_year to albums.<br />* Drop catalog_number from song_data and use album instead.<br />"; 157 $version[] = array('version' => '400002', 'description' => $update_string); 158 159 $update_string = "* Make sure preference names are updated to current strings<br />"; 160 $version[] = array('version' => '400003', 'description' => $update_string); 161 162 $update_string = "* Delete upload_user_artist database settings<br />"; 163 $version[] = array('version' => '400004', 'description' => $update_string); 164 165 $update_string = "* Add a last_count to search table to speed up access requests<br />"; 166 $version[] = array('version' => '400005', 'description' => $update_string); 167 168 $update_string = "* Drop shoutcast_active preferences. (Feature has not existed for years)<br />* Drop localplay_shoutcast table if present.<br />"; 169 $version[] = array('version' => '400006', 'description' => $update_string); 170 171 $update_string = "* Add ui option for skip_count display.<br />* Add ui option for displaying dates in a custom format.<br />"; 172 $version[] = array('version' => '400007', 'description' => $update_string); 173 174 $update_string = "* Add system option for cron based cache and create related tables.<br />"; 175 $version[] = array('version' => '400008', 'description' => $update_string); 176 177 $update_string = "* Add ui option for forcing unique items to playlists.<br />"; 178 $version[] = array('version' => '400009', 'description' => $update_string); 179 180 $update_string = "* Add a last_duration to search table to speed up access requests<br />"; 181 $version[] = array('version' => '400010', 'description' => $update_string); 182 183 $update_string = "**IMPORTANT UPDATE NOTES**<br /><br /> To allow negatives the maximum value of `song`.`track` has been reduced. This shouldn't affect anyone due to the large size allowed.<br /><br />* Allow negative track numbers for albums. (-32,767 -> 32,767)<br />* Truncate database tracks to 0 when greater than 32,767<br />"; 184 $version[] = array('version' => '400011', 'description' => $update_string); 185 186 $update_string = "* Add a rss token to allow the use of RSS unauthenticated feeds<br/ >"; 187 $version[] = array('version' => '400012', 'description' => $update_string); 188 189 $update_string = "* Extend Democratic cooldown beyond 255.<br/ >"; 190 $version[] = array('version' => '400013', 'description' => $update_string); 191 192 $update_string = "* Add last_duration to playlist<br/ > * Add time to artist and album<br/ >"; 193 $version[] = array('version' => '400014', 'description' => $update_string); 194 195 $update_string = "* Extend artist time. smallint was too small<br/ > "; 196 $version[] = array('version' => '400015', 'description' => $update_string); 197 198 $update_string = "* Extend album and make artist even bigger. This should cover everyone.<br/ > "; 199 $version[] = array('version' => '400016', 'description' => $update_string); 200 201 $update_string = ""; // REMOVED update 202 $version[] = array('version' => '400017', 'description' => $update_string); 203 204 $update_string = "* Extend video bitrate to unsigned. There's no reason for a negative bitrate.<br/ > "; 205 $version[] = array('version' => '400018', 'description' => $update_string); 206 207 $update_string = "* Put 'of_the_moment' into a per user preference.<br/ > "; 208 $version[] = array('version' => '400019', 'description' => $update_string); 209 210 $update_string = "* Customizable login page background.<br/ > "; 211 $version[] = array('version' => '400020', 'description' => $update_string); 212 213 $update_string = "* Add r128 gain columns to song_data.<br/ > "; 214 $version[] = array('version' => '400021', 'description' => $update_string); 215 216 $update_string = "* Extend allowed time for podcast_episodes.<br/ > "; 217 $version[] = array('version' => '400022', 'description' => $update_string); 218 219 $update_string = "* Delete 'concerts_limit_past' and 'concerts_limit_future' database settings.<br/ > "; 220 $version[] = array('version' => '400023', 'description' => $update_string); 221 222 $update_string = "**IMPORTANT UPDATE NOTES**<br />These columns will fill dynamically in the web UI but you should do a catalog 'add' as soon as possible to fill them.<br />It will take a while for large libraries but will help API and SubSonic clients.<br /><br />* Add 'song_count', 'album_count' and 'album_group_count' to artist. <br />"; 223 $version[] = array('version' => '400024', 'description' => $update_string); 224 225 $update_string = "* Delete duplicate files in the song table<br />"; 226 $version[] = array('version' => '500000', 'description' => $update_string); 227 228 $update_string = "* Add `release_status`, `addition_time`, `catalog` to album table<br />* Add `mbid`, `country` and `active` to label table<br />* Fill the album `catalog` value using the song table<br />* Fill the artist `album_count`, `album_group_count` and `song_count` values"; 229 $version[] = array('version' => '500001', 'description' => $update_string); 230 231 $update_string = "* Create `total_count` and `total_skip` to album, artist, song, video and podcast_episode tables<br />* Fill counts into the columns"; 232 $version[] = array('version' => '500002', 'description' => $update_string); 233 234 $update_string = "* Create catalog_map table and fill it with data"; 235 $version[] = array('version' => '500003', 'description' => $update_string); 236 237 $update_string = "**IMPORTANT UPDATE NOTES**<br />For large catalogs this will be slow!<br />* Create catalog_map table and fill it with data"; 238 $version[] = array('version' => '500004', 'description' => $update_string); 239 240 $update_string = "* Add song_count, artist_count to album"; 241 $version[] = array('version' => '500005', 'description' => $update_string); 242 243 $update_string = "* Add user_playlist and user_data table"; 244 $version[] = array('version' => '500006', 'description' => $update_string); 245 246 $update_string = "* Add a 'Browse' category to interface preferences<br />* Add option ('show_license') for hiding license column in song rows"; 247 $version[] = array('version' => '500007', 'description' => $update_string); 248 249 $update_string = "* Add filter_user to catalog table<br />* Set a unique key on user_data"; 250 $version[] = array('version' => '500008', 'description' => $update_string); 251 252 $update_string = "* Add ui option ('use_original_year') Browse by Original Year for albums (falls back to Year)"; 253 $version[] = array('version' => '500009', 'description' => $update_string); 254 255 $update_string = "* Add ui option ('hide_single_artist') Hide the Song Artist column for Albums with one Artist"; 256 $version[] = array('version' => '500010', 'description' => $update_string); 257 258 $update_string = "* Add `total_count` to podcast table and fill counts into the column"; 259 $version[] = array('version' => '500011', 'description' => $update_string); 260 261 $update_string = "* Move user bandwidth calculations out of the user format function into the user_data table"; 262 $version[] = array('version' => '500012', 'description' => $update_string); 263 264 $update_string = "* Add tables for tracking deleted files. (deleted_song, deleted_video, deleted_podcast_episode)<br />* Add username to the playlist table to stop pulling user all the time"; 265 $version[] = array('version' => '500013', 'description' => $update_string); 266 267 $update_string = "* Add `episodes` to podcast table to track episode count"; 268 $version[] = array('version' => '500014', 'description' => $update_string); 269 270 $update_string = "* Add ui option ('hide_genres') Hide the Genre column in browse table rows"; 271 $version[] = array('version' => '500015', 'description' => $update_string); 272 273 return $version; 274 } 275 276 /** 277 * display_update 278 * This displays a list of the needed 279 * updates to the database. This will actually 280 * echo out the list... 281 */ 282 public static function display_update(): array 283 { 284 $result = []; 285 $current_version = self::get_version(); 286 if (!is_array(self::$versions)) { 287 self::$versions = self::populate_version(); 288 } 289 290 foreach (self::$versions as $update) { 291 if ($update['version'] > $current_version) { 292 $result[] = [ 293 'version' => T_('Version') . ': ' . self::format_version($update['version']), 294 'description' => $update['description'] 295 ]; 296 } 297 } 298 299 return $result; 300 } 301 302 /** 303 * run_update 304 * This function actually updates the db. 305 * it goes through versions and finds the ones 306 * that need to be run. Checking to make sure 307 * the function exists first. 308 */ 309 public static function run_update() 310 { 311 debug_event(self::class, 'run_update: starting', 4); 312 /* Nuke All Active session before we start the mojo */ 313 $sql = "TRUNCATE session"; 314 Dba::write($sql); 315 316 // Prevent the script from timing out, which could be bad 317 set_time_limit(0); 318 319 $current_version = self::get_version(); 320 321 // Run a check to make sure that they don't try to upgrade from a version that 322 // won't work. 323 if ($current_version < '380004') { 324 echo '<p class="database-update">Database version too old, please upgrade to <a href="https://github.com/ampache/ampache/releases/download/3.8.2/ampache-3.8.2_all.zip">Ampache-3.8.2</a> first</p>'; 325 326 return false; 327 } 328 329 $methods = get_class_methods(Update::class); 330 331 if (!is_array((self::$versions))) { 332 self::$versions = self::populate_version(); 333 } 334 335 debug_event(self::class, 'run_update: checking versions', 4); 336 foreach (self::$versions as $version) { 337 // If it's newer than our current version let's see if a function 338 // exists and run the bugger. 339 if ($version['version'] > $current_version) { 340 $update_function = "update_" . $version['version']; 341 if (in_array($update_function, $methods)) { 342 $success = call_user_func(array('Ampache\Module\System\Update', $update_function)); 343 344 // If the update fails drop out 345 if ($success) { 346 debug_event(self::class, 'run_update: successfully updated to ' . $version['version'], 3); 347 self::set_version('db_version', $version['version']); 348 } else { 349 echo AmpError::display('update'); 350 351 return false; 352 } 353 } 354 } 355 } // end foreach version 356 357 // Let's also clean up the preferences unconditionally 358 debug_event(self::class, 'run_update: starting rebuild_all_preferences', 5); 359 User::rebuild_all_preferences(); 360 361 // Upgrade complete 362 debug_event(self::class, 'run_update: starting', 4); 363 364 return true; 365 } // run_update 366 367 /** 368 * set_version 369 * 370 * This updates the 'update_info' which is used by the updater 371 * and plugins 372 * @param string $key 373 * @param $value 374 */ 375 private static function set_version($key, $value) 376 { 377 $sql = "UPDATE update_info SET value='$value' WHERE `key`='$key'"; 378 Dba::write($sql); 379 } 380 381 /** 382 * update_380005 383 * 384 * Add manual update flag on artist 385 */ 386 public static function update_380005() 387 { 388 $retval = true; 389 390 $sql = "ALTER TABLE `artist` ADD COLUMN `manual_update` SMALLINT(1) DEFAULT '0'"; 391 $retval &= Dba::write($sql); 392 393 return $retval; 394 } 395 396 /** 397 * update_380006 398 * 399 * Add library item context menu option 400 */ 401 public static function update_380006() 402 { 403 $retval = true; 404 405 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('libitem_contextmenu', '1', 'Library item context menu',0, 'boolean', 'interface', 'library')"; 406 $retval &= Dba::write($sql); 407 $row_id = Dba::insert_id(); 408 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '1')"; 409 $retval &= Dba::write($sql, array($row_id)); 410 411 return $retval; 412 } 413 414 /** 415 * update_380007 416 * 417 * Add upload rename pattern and ignore duplicate options 418 */ 419 public static function update_380007() 420 { 421 $retval = true; 422 423 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('upload_catalog_pattern', '0', 'Rename uploaded file according to catalog pattern',100, 'boolean', 'system', 'upload')"; 424 $retval &= Dba::write($sql); 425 $row_id = Dba::insert_id(); 426 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 427 $retval &= Dba::write($sql, array($row_id)); 428 429 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('catalog_check_duplicate', '0', 'Check library item at import time and don\'t import duplicates',100, 'boolean', 'system', 'catalog')"; 430 $retval &= Dba::write($sql); 431 $row_id = Dba::insert_id(); 432 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 433 $retval &= Dba::write($sql, array($row_id)); 434 435 return $retval; 436 } 437 438 /** 439 * update_380008 440 * 441 * Add browse filter and light sidebar options 442 */ 443 public static function update_380008() 444 { 445 $retval = true; 446 447 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('browse_filter', '0', 'Show filter box on browse', 25, 'boolean', 'interface', 'library')"; 448 $retval &= Dba::write($sql); 449 $row_id = Dba::insert_id(); 450 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 451 $retval &= Dba::write($sql, array($row_id)); 452 453 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('sidebar_light', '0', 'Light sidebar by default', 25, 'boolean', 'interface', 'theme')"; 454 $retval &= Dba::write($sql); 455 $row_id = Dba::insert_id(); 456 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 457 $retval &= Dba::write($sql, array($row_id)); 458 459 return $retval; 460 } 461 462 /** 463 * update_380009 464 * 465 * Add update date to playlist 466 */ 467 public static function update_380009() 468 { 469 $retval = true; 470 471 $sql = "ALTER TABLE `playlist` ADD COLUMN `last_update` int(11) unsigned NOT NULL DEFAULT '0'"; 472 $retval &= Dba::write($sql); 473 474 return $retval; 475 } 476 477 /** 478 * update_380010 479 * 480 * Add custom blank album/video default image and alphabet browsing options 481 */ 482 public static function update_380010() 483 { 484 $retval = true; 485 486 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('custom_blankalbum', '', 'Custom blank album default image',75, 'string', 'interface', 'custom')"; 487 $retval &= Dba::write($sql); 488 $row_id = Dba::insert_id(); 489 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '')"; 490 $retval &= Dba::write($sql, array($row_id)); 491 492 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('custom_blankmovie', '', 'Custom blank video default image',75, 'string', 'interface', 'custom')"; 493 $retval &= Dba::write($sql); 494 $row_id = Dba::insert_id(); 495 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '')"; 496 $retval &= Dba::write($sql, array($row_id)); 497 498 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('libitem_browse_alpha', '', 'Alphabet browsing by default for following library items (album,artist,...)',75, 'string', 'interface', 'library')"; 499 $retval &= Dba::write($sql); 500 $row_id = Dba::insert_id(); 501 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '')"; 502 $retval &= Dba::write($sql, array($row_id)); 503 504 return $retval; 505 } 506 507 /** 508 * update_380011 509 * 510 * Fix username max size to be the same one across all tables. 511 */ 512 public static function update_380011() 513 { 514 $retval = true; 515 516 $sql = "ALTER TABLE session MODIFY username VARCHAR(255)"; 517 $retval &= Dba::write($sql); 518 519 $sql = "ALTER TABLE session_remember MODIFY username VARCHAR(255)"; 520 $retval &= Dba::write($sql); 521 522 $sql = "ALTER TABLE user MODIFY username VARCHAR(255)"; 523 $retval &= Dba::write($sql); 524 525 $sql = "ALTER TABLE user MODIFY fullname VARCHAR(255)"; 526 $retval &= Dba::write($sql); 527 528 return $retval; 529 } 530 531 /** 532 * update_380012 533 * 534 * Fix change in https://github.com/ampache/ampache/commit/0c26c336269624d75985e46d324e2bc8108576ee 535 * That left the user base with an inconsistent database. 536 * For more information, please look at update_360035. 537 */ 538 public static function update_380012() 539 { 540 $retval = true; 541 542 $sql = "UPDATE `preference` SET `description`='Enable url rewriting' WHERE `preference`.`name`='stream_beautiful_url'"; 543 $retval &= Dba::write($sql); 544 545 return $retval; 546 } 547 548 /** 549 * update_400000 550 * 551 * Increase copyright column size to fix issue #1861 552 * Add name_track, name_artist, name_album to user_activity 553 * Add mbid_track, mbid_artist, mbid_album to user_activity 554 * Insert some decent SmartLists for a better default experience 555 * Delete the following plex preferences from the server 556 * plex_backend 557 * myplex_username 558 * myplex_authtoken 559 * myplex_published 560 * plex_uniqid 561 * plex_servername 562 * plex_public_address 563 * plex_public_port 564 * plex_local_auth 565 * plex_match_email 566 * Add preference for master/develop branch selection 567 */ 568 public static function update_400000() 569 { 570 $retval = true; 571 572 $sql = "ALTER TABLE `podcast` MODIFY `copyright` VARCHAR(255)"; 573 $retval &= Dba::write($sql); 574 575 $sql = "ALTER TABLE `user_activity` ADD COLUMN `name_track` VARCHAR(255) NULL DEFAULT NULL, ADD COLUMN `name_artist` VARCHAR(255) NULL DEFAULT NULL, ADD COLUMN `name_album` VARCHAR(255) NULL DEFAULT NULL;"; 576 $retval &= Dba::write($sql); 577 578 $sql = "ALTER TABLE `user_activity` ADD COLUMN `mbid_track` VARCHAR(255) NULL DEFAULT NULL, ADD COLUMN `mbid_artist` VARCHAR(255) NULL DEFAULT NULL, ADD COLUMN `mbid_album` VARCHAR(255) NULL DEFAULT NULL;"; 579 $retval &= Dba::write($sql); 580 581 $sql = "INSERT IGNORE INTO `search` (`user`, `type`, `rules`, `name`, `logic_operator`, `random`, `limit`) VALUES (-1, 'public', '[[\"artistrating\",\"equal\",\"5\",null]]', 'Artist 5*', 'AND', 0, 0), (-1, 'public', '[[\"artistrating\",\"equal\",\"4\",null]]', 'Artist 4*', 'AND', 0, 0), (-1, 'public', '[[\"artistrating\",\"equal\",\"3\",null]]', 'Artist 3*', 'AND', 0, 0), (-1, 'public', '[[\"artistrating\",\"equal\",\"2\",null]]', 'Artist 2*', 'AND', 0, 0), (-1, 'public', '[[\"artistrating\",\"equal\",\"1\",null]]', 'Artist 1*', 'AND', 0, 0), (-1, 'public', '[[\"albumrating\",\"equal\",\"5\",null]]', 'Album 5*', 'AND', 0, 0), (-1, 'public', '[[\"albumrating\",\"equal\",\"4\",null]]', 'Album 4*', 'AND', 0, 0), (-1, 'public', '[[\"albumrating\",\"equal\",\"3\",null]]', 'Album 3*', 'AND', 0, 0), (-1, 'public', '[[\"albumrating\",\"equal\",\"2\",null]]', 'Album 2*', 'AND', 0, 0), (-1, 'public', '[[\"albumrating\",\"equal\",\"1\",null]]', 'Album 1*', 'AND', 0, 0), (-1, 'public', '[[\"myrating\",\"equal\",\"5\",null]]', 'Song 5*', 'AND', 0, 0), (-1, 'public', '[[\"myrating\",\"equal\",\"4\",null]]', 'Song 4*', 'AND', 0, 0), (-1, 'public', '[[\"myrating\",\"equal\",\"3\",null]]', 'Song 3*', 'AND', 0, 0), (-1, 'public', '[[\"myrating\",\"equal\",\"2\",null]]', 'Song 2*', 'AND', 0, 0), (-1, 'public', '[[\"myrating\",\"equal\",\"1\",null]]', 'Song 1*', 'AND', 0, 0);"; 582 $retval &= Dba::write($sql); 583 584 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'plex_backend');"; 585 $retval &= Dba::write($sql); 586 587 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'myplex_username');"; 588 $retval &= Dba::write($sql); 589 590 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'myplex_authtoken');"; 591 $retval &= Dba::write($sql); 592 593 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'myplex_published');"; 594 $retval &= Dba::write($sql); 595 596 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'plex_uniqid');"; 597 $retval &= Dba::write($sql); 598 599 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'plex_servername');"; 600 $retval &= Dba::write($sql); 601 602 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'plex_public_address');"; 603 $retval &= Dba::write($sql); 604 605 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'plex_public_port');"; 606 $retval &= Dba::write($sql); 607 608 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'plex_local_auth');"; 609 $retval &= Dba::write($sql); 610 611 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'plex_match_email');"; 612 $retval &= Dba::write($sql); 613 614 $sql = "DELETE FROM `preference` WHERE `preference`.`name` IN ('plex_backend', 'myplex_username', 'myplex_authtoken', 'myplex_published', 'plex_uniqid', 'plex_servername', 'plex_public_address', 'plex_public_port ', 'plex_local_auth', 'plex_match_email');"; 615 $retval &= Dba::write($sql); 616 617 return $retval; 618 } 619 620 /** 621 * update_400001 622 * 623 * Make sure people on older databases have the same preference categories 624 */ 625 public static function update_400001() 626 { 627 $retval = true; 628 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'library' WHERE `preference`.`name` in ('album_sort', 'show_played_times', 'album_group', 'album_release_type', 'album_release_type_sort', 'libitem_contextmenu', 'browse_filter', 'libitem_browse_alpha') AND `preference`.`subcatagory` IS NULL;"; 629 $retval &= Dba::write($sql); 630 631 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'backend' WHERE `preference`.`name` in ('subsonic_backend', 'daap_backend', 'daap_pass', 'upnp_backend', 'webdav_backend') AND `preference`.`subcatagory` IS NULL;"; 632 $retval &= Dba::write($sql); 633 634 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'catalog' WHERE `preference`.`name` = 'catalog_check_duplicate' AND `preference`.`subcatagory` IS NULL;"; 635 $retval &= Dba::write($sql); 636 637 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'custom' WHERE `preference`.`name` in ('site_title', 'custom_logo', 'custom_login_logo', 'custom_favicon', 'custom_text_footer', 'custom_blankalbum', 'custom_blankmovie') AND `preference`.`subcatagory` IS NULL;"; 638 $retval &= Dba::write($sql); 639 640 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'feature' WHERE `preference`.`name` in ('download', 'allow_stream_playback', 'allow_democratic_playback', 'share', 'allow_video', 'geolocation') AND `preference`.`subcatagory` IS NULL;"; 641 $retval &= Dba::write($sql); 642 643 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'home' WHERE `preference`.`name` in ('now_playing_per_user', 'home_moment_albums', 'home_moment_videos', 'home_recently_played', 'home_now_playing') AND `preference`.`subcatagory` IS NULL;"; 644 $retval &= Dba::write($sql); 645 646 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'httpq' WHERE `preference`.`name` = 'httpq_active' AND `preference`.`subcatagory` IS NULL;"; 647 $retval &= Dba::write($sql); 648 649 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'lastfm' WHERE `preference`.`name` in ('lastfm_grant_link', 'lastfm_challenge') AND `preference`.`subcatagory` IS NULL;"; 650 $retval &= Dba::write($sql); 651 652 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'localplay' WHERE `preference`.`name` in ('localplay_controller', 'localplay_level', 'allow_localplay_playback') AND `preference`.`subcatagory` IS NULL;"; 653 $retval &= Dba::write($sql); 654 655 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'metadata' WHERE `preference`.`name` in ('disabled_custom_metadata_fields', 'disabled_custom_metadata_fields_input') AND `preference`.`subcatagory` IS NULL;"; 656 $retval &= Dba::write($sql); 657 658 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'mpd' WHERE `preference`.`name` = 'mpd_active' AND `preference`.`subcatagory` IS NULL;"; 659 $retval &= Dba::write($sql); 660 661 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'notification' WHERE `preference`.`name` in ('browser_notify', 'browser_notify_timeout') AND `preference`.`subcatagory` IS NULL;"; 662 $retval &= Dba::write($sql); 663 664 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'player' WHERE `preference`.`name` in ('show_lyrics', 'song_page_title', 'webplayer_flash', 'webplayer_html5', 'webplayer_confirmclose', 'webplayer_pausetabs', 'slideshow_time', 'broadcast_by_default', 'direct_play_limit', 'webplayer_aurora') AND `preference`.`subcatagory` IS NULL;"; 665 $retval &= Dba::write($sql); 666 667 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'podcast' WHERE `preference`.`name` in ('podcast_keep', 'podcast_new_download') AND `preference`.`subcatagory` IS NULL;"; 668 $retval &= Dba::write($sql); 669 670 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'privacy' WHERE `preference`.`name` in ('allow_personal_info_now', 'allow_personal_info_recent', 'allow_personal_info_time', 'allow_personal_info_agent') AND `preference`.`subcatagory` IS NULL;"; 671 $retval &= Dba::write($sql); 672 673 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'query' WHERE `preference`.`name` in ('popular_threshold', 'offset_limit', 'stats_threshold', 'concerts_limit_future', 'concerts_limit_past') AND `preference`.`subcatagory` IS NULL;"; 674 $retval &= Dba::write($sql); 675 676 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'share' WHERE `preference`.`name` = 'share_expire' AND `preference`.`subcatagory` IS NULL;"; 677 $retval &= Dba::write($sql); 678 679 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'shoutcast' WHERE `preference`.`name` = 'shoutcast_active' AND `preference`.`subcatagory` IS NULL;"; 680 $retval &= Dba::write($sql); 681 682 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'theme' WHERE `preference`.`name` in ('theme_name', 'ui_fixed', 'topmenu', 'theme_color', 'sidebar_light') AND `preference`.`subcatagory` IS NULL;"; 683 $retval &= Dba::write($sql); 684 685 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'transcoding' WHERE `preference`.`name` in ('transcode_bitrate', 'rate_limit', 'transcode') AND `preference`.`subcatagory` IS NULL;"; 686 $retval &= Dba::write($sql); 687 688 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'update' WHERE `preference`.`name` in ('autoupdate', 'autoupdate_lastcheck', 'autoupdate_lastversion', 'autoupdate_lastversion_new') AND `preference`.`subcatagory` IS NULL;"; 689 $retval &= Dba::write($sql); 690 691 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'upload' WHERE `preference`.`name` in ('upload_catalog', 'allow_upload', 'upload_subdir', 'upload_user_artist', 'upload_script', 'upload_allow_edit', 'upload_allow_remove', 'upload_catalog_pattern') AND `preference`.`subcatagory` IS NULL;"; 692 $retval &= Dba::write($sql); 693 694 return $retval; 695 } 696 697 /** 698 * update_400002 699 * 700 * Update disk to allow 1 instead of making it 0 by default 701 * Add barcode catalog_number and original_year 702 * Drop catalog_number from song_data 703 */ 704 public static function update_400002() 705 { 706 $retval = true; 707 $sql = "UPDATE `album` SET `album`.`disk` = 1 WHERE `album`.`disk` = 0;"; 708 $retval &= Dba::write($sql); 709 710 $sql = "ALTER TABLE `album` ADD `original_year` INT(4) NULL, ADD `barcode` VARCHAR(64) NULL, ADD `catalog_number` VARCHAR(64) NULL;"; 711 $retval &= Dba::write($sql); 712 713 $sql = "ALTER TABLE `song_data` DROP `catalog_number`"; 714 $retval &= Dba::write($sql); 715 716 return $retval; 717 } 718 719 /** 720 * update_400003 721 * 722 * Make sure preference names are updated to current strings 723 */ 724 public static function update_400003() 725 { 726 $retval = true; 727 $sql = "UPDATE `preference` SET `preference`.`description` = 'Force HTTP playback regardless of port' WHERE `preference`.`name` = 'force_http_play' "; 728 $retval &= Dba::write($sql); 729 730 $sql = "UPDATE `preference` SET `preference`.`description` = 'Playback Type' WHERE `preference`.`name` = 'play_type' "; 731 $retval &= Dba::write($sql); 732 733 $sql = "UPDATE `preference` SET `preference`.`description` = 'httpQ Active Instance' WHERE `preference`.`name` = 'httpq_active' "; 734 $retval &= Dba::write($sql); 735 736 $sql = "UPDATE `preference` SET `preference`.`description` = 'Now Playing filtered per user' WHERE `preference`.`name` = 'now_playing_per_user' "; 737 $retval &= Dba::write($sql); 738 739 $sql = "UPDATE `preference` SET `preference`.`description` = 'Use Subsonic backend' WHERE `preference`.`name` = 'subsonic_backend' "; 740 $retval &= Dba::write($sql); 741 742 $sql = "UPDATE `preference` SET `preference`.`description` = 'Share Now Playing information' WHERE `preference`.`name` = 'allow_personal_info_now' "; 743 $retval &= Dba::write($sql); 744 745 $sql = "UPDATE `preference` SET `preference`.`description` = 'Share Recently Played information' WHERE `preference`.`name` = 'allow_personal_info_recent' "; 746 $retval &= Dba::write($sql); 747 748 $sql = "UPDATE `preference` SET `preference`.`description` = 'Share Recently Played information - Allow access to streaming date/time' WHERE `preference`.`name` = 'allow_personal_info_time' "; 749 $retval &= Dba::write($sql); 750 751 $sql = "UPDATE `preference` SET `preference`.`description` = 'Share Recently Played information - Allow access to streaming agent' WHERE `preference`.`name` = 'allow_personal_info_agent' "; 752 $retval &= Dba::write($sql); 753 754 $sql = "UPDATE `preference` SET `preference`.`description` = 'Enable URL Rewriting' WHERE `preference`.`name` = 'stream_beautiful_url' "; 755 $retval &= Dba::write($sql); 756 757 $sql = "UPDATE `preference` SET `preference`.`description` = 'Destination catalog' WHERE `preference`.`name` = 'upload_catalog' "; 758 $retval &= Dba::write($sql); 759 760 $sql = "UPDATE `preference` SET `preference`.`description` = 'Allow user uploads' WHERE `preference`.`name` = 'allow_upload' "; 761 $retval &= Dba::write($sql); 762 763 $sql = "UPDATE `preference` SET `preference`.`description` = 'Create a subdirectory per user' WHERE `preference`.`name` = 'upload_subdir' "; 764 $retval &= Dba::write($sql); 765 766 $sql = "UPDATE `preference` SET `preference`.`description` = 'Consider the user sender as the track''s artist' WHERE `preference`.`name` = 'upload_user_artist' "; 767 $retval &= Dba::write($sql); 768 769 $sql = "UPDATE `preference` SET `preference`.`description` = 'Post-upload script (current directory = upload target directory)' WHERE `preference`.`name` = 'upload_script' "; 770 $retval &= Dba::write($sql); 771 772 $sql = "UPDATE `preference` SET `preference`.`description` = 'Allow users to edit uploaded songs' WHERE `preference`.`name` = 'upload_allow_edit' "; 773 $retval &= Dba::write($sql); 774 775 $sql = "UPDATE `preference` SET `preference`.`description` = 'Allow users to remove uploaded songs' WHERE `preference`.`name` = 'upload_allow_remove' "; 776 $retval &= Dba::write($sql); 777 778 $sql = "UPDATE `preference` SET `preference`.`description` = 'Show Albums of the Moment' WHERE `preference`.`name` = 'home_moment_albums' "; 779 $retval &= Dba::write($sql); 780 781 $sql = "UPDATE `preference` SET `preference`.`description` = 'Show Videos of the Moment' WHERE `preference`.`name` = 'home_moment_videos' "; 782 $retval &= Dba::write($sql); 783 784 $sql = "UPDATE `preference` SET `preference`.`description` = 'Custom URL - Logo' WHERE `preference`.`name` = 'custom_logo' "; 785 $retval &= Dba::write($sql); 786 787 $sql = "UPDATE `preference` SET `preference`.`description` = 'Custom URL - Login page logo' WHERE `preference`.`name` = 'custom_login_logo' "; 788 $retval &= Dba::write($sql); 789 790 $sql = "UPDATE `preference` SET `preference`.`description` = 'Custom URL - Favicon' WHERE `preference`.`name` = 'custom_favicon' "; 791 $retval &= Dba::write($sql); 792 793 $sql = "UPDATE `preference` SET `preference`.`description` = 'Album - Default sort' WHERE `preference`.`name` = 'album_sort' "; 794 $retval &= Dba::write($sql); 795 796 $sql = "UPDATE `preference` SET `preference`.`description` = 'Allow Geolocation' WHERE `preference`.`name` = 'Geolocation' "; 797 $retval &= Dba::write($sql); 798 799 $sql = "UPDATE `preference` SET `preference`.`description` = 'Allow Video Features' WHERE `preference`.`name` = 'allow_video' "; 800 $retval &= Dba::write($sql); 801 802 $sql = "UPDATE `preference` SET `preference`.`description` = 'Democratic - Clear votes for expired user sessions' WHERE `preference`.`name` = 'demo_clear_sessions' "; 803 $retval &= Dba::write($sql); 804 805 $sql = "UPDATE `preference` SET `preference`.`description` = 'Allow Transcoding' WHERE `preference`.`name` = 'transcoding' "; 806 $retval &= Dba::write($sql); 807 808 $sql = "UPDATE `preference` SET `preference`.`description` = 'Authorize Flash Web Player' WHERE `preference`.`name` = 'webplayer_flash' "; 809 $retval &= Dba::write($sql); 810 811 $sql = "UPDATE `preference` SET `preference`.`description` = 'Authorize HTML5 Web Player' WHERE `preference`.`name` = 'webplayer_html5' "; 812 $retval &= Dba::write($sql); 813 814 $sql = "UPDATE `preference` SET `preference`.`description` = 'Web Player browser notifications' WHERE `preference`.`name` = 'browser_notify' "; 815 $retval &= Dba::write($sql); 816 817 $sql = "UPDATE `preference` SET `preference`.`description` = 'Web Player browser notifications timeout (seconds)' WHERE `preference`.`name` = 'browser_notify_timeout' "; 818 $retval &= Dba::write($sql); 819 820 $sql = "UPDATE `preference` SET `preference`.`description` = 'Authorize JavaScript decoder (Aurora.js) in Web Player' WHERE `preference`.`name` = 'webplayer_aurora' "; 821 $retval &= Dba::write($sql); 822 823 $sql = "UPDATE `preference` SET `preference`.`description` = 'Show Now Playing' WHERE `preference`.`name` = 'home_now_playing' "; 824 $retval &= Dba::write($sql); 825 826 $sql = "UPDATE `preference` SET `preference`.`description` = 'Show Recently Played' WHERE `preference`.`name` = 'home_recently_played' "; 827 $retval &= Dba::write($sql); 828 829 $sql = "UPDATE `preference` SET `preference`.`description` = '# latest episodes to keep' WHERE `preference`.`name` = 'podcast_keep' "; 830 $retval &= Dba::write($sql); 831 832 $sql = "UPDATE `preference` SET `preference`.`description` = '# episodes to download when new episodes are available' WHERE `preference`.`name` = 'podcast_new_download' "; 833 $retval &= Dba::write($sql); 834 835 $sql = "UPDATE `preference` SET `preference`.`description` = 'Allow Transcoding' WHERE `preference`.`name` = 'transcode' "; 836 $retval &= Dba::write($sql); 837 838 $sql = "UPDATE `preference` SET `preference`.`description` = 'Allow E-mail notifications' WHERE `preference`.`name` = 'notify_email' "; 839 $retval &= Dba::write($sql); 840 841 $sql = "UPDATE `preference` SET `preference`.`description` = 'Custom metadata - Disable these fields' WHERE `preference`.`name` = 'disabled_custom_metadata_fields' "; 842 $retval &= Dba::write($sql); 843 844 $sql = "UPDATE `preference` SET `preference`.`description` = 'Custom metadata - Define field list' WHERE `preference`.`name` = 'disabled_custom_metadata_fields_input' "; 845 $retval &= Dba::write($sql); 846 847 $sql = "UPDATE `preference` SET `preference`.`description` = 'Auto-pause between tabs' WHERE `preference`.`name` = 'webplayer_pausetabs' "; 848 $retval &= Dba::write($sql); 849 850 return $retval; 851 } 852 853 /** 854 * update_400004 855 * 856 * delete upload_user_artist database settings 857 */ 858 public static function update_400004() 859 { 860 $retval = true; 861 862 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'upload_user_artist');"; 863 $retval &= Dba::write($sql); 864 865 $sql = "DELETE FROM `preference` WHERE `preference`.`name` = 'upload_user_artist';"; 866 $retval &= Dba::write($sql); 867 868 return $retval; 869 } 870 871 /** 872 * update_400005 873 * 874 * Add a last_count to searches to speed up access requests 875 */ 876 public static function update_400005() 877 { 878 $retval = true; 879 $sql = "ALTER TABLE `search` ADD `last_count` INT(11) NULL;"; 880 $retval &= Dba::write($sql); 881 882 return $retval; 883 } 884 885 /** 886 * update_400006 887 * 888 * drop shoutcast_active preferences and localplay_shoutcast table 889 */ 890 public static function update_400006() 891 { 892 $retval = true; 893 894 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` = 'shoutcast_active');"; 895 $retval &= Dba::write($sql); 896 897 $sql = "DELETE FROM `preference` WHERE `preference`.`name` = 'shoutcast_active';"; 898 $retval &= Dba::write($sql); 899 900 $sql = "DROP TABLE IF EXISTS `localplay_shoutcast`"; 901 $retval &= Dba::write($sql); 902 903 return $retval; 904 } 905 906 /** 907 * update_400007 908 * 909 * Add ui option for skip_count display 910 * Add ui option for displaying dates in a custom format 911 */ 912 public static function update_400007() 913 { 914 $retval = true; 915 916 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('show_skipped_times', '0', 'Show # skipped', 25, 'boolean', 'interface', 'library')"; 917 $retval &= Dba::write($sql); 918 $row_id = Dba::insert_id(); 919 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 920 $retval &= Dba::write($sql, array($row_id)); 921 922 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('custom_datetime', '', 'Custom datetime', 25, 'string', 'interface', 'custom')"; 923 $retval &= Dba::write($sql); 924 $row_id = Dba::insert_id(); 925 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '')"; 926 $retval &= Dba::write($sql, array($row_id)); 927 928 return $retval; 929 } 930 931 /** 932 * update_400008 933 * 934 * Add system option for cron based cache and create related tables 935 */ 936 public static function update_400008() 937 { 938 $retval = true; 939 940 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('cron_cache', '0', 'Cache computed SQL data (eg. media hits stats) using a cron', 25, 'boolean', 'system', 'catalog')"; 941 $retval &= Dba::write($sql); 942 $row_id = Dba::insert_id(); 943 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 944 $retval &= Dba::write($sql, array($row_id)); 945 946 $tables = ['cache_object_count', 'cache_object_count_run']; 947 $collation = (AmpConfig::get('database_collation', 'utf8mb4_unicode_ci')); 948 $charset = (AmpConfig::get('database_charset', 'utf8mb4')); 949 $engine = ($charset == 'utf8mb4') ? 'InnoDB' : 'MYISAM'; 950 foreach ($tables as $table) { 951 $sql = "CREATE TABLE IF NOT EXISTS `" . $table . "` (`object_id` int(11) unsigned NOT NULL, `object_type` enum('album','artist','song','playlist','genre','catalog','live_stream','video','podcast_episode') CHARACTER SET $charset NOT NULL, `count` int(11) unsigned NOT NULL DEFAULT '0', `threshold` int(11) unsigned NOT NULL DEFAULT '0', `count_type` varchar(16) NOT NULL, PRIMARY KEY (`object_id`, `object_type`, `threshold`, `count_type`)) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$collation;"; 952 $retval &= Dba::write($sql); 953 } 954 955 $sql = "UPDATE `preference` SET `level`=75 WHERE `preference`.`name`='stats_threshold'"; 956 $retval &= Dba::write($sql); 957 958 return $retval; 959 } 960 961 /** 962 * update_400009 963 * 964 * Add ui option for forcing unique items to playlists 965 */ 966 public static function update_400009() 967 { 968 $retval = true; 969 970 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('unique_playlist', '0', 'Only add unique items to playlists', 25, 'boolean', 'playlist', null)"; 971 $retval &= Dba::write($sql); 972 $row_id = Dba::insert_id(); 973 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 974 $retval &= Dba::write($sql, array($row_id)); 975 976 return $retval; 977 } 978 979 /** 980 * update_400010 981 * 982 * Add a last_duration to searches to speed up access requests 983 */ 984 public static function update_400010() 985 { 986 $retval = true; 987 $sql = "ALTER TABLE `search` ADD `last_duration` INT(11) NULL;"; 988 $retval &= Dba::write($sql); 989 990 return $retval; 991 } 992 993 /** 994 * update_400011 995 * 996 * Allow negative track numbers for albums 997 * Truncate database tracks to 0 when greater than 32767 998 */ 999 public static function update_400011() 1000 { 1001 $retval = true; 1002 $sql = "UPDATE `song` SET `track` = 0 WHERE `track` > 32767;"; 1003 $retval &= Dba::write($sql); 1004 1005 $sql = "ALTER TABLE `song` MODIFY COLUMN `track` SMALLINT DEFAULT NULL NULL;"; 1006 $retval &= Dba::write($sql); 1007 1008 return $retval; 1009 } 1010 1011 /** 1012 * update_400012 1013 * 1014 * Add a rss token to use an RSS unauthenticated feed. 1015 */ 1016 public static function update_400012() 1017 { 1018 $retval = true; 1019 $sql = "ALTER TABLE `user` ADD `rsstoken` VARCHAR(255) NULL;"; 1020 $retval &= Dba::write($sql); 1021 1022 return $retval; 1023 } 1024 1025 /** 1026 * update_400013 1027 * 1028 * Extend Democratic cooldown beyond 255. 1029 */ 1030 public static function update_400013() 1031 { 1032 $retval = true; 1033 $sql = "ALTER TABLE `democratic` MODIFY COLUMN `cooldown` int(11) unsigned DEFAULT NULL NULL;"; 1034 $retval &= Dba::write($sql); 1035 1036 return $retval; 1037 } 1038 1039 /** 1040 * update_400014 1041 * 1042 * Add last_duration to playlist 1043 * Add time to artist and album 1044 */ 1045 public static function update_400014() 1046 { 1047 $retval = true; 1048 1049 $sql = "ALTER TABLE `playlist` ADD COLUMN `last_duration` int(11) unsigned NOT NULL DEFAULT '0'"; 1050 $retval &= Dba::write($sql); 1051 1052 $sql = "ALTER TABLE `album` ADD COLUMN `time` smallint(5) unsigned NOT NULL DEFAULT '0'"; 1053 $retval &= Dba::write($sql); 1054 1055 $sql = "ALTER TABLE `artist` ADD COLUMN `time` smallint(5) unsigned NOT NULL DEFAULT '0'"; 1056 $retval &= Dba::write($sql); 1057 1058 return $retval; 1059 } // 1060 1061 /** 1062 * update_400015 1063 * 1064 * Extend artist time. smallint was too small 1065 */ 1066 public static function update_400015() 1067 { 1068 $retval = true; 1069 1070 $sql = "ALTER TABLE `artist` MODIFY COLUMN `time` int(11) unsigned DEFAULT NULL NULL;"; 1071 $retval &= Dba::write($sql); 1072 1073 return $retval; 1074 } 1075 1076 /** 1077 * update_400016 1078 * 1079 * Extend album and make artist even bigger. This should cover everyone. 1080 */ 1081 public static function update_400016() 1082 { 1083 $retval = true; 1084 1085 $sql = "ALTER TABLE `album` MODIFY COLUMN `time` bigint(20) unsigned DEFAULT NULL NULL;"; 1086 $retval &= Dba::write($sql); 1087 1088 $sql = "ALTER TABLE `artist` MODIFY COLUMN `time` int(11) unsigned DEFAULT NULL NULL;"; 1089 $retval &= Dba::write($sql); 1090 1091 return $retval; 1092 } 1093 1094 /** 1095 * update_400017 1096 * 1097 * Removed. 1098 */ 1099 public static function update_400017() 1100 { 1101 return true; 1102 } 1103 1104 /** 1105 * update_400018 1106 * 1107 * Extend video bitrate to unsigned. There's no reason for a negative bitrate. 1108 */ 1109 public static function update_400018() 1110 { 1111 $retval = true; 1112 $sql = "UPDATE `video` SET `video_bitrate` = 0 WHERE `video_bitrate` < 0;"; 1113 $retval &= Dba::write($sql); 1114 1115 $sql = "ALTER TABLE `video` MODIFY COLUMN `video_bitrate` int(11) unsigned DEFAULT NULL NULL;"; 1116 $retval &= Dba::write($sql); 1117 1118 return $retval; 1119 } 1120 1121 /** 1122 * update_400019 1123 * 1124 * Put of_the_moment into a per user preference 1125 */ 1126 public static function update_400019() 1127 { 1128 $retval = true; 1129 1130 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('of_the_moment', '6', 'Set the amount of items Album/Video of the Moment will display', 25, 'integer', 'interface', 'home')"; 1131 $retval &= Dba::write($sql); 1132 $row_id = Dba::insert_id(); 1133 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '6')"; 1134 $retval &= Dba::write($sql, array($row_id)); 1135 1136 return $retval; 1137 } 1138 1139 /** 1140 * update_400020 1141 * 1142 * Customizable login background image 1143 */ 1144 public static function update_400020() 1145 { 1146 $retval = true; 1147 1148 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('custom_login_background', '', 'Custom URL - Login page background', 75, 'string', 'interface', 'custom')"; 1149 $retval &= Dba::write($sql); 1150 $row_id = Dba::insert_id(); 1151 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '')"; 1152 $retval &= Dba::write($sql, array($row_id)); 1153 1154 return $retval; 1155 } 1156 1157 /** 1158 * update_400021 1159 * 1160 * Add r128 gain columns to song_data 1161 */ 1162 public static function update_400021() 1163 { 1164 $retval = true; 1165 1166 $sql = "ALTER TABLE `song_data` ADD `r128_track_gain` smallint(5) DEFAULT NULL, ADD `r128_album_gain` smallint(5) DEFAULT NULL;"; 1167 $retval &= Dba::write($sql); 1168 1169 return $retval; 1170 } 1171 1172 /** 1173 * update_400022 1174 * 1175 * Extend allowed time for podcast_episodes 1176 */ 1177 public static function update_400022() 1178 { 1179 $retval = true; 1180 1181 $sql = "ALTER TABLE `podcast_episode` MODIFY COLUMN `time` int(11) unsigned DEFAULT 0 NOT NULL; "; 1182 $retval &= Dba::write($sql); 1183 1184 return $retval; 1185 } 1186 1187 /** 1188 * update_400023 1189 * 1190 * delete concerts_limit_past and concerts_limit_future database settings 1191 */ 1192 public static function update_400023() 1193 { 1194 $retval = true; 1195 1196 $sql = "DELETE FROM `user_preference` WHERE `user_preference`.`preference` IN (SELECT `preference`.`id` FROM `preference` WHERE `preference`.`name` IN ('concerts_limit_past', 'concerts_limit_future'));"; 1197 $retval &= Dba::write($sql); 1198 1199 $sql = "DELETE FROM `preference` WHERE `preference`.`name` IN ('concerts_limit_past', 'concerts_limit_future');"; 1200 $retval &= Dba::write($sql); 1201 1202 return $retval; 1203 } 1204 1205 /** 1206 * update_400024 1207 * 1208 * Add song_count, album_count and album_group_count to artist 1209 */ 1210 public static function update_400024() 1211 { 1212 $retval = true; 1213 $sql = "ALTER TABLE `artist` ADD `song_count` smallint(5) unsigned DEFAULT 0 NULL;"; 1214 $retval &= Dba::write($sql); 1215 $sql = "ALTER TABLE `artist` ADD `album_count` smallint(5) unsigned DEFAULT 0 NULL;"; 1216 $retval &= Dba::write($sql); 1217 $sql = "ALTER TABLE `artist` ADD `album_group_count` smallint(5) unsigned DEFAULT 0 NULL;"; 1218 $retval &= Dba::write($sql); 1219 1220 return $retval; 1221 } 1222 1223 /** 1224 * update_500000 1225 * 1226 * Delete duplicate files in the song table 1227 */ 1228 public static function update_500000() 1229 { 1230 $retval = true; 1231 $sql = "DELETE `dupe` FROM `song` AS `dupe`, `song` AS `orig` WHERE `dupe`.`id` > `orig`.`id` AND `dupe`.`file` <=> `orig`.`file`;"; 1232 $retval &= Dba::write($sql); 1233 1234 return $retval; 1235 } 1236 1237 /** 1238 * update_500001 1239 * 1240 * Add `release_status`, `addition_time`, `catalog` to album table 1241 * Add `mbid`, `country`, `active` to label table 1242 * Fill the album `catalog` and `time` values using the song table 1243 * Fill the artist `album_count`, `album_group_count` and `song_count` values 1244 */ 1245 public static function update_500001() 1246 { 1247 $retval = true; 1248 $sql = "ALTER TABLE `album` ADD `release_status` varchar(32) DEFAULT NULL;"; 1249 $retval &= Dba::write($sql); 1250 $sql = "ALTER TABLE `album` ADD `addition_time` int(11) UNSIGNED DEFAULT 0 NULL;"; 1251 $retval &= Dba::write($sql); 1252 $sql = "ALTER TABLE `album` ADD `catalog` int(11) UNSIGNED NOT NULL DEFAULT '0';"; 1253 $retval &= Dba::write($sql); 1254 $sql = "ALTER TABLE `label` ADD `mbid` varchar(36) DEFAULT NULL;"; 1255 $retval &= Dba::write($sql); 1256 $sql = "ALTER TABLE `label` ADD `country` varchar(64) DEFAULT NULL;"; 1257 $retval &= Dba::write($sql); 1258 $sql = "ALTER TABLE `label` ADD `active` tinyint(1) UNSIGNED NOT NULL DEFAULT '1';"; 1259 $retval &= Dba::write($sql); 1260 $sql = "UPDATE `album`, (SELECT min(`song`.`catalog`) as `catalog`, `song`.`album` FROM `song` GROUP BY `song`.`album`) AS `song` SET `album`.`catalog` = `song`.`catalog` WHERE `album`.`catalog` != `song`.`catalog` AND `album`.`id` = `song`.`album`;"; 1261 $retval &= Dba::write($sql); 1262 $sql = "UPDATE `album`, (SELECT SUM(`song`.`time`) as `time`, `song`.`album` FROM `song` GROUP BY `song`.`album`) AS `song` SET `album`.`time` = `song`.`time` WHERE `album`.`id` = `song`.`album` AND `album`.`time` != `song`.`time`;"; 1263 $retval &= Dba::write($sql); 1264 $sql = "UPDATE `album`, (SELECT MIN(`song`.`addition_time`) AS `addition_time`, `song`.`album` FROM `song` GROUP BY `song`.`album`) AS `song` SET `album`.`addition_time` = `song`.`addition_time` WHERE `album`.`addition_time` != `song`.`addition_time` AND `song`.`album` = `album`.`id`;"; 1265 $retval &= Dba::write($sql); 1266 $sql = "UPDATE `artist`, (SELECT COUNT(DISTINCT `album`.`id`) AS `album_count`, `album_artist` FROM `album` LEFT JOIN `catalog` ON `catalog`.`id` = `album`.`catalog` WHERE `catalog`.`enabled` = '1' GROUP BY `album_artist`) AS `album` SET `artist`.`album_count` = `album`.`album_count` WHERE `artist`.`album_count` != `album`.`album_count` AND `artist`.`id` = `album`.`album_artist`;"; 1267 $retval &= Dba::write($sql); 1268 $sql = "UPDATE `artist`, (SELECT COUNT(DISTINCT CONCAT(COALESCE(`album`.`prefix`, ''), `album`.`name`, COALESCE(`album`.`album_artist`, ''), COALESCE(`album`.`mbid`, ''), COALESCE(`album`.`year`, ''))) AS `album_group_count`, `album_artist` FROM `album` LEFT JOIN `catalog` ON `catalog`.`id` = `album`.`catalog` WHERE `catalog`.`enabled` = '1' GROUP BY `album_artist`) AS `album` SET `artist`.`album_group_count` = `album`.`album_group_count` WHERE `artist`.`album_group_count` != `album`.`album_group_count` AND `artist`.`id` = `album`.`album_artist`;"; 1269 $retval &= Dba::write($sql); 1270 $sql = "UPDATE `artist`, (SELECT COUNT(`song`.`id`) AS `song_count`, `artist` FROM `song` LEFT JOIN `catalog` ON `catalog`.`id` = `song`.`catalog` WHERE `catalog`.`enabled` = '1' GROUP BY `artist`) AS `song` SET `artist`.`song_count` = `song`.`song_count` WHERE `artist`.`song_count` != `song`.`song_count` AND `artist`.`id` = `song`.`artist`;"; 1271 $retval &= Dba::write($sql); 1272 1273 return $retval; 1274 } 1275 1276 /** 1277 * update_500002 1278 * 1279 * Create `total_count` and `total_skip` to album, artist, song, video and podcast_episode tables 1280 * Fill counts into the columns 1281 */ 1282 public static function update_500002() 1283 { 1284 $retval = true; 1285 // tables which usually calculate a count 1286 $tables = ['album', 'artist', 'song', 'video', 'podcast_episode']; 1287 foreach ($tables as $type) { 1288 $sql = "ALTER TABLE `$type` ADD `total_count` int(11) UNSIGNED NOT NULL DEFAULT '0';"; 1289 $retval &= Dba::write($sql); 1290 $sql = "UPDATE `$type`, (SELECT COUNT(`object_count`.`object_id`) AS `total_count`, `object_id` FROM `object_count` WHERE `object_count`.`object_type` = '$type' AND `object_count`.`count_type` = 'stream' GROUP BY `object_count`.`object_id`) AS `object_count` SET `$type`.`total_count` = `object_count`.`total_count` WHERE `$type`.`total_count` != `object_count`.`total_count` AND `$type`.`id` = `object_count`.`object_id`;"; 1291 $retval &= Dba::write($sql); 1292 } 1293 // tables that also have a skip count 1294 $tables = ['song', 'video', 'podcast_episode']; 1295 foreach ($tables as $type) { 1296 $sql = "ALTER TABLE `$type` ADD `total_skip` int(11) UNSIGNED NOT NULL DEFAULT '0';"; 1297 $retval &= Dba::write($sql); 1298 $sql = "UPDATE `$type`, (SELECT COUNT(`object_count`.`object_id`) AS `total_skip`, `object_id` FROM `object_count` WHERE `object_count`.`object_type` = '$type' AND `object_count`.`count_type` = 'skip' GROUP BY `object_count`.`object_id`) AS `object_count` SET `$type`.`total_skip` = `object_count`.`total_skip` WHERE `$type`.`total_skip` != `object_count`.`total_skip` AND `$type`.`id` = `object_count`.`object_id`;"; 1299 $retval &= Dba::write($sql); 1300 } 1301 1302 return $retval; 1303 } 1304 1305 /** 1306 * update_500003 1307 * 1308 * add `catalog` to podcast_episode table 1309 */ 1310 public static function update_500003() 1311 { 1312 $retval = true; 1313 $sql = "ALTER TABLE `podcast_episode` DROP COLUMN `catalog`;"; 1314 $retval &= Dba::write($sql); 1315 $sql = "ALTER TABLE `podcast_episode` ADD `catalog` int(11) UNSIGNED NOT NULL DEFAULT '0';"; 1316 $retval &= Dba::write($sql); 1317 $sql = "UPDATE `podcast_episode`, (SELECT min(`podcast`.`catalog`) as `catalog`, `podcast`.`id` FROM `podcast` GROUP BY `podcast`.`id`) AS `podcast` SET `podcast_episode`.`catalog` = `podcast`.`catalog` WHERE `podcast_episode`.`catalog` != `podcast`.`catalog` AND `podcast_episode`.`podcast` = `podcast`.`id` AND `podcast`.`catalog` > 0;"; 1318 $retval &= Dba::write($sql); 1319 1320 return $retval; 1321 } 1322 1323 /** 1324 * update_500004 1325 * 1326 * Create catalog_map table and fill it with data 1327 */ 1328 public static function update_500004() 1329 { 1330 $retval = true; 1331 $collation = (AmpConfig::get('database_collation', 'utf8mb4_unicode_ci')); 1332 $charset = (AmpConfig::get('database_charset', 'utf8mb4')); 1333 $engine = ($charset == 'utf8mb4') ? 'InnoDB' : 'MYISAM'; 1334 // create the table 1335 $sql = "CREATE TABLE IF NOT EXISTS `catalog_map` (`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `catalog_id` int(11) UNSIGNED NOT NULL, `object_id` int(11) UNSIGNED NOT NULL, `object_type` varchar(16) CHARACTER SET $charset COLLATE $collation DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_catalog_map` (`object_id`, `object_type`, `catalog_id`)) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$collation;"; 1336 $retval &= Dba::write($sql); 1337 // fill the data 1338 $tables = ['album', 'song', 'video', 'podcast_episode']; 1339 foreach ($tables as $type) { 1340 $sql = "REPLACE INTO `catalog_map` (`catalog_id`, `object_type`, `object_id`) SELECT `$type`.`catalog`, '$type', `$type`.`id` FROM `$type` WHERE `$type`.`catalog` > 0;"; 1341 $retval &= Dba::write($sql); 1342 } 1343 // artist is a special one as it can be across multiple tables 1344 $sql = "REPLACE INTO `catalog_map` (`catalog_id`, `object_type`, `object_id`) SELECT `song`.`catalog`, 'artist', `artist`.`id` FROM `artist` LEFT JOIN `song` ON `song`.`artist` = `artist`.`id` WHERE `song`.`catalog` > 0;"; 1345 $retval &= Dba::write($sql); 1346 $sql = "REPLACE INTO `catalog_map` (`catalog_id`, `object_type`, `object_id`) SELECT `album`.`catalog`, 'artist', `artist`.`id` FROM `artist` LEFT JOIN `album` ON `album`.`album_artist` = `artist`.`id` WHERE `album`.`catalog` > 0;"; 1347 $retval &= Dba::write($sql); 1348 1349 return $retval; 1350 } 1351 1352 /** 1353 * update_500005 1354 * 1355 * Add song_count, artist_count to album 1356 */ 1357 public static function update_500005() 1358 { 1359 $retval = true; 1360 $sql = "ALTER TABLE `album` ADD `song_count` smallint(5) unsigned DEFAULT 0 NULL;"; 1361 $retval &= Dba::write($sql); 1362 $sql = "ALTER TABLE `album` ADD `artist_count` smallint(5) unsigned DEFAULT 0 NULL;"; 1363 $retval &= Dba::write($sql); 1364 $sql = "UPDATE `album`, (SELECT COUNT(`song`.`id`) AS `song_count`, `album` FROM `song` LEFT JOIN `catalog` ON `catalog`.`id` = `song`.`catalog` WHERE `catalog`.`enabled` = '1' GROUP BY `album`) AS `song` SET `album`.`song_count` = `song`.`song_count` WHERE `album`.`song_count` != `song`.`song_count` AND `album`.`id` = `song`.`album`;"; 1365 $retval &= Dba::write($sql); 1366 $sql = "UPDATE `album`, (SELECT COUNT(DISTINCT(`song`.`artist`)) AS `artist_count`, `album` FROM `song` LEFT JOIN `catalog` ON `catalog`.`id` = `song`.`catalog` WHERE `catalog`.`enabled` = '1' GROUP BY `album`) AS `song` SET `album`.`artist_count` = `song`.`artist_count` WHERE `album`.`artist_count` != `song`.`artist_count` AND `album`.`id` = `song`.`album`;"; 1367 $retval &= Dba::write($sql); 1368 $sql = "REPLACE INTO `update_info` SET `key`= 'album_group', `value`= (SELECT COUNT(DISTINCT(`album`.`id`)) AS `count` FROM `album` WHERE `id` in (SELECT MIN(`id`) from `album` GROUP BY `album`.`prefix`, `album`.`name`, `album`.`album_artist`, `album`.`release_type`, `album`.`release_status`, `album`.`mbid`, `album`.`year`, `album`.`original_year`));"; 1369 $retval &= Dba::write($sql); 1370 1371 return $retval; 1372 } 1373 1374 /** 1375 * update_500006 1376 * 1377 * Add user_playlist table 1378 */ 1379 public static function update_500006() 1380 { 1381 $retval = true; 1382 $collation = (AmpConfig::get('database_collation', 'utf8mb4_unicode_ci')); 1383 $charset = (AmpConfig::get('database_charset', 'utf8mb4')); 1384 $engine = ($charset == 'utf8mb4') ? 'InnoDB' : 'MYISAM'; 1385 $sql = "CREATE TABLE IF NOT EXISTS `user_playlist` (`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `user` int(11) DEFAULT NULL, `object_type` enum('song','live_stream','video','podcast_episode') CHARACTER SET $charset COLLATE $collation DEFAULT NULL, `object_id` int(11) UNSIGNED NOT NULL DEFAULT '0', `track` smallint(6) DEFAULT NULL, `current_track` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, `current_time` smallint(5) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`),KEY `user` (`user`)) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$collation;"; 1386 $retval &= Dba::write($sql); 1387 $sql = "CREATE TABLE IF NOT EXISTS `user_data` (`user` int(11) DEFAULT NULL, `key` varchar(128) CHARACTER SET $charset COLLATE $collation DEFAULT NULL, `value` varchar(255) CHARACTER SET $charset COLLATE $collation DEFAULT NULL, KEY `user` (`user`), KEY `key` (`key`)) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$collation;"; 1388 $retval &= Dba::write($sql); 1389 1390 return $retval; 1391 } 1392 1393 /** 1394 * update_500007 1395 * 1396 * Add a 'Browse' category to interface preferences 1397 * Add ui option ('show_license') for hiding license column in song rows 1398 */ 1399 public static function update_500007() 1400 { 1401 $retval = true; 1402 $sql = "UPDATE `preference` SET `preference`.`subcatagory` = 'browse' WHERE `preference`.`name` IN ('show_played_times', 'browse_filter', 'libitem_browse_alpha', 'show_skipped_times')"; 1403 $retval &= Dba::write($sql); 1404 1405 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('show_license', '1', 'Show Licence', 25, 'boolean', 'interface', 'browse')"; 1406 $retval &= Dba::write($sql); 1407 $row_id = Dba::insert_id(); 1408 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '1')"; 1409 $retval &= Dba::write($sql, array($row_id)); 1410 1411 return $retval; 1412 } 1413 1414 /** 1415 * update_500008 1416 * 1417 * Add filter_user to catalog table, set unique on user_data 1418 */ 1419 public static function update_500008() 1420 { 1421 $retval = true; 1422 $sql = "ALTER TABLE `catalog` ADD `filter_user` int(11) unsigned DEFAULT 0 NOT NULL;"; 1423 $retval &= Dba::write($sql); 1424 1425 $tables = ['podcast', 'live_stream']; 1426 foreach ($tables as $type) { 1427 $sql = "REPLACE INTO `catalog_map` (`catalog_id`, `object_type`, `object_id`) SELECT `$type`.`catalog`, '$type', `$type`.`id` FROM `$type` WHERE `$type`.`catalog` > 0;"; 1428 $retval &= Dba::write($sql); 1429 } 1430 $sql = "ALTER TABLE `user_data` ADD UNIQUE `unique_data` (`user`,`key`);"; 1431 $retval &= Dba::write($sql); 1432 1433 return $retval; 1434 } 1435 1436 /** 1437 * update_500009 1438 * 1439 * Add ui option ('use_original_year') Browse by Original Year for albums (falls back to Year) 1440 */ 1441 public static function update_500009() 1442 { 1443 $retval = true; 1444 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('use_original_year', '0', 'Browse by Original Year for albums (falls back to Year)', 25, 'boolean', 'interface', 'browse')"; 1445 $retval &= Dba::write($sql); 1446 $row_id = Dba::insert_id(); 1447 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 1448 $retval &= Dba::write($sql, array($row_id)); 1449 1450 return $retval; 1451 } 1452 1453 /** 1454 * update_500010 1455 * 1456 * Add ui option ('hide_single_artist') Hide the Song Artist column for Albums with one Artist 1457 */ 1458 public static function update_500010() 1459 { 1460 $retval = true; 1461 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('hide_single_artist', '0', 'Hide the Song Artist column for Albums with one Artist', 25, 'boolean', 'interface', 'browse')"; 1462 $retval &= Dba::write($sql); 1463 $row_id = Dba::insert_id(); 1464 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 1465 $retval &= Dba::write($sql, array($row_id)); 1466 1467 return $retval; 1468 } 1469 1470 /** 1471 * update_500011 1472 * 1473 * Add `total_count` to podcast table and fill counts into the column 1474 */ 1475 public static function update_500011() 1476 { 1477 $retval = true; 1478 $sql = "ALTER TABLE `podcast` ADD `total_count` int(11) UNSIGNED NOT NULL DEFAULT '0';"; 1479 $retval &= Dba::write($sql); 1480 $sql = "UPDATE `podcast`, (SELECT SUM(`podcast_episode`.`total_count`) AS `total_count`, `podcast` FROM `podcast_episode` GROUP BY `podcast_episode`.`podcast`) AS `object_count` SET `podcast`.`total_count` = `object_count`.`total_count` WHERE `podcast`.`total_count` != `object_count`.`total_count` AND `podcast`.`id` = `object_count`.`podcast`;"; 1481 $retval &= Dba::write($sql); 1482 1483 return $retval; 1484 } 1485 1486 /** 1487 * update_500012 1488 * 1489 * Move user bandwidth calculations out of the user format function into the user_data table 1490 */ 1491 public static function update_500012() 1492 { 1493 $retval = true; 1494 $sql = "SELECT `id` FROM `user`"; 1495 $db_users = Dba::read($sql); 1496 $user_list = array(); 1497 while ($results = Dba::fetch_assoc($db_users)) { 1498 $user_list[] = (int)$results['id']; 1499 } 1500 // Calculate their total Bandwidth Usage 1501 foreach ($user_list as $user_id) { 1502 $params = array($user_id); 1503 $total = 0; 1504 $sql_s = "SELECT SUM(`song`.`size`) as `size` FROM `object_count` LEFT JOIN `song` ON `song`.`id`=`object_count`.`object_id` AND `object_count`.`count_type` = 'stream' AND `object_count`.`object_type` = 'song' AND `object_count`.`user` = ?;"; 1505 $db_s = Dba::read($sql_s, $params); 1506 while ($results = Dba::fetch_assoc($db_s)) { 1507 $total = $total + (int)$results['size']; 1508 } 1509 $sql_v = "SELECT SUM(`video`.`size`) as `size` FROM `object_count` LEFT JOIN `video` ON `video`.`id`=`object_count`.`object_id` AND `object_count`.`count_type` = 'stream' AND `object_count`.`object_type` = 'video' AND `object_count`.`user` = ?;"; 1510 $db_v = Dba::read($sql_v, $params); 1511 while ($results = Dba::fetch_assoc($db_v)) { 1512 $total = $total + (int)$results['size']; 1513 } 1514 $sql_p = "SELECT SUM(`podcast_episode`.`size`) as `size` FROM `object_count`LEFT JOIN `podcast_episode` ON `podcast_episode`.`id`=`object_count`.`object_id` AND `object_count`.`count_type` = 'stream' AND `object_count`.`object_type` = 'podcast_episode' AND `object_count`.`user` = ?;"; 1515 $db_p = Dba::read($sql_p, $params); 1516 while ($results = Dba::fetch_assoc($db_p)) { 1517 $total = $total + (int)$results['size']; 1518 } 1519 $retval &= Dba::write("REPLACE INTO `user_data` SET `user`= ?, `key`= ?, `value`= ?;", array($user_id, 'play_size', $total)); 1520 } 1521 1522 return $retval; 1523 } 1524 1525 /** 1526 * update_500013 1527 * 1528 * Add tables for tracking deleted files. (deleted_song, deleted_video, deleted_podcast_episode) 1529 * Add username to the playlist table to stop pulling user all the time 1530 */ 1531 public static function update_500013() 1532 { 1533 $retval = true; 1534 $collation = (AmpConfig::get('database_collation', 'utf8mb4_unicode_ci')); 1535 $charset = (AmpConfig::get('database_charset', 'utf8mb4')); 1536 $engine = ($charset == 'utf8mb4') ? 'InnoDB' : 'MYISAM'; 1537 // deleted_song (id, addition_time, delete_time, title, file, catalog, total_count, total_skip, album, artist) 1538 $sql = "CREATE TABLE IF NOT EXISTS `deleted_song` (`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `addition_time` int(11) UNSIGNED DEFAULT '0', `delete_time` int(11) UNSIGNED DEFAULT '0', `title` varchar(255) CHARACTER SET $charset COLLATE $collation DEFAULT NULL, `file` varchar(4096) CHARACTER SET $charset COLLATE $collation DEFAULT NULL, `catalog` int(11) UNSIGNED NOT NULL DEFAULT '0', `total_count` int(11) UNSIGNED NOT NULL DEFAULT '0', `total_skip` int(11) UNSIGNED NOT NULL DEFAULT '0', `update_time` int(11) UNSIGNED DEFAULT '0', `album` int(11) UNSIGNED NOT NULL DEFAULT '0', `artist` int(11) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$collation;"; 1539 $retval &= Dba::write($sql); 1540 1541 // deleted_video (id, addition_time, delete_time, title, file, catalog, total_count, total_skip) 1542 $sql = "CREATE TABLE IF NOT EXISTS `deleted_video` (`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `addition_time` int(11) UNSIGNED NOT NULL, `delete_time` int(11) UNSIGNED NOT NULL, `title` varchar(255) CHARACTER SET $charset COLLATE $collation DEFAULT NULL, `file` varchar(4096) CHARACTER SET $charset COLLATE $collation DEFAULT NULL, `catalog` int(11) UNSIGNED NOT NULL, `total_count` int(11) UNSIGNED NOT NULL DEFAULT '0', `total_skip` int(11) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$collation;"; 1543 $retval &= Dba::write($sql); 1544 1545 // deleted_podcast_episode (id, addition_time, delete_time, title, file, catalog, total_count, total_skip, podcast) 1546 $sql = "CREATE TABLE IF NOT EXISTS `deleted_podcast_episode` (`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `addition_time` int(11) UNSIGNED NOT NULL, `delete_time` int(11) UNSIGNED NOT NULL, `title` varchar(255) CHARACTER SET $charset COLLATE $collation DEFAULT NULL, `file` varchar(4096) CHARACTER SET $charset COLLATE $collation DEFAULT NULL, `catalog` int(11) UNSIGNED NOT NULL, `total_count` int(11) UNSIGNED NOT NULL DEFAULT '0', `total_skip` int(11) UNSIGNED NOT NULL DEFAULT '0', `podcast` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=$engine DEFAULT CHARSET=$charset COLLATE=$collation;"; 1547 $retval &= Dba::write($sql); 1548 1549 // add username to playlist and searches to stop calling the objects all the time 1550 $sql = "ALTER TABLE `playlist` ADD `username` varchar(255) CHARACTER SET $charset COLLATE $collation DEFAULT NULL;"; 1551 $retval &= Dba::write($sql); 1552 $sql = "ALTER TABLE `search` ADD `username` varchar(255) CHARACTER SET $charset COLLATE $collation DEFAULT NULL;"; 1553 $retval &= Dba::write($sql); 1554 1555 // fill the data 1556 $sql = "UPDATE `playlist`, (SELECT `id`, `username` FROM `user`) AS `user` SET `playlist`.`username` = `user`.`username` WHERE `playlist`.`user` = `user`.`id`;"; 1557 $retval &= Dba::write($sql); 1558 $sql = "UPDATE `search`, (SELECT `id`, `username` FROM `user`) AS `user` SET `search`.`username` = `user`.`username` WHERE `search`.`user` = `user`.`id`;"; 1559 $retval &= Dba::write($sql); 1560 // system playlists are also possible 1561 $sql = "UPDATE `playlist` SET `playlist`.`username` = ? WHERE `playlist`.`user` = -1;"; 1562 $retval &= Dba::write($sql, array(T_('System'))); 1563 $sql = "UPDATE `search` SET `search`.`username` = ? WHERE `search`.`user` = -1;"; 1564 $retval &= Dba::write($sql, array(T_('System'))); 1565 1566 return $retval; 1567 } 1568 1569 /** 1570 * update_500014 1571 * 1572 * Add `episodes` to podcast table to track episode count 1573 */ 1574 public static function update_500014() 1575 { 1576 $retval = true; 1577 $sql = "ALTER TABLE `podcast` ADD `episodes` int(11) UNSIGNED NOT NULL DEFAULT '0';"; 1578 $retval &= Dba::write($sql); 1579 $sql = "UPDATE `podcast`, (SELECT COUNT(`podcast_episode`.`id`) AS `episodes`, `podcast` FROM `podcast_episode` GROUP BY `podcast_episode`.`podcast`) AS `episode_count` SET `podcast`.`episodes` = `episode_count`.`episodes` WHERE `podcast`.`episodes` != `episode_count`.`episodes` AND `podcast`.`id` = `episode_count`.`podcast`;"; 1580 $retval &= Dba::write($sql); 1581 1582 return $retval; 1583 } 1584 1585 /** 1586 * update_500015 1587 * 1588 * Add ui option ('hide_genres') Hide the Genre column in browse table rows 1589 */ 1590 public static function update_500015() 1591 { 1592 $retval = true; 1593 $sql = "INSERT INTO `preference` (`name`, `value`, `description`, `level`, `type`, `catagory`, `subcatagory`) VALUES ('hide_genres', '0', 'Hide the Genre column in browse table rows', 25, 'boolean', 'interface', 'browse')"; 1594 $retval &= Dba::write($sql); 1595 $row_id = Dba::insert_id(); 1596 $sql = "INSERT INTO `user_preference` VALUES (-1, ?, '0')"; 1597 $retval &= Dba::write($sql, array($row_id)); 1598 1599 return $retval; 1600 } 1601} // end update.class 1602