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

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #351
Oh, once standart install, second is a portable. It seems there is no ex-/import possible!
I see the problem!
thank you grimes

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #352
I'm still having trouble getting my bitmaps to appear in the icon selection dialog. I had three of them entered in the Properties > Advanced > Tools > SQL Tree > Icon file paths (separate by |) box with the previous version of the component. They all had direct links, but only the first appeared.

I upgraded to the new version of the component, and still just one appeared in that dialog and also in the SQL Tree itself for the folder I had selected it for. I then tried to change them to relative links, and probably misdirected ones. The one bitmap that showed disappeared from the dialog, the tree, and also the button in the Edit Folder dialog. There was just a part of the text from the Advanced setting appearing in the button.

Now, try as I might, I cannot get even one image to appear anywhere. they are all in a directory at profile\customizations\img\nodes\. I've tried the following:
  • customizations\img\nodes\library.bmp
  • \customizations\img\nodes\library.bmp
  • .\customizations\img\nodes\library.bmp
  • ..\customizations\img\nodes\library.bmp
  • ..\..\customizations\img\nodes\library.bmp
  • C:\<direct_path>\customizations\img\nodes\library.bmp

Which file is this linked relative to? They're all 16 x 16 .bmp files.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #353
I'm still having trouble getting my bitmaps to appear in the icon selection dialog. I had three of them entered in the Properties > Advanced > Tools > SQL Tree > Icon file paths (separate by |) box with the previous version of the component. They all had direct links, but only the first appeared.
I tried with your images using relative paths and even the first one didn't work for me. In general I need to check it, but you should combine all your images to one bitmap (see attachment). Using this it works for me.

I've tried the following:
  • customizations\img\nodes\library.bmp
  • \customizations\img\nodes\library.bmp
  • .\customizations\img\nodes\library.bmp
  • ..\customizations\img\nodes\library.bmp
  • ..\..\customizations\img\nodes\library.bmp
  • C:\<direct_path>\customizations\img\nodes\library.bmp

Which file is this linked relative to? They're all 16 x 16 .bmp files.
It's relative to the profile folder. Therefore the first one is the correct one. But the direct path (the last one) should also work.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #354
Ah yes, that works now. I thought I remembered from last time I tried to do this a while back that there was a certain way to set the file up (with all of the images in a row like that), but I couldn't find what it was.

Thanks. All better. ;D

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #355
I guess I spoke too soon, and I'm back to the pain on the node icons. It seems that when I change the Advanced > Tools > SQL Tree > Icon file paths setting, I have difficulty getting my icons to show up in the dialog for selecting icons afterwards. It worked the first time, and it eventually worked again when I used the file you made above, but I have no idea how that happened.

Now, I've swapped the file for another, and I was expecting my nodes to display the wrong ones, since the order changed. Instead, they displayed nothing. So I set them all back to using their parent node settings and cleared all of my changes out. I've restarted fb2k several times. Still no icons in the dialog.

I tried uninstalling SQLite Utilities and SQL Tree. I left the

I tried a different fb2k installation where I had put a value in the advances properties but not yet used any of the icons. I updated that, and that installation doesn't show any of my icons either.

I can't find how to reliably get my icons into the dialog. I anticipate more trial and error with them as I see what looks good and what doesn't. Help!

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #356
The problem with your icons is that they are 32bit bitmap files. Those are not supported. When I created the previously attached file with IrfanView it was automatically converted to a 24bit bitmap file and therefore it could be loaded. Converting your single icon files to 24bit makes them loadable too. However the preferred way is to use combined bitmap files.

The next release will show an error dialog containing all the files which not could be loaded.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #357
Ah, thanks for that explanation. Perhaps I can work with that.

I did try using again the file that you linked here and didn't have any success. Maybe I had something else wrong.

I noticed too that—while the icon numbers in the dialog are a 1-based sequence—the values in the database seem to start from zero. This doesn't affect anything, and I probably shouldn't go mucking about in the databases, but it's something to keep in mind when I do.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #358
OK, I got it working, but I have questions. I have a Python script that takes Material Design Icons svg files, colors them, and converts them to a merged bmp. This file is not able to be read, but opening it up in IrfanView shows the result to be 24 BPP, and going over to Image > Show Channel > has the Alpha channel greyed out. However, the size in the status bar reads "11.13 KB / 8.29 KB".

If I then use IrfanView to Save As this bmp to another, I get a working file with a size of "8.30 KB / 8.29 KB".

So I need to add another step to my script, but my output seems to be both 24 BPP and not 24 BPP, assuming those are the right 24-bit property.

How can I better inspect the difference between the two images and determine what accounts for it?

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #359
This file is not able to be read, but opening it up in IrfanView shows the result to be 24 BPP
IrfanView shows actually the result of the in memory image after converting it from 32bit in the status bar. Selecting the menu "Image -> Information..." gives you the information, that the original image is 32bit. And, of course, this explains also the size difference.

