Home of the Squeezebox™ & Transporter® network music players.
Page 1 of 3 123 LastLast
Results 1 to 10 of 25
  1. #1
    Senior Member
    Join Date
    Jul 2010
    Location
    Oz
    Posts
    405

    Dynamic Playlists 3 - get help with your custom playlists

    This thread is dedicated exclusively to helping users who want to create their own (custom) dynamic playlists with their SQLite statements.
    Of course, you can also post your working custom dynamic playlists here for others to use or as a source of inspiration. So nobody has to reinvent to wheel.


    Before posting I recommend you take a look at the dynamic playlists (incl. context menu playlists) that come with the plugin. They are a good starting point and may help you understand how SQLite works.

    And please read the wiki on the DPL playlist format and the FAQ.


    Once you have a working SQLite statement, all you need to do is
    • open a plain text editor of your choice (e.g. Notepad on Windows or BBEdit on macOS)
    • paste the dynamic playlist definition (incl. your SQLite statement)
    • save the file with a sql.xml file extension (instead of txt). Example: mycustomplaylistname.sql.xml
    • move this file to the folder for custom playlists called "DPL-custom-lists" in the LMS playlists folder (unless you changed its location).


    Now your dynamic playlist should be listed in the playlist group you chose/created or in the Not classified group if it doesn't belong to any group.
    Very easy.

    So the challenging part is figuring out the SQLite statement. And that's what this thread is for.

    I can't promise that I'll have time to answer your requests. Fortunately, there are other users who I know will help you as well.
    Last edited by AF1; 2021-12-02 at 12:20.

  2. #2
    Glad to see this new thread as I am one of those folks who can't code to save my life. My holy grail is a context menu that uses an existing static playlist and allows me to choose a subset of that playlist that chooses multiple genres and/or a year. If anyone wants to tackle that, I would be grateful.

  3. #3
    Senior Member
    Join Date
    Jul 2010
    Location
    Oz
    Posts
    405
    Quote Originally Posted by nyindieguy View Post
    context menu that uses an existing static playlist and allows me to choose a subset of that playlist that chooses multiple genres and/or a year.
    With year you'll have to choose a year. Not optional.

    Code:
    -- PlaylistName:Songs - multiple genres & year   (from selected playlist)
    -- PlaylistGroups:
    -- PlaylistMenuListType:contextmenu
    -- PlaylistParameter1:playlist:Select playlist:
    -- PlaylistParameter2:multiplegenres:Select genres:
    -- PlaylistParameter3:year:Select year:
    select distinct playlist_track.track from playlist_track
    	join tracks on
    		tracks.url = playlist_track.track and playlist_track.playlist='PlaylistParameter1'
    	join genre_track on
    		genre_track.track = tracks.id and genre_track.genre in ('PlaylistParameter2')
    	left join dynamicplaylist_history on
    		dynamicplaylist_history.id=tracks.id and dynamicplaylist_history.client='PlaylistPlayer'
    	where
    		tracks.year='PlaylistParameter3'
    		and tracks.audio = 1
    		and tracks.secs >= 'PlaylistTrackMinDuration'
    		and dynamicplaylist_history.id is null
    	group by playlist_track.track
    	order by random()
    	limit 'PlaylistLimit';
    With decades it's optional (select any).

    Code:
    -- PlaylistName:Songs - multiple genres & decade   (from selected playlist)
    -- PlaylistGroups:
    -- PlaylistMenuListType:contextmenu
    -- PlaylistParameter1:playlist:Select playlist:
    -- PlaylistParameter2:multiplegenres:Select genres:
    -- PlaylistParameter3:customdecadechained:Select decade:select cast(((ifnull(tracks.year,0)/10)*10) as int),case when tracks.year>0 then cast(((tracks.year/10)*10) as int)||'s' else 'Unknown' end from tracks where tracks.audio=1 group by cast(((ifnull(tracks.year,0)/10)*10) as int) order by tracks.year desc
    select distinct playlist_track.track from playlist_track
    	join tracks on
    		tracks.url = playlist_track.track and playlist_track.playlist='PlaylistParameter1'
    	join genre_track on
    		genre_track.track = tracks.id and genre_track.genre in ('PlaylistParameter2')
    	left join dynamicplaylist_history on
    		dynamicplaylist_history.id=tracks.id and dynamicplaylist_history.client='PlaylistPlayer'
    	where
    		tracks.audio = 1
    		and tracks.secs >= 'PlaylistTrackMinDuration'
    		and dynamicplaylist_history.id is null
    		and
    			case
    				when 'PlaylistParameter3'!=999999999 then tracks.year>='PlaylistParameter3' and tracks.year<('PlaylistParameter3'+10)
    				else 1
    			end
    	group by playlist_track.track
    	order by random()
    	limit 'PlaylistLimit';
    And without year/decade:

    Code:
    -- PlaylistName:Songs - multiple genres   (from selected playlist)
    -- PlaylistGroups:
    -- PlaylistMenuListType:contextmenu
    -- PlaylistParameter1:playlist:Select playlist:
    -- PlaylistParameter2:multiplegenres:Select genres:
    select distinct playlist_track.track from playlist_track
    	join tracks on
    		tracks.url = playlist_track.track and playlist_track.playlist='PlaylistParameter1'
    	join genre_track on
    		genre_track.track = tracks.id and genre_track.genre in ('PlaylistParameter2')
    	left join dynamicplaylist_history on
    		dynamicplaylist_history.id=tracks.id and dynamicplaylist_history.client='PlaylistPlayer'
    	where
    		tracks.audio = 1
    		and tracks.secs >= 'PlaylistTrackMinDuration'
    		and dynamicplaylist_history.id is null
    	group by playlist_track.track
    	order by random()
    	limit 'PlaylistLimit';
    Last edited by AF1; 2021-12-05 at 10:50.

  4. #4
    You make all this seem easy!
    Last edited by nyindieguy; 2021-12-05 at 15:16.

  5. #5
    I tried these out and it appears that there is either a flaw in the code or a bug in the plugin.

    With the "multiple genres/select decade" playlist, it selected only 9 songs in spite of there being hundreds that would have qualified. All of the 9 songs were from 1960, in spite of having selected that entire decade of the '60s. When I tried a second time, the exact same 8 songs came up.

    With the "multiple genres/select year" playlist, the songs seemed like they were correct, though not as shuffled as I might have wanted, but ended after several hours of play, when there should have been enough songs to last all day. The big tipoff was no Beatles included when Rock and Roll was one of the selected genres and 1967 was the selected year.

    Any thoughts?
    Last edited by nyindieguy; 2021-12-05 at 15:16.

  6. #6
    Senior Member
    Join Date
    Jul 2010
    Location
    Oz
    Posts
    405
    Quote Originally Posted by nyindieguy View Post
    With the "multiple genres/select decade" playlist, it selected only 9 songs in spite of there being hundreds that would have qualified. All of the 9 songs were from 1960, in spite of having selected that entire decade of the '60s. When I tried a second time, the exact same 8 songs came up.
    There was a line I forgot to delete. Fixed.

    Quote Originally Posted by nyindieguy View Post
    With the "multiple genres/select year" playlist, the songs seemed like they were correct, though not as shuffled as I might have wanted, but ended after several hours of play, when there should have been enough songs to last all day. The big tipoff was no Beatles included when Rock and Roll was one of the selected genres and 1967 was the selected year.
    I recreated this as best I could. A static playlist with about 1500 tracks incl. all Beatles tracks I could find.
    Then I called the DPL context playlist from above (with year=1967 and the genres of the tracks in the static playlist) - and no problem, 1967, Beatles and all. Reasonably shuffled as well - though if the source is a playlist with albums (compared to your entire library) I would expect groups of tracks from the same album to show up.

    P.S. Your posts are easier to read if you don't quote/repost the entire playlist code :-)

  7. #7
    Quote Originally Posted by afriend View Post
    There was a line I forgot to delete. Fixed.



    I recreated this as best I could. A static playlist with about 1500 tracks incl. all Beatles tracks I could find.
    Then I called the DPL context playlist from above (with year=1967 and the genres of the tracks in the static playlist) - and no problem, 1967, Beatles and all. Reasonably shuffled as well - though if the source is a playlist with albums (compared to your entire library) I would expect groups of tracks from the same album to show up.

    P.S. Your posts are easier to read if you don't quote/repost the entire playlist code :-)
    Sorry about all that quoting. I've removed them so no one gets confused about your revised code. And thanks again. I'll try it out.

  8. #8
    Senior Member
    Join Date
    Nov 2010
    Location
    Hertfordshire, UK
    Posts
    3,280
    With apologies for being a bit slow. I have a long standing LMS system, buit I don't quite get what the new DPL vs old DPL issues are.

    I've read the FAQ and everything else I could find, but I don't really understand what impact the new version will have on the dynamic play lists I already have. These make extensive use of the database entries created by custom scan and multilibrary. My use of LMS depends almost completely on those database tables. I play almost exclusively classical music, and 'works', so I have to use custom scan and browse, and multilibrary. I did look at the Virtual Libraries but I failed to understand how I could get it to work in a similar way to multilibrary. I have several dozen queries that depend on the multilibrary database.

    I don't understand what is meant by 'removes ties to other (unsupported) plugins'. I see that it says that it may work with such plugins, but I'm not sure what it means by 'ties'.

    As an example dynamic sql (I have about 20), this one selects random classical works (a series of movements/tracks) where one movement has a top 'rating' of 90 or more. Will the new DPL at least initially still work with this? Will it only fail if multilibrary or customscan fail to create the database tables, or is there some deeper 'tie' that could cause problems?

    -- PlaylistName:Random Classical Top Rated Works
    -- PlaylistGroups:Random
    -- PlaylistOption Unlimited:1
    create temporary table sqlplaylist_random_work as
    select customscan_track_attributes.value as work, tracks.album as album from customscan_track_attributes
    join tracks on
    customscan_track_attributes.track = tracks.id
    left join dynamicplaylist_history on
    tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
    join track_statistics on
    tracks.url=track_statistics.url
    and track_statistics.rating > 89
    where
    customscan_track_attributes.module='customtag' and
    customscan_track_attributes.attr='WORK' and
    dynamicplaylist_history.id is null and
    audio=1
    group by customscan_track_attributes.value, tracks.album
    order by random()
    limit 10;

    select tracks.url from tracks
    join customscan_track_attributes on tracks.id = customscan_track_attributes.track
    join sqlplaylist_random_work on tracks.album = sqlplaylist_random_work.album
    where sqlplaylist_random_work.work = customscan_track_attributes.value and
    customscan_track_attributes.module='customtag' and
    customscan_track_attributes.attr='WORK' and
    audio=1
    group by tracks.id
    order by tracks.album,tracks.disc,tracks.tracknum;
    drop table sqlplaylist_random_work;
    LMS 8.1 on PC, Xubuntu 20.04, FLACs 16->24 bit, 44.1->192kbps. 2 Touches & EDO.
    LMS plugin UPnP/DLNA Bridge to MF M1 CLiC (A308CR amp & ESLs) & Marantz CR603 UPnP renderers.
    Also Minimserver & Upplay to same & to upmpdcli/mpd PC renderers.
    Squeezelite to Meridian USB Explorer DAC to PC speakers/headphones.
    Wireless Xubuntu 20.04 laptop firefox/upplay or Android mobile with Squeeze-Ctrl/BubbleUPnP controls LMS/Minimserver.

  9. #9
    Senior Member
    Join Date
    Jul 2010
    Location
    Oz
    Posts
    405
    Quote Originally Posted by PasTim View Post
    I don't understand what is meant by 'removes ties to other (unsupported) plugins'. I see that it says that it may work with such plugins, but I'm not sure what it means by 'ties'.
    I removed code in DPL3 that was specifically aimed at unsupported plugins - for all the reasons and with all the consquences (e.g. no support) explained in the FAQ.
    But as long as your SQLite code is valid and the tables you need exists/work there's a good chance it might work with DPL3. DPL3 and DPL2 have different names and preference files so you could temporarily uninstall DPL2 and install DPL3 to test it. Just make sure you read the FAQ parts on SQLPlayList too. In your example above -- PlaylistOption Unlimited:1 won't have any effect with DPL3. It's -- PlaylistLimitOption:unlimited (see DPL3 wiki on DPL playlist format).


    Quote Originally Posted by PasTim View Post
    I did look at the Virtual Libraries but I failed to understand how I could get it to work in a similar way to multilibrary.
    Just out of curiosity: Can you post the code of some of your dynamic playlists (preferably in CODE tags) that involve the MultiLibrary plugin? If there's a chance to replace this plugin with native virtual libraries you'd have one less plugin to worry about and make your setup a bit more 'future-proof'.

  10. #10
    Senior Member
    Join Date
    Nov 2010
    Location
    Hertfordshire, UK
    Posts
    3,280
    Quote Originally Posted by afriend View Post
    I removed code in DPL3 that was specifically aimed at unsupported plugins - for all the reasons and with all the consquences (e.g. no support) explained in the FAQ.
    But as long as your SQLite code is valid and the tables you need exists/work there's a good chance it might work with DPL3. DPL3 and DPL2 have different names and preference files so you could temporarily uninstall DPL2 and install DPL3 to test it. Just make sure you read the FAQ parts on SQLPlayList too. In your example above -- PlaylistOption Unlimited:1 won't have any effect with DPL3. It's -- PlaylistLimitOption:unlimited (see DPL3 wiki on DPL playlist format).
    I'm struggling with the complexity of the FAQs. There are so many features I've never heard of, and some terms that evade me, but I'll try. It may prove to be too hard (i'm not as young as I once was).

    Just out of curiosity: Can you post the code of some of your dynamic playlists (preferably in CODE tags) that involve the MultiLibrary plugin? If there's a chance to replace this plugin with native virtual libraries you'd have one less plugin to worry about and make your setup a bit more 'future-proof'.
    I'd forgotten that the playlist I quoted didn't access the multilibrary table (it didn't need to because I only use WORK on classical tracks). Here's one that does.
    Code:
    -- PlaylistName:Random Non-Classical Rated
    -- PlaylistGroups:Random
    select url from
    	(select tracks.url from tracks
    		join multilibrary_track on
    			tracks.id=multilibrary_track.track and
    			multilibrary_track.library=1
    		join track_statistics on
    			tracks.url=track_statistics.url
    			and track_statistics.rating > 79
    		left join dynamicplaylist_history on
    			tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
    		where
    			audio=1
    			and dynamicplaylist_history.id is null
    		group by tracks.id
    		order by random()
    		
    		limit 20) as rated
    	order by random();
    LMS 8.1 on PC, Xubuntu 20.04, FLACs 16->24 bit, 44.1->192kbps. 2 Touches & EDO.
    LMS plugin UPnP/DLNA Bridge to MF M1 CLiC (A308CR amp & ESLs) & Marantz CR603 UPnP renderers.
    Also Minimserver & Upplay to same & to upmpdcli/mpd PC renderers.
    Squeezelite to Meridian USB Explorer DAC to PC speakers/headphones.
    Wireless Xubuntu 20.04 laptop firefox/upplay or Android mobile with Squeeze-Ctrl/BubbleUPnP controls LMS/Minimserver.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •