HydrogenAudio

foo_sqlite is a foobar2000 component for viewing the contents of the media library and the playlists by using SQL queries. Additionally, foo_sqlite provides the possibility to store tag information in a database by defining attributes for each tag individually.

Upgrade Note: If you're upgrading from an older version than 3.0.0, it is highly recommended to backup your current configuration before. If you are also using foo_uie_sql_tree you must upgrade this component also.

Prerequisites:

• foobar 2000 2.0+

Download (https://www.foobar2000.org/components/view/foo_sqlite)
Version history (https://www.foobar2000.org/components/view/foo_sqlite/releases)

Thank you for this component.

Question: Why are SELECT statements not case sensitive? A query like:

select composer from medialibrary where composer IS 'LUDWIG VAN BEETHOVEN'

still returns rows with 'Ludwig van Beethoven'.

Because the default collation of the columns of the MediaLibrary table is NaturalNoCase. You can change the collation for each column in the preferences : Media Library -> SQLite viewer -> MediaLibrary table to Binary, if you want to have always a case sensitive comparison, or you could rewrite your query as:

select composer from medialibrary where composer IS 'LUDWIG VAN BEETHOVEN' collate binary

This won't return "Ludwig van Beethoven" anymore.

Thank you, this solves it. I didn't think of collation.

How can I implement my own icons?
Is there an example?

How can I implement my own icons?
Is there an example?

There are not any icons in foo_sqlite. Do you mean perhaps foo_uie_sql_tree?

Oh sorry!!
Yes, I mean foo_uie_sql_tree!
I can not view custom icons there.
I Have already set the path under 'Config' + 'Advanced'

What is the content of the path=
Only custom icons, which are placed into a bitmap file with a height of 16px and a with of multiple of 16px, for example a bitmap file with 16x48 pixels would contain 3 icons.

Thank you very much for this info!
Can you get me an example?
That would help me a lot.
Thanks for the quick replies.

First of all you need a set of icons with the dimensions 16x16 px. With an image processing application you can combine these icons to one image file and save it as BMP file.

This can easily done with IrfanView:
- open "Image -> Create panorama image ..."
- Click on "Add images" and add your icon files
- Click on "Create image" and save the result as "BMP - Windows Bitmap".
- Add the full path of the created image to foobar2000 under "Preferences -> Advanced -> Tools -> SQL tree -> Icon file paths". You can add multiple of such images by separating their paths with |.
- Restart foobar2000 to get the images loaded.

Thank you very much for this instruction!!
Now, I can see my own Icons in the SQL-Tree!  :)

Are there any examples on how to select songs from a playlist?

What I want to do is generate a playlist C which contains every song in playlist B, except the songs which are in playlist A.

Are there any examples on how to select songs from a playlist?

What I want to do is generate a playlist C which contains every song in playlist B, except the songs which are in playlist A.

So I also got foo_uie_sql_tree which comes with an SQL statement creating a "Playlist" table, which I queried.

Figured out that comparing filenames between playlists works best (since some of the tags are empty) and the resulting SQL statement is:

SELECT filename FROM Playlist WHERE playlist_index=3
EXCEPT
SELECT filename FROM Playlist WHERE playlist_index=2

I have no clue how to create a Playlist from SQL, but foo_uie_sql_tree allows for creation of Playlists from SQL statements.

I have no clue how to create a Playlist from SQL, but foo_uie_sql_tree allows for creation of Playlists from SQL statements.

First you neead to create a virtual table, which allows modifying playlists:

CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split,playlist);

Then you can add items to a playlist (T), which belong to one playlist (A) but don't belong to another playlist (B) with this statement:

INSERT INTO Playlist_Updatable (metadb_handle,playlist_name)
SELECT metadb_handle,'Playlist T'
FROM Playlist a
WHERE playlist_name='Playlist A'
  AND NOT EXISTS (SELECT 1
                  FROM Playlist
                  WHERE playlist_name='Playlist B'
                    AND filename=a.filename)

If the playlist 'Playlist T' does not exist, it will be created (unless it was already existing and was deleted before the insert statement was executed. This is a bug and will be fixed with the next release).

More infomation for this topic you can find here (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/MetaDb_Module.html).

There is a bug in the online documentation at http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html). The links to the help pages are absolute file:// URLs. Relative links would work better. ;)

Doesnt work.

I have a small graphical problem with "SQL Tree"
Whenever "Database activity ..." appears at the bottom, the graphical view of the vertical scroll bars will briefly change.
Is this a known problem?

There is a bug in the online documentation at http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html). The links to the help pages are absolute file:// URLs. Relative links would work better. ;)

Thanks for the hint. Of course, the links should've been relative. Will be fixed soon.

Doesnt work.

What does not work?

I have a small graphical problem with "SQL Tree"
Whenever "Database activity ..." appears at the bottom, the graphical view of the vertical scroll bars will briefly change.
Is this a known problem?

This has again nothing to do with foo_sqlite. But to answer the question: It's not a problem, it's intended. During database activities, the scrollbars will be disabled.

I was trying a couple of things but I was not able to achieve the following:
I wanted to list and insert all the content from an play list A which is not part of media library.
How to achieve this?

Thanks in advance.

I wanted to list and insert all the content from an play list A which is not part of media library.
How to achieve this?

The listing can be done with this query:

SELECT *
          FROM Playlist p
          WHERE playlist_name='A'
               AND NOT EXISTS (SELECT 1
                                           FROM MediaLibrary
                                           WHERE path=p.path)

In case you have many items in your media library the following sequence of sql statements might be faster:

CREATE TEMPORARY TABLE tmp_Path
AS SELECT DISTINCT path
FROM MediaLibrary;

CREATE UNIQUE INDEX tmp_Path_in_unique ON tmp_Path(path);

SELECT *
FROM Playlist p
WHERE playlist_name='A'
  AND NOT EXISTS (SELECT 1
                  FROM tmp_Path
                  WHERE path=p.path)

Where do you want to insert the result?

Thanks for the reply. I played a little bit around with the code.
Unfortunately the code doesn't suit my purpose. Your example compares on path but the path will be different because these are different files.

I had difficulties with the exists syntax and I tried a join instead. For speed reasons I created two temp tables:

DROP TABLE IF EXISTS ml_temp;
CREATE TEMPORARY TABLE ml_temp
AS SELECT DISTINCT artist,album,title,codec
FROM MediaLibrary;

DROP TABLE IF EXISTS Test;
CREATE TEMPORARY TABLE Test
AS SELECT DISTINCT artist,album,title,codec
FROM Playlist p
WHERE playlist_name='Test';

SELECT DISTINCT p.artist,p.album,ml.codec AS lib_codec,p.codec AS logs_codec
FROM Test p
JOIN ml_temp ml
ON ml.artist=p.artist
AND
ml.album=p.album

So this gives me a visual list of all albums which exist in Playlist "Test" and the media library.

Is there any way to have this fed back into a new Playlist "Result"?

Unfortunately the code doesn't suit my purpose.

But it's doing what you actually asked for.  :)

So this gives me a visual list of all albums which exist in Playlist "Test" and the media library.

Which is the opposite from your original question.  :)

Is there any way to have this fed back into a new Playlist "Result"?

No, this is logically not possible, because only tracks can be added to a playlist, not summarized results like in your query. However you could either remove the tracks from the test playlist, which don't exist in the media library or add the matching tracks to another playlist.

To do this you first need to create another virtual table for the playlists, which allows you to manipulate them:

DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split,playlist);

The deleting of the unmatched tracks could be done with (not possible in this case without NOT EXISTS):

DELETE FROM Playlist_Updatable pu
WHERE playlist_name='Test'
    AND NOT EXISTS (SELECT 1
                                FROM ml_tmp
                                WHERE artist=pu.artist
                                     AND album=pu.album)

If you don't want to list the result it's not necessary to add the columns title and codec to the table ml_tmp and also an index to speed up the lookup on this table should be created. It could look like this:

DROP TABLE IF EXISTS ml_temp;
CREATE TEMPORARY TABLE ml_temp
AS SELECT DISTINCT artist,album
FROM MediaLibrary;

CREATE UNIQUE INDEX ml_temp_in_unique on ml_tmp(artist,album);

