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

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #175
Hello, I just found out about SQLite for fb2k, I've been going for months thinking if it's possible to implement a tagging system and a few more other implementations using SQLite, The External Tags plugin for fb2k supports SQLite database so that it can add values to a database... But I'm not sure if there's a way for me to update these tags... Currently, my setup is as follows: Firstly, I use the context menu to edit external tags and set a custom field called FAVOURITE which has a value of ❤ and SQL Console correctly picks up this field...

What I want here is if it's possible to assign a button to run SQLite scripts behind the scenes to tag the currently selected item using Playlist.item_is_selected in an attempt to remove the value in the FAVOURITE field that way it simulates tagging and untagging tracks.

Secondly, I wanted to know if there is a way to have something similar to foo_playcount where you can set the last played and play count fields of a track...

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #176
them. Took a little digging but the component appears coded to look for the documentation in:
AppData\Roaming\foobar2000-v2\user-components-x64\foo_sqlite\help\

However, when Foobar2000 is installed from the Microsoft Store, the equivalent location is actually:
AppData\Local\Packages\Resolute.foobar2000modern_cg7j1awqsza28\LocalCache\Roaming\foobar2000-v2\user-components-x64\foo_sqlite\help
Well, the component actually does not look directly into the mentioned folders. It's using only functions provided by the foobar2000 SDK to identify the component's folder and going further down to the help folder inside of it. Anyway I'll try to track down the problem, if I find the time.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #177
The External Tags plugin for fb2k supports SQLite database so that it can add values to a database...
Just to prevent you from thinking in the wrong direction: You definitely should not access its database with with foo_sqlite. Writing to this database is supposed to be done only by just using tagging means of foobar2000 or relevant plugins. It wouldn't work quite well anyway as the external tags plugin stores the tag data in a binary format.

By the way, foo_sqlite provides also the possibility to store tag data in a database, just with a different approach.

But I'm not sure if there's a way for me to update these tags...
As mentioned above, just use the available tagging means. One of it is to utilize the PlaylistUpdatable or MediaLibraryUpdatable virtual tables of foo_sqlite.

What I want here is if it's possible to assign a button to run SQLite scripts behind the scenes to tag the currently selected item using Playlist.item_is_selected in an attempt to remove the value in the FAVOURITE field that way it simulates tagging and untagging tracks.
That's not possible. However, you could have a look at foo_uie_sql_tree. It allows you to define clickable nodes to execute a sql script, which can do what you want.

Secondly, I wanted to know if there is a way to have something similar to foo_playcount where you can set the last played and play count fields of a track...
No, this is not possible. These information are handled internally by foo_playcount in its own database.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #178
Hey thanks a-lot for pointing out some flaws... Will definitely check SQL trees and try to figure out something.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #179
What I want here is if it's possible to assign a button to run SQLite scripts behind the scenes to tag the currently selected item using Playlist.item_is_selected in an attempt to remove the value in the FAVOURITE field that way it simulates tagging and untagging tracks.
I think easier to use Masstagger if you want a simple tag on/off button.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #180
What I want here is if it's possible to assign a button to run SQLite scripts behind the scenes to tag the currently selected item using Playlist.item_is_selected in an attempt to remove the value in the FAVOURITE field that way it simulates tagging and untagging tracks.
I think easier to use Masstagger if you want a simple tag on/off button.

Hey, I'm currently using that setup... while it's working just as expected it will be better to avoid writing tags directly to files and instead use External Tags or m-Tags. MusicBee has something similar to this called Virtual Tags, was just hoping if we had something similar to this with Foobar?

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #181
^ I think you just answered your own question. I'm under the impression when you create External Tags for an album, all future tagging (including Masstagger) is written to external tags, no? And I believe you can set it to write to SQL database, and take over all tagging operations automatically...

