[banshee] Future-proof INSERT INTO queries (bgo#585725)

[Alexander Kojevnikov <alexk src gnome org>]

commit 736194e2a0982311de2ee29ca6382e57b4c506db
Author: Alexander Kojevnikov <alexander kojevnikov com>
Date:   Tue Jun 16 08:39:21 2009 +1000

    Future-proof INSERT INTO queries (bgo#585725)
    
    Replaced all queries in this form:
    
        INSERT INTO SomeTable
            SELECT foo, bar FROM OtherTable
    
    ...with this form:
    
        INSERT INTO SomeTable
            (ham, spam)
            SELECT foo, bar FROM OtherTable
    
    Also fixed errors in MigrateFromLegacyBanshee(), recently added columns broke
    those INSERT INTOs.

 .../DatabaseConfigurationClient.cs                 |    2 +-
 .../Banshee.Database/BansheeDbFormatMigrator.cs    |   23 +++++++++++++------
 .../Banshee.Playlist/PlaylistSource.cs             |    3 ++
 .../Banshee.SmartPlaylist/SmartPlaylistSource.cs   |    3 +-
 .../Banshee.Sources/PrimarySource.cs               |    4 +-
 5 files changed, 24 insertions(+), 11 deletions(-)
---
diff --git a/src/Core/Banshee.Services/Banshee.Configuration/DatabaseConfigurationClient.cs b/src/Core/Banshee.Services/Banshee.Configuration/DatabaseConfigurationClient.cs
index 19784e3..325fbae 100644
--- a/src/Core/Banshee.Services/Banshee.Configuration/DatabaseConfigurationClient.cs
+++ b/src/Core/Banshee.Services/Banshee.Configuration/DatabaseConfigurationClient.cs
@@ -58,7 +58,7 @@ namespace Banshee.Configuration
                 "SELECT EntryID FROM {0} WHERE Key=?", TableName));
             
             insert_command = new HyenaSqliteCommand (String.Format (
-                "INSERT INTO {0} VALUES (NULL, ?, ?)", TableName));
+                "INSERT INTO {0} (EntryID, Key, Value) VALUES (NULL, ?, ?)", TableName));
             
             update_command = new HyenaSqliteCommand (String.Format (
                 "UPDATE {0} SET Value=? WHERE Key=?", TableName));
diff --git a/src/Core/Banshee.Services/Banshee.Database/BansheeDbFormatMigrator.cs b/src/Core/Banshee.Services/Banshee.Database/BansheeDbFormatMigrator.cs
index 47e22f8..7c3bf20 100644
--- a/src/Core/Banshee.Services/Banshee.Database/BansheeDbFormatMigrator.cs
+++ b/src/Core/Banshee.Services/Banshee.Database/BansheeDbFormatMigrator.cs
@@ -338,7 +338,7 @@ namespace Banshee.Database
         [DatabaseVersion (6)]
         private bool Migrate_6 ()
         {
-            Execute ("INSERT INTO CoreConfiguration VALUES (null, 'MetadataVersion', 0)");
+            Execute ("INSERT INTO CoreConfiguration (EntryID, Key, Value) VALUES (null, 'MetadataVersion', 0)");
             return true;
         }
         
@@ -803,9 +803,9 @@ namespace Banshee.Database
                     Value               TEXT
                 )
             ");
