PDA

View Full Version : Virtual Libraries - help beginner please



afriend
2018-08-12, 05:46
Hello.

I'm trying to use Virtual Libraries to rebuild a couple of menus I had previously created with Erland's plugin. I don't have any experience with SQL but I'm quite willing to learn.
So if I understand all this correctly, I have to 'master' 3 steps to get my own custom menus/virtual libraries:


1) use the Library demo as a starting point to create the basic Plugin.pm, install.xml and strings.txt I need for my own plugin [DONE]

2) figure out the correct SQL (query) code to be used in the plugin in each case:
.
a comments based menu group "AAA" with submenus artists, albums, genres, years, songs for all tracks that include comment AAA but not comment BBB and not comment CCC [DONE]
By "songs" I mean a submenu for virtual libraries (with few songs) which when clicked upon shows only the tracks (like in the Favorites menu). [DONE]
.
a menu [I]group "Compilations" with submenus artists, albums, genres, years (for all compilations, except tracks that contain BBB in the comment tag) [DONE]
.
a menu "Random Compilations" - exactly like the built-in random albums menu, only for compilations = 20 random compilation albums [DONE]
.
a menu group "3 Stars+" with submenus artists, albums, genres, years for all tracks rated 3 stars or higher [DONE]


3) create/group the menus in LMS
.
group all menus belonging to a virtual library under one top level menu (folder) with the library name [gave up, HELP REQUIRED]

Example:
AAA > artists
> albums
> genres
> years
> songs
.
maybe a folder for collecting/grouping some (not all) virtual library menu groups [2DO]

Example:

MyCustomMenus Collection > VirtualLib01 > artists
> albums
> ...

> VirtualLib03 > artists
> albums
> ...

.


Here's the code of my Plugin.pm so far (filtering for songs with AAA but not BBB or CCC in the comments tag, creating 4 menus):

EDIT: I will update the code below as or if I figure out how to do the stuff from my bucket list above. Maybe it will save somebody else interested in creating virtual libraries + menus some time.


package Plugins::MyCustomMenus::Plugin;
use strict;

use base qw(Slim::Plugin::Base);

use Slim::Menu::BrowseLibrary;
use Slim::Music::Import;
use Slim::Utils::Log;

sub initPlugin {
my $class = shift;

foreach ( {

id => 'AAA',
name => 'AAA',
sql => qq{
INSERT OR IGNORE INTO library_track (library, track)
SELECT '%s', tracks.id
FROM tracks
LEFT JOIN comments comments ON comments.track = tracks.id
WHERE
(comments.value LIKE '%% AAA%%' OR
comments.value LIKE 'AAA%%') AND
tracks.audio = 1
AND not exists(select * FROM comments
where
comments.track=tracks.id AND
(comments.value LIKE '%%BBB%%' OR
comments.value LIKE '%%CCC%%')
)
GROUP by tracks.id
},
},{

id => 'COMPIS',
name => 'Compilations',
sql => qq{
INSERT OR IGNORE INTO library_track (library, track)
SELECT '%s', tracks.id
FROM tracks,albums
LEFT JOIN comments comments ON comments.track = tracks.id
WHERE
albums.id=tracks.album AND
albums.compilation=1 AND
tracks.audio = 1

and not exists(select * FROM comments
where
comments.track=tracks.id AND
comments.value LIKE '%%BBB%%'
)

GROUP by tracks.id
},

} ) {
Slim::Music::VirtualLibraries->registerLibrary($_);
Slim::Music::VirtualLibraries->rebuild($_->{id});
};

my @menus = ( {
name => 'AAA_ARTISTS',
icon => 'html/images/artists.png',
feed => \&Slim::Menu::BrowseLibrary::_artists,
id => 'AAA_TracksByArtist',
weight => 25,
virtualID => 'AAA',
},{
name => 'AAA_ALBUMS',
icon => 'html/images/albums.png',
feed => \&Slim::Menu::BrowseLibrary::_albums,
id => 'AAA_TracksByAlbum',
weight => 25,
virtualID => 'AAA',
},{
name => 'AAA_GENRES',
icon => 'html/images/genres.png',
feed => \&Slim::Menu::BrowseLibrary::_genres,
id => 'AAA_TracksByGenres',
weight => 25,
virtualID => 'AAA',
},{
name => 'AAA_YEARS',
icon => 'html/images/years.png',
feed => \&Slim::Menu::BrowseLibrary::_years,
id => 'AAA_TracksByYears',
weight => 25,
virtualID => 'AAA',
} );

foreach (@menus) {
Slim::Menu::BrowseLibrary->registerNode({
type => 'link',
name => $_->{name},
params => { library_id => Slim::Music::VirtualLibraries->getRealId($_->{virtualID}) },
feed => $_->{feed},
icon => $_->{icon},
jiveIcon => $_->{icon},
homeMenuText => $_->{name},
condition => \&Slim::Menu::BrowseLibrary::isEnabledNode,
id => $_->{id},
weight => $_->{weight},
cache => 1,
});
}
$class->SUPER::initPlugin(@_);
}
1;

