Bug 470017

Created attachment 159119 [details]
krita_manage-resources-id375.png

SUMMARY
I'll try to explain my case, but bug tracker is not easy to provide properly formatted information ^_^'
It's a long and technical explanation for a summary :)


I was playing with a copy of my resourcecache.sqlite file, trying to understand it.
Then I discover that I have duplicates resources.


Here's my query:
> SELECT r.id,
>        r.name,
>        r.md5sum,
>        s.id as "storageId",
>        s.location
> FROM resources r
>     -- need to reduce resources from ACTIVE storages only
>     JOIN storages s
>       ON s.id = r.storage_id
>      AND s.active = 1
>     -- reduce list to expected resource type
>     JOIN resource_types rty
>       ON r.resource_type_id = rty.id
>      AND rty.name = 'paintoppresets'
> ORDER BY r.name

Results: 
- I have the same resource from 2 different location
> +------------------------------------------------------------------+----------------------------------------------------------+
> | Resource                                                         | Storage                                                  |
> +------+------------------------+----------------------------------+----+-----------------------------------------------------+
> + Id   | Name                   | MD5 Sum                          | Id | Location                                            |
> +------+------------------------+----------------------------------+----+-----------------------------------------------------+
> | 375  | Essential Cloud Cloud4 | bf16d43e5dcf26f384dafa03ce9bcd56 | 7  | Cloud_Water_FIzzyflower_EssentialBrushesVer2.bundle |
> | 1208 | Essential Cloud Cloud4 | bf16d43e5dcf26f384dafa03ce9bcd56 | 13 | FIzzyflower_EssentialBrushesVer2_ALLBRUSHES.bundle  |
> +-------------------------------+----------------------------------+----------------------------------------------------------+

In Krita's brush preset docker, only one resource matching name "Essential Cloud Cloud4" is returned.
This resource is linked to 3 tags (2 provided trhough bundle I think, one added by me)

> SELECT r.id,
>         r.name,
>         group_concat(DISTINCT cast(t.id as text) || ':' || t.name)
> FROM resources r
>     -- need to reduce resources from ACTIVE storages only
>     JOIN storages s
>         ON s.id = r.storage_id
>         AND s.active = 1
>     -- reduce list to expected resrouce type
>     JOIN resource_types rty
>         ON r.resource_type_id = rty.id
>         AND rty.name = 'paintoppresets'
>     LEFT OUTER JOIN resource_tags rta
>         ON rta.active = 1
>         AND rta.resource_id = r.id
>     LEFT JOIN tags t
>         ON t.resource_type_id = r.resource_type_id
>         AND t.id = rta.tag_id
>         AND t.active = 1 | 
> WHERE r.id in (375, 1208)
> GROUP BY r.id,
>             r.name
> ORDER BY r.name

Results: 
- For the resource 375, the 3 tags are visible
- For the resource 1208, only 2 tags are visible
> +------+------------------------+------------------------------------------------------------------+
> + Id   | Name                   | Tags                                                             |
> +------+------------------------+------------------------------------------------------------------+
> | 375  | Essential Cloud Cloud4 | 11:Essential Cloud & Water,24:Essential ALL BRUSHES,37:test_gbln |
> | 1208 | Essential Cloud Cloud4 | 11:Essential Cloud & Water,24:Essential ALL BRUSHES              |
> +-------------------------------+------------------------------------------------------------------+

Then only one (probably the one visible in brush preset docker) have the tag added manually.

We can see in resource manager that only resource 375 is available:
- screenshot krita_manage-resources-id375.png (Cloud_Water_FIzzyflower_EssentialBrushesVer2.bundle)
- screenshot krita_manage-resources-id1208.png (FIzzyflower_EssentialBrushesVer2_ALLBRUSHES.bundle )

Now, as I see that Cloud_Water_FIzzyflower_EssentialBrushesVer2.bundle is a subset of FIzzyflower_EssentialBrushesVer2_ALLBRUSHES.bundle, I decide to deactivate it
- screenshot krita_manage-resources-libraries.png

So now, executing my 2 SQL queries, I have:
> +------------------------------------------------------------------+----------------------------------------------------------+
> | Resource                                                         | Storage                                                  |
> +------+------------------------+----------------------------------+----+-----------------------------------------------------+
> + Id   | Name                   | MD5 Sum                          | Id | Location                                            |
> +------+------------------------+----------------------------------+----+-----------------------------------------------------+
> | 1208 | Essential Cloud Cloud4 | bf16d43e5dcf26f384dafa03ce9bcd56 | 13 | FIzzyflower_EssentialBrushesVer2_ALLBRUSHES.bundle  |
> +-------------------------------+----------------------------------+----------------------------------------------------------+
> 
> +------+------------------------+------------------------------------------------------------------+
> + Id   | Name                   | Tags                                                             |
> +------+------------------------+------------------------------------------------------------------+
> | 1208 | Essential Cloud Cloud4 | 11:Essential Cloud & Water,24:Essential ALL BRUSHES              |
> +-------------------------------+------------------------------------------------------------------+


OBSERVED RESULT
1) I lost my tag on "Essential Cloud Cloud4"

2) In Krita, the resource #1208 is still unavailable 
   - "Essential Cloud Cloud4" is not visible in brush preset docker
   - "Essential Cloud Cloud4" is not visible in resource manager for bundle FIzzyflower_EssentialBrushesVer2_ALLBRUSHES.bundle
   - "Essential Cloud Cloud4" is still visible in resource manager for inactive bundle Cloud_Water_FIzzyflower_EssentialBrushesVer2.bundle
     -- no information about the fact bundle is inactive?
     -- no tags are linked anymore to brush preset in UI

     
