NIPL/Implementation

Revision as of 14:43, 11 June 2002

Implementing a NIPL.

First up, we need to consider what we need to store.

• The music
• MetaData about the music
• Scores for song-pairs.

Method

• Music
  • Music is stored on a per-track basis as any format supported by the player. (.ogg and/or .mp3 are the most likely formats, most likely stored simply as files on the filesystem)

• MetaData
  • Music metadata should be stored in an associated file - simple text file initially? (this ensures the data is stored in a consistent manner regardless of music format type)
  • The Album, track number, Artist(s), date recorded, instruments played, genre... there could be alot of stuff. Refer to:
    • Ogg tagging proposal
    • Genres

• Scores
  • For SongA->SongB where SongB follows songA, we cannot score the A->B pair untill after B begins. Nevertheless, we want to store the score with SongA, since that's where we'll need to know about it for next time we want to refer to it.
  • For each song, a simple database to hold the scores and the song that score relates to. (maybe hold the number of votes also, for added precision when re-scoring)

Issues to think about:

• Implied scores (album/artist/genre) are difficult without further lookup tables keeping track of what songs belong to which album(s), artist(s) and genre(s)...
  • Implied scores are gonna be kinda nescessary IMHO, given it's the only reasonable way to populate the scoring database with any reasonable speed.

Schema

This should maybe be put with some other part of the wiki, but for the time being, here 'tis:

We'll need a TRACKS table. We can safely assume we'll need a primary key "TRACKID" for TRACKS.

• Note that "TRACKID" key uniqueness is covered in NCDI

For the database to work properly, it matters only that genres are uniquely identified - the actual human-readable strings are not necessary. So, we'll make another table GENRES, with two columns: GENREID and NAME. How sub-genres can be implemented, I don't know - I don't think anyone knows how subgenres work yet, so implementing them is pointless.

To connect genres and songs, we'll need a third table, called (why not?) GENRECONNECT. It can have two colums, TRACKID and GENREID.

To find the genre IDs attached to a song with, say, SONGID=123, we run:

SELECT GENREID FROM GENRECONNECT WHERE TRACKID=123

To find the genre names attached to a song, we have the slightly more complex query:

SELECT GENRES.NAME FROM GENRECONNECT, GENRES WHERE GENRECONNECT.TRACKID=123 AND GENRECONNECT.GENREID=GENRES.GENREID

SQL gurus can correct me on this, SQL examples included only for the Edification and Education of Nemo, who persists in his unorthodox obsessions.

...

So what about Albums?

Just like the TRACKS table, we have an ALBUMS table. It has "ALBUMID" primary key (see NCDI), and ALBUMNAME column.

How about Artists?

Similarly, an ARTISTS table with ARTISTID primary key (assignment can be sequential :), and ARTISTNAME column

And Genres?

Am thinking just have a flat list of genres - any sub-genre organisation is, for now, outside the scope of this implementation. Thus, "GENREID" and "GENRENAME"