SQL Playlist plugin
From SqueezeboxWiki
Contents |
Overview
This plugin for SqueezeCenter(formerly known as SlimServer) makes it possible to create smart playlists. The standard SqueezeCenter doesn't support smart playlists by itself, together with iTunes and the standard SqueezeCenter iTunes integration it is possible to use smart playlists defined in iTunes. The problem though is that the integrated iTunes smart playlists will not be recalculated until the next time you perform a rescan in SqueezeCenter. The SQL Playlist plugin takes care of this problem and implements native smart playlist in SqueezeCenter without any need to use iTunes. The smart playlists implemented in SQL Playlist will also be automatically re-calculated after each track played so they will be based on the latest statistic information.
A smart playlist in SQL Playlist are continous and will run forever in the same way as the standard Random Mix plugin, you can optionally also choose that a smart playlist shouldn't repeat already played tracks and in that case the music will stop when all songs matching the playlist has been played.
First you will have to configure a SQLPlayList playlist directory in the "SQL Playlist" section of the SqueezeCenter Settings web interface. After this is done you are ready to create your smart playlists as described below.
The easiest way to create a playlist is from the Extras/SQL PlayList menu in the web user interface, but you can also create it manually as described in a separate chapter below.
Installation
- Remove any previous version of SQL Playlist from the SqueezeCenter Plugins directory if you have manually installed it earlier (this is not needed if you have installed it through Extension Downloader)
- Goto SqueezeCenter Settings/Plugins and select to install SQL Playlist. You might need to add Erland's repository to see it, see here for more information regarding this: SqueezeCenter_Repositories. If you don't want to install it through Extension Downloader, you can also download it from the download page and manually unzip the new version in the SqueezeCenter Plugins directory
- Please note that earlier versions of this plugin will require you to also install the License Manager plugin.
- Please note that earlier versions of this plugin will require you to purchase a license.
- Install other plugins required for SQL Playlist
- Optionally Install other plugins that extends SQL Playlist with more functionality
- When everything has been installed you might also want to look at this:
Note!
- More information about how to purchase and activate the commercial license can be found through the License Manager plugin
If you are using SlimServer 6.5 or earlier, you may want to use the old guide instead.
Bugs and new features
The current list of known bugs and wishes for new features can be found here:
If you want to encourage future development of this plugin you should also consider making a donation or purchasing a license
Create and use a smart playlist
The first thing you need to do is create a playlist, this is done as follows
- Goto Extras/SQL Playlist in the web interface
- Click Create new playlist
- Select the type of playlist you want to create
- Enter parameters for the playlist, some common parameters which is available on most playlist types are
- Playlist name: The name of the playlist which will be shown to the user
- Groups: Which group the playlist shall be put in, for example a value like:
Random,Summer/Hits
Will make the playlist available in both the Random group and in the Summer/Hits group - Do not repeat tracks within same playlist: Indicates that a specific track shall only be played once from the time you hit played until all tracks that is part of the playlist has been played
- Limit to active library: Indicates that the playlist only shall contain music files which is part of the sub library that is activated in the Multi Library plugin
- Library: Indicates that the playlist only shall contain music files for the specified sub library defined in the Multi Library plugin
- Custom Skip filter: The Custom Skip filter set from the Custom Skip plugin that shall be activated as a secondary skip filter when this playlist is started. See Custom Skip plugin for more details about skip filters.
- Customize SQL: Specify Customize SQL here if you like to customize the SQL code manually. Generally speaking it is best to select Use predefined unless you know SQL. If you select to customize the SQL you won't get the user friendly interface back for changing playlist parameters so make sure every other parameter has a good value before you start to customize the SQL.
- As a guide if you leave every parameter with its default value you will for most of the playlist types get a playlist that contains random music for your whole library. Specifying parameters limit the contents of the playlist to a smaller part of the library. As an example to this there is no idea to mark every genre in the "Include genres" parameter, this is exactly the same as marking no "Include genres" at all. If you just want to include music from a single genre on the other hand, you should of course select that single genre in the "Include genres" parameter.
- Click Next to continue
- Enter a filename, just change the first part and leave the extensions as they are.
- Click Save to store the playlist
You now have a smart playlist which is ready to use, to start playing it you:
- Navigate to Music Library/Dynamic Playlists on the SqueezeBox and select the playlist and hit play
- Navigate to Music Library/Dynamic Playlists in the web interface and select the playlist and hit play
- Navigate to Extras/SQL Playlist in the web interface and select the playlist and hit play
Manual creation of playlist files
There is actually no idea to create the playlist files manually, you should be able to do everything from the web interface. But if you still want to do it manually you will have to:
- Create a file with .sql extension (See format description in separate chapter)
- Put the sql file in the configured SQL PlayList playlist directory
- Navigate to the "Extras/SQL PlayList" menu on the Squeezebox and select the playlist and start playing, or use the "Extras/SQL PlayList" menu in the web interface.
Downloading and sharing playlists
SQL Playlist provides a way to download and share playlist definitions with other users, this is used in a number of different ways:
- The latest version of the builtin playlist types are always available for download, this means that instead of waiting for a new official release of SQL Playlist you can download a new playlist directly after it has been corrected by the developer.
- A user can choose to publish/share a playlist he has made himself, after publishing this playlist will be available for download for all other users
When publishing/sharing a playlist you have the following options
- You can choose to register, the advantage with this is that other users will see that you are the author for the playlist so you will get some credit for it. It is also a way to make sure that no one else can modify your shared playlist. You register by selecting on of the "Publish" links and then selecting "Register & Login" in the SQL PlayList menu under "Extras" in the SqueezeCenter web interface.
- You can choose to publish a playlist anonymously, this way you don't need to provide any information about yourself but it also means that other persons can update and modify the shared playlist.
The recommendation is that you register when publishing playlists but its still better that you publish a playlist anonymously than not publish it at all.
Playlist types as separate plugins
You can implement your own playlist templates by implementing the methods described below in your plugin. The plugin must be enabled for it to be detected by SQL PlayList plugin.
The reason to do this is that you can use all the logic available in SQL Playlist plugin for executing the SQL and handling the playlist, all you have to do is provide the playlist definition.
# Returns an array with templates
# This method will be called by the SQLPlayList plugin whenever the type of playlists available shall be shown
# Parameters in each template
# id = A uniqe identifier
# type =
# final: You are responsible for replacing parameters in getSQLPlayListTemplateData,
# template: SQLPlayList will replace [% parametervalue %] with the actual value, same template types as used in the HTML pages
# for SqueezeCenter. See also all *.templates files in SQLPlayList/Templates directory for some samples
# template = The actual playlist template configuration, consists of the following parts
# name = A user friendly name of your playlist type
# description = A description of your playlist type
# parameter = An array of playlist parameters that shall be possible for the user to specify, see *.xml files in SQLPlayList/Templates directory
# for some samples
sub getSQLPlayListTemplates {
my ($client) = @_;
my @result = ();
my %template = (
'id' => 'mynicetemplate',
'type' => 'final',
'template' => {
'name' => 'My nice playlist',
'description' => 'A random playlist with parameter for number of tracks to return',
'parameter' => [
{
'type' => 'text',
'id' => 'playlistlength',
'name' => 'Length of playlist',
'value' => 20
}
]
}
);
push @result,\%template;
return \@result;
}
# Returns the actual playlist for a specified template based on parameter values, this method will be
# called by SQLPlayList plugin when a playlist is required. The returned data is the actual playlist.
# In-parameters:
# template = The template for selected playlist, the same as returned previously by getSQLPlayListTemplates method
# parameters = A hash map with parameter values that the user has entered for your parameters
sub getSQLPlayListTemplateData {
my ($client,$template,$parameters) = @_;
if($template->{'id'} eq 'mynicetemplate') {
if($parameters->{'playlistlength'}) {
my $result = "-- PlaylistName:My nice playlist\nselect url from tracks order by rand() limit ".$parameters->{'playlistlength'}.";";
return $result;
}else {
my $result = "-- PlaylistName:My nice playlist\nselect url from tracks order by rand() limit 10;";
return $result;
}
}
return undef;
}
Playlist file format
Row types in playlist files
The SQLPlayList sql file for a playlist must have the following syntax
| First row | The name of the playlist (This text will be shown for the playlist in the Squeezebox menu for SQLPlayList plugin). This first row can also as the examples begin with:
-- PlaylistName: | ||||||||||||||||||||
| Groups row | The groups where the playlist should be in the navigation tree in the DynamicPlayList plugin. For example the following will put the playlist both below "Albums/Pop" and "Good playlists":
-- PlaylistGroups: Albums/Pop,Good playlists | ||||||||||||||||||||
| Parameter rows | The parameters that should be requested from the user, up to 9 parameters are supported. The parameter row are optional, the id must start with 1 and increase with 1 for each parameter in the playlist. The syntax is:
-- PlaylistParameter[id]:[type]:[name]:[definition]
Some examples of parameter definitions:
| ||||||||||||||||||||
| Option rows | The options for the playlist, the options described below is currently supported. The option rows are optional.
-- PlaylistOption [id]:[value]
Currently supported options:
Some examples:
| ||||||||||||||||||||
| Action rows | The actions that should be executed when the playlist start or stop. The action rows are optional, the id must start with 1 and increase with 1 for each action in the playlist. The syntax is:
Some examples of action definitions:
| ||||||||||||||||||||
| Other rows | SQL queries, all queries will be executed and those starting with SELECT must return the correct information, unless PlaylistOptionContentType has been set this means that they shall return a single "url" column and the tracks returned in all SELECT statements will be part of the playlist. |
Keyword replacement
Before an SQL is executed parameters in the SQL are replaced with real values. Parameters can be those defined on the parameter rows but there are also a number of dynamic parameters which will be replaced every time the SQL statements are executed. Any string in the SQL matching the following values will be replaced with the real value:
- 'PlaylistParameterx' - Where x is the pararemeter number. This will be replaced with the value of the specified parameter.
- 'PlaylistLimit' - Will be replaced with the number of tracks requested from DynamicPlayList plugin
- 'PlaylistOffset' - Will be replaced with the number of tracks previously played for this playlist
- 'PlaylistPlayer' - Will be replaced with the current player id
Note! The ' that surrounds the values above are important and must exist if a replacement shall occur.
Templates
It is possible to configure a template directory in the settings page for SQL Playlist inside SqueezeCenter Settings in the web interface. By doing this you can add your own playlist templates. Templates are usefull if you start to customize the playlist configuration in several similar playlist, in that situation you can instead choose to create a template with some parameters, where each playlist just have different values on the parameters. You can also use templates if you want to base a playlist on the advanced playlist type but limit the number of parameters shown to the user.
A menu template consists of two files:
| xxx.sql.template | The playlist configuration which can contain [% yyy %] keywords that will be replaced with real values when the menu is created. |
| xxx.sql.xml | A description of the template and a definition of the parameterse that the user shall enter. See the templates in the SQLPlayList/Templates directory for some samples.
NOTE! The templates can contain a downloadidentifier element, you shall NOT include this element if you create a new template you should also remove it if you modify an existing template. The purpose of the downloadidentifier element is to make the download function work, it will automatically be added to the template if you choose to publish it. |
Example playlists
Some example playlists follows below, observere that the SQL statements needs to be different for the standard database in SlimServer 6.3(SQLite) and for the MySQL database in SlimServer 6.5 and SqueezeCenter 7.0 or later. So make sure you use the right syntax based on which database you are using. The main difference for simple queries is that SQLite uses "order by random()" while MySQL uses "order by rand()". See also the playlist templates available in the web ui for more examples.
- Playlist1.sql: MySQL (Tracks never played) '
-- PlaylistName: Not played tracks select url from tracks where audio=1 and playCount is null order by rand() limit 10;
- Playlist2.sql: MySQL (Tracks rated as 4-5 in TrackStat, requires TrackStat plugin)
-- PlaylistName: Top rated tracks -- PlaylistGroups: Top rated select tracks.url from track_statistics,tracks,albums where tracks.album=albums.id and tracks.url=track_statistics.url and track_statistics.rating>=80 and tracks.audio=1 order by rand() limit 10;
- Playlist3.sql: MySQL (All tracks besides those which contains genre=Christmas and some bad albums)
-- PlaylistName: Mixed without Christmas
create temporary table genre_track_withname (primary key (track,genre)) select genre_track.track,genre_track.genre,genres.namesort from genre_track,genres where genre_track.genre=genres.id;
create temporary table tracks_nochristmas (primary key (id)) select distinct tracks.id,tracks.title,tracks.url,tracks.album from tracks left join genre_track_withname on tracks.id=genre_track_withname.track and genre_track_withname.namesort='CHRISTMAS' where genre_track_withname.track is null and tracks.audio=1 order by tracks.title;
create temporary table albums_nobad (primary key (id)) select albums.id from albums,tracks_nochristmas,contributor_track where tracks_nochristmas.album=albums.id and tracks_nochristmas.id=contributor_track.track and albums.title not in ('Music Of The Movies - The Love Songs','Piano moods','Love Themes Of The Pan Pipes') group by (albums.id) having count(distinct contributor_track.contributor)<4 order by id;
select tracks_nochristmas.url from tracks_nochristmas,albums_nobad where tracks_nochristmas.album=albums_nobad.id order by rand() limit 10;
drop temporary table genre_track_withname;
drop temporary table tracks_nochristmas;
drop temporary table albums_nobad;
