Skip to content

API.md

Latest commit

 

History

History
162 lines (132 loc) · 9.95 KB

API.md

File metadata and controls

162 lines (132 loc) · 9.95 KB
Raw

HTTP API

Endpoint and transport

The entry point is api/index.php. Use:

POST /api/index.php
Content-Type: application/json

The script advertises GET, POST, OPTIONS for CORS and returns 200 immediately for OPTIONS. It does not otherwise enforce the HTTP method, but POST is the supported client convention because every operation requires a JSON request body. Allowed browser origins are currently hard-coded to http://127.0.0.1:5173 and http://localhost:5173.

Request flow and actions

The body must be one JSON object. The required action is one of:

  • select
  • union, unionAll
  • procedure, function, tableFunction
  • metadata.tables, metadata.columns, metadata.views, metadata.procedures, metadata.schema

For SELECT, source.table and a non-empty fields array are also required. Refer to JSON-Request-Reference.md for every field and default.

{
  "action": "select",
  "source": { "table": "Items", "alias": "I" },
  "fields": ["I.ItemCode", { "field": "I.Description", "alias": "ItemName" }],
  "sort": [{ "field": "I.ItemCode", "direction": "ASC" }],
  "pagination": { "page": 1, "pageSize": 25 }
}

Unknown properties are rejected. Raw SQL, arbitrary SELECT parameters, client-supplied controller names, and internal query-builder keys are not part of the public contract.

Success response

All controllers use the same envelope:

{
  "success": true,
  "message": "Data Loaded Successfully",
  "data": [{ "ItemCode": "A001", "ItemName": "Example" }],
  "meta": {
    "page": 1,
    "pageSize": 25,
    "totalRows": 37,
    "rowsReturned": 1,
    "executionTime": 2.41
  }
}
  • data is always an array.
  • page and pageSize copy the public pagination request, or are null.
  • For paginated SELECT, totalRows is obtained with a separate count query. Otherwise it equals rowsReturned.
  • executionTime is elapsed database execution time in milliseconds, rounded to two decimals, or null if the underlying result did not supply it.
  • rowsReturned counts rows collected across the executed result.
  • Query results do not include a separate column-schema/column-metadata property. The metadata.columns action returns column rows as ordinary data.
  • SELECT/UNION messages are Data Loaded Successfully; routine actions use their corresponding executed-successfully message; metadata actions use their loaded-successfully message.

Error responses

Malformed JSON is HTTP 400:

{
  "success": false,
  "message": "Invalid JSON request.",
  "error": { "code": "INVALID_JSON", "details": [] },
  "data": []
}

Contract validation failures are HTTP 400 and include one or more path/message details:

{
  "success": false,
  "message": "Invalid request.",
  "error": {
    "code": "INVALID_REQUEST",
    "details": [
      { "path": "pagination.page", "message": "Must be a positive integer." }
    ]
  },
  "data": []
}

Unhandled builder, metadata, connection, or execution failures are HTTP 500:

{
  "success": false,
  "message": "Query execution failed.",
  "error": { "code": "QUERY_ERROR", "details": [] },
  "data": []
}

The response does not expose the underlying exception. The exception handler writes details to the dated file in logs/.

Pagination and ordering

pagination requires positive integer page and pageSize. SQL Server compatibility level 110+ uses OFFSET/FETCH; older compatibility levels use a ROW_NUMBER() wrapper. The backend runs a count query before the page query.

Public sorting uses validated logical fields or a selected alias and ASC/DESC; numeric positions such as "1" are rejected. Window functions likewise require a logical sort field. This prevents invalid SQL Server output such as ROW_NUMBER() OVER (ORDER BY 1). If top-level sort is omitted, the builder supplies an order based on the first usable projection (or table metadata when needed); grouped requests default to the first group field.

Capability matrix

Supported means the feature passes the public validator/normalizer and has a current builder/execution path. SQL Server capabilities that are not exposed remain unsupported by this API.

