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 57777 times) previous topic - next topic
0 Members and 3 Guests are viewing this topic.

Re: foo_sqlite

Reply #100 –
Quote from: Sue Dunham on
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.
I tried the same, but it worked for me without any problems. Also updating PlaylistUpdatable to split values with commas into multiple value works for me without any crash. And I tried several cases with and without your database. Even updating almost 40000 tracks which were all prepared to have a multivalue field containing commas was no problem.

So, there must be an additional parameter in your setup, that causes the crashes.

Re: foo_sqlite

Reply #101 –
Miscellaneous observation: I got my latest foobar incarnation all tidied up, and I noticed that the zip of it all is quite a bit bigger than the previous one. I did a pretty good job of getting all my cruft out of there, so I looked around for what else was present. It seems that, between SQLite Viewer and Random Pools, I am now the proud owner of two copies of icutd70.dll, and it's a big 'un. I guess that explains it. :D

It's not a big deal, but could I perhaps get away with one?

Re: foo_sqlite

Reply #102 –
Quote from: Sue Dunham on
It's not a big deal, but could I perhaps get away with one?
In theory, yes. The DLLs just needs to be placed somewhere in the search path. However I highly recommend not to do anything like that, just to save some MB. The DLLs might be different in the future for both components, just because one component was already upgraded with a newer version, while the other wasn't.

Re: foo_sqlite

Reply #103 –
Quote from: SimBun on
I've just run a bunch of my stored programs and with the metadb_handle to path change, they all seem to work…
Help! I'm not finding anything anywhere on what to change metadb_handle to.
Here's a sample query:
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT metadb_handle, "Top 25" FROM MediaLibrary WHERE play_count > 0 ORDER BY play_count DESC LIMIT 25;

What does "the actual file path and subsong index" even mean in this context?
I'm trying to populate the playlist with the top 25 most played songs. So, actual file path to all my music? Subsong index makes no sense here because I want to check all songs…
(It's pretty obvious I don't know why I had to use metadb_handle in the first place, but it just worked, so I never questioned it.)
Processed audio in java and python.

Re: foo_sqlite

Reply #104 –
Quote from: For Serious on
Help! I'm not finding anything anywhere on what to change metadb_handle to.
Just change it to "path, subsong". So your sample query will become

Code: [Select]
INSERT INTO Playlist_Updatable(path, subsong, playlist_name)
SELECT path, subsong, "Top 25" FROM MediaLibrary WHERE play_count > 0 ORDER BY play_count DESC LIMIT 25;

Quote from: For Serious on
What does "the actual file path and subsong index" even mean in this context?
They are just two track attributes, which are necessary to uniquely identify a track.

Quote from: For Serious on
So, actual file path to all my music? Subsong index makes no sense here because I want to check all songs…
You are misunderstanding the terms file path and subsong here. File path refers to the individual full path of the file belonging to a track and subsong is only relevant, if you have multi-track files, e.g. cue sheets. Then you need the subsong index to refer to a track in this multi-track file.

If you have only single track files, you could omit the subsong, but it is no mistake to always specify it.

Quote from: For Serious on
(It's pretty obvious I don't know why I had to use metadb_handle in the first place, but it just worked, so I never questioned it.)
It served the same purpose as "path, subsong", but in a more obscure way.

Re: foo_sqlite

Reply #105 –
Thank you for explaining all that.
Now my error has moved on to 'no such column: Top 25'

Again, no idea why that would happen. That's how I created the playlist Top 25 in the first place. I thought it creates it if it's not there, and updates it if it is there.
Processed audio in java and python.

Re: foo_sqlite

Reply #106 –
Quote from: For Serious on
Now my error has moved on to 'no such column: Top 25'
Yes, of course. I didn't notice it, but you need to put 'Top 25' into single quotes, not double quotes.

Re: foo_sqlite

Reply #107 –
Again, thank you very much! I've got all my queries working again.
Processed audio in java and python.

Re: foo_sqlite

