HydrogenAudio

Hosted Forums => foobar2000 => 3rd Party Plugins - (fb2k) => Topic started by: fbuser on 2016-08-02 10:34:07

Title: foo_sqlite
Post by: fbuser on 2016-08-02 10:34:07
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 2.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.

Download (https://www.foobar2000.org/components/view/foo_sqlite)
Version history (https://www.foobar2000.org/components/view/foo_sqlite/releases)
Title: Re: foo_sqlite
Post by: bas on 2016-08-19 21:48:21
Thank you for this component.

Question: Why are SELECT statements not case sensitive? A query like:
Code: [Select]
select composer from medialibrary where composer IS 'LUDWIG VAN BEETHOVEN'
still returns rows with 'Ludwig van Beethoven'.
Title: Re: foo_sqlite
Post by: fbuser on 2016-08-19 22:21:15
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:
Code: [Select]
select composer from medialibrary where composer IS 'LUDWIG VAN BEETHOVEN' collate binary
This won't return "Ludwig van Beethoven" anymore.
Title: Re: foo_sqlite
Post by: bas on 2016-08-20 12:02:01
Thank you, this solves it. I didn't think of collation.
Title: Re: foo_sqlite
Post by: UweCh on 2016-08-23 19:08:58
How can I implement my own icons?
Is there an example?
Title: Re: foo_sqlite
Post by: fbuser on 2016-08-23 21:38:03
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?
Title: Re: foo_sqlite
Post by: UweCh on 2016-08-24 06:18:36
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'
Title: Re: foo_sqlite
Post by: fbuser on 2016-08-24 22:45:56
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.
Title: Re: foo_sqlite
Post by: UweCh on 2016-08-25 06:32:19
Thank you very much for this info!
Can you get me an example?
That would help me a lot.
Thanks for the quick replies.
Title: Re: foo_sqlite
Post by: fbuser on 2016-08-27 19:08:36
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.
Title: Re: foo_sqlite
Post by: UweCh on 2016-08-28 15:04:04
Thank you very much for this instruction!!
Now, I can see my own Icons in the SQL-Tree!  :)
Title: Re: foo_sqlite
Post by: Phlexonance on 2016-10-03 11:19:21
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.
Title: Re: foo_sqlite
Post by: Phlexonance on 2016-10-03 11:49:28
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:

Code: [Select]
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.
Title: Re: foo_sqlite
Post by: fbuser on 2016-10-03 22:24:22
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:
Code: [Select]
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:
Code: [Select]
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).
Title: Re: foo_sqlite
Post by: foosion on 2016-10-10 20:06:02
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. ;)
Title: Re: foo_sqlite
Post by: kaeksen on 2016-11-05 23:30:17
Doesnt work.
Title: Re: foo_sqlite
Post by: UweCh on 2016-11-25 21:41:41
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?
Title: Re: foo_sqlite
Post by: fbuser on 2016-11-25 22:11:35
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.
Title: Re: foo_sqlite
Post by: Chris Norman on 2018-08-12 16:41:41
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.
Title: Re: foo_sqlite
Post by: fbuser on 2018-08-12 18:07:42
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:
Code: [Select]
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:
Code: [Select]
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?
Title: Re: foo_sqlite
Post by: Chris Norman on 2018-08-18 10:52:29
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:

Code: [Select]
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"?
Title: Re: foo_sqlite
Post by: fbuser on 2018-08-18 12:10:07
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:
Code: [Select]
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):
Code: [Select]
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:
Code: [Select]
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):
Code: [Select]
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.

Title: Re: foo_sqlite
Post by: foxyshadis on 2018-09-27 05:56:33
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!
Title: Re: foo_sqlite
Post by: fbuser on 2018-09-27 19:22:04
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:
Quote
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.
Title: Re: foo_sqlite
Post by: kugel on 2019-03-16 10:59:53
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:
Code: [Select]
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.
Title: Re: foo_sqlite
Post by: fbuser on 2019-03-16 11:54:33
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:

