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 169566 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: foo_uie_sql_tree

Reply #250
First what you describe smells like it should be implemented with triggers instead of multiple scripts. Second there are several possibilities why the second script might not see the changes of the first script. Actually I'm pretty sure, that it is not a problem of foo_uie_sqltree (or foo_sqlite) in the first place. It's seems to be more related to the behavior of SQLite or the extensions (eval, readfile), which you are using in your scripts.

But I'd need to see your scripts to be able to be more specific on this matter.

Re: foo_uie_sql_tree

Reply #251
Ah yes, triggers. You're probably right that that would be the way to go. This approach with the regexes being built by querying tables is new, and I haven't thought it all the way through yet. Formerly the same data was typed into several different regex scripts, and I am trying to solve the maintenance issues with that.

Thanks for the tip. :)

Re: foo_uie_sql_tree

Reply #252
I've gotten into a bad situation.  There's this "Rebuild Media Library" batch command:
Code: [Select]
BEGIN TRANSACTION;
DROP TABLE IF EXISTS MediaLibrary;
CREATE VIRTUAL TABLE MediaLibrary USING MetaDB_Module(add_defaults);
COMMIT;
This has normally worked fine to add new fields to be used in queries.

Now,

1. After clicking this once, it seems to complete.  There's no feedback.  If I exit foobar2000 and re-open it, my SQL Tree is blank and cannot be right-clicked.

2. If I don't exit foobar2000 and click it a 2nd time,
Code: [Select]
Execution error:
SQLite Error: (1) Unable to declare virtual table MediaLibrary:
SQLite Error: (1) near "/": syntax error

Error while executing:
CREATE VIRTUAL TABLE MediaLibrary USING MetaDB_Module(add_defaults)

3. If I click it a third time,
Code: [Select]
Execution error:
SQLite Error: (1) Unable to declare virtual table MediaLibrary:
SQLite Error: (1) near "/": syntax error

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

What is going on?

Re: foo_uie_sql_tree

Reply #253
I've gotten into a bad situation.  There's this "Rebuild Media Library" batch command:

This has normally worked fine to add new fields to be used in queries.
This is actually not necessary as the virtual tables are rebuilt automatically, when you add/change/remove fields. But doing it normally doesn't hurt.