Adding the matching tracks to another playlist could look like this (if the target playlist, doesn't exist, i will be automatically created):

INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT metadb_handle,'New playlist'
FROM Playlist_Updatable /* or just Playlist, it doesn't matter */ pu JOIN ml_temp ml ON ml.artist=pu.artist AND ml.album=pu.album
WHERE playlist_name='Test'

Note1: I didn't test the SQL statements, so they might be not 100% correct.

Note2: You will run into troubles, if the combination of artist and album is not unique, i.e. you have more than one album with the same combination, but this depends on the content of your media library and your individual tagging guidelines.

What I wanted to do with this, remove old crap data that foobar was holding on to instead of rescanning the files, doesn't appear to be possible. Whether I try to update or delete the data, I always get 'SQLite Error: (8) attempt to write a readonly database'. Is that an absolute restriction? Do I have to use external SQLite editors to do this?

As an aside, I checked the entire documentation looking for just one single sample query for the most basic media library query, but there was not one. I don't know the structure of the database, and I guessed at a few things based on this thread. I feel like the entire documentation is a bit bereft without the fundamental basics, or at a bare minimum a link to its structure explained elsewhere. It doesn't do any good to have a tool to query if you don't know what you're querying!

What I wanted to do with this, remove old crap data that foobar was holding on to instead of rescanning the files, doesn't appear to be possible.

No, it isn't.

Whether I try to update or delete the data, I always get 'SQLite Error: (8) attempt to write a readonly database'. Is that an absolute restriction?

No, but you should have a look at the documentation here (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html). It's explained there, which data manipulation commands are available and what needs to be done to use them.

Do I have to use external SQLite editors to do this?

You can't. The MediaLibrary and Playlist virtual tables are only available inside of foobar2000.

As an aside, I checked the entire documentation looking for just one single sample query for the most basic media library query, but there was not one.

Yes, you're right. Originally foo_sqlite was a part of foo_uie_sql_tree, which comes with some example nodes and there relevant documented queries.

I don't know the structure of the database,

There is nothing you need to know besides what you can find in the documentation. There are only a very few internal tables, which are not meant to be used by the user.

It doesn't do any good to have a tool to query if you don't know what you're querying!

It's all written in the documentation:

For querying the foobar2000 media library a SQLite virtual table is provided which includes besides all available tags several other fields per default. The fields of the virtual table can be customized in the preferences. Similarily also a virtual table is provided for querying the playlists. The virtual table names are: MediaLibrary and Playlist.

I want to use foo_sqlite to generate playlist for syncing with my iPod. The playlist should contain all albums with an average rating > 3 and all albums that have at least one track with missing rating. Additionally, I want to reduce the number of items in a playlist by filtering the files by their path. This is the code that I hacked together:

INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, b.playlist_name, a.path, minr, avgr
FROM MediaLibrary a, (SELECT 'SQL_>M' "playlist_name") b
JOIN
  (
  SELECT a.metadb_handle, a.album, min(a.rating) minr, avg(a.rating) avgr, a.path
  FROM MediaLibrary a
  WHERE substr(a.path, 1, 9) = 'D:\Metal\' AND substr(a.path, 10, 1) > 'M'
  GROUP BY a.album
  HAVING minr='' OR avgr>3
  ) albums
ON a.album = albums.album

It seems to work. However, it also returns tracks that do not match the WHERE clause, e.g. there are some tracks from "D:\Rock\" and some from "D:\Electronic\". Any idea what my mistake is? I have no clue about SQL queries so most likely there is a better query for my intended use.

It seems to work.

Well, at least the statement, which you posted here, won't be executed as the syntax is wrong. Anyway, try this:

WITH Albums AS (
  SELECT album
  FROM MediaLibrary
  WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
  GROUP BY album
  HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album

Oh sorry I forgot to remove the extra columns I used for debugging without "INSTER INTO..."-line. I used the columns to directly verify whether the criteria where applied.
 
Your code looks much more clean, but it does also pull in tracks from other folders. There are more tracks in the list than there are in the Metal-folder. With your code I can also not see the results in the console if I remove the "INSTER INTO..."-line. I do not understand how the tracks from other folders make it into the list...

but it does also pull in tracks from other folders.

This is only possible, if you have have albums from the metal folder with the same name in other folders. In this case you could try this (assuming that all selected album names are distinct):

WITH Albums AS (
  SELECT album, max(substr(path,1,10)) path
  FROM MediaLibrary
  WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
  GROUP BY album
  HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album AND a.path LIKE Albums.path||'%'

Quote from: kugel on 2019-03-16 13:47:45

but it does also pull in tracks from other folders.

This is only possible, if you have have albums from the metal folder with the same name in other folders. In this case you could try this (assuming that all selected album names are distinct):

Code: [Select]

WITH Albums AS (
  SELECT album, max(substr(path,1,10)) path
  FROM MediaLibrary
  WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
  GROUP BY album
  HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album AND a.path LIKE Albums.path||'%'

Ah that's it! Thank you for your help. I am happy with your latest query suggestion :)

I have what I think is a very simple use case but can't quite figure out how to get it to work. I want to simply extract all fields from my database and save off to a CSV to use elsewhere.

What's the syntax for using the writefile command? Is it possible to save the output of a query into a file?

I can just copy the results right out of the console as well but then the data comes without the headers which is less than ideal. If it's possible to easily add/get the headers that'd be a solution for me too.

Although not impossible writefile is probably not suitable for your purpose, as you can only write one single value for each record to a different file with it.

But you can just do a "SELECT * FROM MediaLibrary" in the SQLite console, write click on the result and copy all. You will get the content of the result window in a CSV format (separator is a comma and field delimiter a single quote) copied to the clipboard.

You might need to increase the SQLite console row limit in "Preferences -> Advanced -> Tools ->SQLite viewer" to get all your data listed. In case you have a large library you also might consider to split the result sets to a reasonable size to not run into memory problems.

As I said I did try what you suggest - but the headers aren't extracted with the results. Any easy way to grab the headers? Adding them manually will be a pain.

Directly it's not possible, but although it is a bit of a hack and might not work anymore in the future, you can get the headers separately with this SQL:

select group_concat('''metadb_handle'',''||vtc_column_name||'''')
from mdb_VTableColumn
where vtc_table_type='0';

Reply removed, was meant for SQL Playlist.

Hi, I'm trying to write a component (my first attempt at one), that would involve running a few queries against your playlist virtual table.  I'm thinking that for my component to query the virtual table, I would need the pointer to your module (functions) MetaDB_Module, to use in the sqlite function sqlite3_create_module(), (I'm also learning SQLite).  Is my assumption correct?  Can you provide any guidance for how to enable my component to utilize what you've so wonderfully done?

My hope is to be able to get a few fields from/about the prior song and the next song, and then place them into Title Formatting variables.  Then I can display using the foo_osd component on a secondary monitor.  30 years ago I was a fair coder, so this project will give me something to do while I wait for covid to be over. 

Any advice is very much appreciated.

Hi, I'm trying to write a component (my first attempt at one), that would involve running a few queries against your playlist virtual table.

I'm sorry, but this is not possible.

I'm thinking that for my component to query the virtual table, I would need the pointer to your module (functions) MetaDB_Module, to use in the sqlite function sqlite3_create_module(), (I'm also learning SQLite).

No, it would work a bit different, if it would be possible.

Is my assumption correct?  Can you provide any guidance for how to enable my component to utilize what you've so wonderfully done?

I've created a SDK to make such things possible, for example for foo_uie_sqlite_tree, but this SDK is not ready to be published and probably will never be.

No, it would work a bit different, if it would be possible.
I've created a SDK to make such things possible, for example for foo_uie_sqlite_tree, but this SDK is not ready to be published and probably will never be.

Thank you for replying.  It was easy to write the queries to get the data I wanted for my project.  I guess instead, I'll have to figure out where/how foobar stores the running playlist internally, and how to get the data that way.  I'm guessing it will take a while for me to figure that all out.  FWIW, if you ever decide that you want someone to test your SDK, let me know.  ;)
Take care.

I'm having trouble getting my head around conditionals in regex (standard info (https://www.regular-expressions.info/conditional.html)). For example, here's a query for parsing opuses out of the titles of classical pieces. Ultimately I want to make an UPDATE out of it, but the SELECT suffices to see what's going on. The parts I'm interested in are in the first lines of the hairy, concatenated regex patterns. In reality, I don't think I want all of these groups, but it helps to see what's happening if they're there now.

The query's three columns are:

• ok: A working demonstration of a parsed title using a lookahead in the first group to capture everything before "Op", "BMW", or the like. Heaven knows how hairy this will grow when trying to work with many albums, and the options in the first line of reg.lookahead need to be written twice the way I have it now.
• notok: A conditional approach that fails in all cases. I'm trying to catch everything before the opus by seeing if the opus group participates in the overall match. I haven't been able to do this.
• worksortorder: A sorting field formed from the numerical parts of the opus and the number, if present. By that I mean that "Op. 1" will yield "0001-00" and "Op. 2 No. 1" is "0002-01".

Here's the query. It looks at everything in the active playlist.

WITH   re AS (SELECT '^(.*(?=[Oo]p|BMW|bmw|KV?))?'
                  || '(([Oo]p|BMW|bmw|KV?)\.? ?(?:[Pp]osth?\.? ?)?(\d+))?'
                  || ' ?([Nn]o\.? (\d+))?(.*)$'
                  AS lookahead
                   , '^((?(2).*))'    -- This is the broken part
                  || '(([Oo]p|BMW|bmw|KV?)\.? ?(?:[Pp]osth?\.? ?)?(\d+))?'
                  || ' ?([Nn]o\.? (\d+))?(.*)$'
                  AS conditional
             )
SELECT regexp_replace(re.lookahead, '$1—$2—$3—$4—$5—$6—$7', title)
    AS ok
     , regexp_replace(re.conditional, '$1—$2—$3—$4—$5—$6—$7', title)
    AS notok
     , nullif(padl(regexp_replace(re.lookahead, '$4', title), 4, '0')
    || '-'
    || padl(regexp_replace(re.lookahead, '$6', title), 2, '0'), '0000-00')
    AS worksortorder
  FROM Playlist, re
 WHERE playlist_index = active_playlist()

And here's some sample output. I've omitted the notok column for space considerations, but it fails in every row as the second and third rows of the ok column do here—lacking opuses altogether. Basically, nothing is found and the whole title goes into the final (.*) group. That is how I want such lines handled, but not every line! I've also edited the "No. 1" out of the first part of "Op. 9"'s title just to see what happens.

                           ok                                        worksortorder
Nocturne in E Minor, —Op. Post. 72—Op—72—No. 1—1—: Andante              0072-01
——————Nocturne in C-sharp minor (1830): Lento con gran espressione
——————Nocturne in C minor (1837)
3 Nocturnes, —Op. 9—Op—9———in B-flat minor: Larghetto                   0009-00
3 Nocturnes, —Op. 9—Op—9—No. 2—2— in E-flat major: Andante              0009-02
3 Nocturnes, —Op. 9—Op—9—No. 3—3— in B major: Allegretto                0009-03
3 Nocturnes, —Op. 15—Op—15—No. 1—1— in F major: Andante cantabile       0015-01
3 Nocturnes, —Op. 15—Op—15—No. 2—2— in F-sharp major: Larghetto         0015-02
3 Nocturnes, —Op. 15—Op—15—No. 3—3— in G minor: Lento                   0015-03
2 Nocturnes, —Op. 27—Op—27—No. 1—1— in C-sharp minor: Larghetto         0027-01
2 Nocturnes, —Op. 27—Op—27—No. 2—2— in D-flat major: Lento sostenuto    0027-02
2 Nocturnes, —Op. 32—Op—32—No. 1—1— in B major: Andante sostenuto       0032-01
2 Nocturnes, —Op. 32—Op—32—No. 2—2— in A-flat major: Lento              0032-02
2 Nocturnes, —Op. 37—Op—37—No. 1—1— in G minor: Andante sostenuto       0037-01
2 Nocturnes, —Op. 37—Op—37—No. 2—2— in G major: Andantino               0037-02
2 Nocturnes, —Op. 48—Op—48—No. 1—1— in C minor: Lento                   0048-01
2 Nocturnes, —Op. 48—Op—48—No. 2—2— in F-sharp minor: Andantino         0048-02
2 Nocturnes, —Op. 55—Op—55—No. 1—1— in F minor: Andante                 0055-01
2 Nocturnes, —Op. 55—Op—55—No. 2—2— in E-flat major: Lento sostenuto    0055-02
2 Nocturnes, —Op. 62—Op—62—No. 1—1— in B major: Andante                 0062-01
2 Nocturnes, —Op. 62—Op—62—No. 2—2— in E major: Lento                   0062-02

So, any tips on how I can get that conditional approach to work? Barring that, are there any other good ways to accomplish this sort of thing? I imagine that better minds than mine have done something like this.

I have what I think is a very simple use case but can't quite figure out how to get it to work. I want to simply extract all fields from my database and save off to a CSV to use elsewhere.

I've had some success saving what I need to another .db file, which can be manipulated with sqlite3 or something like SQLite Studio (https://sqlitestudio.pl/) to get CSVs. This grabs the whole MediaLibrary as well as the internal table that holds all of the SQL Tree queries.

VACUUM;
ATTACH DATABASE 'export\foobarlibrary.db' AS 'LibraryDB';
DROP TABLE IF EXISTS LibraryDB.MediaLibrary;
CREATE TABLE         LibraryDB.MediaLibrary AS
              SELECT *
                FROM MediaLibrary;

DROP TABLE IF EXISTS LibraryDB.ust_TreeItem;
CREATE TABLE         LibraryDB.ust_TreeItem AS
              SELECT *
                FROM ust_TreeItem;

DETACH DATABASE 'LibraryDB';

At the moment, it just puts the db in a folder called export off of the foobar2000 root directory. I forget if this fails if the folder has not yet been created, so that may happen. I'd love some way to get another folder in there—perhaps even a user-selected one—but that's probably not SQLite's job. Sure, one could just type in another location, but I'm trying to keep it somewhat generic here.

Long as I'm here, I'd like to make a feature request: The third answer down on this stackoverflow post (https://stackoverflow.com/questions/3845718/update-table-values-from-another-table-with-the-same-user-name) mentions that UPDATE FROM queries are available as of SQLite version 3.33.0. I've been having trouble implementing any of the solutions given there, but that particular way may be what I really want when I write tags. Is there any chance you can bump up the SQLite version? Thanks.  ;D

I'm having trouble getting my head around conditionals in regex ...

I didn't have a closer look at your code, but here is the code which I use to move a featuring artist from title to an own tag. You should be able to adapt it for your needs.

I'm using a permant table for my regular expressions. The expression searches for common occurrances of featuring artist with or without braces in the track title.

CREATE TABLE RegularExpressions
(
  name TEXT PRIMARY KEY,
  expression TEXT
);

INSERT INTO RegularExpressions
VALUES('Featuring','(?(DEFINE)'||
                     '(?<open-braces>[({\[])'||
                     '(?<close-braces>[)}\]])'||
                     '(?<no-braces>[^(){}\[\]])'||
                     '(?<content-1>(?i)f(ea)?t((\.\s*)|\s+))'||
                     '(?<content-2>(?i)featuring\s+)'||
                     '(?<content>\b(?&content-1)|(?&content-2))\b'||
                   ')'||
                  '(?:(?:\s*(?&open-braces)(?&no-braces)*)?|\s+)(?&content)(?<featuring-artist>(?&no-braces)+)(?:(?&close-braces)\s*)?');

For updating the tags I'm using this statement:

WITH RegularExpression AS (
  SELECT expression
  FROM RegularExpressions
  WHERE name='Featuring'
)
UPDATE PlaylistUpdatable
SET "featuring artist"=regexp_replace((SELECT expression FROM RegularExpression),
                                      '$+{featuring-artist}',
                                      title,
                                      1),
     title=regexp_replace((SELECT expression FROM RegularExpression),
                           '',
                          title)
WHERE "featuring artist" IS NULL AND
       playlist_index=active_playlist() AND
       item_is_selected;

The updatable virtual playlist table is created with this code:

CREATE VIRTUAL TABLE `PlaylistUpdatable` USING MetaDB_Module(no_multivalue_split,playlist)

At the moment, it just puts the db in a folder called export off of the foobar2000 root directory. I forget if this fails if the folder has not yet been created, so that may happen. I'd love some way to get another folder in there—perhaps even a user-selected one—but that's probably not SQLite's job. Sure, one could just type in another location, but I'm trying to keep it somewhat generic here.

Well, currently this cannot be done with foo_sqlite and at the moment I don't have any plans to support this. But why do you need to write the tables to another SQLite db? You can perfectly access the db from foo_sqlite with an external tool. Only the virtual tables are not usable with it.

Is there any chance you can bump up the SQLite version?

This will definitely happen, but at the moment I cannot make any promises, when it will be. I'm currently working on some updates, which need to be finished first. It should be somewhen this year, but I cannot guarantee it.

Thanks for the code help. I'll have to study it a bit to learn what it's doing, but that does look like a good way to handle this. I'm glad to hear that an update is in the offing when it comes; thanks for all that you've done.

But why do you need to write the tables to another SQLite db? You can perfectly access the db from foo_sqlite with an external tool. Only the virtual tables are not usable with it.

Could you say a little more about this? Part of my reasons for exporting is just for the sake of backing up all of my tags, but I didn't know of any ways to access foo_sqlite externally. Is it simply a matter of attaching foo_sqlite.db and executing a saved view? I haven't been able to read any of the data that way, but maybe I could make buttons that duplicate clicking on foo_uie_tree_view nodes? Maybe just make those node queries all SELECT * FROM view_blahblahblah (or bypass the treeview altogether) and work with view_blahblahblah from a Spider Monkey Panel? That would be sweet.

Could you say a little more about this? Part of my reasons for exporting is just for the sake of backing up all of my tags,

For backing up your tags and all your settings you just need to backup the folder with your user data (the location is dependent of the installation type and the foobar2000 version). You can also use foo_jesus to automate this task.

but I didn't know of any ways to access foo_sqlite externally. Is it simply a matter of attaching foo_sqlite.db and executing a saved view?

It's actually just opening the database with an external tool and work with the saved data.

I haven't been able to read any of the data that way,

This is strange. I no problems to access the db with a tool like this (https://sqlitebrowser.org/).

but maybe I could make buttons that duplicate clicking on foo_uie_tree_view nodes?

This would be a possibility. The upcoming version of foo_sqlite will support the materialization of the virtual tables. This will be done automatically in the background.

Maybe just make those node queries all SELECT * FROM view_blahblahblah (or bypass the treeview altogether) and work with view_blahblahblah from a Spider Monkey Panel? That would be sweet.

This is not possible as a SMP doesn't have access to the virtual tables.

I'm probably not being clear and jumbling many different ideas together. Indeed, I don't come to foobar knowing any of this; rather, I'm using foobar to learn all this. It's a hell of a lot more fun than all the tutorials droning on about company personnel, inventory, and quarterly sales. :)

I've used SQLiteStudio just to look into the dbs, and only now am I realizing that I can start working in there to create views and whatnot rather than going through the SQLite console or the treeview batch tab in foobar for everything. Cool!

But when I say that I can't read the data, I guess I mean that I can't read the contents of the virtual tables or any views that refer to them. I have, in one instance, created a permanent table and saved data to it in order to export an album list, and I can read the contents of that just fine. (I didn't actually mean to leave it there, and it gets dropped and recreated before each such export. I guess I should drop it when I'm done, or use a temporary table, or…) That's just by album and doesn't have records for each track, but the query I pasted above could certainly be redirected within foo_sqlite.db if desired. This is what you see in your db?

I guess I wanted to put things like that in a separate db because I had in mind the examples of other music players that could export metadata to xml files or what have you, and I hadn't yet found any other ways in foobar to achieve something like that. I also didn't want to make foo_sqlite.db bigger than it had to be. That directory I saved my db to is just a temporary location since I haven't figured out how to supply a directory more specific to my current computer short of explicitly writing it into the query, which I don't want to do. Once I've created it, I move it by hand. Perhaps I could learn some code for moving it from the place SQLite leaves it to where I want to put it, but I haven't gotten to that bit yet. :)

This is what you see in your db?

Well, the main idea behind foo_sqlite is to have the possibility to query the media library with SQL. While title format queries are sufficient in many cases, they cannot be used for all kind of queries, which needs to query the tags from more than one track, e.g. get all tracks from albums, which have a track with a rating of 5. With title format queries you can only get the single tracks, which have a rating of 5, while it is possible with SQL to get also all other tracks of these albums, which don't have a ratnig of 5.

The virtual tables are just a SQL wrapper for the media library or the playlists, which are always completely loaded into memory. The foobar2000 SDK provides means to access these in-memory data. That's the reason, why you can only access the virtual tables from the SQLite console and not from external tools.

OK, weird errors. I was trying to redo my query along the lines you showed me. I was having trouble getting the results I wanted, but it was doing its thing and giving me one row of something and the rest NULL. I also had foo_sqlite.db open in SQLiteStudio to modify my regex table.

I commented out a line in that regex and replaced it with a bunch of (.*) just to see if I could get something. It looked like this below. There may be errors in it, but I'm not asking about those.  :)

DROP TABLE IF EXISTS regexes;
CREATE TABLE         regexes (
                        name TEXT UNIQUE PRIMARY KEY,
                  expression TEXT
                             );

INSERT INTO regexes
     VALUES (
            'work'      -- opus and number
          , '(?(DEFINE)'
         || '(?<worktext>(?i)\b(?:opp?|bwv|kv?)\.?\b)?'
         || '(?<posth>(?i)\bposth?\b)?'
         || '(?<num>(?i)no)?'
         || ')'
         -- || '(?&worktext)\.?\s*(?&posth)\.?\s*(\d+)?\s*'
         -- || '(?&num)\.?\s*(\d+)?'
         || '(.*)(\d+)?(.*)(\d+)?'
            );

I ran that in SQLiteStudio and went back to the SQL console in foobar to rerun my SELECT there, and it seemed to hang. I aborted and tried again. Foobar crashed.

After restarting, the SQL Tree was no longer displayed. I tried to rebuild Playlist_Updatable but it failed due to a duplicated column. I tried the same with the Playlist table but could not. Here's the error output for that.

Execution error:
SQLite Error: (1) Unable to declare virtual table Playlist:
SQLite Error: (1) duplicate column name: asin

Error while preparing the first statement of:
DROP TABLE IF EXISTS Playlist;
CREATE VIRTUAL TABLE Playlist USING MetaDB_Module(add_defaults);
COMMIT;

Looking at the Playlist table in Preferences, I see that most of the fields have been duplicated. I put a screencap below.

I done broke it good.  :o  I guess I'll try manually removing fields via Preferences, but it pretty much doubled all of them after artist.

OK, I removed the duplicate columns and restarted. It came up with the default nodes in the treeview. Running SELECT * FROM ust_TreeItem in the console showed no rows.

• Uninstalled/reinstalled foo_uie_sql_tree. No change.
• Uninstalled/reinstalled foo_uie_sql_tree and foo_sqlite. No change.
• Noticed that foobar 1.6.6 is out, made a new portable installation, and copied my profile folders over. Treeview is restored, but ust_TreeItem is still showing no rows, even from a different installation's copy of foo_uie_sql_tree.db.

So it looks like disaster is averted, but I'll probably do a more full rebuild. Oh look, a crash report.

Just to be clear, I'm merely reporting my experience. You do not need to resolve anything for me now, though I can try any suggestions you may have if you need to know more.

I do think though that maybe I ought to just work my new stuff out entirely within SQLiteStudio—in this handy export database I happen to have right here—and then just paste my final working version into foobar.  :))

Ooh, one other thing I've been noticing: the Playlist_Updatable table seems to get corrupted if I'm moving things around from playlist to playlist, removing playlists, or something along those lines. My WHERE playlist_index = active_playlist() will exclude everything because that field will no longer contain playlist indexes. I've seen it contain different things at different times: nothing, incremented numbers, or the file paths of the tracks, if I'm remembering everything correctly. Dropping that table and recreating it restores order.

I ran that in SQLiteStudio and went back to the SQL console in foobar to rerun my SELECT there, and it seemed to hang. I aborted and tried again. Foobar crashed.

Ok, I never tried it, but changing foo_sqlite.db with an external tool, which is using a much newer version of SQLite, seems to be problematic and led to a database corruption.

Treeview is restored, but ust_TreeItem is still showing no rows, even from a different installation's copy of foo_uie_sql_tree.db

The reason is, that the information, which the root node of the tree is not stored in the db, but in the foobar 2000 config file, as there can be multiple panels with a tree all with the different root node. So, if you want to restore from a backup you, also need to restore the file foo_uie_sql_tree.cfg. The database alone is not enough.

So it looks like disaster is averted,

I hope you have a recent backup.

Oh look, a crash report.

Most likely caused by the corrupted database.

Ooh, one other thing I've been noticing: the Playlist_Updatable table seems to get corrupted if I'm moving things around from playlist to playlist, removing playlists, or something along those lines. My WHERE playlist_index = active_playlist() will exclude everything because that field will no longer contain playlist indexes. I've seen it contain different things at different times: nothing, incremented numbers, or the file paths of the tracks, if I'm remembering everything correctly. Dropping that table and recreating it restores order.

Is this something, that can be reproduced somehow or happens more than once? Or did it only occur after the crash?

Is this something, that can be reproduced somehow or happens more than once? Or did it only occur after the crash?

That was happening periodically before the crash (and before my bright idea to involve SQLiteStudio) throughout my work with this query and other, more successful update queries. I'd be pulling files from one playlist which was a work queue to another to work on (I hadn't seen that item_is_selected technique), moving those to a third playlist of finished tracks, and often playing music in a fourth with albums passing in and out. Some of that work involved also updating and adding tags with masstagger, musicbrainz, and discogs, the latter often throwing up "stoi" errors of its own with inexactly-matched tracks. So, lots going on.

Ok, thank you. I will try to reproduce this. I never stumbled upon such a problem, but this might be related to my usage pattern as I have a fixed set of playlist, which I usually don't change. The playlist index is a dynamic value which might change, if the playlists are reordered or if a playlist is removed.

Back to SQL matters, this works but it makes me feel dirty. I'm parsing opus numbers and the like from the titles, and the current album includes two different numbering schemes. So I'm trying to construct three semicolon-separated fields for eventual splitting and correlating with one another: work, worknumber, and worksortorder. The last is a concatenation of the numeric portions of the other two, each of those numerals left-padded with zeros to take the general shape like '0000-0000'. However, if it is all zeroes, it should be nulled out. For example, a track with "Op. 4 No. 1, RV 383" will have "Op. 4; RV 383", "No. 1;", and "0004-0001; 0383-0000" in those three fields.

Yes, it gets more complicated than this. Some RV numbers on this album end in "a" or "b", and the next album has Fanna numbers with Roman numerals and slashes. Heavens, this is madness, but one thing at a time.

Like I said, this works, but I had to resort to a hairy eval() that makes me think there must be a better solution. Is there?

Base regex:

DROP TABLE IF EXISTS regexes;
CREATE TABLE         regexes (
                        name TEXT UNIQUE PRIMARY KEY,
                  expression TEXT
                             );

INSERT INTO regexes
     VALUES (
            'work'      -- opus and number
          , '(?i)('
         || '?(DEFINE)'
         || '(?<titlecase>(?:\b(?:op)(?=[\s\d\.])\.?))'
         || '(?<uppercase>(?:\b(?:bb|bwv|kv?|rv)(?=[\s\d\.])\.?))'
         || '(?<mixedcase>(?:\b(?:WoO|BuxWV)(?=[\s\d\.])\.?))'
         || '(?<posth>\s*(?:\bposth?(?=[\s\d\.])\.?)?)'
         || '(?<sp>(?:\s*))'
         || '(?<num>(?:\bn(?:o|º)(?=[\s\d\.])\.?))'
         || ')'
         || '(?:'
         || '(?:'
         || '(?<titlecase>(?&titlecase))'
         || '|(?<uppercase>(?&uppercase))'
         || '|(?<mixedcase>(?&mixedcase))'
         || ')'
         || '(?<posth>(?&posth))'
         || '(?<workspace>(?&sp))'
         || '(?<worknum>(\d+)?)'
         || '(?:'
         || '\s*(?<numbertext>(?&num))'
         || '\s*(?<numbernum>(\d+))'
         || ')?'
         || ')'
            )

SELECT queries referring to regex table
 

DROP VIEW IF EXISTS view_workfromtitle;
CREATE VIEW         view_workfromtitle AS
WITH   reg AS (
       SELECT expression ex
         FROM regexes
        WHERE name = 'work'
              )
SELECT regexp_replace(
                      reg.ex
                    , '\u$+{titlecase}\U$+{uppercase}\E$+{mixedcase}'
                   || '$+{posth}$+{workspace}$+{worknum}; '
                    , title
                    , 1
                      )
    AS work
     , replace(regexp_replace(
                              reg.ex
                            , '$+{numbertext} $+{numbernum}; '
                            , title
                            , 1
                              )
            , 'º', 'o'
               )
    AS worknumber
     , regexp_replace(reg.ex, 'nullif\(padl\(''$+{worknum}'',4,''0''\)||''-''||padl\(''$+{numbernum}'',4,''0''\),''0000-0000''\)||''; ''||', title, 1)
    AS worksortorder
     , title
  FROM Playlist_Updatable, reg
 WHERE playlist_index = active_playlist()
   AND item_is_selected;

SELECT substr(work, 1, length(work) - 2)
    AS work
     , substr(worknumber, 1, length(worknumber) - 2)
    AS worknumber
     , eval('SELECT '||substr(worksortorder, 1, length(worksortorder) - 8))
    AS worksortorder
     , title
  FROM view_workfromtitle

Sample output

work                worknumber      worksortorder           title
Op. 4; RV 383       No 1;           0004-0001; 0383-0000    Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, I. Allegro
Op. 4; RV 383       No 1;           0004-0001; 0383-0000    Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, II. Largo
Op. 4; RV 383       No 1;           0004-0001; 0383-0000    Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, III. Allegro
Op. 4; RV 279       No 2;           0004-0002; 0279-0000    Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, I. Allegro
Op. 4; RV 279       No 2;           0004-0002; 0279-0000    Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, II. Largo
Op. 4; RV 279       No 2;           0004-0002; 0279-0000    Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, III. Allegro
Op. 4; RV 301       No 3;           0004-0003; 0301-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, I. Allegro
Op. 4; RV 301       No 3;           0004-0003; 0301-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, II. Largo
Op. 4; RV 301       No 3;           0004-0003; 0301-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, III. Allegro assai
Op. 4; RV 357       No 4;           0004-0004; 0357-0000    Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, I. Allegro
Op. 4; RV 357       No 4;           0004-0004; 0357-0000    Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, II. Grave
Op. 4; RV 357       No 4;           0004-0004; 0357-0000    Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, III. Allegro
Op. 4; RV 347       No 5;           0004-0005; 0347-0000    Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, I. Allegro
Op. 4; RV 347       No 5;           0004-0005; 0347-0000    Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, II. Largo
Op. 4; RV 347       No 5;           0004-0005; 0347-0000    Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, III. Allegro
Op. 4; RV 316       No 6;           0004-0006; 0316-0000    Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, I. Allegro
Op. 4; RV 316       No 6;           0004-0006; 0316-0000    Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, II. Largo
Op. 4; RV 316       No 6;           0004-0006; 0316-0000    Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, III. Allegro
Op. 4; RV 185       No 7;           0004-0007; 0185-0000    Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, I. Largo - Allegro
Op. 4; RV 185       No 7;           0004-0007; 0185-0000    Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, II. Largo
Op. 4; RV 185       No 7;           0004-0007; 0185-0000    Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, III. Allegro
Op. 4; RV 249       No 8;           0004-0008; 0249-0000    Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, I. Allegro - Adagio - Presto - Adagio
Op. 4; RV 249       No 8;           0004-0008; 0249-0000    Concierto en Re menor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, II. Adagio
Op. 4; RV 249       No 8;           0004-0008; 0249-0000    Concierto en Re menor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, III. Allegro
Op. 4; RV 284       No 9;           0004-0009; 0284-0000    Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, I. Allegro
Op. 4; RV 284       No 9;           0004-0009; 0284-0000    Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, II. Largo
Op. 4; RV 284       No 9;           0004-0009; 0284-0000    Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, III. Allegro
Op. 4; RV 196       No 10;          0004-0010; 0196-0000    Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, I. Spiritoso
Op. 4; RV 196       No 10;          0004-0010; 0196-0000    Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, II. Adagio
Op. 4; RV 196       No 10;          0004-0010; 0196-0000    Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, III. Allegro
Op. 4; RV 204       No 11;          0004-0011; 0204-0000    Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, I. Allegro
Op. 4; RV 204       No 11;          0004-0011; 0204-0000    Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, II. Largo
Op. 4; RV 204       No 11;          0004-0011; 0204-0000    Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, III. Allegro assai
Op. 4; RV 298       No 12;          0004-0012; 0298-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, I. Spiritoso e non presto
Op. 4; RV 298       No 12;          0004-0012; 0298-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, II. Largo
Op. 4; RV 298       No 12;          0004-0012; 0298-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, III. Allegro

Like I said, this works, but I had to resort to a hairy eval() that makes me think there must be a better solution. Is there?

I don't think that it is worth the effort to find one or the other small structural optimization, if ever. Just imagine how the regular expression would look like, if you couldn't use named subexpressions. It would become completely unmaintainable. You should also not forget, that you are parsing text, which is following more or less loose rules. The more alternatives are possible the more complex will become the RE.

I'm having trouble with a corner case in the regexp_replace function. Sometimes, as in this album (https://musicbrainz.org/release/68d41048-6bda-40fb-bde2-81428ebb948a), it is the style to write the abbreviation of "Number" as "Nº", with an ordinal symbol (https://unicode-table.com/en/00BA/). I can write the replace string as something like '$+{number}', and it returns that string just fine, provided that the regular expression finds it. I can also make the first character lowercase with '\l$+{number}'. However, I cannot make the entire result lowercase, such as with '\L$+{number}\E'. In that instance, the rest of the string is truncated after that first "n". I'd like to be able to change the case to ease lookups in another table based on that value, which could be "no", "No", "NO", or whatever else comes up.

I see that there is a lower function provided by the unicode module, but I haven't found the right way to use that on the result. Is there an example of that somewhere?

I suppose I can just replace all the symbols with o's in the properties window, but I'd prefer to be able to take the titles as they are. Are there any other ways to do this?

I see that there is a lower function provided by the unicode module, but I haven't found the right way to use that on the result. Is there an example of that somewhere?

No, but it's just lower(<string>), where <string> can be any string also the return value of regexp_replace(). Do you have a simplified example for which lower() is not working as expected?

Simplified. Heh. Oh dear.  :)