Code: [Select]
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
Title: Re: foo_sqlite
Post by: kugel on 2019-03-16 13:47:45
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...
Title: Re: foo_sqlite
Post by: fbuser on 2019-03-16 14:58:16
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||'%'
Title: Re: foo_sqlite
Post by: kugel on 2019-03-16 15:49:13
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 :)
Title: Re: foo_sqlite
Post by: sevenarts on 2019-08-21 05:20:50
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.
Title: Re: foo_sqlite
Post by: fbuser on 2019-08-21 18:58:40
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.
Title: Re: foo_sqlite
Post by: sevenarts on 2019-08-21 23:37:51
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.
Title: Re: foo_sqlite
Post by: fbuser on 2019-08-22 19:13:59
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:

Code: [Select]
select group_concat('''metadb_handle'',''||vtc_column_name||'''')
from mdb_VTableColumn
where vtc_table_type='0';
Title: Re: foo_sqlite
Post by: stevehero on 2020-12-20 00:40:21
Reply removed, was meant for SQL Playlist.
Title: Re: foo_sqlite
Post by: dchester@charter.net on 2021-02-17 02:51:55
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.
Title: Re: foo_sqlite
Post by: fbuser on 2021-02-17 06:30:33
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.
Title: Re: foo_sqlite
Post by: dchester@charter.net on 2021-02-19 00:45:58
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-25 23:46:05
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:

Here's the query. It looks at everything in the active playlist.
Code: [Select]
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.

Code: [Select]
                           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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-25 23:59:44
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.

Code: [Select]
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-26 11:19:36
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
Title: Re: foo_sqlite
Post by: fbuser on 2021-05-26 17:15:41
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.
Code: [Select]
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:
Code: [Select]
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:
Code: [Select]
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-26 23:18:40
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.
Title: Re: foo_sqlite
Post by: fbuser on 2021-05-27 05:33:25
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-27 12:35:22
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. :)
Title: Re: foo_sqlite
Post by: fbuser on 2021-05-27 17:44:03
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-28 00:36:41
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.  :)
Code: [Select]
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-28 01:52:40
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.


So it looks like disaster is averted, but I'll probably do a more full rebuild. Oh look, a crash report.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-28 12:04:06
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.
Title: Re: foo_sqlite
Post by: fbuser on 2021-05-28 16:04:29
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?
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-28 18:35:46
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.
Title: Re: foo_sqlite
Post by: fbuser on 2021-05-28 22:06:57
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-05-31 01:55:38
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:
Code: [Select]
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
 
Code: [Select]
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


Title: Re: foo_sqlite
Post by: fbuser on 2021-05-31 18:31:24
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-06-15 18:44:29
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?
Title: Re: foo_sqlite
Post by: fbuser on 2021-06-15 19:20:05
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?
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-06-16 12:27:04
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.
Code: [Select]
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
Title: Re: foo_sqlite
Post by: fbuser on 2021-06-16 18:21:12
Ok, with a simplified example I meant more something like this: ;D

Code: [Select]
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:
Code: [Select]
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-06-18 19:59:12
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.
Title: Re: foo_sqlite
Post by: fbuser on 2021-06-18 21:00:02
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:

Code: [Select]
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:
Code: [Select]
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
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.

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?
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-06-19 23:43:02
I don't seem to have any crash reports; the folder is empty.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-07-05 12:54:44
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.

Code: [Select]
UPDATE Playlist_Updatable
   SET (
       test
       ) = (
       '#1 · #2'
       )
 WHERE Playlist_Updatable.playlist_index = active_playlist()
   AND Playlist_Updatable.item_is_selected

Code: [Select]
SELECT    title
        , test
     FROM Playlist
    WHERE playlist_index = active_playlist()
      AND item_is_selected
Title: Re: foo_sqlite
Post by: fbuser on 2021-07-05 17:55:09
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-07-05 21:59:31
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

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

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

Code: [Select]
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
Title: Re: foo_sqlite
Post by: fbuser on 2021-07-05 22:31:31
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:

