function checkCollectionStatus()
{
if ($result = generic_sql_query("SELECT Value FROM Statstable WHERE Item = 'ListVersion'")) {
$lv = 0;
while ($obj = $result->fetch(PDO::FETCH_OBJ)) {
$found = true;
$lv = $obj->Value;
}
if ($lv == ROMPR_COLLECTION_VERSION) {
debuglog("Collection version is correct", "MYSQL", 8);
return "0";
} else {
if ($lv > 0) {
debuglog("Collection version is outdated - " . $lv, "MYSQL", 4);
return "1";
} else {
debuglog("Collection has not been built" . $lv, "MYSQL", 7);
return "2";
}
}
}
}
function check_and_update_track($trackobj, $albumindex, $artistindex, $artistname)
{
global $find_track, $update_track, $numdone, $prefs, $doing_search;
static $current_trackartist = null;
static $trackartistindex = null;
$ttid = null;
$lastmodified = null;
$hidden = 0;
$disc = 0;
$uri = null;
$issearchresult = 0;
// Why are we not checking by URI? That should be unique, right?
// Well, er. no. They're not.
// Especially Spotify returns the same URI multiple times if it's in mutliple playlists
// We CANNOT HANDLE that. Nor do we want to.
// The other advantage of this is that we can put an INDEX on Albumindex, TrackNo, and Title,
// which we can't do with Uri cos it's too long - this speeds the whole process up by a factor
// of about 32 (9 minutes when checking by URI vs 15 seconds this way, on my collection)
// Also, URIs might change if the user moves his music collection.
if ($prefs['collection_type'] == "sqlite") {
// Lord knows why, but we have to re-prepare these every single bloody time!
prepare_findtracks();
}
if ($find_track->execute(array($trackobj->tags['Title'], $albumindex, $trackobj->tags['Track'], $trackobj->tags['Disc'], $artistindex))) {
$obj = $find_track->fetch(PDO::FETCH_OBJ);
if ($obj) {
$ttid = $obj->TTindex;
$lastmodified = $obj->LastModified;
$hidden = $obj->Hidden;
$disc = $obj->Disc;
$issearchresult = $obj->isSearchResult;
}
} else {
show_sql_error();
return false;
}
// NOTE: It is imperative that the search results have been tidied up -
// i.e. there are no 1s or 2s in the database before we do a collection update
// When doing a search, we MUST NOT change lastmodified of any track, because this will cause
// user-added tracks to get a lastmodified date, and lastmodified == NULL
// is how we detect user-added tracks and prevent them being deleted on collection updates
if ($ttid) {
if (!$doing_search && $trackobj->tags['Last-Modified'] != $lastmodified || $doing_search && $issearchresult == 0 || $trackobj->tags['Disc'] != $disc && $trackobj->tags['Disc'] !== '' || $hidden != 0) {
if ($prefs['debug_enabled'] > 6) {
# Don't bother doing all these string comparisons if debugging is disabled. It's slow.
debuglog(" Updating track with ttid {$ttid} because :", "MYSQL", 7);
if (!$doing_search && $lastmodified === null) {
debuglog(" LastModified is not set in the database", "MYSQL", 7);
}
if (!$doing_search && $trackobj->tags['Last-Modified'] === null) {
debuglog(" TrackObj LastModified is NULL too!", "MYSQL", 7);
}
if (!$doing_search && $lastmodified != $trackobj->tags['Last-Modified']) {
debuglog(" LastModified has changed: We have " . $lastmodified . " but track has " . $trackobj->tags['Last-Modified'], "MYSQL", 7);
}
if ($disc != $trackobj->tags['Disc']) {
debuglog(" Disc Number has changed: We have " . $disc . " but track has " . $trackobj->tags['Disc'], "MYSQL", 7);
}
if ($hidden != 0) {
debuglog(" It is hidden", "MYSQL", 7);
}
}
$newsearchresult = 0;
$newlastmodified = $trackobj->tags['Last-Modified'];
if ($issearchresult == 0 && $doing_search) {
$newsearchresult = $hidden != 0 ? 3 : 1;
debuglog(" It needs to be marked as a search result : Value " . $newsearchresult, "MYSQL", 7);
$newlastmodified = $lastmodified;
}
if ($update_track->execute(array($trackobj->tags['Track'], $trackobj->tags['Time'], $trackobj->tags['Disc'], $newlastmodified, $trackobj->tags['file'], $albumindex, $newsearchresult, $ttid))) {
$numdone++;
check_transaction();
} else {
show_sql_error();
}
}
} else {
$a = $trackobj->get_artist_string();
if ($a != $current_trackartist || $trackartistindex == null) {
if ($artistname != $a && $a != null) {
$trackartistindex = check_artist($a, false);
} else {
$trackartistindex = $artistindex;
}
}
if ($trackartistindex == null) {
debuglog("ERROR! Trackartistindex is still null!", "MYSQL_TBT", 1);
return false;
}
$current_trackartist = $a;
$sflag = $doing_search ? 2 : 0;
$ttid = create_new_track($trackobj->tags['Title'], null, $trackobj->tags['Track'], $trackobj->tags['Time'], null, null, null, null, null, $trackobj->tags['file'], $trackartistindex, $artistindex, $albumindex, null, null, $trackobj->tags['Last-Modified'], $trackobj->tags['Disc'], null, null, 0, $trackobj->getImage(), $sflag);
$numdone++;
check_transaction();
}
if ($ttid == null) {
debuglog("ERROR! No ttid for track " . $trackobj->tags['file'], "MYSQL", 1);
} else {
if (!$doing_search) {
generic_sql_query("INSERT INTO Foundtracks (TTindex) VALUES (" . $ttid . ")", false, false);
}
}
}
function doDbCollection($terms, $domains, $resultstype)
{
// This can actually be used to search the database for title, album, artist, anything, rating, and tag
// But it isn't because we let Mopidy/MPD search for anything they support because otherwise we
// have to duplicate their entire database, which is daft.
// This function was written before I realised that... :)
// It's still used for searches where we're only looking for tags and/or ratings in conjunction with
// any of the above terms, because mopidy often returns incomplete search results.
global $mysqlc, $tree;
if ($mysqlc === null) {
connect_to_database();
}
$parameters = array();
$qstring = "SELECT t.*, al.*, a1.*, a2.Artistname AS AlbumArtistName ";
if (array_key_exists('rating', $terms)) {
$qstring .= ",rat.Rating ";
}
$qstring .= "FROM Tracktable AS t ";
if (array_key_exists('tag', $terms)) {
$qstring .= "JOIN (SELECT DISTINCT TTindex FROM TagListtable JOIN Tagtable AS tag USING (Tagindex) WHERE";
$tagterms = array();
foreach ($terms['tag'] as $tag) {
$parameters[] = trim($tag);
array_push($tagterms, " tag.Name LIKE ?");
}
$qstring .= implode(" OR", $tagterms);
$qstring .= ") AS j ON j.TTindex = t.TTindex ";
}
if (array_key_exists('rating', $terms)) {
$qstring .= "JOIN (SELECT * FROM Ratingtable WHERE Rating >= " . $terms['rating'] . ") AS rat ON rat.TTindex = t.TTindex ";
}
$qstring .= "JOIN Artisttable AS a1 ON a1.Artistindex = t.Artistindex ";
$qstring .= "JOIN Albumtable AS al ON al.Albumindex = t.Albumindex ";
$qstring .= "JOIN Artisttable AS a2 ON al.AlbumArtistindex = a2.Artistindex ";
if (array_key_exists('wishlist', $terms)) {
$qstring .= "WHERE t.Uri IS NULL";
} else {
$qstring .= "WHERE t.Uri IS NOT NULL ";
}
$qstring .= "AND t.Hidden = 0 AND t.isSearchResult < 2 ";
if (array_key_exists('artist', $terms)) {
$qstring .= "AND ";
if (array_key_exists('any', $terms)) {
$qstring .= "(";
}
$parameters[] = "%" . trim($terms['artist'][0]) . "%";
$qstring .= "a1.Artistname LIKE ? ";
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR a1.Artistname LIKE ?) ";
}
} else {
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR a1.Artistname LIKE ? ";
}
}
if (array_key_exists('album', $terms)) {
$qstring .= "AND ";
if (array_key_exists('any', $terms)) {
$qstring .= "(";
}
$parameters[] = "%" . trim($terms['album'][0]) . "%";
$qstring .= "al.Albumname LIKE ? ";
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR al.Albumname LIKE ?) ";
}
} else {
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR al.Albumname LIKE ? ";
}
}
if (array_key_exists('track_name', $terms)) {
$qstring .= "AND ";
if (array_key_exists('any', $terms)) {
$qstring .= "(";
}
$parameters[] = "%" . trim($terms['track_name'][0]) . "%";
$qstring .= "t.Title LIKE ? ";
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR t.Title LIKE ?) ";
}
} else {
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR t.Title LIKE ? ";
}
}
if (array_key_exists('file', $terms)) {
$qstring .= "AND ";
if (array_key_exists('any', $terms)) {
$qstring .= "(";
}
$parameters[] = "%" . trim($terms['file'][0]) . "%";
$qstring .= "t.Uri LIKE ? ";
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR t.Uri LIKE ?) ";
}
} else {
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR t.Uri LIKE ? ";
}
}
if (array_key_exists('albumartist', $terms)) {
$qstring .= "AND ";
if (array_key_exists('any', $terms)) {
$qstring .= "(";
}
$parameters[] = "%" . trim($terms['albumartist'][0]) . "%";
$qstring .= "AlbumArtistName LIKE ? ";
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR AlbumArtistName LIKE ?) ";
}
} else {
if (array_key_exists('any', $terms)) {
$parameters[] = "%" . trim($terms['any'][0]) . "%";
$qstring .= "OR AlbumArtistName LIKE ? ";
}
}
if (array_key_exists('date', $terms)) {
$qstring .= "AND ";
$parameters[] = trim($terms['date'][0]);
$qstring .= "al.Year = ? ";
}
if ($domains !== null) {
$qstring .= "AND (";
$domainterms = array();
foreach ($domains as $dom) {
$parameters[] = trim($dom) . "%";
array_push($domainterms, "t.Uri LIKE ?");
}
$qstring .= implode(" OR ", $domainterms);
$qstring .= ")";
}
debuglog("SQL Search String is " . $qstring, "SEARCH");
$fcount = 0;
if ($result = sql_prepare_query_later($qstring)) {
if ($result->execute($parameters)) {
while ($obj = $result->fetch(PDO::FETCH_OBJ)) {
if ($resultstype == "tree") {
$filedata = array('Artist' => array($obj->Artistname), 'Album' => $obj->Albumname, 'AlbumArtist' => array($obj->AlbumArtistName), 'file' => $obj->Uri, 'Title' => $obj->Title, 'Track' => $obj->TrackNo, 'Image' => $obj->Image, 'Time' => $obj->Duration, 'AlbumUri' => $obj->AlbumUri, 'Date' => $obj->Year, 'Last-Modified' => $obj->LastModified);
$tree->newItem($filedata);
$fcount++;
} else {
debuglog('Updating isSearchResult for TTindex ' . $obj->TTindex, "DBSEARCH", 8);
generic_sql_query("UPDATE Tracktable SET isSearchResult = 1 WHERE TTindex = " . $obj->TTindex);
}
}
} else {
show_sql_error();
}
} else {
show_sql_error();
}
return $fcount;
}
function preparePlaylist()
{
generic_sql_query("DROP TABLE IF EXISTS pltable");
generic_sql_query("CREATE TABLE pltable(TTindex INT UNSIGNED NOT NULL UNIQUE)");
}
function getWishlist()
{
global $mysqlc, $divtype, $prefs;
if ($mysqlc === null) {
connect_to_database();
}
$qstring = 'SELECT wishlist.*, Ratingtable.Rating FROM
(SELECT Tracktable.TTindex AS ttindex, Tracktable.Title AS track, Tracktable.TrackNo
AS num, Tracktable.Duration AS time, Tracktable.Disc as disc, Artisttable.Artistname
AS artist, Albumtable.Albumname AS album, Albumtable.Image AS image, Albumtable.ImgKey
AS imgkey FROM Tracktable JOIN Artisttable USING (Artistindex) JOIN Albumtable ON
Tracktable.Albumindex = Albumtable.Albumindex WHERE Uri IS NULL AND Hidden = 0
UNION
SELECT Tracktable.TTindex AS ttindex, Tracktable.Title AS track, Tracktable.TrackNo
AS num, Tracktable.Duration AS time, Tracktable.Disc as disc, Artisttable.Artistname
AS artist, "" AS Albumname, NULL as Image, NULL AS ImgKey FROM Tracktable JOIN
Artisttable USING (Artistindex) WHERE Albumindex IS NULL AND Uri IS NULL AND Hidden = 0)
AS wishlist
LEFT JOIN Ratingtable ON Ratingtable.TTindex = wishlist.ttindex
ORDER BY ';
foreach ($prefs['artistsatstart'] as $a) {
$qstring .= "CASE WHEN LOWER(artist) = LOWER('" . $a . "') THEN 1 ELSE 2 END, ";
}
if (count($prefs['nosortprefixes']) > 0) {
$qstring .= "(CASE ";
foreach ($prefs['nosortprefixes'] as $p) {
$phpisshitsometimes = strlen($p) + 2;
$qstring .= "WHEN LOWER(artist) LIKE '" . strtolower($p) . " %' THEN LOWER(SUBSTR(artist," . $phpisshitsometimes . ")) ";
}
$qstring .= "ELSE LOWER(artist) END)";
} else {
$qstring .= "LOWER(artist)";
}
$qstring .= ', LOWER (album),
num';
$current_artist = "";
$current_album = "";
$count = 0;
if ($result = generic_sql_query($qstring)) {
while ($obj = $result->fetch(PDO::FETCH_OBJ)) {
$bothclosed = false;
debuglog("Found Track " . $obj->track, "WISHLIST");
if ($current_artist != $obj->artist) {
if ($current_artist != "") {
print '</div></div>';
$bothclosed = true;
}
$current_artist = $obj->artist;
print artistHeader("wishlistartist_" . $count, $obj->artist);
print '<div id="wishlistartist_' . $count . '" class="dropmenu ' . $divtype . '">';
$count++;
$divtype = $divtype == "album1" ? "album2" : "album1";
}
if ($current_album != $obj->album) {
if (!$bothclosed && $current_album != "") {
print '</div>';
}
$current_album = $obj->album;
print albumHeader($obj->album, null, 'wishlistalbum_' . $count, $obj->image === null ? "no" : "yes", $obj->album == "" ? "yes" : "no", $obj->imgkey, $obj->image, null, null, null);
print '<div id="wishlistalbum_' . $count . '" class="dropmenu">';
$count++;
}
albumTrack(null, $obj->Rating, null, 0, $obj->num, $obj->track, 0, null, null);
}
}
}
$uris = array();
$qstring = "";
if ($mode == "random") {
if ($result = generic_sql_query(sql_recent_tracks(), true)) {
while ($obj = $result->fetch(PDO::FETCH_OBJ)) {
array_push($uris, $obj->Uri);
}
} else {
debuglog("Nope, that didn't work", "RECENTLY ADDED");
}
} else {
// This rather cumbersome code gives us albums in a random order but tracks in order.
// All attempts to do this with a single SQL query hit a brick wall.
// But then I don't know much about SQL.
$albums = array();
if ($result = generic_sql_query(sql_recent_albums())) {
while ($obj = $result->fetch(PDO::FETCH_OBJ)) {
if (!array_key_exists($obj->Albumindex, $albums)) {
$albums[$obj->Albumindex] = array($obj->TrackNo => $obj->Uri);
} else {
if (array_key_exists($obj->TrackNo, $albums[$obj->Albumindex])) {
array_push($albums[$obj->Albumindex], $obj->Uri);
} else {
$albums[$obj->Albumindex][$obj->TrackNo] = $obj->Uri;
}
}
}
shuffle($albums);
foreach ($albums as $a) {
ksort($a);
foreach ($a as $t) {
function hide_played_tracks()
{
generic_sql_query("CREATE TEMPORARY TABLE Fluff(TTindex INT UNSIGNED NOT NULL UNIQUE, PRIMARY KEY(TTindex)) AS SELECT TTindex FROM Tracktable JOIN Playcounttable USING (TTindex) WHERE isSearchResult = 2", true);
generic_sql_query("UPDATE Tracktable SET Hidden = 1, isSearchResult = 0 WHERE TTindex IN (SELECT TTindex FROM Fluff)", true);
}
debuglog("Populating Favourite Album Radio", "FAVEALBUMS");
$uris = array();
$qstring = "";
generic_sql_query("CREATE TEMPORARY TABLE alplaytable AS SELECT SUM(Playcount) AS playtotal,\n\tAlbumindex\n\tFROM (SELECT Playcount, Albumindex FROM Playcounttable JOIN Tracktable USING (TTindex)\n\tWHERE Playcount > 3) AS derived GROUP BY Albumindex ORDER BY " . SQL_RANDOM_SORT);
// This rather cumbersome code gives us albums in a random order but tracks in order.
// All attempts to do this with a single SQL query hit a brick wall.
// But then I don't know much about SQL.
$albums = array();
$uris = array();
$avgplays = 0;
if ($result = generic_sql_query("SELECT AVG(playtotal) AS plavg FROM alplaytable")) {
while ($obj = $result->fetch(PDO::FETCH_OBJ)) {
$avgplays = $obj->plavg;
}
}
if ($result = generic_sql_query("SELECT Uri, TrackNo, Albumindex FROM Tracktable JOIN alplaytable\n\tUSING (Albumindex) WHERE playtotal > " . $avgplays . " AND Uri IS NOT NULL AND Hidden = 0")) {
while ($obj = $result->fetch(PDO::FETCH_OBJ)) {
if (!array_key_exists($obj->Albumindex, $albums)) {
$albums[$obj->Albumindex] = array($obj->TrackNo => $obj->Uri);
} else {
if (array_key_exists($obj->TrackNo, $albums[$obj->Albumindex])) {
array_push($albums[$obj->Albumindex], $obj->Uri);
} else {
$albums[$obj->Albumindex][$obj->TrackNo] = $obj->Uri;
}
}
}
foreach ($albums as $a) {
ksort($a);
foreach ($a as $t) {
array_push($uris, $t);
function hide_played_tracks()
{
generic_sql_query("UPDATE Tracktable SET Hidden = 1, isSearchResult = 0 WHERE TTindex IN (SELECT TTindex FROM Tracktable JOIN Playcounttable USING (TTindex) WHERE isSearchResult = 2)", true);
}