Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
DJanGo wrote: > @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) QLMS 7.9.2@2.02.03 x64 (digimaster) / QNAP 469L QTS 4.3.4 dolodobendan's Profile: http://forums.slimdevices.com/member.php?userid=67663 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
erland wrote: > 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. RobbH's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
erland wrote: > You can do it with exists directive and an inner select, something like > this: > > > Code: > > -- 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's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
RobbH wrote: > 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. Erland Isaksson ('My homepage' (http://erland.isaksson.info)) LEAD PLATFORM DEVELOPER OF 'ICKSTREAM MUSIC PLATFORM' (HTTP://WWW.ICKSTREAM.COM) - A WORLD OF MUSIC AT YOUR FINGERTIPS (Also developer of 'many plugins/applets' (http://wiki.slimdevices.com/index.php/User:Erland)) erland's Profile: http://forums.slimdevices.com/member.php?userid=3124 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
RobbH wrote: > 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: Code: -- 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 Isaksson ('My homepage' (http://erland.isaksson.info)) LEAD PLATFORM DEVELOPER OF 'ICKSTREAM MUSIC PLATFORM' (HTTP://WWW.ICKSTREAM.COM) - A WORLD OF MUSIC AT YOUR FINGERTIPS (Also developer of 'many plugins/applets' (http://wiki.slimdevices.com/index.php/User:Erland)) erland's Profile: http://forums.slimdevices.com/member.php?userid=3124 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
DJanGo wrote: > 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 *F*unk and *D*ance. > 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. RobbH's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
RobbH wrote: > 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 *F*unk and *D*ance. 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. DJanGo's Profile: http://forums.slimdevices.com/member.php?userid=1516 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
dolodobendan wrote: > 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. RobbH's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
DJanGo wrote: > 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's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
bradcook wrote: > 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); DJanGo's Profile: http://forums.slimdevices.com/member.php?userid=1516 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
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 bradcook's Profile: http://forums.slimdevices.com/member.php?userid=1033 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
@dolo: i am old & rusty. please open another thread (in the german section?) https://www.youtube.com/watch?v=YP5fkvNCeXw DJanGo's Profile: http://forums.slimdevices.com/member.php?userid=1516 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
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? Code: 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: Code: 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 Code: sqlite> select FUNK.url, FUNK.id, DANCE.id 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|7|7 file:///media/music/W/Womack%20&%20Womack/Teardrops/Womack%20&%20Womack%20-%20Teardrops%20-%2002%20-%20Teardrops.mp3|8|8 sqlite> open Trashcan Code: 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's Profile: http://forums.slimdevices.com/member.php?userid=1516 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
DJanGo wrote: > 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.) QLMS 7.9.2@2.02.03 x64 (digimaster) / QNAP 469L QTS 4.3.4 dolodobendan's Profile: http://forums.slimdevices.com/member.php?userid=67663 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
RobbH wrote: > 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. QLMS 7.9.2@2.02.03 x64 (digimaster) / QNAP 469L QTS 4.3.4 dolodobendan's Profile: http://forums.slimdevices.com/member.php?userid=67663 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
RobbH wrote: > 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. i am a mysql guy most things do work with sqlite as well but i am rusty and old now.. eg - That track has 3 Genres. Code: sqlite> select * from genre_track where genre_track.track like '7'; 28|7 18|7 40|7 Check Code: select * from genre_track where genre_track.track like '7' and genre_track.genre like '18'; 18|7 one match like it should Code: select * from genre_track where genre_track.track like '7' and genre_track.genre like '18' and genre_track.genre like '28'; sqlite> no match cause its the same table - there are some joins and temp. tables needed but like i already wrote i am rusty and old ;-) DJanGo's Profile: http://forums.slimdevices.com/member.php?userid=1516 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
RobbH wrote: > 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... DJanGo's Profile: http://forums.slimdevices.com/member.php?userid=1516 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
dolodobendan wrote: > 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? RobbH's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
DJanGo wrote: > 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's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
RobbH wrote: > > 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 DJanGo's Profile: http://forums.slimdevices.com/member.php?userid=1516 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
dolodobendan wrote: > 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. RobbH's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
RobbH wrote: > 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. QLMS 7.9.2@2.02.03 x64 (digimaster) / QNAP 469L QTS 4.3.4 dolodobendan's Profile: http://forums.slimdevices.com/member.php?userid=67663 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist
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! RobbH's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins
[SlimDevices: Plugins] Hoping for help with SQL Playlist
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 'Xmas'. (There are many more files that have the 'Xmas' genre tag, along with some other genre, and a few that only have the 'Xmas' tag.) My goal is to set up a playlist that only plays files that have both 'Jazz' and 'Xmas' tags. All attempts so far have failed miserably. Here's the code generated by SQL Playlist for a playlist that plays only 'Jazz': Code: -- 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: Code: and (genres.name in ('Jazz') and genres.name in ('Xmas')) I have tried different groupings of parentheses, without success. I also tried doubling the 'where' section: Code: (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 ('Xmas'))) 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's Profile: http://forums.slimdevices.com/member.php?userid=67008 View this thread: http://forums.slimdevices.com/showthread.php?t=109818 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/plugins