UUGRN::PostInstallParty::Shellscripting::MySQL</titel> </head> <body> <h1><a href="http://www.uugrn.org/">UUGRN</a>::<a href="http://www.uugrn.org/party/">PostInstallParty</a>::Shellscripting</h1> <h3>MySQL</h3> <p>MySQL geht nicht nur mit Perl oder PHP</p> <dl> <dt><b><a href="http://rabe.uugrn.org/scripts/"></a>mp3s in eine Datenbank laden</b></dt> <dd> <p>Wir haben folgende Tabelle <em>mp3.files</em>:</p> <pre> mysql> desc files; +------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | md5 | varchar(32) | YES | MUL | NULL | | | filename | varchar(255) | YES | MUL | NULL | | | bitrate | int(11) | YES | | NULL | | | samplerate | int(11) | YES | | NULL | | | title | varchar(255) | YES | MUL | NULL | | | artist | varchar(255) | YES | MUL | NULL | | | album | varchar(255) | YES | | NULL | | | comment | varchar(255) | YES | | NULL | | | year | year(4) | YES | | NULL | | | trackno | int(11) | YES | | NULL | | | genre | int(11) | YES | | NULL | | | size | bigint(20) | YES | | NULL | | | mtime | timestamp(14) | YES | | NULL | | +------------+---------------+------+-----+---------+----------------+ 14 rows in set (0.00 sec) </pre> <p>Diese Tabelle füllen wir mit Hilfe eines kleinen gfind Aufrufs mit der lokalen mp3-Sammlung. Der folgende gfind Aufruf sammelt an den angegeben Orten die Dateien (%p) und deren Größen in bytes (%s) ein und gibt das ganze als einen Strom von mysql-Statements aus. Diese Ausgabe pipen wir direkt nach mysql.</p> <p><kbd>$ gfind /data/mp3 /data2/mp3 -type f -name "*.mp3" -printf "insert into mp3.files set id=0, filename=\"%p\", size=%s;\n" | mysql </kbd></p> </dd> <dt><b><a href="http://rabe.uugrn.org/scripts/update_db_mp3_files.sh">update_db_mp3_files.sh</a>: Updaten der Tabelle mit Hilfe von mp3info</b></dt> <dd> <p>Die Datenbank enthält nun zunächst nur alle Dateiinformationen aus dem Filesystem. mp3s bieten jedoch noch zusätzliche Informationen, die auch als ID3-Tags bekannt sind. Das sind Header-Informationen, die am Dateianfang stehen. Mit Hilfe eines geeigneten Tools, hier mp3info, können wir diese Information auslesen und Tabellarisch ausgeben und damit wiederum update-Statements erzeugen</p> <p>Das Script besteht in seiner Struktur aus 4 Teilen, die in einer Kette zusammenarbeiten:</p> <ol> <li>Auslesen von id und filename aus der Datenbank, formatieren als Pipe-separierte Liste</li> <li>1. Schleife: einlesen der Liste, für jede Datei md5-Summe berechnen und Headerinformationen mit mp3info auslesen, Weitergabe aller Werte weiderum als Pipe-separierte Liste.</li> <li>2. Schleife: einlesen der Liste mit allen Werten, erzeugen von update-Statements für MySQL, wobei id als der mitgeführte Primary Key verwendet wird, um die Werte den Datensätzen zuzuordnen. Ausgabe ist eine SQL-Datei.</li> <li>Einlesen der SQL-Datei mittels mysql.</li> </ol> <p>Der dritte und 4. Schritt müssen dabei über die temporäre Datei entkoppelt werden, da die update Statements in die gleiche Tabelle schreiben, wo schon Teil 1 seine Abfrage laufen hat. Beide Teile würden sich vermutlich irgendwann gegenseitig blockieren. Ein solcher Datensatz könnte so aussehen:</p> <pre> update mp3.files set md5="63e95b6792f40a3fd47614b3e5e398e3", bitrate="192", samplerate="44100", title="One of These Days" , artist="Pink Floyd" , album="Meddle" , comment="" , year="1971" , trackno="" , genre="17" where id="8699"; </pre> <p>Mit Hilfe dieser Tabelle lassen sich jetzt mit SQL sehr interessante Abfragen gestalten, zB alle Titel und Alben von Pink Floyd, die im Jahre 1971 erschienen sind:</p> </p> <pre> mysql> select id,artist,album,title from files where artist like "Pink Floyd" and year= 1971; +------+------------+-----------------------+-------------------------------+ | id | artist | album | title | +------+------------+-----------------------+-------------------------------+ | 8688 | Pink Floyd | Meddle | One of These Days | | 8689 | Pink Floyd | Meddle | A Pillow of Winds | | 8690 | Pink Floyd | Meddle | Fearless | | 8691 | Pink Floyd | Meddle | San Tropez | | 8692 | Pink Floyd | Meddle | Seamus | | 8693 | Pink Floyd | Meddle | Echoes | | 8866 | Pink Floyd | Meddle | One of these Days live | | 9042 | Pink Floyd | Live In Montreux 1971 | Set The Controls... | | 9044 | Pink Floyd | Live In Montreux 1971 | A Saucerful Of Secrets | | 9045 | Pink Floyd | Live In Montreux | Echoes | | 9046 | Pink Floyd | Live In Montreux | Careful with that Axe, Eugene | | 9047 | Pink Floyd | Live In Montreux 1971 | Atom Heart Mother | +------+------------+-----------------------+-------------------------------+ 12 rows in set (0.02 sec) </pre> <p>Oder auch rein technische Dinge lassen sich so herausfinden, zB identische Dateien, die unter Verschiedenen Namen vorhanden sind und/oder in verschiedenen Verzeichnissen liegen (also Doubletten). Dateien mit identischer md5-Summe setzen wir als inhaltsgleich voraus:</p> <pre> mysql> select a.id,b.id,a.md5,b.md5 from files a, files b where a.md5=b.md5 and a.id < b.id; +-------+-------+----------------------------------+----------------------------------+ | id | id | md5 | md5 | +-------+-------+----------------------------------+----------------------------------+ | 1144 | 21050 | 377149fa8b287299bfc5006fb6471867 | 377149fa8b287299bfc5006fb6471867 | | 1150 | 21056 | bee386da9c235d7719732940cac9f73f | bee386da9c235d7719732940cac9f73f | | 1198 | 1479 | af3933e1fade8d2de72e4c7ea7c30396 | af3933e1fade8d2de72e4c7ea7c30396 | | 1200 | 1481 | 5128e9e3ffb2f2a4c66ca6d20dccc36c | 5128e9e3ffb2f2a4c66ca6d20dccc36c | [...] | 22950 | 23030 | d7b9eec91716dbd7c697e69ddcece341 | d7b9eec91716dbd7c697e69ddcece341 | | 23682 | 23704 | 331ecfd1fe2467ea540515ea401a5574 | 331ecfd1fe2467ea540515ea401a5574 | | 24818 | 24819 | 6c97082ae74d318d915a8924177fa137 | 6c97082ae74d318d915a8924177fa137 | | 24849 | 24850 | 036c7cafb0b945151c4ac5215f014111 | 036c7cafb0b945151c4ac5215f014111 | +-------+-------+----------------------------------+----------------------------------+ 71 rows in set (1.26 sec) </pre> </dd> <dt></dt> <dd> <p>... oder Suchen nach Bestandteilen von Titeln:</p> <pre> mysql> select id,artist,album,title from files where title rlike 'zombie'; +-------+-----------------+------------------+-----------------+ | id | artist | album | title | +-------+-----------------+------------------+-----------------+ | 5122 | The Cranberries | No need to argue | Zombie | | 5136 | Cranberries | NULL | Zombie | | 10447 | The Hooters | Best Of | All You Zombies | | 15354 | Faith No More | The Real Thing | Zombie Eaters | +-------+-----------------+------------------+-----------------+ </pre> </dd> <dt><b><a href="http://rabe.uugrn.org/scripts/FreeBSD/port-bla">FreeBSD Ports</a></b></dt> <dd> <p>Ein etwas umfangreicheres System ist der Versuch, die FreeBSD-Ports in MySQL abzubilden. Ziel ist es, beispielsweise zu einem Port alle in Frage kommenden Download-URLs aufzulisten. Eine weitere Anwendung ist die Volltextsuche über alle pkg-descr Dateien. Das Projekt ist im Rahmen des Vortrages etwas zu umfangreich, der interessierte User kann Details in der <a href="http://rabe.uugrn.org/scripts/FreeBSD/port-bla/README">README</a> nachlesen. </p> </dd> <dt><b><a href="http://rabe.uugrn.org/scripts/"></a></b></dt> <dd> <p> </p> </dd> </dl> [<a href="http://rabe.uugrn.org/">home</a>] [<a href="http://rabe.uugrn.org/scripts/">scripts</a>] [<a href="index.html">Übersicht</a>] <hr> Raphael Becker <<a href="rabe@uugrn.org">rabe@uugrn.org</a>> </body> </html>