Code: [Select]
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2021-07-06 21:30:52
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.
Title: Re: foo_sqlite
Post by: SimBun on 2022-02-26 20:49:47
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
Title: Re: foo_sqlite
Post by: SimBun on 2022-02-27 09:54:35
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!
Title: Re: foo_sqlite
Post by: fbuser on 2022-02-27 10:24:19
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.
Title: Re: foo_sqlite
Post by: SimBun on 2022-02-27 17:12:53
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!
Title: Re: foo_sqlite
Post by: 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, 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.

Title: Re: foo_sqlite
Post by: fbuser on 2022-02-28 16:29:54
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.
Title: Re: foo_sqlite
Post by: SimBun on 2022-02-28 18:00:49
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!
Title: Re: foo_sqlite
Post by: fbuser on 2022-02-28 18:10:42
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.
Title: Re: foo_sqlite
Post by: SimBun on 2022-03-03 08:49:43
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.
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-03 18:38:53
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

Code: [Select]
SELECT * FROM MediaLibrary WHERE album COLLATE BINARY BETWEEN 'a' AND 'z';
to list all albums which start with a lowercase ascii letter

or
Code: [Select]
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.
Title: Re: foo_sqlite
Post by: SimBun on 2022-03-04 12:03:38
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
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-13 08:43:40
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.
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-13 08:46:47
A new version is out. Please, check the first post for details.
Title: Re: foo_sqlite
Post by: grimes on 2022-03-13 19:20:54
Typo: [16:46:31] [foo_sqlite] 16:46:31.124: Open datatabase
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-13 19:50:49
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.  :)
Title: Re: foo_sqlite
Post by: SimBun on 2022-03-13 22:38:49
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.
Title: Re: foo_sqlite
Post by: grimes on 2022-03-14 00:10:10
Want to give feedback. Fantastic work. Thank you.
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-14 06:26:07
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.
Title: Re: foo_sqlite
Post by: SimBun on 2022-03-14 09:11:41
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!
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-03-17 15:19:24
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.

Code: [Select]
/* 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:
Code: [Select]
-- 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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-03-17 19:14:38
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.
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-20 22:13:11
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.
Title: Re: foo_sqlite
Post by: SimBun on 2022-03-24 14:32:39
Quote from: fbuser
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 :-)
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-24 18:05:52
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.
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-24 22:37:15
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.
Title: Re: foo_sqlite
Post by: SimBun on 2022-03-25 16:23:18
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!
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-03-26 16:58:37
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.
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-26 22:24:24
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-03-28 10:16:27
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.
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-28 18:07:46
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-03-29 20:47:29
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 ' · '.

Code: [Select]
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-03-29 21:01:02
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
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-03-29 23:42:55
OK, I offloaded some backup and working tables to a different db, so I should go into the next crash a bit leaner.  :-[
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-03-31 10:20:35
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-03-31 11:56:15
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.
Title: Re: foo_sqlite
Post by: fbuser on 2022-03-31 22:39:54
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.
Title: Re: foo_sqlite
Post by: Sue Dunham on 2022-04-01 21:48:45
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?
Title: Re: foo_sqlite
Post by: fbuser on 2022-04-14 22:33:12
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.
Title: Re: foo_sqlite
Post by: For Serious on 2022-04-25 21:14:44
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.)
Title: Re: foo_sqlite
Post by: fbuser on 2022-04-25 21:57:53
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

Code: [Select]
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.
Title: Re: foo_sqlite
Post by: For Serious on 2022-04-26 15:27:32
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.
Title: Re: foo_sqlite
Post by: fbuser on 2022-04-26 15:35:05
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.
Title: Re: foo_sqlite
Post by: For Serious on 2022-04-26 18:21:26
Again, thank you very much! I've got all my queries working again.
Title: Re: foo_sqlite
Post by: grimes on 2022-05-10 17:46:44
error again.
Title: Re: foo_sqlite
Post by: fbuser on 2022-05-10 18:43:27
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?
Title: Re: foo_sqlite
Post by: grimes on 2022-05-10 18:52:46
Updated to 2.1.4.
SQL statement:
SELECT count(DISTINCT album) album_count
   FROM medialibrary
Title: Re: foo_sqlite
Post by: fbuser on 2022-05-10 19:26:34
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.