Reply #109 –
Quote from: grimes on
error again.
Well, the reports are still for foo_sqlite version 2.1.1, while the latest version is 2.1.4. Nevertheless, the new report shows an additional hint, which could help to fix the issue, as I still don't have any crashes although I use it quite frequently. The dump file shows, of course, where the crash happens, but it's not obvious why it happens there. In addition to the files it would also be helpful which SQL statement exactly you executed.

Besides this, could you please also post the relevant files for version 2.1.4 as I highly doubt, that the issue is fixed with this version?

Re: foo_sqlite

Reply #111 –
Quote from: grimes on
Updated to 2.1.4.
SQL statement:
SELECT count(DISTINCT album) album_count
   FROM medialibrary
Hmm, according to crash log #127 from today, it must have been a different SQL statement, as "Row limit (5000 rows) for the SQLite console exceeded." was logged to the console right before the crash, while the statement above always returns only one row. So, it would be helpful to get also the crash reports for version 2.1.4.

Re: foo_sqlite

Reply #112 –
Looking for a bit of help.  I'm getting close to sensible results, but I've got two issues.  I've got some code that is basically a duplicate finder ....

1) Code:

Code: [Select]
select a.path, a.artist, a.title, a.album, a.length, a.tracknumber, a.composer
  from mediaLibrary a
         inner join
       (select artist, title, album, length, tracknumber, composer
          from MediaLibrary
          group by artist, title, album, length, tracknumber, composer
          having count(*)>1
       ) b on (    a.artist = b.artist
               and a.title = b.title
               and a.album = b.album
               and a.length = b.length               
               and a.tracknumber = b.tracknumber
               and a.composer = b.composer)
  order by a.path

But some of the files don't have data in the last two tags:  tracknumber and composer.  Is there a way to ignore these fields if they are blank.  A bit like [%tracknumber%] ?

2) When I do "copy all" it's not copying (I wanted to copy to Excel) but that's not working (and the list isn't that large, and I've set the limit to 8000 rows which is easily large enough). 

So I'd like to export the results to a new playlist called "export". 

Can anyone tell me how to:
a) export to a new playlist called "export" and,
b) ignore fields in the comparison where those fields are blank. 

Any and all help much appreciated.

Cheers,
C.
PC = TAK + LossyWAV  ::  Portable = Opus (130)

Re: foo_sqlite

Reply #113 –
Quote from: carpman on
2) When I do "copy all" it's not copying (I wanted to copy to Excel) but that's not working (and the list isn't that large, and I've set the limit to 8000 rows which is easily large enough).
In general this should work. However, I figured out, that there is some kind of initializing problem, at least if there was already copied something before. Calling "Copy all" a second time seems to solve this problem for the moment. I will fix this for the next version.

Quote from: carpman on
Can anyone tell me how to:
a) export to a new playlist called "export" and,
b) ignore fields in the comparison where those fields are blank. 
You can get both together this with this statement

Code: [Select]
insert into PlaylistUpdatable(path,playlist_name)
select a.path, 'export'
  from mediaLibrary a
         inner join
       (select artist, title, album, length, tracknumber, composer
          from MediaLibrary
          where tracknumber is not null and composer is not null
          group by artist, title, album, length, tracknumber, composer
          having count(*)>1
       ) b on (    a.artist = b.artist
               and a.title = b.title
               and a.album = b.album
               and a.length = b.length              
               and a.tracknumber = b.tracknumber
               and a.composer = b.composer)
  order by a.path

Re: foo_sqlite

Reply #114 –
Quote from: carpman on
But some of the files don't have data in the last two tags:  tracknumber and composer.  Is there a way to ignore these fields if they are blank.  A bit like [%tracknumber%] ?
Quote from: fbuser on
where tracknumber is not null and composer is not null
This will remove the tracks from the matching process entirely.

Generally, SQL treats a missing value as unknown, so when comparing one unknown to any other value (including another unknown) the result is also unknown (not true). In your example, if two tracks matched on all the tags except one (or both) tracks had missing track numbers the join at the bottom of your step (and a.tracknumber = b.tracknumber) would exclude those matches from the result set.

