System.Data.SQLite

Hi,

I've been getting on really well with the SQLite data provider over the last couple of weeks, but have now run into a problem.

I have a process creating a new table, dumping data into that table (INSERT INTO - SELECT FROM multiple tables) and displaying that data. 

A subsequent process then adds additional data into the table (INSERT INTO - SELECT FROM a single table). 

I was expecting SELECT * FROM <new table> ORDER BY ROWID to return the results in the order that the data was created.  Instead there seems to be a completely different order where the newly added rows have ROWIDs lower than the original data rows.

Does anyone have any idea how I make this work properly?

 TIA

Answer:  Create an AUTOINCREMENT field, and use that to ORDER BY instead.

Can you post your queries & schema? 

Sure, current (working schema with data) attached.

To repeat the scenario, you need to script up the 'playlisteditor' table, without the playlisteditorid column, and setting mediaid to be the primary key.

Drop the existing table, and re-create it.

I have a bunch of constants for using different methods of initially populating the table, as so:

 Public Const INSERT_EditorGetPlaylistMedia = "INSERT INTO PlaylistEditor (playlistid, mediaid, media_type, filename, title, name_phonetic, artistid, genreid, year, coverartid, track_number, md5_hash) SELECT %1 as playlistid, media.mediaid, media.media_type, media.filename, media.title, media.name_phonetic, media.artistid, media.genreid, media.year, media.coverartid, media.track_number, media.md5_hash FROM media, artists, genres, playlist_media, playlists, albums, album_media WHERE album_media.mediaid = media.mediaid AND albums.albumid = album_media.albumid AND artists.artistid = media.artistid AND genres.genreid = media.genreid AND playlist_media.mediaid = media.mediaid AND playlists.playlistid = playlist_media.playlistid AND %2 ORDER BY playlist_media.playlist_position;"

Public Const INSERT_EditorGetAlbumMedia = "INSERT INTO PlaylistEditor (playlistid, mediaid, media_type, filename, title, name_phonetic, artistid, genreid, year, coverartid, track_number, md5_hash) SELECT %1 as playlistid, media.mediaid, media.media_type, media.filename, media.title, media.name_phonetic, media.artistid, media.genreid, media.year, media.coverartid, media.track_number, media.md5_hash FROM media, artists, genres, albums, album_media WHERE album_media.mediaid = media.mediaid AND albums.albumid = album_media.albumid AND artists.artistid = media.artistid AND genres.genreid = media.genreid AND %2 ORDER BY albums.name, media.track_number;"

Public Const INSERT_EditorGetArtistMedia = "INSERT INTO PlaylistEditor (playlistid, mediaid, media_type, filename, title, name_phonetic, artistid, genreid, year, coverartid, track_number, md5_hash) SELECT %1 as playlistid, media.mediaid, media.media_type, media.filename, media.title, media.name_phonetic, media.artistid, media.genreid, media.year, media.coverartid, media.track_number, media.md5_hash FROM media, artists, genres, albums, album_media WHERE album_media.mediaid = media.mediaid AND albums.albumid = album_media.albumid AND artists.artistid = media.artistid AND genres.genreid = media.genreid AND %2 ORDER BY albums.name, media.track_number;"

Public Const INSERT_EditorGetGenreMedia = "INSERT INTO PlaylistEditor (playlistid, mediaid, media_type, filename, title, name_phonetic, artistid, genreid, year, coverartid, track_number, md5_hash) SELECT %1 as playlistid, media.mediaid, media.media_type, media.filename, media.title, media.name_phonetic, media.artistid, media.genreid, media.year, media.coverartid, media.track_number, media.md5_hash FROM media, artists, genres, albums, album_media WHERE album_media.mediaid = media.mediaid AND albums.albumid = album_media.albumid AND artists.artistid = media.artistid AND genres.genreid = media.genreid AND %2 ORDER BY albums.name, media.track_number;"

Public Const INSERT_EditorGetYearMedia = "INSERT INTO PlaylistEditor (playlistid, mediaid, media_type, filename, title, name_phonetic, artistid, genreid, year, coverartid, track_number, md5_hash) SELECT %1 as playlistid, media.mediaid, media.media_type, media.filename, media.title, media.name_phonetic, media.artistid, media.genreid, media.year, media.coverartid, media.track_number, media.md5_hash FROM media, artists, genres, albums, album_media WHERE album_media.mediaid = media.mediaid AND albums.albumid = album_media.albumid AND artists.artistid = media.artistid AND genres.genreid = media.genreid AND %2 ORDER BY albums.name, media.track_number;"

For %1 just use -1, for %2 the criteria is based on a value from the media table dependant on the view.  So Genre uses media.genreid=, playlist = playlist_media.playlistid=, album is album_media.albumid= and so on.

Next, displaying the results is done using the following query:

Public Const QUERY_GetPlaylistEditorTracks = "SELECT Title AS Title, artists.name AS Artist, albums.name, genres.name AS Genre, year as Year, filename as Location, playlisteditor.playlisteditorid AS TAGID FROM playlisteditor, artists, genres, albums, album_media WHERE playlisteditor.mediaid = album_media.mediaid AND albums.albumid=album_media.albumid AND artists.artistid = playlisteditor.artistid AND genres.genreid = playlisteditor.genreid ORDER BY playlisteditor.playlisteditorid"

Here, I was simplying using ORDER BY playlisteditor.rowid.

The results were that the initial entries were created with a ROWID of somewhere around 100, incrementing correctly, in the correct order for each row.

Next a secondary process adds more data into the table using the same INSERT queries as above, again dependant on what the user chooses, and then the SELECT query above then used to re-display the contents of the entire table.

This time the results were, as I mentioned in the first post, odd.  The newly added rows started with a rowid of around 50, incrementing in the correct order for the rows added.  The existing rows remained with their 'correct' values.  The newly added rows should have continued in the correct sequence from the rows last added.  There are many other queries run in between to display data elsewhere in the UI before the second addition of data into the table.

Just in case the playlisteditor table isn't in that database, here's the current script for it:

 Right as it seems you figured out by default the ROWID is just unique to the table with no real guarentees as to how it's going to be selected.  Autoincrement makes this behave more like a sql server identity.  Ideally I'd want something like Oracle's %ROWNUM meta-column but I havent figured out enough of the VM to try to implement that yet.