This method returns a list containing the various CREATE statements needed to initialize the database tables. Each create statement is built programatically so I can maintain all fields in a central location . This raw schema is returned for building temporary tables for loading.

This adaptor implements a specific mysql database schema that is compatible with Audio::DB. It inherits from Audio::DB. In addition to implementing the abstract SQL-generating methods of Audio::DB::Adaptor::dbi, this module also implements the data loading functionality of Audio::DB.

album_id album ID (integer); primary key
album album name (string); may be null; indexed
album_type one of compilation or standard; may be null
total_tracks total songs on album (integer)
year self explanatory, no? (integer)

Currently, ID3 tags support but a single genre. The genre_id is now stored with the song table. Multiple genres may be assigned via the song_genres join table below. The 'year' is a database denormalization that allows the assignment of years to single tracks not belonging to an album.

genres This is the genres table. Its columns are:

genre_id genre ID (integer); primary key
genre genre (string)

album_artists This is the album_artists join table. Its columns are:

artist_id artist ID. May not be null.
album_id album ID. May not be null.

artist_genres This is the artists_genres join table. It enables multiple genres to be assigned to a single artist. Its columns are:

artist_id artist ID. May not be null
genre_id genre ID. May not be null

song_genres This is the song_genres join table. It enables multiple genres to be assigned to a single song. Its columns are:

song_id artist ID. May not be null
genre_id genre ID. May not be null

song_types This is the song_types join table. It enables multiple general descriptive types to be assigned to a single song. Its columns are:

song_id artist ID. May not be null
type one of: live cover bootleg single

Audio::DB::Web provides a web interface to databases
created with Audio::DB. It requires a few extra
tables that are not directly related to the MP3
tag data.

users The users table provides support for multiple users of the database. Its columns are:

user_id user UD. May not be null; primary key
first users first name (text)
last last name (text_
email email address (text)
username username in the system (text)
password password (text)
privs privileges (text)
joined date user joined (date)
last_access date of last access (timestamp)
songs_played number of songs played (integer)

user_ratings The user_ratings table allows users to maintain individual ratings and playcounts for every song (as opposed to the uber playcounts and ratings above). I'll probably pitch the uber columns above, instead determining these values in middleware.

user_id may not be null
song_id may not be null
rating user rating from 1-100 (integer)
playcount user playcount (integer)

playlists Playlist names and descriptions. Columns are:

playlist_id may not be null; primary key
playlist the playlist name (text)
description brief description of the playlist (text)
user_id the owner of the playlist (integer)
is_shared yes/no. Controls the public-accessiblity of the playlist
created date playlist created. (date)
viewed number of times playlist viewed (integer)

playlist_songs A small join table that associates songs with playlists:

This module implements a fairly complex internal data structure, which in itself rests upon lots of things going right, like reading ID3 tags, tag naming conventions, etc. On top of that, I wrote this in a Starbucks full of screaming children.