[jira] [Created] (CONNECTORS-1789) Add sort column validation and canonical mapping for repository connection queue and history reports

["Piergiorgio Lucidi (Jira)" <ji...@apache.org>]

Piergiorgio Lucidi created CONNECTORS-1789:
----------------------------------------------

             Summary: Add sort column validation and canonical mapping for repository connection queue and history reports
                 Key: CONNECTORS-1789
                 URL: https://issues.apache.org/jira/browse/CONNECTORS-1789
             Project: ManifoldCF
          Issue Type: Improvement
          Components: Framework core
    Affects Versions: ManifoldCF 2.31
            Reporter: Piergiorgio Lucidi
            Assignee: Piergiorgio Lucidi
             Fix For: ManifoldCF 2.32


 
h2. Description
h3. Background

The ManifoldCF JSON API provides endpoints for retrieving queue and history reports for repository connections:
 * {{repositoryconnectionqueue/<encoded_connection_name>}}
 * {{repositoryconnectionhistory/<encoded_connection_name>}}

Clients can customize result ordering via the {{sortcolumn}} and {{sortcolumn_direction}} query parameters. Previously, caller-provided sort columns were accepted without validation against the schema of the requested report and passed directly through {{SortOrder}} into query construction in {{JobManager}} and {{{}RepositoryHistoryManager{}}}.

If a client supplied an unrecognized, misspelled, or unsupported column name, the request would propagate down to the database layer, potentially causing database-specific query syntax errors (HTTP 500 Internal Server Error) or unpredictable sorting behavior rather than a clean, predictable client response.
h3. Improvements Implemented

A comprehensive validation and canonical mapping mechanism has been introduced across the API, data model, and database access layers:
h4. 1. API-Level Schema Validation ({{{}ManifoldCF.java{}}})
 * The API methods {{apiReadRepositoryConnectionQueue}} and {{apiReadRepositoryConnectionHistory}} now resolve the {{report}} type prior to parsing sort criteria.
 * Each {{sortcolumn}} is validated against an explicit schema matching the documented return columns for the specific report type:
 ** *Queue Document Report:* {{{}identifier{}}}, {{{}job{}}}, {{{}state{}}}, {{{}status{}}}, {{{}scheduled{}}}, {{{}action{}}}, {{{}retrycount{}}}, {{{}retrylimit{}}}, {{id}}
 ** *Queue Status Report:* {{{}idbucket{}}}, {{{}inactive{}}}, {{{}processing{}}}, {{{}expiring{}}}, {{{}deleting{}}}, {{{}processready{}}}, {{{}expireready{}}}, {{{}processwaiting{}}}, {{{}expirewaiting{}}}, {{{}waitingforever{}}}, {{hopcountexceeded}}
 ** *History Simple Report:* {{{}starttime{}}}, {{{}resultcode{}}}, {{{}resultdesc{}}}, {{{}identifier{}}}, {{{}activity{}}}, {{{}bytes{}}}, {{{}elapsedtime{}}}, {{{}id{}}}, {{entityid}}
 ** *History Max Activity Report:* {{{}starttime{}}}, {{{}endtime{}}}, {{{}activitycount{}}}, {{idbucket}}
 ** *History Max Bandwidth Report:* {{{}starttime{}}}, {{{}endtime{}}}, {{{}bytecount{}}}, {{idbucket}}
 ** *History Result Codes Report:* {{{}idbucket{}}}, {{{}resultcodebucket{}}}, {{eventcount}}
 * Specifying an unknown or unsupported sort column now immediately raises a {{ManifoldCFException}} ("Unknown sortcolumn '...' for report '...'"), resulting in a clear HTTP 400 Bad Request error response.

h4. 2. Data Model Identifier Validation ({{{}SortOrder.java{}}})
 * Added identifier syntax enforcement ({{{}^[a-zA-Z_][a-zA-Z0-9_]*${}}}) in {{{}SortSpec(String, int){}}}, the serialization parser {{{}SortSpec(ParseBuffer){}}}, and UI column click navigation {{{}clickColumn(String){}}}.
 * Ensures only syntactically valid column identifiers can be represented within {{SortOrder}} objects.

h4. 3. Database Layer Canonical Column Mapping ({{{}JobManager.java{}}} & {{{}RepositoryHistoryManager.java{}}})
 * Defined canonical field lists for each report type.
 * Updated {{addOrdering(StringBuilder sb, String[] validFieldList, String[] completeFieldList, SortOrder sort)}} to perform case-insensitive validation against {{validFieldList}} and append the canonical column name to the {{ORDER BY}} clause.
 * Unknown column names encounter a fast-fail {{ManifoldCFException}} before query execution.
 * Preserved the previous 3-argument {{addOrdering}} signature as deprecated to maintain backwards compatibility for existing callers.

----
h2. Verification & Testing
 * *Unit Testing ({{{}SortOrderTest{}}}):*
 ** Verified serialization and deserialization round-tripping of sort criteria.
 ** Verified valid and invalid column identifier handling.
 ** Verified API parameter validation and error messages across all queue and history report types.
 * *Database Integration Testing ({{{}SanityHSQLDBTest{}}}):*
 ** Verified successful database query execution in {{genDocumentStatus}} and {{genHistorySimple}} with valid sort columns.
 ** Verified expected exception handling for unsupported sort columns.
 * Updated {{framework/build.xml}} to include {{SortOrderTest}} within the Ant {{run-pull-agent-tests}} target.
 * Full test suite in {{framework/pull-agent}} executed and passed.
 * License compliance verified with {{{}mvn apache-rat:check{}}}.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)