NIPL/Implementation

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 most likely)

• 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 textfile holding 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 SONGS table. We can safely assume we'll need a primary key "SONGID" for SONGS.

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, SONGID and GENREID.

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

SELECT GENREID FROM GENRECONNECT WHERE SONGID=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.SONGID=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.