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 143374 times) previous topic - next topic
0 Members and 1 Guest 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 Portable | 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 Portable | 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 Portable | 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.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #367
Is there a way to edit the queries outside foobar? I want to uncheck the checkbox and set 2000 in these settings for all my queries.





Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #368
Yes, it is possible and also inside foobar2000, but not really recommended.

Especially as the desired changes at least the one for the node limit doesn't appear reasonable to me. As long as you don't have many hundreds of nodes the default of 100,000 should not cause any problems. With such a low limit you only increase the chance, that a query fails depending on your library size. Furthermore there is also an overall node limit in the advanced properties (default 1,000,000) which prevents too much memory usage.

Anyway, you can change it by executing the following code in the SQLite console at your own risk:

Code: [Select]
ATTACH fb2k_profile_path()||'\configuration\foo_uie_sql_tree.db' AS sqltree;

UPDATE ust_TreeItem
SET tri_node_limit=2000,
    tri_include_refresh_all=0
WHERE tri_node_type=3
  AND tri_sql_query<>'';

Afterwards you need to restart the application.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #369
Afterwards you need to restart the application.
Thanks very much. I edited the .db file in SQLiteStudio.

I have an issue. The program crashes if I refresh all nodes. Here's my db file for you to try. I'm using the last v2.2 preview of foobar2000.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #370
In the table ust_treeitem, the color values are stored as one number. How are those numbers calculated? I'd like to do those calculations for new colors to update the table with.

For instance, my background of rgb(25, 25, 35) is stored in tri_color_back as 2300185.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #371
color values are stored as one number. How are those numbers calculated?
Red + Green×256 + Blue×256².

Your example color for example is 25 + 25*256 + 35*65536 = 2300185.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #372
Red + Green×256 + Blue×256².

Your example color for example is 25 + 25*256 + 35*65536 = 2300185.

Ah, gotcha, thanks. I tried some online converter and got the wrong number, not really knowing my big end from my little.

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #373
The Imcomplete Albums gives incorrect result as album with multiple discs are grouped.
I found this work for me:

SELECT "%album artist%"||' - '||album "Artist - Album",
       date,
       discnumber,
       count(*) count,
       max(totaltracks) total_tracks
FROM MediaLibrary
GROUP BY 1,2,3
HAVING count(*)<>CAST(max(totaltracks) AS INTEGER)
ORDER BY "%album artist%", discnumber

Re: [fb2k v2] SQL Tree (foo_uie_sql_tree)

Reply #374
Thanks for the tips and explanations back in Reply #366.  Here's the latest version of my Soundtrack SQL Tree and I would really appreciate any advice how to make it simpler or faster.  I'm new to SQL so please forgive any embarrassing mistakes.   O:)

Here's some background about what I'm trying to do.  MediaMonkey is my main player / library manager and I rely heavily on the MagicNodes plugin which uses SQL queries to build tree nodes and sub-nodes; I'm sure that sounds familiar.   :))

But, MagicNodes can't generate sections / folders with its queries, as far as I can tell.  What I mean by section is using the AS statement to name a section of sub-nodes.  I also wanted to add visual dividers like "---------".

Here's a reminder of the what my genres look like:
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

Please see the screenshot to see the results of the query.  Everything above the dividers are the Sections.  I think it came out great!  But, I'm sure there's room for improvements.  For example, I created a table from MediaLibrary called MediaTable so I could insert the dividers.  Then I created SectionTable from MediaTable to create the sections and their sub-nodes.  But, I think I read in this thread that nested tables will slow the query.  Also, I'm not sure if I should be using the Batch section to create the tables.  I'm using the UNION ALL statements to make sure that a track that qualifies for the Artists section and the Series section will appear under both.  Here it is...

BATCH:
Code: [Select]
----------------------------------------------------- Create MediaTable From MediaLibrary

DROP TABLE IF EXISTS MediaTable;
CREATE TABLE MediaTable AS
  SELECT

    path,
    subsong,
    genre,
    genre_mv,
    multivaluegenre2jsonarray(genre) genresplit,
    grouping,
    [original album],
    [original title],
    [album artist],
    album,
    discnumber,
    tracknumber

  FROM MediaLibrary
  WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%'
  ORDER BY album, discnumber, tracknumber;

----------------------------------------------------- Insert Dummy Dividers Into MediaTable

INSERT INTO MediaTable(genre, genresplit, album)
VALUES

--('Soundtrack;Score;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;---------------')),'---------------'), --Only for Genres Section
('Soundtrack;Score;Anime;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Anime;---------------')),'---------------'),
('Soundtrack;Score;Movie;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Movie;---------------')),'---------------'),
('Soundtrack;Score;Musical;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Musical;---------------')),'---------------'),
('Soundtrack;Score;Television;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Television;---------------')),'---------------'),
('Soundtrack;Score;Video Game;---------------',(multivaluegenre2jsonarray('Soundtrack;Score;Video Game;---------------')),'---------------'),
('Soundtrack;Stage & Screen;---------------',(multivaluegenre2jsonarray('Soundtrack;Stage & Screen;---------------')),'---------------');

