PDA

View Full Version : Hoping for help with SQL Playlist



RobbH
2018-11-30, 14:15
Running LMS 7.9.1 - 1522157629 on Debian 8 X86_64, and I have recently discovered SQL Playlist. I see some references in the forum saying that it does not work well with recent versions of LMS. So far it is working well for me, but I am struggling to get it to do some things.

I do not know SQL, nor am I familiar with the structure of the LMS database. I have general experience with relational databases and Boolean expressions. Following some basic online guides to SQL, I have tried to apply those concepts. So far, nothing has worked.

Specifically, I want to make use of multiple genre tags. For instance, I have many files with the genre tag, 'Jazz'. Some of those files also have a genre tag that says 'Holiday'. (There are many more files that have the 'Holiday' genre tag, along with some other genre, and a few that only have the 'Holiday' tag.)

My goal is to set up a playlist that only plays files that have both 'Jazz' and 'Holiday' tags. All attempts so far have failed miserably.

Here's the code generated by SQL Playlist for a playlist that plays only 'Jazz':


-- PlaylistName:Random temp
-- PlaylistGroups:
select tracks.url from tracks
join genre_track on
tracks.id=genre_track.track
join genres on
genre_track.genre=genres.id
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
where
audio=1
and dynamicplaylist_history.id is null
and genres.name in ('Jazz')
group by tracks.id
order by random()
limit 10;


So far, I have attempted to another line to the 'where' section, like so:


and (genres.name in ('Jazz')
and genres.name in ('Holiday'))

I have tried different groupings of parentheses, without success.

I also tried doubling the 'where' section:


(audio=1
and dynamicplaylist_history.id is null
and genres.name in ('Jazz')
and (audio=1
and dynamicplaylist_history.id is null
and genres.name in ('Holiday')))

Again, no luck with several variations on that format.

Is what I'm trying to do possible? If anyone is willing to share some hints about the right way to do it, I will be very grateful!

RobbH
2018-12-04, 13:33
Thanks to all who have read my post! With well over a hundred views and no responses, I suspect my question is just too obscure. Maybe nobody has tried to do this before.

But it has occurred to me that there might be other reasons for the lack of response:

1. Maybe I did not describe the problem well enough, so nobody understands what I'm trying to do. I will try to remedy that with a clearer description below. Or,

2. in the first post, I mentioned a genre tag that uses a medieval ecclesiastical abbreviation for Christmas. Some people find that abbreviation offensive. So it might be that I have offended someone, and of course they would feel reluctant to help me. I apologize for any offense I gave, and I have edited the first post to remove the possibly offensive word. Or,

3. somebody might have experience with this very issue, but suspect that I am unwilling to do my own work, and thus be reluctant to help me out. If so, I understand that position. I'll be grateful for any advice that helps to steer me in the right direction, and I do not necessarily expect anyone to hand me a fully worked-out solution. Or,

4. I might have doomed my chances by failing to specify that I am using SQLite 1.34_01 (sqlite 3.7.7.1).

With that out of the way, here's my second attempt to describe the situation:

In brief, it is possible to have multiple genre tags in a single file. And it's easy to select music on the basis of any single tag contained in the files. Is it possible (using SQL Playlist or any other software that works with LMS) to select music on the basis that the files must contain two (or more) specific genre tags?

Here's a more detailed description with examples:

Let's say that I have a thousand music files. Every one of those files has at least one of the following genre tags: Country, Jazz, or Rock. Each file also contains one of these genre tags: Instrumental or Vocal.

It's very easy to set up a dynamic playlist that will play all Jazz files, for instance, regardless of whether they are Vocal or Instrumental. It's also easy to play all Vocal files, regardless of whether they are Country, Jazz, or Rock.

But is it possible to play only files that have both Country and Instrumental tags? Using the predefined options in SQL playlist, it's easy to select both those tags, but only with an "OR" Boolean relationship: files played will either be Country (vocal or instrumental) or they will be Instrumental (country, jazz or rock).

Note that there is an easy workaround for this simple case, using the predefined options: just select to play Country files, then select to exclude all Vocal files. But that's as far as the predefined options can go.

What I want to do is more complex, selecting files on the basis of multiple options, like this:

