diff options
Diffstat (limited to 'lib/class')
| -rw-r--r-- | lib/class/album.class.php | 15 | ||||
| -rw-r--r-- | lib/class/artist.class.php | 15 | ||||
| -rw-r--r-- | lib/class/browse.class.php | 185 | ||||
| -rw-r--r-- | lib/class/dba.class.php | 21 | ||||
| -rw-r--r-- | lib/class/genre.class.php | 15 | ||||
| -rw-r--r-- | lib/class/rating.class.php | 17 | ||||
| -rw-r--r-- | lib/class/song.class.php | 95 |
7 files changed, 281 insertions, 82 deletions
diff --git a/lib/class/album.class.php b/lib/class/album.class.php index 5c810ca6..eef95831 100644 --- a/lib/class/album.class.php +++ b/lib/class/album.class.php @@ -90,14 +90,25 @@ class Album { return $album; } // construct_from_array - + public static function build_cache($ids, $fields='*') { + $idlist = '(' . implode(',', $ids) . ')'; + $sql = "SELECT $fields FROM album WHERE id in $idlist"; + $db_results = Dba::query($sql); + global $album_cache; + $album_cache = array(); + while ($results = Dba::fetch_assoc($db_results)) { + $album_cache[intval($results['id'])] = $results; + } + } /** * _get_info * This is a private function that pulls the album * from the database */ private function _get_info() { - + global $album_cache; + if (isset($album_cache[intval($this->id)])) + return $album_cache[intval($this->id)]; // Just get the album information $sql = "SELECT * FROM `album` WHERE `id`='" . $this->id . "'"; $db_results = Dba::query($sql); diff --git a/lib/class/artist.class.php b/lib/class/artist.class.php index 38361194..73d5bef7 100644 --- a/lib/class/artist.class.php +++ b/lib/class/artist.class.php @@ -76,13 +76,24 @@ class Artist { return $artist; } // construct_from_array - + public static function build_cache($ids, $fields='*') { + $idlist = '(' . implode(',', $ids) . ')'; + $sql = "SELECT $fields FROM artist WHERE id in $idlist"; + $db_results = Dba::query($sql); + global $artist_cache; + $artist_cache = array(); + while ($results = Dba::fetch_assoc($db_results)) { + $artist_cache[intval($results['id'])] = $results; + } + } /** * _get_info * get's the vars for $this out of the database taken from the object */ private function _get_info() { - + global $artist_cache; + if (isset($artist_cache[intval($this->id)])) + return $artist_cache[intval($this->id)]; /* Grab the basic information from the catalog and return it */ $sql = "SELECT * FROM artist WHERE id='" . Dba::escape($this->id) . "'"; $db_results = Dba::query($sql); diff --git a/lib/class/browse.class.php b/lib/class/browse.class.php index c0135679..0afbf8f7 100644 --- a/lib/class/browse.class.php +++ b/lib/class/browse.class.php @@ -71,7 +71,22 @@ class Browse { $_SESSION['browse']['filter'][$key] = 1; } break; + case 'tag': + //var_dump($value); + if (is_array($value)) + $_SESSION['browse']['filter'][$key] = $value; + else if (is_numeric($value)) + $_SESSION['browse']['filter'][$key] = + array($value); + else + $_SESSION['browse']['filter'][$key] = array(); + break; + case 'artist': + case 'album': + $_SESSION['browse']['filter'][$key] = $value; + break; case 'min_count': + case 'unplayed': case 'rated': @@ -346,22 +361,25 @@ class Browse { // First we need to get the SQL statement we are going to run // This has to run against any possible filters (dependent on type) $sql = self::get_sql(); - $db_results = Dba::query($sql); $results = array(); - - while ($data = Dba::fetch_assoc($db_results)) { + while ($data = Dba::fetch_assoc($db_results)) + $results[] = $data; + var_dump($results); + $results = self::post_process($results); + $filtered = array(); + foreach ($results as $data) { // Make sure that this object passes the logic filter if (self::logic_filter($data['id'])) { - $results[] = $data['id']; + $filtered[] = $data['id']; } } // end while // Save what we've found and then return it - self::save_objects($results); + self::save_objects($filtered); - return $results; + return $filtered; } // get_objects @@ -399,35 +417,51 @@ class Browse { private static function get_base_sql() { // Get our base SQL must always return ID + $includetags = (is_array($_SESSION['browse']['filter']['tag']) + && sizeof($_SESSION['browse']['filter']['tag'])); + $megajoin = ''; + if ($includetags) + $megajoin = ', tags.id as tagid'; + $megajoin .= ' FROM song, artist, album '; + if ($includetags) + $megajoin.= ', tags, tag_map '; + $megajoin .= 'WHERE song.album = album.id AND + song.artist = artist.id AND '; + if ($includetags) + $megajoin .= ' tag_map.tag_id = tags.id AND '; + $w = " WHERE 1=1 AND "; switch ($_SESSION['browse']['type']) { case 'album': - $sql = "SELECT `album`.`id` FROM `album` "; + $sql = "SELECT DISTINCT `album`.`id` " + .$megajoin; break; case 'artist': - $sql = "SELECT `artist`.`id` FROM `artist` "; + $sql = "SELECT DISTINCT `artist`.`id` " + .$megajoin; break; case 'genre': - $sql = "SELECT `genre`.`id` FROM `genre` "; + $sql = "SELECT `genre`.`id` FROM `genre` ".$w; break; case 'user': - $sql = "SELECT `user`.`id` FROM `user` "; + $sql = "SELECT `user`.`id` FROM `user` ".$w; break; case 'live_stream': - $sql = "SELECT `live_stream`.`id` FROM `live_stream` "; + $sql = "SELECT `live_stream`.`id` FROM `live_stream` ".$w; break; case 'playlist': - $sql = "SELECT `playlist`.`id` FROM `playlist` "; + $sql = "SELECT `playlist`.`id` FROM `playlist` ".$w; break; case 'flagged': - $sql = "SELECT `flagged`.`id` FROM `flagged` "; + $sql = "SELECT `flagged`.`id` FROM `flagged` " + .$w; break; case 'shoutbox': - $sql = "SELECT `user_shout`.`id` FROM `user_shout` "; + $sql = "SELECT `user_shout`.`id` FROM `user_shout` ".$w; break; case 'playlist_song': case 'song': default: - $sql = "SELECT `song`.`id` FROM `song` "; + $sql = "SELECT DISTINCT `song`.`id` ".$megajoin; break; } // end base sql @@ -450,17 +484,14 @@ class Browse { // Foreach the filters and see if any of them can be applied // as part of a where statement in this sql (type dependent) - $where_sql = "WHERE 1=1 AND "; + $where_sql = ""; foreach ($_SESSION['browse']['filter'] as $key=>$value) { $where_sql .= self::sql_filter($key,$value); } // end foreach - - $where_sql = rtrim($where_sql,'AND '); - $sql .= $where_sql; } // if filters - + $sql = rtrim($sql,'AND '); // Now Add the Order $order_sql = " ORDER BY "; @@ -475,11 +506,25 @@ class Browse { $order_sql = rtrim($order_sql,","); $sql = $sql . $order_sql; - + var_dump($sql); return $sql; } // get_sql - + private static function post_process($results) + { + $tags = $_SESSION['browse']['filter']['tag']; + if (!is_array($tags) || sizeof($tags) < 2) + return $results; + $cnt = sizeof($tags); + $ar = array(); + foreach($results as $row) + $ar[$row['id']]++; + $res = array(); + foreach($ar as $k=>$v) + if ($v >= $cnt) + $res[] = array('id' => $k); + return $res; + } /** * sql_filter * This takes a filter name and value and if it is possible @@ -489,7 +534,29 @@ class Browse { private static function sql_filter($filter,$value) { $filter_sql = ''; - + //tag + if ($filter == 'tag' && ( + $_SESSION['browse']['type'] == 'song' + || $_SESSION['browse']['type'] == 'artist' + || $_SESSION['browse']['type'] == 'album' + )) { + //var_dump($value); + if (is_array($value) && sizeof($value)) + $vals = '(' . implode(',',$value) . ')'; + else if (is_integer($value)) + $vals = '('.$value.')'; + else return ''; + $or_sql = ''; + $object_type = $_SESSION['browse']['type']; + if ($object_type == 'artist' || $object_type == 'album') + $or_sql=" or (tag_map.object_id = song.id AND + tag_map.object_type='song' )"; + if ($object_type == 'artist') + $or_sql.= " or (tag_map.object_id = album.id AND + tag_map.object_type='album' )"; + $filter_sql = " `tags`.`id` in $vals AND + (($object_type.id = `tag_map`.`object_id` AND tag_map.object_type='$object_type') $or_sql) AND "; + } if ($_SESSION['browse']['type'] == 'song') { switch($filter) { case 'alpha_match': @@ -498,6 +565,16 @@ class Browse { case 'unplayed': $filter_sql = " `song`.`played`='0' AND "; break; + case 'album': + if ($value) + $filter_sql = " `album`.`id` = '". + Dba::escape($value) . "' AND "; + break; + case 'artist': + if ($value) + $filter_sql = " `artist`.`id` = '". + Dba::escape($value) . "' AND "; + break; default: // Rien a faire break; @@ -511,6 +588,11 @@ class Browse { case 'min_count': break; + case 'artist': + if ($value) + $filter_sql = " `artist`.`id` = '". + Dba::escape($value) . "' AND "; + break; default: // Rien a faire break; @@ -692,7 +774,7 @@ class Browse { * and requires the correct template based on the * type that we are currently browsing */ - public static function show_objects($object_ids='') { + public static function show_objects($object_ids='', $ajax=false) { $object_ids = $object_ids ? $object_ids : self::get_saved(); @@ -701,7 +783,7 @@ class Browse { // Limit is based on the users preferences $limit = Config::get('offset_limit') ? Config::get('offset_limit') : '25'; - + $all_ids = $object_ids; if (count($object_ids) > self::$start) { $object_ids = array_slice($object_ids,self::$start,$limit); } @@ -714,13 +796,21 @@ class Browse { // Load any additional object we need for this $extra_objects = self::get_supplemental_objects(); - + var_dump($object_ids); foreach ($extra_objects as $class_name => $id) { ${$class_name} = new $class_name($id); } + + if (!$ajax && in_array($_SESSION['browse']['type'], + array('artist','album','song'))) { + $tagcloudHead = "Matching tags"; + $tagcloudList = + TagCloud::get_tags($_SESSION['browse']['type'], $all_ids); + require_once Config::get('prefix') . '/templates/show_tagcloud.inc.php'; + } + Dba::show_profile(); Ajax::start_container('browse_content'); - // Switch on the type of browsing we're doing switch ($_SESSION['browse']['type']) { case 'song': @@ -821,19 +911,20 @@ class Browse { // If there's nothing there don't do anything if (!count($objects)) { return false; } + $type = $_SESSION['browse']['type']; + $where_sql .= "`$type`.`id` IN ("; - $where_sql .= "`id` IN ("; - - foreach ($objects as $object_id) { - $object_id = Dba::escape($object_id); - $where_sql .= "'$object_id',"; + foreach ($objects as $object_id) { + $object_id = Dba::escape($object_id); + $where_sql .= "'$object_id',"; } $where_sql = rtrim($where_sql,','); - - $where_sql .= ")"; - $sql = self::get_base_sql() . ' WHERE ' . $where_sql; - } + $where_sql .= ")"; + + $sql = self::get_base_sql(); + $sql .= $where_sql; + } $order_sql = "ORDER BY "; @@ -871,5 +962,27 @@ class Browse { self::$start = intval($_SESSION['browse'][self::$type]['start']); } // _auto_init + + public static function set_filter_from_request($r) + { + //var_dump($r); + foreach ($r as $k=>$v) { + //reinterpret v as a list of int + $vl = explode(',', $v); + //var_dump($vl); + $ok = 1; + foreach($vl as $i) { + if (!is_numeric($i)) { + $ok = 0; + break; + } + } + if ($ok) + if (sizeof($vl) == 1) + Browse::set_filter($k, $vl[0]); + else + Browse::set_filter($k, $vl); + } + } } // browse diff --git a/lib/class/dba.class.php b/lib/class/dba.class.php index 62df46fe..6515b71f 100644 --- a/lib/class/dba.class.php +++ b/lib/class/dba.class.php @@ -53,7 +53,10 @@ class Dba { * The mysql_query function */ public static function query($sql) { - + /*if ($_REQUEST['profiling']) { + $sql = rtrim($sql, '; '); + $sql .= ' SQL_NO_CACHE'; + }*/ // Run the query $resource = mysql_query($sql,self::dbh()); debug_event('Query',$sql,'6'); @@ -188,10 +191,26 @@ class Dba { $select_db = mysql_select_db($database,$dbh); if (!$select_db) { debug_event('Database','Error unable to select ' . $database . ' error ' . mysql_error(),'1'); } + if ($_REQUEST['profiling']) { + mysql_query('set profiling=1', $dbh); + mysql_query('set profiling_history_size=50', $dbh); + mysql_query('set query_cache_type=0', $dbh); + } return $dbh; } // _connect + public static function show_profile() { + if ($_REQUEST['profiling']) { + print '<br/>Profiling data: <br/>'; + $res = Dba::query('show profiles'); + print '<table>'; + while ($r = Dba::fetch_row($res)) { + print '<tr><td>' . implode('</td><td>', $r) . '</td></tr>'; + } + print '</table>'; + } + } /** * dbh * This is called by the class to return the database handle diff --git a/lib/class/genre.class.php b/lib/class/genre.class.php index 633e8f9d..468d727f 100644 --- a/lib/class/genre.class.php +++ b/lib/class/genre.class.php @@ -42,13 +42,24 @@ class Genre { } // Genre - + public static function build_cache($ids, $fields='*') { + $idlist = '(' . implode(',', $ids) . ')'; + $sql = "SELECT $fields FROM genre WHERE id in $idlist"; + $db_results = Dba::query($sql); + global $genre_cache; + $genre_cache = array(); + while ($results = Dba::fetch_assoc($db_results)) { + $genre_cache[intval($results['id'])] = $results; + } + } /** * Private Get Info * This simply returns the information for this genre */ private function _get_info() { - + global $genre_cache; + if (isset($genre_cache[intval($this->id)])) + return $genre_cache[intval($this->id)]; $sql = "SELECT * FROM `genre` WHERE `id`='$this->id'"; $db_results = Dba::query($sql); diff --git a/lib/class/rating.class.php b/lib/class/rating.class.php index 51555c10..0519672e 100644 --- a/lib/class/rating.class.php +++ b/lib/class/rating.class.php @@ -56,14 +56,25 @@ class Rating { return true; } // Constructor - + public static function build_cache($type, $ids) { + $idlist = '(' . implode(',', $ids) . ')'; + $sql = "SELECT `rating`, object_id FROM `rating` WHERE `user`='$user_id' AND `object_id` in $idlist AND `object_type`='$type'"; + global $rating_cache; + $rating_cache = array(); + $db_results = Dba::query($sql); + while ($results = Dba::fetch_assoc($db_results)) { + $rating_cache[intval($results['object_id'])] = $results; + } + } /** * get_user * Get the user's rating this is based off the currently logged * in user. It returns the value */ - public function get_user($user_id) { - + public function get_user($user_id) { + global $rating_cache; + if (isset($rating_cache[intval($this->id)])); + return $rating_cache[intval($this->id)]['rating']; $user_id = Dba::escape($user_id); $sql = "SELECT `rating` FROM `rating` WHERE `user`='$user_id' AND `object_id`='$this->id' AND `object_type`='$this->type'"; diff --git a/lib/class/song.class.php b/lib/class/song.class.php index a0d2b2cf..ef2bfd8e 100644 --- a/lib/class/song.class.php +++ b/lib/class/song.class.php @@ -70,14 +70,56 @@ class Song { return true; } // constructor - + public static function build_cache($ids) + { + $idlist = '(' . implode(',', $ids) . ')'; + + // Song data cache + $sql = "SELECT song.id,file,catalog,album,year,artist,". + "title,bitrate,rate,mode,size,time,track,genre,played,song.enabled,update_time,". + "addition_time FROM `song` WHERE `song`.`id` in + $idlist"; + $db_results = Dba::query($sql); + global $song_cache; + $song_cache = array(); + while ($results = Dba::fetch_assoc($db_results)) + { + $song_cache[intval($results['id'])] = $results; + } + + // Extra sound data cache + global $song_data_cache; + $song_data_cache = array(); + $sql = "SELECT * FROM song_data WHERE song_id in $idlist"; + $db_results = Dba::query($sql); + while ($results = Dba::fetch_assoc($db_results)) + { + $song_data_cache[intval($results['song_id'])] = $results; + } + + // Get all artist, album, genre ids. + $artists = array(); + $albums = array(); + $genre = array(); + foreach ($song_cache as $i) + { + $artists[$i['artist']] = 1; + $albums[$i['album']] = 1; + $genre[$i['genre']] = 1; + } + Artist::build_cache(array_keys($artists), 'id,name'); + Album::build_cache(array_keys($albums), 'id,name'); + Genre::build_cache(array_keys($genre), 'id,name'); + } /*! @function _get_info @discussion get's the vars for $this out of the database @param $this->id Taken from the object */ private function _get_info() { - + global $song_cache; + if (isset($song_cache[intval($this->id)])) + return $song_cache[intval($this->id)]; /* Grab the basic information from the catalog and return it */ $sql = "SELECT song.id,file,catalog,album,year,artist,". "title,bitrate,rate,mode,size,time,track,genre,played,song.enabled,update_time,". @@ -97,7 +139,9 @@ class Song { * current object */ public function _get_ext_info() { - + global $song_data_cache; + if (isset($song_data_cache[intval($this->id)])) + return $song_data_cache[intval($this->id)]; $sql = "SELECT * FROM song_data WHERE `song_id`='" . Dba::escape($this->id) . "'"; $db_results = Dba::query($sql); @@ -203,21 +247,12 @@ class Song { * gets the name of $this->album, allows passing of id */ function get_album_name($album_id=0) { - if (!$album_id) { $album_id = $this->album; } - - $sql = "SELECT `name`,`prefix` FROM `album` WHERE `id`='" . Dba::escape($album_id) . "'"; - $db_results = Dba::query($sql); - - $results = Dba::fetch_assoc($db_results); - - if ($results['prefix']) { - return $results['prefix'] . " " .$results['name']; - } - else { - return $results['name']; - } - + $album = new Album($album_id); + if ($album->prefix) + return $album->prefix . " " . $album->name; + else + return $album->name; } // get_album_name /** @@ -227,18 +262,11 @@ class Song { function get_artist_name($artist_id=0) { if (!$artist_id) { $artist_id = $this->artist; } - - $sql = "SELECT name,prefix FROM artist WHERE id='" . Dba::escape($artist_id) . "'"; - $db_results = Dba::query($sql); - - $results = Dba::fetch_assoc($db_results); - - if ($results['prefix']) { - return $results['prefix'] . " " . $results['name']; - } - else { - return $results['name']; - } + $artist = new Artist($artist_id); + if ($artist->prefix) + return $artist->prefix . " " . $artist->name; + else + return $artist->name; } // get_album_name @@ -250,13 +278,8 @@ class Song { function get_genre_name($genre_id=0) { if (!$genre_id) { $genre_id = $this->genre; } - - $sql = "SELECT name FROM genre WHERE id='" . Dba::escape($genre_id) . "'"; - $db_results = Dba::query($sql); - - $results = Dba::fetch_assoc($db_results); - - return $results['name']; + $genre = new Genre($genre_id); + return $genre->name; } // get_genre_name |