/// <summary>
/// Compress banshee database if it is not already compressed.
/// </summary>
/// This won't do anything if last compression is not more than _DB_CACHED_COMPRESSION
/// senconds ago. To avoid that you can reset _dbCompressTime to 0.
///
/// See https://github.com/Knickedi/banshee-remote for more.
public static void CompressDatabase()
{
if (Timestamp() - _dbCompressTime < _DB_CACHED_COMPRESSION)
{
return;
}
try {
_dbCompressTime = Timestamp();
File.Delete(DatabasePath(true));
File.Delete(DatabasePath(true) + "-journal");
File.Copy(DatabasePath(false), DatabasePath(true));
// database was in use, unlock it or queries will just fail
HyenaSqliteConnection db = new HyenaSqliteConnection(DatabasePath(true));
db.BeginTransaction();
db.CommitTransaction();
db.Dispose();
db = new HyenaSqliteConnection(DatabasePath(true));
db.BeginTransaction();
IDataReader cursor = db.Query(
"SELECT tbl_name FROM sqlite_master WHERE "
+ "type='table' AND tbl_name NOT IN "
+ "('CoreTracks', 'CoreArtists', 'CoreAlbums', 'sqlite_stat1', 'sqlite_stat2');");
// drop unnecessary tables
while (cursor.Read())
{
db.Execute("DROP TABLE " + cursor.Get(0, typeof(string)) + ";");
}
// clear analytic data (if available)
if (db.TableExists("sqlite_stat1"))
{
db.Execute("DELETE FROM sqlite_stat1;");
}
if (db.TableExists("sqlite_stat2"))
{
db.Execute("DELETE FROM sqlite_stat2;");
}
cursor.Dispose();
// remove unecessary columns from tracks table
db.Execute("CREATE TABLE tracks (\n"
+ " _id INTEGER PRIMARY KEY,\n"
+ " artistId INTEGER,\n"
+ " albumId INTEGER,\n"
+ " title TEXT,\n"
+ " trackNumber INTEGER,\n"
+ " duration INTEGER,\n"
+ " year INTEGER,\n"
+ " genre TEXT,\n"
+ " rating INTEGER\n"
+ ");");
db.Execute("INSERT INTO tracks(_id, artistId, albumId, "
+ "title, trackNumber, duration, year, genre, rating) "
+ "SELECT TrackID, ArtistID, AlbumId, "
+ "Title, TrackNumber, Duration, Year, Genre, Rating "
+ "FROM CoreTracks;");
// remove unecessary columns from artist table
db.Execute("CREATE TABLE artists (\n"
+ " _id INTEGER PRIMARY KEY,\n"
+ " name TEXT\n"
+ ");");
db.Execute("INSERT INTO artists(_id, name) "
+ "SELECT ArtistID, Name FROM CoreArtists;");
// remove unecessary columns from album table
db.Execute("CREATE TABLE albums (\n"
+ " _id INTEGER PRIMARY KEY,\n"
+ " artistId INTEGER,\n"
+ " title TEXT,\n"
+ " artId TEXT\n"
+ ");");
if (db.ColumnExists("CoreAlbums", "ArtworkID"))
{
db.Execute("INSERT INTO albums(_id, artistId, title, artId) "
+ "SELECT AlbumID, ArtistID, Title, ArtworkID FROM CoreAlbums;");
}
else
{
// old banshee versions don't cache the cover ID - fix that manually
Dictionary <int, string> artists = new Dictionary <int, string>();
IDataReader r = db.Query("SELECT ArtistId, Name FROM CoreArtists;");
while (r.Read())
{
artists.Add(r.Get <int>(0), r.Get <string>(1));
}
r.Dispose();
r = db.Query("SELECT AlbumID, ArtistID, Title FROM CoreAlbums;");
while (r.Read())
{
int artistId = r.Get <int>(1);
string album = r.Get <string>(2);
string artId = "";
string artist = null;
if (artists.TryGetValue(artistId, out artist))
{
artId = CoverArtSpec.CreateArtistAlbumId(artist ?? "", album ?? "").Replace("'", "''");
}
db.Execute("INSERT INTO albums(_id, artistId, title, artId) VALUES ("
+ r.Get <int>(0) + "," + artistId + ","
+ "'" + (album ?? "").Replace("'", "''") + "', "
+ "'" + artId + "');");
}
r.Dispose();
}
db.Execute("DROP TABLE CoreTracks;");
db.Execute("DROP TABLE CoreArtists;");
db.Execute("DROP TABLE CoreAlbums;");
db.CommitTransaction();
db.Execute("VACUUM;");
db.Dispose();
SetDbCompressTimeFromFile();
} catch (Exception e) {
Log.Error("remote listener failed to compress database: " + e.Message);
File.Delete(DatabasePath(true));
}
}
protected bool TableExists(string tableName)
{
return(connection.TableExists(tableName));
}