Skip to main content

Notice

Please note that most of the software linked on this forum is likely to be safe to use. If you are unsure, feel free to ask in the relevant topics, or send a private message to an administrator or moderator. To help curb the problems of false positives, or in the event that you do find actual malware, you can contribute through the article linked here.
Topic: [fb2k v2] SQL Tree (foo_uie_sql_tree) (Read 121449 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: foo_uie_sql_tree

Reply #276
After some time, the data are transferred mostly from foo_sqlite.db to foo_sqlite.user.db.
Yes, this is part of the migration to version 2.0.0

I have compressed the user database. Is this correct?
It's neither correct nor wrong. It does not have an impact on the functionality. It just saves you some MB disk space (not really much in our days). It will become more interesting when your database grows due to heavy activities with inserting and deleting data to several GB.

Re: foo_uie_sql_tree

Reply #277
I'm having a persistent crash now when trying to run a particular UPDATE query on PlaylistUpdatable. This query was working previously today, and then just stopped. It's a long script, but a second query that runs most of it but then SELECTs the results to the SQLite console rather than updating works fine.

Since this has been happening, my restarts are now not working correctly. I have about 10 queries refreshing on load, and now foobar2000 seems to get hung up after just two of them are loaded. the rest show your progress bars for a while without progress. I wind up just aborting them.

I tried to do the VACUUM thing you showed up above into a new db. Even that took way too long before I just wound up aborting it too. I did try just a plain VACUUM directly in the main db, not having read that perhaps some tables may have gone missing doing that.

Anyway, here are several crash reports from the same issue and some of the other files.


Re: foo_uie_sql_tree

Reply #279
I tried to do the VACUUM thing you showed up above into a new db. Even that took way too long before I just wound up aborting it too.
This is something which never will help in such cases. I asked @Grimes only to do this to exclude the quite low possibility, that his database is corrupted.

I did try just a plain VACUUM directly in the main db, not having read that perhaps some tables may have gone missing doing that.
No, tables are not gone missing when doing VACUUM. It just looked like that it was the case for @Grimes databases. But it definitely wasn't the case.

Anyway, here are several crash reports from the same issue and some of the other files.
I will check them

Album column size is too wide in sqlite console window. How can I adjust.
We should better continue to discuss foo_sqlite related issues in the relevant thread, but the short answer is: You can't besides doing it manually, if you have such long album names in the query, which causes the column to be adjusted accordingly.

Re: foo_uie_sql_tree

Reply #280
A bit more: since the crashes, sqlite was usually hanging trying to refresh those 10 nodes on loading, but sometimes not. In those former cases, sqlite wouldn't run very well in the console either. I have since unticked "Refresh on load" for all of them, and two startups since have been fine. Those nodes will refresh manually in the usual time, and the console runs in a timely manner as well.

Also, the UPDATE query that triggered everything will execute properly again. It hadn't before for several different restarts.

Re: foo_uie_sql_tree

Reply #281
Anyway, here are several crash reports from the same issue and some of the other files.
I will check them
The reason for the problem could be the same as for @grimes' crashes. So, you should first upgrade foo_sqlite and foo_uie_sql_tree to their latest version. If the problem still occurs, please post the new crash reports. In this case it could also be helpful to include all database files for foo_uie_sql_tree.db. The main file is missing in your archive.


Re: foo_uie_sql_tree

Reply #283
Another issue: node Examples | Library | Genre

foobar2000 Console output:
Quote
[22:23:46] [foo_uie_sql_tree] 22:23:46.435: Get tracks for query node "Genre": 0:00.000537
[22:23:46] [foo_uie_sql_tree] 22:23:46.435
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQLite Error: (1) no such column: metadb_handle

Error while preparing the first statement of:
SELECT path,
       subsong
FROM genre_query

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
[22:24:25] [foo_sqlite] 22:24:25.394: Close database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db ...
[22:24:25] [foo_sqlite] 22:24:25.397: Close database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db: 0:00.002724

Re: foo_uie_sql_tree

Reply #284
Another issue: node Examples | Library | Genre
This is caused by the breaking change in foo_sqlite 2.0.0 and it is unfortunately not safe to do the migration automatically in this case. The error is caused by genre_query still having metadb_handle in its definition which is not valid anymore and SQLite always checks a view definition for its validity even on dropping it.

For newly created example nodes this problem does not exists, as the view has a different name than before for the genre query. So deleting the old example nodes and re-creating them, will solve the problem.

However, this will keep the wrongly defined view in the database.  To get rid of it, you can execute the following commands:
Code: [Select]
PRAGMA writable_schema=ON;
DROP VIEW genre_query;
PRAGMA writable_schema=OFF;
This will allow to drop the view without checking its validity.

Re: foo_uie_sql_tree

Reply #285
Another crash (Similar to Sue Dunham's crash).
This happens, when a query is executed shortly after foobar2000 start. A simple query is hanging (after 2min: aborting query then crash).
EDIT: I had a series of 4 crashes and couldn't reproduce the crash afterwards.

Re: foo_uie_sql_tree

Reply #286
I have a general question about migrating my SQL Tree from my main foobar2000 to a new clean one. I don't have any trouble migrating everything to a new foobar, but I can't get my nodes to appear when just migrating the SQLite components.

I am copying the following files and directories from one portable installation to another:

foobar2000/
└── profile/
    ├── configuration/
    │   ├── foo_sqlite.db
    │   ├── foo_sqlite.dll.cfg
    │   ├── foo_sqlite.materialize.db
    │   ├── foo_sqlite.tags.db
    │   ├── foo_sqlite.user.db
    │   ├── foo_uie_sql_tree.db
    │   └── foo_uie_sql_tree.dll.cfg
    └── user-components/
        ├── foo_sqlite/
        └── foo_uie_sql_tree/


The components install, but instead of all my nodes, the general example nodes appear instead. Looking under the hood in foo_uie_sql_tree.db, I see that new records have been added to ust_TreeItem holding these nodes. Presumably, the new #ROOT# record is tied to the current panel, a regular Default UI popup as opposed to the panel in my busy and involved Columns UI layout.

So my question: is there something else I should be copying over to tie the old nodes to the new panel? Barring that—and understanding that there is danger ahead—could I try editing ust_TreeItem to change the tri_parent_id of my top-level nodes to that of the new #ROOT#? Is there another edit to make instead/as well?

 

Re: foo_uie_sql_tree

Reply #287
Barring that—and understanding that there is danger ahead—could I try editing ust_TreeItem to change the tri_parent_id of my top-level nodes to that of the new #ROOT#?
Yes and besides that nothing more is necessary.

Re: foo_uie_sql_tree

Reply #288
Barring that—and understanding that there is danger ahead—could I try editing ust_TreeItem to change the tri_parent_id of my top-level nodes to that of the new #ROOT#?
Yes and besides that nothing more is necessary.

I'm having trouble with this: it keeps making more example nodes. I edit foo_uie_sql_tree.db in the SQLite Console, Deleting the example nodes and changing tri_parent_id to the tri_node_id of the vacated root, close foobar2000, reopen, and new example nodes appear, further on in ust_TreeItem. I've tried leaving the example nodes there, same result. I've tried editing the db with a different program, same result.

How can I prevent the creation of yet more example nodes?

Re: foo_uie_sql_tree

Reply #289
New bug: I open a node, and the newly opened node moves to the top of the Treeview display, which is fine. However, when I close the node and the display flips back to show the top (there are now few enough nodes showing that the entire list can be displayed in the panel), the topmost node now under the mouse pointer captures part of that node-closing click and acts as if it were clicked as well. This happens repeatedly, so I don't think I'm just fat fingering it.

Re: foo_uie_sql_tree

Reply #290
How can I prevent the creation of yet more example nodes?
The example nodes are only automatically created, if the root node id of the panel can't be found in the database. So, although what you are doing according your description should work, it seems that you are doing something wrong.

By the way, your sqltree from the crash archive in the foo_sqlite thread was loaded in a new clean foobar2000 installation for me because its root node id is 1.

New bug
Maybe an old one. Anyway, it's reproducible for me. Until it is fixed you might consider to open an close nodes by clicking on the expansion button on the left side of the label instead of double clicking on the label.

Re: foo_uie_sql_tree

Reply #291
This SQL query to find different album artists worked before but now it doesn't since an update along the line somewhere.

Any help would be appreciated. Thanks.

The error is:
Code: [Select]
SQLite Error: (1) no such column: %BOOTLEG%

The query:
Code: [Select]
SELECT album,"album artist"
FROM MediaLibrary
WHERE album IN
      (SELECT album
      FROM MediaLibrary
      WHERE album NOT LIKE "%BOOTLEG%"
          AND  album NOT LIKE "%Singles%"
          AND  album NOT LIKE "%[SC]"
      GROUP BY 1
      HAVING count(distinct coalesce("album artist",''))>1)
ORDER BY album

Re: foo_uie_sql_tree

Reply #292
This SQL query to find different album artists worked before but now it doesn't since an update along the line somewhere.

Any help would be appreciated. Thanks.
Strings in SQL are surrounded by single quotes not double quotes. With double quotes SQLite assumes a column name. Therefore the error message.

You should try this:
Code: [Select]
SELECT album,"album artist"
FROM MediaLibrary
WHERE album IN
      (SELECT album
      FROM MediaLibrary
      WHERE album NOT LIKE '%BOOTLEG%'
          AND  album NOT LIKE '%Singles%'
          AND  album NOT LIKE '%[SC]'
      GROUP BY 1
      HAVING count(distinct coalesce("album artist",''))>1)
ORDER BY album


Re: foo_uie_sql_tree

Reply #294
Hello fbuser, I updated to 3.0.3 and I am quite pleased with some performance improvements I've seen in some queries (5 minutes to 5 seconds!), but I'm having trouble with other queries.

Some stores sell uncompressed FLAC, or embed massive cover art, so I had a query to find files that are "too large" so that I could re-encode them:

Code: [Select]
SELECT m.path, c.MB_per_min, c.[bits per sample], c.samplerate, c.channels
FROM medialibrary m
JOIN
(SELECT path, [bits per sample], samplerate, channels,
 MAX(ROUND(CAST(filesize * 60 AS float)/(length_seconds * 1024 * 1024), 3)) AS MB_per_min, COUNT(*) AS cnt
FROM medialibrary

WHERE [bits per sample] > 16 OR [samplerate] > 44100

GROUP BY path, [bits per sample], samplerate, channels
) c
ON m.path = c.path
  AND m.[bits per sample] = c.[bits per sample]
  AND m.samplerate = c.samplerate
  AND m.channels = c.channels

WHERE
    (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels > 2 AND c.MB_per_min > 58.600)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels > 2 AND c.MB_per_min > 38.600)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels = 2 AND c.MB_per_min > 26.489)

 OR (c.[bits per sample] >= 32 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 22.100)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 14.900)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 44100 AND c.channels = 2 AND c.MB_per_min > 13.900)