Since this is a going into what will be a multivalue tag, the way I finally got these things working in general was to do my manipulations inside each regexp_replace call by wrapping the text of a subquery around the replace parameter. This takes place in a preliminary view, and then the UPDATE query (I'm using just a SELECT below) runs eval(SELECT … on the whole field of semicolon-separated subqueries (the semicolons being foobar2000 multivalue delimiters, not SQL statement terminators) and strips the final semicolon and space from the end.

And trying to put a simple example together, I seem to have gotten that mess working, or at least not failing in this way. So I guess I'll look for my problem elsewhere.  :)

But to finish the thought, here's what I'm doing. The lookup table is used to standardize the output from the variously-written title tags, and I wanted to make the lookup field lowercase for easier comparisons.

DROP TABLE IF EXISTS works;
CREATE TABLE         works (
                  id INTEGER PRIMARY KEY
            , lookup TEXT UNIQUE
            ,   work TEXT
            ,   sort TEXT
                     );

CREATE UNIQUE INDEX idx_workslookup
                 ON works(lookup);

INSERT INTO works(lookup, work, sort)
     VALUES ('op'   , 'Op.'   , 'OPUS')
          , ('no'   , 'No.'   , NULL  )
          , ('n°'   , 'No.'   , NULL  )
          , ('nº'   , 'No.'   , NULL  )
          , ('post' , 'posth.', NULL  )
          , ('posth', 'posth.', NULL  )
          , ('woo'  , 'WoO'   , 'WOOP')
          , ('bb'   , 'BB'    , 'BBAR')
          , ('sz'   , 'Sz.'   , 'SZOL')
          , ('bwv'  , 'BWV'   , 'BAWV')
          , ('buxwv', 'BuxWV' , 'BUWV')
          , ('k'    , 'KV'    , 'KOCH')
          , ('kv'   , 'KV'    , 'KOCH')
          , ('rv'   , 'RV'    , 'RYOM')
          , ('f'    , 'F.'    , 'FANN')
          ;

DROP VIEW IF EXISTS view_workfromtitle;
CREATE VIEW         view_workfromtitle AS
SELECT regexp_replace('(N(?:o|º))'
                    , '\(SELECT work FROM works WHERE lookup = lower\(''\1''\)\); '
                    , title
                    , 1
                      )
    AS simple
  FROM Playlist_Updatable
 WHERE playlist_index = active_playlist()
   AND item_is_selected;

SELECT eval('SELECT '||substr(simple, 1, length(simple) - 2))
    AS simple
  FROM view_workfromtitle

Ok, with a simplified example I meant more something like this: ;D

SELECT regexp_replace('(N(?:o|º))'
                    , '\L\1\E'
                    , 'Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, I. Allegro');

The result is indeed this:

Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 n

And the explanation is like I thought quite simple: regexp_replace() is implemented without unicode support. I already added this for the next version a while ago and with this version the result is as expected.

Had runtime errors and foobar2000 crashes that seemed to hinge on trying to update multivalue fields in Playlist_Updatable. Yes, I know that multivalue fields are not allowed in Playlist_Updatable, but I wanted to see if they could exist in Playlist or MediaLibrary and were simply treated as single value in Playlist_Updatable.

These fields had previously been single value, but I changed them to multivalue in the Playlist table, and used the first of them as an associated column of the other two. I also added the fields to the list in Preferences>Advanced>Display>Properties dialog>Multivalue fields. After making this change, my update query ran with no effect.

I then changed the fields back to single value. This is when all the runtime errors began. I made sure to remove the associated columns. More errors. I removed the tags from the advanced preferences. More errors. I rebuilt the Playlist and Playlist_Updatable tables. More errors.

I was only able to stop the errors and run the update query after removing the offending tags from the Playlist table and adding them again.

Is this the expected behavior? I would at some point like to make these tags multivalue and have them exist as such in MediaLibrary while being able to update those tags in new albums added to the collection. I know that Playlist_Updatable won't make them multivalue, but I was hoping that I could just split the tags in the properties window after I'm done updating them with SQL.

Yes, I know that multivalue fields are not allowed in Playlist_Updatable

This is actually not correct. You can't use the "Split multivalue tag" functionality for an updatable virtual table as it simply does not make sense here.

Consider the following example for one track:

album: "The Best"
artist: "The Greatest"
genre: "Rock", "Pop" (multivalue field with two values)

Without setting "split multivalue tags" for genre, the result of SELECT * FROM MediaLibrary (or Playlist, of course) will return this result for this track:

album           artist               genre
-------------------------------------------------
The Best        The Greatest         Rock · Pop

" · " is the standard multivalue separator used by foo_sqlite (in opposite to just ";" for the properties dialog), but you can define it for each tag by yourself.

With setting "split multivalue tags" for genre you will get this result:

album           artist               genre
-------------------------------------------------
The Best        The Greatest         Rock
The Best        The Greatest         Pop

So you will get one record for each value of a multivalue field.

But updating a virtual table allows you set multiple values for a single tag. This SQL

UPDATE Playlist_Updatable
SET genre='Rock · Pop'
WHERE album='The Best'
    AND artist='The Greatest';

will update the genre tag with two values. You just need to use the multivalue separator, which is defined for the relevant tag.

Is this the expected behavior?

Of course, not. Crashing is never an exprected behavior.  Thanks for the detailed error description. I will try to reproduce the problem and I think I have to disallow somehow what you did. Besides this, could you please also post one of the crash reports (txt and dmp file) as it might give some more information?

I don't seem to have any crash reports; the folder is empty.

But updating a virtual table allows you set multiple values for a single tag. This SQL

Code: [Select]

UPDATE Playlist_Updatable
SET genre='Rock · Pop'
WHERE album='The Best'
    AND artist='The Greatest';

will update the genre tag with two values. You just need to use the multivalue separator, which is defined for the relevant tag.

I am having trouble getting this to work as you describe. If I create a tag in Playlist called test, select Split multivalue tag and rebuild Playlist_Updatable, my update query won't do anything. If I do not select Split multivalue tag, then it will write the unsplit value to to the tag. If I create test with that checkbox selected first, and then edit it to unselect it (and rebuild Playlist_Updatable), running the update query will crash foobar without generating a crash report. I've added the crash dialog in case there's anything to be gleaned from that.

UPDATE Playlist_Updatable
   SET (
       test
       ) = (
       '#1 · #2'
       )
 WHERE Playlist_Updatable.playlist_index = active_playlist()
   AND Playlist_Updatable.item_is_selected

SELECT    title
        , test
     FROM Playlist
    WHERE playlist_index = active_playlist()
      AND item_is_selected

I am having trouble getting this to work as you describe. If I create a tag in Playlist called test, select Split multivalue tag and rebuild Playlist_Updatable, my update query won't do anything. If I do not select Split multivalue tag, then it will write the unsplit value to to the tag. If I create test with that checkbox selected first, and then edit it to unselect it (and rebuild Playlist_Updatable), running the update query will crash foobar without generating a crash report.

First of all, please don't touch "Split multivalue tags", when you are using an updatable virtual table. Just leave it empty. Please try writing multi value tags as described by me on a clean installation without any experiments of selecting or unselecting the "split multivalue tags" checkbox. If this is still not working, please post screenshots with all relevant settings or maybe better post the files foo_sqlite.db and foo_sqlite.dll.cfg from your configuration folder.

OK, fresh portable install of foobar2000 1.6.6 with SQLite viewer added. I dragged in a track and ran this script. I did not touch Split multivalue tag.  :D

DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable
               USING MetaDB_Module(no_multivalue_split,add_defaults,playlist);

UPDATE Playlist_Updatable
   SET (
       genre
       ) = (
       'Rock · Pop'
       )
 WHERE Playlist_Updatable.playlist_index = active_playlist()
   AND Playlist_Updatable.item_is_selected

The genre tag is filled in the properties view with "Rock; Pop", so that looks like it should, I'm guessing.

However, back in the SQLite console, this query returns just one record with "Rock · Pop".

SELECT    title
        , genre
     FROM Playlist a
    WHERE playlist_index = active_playlist()
      AND item_is_selected

I need this to be two records with the genre split. Having done whatever I need to do to accomplish that, I need to then be able to update subsequent files with multiple values in genre.

I have done nothing else since, so the installation is still unsullied for any further instructions. :D

I need this to be two records with the genre split. Having done whatever I need to do to accomplish that, I need to then be able to update subsequent files with multiple values in genre.

I have done nothing else since, so the installation is still unsullied for any further instructions. :D

Ok, then you need to add a second genre column (e.g. genremv) to the playlist table which now has "split multivalue tags" checked like this:

Type: Tag
Name: genremv
Tag
   Name: genre
   Split multivalue tag: checked

All other column settings remain unchanged.

And your query would be:

SELECT    title
        , genremv
     FROM Playlist a
    WHERE playlist_index = active_playlist()
      AND item_is_selected

So you will have two genre columns: "genre" for updating and "genremv" for splitting multi value tags. There is no need to rebuild the virtual playlist. It's done automatically.

Well all right, that works. :D Thank you for the help. I was wondering what that second "Name" box was used for, and now I know that this is one application for it. I would not have guessed this solution though. There is still a lot I have to learn about SQL in general.

Am I right in thinking that I can update tags from within a playlist using foo_sqlite? I'd like to be able to create an album_duration tag that will contain the duration of the album the tracks belong to.
I've created a new portable install, added a folder of songs to the media library, created a playlist with some tracks in it and selected a few of them, and then I've attempted to run this code from the above post and all I see is 'Executing SQL' and then foobar crashes.

I know SQL but I'm struggling to understand how to use and reference the playlist modules.

Any help would be much appreciated.

Code: [Select]

DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable
               USING MetaDB_Module(no_multivalue_split,add_defaults,playlist);

UPDATE Playlist_Updatable
   SET (
       genre
       ) = (
       'Rock · Pop'
       )
 WHERE Playlist_Updatable.playlist_index = active_playlist()
   AND Playlist_Updatable.item_is_selected

I had created a new, clean, portable install, but then - on autopilot I guess - added to that environment my other components, which are obviously to blame for causing it to crash!

Thanks for the feedback. I can confirm, that the SQL, which you are using, normally should not crash the application. I can only guess, if foo_sqlite is not causing the crash, that the tag update is causing another component to crash.

But if you don't mind, it would be good to verify, that the crash is caused by another component. In case of a crash you should get a crash report, which in most cases can identify the crashing component. If you didn't delete the relevant portable installation, you should have the crash report in the profile folder of this installation. Otherwise it should be probably not difficult to recreate the crash scenario.

So, if you have the crash report available, could you please post it here? TIA.

But if you don't mind, it would be good to verify, that the crash is caused by another component. In case of a crash you should get a crash report, which in most cases can identify the crashing component. If you didn't delete the relevant portable installation, you should have the crash report in the profile folder of this installation. Otherwise it should be probably not difficult to recreate the crash scenario.

Hi fbuser,

I had a quick look through the .txt file at the time and nothing jumped out at me, and I've just removed what I thought could be causing the problem (foo_tagbox) but it's not that either, so would appreciate you having a look for me. I've used foo_sqlite for lots of other purposes just not writing, so I guess it's that causing the problem.

The dump files are attached, and thanks for the invaluable plugin!

In the end it turned out to be the foo_sqlite.db that was causing the problem, no doubt it's something I did ages ago whilst playing with it initially.
I thought it would be something to do with the definition of the Playlist or MediaLibrary tables, but I can't seem to set them to defaults.
With a clean install, I added album_duration to the Playlist definition, ran a normal select query and closed foobar down. I went into .\profile\configuration and removed foo_sqlite.db and foo_sqlite.dll.cfg, and restarted foobar.
I then added the component back into foobar, restarted, and went back to the Playlist definition and album_duration is still there. I then went through and removed each column in the Playlist and MediaLibrary definition, clicked 'Add defaults' and it adds album_duration back again.
I assume it's stored elsewhere and maybe they're not to blame?

I've attached the foo_sqlite.db just in case it's useful.

Thank you for your efforts. I already saw from the crash log, that the problem is coming from foo_sqlite. I will look into it. If it is not already fixed for the next, partially internally rewritten, version, I will do it, if I can reproduce the issue.

I then went through and removed each column in the Playlist and MediaLibrary definition, clicked 'Add defaults' and it adds album_duration back again.
I assume it's stored elsewhere and maybe they're not to blame?

Yes, in your files. Besides some basic columns "Add defaults" adds also all tags it can find in your files as columns.

Quote from: SimBun on 2022-02-28 11:59:58

I then went through and removed each column in the Playlist and MediaLibrary definition, clicked 'Add defaults' and it adds album_duration back again.
I assume it's stored elsewhere and maybe they're not to blame?

Yes, in your files. Besides some basic columns "Add defaults" adds also all tags it can find in your files as columns.

I hadn't considered that. It's not really a default then, but I supposed it's what 99% of people want so that's fine at least it explains it.

I have one further question if you will. In the MetaDb_Module (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/MetaDb_Module.html) documentation it states "The UPDATE statement on such a table will only prepare the update of the tags. The actual update will be done with committing the changes. This means reverting changes with a rollback is not possible."

Are you referring to within a single execution here i.e. Should I add a COMMIT to the code, or is the successful run of the code sufficient for the tag changes to be committed (I've read that SQLite autocommits by default unless you declare a BEGIN statement).

Thank you for your efforts. I already saw from the crash log, that the problem is coming from foo_sqlite. I will look into it. If it is not already fixed for the next, partially internally rewritten, version, I will do it, if I can reproduce the issue.

I'm glad to see the plugin is still alive and kicking!

Should I add a COMMIT to the code

No, it's not necessary. Furthermore adding a COMMIT without a BEGIN would throw an error.

is the successful run of the code sufficient for the tag changes to be committed (I've read that SQLite autocommits by default unless you declare a BEGIN statement).

Autocommit mode is exactly the reason, why you don't need to supply a COMMIT by yourself.

Hi fbuser,

Last question I promise :-)
Is there any way of changing the session collation type, or a quick way of changing the collation of all the variables in the MediaLibrary and Playlist table to BINARY?
I use this component for various QA checks so it'd be nice to have the default be "correct" (it's my first time using a database with such a default).

Thanks again.

Is there any way of changing the session collation type

No, because there is no session collation type. The collation is always bound to the table columns and it defaults indeed to BINARY. So, if you create tables of your own, the collation of all columns is BINARY.

quick way of changing the collation of all the variables in the MediaLibrary and Playlist table to BINARY?

Officially: No. Inofficially: The collation types for the MediaLibrary and Playlist table columns are stored in the database. So, if you are familiar with SQL and databases, it is probably quite easy to find out, what to do. But, of course, don't do this without a backup.

But you can also always override the collation of a column explicitely in the SQL statement, e.g

SELECT * FROM MediaLibrary WHERE album COLLATE BINARY BETWEEN 'a' AND 'z';

to list all albums which start with a lowercase ascii letter

or

SELECT * FROM MediaLibrary ORDER BY album COLLATE BINARY;

to sort the result using the BINARY collation.

it's my first time using a database with such a default.

As already said above, the default for the database is BINARY and it is not possible to change it.

Quote from: SimBun on 2022-03-03 08:49:43

Is there any way of changing the session collation type

No, because there is no session collation type. The collation is always bound to the table columns and it defaults indeed to BINARY. So, if you create tables of your own, the collation of all columns is BINARY.

I do understand that, but the vast majority of columns I'll be using have been set as NOCASE in the MediaLibrary and Playlist table definitions.

Having had a play around with it now for a couple of hours it's fine, it's just something you NEED to be aware of when using such a database, which I find a strange default. I use MariaDB for some personal accounts which on Windows is similarly case insensitive, but I just changed the config and didn't have to worry about it again :-)

I don't know how I'd feel if I had to use a case insensitive database created by someone else (unless there was a single standard collation across all tables/columns), I'd probably end up explicitly specifying a collation everywhere :-)

Thanks again

In the end it turned out to be the foo_sqlite.db that was causing the problem

I checked this, but unfortunately I could not identify the reason for the problem. But I released a new version with some substantial internal changes. These could have fixed the problem.

A new version is out. Please, check the first post for details.

Typo: [16:46:31] [foo_sqlite] 16:46:31.124: Open datatabase

Typo: [16:46:31] [foo_sqlite] 16:46:31.124: Open datatabase

Thanks. I saw this since about 9 months nearly every day, but never noticed it. At least I'm consistent. I'm also stuttering when internally attaching a database.  :)

