public override void UpdateUnfilteredAggregates ()
{
HyenaSqliteCommand count_command = new HyenaSqliteCommand (String.Format (
"SELECT COUNT(*) {0} AND PodcastItems.IsRead = 0", UnfilteredQuery
));
UnfilteredCount = Connection.Query<int> (count_command);
}
private void CreatePlaylist(object o, EventArgs args)
{
if (finished) {
return;
}
finished = true;
try {
PlaylistSource playlist = new PlaylistSource (name, source);
playlist.Save ();
source.AddChildSource (playlist);
HyenaSqliteCommand insert_command = new HyenaSqliteCommand (String.Format (
@"INSERT INTO CorePlaylistEntries (PlaylistID, TrackID) VALUES ({0}, ?)", playlist.DbId));
//ServiceManager.DbConnection.BeginTransaction ();
foreach (string uri in uris) {
// FIXME: Does the following call work if the source is just a PrimarySource (not LibrarySource)?
long track_id = source.GetTrackIdForUri (uri);
if (track_id > 0) {
ServiceManager.DbConnection.Execute (insert_command, track_id);
}
}
playlist.Reload ();
playlist.NotifyUser ();
} catch (Exception e) {
Hyena.Log.Exception (e);
}
}
public LyricsDownloadJob(bool force)
: base(AddinManager.CurrentLocalizer.GetString ("Downloading Lyrics"))
{
PriorityHints = PriorityHints.LongRunning;
IsBackground = true;
CanCancel = true;
DelayShow = true;
SetResources (Resource.Database);
if (force) {
/*remove from Lyrics Downloads trakcs without lyrics */
ServiceManager.DbConnection.Execute (new HyenaSqliteCommand (@"
DELETE FROM LyricsDownloads WHERE Downloaded = 0"));
}
SelectCommand = new HyenaSqliteCommand (@"
SELECT CoreTracks.TrackID, CoreArtists.Name, CoreTracks.Title, CoreTracks.Uri
FROM CoreTracks, CoreArtists
WHERE
CoreTracks.PrimarySourceID = ? AND
CoreTracks.ArtistID = CoreArtists.ArtistID AND
CoreTracks.TrackID NOT IN (
SELECT TrackID from LyricsDownloads) LIMIT 1",
ServiceManager.SourceManager.MusicLibrary.DbId);
CountCommand = new HyenaSqliteCommand (@"
SELECT count(CoreTracks.TrackID)
FROM CoreTracks
WHERE
CoreTracks.PrimarySourceID = ? AND
CoreTracks.TrackID NOT IN (
SELECT TrackID from LyricsDownloads)",
ServiceManager.SourceManager.MusicLibrary.DbId);
}
private void QueueCommand(HyenaSqliteCommand command, object arg, object [] args)
{
if (warn_if_called_from_thread != null && Thread.CurrentThread == warn_if_called_from_thread)
{
Hyena.Log.Warning("HyenaSqliteConnection command issued from the main thread");
}
while (true)
{
lock (command_queue) {
if (dispose_requested)
{
// No point in queueing the command if we're already disposing.
// This helps avoid using the probably-disposed queue_signal below too
return;
}
else if (transaction_thread == null || Thread.CurrentThread == transaction_thread)
{
command_queue.Enqueue(command);
args_queue.Enqueue(args);
arg_queue.Enqueue(arg);
break;
}
}
transaction_signal.WaitOne();
}
queue_signal.Set();
}
static PlaylistSource()
{
add_track_range_command = new HyenaSqliteCommand (@"
INSERT INTO CorePlaylistEntries
(EntryID, PlaylistID, TrackID, ViewOrder)
SELECT null, ?, ItemID, OrderId + ?
FROM CoreCache WHERE ModelID = ?
LIMIT ?, ?"
);
add_track_command = new HyenaSqliteCommand (@"
INSERT INTO CorePlaylistEntries
(EntryID, PlaylistID, TrackID, ViewOrder)
VALUES (null, ?, ?, ?)"
);
add_track_range_from_joined_model_sql = @"
INSERT INTO CorePlaylistEntries
(EntryID, PlaylistID, TrackID, ViewOrder)
SELECT null, ?, TrackID, OrderId + ?
FROM CoreCache c INNER JOIN {0} e ON c.ItemID = e.{1}
WHERE ModelID = ?
LIMIT ?, ?";
remove_track_range_command = new HyenaSqliteCommand (@"
DELETE FROM CorePlaylistEntries WHERE PlaylistID = ? AND
EntryID IN (SELECT ItemID FROM CoreCache
WHERE ModelID = ? ORDER BY OrderID LIMIT ?, ?)"
);
}
public T GetSingle(string selectFragment, string fromFragment, string conditionOrderFragment, params object [] args)
{
if (selectFragment != last_get_single_select_fragment ||
conditionOrderFragment != last_get_single_condition_fragment ||
fromFragment != last_get_single_from_fragment ||
get_single_command == null)
{
last_get_single_select_fragment = selectFragment;
last_get_single_condition_fragment = conditionOrderFragment;
last_get_single_from_fragment = fromFragment;
get_single_command = new HyenaSqliteCommand(String.Format(String.Format(
"{0} {1} {2}", select_str, conditionOrderFragment, "LIMIT 1"), selectFragment, fromFragment
));
}
using (IDataReader reader = connection.Query(get_single_command, args)) {
if (reader.Read())
{
T item = provider.Load(reader);
item.CacheEntryId = reader[reader.FieldCount - 1];
item.CacheModelId = uid;
return(item);
}
}
return(default(T));
}
public T Query <T> (HyenaSqliteCommand command, params object [] param_values)
{
command.CommandType = HyenaCommandType.Scalar;
QueueCommand(command, param_values);
object result = command.WaitForResult(this);
return((T)SqliteUtils.FromDbFormat(typeof(T), result));
}
public IEnumerable <T> FetchAllMatching(string condition, params object [] vals)
{
HyenaSqliteCommand fetch_matching_command = CreateFetchCommand(condition);
using (IDataReader reader = connection.Query(fetch_matching_command, vals)) {
while (reader.Read())
{
yield return(Load(reader));
}
}
}
// SELECT single column, multiple rows queries
public IEnumerable <T> QueryEnumerable <T> (HyenaSqliteCommand command)
{
var type = typeof(T);
using (var reader = Query(command)) {
while (reader.Read())
{
yield return((T)reader.Get(0, type));
}
}
}
public IEnumerable <T> QueryEnumerable <T> (HyenaSqliteCommand command, params object [] param_values)
{
Type type = typeof(T);
using (IDataReader reader = Query(command, param_values)) {
while (reader.Read())
{
yield return((T)reader.Get(0, type));
}
}
}
public IEnumerable <T> QueryEnumerable <T> (HyenaSqliteCommand command, params object [] param_values)
{
Type type = typeof(T);
using (IDataReader reader = Query(command, param_values)) {
while (reader.Read())
{
yield return((T)SqliteUtils.FromDbFormat(type, reader[0]));
}
}
}
public override void Reload()
{
lock (this) {
if (last_reload_fragment != model.ReloadFragment || last_reload_command == null)
{
last_reload_fragment = model.ReloadFragment;
last_reload_command = new HyenaSqliteCommand(String.Format("{0}{1}", reload_sql, last_reload_fragment));
}
Clear();
//Log.DebugFormat ("Reloading {0} with {1}", model, last_reload_command.Text);
connection.Execute(last_reload_command);
}
}
public IEnumerable<RecommendedArtist> GetRecommendations()
{
var command = new HyenaSqliteCommand (topArtistsQuery);
using (IDataReader reader = ServiceManager.DbConnection.Query (command)) {
while (reader.Read ()) {
var artistName = reader.Get<string> (0);
var artistMusicBrainzId = reader.Get<string> (1);
var artist = new RecommendedArtist (artistName, artistMusicBrainzId);
yield return artist;
}
}
}
public CoverArtJob (DateTime lastScan) : base (Catalog.GetString ("Downloading Cover Art"))
{
last_scan = lastScan;
// Since we do last_scan - retry_every, avoid out-of-range error by ensuring
// the last_scan date isn't already MinValue
if (last_scan == DateTime.MinValue) {
last_scan = DateTime.Now - TimeSpan.FromDays (365*50);
}
CountCommand = new HyenaSqliteCommand (@"
SELECT count(DISTINCT CoreTracks.AlbumID)
FROM CoreTracks, CoreArtists, CoreAlbums
WHERE
CoreTracks.PrimarySourceID = ? AND
CoreTracks.DateUpdatedStamp > ? AND
CoreTracks.AlbumID = CoreAlbums.AlbumID AND
CoreAlbums.ArtistID = CoreArtists.ArtistID AND
CoreTracks.AlbumID NOT IN (
SELECT AlbumID FROM CoverArtDownloads WHERE
LastAttempt > ? OR Downloaded = 1)",
ServiceManager.SourceManager.MusicLibrary.DbId, last_scan, last_scan - retry_every
);
SelectCommand = new HyenaSqliteCommand (String.Format (@"
SELECT DISTINCT CoreAlbums.AlbumID, CoreAlbums.Title, CoreArtists.Name, {0}, CoreTracks.TrackID
FROM CoreTracks, CoreArtists, CoreAlbums
WHERE
CoreTracks.PrimarySourceID = ? AND
CoreTracks.DateUpdatedStamp > ? AND
CoreTracks.AlbumID = CoreAlbums.AlbumID AND
CoreAlbums.ArtistID = CoreArtists.ArtistID AND
CoreTracks.AlbumID NOT IN (
SELECT AlbumID FROM CoverArtDownloads WHERE
LastAttempt > ? OR Downloaded = 1)
GROUP BY CoreTracks.AlbumID ORDER BY CoreTracks.DateUpdatedStamp DESC LIMIT ?",
Banshee.Query.BansheeQuery.UriField.Column),
ServiceManager.SourceManager.MusicLibrary.DbId, last_scan, last_scan - retry_every, 1
);
SetResources (Resource.Database);
PriorityHints = PriorityHints.LongRunning;
IsBackground = true;
CanCancel = true;
DelayShow = true;
}
public CS_TrackInfoDb(BansheeDbConnection con)
{
_con=con;
_sql_check=new HyenaSqliteCommand("SELECT COUNT(*) FROM cuesheet_info WHERE key=?");
_sql_get=new HyenaSqliteCommand("SELECT type,value FROM cuesheet_info WHERE key=?");
_sql_insert=new HyenaSqliteCommand("INSERT INTO cuesheet_info VALUES(?,?,?)");
_sql_update=new HyenaSqliteCommand("UPDATE cuesheet_info SET type=?, value=? WHERE key=?");
_sql_key_begin=new HyenaSqliteCommand("SELECT key FROM cuesheet_info WHERE key LIKE ?");
try {
if (!_con.TableExists ("cuesheet_info")) {
_con.Query ("CREATE TABLE cuesheet_info(key varchar,type varchar,value varchar)");
_con.Query ("CREATE INDEX cuesheet_idx1 ON cuesheet_info(key)");
}
} catch (System.Exception ex) {
Hyena.Log.Information (ex.ToString ());
}
}
public void IdentifiesParameters ()
{
HyenaSqliteCommand cmd = null;
try {
cmd = new HyenaSqliteCommand ("select foo from bar where baz = ?, bbz = ?, this = ?",
"a", 32);
Assert.Fail ("Should not have been able to pass 2 values to ApplyValues without exception");
} catch {}
try {
cmd = new HyenaSqliteCommand ("select foo from bar where baz = ?, bbz = ?, this = ?",
"a", 32, "22");
} catch {
Assert.Fail ("Should have been able to pass 3 values to ApplyValues without exception");
}
Assert.AreEqual ("select foo from bar where baz = 'a', bbz = 32, this = '22'", GetGeneratedSql (cmd));
}
public SaveTrackMetadataJob () : base (Catalog.GetString ("Saving Metadata to File"))
{
SetResources (Resource.Cpu, Resource.Disk, Resource.Database);
IsBackground = true;
var db_ids = ServiceManager.Get<SaveTrackMetadataService> ().Sources.
Select (s => s.DbId.ToString ()).ToArray ();
string range = String.Join (",", db_ids);
//FIXME: should we add the case in which LastSyncedStamp IS NULL?
string condition = String.Format (
@"DateUpdatedStamp > LastSyncedStamp
AND PrimarySourceID IN ({0})", range);
CountCommand = new HyenaSqliteCommand (
"SELECT COUNT(*) FROM CoreTracks WHERE " + condition);
SelectCommand = DatabaseTrackInfo.Provider.CreateFetchCommand (condition);
}
public void ParameterSerialization ()
{
HyenaSqliteCommand cmd = new HyenaSqliteCommand ("select foo from bar where baz = ?");
Assert.AreEqual ("select foo from bar where baz = NULL", GetGeneratedSql (cmd, null));
Assert.AreEqual ("select foo from bar where baz = 'It''s complicated, \"but\" ''''why not''''?'", GetGeneratedSql (cmd, "It's complicated, \"but\" ''why not''?"));
Assert.AreEqual ("select foo from bar where baz = 0", GetGeneratedSql (cmd, new DateTime (1970, 1, 1).ToLocalTime ()));
Assert.AreEqual ("select foo from bar where baz = 931309200", GetGeneratedSql (cmd, new DateTime (1999, 7, 7).ToLocalTime ()));
Assert.AreEqual ("select foo from bar where baz = 555.55", GetGeneratedSql (cmd, 555.55f));
Assert.AreEqual ("select foo from bar where baz = 555.55", GetGeneratedSql (cmd, 555.55));
Assert.AreEqual ("select foo from bar where baz = 555", GetGeneratedSql (cmd, 555));
Assert.AreEqual ("select foo from bar where baz = 1", GetGeneratedSql (cmd, true));
Assert.AreEqual ("select foo from bar where baz = 0", GetGeneratedSql (cmd, false));
HyenaSqliteCommand cmd2 = new HyenaSqliteCommand ("select foo from bar where baz = ?, bar = ?, boo = ?");
Assert.AreEqual ("select foo from bar where baz = NULL, bar = NULL, boo = 22", GetGeneratedSql (cmd2, null, null, 22));
HyenaSqliteCommand cmd3 = new HyenaSqliteCommand ("select foo from bar where id in (?) and foo not in (?)");
Assert.AreEqual ("select foo from bar where id in (1,2,4) and foo not in ('foo','baz')",
GetGeneratedSql (cmd3, new int [] {1, 2, 4}, new string [] {"foo", "baz"}));
}
public SaveTrackMetadataJob () : base (Catalog.GetString ("Saving Metadata to File"))
{
SetResources (Resource.Cpu, Resource.Disk, Resource.Database);
IsBackground = true;
var db_ids = ServiceManager.Get<SaveTrackMetadataService> ().Sources.
Select (s => s.DbId.ToString ()).ToArray ();
string range = String.Join (",", db_ids);
string condition = String.Format (
@"(DateUpdatedStamp > LastSyncedStamp OR
(DateUpdatedStamp IS NOT NULL AND LastSyncedStamp IS NULL))
AND PrimarySourceID IN ({0})
AND Uri LIKE '{1}%'", range, "file:");
CountCommand = new HyenaSqliteCommand (
"SELECT COUNT(*) FROM CoreTracks WHERE " + condition);
SelectCommand = DatabaseTrackInfo.Provider.CreateFetchCommand (condition + " LIMIT 1");
}
public AlbumArtWriterJob(AlbumArtWriterService in_service)
: base(AddinManager.CurrentLocalizer.GetString ("Saving Cover Art To Album folders"))
{
service = in_service;
CurrentTime = DateTime.Now;
if ((service != null) && (service.ForceRecopy)){
TimeOffset = CurrentTime;
} else {
TimeOffset = CurrentTime - TimeSpan.FromDays(7);
}
CountCommand = new HyenaSqliteCommand (@"
SELECT count(DISTINCT CoreTracks.AlbumID)
FROM CoreTracks, CoreAlbums
WHERE
CoreTracks.PrimarySourceID = ? AND
CoreTracks.AlbumID = CoreAlbums.AlbumID AND
CoreTracks.AlbumID NOT IN (
SELECT AlbumID from AlbumArtWriter WHERE
SavedOrTried > 0 AND LastUpdated >= ?)"
, ServiceManager.SourceManager.MusicLibrary.DbId, TimeOffset);
SelectCommand = new HyenaSqliteCommand (@"
SELECT DISTINCT CoreAlbums.AlbumID, CoreAlbums.Title, CoreArtists.Name, CoreTracks.Uri, CoreTracks.TrackID
FROM CoreTracks, CoreArtists, CoreAlbums
WHERE
CoreTracks.PrimarySourceID = ? AND
CoreTracks.AlbumID = CoreAlbums.AlbumID AND
CoreAlbums.ArtistID = CoreArtists.ArtistID AND
CoreTracks.AlbumID NOT IN (
SELECT AlbumID from AlbumArtWriter WHERE
SavedOrTried > 0 AND LastUpdated >= ?)
GROUP BY CoreTracks.AlbumID ORDER BY CoreTracks.DateUpdatedStamp DESC LIMIT ?",
ServiceManager.SourceManager.MusicLibrary.DbId, TimeOffset, 1);
SetResources (Resource.Database);
PriorityHints = PriorityHints.LongRunning;
IsBackground = true;
CanCancel = true;
DelayShow = true;
}
private void QueueCommand(HyenaSqliteCommand command, object arg, object [] args)
{
if (warn_if_called_from_thread != null && Thread.CurrentThread == warn_if_called_from_thread)
{
Hyena.Log.Warning("HyenaSqliteConnection command issued from the main thread");
}
while (true)
{
lock (command_queue) {
if (transaction_thread == null || Thread.CurrentThread == transaction_thread)
{
command_queue.Enqueue(command);
args_queue.Enqueue(args);
arg_queue.Enqueue(arg);
break;
}
}
transaction_signal.WaitOne();
}
queue_signal.Set();
}
public BpmDetectJob () : base (Catalog.GetString ("Detecting BPM"))
{
IconNames = new string [] {"audio-x-generic"};
IsBackground = true;
SetResources (Resource.Cpu, Resource.Disk);
PriorityHints = PriorityHints.LongRunning;
music_library = ServiceManager.SourceManager.MusicLibrary;
CountCommand = new HyenaSqliteCommand (String.Format (
"SELECT COUNT(*) FROM CoreTracks WHERE PrimarySourceID = {0} AND (BPM = 0 OR BPM IS NULL)",
music_library.DbId
));
SelectCommand = new HyenaSqliteCommand (String.Format (@"
SELECT DISTINCT {0}, TrackID
FROM CoreTracks
WHERE PrimarySourceID IN ({1}) AND (BPM IS NULL OR BPM = 0) LIMIT 1",
Banshee.Query.BansheeQuery.UriField.Column, music_library.DbId
));
Register ();
}
public void Run(object o, EventArgs e)
{
Photo[] photos = App.Instance.Organizer.SelectedPhotos ();
if (photos.Length == 0) {
Log.Debug ("no photos selected, returning");
return;
}
DateTime import_time = photos[0].Time;
foreach (Photo p in photos)
if (p.Time > import_time)
import_time = p.Time;
RollStore rolls = App.Instance.Database.Rolls;
Roll roll = rolls.Create(import_time);
foreach (Photo p in photos) {
HyenaSqliteCommand cmd = new HyenaSqliteCommand ("UPDATE photos SET roll_id = ? " +
"WHERE id = ? ", roll.Id, p.Id);
App.Instance.Database.Database.Execute (cmd);
p.RollId = roll.Id;
}
Log.Debug ("RetroactiveRoll done: " + photos.Length + " photos in roll " + roll.Id);
}
public long IndexOf(string where_fragment, long offset)
{
if (String.IsNullOrEmpty(where_fragment))
{
return(-1);
}
if (!where_fragment.Equals(last_indexof_where_fragment))
{
last_indexof_where_fragment = where_fragment;
if (!where_fragment.Trim().ToLower().StartsWith("and "))
{
where_fragment = " AND " + where_fragment;
}
string sql = String.Format("{0} {1} LIMIT ?, 1", select_str, where_fragment);
indexof_command = new HyenaSqliteCommand(String.Format(sql, null, null));
}
lock (this) {
using (IDataReader reader = connection.Query(indexof_command, offset)) {
if (reader.Read())
{
long target_id = (long)reader[reader.FieldCount - 2];
if (target_id == 0)
{
return(-1);
}
return(target_id - FirstOrderId);
}
}
return(-1);
}
}
public AnalyzeLibraryJob()
: base(AddinManager.CurrentLocalizer.GetString ("Analyzing Song Similarity"))
{
IconNames = new string [] {"audio-x-generic"};
IsBackground = true;
SetResources (Resource.Cpu, Resource.Disk);
PriorityHints = PriorityHints.LongRunning;
var music_id = ServiceManager.SourceManager.MusicLibrary.DbId;
CountCommand = new HyenaSqliteCommand (String.Format (
@"SELECT COUNT(*)
FROM CoreTracks
WHERE PrimarySourceID IN ({0}) AND TrackID NOT IN
(SELECT TrackID FROM MirageTrackAnalysis)",
music_id
));
SelectCommand = new HyenaSqliteCommand (String.Format (@"
SELECT TrackID
FROM CoreTracks
WHERE PrimarySourceID IN ({0}) AND TrackID NOT IN
(SELECT TrackID FROM MirageTrackAnalysis)
ORDER BY Rating DESC, PlayCount DESC LIMIT 1",
music_id
));
CancelMessage = AddinManager.CurrentLocalizer.GetString (
"Are you sure you want to stop Mirage?\n" +
"Shuffle by Similar will only work for the tracks which are already analyzed. " +
"The operation can be resumed at any time from the <i>Tools</i> menu."
);
CanCancel = true;
Register ();
}
public static List<StationSource> LoadAll(LastfmSource lastfm, string creator)
{
List<StationSource> stations = new List<StationSource> ();
HyenaSqliteCommand command = new HyenaSqliteCommand (
"SELECT StationID, Name, Type, Arg, PlayCount FROM LastfmStations WHERE Creator = ?",
creator
);
using (IDataReader reader = ServiceManager.DbConnection.Query (command)) {
while (reader.Read ()) {
try {
stations.Add (new StationSource (lastfm,
Convert.ToInt64 (reader[0]),
reader[1] as string,
reader[2] as string,
reader[3] as string,
Convert.ToInt32 (reader[4])
));
} catch (Exception e) {
Log.Warning ("Error Loading Last.fm Station", e.ToString (), false);
}
}
}
// Create some default stations if the user has none
if (stations.Count == 0) {
stations.Add (new StationSource (lastfm, Catalog.GetString ("Recommended"), "Recommended", creator));
stations.Add (new StationSource (lastfm, Catalog.GetString ("Personal"), "Personal", creator));
stations.Add (new StationSource (lastfm, Catalog.GetString ("Mix"), "Mix", creator));
stations.Add (new StationSource (lastfm, Catalog.GetString ("Banshee Group"), "Group", "Banshee"));
stations.Add (new StationSource (lastfm, Catalog.GetString ("Neighbors"), "Neighbor", creator));
stations.Add (new StationSource (lastfm, Catalog.GetString ("Creative Commons"), "Tag", "creative commons"));
}
return stations;
}
public virtual void UpdateUnfilteredAggregates()
{
HyenaSqliteCommand count_command = new HyenaSqliteCommand (String.Format (
"SELECT COUNT(*), SUM({0}), SUM({1}) {2}",
BansheeQuery.FileSizeField.Column, BansheeQuery.DurationField.Column, UnfilteredQuery
));
using (HyenaDataReader reader = new HyenaDataReader (connection.Query (count_command))) {
count = reader.Get<long> (0);
filesize = reader.Get<long> (1);
duration = reader.Get<TimeSpan> (2);
}
}
// INSERT, UPDATE, DELETE queries
public int Execute(HyenaSqliteCommand command)
{
command.CommandType = HyenaCommandType.Execute;;
QueueCommand(command);
return(Convert.ToInt32(command.WaitForResult(this)));
}
protected override void AddTrackRange(DatabaseTrackListModel from, RangeCollection.Range range)
{
last_add_range_command = (!from.CachesJoinTableEntries)
? add_track_range_command
: from == last_add_range_from_model
? last_add_range_command
: new HyenaSqliteCommand (String.Format (add_track_range_from_joined_model_sql, from.JoinTable, from.JoinPrimaryKey));
long first_order_id = ServiceManager.DbConnection.Query<long> ("SELECT OrderID FROM CoreCache WHERE ModelID = ? LIMIT 1 OFFSET ?", from.CacheId, range.Start);
ServiceManager.DbConnection.Execute (last_add_range_command, DbId, MaxViewOrder - first_order_id, from.CacheId, range.Start, range.Count);
last_add_range_from_model = from;
}
public virtual void ReorderSelectedTracks(int drop_row)
{
if (TrackModel.Selection.Count == 0 || TrackModel.Selection.AllSelected) {
return;
}
TrackInfo track = TrackModel[drop_row];
long order = track == null
? ServiceManager.DbConnection.Query<long> ("SELECT MAX(ViewOrder) + 1 FROM CorePlaylistEntries WHERE PlaylistID = ?", DbId)
: ServiceManager.DbConnection.Query<long> ("SELECT ViewOrder FROM CorePlaylistEntries WHERE PlaylistID = ? AND EntryID = ?", DbId, Convert.ToInt64 (track.CacheEntryId));
// Make room for our new items
if (track != null) {
ServiceManager.DbConnection.Execute ("UPDATE CorePlaylistEntries SET ViewOrder = ViewOrder + ? WHERE PlaylistID = ? AND ViewOrder >= ?",
TrackModel.Selection.Count, DbId, order
);
}
HyenaSqliteCommand update_command = new HyenaSqliteCommand (String.Format ("UPDATE CorePlaylistEntries SET ViewOrder = ? WHERE PlaylistID = {0} AND EntryID = ?", DbId));
HyenaSqliteCommand select_command = new HyenaSqliteCommand (String.Format ("SELECT ItemID FROM CoreCache WHERE ModelID = {0} LIMIT ?, ?", DatabaseTrackModel.CacheId));
// Reorder the selected items
ServiceManager.DbConnection.BeginTransaction ();
foreach (RangeCollection.Range range in TrackModel.Selection.Ranges) {
foreach (long entry_id in ServiceManager.DbConnection.QueryEnumerable<long> (select_command, range.Start, range.Count)) {
ServiceManager.DbConnection.Execute (update_command, order++, entry_id);
}
}
ServiceManager.DbConnection.CommitTransaction ();
Reload ();
}
private void RenameTrack(string oldFullPath, string fullPath)
{
if (oldFullPath == fullPath) {
// FIXME: bug in Mono, see bnc#322330
return;
}
string old_uri = new SafeUri (oldFullPath).AbsoluteUri;
string new_uri = new SafeUri (fullPath).AbsoluteUri;
string pattern = StringUtil.EscapeLike (old_uri) + "%";
HyenaSqliteCommand rename_command = new HyenaSqliteCommand (@"
UPDATE CoreTracks
SET Uri = REPLACE(Uri, ?, ?), DateUpdatedStamp = ?
WHERE Uri LIKE ? ESCAPE '\'",
old_uri, new_uri, DateTime.Now, pattern);
ServiceManager.DbConnection.Execute (rename_command);
}
protected override void OnModelAndCacheUpdated ()
{
album_query = null;
}
// TODO special case for single object param to avoid object []
// SELECT multiple column queries
public IDataReader Query(HyenaSqliteCommand command)
{
command.CommandType = HyenaCommandType.Reader;
QueueCommand(command);
return((IDataReader)command.WaitForResult(this));
}
public int Execute(HyenaSqliteCommand command, params object [] param_values)
{
command.CommandType = HyenaCommandType.Execute;;
QueueCommand(command, param_values);
return(Convert.ToInt32(command.WaitForResult(this)));
}
public IDataReader Query(HyenaSqliteCommand command, params object [] param_values)
{
command.CommandType = HyenaCommandType.Reader;
QueueCommand(command, param_values);
return((IDataReader)command.WaitForResult(this));
}
private bool Migrate_27 ()
{
// One time fixup to MetadataHash now that our unknown metadata is handled properly
string sql_select = @"
SELECT t.TrackID, al.Title, ar.Name, t.Duration,
t.Genre, t.Title, t.TrackNumber, t.Year
FROM CoreTracks AS t
JOIN CoreAlbums AS al ON al.AlbumID=t.AlbumID
JOIN CoreArtists AS ar ON ar.ArtistID=t.ArtistID
WHERE t.Title IS NULL OR ar.Name IS NULL OR al.Title IS NULL
";
HyenaSqliteCommand sql_update = new HyenaSqliteCommand (@"
UPDATE CoreTracks SET MetadataHash = ? WHERE TrackID = ?
");
StringBuilder sb = new StringBuilder ();
using (var reader = new HyenaDataReader (connection.Query (sql_select))) {
while (reader.Read ()) {
sb.Length = 0;
sb.Append (reader.Get<string> (1));
sb.Append (reader.Get<string> (2));
sb.Append ((int)reader.Get<TimeSpan> (3).TotalSeconds);
sb.Append (reader.Get<string> (4));
sb.Append (reader.Get<string> (5));
sb.Append (reader.Get<int> (6));
sb.Append (reader.Get<int> (7));
string hash = Hyena.CryptoUtil.Md5Encode (sb.ToString (), System.Text.Encoding.UTF8);
connection.Execute (sql_update, hash, reader.Get<int> (0));
}
}
return true;
}
// Commented out for now - this method doesn't handle actually subscribing to the feeds
// (the most important task in migrating podcasts), and it looks like it imports podcast files
// into the Music Library.
/*private void ImportPodcasts(LibraryImportManager manager, XmlNodeList podcasts)
{
foreach (XmlElement entry in podcasts) {
if (CheckForCanceled ()) {
break;
}
processed++;
string title = String.Empty, feed = String.Empty;
SafeUri uri = null;
foreach (XmlElement child in entry.ChildNodes) {
if (child == null || child.InnerText == null || child.InnerText == String.Empty) {
continue;
}
try {
switch (child.Name) {
case "title":
title = child.InnerText;
break;
case "album":
feed = child.InnerText;
break;
case "mountpoint":
uri = new SafeUri (child.InnerText);
break;
}
} catch (Exception) {
// parsing InnerText failed
}
}
if (uri == null) {
continue;
}
UpdateUserJob (processed, count, "", title);
try {
DatabaseTrackInfo track = manager.ImportTrack (uri);
if (track == null) {
LogError (SafeUri.UriToFilename (uri), Catalog.GetString ("Unable to import podcast."));
continue;
}
track.TrackTitle = title;
track.AlbumTitle = feed;
track.Genre = "Podcast";
track.Save (false);
} catch (Exception e) {
LogError (SafeUri.UriToFilename (uri), e);
}
}
}*/
private void ImportStaticPlaylists(XmlNodeList playlists)
{
foreach (XmlElement list in playlists) {
if (CheckForCanceled ()) {
break;
}
processed++;
try {
string title = String.Empty;
if (list.HasAttribute ("name")) {
title = list.GetAttribute ("name");
}
UpdateUserJob (processed, count, "", title);
PlaylistSource playlist = new PlaylistSource (title, ServiceManager.SourceManager.MusicLibrary);
playlist.Save ();
ServiceManager.SourceManager.MusicLibrary.AddChildSource (playlist);
HyenaSqliteCommand insert_command = new HyenaSqliteCommand (String.Format (
@"INSERT INTO CorePlaylistEntries (PlaylistID, TrackID) VALUES ({0}, ?)", playlist.DbId));
foreach (XmlElement entry in list.ChildNodes) {
if (entry.Name != "location") {
continue;
}
int track_id = ServiceManager.SourceManager.MusicLibrary.GetTrackIdForUri (entry.InnerText);
if (track_id > 0) {
ServiceManager.DbConnection.Execute (insert_command, track_id);
}
}
playlist.Reload ();
playlist.NotifyUser ();
} catch (Exception e) {
LogError ("", e);
}
}
}
private void Create()
{
HyenaSqliteCommand command = new HyenaSqliteCommand (
@"INSERT INTO LastfmStations (Creator, Name, Type, Arg, PlayCount)
VALUES (?, ?, ?, ?, ?)",
lastfm.Account.UserName, Name,
Type.ToString (), Arg, PlayCount
);
dbid = ServiceManager.DbConnection.Execute (command);
TypeUniqueId = dbid.ToString ();
}
public SqliteModelCache(HyenaSqliteConnection connection,
string uuid,
ICacheableDatabaseModel model,
SqliteModelProvider <T> provider)
: base(model)
{
this.connection = connection;
this.model = model;
this.provider = provider;
CheckCacheTable();
if (model.SelectAggregates != null)
{
if (model.CachesJoinTableEntries)
{
count_command = new HyenaSqliteCommand(String.Format(@"
SELECT count(*), {0} FROM {1}{2} j
WHERE j.{4} IN (SELECT ItemID FROM {3} WHERE ModelID = ?)
AND {5} = j.{6}",
model.SelectAggregates, // eg sum(FileSize), sum(Duration)
provider.TableName, // eg CoreTracks
model.JoinFragment, // eg , CorePlaylistEntries
CacheTableName, // eg CoreCache
model.JoinPrimaryKey, // eg EntryID
provider.PrimaryKey, // eg CoreTracks.TrackID
model.JoinColumn // eg TrackID
));
}
else
{
count_command = new HyenaSqliteCommand(String.Format(
"SELECT count(*), {0} FROM {1} WHERE {2} IN (SELECT ItemID FROM {3} WHERE ModelID = ?)",
model.SelectAggregates, provider.TableName, provider.PrimaryKey, CacheTableName
));
}
}
else
{
count_command = new HyenaSqliteCommand(String.Format(
"SELECT COUNT(*) FROM {0} WHERE ModelID = ?", CacheTableName
));
}
uid = FindOrCreateCacheModelId(String.Format("{0}-{1}", uuid, typeof(T).Name));
if (model.Selection != null)
{
selection_uid = FindOrCreateCacheModelId(String.Format("{0}-{1}-Selection", uuid, typeof(T).Name));
}
if (model.CachesJoinTableEntries)
{
select_str = String.Format(
@"SELECT {0} {10}, OrderID, {5}.ItemID FROM {1}
INNER JOIN {2}
ON {3} = {2}.{4}
INNER JOIN {5}
ON {2}.{6} = {5}.ItemID {11}
WHERE
{5}.ModelID = {7} {8} {9}",
provider.Select, provider.From,
model.JoinTable, provider.PrimaryKey, model.JoinColumn,
CacheTableName, model.JoinPrimaryKey, uid,
String.IsNullOrEmpty(provider.Where) ? null : "AND",
provider.Where, "{0}", "{1}"
);
reload_sql = String.Format(@"
DELETE FROM {0} WHERE ModelID = {1};
INSERT INTO {0} (ModelID, ItemID) SELECT {1}, {2} ",
CacheTableName, uid, model.JoinPrimaryKey
);
}
else if (model.CachesValues)
{
// The duplication of OrderID/ItemID in the select below is intentional!
// The first is used construct the QueryFilterInfo value, the last internally
// to this class
select_str = String.Format(
@"SELECT OrderID, ItemID {2}, OrderID, ItemID FROM {0} {3} WHERE {0}.ModelID = {1}",
CacheTableName, uid, "{0}", "{1}"
);
reload_sql = String.Format(@"
DELETE FROM {0} WHERE ModelID = {1};
INSERT INTO {0} (ModelID, ItemID) SELECT DISTINCT {1}, {2} ",
CacheTableName, uid, provider.Select
);
}
else
{
select_str = String.Format(
@"SELECT {0} {7}, OrderID, {2}.ItemID FROM {1}
INNER JOIN {2}
ON {3} = {2}.ItemID {8}
WHERE
{2}.ModelID = {4} {5} {6}",
provider.Select, provider.From, CacheTableName,
provider.PrimaryKey, uid,
String.IsNullOrEmpty(provider.Where) ? String.Empty : "AND",
provider.Where, "{0}", "{1}"
);
reload_sql = String.Format(@"
DELETE FROM {0} WHERE ModelID = {1};
INSERT INTO {0} (ModelID, ItemID) SELECT {1}, {2} ",
CacheTableName, uid, provider.PrimaryKey
);
}
select_single_command = new HyenaSqliteCommand(
String.Format(@"
SELECT OrderID FROM {0}
WHERE
ModelID = {1} AND
ItemID = ?",
CacheTableName, uid
)
);
select_range_command = new HyenaSqliteCommand(
String.Format(String.Format("{0} {1}", select_str, "LIMIT ?, ?"), null, null)
);
select_first_command = new HyenaSqliteCommand(
String.Format(
"SELECT OrderID FROM {0} WHERE ModelID = {1} LIMIT 1",
CacheTableName, uid
)
);
if (model.Selection != null)
{
delete_selection_command = new HyenaSqliteCommand(String.Format(
"DELETE FROM {0} WHERE ModelID = {1}", CacheTableName, selection_uid
));
save_selection_command = new HyenaSqliteCommand(String.Format(
"INSERT INTO {0} (ModelID, ItemID) SELECT {1}, ItemID FROM {0} WHERE ModelID = {2} LIMIT ?, ?",
CacheTableName, selection_uid, uid
));
get_selection_command = new HyenaSqliteCommand(String.Format(
"SELECT OrderID FROM {0} WHERE ModelID = {1} AND ItemID IN (SELECT ItemID FROM {0} WHERE ModelID = {2})",
CacheTableName, uid, selection_uid
));
}
}
private void Update()
{
HyenaSqliteCommand command = new HyenaSqliteCommand (
@"UPDATE LastfmStations
SET Name = ?, Type = ?, Arg = ?, PlayCount = ?
WHERE StationID = ?",
Name, Type.ToString (), Arg, PlayCount, dbid
);
ServiceManager.DbConnection.Execute (command);
Station = Type.GetStationFor (Arg);
OnUpdated ();
}
protected override void ImportCore ()
{
LibraryImportManager import_manager = ServiceManager.Get<LibraryImportManager> ();
HyenaSqliteConnection conn;
try {
conn = new HyenaSqliteConnection (amarok_db_path);
} catch (Exception e) {
LogError (amarok_db_path, String.Format (
"Unable to open Amarok database: {0}", e.Message));
return;
}
int count = 0;
try {
count = conn.Query<int> ("SELECT COUNT(*) FROM tags");
} catch (Exception) {}
try {
HyenaSqliteCommand cmd = new HyenaSqliteCommand (@"
SELECT DISTINCT NULL,
tags.url,
tags.title,
artist.name,
genre.name,
album.name,
year.name,
tags.track,
tags.length
FROM tags,
artist,
album,
genre,
year
WHERE tags.deviceid = -1
AND tags.artist = artist.id
AND tags.album = album.id
AND tags.genre = genre.id
AND tags.year = year.id"
);
HyenaSqliteCommand stats_cmd = new HyenaSqliteCommand (@"
SELECT DISTINCT (rating+rating%2)/2, playcounter, createdate, accessdate
FROM statistics
WHERE url = ? AND deviceid = -1");
int processed = 0;
IDataReader reader = conn.Query (cmd);
while (reader.Read ()) {
if (CheckForCanceled ())
break;
processed++;
try {
string path = (string) reader[1];
SafeUri uri = null;
if (path.StartsWith ("./")) {
uri = new SafeUri (path.Substring (1));
} else if (path.StartsWith ("/")) {
uri = new SafeUri (path);
} else {
continue;
}
string title = (string) reader[2];
string artist = (string) reader[3];
//Console.WriteLine ("Amarok import has {0}/{1} - {2}", artist, title, uri);
// the following fields are not critical and can be skipped if something goes wrong
int rating = 0, playcount = 0;
long created = 0, accessed = 0;
// Try to read stats
try {
IDataReader stats_reader = conn.Query (stats_cmd, path);
while (stats_reader.Read ()) {
rating = Convert.ToInt32 (stats_reader[0]);
playcount = Convert.ToInt32 (stats_reader[1]);
created = Convert.ToInt64 (stats_reader[2]);
accessed = Convert.ToInt64 (stats_reader[3]);
}
stats_reader.Dispose ();
} catch (Exception) {}
UpdateUserJob (processed, count, artist, title);
try {
DatabaseTrackInfo track = import_manager.ImportTrack (uri);
if (track == null) {
throw new Exception (String.Format (Catalog.GetString ("Unable to import track: {0}"), uri.AbsoluteUri));
}
if (rating > 0 || playcount > 0 || created > 0 || accessed > 0) {
track.Rating = rating;
track.PlayCount = playcount;
if (created > 0)
track.DateAdded = Hyena.DateTimeUtil.FromTimeT (created);
if (accessed > 0)
track.LastPlayed = Hyena.DateTimeUtil.FromTimeT (accessed);
track.Save (false);
}
} catch (Exception e) {
LogError (SafeUri.UriToFilename (uri), e);
}
} catch (Exception e) {
Hyena.Log.Exception (e);
// something went wrong, skip entry
}
}
reader.Dispose ();
import_manager.NotifyAllSources ();
// TODO migrating more than the podcast subscriptions (eg whether to auto sync them etc) means 1) we need to have those features
// and 2) we need to depend on Migo and/or the Podcast extension
DBusCommandService cmd_service = ServiceManager.Get<DBusCommandService> ();
if (cmd_service != null && ServiceManager.DbConnection.TableExists ("PodcastSyndications")) {
foreach (string podcast_url in conn.QueryEnumerable<string> ("SELECT url FROM podcastchannels")) {
cmd_service.PushFile (podcast_url.Replace ("http:", "feed:"));
}
}
} catch (Exception e) {
Hyena.Log.Exception (e);
LogError (amarok_db_path, Catalog.GetString ("Importing from Amarok failed"));
} finally {
conn.Dispose ();
}
}
public MoodbarDetectJob()
: base(Catalog.GetString ("Detecting Mood"))
{
IconNames = new string[] { "audio-x-generic" };
IsBackground = true;
SetResources (Resource.Cpu, Resource.Disk);
PriorityHints = PriorityHints.LongRunning;
CanCancel = true;
DelayShow = true;
music_library = ServiceManager.SourceManager.MusicLibrary;
CountCommand = new HyenaSqliteCommand (@"
SELECT Uri
FROM CoreTracks
WHERE
PrimarySourceID = ? AND
TrackID NOT IN (
SELECT TrackID FROM MoodPaths
WHERE LastAttempt > ?
) AND
TrackID NOT IN (
SELECT TrackID FROM MoodPaths
WHERE FileName IS NOT NULL
)
",
music_library.DbId, DateTime.Now - TimeSpan.FromDays (1));
SelectCommand = new HyenaSqliteCommand (@"
SELECT Uri
FROM CoreTracks
WHERE
PrimarySourceID = ? AND
TrackID NOT IN (
SELECT TrackID FROM MoodPaths
WHERE LastAttempt > ?
) AND
TrackID NOT IN (
SELECT TrackID FROM MoodPaths
WHERE FileName IS NOT NULL
)
LIMIT 1",
music_library.DbId, DateTime.Now - TimeSpan.FromDays (1));
Register ();
}
// INSERT, UPDATE, DELETE queries
public long Execute(HyenaSqliteCommand command)
{
command.CommandType = HyenaCommandType.Execute;;
QueueCommand(command);
return((long)command.WaitForResult(this));
}
protected override void IterateCore (HyenaDataReader reader)
{
DatabaseTrackInfo track = DatabaseTrackInfo.Provider.Load (reader.Reader);
var write_delay = track.DateUpdated.AddSeconds (2) - DateTime.Now;
if (write_delay.TotalMilliseconds > 0) {
System.Threading.Thread.Sleep (write_delay);
return;
}
bool wrote = false;
bool renamed = false;
try {
if (WriteMetadataEnabled || WriteRatingsAndPlayCountsEnabled) {
Hyena.Log.DebugFormat ("Saving metadata for {0}", track);
wrote = StreamTagger.SaveToFile (track, WriteMetadataEnabled, WriteRatingsAndPlayCountsEnabled);
}
// Rename tracks only from the Music Library
if (RenameEnabled &&
track.PrimarySource.Equals (musicLibrarySource)) {
Hyena.Log.DebugFormat ("Updating file name for {0}", track);
renamed = RenameFile (track);
if (renamed && !wrote) {
track.LastSyncedStamp = DateTime.Now;
}
}
} catch (Exception) {
Hyena.Log.ErrorFormat ("Error writing to or renaming {0}", track);
} finally {
if (wrote || renamed) {
// Save the resulting changes to FileSize, LastSyncedStamp, possibly to Uri, etc
// Clear track model caches if URI changed
track.Save (renamed);
} else {
if (update_synced_at == null) {
update_synced_at = new HyenaSqliteCommand (
"UPDATE CoreTracks SET LastSyncedStamp = ? WHERE TrackID = ?");
}
ServiceManager.DbConnection.Execute (update_synced_at, DateTime.Now, track.TrackId);
}
}
}
public long Execute(HyenaSqliteCommand command, params object [] param_values)
{
command.CommandType = HyenaCommandType.Execute;;
QueueCommand(command, param_values);
return((long)command.WaitForResult(this));
}
private void RefreshMetadataThread (object state)
{
int total = ServiceManager.DbConnection.Query<int> ("SELECT count(*) FROM CoreTracks");
if (total <= 0) {
return;
}
UserJob job = new UserJob (Catalog.GetString ("Refreshing Metadata"));
job.SetResources (Resource.Cpu, Resource.Disk, Resource.Database);
job.PriorityHints = PriorityHints.SpeedSensitive;
job.Status = Catalog.GetString ("Scanning...");
job.IconNames = new string [] { "system-search", "gtk-find" };
job.Register ();
HyenaSqliteCommand select_command = new HyenaSqliteCommand (
String.Format (
"SELECT {0} FROM {1} WHERE {2}",
DatabaseTrackInfo.Provider.Select,
DatabaseTrackInfo.Provider.From,
DatabaseTrackInfo.Provider.Where
)
);
int count = 0;
using (var reader = ServiceManager.DbConnection.Query (select_command)) {
while (reader.Read ()) {
DatabaseTrackInfo track = null;
try {
track = DatabaseTrackInfo.Provider.Load (reader);
if (track != null && track.Uri != null && track.Uri.IsFile) {
try {
using (var file = StreamTagger.ProcessUri (track.Uri)) {
StreamTagger.TrackInfoMerge (track, file, true);
}
} catch (Exception e) {
Log.Warning (String.Format ("Failed to update metadata for {0}", track),
e.GetType ().ToString (), false);
}
track.Save (false);
track.Artist.Save ();
track.Album.Save ();
job.Status = String.Format ("{0} - {1}", track.DisplayArtistName, track.DisplayTrackTitle);
}
} catch (Exception e) {
Log.Warning (String.Format ("Failed to update metadata for {0}", track), e.ToString (), false);
}
job.Progress = (double)++count / (double)total;
}
}
if (ServiceManager.DbConnection.Query<int> ("SELECT count(*) FROM CoreConfiguration WHERE Key = 'MetadataVersion'") == 0) {
Execute (String.Format ("INSERT INTO CoreConfiguration (EntryID, Key, Value) VALUES (null, 'MetadataVersion', {0})", CURRENT_METADATA_VERSION));
} else {
Execute (String.Format ("UPDATE CoreConfiguration SET Value = {0} WHERE Key = 'MetadataVersion'", CURRENT_METADATA_VERSION));
}
job.Finish ();
ServiceManager.SourceManager.MusicLibrary.NotifyTracksChanged ();
}
private void QueueCommand(HyenaSqliteCommand command, object [] args)
{
QueueCommand(command, null, args);
}
private bool Migrate_39 ()
{
// One time fixup to MetadataHash, since we no longer include the Duration
string sql_select = @"
SELECT t.TrackID, al.Title, ar.Name,
t.Genre, t.Title, t.TrackNumber, t.Year
FROM CoreTracks AS t
JOIN CoreAlbums AS al ON al.AlbumID=t.AlbumID
JOIN CoreArtists AS ar ON ar.ArtistID=t.ArtistID
";
HyenaSqliteCommand sql_update = new HyenaSqliteCommand (@"
UPDATE CoreTracks SET MetadataHash = ? WHERE TrackID = ?
");
StringBuilder sb = new StringBuilder ();
using (var reader = new HyenaDataReader (connection.Query (sql_select))) {
while (reader.Read ()) {
sb.Length = 0;
sb.Append (reader.Get<string> (1));
sb.Append (reader.Get<string> (2));
sb.Append (reader.Get<string> (3));
sb.Append (reader.Get<string> (4));
sb.Append (reader.Get<int> (5));
sb.Append (reader.Get<int> (6));
string hash = Hyena.CryptoUtil.Md5Encode (sb.ToString (), System.Text.Encoding.UTF8);
connection.Execute (sql_update, hash, reader.Get<int> (0));
}
}
return true;
}
// TODO optimize object vs object [] code paths?
/*private void QueueCommand(HyenaSqliteCommand command, object arg)
* {
* QueueCommand (command, arg, null);
* }*/
private void QueueCommand(HyenaSqliteCommand command)
{
QueueCommand(command, null, null);
}