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