imported>mutante mNo edit summary |
imported>mutante mNo edit summary |
||
Line 147: | Line 147: | ||
{{UnderConstruction}} |
{{UnderConstruction}} |
||
[[Category:HowTo]] |
|||
[[Category:Linux]] |
|||
[[Category:Under Construction]] |
Revision as of 03:42, 8 May 2005
Want your mp3 id tags in a local mysql database?
On a linux system? commandline?
Preparations
On Debian:
Install mysql
apt-get install mysql-server mysql-client
connect, setup root password, create user,create database,grant rights,...
see MySQL
Create table
CREATE TABLE `muzak` ( `id` int(11) NOT NULL auto_increment, `filename` varchar(255) default NULL, `artist` varchar(255) default NULL, `title` varchar(255) default NULL, `album` varchar(255) default NULL, `comment` varchar(255) default NULL, `track` tinyint(2) default NULL, `year` smallint(4) default NULL, `genre` varchar(255) default NULL, `path` varchar(255) default NULL, `filesize` smallint(6) default NULL, `copyright` varchar(255) default NULL, `layer` varchar(255) default NULL, `stereo` varchar(32) default NULL, `goodframes` int(6) default NULL, `badframes` int(6) default NULL, `frequency` int(6) default NULL, `playtime` int(6) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `path` (`path`) ) TYPE=MyISAM;
Table structure
mysql> describe muzak; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | filename | varchar(255) | YES | | NULL | | | artist | varchar(255) | YES | | NULL | | | title | varchar(255) | YES | | NULL | | | album | varchar(255) | YES | | NULL | | | comment | varchar(255) | YES | | NULL | | | track | tinyint(2) | YES | | NULL | | | year | smallint(4) | YES | | NULL | | | genre | varchar(255) | YES | | NULL | | | path | varchar(255) | YES | MUL | NULL | | | filesize | smallint(6) | YES | | NULL | | | copyright | varchar(255) | YES | | NULL | | | layer | varchar(255) | YES | | NULL | | | stereo | varchar(32) | YES | | NULL | | | goodframes | int(6) | YES | | NULL | | | badframes | int(6) | YES | | NULL | | | frequency | int(6) | YES | | NULL | | | playtime | int(6) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+
Install mp3info
apt-get install mp3info
Description: An MP3 technical info viewer and ID3 1.x tag editor MP3Info has an interactive mode (using curses) and a command line mode.
(we will use the command line mode of course)
Make import script
Then make a bash script to create an input file. This (or something similar) creates a file with SQL syntax for us:
#/bin/bash # "mp3 id3 tag to sql" importer by mutante # reads id3 tags using "mp3info" and outputs a dumpfile that can be imported to mysql # how to import outfile: # mysql -u user -p muzak < mp3.sql # good ones to outfile outfile="mp3.sql" # errors to error file (files without id tag) errfile="mp3.err" # counter count=1 # adjust your path here find /home/mutante/Music/ -name \*.mp3 -print | while read file do # writing to out-files (mp3info rocks, -p option is like a printf statement) mp3info -p "insert into muzak (filename,artist,title,album,comment,track,year,genre,path,filesize,copyright,layer, stereo,goodframes,badframes,frequency,playtime) VALUES (\"%f\",\"%a\",\"%t\",\"%l\",\"%c\",\"%n\",\"%y\",\"%g\",\"%F\",\"%k\",\"%C\",\"%L\",\"%o\",\"%u\",\"%b\",\"%Q\",\"%S\");" "$file" 1>>$outfile 2>>$errfile # some additional,but unnecessary screen output,to see if the script is still running echo -e "\n \n id: $count -" mp3info -p "%a,%t,%F" "$file" let "count+=1" done
Import
Now we have two new files, mp3.err contains a list of files we still need to fix and write correct id3 tags (can also use mp3info for that later), and mp3.sql can be imported to the server via:
mysql -u user -p muzak < mp3.sql
And here i stop for tonight, because i still get in trouble with more special characters inside the tags ,need to escape " ' \. After getting rid of ' there are still "'s.
(If you want to clean your table and reset the id to 0 after unsuccesful imports use truncate table muzak).
Make Webinterface for Searching
TO DO