(Country AND Instrumental) OR (Jazz AND Vocal) OR Rock

Does anyone know if this sort of selection is possible? Obviously, it can not be done with the predefined options in SQL Playlist. So it will have to be done by constructing an SQL query. I'm a rank beginner at that task, but I am hoping to learn. Any advice will be appreciated, even a gentle nudge in the right direction. And again, if I would be better off using something other than the SQL Playlist plugin, that would be very helpful to know.

Thanks to anyone who reads all the way through this!

dolodobendan
2018-12-04, 14:48
Thanks to all who have read my post! With well over a hundred views and no responses, I suspect my question is just too obscure. Maybe nobody has tried to do this before.

But it has occurred to me that there might be other reasons for the lack of response:

1. Maybe I did not describe the problem well enough, so nobody understands what I'm trying to do. I will try to remedy that with a clearer description below. Or,

2. in the first post, I mentioned a genre tag that uses a medieval ecclesiastical abbreviation for Christmas. Some people find that abbreviation offensive. So it might be that I have offended someone, and of course they would feel reluctant to help me. I apologize for any offense I gave, and I have edited the first post to remove the possibly offensive word. Or,

3. somebody might have experience with this very issue, but suspect that I am unwilling to do my own work, and thus be reluctant to help me out. If so, I understand that position. I'll be grateful for any advice that helps to steer me in the right direction, and I do not necessarily expect anyone to hand me a fully worked-out solution. Or,

4. I might have doomed my chances by failing to specify that I am using SQLite 1.34_01 (sqlite 3.7.7.1).

With that out of the way, here's my second attempt to describe the situation:

In brief, it is possible to have multiple genre tags in a single file. And it's easy to select music on the basis of any single tag contained in the files. Is it possible (using SQL Playlist or any other software that works with LMS) to select music on the basis that the files must contain two (or more) specific genre tags?

Here's a more detailed description with examples:

Let's say that I have a thousand music files. Every one of those files has at least one of the following genre tags: Country, Jazz, or Rock. Each file also contains one of these genre tags: Instrumental or Vocal.

It's very easy to set up a dynamic playlist that will play all Jazz files, for instance, regardless of whether they are Vocal or Instrumental. It's also easy to play all Vocal files, regardless of whether they are Country, Jazz, or Rock.

But is it possible to play only files that have both Country and Instrumental tags? Using the predefined options in SQL playlist, it's easy to select both those tags, but only with an "OR" Boolean relationship: files played will either be Country (vocal or instrumental) or they will be Instrumental (country, jazz or rock).

Note that there is an easy workaround for this simple case, using the predefined options: just select to play Country files, then select to exclude all Vocal files. But that's as far as the predefined options can go.

What I want to do is more complex, selecting files on the basis of multiple options, like this:

(Country AND Instrumental) OR (Jazz AND Vocal) OR Rock

Does anyone know if this sort of selection is possible? Obviously, it can not be done with the predefined options in SQL Playlist. So it will have to be done by constructing an SQL query. I'm a rank beginner at that task, but I am hoping to learn. Any advice will be appreciated, even a gentle nudge in the right direction. And again, if I would be better off using something other than the SQL Playlist plugin, that would be very helpful to know.

Thanks to anyone who reads all the way through this!

Don't overthink it.

While I do follow a different approach (Custom Scan, Custom Browse & Multi Library), I think what you're trying to achieve might be possible with the "Sub-Library Demo" Plugin and some tinkering (https://forums.slimdevices.com/showthread.php?103805-How-i-get-the-LibraryDemo-plugin-to-work).

RobbH
2018-12-04, 15:23
Don't overthink it.

While I do follow a different approach (Custom Scan, Custom Browse & Multi Library), I think what you're trying to achieve might be possible with the "Sub-Library Demo" Plugin and some tinkering (https://forums.slimdevices.com/showthread.php?103805-How-i-get-the-LibraryDemo-plugin-to-work).

Many thanks! I will investigate that.

DJanGo
2018-12-05, 14:25
Does anyone know if this sort of selection is possible?
Thats a really hard one...
Because of the DB schema you cant (IMHO) search for a track with two genres...
In (my) Theorie you need a temp. table filled with Country and that temp. Table must be checked for a match Genre like Instrumental to get to your selection.