ORDER BY m.path

However, this is no longer working with the latest components:
Quote
SQLite Error: (1) ambiguous column name: path
...

I tried to qualify the inner queries, but wasn't able to figure out how to fix this query.

Re: foo_uie_sql_tree

Reply #295
I am quite pleased with some performance improvements I've seen in some queries (5 minutes to 5 seconds!)
There were several enhancements for the SQLite query optimizer implemented in the last years, which probably results in better execution plans for your queries. Also 5 minutes appear to be quite long, even if you have a big media library.

I tried to qualify the inner queries, but wasn't able to figure out how to fix this query.
You need to an alias to the "path" column of the sub-select query as due to dropping the metadb_handle pseudo column now the columns "path" and "subsong" are automatically added without any qualifier to the query of a query node, when the result is sent to a playlist.

So your query could look like this:
Code: [Select]
SELECT m.path, c.MB_per_min, c.[bits per sample], c.samplerate, c.channels
FROM medialibrary m
JOIN
(SELECT path subselect_path, [bits per sample], samplerate, channels,
 MAX(ROUND(CAST(filesize * 60 AS float)/(length_seconds * 1024 * 1024), 3)) AS MB_per_min, COUNT(*) AS cnt
FROM medialibrary

WHERE [bits per sample] > 16 OR [samplerate] > 44100

GROUP BY path, [bits per sample], samplerate, channels
) c
ON m.path = c.subselect_path
  AND m.[bits per sample] = c.[bits per sample]
  AND m.samplerate = c.samplerate
  AND m.channels = c.channels

