Re: [SlimDevices: Plugins] Hoping for help with SQL Playlist

2018-12-18 Thread dolodobendan


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

2018-12-10 Thread RobbH


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

2018-12-10 Thread RobbH


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

2018-12-08 Thread erland


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

2018-12-08 Thread erland


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

2018-12-08 Thread RobbH


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

2018-12-08 Thread DJanGo


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

2018-12-07 Thread RobbH


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

2018-12-07 Thread RobbH


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

2018-12-07 Thread DJanGo


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

2018-12-07 Thread bradcook


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

2018-12-07 Thread DJanGo


@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

2018-12-06 Thread DJanGo


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

2018-12-06 Thread dolodobendan


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

2018-12-06 Thread dolodobendan


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

2018-12-06 Thread DJanGo


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

2018-12-06 Thread DJanGo


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

2018-12-06 Thread RobbH


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

2018-12-06 Thread RobbH


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

2018-12-05 Thread DJanGo


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

2018-12-04 Thread RobbH


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

2018-12-04 Thread dolodobendan


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

2018-12-04 Thread RobbH


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

2018-11-30 Thread RobbH


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