I’ve finally found some time to realize my concept written here. In the meantime I’ve also managed to connect external hard drive to my Raspberry Pi. I have a lot of problems with it and tried lots of approaches but finally it’s working (I will describe it in the next post).
Now my music library is available in Raspberry Pi 🙂 It’s quite a lot of mp3s so I decided to write a quick little script in Python that will fetch basic data about file name, location, size, song duration, bitrate and data from ID3 tags. I’m also computing checksum of file to discover duplicates. All this data is later stored in MySQL database.
At first I prepared a SQL script with table creation:
CREATE TABLE IF NOT EXISTS `music` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`file_name` text COLLATE utf8_unicode_ci NOT NULL,
`file_format` text COLLATE utf8_unicode_ci NOT NULL,
`file_location` text COLLATE utf8_unicode_ci NOT NULL,
`file_checksum` text COLLATE utf8_unicode_ci NOT NULL,
`file_size` INT(11) NOT NULL,
`tag_title` text COLLATE utf8_unicode_ci NOT NULL,
`tag_artist` text COLLATE utf8_unicode_ci NOT NULL,
`tag_album` text COLLATE utf8_unicode_ci NOT NULL,
`tag_year` text COLLATE utf8_unicode_ci NOT NULL,
`tag_comment` text COLLATE utf8_unicode_ci NOT NULL,
`tag_genre` text COLLATE utf8_unicode_ci NOT NULL,
`tag_tracknumber` INT(11) NOT NULL,
`song_length` INT(11) NOT NULL,
`song_bitrate` INT(11) NOT NULL,
`date_added` datetime NOT NULL,
`health` INT(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; |
CREATE TABLE IF NOT EXISTS `music` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`file_name` text COLLATE utf8_unicode_ci NOT NULL,
`file_format` text COLLATE utf8_unicode_ci NOT NULL,
`file_location` text COLLATE utf8_unicode_ci NOT NULL,
`file_checksum` text COLLATE utf8_unicode_ci NOT NULL,
`file_size` int(11) NOT NULL,
`tag_title` text COLLATE utf8_unicode_ci NOT NULL,
`tag_artist` text COLLATE utf8_unicode_ci NOT NULL,
`tag_album` text COLLATE utf8_unicode_ci NOT NULL,
`tag_year` text COLLATE utf8_unicode_ci NOT NULL,
`tag_comment` text COLLATE utf8_unicode_ci NOT NULL,
`tag_genre` text COLLATE utf8_unicode_ci NOT NULL,
`tag_tracknumber` int(11) NOT NULL,
`song_length` int(11) NOT NULL,
`song_bitrate` int(11) NOT NULL,
`date_added` datetime NOT NULL,
`health` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;