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)
Thank you for this component.
Question: Why are SELECT statements not case sensitive? A query like:
select composer from medialibrary where composer IS 'LUDWIG VAN BEETHOVEN'
still returns rows with 'Ludwig van Beethoven'.
Because the default collation of the columns of the MediaLibrary table is NaturalNoCase. You can change the collation for each column in the preferences : Media Library -> SQLite viewer -> MediaLibrary table to Binary, if you want to have always a case sensitive comparison, or you could rewrite your query as:
select composer from medialibrary where composer IS 'LUDWIG VAN BEETHOVEN' collate binary
This won't return "Ludwig van Beethoven" anymore.
Thank you, this solves it. I didn't think of collation.
How can I implement my own icons?
Is there an example?
How can I implement my own icons?
Is there an example?
There are not any icons in foo_sqlite. Do you mean perhaps foo_uie_sql_tree?
Oh sorry!!
Yes, I mean foo_uie_sql_tree!
I can not view custom icons there.
I Have already set the path under 'Config' + 'Advanced'
What is the content of the path=
Only custom icons, which are placed into a bitmap file with a height of 16px and a with of multiple of 16px, for example a bitmap file with 16x48 pixels would contain 3 icons.
Thank you very much for this info!
Can you get me an example?
That would help me a lot.
Thanks for the quick replies.
First of all you need a set of icons with the dimensions 16x16 px. With an image processing application you can combine these icons to one image file and save it as BMP file.
This can easily done with IrfanView:
- open "Image -> Create panorama image ..."
- Click on "Add images" and add your icon files
- Click on "Create image" and save the result as "BMP - Windows Bitmap".
- Add the full path of the created image to foobar2000 under "Preferences -> Advanced -> Tools -> SQL tree -> Icon file paths". You can add multiple of such images by separating their paths with |.
- Restart foobar2000 to get the images loaded.
Thank you very much for this instruction!!
Now, I can see my own Icons in the SQL-Tree! :)
Are there any examples on how to select songs from a playlist?
What I want to do is generate a playlist C which contains every song in playlist B, except the songs which are in playlist A.
Are there any examples on how to select songs from a playlist?
What I want to do is generate a playlist C which contains every song in playlist B, except the songs which are in playlist A.
So I also got foo_uie_sql_tree which comes with an SQL statement creating a "Playlist" table, which I queried.
Figured out that comparing filenames between playlists works best (since some of the tags are empty) and the resulting SQL statement is:
SELECT filename FROM Playlist WHERE playlist_index=3
EXCEPT
SELECT filename FROM Playlist WHERE playlist_index=2
I have no clue how to create a Playlist from SQL, but foo_uie_sql_tree allows for creation of Playlists from SQL statements.
I have no clue how to create a Playlist from SQL, but foo_uie_sql_tree allows for creation of Playlists from SQL statements.
First you neead to create a virtual table, which allows modifying playlists:
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split,playlist);
Then you can add items to a playlist (T), which belong to one playlist (A) but don't belong to another playlist (B) with this statement:
INSERT INTO Playlist_Updatable (metadb_handle,playlist_name)
SELECT metadb_handle,'Playlist T'
FROM Playlist a
WHERE playlist_name='Playlist A'
AND NOT EXISTS (SELECT 1
FROM Playlist
WHERE playlist_name='Playlist B'
AND filename=a.filename)
If the playlist 'Playlist T' does not exist, it will be created (unless it was already existing and was deleted before the insert statement was executed. This is a bug and will be fixed with the next release).
More infomation for this topic you can find here (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/MetaDb_Module.html).
There is a bug in the online documentation at http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html). The links to the help pages are absolute file:// URLs. Relative links would work better. ;)
Doesnt work.
I have a small graphical problem with "SQL Tree"
Whenever "Database activity ..." appears at the bottom, the graphical view of the vertical scroll bars will briefly change.
Is this a known problem?
There is a bug in the online documentation at http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html). The links to the help pages are absolute file:// URLs. Relative links would work better. ;)
Thanks for the hint. Of course, the links should've been relative. Will be fixed soon.
Doesnt work.
What does not work?
I have a small graphical problem with "SQL Tree"
Whenever "Database activity ..." appears at the bottom, the graphical view of the vertical scroll bars will briefly change.
Is this a known problem?
This has again nothing to do with foo_sqlite. But to answer the question: It's not a problem, it's intended. During database activities, the scrollbars will be disabled.
I was trying a couple of things but I was not able to achieve the following:
I wanted to list and insert all the content from an play list A which is not part of media library.
How to achieve this?
Thanks in advance.
I wanted to list and insert all the content from an play list A which is not part of media library.
How to achieve this?
The listing can be done with this query:
SELECT *
FROM Playlist p
WHERE playlist_name='A'
AND NOT EXISTS (SELECT 1
FROM MediaLibrary
WHERE path=p.path)
In case you have many items in your media library the following sequence of sql statements might be faster:
CREATE TEMPORARY TABLE tmp_Path
AS SELECT DISTINCT path
FROM MediaLibrary;
CREATE UNIQUE INDEX tmp_Path_in_unique ON tmp_Path(path);
SELECT *
FROM Playlist p
WHERE playlist_name='A'
AND NOT EXISTS (SELECT 1
FROM tmp_Path
WHERE path=p.path)
Where do you want to insert the result?
Thanks for the reply. I played a little bit around with the code.
Unfortunately the code doesn't suit my purpose. Your example compares on path but the path will be different because these are different files.
I had difficulties with the exists syntax and I tried a join instead. For speed reasons I created two temp tables:
DROP TABLE IF EXISTS ml_temp;
CREATE TEMPORARY TABLE ml_temp
AS SELECT DISTINCT artist,album,title,codec
FROM MediaLibrary;
DROP TABLE IF EXISTS Test;
CREATE TEMPORARY TABLE Test
AS SELECT DISTINCT artist,album,title,codec
FROM Playlist p
WHERE playlist_name='Test';
SELECT DISTINCT p.artist,p.album,ml.codec AS lib_codec,p.codec AS logs_codec
FROM Test p
JOIN ml_temp ml
ON ml.artist=p.artist
AND
ml.album=p.album
So this gives me a visual list of all albums which exist in Playlist "Test" and the media library.
Is there any way to have this fed back into a new Playlist "Result"?
Unfortunately the code doesn't suit my purpose.
But it's doing what you actually asked for. :)
So this gives me a visual list of all albums which exist in Playlist "Test" and the media library.
Which is the opposite from your original question. :)
Is there any way to have this fed back into a new Playlist "Result"?
No, this is logically not possible, because only tracks can be added to a playlist, not summarized results like in your query. However you could either remove the tracks from the test playlist, which don't exist in the media library or add the matching tracks to another playlist.
To do this you first need to create another virtual table for the playlists, which allows you to manipulate them:
DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split,playlist);
The deleting of the unmatched tracks could be done with (not possible in this case without NOT EXISTS):
DELETE FROM Playlist_Updatable pu
WHERE playlist_name='Test'
AND NOT EXISTS (SELECT 1
FROM ml_tmp
WHERE artist=pu.artist
AND album=pu.album)
If you don't want to list the result it's not necessary to add the columns title and codec to the table ml_tmp and also an index to speed up the lookup on this table should be created. It could look like this:
DROP TABLE IF EXISTS ml_temp;
CREATE TEMPORARY TABLE ml_temp
AS SELECT DISTINCT artist,album
FROM MediaLibrary;
CREATE UNIQUE INDEX ml_temp_in_unique on ml_tmp(artist,album);
Adding the matching tracks to another playlist could look like this (if the target playlist, doesn't exist, i will be automatically created):
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT metadb_handle,'New playlist'
FROM Playlist_Updatable /* or just Playlist, it doesn't matter */ pu JOIN ml_temp ml ON ml.artist=pu.artist AND ml.album=pu.album
WHERE playlist_name='Test'
Note1: I didn't test the SQL statements, so they might be not 100% correct.
Note2: You will run into troubles, if the combination of artist and album is not unique, i.e. you have more than one album with the same combination, but this depends on the content of your media library and your individual tagging guidelines.
What I wanted to do with this, remove old crap data that foobar was holding on to instead of rescanning the files, doesn't appear to be possible. Whether I try to update or delete the data, I always get 'SQLite Error: (8) attempt to write a readonly database'. Is that an absolute restriction? Do I have to use external SQLite editors to do this?
As an aside, I checked the entire documentation looking for just one single sample query for the most basic media library query, but there was not one. I don't know the structure of the database, and I guessed at a few things based on this thread. I feel like the entire documentation is a bit bereft without the fundamental basics, or at a bare minimum a link to its structure explained elsewhere. It doesn't do any good to have a tool to query if you don't know what you're querying!
What I wanted to do with this, remove old crap data that foobar was holding on to instead of rescanning the files, doesn't appear to be possible.
No, it isn't.
Whether I try to update or delete the data, I always get 'SQLite Error: (8) attempt to write a readonly database'. Is that an absolute restriction?
No, but you should have a look at the documentation here (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html). It's explained there, which data manipulation commands are available and what needs to be done to use them.
Do I have to use external SQLite editors to do this?
You can't. The MediaLibrary and Playlist virtual tables are only available inside of foobar2000.
As an aside, I checked the entire documentation looking for just one single sample query for the most basic media library query, but there was not one.
Yes, you're right. Originally foo_sqlite was a part of foo_uie_sql_tree, which comes with some example nodes and there relevant documented queries.
I don't know the structure of the database,
There is nothing you need to know besides what you can find in the documentation. There are only a very few internal tables, which are not meant to be used by the user.
It doesn't do any good to have a tool to query if you don't know what you're querying!
It's all written in the documentation:
For querying the foobar2000 media library a SQLite virtual table is provided which includes besides all available tags several other fields per default. The fields of the virtual table can be customized in the preferences. Similarily also a virtual table is provided for querying the playlists. The virtual table names are: MediaLibrary and Playlist.
I want to use foo_sqlite to generate playlist for syncing with my iPod. The playlist should contain all albums with an average rating > 3 and all albums that have at least one track with missing rating. Additionally, I want to reduce the number of items in a playlist by filtering the files by their path. This is the code that I hacked together:
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, b.playlist_name, a.path, minr, avgr
FROM MediaLibrary a, (SELECT 'SQL_>M' "playlist_name") b
JOIN
(
SELECT a.metadb_handle, a.album, min(a.rating) minr, avg(a.rating) avgr, a.path
FROM MediaLibrary a
WHERE substr(a.path, 1, 9) = 'D:\Metal\' AND substr(a.path, 10, 1) > 'M'
GROUP BY a.album
HAVING minr='' OR avgr>3
) albums
ON a.album = albums.album
It seems to work. However, it also returns tracks that do not match the WHERE clause, e.g. there are some tracks from "D:\Rock\" and some from "D:\Electronic\". Any idea what my mistake is? I have no clue about SQL queries so most likely there is a better query for my intended use.
It seems to work.
Well, at least the statement, which you posted here, won't be executed as the syntax is wrong. Anyway, try this:
WITH Albums AS (
SELECT album
FROM MediaLibrary
WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
GROUP BY album
HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album
Oh sorry I forgot to remove the extra columns I used for debugging without "INSTER INTO..."-line. I used the columns to directly verify whether the criteria where applied.
Your code looks much more clean, but it does also pull in tracks from other folders. There are more tracks in the list than there are in the Metal-folder. With your code I can also not see the results in the console if I remove the "INSTER INTO..."-line. I do not understand how the tracks from other folders make it into the list...
but it does also pull in tracks from other folders.
This is only possible, if you have have albums from the metal folder with the same name in other folders. In this case you could try this (assuming that all selected album names are distinct):
WITH Albums AS (
SELECT album, max(substr(path,1,10)) path
FROM MediaLibrary
WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
GROUP BY album
HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album AND a.path LIKE Albums.path||'%'
but it does also pull in tracks from other folders.
This is only possible, if you have have albums from the metal folder with the same name in other folders. In this case you could try this (assuming that all selected album names are distinct):
WITH Albums AS (
SELECT album, max(substr(path,1,10)) path
FROM MediaLibrary
WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
GROUP BY album
HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album AND a.path LIKE Albums.path||'%'
Ah that's it! Thank you for your help. I am happy with your latest query suggestion :)
I have what I think is a very simple use case but can't quite figure out how to get it to work. I want to simply extract all fields from my database and save off to a CSV to use elsewhere.
What's the syntax for using the writefile command? Is it possible to save the output of a query into a file?
I can just copy the results right out of the console as well but then the data comes without the headers which is less than ideal. If it's possible to easily add/get the headers that'd be a solution for me too.
Although not impossible writefile is probably not suitable for your purpose, as you can only write one single value for each record to a different file with it.
But you can just do a "SELECT * FROM MediaLibrary" in the SQLite console, write click on the result and copy all. You will get the content of the result window in a CSV format (separator is a comma and field delimiter a single quote) copied to the clipboard.
You might need to increase the SQLite console row limit in "Preferences -> Advanced -> Tools ->SQLite viewer" to get all your data listed. In case you have a large library you also might consider to split the result sets to a reasonable size to not run into memory problems.
As I said I did try what you suggest - but the headers aren't extracted with the results. Any easy way to grab the headers? Adding them manually will be a pain.
Directly it's not possible, but although it is a bit of a hack and might not work anymore in the future, you can get the headers separately with this SQL:
select group_concat('''metadb_handle'',''||vtc_column_name||'''')
from mdb_VTableColumn
where vtc_table_type='0';
Reply removed, was meant for SQL Playlist.
Hi, I'm trying to write a component (my first attempt at one), that would involve running a few queries against your playlist virtual table. I'm thinking that for my component to query the virtual table, I would need the pointer to your module (functions) MetaDB_Module, to use in the sqlite function sqlite3_create_module(), (I'm also learning SQLite). Is my assumption correct? Can you provide any guidance for how to enable my component to utilize what you've so wonderfully done?
My hope is to be able to get a few fields from/about the prior song and the next song, and then place them into Title Formatting variables. Then I can display using the foo_osd component on a secondary monitor. 30 years ago I was a fair coder, so this project will give me something to do while I wait for covid to be over.
Any advice is very much appreciated.
Hi, I'm trying to write a component (my first attempt at one), that would involve running a few queries against your playlist virtual table.
I'm sorry, but this is not possible.
I'm thinking that for my component to query the virtual table, I would need the pointer to your module (functions) MetaDB_Module, to use in the sqlite function sqlite3_create_module(), (I'm also learning SQLite).
No, it would work a bit different, if it would be possible.
Is my assumption correct? Can you provide any guidance for how to enable my component to utilize what you've so wonderfully done?
I've created a SDK to make such things possible, for example for foo_uie_sqlite_tree, but this SDK is not ready to be published and probably will never be.
No, it would work a bit different, if it would be possible.
I've created a SDK to make such things possible, for example for foo_uie_sqlite_tree, but this SDK is not ready to be published and probably will never be.
Thank you for replying. It was easy to write the queries to get the data I wanted for my project. I guess instead, I'll have to figure out where/how foobar stores the running playlist internally, and how to get the data that way. I'm guessing it will take a while for me to figure that all out. FWIW, if you ever decide that you want someone to test your SDK, let me know. ;)
Take care.
I'm having trouble getting my head around conditionals in regex (standard info (https://www.regular-expressions.info/conditional.html)). For example, here's a query for parsing opuses out of the titles of classical pieces. Ultimately I want to make an
UPDATE out of it, but the
SELECT suffices to see what's going on. The parts I'm interested in are in the first lines of the hairy, concatenated regex patterns. In reality, I don't think I want all of these groups, but it helps to see what's happening if they're there now.
The query's three columns are:
- ok: A working demonstration of a parsed title using a lookahead in the first group to capture everything before "Op", "BMW", or the like. Heaven knows how hairy this will grow when trying to work with many albums, and the options in the first line of reg.lookahead need to be written twice the way I have it now.
- notok: A conditional approach that fails in all cases. I'm trying to catch everything before the opus by seeing if the opus group participates in the overall match. I haven't been able to do this.
- worksortorder: A sorting field formed from the numerical parts of the opus and the number, if present. By that I mean that "Op. 1" will yield "0001-00" and "Op. 2 No. 1" is "0002-01".
Here's the query. It looks at everything in the active playlist.
WITH re AS (SELECT '^(.*(?=[Oo]p|BMW|bmw|KV?))?'
|| '(([Oo]p|BMW|bmw|KV?)\.? ?(?:[Pp]osth?\.? ?)?(\d+))?'
|| ' ?([Nn]o\.? (\d+))?(.*)$'
AS lookahead
, '^((?(2).*))' -- This is the broken part
|| '(([Oo]p|BMW|bmw|KV?)\.? ?(?:[Pp]osth?\.? ?)?(\d+))?'
|| ' ?([Nn]o\.? (\d+))?(.*)$'
AS conditional
)
SELECT regexp_replace(re.lookahead, '$1—$2—$3—$4—$5—$6—$7', title)
AS ok
, regexp_replace(re.conditional, '$1—$2—$3—$4—$5—$6—$7', title)
AS notok
, nullif(padl(regexp_replace(re.lookahead, '$4', title), 4, '0')
|| '-'
|| padl(regexp_replace(re.lookahead, '$6', title), 2, '0'), '0000-00')
AS worksortorder
FROM Playlist, re
WHERE playlist_index = active_playlist()
And here's some sample output. I've omitted the
notok column for space considerations, but it fails in every row as the second and third rows of the
ok column do here—lacking opuses altogether. Basically, nothing is found and the whole title goes into the final
(.*) group. That is how I want such lines handled, but not every line! I've also edited the "No. 1" out of the first part of "Op. 9"'s title just to see what happens.
ok worksortorder
Nocturne in E Minor, —Op. Post. 72—Op—72—No. 1—1—: Andante 0072-01
——————Nocturne in C-sharp minor (1830): Lento con gran espressione
——————Nocturne in C minor (1837)
3 Nocturnes, —Op. 9—Op—9———in B-flat minor: Larghetto 0009-00
3 Nocturnes, —Op. 9—Op—9—No. 2—2— in E-flat major: Andante 0009-02
3 Nocturnes, —Op. 9—Op—9—No. 3—3— in B major: Allegretto 0009-03
3 Nocturnes, —Op. 15—Op—15—No. 1—1— in F major: Andante cantabile 0015-01
3 Nocturnes, —Op. 15—Op—15—No. 2—2— in F-sharp major: Larghetto 0015-02
3 Nocturnes, —Op. 15—Op—15—No. 3—3— in G minor: Lento 0015-03
2 Nocturnes, —Op. 27—Op—27—No. 1—1— in C-sharp minor: Larghetto 0027-01
2 Nocturnes, —Op. 27—Op—27—No. 2—2— in D-flat major: Lento sostenuto 0027-02
2 Nocturnes, —Op. 32—Op—32—No. 1—1— in B major: Andante sostenuto 0032-01
2 Nocturnes, —Op. 32—Op—32—No. 2—2— in A-flat major: Lento 0032-02
2 Nocturnes, —Op. 37—Op—37—No. 1—1— in G minor: Andante sostenuto 0037-01
2 Nocturnes, —Op. 37—Op—37—No. 2—2— in G major: Andantino 0037-02
2 Nocturnes, —Op. 48—Op—48—No. 1—1— in C minor: Lento 0048-01
2 Nocturnes, —Op. 48—Op—48—No. 2—2— in F-sharp minor: Andantino 0048-02
2 Nocturnes, —Op. 55—Op—55—No. 1—1— in F minor: Andante 0055-01
2 Nocturnes, —Op. 55—Op—55—No. 2—2— in E-flat major: Lento sostenuto 0055-02
2 Nocturnes, —Op. 62—Op—62—No. 1—1— in B major: Andante 0062-01
2 Nocturnes, —Op. 62—Op—62—No. 2—2— in E major: Lento 0062-02
So, any tips on how I can get that conditional approach to work? Barring that, are there any other good ways to accomplish this sort of thing? I imagine that better minds than mine have done something like this.
I have what I think is a very simple use case but can't quite figure out how to get it to work. I want to simply extract all fields from my database and save off to a CSV to use elsewhere.
I've had some success saving what I need to another
.db file, which can be manipulated with
sqlite3 or something like SQLite Studio (https://sqlitestudio.pl/) to get
CSVs. This grabs the whole MediaLibrary as well as the internal table that holds all of the
SQL Tree queries.
VACUUM;
ATTACH DATABASE 'export\foobarlibrary.db' AS 'LibraryDB';
DROP TABLE IF EXISTS LibraryDB.MediaLibrary;
CREATE TABLE LibraryDB.MediaLibrary AS
SELECT *
FROM MediaLibrary;
DROP TABLE IF EXISTS LibraryDB.ust_TreeItem;
CREATE TABLE LibraryDB.ust_TreeItem AS
SELECT *
FROM ust_TreeItem;
DETACH DATABASE 'LibraryDB';
At the moment, it just puts the
db in a folder called
export off of the foobar2000 root directory. I forget if this fails if the folder has not yet been created, so that may happen. I'd love some way to get another folder in there—perhaps even a user-selected one—but that's probably not SQLite's job. Sure, one could just type in another location, but I'm trying to keep it somewhat generic here.
Long as I'm here, I'd like to make a feature request: The third answer down on this stackoverflow post (https://stackoverflow.com/questions/3845718/update-table-values-from-another-table-with-the-same-user-name) mentions that UPDATE FROM queries are available as of SQLite version 3.33.0. I've been having trouble implementing any of the solutions given there, but that particular way may be what I really want when I write tags. Is there any chance you can bump up the SQLite version? Thanks. ;D
I'm having trouble getting my head around conditionals in regex ...
I didn't have a closer look at your code, but here is the code which I use to move a featuring artist from title to an own tag. You should be able to adapt it for your needs.
I'm using a permant table for my regular expressions. The expression searches for common occurrances of featuring artist with or without braces in the track title.
CREATE TABLE RegularExpressions
(
name TEXT PRIMARY KEY,
expression TEXT
);
INSERT INTO RegularExpressions
VALUES('Featuring','(?(DEFINE)'||
'(?<open-braces>[({\[])'||
'(?<close-braces>[)}\]])'||
'(?<no-braces>[^(){}\[\]])'||
'(?<content-1>(?i)f(ea)?t((\.\s*)|\s+))'||
'(?<content-2>(?i)featuring\s+)'||
'(?<content>\b(?&content-1)|(?&content-2))\b'||
')'||
'(?:(?:\s*(?&open-braces)(?&no-braces)*)?|\s+)(?&content)(?<featuring-artist>(?&no-braces)+)(?:(?&close-braces)\s*)?');
For updating the tags I'm using this statement:
WITH RegularExpression AS (
SELECT expression
FROM RegularExpressions
WHERE name='Featuring'
)
UPDATE PlaylistUpdatable
SET "featuring artist"=regexp_replace((SELECT expression FROM RegularExpression),
'$+{featuring-artist}',
title,
1),
title=regexp_replace((SELECT expression FROM RegularExpression),
'',
title)
WHERE "featuring artist" IS NULL AND
playlist_index=active_playlist() AND
item_is_selected;
The updatable virtual playlist table is created with this code:
CREATE VIRTUAL TABLE `PlaylistUpdatable` USING MetaDB_Module(no_multivalue_split,playlist)
At the moment, it just puts the db in a folder called export off of the foobar2000 root directory. I forget if this fails if the folder has not yet been created, so that may happen. I'd love some way to get another folder in there—perhaps even a user-selected one—but that's probably not SQLite's job. Sure, one could just type in another location, but I'm trying to keep it somewhat generic here.
Well, currently this cannot be done with foo_sqlite and at the moment I don't have any plans to support this. But why do you need to write the tables to another SQLite db? You can perfectly access the db from foo_sqlite with an external tool. Only the virtual tables are not usable with it.
Is there any chance you can bump up the SQLite version?
This will definitely happen, but at the moment I cannot make any promises, when it will be. I'm currently working on some updates, which need to be finished first. It should be somewhen this year, but I cannot guarantee it.
Thanks for the code help. I'll have to study it a bit to learn what it's doing, but that does look like a good way to handle this. I'm glad to hear that an update is in the offing when it comes; thanks for all that you've done.
But why do you need to write the tables to another SQLite db? You can perfectly access the db from foo_sqlite with an external tool. Only the virtual tables are not usable with it.
Could you say a little more about this? Part of my reasons for exporting is just for the sake of backing up all of my tags, but I didn't know of any ways to access
foo_sqlite externally. Is it simply a matter of attaching
foo_sqlite.db and executing a saved view? I haven't been able to read any of the data that way, but maybe I could make buttons that duplicate clicking on
foo_uie_tree_view nodes? Maybe just make those node queries all
SELECT * FROM view_blahblahblah (or bypass the treeview altogether) and work with
view_blahblahblah from a Spider Monkey Panel? That would be sweet.
Could you say a little more about this? Part of my reasons for exporting is just for the sake of backing up all of my tags,
For backing up your tags and all your settings you just need to backup the folder with your user data (the location is dependent of the installation type and the foobar2000 version). You can also use foo_jesus to automate this task.
but I didn't know of any ways to access foo_sqlite externally. Is it simply a matter of attaching foo_sqlite.db and executing a saved view?
It's actually just opening the database with an external tool and work with the saved data.
I haven't been able to read any of the data that way,
This is strange. I no problems to access the db with a tool like this (https://sqlitebrowser.org/).
but maybe I could make buttons that duplicate clicking on foo_uie_tree_view nodes?
This would be a possibility. The upcoming version of foo_sqlite will support the materialization of the virtual tables. This will be done automatically in the background.
Maybe just make those node queries all SELECT * FROM view_blahblahblah (or bypass the treeview altogether) and work with view_blahblahblah from a Spider Monkey Panel? That would be sweet.
This is not possible as a SMP doesn't have access to the virtual tables.
I'm probably not being clear and jumbling many different ideas together. Indeed, I don't come to foobar knowing any of this; rather, I'm using foobar to learn all this. It's a hell of a lot more fun than all the tutorials droning on about company personnel, inventory, and quarterly sales. :)
I've used SQLiteStudio just to look into the dbs, and only now am I realizing that I can start working in there to create views and whatnot rather than going through the SQLite console or the treeview batch tab in foobar for everything. Cool!
But when I say that I can't read the data, I guess I mean that I can't read the contents of the virtual tables or any views that refer to them. I have, in one instance, created a permanent table and saved data to it in order to export an album list, and I can read the contents of that just fine. (I didn't actually mean to leave it there, and it gets dropped and recreated before each such export. I guess I should drop it when I'm done, or use a temporary table, or…) That's just by album and doesn't have records for each track, but the query I pasted above could certainly be redirected within foo_sqlite.db if desired. This is what you see in your db?
I guess I wanted to put things like that in a separate db because I had in mind the examples of other music players that could export metadata to xml files or what have you, and I hadn't yet found any other ways in foobar to achieve something like that. I also didn't want to make foo_sqlite.db bigger than it had to be. That directory I saved my db to is just a temporary location since I haven't figured out how to supply a directory more specific to my current computer short of explicitly writing it into the query, which I don't want to do. Once I've created it, I move it by hand. Perhaps I could learn some code for moving it from the place SQLite leaves it to where I want to put it, but I haven't gotten to that bit yet. :)
This is what you see in your db?
Well, the main idea behind foo_sqlite is to have the possibility to query the media library with SQL. While title format queries are sufficient in many cases, they cannot be used for all kind of queries, which needs to query the tags from more than one track, e.g. get all tracks from albums, which have a track with a rating of 5. With title format queries you can only get the single tracks, which have a rating of 5, while it is possible with SQL to get also all other tracks of these albums, which don't have a ratnig of 5.
The virtual tables are just a SQL wrapper for the media library or the playlists, which are always completely loaded into memory. The foobar2000 SDK provides means to access these in-memory data. That's the reason, why you can only access the virtual tables from the SQLite console and not from external tools.
OK, weird errors. I was trying to redo my query along the lines you showed me. I was having trouble getting the results I wanted, but it was doing its thing and giving me one row of something and the rest NULL. I also had
foo_sqlite.db open in SQLiteStudio to modify my regex table.
I commented out a line in that regex and replaced it with a bunch of
(.*) just to see if I could get something. It looked like this below. There may be errors in it, but I'm not asking about those. :)
DROP TABLE IF EXISTS regexes;
CREATE TABLE regexes (
name TEXT UNIQUE PRIMARY KEY,
expression TEXT
);
INSERT INTO regexes
VALUES (
'work' -- opus and number
, '(?(DEFINE)'
|| '(?<worktext>(?i)\b(?:opp?|bwv|kv?)\.?\b)?'
|| '(?<posth>(?i)\bposth?\b)?'
|| '(?<num>(?i)no)?'
|| ')'
-- || '(?&worktext)\.?\s*(?&posth)\.?\s*(\d+)?\s*'
-- || '(?&num)\.?\s*(\d+)?'
|| '(.*)(\d+)?(.*)(\d+)?'
);
I ran that in SQLiteStudio and went back to the SQL console in foobar to rerun my SELECT there, and it seemed to hang. I aborted and tried again. Foobar crashed.
After restarting, the SQL Tree was no longer displayed. I tried to rebuild
Playlist_Updatable but it failed due to a duplicated column. I tried the same with the
Playlist table but could not. Here's the error output for that.
Execution error:
SQLite Error: (1) Unable to declare virtual table Playlist:
SQLite Error: (1) duplicate column name: asin
Error while preparing the first statement of:
DROP TABLE IF EXISTS Playlist;
CREATE VIRTUAL TABLE Playlist USING MetaDB_Module(add_defaults);
COMMIT;
Looking at the
Playlist table in Preferences, I see that most of the fields have been duplicated. I put a screencap below.
I done broke it good. :o I guess I'll try manually removing fields via Preferences, but it pretty much doubled all of them after artist.
OK, I removed the duplicate columns and restarted. It came up with the default nodes in the treeview. Running
SELECT * FROM ust_TreeItem in the console showed no rows.
- Uninstalled/reinstalled foo_uie_sql_tree. No change.
- Uninstalled/reinstalled foo_uie_sql_tree and foo_sqlite. No change.
- Noticed that foobar 1.6.6 is out, made a new portable installation, and copied my profile folders over. Treeview is restored, but ust_TreeItem is still showing no rows, even from a different installation's copy of foo_uie_sql_tree.db.
So it looks like disaster is averted, but I'll probably do a more full rebuild. Oh look, a crash report.
Just to be clear, I'm merely reporting my experience. You do not need to resolve anything for me now, though I can try any suggestions you may have if you need to know more.
I do think though that maybe I ought to just work my new stuff out entirely within SQLiteStudio—in this handy export database I happen to have right here—and then just paste my final working version into foobar. :))
Ooh, one other thing I've been noticing: the Playlist_Updatable table seems to get corrupted if I'm moving things around from playlist to playlist, removing playlists, or something along those lines. My WHERE playlist_index = active_playlist() will exclude everything because that field will no longer contain playlist indexes. I've seen it contain different things at different times: nothing, incremented numbers, or the file paths of the tracks, if I'm remembering everything correctly. Dropping that table and recreating it restores order.
I ran that in SQLiteStudio and went back to the SQL console in foobar to rerun my SELECT there, and it seemed to hang. I aborted and tried again. Foobar crashed.
Ok, I never tried it, but changing foo_sqlite.db with an external tool, which is using a much newer version of SQLite, seems to be problematic and led to a database corruption.
Treeview is restored, but ust_TreeItem is still showing no rows, even from a different installation's copy of foo_uie_sql_tree.db
The reason is, that the information, which the root node of the tree is not stored in the db, but in the foobar 2000 config file, as there can be multiple panels with a tree all with the different root node. So, if you want to restore from a backup you, also need to restore the file foo_uie_sql_tree.cfg. The database alone is not enough.
So it looks like disaster is averted,
I hope you have a recent backup.
Oh look, a crash report.
Most likely caused by the corrupted database.
Ooh, one other thing I've been noticing: the Playlist_Updatable table seems to get corrupted if I'm moving things around from playlist to playlist, removing playlists, or something along those lines. My WHERE playlist_index = active_playlist() will exclude everything because that field will no longer contain playlist indexes. I've seen it contain different things at different times: nothing, incremented numbers, or the file paths of the tracks, if I'm remembering everything correctly. Dropping that table and recreating it restores order.
Is this something, that can be reproduced somehow or happens more than once? Or did it only occur after the crash?
Is this something, that can be reproduced somehow or happens more than once? Or did it only occur after the crash?
That was happening periodically before the crash (and before my bright idea to involve SQLiteStudio) throughout my work with this query and other, more successful update queries. I'd be pulling files from one playlist which was a work queue to another to work on (I hadn't seen that
item_is_selected technique), moving those to a third playlist of finished tracks, and often playing music in a fourth with albums passing in and out. Some of that work involved also updating and adding tags with masstagger, musicbrainz, and discogs, the latter often throwing up "stoi" errors of its own with inexactly-matched tracks. So, lots going on.
Ok, thank you. I will try to reproduce this. I never stumbled upon such a problem, but this might be related to my usage pattern as I have a fixed set of playlist, which I usually don't change. The playlist index is a dynamic value which might change, if the playlists are reordered or if a playlist is removed.
Back to SQL matters, this works but it makes me feel dirty. I'm parsing opus numbers and the like from the titles, and the current album includes two different numbering schemes. So I'm trying to construct three semicolon-separated fields for eventual splitting and correlating with one another:
work,
worknumber, and
worksortorder. The last is a concatenation of the numeric portions of the other two, each of those numerals left-padded with zeros to take the general shape like '0000-0000'. However, if it is all zeroes, it should be nulled out. For example, a track with "Op. 4 No. 1, RV 383" will have "Op. 4; RV 383", "No. 1;", and "0004-0001; 0383-0000" in those three fields.
Yes, it gets more complicated than this. Some RV numbers on this album end in "a" or "b", and the next album has Fanna numbers with Roman numerals and slashes. Heavens, this is madness, but one thing at a time.
Like I said, this works, but I had to resort to a hairy
eval() that makes me think there must be a better solution. Is there?
Base regex:
DROP TABLE IF EXISTS regexes;
CREATE TABLE regexes (
name TEXT UNIQUE PRIMARY KEY,
expression TEXT
);
INSERT INTO regexes
VALUES (
'work' -- opus and number
, '(?i)('
|| '?(DEFINE)'
|| '(?<titlecase>(?:\b(?:op)(?=[\s\d\.])\.?))'
|| '(?<uppercase>(?:\b(?:bb|bwv|kv?|rv)(?=[\s\d\.])\.?))'
|| '(?<mixedcase>(?:\b(?:WoO|BuxWV)(?=[\s\d\.])\.?))'
|| '(?<posth>\s*(?:\bposth?(?=[\s\d\.])\.?)?)'
|| '(?<sp>(?:\s*))'
|| '(?<num>(?:\bn(?:o|º)(?=[\s\d\.])\.?))'
|| ')'
|| '(?:'
|| '(?:'
|| '(?<titlecase>(?&titlecase))'
|| '|(?<uppercase>(?&uppercase))'
|| '|(?<mixedcase>(?&mixedcase))'
|| ')'
|| '(?<posth>(?&posth))'
|| '(?<workspace>(?&sp))'
|| '(?<worknum>(\d+)?)'
|| '(?:'
|| '\s*(?<numbertext>(?&num))'
|| '\s*(?<numbernum>(\d+))'
|| ')?'
|| ')'
)
SELECT queries referring to regex table
DROP VIEW IF EXISTS view_workfromtitle;
CREATE VIEW view_workfromtitle AS
WITH reg AS (
SELECT expression ex
FROM regexes
WHERE name = 'work'
)
SELECT regexp_replace(
reg.ex
, '\u$+{titlecase}\U$+{uppercase}\E$+{mixedcase}'
|| '$+{posth}$+{workspace}$+{worknum}; '
, title
, 1
)
AS work
, replace(regexp_replace(
reg.ex
, '$+{numbertext} $+{numbernum}; '
, title
, 1
)
, 'º', 'o'
)
AS worknumber
, regexp_replace(reg.ex, 'nullif\(padl\(''$+{worknum}'',4,''0''\)||''-''||padl\(''$+{numbernum}'',4,''0''\),''0000-0000''\)||''; ''||', title, 1)
AS worksortorder
, title
FROM Playlist_Updatable, reg
WHERE playlist_index = active_playlist()
AND item_is_selected;
SELECT substr(work, 1, length(work) - 2)
AS work
, substr(worknumber, 1, length(worknumber) - 2)
AS worknumber
, eval('SELECT '||substr(worksortorder, 1, length(worksortorder) - 8))
AS worksortorder
, title
FROM view_workfromtitle
Sample output
work worknumber worksortorder title
Op. 4; RV 383 No 1; 0004-0001; 0383-0000 Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, I. Allegro
Op. 4; RV 383 No 1; 0004-0001; 0383-0000 Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, II. Largo
Op. 4; RV 383 No 1; 0004-0001; 0383-0000 Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, III. Allegro
Op. 4; RV 279 No 2; 0004-0002; 0279-0000 Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, I. Allegro
Op. 4; RV 279 No 2; 0004-0002; 0279-0000 Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, II. Largo
Op. 4; RV 279 No 2; 0004-0002; 0279-0000 Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, III. Allegro
Op. 4; RV 301 No 3; 0004-0003; 0301-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, I. Allegro
Op. 4; RV 301 No 3; 0004-0003; 0301-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, II. Largo
Op. 4; RV 301 No 3; 0004-0003; 0301-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, III. Allegro assai
Op. 4; RV 357 No 4; 0004-0004; 0357-0000 Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, I. Allegro
Op. 4; RV 357 No 4; 0004-0004; 0357-0000 Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, II. Grave
Op. 4; RV 357 No 4; 0004-0004; 0357-0000 Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, III. Allegro
Op. 4; RV 347 No 5; 0004-0005; 0347-0000 Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, I. Allegro
Op. 4; RV 347 No 5; 0004-0005; 0347-0000 Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, II. Largo
Op. 4; RV 347 No 5; 0004-0005; 0347-0000 Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, III. Allegro
Op. 4; RV 316 No 6; 0004-0006; 0316-0000 Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, I. Allegro
Op. 4; RV 316 No 6; 0004-0006; 0316-0000 Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, II. Largo
Op. 4; RV 316 No 6; 0004-0006; 0316-0000 Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, III. Allegro
Op. 4; RV 185 No 7; 0004-0007; 0185-0000 Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, I. Largo - Allegro
Op. 4; RV 185 No 7; 0004-0007; 0185-0000 Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, II. Largo
Op. 4; RV 185 No 7; 0004-0007; 0185-0000 Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, III. Allegro
Op. 4; RV 249 No 8; 0004-0008; 0249-0000 Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, I. Allegro - Adagio - Presto - Adagio
Op. 4; RV 249 No 8; 0004-0008; 0249-0000 Concierto en Re menor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, II. Adagio
Op. 4; RV 249 No 8; 0004-0008; 0249-0000 Concierto en Re menor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, III. Allegro
Op. 4; RV 284 No 9; 0004-0009; 0284-0000 Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, I. Allegro
Op. 4; RV 284 No 9; 0004-0009; 0284-0000 Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, II. Largo
Op. 4; RV 284 No 9; 0004-0009; 0284-0000 Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, III. Allegro
Op. 4; RV 196 No 10; 0004-0010; 0196-0000 Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, I. Spiritoso
Op. 4; RV 196 No 10; 0004-0010; 0196-0000 Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, II. Adagio
Op. 4; RV 196 No 10; 0004-0010; 0196-0000 Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, III. Allegro
Op. 4; RV 204 No 11; 0004-0011; 0204-0000 Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, I. Allegro
Op. 4; RV 204 No 11; 0004-0011; 0204-0000 Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, II. Largo
Op. 4; RV 204 No 11; 0004-0011; 0204-0000 Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, III. Allegro assai
Op. 4; RV 298 No 12; 0004-0012; 0298-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, I. Spiritoso e non presto
Op. 4; RV 298 No 12; 0004-0012; 0298-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, II. Largo
Op. 4; RV 298 No 12; 0004-0012; 0298-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, III. Allegro
Like I said, this works, but I had to resort to a hairy eval() that makes me think there must be a better solution. Is there?
I don't think that it is worth the effort to find one or the other small structural optimization, if ever. Just imagine how the regular expression would look like, if you couldn't use named subexpressions. It would become completely unmaintainable. You should also not forget, that you are parsing text, which is following more or less loose rules. The more alternatives are possible the more complex will become the RE.
I'm having trouble with a corner case in the regexp_replace function. Sometimes, as in this album (https://musicbrainz.org/release/68d41048-6bda-40fb-bde2-81428ebb948a), it is the style to write the abbreviation of "Number" as "Nº", with an ordinal symbol (https://unicode-table.com/en/00BA/). I can write the replace string as something like '$+{number}', and it returns that string just fine, provided that the regular expression finds it. I can also make the first character lowercase with '\l$+{number}'. However, I cannot make the entire result lowercase, such as with '\L$+{number}\E'. In that instance, the rest of the string is truncated after that first "n". I'd like to be able to change the case to ease lookups in another table based on that value, which could be "no", "No", "NO", or whatever else comes up.
I see that there is a lower function provided by the unicode module, but I haven't found the right way to use that on the result. Is there an example of that somewhere?
I suppose I can just replace all the symbols with o's in the properties window, but I'd prefer to be able to take the titles as they are. Are there any other ways to do this?
I see that there is a lower function provided by the unicode module, but I haven't found the right way to use that on the result. Is there an example of that somewhere?
No, but it's just lower(<string>), where <string> can be any string also the return value of regexp_replace(). Do you have a simplified example for which lower() is not working as expected?
Simplified. Heh. Oh dear. :)
Since this is a going into what will be a multivalue tag, the way I finally got these things working in general was to do my manipulations inside each
regexp_replace call by wrapping the text of a subquery around the replace parameter. This takes place in a preliminary view, and then the
UPDATE query (I'm using just a
SELECT below) runs
eval(SELECT … on the whole field of semicolon-separated subqueries (the semicolons being foobar2000 multivalue delimiters, not SQL statement terminators) and strips the final semicolon and space from the end.
And trying to put a simple example together, I seem to have gotten that mess working, or at least not failing in this way. So I guess I'll look for my problem elsewhere. :)
But to finish the thought, here's what I'm doing. The lookup table is used to standardize the output from the variously-written title tags, and I wanted to make the lookup field lowercase for easier comparisons.
DROP TABLE IF EXISTS works;
CREATE TABLE works (
id INTEGER PRIMARY KEY
, lookup TEXT UNIQUE
, work TEXT
, sort TEXT
);
CREATE UNIQUE INDEX idx_workslookup
ON works(lookup);
INSERT INTO works(lookup, work, sort)
VALUES ('op' , 'Op.' , 'OPUS')
, ('no' , 'No.' , NULL )
, ('n°' , 'No.' , NULL )
, ('nº' , 'No.' , NULL )
, ('post' , 'posth.', NULL )
, ('posth', 'posth.', NULL )
, ('woo' , 'WoO' , 'WOOP')
, ('bb' , 'BB' , 'BBAR')
, ('sz' , 'Sz.' , 'SZOL')
, ('bwv' , 'BWV' , 'BAWV')
, ('buxwv', 'BuxWV' , 'BUWV')
, ('k' , 'KV' , 'KOCH')
, ('kv' , 'KV' , 'KOCH')
, ('rv' , 'RV' , 'RYOM')
, ('f' , 'F.' , 'FANN')
;
DROP VIEW IF EXISTS view_workfromtitle;
CREATE VIEW view_workfromtitle AS
SELECT regexp_replace('(N(?:o|º))'
, '\(SELECT work FROM works WHERE lookup = lower\(''\1''\)\); '
, title
, 1
)
AS simple
FROM Playlist_Updatable
WHERE playlist_index = active_playlist()
AND item_is_selected;
SELECT eval('SELECT '||substr(simple, 1, length(simple) - 2))
AS simple
FROM view_workfromtitle
Ok, with a simplified example I meant more something like this: ;D
SELECT regexp_replace('(N(?:o|º))'
, '\L\1\E'
, 'Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, I. Allegro');The result is indeed this:
Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 n
And the explanation is like I thought quite simple: regexp_replace() is implemented without unicode support. I already added this for the next version a while ago and with this version the result is as expected.
Had runtime errors and foobar2000 crashes that seemed to hinge on trying to update multivalue fields in Playlist_Updatable. Yes, I know that multivalue fields are not allowed in Playlist_Updatable, but I wanted to see if they could exist in Playlist or MediaLibrary and were simply treated as single value in Playlist_Updatable.
These fields had previously been single value, but I changed them to multivalue in the Playlist table, and used the first of them as an associated column of the other two. I also added the fields to the list in Preferences>Advanced>Display>Properties dialog>Multivalue fields. After making this change, my update query ran with no effect.
I then changed the fields back to single value. This is when all the runtime errors began. I made sure to remove the associated columns. More errors. I removed the tags from the advanced preferences. More errors. I rebuilt the Playlist and Playlist_Updatable tables. More errors.
I was only able to stop the errors and run the update query after removing the offending tags from the Playlist table and adding them again.
Is this the expected behavior? I would at some point like to make these tags multivalue and have them exist as such in MediaLibrary while being able to update those tags in new albums added to the collection. I know that Playlist_Updatable won't make them multivalue, but I was hoping that I could just split the tags in the properties window after I'm done updating them with SQL.
Yes, I know that multivalue fields are not allowed in Playlist_Updatable
This is actually not correct. You can't use the "Split multivalue tag" functionality for an updatable virtual table as it simply does not make sense here.
Consider the following example for one track:
album: "The Best"
artist: "The Greatest"
genre: "Rock", "Pop" (multivalue field with two values)
Without setting "split multivalue tags" for genre, the result of SELECT * FROM MediaLibrary (or Playlist, of course) will return this result for this track:
album artist genre
-------------------------------------------------
The Best The Greatest Rock · Pop
" · " is the standard multivalue separator used by foo_sqlite (in opposite to just ";" for the properties dialog), but you can define it for each tag by yourself.
With setting "split multivalue tags" for genre you will get this result:
album artist genre
-------------------------------------------------
The Best The Greatest Rock
The Best The Greatest Pop
So you will get one record for each value of a multivalue field.
But updating a virtual table allows you set multiple values for a single tag. This SQL
UPDATE Playlist_Updatable
SET genre='Rock · Pop'
WHERE album='The Best'
AND artist='The Greatest';
will update the genre tag with two values. You just need to use the multivalue separator, which is defined for the relevant tag.
Is this the expected behavior?
Of course, not. Crashing is never an exprected behavior. Thanks for the detailed error description. I will try to reproduce the problem and I think I have to disallow somehow what you did. Besides this, could you please also post one of the crash reports (txt and dmp file) as it might give some more information?
I don't seem to have any crash reports; the folder is empty.
But updating a virtual table allows you set multiple values for a single tag. This SQL
UPDATE Playlist_Updatable
SET genre='Rock · Pop'
WHERE album='The Best'
AND artist='The Greatest';
will update the genre tag with two values. You just need to use the multivalue separator, which is defined for the relevant tag.
I am having trouble getting this to work as you describe. If I create a tag in Playlist called
test, select
Split multivalue tag and rebuild Playlist_Updatable, my update query won't do anything. If I do not select
Split multivalue tag, then it will write the unsplit value to to the tag. If I create
test with that checkbox selected first, and then edit it to unselect it (and rebuild Playlist_Updatable), running the update query will crash foobar without generating a crash report. I've added the crash dialog in case there's anything to be gleaned from that.
UPDATE Playlist_Updatable
SET (
test
) = (
'#1 · #2'
)
WHERE Playlist_Updatable.playlist_index = active_playlist()
AND Playlist_Updatable.item_is_selected
SELECT title
, test
FROM Playlist
WHERE playlist_index = active_playlist()
AND item_is_selected
I am having trouble getting this to work as you describe. If I create a tag in Playlist called test, select Split multivalue tag and rebuild Playlist_Updatable, my update query won't do anything. If I do not select Split multivalue tag, then it will write the unsplit value to to the tag. If I create test with that checkbox selected first, and then edit it to unselect it (and rebuild Playlist_Updatable), running the update query will crash foobar without generating a crash report.
First of all, please don't touch "Split multivalue tags", when you are using an updatable virtual table. Just leave it empty. Please try writing multi value tags as described by me on a clean installation without any experiments of selecting or unselecting the "split multivalue tags" checkbox. If this is still not working, please post screenshots with all relevant settings or maybe better post the files foo_sqlite.db and foo_sqlite.dll.cfg from your configuration folder.
OK, fresh portable install of foobar2000 1.6.6 with SQLite viewer added. I dragged in a track and ran this script. I did not touch
Split multivalue tag. :D
DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable
USING MetaDB_Module(no_multivalue_split,add_defaults,playlist);
UPDATE Playlist_Updatable
SET (
genre
) = (
'Rock · Pop'
)
WHERE Playlist_Updatable.playlist_index = active_playlist()
AND Playlist_Updatable.item_is_selected
The genre tag is filled in the properties view with "Rock; Pop", so that looks like it should, I'm guessing.
However, back in the SQLite console, this query returns just one record with "Rock · Pop".
SELECT title
, genre
FROM Playlist a
WHERE playlist_index = active_playlist()
AND item_is_selected
I need this to be two records with the genre split. Having done whatever I need to do to accomplish that, I need to then be able to update subsequent files with multiple values in genre.
I have done nothing else since, so the installation is still unsullied for any further instructions. :D
I need this to be two records with the genre split. Having done whatever I need to do to accomplish that, I need to then be able to update subsequent files with multiple values in genre.
I have done nothing else since, so the installation is still unsullied for any further instructions. :D
Ok, then you need to add a second genre column (e.g. genremv) to the playlist table which now has "split multivalue tags" checked like this:
Type: Tag
Name: genremv
Tag
Name: genre
Split multivalue tag: checked
All other column settings remain unchanged.
And your query would be:
SELECT title
, genremv
FROM Playlist a
WHERE playlist_index = active_playlist()
AND item_is_selected
So you will have two genre columns: "genre" for updating and "genremv" for splitting multi value tags. There is no need to rebuild the virtual playlist. It's done automatically.
Well all right, that works. :D Thank you for the help. I was wondering what that second "Name" box was used for, and now I know that this is one application for it. I would not have guessed this solution though. There is still a lot I have to learn about SQL in general.
Am I right in thinking that I can update tags from within a playlist using foo_sqlite? I'd like to be able to create an album_duration tag that will contain the duration of the album the tracks belong to.
I've created a new portable install, added a folder of songs to the media library, created a playlist with some tracks in it and selected a few of them, and then I've attempted to run this code from the above post and all I see is 'Executing SQL' and then foobar crashes.
I know SQL but I'm struggling to understand how to use and reference the playlist modules.
Any help would be much appreciated.
DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable
USING MetaDB_Module(no_multivalue_split,add_defaults,playlist);
UPDATE Playlist_Updatable
SET (
genre
) = (
'Rock · Pop'
)
WHERE Playlist_Updatable.playlist_index = active_playlist()
AND Playlist_Updatable.item_is_selected
I had created a new, clean, portable install, but then - on autopilot I guess - added to that environment my other components, which are obviously to blame for causing it to crash!
Thanks for the feedback. I can confirm, that the SQL, which you are using, normally should not crash the application. I can only guess, if foo_sqlite is not causing the crash, that the tag update is causing another component to crash.
But if you don't mind, it would be good to verify, that the crash is caused by another component. In case of a crash you should get a crash report, which in most cases can identify the crashing component. If you didn't delete the relevant portable installation, you should have the crash report in the profile folder of this installation. Otherwise it should be probably not difficult to recreate the crash scenario.
So, if you have the crash report available, could you please post it here? TIA.
But if you don't mind, it would be good to verify, that the crash is caused by another component. In case of a crash you should get a crash report, which in most cases can identify the crashing component. If you didn't delete the relevant portable installation, you should have the crash report in the profile folder of this installation. Otherwise it should be probably not difficult to recreate the crash scenario.
Hi fbuser,
I had a quick look through the .txt file at the time and nothing jumped out at me, and I've just removed what I thought could be causing the problem (foo_tagbox) but it's not that either, so would appreciate you having a look for me. I've used foo_sqlite for lots of other purposes just not writing, so I guess it's that causing the problem.
The dump files are attached, and thanks for the invaluable plugin!
In the end it turned out to be the foo_sqlite.db that was causing the problem, no doubt it's something I did ages ago whilst playing with it initially.
I thought it would be something to do with the definition of the Playlist or MediaLibrary tables, but I can't seem to set them to defaults.
With a clean install, I added album_duration to the Playlist definition, ran a normal select query and closed foobar down. I went into .\profile\configuration and removed foo_sqlite.db and foo_sqlite.dll.cfg, and restarted foobar.
I then added the component back into foobar, restarted, and went back to the Playlist definition and album_duration is still there. I then went through and removed each column in the Playlist and MediaLibrary definition, clicked 'Add defaults' and it adds album_duration back again.
I assume it's stored elsewhere and maybe they're not to blame?
I've attached the foo_sqlite.db just in case it's useful.
Thank you for your efforts. I already saw from the crash log, that the problem is coming from foo_sqlite. I will look into it. If it is not already fixed for the next, partially internally rewritten, version, I will do it, if I can reproduce the issue.
I then went through and removed each column in the Playlist and MediaLibrary definition, clicked 'Add defaults' and it adds album_duration back again.
I assume it's stored elsewhere and maybe they're not to blame?
Yes, in your files. Besides some basic columns "Add defaults" adds also all tags it can find in your files as columns.
I then went through and removed each column in the Playlist and MediaLibrary definition, clicked 'Add defaults' and it adds album_duration back again.
I assume it's stored elsewhere and maybe they're not to blame?
Yes, in your files. Besides some basic columns "Add defaults" adds also all tags it can find in your files as columns.
I hadn't considered that. It's not really a default then, but I supposed it's what 99% of people want so that's fine at least it explains it.
I have one further question if you will. In the MetaDb_Module (http://www.palm-internet.eu/foobar2000/components/foo_sqlite/MetaDb_Module.html) documentation it states "The UPDATE statement on such a table will only prepare the update of the tags. The actual update will be done with committing the changes. This means reverting changes with a rollback is not possible."
Are you referring to within a single execution here i.e. Should I add a COMMIT to the code, or is the successful run of the code sufficient for the tag changes to be committed (I've read that SQLite autocommits by default unless you declare a BEGIN statement).
Thank you for your efforts. I already saw from the crash log, that the problem is coming from foo_sqlite. I will look into it. If it is not already fixed for the next, partially internally rewritten, version, I will do it, if I can reproduce the issue.
I'm glad to see the plugin is still alive and kicking!
Should I add a COMMIT to the code
No, it's not necessary. Furthermore adding a COMMIT without a BEGIN would throw an error.
is the successful run of the code sufficient for the tag changes to be committed (I've read that SQLite autocommits by default unless you declare a BEGIN statement).
Autocommit mode is exactly the reason, why you don't need to supply a COMMIT by yourself.
Hi fbuser,
Last question I promise :-)
Is there any way of changing the session collation type, or a quick way of changing the collation of all the variables in the MediaLibrary and Playlist table to BINARY?
I use this component for various QA checks so it'd be nice to have the default be "correct" (it's my first time using a database with such a default).
Thanks again.
Is there any way of changing the session collation type
No, because there is no session collation type. The collation is always bound to the table columns and it defaults indeed to BINARY. So, if you create tables of your own, the collation of all columns is BINARY.
quick way of changing the collation of all the variables in the MediaLibrary and Playlist table to BINARY?
Officially: No. Inofficially: The collation types for the MediaLibrary and Playlist table columns are stored in the database. So, if you are familiar with SQL and databases, it is probably quite easy to find out, what to do. But, of course, don't do this without a backup.
But you can also always override the collation of a column explicitely in the SQL statement, e.g
SELECT * FROM MediaLibrary WHERE album COLLATE BINARY BETWEEN 'a' AND 'z';
to list all albums which start with a lowercase ascii letter
or
SELECT * FROM MediaLibrary ORDER BY album COLLATE BINARY;
to sort the result using the BINARY collation.
it's my first time using a database with such a default.
As already said above, the default for the database is BINARY and it is not possible to change it.
Is there any way of changing the session collation type
No, because there is no session collation type. The collation is always bound to the table columns and it defaults indeed to BINARY. So, if you create tables of your own, the collation of all columns is BINARY.
I do understand that, but the vast majority of columns I'll be using have been set as NOCASE in the MediaLibrary and Playlist table definitions.
Having had a play around with it now for a couple of hours it's fine, it's just something you NEED to be aware of when using such a database, which I find a strange default. I use MariaDB for some personal accounts which on Windows is similarly case insensitive, but I just changed the config and didn't have to worry about it again :-)
I don't know how I'd feel if I had to use a case insensitive database created by someone else (unless there was a single standard collation across all tables/columns), I'd probably end up explicitly specifying a collation everywhere :-)
Thanks again
In the end it turned out to be the foo_sqlite.db that was causing the problem
I checked this, but unfortunately I could not identify the reason for the problem. But I released a new version with some substantial internal changes. These could have fixed the problem.
A new version is out. Please, check the first post for details.
Typo: [16:46:31] [foo_sqlite] 16:46:31.124: Open datatabase
Typo: [16:46:31] [foo_sqlite] 16:46:31.124: Open datatabase
Thanks. I saw this since about 9 months nearly every day, but never noticed it. At least I'm consistent. I'm also stuttering when internally attaching a database. :)
In the end it turned out to be the foo_sqlite.db that was causing the problem
I checked this, but unfortunately I could not identify the reason for the problem. But I released a new version with some substantial internal changes. These could have fixed the problem.
Not a problem, deleting foo_sqlite.db fixed it.
The new version, wow, what a lot of new functionality, is there going to be any documentation for it?
I've just run a bunch of my stored programs and with the metadb_handle to path change, they all seem to work, although I'm going to have to have a proper look in the morning as some of them are an order of manitude faster (45 secs compared to 1.9 secs), is this expected?
The only thing I've noticed so far is that when you 'Copy all' from the SQLite console it now includes a header row (which is fantastic), but it doesn't include a column for the row counter (#) and so all the headers for the data columns are shifted left.
Want to give feedback. Fantastic work. Thank you.
is there going to be any documentation for it?
You can call "Help -> SQLite viewer" for a short description of all functionality.
manitude faster (45 secs compared to 1.9 secs), is this expected?
Not really, But it's most likely related to optimizer changs of the SQLite engine.
The only thing I've noticed so far is that when you 'Copy all' from the SQLite console it now includes a header row (which is fantastic), but it doesn't include a column for the row counter (#) and so all the headers for the data columns are shifted left.
Thanks for the finding. Will be fixed for the next version.
is there going to be any documentation for it?
You can call "Help -> SQLite viewer" for a short description of all functionality.
I hadn't even seen that.
There looks to be SO much functionality (and now WINDOW functions too!) but I can't begin to imagine what I'd use it all for e.g. lookup tables.
I think it would really help people coming to this component if there were some real world examples of what it can be used for e.g. I helped some guy the other day to add album_duration to his collection, and after realising I could actually update tags with foo_sqlite I added DISCTOTAL to my collection, both of which probably aren't possible in any other tool.
Fantastic job!
Here's one real-world application I've been finding pretty handy. I have a "SQL replace" node that opens up the SQL console with a template for a
SELECT/UPDATE query that one can use to make regex replacements to tags. By default, the
UPDATE parts are commented out so that the
SELECT runs as a preview of the changes. When they look as they should, simply uncomment the
UPDATE and
SET lines and comment out the
SELECT and
FROM. This is from the older SQLite before the new
UPDATE…FROM syntax that's available in the component now.
/* Enter regex to find text to replace in FINDREGEX
regex to form replacement in REPLACEREGEX
field to write change to in WRITEFIELD
field to read text to change in READFIELD
*/
-- UPDATE PlaylistUpdatable -- un-comment to replace
-- SET WRITEFIELD =
SELECT -- preview, comment out to update
regexp_replace(
FINDREGEX
, REPLACEREGEX
, READFIELD
)
FROM PlaylistUpdatable -- preview, comment out to update
WHERE PlaylistUpdatable.playlist_index = active_playlist()
AND PlaylistUpdatable.item_is_selected
For instance, if one wanted to zero-pad single-digit numbers in the album titles into an album sort tag, fill in the following:
-- UPDATE PlaylistUpdatable -- un-comment to replace
-- SET albumsortorder =
SELECT -- preview, comment out to update
regexp_replace(
'\b(\d)\b'
, '0$1'
, album
)
FROM PlaylistUpdatable -- preview, comment out to update
WHERE PlaylistUpdatable.playlist_index = active_playlist()
AND PlaylistUpdatable.item_is_selected
The new functionality of the
Send to SQL Console vs. the renamed
Execute in SQL console doesn't raise the error I used to get in the previous version when it tried to execute the dummy text and couldn't find the field "READFIELD", which is a nice little plus.
More odd behavior: say I've tried to send errant code to the SQL console. After closing the error dialog and the SQL console, the computer's focus returns not to foobar2000 but to whatever other window had it before foobar2000.
Typo: [16:46:31] [foo_sqlite] 16:46:31.124: Open datatabase
It's fixed.
More odd behavior: say I've tried to send errant code to the SQL console. After closing the error dialog and the SQL console, the computer's focus returns not to foobar2000 but to whatever other window had it before foobar2000.
It's fixed.
2.1.0:
- fixed: add missing column header for the first column, if the whole result content in the SQLite console is copied
Whilst yes, 2.1.0 does add the missing header row for column one (#), now I'm just missing the rest :-)
Whilst yes, 2.1.0 does add the missing header row for column one (#), now I'm just missing the rest :-)
Oops. It was defintely there, when I added the #. Looks like I changed something else unintendently. Will be fixed soon.
Whilst yes, 2.1.0 does add the missing header row for column one (#), now I'm just missing the rest :-)
Finally , you should have all headers. I fixed them indeed away while solving another header related issue.
Finally , you should have all headers. I fixed them indeed away while solving another header related issue.
I report an issue in the afternoon and by the time I've woken up it's fixed; I like this style of delivery :-)
Thanks again!
Most recent crash. Again, it was triggered by running an UPDATE query in the SQL console that had been working fine previously. I'm updating PlaylistUpdatable in one playlist while the player is playing music from another. It seems like I can go a while running various UPDATE queries and then something just doesn't work. The query that triggers it can be complex or simple, but something just doesn't go right with the UPDATE. I have the newest versions of everything installed.
Incidentally, the issues I was having with the treeview refreshing on load seem to have cleared up for now. I have 10 nodes loading fine.
Most recent crash.
It appears to be a similar issue as the one, that
@grimes posted in the foo_uie_sql_tree thread. Unfortunately I'm not able to reproduce the issue, but I have a suspicion, what could cause the problem.
As it seems, that you are abel to reproduce the problem, I have attached two modified versions of foo_sqlite.dll, which are both candidates to fix the problem. I'd appreciate it, if you could make a test with both versions and let me know, if and which ones are solving the problem.
As it seems, that you are abel to reproduce the problem, I have attached two modified versions of foo_sqlite.dll, which are both candidates to fix the problem. I'd appreciate it, if you could make a test with both versions and let me know, if and which ones are solving the problem.
OK, I've swapped the first one in, and I'll see if I have the same issue. I can't really reproduce the crash at will, but I still have many files to update. I guess I'll try the second if I get a crash and let you know either way.
OK, I've swapped the first one in, and I'll see if I have the same issue.
That's not good. This could mean that my suspicion for the crash is wrong and it would be no big surprise, that also the second try to solve the problem will crash sooner or later.
However, if you still have the crash report for the first one, it could contain some more details, as it must be different, than the reports before.
I'm crashing, but I think it's something else related to the multivalue in a virtual table issue. I currently have the Candidate 1 dll in.
Due to some tag trimming somewhere, one of my custom multivalue tags,
albumartistsort, was flattened with commas replacing semicolons. This field is set as a multivalue field in the properties window but
not so set in the MediaLibrary or Playlist tables. If I run this query as given, foobar2000 crashes. The
SELECT constant bit looks up the value ' · '.
UPDATE PlaylistUpdatable
SET artistsortorder =
regexp_replace(
', '
, (SELECT constant
FROM constants
WHERE name = 'multivaluedelim')
, artistsortorder
)
WHERE PlaylistUpdatable.playlist_index = active_playlist()
AND PlaylistUpdatable.item_is_selected
However, if—instead of replacing ', ' (comma space)—I simply replace ',' (comma), the query runs fine, but there's an extraneous space after each delimiter. If I then run a similar query cleaning it up by replacing two spaces with one, I get the result I ultimately need.
Well shoot, the zip said it was 100% loaded, but the icon didn't appear. It's a big one. I'll try again.
It works if I split foo_sqlite.user.db from the rest. I could probably use some hints on removing extraneous stuff from that db. :D
OK, I offloaded some backup and working tables to a different db, so I should go into the next crash a bit leaner. :-[
New crash I can't seem to shake. This is in my main foobar as I haven't gotten clean ones going to my satisfaction yet. I wonder if something got corrupted, since I've been doing stuff like this without a problem before. Currently, the regular SQLite Viewer dll is in, but similar trouble is happening with the others.
Foobar opens with one playlist. I open the SQLite console and run SELECT * FROM Playlist. Works fine. I fill a second playlist with tracks and run the query again. Crash.
I just reproduced the crash in a clean installation with the stock foo_sqlite.dll. Open foobar, add one playlist more than were there on opening, and query SELECT * FROM Playlist.
I just reproduced the crash in a clean installation with the stock foo_sqlite.dll. Open foobar, add one playlist more than were there on opening, and query SELECT * FROM Playlist.
I tried the same, but it worked for me without any problems. Also updating PlaylistUpdatable to split values with commas into multiple value works for me without any crash. And I tried several cases with and without your database. Even updating almost 40000 tracks which were all prepared to have a multivalue field containing commas was no problem.
So, there must be an additional parameter in your setup, that causes the crashes.
Miscellaneous observation: I got my latest foobar incarnation all tidied up, and I noticed that the zip of it all is quite a bit bigger than the previous one. I did a pretty good job of getting all my cruft out of there, so I looked around for what else was present. It seems that, between SQLite Viewer and Random Pools, I am now the proud owner of two copies of icutd70.dll, and it's a big 'un. I guess that explains it. :D
It's not a big deal, but could I perhaps get away with one?
It's not a big deal, but could I perhaps get away with one?
In theory, yes. The DLLs just needs to be placed somewhere in the search path. However I highly recommend not to do anything like that, just to save some MB. The DLLs might be different in the future for both components, just because one component was already upgraded with a newer version, while the other wasn't.
I've just run a bunch of my stored programs and with the metadb_handle to path change, they all seem to work…
Help! I'm not finding anything anywhere on what to change metadb_handle to.
Here's a sample query:
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT metadb_handle, "Top 25" FROM MediaLibrary WHERE play_count > 0 ORDER BY play_count DESC LIMIT 25;
What does "the actual file path and subsong index" even mean in this context?
I'm trying to populate the playlist with the top 25 most played songs. So, actual file path to all my music? Subsong index makes no sense here because I want to check all songs…
(It's pretty obvious I don't know why I had to use metadb_handle in the first place, but it just worked, so I never questioned it.)
Help! I'm not finding anything anywhere on what to change metadb_handle to.
Just change it to "path, subsong". So your sample query will become
INSERT INTO Playlist_Updatable(path, subsong, playlist_name)
SELECT path, subsong, "Top 25" FROM MediaLibrary WHERE play_count > 0 ORDER BY play_count DESC LIMIT 25;
What does "the actual file path and subsong index" even mean in this context?
They are just two track attributes, which are necessary to uniquely identify a track.
So, actual file path to all my music? Subsong index makes no sense here because I want to check all songs…
You are misunderstanding the terms file path and subsong here. File path refers to the individual full path of the file belonging to a track and subsong is only relevant, if you have multi-track files, e.g. cue sheets. Then you need the subsong index to refer to a track in this multi-track file.
If you have only single track files, you could omit the subsong, but it is no mistake to always specify it.
(It's pretty obvious I don't know why I had to use metadb_handle in the first place, but it just worked, so I never questioned it.)
It served the same purpose as "path, subsong", but in a more obscure way.
Thank you for explaining all that.
Now my error has moved on to 'no such column: Top 25'
Again, no idea why that would happen. That's how I created the playlist Top 25 in the first place. I thought it creates it if it's not there, and updates it if it is there.
Now my error has moved on to 'no such column: Top 25'
Yes, of course. I didn't notice it, but you need to put 'Top 25' into single quotes, not double quotes.
Again, thank you very much! I've got all my queries working again.
error again.
error again.
Well, the reports are still for foo_sqlite version 2.1.1, while the latest version is 2.1.4. Nevertheless, the new report shows an additional hint, which could help to fix the issue, as I still don't have any crashes although I use it quite frequently. The dump file shows, of course, where the crash happens, but it's not obvious why it happens there. In addition to the files it would also be helpful which SQL statement exactly you executed.
Besides this, could you please also post the relevant files for version 2.1.4 as I highly doubt, that the issue is fixed with this version?
Updated to 2.1.4.
SQL statement:
SELECT count(DISTINCT album) album_count
FROM medialibrary
Updated to 2.1.4.
SQL statement:
SELECT count(DISTINCT album) album_count
FROM medialibrary
Hmm, according to crash log #127 from today, it must have been a different SQL statement, as "Row limit (5000 rows) for the SQLite console exceeded." was logged to the console right before the crash, while the statement above always returns only one row. So, it would be helpful to get also the crash reports for version 2.1.4.