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; }