Title : new Usage : $db = Audio::DB->new(@args) Function: create a new adaptor Returns : an Audio::DB object Args : see below Status : Public
Argument Description -------- ----------- -dsn the DBI data source, e.g. 'dbi:mysql:music_db' or "music_db" Also accepts DB or database as synonyms. -user username for authentication -pass the password for authentication
Title : _create_database Usage : _create_database(user,pass,host,db) Function: create the database Returns : a boolean indicating the success of the operation Args : username, password, host and database Status : protected
Called internally by new to create the database if it does not already exist.
Title : do_initialize Usage : $success = $db->do_initialize($drop_all) Function: initialize the database Returns : a boolean indicating the success of the operation Args : a boolean indicating whether to delete existing data Status : protected
This method will load the schema into the database. If $drop_all is true, then any existing data in the tables known to the schema will be deleted.
Internally, this method calls schema() to get the schema data.
Title : drop_all Usage : $dbh->drop_all Function: empty the database Returns : void Args : none Status : protected
This method drops the tables known to this module. Internally it calls the abstract tables() method to get a list of all tables to drop.
Title : tables Usage : @tables = $db->tables Function: return list of tables that belong to this module Returns : list of tables Args : none Status : protected
This method returns a list of all the tables in the database.
Title : schema Usage : ($schema,$raw_schema) = $mp3->schema Function: return the CREATE script for the schema and the raw_schema as a hashref for easily accessing columns in proper order. Returns : a hash of CREATE statements; hash of tables and parameters Args : none Status : protected
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.
Title : dbh Usage : $dbh->dbh Function: get database handle Returns : a DBI handle Args : none Status : Public
Title : DESTROY Usage : $dbh->DESTROY Function: disconnect database at destruct time Returns : void Args : none Status : protected
This is the destructor for the class.
Title : debug Usage : $dbh = $dbh->debug Function: prints out debugging information Returns : debugging information Args : none Status : Private
Audio::DB::Adaptor::dbi::mysql -- Database adaptor for a specific mysql schema
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.
The schema uses several tables:
artists This the artists data table. Its columns are:
artist_id artist ID (integer); primary key artist artist name (string); may be null; indexed
albums This is the albums table. Its columns are:
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)
songs This is the primary songs table. Its columns are:
song_id song ID (integer); primary key title song title (string) artist_id artist ID (integer); indexed album_id album ID (integer) genre_id genre ID (integer) # may be superceded...see note track track number (integer) duration formatted song length (string) seconds length in seconds (integer) lyrics song lyrics (long text) comment ID3 tag comment (text) bitrate encoded bitrate (integer) samplerate sample rate (real) format format of the file (ie MPEG) (string) channels channels (string) tag_types type of ID3 tags present (ie ID3v2.3.0) (text) filename file name (text) filesize file size in bytes (real) filepath absolute path (text) year the year tag for single tracks (since singles or songs on compilations each may be different) (integer) uber_playcount total times the song has been played uber_rating overall song rating (see "users" below)
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:
playlist_id may not be null song_id may not be null
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.
Lots of error checking needs to be added. Support for custom data schemas, including new data types like more extensive artist info, paths to images, lyrics, etc.
Robusticize new for different adaptor types.
Copyright 2002, Todd W. Harris <firstname.lastname@example.org>.
This module is distributed under the same terms as Perl itself. Feel free to use, modify and redistribute it as long as you retain the correct attribution.
Much of this module was derived from Bio::DB::GFF, written by Lincoln Stein <email@example.com>.