Quote from: SimBun on 2022-02-28 11:59:58

In the end it turned out to be the foo_sqlite.db that was causing the problem

I checked this, but unfortunately I could not identify the reason for the problem. But I released a new version with some substantial internal changes. These could have fixed the problem.

Not a problem, deleting foo_sqlite.db fixed it.

The new version, wow, what a lot of new functionality, is there going to be any documentation for it?

I've just run a bunch of my stored programs and with the metadb_handle to path change, they all seem to work, although I'm going to have to have a proper look in the morning as some of them are an order of manitude faster (45 secs compared to 1.9 secs), is this expected?

The only thing I've noticed so far is that when you 'Copy all' from the SQLite console it now includes a header row (which is fantastic), but it doesn't include a column for the row counter (#) and so all the headers for the data columns are shifted left.

Want to give feedback. Fantastic work. Thank you.

is there going to be any documentation for it?

You can call "Help -> SQLite viewer" for a short description of all functionality.

manitude faster (45 secs compared to 1.9 secs), is this expected?

Not really, But it's most likely related to optimizer changs of the SQLite engine.

The only thing I've noticed so far is that when you 'Copy all' from the SQLite console it now includes a header row (which is fantastic), but it doesn't include a column for the row counter (#) and so all the headers for the data columns are shifted left.

Thanks for the finding. Will be fixed for the next version.

Quote from: SimBun on 2022-03-13 22:38:49

is there going to be any documentation for it?

You can call "Help -> SQLite viewer" for a short description of all functionality.

I hadn't even seen that.

There looks to be SO much functionality (and now WINDOW functions too!) but I can't begin to imagine what I'd use it all for e.g. lookup tables.
I think it would really help people coming to this component if there were some real world examples of what it can be used for e.g. I helped some guy the other day to add album_duration to his collection, and after realising I could actually update tags with foo_sqlite I added DISCTOTAL to my collection, both of which probably aren't possible in any other tool.

Fantastic job!

Here's one real-world application I've been finding pretty handy. I have a "SQL replace" node that opens up the SQL console with a template for a SELECT/UPDATE query that one can use to make regex replacements to tags. By default, the UPDATE parts are commented out so that the SELECT runs as a preview of the changes. When they look as they should, simply uncomment the UPDATE and SET lines and comment out the SELECT and FROM. This is from the older SQLite before the new UPDATE…FROM syntax that's available in the component now.

/* Enter regex to find text to replace in   FINDREGEX
         regex to form replacement in       REPLACEREGEX
         field to write change to in        WRITEFIELD
         field to read text to change in    READFIELD
*/

-- UPDATE PlaylistUpdatable         -- un-comment to replace
--    SET WRITEFIELD =
SELECT                              -- preview, comment out to update
       regexp_replace(
                      FINDREGEX
                    , REPLACEREGEX
                    , READFIELD
                      )
  FROM PlaylistUpdatable            -- preview, comment out to update
 WHERE PlaylistUpdatable.playlist_index = active_playlist()
   AND PlaylistUpdatable.item_is_selected

For instance, if one wanted to zero-pad single-digit numbers in the album titles into an album sort tag, fill in the following:

-- UPDATE PlaylistUpdatable         -- un-comment to replace
--    SET albumsortorder =
SELECT                              -- preview, comment out to update
       regexp_replace(
                      '\b(\d)\b'
                    , '0$1'
                    , album
                      )
  FROM PlaylistUpdatable            -- preview, comment out to update
 WHERE PlaylistUpdatable.playlist_index = active_playlist()
   AND PlaylistUpdatable.item_is_selected

The new functionality of the Send to SQL Console vs. the renamed Execute in SQL console doesn't raise the error I used to get in the previous version when it tried to execute the dummy text and couldn't find the field "READFIELD", which is a nice little plus.

More odd behavior: say I've tried to send errant code to the SQL console. After closing the error dialog and the SQL console, the computer's focus returns not to foobar2000 but to whatever other window had it before foobar2000.

Typo: [16:46:31] [foo_sqlite] 16:46:31.124: Open datatabase

It's fixed.

More odd behavior: say I've tried to send errant code to the SQL console. After closing the error dialog and the SQL console, the computer's focus returns not to foobar2000 but to whatever other window had it before foobar2000.

It's fixed.

2.1.0:
- fixed: add missing column header for the first column, if the whole result content in the SQLite console is copied

Whilst yes, 2.1.0 does add the missing header row for column one (#), now I'm just missing the rest :-)

Whilst yes, 2.1.0 does add the missing header row for column one (#), now I'm just missing the rest :-)

Oops. It was defintely there, when I added the #. Looks like I changed something else unintendently. Will be fixed soon.

Whilst yes, 2.1.0 does add the missing header row for column one (#), now I'm just missing the rest :-)

Finally , you should have all headers. I fixed them indeed away while solving another header related issue.

Finally , you should have all headers. I fixed them indeed away while solving another header related issue.

I report an issue in the afternoon and by the time I've woken up it's fixed; I like this style of delivery :-)

Thanks again!

Most recent crash. Again, it was triggered by running an UPDATE query in the SQL console that had been working fine previously. I'm updating PlaylistUpdatable in one playlist while the player is playing music from another. It seems like I can go a while running various UPDATE queries and then something just doesn't work. The query that triggers it can be complex or simple, but something just doesn't go right with the UPDATE. I have the newest versions of everything installed.

Incidentally, the issues I was having with the treeview refreshing on load seem to have cleared up for now. I have 10 nodes loading fine.

Most recent crash.

It appears to be a similar issue as the one, that @grimes posted in the foo_uie_sql_tree thread. Unfortunately I'm not able to reproduce the issue, but I have a suspicion, what could cause the problem.

As it seems, that you are abel to reproduce the problem, I have attached two modified versions of foo_sqlite.dll, which are both candidates to fix the problem. I'd appreciate it, if you could make a test with both versions and let me know, if and which ones are solving the problem.

As it seems, that you are abel to reproduce the problem, I have attached two modified versions of foo_sqlite.dll, which are both candidates to fix the problem. I'd appreciate it, if you could make a test with both versions and let me know, if and which ones are solving the problem.

OK, I've swapped the first one in, and I'll see if I have the same issue. I can't really reproduce the crash at will, but I still have many files to update. I guess I'll try the second if I get a crash and let you know either way.

OK, I've swapped the first one in, and I'll see if I have the same issue.

That's not good. This could mean that my suspicion for the crash is wrong and it would be no big surprise, that also the second try to solve the problem will crash sooner or later.

However, if you still have the crash report for the first one, it could contain some more details, as it must be different, than the reports before.

I'm crashing, but I think it's something else related to the multivalue in a virtual table issue. I currently have the Candidate 1 dll in.

Due to some tag trimming somewhere, one of my custom multivalue tags, albumartistsort, was flattened with commas replacing semicolons. This field is set as a multivalue field in the properties window but not so set in the MediaLibrary or Playlist tables. If I run this query as given, foobar2000 crashes. The SELECT constant bit looks up the value ' · '.

UPDATE PlaylistUpdatable
   SET artistsortorder =
       regexp_replace(
                      ', '
                    , (SELECT constant
                         FROM constants
                        WHERE name = 'multivaluedelim')
                    , artistsortorder
                      )
 WHERE PlaylistUpdatable.playlist_index = active_playlist()
   AND PlaylistUpdatable.item_is_selected

However, if—instead of replacing ', ' (comma space)—I simply replace ',' (comma), the query runs fine, but there's an extraneous space after each delimiter. If I then run a similar query cleaning it up by replacing two spaces with one, I get the result I ultimately need.

Well shoot, the zip said it was 100% loaded, but the icon didn't appear. It's a big one. I'll try again.

It works if I split foo_sqlite.user.db from the rest. I could probably use some hints on removing extraneous stuff from that db.  :D

OK, I offloaded some backup and working tables to a different db, so I should go into the next crash a bit leaner.  :-[

New crash I can't seem to shake. This is in my main foobar as I haven't gotten clean ones going to my satisfaction yet. I wonder if something got corrupted, since I've been doing stuff like this without a problem before. Currently, the regular SQLite Viewer dll is in, but similar trouble is happening with the others.

Foobar opens with one playlist. I open the SQLite console and run SELECT * FROM Playlist. Works fine. I fill a second playlist with tracks and run the query again. Crash.

I just reproduced the crash in a clean installation with the stock foo_sqlite.dll. Open foobar, add one playlist more than were there on opening, and query SELECT * FROM Playlist.

I just reproduced the crash in a clean installation with the stock foo_sqlite.dll. Open foobar, add one playlist more than were there on opening, and query SELECT * FROM Playlist.

I tried the same, but it worked for me without any problems. Also updating PlaylistUpdatable to split values with commas into multiple value works for me without any crash. And I tried several cases with and without your database. Even updating almost 40000 tracks which were all prepared to have a multivalue field containing commas was no problem.

So, there must be an additional parameter in your setup, that causes the crashes.

Miscellaneous observation: I got my latest foobar incarnation all tidied up, and I noticed that the zip of it all is quite a bit bigger than the previous one. I did a pretty good job of getting all my cruft out of there, so I looked around for what else was present. It seems that, between SQLite Viewer and Random Pools, I am now the proud owner of two copies of icutd70.dll, and it's a big 'un. I guess that explains it. :D

It's not a big deal, but could I perhaps get away with one?

It's not a big deal, but could I perhaps get away with one?

In theory, yes. The DLLs just needs to be placed somewhere in the search path. However I highly recommend not to do anything like that, just to save some MB. The DLLs might be different in the future for both components, just because one component was already upgraded with a newer version, while the other wasn't.

I've just run a bunch of my stored programs and with the metadb_handle to path change, they all seem to work…

Help! I'm not finding anything anywhere on what to change metadb_handle to.
Here's a sample query:
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT metadb_handle, "Top 25" FROM MediaLibrary WHERE play_count > 0 ORDER BY play_count DESC LIMIT 25;

What does "the actual file path and subsong index" even mean in this context?
I'm trying to populate the playlist with the top 25 most played songs. So, actual file path to all my music? Subsong index makes no sense here because I want to check all songs…
(It's pretty obvious I don't know why I had to use metadb_handle in the first place, but it just worked, so I never questioned it.)

Help! I'm not finding anything anywhere on what to change metadb_handle to.

Just change it to "path, subsong". So your sample query will become

INSERT INTO Playlist_Updatable(path, subsong, playlist_name)
SELECT path, subsong, "Top 25" FROM MediaLibrary WHERE play_count > 0 ORDER BY play_count DESC LIMIT 25;

What does "the actual file path and subsong index" even mean in this context?

They are just two track attributes, which are necessary to uniquely identify a track.

So, actual file path to all my music? Subsong index makes no sense here because I want to check all songs…

You are misunderstanding the terms file path and subsong here. File path refers to the individual full path of the file belonging to a track and subsong is only relevant, if you have multi-track files, e.g. cue sheets. Then you need the subsong index to refer to a track in this multi-track file.

If you have only single track files, you could omit the subsong, but it is no mistake to always specify it.

(It's pretty obvious I don't know why I had to use metadb_handle in the first place, but it just worked, so I never questioned it.)

It served the same purpose as "path, subsong", but in a more obscure way.

Thank you for explaining all that.
Now my error has moved on to 'no such column: Top 25'

Again, no idea why that would happen. That's how I created the playlist Top 25 in the first place. I thought it creates it if it's not there, and updates it if it is there.

Now my error has moved on to 'no such column: Top 25'

Yes, of course. I didn't notice it, but you need to put 'Top 25' into single quotes, not double quotes.

Again, thank you very much! I've got all my queries working again.

error again.

error again.

Well, the reports are still for foo_sqlite version 2.1.1, while the latest version is 2.1.4. Nevertheless, the new report shows an additional hint, which could help to fix the issue, as I still don't have any crashes although I use it quite frequently. The dump file shows, of course, where the crash happens, but it's not obvious why it happens there. In addition to the files it would also be helpful which SQL statement exactly you executed.

Besides this, could you please also post the relevant files for version 2.1.4 as I highly doubt, that the issue is fixed with this version?

Updated to 2.1.4.
SQL statement:
SELECT count(DISTINCT album) album_count
   FROM medialibrary

Updated to 2.1.4.
SQL statement:
SELECT count(DISTINCT album) album_count
   FROM medialibrary

Hmm, according to crash log #127 from today, it must have been a different SQL statement, as "Row limit (5000 rows) for the SQLite console exceeded." was logged to the console right before the crash, while the statement above always returns only one row. So, it would be helpful to get also the crash reports for version 2.1.4.

Looking for a bit of help.  I'm getting close to sensible results, but I've got two issues.  I've got some code that is basically a duplicate finder ....

1) Code:

select a.path, a.artist, a.title, a.album, a.length, a.tracknumber, a.composer
  from mediaLibrary a
         inner join
       (select artist, title, album, length, tracknumber, composer
          from MediaLibrary
          group by artist, title, album, length, tracknumber, composer
          having count(*)>1
       ) b on (    a.artist = b.artist
               and a.title = b.title
               and a.album = b.album
               and a.length = b.length               
               and a.tracknumber = b.tracknumber
               and a.composer = b.composer)
  order by a.path

But some of the files don't have data in the last two tags:  tracknumber and composer.  Is there a way to ignore these fields if they are blank.  A bit like [%tracknumber%] ?

2) When I do "copy all" it's not copying (I wanted to copy to Excel) but that's not working (and the list isn't that large, and I've set the limit to 8000 rows which is easily large enough). 

So I'd like to export the results to a new playlist called "export". 

Can anyone tell me how to:
a) export to a new playlist called "export" and,
b) ignore fields in the comparison where those fields are blank. 

Any and all help much appreciated.

Cheers,
C.

2) When I do "copy all" it's not copying (I wanted to copy to Excel) but that's not working (and the list isn't that large, and I've set the limit to 8000 rows which is easily large enough).

