new
Title : new
Usage : $db = Music::DB->new(@args)
Function: create a new adaptor
Returns : an Music::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 authenticationdo_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 : protectedThis 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 : protectedThis 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 : protectedThis 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 : protectedThis 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 : PublicDESTROY
Title : DESTROY
Usage : $dbh->DESTROY
Function: disconnect database at destruct time
Returns : void
Args : none
Status : protectedThis is the destructor for the class.
debug
Title : debug
Usage : $dbh = $db->debug
Function: prints out debugging information
Returns : debugging information
Args : none
Status : PrivateNAME
Music::DB::Adaptor::dbi::sqlite -- Database adaptor for a specific mysql schema
SYNOPSIS
See Music::DB
DESCRIPTION
This adaptor implements a specific mysql database schema that is compatible with Music::DB. It inherits from Music::DB. In addition to implementing the abstract SQL-generating methods of Music::DB::Adaptor::dbi, this module also implements the data loading functionality of Music::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 nullsong_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 nullsong_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 singleSupplementary tables used by Web.pm
Music::DB::Web provides a web interface to databases
created with Music::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 nullAvailable 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>.