btw. i am NOT using SQL Playlist thats why i didnt answered in the first place

RobbH
2018-12-06, 13:23
Thats a really hard one...
Because of the DB schema you cant (IMHO) search for a track with two genres...
In (my) Theorie you need a temp. table filled with Country and that temp. Table must be checked for a match Genre like Instrumental to get to your selection.

btw. i am NOT using SQL Playlist thats why i didnt answered in the first place

Thanks for replying this time! In addition to all the other problems I mentioned, my first post sounded like I was only interested in solutions that use SQL Playlist.

If I understand what you're saying, and I may not, doing what I want with SQL would require at least two steps: first, select for files where genre="Country", then select from files selected the first time for files where genre="Instrumental". I think I've seem some examples of nested SQL queries. Might it be possible to do what I want that way? In other words, instead of storing the results of the first query in a temporary table to be used by the second query, a single query would first select for Country, then a subquery would operate on that selection to find Instrumental files.

(Remember, I am very much a novice at SQL, so there's much I do not understand and my concepts are shaky. I will not be terribly shocked if that turns out not to be possible.)



Originally Posted by dolodobendan View Post
Don't overthink it.

While I do follow a different approach (Custom Scan, Custom Browse & Multi Library), I think what you're trying to achieve might be possible with the "Sub-Library Demo" Plugin and some tinkering.


Sorry, overthinking is what I do! Except when I blunder headlong into something without thinking, I mean.

I hadn't considered virtual libraries as a way to do this, but what I see so far looks very encouraging. If I understand at all, I could create a virtual library containing all the Vocal tracks, and another with all the Instrumental tracks. Then it would be straightforward to select for genre within those libraries, to create a playlist with, say, all Country AND Instrumental tracks.

Just to complicate matters, would it also be possible to combine tracks from different virtual libraries in a single playlist? As with the example in my second post, could I build a playlist containing all Country OR Rock tracks from the Instrumental library, along with all Jazz OR Rock tracks from the Vocal library?

Thanks for the input so far, which has given me a lot to work with. I do not necessarily expect answer to the questions I've asked in this post, as I'll probably be able to find the answers on my own, eventually. (But clarification is always welcome, if either of you -- or anyone else -- is willing to provide it!) I think there's plenty of discussion of virtual libraries for me to dig through. If I go that route, I'll try to find my answers there and only return to this thread to beg for more help when I feel hopelessly stuck.

RobbH
2018-12-06, 13:31
Don't overthink it.

While I do follow a different approach (Custom Scan, Custom Browse & Multi Library), I think what you're trying to achieve might be possible with the "Sub-Library Demo" Plugin and some tinkering (https://forums.slimdevices.com/showthread.php?103805-How-i-get-the-LibraryDemo-plugin-to-work).

One more question for you! I do not understand the advantage of using the Library Demo plugin, instead of the plugins you use. Would it make sense to consider using those three plugins?

DJanGo
2018-12-06, 13:47
One more question for you! I do not understand the advantage of using the Library Demo plugin, instead of the plugins you use. Would it make sense to consider using those three plugins?

In Theorie you build a library only with Tracks that have eg. Country. Whenever you now search for eg. Instrumental you're shure its country AND Instrumental.
But you didnt need the plugin, with advanced search you can build your own virt. library by mouse klicking...

\edit
NOT only in Theorie - its a little bit tricky but works
/edit

dolodobendan
2018-12-06, 15:07
One more question for you! I do not understand the advantage of using the Library Demo plugin, instead of the plugins you use. Would it make sense to consider using those three plugins?

That depends.

My approach lets me:

+ use my own tags that would not be scanned otherwise (like WORK, RECORDING etc.)
+ use mixed tags like "Classical;Piano Concertos"
+ create a library "Classical" that contains all classical genres
+ create my own browse menus (for example Classical - Genre - Composer - Work - Recording).

- It doesn't use LMS' db, it needs a second scan (Custom Scan) and the custom menus are a bit slower than the regular ones.
- The custom scan needs more RAM. (If you have more than 2GB you should be fine.)
- Multi Library sometimes (rescan, sometimes after a time out) assigns new library IDs thus rendering the menus (relying on those IDs) empty. If this happens, the cache has to be wiped (maybe there's a simpler solution), the DB needs to be rescanned and the libraries have to be enabled one by one.

In my opinion, the possibility to really browse through my library is worth it. I can see which recording I have from this work, which works from that composer etc.

In your case, you could create a menu named Jazz. In there would be - if tagged correctly - three entries: instrumental, jazz and vocal. Instrumental would contain files Jazz;Instrumental tags, vocal would have the Jazz;Vocal files. Jazz contains both. or you could create a menu named Instrumental and let it list all Genres that also contain the Instrumental tag.

But that might be overkill.

dolodobendan
2018-12-06, 15:17
In Theorie you build a library only with Tracks that have eg. Country. Whenever you now search for eg. Instrumental you're shure its country AND Instrumental.
But you didnt need the plugin, with advanced search you can build your own virt. library by mouse klicking...

\edit
NOT only in Theorie - its a little bit tricky but works
/edit

But it's not possible to build something like "show me all classical genres" 1), *pick one*, "now show me all composers for that genre", *pick one*, "now show me all works from that composer matching the genre" 2), *pick one*, "now show me all available recordings" 2) with these virtual libraries, is it?


1) Mixed tags, like classical;piano concertos
2) Here WORK and RECORDINGS would have to be somehow included in the scan.

