/**
* Populates tracks collection with all library tracks matching criteria.
*
* @param array $parameters Requested parameters
*
* @return bool true if the database read is ok, false otherwise
*/
public function populateTracks($parameters)
{
require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
$connection = new DatabaseConnection();
//handle requested parameters
$sqlCondition = '';
foreach ($parameters as $parameter => $value) {
if (isset($value)) {
switch ($parameter) {
case 'trackTitle':
$sqlCondition .= ' AND `track`.`title` LIKE :trackTitle';
break;
case 'artistName':
$sqlCondition .= ' AND `artist`.`name` LIKE :artistName';
break;
case 'albumName':
$sqlCondition .= ' AND `album`.`name` LIKE :albumName';
break;
}
}
}
//prepare query
$query = $connection->prepare('SELECT `track`.`id`, `track`.`title`, `track`.`artist`, `artist`.`name` AS `artistName`, `track`.`album`, `album`.`name` AS `albumName`, `track`.`id` AS `file`, `cover`.`albumId` AS `coverId` FROM `track`, `artist` ,`album` LEFT JOIN `cover` ON `album`.`id`=`cover`.`albumId` AND `cover`.`status` = 1 WHERE `track`.`artist`=`artist`.`id` AND `track`.`album`=`album`.`id`' . $sqlCondition . ' ORDER BY `additionTime` DESC;');
//add query criteria value
foreach ($parameters as $parameter => $value) {
if (isset($value)) {
switch ($parameter) {
case 'trackTitle':
$query->bindValue(':trackTitle', "%{$value}%", PDO::PARAM_STR);
break;
case 'artistName':
$query->bindValue(':artistName', "%{$value}%", PDO::PARAM_STR);
break;
case 'albumName':
$query->bindValue(':albumName', "%{$value}%", PDO::PARAM_STR);
break;
}
}
}
if ($query->execute()) {
$this->tracks = $query->fetchAll(PDO::FETCH_CLASS);
foreach ($this->tracks as $track) {
$trackStructured = new Track();
$track = $trackStructured->structureData($track);
}
//return true to indicate tracks was successfully read
return true;
}
//return false to indicate an error occurred while reading tracks
return false;
}
/**
* Deletes a specific user's track.
*
* @return bool true if the track is deleted from the user's playlist, false on error
*/
public function delete()
{
if (isset($this->userId, $this->sequence)) {
require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
$connection = new DatabaseConnection();
$query = $connection->prepare('DELETE FROM `playlist` WHERE `userId`=:userId AND `sequence`=:sequence;');
$query->bindValue(':userId', $this->userId, PDO::PARAM_INT);
$query->bindValue(':sequence', $this->sequence, PDO::PARAM_INT);
//return true to indicate a successful track deletion
return $query->execute() && $query->rowCount() > 0;
}
//return false to indicate an error occurred while deleting track from user's playlist
return false;
}
/**
* Create SQL schema and tables.
* @param string $dbEngine Database engine (sqlite or mysql, default is sqlite)
* @param string $wmpDbName Database name (only for MySQL)
* @param string $adminUserPwd Admin user account password
* @return boolean True if schema is set
*/
public function createSchema($dbEngine = 'sqlite', $wmpDbName = 'wmp', $adminUserPwd = 'nimda')
{
try {
//connect to database
require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
$connection = new DatabaseConnection();
//clean previous schema (MySQL only)
if ($dbEngine == 'mysql') {
//get all tables
$query = $connection->prepare("SELECT table_name FROM information_schema.TABLES WHERE table_schema = :schema;");
$query->bindValue('schema', $wmpDbName);
if (!$query->execute()) {
throw new RuntimeException('Schema drop has failed: unable to get tables');
}
$tables = $query->fetchAll();
//disable foreign keys before droping table
$query = $connection->prepare("SET FOREIGN_KEY_CHECKS=0;");
$query->execute();
//drop each table
foreach ($tables as $table) {
$table_name = $table['table_name'];
$query = $connection->prepare("DELETE FROM {$wmpDbName}.{$table_name} WHERE 1;");
$query->execute();
$query = $connection->prepare("DROP TABLE {$wmpDbName}.{$table_name};");
if (!$query->execute()) {
throw new RuntimeException('Schema drop has failed on table ' . $table_name);
}
}
//enable foreign keys
$query = $connection->prepare("SET FOREIGN_KEY_CHECKS=1;");
$query->execute();
}
//load language dependant script
$sqlFilename = $_SERVER['DOCUMENT_ROOT'] . '/server/configuration/create-' . $dbEngine . '.sql';
//split each query (separated by the ";EOL")
$array = explode(";\n", file_get_contents($sqlFilename));
$nbLines = count($array);
for ($i = 0; $i < $nbLines; ++$i) {
//remove comments (-- and text behind) and handle line
$queryString = preg_replace('/--.*$/m', '', filter_var($array[$i]));
//remove EOL
$queryString = str_replace("\n", ' ', $queryString);
if ($queryString !== '' && $queryString !== ' ') {
//add end a ";" at the end of the query
$queryString .= ';';
//replace the default schema 'wmp' with the user's one (MySQL only)
$queryString = str_replace('`wmp`', "`{$wmpDbName}`", $queryString);
$query = $connection->prepare($queryString);
if ($query === false) {
//error during statement preparation, display cause
error_log(json_encode($connection->errorInfo()) . ' on query: ' . $queryString);
$results['tables']['Tables creation'] = $connection->errorInfo()[2];
//return to the main thread for displaying error
return false;
}
if (!$query->execute()) {
//error during statement execution, display cause
error_log(json_encode($query->errorInfo()) . ' on query: ' . $queryString);
$results['tables']['Tables creation'] = $query->errorInfo()[2];
$continue = false;
break;
}
//table creation is ok
$results['tables']['Tables creation'] = $i . ' tables set';
$continue = true;
}
}
$results['tables']['Admin user account'] = 'Admin user account password has not been changed';
if ($continue && $adminUserPwd !== '') {
$results['tables']['Admin user account'] = 'Admin user account password has been changed';
//update admin user password
$query = $connection->prepare('UPDATE `user` SET `password`=:password WHERE `id`=1;');
$query->bindValue(':password', md5($adminUserPwd), PDO::PARAM_STR);
if (!$query->execute()) {
$results['tables']['Admin user account'] = 'Admin user account password has not been changed';
return false;
}
return true;
}
return $continue;
} catch (Exception $exception) {
$results['user']['Database access'] = $exception->getMessage();
return false;
}
}
/**
* Get album cover.
*
* @return bool|string Stream of the cover image or false on failure
*/
public function getCoverImage()
{
//check if there is already a cover
require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
$connection = new DatabaseConnection();
$query = $connection->prepare('SELECT `image` FROM `cover` WHERE `albumId` = :albumId AND `status` = 1;');
$query->bindValue(':albumId', $this->id, PDO::PARAM_INT);
$query->execute();
$image = $query->fetchColumn();
if ($image !== false) {
//image found in database
return $image;
}
//not found in database, try to get it from Cover Art Archive if there is a MBID
if ($this->getMBID()) {
return $this->callCoverArtArchive();
}
//returns there is no MBID for this album
return false;
}
/**
* Return all users.
*
* @return array All users
*/
public function getAllUsers()
{
require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
$connection = new DatabaseConnection();
$query = $connection->prepare('SELECT * FROM `user`;');
if ($query->execute()) {
//return array of users
return $query->fetchAll(PDO::FETCH_CLASS, 'User');
}
//indicate there is a problem during querying
return false;
}
/**
* Delete an artist from database.
*
* @return bool True on success or false on failure
*/
public function delete()
{
if (is_int($this->id)) {
require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
$connection = new DatabaseConnection();
$query = $connection->prepare('DELETE FROM `artist` WHERE `id` = :id;');
$query->bindValue(':id', $this->id, PDO::PARAM_INT);
//returns deletion result
return $query->execute();
}
//returns an error if no identifier was provided
return false;
}
