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

Re: foo_sqlite

Reply #75 –
Quote from: fbuser on
Quote from: SimBun on
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

Re: foo_sqlite

Reply #76 –
Quote from: SimBun on
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.

Re: foo_sqlite

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

Re: foo_sqlite

Reply #79 –
Quote from: grimes on
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.  :)

Re: foo_sqlite

Reply #80 –
Quote from: fbuser on
Quote from: SimBun on
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.

Re: foo_sqlite

Reply #82 –
Quote from: SimBun on
is there going to be any documentation for it?
You can call "Help -> SQLite viewer" for a short description of all functionality.

Quote from: SimBun on
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.

Quote from: SimBun on
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.

Re: foo_sqlite

Reply #83 –
Quote from: fbuser on
Quote from: SimBun on
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!

Re: foo_sqlite

Reply #84 –
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.

Code: [Select]
/* 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:
Code: [Select]
-- 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.

Re: foo_sqlite

Reply #85 –
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.

Re: foo_sqlite

Reply #86 –
Quote from: grimes on
Typo: [16:46:31] [foo_sqlite] 16:46:31.124: Open datatabase
It's fixed.

Quote from: Sue Dunham on
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.
 

Re: foo_sqlite

Reply #87 –
Quote from: fbuser
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 :-)

Re: foo_sqlite

Reply #88 –
Quote from: SimBun on
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.

Re: foo_sqlite

Reply #89 –
Quote from: SimBun on
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.

Re: foo_sqlite

Reply #90 –
Quote from: fbuser on
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!

Re: foo_sqlite

Reply #91 –
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.

Re: foo_sqlite

Reply #92 –
Quote from: Sue Dunham on
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.

Re: foo_sqlite

Reply #93 –
Quote from: fbuser on
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.

Re: foo_sqlite

Reply #94 –
Quote from: Sue Dunham on
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.

Re: foo_sqlite

Reply #95 –
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 ' · '.

Code: [Select]
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.

Re: foo_sqlite

Reply #96 –
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

Re: foo_sqlite

Reply #97 –
OK, I offloaded some backup and working tables to a different db, so I should go into the next crash a bit leaner.  :-[

Re: foo_sqlite

Reply #98 –
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.

Re: foo_sqlite

Reply #99 –
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.