In other words, just applying filter after filter, but menu style. (This is what I do with Custom Browse, Custom Scan and Multi Library.)

DJanGo
2018-12-07, 00:38
mea culpa,

i am rusty and old and i am not using Earlands plugin - just sqlite.

How to find a match between FUNK and Dance?


sqlite> select id from genres where name like 'Funk' or name like 'Dance';
18
28


In this case Funk is 18 and Dance is 28
Lets build some temp Tables:


sqlite> CREATE TEMPORARY TABLE DANCE AS select id,url from genre_track, tracks where genre_track.track = tracks.id and genre_track.genre like '18';
sqlite> CREATE TEMPORARY TABLE FUNK AS select id,url from genre_track, tracks where genre_track.track = tracks.id and genre_track.genre like 28;


Now find the matching numbers


sqlite> select FUNK.url from FUNK INNER JOIN DANCE on DANCE.id = FUNK.id;
file:///media/music/W/Womack%20&%20Womack/Teardrops/Womack%20&%20Womack%20-%20Teardrops%20-%2001%20-%20Teardrops%20(Extended%20Remix).mp3|55557|55557
file:///media/music/W/Womack%20&%20Womack/Teardrops/Womack%20&%20Womack%20-%20Teardrops%20-%2002%20-%20Teardrops.mp3|55558|55558
sqlite>


open Trashcan


sqlite> drop table DANCE;
sqlite> drop table FUNK;


Now you have to fiddle out how to manage that with earlands plugin and i am pretty sure there is enough place for improvement.

DJanGo
2018-12-07, 01:05
@dolo:
i am old & rusty.
please open another thread (in the german section?)

https://www.youtube.com/watch?v=YP5fkvNCeXw

bradcook
2018-12-07, 04:41
I dont have the SQL schema to hand - but how are the genres stored for each track ?

If I was writing such a query where I needed to check multiple items I would join the table to itself on its primary key

So something like

select item from track1
join track2
on track1.key = track2.key
and track2.genre in 'Holiday'
where track1.genre in 'Jazz'

or something along those lines

DJanGo
2018-12-07, 06:18
I dont have the SQL schema to hand - but how are the genres stored for each track ?
like this:


.schema genre_track
CREATE TABLE genre_track (
genre int(10),
track int(10),
PRIMARY KEY (genre,track),
FOREIGN KEY (`track`) REFERENCES `tracks` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`genre`) REFERENCES `genres` (`id`) ON DELETE CASCADE
);
CREATE INDEX genre_trackGenreIndex ON genre_track (genre);
CREATE INDEX genre_trackTrackIndex ON genre_track (track);

and its:


select * from genre_track limit 1;
8|24

where 8 is the genre and 24 the track

RobbH
2018-12-07, 15:46
In Theorie you build a library only with Tracks that have eg. Country. Whenever you now search for eg. Instrumental you're shure its country AND Instrumental.
But you didnt need the plugin, with advanced search you can build your own virt. library by mouse klicking...