But why do you use a Python script for creating the final icon bitmap file. It appears a bit overcomplicated to me. This can be easily done with IrfanView by selecting the menu "Image -> Merge images (Simple Panorama image)..." (*)

(*) The files which are used in the attached screen shot are your 32bit BMP files, but it can also be done directly with the SVG files in the same way.


Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #360
Hey, these little scripts are the one coding thing I'm kinda getting good at. Don't take that away from me! ;D

It's the script that produced all of those bmp images in the first place. I saw a chance to learn more stuff and lock in all the coloring and resizing too. It's easier now that its done than hand-making the source files was before, especially with another color scheme for a different profile.

After casting about fruitlessly with ImageMagick SVG delegates and whatnot, I got an effective function to add at the end. ;D  :'(

Code: (python) [Select]
    def convert_again(self, file_name: str) -> None:
        """Clean up weird conversion from Wand for fb2k SQL Tree.

        The final output was not loading successfully in the foobar2000
        SQL Tree component, but reconverting with IrfanView did the
        trick. I don't know if Wand/ImageMagick is properly configured
        to use Inkscape as a delegate for SVG conversion.

        This does not protect paths containing spaces or maybe even
        capital letters.
        """
        subprocess.run([IRFANVIEW, file_name, f'/convert={file_name}'])

[EDIT:] Oh hang on, I got it to work a better way without my convert_again kludge. The key was changing the format of those intermediate files from bmp to png and then saving the merged file as a bmp and prepending the filename in that save with BMP3:. Omit any of those bits—and I have—and no dice.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #361
I'm having new trouble with the node images. In trying a new installation, I get the new dialog that the image could not be loaded. However, this is an image that works in another installation.

I made a copy of that installation, removed the file from the Advanced Preferences, and restarted fb2k. No images appeared in the nodes as expected. Then I entered the file back into the the Preferences and restarted again. This time, the dialog came up, and the previously-working image failed to load.

For good measure, I opened the image up in IrfanView and saved it as itself. That image didn't work either.

{Edit:] Another test. I had made the same image in two different color schemes for two different installations. With a fresh copy of one of those installations, I swapped out the bmp file with the differently-colored one without editing the Preferences. Opening fb2k, the new file was applied, and the colors had changed.


Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #362
I'm trying to make tree node with conditional sub-nodes based on the multi-value genre field.  For example:
Code: [Select]
Track #1 Genre - Soundtrack; Score; Anime; Electronic
Track #2 Genre - Soundtrack; Score; Anime; Rock
Track #3 Genre - Soundtrack; Score; Movie; Jazz
Track #4 Genre - Soundtrack; Stage & Screen; Soul; Funk
Track #5 Genre - Soundtrack; Stage & Screen; Rock; Progressive

Ideally, it should look like this:
Code: [Select]
Soundtracks
--Score
----Anime
------Electronic
------Rock
----Movie
------Jazz
--Stage & Screen
----Soul
----Funk
----Progressive
----Rock

This is the Query I tried and it works for the 1st level of sub-nodes, but both the Score and Stage & Screen nodes include ALL of the tracks with Soundtrack in the genre.  I didn't even get to trying to adding the 3rd and 4th levels of sub-nodes.

Code: [Select]
SELECT
CASE
WHEN genre_mv IN ('Score', 'Stage & Screen') THEN genre_mv
END Category
FROM MediaLibrary
WHERE genre LIKE 'Soundtrack%'
GROUP BY genre_mv

Also, I have a genre field without split values and I have a genre_mv field with split multivalue tag checked.

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

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #363
Using multivalue splitting is the wrong approach here as it duplicates the rows of the result which leads to having an own node for each value of the column.

Instead you should first define a function to convert the multivalue column to a JSON array in the SQLite console:
Code: [Select]
SELECT define('mv2json','''["''||replace(coalesce(:v,''''),'' · '',''","'')||''"]''');

This function is persistent and therefore needs to be created only once. In case you defined an own multivalue separator for your genre column you need to replace this
Code: [Select]
... '' · '' ...

by this
Code: [Select]
... ''<your multivalue separator>'' ...

Afterwards you can define your query like this:

Code: [Select]
WITH Genres AS (
  SELECT mv2json(genre) genres
  FROM MediaLibrary
)
SELECT
  genres->>0,
  genres->>1,
  genres->>2,
  ...
FROM Genres

Add as many columns as you need to cover the maximum numbers of values in your genre tag.

Finally, tick "Omit <null>" in the advanced tab.

I did not test it in detail, but it should work like this.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #364
fbuser, you literally replied while I was writing this new post; awkward timing, but deeply appreciated none-the-less.  I made a bunch of changes since my last one, but I'm going to read your reply and see what changes I should make.

Multi-Genre Examples:
Code: [Select]
Track #1 Genre - Soundtrack; Score; Anime; Electronic
Track #2 Genre - Soundtrack; Score; Anime; Rock
Track #3 Genre - Soundtrack; Score; Movie; Jazz
Track #4 Genre - Soundtrack; Stage & Screen; Soul; Funk
Track #5 Genre - Soundtrack; Stage & Screen; Rock; Progressive