WHERE
    (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels > 2 AND c.MB_per_min > 58.600)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels > 2 AND c.MB_per_min > 38.600)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels = 2 AND c.MB_per_min > 26.489)

 OR (c.[bits per sample] >= 32 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 22.100)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 14.900)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 44100 AND c.channels = 2 AND c.MB_per_min > 13.900)

ORDER BY m.path

However, as grouping by path could only be useful for multitrack files with different subsongs (and only in very rare cases), as for single track files the path is always already unique your query could also be written like this:
Code: [Select]
SELECT c.path, c.MB_per_min, c.[bits per sample], c.samplerate, c.channels
FROM (
  SELECT path, subsong, [bits per sample], samplerate, channels,
         ROUND(CAST(filesize * 60 AS float)/(length_seconds * 1024 * 1024), 3) AS MB_per_min
  FROM medialibrary
  WHERE [bits per sample] > 16 OR [samplerate] > 44100
) c
WHERE
    (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels > 2 AND c.MB_per_min > 58.600)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels > 2 AND c.MB_per_min > 38.600)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels = 2 AND c.MB_per_min > 26.489)

 OR (c.[bits per sample] >= 32 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 22.100)
 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 14.900)

 OR (c.[bits per sample] = 24 AND c.[samplerate] IS 44100 AND c.channels = 2 AND c.MB_per_min > 13.900)