(We're getting off-topic here, best to move any follow-up questions to the correct topic)

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #182
Hi, I'm trying to wrap my head around this plugin, and its usage. I wonder if it is possible to use it to store information per artist and per album (like an internal wiki or reviews database) without writing this information to the audio files itself (and getting it "materialized" in the sqlite db). Thanks for any hints in the right direction.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #183
Hi, I'm trying to wrap my head around this plugin, and its usage. I wonder if it is possible to use it to store information per artist and per album (like an internal wiki or reviews database) without writing this information to the audio files itself (and getting it "materialized" in the sqlite db).
Yes, it's possible. Just have a look at the SQLite tag functionality in the help file. It allows you to define, which of the defined tags should will be written only to the db and which will be also written to the file. Additionally you can define lookup tables for the artist and album tags containing the tags for the relevant information.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #184
I'm trying to use foo_random_pools with foo_sqlite, but if I try to refer to any column that exists in the MediaLibrary table other than two that I added manually, I get a "no such column" error. I've tried entering examples from the foo_random_pools documentation verbatim (like putting
Code: [Select]
genre='Rock'
in the "Pool set SQL filter" field) in case the problem was that my syntax was bad, but I still get the same error. Any idea why this might be happening?

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #185
Any idea why this might be happening?
Please, post the full details of what you are trying to do and the full error message as well.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #186
Never mind, it was apparently a problem with my CTE AS statement manifesting as an inability to recognize column names in other fields. Sorry to bother you!

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #187
Thanks for the great plugin.  I wrote the query below to combine the Series field and Genre field into a single column called TestGrouping.  I do NOT want to concatenate the two fields.

If the track has a Series, then I want the TestGrouping column to show the Series.
If the track does NOT have a Series, then I want the TestGrouping to show the Genre.

I added "series" to the MediaLibrary Table. It's set to TFExpression and it uses the %original album% field.
I also added "testgrouping" to the MediaLibrary Table. It's set to TFExpression.

The problem is that the query adds the Series where appropriate, but not the Genre when the Series field is empty.  I just get a question mark (?).

Here's the query:
Code: [Select]
SELECT
    COALESCE(series, genre) AS testgrouping,
    [album artist],
    grouping,
    album
FROM MediaLibrary
ORDER BY
    testgrouping DESC,
    [album artist],
    grouping,
    album;

Thanks for any help!

Windows 10
Columns 2.1.0
Foobar2000 v2.0
SQLite Utilities 3.0.4
SQLite Tree 4.0.6

PS - ignore my old signature below.
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #188
PS - ignore my old signature below.
If you update your signature, the update will appear on all your posts – not just your new posts.
It's your privilege to disagree, but that doesn't make you right and me wrong.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #189
If you update your signature, the update will appear on all your posts – not just your new posts.

I had changed my signature, but I guess I hit the wrong button or something.  It's updated now.
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #190
I figured out the problem.  I needed to add brackets ([]) around the Series field in the SQLite Utilities \ MediaLibrary table.  Why did that effect anything?  I noticed it removed the ? from the empty fields in the Query results.

I also had to change the Coalesce function:
Code: [Select]
SELECT
    COALESCE(NULLIF(series, ''), genre) AS testgrouping,
    [album artist],
    grouping,
    album
FROM MediaLibrary
ORDER BY
    testgrouping ASC,
    [album artist],
    grouping,
    album;
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

 

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #191
I also added "testgrouping" to the MediaLibrary Table. It's set to TFExpression.
This is not necessary and has no impact on your query.

I figured out the problem.  I needed to add brackets ([]) around the Series field in the SQLite Utilities \ MediaLibrary table.  Why did that effect anything?
It's just how title format works. It has actually nothing to do with SQL.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #192
Awesome, thanks for the explanation.
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #193
Getting fb2k running under WINE in Linux, I saw that the font in the code portion of the SQLite console was not monospaced. I got it looking normal again by installing Courier New on the system.

Naturally, this prompts me to ask if there is a way to set that font. I couldn't find anything in the preferences. If not, that would be a nice little feature to tack onto the next version.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #194
I'm not really familiar with SQL but I'm willing to learn.

I would like to create some statistical tags, e.g. the last time an artist was played or the average play count.

The SQLite tag creation window is not exactly self-explanatory. It would be good to have some examples since there doesn't seem to be any official documentation for the component.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #195
there doesn't seem to be any official documentation for the component.
There is: select "SQLite utitilies" from the help menu.

However, you won't be able to achieve this
I would like to create some statistical tags, e.g. the last time an artist was played or the average play count.
with the SQLite tags.

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #196
How do I remove unused fields from the MediaLibrary table?  Is there a way to refresh the table or update it?

Thanks for any help!
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #197
How do I remove unused fields from the MediaLibrary table?  Is there a way to refresh the table or update it?
You can remove them in the preferences under "MediaLibrary -> SQLite utitilies -> MediaLibrary table"

Re: [fb2k v2] SQLite Utilities (foo_sqlite)

Reply #198
Sorry, I meant to ask how do I delete the unused fields in bulk?  I know I can remove them individually, but I have a lot of unused fields now.  It's okay if it takes a long time to refresh the MediaLibrary Table.  Thanks!
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6