New in 7.9: Fulltext search

[mherger]

New in 7.9: Fulltext search

> Yes, thanks - after 55 minutes it finished full text search (for 420k
> mp3-files). I will now test the feature and report if it was only the
> initial scan that took so "long" or if it's the same with every
> (update-) scan. Great idea, by the way! :-)

How many stored playlists do you have? And how many tracks are in there
(approximately)? I think indexing playlists is brutally slow right now.
There definitely is some room for improvement in many places...

--

Michael

[JJZolx]

> At this time (v38612, updated via SVN just a few minutes ago), search
> appears to still be broken.

Broken in what way? Anything in the log file?

At the moment:
- The drop-down search fails to find anything.
- The quick search and the Search > Artists/Albums/Songs/Playlists form only finds artists. All other search categories come up empty.
- Advanced search is fine.

I have 'search within words' enabled, but I've tried partial searches, start-of-title searches, and exact title searches and all of them fail. The only thing I see in the server log is from the scheduled new & changed scan that ran last night:

[14-10-24 04:12:13.1788] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::db do failed: no such function: CONCAT_CONTRIBUTOR_ROLE [for Statement "
		INSERT IGNORE INTO fulltext (id, type, w10, w5, w3, w1)
			SELECT tracks.id, 'track', 
			-- weight 10
			IFNULL(tracks.title, '') || ' ' || IFNULL(tracks.titlesearch, '') || ' ' || IFNULL(tracks.customsearch, '') || ' ' || IFNULL(tracks.musicbrainz_id, ''),
			-- weight 5
			IFNULL(tracks.year, '') || ' ' || GROUP_CONCAT(albums.title, ' ') || ' ' || GROUP_CONCAT(albums.titlesearch, ' ') || ' ' || GROUP_CONCAT(genres.name, ' ') || ' ' || GROUP_CONCAT(genres.namesearch, ' '),
--			IFNULL(tracks.year, '') || ' ' || GROUP_CONCAT(contributors.name, ' ') || ' ' || GROUP_CONCAT(contributors.namesearch, ' ') || ' ' || GROUP_CONCAT(albums.title, ' ') || ' ' || GROUP_CONCAT(albums.titlesearch, ' ') || ' ' || GROUP_CONCAT(genres.name, ' ') || ' ' || GROUP_CONCAT(genres.namesearch, ' '),
			-- weight 3 - contributors create multiple hits, therefore only w3
			CONCAT_CONTRIBUTOR_ROLE(tracks.id, GROUP_CONCAT(contributor_track.contributor, ','), 'contributor_track') || ' ' || IFNULL(comments.value, '') || ' ' || IFNULL(tracks.lyrics, '') || ' ' || IFNULL(tracks.content_type, '') || ' ' || CASE WHEN tracks.channels = 1 THEN 'mono' WHEN tracks.channels = 2 THEN 'stereo' END,
			-- weight 1
			printf('%i', tracks.bitrate) || ' ' || printf('%ikbps', tracks.bitrate / 1000) || ' ' || IFNULL(tracks.samplerate, '') || ' ' || (round(tracks.samplerate, 0) / 1000) || ' ' || IFNULL(tracks.samplesize, '') || ' ' || tracks.url
			 
			FROM tracks
			LEFT JOIN contributor_track ON contributor_track.track = tracks.id
--			LEFT JOIN contributors ON contributors.id = contributor_track.contributor
			LEFT JOIN albums ON albums.id = tracks.album
			LEFT JOIN genre_track ON genre_track.track = tracks.id
			LEFT JOIN genres ON genres.id = genre_track.genre
			LEFT JOIN comments ON comments.track = tracks.id
		
			GROUP BY tracks.id;
	"]
[14-10-24 04:12:13.1795] Slim::Schema::Storage::throw_exception (122) Backtrace:

   frame 0: Slim::Utils::Log::logBacktrace (C:/Program Files (x86)/Logitech Media Server 7.9/server/Slim/Schema/Storage.pm line 122)
   frame 1: Slim::Schema::Storage::throw_exception (C:\Program Files (x86)\Logitech Media Server 7.9\server\CPAN/DBIx/Class/Storage/DBI.pm line 1006)
   frame 2: DBIx::Class::Storage::DBI::__ANON__ (C:/Program Files (x86)/Logitech Media Server 7.9/server/Slim/Plugin/FullTextSearch/Plugin.pm line 190)
   frame 3: Slim::Plugin::FullTextSearch::Plugin::_rebuildIndex (C:/Program Files (x86)/Logitech Media Server 7.9/server/Slim/Plugin/FullTextSearch/Plugin.pm line 170)
   frame 4: Slim::Plugin::FullTextSearch::Plugin::_triggerIndexRebuild (C:/Program Files (x86)/Logitech Media Server 7.9/server/Slim/Utils/Timers.pm line 266)
   frame 5: (eval) (C:/Program Files (x86)/Logitech Media Server 7.9/server/Slim/Utils/Timers.pm line 266)
   frame 6: Slim::Utils::Timers::__ANON__ (C:/Program Files (x86)/Logitech Media Server 7.9/server/Slim/Networking/IO/Select.pm line 168)
   frame 7: (eval) (C:/Program Files (x86)/Logitech Media Server 7.9/server/Slim/Networking/IO/Select.pm line 168)
   frame 8: Slim::Networking::IO::Select::loop (C:\Program Files (x86)\Logitech Media Server 7.9\server\slimserver.pl line 715)
   frame 9: main::idle (C:\Program Files (x86)\Logitech Media Server 7.9\server\slimserver.pl line 665)
   frame 10: main::main (C:\Program Files (x86)\Logitech Media Server 7.9\server\slimserver.pl line 1200)

[14-10-24 04:12:13.1822] Slim::Utils::Timers::__ANON__ (273) Error: Timer Slim::Plugin::FullTextSearch::Plugin::_triggerIndexRebuild failed:

[mherger]

New in 7.9: Fulltext search

> [14-10-24 04:12:13.1788] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::db do failed: no such function: CONCAT_CONTRIBUTOR_ROLE [for Statement "

Huh!?! Did you disable the plugin without restarting or something? Are
you running from the source or the Windows binary? It complains about a
missing function which is defined in the plugin itself... but that code
would not even be called without the function being initialized. Puzzling...

--

Michael

[JJZolx]

Huh!?! Did you disable the plugin without restarting or something? Are
you running from the source or the Windows binary?

The plugin has not been disabled. This is run from the source.

[mherger]

New in 7.9: Fulltext search

> The plugin has not been disabled. This is run from the source.

Ok, I have an idea what could have caused this. Sometimes the database
handles are closed. In this case I need to re-register those custom
functions.

I've applied a change which should do this. Please give it a try.

--

Michael

[JJZolx]

Ok. Not quite as broken, but still broken.

Then biggest issue is that search within strings no longer works. I would typically use searches such as 'nny c' to find 'Johnny Cash' or 'slow' to find Clapton's 'Slowhand' album. But it only finds full words.

Searching in the quick search of the title bar for 'johnn' finds nothing, but 'johnny' works.

Searching in the quick search form for 'johnn' shows the following, listing no artist or song names. Clicking 'more' gives a similarly blank list. Clicking 'All Songs' does show a list of songs (with no artist or album shown). If a full word such as 'johnny' is used, it does list artists, albums and songs.

Artists: 1 / 20: More...
All Songs
Albums: 1 / 3: More...
All Songs
Songs: None
Playlists: None

[JJZolx]

I ran a full scan after the last update. Here's the scanner log.

[mherger]

New in 7.9: Fulltext search

> Then biggest issue is that search within strings no longer works. I
> would typically use searches such as 'nny c' to find 'Johnny Cash' or
> 'slow' to find Clapton's 'Slowhand' album. But it only finds full
> words.

Are you running the latest? I changed that behaviour again (it doesn't respect the pref yet - that's on the todo list)

> Searching in the quick search -form- for 'johnn' shows the following,
> listing no artist or song names. Clicking 'more' gives a similarly blank
> list.

Yeah, I've seen similar things... concentrated too much on performance with a large library today... Will review this.

[gharris999]

Ok, searching for "Mozart K.466"

..and expecting to find 2 tracks (from two Mozart different albums) with the title:

"Piano Concerto No. 20 in d, K.466 (1785) - 1 Allegro"

..instead FTS is returning 75 songs, all but two seemingly unrelated to the search. I'm a bit flummoxed as to why "Mozart K.466" should return a track like:

"11 Andante, ma non troppo in Bb" from the album "Piano Bagatelles - Stephen Kovacevich" by "Beethoven, L"

Are there any debugging options with FTS? I.e. can we log the SQL query statement?

Can FTS be made optional?

[gharris999]

..or could FTS be made to search for littoral strings when the input is quoted?

[mherger]

New in 7.9: Fulltext search

> Are there any debugging options with FTS? I.e. can we log the SQL query
> statement?

database.sql? If this doesn't help, then I'll add some.

> Can FTS be made optional?

Just disable the plugin. See the announcement posting ;-).

But before you do so I'd be interested in your library.db. I'd like to
understand why you're getting the seemingly wrong results you see.

--

Michael

[gharris999]

...I'd be interested in your library.db. I'd like to understand why you're getting the seemingly wrong results you see.

Ok, my db files (artwork.db, cache.db, imgproxy.db, library.db, persist.db) are posted at:

http://www.hegardtfoundation.org/slimstuff/lms_db_files.zip

Thanks..

[JJZolx]

Back to the original premise...

If I'm doing a basic search and I search for 'james', why would I want to see all 356 albums in my library that have a track on them by anyone named James? I would expect it to find the following two albums and no others:

The Best of James Bond: 30th Anniversary Collection (1993) by Various Artists
The Best of Joe Walsh and The James Gang (1969-1974) (1974) by Joe Walsh

[mherger]

New in 7.9: Fulltext search

> ..or could FTS be made to search for littoral strings when the input is
> quoted?

I think it should do so. See the "Phrase queries" section under
http://www.sqlite.org/fts3.html#section_3.

But I might actually have a bug breaking this. I'll look into this.

--

Michael

[mherger]

New in 7.9: Fulltext search

> If I'm doing a basic search and I search for 'james', why would I want
> to see all 356 albums in my library that have a track on them by anyone
> named James?

Because you've forgotten who covered that track written by James Taylor.

> I _would_ expect it to find the following, but nothing
> else:
>
> The Best of James Bond: 30th Anniversary Collection (1993) by Various
> Artists
> The Best of Joe Walsh and The James Gang (1969-1974) (1974) by Joe Walsh

Your choice. Turn off FTS.

--

Michael