by Steegy » Mon Apr 24, 2006 12:20 pm
Mood, Tempo, ... *are* stored in the DB.
The list table gives, as you said, a list of all possible entries (and each entry has an ID)
In the AddSongInfoInt table, the actual references to the List table are stored for each song in the database (that has a mood, tempo, ... defined). The IntData column is linked to List.ID.
So to find the a certain characteristic of a song, you look in the Songs table to get it's ID. With that ID, you search the matching IDSong in the AddSongInfoInt table. You will then possibly see different entries for the same song, with different IntData fields. Each IntData field represents a certain characteristic that is linked to the ID of the List table.
E.g., if you want to find all songs matching a certain characteristic (from the PlayHotkeys_v1.0.vbs/Utility_SQL.vbs script)
Code: Select all
' Returns a SDBSongIterator object (EOF/Item/Next) for all songs with the specified InfoIntValue
Public Function SQLGetSongsInfoInt(InfoIntValue)
Set SQLGetSongsInfoInt = SDB.Database.QuerySongs("AND Songs.ID IN (SELECT Songs.ID FROM ((Lists INNER JOIN AddSongInfoInt ON Lists.ID = AddSongInfoInt.IntData) INNER JOIN Songs ON AddSongInfoInt.IDSong = Songs.ID) INNER JOIN Artists ON Songs.IdArtist = Artists.ID WHERE Lists.TextData = '" & InfoIntValue & "')")
End Function
maybe code can be made "easier" like "SELECT blablabla FROM foo, bar, baz ...", but the above way seems to be the preferred sql way.
See the "scripting resources" (link in my signature) to find a text file with the database locations for (almost) all available track properties.
Cheers
Steegy
Mood, Tempo, ... *are* stored in the DB.
The list table gives, as you said, a list of all possible entries (and each entry has an ID)
In the AddSongInfoInt table, the actual references to the List table are stored for each song in the database (that has a mood, tempo, ... defined). The IntData column is linked to List.ID.
So to find the a certain characteristic of a song, you look in the Songs table to get it's ID. With that ID, you search the matching IDSong in the AddSongInfoInt table. You will then possibly see different entries for the same song, with different IntData fields. Each IntData field represents a certain characteristic that is linked to the ID of the List table.
E.g., if you want to find all songs matching a certain characteristic (from the PlayHotkeys_v1.0.vbs/Utility_SQL.vbs script)
[code]' Returns a SDBSongIterator object (EOF/Item/Next) for all songs with the specified InfoIntValue
Public Function SQLGetSongsInfoInt(InfoIntValue)
Set SQLGetSongsInfoInt = SDB.Database.QuerySongs("AND Songs.ID IN (SELECT Songs.ID FROM ((Lists INNER JOIN AddSongInfoInt ON Lists.ID = AddSongInfoInt.IntData) INNER JOIN Songs ON AddSongInfoInt.IDSong = Songs.ID) INNER JOIN Artists ON Songs.IdArtist = Artists.ID WHERE Lists.TextData = '" & InfoIntValue & "')")
End Function[/code]maybe code can be made "easier" like "SELECT blablabla FROM foo, bar, baz ...", but the above way seems to be the preferred sql way.
[i]See the "scripting resources" (link in my signature) to find a text file with the database locations for (almost) all available track properties.[/i]
Cheers
Steegy