SQL Playlists: updating to 7.5 versions to LMS

[MillmoorRon]

Erland,

I'm trying to update my SQL Playlists to work on LMS.

I've changed all instances of rand() to random() which makes most work OK but I still have some problematic ones.

My Recently Added SQL have a 'days' parameter:

-- PlaylistParameter1:list:Added in the last:1:Day,7:Week,14:Fortnight,30:Month,91:Quarter,182:Six Months,365:Year

which is later used in:

and track_statistics.added>UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL -'PlaylistParameter1' DAY))

Could you please advise the syntax for the new version:

and track_statistics.added>STRFTIME("%s",DATE('NOW','-30 DAY'))

to add this parameter in the place of the 30 above?

[erland]

Have you tried this ?

and track_statistics.added>STRFTIME("%s",DATE('NOW','-'PlaylistParameter1' DAY'))

Let me know if this doesn't work and I'll take a closer look at it.

[MillmoorRon]

Yes, that seems to work.

Thanks!

One other (slightly related) issue I'm having is that after creating a new SQL Playlist or Custom Browse menu through the web interface I find that the owner of the file (server is on Ubuntu) is 'squeezeboxserver' and that only the owner has write permissions. I tend to edit these files in a text editor (on a different Windows PC) rather than in the web interface as it is easier to see the whole file.

Is there any way to tweak it so that the owner is 'nobody' or write permission is added for all?

[erland]

Yes, that seems to work.

Thanks!

One other (slightly related) issue I'm having is that after creating a new SQL Playlist or Custom Browse menu through the web interface I find that the owner of the file (server is on Ubuntu) is 'squeezeboxserver' and that only the owner has write permissions. I tend to edit these files in a text editor (on a different Windows PC) rather than in the web interface as it is easier to see the whole file.

Is there any way to tweak it so that the owner is 'nobody' or write permission is added for all?

I don't want to add any code in the plugins to handle this as they have to work on all operating systems.
But the possible solutions I can see is:
- Setup a default umask for the squeezeboxserver user which allows everyone to write, just note that this will affect all files created by squeezeboxserver user and not just the Custom Browse/SQL Playlist configuration files.
- Setup a background cron job that modifies the permissions once a minute one the files in the Custom Browse and SQL Playlist configuration directories.
- If the files are mounted via Samba and edited on a Windows PC, the best solution is probably to handle it in the samba configuration on the Linux machine.

All the above solutions are more about Linux than my plugins, I just wanted to give you a few clues so you know what to search for.
-

[MillmoorRon]

Thanks for the tips.

I think I'll go with the cron suggestion as it seems the easiest!

[MillmoorRon]

I have now tested all my old playlists and I have just one type which is causing errors.

They are all generally of this format:

-- PlaylistName:All by Years...
-- PlaylistParameter1:year:Select 1st year:
-- PlaylistParameter2:custom:Select 2nd year:select tracks.year,tracks.year from tracks where tracks.year>='PlaylistParameter1' and tracks.audio=1 group by tracks.year order by tracks.year
select tracks.url from tracks
 	left join dynamicplaylist_history on
		tracks.id=dynamicplaylist_history.id
	where
 		tracks.audio=1
 		and dynamicplaylist_history.id is null
		and tracks.year>='PlaylistParameter1'
		and tracks.year<='PlaylistParameter2'
	group by tracks.id
	order by random()
 	limit 20;

In iPeng or SqueezeCommander the '2nd year' selection does not appear at all. I think Pippin suggested that the plugin was not passing the correct data to is app but I thought that you had fixed this a couple of years ago.

I get this in the log:

[12-05-08 15:53:44.2372] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::db prepare failed: near "and": syntax error [for Statement "select tracks.year,tracks.year from tracks where tracks.year>= and tracks.audio=1 group by tracks.year order by tracks.year"]

If I try it through the web interface it seems to work - i.e. it returns the expected tracks - however I get the following (and some backtrace lines) log:

[12-05-08 15:59:22.4414] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select id,name from genres order by namesort"]
[12-05-08 15:59:24.7529] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select id,name from genres order by namesort"]
[12-05-08 15:59:25.1660] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select year,year from tracks where year is not null group by year order by year desc"]
[12-05-08 15:59:28.7134] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select id,name from genres order by namesort"]
[12-05-08 15:59:29.1253] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select year,year from tracks where year is not null group by year order by year desc"]
[12-05-08 15:59:29.5156] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select tracks.year,tracks.year from tracks where tracks.year>=1997 and tracks.audio=1 group by tracks.year order by tracks.year"]