Ideally, it should look like this:
Soundtracks
Code: [Select]
--Score
----Anime
------Electronic
------Rock
----Movie
------Jazz
--Stage & Screen
----Soul
----Funk
----Progressive
----Rock

The album sub-nodes would follow their respective genres.  Also, I have a genre field without split values and I have a genre_mv field with split multivalue tag checked.

If I use GROUP BY at the end of the query, it kind of looks like what I want (see screenshot) but I know I can't use GROUP BY because it will only return a single album for each genre_mv.

Batch:
Code: [Select]
DROP TABLE IF EXISTS tabletest;
CREATE TABLE tabletest AS
SELECT
       genre,
       genre_mv,
       album,
       discnumber,
       tracknumber,
       title,
       [original album],
       [album artist],
       path,
       subsong

FROM MediaLibrary
WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%'

ORDER BY genre_mv

Query:
Code: [Select]
SELECT
    CASE
        WHEN genre LIKE '%Score%' THEN 'Score'
        WHEN genre LIKE '%Stage & Screen%' THEN 'Stage & Screen'
        END AS Category1,
    CASE
        WHEN genre LIKE '%Score%' AND genre_mv IN ('Anime', 'Movie', 'Musical', 'Television', 'Video Game') THEN genre_mv
        WHEN genre LIKE '%Stage & Screen%' AND genre_mv NOT IN ('Soundtrack', 'Score', 'Stage & Screen') THEN genre_mv
        END AS Category2,
    album

FROM tabletest
GROUP BY Category1, Category2;

I still a have a lot to learn about SQL so thanks for any help!
Windows 10 | Foobar2000 v2.0 | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #365
Please ignore that last post.  Regarding the JSON Array:

I just want to make sure that I add a semi-colon to the Multivalue Separator when I edit the genre column in the MediaLibrary Table and I leave the Split multivalue tag unchecked (see screenshot)

To define the function, I change the separator to a semi-colon and execute it in an SQL Console window:
Code: [Select]
SELECT define('mv2json','''["''||replace(coalesce(:v,''''),'';'',''","'')||''"]''')
It seems I can only define it once.  If I change the name from "mv2json" then I can I can run it again, otherwise I get this error:
Code: [Select]
SQLite Error: (5) database is locked
If I want to change the name of the function to "multivaluegenre2jsonarray", how do I delete the old one?

Also, after I define the JSON array function and run the following query with Omit Null checked, it works great in SQLite console but when I click OK I get the following error:
Code: [Select]
SQLite Error: (1) no such function: mv2json
I made sure "mv2json" was entered correctly in the query and when I defined the function.

Query:
Code: [Select]
WITH Genres AS (
  SELECT mv2json(genre) genres
  FROM MediaLibrary
)

SELECT
  genres->>0 AS Genre1,
  genres->>1 AS Genre2,
  genres->>2 AS Genre3,
  genres->>3 AS Genre4

FROM Genres
ORDER BY Genre1, Genre2, Genre3, Genre4
Also, for unrelated reasons, Foobar2000 crashed a bunch of times while I was working on this.  I don't know if that contributed to the locked database problem.  Thanks for your help!
Windows 10 | Foobar2000 v2.0 | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6

 

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #366
It seems I can only define it once.
Yes, this is what I meant with "persistent".

If I change the name from "mv2json" then I can I can run it again
That's because you defined a new function with another name.

If I want to change the name of the function to "multivaluegenre2jsonarray", how do I delete the old one?
With
Code: [Select]
SELECT undefine('mv2json');

Also, after I define the JSON array function and run the following query with Omit Null checked, it works great in SQLite console but when I click OK I get the following error:
Code: [Select]
SQLite Error: (1) no such function: mv2json
This should not be the case. Is the function listed, when you execute the following command?
Code: [Select]
SELECT * FROM sqlean_define;
You might need to restart the application to get finally rid of it.

Query:
...
This query wouldn't work, even if you wouldn't get the "no such function" error. First because I missed to add path and subsong to the Genres CTE, second because of the "order by" that you added. Actually you would be able to create the tree, but clicking on a node would throw an error.

The make it work the query should be re-written like this:
Code: [Select]
SELECT
  Genre1,
  Genre2,
  Genre3,
  Genre4
FROM (
  WITH Genres AS (
    SELECT path,
           subsong,
           mv2json(genre) genres
    FROM MediaLibrary
  )
  SELECT
    path,
    subsong,
    genres->>0 AS Genre1,
    genres->>1 AS Genre2,
    genres->>2 AS Genre3,
    genres->>3 AS Genre4
  FROM Genres
)
ORDER BY Genre1, Genre2, Genre3, Genre4

Also, for unrelated reasons, Foobar2000 crashed a bunch of times while I was working on this.  I don't know if that contributed to the locked database problem.
Possibly. I already reduced the functionality of the define extension (see here.) because the internal default function handling was instable. However, after the adjustments I didn't face any issues anymore.