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] SQLite Utilities (foo_sqlite) (Read 76147 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #200
How do I properly access custom tag fields? If I add a "part" TFExpression with [%part%] to MediaLibrary table to get the value of a <PART> tag I added to many tracks, it always seems to return empty. Do I have to do something else to make the component add those tags to MediaLibrary table?

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #201
How do I properly access custom tag fields? If I add a "part" TFExpression with [%part%] to MediaLibrary table to get the value of a <PART> tag I added to many tracks, it always seems to return empty. Do I have to do something else to make the component add those tags to MediaLibrary table?
There is no need to create a TFExpression column for existing tags. It will only decrease the performance. Just use a normal tag column for it.

Apart from this I see no reason why you always get empty values, if you have your files have values for this tag. It definitely should work without further adjustments.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #202
Hm interesting, it does work with a normal tag. Thx.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #203
It would be amazing if there was some way to use SQLite to generate autoplaylists. Is there a chance for something like this in the future, or is there already some way to do it I haven't noticed cos I'm dumb?

Currently I just know that I can use foo_uie_sql_tree to send SQL query results to a playlist by interacting with the panel.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #204
It would be amazing if there was some way to use SQLite to generate autoplaylists. Is there a chance for something like this in the future
There is absolutely no chance. This is technically not possible.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #205
Well, just to be clear, what I'm thinking of is not to create F2k standard autoplaylists (I know those only work with standard library queries) but rather to automatically fill regular playlists based on SQL queries, without explicit user interaction. The main obstacle I can think of here is the question of how to schedule playlist updates. I don't know how autoplaylists update their contents, and I don't know whether those triggers are also exposed to components, or whether that would even be practical given the performance limitations of SQL requests.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #206
Well, just to be clear, what I'm thinking of is not to create F2k standard autoplaylists (I know those only work with standard library queries) but rather to automatically fill regular playlists based on SQL queries, without explicit user interaction. The main obstacle I can think of here is the question of how to schedule playlist updates. I don't know how autoplaylists update their contents, and I don't know whether those triggers are also exposed to components, or whether that would even be practical given the performance limitations of SQL requests.
Without entering on technical details, that is somewhat possible. Another thing is the conditions required, ensuring it works properly or if that goes beyond the component aim. It can not be an AutoPlaylist. That's true.

But exactly your request is present on my playlist manager, in "Smart Playlists", and they are updated on real time whenever the source changes. In this case they use other playlists as source, and apply a query on them. Such special playlist may also be locked by the component, so it can only be changed by it, whether on demand or by any callback.

If it's possible on JS, it's obviously possible on a component using the SDK, since the callbacks are the same.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #207
I don't know how autoplaylists update their contents, and I don't know whether those triggers are also exposed to components
They are, but the problem is that the information provided by those triggers can't be used reasonably. So, the only information which can be used from this triggers is that something happened.

or whether that would even be practical given the performance limitations of SQL requests.
Not only because of the performance of a single query. Due to the problem of utilizing the information of change events you would need always to execute all defined queries for each change. It might be possible to soften this constraint a bit by providing some configuration options when to execute a query, but it will not really solve the problem.

Without entering on technical details, that is somewhat possible.
No, it's not.

But exactly your request is present on my playlist manager
Not really. It might be the case for certain use cases but in general this is not true.

If it's possible on JS, it's obviously possible on a component using the SDK, since the callbacks are the same.
This is a wrong conclusion.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #208
 ::)  ::)  SMP is exposing callbacks present on the SDK... another thing is you not wanting to open that can of worms, the feature being beyond the component aim or whatever.

But please don't say it is not possible when I'm clearly doing it for some specific use cases (which was the premise of my post anyway). I specifically said they were not going to be Autoplaylists, which clearly means it's not valid for general cases. Anyway that's totally off topic, since clearly the user request is about not having to update a playlist on demand, it may well be enough to have it updated once a day, after startup or whatever.

But it's clearly possible to bind a playlist to a SQL query (within your component scope) and update it with some specific triggers, the same is possible in JS to do it and update a playlist whenever other playlists change. And that's just an example, it can be done by time, on metadb changes, or whatever. A callback not providing the specific change, doesn't mean you can not simply re-update the entire playlist whenever there is a change, even if sub-optimal. Or limit it by a refresh rate. Or keep internal caching of changes for critical things needed. You don't like those approaches, which you are clearly aware about per your post, fine. But It's possible.

Find it funny how many things are "impossible" when we talk about the SDK or native foobar (example), and then the same works fine, with some code logic changes, on JS wich is much more limited and should have lower performance.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #209
Well, just to be clear, what I'm thinking of is not to create F2k standard autoplaylists (I know those only work with standard library queries) but rather to automatically fill regular playlists based on SQL queries, without explicit user interaction. The main obstacle I can think of here is the question of how to schedule playlist updates. I don't know how autoplaylists update their contents, and I don't know whether those triggers are also exposed to components, or whether that would even be practical given the performance limitations of SQL requests.
But exactly your request is present on my playlist manager, in "Smart Playlists", and they are updated on real time whenever the source changes. In this case they use other playlists as source, and apply a query on them. Such special playlist may also be locked by the component, so it can only be changed by it, whether on demand or by any callback.

Oh, I wasn't sure what they do, but I read something in the release notes about support for foo_sqlite stuff. So I guess that's what they're for. I'll take a look in the docs.

They are, but the problem is that the information provided by those triggers can't be used reasonably. So, the only information which can be used from this triggers is that something happened.