If all you're asking is to bring back matches where BOTH matching tracks are missing tracknumber or composer then the following sql should work (because it replaces the missing/unknown value with #).
Code: [Select]
select a.path, a.artist, a.title, a.album, a.length, a.tracknumber, a.composer
  from mediaLibrary a
         inner join
       (select artist, title, album, length, coalesce(tracknumber,'#') as tracknumber, coalesce(composer,'#') as composer
          from mediaLibrary
          group by 1, 2, 3, 4, 5, 6
          having count(*)>1
       ) b on (    a.artist = b.artist
               and a.title = b.title
               and a.album = b.album
               and a.length = b.length              
               and coalesce(a.tracknumber,'#') = b.tracknumber
               and coalesce(a.composer,'#') = b.composer)
  order by a.path
This however won't match tracks where one has a tracknumber but the other doesn't, and I'm not sure there's a way around that.

Re: foo_sqlite

Reply #115 –
Quote from: SimBun on
Quote from: carpman on
But some of the files don't have data in the last two tags:  tracknumber and composer.  Is there a way to ignore these fields if they are blank.  A bit like [%tracknumber%] ?
Quote from: fbuser on
where tracknumber is not null and composer is not null
This will remove the tracks from the matching process entirely.
Of course. This is what I understood @carpman wanted to achieve. But at a second glance, this should already be the case with the original query caused by the inner join. On the other hand using the coalesce() function is not necessary for your solution. Just write the relevant part of the join as
Code: [Select]
a.tracknumber is b.tracknumber
and a.composer is b.composer
and keep the rest of the query as it is.

Re: foo_sqlite

Reply #116 –
@fbuser

Yes, that works:

Code: [Select]
select a.path, a.artist, a.title, a.album, a.length, a.tracknumber, a.composer
  from mediaLibrary a
         inner join
       (select artist, title, album, length, tracknumber, composer
          from MediaLibrary
          group by artist, title, album, length, tracknumber, composer
          having count(*)>1
       ) b on (    a.artist = b.artist
               and a.title = b.title
               and a.album = b.album
               and a.length = b.length               
               and a.tracknumber is b.tracknumber
               and a.composer is b.composer)
  order by a.path

Weird thing is, I did "copy all" and that also worked.  So, don't know why that works sometimes and not others.

I'm entirely useless at getting my head around SQL queries.   Where do I put the code to turn this query into a query that generates a playlist within fb2k (rather than just the SQLite console)?

Thanks for sorting out the main issue. 

C.
PC = TAK + LossyWAV  ::  Portable = Opus (130)

Re: foo_sqlite

Reply #117 –
Quote from: fbuser on
Of course. This is what I understood @carpman wanted to achieve. But at a second glance, this should already be the case with the original query caused by the inner join.
Sorry I should have made that more clear, I assumed it was just an interpretation problem, and my explanation was for @carpman as in the related post he said he knows nothing about sql; I wasn't trying to teach you about NULL/sql :-)

Quote from: fbuser on
On the other hand using the coalesce() function is not necessary for your solution. Just write the relevant part of the join as
Code: [Select]
a.tracknumber is b.tracknumber
and a.composer is b.composer
and keep the rest of the query as it is.
Interesting, I've never used IS like that before!

Quote from: carpman on
Where do I put the code to turn this query into a query that generates a playlist within fb2k (rather than just the SQLite console)?
Just replace the first select line with the line that @fbuser included in his response:
Code: [Select]
insert into PlaylistUpdatable(path,playlist_name)
select a.path, 'export'

Re: foo_sqlite

Reply #118 –
Thanks @SimBun and @fbuser  

Really appreciate the help and patience.  I completely missed that part of fbuser's reply.  So sorry for being a dunce.

All working perfectly now.   I ended up making all the statements "is" not "=" as for some reason, the code was missing one item out of the 2 x 1,260 that were being cross checked. 

Just in case anyone else should do something similar to me, I'll outline why I wanted this code. 

