Medialib queries

Erik Massop edited this page Nov 4, 2017 · 1 revision

Raw SQL Queries have been deprecated!!

Collections have been merged into xmms2 as of the DrJekyll release. This page will disappear not too long after Linux Distributions release DrJekyll into their repositories. Please see Collections usage with cli instead.

Here are some examples of some SQL queries that can be run on the XMMS2 medialib, shown here as performed directly on the database, via sqlite.

Select all genres:

 sqlite> SELECT value           FROM Media           WHERE key="genre"          GROUP BY LOWER (value) ORDER BY value;  Alternative  Blues  Classic Rock  Easy Listening  Electronic  ...

Select all artists (using the artists view):

 sqlite> SELECT *           FROM artists   Alien Vs. The Cat  Apoptygma Berzerk  Astrix  ...

Select all albums by an artist:

 sqlite> SELECT *           FROM albums           WHERE artist LIKE "S.P.O.C.K";  S.P.O.C.K|A Piece Of The Action (Disc 1 - The Singles)  S.P.O.C.K|A Piece Of The Action (Disc 2 - The rest)

Select five random songs:

 sqlite> SELECT *           FROM songs           ORDER BY RANDOM() LIMIT 5;  mesh              |fragmente          |08 - So Important (original ac |-1 |319  Kent (Sweden)     |Vapen & Ammunition |FF                             |8  |72  Air               |Moon Safari        |sexy boy [cassius remix]       |2  |165  Kultiration       |Om Gaia            |Babylon faller                 |-1 |60  Chemical Brothers |Digweed Kiss100    |Get You High (feat. K-Os)      |-1 |154

Select five random songs from the 'Rock' genre:

 sqlite> SELECT m1.id, m1.value, m2.value, m3.value           FROM Media m1, Media m2, Media m3           WHERE m1.key="genre" AND m1.value="Rock"            AND m2.id=m1.id AND m2.key="title"            AND m3.id=m1.id AND m3.key="artist"           ORDER BY RANDOM() LIMIT 5;   5 | Rock | Fix you          | Coldplay  84 | Rock | Rosor & Palmblad | Kent  14 | Rock | Til Kingdom come | Coldplay  80 | Rock | Järnspöken       | Kent  10 | Rock | Low              | Coldplay

(yeah, coldplay is not rock.. i'll update that tag asap ;)

Select five random albums:

 sqlite> SELECT *           FROM albums           ORDER BY RANDOM() LIMIT 5;  Allister Brimble      |Immortal  C-Mos                 |2 Million Ways (Axwell Remix)  Nathan G              |Promo Only UK Underground Beat  The chemical brothers |Believe  the knife             |pass this on CDM

An example of what you might find in the property table for a medialib entry:

 sqlite> SELECT *           FROM Media           WHERE id=252;  252| added       | 1119380208  252| album       | Med Solen I Ögonen  252| album_id    | 6688440a-58f8-4995-aec2-7444ef8c9100  252| artist      | Lars Winnerbäck  252| artist_id   | 1723c867-d5c5-4a61-a40b-8c04fa7acf1b  252| bitrate     | 256  252| duration    | 298000  252| laststarted | 1119382203  252| lmod        | 1092834093  252| mime        | application/ogg  252| resolved    | 1  252| title       | Pollenchock & Stjärnfall  252| track_id    | 5faafcbc-a286-4168-91a1-d14fa98b3fc1  252| tracknr     | 6  252| url         | file:///music/Lars Winnerbäck/Med Solen I Ögonen/06-Pollenchock & Stjärnfall.ogg

(Note that this data differs from entry to entry)

Select the name and size (number of songs) of all the playlists:

 sqlite> SELECT name, COUNT(entry) AS size          FROM Playlist          LEFT JOIN PlaylistEntries ON id=playlist_id          GROUP BY playlist_id;  name        size        ----------  ----------  emptylist   0           autosaved   3           test        2         

Add all the unrated songs to your playlist, written in a MUCH better way:

xmms2 mlib queryadd "select m1.id as id from Media m1 left join Media m2 on    m1.id = m2.id and m2.key='rating' where m1.key = 'url' and m2.value is null"

Sorry for the long line, but I put this in cron(SHELL=bash), should remove any poorely rated songs automagicaly. For testing only run this every 10min, also notice there is no locking so many instances can ruin your whole day.

*/10 * * * * xmms2 remove $(data=$(xmms2 mlib query "select id from Media where key='rating' and value = 1" | cut -f3 -d\ ); for ech in $(xmms2 list | head -n-2 | tr ']' '[' | cut -f2 -d'['); do echo "$data" | grep -q "^$(echo $ech | cut -f2 -d/)$" && { pos=$(echo $ech | cut -f1 -d/); echo $pos; }; done)

This worked better then the above, but depends on awk. Put this in a script called from bash, I guess you could turn it into a crontab. As you can see it also can remove duplicated unrated songs and will not call remove if there is nothing todo.

data=$( {  xmms2 mlib query "select id from Media where key='rating' and value = 1" # xmms2 mlib query "select m1.id as unrated from Media m1 left join #    Media m2 on m1.id = m2.id and m2.key='rating' where #    m1.key = 'url' and m2.value is null"  xmms2 list |    sed 's%^.* $$\([^/[:space:]]*\)/\([^][:space:]]*\)$$ .*$%list = \2 \1%'  } | awk '    /id = / { id[$3] = 1; }    /unrated = /    { unrated[$3] = 1; }    /list = /   {     if ($3 in list && $3 in unrated) print $4;     list[$3] = list[$3] " " $4;    }    END     { for (tmp in id) if (tmp in list) print list[tmp]; }  ' ) [ "$data" ] && xmms2 remove $data;

Select id, artist and title (from all songs, including those without title and/or artist):

Use this to "fully" search for id's that have a specific artist and a specific title.

SELECT   m.id,  artist.value AS vartist,  title.value AS vtitle  FROM Media AS m  LEFT JOIN Media AS arist ON(   artist.id = m.id AND   artist.key = "%%your-artist%%") LEFT JOIN Media AS title ON(   title.id = m.id AND   title.key = "%%your-title%%") WHERE m.key = "url";

"WHERE m.key = "url";" is added because of you want to JOIN on only 1 record per id and key = url is the only key which always exists. If you only want to see the the items with a arist, you can leave LEFT before "LEFT JOIN Media AS arist".

ToDo:

 Looking for a way to clean up the active playlist, something to put in cron that might run every 5 minuets and auto-remove any song with a rating of 0.  An insert/update so rating songs(ect) can be scripted.  A overview or map, listing table names, descriptions, and relations.

Related links:

This tool might be useful to optimize queries.

Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.