summaryrefslogtreecommitdiffstats
path: root/lib/class/search.class.php
diff options
context:
space:
mode:
Diffstat (limited to 'lib/class/search.class.php')
-rw-r--r--lib/class/search.class.php2108
1 files changed, 1054 insertions, 1054 deletions
diff --git a/lib/class/search.class.php b/lib/class/search.class.php
index dea0b41d..6a1c2352 100644
--- a/lib/class/search.class.php
+++ b/lib/class/search.class.php
@@ -1,5 +1,5 @@
<?php
-/* vim:set tabstop=8 softtabstop=8 shiftwidth=8 noexpandtab: */
+/* vim:set softtabstop=4 shiftwidth=4 expandtab: */
/**
*
* LICENSE: GNU General Public License, version 2 (GPLv2)
@@ -27,1059 +27,1059 @@
class Search extends playlist_object {
- public $searchtype;
- public $rules;
- public $logic_operator = 'AND';
- public $type = 'public';
-
- public $basetypes;
- public $types;
-
- /**
- * constructor
- */
- public function __construct($searchtype = 'song', $id = '') {
- $this->searchtype = $searchtype;
- if ($id) {
- $info = $this->get_info($id);
- foreach ($info as $key=>$value) {
- $this->$key = $value;
- }
-
- $this->rules = unserialize($this->rules);
- }
-
- // Define our basetypes
-
- $this->basetypes['numeric'][] = array(
- 'name' => 'gte',
- 'description' => T_('is greater than or equal to'),
- 'sql' => '>='
- );
-
- $this->basetypes['numeric'][] = array(
- 'name' => 'lte',
- 'description' => T_('is less than or equal to'),
- 'sql' => '<='
- );
-
- $this->basetypes['numeric'][] = array(
- 'name' => 'equal',
- 'description' => T_('is'),
- 'sql' => '<=>'
- );
-
- $this->basetypes['numeric'][] = array(
- 'name' => 'ne',
- 'description' => T_('is not'),
- 'sql' => '<>'
- );
-
- $this->basetypes['numeric'][] = array(
- 'name' => 'gt',
- 'description' => T_('is greater than'),
- 'sql' => '>'
- );
-
- $this->basetypes['numeric'][] = array(
- 'name' => 'lt',
- 'description' => T_('is less than'),
- 'sql' => '<'
- );
-
-
- $this->basetypes['boolean'][] = array(
- 'name' => 'true',
- 'description' => T_('is true')
- );
-
- $this->basetypes['boolean'][] = array(
- 'name' => 'false',
- 'description' => T_('is false')
- );
-
-
- $this->basetypes['text'][] = array(
- 'name' => 'contain',
- 'description' => T_('contains'),
- 'sql' => 'LIKE',
- 'preg_match' => array('/^/','/$/'),
- 'preg_replace' => array('%', '%')
- );
-
- $this->basetypes['text'][] = array(
- 'name' => 'notcontain',
- 'description' => T_('does not contain'),
- 'sql' => 'NOT LIKE',
- 'preg_match' => array('/^/','/$/'),
- 'preg_replace' => array('%', '%')
- );
-
- $this->basetypes['text'][] = array(
- 'name' => 'start',
- 'description' => T_('starts with'),
- 'sql' => 'LIKE',
- 'preg_match' => '/$/',
- 'preg_replace' => '%'
- );
-
- $this->basetypes['text'][] = array(
- 'name' => 'end',
- 'description' => T_('ends with'),
- 'sql' => 'LIKE',
- 'preg_match' => '/^/',
- 'preg_replace' => '%'
- );
-
- $this->basetypes['text'][] = array(
- 'name' => 'equal',
- 'description' => T_('is'),
- 'sql' => '='
- );
-
- $this->basetypes['text'][] = array(
- 'name' => 'sounds',
- 'description' => T_('sounds like'),
- 'sql' => 'SOUNDS LIKE'
- );
-
- $this->basetypes['text'][] = array(
- 'name' => 'notsounds',
- 'description' => T_('does not sound like'),
- 'sql' => 'NOT SOUNDS LIKE'
- );
-
-
- $this->basetypes['boolean_numeric'][] = array(
- 'name' => 'equal',
- 'description' => T_('is'),
- 'sql' => '<=>'
- );
-
- $this->basetypes['boolean_numeric'][] = array(
- 'name' => 'ne',
- 'description' => T_('is not'),
- 'sql' => '<>'
- );
-
-
- $this->basetypes['boolean_subsearch'][] = array(
- 'name' => 'equal',
- 'description' => T_('is'),
- 'sql' => ''
- );
-
- $this->basetypes['boolean_subsearch'][] = array(
- 'name' => 'ne',
- 'description' => T_('is not'),
- 'sql' => 'NOT'
- );
-
-
- $this->basetypes['date'][] = array(
- 'name' => 'lt',
- 'description' => T_('before'),
- 'sql' => '>'
- );
-
- $this->basetypes['date'][] = array(
- 'name' => 'gt',
- 'description' => T_('after'),
- 'sql' => '>'
- );
-
- switch ($searchtype) {
- case 'song':
- $this->types[] = array(
- 'name' => 'anywhere',
- 'label' => T_('Any searchable text'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
-
- $this->types[] = array(
- 'name' => 'title',
- 'label' => T_('Title'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
-
- $this->types[] = array(
- 'name' => 'album',
- 'label' => T_('Album'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
-
- $this->types[] = array(
- 'name' => 'artist',
- 'label' => T_('Artist'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
-
- $this->types[] = array(
- 'name' => 'comment',
- 'label' => T_('Comment'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
-
-
- $this->types[] = array(
- 'name' => 'tag',
- 'label' => T_('Tag'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
-
- $this->types[] = array(
- 'name' => 'file',
- 'label' => T_('Filename'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
-
- $this->types[] = array(
- 'name' => 'year',
- 'label' => T_('Year'),
- 'type' => 'numeric',
- 'widget' => array('input', 'text')
- );
-
- $this->types[] = array(
- 'name' => 'time',
- 'label' => T_('Length (in minutes)'),
- 'type' => 'numeric',
- 'widget' => array('input', 'text')
- );
-
- if (Config::get('ratings')) {
- $this->types[] = array(
- 'name' => 'rating',
- 'label' => T_('Rating'),
- 'type' => 'numeric',
- 'widget' => array(
- 'select',
- array(
- '1 Star',
- '2 Stars',
- '3 Stars',
- '4 Stars',
- '5 Stars'
- )
- )
- );
- }
-
- $this->types[] = array(
- 'name' => 'bitrate',
- 'label' => T_('Bitrate'),
- 'type' => 'numeric',
- 'widget' => array(
- 'select',
- array(
- '32',
- '40',
- '48',
- '56',
- '64',
- '80',
- '96',
- '112',
- '128',
- '160',
- '192',
- '224',
- '256',
- '320'
- )
- )
- );
-
- $this->types[] = array(
- 'name' => 'played',
- 'label' => T_('Played'),
- 'type' => 'boolean',
- 'widget' => array('input', 'hidden')
- );
-
- $this->types[] = array(
- 'name' => 'added',
- 'label' => T_('Added'),
- 'type' => 'date',
- 'widget' => array('input', 'text')
- );
-
- $this->types[] = array(
- 'name' => 'updated',
- 'label' => T_('Updated'),
- 'type' => 'date',
- 'widget' => array('input', 'text')
- );
-
- $catalogs = array();
- foreach (Catalog::get_catalogs() as $catid) {
- $catalog = new Catalog($catid);
- $catalog->format();
- $catalogs[$catid] = $catalog->f_name;
- }
- $this->types[] = array(
- 'name' => 'catalog',
- 'label' => T_('Catalog'),
- 'type' => 'boolean_numeric',
- 'widget' => array('select', $catalogs)
- );
-
- $playlists = array();
- foreach (Playlist::get_playlists() as $playlistid) {
- $playlist = new Playlist($playlistid);
- $playlist->format();
- $playlists[$playlistid] = $playlist->f_name;
- }
- $this->types[] = array(
- 'name' => 'playlist',
- 'label' => T_('Playlist'),
- 'type' => 'boolean_numeric',
- 'widget' => array('select', $playlists)
- );
-
- $playlists = array();
- foreach (Search::get_searches() as $playlistid) {
- // Slightly different from the above so we don't
- // instigate a vicious loop.
- $playlists[$playlistid] = Search::get_name_byid($playlistid);
- }
- $this->types[] = array(
- 'name' => 'smartplaylist',
- 'label' => T_('Smart Playlist'),
- 'type' => 'boolean_subsearch',
- 'widget' => array('select', $playlists)
- );
- break;
- case 'album':
- $this->types[] = array(
- 'name' => 'title',
- 'label' => T_('Title'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
-
- $this->types[] = array(
- 'name' => 'year',
- 'label' => T_('Year'),
- 'type' => 'numeric',
- 'widget' => array('input', 'text')
- );
-
- if (Config::get('ratings')) {
- $this->types[] = array(
- 'name' => 'rating',
- 'label' => T_('Rating'),
- 'type' => 'numeric',
- 'widget' => array(
- 'select',
- array(
- '1 Star',
- '2 Stars',
- '3 Stars',
- '4 Stars',
- '5 Stars'
- )
- )
- );
- }
-
- $catalogs = array();
- foreach (Catalog::get_catalogs() as $catid) {
- $catalog = new Catalog($catid);
- $catalog->format();
- $catalogs[$catid] = $catalog->f_name;
- }
- $this->types[] = array(
- 'name' => 'catalog',
- 'label' => T_('Catalog'),
- 'type' => 'boolean_numeric',
- 'widget' => array('select', $catalogs)
- );
-
-
- $this->types[] = array(
- 'name' => 'tag',
- 'label' => T_('Tag'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
- break;
- case 'video':
- $this->types[] = array(
- 'name' => 'filename',
- 'label' => T_('Filename'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
- break;
- case 'artist':
- $this->types[] = array(
- 'name' => 'name',
- 'label' => T_('Name'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
- $this->types[] = array(
- 'name' => 'tag',
- 'label' => T_('Tag'),
- 'type' => 'text',
- 'widget' => array('input', 'text')
- );
- break;
- } // end switch on searchtype
-
- } // end constructor
-
- /**
- * clean_request
- * Sanitizes raw search data
- */
- public static function clean_request($data) {
- foreach ($data as $key => $value) {
- $prefix = substr($key, 0, 4);
- $value = trim($value);
-
- if ($prefix == 'rule' && strlen($value)) {
- $request[$key] = Dba::escape($value);
- }
- } // end foreach $data
-
- // Figure out if they want an AND based search or an OR based
- // search
- switch($data['operator']) {
- case 'or':
- $request['operator'] = 'OR';
- break;
- default:
- $request['operator'] = 'AND';
- break;
- } // end switcn on operator
-
- // Verify the type
- switch($data['type']) {
- case 'album':
- case 'artist':
- case 'video':
- case 'song':
- $request['type'] = $data['type'];
- break;
- default:
- $request['type'] = 'song';
- break;
- }
-
- return $request;
- } // end clean_request
-
- /**
- * get_name_byid
- * Returns the name of the saved search corresponding to the given ID
- */
- public static function get_name_byid($id) {
- $sql = "SELECT `name` FROM `search` WHERE `id`='$id'";
- $db_results = Dba::read($sql);
- $r = Dba::fetch_assoc($db_results);
- return $r['name'];
- } // end get_name_byid
-
- /**
- * get_searches
- * Return the IDs of all saved searches accessible by the current user.
- */
- public static function get_searches() {
- $sql = "SELECT `id` from `search` WHERE `type`='public' OR " .
- "`user`='" . $GLOBALS['user']->id . "' ORDER BY `name`";
- $db_results = Dba::read($sql);
-
- $results = array();
-
- while ($row = Dba::fetch_assoc($db_results)) {
- $results[] = $row['id'];
- }
-
- return $results;
- } // end get_searches
-
- /**
- * run
- * This function actually runs the search, and returns an array of the
- * results.
- */
- public static function run($data) {
- $limit = intval($data['limit']);
- /* Create an array of the object we need to search on */
- $data = Search::clean_request($data);
-
- $search = new Search($data['type']);
- $search->parse_rules($data);
-
- /* Generate BASE SQL */
-
- if ($limit > 0) {
- $limit_sql = " LIMIT " . $limit;
- }
-
- $search_info = $search->to_sql();
- $sql = $search_info['base'] . ' ' . $search_info['table_sql'] .
- ' WHERE ' . $search_info['where_sql'] . " $limit_sql";
-
- $db_results = Dba::read($sql);
-
- $results = array();
-
- while ($row = Dba::fetch_assoc($db_results)) {
- $results[] = $row['id'];
- }
-
- return $results;
- } // run
-
- /**
- * delete
- * Does what it says on the tin.
- */
- public function delete() {
- $id = Dba::escape($this->id);
- $sql = "DELETE FROM `search` WHERE `id`='$id'";
- $db_results = Dba::write($sql);
-
- return true;
- } // end delete
-
- /**
- * format
- * Gussy up the data
- */
- public function format() {
- parent::format();
- $this->f_link = '<a href="' . Config::get('web_path') . '/smartplaylist.php?action=show_playlist&amp;playlist_id=' . $this->id . '">' . $this->f_name . '</a>';
- } // end format
-
- /**
- * get_items
- * return an array of the items output by our search (part of the
- * playlist interface).
- */
- public function get_items() {
- $results = array();
-
- $sql = $this->to_sql();
- $sql = $sql['base'] . ' ' . $sql['table_sql'] . ' WHERE ' .
- $sql['where_sql'];
-
- $db_results = Dba::read($sql);
-
- while ($row = Dba::fetch_assoc($db_results)) {
- $results[] = array(
- 'object_id' => $row['id'],
- 'object_type' => $this->searchtype
- );
- }
-
- return $results;
- } // end get_items
-
- /**
- * get_random_items
- * return a randomly sorted array (with an optional limit) of the items
- * output by our search (part of the playlist interface)
- */
-
- public function get_random_items($limit = null) {
- $results = array();
-
- $sql = $this->to_sql();
- $sql = $sql['base'] . ' ' . $sql['table_sql'] . ' WHERE ' .
- $sql['where_sql'];
-
- $sql .= ' ORDER BY RAND()';
- $sql .= $limit ? ' LIMIT ' . intval($limit) : '';
-
- $db_results = Dba::read($sql);
-
- while ($row = Dba::fetch_assoc($db_results)) {
- $results[] = array(
- 'object_id' => $row['id'],
- 'object_type' => $this->searchtype
- );
- }
-
- return $results;
- }
-
- /**
- * name_to_basetype
- * Iterates over our array of types to find out the basetype for
- * the passed string.
- */
- public function name_to_basetype($name) {
- foreach ($this->types as $type) {
- if ($type['name'] == $name) {
- return $type['type'];
- }
- }
- return false;
- } // end name_to_basetype
-
- /**
- * parse_rules
- * Takes an array of sanitized search data from the form and generates
- * our real array from it.
- */
- public function parse_rules($data) {
- $this->rules = array();
- foreach ($data as $rule => $value) {
- if (preg_match('/^rule_(\d+)$/', $rule, $ruleID)) {
- $ruleID = $ruleID[1];
- foreach (explode('|', $data['rule_' . $ruleID . '_input']) as $input) {
- $this->rules[] = array(
- $value,
- $this->basetypes[$this->name_to_basetype($value)][$data['rule_' . $ruleID . '_operator']]['name'],
- $input
- );
- }
- }
- }
- $this->logic_operator = $data['operator'];
- } // end parse_rules
-
- /**
- * save
- * Save this search to the database for use as a smart playlist
- */
- public function save() {
- // Make sure we have a unique name
- if (! $this->name) {
- $this->name = $GLOBALS['user']->username . ' - ' . date("Y-m-d H:i:s",time());
- }
- $sql = "SELECT `id` FROM `search` WHERE `name`='$this->name'";
- $db_results = Dba::read($sql);
- if (Dba::num_rows($db_results)) {
- $this->name .= uniqid('', true);
- }
-
- // clean up variables for insert
- $name = Dba::escape($this->name);
- $user = Dba::escape($GLOBALS['user']->id);
- $type = Dba::escape($this->type);
- $rules = serialize($this->rules);
- $logic_operator = $this->logic_operator;
-
- $sql = "INSERT INTO `search` (`name`, `type`, `user`, `rules`, `logic_operator`) VALUES ('$name', '$type', '$user', '$rules', '$logic_operator')";
- $db_results = Dba::write($sql);
- $insert_id = Dba::insert_id();
- $this->id = $insert_id;
- return $insert_id;
- } // end save
-
-
- /**
- * to_js
- * Outputs the javascript necessary to re-show the current set of
- * rules.
- */
- public function to_js() {
- foreach ($this->rules as $rule) {
- $js .= '<script type="text/javascript">' .
- 'SearchRow.add("' . $rule[0] . '","' .
- $rule[1] . '","' . $rule[2] . '"); </script>';
- }
- return $js;
- } // end to_js
-
- /**
- * to_sql
- * Call the appropriate real function
- */
- public function to_sql() {
- return call_user_func(
- array($this, $this->searchtype . "_to_sql"));
- } // end to_sql
-
- /**
- * update
- * This function updates the saved version with the current settings
- */
- public function update() {
- if (!$this->id) {
- return false;
- }
-
- $name = Dba::escape($this->name);
- $user = Dba::escape($GLOBALS['user']->id);
- $type = Dba::escape($this->type);
- $rules = serialize($this->rules);
- $logic_operator = $this->logic_operator;
-
- $sql = "UPDATE `search` SET `name`='$name', `type`='$type', `rules`='$rules', `logic_operator`='$logic_operator' WHERE `id`='" . Dba::escape($this->id) . "'";
- $db_results = Dba::write($sql);
- return $db_results;
- } // end update
-
- /**
- * mangle_data
- * Private convenience function. Mangles the input according to a set
- * of predefined rules so that we don't have to include this logic in
- * foo_to_sql.
- */
- private function mangle_data($data, $type, $operator) {
- if ($operator['preg_match']) {
- $data = preg_replace(
- $operator['preg_match'],
- $operator['preg_replace'],
- $data
- );
- }
-
- if ($type == 'numeric') {
- return intval($data);
- }
-
- if ($type == 'boolean') {
- return make_bool($input);
- }
-
- return $data;
- } // end mangle_data
-
- /**
- * album_to_sql
- * Handles the generation of the SQL for album searches.
- */
- private function album_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]);
- foreach ($this->basetypes[$type] as $operator) {
- if ($operator['name'] == $rule[1]) {
- break;
- }
- }
- $input = $this->mangle_data($rule[2], $type, $operator);
- $sql_match_operator = $operator['sql'];
-
- switch ($rule[0]) {
- case 'title':
- $where[] = "`album`.`name` $sql_match_operator '$input'";
- break;
- case 'year':
- $where[] = "`album`.`year` $sql_match_operator '$input'";
- break;
- case 'rating':
- $where[] = "COALESCE(`rating`.`rating`,0) $sql_match_operator '$input'";
- $join['rating'] = true;
- break;
- case 'catalog':
- $where[] = "`song`.`catalog` $sql_match_operator '$input'";
- $join['song'] = true;
- break;
- case 'tag':
- $key = md5($input . $sql_match_operator);
- $where[] = "`realtag_$key`.`match` > 0";
- $join['tag'][$key] = "$sql_match_operator '$input'";
- break;
- default:
- // Nae laird!
- break;
- } // switch on ruletype
- } // foreach rule
-
- $where_sql = implode(" $sql_logic_operator ", $where);
-
- 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`='album' " .
- "AND `tag`.`name` $value GROUP BY `object_id`" .
- ") AS realtag_$key " .
- "ON `album`.`id`=`realtag_$key`.`object_id`";
- }
- if ($join['song']) {
- $table['song'] = "LEFT JOIN `song` ON `song`.`album`=`album`.`id`";
- }
- if ($join['rating']) {
- $userid = $GLOBALS['user']->id;
- $table['rating'] = "LEFT JOIN `rating` ON " .
- "`rating`.`object_type`='album' " .
- "AND `rating`.`user`='$userid' " .
- "AND `rating`.`object_id`=`album`.`id`";
- }
-
- $table_sql = implode(' ', $table);
-
- return array(
- 'base' => 'SELECT DISTINCT(`album`.`id`) FROM `album`',
- 'join' => $join,
- 'where' => $where,
- 'where_sql' => $where_sql,
- 'table' => $table,
- 'table_sql' => $table_sql
- );
- } // album_to_sql
-
- /**
- * artist_to_sql
- * Handles the generation of the SQL for artist searches.
- */
- private function artist_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]);
- foreach ($this->basetypes[$type] as $operator) {
- if ($operator['name'] == $rule[1]) {
- break;
- }
- }
- $input = $this->mangle_data($rule[2], $type, $operator);
- $sql_match_operator = $operator['sql'];
-
- switch ($rule[0]) {
- case 'name':
- $where[] = "`artist`.`name` $sql_match_operator '$input'";
- break;
- case 'tag':
- $key = md5($input . $sql_match_operator);
- $where[] = "`realtag_$key`.`match` > 0";
- $join['tag'][$key] = "$sql_match_operator '$input'";
- break;
- default:
- // Nihil
- break;
- } // switch on ruletype
- } // foreach rule
-
- $where_sql = implode(" $sql_logic_operator ", $where);
-
- 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`='artist' " .
- "AND `tag`.`name` $value GROUP BY `object_id`".
- ") AS realtag_$key " .
- "ON `artist`.`id`=`realtag_$key`.`object_id`";
- }
-
- $table_sql = implode(' ', $table);
-
- return array(
- 'base' => 'SELECT DISTINCT(`artist`.`id`) FROM `artist`',
- 'join' => $join,
- 'where' => $where,
- 'where_sql' => $where_sql,
- 'table' => $table,
- 'table_sql' => $table_sql
- );
- } // artist_to_sql
-
- /**
- * 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]);
- foreach ($this->basetypes[$type] as $operator) {
- if ($operator['name'] == $rule[1]) {
- 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 'catalog':
- $where[] = "`song`.`catalog` $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;
- } // end switch on type
- } // end foreach over rules
-
- $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'";
- }
-
- $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
- );
- } // end song_to_sql
-
- /**
- * video_to_sql
- * Handles the generation of the SQL for video searches.
- */
- private function video_to_sql() {
- $sql_logic_operator = $this->logic_operator;
-
- $where = array();
-
-
- foreach ($this->rules as $rule) {
- $type = $this->name_to_basetype($rule[0]);
- foreach ($this->basetypes[$type] as $operator) {
- if ($operator['name'] == $rule[1]) {
- break;
- }
- }
- $input = $this->mangle_data($rule[2], $type, $operator);
- $sql_match_operator = $operator['sql'];
-
- switch ($rule[0]) {
- case 'filename':
- $where[] = "`video`.`file` $sql_match_operator '$input'";
- break;
- default:
- // WE WILLNA BE FOOLED AGAIN!
- } // switch on ruletype
- } // foreach rule
-
- $where_sql = implode(" $sql_logic_operator ", $where);
-
- return array(
- 'base' => 'SELECT DISTINCT(`video`.`id`) FROM `video`',
- 'where' => $where,
- 'where_sql' => $where_sql
- );
- } // end video_to_sql
+ public $searchtype;
+ public $rules;
+ public $logic_operator = 'AND';
+ public $type = 'public';
+
+ public $basetypes;
+ public $types;
+
+ /**
+ * constructor
+ */
+ public function __construct($searchtype = 'song', $id = '') {
+ $this->searchtype = $searchtype;
+ if ($id) {
+ $info = $this->get_info($id);
+ foreach ($info as $key=>$value) {
+ $this->$key = $value;
+ }
+
+ $this->rules = unserialize($this->rules);
+ }
+
+ // Define our basetypes
+
+ $this->basetypes['numeric'][] = array(
+ 'name' => 'gte',
+ 'description' => T_('is greater than or equal to'),
+ 'sql' => '>='
+ );
+
+ $this->basetypes['numeric'][] = array(
+ 'name' => 'lte',
+ 'description' => T_('is less than or equal to'),
+ 'sql' => '<='
+ );
+
+ $this->basetypes['numeric'][] = array(
+ 'name' => 'equal',
+ 'description' => T_('is'),
+ 'sql' => '<=>'
+ );
+
+ $this->basetypes['numeric'][] = array(
+ 'name' => 'ne',
+ 'description' => T_('is not'),
+ 'sql' => '<>'
+ );
+
+ $this->basetypes['numeric'][] = array(
+ 'name' => 'gt',
+ 'description' => T_('is greater than'),
+ 'sql' => '>'
+ );
+
+ $this->basetypes['numeric'][] = array(
+ 'name' => 'lt',
+ 'description' => T_('is less than'),
+ 'sql' => '<'
+ );
+
+
+ $this->basetypes['boolean'][] = array(
+ 'name' => 'true',
+ 'description' => T_('is true')
+ );
+
+ $this->basetypes['boolean'][] = array(
+ 'name' => 'false',
+ 'description' => T_('is false')
+ );
+
+
+ $this->basetypes['text'][] = array(
+ 'name' => 'contain',
+ 'description' => T_('contains'),
+ 'sql' => 'LIKE',
+ 'preg_match' => array('/^/','/$/'),
+ 'preg_replace' => array('%', '%')
+ );
+
+ $this->basetypes['text'][] = array(
+ 'name' => 'notcontain',
+ 'description' => T_('does not contain'),
+ 'sql' => 'NOT LIKE',
+ 'preg_match' => array('/^/','/$/'),
+ 'preg_replace' => array('%', '%')
+ );
+
+ $this->basetypes['text'][] = array(
+ 'name' => 'start',
+ 'description' => T_('starts with'),
+ 'sql' => 'LIKE',
+ 'preg_match' => '/$/',
+ 'preg_replace' => '%'
+ );
+
+ $this->basetypes['text'][] = array(
+ 'name' => 'end',
+ 'description' => T_('ends with'),
+ 'sql' => 'LIKE',
+ 'preg_match' => '/^/',
+ 'preg_replace' => '%'
+ );
+
+ $this->basetypes['text'][] = array(
+ 'name' => 'equal',
+ 'description' => T_('is'),
+ 'sql' => '='
+ );
+
+ $this->basetypes['text'][] = array(
+ 'name' => 'sounds',
+ 'description' => T_('sounds like'),
+ 'sql' => 'SOUNDS LIKE'
+ );
+
+ $this->basetypes['text'][] = array(
+ 'name' => 'notsounds',
+ 'description' => T_('does not sound like'),
+ 'sql' => 'NOT SOUNDS LIKE'
+ );
+
+
+ $this->basetypes['boolean_numeric'][] = array(
+ 'name' => 'equal',
+ 'description' => T_('is'),
+ 'sql' => '<=>'
+ );
+
+ $this->basetypes['boolean_numeric'][] = array(
+ 'name' => 'ne',
+ 'description' => T_('is not'),
+ 'sql' => '<>'
+ );
+
+
+ $this->basetypes['boolean_subsearch'][] = array(
+ 'name' => 'equal',
+ 'description' => T_('is'),
+ 'sql' => ''
+ );
+
+ $this->basetypes['boolean_subsearch'][] = array(
+ 'name' => 'ne',
+ 'description' => T_('is not'),
+ 'sql' => 'NOT'
+ );
+
+
+ $this->basetypes['date'][] = array(
+ 'name' => 'lt',
+ 'description' => T_('before'),
+ 'sql' => '>'
+ );
+
+ $this->basetypes['date'][] = array(
+ 'name' => 'gt',
+ 'description' => T_('after'),
+ 'sql' => '>'
+ );
+
+ switch ($searchtype) {
+ case 'song':
+ $this->types[] = array(
+ 'name' => 'anywhere',
+ 'label' => T_('Any searchable text'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+
+ $this->types[] = array(
+ 'name' => 'title',
+ 'label' => T_('Title'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+
+ $this->types[] = array(
+ 'name' => 'album',
+ 'label' => T_('Album'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+
+ $this->types[] = array(
+ 'name' => 'artist',
+ 'label' => T_('Artist'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+
+ $this->types[] = array(
+ 'name' => 'comment',
+ 'label' => T_('Comment'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+
+
+ $this->types[] = array(
+ 'name' => 'tag',
+ 'label' => T_('Tag'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+
+ $this->types[] = array(
+ 'name' => 'file',
+ 'label' => T_('Filename'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+
+ $this->types[] = array(
+ 'name' => 'year',
+ 'label' => T_('Year'),
+ 'type' => 'numeric',
+ 'widget' => array('input', 'text')
+ );
+
+ $this->types[] = array(
+ 'name' => 'time',
+ 'label' => T_('Length (in minutes)'),
+ 'type' => 'numeric',
+ 'widget' => array('input', 'text')
+ );
+
+ if (Config::get('ratings')) {
+ $this->types[] = array(
+ 'name' => 'rating',
+ 'label' => T_('Rating'),
+ 'type' => 'numeric',
+ 'widget' => array(
+ 'select',
+ array(
+ '1 Star',
+ '2 Stars',
+ '3 Stars',
+ '4 Stars',
+ '5 Stars'
+ )
+ )
+ );
+ }
+
+ $this->types[] = array(
+ 'name' => 'bitrate',
+ 'label' => T_('Bitrate'),
+ 'type' => 'numeric',
+ 'widget' => array(
+ 'select',
+ array(
+ '32',
+ '40',
+ '48',
+ '56',
+ '64',
+ '80',
+ '96',
+ '112',
+ '128',
+ '160',
+ '192',
+ '224',
+ '256',
+ '320'
+ )
+ )
+ );
+
+ $this->types[] = array(
+ 'name' => 'played',
+ 'label' => T_('Played'),
+ 'type' => 'boolean',
+ 'widget' => array('input', 'hidden')
+ );
+
+ $this->types[] = array(
+ 'name' => 'added',
+ 'label' => T_('Added'),
+ 'type' => 'date',
+ 'widget' => array('input', 'text')
+ );
+
+ $this->types[] = array(
+ 'name' => 'updated',
+ 'label' => T_('Updated'),
+ 'type' => 'date',
+ 'widget' => array('input', 'text')
+ );
+
+ $catalogs = array();
+ foreach (Catalog::get_catalogs() as $catid) {
+ $catalog = new Catalog($catid);
+ $catalog->format();
+ $catalogs[$catid] = $catalog->f_name;
+ }
+ $this->types[] = array(
+ 'name' => 'catalog',
+ 'label' => T_('Catalog'),
+ 'type' => 'boolean_numeric',
+ 'widget' => array('select', $catalogs)
+ );
+
+ $playlists = array();
+ foreach (Playlist::get_playlists() as $playlistid) {
+ $playlist = new Playlist($playlistid);
+ $playlist->format();
+ $playlists[$playlistid] = $playlist->f_name;
+ }
+ $this->types[] = array(
+ 'name' => 'playlist',
+ 'label' => T_('Playlist'),
+ 'type' => 'boolean_numeric',
+ 'widget' => array('select', $playlists)
+ );
+
+ $playlists = array();
+ foreach (Search::get_searches() as $playlistid) {
+ // Slightly different from the above so we don't
+ // instigate a vicious loop.
+ $playlists[$playlistid] = Search::get_name_byid($playlistid);
+ }
+ $this->types[] = array(
+ 'name' => 'smartplaylist',
+ 'label' => T_('Smart Playlist'),
+ 'type' => 'boolean_subsearch',
+ 'widget' => array('select', $playlists)
+ );
+ break;
+ case 'album':
+ $this->types[] = array(
+ 'name' => 'title',
+ 'label' => T_('Title'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+
+ $this->types[] = array(
+ 'name' => 'year',
+ 'label' => T_('Year'),
+ 'type' => 'numeric',
+ 'widget' => array('input', 'text')
+ );
+
+ if (Config::get('ratings')) {
+ $this->types[] = array(
+ 'name' => 'rating',
+ 'label' => T_('Rating'),
+ 'type' => 'numeric',
+ 'widget' => array(
+ 'select',
+ array(
+ '1 Star',
+ '2 Stars',
+ '3 Stars',
+ '4 Stars',
+ '5 Stars'
+ )
+ )
+ );
+ }
+
+ $catalogs = array();
+ foreach (Catalog::get_catalogs() as $catid) {
+ $catalog = new Catalog($catid);
+ $catalog->format();
+ $catalogs[$catid] = $catalog->f_name;
+ }
+ $this->types[] = array(
+ 'name' => 'catalog',
+ 'label' => T_('Catalog'),
+ 'type' => 'boolean_numeric',
+ 'widget' => array('select', $catalogs)
+ );
+
+
+ $this->types[] = array(
+ 'name' => 'tag',
+ 'label' => T_('Tag'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+ break;
+ case 'video':
+ $this->types[] = array(
+ 'name' => 'filename',
+ 'label' => T_('Filename'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+ break;
+ case 'artist':
+ $this->types[] = array(
+ 'name' => 'name',
+ 'label' => T_('Name'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+ $this->types[] = array(
+ 'name' => 'tag',
+ 'label' => T_('Tag'),
+ 'type' => 'text',
+ 'widget' => array('input', 'text')
+ );
+ break;
+ } // end switch on searchtype
+
+ } // end constructor
+
+ /**
+ * clean_request
+ * Sanitizes raw search data
+ */
+ public static function clean_request($data) {
+ foreach ($data as $key => $value) {
+ $prefix = substr($key, 0, 4);
+ $value = trim($value);
+
+ if ($prefix == 'rule' && strlen($value)) {
+ $request[$key] = Dba::escape($value);
+ }
+ } // end foreach $data
+
+ // Figure out if they want an AND based search or an OR based
+ // search
+ switch($data['operator']) {
+ case 'or':
+ $request['operator'] = 'OR';
+ break;
+ default:
+ $request['operator'] = 'AND';
+ break;
+ } // end switcn on operator
+
+ // Verify the type
+ switch($data['type']) {
+ case 'album':
+ case 'artist':
+ case 'video':
+ case 'song':
+ $request['type'] = $data['type'];
+ break;
+ default:
+ $request['type'] = 'song';
+ break;
+ }
+
+ return $request;
+ } // end clean_request
+
+ /**
+ * get_name_byid
+ * Returns the name of the saved search corresponding to the given ID
+ */
+ public static function get_name_byid($id) {
+ $sql = "SELECT `name` FROM `search` WHERE `id`='$id'";
+ $db_results = Dba::read($sql);
+ $r = Dba::fetch_assoc($db_results);
+ return $r['name'];
+ } // end get_name_byid
+
+ /**
+ * get_searches
+ * Return the IDs of all saved searches accessible by the current user.
+ */
+ public static function get_searches() {
+ $sql = "SELECT `id` from `search` WHERE `type`='public' OR " .
+ "`user`='" . $GLOBALS['user']->id . "' ORDER BY `name`";
+ $db_results = Dba::read($sql);
+
+ $results = array();
+
+ while ($row = Dba::fetch_assoc($db_results)) {
+ $results[] = $row['id'];
+ }
+
+ return $results;
+ } // end get_searches
+
+ /**
+ * run
+ * This function actually runs the search, and returns an array of the
+ * results.
+ */
+ public static function run($data) {
+ $limit = intval($data['limit']);
+ /* Create an array of the object we need to search on */
+ $data = Search::clean_request($data);
+
+ $search = new Search($data['type']);
+ $search->parse_rules($data);
+
+ /* Generate BASE SQL */
+
+ if ($limit > 0) {
+ $limit_sql = " LIMIT " . $limit;
+ }
+
+ $search_info = $search->to_sql();
+ $sql = $search_info['base'] . ' ' . $search_info['table_sql'] .
+ ' WHERE ' . $search_info['where_sql'] . " $limit_sql";
+
+ $db_results = Dba::read($sql);
+
+ $results = array();
+
+ while ($row = Dba::fetch_assoc($db_results)) {
+ $results[] = $row['id'];
+ }
+
+ return $results;
+ } // run
+
+ /**
+ * delete
+ * Does what it says on the tin.
+ */
+ public function delete() {
+ $id = Dba::escape($this->id);
+ $sql = "DELETE FROM `search` WHERE `id`='$id'";
+ $db_results = Dba::write($sql);
+
+ return true;
+ } // end delete
+
+ /**
+ * format
+ * Gussy up the data
+ */
+ public function format() {
+ parent::format();
+ $this->f_link = '<a href="' . Config::get('web_path') . '/smartplaylist.php?action=show_playlist&amp;playlist_id=' . $this->id . '">' . $this->f_name . '</a>';
+ } // end format
+
+ /**
+ * get_items
+ * return an array of the items output by our search (part of the
+ * playlist interface).
+ */
+ public function get_items() {
+ $results = array();
+
+ $sql = $this->to_sql();
+ $sql = $sql['base'] . ' ' . $sql['table_sql'] . ' WHERE ' .
+ $sql['where_sql'];
+
+ $db_results = Dba::read($sql);
+
+ while ($row = Dba::fetch_assoc($db_results)) {
+ $results[] = array(
+ 'object_id' => $row['id'],
+ 'object_type' => $this->searchtype
+ );
+ }
+
+ return $results;
+ } // end get_items
+
+ /**
+ * get_random_items
+ * return a randomly sorted array (with an optional limit) of the items
+ * output by our search (part of the playlist interface)
+ */
+
+ public function get_random_items($limit = null) {
+ $results = array();
+
+ $sql = $this->to_sql();
+ $sql = $sql['base'] . ' ' . $sql['table_sql'] . ' WHERE ' .
+ $sql['where_sql'];
+
+ $sql .= ' ORDER BY RAND()';
+ $sql .= $limit ? ' LIMIT ' . intval($limit) : '';
+
+ $db_results = Dba::read($sql);
+
+ while ($row = Dba::fetch_assoc($db_results)) {
+ $results[] = array(
+ 'object_id' => $row['id'],
+ 'object_type' => $this->searchtype
+ );
+ }
+
+ return $results;
+ }
+
+ /**
+ * name_to_basetype
+ * Iterates over our array of types to find out the basetype for
+ * the passed string.
+ */
+ public function name_to_basetype($name) {
+ foreach ($this->types as $type) {
+ if ($type['name'] == $name) {
+ return $type['type'];
+ }
+ }
+ return false;
+ } // end name_to_basetype
+
+ /**
+ * parse_rules
+ * Takes an array of sanitized search data from the form and generates
+ * our real array from it.
+ */
+ public function parse_rules($data) {
+ $this->rules = array();
+ foreach ($data as $rule => $value) {
+ if (preg_match('/^rule_(\d+)$/', $rule, $ruleID)) {
+ $ruleID = $ruleID[1];
+ foreach (explode('|', $data['rule_' . $ruleID . '_input']) as $input) {
+ $this->rules[] = array(
+ $value,
+ $this->basetypes[$this->name_to_basetype($value)][$data['rule_' . $ruleID . '_operator']]['name'],
+ $input
+ );
+ }
+ }
+ }
+ $this->logic_operator = $data['operator'];
+ } // end parse_rules
+
+ /**
+ * save
+ * Save this search to the database for use as a smart playlist
+ */
+ public function save() {
+ // Make sure we have a unique name
+ if (! $this->name) {
+ $this->name = $GLOBALS['user']->username . ' - ' . date("Y-m-d H:i:s",time());
+ }
+ $sql = "SELECT `id` FROM `search` WHERE `name`='$this->name'";
+ $db_results = Dba::read($sql);
+ if (Dba::num_rows($db_results)) {
+ $this->name .= uniqid('', true);
+ }
+
+ // clean up variables for insert
+ $name = Dba::escape($this->name);
+ $user = Dba::escape($GLOBALS['user']->id);
+ $type = Dba::escape($this->type);
+ $rules = serialize($this->rules);
+ $logic_operator = $this->logic_operator;
+
+ $sql = "INSERT INTO `search` (`name`, `type`, `user`, `rules`, `logic_operator`) VALUES ('$name', '$type', '$user', '$rules', '$logic_operator')";
+ $db_results = Dba::write($sql);
+ $insert_id = Dba::insert_id();
+ $this->id = $insert_id;
+ return $insert_id;
+ } // end save
+
+
+ /**
+ * to_js
+ * Outputs the javascript necessary to re-show the current set of
+ * rules.
+ */
+ public function to_js() {
+ foreach ($this->rules as $rule) {
+ $js .= '<script type="text/javascript">' .
+ 'SearchRow.add("' . $rule[0] . '","' .
+ $rule[1] . '","' . $rule[2] . '"); </script>';
+ }
+ return $js;
+ } // end to_js
+
+ /**
+ * to_sql
+ * Call the appropriate real function
+ */
+ public function to_sql() {
+ return call_user_func(
+ array($this, $this->searchtype . "_to_sql"));
+ } // end to_sql
+
+ /**
+ * update
+ * This function updates the saved version with the current settings
+ */
+ public function update() {
+ if (!$this->id) {
+ return false;
+ }
+
+ $name = Dba::escape($this->name);
+ $user = Dba::escape($GLOBALS['user']->id);
+ $type = Dba::escape($this->type);
+ $rules = serialize($this->rules);
+ $logic_operator = $this->logic_operator;
+
+ $sql = "UPDATE `search` SET `name`='$name', `type`='$type', `rules`='$rules', `logic_operator`='$logic_operator' WHERE `id`='" . Dba::escape($this->id) . "'";
+ $db_results = Dba::write($sql);
+ return $db_results;
+ } // end update
+
+ /**
+ * mangle_data
+ * Private convenience function. Mangles the input according to a set
+ * of predefined rules so that we don't have to include this logic in
+ * foo_to_sql.
+ */
+ private function mangle_data($data, $type, $operator) {
+ if ($operator['preg_match']) {
+ $data = preg_replace(
+ $operator['preg_match'],
+ $operator['preg_replace'],
+ $data
+ );
+ }
+
+ if ($type == 'numeric') {
+ return intval($data);
+ }
+
+ if ($type == 'boolean') {
+ return make_bool($input);
+ }
+
+ return $data;
+ } // end mangle_data
+
+ /**
+ * album_to_sql
+ * Handles the generation of the SQL for album searches.
+ */
+ private function album_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]);
+ foreach ($this->basetypes[$type] as $operator) {
+ if ($operator['name'] == $rule[1]) {
+ break;
+ }
+ }
+ $input = $this->mangle_data($rule[2], $type, $operator);
+ $sql_match_operator = $operator['sql'];
+
+ switch ($rule[0]) {
+ case 'title':
+ $where[] = "`album`.`name` $sql_match_operator '$input'";
+ break;
+ case 'year':
+ $where[] = "`album`.`year` $sql_match_operator '$input'";
+ break;
+ case 'rating':
+ $where[] = "COALESCE(`rating`.`rating`,0) $sql_match_operator '$input'";
+ $join['rating'] = true;
+ break;
+ case 'catalog':
+ $where[] = "`song`.`catalog` $sql_match_operator '$input'";
+ $join['song'] = true;
+ break;
+ case 'tag':
+ $key = md5($input . $sql_match_operator);
+ $where[] = "`realtag_$key`.`match` > 0";
+ $join['tag'][$key] = "$sql_match_operator '$input'";
+ break;
+ default:
+ // Nae laird!
+ break;
+ } // switch on ruletype
+ } // foreach rule
+
+ $where_sql = implode(" $sql_logic_operator ", $where);
+
+ 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`='album' " .
+ "AND `tag`.`name` $value GROUP BY `object_id`" .
+ ") AS realtag_$key " .
+ "ON `album`.`id`=`realtag_$key`.`object_id`";
+ }
+ if ($join['song']) {
+ $table['song'] = "LEFT JOIN `song` ON `song`.`album`=`album`.`id`";
+ }
+ if ($join['rating']) {
+ $userid = $GLOBALS['user']->id;
+ $table['rating'] = "LEFT JOIN `rating` ON " .
+ "`rating`.`object_type`='album' " .
+ "AND `rating`.`user`='$userid' " .
+ "AND `rating`.`object_id`=`album`.`id`";
+ }
+
+ $table_sql = implode(' ', $table);
+
+ return array(
+ 'base' => 'SELECT DISTINCT(`album`.`id`) FROM `album`',
+ 'join' => $join,
+ 'where' => $where,
+ 'where_sql' => $where_sql,
+ 'table' => $table,
+ 'table_sql' => $table_sql
+ );
+ } // album_to_sql
+
+ /**
+ * artist_to_sql
+ * Handles the generation of the SQL for artist searches.
+ */
+ private function artist_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]);
+ foreach ($this->basetypes[$type] as $operator) {
+ if ($operator['name'] == $rule[1]) {
+ break;
+ }
+ }
+ $input = $this->mangle_data($rule[2], $type, $operator);
+ $sql_match_operator = $operator['sql'];
+
+ switch ($rule[0]) {
+ case 'name':
+ $where[] = "`artist`.`name` $sql_match_operator '$input'";
+ break;
+ case 'tag':
+ $key = md5($input . $sql_match_operator);
+ $where[] = "`realtag_$key`.`match` > 0";
+ $join['tag'][$key] = "$sql_match_operator '$input'";
+ break;
+ default:
+ // Nihil
+ break;
+ } // switch on ruletype
+ } // foreach rule
+
+ $where_sql = implode(" $sql_logic_operator ", $where);
+
+ 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`='artist' " .
+ "AND `tag`.`name` $value GROUP BY `object_id`".
+ ") AS realtag_$key " .
+ "ON `artist`.`id`=`realtag_$key`.`object_id`";
+ }
+
+ $table_sql = implode(' ', $table);
+
+ return array(
+ 'base' => 'SELECT DISTINCT(`artist`.`id`) FROM `artist`',
+ 'join' => $join,
+ 'where' => $where,
+ 'where_sql' => $where_sql,
+ 'table' => $table,
+ 'table_sql' => $table_sql
+ );
+ } // artist_to_sql
+
+ /**
+ * 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]);
+ foreach ($this->basetypes[$type] as $operator) {
+ if ($operator['name'] == $rule[1]) {
+ 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 'catalog':
+ $where[] = "`song`.`catalog` $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;
+ } // end switch on type
+ } // end foreach over rules
+
+ $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'";
+ }
+
+ $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
+ );
+ } // end song_to_sql
+
+ /**
+ * video_to_sql
+ * Handles the generation of the SQL for video searches.
+ */
+ private function video_to_sql() {
+ $sql_logic_operator = $this->logic_operator;
+
+ $where = array();
+
+
+ foreach ($this->rules as $rule) {
+ $type = $this->name_to_basetype($rule[0]);
+ foreach ($this->basetypes[$type] as $operator) {
+ if ($operator['name'] == $rule[1]) {
+ break;
+ }
+ }
+ $input = $this->mangle_data($rule[2], $type, $operator);
+ $sql_match_operator = $operator['sql'];
+
+ switch ($rule[0]) {
+ case 'filename':
+ $where[] = "`video`.`file` $sql_match_operator '$input'";
+ break;
+ default:
+ // WE WILLNA BE FOOLED AGAIN!
+ } // switch on ruletype
+ } // foreach rule
+
+ $where_sql = implode(" $sql_logic_operator ", $where);
+
+ return array(
+ 'base' => 'SELECT DISTINCT(`video`.`id`) FROM `video`',
+ 'where' => $where,
+ 'where_sql' => $where_sql
+ );
+ } // end video_to_sql
} // end of Search class
?>
generated by cgit (git 2.34.1) at 2025-08-13 09:52:31 +0000