/ 
Audio-DB-0.01
River stage zero No dependents
/ Audio::DB::Adaptor::dbi::sqlite

new

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

do_initialize

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.

drop_all

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.

tables

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.

schema

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.

dbh

Title   : dbh
Usage   : $db = $dbh->dbh
Function: get database handle
Returns : a DBI handle
Args    : none
Status  : Public

DESTROY

Title   : DESTROY
Usage   : $dbh->DESTROY
Function: disconnect database at destruct time
Returns : void
Args    : none
Status  : protected

This is the destructor for the class.

debug

Title   : debug
Usage   : $dbh = $db->debug
Function: prints out debugging information
Returns : debugging information
Args    : none
Status  : Private

NAME

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

SYNOPSIS

See Audio::DB

DESCRIPTION

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

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

BUGS

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.

TODO

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.

AUTHOR

Copyright 2002, Todd W. Harris <harris@cshl.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.

ACKNOWLEDGEMENTS

Much of this module was derived from Bio::DB::GFF, written by Lincoln Stein <lstein@cshl.org>.

SEE ALSO

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