imported>mutante mNo edit summary |
imported>mutante |
||
Line 142: | Line 142: | ||
==== Make |
==== Make browser interface for searching ==== |
||
Install [[Apache]] |
|||
Make [[PHP]],[[Perl]] or [[Python]] scripted html page that offers search form and connects to MySQL via the local socket (faster than TCP/IP). |
|||
TO DO |
TO DO |
Revision as of 03:44, 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 browser interface for searching
Install Apache
Make PHP,Perl or Python scripted html page that offers search form and connects to MySQL via the local socket (faster than TCP/IP).
TO DO