"Fossies" - the Fresh Open Source Software Archive

Member "ampache-5.0.0/src/Module/System/Update.php" (31 Aug 2021, 79378 Bytes) of package /linux/www/ampache-5.0.0.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PHP source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. For more information about "Update.php" see the Fossies "Dox" file reference documentation.

    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 
   23 declare(strict_types=0);
   24 
   25 namespace Ampache\Module\System;
   26 
   27 use Ampache\Config\AmpConfig;
   28 use 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  */
   39 class 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