The Standard Mp3 Database Project - Documentation
Tom von Schwerdtner
tvon@etria.org
| Revision History | ||
|---|---|---|
| Revision 0.01 | 2001-08-01 | Revised by: TGvS |
| Initial release. | ||
This document is copyrighted (c) 2001 Tom von Schwerdtner and is distributed under the GNU Free Documentation License, stated below.
No liability for the contents of this document can be accepted. Use the concepts, examples and other content at your own risk. As this is a new edition of this document, there may be errors and inaccuracies, that may of course be damaging to your system. Proceed with caution, and although this is highly unlikely, the author(s) do not take any responsibility for that.
All copyrights are held by their respective owners, unless specifically noted otherwise. Use of a term in this document should not be regarded as affecting the validity of any trademark or service mark.
Naming of particular products or brands should not be seen as endorsements.
You are strongly recommended to take a backup of your system before major installation and backups at regular intervals.
- Table of Contents
- Foreword
- 1. Introduction
- 2. The SMDP Database
- 3. The SMDP API
- 3.1. API Introduction
- 3.2. The Design of the SMDP API
- 3.3. Using the C API
- 3.4. Using the C++ API
- 3.5. Using the Java API
- 3.6. Using the PHP API
- 3.7. Using the Perl API
- A. SMDP ERD
- A.1. SMDP ERD
- A.2. Optional ERD
- B. feature_type information
- C. Correlation Identifiers
- D. Installing the SMDP
- D.1. Setting up the Database
- D.1.1. Installing a release
- D.1.2. Installing from CVS
- E. Utility Programs
- E.1. dia2sql
- E.1.1. Description
- E.1.2. Requirements
- E.1.3. Usage
Foreword
A few years ago I went to one of those "Computer Expo and Sale" things that you always hear about on TV (the ones with the monster truck esque "sunday Sunday SUNDAY!" announcer that really speaks volumes about what the advertising people think of the target audience). I had never been to one of these shows and my only reason for going this time was to pick up a very large hard drive for a very small amount of money.
After a few hours of very careful comparison shopping, I settled my eyes on a 20 gig drive priced at a modest $150 or so (roughly the exact same price I would have payed for it anywhere else). The size of the drive was adequate for my purposes, since all I wanted to do was to pack it full of mp3's for my new "home-stereo" Linux box I had just setup, and you can fit somewhere around 10,000 mp3's on a 20 gig drive without any problems, so I was fairly pleased with purchase.
At any rate, I took the drive home and quickly began ripping every single CD I owned onto it, and for a month or two it served me well as a fairly large repository of mp3's. Then, one sad day while I was trying out some "experimental" software (namely the BeOS demo for Linux), the entire drive was hopelessly corrupted beyond the reach of fsck or any other diagnostic software I could get my hands on. Needless to say, I was not happy. I ranted and raved for a while. I posted to newsgroups and mailing lists trying to find ways to blame anyone but myself for using software that was very clearly labeled "alpha" and "100% totally and completely unsupported, use at your own risk". Then, I just sat there, and after a few hours, I went on with my life and totally forgot about the drive.
About a year passed, maybe more, maybe less, and one faithful day a few months ago I randomly ran fsck on the drive for kicks, and because I had upgraded things quite a few times since I had last tried anything on the drive. Well, to my total shock, fsck started running normally on the drive and, where previously it had very slowly spat out I/O errorrs, it quickly spat out less serious but still not-kosher errors about inodes and last-mod times, and after about 20 minutes of checking, it finished up and I was able to mount the drive. Things were still a mess on the drive since the directory strucute was trashed and all the files had archaic names in the lost+found directory, but after a little reorganizing, I had access to my beloved mp3 collection again.
Since I suddenly had a reasonable collection of mp3's again, I really wanted a good way to use and maintain them. I did a little research on mp3 players and collection managers and came to the conclusion that one of the SQL based mp3 players was the best solution. I downloaded a few of these apps and after trying them out I became somewhat irritated at the amount of effort it took to setup a database, index it and load it up into an app just to find out that I really didn't like the app all that much because it lacked some feature I wanted. I was not really suprised since most of the non-SQL mp3 players out there don't do everything I want them to do, but since they all interact directly with the mp3 files, there is no problem with using one app to create playslits, another to listen to them, another to tag them, and a another to rename the files based on the tags. The problem with the SQL based apps is that they all used thier own custom datbase designs, so one app could not use the database created by a seperate app. So, my little brain started to churn out some smoke and I decided that it would be very cool if there was some sort of standard database for storing mp3's that all these apps used, hence the creation of the Standard Mp3 Database Proejct.
At any rate, the SMDP wont change the world, in fact, most people will never even know it exists, but it has so far been serving to entertain a small handful of coders around the world. All I really wanted out of this project was a little fun, and with some luck a useful end product, and while the use of the ned product has yet to be determined, I have certainly enjoyed the experience to date. So, I hope you like it.
- Tom
Chapter 1. Introduction
The Standard Mp3 Database Project (SMDP) aims to make mp3 management easier for both the developer and the end-user. This document servers to assist developers in taking full advantage of the SMDP database and API, while cluing in the users as to the extent of the possibilities --FIXME--
Chapter 2. The SMDP Database
2.1. Database Introduction
In this section we will discuss some of the the ideas and reasoning that went into the design of the SMDP database. We will also discuss the varios diffrences you will encounter when using diffrent RDBMS's with the SMDP.
This section is not meant to be a tutorial or HOWTO on using databases, as some initial knowledge is required to understand the concepts discussed.
2.2. The Design of the SMDP Database
The SMDP database is designed to allow for a very dynamic set of information relating to mp3 and ogg files. The basic structure covers the usual entities in a traditional relational model. This is then complemented by a set of tables essentially composing a directory-like storage structure, geared toward both specific attribute classes as well as a completely "free" relations table - ie where the type of the objects are specified on both sides of the relation.
This provides - in addition to the usual information such as artist name, song title, album name and so on - for space to define unique information as well as to establish relationships betewwen any two objects in the database.
Anyways, enough of the hot air...read on for more information.
2.2.1. Central Tableset
The central tableset is composed of the song, artist, collection (album), file, and media (filesystem) tables. Aside from the feature tables (Section 2.2.2), these will be the most frequently queried tables in the database.
2.2.2. The feature and feature_type Tables
The feature tables serve to store specific information relating to the central tableset. In turn, the feature tables all point to 'tag' information stored in the feature_type table.
For each of the main objects in the database, there exists a companion 'feature' table. The 'feature' tables serves to further describe the records in the object table.
See Appendix B for information on translating Id3 tags and Ogg comments into SMDP records.
2.2.3. Levels of Data Storage
To allow some felxability in the size and complexity of the database, there will be a number of storage options available. The details have not been hashed out yet, but there will most likely be option-specific settings as well as more generalized settings that take care of the specific options. Some of the options may include:
store_binary. [true|false] Determines if binary information from tags is actually stored in the database or if the location and type of information is stored (but not the binary data)
use_musicbrainz. [true|false] Weather or not to store MusicBrainz information in the database.
use_freedb. [true|false] Weather or not to store FreeDB information in the database.
Chapter 3. The SMDP API
3.1. API Introduction
API Introduction goes here
3.5. Using the Java API
You can find the javadoc files for the java api at http://wwwsmdp.sourceforge.net/docs/javadoc/index.html
Appendix B. feature_type information
Up-to-date ID3 Frame Identfiers can be found at the official id3 website.
Table B-1. feature_type information
| Id | FIXME-label | Description | SMDP Record |
|---|---|---|---|
| ID3FN_NOFIELD | No field | ||
| ID3FN_TEXTENC | Text encoding (unicode or ASCII) | ||
| ID3FN_TEXT | Text field | ||
| ID3FN_URL | A URL | ||
| ID3FN_DATA | Data field | ||
| ID3FN_DESCRIPTION | Description field | ||
| ID3FN_OWNER | Owner field | ||
| ID3FN_EMAIL | Email field | ||
| ID3FN_RATING | Rating field | ||
| ID3FN_FILENAME | Filename field | ||
| ID3FN_LANGUAGE | Language field | ||
| ID3FN_PICTURETYPE | Picture type field | ||
| ID3FN_IMAGEFORMAT | Image format field | ||
| ID3FN_MIMETYPE | Mimetype field | ||
| ID3FN_COUNTER | Counter field | ||
| ID3FN_ID | Identifier/Symbol field | ||
| ID3FN_VOLUMEADJ | Volume adjustment field | ||
| ID3FN_NUMBITS | Number of bits field | ||
| ID3FN_VOLCHGRIGHT | Volume chage on the right channel | ||
| ID3FN_VOLCHGLEFT | Volume chage on the left channel | ||
| ID3FN_PEAKVOLRIGHT | Peak volume on the right channel | ||
| ID3FN_PEAKVOLLEFT | Peak volume on the left channel | ||
| ID3FN_TIMESTAMPFORMAT | SYLT Timestamp Format | ||
| ID3FN_CONTENTTYPE | SYLT content type | ||
| ID3FN_LASTFIELDID | Last field placeholder | ||
| ID3FID_NOFRAME | No known frame | ||
| ID3FID_AUDIOCRYPTO | Audio encryption | ||
| ID3FID_PICTURE | Attached picture | ||
| ID3FID_COMMENT | Comments | ||
| ID3FID_COMMERCIAL | Commercial frame | ||
| ID3FID_CRYPTOREG | Encryption method registration | ||
| ID3FID_EQUALIZATION | Equalization | ||
| ID3FID_EVENTTIMING | Event timing codes | ||
| ID3FID_GENERALOBJECT | General encapsulated object | ||
| ID3FID_GROUPINGREG | Group identification registration | ||
| ID3FID_INVOLVEDPEOPLE | Involved people list | ||
| ID3FID_LINKEDINFO | Linked information | ||
| ID3FID_CDID | Music CD identifier | ||
| ID3FID_MPEGLOOKUP | MPEG location lookup table | ||
| ID3FID_OWNERSHIP | Ownership frame | ||
| ID3FID_PRIVATE | Private frame | ||
| ID3FID_PLAYCOUNTER | Play counter | ||
| ID3FID_POPULARIMETER | Popularimeter | ||
| ID3FID_POSITIONSYNC | Position synchronisation frame | ||
| ID3FID_BUFFERSIZE | Recommended buffer size | ||
| ID3FID_VOLUMEADJ | Relative volume adjustment | ||
| ID3FID_REVERB | Reverb | ||
| ID3FID_SYNCEDLYRICS | Synchronized lyric/text | ||
| ID3FID_SYNCEDTEMPO | Synchronized tempo codes | ||
| ID3FID_ALBUM | Album/Movie/Show title | ||
| ID3FID_BPM | BPM (beats per minute) | ||
| ID3FID_COMPOSER | Composer | ||
| ID3FID_CONTENTTYPE | Content type | ||
| ID3FID_COPYRIGHT | Copyright message | ||
| ID3FID_DATE | Date | ||
| ID3FID_PLAYLISTDELAY | Playlist delay | ||
| ID3FID_ENCODEDBY | Encoded by | ||
| ID3FID_LYRICIST | Lyricist/Text writer | ||
| ID3FID_FILETYPE | File type | ||
| ID3FID_TIME | Time | ||
| ID3FID_CONTENTGROUP | Content group description | ||
| ID3FID_TITLE | Title/songname/content description | ||
| ID3FID_SUBTITLE | Subtitle/Description refinement | ||
| ID3FID_INITIALKEY | Initial key | ||
| ID3FID_LANGUAGE | Language(s) | ||
| ID3FID_SONGLEN | Length | ||
| ID3FID_MEDIATYPE | Media type | ||
| ID3FID_ORIGALBUM | Original album/movie/show title | ||
| ID3FID_ORIGFILENAME | Original filename | ||
| ID3FID_ORIGLYRICIST | Original lyricist(s)/text writer(s) | ||
| ID3FID_ORIGARTIST | Original artist(s)/performer(s) | ||
| ID3FID_ORIGYEAR | Original release year | ||
| ID3FID_FILEOWNER | File owner/licensee | ||
| ID3FID_LEADARTIST | Lead performer(s)/Soloist(s) | ||
| ID3FID_BAND | Band/orchestra/accompaniment | ||
| ID3FID_CONDUCTOR | Conductor/performer refinement | ||
| ID3FID_MIXARTIST | Interpreted, remixed, or otherwise modified by | ||
| ID3FID_PARTINSET | Part of a set | ||
| ID3FID_PUBLISHER | Publisher | ||
| ID3FID_TRACKNUM | Track number/Position in set | ||
| ID3FID_RECORDINGDATES | Recording dates | ||
| ID3FID_NETRADIOSTATION | Internet radio station name | ||
| ID3FID_NETRADIOOWNER | Internet radio station owner | ||
| ID3FID_SIZE | Size | ||
| ID3FID_ISRC | ISRC (international standard recording code) | ||
| ID3FID_ENCODERSETTINGS | Software/Hardware and settings used for encoding | ||
| ID3FID_USERTEXT | User defined text information | ||
| ID3FID_YEAR | Year | ||
| ID3FID_UNIQUEFILEID | Unique file identifier | ||
| ID3FID_TERMSOFUSE | Terms of use | ||
| ID3FID_UNSYNCEDLYRICS | Unsynchronized lyric/text transcription | ||
| ID3FID_WWWCOMMERCIALINFO | Commercial information | ||
| ID3FID_WWWCOPYRIGHT | Copyright/Legal infromation | ||
| ID3FID_WWWAUDIOFILE | Official audio file webpage | ||
| ID3FID_WWWARTIST | Official artist/performer webpage | ||
| ID3FID_WWWAUDIOSOURCE | Official audio source webpage | ||
| ID3FID_WWWRADIOPAGE | Official internet radio station homepage | ||
| ID3FID_WWWPAYMENT | Payment | ||
| ID3FID_WWWPUBLISHER | Official publisher webpage | ||
| ID3FID_WWWUSER | User defined URL link | ||
| ID3FID_METACRYPTO | Encrypted meta frame (id3v2.2.x) | ||
| ID3FID_METACOMPRESSION | Compressed meta frame (id3v2.2.1) | ||
| Id | FIXME-label | Description | SMDP Record |
Appendix C. Correlation Identifiers
If you wish to add identifiers, please contact the SMDP developers first to prevent overlapping values.
Table C-1. Correlation Identifiers
| Binary | Decimal | Represents |
|---|---|---|
| 000001 | 1 | Song |
| 000010 | 2 | Artist |
| 000011 | 3 | Album |
| 000100 | 4 | |
| 5 | ||
| 6 | ||
| 7 | ||
| 8 | ||
| 9 | ||
| 10 | ||
| 11 | ||
| 12 | ||
| 13 | ||
| 14 | ||
| 15 | ||
| 16 | ||
| 17 | ||
| 18 | ||
| 19 | ||
| 20 | ||
| 21 | ||
| 22 | ||
| 23 | ||
| 24 | ||
| 25 | ||
| 26 | ||
| 27 | ||
| 28 | ||
| 29 | ||
| 30 | ||
| 31 | ||
| 32 | ||
| Binary | Decimal | Represents |
Appendix D. Installing the SMDP
FIXME - some introductory nonsense
D.1. Setting up the Database
Untill there are packages available, you will have to install the schema from the released tarballs or from a CVS checkout.
D.1.2. Installing from CVS
$ cd /path/to/schema/dir/
$ autoconf
$ ./configure
loading cache ./config.cache
checking whether make sets ${MAKE}... (cached) yes
checking for perl... (cached) /usr/bin/perl
checking for mysql... (cached) /usr/bin/mysql
checking for psql... no
checking for cat... (cached) /bin/cat
creating ./config.status
creating Makefile
$ make
make schema Build sql-scripts for production-systems
make schema-devel Build sql-scripts for developement
make csv Build csv files
make db-init Initialize a database
make db-init-devel Initialize a database with devel-mode
$ make db-init
|
Appendix E. Utility Programs
fixme
E.1. dia2sql
E.1.1. Description
dia2sql generates sql scripts from native Dia [1] files.
E.1.2. Requirements
In order to use dia2sql, you need:
Perl: any version greater 5.00x will work.
The expat library, you can find it here: http://www.jclark.com/xml/expat.html
XML::Parser from CPAN, you can install it via the CPAN shell (at least on *nix systems you can :)
E.1.3. Usage
dia2sql.pl v1.2 smdp-pl7 - \$Revision: 1.2 $
original author: 2001 by Alexander Troppmann
heavily patched by the SMDP-Group
Usage: dia2sql.pl [options] infile.dia [outfile.sql]
Options may be:
-h print this help.
--mysql create SQL for mysql
--postgresql create SQL for postgresql
--mssql create SQL for ms-sql-server
--debug enter debug-mode
--with-comments write comments
--with-create write create statements for tables
--with-html-output writes print-statements with html-stuff
into sql-script. Only useful for
database-parsers that will display that
like isql.exe from MS-SQL Server.
--absolute-clean write drop/create statements for the
database and all tables. use it wisely!
--create-indices create indeces (postgres-only)
--convert convert datatypes to fit target db
--database=<database>
name of the database to be used for the
create-statement if not given, defaults
to smdp.
--datatype=<file> file needs to hold the mapping of the
datatypes. (defaults to datatypes.txt)
--input=<input-type> Datatypes in input belong to
<input-type>.
valid values: mysql (for mysql)
mssql (for ms-sql)
pgsql (for postgresql)
Converts xml data input from Dia to sql statements. If outfile.sql is not
specified the sql statements will be printed to STDOUT.
Edit dia2sql.pl and change the configuration at top of the Perl script.
Make sure you have defined the right database (MySQL or PostgreSQL) for
SQL output.
|