[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
erland;166036 Wrote:
It works in my setup, how does the complete SQL look like ?
Like So:
select tracks.url from tracks
join track_statistics on
tracks.url=track_statistics.url
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id
where
audio=1
and dynamicplaylist_history.id is null
and tracks.secs60
and track_statistics.rating=50
or track_statistics.rating is null
and not exists (select * from tracks t2,genre_track,genres
where
t2.id=tracks.id and
tracks.id=genre_track.track and
genre_track.genre=genres.id and
genres.name in
('Christmas','Musical','Spoken Word'))
group by tracks.id
order by rand()
limit 10;
--
Yannzola
Yannzola's Profile: http://forums.slimdevices.com/member.php?userid=874
View this thread: http://forums.slimdevices.com/showthread.php?t=20533
___
plugins mailing list
plugins@lists.slimdevices.com
http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
Yannzola;166012 Wrote: Erland, as of the latest version of SQLPlaylist, my old dynamic playlists no longer work as expected. In particular the problem seems to be at the following lines: where and track_statistics.rating=50 or track_statistics.rating is null Previously, This would return only tracks listed with a rating of 3 stars or more =and= unrated tracks. Now it only returns unrated tracks. Bug or feature? It works in my setup, how does the complete SQL look like ? -- erland Erland Isaksson 'My homepage' (http://erland.homeip.net) 'My download page' (http://erland.homeip.net/download) (Developer of 'TrackStat' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-trackstat) , 'SQLPlayList' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-sqlplaylist) , 'DynamicPlayList' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-dynamicplaylist), 'Custom Browse' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-custombrowse),'Custom Scan' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-customscan) and 'RandomPlayList' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-randomplaylist) plugins) erland's Profile: http://forums.slimdevices.com/member.php?userid=3124 View this thread: http://forums.slimdevices.com/showthread.php?t=20533 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
Yannzola;166038 Wrote:
Like So:
select tracks.url from tracks
join track_statistics on
tracks.url=track_statistics.url
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id
where
audio=1
and dynamicplaylist_history.id is null
and tracks.secs60
and track_statistics.rating=50
or track_statistics.rating is null
and not exists (select * from tracks t2,genre_track,genres
where
t2.id=tracks.id and
tracks.id=genre_track.track and
genre_track.genre=genres.id and
genres.name in
('Christmas','Musical','Spoken Word'))
group by tracks.id
order by rand()
limit 10;
It still works in my setup, are you sure you have rated tracks that
matches the search critera ?
To match the critera they have to fullfill the following:
- They haven't already been played in this playlist since you started
to play it the last time.
- They don't belong to genres Christmas, Musical or Spoken Word
- The track length it as least 60 seconds
- They are unrated or have a rating of 3 or above
Maybe you already have played most of the tracks rated above 3 that
matches these criterias, then you just have to stop and start to play
the playlist again to start over again.
You might also try to put track_statistics.rating=50or
track_statistics.rating is null in between parenteses like
(track_statistics.rating=50 or track_statistics.rating is null).
This isn't required in my own setup, but it might work different in
your setup for some reason.
--
erland
Erland Isaksson
'My homepage' (http://erland.homeip.net) 'My download page'
(http://erland.homeip.net/download)
(Developer of 'TrackStat'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-trackstat)
, 'SQLPlayList'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-sqlplaylist)
, 'DynamicPlayList'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-dynamicplaylist),
'Custom Browse'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-custombrowse),'Custom
Scan'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-customscan)
and 'RandomPlayList'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-randomplaylist)
plugins)
erland's Profile: http://forums.slimdevices.com/member.php?userid=3124
View this thread: http://forums.slimdevices.com/showthread.php?t=20533
___
plugins mailing list
plugins@lists.slimdevices.com
http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Please help me with my synch problem
Well done. BTW. I am not one of these people who takes all the technical set up stuff with Slimserver etc in their stride. I find it very painful too. MC -- ModelCitizen Now what? Transporter Naim NAP 250 PMC OB1s. Music catalog: http://modelcitizen.mine.nu/music.txt ModelCitizen's Profile: http://forums.slimdevices.com/member.php?userid=446 View this thread: http://forums.slimdevices.com/showthread.php?t=31084 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
erland;166041 Wrote: Maybe you already have played most of the tracks rated above 3 that matches these criterias, then you just have to stop and start to play the playlist again to start over again. I doubt this very much... unless dynamicplaylist_history.id data is cumulative across sessions. I have approx 12,000 songs (4000 of which are rated) in my collection. Most playlists only end up being 40-80 tracks long on average. erland;166041 Wrote: You might also try to put track_statistics.rating=50or track_statistics.rating is null in between parenteses like (track_statistics.rating=50 or track_statistics.rating is null). This isn't required in my own setup, but it might work different in your setup for some reason. Nope. No joy. Any other suggestions or data I can provide? Thanks! y. -- Yannzola Yannzola's Profile: http://forums.slimdevices.com/member.php?userid=874 View this thread: http://forums.slimdevices.com/showthread.php?t=20533 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
Yannzola;166054 Wrote:
Nope. No joy. Any other suggestions or data I can provide?
1.
Just to make sure, you have restarted slimserver after you upgraded to
the new plugin versions ?
2.
Have you checked if there are any errors in the slimserver log:
http://localhost:9000/log.txt
3.
Could you try to create a number of new playlists like:
Playlist 1: Simple playlist, check that it only return tracks with
rating=3. If this playlist doesn't return anything, you don't have to
check the next playlists because then it means that your ratings
doesn't seem to exist. We then need to start investigate why.
Code:
select tracks.url from tracks
join track_statistics on
tracks.url=track_statistics.url
where
audio=1
and track_statistics.rating=50
group by tracks.id
order by rand()
limit 10;
Playlist 2: Doesn't exclude already played tracks, check if it returns
both unrated tracks and track rated=3
Code:
select tracks.url from tracks
join track_statistics on
tracks.url=track_statistics.url
where
audio=1
and tracks.secs60
and track_statistics.rating=50
or track_statistics.rating is null
and not exists (select * from tracks t2,genre_track,genres
where
t2.id=tracks.id and
tracks.id=genre_track.track and
genre_track.genre=genres.id and
genres.name in ('Christmas','Musical','Spoken Word'))
group by tracks.id
order by rand()
limit 10;
Playlist 3: Doesn't return unrated tracks, check that it only return
tracks rated=3
Code:
select tracks.url from tracks
join track_statistics on
tracks.url=track_statistics.url
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id
where
audio=1
and dynamicplaylist_history.id is null
and tracks.secs60
and track_statistics.rating=50
and not exists (select * from tracks t2,genre_track,genres
where
t2.id=tracks.id and
tracks.id=genre_track.track and
genre_track.genre=genres.id and
genres.name in ('Christmas','Musical','Spoken Word'))
group by tracks.id
order by rand()
limit 10;
Playlist 4: Doesn't exclude the genres, check if it returns both
unrated tracks and tracks rated=3
Code:
select tracks.url from tracks
join track_statistics on
tracks.url=track_statistics.url
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id
where
audio=1
and dynamicplaylist_history.id is null
and tracks.secs60
and track_statistics.rating=50
or track_statistics.rating is null
group by tracks.id
order by rand()
limit 10;
Playlist 5: Doesn't exclude short tracks, check if it returns tracks
with both unrated tracks and tracks rated=3
Code:
select tracks.url from tracks
join track_statistics on
tracks.url=track_statistics.url
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id
where
audio=1
and dynamicplaylist_history.id is null
and track_statistics.rating=50
or track_statistics.rating is null
and not exists (select * from tracks t2,genre_track,genres
where
t2.id=tracks.id and
tracks.id=genre_track.track and
genre_track.genre=genres.id and
genres.name in ('Christmas','Musical','Spoken Word'))
group by tracks.id
order by rand()
limit 10;
--
erland
Erland Isaksson
'My homepage' (http://erland.homeip.net) 'My download page'
(http://erland.homeip.net/download)
(Developer of 'TrackStat'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-trackstat)
, 'SQLPlayList'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-sqlplaylist)
, 'DynamicPlayList'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-dynamicplaylist),
'Custom Browse'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-custombrowse),'Custom
Scan'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-customscan)
and 'RandomPlayList'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-randomplaylist)
plugins)
erland's Profile: http://forums.slimdevices.com/member.php?userid=3124
View this thread: http://forums.slimdevices.com/showthread.php?t=20533
___
plugins mailing list
plugins@lists.slimdevices.com
http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
What I noticed from testing the code you provided was this: All of them worked as expected =except= for the ones using the lineor track_statistics.rating is null which =only= returned unrated tracks. The result I was expecting was a mix of 3+ star rated tracks and unrated tracks. Note: I am using the latest nightly of 6.5.1 -- Yannzola Yannzola's Profile: http://forums.slimdevices.com/member.php?userid=874 View this thread: http://forums.slimdevices.com/showthread.php?t=20533 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
Yannzola;166066 Wrote: What I noticed from testing the code you provided was this: All of them worked as expected =except= for the ones using the lineor track_statistics.rating is null which =only= returned unrated tracks. The result I was expecting was a mix of 3+ star rated tracks and unrated tracks. Note: I am using the latest nightly of 6.5.1This is really strange, it works perfectly at my end. I have tried it both on Windows and Linux (not with the latest 6.5.1 though, my installation is a few weeks old) Are you using the mysql bundled with slimserver ? Which operating system are you running slimserver/mysql on ? If you are running Windows, have you tried restarting the slimserver computer ? (Sometimes solves strange problems on Windows) Have you changed anything else in your setup besides upgrading the plugins that could affect the behaviour in mysql ? Could you turn on debug logging in SQLPlayList and see if you see some errors in the slimserver log ? -- erland Erland Isaksson 'My homepage' (http://erland.homeip.net) 'My download page' (http://erland.homeip.net/download) (Developer of 'TrackStat' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-trackstat) , 'SQLPlayList' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-sqlplaylist) , 'DynamicPlayList' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-dynamicplaylist), 'Custom Browse' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-custombrowse),'Custom Scan' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-customscan) and 'RandomPlayList' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-randomplaylist) plugins) erland's Profile: http://forums.slimdevices.com/member.php?userid=3124 View this thread: http://forums.slimdevices.com/showthread.php?t=20533 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
Also, could you try to raise the limit in the SQL statement from 10 to something higher just to make sure it doesn't randomly select unrated tracks because you have more of them. -- erland Erland Isaksson 'My homepage' (http://erland.homeip.net) 'My download page' (http://erland.homeip.net/download) (Developer of 'TrackStat' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-trackstat) , 'SQLPlayList' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-sqlplaylist) , 'DynamicPlayList' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-dynamicplaylist), 'Custom Browse' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-custombrowse),'Custom Scan' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-customscan) and 'RandomPlayList' (http://erland.homeip.net/download/do/viewapplication?name=slimserver-randomplaylist) plugins) erland's Profile: http://forums.slimdevices.com/member.php?userid=3124 View this thread: http://forums.slimdevices.com/showthread.php?t=20533 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: itunesupdate 2.0 not updating ratings but otherwise ok?
I am changing the ratings on the remote eg. pressing 3 when song is playing. Dos box reports itunesUpdate.saverating_3,3, 10 and : changing song rating to : 30. If I select itunesupdate in the localhost browser I get Nothing being timed -- ajmitchell Check out my other hobby http://ajmitchell.shutterpoint.com ajmitchell's Profile: http://forums.slimdevices.com/member.php?userid=800 View this thread: http://forums.slimdevices.com/showthread.php?t=31104 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: itunesupdate 2.0 not updating ratings but otherwise ok?
It works fine if I only use one squeezebox. If I syn to any of my other players then the ratings doesn't get logged but the playcount does! Once unsynched itunesupdate web reports correctly: eg. Title: Great Gaels Of Ireland iTunes Rating: iTunes Play Count: 4 iTunes Last Played: 12-Dec-2006 15:59:20 iTunes Skip Count: 1 iTunes Last Skipped: 31-Dec-2006 12:41:24 Because I synch much of the time this is why I reported it doesnt usually work for me. Might be worth listing this as a bug although once you know about it its not such a big deal. Alex ps.Keep up the excellent work James -- ajmitchell Check out my other hobby http://ajmitchell.shutterpoint.com ajmitchell's Profile: http://forums.slimdevices.com/member.php?userid=800 View this thread: http://forums.slimdevices.com/showthread.php?t=31104 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: itunesupdate 2.0 not updating ratings but otherwise ok?
James, I don't know if this is a similar issue as I had with TrackStat a
while back. The problem I had was that the slimserver events doesn't
work the same way when you run several synchronized squeezeboxes. I
solved it in TrackStat by using the syncgroupid attribute as key in the
player hash for status information. Sample code below.
Code:
my $key = $client;
if(defined($client-syncgroupid)) {
$key = SyncGroup.$client-syncgroupid;
}
if (!defined($playerStatusHash{$key}))
.
.
.
--
erland
Erland Isaksson
'My homepage' (http://erland.homeip.net) 'My download page'
(http://erland.homeip.net/download)
(Developer of 'TrackStat'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-trackstat)
, 'SQLPlayList'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-sqlplaylist)
, 'DynamicPlayList'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-dynamicplaylist),
'Custom Browse'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-custombrowse),'Custom
Scan'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-customscan)
and 'RandomPlayList'
(http://erland.homeip.net/download/do/viewapplication?name=slimserver-randomplaylist)
plugins)
erland's Profile: http://forums.slimdevices.com/member.php?userid=3124
View this thread: http://forums.slimdevices.com/showthread.php?t=31104
___
plugins mailing list
plugins@lists.slimdevices.com
http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: itunesupdate 2.0 not updating ratings but otherwise ok?
Thanks Erland, that sounds like the problem... I never sync my squeezeboxes - I don't have a big enough flat! James -- James James's Profile: http://forums.slimdevices.com/member.php?userid=189 View this thread: http://forums.slimdevices.com/showthread.php?t=31104 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
erland;166075 Wrote: Are you using the mysql bundled with slimserver ? Which operating system are you running slimserver/mysql on ? If you are running Windows, have you tried restarting the slimserver computer ? (Sometimes solves strange problems on Windows) Have you changed anything else in your setup besides upgrading the plugins that could affect the behaviour in mysql ? Could you turn on debug logging in SQLPlayList and see if you see some errors in the slimserver log ? I'm using the standard mysql bundled with SS. I'm running windows and have already tried restarting the computer to no effect. I can't think of any other changes other than: Upgrading to the latest nightly and upgrading to the 12/25 builds of the plugins. I'll turn on debugging + try increasing the number of results and report. Thanks Erland! -- Yannzola Yannzola's Profile: http://forums.slimdevices.com/member.php?userid=874 View this thread: http://forums.slimdevices.com/showthread.php?t=20533 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Audioscrobbler Tag Submitting?
I now thing that the original problem with the comma was a red herring. I've tried chars ()[]#, and they all work in plain text... so, I've uploaded a new version 0.37.5 to Sourceforge: http://sourceforge.net/project/showfiles.php?group_id=105780 James -- James James's Profile: http://forums.slimdevices.com/member.php?userid=189 View this thread: http://forums.slimdevices.com/showthread.php?t=30127 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Slimserver crashing when selectng Internet radio stations from Remote
I just upgraded from SlimServer 6.2.1 and AlienBBC 1.0 to SS 6.5.0 and AlienBBC 1.05. Since I upgraded my Server crashes almost every time I select either a BBC channel from AlienBBC or one of the new channels from RadioTine.com using the remote control. If I select from the SlimServer GUI on my PC all is fine. (Most annoying as I have to go upstairs and reboot every time it happens!) I have tried reverting to SS 6.2.1 (though still with Alien BBC 1.05 reinstalled again using the correct Install pack for 6.2.1 on top but the problem now exists with 6.2.1 as well. I could go back to the original configuration I guess but I need to know how to make sure I don't leave any configuration artefacts of either SS or ALienBBC on the PC that will affect the new install Help - anyone got any ideas please? -- adrianh1960 adrianh1960's Profile: http://forums.slimdevices.com/member.php?userid=5421 View this thread: http://forums.slimdevices.com/showthread.php?t=31150 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Slimserver crashing when selectng Internet radio stations from Remote
Adrian, I note that you refer to BBC channels on your post. I am struggling to get BBC Radio 2 on the squeeze box and am concerned that might not be possible at all ! Can you reassure me that it is possible to get radio 2 in the UK on a squeeze box? Many thanks, Simon -- Simon Wilkinson Regards, Simon Simon Wilkinson's Profile: http://forums.slimdevices.com/member.php?userid=9326 View this thread: http://forums.slimdevices.com/showthread.php?t=31150 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Slimserver crashing when selectng Internet radio stations from Remote
Latest AlienBBC is 1.06. Have you tried that ? Have you upgraded mplayer ? Have you tried the Test mplayer.bat from the alien install ? -- Benway Benway Benway's Profile: http://forums.slimdevices.com/member.php?userid=8944 View this thread: http://forums.slimdevices.com/showthread.php?t=31150 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
[SlimDevices: Plugins] Re: Announce: Plugins for rating and smart playlists using SQL
Okay. Reinstalled all of the latest 12/25 plugins, changed the number of results up to 100, deleted and rebuilt my old .sql files ... still no joy. Attached is a sample log report: +---+ |Filename: errors.txt | |Download: http://forums.slimdevices.com/attachment.php?attachmentid=2205| +---+ -- Yannzola Yannzola's Profile: http://forums.slimdevices.com/member.php?userid=874 View this thread: http://forums.slimdevices.com/showthread.php?t=20533 ___ plugins mailing list plugins@lists.slimdevices.com http://lists.slimdevices.com/lists/listinfo/plugins