-            Execute (String.Format ("INSERT INTO CoreConfiguration VALUES (null, 'DatabaseVersion', {0})", CURRENT_VERSION));
+            Execute (String.Format ("INSERT INTO CoreConfiguration (EntryID, Key, Value) VALUES (null, 'DatabaseVersion', {0})", CURRENT_VERSION));
             if (!refresh_metadata) {
-                Execute (String.Format ("INSERT INTO CoreConfiguration VALUES (null, 'MetadataVersion', {0})", CURRENT_METADATA_VERSION));
+                Execute (String.Format ("INSERT INTO CoreConfiguration (EntryID, Key, Value) VALUES (null, 'MetadataVersion', {0})", CURRENT_METADATA_VERSION));
             }
             
             Execute(@"
@@ -993,26 +993,34 @@ namespace Banshee.Database
         private void MigrateFromLegacyBanshee()
         {
             Execute(@"
-                INSERT INTO CoreArtists 
-                    SELECT DISTINCT null, 0, null, Artist, NULL, 0 
+                INSERT INTO CoreArtists
+                    (ArtistID, TagSetID, MusicBrainzID, Name, NameLowered, NameSort, Rating)
+                    SELECT DISTINCT null, 0, null, Artist, NULL, NULL, 0
                         FROM Tracks 
                         ORDER BY Artist
             ");
             
             Execute(@"
                 INSERT INTO CoreAlbums
+                    (AlbumID, ArtistID, TagSetID, MusicBrainzID, Title, TitleLowered, TitleSort, ReleaseDate,
+                    Duration, Year, IsCompilation, ArtistName, ArtistNameLowered, ArtistNameSort, Rating)
                     SELECT DISTINCT null,
                         (SELECT ArtistID 
                             FROM CoreArtists 
                             WHERE Name = Tracks.Artist
                             LIMIT 1),
-                        0, null, AlbumTitle, NULL, ReleaseDate, 0, 0, 0, Artist, NULL, 0
+                        0, null, AlbumTitle, NULL, NULL, ReleaseDate, 0, 0, 0, Artist, NULL, NULL, 0
                         FROM Tracks
                         ORDER BY AlbumTitle
             ");
             
             Execute (String.Format (@"
                 INSERT INTO CoreTracks
+                    (PrimarySourceID, TrackID, ArtistID, AlbumID, TagSetID, ExternalID, MusicBrainzID, Uri, MimeType,
+                    FileSize, BitRate, Attributes, LastStreamError, Title, TitleLowered, TrackNumber, TrackCount,
+                    Disc, DiscCount, Duration, Year, Genre, Composer, Conductor, Grouping, Copyright, LicenseUri,
+                    Comment, Rating, Score, PlayCount, SkipCount, LastPlayedStamp, LastSkippedStamp, DateAddedStamp,
+                    DateUpdatedStamp, MetadataHash, BPM, LastSyncedStamp, FileModifiedStamp)
                     SELECT 
                         1,
                         TrackID, 
@@ -1060,6 +1068,7 @@ namespace Banshee.Database
                     INSERT INTO CorePlaylists (PlaylistID, Name, SortColumn, SortType)
                         SELECT * FROM Playlists;
                     INSERT INTO CorePlaylistEntries
+                        (EntryID, PlaylistID, TrackID, ViewOrder)
                         SELECT * FROM PlaylistEntries
                 ");
             } catch (Exception e) {
@@ -1212,7 +1221,7 @@ namespace Banshee.Database
             }
 
             if (ServiceManager.DbConnection.Query<int> ("SELECT count(*) FROM CoreConfiguration WHERE Key = 'MetadataVersion'") == 0) {
-                Execute (String.Format ("INSERT INTO CoreConfiguration VALUES (null, 'MetadataVersion', {0})", CURRENT_METADATA_VERSION));
+                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));
             }
diff --git a/src/Core/Banshee.Services/Banshee.Playlist/PlaylistSource.cs b/src/Core/Banshee.Services/Banshee.Playlist/PlaylistSource.cs
index ca33e5a..97b3902 100644
--- a/src/Core/Banshee.Services/Banshee.Playlist/PlaylistSource.cs
+++ b/src/Core/Banshee.Services/Banshee.Playlist/PlaylistSource.cs
@@ -81,6 +81,7 @@ namespace Banshee.Playlist
         {
             add_track_range_command = new HyenaSqliteCommand (@"
                 INSERT INTO CorePlaylistEntries
+                    (EntryID, PlaylistID, TrackID, ViewOrder)
                     SELECT null, ?, ItemID, OrderId + ?
                         FROM CoreCache WHERE ModelID = ?
                         LIMIT ?, ?"
@@ -88,11 +89,13 @@ namespace Banshee.Playlist
 
             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 = ?
diff --git a/src/Core/Banshee.Services/Banshee.SmartPlaylist/SmartPlaylistSource.cs b/src/Core/Banshee.Services/Banshee.SmartPlaylist/SmartPlaylistSource.cs
index 84b0283..9322c33 100644
--- a/src/Core/Banshee.Services/Banshee.SmartPlaylist/SmartPlaylistSource.cs
+++ b/src/Core/Banshee.Services/Banshee.SmartPlaylist/SmartPlaylistSource.cs
@@ -360,7 +360,8 @@ namespace Banshee.SmartPlaylist
             // Wipe the member list clean and repopulate it 
             string reload_str = String.Format (
                 @"DELETE FROM CoreSmartPlaylistEntries WHERE SmartPlaylistID = {0};
-                  INSERT INTO CoreSmartPlaylistEntries 
+                  INSERT INTO CoreSmartPlaylistEntries
+                    (EntryID, SmartPlaylistID, TrackID)
                     SELECT NULL, {0} as SmartPlaylistID, TrackId FROM {1}
                         WHERE {2} AND CoreTracks.PrimarySourceID = {3}
                         {4} {5} {6}",
diff --git a/src/Core/Banshee.Services/Banshee.Sources/PrimarySource.cs b/src/Core/Banshee.Services/Banshee.Sources/PrimarySource.cs
index 9bf98d6..f19049f 100644
--- a/src/Core/Banshee.Services/Banshee.Sources/PrimarySource.cs
+++ b/src/Core/Banshee.Services/Banshee.Sources/PrimarySource.cs
@@ -104,11 +104,11 @@ namespace Banshee.Sources
         protected bool error_source_visible = false;
 
         protected string remove_range_sql = @"
-            INSERT INTO CoreRemovedTracks SELECT ?, TrackID, Uri FROM CoreTracks WHERE TrackID IN (SELECT {0});
+            INSERT INTO CoreRemovedTracks (DateRemovedStamp, TrackID, Uri) SELECT ?, TrackID, Uri FROM CoreTracks WHERE TrackID IN (SELECT {0});
             DELETE FROM CoreTracks WHERE TrackID IN (SELECT {0})";
 
         protected HyenaSqliteCommand remove_list_command = new HyenaSqliteCommand (@"
-            INSERT INTO CoreRemovedTracks SELECT ?, TrackID, Uri FROM CoreTracks WHERE TrackID IN (SELECT ItemID FROM CoreCache WHERE ModelID = ?);
+            INSERT INTO CoreRemovedTracks (DateRemovedStamp, TrackID, Uri) SELECT ?, TrackID, Uri FROM CoreTracks WHERE TrackID IN (SELECT ItemID FROM CoreCache WHERE ModelID = ?);
             DELETE FROM CoreTracks WHERE TrackID IN (SELECT ItemID FROM CoreCache WHERE ModelID = ?)
         ");