What is going on?
I can only guess, but the only explanation I have on my mind, is that you defined a new field containing a single quote (') in its name. For building the virtual table the column names are surrounded by single quotes and having a single quote in the name will break the virtual table creation.

Unfortunately there is currently no check for such a problem. This will be added for the next version and instead of single quotes the column names will be surrounded by backticks (`).

Re: foo_uie_sql_tree

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


Re: foo_uie_sql_tree

Reply #256
This crash might be related.
EDIT: It happened after a double-click on track.

Re: foo_uie_sql_tree

Reply #257
Oh, this update is glorious. I've been waiting for it, and it looks like a lot of fun. Thank you for your hard work  ;D

Naturally, I have come across a couple nits in the Edit Query window.
  • The window itself is pretty big and can't be resized. Nothing major, but I'd like to scrunch it up a bit.
  • Can one type a script name directly into the textbox at the top of the query and batch tabs? I seem to only be able to input a filename by clicking the "…" button and navigating.
  • I'm having character coding issues coming from my UTF-8 sql files that look like they're being read as cp1252. I have a "•" being used as a visual delimiter between an album and an artist in one node, and that is rendered "•" in the query textbox and the nodes, though the Result node options display is fine. I'd love an Advanced option to set the default script encoding.

Generally too, the "Documentation" link on the foobar2000 Component page goes to an empty wiki page instead of the old pages you had linked formerly. Are new pages on the way?

Thanks again. Being able to grab the scripts like that is really going to help me quite a lot.

Re: foo_uie_sql_tree

Reply #258
Now I'm encountering a weird bug I'm not sure how I can reproduce involving an interaction with the venerable Panel Stack Splitter. I have a button in a Spider Monkey Panel that toggles which panels are visible in the PSS by alternately creating or deleting a blank text file. The PSS then uses the presence/absence of this file to determine which panels to show via a per-second script. Yes, it's hackish, rickety, and only works when a track is playing, but it's all I've found to do that till now.  :P 

Anyway, if I use the SQL Tree edit query window to link and import a sql file for the query or batch panel, this button stops toggling the PSS. The SMP toggles its icon, the text file is created or removed, but the PSS doesn't do anything with it. Restarting foobar2000 restores order.

So, um, yeah, fix that please?  ;D

Re: foo_uie_sql_tree

Reply #259
I've crash reports by quitting foobar, older version with older sqlite dosn't. Without installing sql-tree there're no crash reports.
Thanks for the report. I can't reproduce the crash, but I will change the code in question to hopefully fix it for foo_uie_sql_tree as it could have an impact on the crash. Nevertheless the crash is actually caused by foo_spider_monkey_panel, so that @TheQwertiest might also want to look into it.

This crash might be related.
Although it didn't look at all like that at the first glance, it seems to be indeed related, although in this case foo_sqlite is causing the crash.
EDIT: It happened after a double-click on track.
This is a bit strange, but as the relevant code is shared by foo_sqlite and foo_uie_sql_tree, the change mentioned above might help here too.

The window itself is pretty big and can't be resized. Nothing major, but I'd like to scrunch it up a bit.
You're right. I should change this in a future version, but it doesn't have the highest priority.

Can one type a script name directly into the textbox at the top of the query and batch tabs? I seem to only be able to input a filename by clicking the "…" button and navigating.
This is correct. I was a bit lazy here and it saves me from adding additional input checking. I'm not sure, if I will change this. Maybe.

I'm having character coding issues coming from my UTF-8 sql files that look like they're being read as cp1252. I have a "•" being used as a visual delimiter between an album and an artist in one node, and that is rendered "•" in the query textbox and the nodes, though the Result node options display is fine.
Confirmed. I'll try to fix it without the need of this
I'd love an Advanced option to set the default script

Generally too, the "Documentation" link on the foobar2000 Component page goes to an empty wiki page instead of the old pages you had linked formerly. Are new pages on the way?
Probably not. There is no real benefit to view them in the internet, while you already have them locally available in the component folder and you are able to open them from the application. I used the old pages in the past mainly to have the possibility to directly link to them from a thread in this forum.

Re: foo_uie_sql_tree

Reply #260
Anyway, if I use the SQL Tree edit query window to link and import a sql file for the query or batch panel, this button stops toggling the PSS. The SMP toggles its icon, the text file is created or removed, but the PSS doesn't do anything with it. Restarting foobar2000 restores order.
When opening the dialog for selecting a file, I'm currently setting the initial path for the dialog to the components directory (but I'll change it to set it to the profile folder as the first idea for using the components directory actually doesn't work and no worry the scripts will still be loaded relative to the component directory). Actually, that should not have an impact on the behavior that you described. It's only a rough guess, but it looks like you are using a relative path to check your file and it can't be found anymore after you opened the dialog.

So, um, yeah, fix that please?  ;D
:D

Re: foo_uie_sql_tree

Reply #261
Another bug: linking a sql file for the query isn't itself sufficient to enable the OK button. Text must be pasted in manually to do so.

Re: foo_uie_sql_tree

Reply #262
Another issue: once a file has been linked in the query pane, I can't remove the link. "Delete" in the context menu is disabled, and I'm not sure how to enable it.

Re: foo_uie_sql_tree

Reply #263
Weird query issue. If I put this into the batch tab (pasting directly or linking):
Code: [Select]
DROP VIEW IF EXISTS view_albumartist;
CREATE VIEW         view_albumartist AS
SELECT CASE albumartist
       WHEN 'Various'
       THEN 'Various'
       ELSE upper(
                  substr(
                         albumartistsortcoal
                       , 1, 1
                         )
                  )
       END
    AS initial
     , albumartistcoal
     , albumartistsortcoal
     , albumcoal
     , albumsortcoal
     , datesortcoal
     , releasesortorder
     , discnumber
     , tracknumber
     , path
     , subsong
  FROM view_medialibrary
 WHERE CAST(ifnull(hideflags, 0) AS INTEGER) < 2
   AND filename_ext NOT LIKE '%.wav'
       ;


DROP VIEW IF EXISTS tree_library_albumartist;
CREATE VIEW         tree_library_albumartist AS
SELECT   (CASE WHEN initial >= ' ' AND initial <= 'B' THEN '# to B'
               WHEN initial >= 'C' AND initial <= 'D' THEN 'C to D'
               WHEN initial >= 'E' AND initial <= 'G' THEN 'E to G'
               WHEN initial >= 'H' AND initial <= 'L' THEN 'H to L'
               WHEN initial >= 'M' AND initial <= 'O' THEN 'M to O'
               WHEN initial >= 'P' AND initial <= 'S' THEN 'P to S'
                                                      ELSE 'T to Z, Various'
         END)
      AS initgroup
       , albumartistcoal
       , albumcoal
      ||' (' || substr(datesortcoal, 1, 4) || ')'
      AS albumdate
       , path
       , subsong
    FROM view_albumartist
ORDER BY albumartistsortcoal
       , datesortcoal
       , albumsortcoal
       , releasesortorder
       , discnumber
       , tracknumber

…and then this into the query tab:
Code: [Select]
SELECT initgroup
     , albumartistcoal
     , albumdate
  FROM tree_library_albumartist

…then things work as expected.

However, if I put just that first SELECT statement in the batch and the SELECT portion of the second one (minus the path and subsong fields) into the query, then the treeview will fill with all the right nodes, but nothing will be sent to the playlist on clicking.

Further puzzler: the working version will not work if the Omit not existing files when sending to a playlist box is checked, only working when it is not. I like to think that the files exist: they play like they do.  ;)

Re: foo_uie_sql_tree

Reply #264
Not sure if this is a bug or an expected change in behavior, but I formerly had temporary tables in the batch portions of nodes that the query portions can no longer see in time. Removing TEMPORARY from the sql restores the functionality.

Re: foo_uie_sql_tree

Reply #265
I've crash reports by quitting foobar
This crash might be related.
@ASopH, @grimes: I couldn't reproduce the reported crashes. However, I made a adjustment of the affected code. Could you please test with the new versions of foo_uie_sql_tree (ASopH) and foo_sqlite (grimes), if the crashes still happen? If yes, could you please post the crash reports again? Thanks a lot.

@Sue Dunham: First of all thanks a lot for all your detailed bug reports. They were very helpful. Actually, I overlooked the one from 2022-03-17 00:12:56, but I will also look into it.

Can one type a script name directly into the textbox at the top of the query and batch tabs? I seem to only be able to input a filename by clicking the "…" button and navigating.
I didn't change this, but by accident I figured out, that you can paste the whole path into the filename box of the open file dialog. It's not that convenient as directly pasting a path into the textbox, but there is no need to always navigate.

I'm having character coding issues coming from my UTF-8 sql files that look like they're being read as cp1252. I have a "•" being used as a visual delimiter between an album and an artist in one node, and that is rendered "•" in the query textbox and the nodes, though the Result node options display is fine. I'd love an Advanced option to set the default script encoding.
Actually, it worked with UTF-8 files with BOM before. Only UTF-8 files without BOM were not properly recognized. But this is now also the case. So there is no need for an option to set the default script encoding.

Anyway, if I use the SQL Tree edit query window to link and import a sql file for the query or batch panel, this button stops toggling the PSS. The SMP toggles its icon, the text file is created or removed, but the PSS doesn't do anything with it. Restarting foobar2000 restores order.
As already assumed the open file dialog changed the current directory.

So, um, yeah, fix that please?  ;D
It's done.

Another bug: linking a sql file for the query isn't itself sufficient to enable the OK button. Text must be pasted in manually to do so.
It's fixed.

Another issue: once a file has been linked in the query pane, I can't remove the link. "Delete" in the context menu is disabled, and I'm not sure how to enable it.
You can't, but there is now an additional button to remove the link.

Not sure if this is a bug or an expected change in behavior, but I formerly had temporary tables in the batch portions of nodes that the query portions can no longer see in time.
This is most likely not a bug, but it's not really an expected change also. I tested this scenario and I couldn't reproduce your problem, but this doesn't mean that it cannot happen.

What you are describing, normally happens, if a sql statement is running on a different connection, than the one, which is creating the temporary table. And I think, this is what is happening here. In the old version there was only one permanent database connection and you could therefore rely on having the temporary table available, whenever you need it. This is not the case anymore. A database connection is requested from a connection pool, whenever one is needed and given back to it, when it is not needd ynamore. Although in most cases you will use the same database connection for batch and query part, it cannot be guaranteed.

If your temporary tables are not to big you could try to create them in memory schema, by attaching it like this:
Code: [Select]
ATTACH DATABASE 'file::memory:?cache=shared' AS memtemp;



Re: foo_uie_sql_tree

Reply #266
Hi,

seems to be fixed. Thank you :-)


Re: foo_uie_sql_tree

Reply #268
crash still happens.
I still can't reproduce the crash, but there is something strange in your bug report concerning the last log messages. The last message is "Create table `sql_SQLiteTags` ..." which is normally immediate followed by "updating table `sql_SQLiteTags` ...", but this is missing. Between these two messages are only few checks in the database. So maybe there is an issue with the database. To check this, you could try the following:
  • Start the application and open the SQLite console.
  • Execute the following commands:
    Code: [Select]
    ATTACH fb2k_profile_path()||'\configuration\foo_sqlite.db' AS sqlite;
    VACUUM sqlite INTO fb2k_profile_path()||'\configuration\foo_sqlite_new.db';
  • Close the application
  • Move foo_sqlite.db and if exist foo_sqlite.db-shm and foo_sqlite.db-wal from your configuration folder to another location
  • Rename foo_sqlite_new.db to foo_sqlite.db
  • Start the application and check, if the problem still exists

In any case I'd be glad, if you could provide me the up to 3 old dabase files, so that I can do some tests on my own.

Re: foo_uie_sql_tree

Reply #269
Crash still happens.

What I've done now:

  • Deinstall foo_uie_sql_tree and foo_sqlite
  • Clean all traces of the plugins
  • Reinstall the plugins
The console output is:
Quote
[01:02:41] [foo_uie_sql_tree] 01:02:41.553: Create sqltree tables ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.553: Get new pool database connection, db pool size: 0, available connections: 0 ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.553: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.553: Create table sql_ObjectVersion ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.569: Create table sql_ObjectVersion: 0:00.010924
[01:02:41] [foo_uie_sql_tree] 01:02:41.569: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db: 0:00.012490
[01:02:41] [foo_uie_sql_tree] 01:02:41.569: Get new pool database connection, db pool size: 0, available connections: 0: 0:00.018491
[01:02:41] [foo_uie_sql_tree] 01:02:41.569: creating table `ust_TreeItem` ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_TreeItem`: 0:00.001845
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_TreePath` ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_TreePath`: 0:00.001114
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_QueryNodeDetail` ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.584: creating table `ust_QueryNodeDetail`: 0:00.001792
[01:02:41] [foo_uie_sql_tree] 01:02:41.591: Close database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Close database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db: 0:00.014596
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Create sqltree tables: 0:00.045786
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Get new pool database connection, db pool size: 0, available connections: 0 ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_uie_sql_tree.db: 0:00.001489
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Get new pool database connection, db pool size: 0, available connections: 0: 0:00.004170
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Get new pool database connection, db pool size: 0, available connections: 0 ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db: 0:00.001613
[01:02:41] [foo_sqlite] 01:02:41.607: Create MetaDB_Module ...
[01:02:41] [foo_sqlite] 01:02:41.607: Create MetaDB_Module: 0:00.000139
[01:02:41] [foo_uie_sql_tree] 01:02:41.607: Attach database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.materialize.db as materialize ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Attach database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.materialize.db as materialize: 0:00.004036
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Get new pool database connection, db pool size: 0, available connections: 0: 0:00.009339
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Create example nodes ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Get new pool database connection, db pool size: 1, available connections: 0 ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Open database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.user.db: 0:00.001540
[01:02:41] [foo_sqlite] 01:02:41.622: Create MetaDB_Module ...
[01:02:41] [foo_sqlite] 01:02:41.622: Create MetaDB_Module: 0:00.000157
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Attach database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.materialize.db as materialize ...
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Attach database C:\Users\Lange\AppData\Roaming\foobar2000\configuration\foo_sqlite.materialize.db as materialize: 0:00.002033
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Get new pool database connection, db pool size: 1, available connections: 0: 0:00.005188
[01:02:41] [foo_uie_sql_tree] 01:02:41.622
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQLite Error: (1) no such column: album artist

Error while preparing the first statement of:
SELECT ifnull([album artist],artist) album_artist,
       '['||date||'] '||album album_col,
        (CASE WHEN discnumber THEN 'Disc '||discnumber ELSE NULL END) disc_number
FROM MediaLibrary
GROUP BY 1,album,discnumber
ORDER BY ifnull([album artist],artist) COLLATE NaturalNoCase,'['||date||'] '||album desc,discnumber
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
[01:02:41] [foo_uie_sql_tree] 01:02:41.622: Create example nodes: 0:00.009555
I must confess, that I don't have tagged album artist in my whole library (don't have compilations etc.).

Re: foo_uie_sql_tree

Reply #270
I must confess, that I don't have tagged album artist in my whole library (don't have compilations etc.).
That is not what is causing the error message. In fact you're databases are quite strange. It seems that there someting went wrong during the upgrade to version 2.0.0. You have still tables in foo_sqlite.db, which should have been moved to foo_sqlite.user.db and even more strange you have a new internal table in foo_sqlite.user.db.

Additionally the VACUUM command, which you executed to re-create the database, dropped more than half of the tables in your foo_sqlite.db. And this is the reason, why you received now the errro message. I also ran the VACUUM command on your old foo_sqlite.db and it worked as it should. All tables where available in foo_sqlite_new.db. Although executing of PRAGMA integrity_check didn't show any error, it could be that your database is somehow broken.

Nevertheless your database problems are probably not responsible for the crash. The first access to your foo_sqlite.db is much later than it normally should be and during the first access some initialization is executed for the database. There could be reasons why the initialization takes place so late, but to avoid problems relating to the late initialization, the initialization is now forced to be executed during the application startup.This cannot solve your database problems, but it should solve the crash you had.

Re: foo_uie_sql_tree

Reply #271
Crash is fixed. Thank you.

Database problem: The reason for the problems could be that I was upgrading and then downgrading again (because of the bug). I attach the hopefully repaired and not broken version of the database files.


Re: foo_uie_sql_tree

Reply #273
However, if I put just that first SELECT statement in the batch and the SELECT portion of the second one (minus the path and subsong fields) into the query, then the treeview will fill with all the right nodes, but nothing will be sent to the playlist on clicking.
Actually, I could not reproduce this, but there was an issue with SELECT statements containing CASE expressions. Although the relevant CASE expression for your second SELECT is wrapped in parantheses and should not have been affected, the fix might have helped. If this is still not working, please check the console after you clicked on the node. The generated node query is now printed to the console and should help for further investigations.

Further puzzler: the working version will not work if the Omit not existing files when sending to a playlist box is checked, only working when it is not.
This is fixed now.

Re: foo_uie_sql_tree

Reply #274
Database problem: The reason for the problems could be that I was upgrading and then downgrading again (because of the bug).
This explains most of your database problem. Only the dropped tables after the VACUUM is still a mystery. This is pure SQLite code and definitely works. The only thing what this could explain, is that you somehow mixed up the files.

I attach the hopefully repaired and not broken version of the database files.
Yes, all databases are ok. The foo_sqlite.db in the configuration2 archive is quite large and could be compressed by executing
Code: [Select]
ATTACH fb2k_profile_path()||'\configuration\foo_sqlite.db' AS sqlite;
VACUUM sqlite;
but this is not really necessary.

Here the tree database.
This database was not affected by the problems, but it is also ok.