Airsonic: Meta: Smart playlists

[fxthomas]

I'm creating this issue to gather information and discuss about enhancing playlist management in Airsonic, particularly for the purpose of creating smart playlists.

Existing features

• In the "More" page, "Shuffle Radio" can shuffle through a collection based on a combination of the following parameters, provided through HTTP form data:

  • Genre
  • Starred status
  • Folder
  • Format
  • Year
  • Album rating
  • Last played date
  • Play count

• Single-parameter shuffle is present on many pages for these items:

  • Home
  • Random
  • Recently Added
  • Starred albums
  • Top rated
  • Most played
  • Recently played
  • Year
  • Genre
  • Albums
  • Artists

Easy changes

The following changes can be easily added without changing the architecture a lot:

• [ ] Shuffle is not present on Playlist pages

Planned changes

Existing feature requests/issues

• Smart playlist feature request (#803)
  
  
  
  • Regular expressions
  
  
• Custom search queries (#605)
  
  
  
  • Custom search query language
  
  
  • Boolean expressions
  
  
• Custom search queries (#370)
  
  
  
  • Use Lucene search queries directly
  
  
• Additional search terms:
  
  
  
  • Skip to play ratio (#1480)
  
  
  • 1-to-5 star ratings for songs (#1497)
  
  
  • Per-song play count (#1106) (already present in Shuffle Radio)
  
  
  • Last play time for songs (#1106) (already present in Shuffle Radio)
  
  

Current limitations

The "Shuffle Radio" feature is a first workaround for the absence of smart playlists, but it suffers from the following issues.

Limited search keywords

We're going to need a new way to search through the database in order to implement smart playlists in a maintainable way. The way parameters are passed to Shuffle Radio is not going to be very easy to maintain.

I want to add a new search query syntax, that can be used as a base for smart playlists (this means we can just record the search query in a field in the db and use that). Some ideas already discussed in https://github.com/airsonic/airsonic/issues/803#issuecomment-570927765.

The search query syntax can be:

1. Based off Lucene queries (not very maintainable but easy to use?)
2. A custom parser (e.g. ANTLR)

Running the search query can be:

1. On the database
2. On Lucene (but how do we update it outside of scans?)
3. On a mix of both (how do we synchronize things, harder to implement due to multiple layers?)

Save search queries to automatically updated playlists

I think adding a new table/DAO is the correct thing to do. Existing PLAYLIST table is not made for automatically-updated playlists, so anything we bolt on top of that is not going to be very clean.

So, this means adding a new SMART_PLAYLIST table to Airsonic, with the following fields:

• id
• username
• is_public
• name
• comment
• query
• cached_file_count ?
• cached_duration ?

Search results can be populated on demand (I think this is the easiest solution, hopefully we won't have too much trouble with performance).

Some attributes could be cached (e.g. an estimate of the file_count and duration) so that they could be displayed in the playlist page, optionally updated during media scans.

Browse the search results/playlist

We should add a way to browse the resulting search results/playlist. Here are a few suggestions, in order of difficulty:

1. Use the current playlist page
   
   
   
   • Pros: Can be filtered somewhat (Ctrl-F)
   
   
   • Cons: Very slow if the number of results is large
   
   
2. Create a new search result page, paginated with HTTP parameters
   
   
   
   • Pros: Relatively easy to implement and can have enough performance
   
   
   • Cons: No easy way to filter through a large number of results
   
   
3. Create a new search result page, paginated with an API end point (e.g. datatables)
   
   
   
   • Pros: More modern, fast, easy to search/sort/filter
   
   
4. Completely overhaul how lists of items are displayed (use datatables everywhere in Airsonic)
   
   
   
   • Pros: Uses the same behavior everywhere in Airsonic
   
   

Update the playlist UI

A few things could be added to make it easier to use:

• Two "new playlist" and "new smart playlist" buttons on the "Playlists" page.
• A "edit smart playlist" page that reuses the new search page
• A way to tell (in the playlist page) that a playlist is a smart/regular playlist

Use from Subsonic API clients

Some ideas on the subject:

1. Add ability to use the new search query syntax from API (with a prefix, e.g. =)
2. Return the _n_ first items of smart playlists using the existing playlist API
3. Return all items of smart playlists using the existing playlist API (potentially very slow?)

Development plan

New query syntax

Basic syntax

The most basic query syntax I can imagine is a space-separated sequence of search terms in the form, where a field only appears once:

• field:value (if value doesn't have spaces)
• field:"value with spaces" (if value has spaces)

The following column types can be supported (mostly borrowed from Beets, with a small subset of the features):

• Boolean
  
  
  
  • field:true, field:t, field:1, field:y, field:yes all evaluate to "field is true"
  
  
  • field:false, field:f, field:0, field:n, field:no all evaluate to "field is false"
  
  
• String (case-insensitive)
  
  
  
  • field:value means that field contains value
  
  
• Numerical
  
  
  
  • field:value means that field is exactly equal to value
  
  
• Dates (ISO date, time or date/time)
  
  
  
  • field:yyyy-mm-dd
  
  
  • field:yyyy-MM-dd hh:mm:ss
  
  
  • field:hh:mm:ss
  
  

The following columns can be supported:

• path: media_file.path
• title: media_file.title
• album: media_file.album
• artist: media_file.artist
• albumartist: media_file.album_artist
• discnumber: media_file.disc_number
• tracknumber: media_file.track_number
• bitrate: media_file.bit_rate
• duration: media_file.variable_bit_rate
• filesize: media_file.file_size
• comment: media_file.comment
• created: media_file.created
• changed: media_file.changed
• lastscanned: media_file.last_scanned
• mb_release_id: media_file.mb_release_id
• mb_recording_id: media_file.mb_recording_id
• genre: media_file.genre
• year: media_file.year
• folder: media_file.folder
• lastplayed: media_file.last_played
• format: media_file.format
• playcount: media_file.play_count
• albumrating: user_rating.rating
• starred: starred_media_file.id

Syntax addition: Ranges

The following range queries can help a lot to narrow down media files, for both numerical and date types:

• field:..value means that field is at most value
• field:value.. means that field is at least value
• field:a..b means that field is between a and b

Syntax addition: Relative dates

Relative values can be supported:

• -1d (1 day ago)
• -2w (2 weeks ago)
• -3m (3 months ago)
• -1y (1 year ago)

Syntax addition: Negation

A search term can be negated by prefixing it with - or ^.

Syntax addition: Multiple occurences

If a field appears multiple times, these occurrences are grouped together with an OR boolean operator ; otherwise fields are linked with the AND boolean operator.

Negated fields would be grouped together separately, for example:

^artist:who ^artist:beatles artist:the

..is equivalent to artist contains 'the' AND artist contains neither "who" nor or "beatles".

Note that this is not compatible with how Beets does things (which is, default to "AND" everywhere).

Syntax addition: Partial dates

We could add support for partial dates (yyyy[-mm[-dd[ hh[:mm[:ss]]]).

Syntax addition: Exact string match

We can also add field:=value, meaning that field is exactly equal to value.

Syntax addition: Regular expressions

Regular expressions support can be added to the existing string comparison operators using something like field::regexp.

It is unclear to me now if SQL support on different databases is good enough, or if we should use our own regular expression engine so that things are consistent. From what I hear we may have to use our the latter.

Syntax addition: Full support for boolean operators

Boolean operators can work with the following priorities:

1. Terms within parentheses ((field:value field2:value2))
2. Multiple occurrences of the same field (field:value field:value2)
3. Group of terms (field:value field2:value2)
4. AND operator
5. OR operator

This way, the basic syntax keep on working, but boolean operators add additional possibilities.

Examples

Basic query:

genre:Rock year:2018 starred:true albumrating:5

More complex query with year and rating ranges, and multiple genre selection:

genre:Pop genre:Rock year:2010..2018 starred:true albumrating:3..

Find songs that are not starred or rated and were not recently played:

lastplayed:..-2m starred:false albumrating:0

[tesshucom]

Let's roughly design the whole picture.
By doing so, you can divide your requirements into stages, right?

OK.
I will reconsider according to your plan

[fxthomas]

It's a good idea to create a new, completely new advanced search screen, and then delete the legacy incomplete advanced search once it's complete. To be honest, it's not worth the reuse.

Sounds like a good idea. That's what I was thinking of doing too.

In terms of interface design order, the new Criteria and Results placed in the domain package will be the top priority. Since they already exist, a name like DetailsSearchCriteria and DetailsSearchResults might make sense. Criteria are necessary for design consideration of search, and Results are necessary for design consideration of screen design.

:+1:

You can think of SMART_PLAYLIST as a master and the old playlist table as a snapshot of SMART_PLAYLIST.

So this would need an additional column (dynamic?) in PLAYLIST to indicate that it is in fact dynamic?

Caching the results in the PLAYLIST table could be needed to improve performance, but I'm going to measure first.

[fxthomas]

Another thing I'm thinking about : I use a music collection management tool called Beets, which has a fairly nice query syntax for music management. How about reusing it, or implementing a subset?

[tesshucom]

Some ideas on the subject:
Add ability to use the new search query syntax from API (with a prefix, e.g. =)

We recommend that you start with a cautious approach to regular expressions, SQL, exact string matches and prefixes. Many music software features are based on specifications and implementations about 20 years ago, and backward compatibility cannot be removed. We need to find out if they are really good examples.

The ideas for regular expressions, SQL, exact string matches, and prefixes are based on a basic knowledge of databases. Airsonic uses a more sophisticated search engine, so I think it's a simpler solution.

https://github.com/jpsonic/jpsonic/releases/tag/v109.0.0

You can see an example of phrase search in my clone. Unlike previous methods, word order is considered, reducing the need for exact matches and prefixes.

Note that for large specifications like UPnP, there are only "contains" and "not contains" in the string condition. The reason why the guidelines did not recommend any other method was that they were logically less needed.

If you need to mimic some software, you can optionally add basic functions after creation. If you start by imitating some software, you will inherit the problems that the software has.

[tesshucom]

I imagine a flow like this for queries.
It is assumed that the following are required from the various demands currently issued.

Serializer and Deserializer can be created with ANTLR. The role of serializers and deserializers is simple. Same as serializer and deserializer that are often used to register dates and enums in the database with ORM.

Regarding this Query. I often see opinions equating to LuceneQuery, but that is not possible.
To be precise, it is possible only in limited circumstances. (For example, if we do not need a deserializer by design, and only use English.)

So you have to design the query as part of your application. Like most products that do things like this. Most of the smart lists are proprietary for each product. The smart list design mechanism is not very smart, because there is no such thing as a guideline.
If you want to avoid relying on the specific product specifications, UPnP's search query specifications are helpful. If you want a somewhat abstract and simple query. Most existing query specifications for existing products are completely dependent on the search impl spec, so it is important to be careful whether or not they are worth reference.

How you tie the query to the search functionality is a matter of future design. It could be converted to a SQL query or a LuceneQuery. We do not object to the recommendation of Beets, but it may add administrative overhead.

[fxthomas]

Serializer and Deserializer can be created with ANTLR.

[...] LuceneQuery, but that is not possible.

I think ANTLR is a good solution, re-using the Lucene query parser means we're effectively tied to it and its limitations.

I have a proof of concept up and running, so I know I can use it now.

UPnP's search query specifications are helpful.

Do you have a link to such a specification? I wasn't able to find it with a quick search.

[fxthomas]

I added a proposal for the query syntax to the top comment, let me know what you think.

Exact compatibility is explicitly _not_ a goal (we can change it if there is a better idea), but I was heavily inspired by Beets.

It should however be compatible with the basic "field" syntax used by Lucene, which is nice.

Note that for completeness I added boolean operators, but I think it's probably not needed.

For me the priority would be (in parentheses those that are probably not going to be in the first implementation):

• Basic syntax
• Range queries
• Relative dates
• Negation
• Multiple occurrences
• Exact string match
• Partial dates
• (Regular expressions)
• (Boolean operators)

[tesshucom]

Do you have a link to such a specification? I wasn't able to find it with a quick search.

There is a huge amount of data, but I think the following will help you with your search syntax.
I think it's much more convenient than referring to the external specifications of other products.

ContentDirectory:1 Service Template Version 1.01

2.5.5. A_ARG_TYPE_SearchCriteria

let me know what you think.

For now, I think it's a good idea to start with something simple. As you may have noticed, I take a rather conservative position. But that doesn't mean we are pessimistic about incorporating new ideas. I just think that evolution requires some steps.

• Knowing user needs is very important. The issues have been sorted out for that purpose.
• Is it possible to create the strongest tool by summing up user needs? It's neutral about whether it's right. The reason is that the idea of ​​the user is based on the existing tool, and the negative side of the existing tool is also held.
• The ultimate goal is user-friendly. There is no doubt!

A realistic idea is to distinguish between progressive goals and relay points. First, create what is practical with as few requirements as possible. It's a little more engineer's perspective than user's perspective. Expansion is easy, but the opposite is difficult. We may be careful not to carry a negative heritage.

mostly borrowed from [Beets]

I think the specification is redundant because it is written for explanation of Beets. It's a little simpler to create something similar. We should also avoid some of them.

• Numbers and ranges can be aggregated in relOp. relOp ::= ‘=’ | ‘!=’ | ‘<’ | ‘<=’ | ‘>’ | ‘>=’
• New **Op should be defined if needed.(Eg relative date)
• Define the fields and the **Op used for them

beets also supports regular expression matching for more advanced queries.(quoted from Beets)

The high-level and good features of regular expressions are for limited uses. Not everywhere gives great results ... I think we should redesign phrases and regular expressions.

As with UPnP, we recommend that string fields be one of the following:

stringOp ::= ‘contains’ | ‘doesNotContain’

I think that practical use can be secured by performing an approximate phrase search in the background. At least at first. (Verification should be separate. Just because UPnP didn't support extra **Op doesn't mean it wasn't well thought out.)

Regarding Boolean operators and nesting, I think that we can consider introducing it if we can express it well in GUI. (We don't enter the query directly... right?) And if analysis is possible with ANTLR.
Finally, I think the query length is finite (DB). We need to think about it a little.

It can support the following columns:

There is no opposition to the fact that there are many items to deal with. The main concern is the string search method. contains | doesNotContain can guarantee a certain degree of practicality and there is no technical lock-in.

Another thing I'm thinking about : I use a music collection management tool called Beets, which has a fairly nice query syntax for music management. How about reusing it, or implementing a subset?

I can understand my interest in a library that looks more useful. But search libraries are very complex. Update risk and bug risk are high. (This is the risk I'm afraid or have experienced.) The current DB + Lucene configuration is fairly typical and reliable in the long run. Even if not the best in a particular perspective.

I've confirmed issues of Beets, but the bug is primitive, such as a specific string crash. If you plan to use it directly, you should be prepared to encounter many defects that you have never encountered. I think that it is possible to some extent in the sense of referring to the query syntax.

By the way, if there is no limit, the development policy of phrase search with postgres is simple. Development is easy and performance is easy to maintain. Of course, that is not possible, so background options are limited. I think Beat is a little uncertain about its reliability. I'm not claiming that Lucene + DB is the best configuration!

[tesshucom]

There is already such a GUI :

Are you assuming an advanced version of such an input? If significantly simplified, the design will change. This seems to need confirmation first.