I have a select group of tracks from my library that I play on my DAP.  Because I alter the files during encoding (use Replay Gain +3db with a limiter) which are transcoded (from lossy and lossless sources) into lossyFLAC.  Thus, all the tags in the code I used should be identical (including length).  I just wanted a way to check if all the files on the DAP could be quickly found in my media library.  They've now been tagged with a DAP = 1 tag.

Very useful for duplicate finding now I have this code -- so thanks so much - really appreciate the help and thanks to fbuser for another nice component !! 

Final code: 

Code: [Select]
insert into PlaylistUpdatable(path,playlist_name)
select a.path, 'Temp'
  from mediaLibrary a
         inner join
       (select artist, title, album, length, tracknumber, composer
          from MediaLibrary
          group by artist, title, album, length, tracknumber, composer
          having count(*)>1
       ) b on (    a.artist is b.artist
               and a.title is b.title
               and a.album is b.album
               and a.length is b.length               
               and a.tracknumber is b.tracknumber
               and a.composer is b.composer)
  order by a.path

Cheers,
C.
PC = TAK + LossyWAV  ::  Portable = Opus (130)

Re: foo_sqlite

Reply #119 –
Thanks carpman and fbuser for the sql.

How could I modify this to basically match artist and title even though the case of those would be different?

E.g. This Song Title and the other is This song title.

Code: [Select]
select a.path
  from mediaLibrary a
         inner join
       (select artist, title, length
          from MediaLibrary
          group by artist, title, length
          having count(*)>1
       ) b on (a.artist is b.artist
               and a.title is b.title
               and a.length is b.length)
  order by a.title

Re: foo_sqlite

Reply #120 –
I think the case insensitive already works as it's picking up andhim and Andhim but slight problem.

The query I posted above duplicates the same file twice in the created playlist.



Edit, silly me. I clicked twice on the query and it added them again.

Is there a way around this, without the need to remove duplicates from the Edit menu? See my settings for this.



Edit 2: Send to playlist fixed that. Sorry for that.

Re: foo_sqlite

Reply #121 –
Quote from: stevehero on
Send to playlist fixed that.
"Send" replaces everything currently in the destination playlist, which I (personally) don't find particularly useful.
It's your privilege to disagree, but that doesn't make you right and me wrong.

Re: foo_sqlite

Reply #122 –
Hello, on my old and slow 32bit audio PC I am getting, time to time, pop-up reporting "Long running query detected" and asking me to continue or terminate it.

Is there the option to suppress it or just flag "YES continue" by default?

Kind regards, Andrea

Re: foo_sqlite

Reply #123 –
Quote from: AndreaT on
Hello, on my old and slow 32bit audio PC I am getting, time to time, pop-up reporting "Long running query detected" and asking me to continue or terminate it.

Is there the option to suppress it or just flag "YES continue" by default?
Under "Preferences -> Advanced -> Tools -> SQLite utilities -> Control parameters -> Threshold for detecting long running SQL background queries (in seconds)" you can define the interval for displaying this message. But you should be aware, that without this dialog you don't have any chance to abort a background query without restarting the application. This is mainly needed for badly defined background queries in foo_uie_sql_tree, e.g. unwanted cartesian products could lead to running a query for many hours.

For the materialization background maintenance, which is causing the dialog in your case, this should normally not occur in everyday use. However, with fb2k v2 there is an issue, that on manual rescanning a monitored library folder or a folder on an unavailable external drive is becoming available again, all tracks for this folder are signaled as newly inserted to the library. If this is the case also for you, you can see in the console how many tracks were added to the materialized media library. If this is not the case for you, I'd like to know, what you are doing before this dialog appears and how many tracks are affected.

In the end you should not set the threshold too high, because otherwise you wouldn't be notified if something is wrong.

Re: foo_sqlite

Reply #124 –
Hello @fbuser for your kind and precise support.
In my case, I found the problem was due to ".., with fb2k v2 there is an issue, that on manual rescanning a monitored library folder or a folder on an unavailable external drive is becoming available again,".
Thanks and regards, Andrea