In general this should work. However, I figured out, that there is some kind of initializing problem, at least if there was already copied something before. Calling "Copy all" a second time seems to solve this problem for the moment. I will fix this for the next version.

Can anyone tell me how to:
a) export to a new playlist called "export" and,
b) ignore fields in the comparison where those fields are blank. 

You can get both together this with this statement

insert into PlaylistUpdatable(path,playlist_name)
select a.path, 'export'
  from mediaLibrary a
         inner join
       (select artist, title, album, length, tracknumber, composer
          from MediaLibrary
          where tracknumber is not null and composer is not null
          group by artist, title, album, length, tracknumber, composer
          having count(*)>1
       ) b on (    a.artist = b.artist
               and a.title = b.title
               and a.album = b.album
               and a.length = b.length              
               and a.tracknumber = b.tracknumber
               and a.composer = b.composer)
  order by a.path

But some of the files don't have data in the last two tags:  tracknumber and composer.  Is there a way to ignore these fields if they are blank.  A bit like [%tracknumber%] ?

where tracknumber is not null and composer is not null

This will remove the tracks from the matching process entirely.

Generally, SQL treats a missing value as unknown, so when comparing one unknown to any other value (including another unknown) the result is also unknown (not true). In your example, if two tracks matched on all the tags except one (or both) tracks had missing track numbers the join at the bottom of your step (and a.tracknumber = b.tracknumber) would exclude those matches from the result set.