Is this something that needs changing in my SQL to make it compatible, or a problem with the plugin?

[erland]

I have now tested all my old playlists and I have just one type which is causing errors.

...

In iPeng or SqueezeCommander the '2nd year' selection does not appear at all. I think Pippin suggested that the plugin was not passing the correct data to is app but I thought that you had fixed this a couple of years ago.

...

I get this in the log:

[12-05-08 15:53:44.2372] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::db prepare failed: near "and": syntax error [for Statement "select tracks.year,tracks.year from tracks where tracks.year>= and tracks.audio=1 group by tracks.year order by tracks.year"]

It's a bug in Dynamic Playlist, will try to release a new version where it's fixed in the near future, just want to test the correction a bit more first, if everything goes as planned I should be able to release it later this week.
The issue is when PlaylistParameter2 sql have a SQL statement that contains PlaylistParameter1.

Just out of interest, have these playlists really worked with a previous Dynamic Playlist version in iPeng/SqueezeCommander/Radio/Touch/Controller ?
I'm just asking because I can't remember that I've done any changes recently in this area, so it feels like a very old problem, but I might have done things which I don't remember at the moment.

If I try it through the web interface it seems to work - i.e. it returns the expected tracks - however I get the following (and some backtrace lines) log:

[12-05-08 15:59:22.4414] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select id,name from genres order by namesort"]
[12-05-08 15:59:24.7529] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select id,name from genres order by namesort"]
[12-05-08 15:59:25.1660] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select year,year from tracks where year is not null group by year order by year desc"]
[12-05-08 15:59:28.7134] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select id,name from genres order by namesort"]
[12-05-08 15:59:29.1253] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select year,year from tracks where year is not null group by year order by year desc"]
[12-05-08 15:59:29.5156] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::st bind_columns failed: bind_columns called with 3 values but 2 are needed [for Statement "select tracks.year,tracks.year from tracks where tracks.year>=1997 and tracks.audio=1 group by tracks.year order by tracks.year"]

These are harmless, I really should try to find a better way to handle it so they don't produce log messages but for now you can just ignore these messages.

[MillmoorRon]

It's a bug in Dynamic Playlist, will try to release a new version where it's fixed in the near future, just want to test the correction a bit more first, if everything goes as planned I should be able to release it later this week.

Thanks!

Just out of interest, have these playlists really worked with a previous Dynamic Playlist version in iPeng/SqueezeCommander/Radio/Touch/Controller ?
I'm just asking because I can't remember that I've done any changes recently in this area, so it feels like a very old problem, but I might have done things which I don't remember at the moment.

I think it was a couple of years ago but I'm not entirely sure whether it was actually fixed or I just stopped using them as I knew they didn't work properly!

[MillmoorRon]

Works perfectly now. Thanks!

[Goattee]

This query ran fine in 7.5. I only had to change rand() to random().

-- PlaylistName:1) Evening Breeze
-- PlaylistGroups:Casual/Jazz & Pop
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'
left join track_statistics on
tracks.url=track_statistics.url
where
audio=1
and dynamicplaylist_history.id is null
and genres.name in ('Brazilian','Brazilian: Bossa Nova','Jazz: Brazilian','Jazz: Guitar','Jazz: Piano','Jazz Vocal','Jazz Vocal: Female','Jazz Vocal: Male','Jazz Vocal: Pop','Pop Vocal','Pop Vocal: Female','Pop Vocal: Male')
and ifnull(track_statistics.lastplayed,0)<(unix_timest amp()-2419200)
group by tracks.id
order by random()
limit 100;



But when I test it, I get the following error. (I am currently scanning a large set of music files on a NAS elsehwhere on my LAN-- could this be the cause of thte error.)

Error:
Carp::Clan::__ANON__(): Carp::Clan::__ANON__(): DBI Exception: DBD::SQLite::st execute failed: database is locked [for Statement "DELETE FROM tracks WHERE ( id = ? )"] at /Slim/Schema/Storage.pm line 126