EDIT: I updated the code. As far as I can tell my mistake was to omit the comma right after the sql query (a bit counterintuitive but it doesn't work without it).
Next step: How do I manage to make all 5 menus (artists, albums, genres, years, songs) submenus of 1 menu group AAA? It is possible I think (because one of Erland's plugins used to do that) but I don't know how.

Thank you very much for your much needed help.

mherger
2018-08-12, 21:15
Did you try to define your libraries using the Advanced Search in the
web UI?

--

Michael

DJanGo
2018-08-12, 21:29
comments.track=tracks.id and
comments.value like '%BBB%' and
comments.value like '%CCC%'

you need to change a single % to double %% like this

comments.track=tracks.id and
comments.value like '%%BBB%%' and
comments.value like '%%CCC%%'

and another little tip - first try to add a single library and after you find out -add more with the plugin.

afriend
2018-08-13, 07:18
Did you try to define your libraries using the Advanced Search in the
web UI?

Yes. But as you and others have explained previously, there are limits to what the UI generated library views can do. For example, I don't think that I can't use it to search the comment tag for something like: include AAA + exclude BBB + exclude CCC.
So while I don't actually have the time and don't know much about coding at all, I would love to create some simple menus with my own little 'plugin'. Just need a little help.

afriend
2018-08-13, 07:29
another little tip - first try to add a single library and after you find out -add more with the plugin.

Thank you. That's exactly what I'm trying/failing to do. That in my post above was more like my road map for the menus I'd like to create eventually.
So first, I concentrate on a library view with all the tracks whose comments tag includes AAA but does not have BBB and not CCC in the comments tag. That's what the code above was for.
But even with the %% I don't get the result I want.

I'm not even sure the problem is the SQL query at this point because no matter what I change/put in the SQL query I always see the same number of entries in the menus and the corresponding library view.
There's something I'm missing here... and I just can't figure it out. It's like LMS doesn't reflect my changes/updates I make.

DJanGo
2018-08-13, 08:29
did you restart lms after these changes?

Its a long time ago - so i am not 100% sure - but i think a rescan was needed and i used the playlist version.
Not sure about that.

afriend
2018-08-13, 08:49
After every code change I restart LMS. I'm not sure a rescan is needed. I can create a new 'simple' library with the advanced search UI that shows up immediately.
I suspect either I have some incorrect or missing code in my example or LMS caches the whole thing and that's why I always get the same result. Bit of a bummer...

DJanGo
2018-08-13, 08:49
i cant tell to your #2 question -but #1

1) where are the mistakes in my SQL?

seems fine to me...

What exactly do you want aaa is really aaa or also something like baaad?

I would open a sqlite shell and run your sql statement with -limit 1

sqlite> select tracks.id from tracks LEFT JOIN comments comments ON comments.track = tracks.id where comments.value LIKE 'aaa' limit 1;

That should show one match.
copy that value
add it in a browserlink
http://lms:9000/trackinfo.html?sess=that_id

you should see a track with the comment aaa
works for me

mherger
2018-08-13, 08:57
> Yes. But as you and others have explained previously, there are limits
> to what the UI generated library views can do. For example, I don't
> think that I can't use it to search the comment tag for something like:
> include AAA + exclude BBB + exclude CCC.
> So while I don't actually have the time and don't know much about coding
> at all, I would love to create some simple menus with my own little
> 'plugin'. Just need a little help.

But you should be able to filter for AAA. Save that as a library. Then
you can edit the generated SQL. That should be much easier than to come
up with all of it yourself, hack a plugin etc.

--

Michael

afriend
2018-08-13, 10:47
>But you should be able to filter for AAA. Save that as a library. Then
you can edit the generated SQL. That should be much easier than to come
up with all of it yourself

That's what I did. I used some SQL from the virtualLibraries.prefs and some created by Erlands plugins. I can tell you, otherwise I wouldn't have been able to come up with anything at all.
So I finally managed to get it working (I updated the code in the first post). I think it was all about a missing comma after the sql query.

Before creating other virutal libraries I will now try to group the menus because otherwise the top level menu will become extremely crowded/overloaded.

One oddity: in the artists menu for this virtual ibrary, some artists have - in addition to the correct albums - empty albums listed (when you click on them, it says empty). That happens also if I create such a library with the advanced search UI. Any idea how to get rid of those?

DJanGo
2018-08-13, 11:00
One oddity: in the artists menu for this virtual ibrary, some artists have - in addition to the correct albums - empty albums listed (when you click on them, it says empty). That happens also if I create such a library with the advanced search UI. Any idea how to get rid of those?

please check these artists for "member of a sampler/compilation"
Do you use one or two lists? -> settings/my music / browse artists
I believe your using two - try one and these emptry artists are gone.

afriend
2018-08-13, 12:30
please check these artists for "member of a sampler/compilation"
Do you use one or two lists? -> settings/my music / browse artists
I believe your using two - try one and these emptry artists are gone.

I think you are right. I haven't checked all of the empty album entries but they seem to be compilations that do include a song by the chosen artist but just not one with AAA in the comments tag.

As for the LMS settings, I don't know what configurable in "single, configurable artists list" means but I've always had only one single list for artists and album artists alike. I have toggled between the 2 available settings but that doesn't make the "ghost" albums disappear.
I'll probably table this for now so I don't get distracted from the virtual library tasks. I might require a separate thread.

afriend
2018-08-16, 03:35
#1
Ok, so I have almost finished creating the virtual libraries I need.
For the "20 Random Compilation Albums" (= built-in "20 Random Albums" for compilations) I could only come up with a workaround.
I've tried creating a virtual library for that but couldn't figure out the exact SQL query (using albums.id or what not) to get it working, however many SQL query variations I tried. The only code that worked gave me 20 random compilation albums with just 1 track in it = 20 random tracks.
I know it's because it's on the track and not album level but just couldn't fugure it out.
And - it didn't rebuild the menu when clicking on it again.


sql => qq{
INSERT OR IGNORE INTO library_track (library, track)
SELECT '%s', tracks.id
FROM tracks
JOIN albums ON tracks.album = albums.id
WHERE albums.compilation=1
AND tracks.audio = 1
AND not exists(select * from genre_track,genres
where
genre_track.track=tracks.id and
genre_track.genre=genres.id and
genres.name in ('Classical','Soundtracks'))

group by albums.id
order by random()

limit 20
},
rebuildOnSelect => 1


Anyway, so I tried this workaround: I used the virtual library that gives me all compilation albums and I added mode => 'randomalbums' and sort => 'random' to the code that creates the menu:


Slim::Menu::BrowseLibrary->registerNode({
type => 'link',
name => 'COMPILATIONS_RANDOM_20_ALBUMS',
params => {library_id => Slim::Music::VirtualLibraries->getRealId('COMPIS_with_EXCLUDED_GENRES'),
mode => 'randomalbums',
sort => 'random'},
feed => \&Slim::Menu::BrowseLibrary::_albums,
icon => 'html/images/albums.png',
jiveIcon => 'html/images/albums.png',
homeMenuText => 'COMPILATIONS_RANDOM_20_ALBUMS',
condition => \&Slim::Menu::BrowseLibrary::isEnabledNode,
id => 'COMPILATIONS_RANDOM_20_TracksByAlbum',
weight => 90,
cache => 1,
});


I'd be ok with this workaround but it always gives me 50 albums.
Is there a way to insert a limit (20 albums) into this workaround?

#2
But now comes the really hard part: with all those menus on the top level it's getting more and more crowded. And while I can play around and do some trial and error with SQL, there's no way I can do the same with perl.
I took a look at the code of some plugins, especially Erland's Custom Libraries which has a part in it that makes all menus (artists, albums...) of a virtual library submenus of a top level menu with the name of the virtual library. But that menu grouping code is so beyond what I can do. I just couldn't get it to work.

So if you know how to group menus together the way I described in the opening post (https://forums.slimdevices.com/showthread.php?109334-Virtual-Libraries-help-beginner-please&p=918401&viewfull=1#post918401), if you have some working code that I can easily adjust to my needs, I would be very glad to see it here.
From my many hours of tinkering with virtual libraries, I can say it's not easy but ultimately rewarding because it allows finer control and will hopefully still work by the time Erland's plugins will have stopped working. Thank you very much for your help.

mherger
2018-08-16, 05:27
> *And* - *it didn't rebuild the menu when clicking on it* again.

There's no mechanism to achieve this.

> rebuildOnSelect => 1

That flag you must have invented yourself. Or I'm victim of my
forgetfulness again... I didn't find it in all of the LMS code base.

> Anyway, so I tried *this workaround*: I used the virtual library that
> gives me all compilation albums and I added *mode => 'randomalbums'* and
> *sort => 'random'* to -the code that creates the menu:-

You're definitely exploring new ground here! :-) Great, if that works!

But the 'order by...' is irrelevant in the insert. It's only relevant
when querying data.

> I'd be ok with this workaround but it always gives me *50* albums.
> Is there a way to insert a limit (*20* albums) into this workaround?

I guess that's just the default number of items per page.

--

Michael

afriend
2018-08-20, 05:54
So I gave up on the idea of having a menu group for the menu I've created. It's too difficult for me. If somebody digs up this thread at some time and get this working, please post your code here.

One final (I hope) question regarding the genre menu I've created for compilations.
The menu levels are like this:

Compis by Genre (Top Level Name) > genres (where you select a genre) > artists (it shows you all artists in this genre, you select all albums) > all albums

How can I eliminate the artists menu level and make it go straight from genres to all albums from this genre?

afriend
2018-08-22, 06:28
Uh, and I'd like to include my own icons in the plugin 'bundle' for Web UI and iPeng:
what's the correct path to use in the menu parameters?

icon => '???????????.png'