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.

Latest changes:
1.0:
- initial version, SQL functionality extracted from SQL Tree
- fixed: two or more update statements on the virtual tables in a batch were executed in parallel instead of one after another in autocommit mode (outside transaction)
- fixed: tracknumber was missing as default column for the virtual tables
-added: STRG-A selects the whole content in the SQLite console
- changed: Don't show an error anymore in the SQLite console, if the error code is "not an error"
- other: upgrade to SQLite 3.13.0

Download the latest version: here (http://www.foobar2000.org/components/view/foo_sqlite)
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.
SimplePortal 1.0.0 RC1 © 2008-2021