Quote from: davideleo on 2023-08-03 20:48:45

I tried to insert a record with the SQLite console, but it didn't work.

Of course, not. The lookup tables are not meant to be filled by the user. There is a reason why the tags database is not exposed to the user. The values in these table require a specific format.

I was expecting the lookup value tag to be a virtual tag,

but if I understand correctly, the only way to insert values in any field of a lookup table is by copying it from an actual file tag.

An actual tag can hold inconsistent values, tough. I made some tests and I've seen that once a lookup value is copied in the SQLite db, it is not overwritten by subsequent attempts to copy a different value.

Nevertheless, the actual tag has precedence over the SQLite tag, so how can I grant referential integrity in the properties dialog?

Also, how do I modify an already existing lookup value in the lookup table?

• create 2 SQLite tags "artist" and "genre" without changing the default settings.
• define a lookup table "artist" with the key tag "artist".
• edit the SQLite tag "genre", set the lookup table name to "artist" and keep the automatically set lookup table column "genre".

• initialize the tag database by copying the file tags to it using the context menu: "Tagging -> SQLite tags -> Copy cached tags to the  SQLite db. If you don't have any other tag info filters active you could also use "Copy unfiltered tags to the SQLite db". The result will be in general the same. All three files should now have the genre "Pop" while actual file tags for track1 and track2 are still "Rock" as you can see in the details section for each file
• open the properties dialog for track2 and change the value of genre to "Pop Rock". This becomes now an individuell tag value for track2 as you also can see in the details section: genre is listed under "Tag values" while it is listed under "Lookup values" for the other two tracks which have still the genre "Pop"
• open the properties dialog for track1 and change the value of genre to "@@Rock". Now the genre lookup value for the artist "Rockband" is "Rock", so the genre for track3 is now also "Rock", while it is still "Pop Rock" for track2
• open the properties dialog for track2 and remove the genre tag value for it. Now the genre value for track2 becomes the lookup value "Rock"

In a separate topic I've asked if it is possible to 'automatically' rate an album based on the individual songs ratings - some kind of averaging - and I've been suggested to ask my question here since sqlite seems to be a powerful toolbox, so here I am.

• Go to 'File > Preferences > Media Library > SQLite utilities > Playlist table' click 'Add defaults', then add the tag that you want to store the album rating in (just put the name in the Name property and click OK). The following sql assumes albumRating.
• Drag files from a number of albums into a new playlist.
• Select 'Library > SQLite console' and a popup window should appear.
• Copy the following code into the window and Execute it.

select album,
       [album artist],
       date,
       avg(rating) albumrating
  from Playlist
  where playlist_index = active_playlist()
  group by 1, 2, 3;

However I'm wondering what to do about songs I've rated 1/5 because usually those are intro/interlude/outro tracks I want to skip with foo_skip when listening to shuffled playlist. They could degrade the album rating though I do not care about them. Anyway it's something I could consider later

drop table if exists albumSummary;
create table albumSummary as
  select album,
         [album artist],
         date,
         round(avg(rating), 2) albumrating
  from Playlist
  where playlist_index = active_playlist()
        and rating <> 1
  group by 1, 2, 3;

update PlaylistUpdatable as a
  set albumrating = b.albumrating
  from albumSummary as b
  where       playlist_index = active_playlist()
        and a.album          = b.album
        and a.[album artist] = b.[album artist]
        and a.date           = b.date;
drop table if exists albumSummary;

otherwise, maybe you could base it on the length of the tracks.

round(avg(rating), 2) albumrating

I have basic knowledge in scripting and none in sql syntax but I would have expected some iteration loop but can't see any obvious one, where is the magic ?

I've just tested your script and it works great, many thanks!

I have basic knowledge in scripting and none in sql syntax but I would have expected some iteration loop but can't see any obvious one, where is the magic ? Definitely I need to read some sql 101 

Quote from: SimBun on 2023-09-16 18:53:38

otherwise, maybe you could base it on the length of the tracks.

I guess the part of the script to be modified would be