EXPECTED RESULT
I'm not really sure how Krita should react in case of duplicates values but probably the best thing could be:
- Display only one resource in brush preset docker like now to avoid duplicates resources in UI
- Add/remove users tags on ALL duplicates resources (in my case, tag 'test_gbln' would be added on resources #375 and #1208)
- In resource manager, even if a value is duplicated in different bundles, it must be visible from selected bundle



ADDITIONAL INFORMATION
I didn't took a look everywhere in Krita's source code

Looking on how the request to get resources is built (for what I understand):
https://invent.kde.org/graphics/krita/-/blob/44fff38514b3511c53b68c95db378f3cfc732f77/libs/resources/KisResourceModel.cpp#L55-75

The query is quite weird to me.
I'm most used to work on Oracle databases than SQLite and a request like this one will generate an ORA-00979 exception because GROUP BY expression is not a valid GROUP BY expression.

Here's the result after the bundle is deactivated:
> +-----+------------+------------------------+----------------------------+------------------------+--------+----------------------------------+-----------------------------------------------------+----------------+-----------------+----------------+
> | id  | storage_id | name                   | filename                   | tooltip                | status | md5sum                           | location                                            | resource_type  | resource_active | storage_active |
> +-----+------------+------------------------+----------------------------+------------------------+--------+----------------------------------+-----------------------------------------------------+----------------+-----------------+----------------+
> | 375 | 7          | Essential Cloud Cloud4 | Essential Cloud Cloud4.kpp | Essential Cloud Cloud4 | 1      | bf16d43e5dcf26f384dafa03ce9bcd56 | Cloud_Water_FIzzyflower_EssentialBrushesVer2.bundle | paintoppresets | 1               | 0              |
> +-----+------------+------------------------+----------------------------+------------------------+--------+----------------------------------+-----------------------------------------------------+----------------+-----------------+----------------+

Here's the result if the bundle is activated:
> +-----+------------+------------------------+----------------------------+------------------------+--------+----------------------------------+-----------------------------------------------------+----------------+-----------------+----------------+
> | id  | storage_id | name                   | filename                   | tooltip                | status | md5sum                           | location                                            | resource_type  | resource_active | storage_active |
> +-----+------------+------------------------+----------------------------+------------------------+--------+----------------------------------+-----------------------------------------------------+----------------+-----------------+----------------+
> | 375 | 7          | Essential Cloud Cloud4 | Essential Cloud Cloud4.kpp | Essential Cloud Cloud4 | 1      | bf16d43e5dcf26f384dafa03ce9bcd56 | Cloud_Water_FIzzyflower_EssentialBrushesVer2.bundle | paintoppresets | 1               | 1              |
> +-----+------------+------------------------+----------------------------+------------------------+--------+----------------------------------+-----------------------------------------------------+----------------+-----------------+----------------+

In both case, with the current GROUP BY SQLite return an arbitrary (I can't tell anything else for now) row.
Why SQLite privilegiate a row from another one?
In Oracle the GROUP BY can't be applied because Oracle don't have any clue to determinate which 'id' from both rows to return.
It **seems** SQlite returns the first one, or the lower, or... but I'm pretty sure it not always guaranted to return the same value.

Removing the GROUP BY clause, I can see the both rows
> +-----+------------+------------------------+----------------------------+------------------------+--------+----------------------------------+-----------------------------------------------------+----------------+-----------------+----------------+
> | id  | storage_id | name                   | filename                   | tooltip                | status | md5sum                           | location                                            | resource_type  | resource_active | storage_active |
> +-----+------------+------------------------+----------------------------+------------------------+--------+----------------------------------+-----------------------------------------------------+----------------+-----------------+----------------+
> | 375 | 7          | Essential Cloud Cloud4 | Essential Cloud Cloud4.kpp | Essential Cloud Cloud4 | 1      | bf16d43e5dcf26f384dafa03ce9bcd56 | Cloud_Water_FIzzyflower_EssentialBrushesVer2.bundle | paintoppresets | 1               | 0              |
> | 1209| 13         | Essential Cloud Cloud4 | Essential Cloud Cloud4.kpp | Essential Cloud Cloud4 | 1      | bf16d43e5dcf26f384dafa03ce9bcd56 | FIzzyflower_EssentialBrushesVer2_ALLBRUSHES.bundle  | paintoppresets | 1               | 1              |
> +-----+------------+------------------------+----------------------------+------------------------+--------+----------------------------------+-----------------------------------------------------+----------------+-----------------+----------------+

Idea to return ONE value could be to privilegiate active storage first, and then lower (oldest I suppose) id.

But if this query is used both for brush preset docker AND resource manager, it can't work as ideally as proposed in EXPECTED RESULT:
- Display only one resource in brush preset docker like now to avoid duplicates resources in UI
- In resource manager, even if a value is duplicated in different bundles, it must be visible from selected bundle


For what I read, there's a Summer Google of Code student that will work on resource manager.
This case could be problematic in realization of UI and may be it can also be taken in account for being solved with rewritting of resource manager UI?

SOFTWARE/OS VERSIONS
Krita
 Version: 5.2.0-prealpha (git cdb36cb)

Qt
  Version (compiled): 5.15.7
  Version (loaded): 5.15.7

OS Information
  Build ABI: x86_64-little_endian-lp64
  Build CPU: x86_64
  CPU: x86_64
  Kernel Type: linux
  Kernel Version: 5.19.0-41-generic
  Pretty Productname: KDE neon 5.27
  Product Type: neon
  Product Version: 22.04
  Desktop: KDE

==> don't know which SQLite engine version is embedded with Krita, but here SQL Editor I used for my requests:
DB Browser for SQLite Version 3.12.1
  Qt Version 5.15.1
  SQLite Version 3.33.0.

Grum999