fix: handle NULL num_rows and empty aggregates in Oracle smart query - #1145

Problem

The Oracle smart query emits JSON that is syntactically valid but fails ChartDB's own import validation (DatabaseMetadataSchema), so the user only sees the generic "Invalid JSON. Please correct it or contact us at support@chartdb.io" error with nothing to act on.

Two independent causes, both hit by ordinary schemas:

1. Tables without gathered statistics — all_tables.num_rows is NULL until statistics are gathered, so the query emits "rows": null. TableInfoSchema declares rows: z.number().optional(), and .optional() accepts undefined, not null.

2. Empty aggregates — JSON_ARRAYAGG over zero rows returns NULL, not an empty array, so a schema with no views emits "views": null. DatabaseMetadataSchema requires z.array(...) for all six aggregates, so the same applies to fk_info, pk_info, columns, indexes and tables.

Originally hit on a real Oracle schema of 226 tables, 44 of which had no gathered statistics, and which contained no views.

Fix

• NVL(num_rows, 0) for the per-table row count
• NVL(<aggregate>, TO_CLOB('[]')) FORMAT JSON for all six top-level aggregates

FORMAT JSON is required on the wrapped aggregates: NVL returns a plain CLOB and strips Oracle's internal JSON typing, so without it the array is emitted as an escaped string instead of a nested array. The same idiom is already used elsewhere in this script for the nullable and unique boolean keys.

Verification

Reproduced and verified against Oracle Database 26ai Free 23.26.2.0.0 (gvenzl/oracle-free), on a schema with two freshly created tables (statistics never gathered, num_rows NULL) and no views.

Before — parses as JSON, fails validation:

rows=null  views=null

DatabaseMetadataSchema.safeParse: FAIL
  - tables.0.rows: Expected number, received null
  - tables.1.rows: Expected number, received null
  - views: Expected array, received null

After:

rows=0  views=[]

DatabaseMetadataSchema.safeParse: PASS

Why FORMAT JSON is needed — same patch with FORMAT JSON removed from the six aggregates:

{"fk_info":"[{\"schema\":\"CHARTDB\",\"table\":\"ORDERS\", ...

typeof fk_info: string

DatabaseMetadataSchema.safeParse: FAIL
  - fk_info: Expected array, received string
  - pk_info: Expected array, received string
  - columns: Expected array, received string
  - indexes: Expected array, received string

So dropping FORMAT JSON would trade one validation failure for a worse one across every aggregate.

No behaviour change when nothing is null — after gathering statistics and adding a view to the same schema, the patched and unpatched queries produce byte-identical output:

patched === unpatched on populated schema: true
rows=2  views=[{"schema":"CHARTDB","view_name":"ACTIVE_USERS","view_definition":""}]
safeParse: PASS

npm run lint, tsc --noEmit, prettier --check and npm test (833 tests) all pass.