Code: [Select]

round(avg(rating), 2) albumrating

select *
  from Playlist
  where playlist_index = active_playlist()

The section you highlighted is the code that computes and rounds the rating, so if you wanted to change the logic it would go there.
If you run the following code it'll show you everything you could reference in your calculation - although likely it's just length_seconds that would be of interest.

Quote from: SimBun on 2023-09-17 09:30:58

The section you highlighted is the code that computes and rounds the rating, so if you wanted to change the logic it would go there.
If you run the following code it'll show you everything you could reference in your calculation - although likely it's just length_seconds that would be of interest.

Finally I've been thinking about averaging the rating weighted on track length (sometimes I do have instrumental only tracks which can be >3 mins on which i defined a rating of 1)
I tried to play with length_seconds and with the sum() function to get total album length but without succes so far, still investigating 

sum(length_seconds) as name_of_calculated_column

  select album,
         [album artist],
         date,
         round(avg(rating), 2) albumrating,
         sum(length_seconds) as total_album_length_seconds
  from Playlist
  where playlist_index = active_playlist()
        and rating <> 1
  group by 1, 2, 3;

drop table if exists albumSummary;
create table albumSummary as
  select album,
         [album artist],
         date,
         round(sum(rating*length_seconds)/sum(length_seconds), 2) as albumrating
  from Playlist
  where playlist_index = active_playlist()
  group by 1, 2, 3;

update PlaylistUpdatable as a
  set albumrating = b.albumrating
  from albumSummary as b
  where       playlist_index = active_playlist()
        and a.album          = b.album
        and a.[album artist] = b.[album artist]
        and a.date           = b.date;
drop table if exists albumSummary;

Thanks, I hope I got it now. Here should be the final code:

...
       padr(round(sum(rating*length_seconds)/sum(length_seconds),2),4,'0') as albumrating
...

- As far as I have tested this saves the tag to the files; is it possible to save them on the database only?

- Is it possible to automatically re-calculate this tag when new files are added to the library?

Code: [Select]

...
       padr(round(sum(rating*length_seconds)/sum(length_seconds),2),4,'0') as albumrating
...

...
       format('%.2f',round(sum(rating*length_seconds)/sum(length_seconds),2)) as albumrating
...

Yes, with the tagging functionality of foo_sqlite by defining the tag as an SQLite tag.

Only indirectly by using the possibility to materialize the virtual media library table. You could add one ore more triggers to the materialized table which are doing the calculation. Not only when adding new files to the library, also when updating the rating tag. But these triggers need to be carefully designed to avoid endless recursions or too long running updates.

A better solution would be to use the format() function:

Code: [Select]

...
       format('%.2f',round(sum(rating*length_seconds)/sum(length_seconds),2)) as albumrating
...

Quote

Only indirectly by using the possibility to materialize the virtual media library table. You could add one ore more triggers to the materialized table which are doing the calculation. Not only when adding new files to the library, also when updating the rating tag. But these triggers need to be carefully designed to avoid endless recursions or too long running updates.

Too complex for me at this moment; I barely understand the basics right now. I appreciate the component capabilities but functionality/docs are abstract as hell (for me).

I tried the format function as first solution and it didn't work at all for me. Maybe I didn't use it properly though.
In docs I always find that SQL format works like this:
FORMAT (value, format [, culture])

Buy you are reversing the args there (?) Just checking now, the reversed version works xd so that was the problem.

Does this plugin have any documentation?

but the question mark in the program leads to a file not found error

Quote from: Amazing Mr. X on 2023-11-12 23:41:09

Does this plugin have any documentation?

Yes via the menu "Help -> SQLite utilities".

Quote from: Amazing Mr. X on 2023-11-12 23:41:09

but the question mark in the program leads to a file not found error

If you mean the question mark on the titlebar of the preferences dialog, it's indeed not implemented for the main entry. However, for all four subpages clicking on the question mark will lead you to the relevant page of the documentation.

Where do I download the documentation files?

They are shipped with the component and you can find them in the components folder in your profile folder.