Todd Harris


  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   : 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   : $db = $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 = $db->debug
 Function: prints out debugging information
 Returns : debugging information
 Args    : none
 Status  : Private


Audio::DB::Adaptor::dbi::sqlite -- Database adaptor for a specific mysql schema


See Audio::DB


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

Supplementary tables used by

  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

Available Methods


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 <>.

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 <>.


Audio::DB,Audio::DB::Web, Apache::MP3