If all you're asking is to bring back matches where BOTH matching tracks are missing tracknumber or composer then the following sql should work (because it replaces the missing/unknown value with #).

select a.path, a.artist, a.title, a.album, a.length, a.tracknumber, a.composer
  from mediaLibrary a
         inner join
       (select artist, title, album, length, coalesce(tracknumber,'#') as tracknumber, coalesce(composer,'#') as composer
          from mediaLibrary
          group by 1, 2, 3, 4, 5, 6
          having count(*)>1
       ) b on (    a.artist = b.artist
               and a.title = b.title
               and a.album = b.album
               and a.length = b.length              
               and coalesce(a.tracknumber,'#') = b.tracknumber
               and coalesce(a.composer,'#') = b.composer)
  order by a.path

This however won't match tracks where one has a tracknumber but the other doesn't, and I'm not sure there's a way around that.

Quote from: carpman on 2022-08-10 00:23:09

But some of the files don't have data in the last two tags:  tracknumber and composer.  Is there a way to ignore these fields if they are blank.  A bit like [%tracknumber%] ?

Quote from: fbuser on 2022-08-10 06:33:51

where tracknumber is not null and composer is not null

This will remove the tracks from the matching process entirely.

Of course. This is what I understood @carpman wanted to achieve. But at a second glance, this should already be the case with the original query caused by the inner join. On the other hand using the coalesce() function is not necessary for your solution. Just write the relevant part of the join as

a.tracknumber is b.tracknumber
and a.composer is b.composer

and keep the rest of the query as it is.

@fbuser

Yes, that works:

select a.path, a.artist, a.title, a.album, a.length, a.tracknumber, a.composer
  from mediaLibrary a
         inner join
       (select artist, title, album, length, tracknumber, composer
          from MediaLibrary
          group by artist, title, album, length, tracknumber, composer
          having count(*)>1
       ) b on (    a.artist = b.artist
               and a.title = b.title
               and a.album = b.album
               and a.length = b.length               
               and a.tracknumber is b.tracknumber
               and a.composer is b.composer)
  order by a.path

Weird thing is, I did "copy all" and that also worked.  So, don't know why that works sometimes and not others.

I'm entirely useless at getting my head around SQL queries.   Where do I put the code to turn this query into a query that generates a playlist within fb2k (rather than just the SQLite console)?

Thanks for sorting out the main issue. 

C.

Of course. This is what I understood @carpman wanted to achieve. But at a second glance, this should already be the case with the original query caused by the inner join.

