/**
* song_to_sql
* Handles the generation of the SQL for song searches.
*/
private function song_to_sql()
{
$sql_logic_operator = $this->logic_operator;
$where = array();
$table = array();
$join = array();
$group = array();
$having = array();
$join['tag'] = array();
foreach ($this->rules as $rule) {
$type = $this->name_to_basetype($rule[0]);
$operator = array();
foreach ($this->basetypes[$type] as $op) {
if ($op['name'] == $rule[1]) {
$operator = $op;
break;
}
}
$input = $this->_mangle_data($rule[2], $type, $operator);
$sql_match_operator = $operator['sql'];
switch ($rule[0]) {
case 'anywhere':
$where[] = "(`artist`.`name` {$sql_match_operator} '{$input}' OR `album`.`name` {$sql_match_operator} '{$input}' OR `song_data`.`comment` {$sql_match_operator} '{$input}' OR `song_data`.`label` {$sql_match_operator} '{$input}' OR `song`.`file` {$sql_match_operator} '{$input}' OR `song`.`title` {$sql_match_operator} '{$input}')";
$join['album'] = true;
$join['artist'] = true;
$join['song_data'] = true;
break;
case 'tag':
$key = md5($input . $sql_match_operator);
$where[] = "`realtag_{$key}`.`match` > 0";
$join['tag'][$key] = "{$sql_match_operator} '{$input}'";
break;
case 'album_tag':
$key = md5($input . $sql_match_operator);
$where[] = "`realtag_{$key}`.`match` > 0";
$join['album_tag'][$key] = "{$sql_match_operator} '{$input}'";
$join['album'] = true;
break;
case 'title':
$where[] = "`song`.`title` {$sql_match_operator} '{$input}'";
break;
case 'album':
$where[] = "`album`.`name` {$sql_match_operator} '{$input}'";
$join['album'] = true;
break;
case 'artist':
$where[] = "`artist`.`name` {$sql_match_operator} '{$input}'";
$join['artist'] = true;
break;
case 'composer':
$where[] = "`song`.`composer` {$sql_match_operator} '{$input}'";
break;
case 'time':
$input = $input * 60;
$where[] = "`song`.`time` {$sql_match_operator} '{$input}'";
break;
case 'file':
$where[] = "`song`.`file` {$sql_match_operator} '{$input}'";
break;
case 'year':
$where[] = "`song`.`year` {$sql_match_operator} '{$input}'";
break;
case 'comment':
$where[] = "`song_data`.`comment` {$sql_match_operator} '{$input}'";
$join['song_data'] = true;
break;
case 'label':
$where[] = "`song_data`.`label` {$sql_match_operator} '{$input}'";
$join['song_data'] = true;
break;
case 'played':
$where[] = " `song`.`played` = '{$input}'";
break;
case 'bitrate':
$input = $input * 1000;
$where[] = "`song`.`bitrate` {$sql_match_operator} '{$input}'";
break;
case 'rating':
if ($this->type != "public") {
$where[] = "COALESCE(`rating`.`rating`,0) {$sql_match_operator} '{$input}'";
} else {
$group[] = "`song`.`id`";
$having[] = "ROUND(AVG(IFNULL(`rating`.`rating`,0))) {$sql_match_operator} '{$input}'";
}
$join['rating'] = true;
break;
case 'played_times':
$where[] = "`song`.`id` IN (SELECT `object_count`.`object_id` FROM `object_count` " . "WHERE `object_count`.`object_type` = 'song' AND `object_count`.`count_type` = 'stream' " . "GROUP BY `object_count`.`object_id` HAVING COUNT(*) {$sql_match_operator} '{$input}')";
break;
case 'catalog':
$where[] = "`song`.`catalog` {$sql_match_operator} '{$input}'";
break;
case 'playlist_name':
$join['playlist'] = true;
$join['playlist_data'] = true;
$where[] = "`playlist`.`name` {$sql_match_operator} '{$input}'";
break;
case 'playlist':
$join['playlist_data'] = true;
$where[] = "`playlist_data`.`playlist` {$sql_match_operator} '{$input}'";
break;
case 'smartplaylist':
$subsearch = new Search($input, 'song');
$subsql = $subsearch->to_sql();
$where[] = "{$sql_match_operator} (" . $subsql['where_sql'] . ")";
// HACK: array_merge would potentially lose tags, since it
// overwrites. Save our merged tag joins in a temp variable,
// even though that's ugly.
$tagjoin = array_merge($subsql['join']['tag'], $join['tag']);
$join = array_merge($subsql['join'], $join);
$join['tag'] = $tagjoin;
break;
case 'license':
$where[] = "`song`.`license` {$sql_match_operator} '{$input}'";
break;
case 'added':
$input = strtotime($input);
$where[] = "`song`.`addition_time` {$sql_match_operator} {$input}";
break;
case 'updated':
$input = strtotime($input);
$where[] = "`song`.`update_time` {$sql_match_operator} {$input}";
break;
case 'metadata':
// Need to create a join for every field so we can create and / or queries with only one table
$tableAlias = 'metadata' . uniqid();
$field = (int) $rule[3];
$join[$tableAlias] = true;
$parsedInput = is_numeric($input) ? $input : '"' . $input . '"';
$where[] = "(`{$tableAlias}`.`field` = {$field} AND `{$tableAlias}`.`data` {$sql_match_operator} {$parsedInput})";
$table[$tableAlias] = 'LEFT JOIN `metadata` AS ' . $tableAlias . ' ON `song`.`id` = `' . $tableAlias . '`.`object_id`';
break;
default:
// NOSSINK!
break;
}
// switch on type
}
// foreach over rules
$join['catalog'] = AmpConfig::get('catalog_disable');
$where_sql = implode(" {$sql_logic_operator} ", $where);
// now that we know which things we want to JOIN...
if ($join['artist']) {
$table['artist'] = "LEFT JOIN `artist` ON `song`.`artist`=`artist`.`id`";
}
if ($join['album']) {
$table['album'] = "LEFT JOIN `album` ON `song`.`album`=`album`.`id`";
}
if ($join['song_data']) {
$table['song_data'] = "LEFT JOIN `song_data` ON `song`.`id`=`song_data`.`song_id`";
}
foreach ($join['tag'] as $key => $value) {
$table['tag_' . $key] = "LEFT JOIN (" . "SELECT `object_id`, COUNT(`name`) AS `match` " . "FROM `tag` LEFT JOIN `tag_map` " . "ON `tag`.`id`=`tag_map`.`tag_id` " . "WHERE `tag_map`.`object_type`='song' " . "AND `tag`.`name` {$value} GROUP BY `object_id`" . ") AS realtag_{$key} " . "ON `song`.`id`=`realtag_{$key}`.`object_id`";
}
foreach ($join['album_tag'] as $key => $value) {
$table['tag_' . $key] = "LEFT JOIN (" . "SELECT `object_id`, COUNT(`name`) AS `match` " . "FROM `tag` LEFT JOIN `tag_map` " . "ON `tag`.`id`=`tag_map`.`tag_id` " . "WHERE `tag_map`.`object_type`='album' " . "AND `tag`.`name` {$value} GROUP BY `object_id`" . ") AS realtag_{$key} " . "ON `album`.`id`=`realtag_{$key}`.`object_id`";
}
if ($join['rating']) {
$userid = $GLOBALS['user']->id;
$table['rating'] = "LEFT JOIN `rating` ON `rating`.`object_type`='song' AND ";
if ($this->type != "public") {
$table['rating'] .= "`rating`.`user`='{$userid}' AND ";
}
$table['rating'] .= "`rating`.`object_id`=`song`.`id`";
}
if ($join['playlist_data']) {
$table['playlist_data'] = "LEFT JOIN `playlist_data` ON `song`.`id`=`playlist_data`.`object_id` AND `playlist_data`.`object_type`='song'";
if ($join['playlist']) {
$table['playlist'] = "LEFT JOIN `playlist` ON `playlist_data`.`playlist`=`playlist`.`id`";
}
}
if ($join['catalog']) {
$table['catalog'] = "LEFT JOIN `catalog` AS `catalog_se` ON `catalog_se`.`id`=`song`.`catalog`";
$where_sql .= " AND `catalog_se`.`enabled` = '1'";
}
$table_sql = implode(' ', $table);
$group_sql = implode(', ', $group);
$having_sql = implode(" {$sql_logic_operator} ", $having);
return array('base' => 'SELECT DISTINCT(`song`.`id`) FROM `song`', 'join' => $join, 'where' => $where, 'where_sql' => $where_sql, 'table' => $table, 'table_sql' => $table_sql, 'group_sql' => $group_sql, 'having_sql' => $having_sql);
}
/**
* advanced
* This processes the results of a post from a form and returns an
* array of song items that were returned from said randomness
*/
public static function advanced($type, $data)
{
/* Figure out our object limit */
$limit = intval($data['random']);
// Generate our matchlist
/* If they've passed -1 as limit then get everything */
$limit_sql = "";
if ($data['random'] == "-1") {
unset($data['random']);
} else {
$limit_sql = "LIMIT " . Dba::escape($limit);
}
$search_data = Search::clean_request($data);
$search_info = false;
if (count($search_data) > 1) {
$search = new Search(null, $type);
$search->parse_rules($search_data);
$search_info = $search->to_sql();
}
$sql = "";
switch ($type) {
case 'song':
$sql = "SELECT `song`.`id`, `size`, `time` " . "FROM `song` ";
if ($search_info) {
$sql .= $search_info['table_sql'];
}
if (AmpConfig::get('catalog_disable')) {
$sql .= " LEFT JOIN `catalog` ON `catalog`.`id` = `song`.`catalog`";
$sql .= " WHERE `catalog`.`enabled` = '1'";
}
if ($search_info) {
if (AmpConfig::get('catalog_disable')) {
$sql .= ' AND ' . $search_info['where_sql'];
} else {
$sql .= ' WHERE ' . $search_info['where_sql'];
}
}
break;
case 'album':
$sql = "SELECT `album`.`id`, SUM(`song`.`size`) AS `size`, SUM(`song`.`time`) AS `time` FROM `album` ";
if (!$search_info || !$search_info['join']['song']) {
$sql .= "LEFT JOIN `song` ON `song`.`album`=`album`.`id` ";
}
if ($search_info) {
$sql .= $search_info['table_sql'];
}
if (AmpConfig::get('catalog_disable')) {
$sql .= " LEFT JOIN `catalog` ON `catalog`.`id` = `song`.`catalog`";
$sql .= " WHERE `catalog`.`enabled` = '1'";
}
if ($search_info) {
if (AmpConfig::get('catalog_disable')) {
$sql .= ' AND ' . $search_info['where_sql'];
} else {
$sql .= ' WHERE ' . $search_info['where_sql'];
}
}
$sql .= ' GROUP BY `album`.`id`';
break;
case 'artist':
$sql = "SELECT `artist`.`id`, SUM(`song`.`size`) AS `size`, SUM(`song`.`time`) AS `time` FROM `artist` ";
if (!$search_info || !$search_info['join']['song']) {
$sql .= "LEFT JOIN `song` ON `song`.`artist`=`artist`.`id` ";
}
if ($search_info) {
$sql .= $search_info['table_sql'];
}
if (AmpConfig::get('catalog_disable')) {
$sql .= " LEFT JOIN `catalog` ON `catalog`.`id` = `song`.`catalog`";
$sql .= " WHERE `catalog`.`enabled` = '1'";
}
if ($search_info) {
if (AmpConfig::get('catalog_disable')) {
$sql .= ' AND ' . $search_info['where_sql'];
} else {
$sql .= ' WHERE ' . $search_info['where_sql'];
}
}
$sql .= ' GROUP BY `artist`.`id`';
break;
}
$sql .= " ORDER BY RAND() {$limit_sql}";
// Run the query generated above so we can while it
$db_results = Dba::read($sql);
$results = array();
$size_total = 0;
$fuzzy_size = 0;
$time_total = 0;
$fuzzy_time = 0;
while ($row = Dba::fetch_assoc($db_results)) {
// If size limit is specified
if ($data['size_limit']) {
// Convert
$new_size = $row['size'] / 1024 / 1024;
// Only fuzzy 100 times
if ($fuzzy_size > 100) {
break;
}
// Add and check, skip if over size
if ($size_total + $new_size > $data['size_limit']) {
$fuzzy_size++;
continue;
}
$size_total = $size_total + $new_size;
$results[] = $row['id'];
// If we are within 4mb of target then jump ship
if ($data['size_limit'] - floor($size_total) < 4) {
break;
}
}
// if size_limit
// If length really does matter
if ($data['length']) {
// base on min, seconds are for chumps and chumpettes
$new_time = floor($row['time'] / 60);
if ($fuzzy_time > 100) {
break;
}
// If the new one would go over skip!
if ($time_total + $new_time > $data['length']) {
$fuzzy_time++;
continue;
}
$time_total = $time_total + $new_time;
$results[] = $row['id'];
// If there are less then 2 min of free space return
if ($data['length'] - $time_total < 2) {
return $results;
}
}
// if length does matter
if (!$data['size_limit'] && !$data['length']) {
$results[] = $row['id'];
}
}
// end while results
switch ($type) {
case 'song':
return $results;
case 'album':
$songs = array();
foreach ($results as $result) {
$album = new Album($result);
$songs = array_merge($songs, $album->get_songs());
}
return $songs;
case 'artist':
$songs = array();
foreach ($results as $result) {
$artist = new Artist($result);
$songs = array_merge($songs, $artist->get_songs());
}
return $songs;
default:
return false;
}
}
/**
* song_to_sql
* Handles the generation of the SQL for song searches.
*/
private function song_to_sql()
{
$sql_logic_operator = $this->logic_operator;
$where = array();
$table = array();
$join = array();
$join['tag'] = array();
foreach ($this->rules as $rule) {
$type = $this->name_to_basetype($rule[0]);
$operator = array();
foreach ($this->basetypes[$type] as $op) {
if ($op['name'] == $rule[1]) {
$operator = $op;
break;
}
}
$input = $this->_mangle_data($rule[2], $type, $operator);
$sql_match_operator = $operator['sql'];
switch ($rule[0]) {
case 'anywhere':
$where[] = "(`artist`.`name` {$sql_match_operator} '{$input}' OR `album`.`name` {$sql_match_operator} '{$input}' OR `song_data`.`comment` {$sql_match_operator} '{$input}' OR `song`.`file` {$sql_match_operator} '{$input}' OR `song`.`title` {$sql_match_operator} '{$input}')";
$join['album'] = true;
$join['artist'] = true;
$join['song_data'] = true;
break;
case 'tag':
$key = md5($input . $sql_match_operator);
$where[] = "`realtag_{$key}`.`match` > 0";
$join['tag'][$key] = "{$sql_match_operator} '{$input}'";
break;
case 'title':
$where[] = "`song`.`title` {$sql_match_operator} '{$input}'";
break;
case 'album':
$where[] = "`album`.`name` {$sql_match_operator} '{$input}'";
$join['album'] = true;
break;
case 'artist':
$where[] = "`artist`.`name` {$sql_match_operator} '{$input}'";
$join['artist'] = true;
break;
case 'time':
$input = $input * 60;
$where[] = "`song`.`time` {$sql_match_operator} '{$input}'";
break;
case 'file':
$where[] = "`song`.`file` {$sql_match_operator} '{$input}'";
break;
case 'year':
$where[] = "`song`.`year` {$sql_match_operator} '{$input}'";
break;
case 'comment':
$where[] = "`song_data`.`comment` {$sql_match_operator} '{$input}'";
$join['song_data'] = true;
break;
case 'played':
$where[] = " `song`.`played` = '{$input}'";
break;
case 'bitrate':
$input = $input * 1000;
$where[] = "`song`.`bitrate` {$sql_match_operator} '{$input}'";
break;
case 'rating':
$where[] = "COALESCE(`rating`.`rating`,0) {$sql_match_operator} '{$input}'";
$join['rating'] = true;
break;
case 'played_times':
$where[] = "`song`.`id` IN (SELECT `object_count`.`object_id` FROM `object_count` " . "WHERE `object_count`.`object_type` = 'song'" . "GROUP BY `object_count`.`object_id` HAVING COUNT(*) {$sql_match_operator} '{$input}')";
break;
case 'catalog':
$where[] = "`song`.`catalog` {$sql_match_operator} '{$input}'";
break;
case 'playlist_name':
$join['playlist'] = true;
$join['playlist_data'] = true;
$where[] = "`playlist`.`name` {$sql_match_operator} '{$input}'";
break;
case 'playlist':
$join['playlist_data'] = true;
$where[] = "`playlist_data`.`playlist` {$sql_match_operator} '{$input}'";
break;
case 'smartplaylist':
$subsearch = new Search('song', $input);
$subsql = $subsearch->to_sql();
$where[] = "{$sql_match_operator} (" . $subsql['where_sql'] . ")";
// HACK: array_merge would potentially lose tags, since it
// overwrites. Save our merged tag joins in a temp variable,
// even though that's ugly.
$tagjoin = array_merge($subsql['join']['tag'], $join['tag']);
$join = array_merge($subsql['join'], $join);
$join['tag'] = $tagjoin;
break;
case 'added':
$input = strtotime($input);
$where[] = "`song`.`addition_time` {$sql_match_operator} {$input}";
break;
case 'updated':
$input = strtotime($input);
$where[] = "`song`.`update_time` {$sql_match_operator} {$input}";
default:
// NOSSINK!
break;
}
// switch on type
}
// foreach over rules
$join['catalog'] = AmpConfig::get('catalog_disable');
$where_sql = implode(" {$sql_logic_operator} ", $where);
// now that we know which things we want to JOIN...
if ($join['artist']) {
$table['artist'] = "LEFT JOIN `artist` ON `song`.`artist`=`artist`.`id`";
}
if ($join['album']) {
$table['album'] = "LEFT JOIN `album` ON `song`.`album`=`album`.`id`";
}
if ($join['song_data']) {
$table['song_data'] = "LEFT JOIN `song_data` ON `song`.`id`=`song_data`.`song_id`";
}
foreach ($join['tag'] as $key => $value) {
$table['tag_' . $key] = "LEFT JOIN (" . "SELECT `object_id`, COUNT(`name`) AS `match` " . "FROM `tag` LEFT JOIN `tag_map` " . "ON `tag`.`id`=`tag_map`.`tag_id` " . "WHERE `tag_map`.`object_type`='song' " . "AND `tag`.`name` {$value} GROUP BY `object_id`" . ") AS realtag_{$key} " . "ON `song`.`id`=`realtag_{$key}`.`object_id`";
}
if ($join['rating']) {
$userid = $GLOBALS['user']->id;
$table['rating'] = "LEFT JOIN `rating` ON " . "`rating`.`object_type`='song' AND " . "`rating`.`user`='{$userid}' AND " . "`rating`.`object_id`=`song`.`id`";
}
if ($join['playlist_data']) {
$table['playlist_data'] = "LEFT JOIN `playlist_data` ON `song`.`id`=`playlist_data`.`object_id` AND `playlist_data`.`object_type`='song'";
if ($join['playlist']) {
$table['playlist'] = "LEFT JOIN `playlist` ON `playlist_data`.`playlist`=`playlist`.`id`";
}
}
if ($join['catalog']) {
$table['catalog'] = "LEFT JOIN `catalog` AS `catalog_se` ON `catalog_se`.`id`=`song`.`catalog`";
$where_sql .= " AND `catalog_se`.`enabled` = '1'";
}
$table_sql = implode(' ', $table);
return array('base' => 'SELECT DISTINCT(`song`.`id`) FROM `song`', 'join' => $join, 'where' => $where, 'where_sql' => $where_sql, 'table' => $table, 'table_sql' => $table_sql);
}
set_time_limit(0);
$media_ids = array();
$name = "Unknown.zip";
switch ($_REQUEST['action']) {
case 'tmp_playlist':
$media_ids = $GLOBALS['user']->playlist->get_items();
$name = $GLOBALS['user']->username . ' - Playlist';
break;
case 'playlist':
$playlist = new Playlist($_REQUEST['id']);
$media_ids = $playlist->get_songs();
$name = $playlist->name;
break;
case 'smartplaylist':
$search = new Search('song', $_REQUEST['id']);
$sql = $search->to_sql();
$sql = $sql['base'] . ' ' . $sql['table_sql'] . ' WHERE ' . $sql['where_sql'];
$db_results = Dba::read($sql);
while ($row = Dba::fetch_assoc($db_results)) {
$media_ids[] = $row['id'];
}
$name = $search->name;
break;
case 'album':
foreach ($_REQUEST['id'] as $a) {
$album = new Album($a);
if (empty($name)) {
$name = $album->name;
}
$asongs = $album->get_songs();
foreach ($asongs as $song_id) {