function sql_prepare_query_later($query)
{
global $mysqlc;
$stmt = $mysqlc->prepare($query);
if ($stmt === FALSE) {
show_sql_error("Query Prep Error For " . $query, 2);
}
return $stmt;
}
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;
}