Yeah, that was what I was worried about. Running the query on the whole library every time any little thing changes doesn't seem practical.

But please don't say it is not possible when I'm clearly doing it for some specific use cases (which was the premise of my post anyway). I specifically said they were not going to be Autoplaylists, which clearly means it's not valid for general cases. Anyway that's totally off topic, since clearly the user request is about not having to update a playlist on demand, it may well be enough to have it updated once a day, after startup or whatever.

Indeed, some basic options for automatic "send top playlist" conditions would already go a long way as far as I'm concerned.

 

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #210
Well, just to be clear, what I'm thinking of is not to create F2k standard autoplaylists (I know those only work with standard library queries) but rather to automatically fill regular playlists based on SQL queries, without explicit user interaction. The main obstacle I can think of here is the question of how to schedule playlist updates. I don't know how autoplaylists update their contents, and I don't know whether those triggers are also exposed to components, or whether that would even be practical given the performance limitations of SQL requests.
But exactly your request is present on my playlist manager, in "Smart Playlists", and they are updated on real time whenever the source changes. In this case they use other playlists as source, and apply a query on them. Such special playlist may also be locked by the component, so it can only be changed by it, whether on demand or by any callback.

Oh, I wasn't sure what they do, but I read something in the release notes about support for foo_sqlite stuff. So I guess that's what they're for. I'll take a look in the docs.

Hmm I looked at the docs and it doesn't seem like XSP queries as described there are nearly as powerful as SQL queries. I'm mainly using SQL queries for contextual queries, i.e. using the "OVER" operator to get statistical max(), min(), sum() etc. values from previously defined sets of tracks (especially artists and albums). Apart from the ability of XSP to use playlists as pools (which would require me to generate absurd numbers of playlists) it doesn't sound like smart playlists can do that sort of stuff. Is there another way to dynamically generate pools independently of playlists, analogously to partitions in SQL? Is there a more extensive documentation of XSP queries somewhere that I can refer to?

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #211
I improved several scripts and thus broke some of them. I did find a fix for the places where it was broken (that I've found, at any rate), but I don't understand why the fix worked and why only some broke in the first place.

All of my queries are separate .sql files stored in a subfolder of the profile directory. Some of those used a nested call of the eval and fileio_read functions to execute other such scripts. I do this to cut down on repetition, especially for long sequences of statements that several queries have in common.

What this used to look like was something like this:

Code: [Select]
[setup stuff]


SELECT eval(fileio_read(fb2k_profile_path()
                     || '\themename\sql\scriptfile.sql'));


[SELECT query to display results in SQLite Console
 | UPDATE query to write data to music file tags]

I wanted to get away from hard-coding the themename directory in each script, since that changes with different themes. So this value became a field in a table, constants, and a view created the full paths with that and other values stored in that table. So the replacement became this:

Code: [Select]
SELECT eval(fileio_read((SELECT sql FROM view_paths)
                     || 'scriptfile.sql'));

Inserting that query in lieu of the string value gave rise to errors from the database being locked. I don't know anything about this issue.

The fix was to take the beginning of the scriptfile.sql out of it and paste it into each calling script right before the eval(fileio_read()) part. What was done for brevity may have been bad for other reasons. These first statements would DROP and CREATE the table that was subsequently used for the [SELECT | UPDATE] that occurred afterward in the main query. It seems that that separation was causing the lock issue, though it was fine before with the string values. The meat of scriptfile.sql is a series of statements manipulating that table, but maybe there's some parent-child threading circumstance I don't grok, or something.

Does that make any sense? How can I understand what happened? My use case is such a mass of bespoke tables and custom metadata tags that I'm hoping this description will suffice.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #212
Hmm I looked at the docs and it doesn't seem like XSP queries as described there are nearly as powerful as SQL queries. I'm mainly using SQL queries for contextual queries, i.e. using the "OVER" operator to get statistical max(), min(), sum() etc. values from previously defined sets of tracks (especially artists and albums). Apart from the ability of XSP to use playlists as pools (which would require me to generate absurd numbers of playlists) it doesn't sound like smart playlists can do that sort of stuff. Is there another way to dynamically generate pools independently of playlists, analogously to partitions in SQL? Is there a more extensive documentation of XSP queries somewhere that I can refer to?
XSP playlists were never meant to be as powerful as SQL nor Autoplaylists. They are a format used by Kodi, which I support to:
1. Provide direct translation between simple AutoPlaylists and Kodi systems.
2. Have an "AutoPlaylist" which can use other playlists as sources (#PLAYLIST# IS blabla).

That's all. And they were just a example of how a playlist in UI can be bound to arbitrary code and be updated with some callbacks or conditions (your request). You have full documentation on the readme PDF, which also links to kodi wikis.

Anyway, as I said, what you want has nothing to do with any kind of -existing- playlist. My replies were primarily mean for the developer. You want SQL and you want auto-updating of playlists by some triggers, and this is the component you need. Another thing is if your request is ever implemented. For sure, I would appreciate it too.. since query syntax is pretty limited in some cases and there have been no updates on years.
Spoiler (click to show/hide)

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #213
Hi All and Hi @fbuser ,
I need some help on repairing the config.sqlite file/dbase.
I am new to sqlite, so I tried first using "DB Browser for SQLite": exporting to SQL and then trying to rebuild a .sqlite file by importing the previously exported SQL. But I all the times faced blocking errors.

Is it possible to use the Foobar embedded SQLite console?
Or, can anyone give me a step by step process to follow?

Many thanks, Andrea