ORDER BY c.path
As there is no need for the group-by, there is also no need for the join. Adding the subsong column to the sub-select is necessary as the medialibrary is now only referenced in the sub-select and as mentionend "path" and "subsong" will be added automatically.

Actually, also the colums of the main select could be replaced by just 1, if you use the query only for sending results to a playlist., as creating a tree structure with the currently used columns doesn't appear to be useful for me.

Re: foo_uie_sql_tree

Reply #296
Thank you for the magnificent work. Small issue: tree, rightclick node | Edit | Query. Window is too large to hit ok. Scale (Skalierung) under Windows 10: 125%.
This should be no problem anymore. The dialog is now resizable and the initial size is smaller.

New bug: I open a node, and the newly opened node moves to the top of the Treeview display, which is fine. However, when I close the node and the display flips back to show the top (there are now few enough nodes showing that the entire list can be displayed in the panel), the topmost node now under the mouse pointer captures part of that node-closing click and acts as if it were clicked as well.
It's fixed now.

Re: foo_uie_sql_tree

Reply #297
I have a bug report for SQL Tree 3.0.4 / SQLite View 2.1.1 - if I move a playlist, then my query no longer selects from the correct playlist.
Code: [Select]
DROP TABLE IF EXISTS MediaLibrary_NoSplit;
CREATE VIRTUAL TABLE MediaLibrary_NoSplit USING MetaDB_Module(no_multivalue_split);
;
SELECT P.discnumber, P.tracknumber, P.title, P.artist, ...
FROM playlist P
LEFT JOIN (
 SELECT artist...
 FROM MediaLibrary_NoSplit
 WHERE ...
 GROUP BY artist, ...
 COLLATE BINARY
 HAVING cnt > 0
) m
ON P.artist = m.artist
COLLATE BINARY
WHERE P.playlist_name = 'my playlist'
GROUP BY P.discnumber, P.tracknumber, P.title, P.artist

Re: foo_uie_sql_tree

Reply #298
if I move a playlist, then my query no longer selects from the correct playlist.
What do you mean with "move a playlist" and what should be the "correct playlist"? Which result do you expect and which result do you get?

Re: foo_uie_sql_tree

Reply #299
if I move a playlist, then my query no longer selects from the correct playlist.
What do you mean with "move a playlist" and what should be the "correct playlist"? Which result do you expect and which result do you get?

Let's say my playlists currently are like this:

10. my playlist
11. something else

Then I move "my playlist" down, so that "something else" takes its spot.  The query then selects the contents of "something else" instead of "my playlist".  This is still happening on the newest version(s) for me.