\edit
NOT only in Theorie - its a little bit tricky but works
/edit

I had completely overlooked the capabilities of Library Views. I was vaguely aware that the feature offered the ability to restrict access to parts of the library for some users. It hadn't occurred to me that this is exactly what I want to do: temporarily restrict MY OWN access to parts of the library.

I see now that it is possible to do something very much like what I have in mind, just using library views. It's not just tricky; it's convoluted. But it works.

Using my earlier examples, I could do an advanced search for all Country tracks, create a library view from that, switch to that view, then do an advanced search in that view for all Instrumental tracks, the save that search as a Country Instrumental library view. Then I could do the same for Jazz --> Vocal. And create a Rock view from the full library.

At that point, I've got three library views that, together, contain all the tracks I want to use in building my Random Mix. If there were a way to merge library views, this would be perfect. But there is no way to do that, is there?

Instead, what I've done is create Random Mix from each of those library views, then save each mix as a playlist, then finally load those three playlists together and select shuffle mode.

It's not exactly a direct path to where I want to go, but it gets me there, eventually. For now, I can use this method, but I till want to learn to use SQL. I can see that I shouldn't expect to be proficient with it anytime soon, though.

This method also provides a feature I haven't asked about yet: weighted random playlists. By varying the size of the playlists made from different library views, I can control the percentage of the final shuffled playlist that will come from each of the library views. Unfortunately, Random Mix seems not to create playlists longer than 100 tracks. I do not know yet if the same limitation applies to SQL Playlist, but I have found that it will only add ten tracks at a time. These are minor frustrations, but I'm happy to have found something that works. Thanks for leading me to that solution!

RobbH
2018-12-07, 16:19
That depends.

My approach lets me:

+ use my own tags that would not be scanned otherwise (like WORK, RECORDING etc.)
+ use mixed tags like "Classical;Piano Concertos"
+ create a library "Classical" that contains all classical genres
+ create my own browse menus (for example Classical - Genre - Composer - Work - Recording).