Sorry I should have made that more clear, I assumed it was just an interpretation problem, and my explanation was for @carpman as in the related (https://hydrogenaud.io/index.ph/topic,122739.msg1013185.html#msg1013185) post he said he knows nothing about sql; I wasn't trying to teach you about NULL/sql :-)

On the other hand using the coalesce() function is not necessary for your solution. Just write the relevant part of the join as

Code: [Select]

a.tracknumber is b.tracknumber
and a.composer is b.composer

and keep the rest of the query as it is.

Interesting, I've never used IS like that before!

Where do I put the code to turn this query into a query that generates a playlist within fb2k (rather than just the SQLite console)?

Just replace the first select line with the line that @fbuser included in his response:

insert into PlaylistUpdatable(path,playlist_name)
select a.path, 'export'

Thanks @SimBun and @fbuser  

Really appreciate the help and patience.  I completely missed that part of fbuser's reply.  So sorry for being a dunce.

All working perfectly now.   I ended up making all the statements "is" not "=" as for some reason, the code was missing one item out of the 2 x 1,260 that were being cross checked. 

Just in case anyone else should do something similar to me, I'll outline why I wanted this code. 

I have a select group of tracks from my library that I play on my DAP.  Because I alter the files during encoding (use Replay Gain +3db with a limiter) which are transcoded (from lossy and lossless sources) into lossyFLAC.  Thus, all the tags in the code I used should be identical (including length).  I just wanted a way to check if all the files on the DAP could be quickly found in my media library.  They've now been tagged with a DAP = 1 tag.

Very useful for duplicate finding now I have this code -- so thanks so much - really appreciate the help and thanks to fbuser for another nice component !! 

Final code: 

insert into PlaylistUpdatable(path,playlist_name)
select a.path, 'Temp'
  from mediaLibrary a
         inner join
       (select artist, title, album, length, tracknumber, composer
          from MediaLibrary
          group by artist, title, album, length, tracknumber, composer
          having count(*)>1
       ) b on (    a.artist is b.artist
               and a.title is b.title
               and a.album is b.album
               and a.length is b.length               
               and a.tracknumber is b.tracknumber
               and a.composer is b.composer)
  order by a.path

Cheers,
C.

Thanks carpman and fbuser for the sql.

How could I modify this to basically match artist and title even though the case of those would be different?

E.g. This Song Title and the other is This song title.

select a.path
  from mediaLibrary a
         inner join
       (select artist, title, length
          from MediaLibrary
          group by artist, title, length
          having count(*)>1
       ) b on (a.artist is b.artist
               and a.title is b.title
               and a.length is b.length)
  order by a.title

I think the case insensitive already works as it's picking up andhim and Andhim but slight problem.

The query I posted above duplicates the same file twice in the created playlist.



Edit, silly me. I clicked twice on the query and it added them again.

Is there a way around this, without the need to remove duplicates from the Edit menu? See my settings for this.



Edit 2: Send to playlist fixed that. Sorry for that.

Send to playlist fixed that.

"Send" replaces everything currently in the destination playlist, which I (personally) don't find particularly useful.

Hello, on my old and slow 32bit audio PC I am getting, time to time, pop-up reporting "Long running query detected" and asking me to continue or terminate it.

Is there the option to suppress it or just flag "YES continue" by default?

Kind regards, Andrea

Hello, on my old and slow 32bit audio PC I am getting, time to time, pop-up reporting "Long running query detected" and asking me to continue or terminate it.

Is there the option to suppress it or just flag "YES continue" by default?

Under "Preferences -> Advanced -> Tools -> SQLite utilities -> Control parameters -> Threshold for detecting long running SQL background queries (in seconds)" you can define the interval for displaying this message. But you should be aware, that without this dialog you don't have any chance to abort a background query without restarting the application. This is mainly needed for badly defined background queries in foo_uie_sql_tree, e.g. unwanted cartesian products could lead to running a query for many hours.

For the materialization background maintenance, which is causing the dialog in your case, this should normally not occur in everyday use. However, with fb2k v2 there is an issue, that on manual rescanning a monitored library folder or a folder on an unavailable external drive is becoming available again, all tracks for this folder are signaled as newly inserted to the library. If this is the case also for you, you can see in the console how many tracks were added to the materialized media library. If this is not the case for you, I'd like to know, what you are doing before this dialog appears and how many tracks are affected.

In the end you should not set the threshold too high, because otherwise you wouldn't be notified if something is wrong.

Hello @fbuser for your kind and precise support.
In my case, I found the problem was due to ".., with fb2k v2 there is an issue, that on manual rescanning a monitored library folder or a folder on an unavailable external drive is becoming available again,".
Thanks and regards, Andrea

Hello. I need to find songs with same duration. In foobar2000 in SQLite utilities/playlist table I want to create column duration_ms. I know that in playlist of foobar2000 that column is %length_ex% (in preferences/Display/Default user interface/Playlistview). How to create this column in SQlite?

Add a new field under 'Preferences > Media Library > SQLite utilities > Playlist table' using the attached configuration.

@VivinCels: Although the solution of @SimBun is totally correct, you should consider to use just the predefined column length_seconds for your comparisons as the evaluation is faster in general than using a title format based column.

If the value is too precise you can use the round() function to ignore (partially) fractions, e.g. round(length_seconds) to remove milliseconds at all or round(length_seconds,3) to get the same behaviour as provided by %length_ex%.

If you want to have formatted values for display purposes and you don't care about milliseconds you can also use format_length_hours(length_seconds).

New version, see first post.

Preferences window cut.

EDIT: Forgot to say, that I use Windows 11 at 125% DPI Scaling. With 100% its ok.

See: https://hydrogenaud.io/index.php/topic,116045.msg1010332.html#msg1010332

I'm enjoying the new component versions. Thank you for continuing to develop them. I nearly have all I need to jump to 64-bit fb2k now. The nodes load so fast that my attempts to get foobar to run under Wine in Linux might even get closer to success.

Naturally, though, I still have issues to raise.

Firstly, I'm still crashing when creating a new playlist by sending tracks to one, opening up the SQLite console, and executing SELECT * FROM Playlist. I noticed a new wrinkle, however. I reopened foobar after the crash and deleted the previously-new playlist, leaving just the default one. Then, sending another batch of tracks to a different new playlist, I could execute the console fine. Then, sending another batch of tracks into a third playlist, I crashed again. Is the total of open playlists somehow a factor? [ETA, in this test, I still have not yet put a SQL Tree in the layout and have been sending tracks with the DUI Album List. That's why there aren't any foo_uie_sql_tree files included.]

The second is a query of mine that no longer works which displayed the contents of foo_uie_sql_tree.sqlite_master. That table seems to no longer exist, which seems an odd one to go missing. I've tried querying it in an external program, SQLiteStudio, as well with similar problems. The query is something like this, executed in the SQLite console. [ETA: This and the rest of these issues are in my working foobar installation.]

ATTACH DATABASE 'profile/configuration/foo_uie_sql_tree.db' AS sqltree;


DROP TABLE IF EXISTS   sqltree_master;
CREATE TEMPORARY TABLE sqltree_master AS
SELECT   type
       , name
       , tbl_name
       , rootpage
       , sql
    FROM sqltree.sqlite_master
ORDER BY type
       , name
         ;


DETACH DATABASE sqltree;


SELECT *
  FROM sqltree_master

Third issue: in the properties window, I have to press the OK button twice when making changes to the MediaLibrary or Playlist tables. The first press acts like pressing Apply.

I have some issues in the SQL Tree as well, but I guess I'll just mention them here, if that's OK.

In the SQL Tree Edit query node Action tab, a previously set value for Target playlist appears blanked out if there is not currently a playlist of that name open in foobar. Pressing OK will overwrite the previous value with a blank one.

And then I have a feature request for the SQL Tree: I'd love for the scrollbars to be dark in dark mode.

Thanks again. Your components are the basis of my whole fb2k experience.

Preferences window cut.

EDIT: Forgot to say, that I use Windows 11 at 125% DPI Scaling. With 100% its ok.

See: https://hydrogenaud.io/index.php/topic,116045.msg1010332.html#msg1010332

See also: https://github.com/stuerp/foo_midi/issues/3

Quote from: grimes on 2023-04-24 07:20:29

Preferences window cut.

EDIT: Forgot to say, that I use Windows 11 at 125% DPI Scaling. With 100% its ok.

See: https://hydrogenaud.io/index.php/topic,116045.msg1010332.html#msg1010332

See also: https://github.com/stuerp/foo_midi/issues/3

As I cannot reproduce this problem on my Windows 11 systems, I asked @Case what he did to fix it and implemented his solution. Well, it's just more or less the opposite of the fix for foo_midi.

Could you please with the attached 64bit version of foo_sqlite, if the issue is fixed on your PC?

Almost. Table is cut a little bit. Sorry, scrollbar. Fixed.

Firstly, I'm still crashing when creating a new playlist by sending tracks to one, opening up the SQLite console, and executing SELECT * FROM Playlist.

Finally, I could reproduce the problem and fix it.

Is the total of open playlists somehow a factor?

Yes, this was in general the problem.

That table seems to no longer exist,

It does, of course, exist, but I guess, that the file foo_uie_sql_tree.db is not found as the current working directory of the applications seems to have changed. Therefore you need to attach the database like this:

ATTACH fb2k_profile_path()||'\configuration\foo_uie_sql_tree.db' AS sqltree;

Third issue: in the properties window, I have to press the OK button twice when making changes to the MediaLibrary or Playlist tables. The first press acts like pressing Apply.

Fixed.

In the SQL Tree Edit query node Action tab, a previously set value for Target playlist appears blanked out if there is not currently a playlist of that name open in foobar. Pressing OK will overwrite the previous value with a blank one.

Fixed.

I'd love for the scrollbars to be dark in dark mode.

I just forgot to add dark mode support to the tree panel itself. It's done now.

Fixed.

Thanks for testing.

Thanks for Logging: Off.

I've been playing with the vsv() function looking for other ways to handle some of my default data, and I was wondering if there was a way to bridge the standard/portable installation difference with respect to filenames with it. For instance, I have a script that begins like this.

CREATE VIRTUAL TABLE temp.csv USING vsv(filename='profile/moodflag/csv/custom_tags_dflt.csv'
                                      , header=1
                                        );


ATTACH DATABASE (SELECT fb2k_profile_path()
                     || '/configuration/foo_sqlite.db'
                        )
             AS sqlitedb
                ;

I can't put a SELECT fb2k_profile_path() in the first statement like I can in the second, since I seem to have left SQLite inside those parentheses of the vsv() function. Is there some slick bit of C I can stick in there to do the same thing?

I can't put a SELECT fb2k_profile_path() in the first statement like I can in the second, since I seem to have left SQLite inside those parentheses of the vsv() function. Is there some slick bit of C I can stick in there to do the same thing?

No, just omit the SELECT. It's not possible in the first case and not necessary in the second case. Further more you should use Windows path separators.

CREATE VIRTUAL TABLE temp.csv USING vsv(filename=fb2k_profile_path()||'\moodflag\csv\custom_tags_dflt.csv'
                                                                   , header=1
                                                                    );

ATTACH DATABASE fb2k_profile_path()||'\configuration\foo_sqlite.db' AS sqlitedb;

I changed my code to that, and it fails now at the first line with a SQLite Error. I've tried several variations: backslash, forward slash, double backslash, omitting the first slash, double quotes, single quotes. I can't make it work the way the plain relative-to-foobar.exe-directory way does.

hi,

when I execute my query in console: after execution 5000 row limit is reached, but in [advanced] the query node limit is 100000 set.
Is there something wrong or is this only in SQLlite console view?

I changed my code to that, and it fails now at the first line with a SQLite Error.

Ah, yes. The parameters are virtual table module arguments which are always taken literally and parsed and interpreted by the module. It means, there is no way to specify the profile path.

when I execute my query in console: after execution 5000 row limit is reached, but in [advanced] the query node limit is 100000 set.
Is there something wrong or is this only in SQLlite console view?

The query node limit is for the SQL Tree. The relevant advanced setting for the SQLite console is "SQLite console row limit"

Hi,
@fbuser thx for poining :-) "he query node limit is for the SQL Tree. The relevant advanced setting for the SQLite console is "SQLite console row limit""

Hi @fbuser,
If you ever get a chance for a feature request, one thing I would love to see someday is a Title Format function that would allow one to specify a SQL Select statement, and then get back the query result. 
Just my two cents.
Regards

s a Title Format function that would allow one to specify a SQL Select statement, and then get back the query result. 

This is not possible. The possibility to define title format functions for 3rdparty components was removed form the SDK almost 15 years ago. Apart from this such a function would probably cause more problems than it solves.

Anyway, for certain use cases the tagging functionality of foo_sqlite could be used, at least in a limited way. What exactly do you want to achieve with the mentioned title format function?

Hi, I just installed this component for the very first time. I wanted to play around with the lookup tables, but all the buttons in the lookup tables dialog are disabled.
How am I supposed to proceed?

Hi, I just installed this component for the very first time. I wanted to play around with the lookup tables, but all the buttons in the lookup tables dialog are disabled.

This should not be. Could you please post a screen shot of your dialog?

This should not be. Could you please post a screen shot of your dialog?

See the image attached.
FYI, I'm testing SQLite utilities 3.0.4 on a x64 2.0 fresh installation. I only configured the library (which is around 338.000 tracks) and a few properties dialog settings. No other user plugin is installed.
Thanks for helping.

See the image attached.

Ok. Actually, there is one situation, where all buttons are disabled: You didn't define any SQLite tag. The key of a lookup table can only be a SQLite tag. Furthermore any columns of a lookup table are also required to be SQLite tags, as they are defined in the SQLite tag dialog. In the lookup table dialog only the name and the key of the lookup table are specified.

Here the documentation is not clear. This will be changed.

In the lookup table dialog only the name and the key of the lookup table are specified.

I see, thanks for the clarification. I managed to create my first lookup table, but how should I populate it now? I tried to insert a record with the SQLite console, but it didn't work.
One more thing puzzles me: I was expecting the newly created SQLite tags to show up in the details section of the properties dialog, but they don't.

I managed to create my first lookup table, but how should I populate it now?

By populating the relevant tags in the properties dialog or by any other means to write tags. Just make sure that the "SQLite tagger" track info filter is active (see also in the help file under "SQLite Utilities - Preferences SQLite tags dialog").

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.

One more thing puzzles me: I was expecting the newly created SQLite tags to show up in the details section of the properties dialog, but they don't.

They are only shown for single files and only, if SQLite tags for the relevant file are written to the database.

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.

OK, I think I'm starting to grasp the logic of the component. 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?

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

As long as you don't activate "Forward read, if empty" and "Forward write" it is. However, for existing tagged files, the file tag values are cached and won't be neither automatically changed by just defining a SQLite tag for them nor the values will be written to the database. And as long as there is no entry for a file in the database, also no lookup values will be used.

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.

No, this is only one possibility. You could also for example just enter a value in the properties dialog or any other means, which let you write tag, e.g. masstagger scripts, using updatable virtual tables (MediaLibraryUpdatable, PlaylistUpdatable), etc. But for initializing the tag database it is probably the best way just to copy the actual file tags to the it.

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.

Also this depends on the configuration of the SQLite tag, see later.

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

In general by not activating "Forward read, if empty" for the relevant SQLite tag. In this case the actual file tag is ignored. But if you copy all file tags to the db using the default configuration for a certain lookup value, the last copied value will become the lookup value for all files.

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

Also this depends on the configuration of the SQLite tag. The relevant options are "Overwrite without marker" and "Only if empty" (see help file for further information), but in any case you can overwrite it by prepending the overwrite marker (default @@) to the new value.

Consider the following example:

Given are 3 tagged tracks without any SQLite tag or lookup table defined so far:
track1: Artist=Rockband; Genre=Rock
track2: Artist=Rockband; Genre=Rock
track3: Artist=Rockband; Genre=Pop

Now execute the following steps:

• 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".

A possible scenario for initializing the tag database and adjusting the genre tag afterwards could be:

• 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"

Hi everyone,

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.

Would you think it would even be possible to achieve such a task with sqlite ? I must admit I'm not familiar at all with it

thanks a lot for your feedback

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.

Once you have the plugin installed:

• 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;

NOTE:
The code runs against the tracks in the active playlist and produces a summary for you to verify.
I've assumed the current tag holding your track rating is called RATING, change that in the sql above if it is not.
I have no idea about how ratings are stored so at the moment it simply averages the tag.
It assumes the combination of <album,[album artist],date> uniquely identifies an album in your collection.

The above simply produces a listing showing the albumrating that could be stored.
I did see some discussion about how best it could be calculated, so I'm assuming this first pass will generate further discussion and evolve the sql.
When you're happy with the calculation we can modify the code to update your files, but I would want you to test that against a subset of your data first.

Hi SimBun,

thanks for the support!
Just had a try and it looks OK. The only obvious improvement I see would be to round up or truncate the returned results to 2 digits after the decimal point like 2.08 instead of 2.07692307692308

This could be enough for what I'm looking for. Even if weighting based on songs length - as I was suggested - could be fancier, I would prefer to keep it simple.

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

