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 prepare_findtracks() { global $find_track, $update_track; if ($find_track = sql_prepare_query_later("SELECT TTindex, Disc, LastModified, Hidden, isSearchResult FROM Tracktable WHERE Title=?\n\t\tAND ((Albumindex=? AND TrackNo=? AND Disc=?)" . " OR (Artistindex=? AND TrackNo IS NULL AND Uri IS NULL))")) { } else { show_sql_error(); exit(1); } if ($update_track = sql_prepare_query_later("UPDATE Tracktable SET Trackno=?, Duration=?, Disc=?, LastModified=?, Uri=?, Albumindex=?,\n\t\tisSearchResult=?, Hidden=0 WHERE TTindex=?")) { } else { show_sql_error(); exit(1); } }
function check_sql_tables() { global $mysqlc; if (generic_sql_query("CREATE TABLE IF NOT EXISTS Tracktable(" . "TTindex INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, " . "PRIMARY KEY(TTindex), " . "Title VARCHAR(255), " . "Albumindex INT UNSIGNED, " . "TrackNo SMALLINT UNSIGNED, " . "Duration INT UNSIGNED, " . "Artistindex INT UNSIGNED, " . "Disc TINYINT(3) UNSIGNED, " . "Uri VARCHAR(2000), " . "LastModified INT UNSIGNED, " . "Hidden TINYINT(1) UNSIGNED DEFAULT 0, " . "DateAdded TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " . "isSearchResult TINYINT(1) UNSIGNED DEFAULT 0, " . "INDEX(Albumindex), " . "INDEX(Title), " . "INDEX(TrackNo)) ENGINE=InnoDB")) { debuglog(" Tracktable OK", "MYSQL_CONNECT"); } else { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking Tracktable : " . $err); } if (generic_sql_query("CREATE TABLE IF NOT EXISTS Albumtable(" . "Albumindex INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, " . "PRIMARY KEY(Albumindex), " . "Albumname VARCHAR(255), " . "AlbumArtistindex INT UNSIGNED, " . "AlbumUri VARCHAR(255), " . "Year YEAR, " . "Searched TINYINT(1) UNSIGNED, " . "ImgKey CHAR(32), " . "mbid CHAR(40), " . "Domain CHAR(32), " . "Image VARCHAR(255), " . "INDEX(Albumname), " . "INDEX(AlbumArtistindex), " . "INDEX(Domain), " . "INDEX(ImgKey)) ENGINE=InnoDB")) { debuglog(" Albumtable OK", "MYSQL_CONNECT"); } else { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking Albumtable : " . $err); } if (generic_sql_query("CREATE TABLE IF NOT EXISTS Artisttable(" . "Artistindex INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, " . "PRIMARY KEY(Artistindex), " . "Artistname VARCHAR(255), " . "INDEX(Artistname)) ENGINE=InnoDB")) { debuglog(" Artisttable OK", "MYSQL_CONNECT"); } else { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking Artisttable : " . $err); } if (generic_sql_query("CREATE TABLE IF NOT EXISTS Ratingtable(" . "TTindex INT UNSIGNED, " . "PRIMARY KEY(TTindex), " . "Rating TINYINT(1) UNSIGNED) ENGINE=InnoDB")) { debuglog(" Ratingtable OK", "MYSQL_CONNECT"); } else { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking Ratingtable : " . $err); } if (generic_sql_query("CREATE TABLE IF NOT EXISTS Tagtable(" . "Tagindex INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, " . "PRIMARY KEY(Tagindex), " . "Name VARCHAR(255)) ENGINE=InnoDB")) { debuglog(" Tagtable OK", "MYSQL_CONNECT"); } else { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking Tagtable : " . $err); } if (generic_sql_query("CREATE TABLE IF NOT EXISTS TagListtable(" . "Tagindex INT UNSIGNED NOT NULL REFERENCES Tagtable(Tagindex), " . "TTindex INT UNSIGNED NOT NULL REFERENCES Tracktable(TTindex), " . "PRIMARY KEY (Tagindex, TTindex)) ENGINE=InnoDB")) { debuglog(" TagListtable OK", "MYSQL_CONNECT"); } else { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking TagListtable : " . $err); } if (generic_sql_query("CREATE TABLE IF NOT EXISTS Playcounttable(" . "TTindex INT UNSIGNED NOT NULL REFERENCES Tracktable(TTindex), " . "Playcount INT UNSIGNED NOT NULL, " . "PRIMARY KEY (TTindex)) ENGINE=InnoDB")) { debuglog(" Playcounttable OK", "MYSQL_CONNECT"); } else { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking Playcounttable : " . $err); } if (generic_sql_query("CREATE TABLE IF NOT EXISTS Trackimagetable(" . "TTindex INT UNSIGNED NOT NULL REFERENCES Tracktable(TTindex), " . "Image VARCHAR(500), " . "PRIMARY KEY (TTindex)) ENGINE=InnoDB")) { debuglog(" Trackimagetable OK", "MYSQL_CONNECT"); } else { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking Trackimagetable : " . $err); } if (!generic_sql_query("CREATE TABLE IF NOT EXISTS Statstable(Item CHAR(11), PRIMARY KEY(Item), Value INT UNSIGNED) ENGINE=InnoDB")) { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking Statstable : " . $err); } // Check schema version and update tables as necessary $sv = 0; if ($result = generic_sql_query("SELECT Value FROM Statstable WHERE Item = 'SchemaVer'")) { if ($result->rowCount() == 0) { debuglog("No Schema Version Found - initialising table", "SQL_CONNECT"); generic_sql_query("INSERT INTO Statstable (Item, Value) VALUES ('ListVersion', '0')"); generic_sql_query("INSERT INTO Statstable (Item, Value) VALUES ('ArtistCount', '0')"); generic_sql_query("INSERT INTO Statstable (Item, Value) VALUES ('AlbumCount', '0')"); generic_sql_query("INSERT INTO Statstable (Item, Value) VALUES ('TrackCount', '0')"); generic_sql_query("INSERT INTO Statstable (Item, Value) VALUES ('TotalTime', '0')"); generic_sql_query("INSERT INTO Statstable (Item, Value) VALUES ('SchemaVer', '" . ROMPR_SCHEMA_VERSION . "')"); $sv = ROMPR_SCHEMA_VERSION; debuglog("Statstable populated", "MYSQL_CONNECT"); } else { while ($obj = $result->fetch(PDO::FETCH_OBJ)) { $sv = $obj->Value; } } } else { $err = $mysqlc->errorInfo()[2]; return array(false, "Error While Checking Database Schema Version : " . $err); } if ($sv > ROMPR_SCHEMA_VERSION) { debuglog("Schema Mismatch! We are version " . ROMPR_SCHEMA_VERSION . " but database is version " . $sv, "MYSQL_CONNECT"); return array(false, "Your database has version number " . $sv . " but this version of rompr only handles version " . ROMPR_SCHEMA_VERSION); } while ($sv < ROMPR_SCHEMA_VERSION) { switch ($sv) { case 0: debuglog("BIG ERROR! No Schema Version found!!", "SQL"); return array(false, "Database Error - could not read schema version. Cannot continue."); break; case 1: debuglog("Updating FROM Schema version 1 TO Schema version 2", "SQL"); generic_sql_query("ALTER TABLE Albumtable DROP Directory"); generic_sql_query("UPDATE Statstable SET Value = 2 WHERE Item = 'SchemaVer'"); break; case 2: debuglog("Updating FROM Schema version 2 TO Schema version 3", "SQL"); generic_sql_query("ALTER TABLE Tracktable ADD Hidden TINYINT(1) UNSIGNED DEFAULT 0"); generic_sql_query("UPDATE Tracktable SET Hidden = 0 WHERE Hidden IS NULL"); generic_sql_query("UPDATE Statstable SET Value = 3 WHERE Item = 'SchemaVer'"); break; case 3: debuglog("Updating FROM Schema version 3 TO Schema version 4", "SQL"); generic_sql_query("UPDATE Tracktable SET Disc = 1 WHERE Disc IS NULL OR Disc = 0"); generic_sql_query("UPDATE Statstable SET Value = 4 WHERE Item = 'SchemaVer'"); break; case 4: debuglog("Updating FROM Schema version 4 TO Schema version 5", "SQL"); generic_sql_query("UPDATE Albumtable SET Searched = 0 WHERE Image NOT LIKE 'albumart%'"); generic_sql_query("ALTER TABLE Albumtable DROP Image"); generic_sql_query("UPDATE Statstable SET Value = 5 WHERE Item = 'SchemaVer'"); break; case 5: debuglog("Updating FROM Schema version 5 TO Schema version 6", "SQL"); generic_sql_query("DROP INDEX Disc on Tracktable"); generic_sql_query("UPDATE Statstable SET Value = 6 WHERE Item = 'SchemaVer'"); break; case 6: debuglog("Updating FROM Schema version 6 TO Schema version 7", "SQL"); // This was going to be a nice datestamp but newer versions of mysql don't work that way generic_sql_query("ALTER TABLE Tracktable ADD DateAdded TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"); generic_sql_query("UPDATE Tracktable SET DateAdded = FROM_UNIXTIME(LastModified) WHERE LastModified IS NOT NULL AND LastModified > 0"); generic_sql_query("UPDATE Statstable SET Value = 7 WHERE Item = 'SchemaVer'"); break; case 7: debuglog("Updating FROM Schema version 7 TO Schema version 8", "SQL"); // Since we've changed the way we're joining artist names together, // rather than force the database to be recreated and screw up everyone's // tags and rating, just modify the artist data. $stmt = sql_prepare_query_later("UPDATE Artisttable SET Artistname = ? WHERE Artistindex = ?"); if ($stmt !== FALSE) { if ($result = generic_sql_query("SELECT * FROM Artisttable")) { while ($obj = $result->fetch(PDO::FETCH_OBJ)) { $artist = (string) $obj->Artistname; $art = explode(' & ', $artist); if (count($art) > 2) { $f = array_slice($art, 0, count($art) - 1); $newname = implode($f, ", ") . " & " . $art[count($art) - 1]; debuglog("Updating artist name from " . $artist . " to " . $newname, "UPGRADE_SCHEMA"); $stmt->execute(array($newname, $obj->Artistindex)); } } generic_sql_query("UPDATE Statstable SET Value = 8 WHERE Item = 'SchemaVer'"); } else { $err = $mysqlc->errorInfo()[2]; debuglog("Error Updating to version 8 " . $err, "MYSQL_CONNECT"); return array(false, "There was an error while updating your database to version 8 : " . $err); } } else { $err = $mysqlc->errorInfo()[2]; debuglog("Error Updating to version 8 " . $err, "MYSQL_CONNECT"); return array(false, "There was an error while updating your database to version 8 : " . $err); } break; case 8: debuglog("Updating FROM Schema version 8 TO Schema version 9", "SQL"); // We removed the image column earlier, but I've decided we need it again // because some mopidy backends supply images and archiving them all makes // creating the collection take waaaaay too long. generic_sql_query("ALTER TABLE Albumtable ADD Image VARCHAR(255)"); // So we now need to recreate the image database. Sadly this means that people using Beets // will lose their album images. if ($result = generic_sql_query("SELECT Albumindex, ImgKey FROM Albumtable")) { while ($obj = $result->fetch(PDO::FETCH_OBJ)) { if (file_exists('albumart/small/' . $obj->ImgKey . '.jpg')) { generic_sql_query("UPDATE Albumtable SET Image = 'albumart/small/" . $obj->ImgKey . ".jpg', Searched = 1 WHERE Albumindex = " . $obj->Albumindex); } else { generic_sql_query("UPDATE Albumtable SET Image = '', Searched = 0 WHERE Albumindex = " . $obj->Albumindex); } } generic_sql_query("UPDATE Statstable SET Value = 9 WHERE Item = 'SchemaVer'"); } else { $err = $mysqlc->errorInfo()[2]; debuglog("Error Updating to version 8 " . $err, "MYSQL_CONNECT"); return array(false, "There was an error while updating your database to version 9 : " . $err); } break; case 9: debuglog("Updating FROM Schema version 9 TO Schema version 10", "SQL"); generic_sql_query("ALTER TABLE Albumtable DROP NumDiscs"); generic_sql_query("UPDATE Statstable SET Value = 10 WHERE Item = 'SchemaVer'"); break; case 10: debuglog("Updating FROM Schema version 10 TO Schema version 11", "SQL"); generic_sql_query("ALTER TABLE Albumtable DROP IsOneFile"); generic_sql_query("UPDATE Statstable SET Value = 11 WHERE Item = 'SchemaVer'"); break; case 11: debuglog("Updating FROM Schema version 11 TO Scheme version 12", "SQL"); generic_sql_query("ALTER TABLE Tracktable ADD isSearchResult TINYINT(1) UNSIGNED DEFAULT 0"); generic_sql_query("UPDATE Statstable SET Value = 12 WHERE Item = 'SchemaVer'"); break; case 12: debuglog("Updating FROM Schema version 12 TO Scheme version 13", "SQL"); generic_sql_query("ALTER TABLE Albumtable CHANGE Spotilink AlbumUri VARCHAR(255)"); generic_sql_query("UPDATE Statstable SET Value = 13 WHERE Item = 'SchemaVer'"); break; case 13: debuglog("Updating FROM Schema version 13 TO Scheme version 14", "SQL"); // Nothing to do here, this is for SQLite only. generic_sql_query("UPDATE Statstable SET Value = 14 WHERE Item = 'SchemaVer'"); break; case 14: debuglog("Updating FROM Schema version 14 TO Scheme version 15", "SQL"); generic_sql_query("ALTER TABLE Tracktable MODIFY LastModified CHAR(32)"); generic_sql_query("UPDATE Statstable SET Value = 15 WHERE Item = 'SchemaVer'"); break; } $sv++; } return array(true, ""); }