Feature Backend support Public JSON representation Validation Notes
SELECT Supported action: "select", source, fields Table/field identifier shape, then live metadata Read-only query action
DISTINCT Supported distinct: true Boolean Default false
TOP Supported limit: 10 Positive integer Normalizes to internal top
Column/table aliases Supported field alias; source.alias Identifier Selected aliases may be used by top-level sort
CASE Supported field object with case.when, optional else, alias Comparison conditions only CASE values are rendered as controlled literals
Arithmetic expressions Supported expression: {left, operator, right} Operands are numbers/identifiers; + - * / % One binary expression level in public shape
COUNT/SUM/AVG/MIN/MAX Supported field function, field, optional alias Function allow-list and metadata COUNT accepts *
STRING_AGG Supported plus separator, optional sort Aggregate/function options checked by builder SQL Server syntax
String functions Supported function field object Allow-list UPPER, LOWER, LTRIM, RTRIM, TRIM, LEN, CONCAT, LEFT, RIGHT, SUBSTRING, REPLACE, CHARINDEX, PATINDEX, FORMAT
Date/time functions Supported, except TIMEFROMPARTS function field object Allow-list plus builder-required options YEAR, MONTH, DAY convert integer YYYYMMDD values using style 112; see JSON reference
Math functions Supported function field object Allow-list ABS, ROUND, CEILING, FLOOR, POWER, SQRT, EXP, LOG
Conditional functions Supported IIF, CHOOSE field objects Allow-list; builder validates required options CASE is also supported
CAST/CONVERT Supported datatype, optional CONVERT style Datatype pattern allow-list No free-form SQL datatype expression
NULL functions Supported COALESCE/ISNULL/NULLIF field objects Function allow-list; builder-required options COALESCE public fields are identifiers
WHERE comparisons Supported filters[] = != <> > < >= <= Values use prepared placeholders
LIKE/NOT LIKE Supported filters[] Operator allow-list Pattern is a prepared value
IN/NOT IN Supported array value or query Non-empty array or valid nested SELECT Prepared list values
BETWEEN/NOT BETWEEN Supported two-element value Exactly two values Integer date columns convert YYYY-MM-DD to YYYYMMDD
IS NULL/IS NOT NULL Supported filter without value Operator allow-list No placeholder
EXISTS/NOT EXISTS Supported filter query, no field required Nested SELECT required Filter subquery only
INNER/LEFT/RIGHT JOIN Supported joins[] Valid source, logical left/right, equality only One on equality per join
FULL/CROSS JOIN Not supported None Rejected join type Not exposed
GROUP BY Supported groupBy[] Identifier plus metadata Array of fields
HAVING Supported having[] Aggregate + comparison + value Conditions are combined with AND
ORDER BY Supported sort[] Logical field/alias and ASC/DESC Multiple fields supported; direction defaults ASC
Positional ORDER BY Internal compatibility only None Numeric public sort fields rejected Internal positions are resolved to real fields in window contexts
Pagination Supported pagination.page/pageSize Both positive integers Count + OFFSET/FETCH or ROW_NUMBER fallback
Window functions Supported field function plus sort Function allow-list and mandatory sort ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE
Window PARTITION BY Not supported None partitionBy rejected Only window ORDER BY is exposed
Filter subqueries Supported IN/NOT IN/EXISTS/NOT EXISTS query Nested SELECT validation Subqueries are not general field/table expressions
CTE Supported with: {name, query} One named SELECT body One CTE per request
Recursive CTE Supported with: {name, anchor, recursive} Both SELECT bodies required Builder combines branches with UNION ALL
UNION/UNION ALL Supported top-level action plus queries At least one SELECT body Branch actions are omitted
INTERSECT/EXCEPT Internal builder only None Public action rejected Not a public API feature
Stored procedure Supported procedure action, source.procedure, parameters Identifier and array parameters Positional prepared parameters
Scalar function Supported function action, source.function, parameters Identifier and array parameters Returns Result column
Table-valued function Supported tableFunction action Identifier and array parameters Executes SELECT * FROM function(...)
SELECT parameters Values only filter/HAVING values Prepared by builders No arbitrary public parameters on SELECT
Metadata Supported five metadata.* actions Action allow-list; columns requires source table Database-backed
Column description metadata Not supported in query envelope None N/A Use metadata.columns separately
Validation/error envelope Supported N/A Unknown properties and invalid shapes rejected 400 contract errors; generic 500 query errors

Known contract boundary: TIMEFROMPARTS is named in the function allow-list and exists in the internal builder, but its required fractions property is not accepted by the public field-property allow-list. It is therefore not a usable public feature and is not shown as a supported example.

The source validator technically accepts source.alias for routines and metadata.columns; normalization ignores that alias, so it has no public effect. Routine parameters should be a JSON list because placeholders are positional.