Improve performance of listTemplates API - #13566

Took the help of AI in testing
cc @Pearl1594 @nvazquez

shell scripts

whichpath-pr13566.sh
compare-pr13566.sh
setup-pr13566.sh

Test results for template.list.bypass.view

Tested on a KVM environment with the PR build: 2 zones, about 118 templates and 8 ISOs.

Test data. Owned by root admin, a domain admin, users in a child domain and a user in an unrelated domain, plus a project (owner, member and non-member). The templates include:

• public/featured, private, cross-zone and multi-zone
• direct-download
• one in an error state (failed download)
• one with tags
• one deleted
• one shared with another account
• bootable and non-bootable ISOs
• direct-download bulk templates to give paging something to work on

Method. listTemplates/listIsos calls covering these filters: all, executable, self, selfexecutable, featured, community, shared and sharedexecutable. They also cover zoneid, isready, keyword, name, hypervisor, id/ids, paging, showunique, templatetype, bootable, ispublic, projectid=<id>/-1, tags and showremoved.

Each call ran once with the flag set to false and once set to true,

I compared count plus id, name, zoneid, isready and projectid for every row.

I used the MySQL general log to check which query actually ran: AS distinct_key means the new SQL, and SELECT DISTINCT temp_zone_pair / template_view.id FROM template_view means the old view.

Routing works as intended. With the flag on, all (admin), executable, self/selfexecutable, ISOs and project-scoped calls use the new SQL. featured, community, shared, sharedexecutable, tags, showremoved and non-admin all with projectid=-1 stay on the view.

Results match in 51 of 53 cases. The two mismatches are below.

1. templatetype (system and probably isvnf / forcks) is ignored on the new path

cmk list templates templatefilter=all templatetype=SYSTEM
  flag=false -> count 2
  flag=true  -> count 118   (every template)

templateChecks() adds templateType, isVnf and forCks to the SearchCriteria, but buildTemplateListFilter() doesn't take them, and TemplateListFilter has no fields for them. The new SQL drops these filters without any error. I didn't test isvnf or forcks, but reading the code they hit the same gap. The UI uses both: isvnf for the VNF template lists and forcks for CKS node templates.

Suggested fix: add these fields to TemplateListFilter and appendCommonWhere() (vt.type = ?, vt.type = 'VNF' / != 'VNF', vt.for_cks = ?). Or, at minimum, make canBypass() return false when any of them is set.

2. showunique=true with paging returns different templates on a page

cmk list templates templatefilter=all page=2 pagesize=10 showunique=true

The count is the same, but page 2 has different templates. With the flag on, ptest-domadmin-public and ptest-user1-private appear on page 2. They don't appear there with the flag off.

The view path always sorts by sort_key, temp_zone_pair, which is the "<id>_<zone>" string. With showUnique, the new SQL sorts by sort_key, distinct_key, where distinct_key is the numeric vt.id. When sort_key ties (all templates here have 0), the two orders differ ("10_1" < "9_1" but 9 < 10), so the page boundaries move. Without showunique, both paths sort by the same string, and paging matched (I checked pages 1 and 4).

Suggested fix: always use CONCAT(vt.id, '_', IFNULL(dc.id, 0)) as the second sort column, even when the SELECT key is vt.id.

3. Possible gap: EXTERNAL format with onlyReady (from reading the code, not tested)

The view path treats ImageFormat.BAREMETAL and ImageFormat.EXTERNAL as ready:

readySc.addOr("format", SearchCriteria.Op.IN, ImageFormat.BAREMETAL, ImageFormat.EXTERNAL);

The new SQL only has vt.format = 'BAREMETAL'. With the flag on, EXTERNAL-format templates (Extensions) would likely disappear from isready=true / executable lists.

4. Description and comments are out of date: domain-admin self

The description says self/selfexecutable for DOMAIN_ADMIN/RESOURCE_DOMAIN_ADMIN falls back to the view. In practice they run on the new SQL: domainPathLike is set without requiresViewFallback, canBypass() doesn't check it, and the SQL handles domain.path LIKE ?. Results matched in my tests, so the behaviour looks correct. The PR description, the Javadoc on buildTemplateListFilter / findDistinctTempZonePairs, and the IllegalArgumentException message (which lists domainPath as unsupported) should be updated to match.

Suggested test additions. Add unit tests covering templatetype, isvnf, forcks and EXTERNAL + onlyReady on the new path, and one that pages through showunique=true and checks that the IDs on each page match the view path.