thanks again

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

If you want to exclude ALL tracks with a rating of 1 then it's trivial to do in the sql (I've excluded them in the sql below - "and rating <> 1"), otherwise, maybe you could base it on the length of the tracks.

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;

I've tested it myself and it looks fine, but please try this on a copy first and always make sure you have an up to date backup.

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  ;)

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

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

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 ?

The magic is that database operations act on an entire database and not just one entry in the database, in other words iteration is built into the "select... from" structure.

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  ;)

SQL reads row by row but you can produce aggregations using 'group' and other window functions.
There's really not a lot to the basic sql syntax so it's definitely worth getting comfortable with.

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

I was actually thinking more about how to exclude the intro/interlude/outro if it wasn't just these that were rated 1. Are they generally shorter, maybe the replaygain figures would be a good way to identify them.

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.

select *
  from Playlist
  where playlist_index = active_playlist()

Good to know.
Sure there are likely several ways to filter out unneeded tracks and calculate an 'accurate' average.
I'll be travelling for work this week so not much time to think about it but hopefully the week after I'll be able to

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  ;)

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  ;)

To sum length_seconds it's simply:

sum(length_seconds) as name_of_calculated_column

For the average you'd just need to use "avg" instead of "sum".

So in the original sql it would be:

  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;

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

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:

Looks good to me!

Thanks! Following this discussion:
- 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?

Btw, I have found round does not output numbers in a consistent format. Had to use padr() too

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

Otherwise I keep getting ratings as 2.0 and other times as 2.72, ... instead of always 2 decimals.

Hi Regor,
thanks for the correction. Unfortunately I'm not skilled enough to answer your questions but likely someone else will

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

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

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

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.

Code: [Select]

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

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

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

Thanks!

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

Ok, just seen it. Will try experimenting with it.

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).

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
...

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.

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).

Actually, currently it wouldn't work anyway. After checking the code I saw, that the virtual tables are not visible for the db connection, which is running the materialization. Will be changed for the next version.

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])

That were the wrong docs. You should always check only the SQLite docs as especially function implementations are not really standardized.

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

It's documented like that here (https://www.sqlite.org/lang_corefunc.html#format).

Does this plugin have any documentation? I'm hoping I can use this to sanitize my library tags without modifying the original files, but the question mark in the program leads to a file not found error and the documentation link on the download page leads to a blank wiki entry. Is there a site for this that I'm just not seeing? Id like to know what this component is capable of!

Does this plugin have any documentation?

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

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.

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".

File not found.

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.

File not found for all these as well. Exception being the one you called out, that one produces a little box that says "No help available for this page." Where do I download the documentation files?

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. But I guess the problem is caused by a wrong system setting. Clicking on the question mark or the help menu entry executes the application which is associated in your system with the file extension html. If this application can't be find, you will get a "File not found" error.

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

Thanks! Found them. Took a little digging but the component appears coded to look for the documentation in:
AppData\Roaming\foobar2000-v2\user-components-x64\foo_sqlite\help\

However, when Foobar2000 is installed from the Microsoft Store, the equivalent location is actually:
AppData\Local\Packages\Resolute.foobar2000modern_cg7j1awqsza28\LocalCache\Roaming\foobar2000-v2\user-components-x64\foo_sqlite\help

Looks like a minor bug.
Workaround might be to symlink the actual help folder to the location where the component is looking. That's non-trivial, but I might give it a try for convenience's sake if time allows.

Edit: I found some time and set up the symlink. It works! Question Mark in the app leads to the documentation as expected. The join is:
"\Appdata\Roaming\foobar2000-v2" to "\Appdata\Local\Packages\Resolute.foobar2000modern_cg7j1awqsza28\LocalCache\Roaming\foobar2000-v2"
I didn't want to just copy/paste the docs to the location since there's no telling if other functionality of the component is broken because of this. This way, any incorrect locations in the code will point to the things they're supposed to.

Hello, I just found out about SQLite for fb2k, I've been going for months thinking if it's possible to implement a tagging system and a few more other implementations using SQLite, The External Tags plugin for fb2k supports SQLite database so that it can add values to a database... But I'm not sure if there's a way for me to update these tags... Currently, my setup is as follows: Firstly, I use the context menu to edit external tags and set a custom field called FAVOURITE which has a value of ❤ and SQL Console correctly picks up this field...

What I want here is if it's possible to assign a button to run SQLite scripts behind the scenes to tag the currently selected item using Playlist.item_is_selected in an attempt to remove the value in the FAVOURITE field that way it simulates tagging and untagging tracks.

Secondly, I wanted to know if there is a way to have something similar to foo_playcount where you can set the last played and play count fields of a track...

them. Took a little digging but the component appears coded to look for the documentation in:
AppData\Roaming\foobar2000-v2\user-components-x64\foo_sqlite\help\

However, when Foobar2000 is installed from the Microsoft Store, the equivalent location is actually:
AppData\Local\Packages\Resolute.foobar2000modern_cg7j1awqsza28\LocalCache\Roaming\foobar2000-v2\user-components-x64\foo_sqlite\help

Well, the component actually does not look directly into the mentioned folders. It's using only functions provided by the foobar2000 SDK to identify the component's folder and going further down to the help folder inside of it. Anyway I'll try to track down the problem, if I find the time.

The External Tags plugin for fb2k supports SQLite database so that it can add values to a database...

Just to prevent you from thinking in the wrong direction: You definitely should not access its database with with foo_sqlite. Writing to this database is supposed to be done only by just using tagging means of foobar2000 or relevant plugins. It wouldn't work quite well anyway as the external tags plugin stores the tag data in a binary format.

By the way, foo_sqlite provides also the possibility to store tag data in a database, just with a different approach.

But I'm not sure if there's a way for me to update these tags...

As mentioned above, just use the available tagging means. One of it is to utilize the PlaylistUpdatable or MediaLibraryUpdatable virtual tables of foo_sqlite.

What I want here is if it's possible to assign a button to run SQLite scripts behind the scenes to tag the currently selected item using Playlist.item_is_selected in an attempt to remove the value in the FAVOURITE field that way it simulates tagging and untagging tracks.

That's not possible. However, you could have a look at foo_uie_sql_tree. It allows you to define clickable nodes to execute a sql script, which can do what you want.

Secondly, I wanted to know if there is a way to have something similar to foo_playcount where you can set the last played and play count fields of a track...

No, this is not possible. These information are handled internally by foo_playcount in its own database.

Hey thanks a-lot for pointing out some flaws... Will definitely check SQL trees and try to figure out something.

What I want here is if it's possible to assign a button to run SQLite scripts behind the scenes to tag the currently selected item using Playlist.item_is_selected in an attempt to remove the value in the FAVOURITE field that way it simulates tagging and untagging tracks.

I think easier to use Masstagger (https://www.foobar2000.org/components/view/foo_masstag) if you want a simple tag on/off button (https://www.reddit.com/r/foobar2000/comments/c7om2h/comment/esit1nz/).

Quote from: Azuriye on 2023-11-19 17:10:11

What I want here is if it's possible to assign a button to run SQLite scripts behind the scenes to tag the currently selected item using Playlist.item_is_selected in an attempt to remove the value in the FAVOURITE field that way it simulates tagging and untagging tracks.

I think easier to use Masstagger (https://www.foobar2000.org/components/view/foo_masstag) if you want a simple tag on/off button (https://www.reddit.com/r/foobar2000/comments/c7om2h/comment/esit1nz/).

Hey, I'm currently using that setup... while it's working just as expected it will be better to avoid writing tags directly to files and instead use External Tags or m-Tags. MusicBee has something similar to this called Virtual Tags, was just hoping if we had something similar to this with Foobar?

^ I think you just answered your own question. I'm under the impression when you create External Tags for an album, all future tagging (including Masstagger) is written to external tags, no? And I believe you can set it to write to SQL database, and take over all tagging operations automatically...

(We're getting off-topic here, best to move any follow-up questions to the correct topic)

Hi, I'm trying to wrap my head around this plugin, and its usage. I wonder if it is possible to use it to store information per artist and per album (like an internal wiki or reviews database) without writing this information to the audio files itself (and getting it "materialized" in the sqlite db). Thanks for any hints in the right direction.

Hi, I'm trying to wrap my head around this plugin, and its usage. I wonder if it is possible to use it to store information per artist and per album (like an internal wiki or reviews database) without writing this information to the audio files itself (and getting it "materialized" in the sqlite db).

Yes, it's possible. Just have a look at the SQLite tag functionality in the help file. It allows you to define, which of the defined tags should will be written only to the db and which will be also written to the file. Additionally you can define lookup tables for the artist and album tags containing the tags for the relevant information.

I'm trying to use foo_random_pools with foo_sqlite, but if I try to refer to any column that exists in the MediaLibrary table other than two that I added manually, I get a "no such column" error. I've tried entering examples from the foo_random_pools documentation verbatim (like putting

genre='Rock'

in the "Pool set SQL filter" field) in case the problem was that my syntax was bad, but I still get the same error. Any idea why this might be happening?

Any idea why this might be happening?

Please, post the full details of what you are trying to do and the full error message as well.

Never mind, it was apparently a problem with my CTE AS statement manifesting as an inability to recognize column names in other fields. Sorry to bother you!

Thanks for the great plugin.  I wrote the query below to combine the Series field and Genre field into a single column called TestGrouping.  I do NOT want to concatenate the two fields.

If the track has a Series, then I want the TestGrouping column to show the Series.
If the track does NOT have a Series, then I want the TestGrouping to show the Genre.

I added "series" to the MediaLibrary Table. It's set to TFExpression and it uses the %original album% field.
I also added "testgrouping" to the MediaLibrary Table. It's set to TFExpression.

The problem is that the query adds the Series where appropriate, but not the Genre when the Series field is empty.  I just get a question mark (?).

Here's the query:

SELECT
    COALESCE(series, genre) AS testgrouping,
    [album artist],
    grouping,
    album
FROM MediaLibrary
ORDER BY
    testgrouping DESC,
    [album artist],
    grouping,
    album;

Thanks for any help!

Windows 10
Columns 2.1.0
Foobar2000 v2.0
SQLite Utilities 3.0.4
SQLite Tree 4.0.6

PS - ignore my old signature below.

PS - ignore my old signature below.

If you update your signature, the update will appear on all your posts – not just your new posts.

If you update your signature, the update will appear on all your posts – not just your new posts.

I had changed my signature, but I guess I hit the wrong button or something.  It's updated now.

I figured out the problem.  I needed to add brackets ([]) around the Series field in the SQLite Utilities \ MediaLibrary table.  Why did that effect anything?  I noticed it removed the ? from the empty fields in the Query results.

I also had to change the Coalesce function:

SELECT
    COALESCE(NULLIF(series, ''), genre) AS testgrouping,
    [album artist],
    grouping,
    album
FROM MediaLibrary
ORDER BY
    testgrouping ASC,
    [album artist],
    grouping,
    album;

I also added "testgrouping" to the MediaLibrary Table. It's set to TFExpression.

This is not necessary and has no impact on your query.

I figured out the problem.  I needed to add brackets ([]) around the Series field in the SQLite Utilities \ MediaLibrary table.  Why did that effect anything?

It's just how title format works. It has actually nothing to do with SQL.

Awesome, thanks for the explanation.

Getting fb2k running under WINE in Linux, I saw that the font in the code portion of the SQLite console was not monospaced. I got it looking normal again by installing Courier New on the system.

Naturally, this prompts me to ask if there is a way to set that font. I couldn't find anything in the preferences. If not, that would be a nice little feature to tack onto the next version.

I'm not really familiar with SQL but I'm willing to learn.

I would like to create some statistical tags, e.g. the last time an artist was played or the average play count.

The SQLite tag creation window is not exactly self-explanatory. It would be good to have some examples since there doesn't seem to be any official documentation for the component.

there doesn't seem to be any official documentation for the component.

There is: select "SQLite utitilies" from the help menu.

However, you won't be able to achieve this

I would like to create some statistical tags, e.g. the last time an artist was played or the average play count.

with the SQLite tags.

How do I remove unused fields from the MediaLibrary table?  Is there a way to refresh the table or update it?

Thanks for any help!

How do I remove unused fields from the MediaLibrary table?  Is there a way to refresh the table or update it?

You can remove them in the preferences under "MediaLibrary -> SQLite utitilies -> MediaLibrary table"

Sorry, I meant to ask how do I delete the unused fields in bulk?  I know I can remove them individually, but I have a lot of unused fields now.  It's okay if it takes a long time to refresh the MediaLibrary Table.  Thanks!

Sorry, I meant to ask how do I delete the unused fields in bulk?

This is not possible.

I have a lot of unused fields now.

I doubt, that there are so many, that it is not feasible to remove them manually in a reasonable amount of time.

How do I properly access custom tag fields? If I add a "part" TFExpression with [%part%] to MediaLibrary table to get the value of a <PART> tag I added to many tracks, it always seems to return empty. Do I have to do something else to make the component add those tags to MediaLibrary table?

How do I properly access custom tag fields? If I add a "part" TFExpression with [%part%] to MediaLibrary table to get the value of a <PART> tag I added to many tracks, it always seems to return empty. Do I have to do something else to make the component add those tags to MediaLibrary table?

There is no need to create a TFExpression column for existing tags. It will only decrease the performance. Just use a normal tag column for it.

Apart from this I see no reason why you always get empty values, if you have your files have values for this tag. It definitely should work without further adjustments.

Hm interesting, it does work with a normal tag. Thx.