----------------------------------------------------- Create SectionsTable From MediaTable

DROP TABLE IF EXISTS SectionsTable;
CREATE TABLE SectionsTable AS

----------------------------------------------------- Artists Section

SELECT DISTINCT
  genresplit->>1 AS Category1,

  CASE
    WHEN genresplit LIKE '%Score%' THEN genresplit->>2
    WHEN genresplit LIKE '%Stage & Screen%' THEN 'Artists'
    END AS Category2,

  CASE
    WHEN genresplit LIKE '%Score%' THEN 'Artists'
    WHEN genresplit LIKE '%Stage & Screen%' THEN [album artist]
    END AS Category3,

  CASE
    WHEN genresplit LIKE '%Score%' THEN [album artist]
    WHEN genresplit LIKE '%Stage & Screen%' THEN album
    END AS Category4,

  CASE
    WHEN genresplit LIKE '%Score%' THEN album
    END AS Category5,

   path,
   subsong,
   genre,
   genresplit,
   grouping,
   [original album],
   [original title],
   [album artist],
   album,
   discnumber,
   tracknumber

FROM MediaTable
WHERE [album artist] IS NOT NULL AND [album artist] <> 'Various Artists'

----------------------------------------------------- Universes/Series Section

UNION ALL

SELECT DISTINCT
  genresplit->>1 AS Category1,

  CASE
    WHEN genresplit LIKE '%Score%' THEN genresplit->>2
    WHEN genresplit LIKE '%Stage & Screen%' AND [original album] IS NOT NULL THEN 'Series'
    WHEN genresplit LIKE '%Stage & Screen%' AND [ORIGINAL TITLE] IS NOT NULL THEN 'Series'
    END AS Category2,

  CASE
    WHEN genresplit LIKE '%Score%' THEN 'Universe'
    WHEN genresplit LIKE '%Stage & Screen%' AND [original album] IS NOT NULL THEN [original album]
    WHEN genresplit LIKE '%Stage & Screen%' AND [ORIGINAL TITLE] IS NOT NULL THEN [ORIGINAL TITLE]
    END AS Category3,

  CASE
    WHEN genresplit LIKE '%Score%' AND [original album] IS NOT NULL THEN [original album]
    WHEN genresplit LIKE '%Score%' AND [ORIGINAL TITLE] IS NOT NULL THEN [ORIGINAL TITLE]
    WHEN genresplit LIKE '%Stage & Screen%' THEN album
    END AS Category4,

  CASE
    WHEN genresplit LIKE '%Score%' THEN album
    END AS Category5,

    path,
    subsong,
    genre,
    genresplit,
    grouping,
    [original album],
    [original title],
    [album artist],
    album,
    discnumber,
    tracknumber

FROM MediaTable
WHERE [original album] IS NOT NULL OR [ORIGINAL TITLE] IS NOT NULL

----------------------------------------------------- Everything Else

UNION ALL

SELECT DISTINCT

genresplit->>1 AS Category1,

CASE
  WHEN genre <> 'Soundtrack;Score;---------------' THEN genresplit->>2
  END AS Category2,

CASE
  WHEN genresplit LIKE '%Score%' THEN album
  WHEN genresplit LIKE '%Stage & Screen%' THEN album
  END AS Category3,

NULL AS Category4,
NULL AS Category5,

path,
subsong,
genre,
genresplit,
grouping,
[original album],
[original title],
[album artist],
album,
discnumber,
tracknumber

FROM MediaTable
WHERE ([album artist] IS NULL  OR [album artist] = 'Various Artists') AND [original album] IS NULL AND [ORIGINAL TITLE] IS NULL;

QUERY:
Code: [Select]
SELECT
Category1,
Category2,
Category3,
Category4,
Category5

FROM SectionsTable
ORDER BY
Category1,

CASE
  WHEN Category2 = 'Artists' THEN 0
  WHEN Category2 = 'Genres' THEN 1
  WHEN Category2 = 'Series' THEN 2
  WHEN Category2 = 'Universe' THEN 2
  WHEN Category2 = '---------------' THEN 3
  ELSE Category2
  END,

CASE
  WHEN Category3 = 'Artists' THEN 0
  WHEN Category3 = 'Genres' THEN 1
  WHEN Category3 = 'Series' THEN 2
  WHEN Category3 = 'Universe' THEN 2
  WHEN Category3 = '---------------' THEN 3
  ELSE Category3
  END,

Category4,
Category5
Windows 10 | Foobar2000 v2.0 Portable | Columns 2.1.0 | SQLite Utilities 3.0.4 | SQLite Tree 4.0.6