Location: PHPKode > scripts > AmaroK Web Frontend > obsidianmusic-2.3.0/inc/sql.php
<?php

//
//  ObsidianMusic
//   a.k.a. amaroK Web Frontend 2.0
//
//  Created 11/24/05
//   Copyright (C) Ryan Loebs (ObsidianX) 2005/2006
//   See LICENSE for GPL
//
//  sql.php - SQL queries and commands
//
///////////////////////////////////////

if(eregi("sql.php", $_SERVER['PHP_SELF'])){
	die("Cannot access directly.");
}

// Queries array
function getquery($q, $sort = "song", $input = ""){
	switch($q) {
		case 'count':
			return "SELECT COUNT(*) FROM tracks";
		case 'latest':
			// FIXME: This query _really_ needs opmization...
			return "SELECT tracks.*, artists.name AS artistN, albums.name AS albumN 
			   FROM tracks
			   LEFT JOIN artists ON (tracks.artist=artists.id)
			   LEFT JOIN albums ON (tracks.album=albums.id)
			   ORDER BY createdate DESC
			   LIMIT 10";
		case 'all':
			return "SELECT tracks.*, albums.name AS albumN, artists.name AS artistN 
			  FROM tracks
			  LEFT JOIN artists ON (tracks.artist=artists.id)
			  LEFT JOIN albums ON (tracks.album=albums.id)
			  ORDER BY artists.name, tracks.title
			  LIMIT {$input[':limit']} OFFSET {$input[':offset']}";
			  
		case 'random_song':
			return "SELECT id FROM tracks ORDER BY RAND() LIMIT 1";
			
		case 'abc':
			return "SELECT tracks.*,
				 albums.name AS albumN,
				 artists.name AS artistN
			  FROM tracks,
			       albums,
			       artists
			  WHERE artists.name LIKE '"hide@address.com($input)."%' COLLATE UTF8_general_ci
			  AND artists.id=tracks.artist
			  AND albums.id=tracks.album
			  ORDER BY artists.name,title ASC";
		case 'getgenres':
			return "SELECT *
				FROM genres
				ORDER BY name ASC";
		case 'getgname':
			return "SELECT name
				FROM genres
				WHERE id="hide@address.com($input);
		case 'genre':
			return "SELECT tracks.*,
				   artists.name AS artistN,
				   albums.name AS albumN
				FROM tracks,
				     artists,
				     albums
				WHERE genre="hide@address.com($input)."
				AND tracks.artist=artists.id
				AND tracks.album=albums.id
				ORDER BY artists.name,
					 title ASC";
		case 'getyears':
			return "SELECT *
				FROM years
				ORDER BY name ASC";
		case 'getyname':
			return "SELECT name
				FROM years
				WHERE id="hide@address.com($input);
		case 'year':
			return "SELECT tracks.*,
				  albums.name AS albumN,
			          artists.name AS artistN
			   FROM tracks,
			        artists,
			        albums
			   WHERE year="hide@address.com($input)."
			   AND artists.id=tracks.artist
			   AND albums.id=tracks.album
			   ORDER BY artists.name,
			            title ASC";
		case 'artist':
			return "SELECT * FROM artists
			     WHERE name
			     REGEXP '^"hide@address.com($input)."'
			     ORDER BY name ASC";
		case 'album':
			return "SELECT * FROM albums
			    WHERE name REGEXP '^"hide@address.com($input)."'
			    ORDER BY name ASC";
		case 'alview':
			return "SELECT tracks.id, tracknumber,
				    title,
				    createdate,
				    length,
				    tracks.artist,
				    artists.name AS artistN,
				    albums.name AS albumN,
				    album,
				    image
			     FROM tracks,
			          artists,
			          albums
			     WHERE album="hide@address.com($input)."
			     AND artists.id=tracks.artist
			     AND albums.id=tracks.album
			     ORDER BY tracknumber, title ASC";
		case 'alcover':
			return "SELECT path
			      FROM images
			      WHERE id='".(int)$input."'";
		case 'arsongs':
			return "SELECT tracks.*,
				     albums.name AS albumN,
				     artists.name AS artistN
			      FROM tracks,
			           artists,
			           albums
			      WHERE artists.id=tracks.artist
			      AND albums.id=tracks.album
			      AND tracks.artist="hide@address.com($input)."
			      ORDER BY title ASC";
		case 'arlen':
			return "SELECT length
			    FROM tracks
			    WHERE artist="hide@address.com($input);
		case 'arname':
			return "SELECT name
			     FROM artists
			     WHERE id="hide@address.com($input);
		case 'aralcount':
			return "SELECT COUNT(DISTINCT tracks.album) AS track_count
				FROM tracks,albums
				WHERE tracks.artist="hide@address.com($input)."
				AND albums.id=tracks.album
				AND albums.name!=\"\"";
		case 'aralname':
			return "SELECT DISTINCT albums.name,
				      albums.id
			       FROM tracks,albums
			       WHERE tracks.album=albums.id
			       AND tracks.artist='"hide@address.com($input)."'";
		case 'basicsearch':
			return "SELECT tracks.*,
					 artists.name AS artistN,
					 albums.name AS albumN
				  FROM tracks,
				       artists,
				       albums
				  WHERE tracks.artist=artists.id
				  AND tracks.album=albums.id
				  AND (artists.name LIKE '%"hide@address.com($input)."%' COLLATE UTF8_general_ci
				       OR title LIKE '%"hide@address.com($input)."%' COLLATE UTF8_general_ci
				       OR albums.name LIKE '%"hide@address.com($input)."%' COLLATE UTF8_general_ci)
				  ORDER BY artists.name,title ASC";
		case 'advsearch':
			return "SELECT tracks.*,
				       artists.name AS artistN,
				       tracks.artist,
				       albums.name AS albumN
				FROM tracks,
				     artists,
				     albums,
				     genres
				WHERE genres.id=tracks.genre
				AND artists.id=tracks.artist
				AND albums.id=tracks.album
				".@$input."
				ORDER BY artists.name,title ASC";
				
		case 'songlength':
			return "SELECT length FROM tracks WHERE id=".(int)$input;
		
		/**
		 * Get info about tracks in specified list
		 * $input - array if tracks.id's
		 */
		case 'tracks':
			return "SELECT tracks.id, url, title, artist, length, deviceid, artists.name AS artistN, urls.rpath
			        FROM tracks
			        LEFT JOIN artists ON (artists.id=tracks.artist)
			        LEFT JOIN urls ON (url=urls.id)
			        WHERE tracks.id IN (".implode(', ', $input).")";
		case 'getfile':
		case 'getfile_id':
			return "SELECT tracks.id, url, title, artist, length, deviceid, artists.name AS artistN, urls.rpath
			        FROM tracks,artists,urls
			        WHERE tracks.id = ".(int)$input." AND url=urls.id AND artists.id=tracks.artist";
		case 'getalbum':
			return "SELECT tracks.id, url,
				      title,
				      artist,
				      length,
				      createdate,
				      deviceid,
				      artists.name AS artistN
			       FROM tracks
			       LEFT JOIN artists ON (artists.id=tracks.artist)
			       LEFT JOIN urls ON (url=urls.id)
			       WHERE album=\""hide@address.com($input)."\"
			       AND artists.id=tracks.artist
			       ORDER BY tracknumber, title ASC";
		case 'getalbumn':
			return "SELECT name
				FROM albums
				WHERE id="hide@address.com($input);
		case 'getdevice':
			return "SELECT lastmountpoint
				FROM devices
				WHERE id="hide@address.com($input);
		default:
			echo "Wrong query type: $q";

	}// end switch
}

// SQL Functions
switch($dbtype){
	// Grab MySQL
	case 0:
		require_once("inc/sql/mysql.php");
		break;
	
	// Grab PostgreSQL
	case 1:
		require_once("inc/sql/postgresql.php");
		break;
	
	// Grab SQLite
	//  REQUIRES PHP5
	case 2:
		require_once("inc/sql/sqlite.php");
		break;
}

Return current item: AmaroK Web Frontend