C# Sqlite.HyenaSqliteCommand类代码示例

        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 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 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 override void UpdateUnfilteredAggregates ()
 {
     HyenaSqliteCommand count_command = new HyenaSqliteCommand (String.Format (
         "SELECT COUNT(*) {0} AND PodcastItems.IsRead = 0", UnfilteredQuery
     ));
     UnfilteredCount = Connection.Query<int> (count_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;
        }

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