Medialib queries
From XMMS2
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:
- http://www.sqlite.org/
- http://www.sqlite.org/vdbe.html
- http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
This tool might be useful to optimize queries.