- It doesn't use LMS' db, it needs a second scan (Custom Scan) and the custom menus are a bit slower than the regular ones.
- The custom scan needs more RAM. (If you have more than 2GB you should be fine.)
- Multi Library sometimes (rescan, sometimes after a time out) assigns new library IDs thus rendering the menus (relying on those IDs) empty. If this happens, the cache has to be wiped (maybe there's a simpler solution), the DB needs to be rescanned and the libraries have to be enabled one by one.

In my opinion, the possibility to really browse through my library is worth it. I can see which recording I have from this work, which works from that composer etc.

In your case, you could create a menu named Jazz. In there would be - if tagged correctly - three entries: instrumental, jazz and vocal. Instrumental would contain files Jazz;Instrumental tags, vocal would have the Jazz;Vocal files. Jazz contains both. or you could create a menu named Instrumental and let it list all Genres that also contain the Instrumental tag.

But that might be overkill.

Yes, it might be overkill, compared to what I was hoping to achieve at the start of this thread. But it addresses some other issues, especially the frustration of trying to use the conventional tag format to accommodate classical music. I am interested, and plan to look into these options further.

DJanGo
2018-12-08, 05:59
Unfortunately, Random Mix seems not to create playlists longer than 100 tracks.
Take a look at http://lms.ip:9000/settings/server/performance.html?
and change 100 to whatever

And as dolodobendan stated:
you can also use non std. genres like F&D for Funk and Dance.
Thats the way i am using as well.

That using a special genre is really easy maybe the easiest way for your current library.

If you using mp3 not flacs and since your running a debianish LMS its pretty easy -> ok getting eyeD3 to work is a bit tricky.

RobbH
2018-12-08, 19:09
Take a look at http://lms.ip:9000/settings/server/performance.html?
and change 100 to whatever

And as dolodobendan stated:
you can also use non std. genres like F&D for Funk and Dance.
Thats the way i am using as well.

That using a special genre is really easy maybe the easiest way for your current library.

If you using mp3 not flacs and since your running a debianish LMS its pretty easy -> ok getting eyeD3 to work is a bit tricky.

Thanks again! I had forgotten about that Maximum Playlist Length setting (if I ever knew about it). It was set to 500, so I disabled it by setting it to zero, as my work-around requires playlists larger than that.

Unfortunately, it's the Random Mix plugin itself that appears to limit the playlists it generates to 100 entries. Settings for that plugin will not save a value larger than 100.

No matter! It's working, after a fashion, with the SQL Playlist plugin, so I'm happy for now. I'll work on learning a more elegant and efficient way to do it,

That's probably not going to be adding combined genre tags. Nothing wrong with that idea, but I have tried to limit the number of genre tags I use. But if I change my mind about that, I will tackle eyeD3. It looks powerful and complicated!

Speaking of complicated, if I attempt to conquer SQL, I will also need to familiarize myself with the database schema. Where can I find that documented? This page (http://wiki.slimdevices.com/index.php/SlimServerDatabaseStructure) seems not have been updated since 2010, so my guess is that it's outdated.

erland
2018-12-08, 23:11
My goal is to set up a playlist that only plays files that have both 'Jazz' and 'Holiday' tags. All attempts so far have failed miserably.

You can do it with exists directive and an inner select, something like this:


-- PlaylistName:Random temp
-- PlaylistGroups:
select tracks.url from tracks
join genre_track on
tracks.id=genre_track.track
join genres on
genre_track.genre=genres.id
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
where
audio=1
and dynamicplaylist_history.id is null
and genres.name in ('Jazz')
and exists (select track from genre_track
join genres on
genre_track.genre=genres.id
where
genres.name in ('Holiday')
and genre_track.track = tracks.id)
group by tracks.id
order by random()
limit 10;


Please note that inner selects could be slow in large libraries, in that case there is might also a possibility to do it through an extra set of joins.

erland
2018-12-08, 23:29
Speaking of complicated, if I attempt to conquer SQL, I will also need to familiarize myself with the database schema. Where can I find that documented? This page (http://wiki.slimdevices.com/index.php/SlimServerDatabaseStructure) seems not have been updated since 2010, so my guess is that it's outdated.


I think the information mentioned on the page you refer to are still accurate, at least that's what all my plugins (Custom Scan, Multi Library, Custom Browse, SQL Playlist, TrackStat, Database Query, Dynamic Playlist) are based on.
Possibly LMS 7.9 might have added some extra tables/columns that aren't used by my plugins.

RobbH
2018-12-10, 11:08
You can do it with exists directive and an inner select, something like this:


-- PlaylistName:Random temp
-- PlaylistGroups:
select tracks.url from tracks
join genre_track on
tracks.id=genre_track.track
join genres on
genre_track.genre=genres.id
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
where
audio=1
and dynamicplaylist_history.id is null
and genres.name in ('Jazz')
and exists (select track from genre_track
join genres on
genre_track.genre=genres.id
where
genres.name in ('Holiday')
and genre_track.track = tracks.id)
group by tracks.id
order by random()
limit 10;


Please note that inner selects could be slow in large libraries, in that case there is might also a possibility to do it through an extra set of joins.

Many, many thanks for this! I probably don't know enough to make use of your suggestion yet, but I'll certainly see if my heavy-handed cut and paste efforts can make it work. But know that it's possible should be the inspiration I need to learn how to make it work. Something like this was what I was thinking of when I referred to "nested queries".

RobbH
2018-12-10, 11:11
I think the information mentioned on the page you refer to are still accurate, at least that's what all my plugins (Custom Scan, Multi Library, Custom Browse, SQL Playlist, TrackStat, Database Query, Dynamic Playlist) are based on.
Possibly LMS 7.9 might have added some extra tables/columns that aren't used by my plugins.

Thanks for that, too. I thought I had seen some references to database incompatibilities in recent versions. Good to know that what's on that page is still accurate, even if it leaves out some new features.

dolodobendan
2018-12-18, 09:17
@dolo:
i am old & rusty.
please open another thread (in the german section?)

https://www.youtube.com/watch?v=YP5fkvNCeXw

Sind wir das nicht alle? :D

➤➤➤ (https://forums.slimdevices.com/showthread.php?109913-Virtual-Libraries-Views-Was-ist-m%F6glich)