Add SYS.USER_CONS_COLUMNS view and test case by 3296442201 · Pull Request #1289 · IvorySQL/IvorySQL

3296442201 · 2026-04-10T12:29:15Z

Summary by CodeRabbit

New Features
- Added SYS.USER_CONS_COLUMNS view exposing constraint-to-column metadata (owner, table, constraint, column, position) for user-owned objects; view is queryable by users.
Tests
- Added smoke tests covering creation of a sample table with constraints, queries against the new view, and cleanup to validate expected outputs.

coderabbitai · 2026-04-10T12:29:35Z

No actionable comments were generated in the recent review. 🎉

ℹ️ Recent review info

⚙️ Run configuration

Configuration used: Path: .coderabbit.yaml

Review profile: CHILL

Plan: Pro

Run ID: 42abe60c-f222-426f-a702-7367112f5f20

📥 Commits

Reviewing files that changed from the base of the PR and between ac98e24 and 0b66659.

📒 Files selected for processing (3)

contrib/ivorysql_ora/expected/ora_sysview.out
contrib/ivorysql_ora/sql/ora_sysview.sql
contrib/ivorysql_ora/src/sysview/sysview--1.0.sql

🚧 Files skipped from review as they are similar to previous changes (3)

contrib/ivorysql_ora/expected/ora_sysview.out
contrib/ivorysql_ora/src/sysview/sysview--1.0.sql
contrib/ivorysql_ora/sql/ora_sysview.sql

📝 Walkthrough

Walkthrough

Adds a new Oracle-compatible view SYS.USER_CONS_COLUMNS exposing constraint-to-column mappings for the current user, plus SQL smoke-tests and updated expected output validating queries against the new view.

Changes

Cohort / File(s)	Summary
View Definition `contrib/ivorysql_ora/src/sysview/sysview--1.0.sql`	Creates `CREATE OR REPLACE VIEW SYS.USER_CONS_COLUMNS (OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION)` joining `pg_constraint`, `pg_class`, and `pg_attribute`; derives `POSITION` via `array_position`; filters out system schemas and limits to current user's objects; grants `SELECT` to `PUBLIC`.
Tests / Expected Output `contrib/ivorysql_ora/sql/ora_sysview.sql`, `contrib/ivorysql_ora/expected/ora_sysview.out`	Adds a smoke-test that creates `TEST_USER_CONS_COLUMNS` with `PK_TEST` and `UQ_EMAIL`, runs three queries against `SYS.USER_CONS_COLUMNS` (filters: `LIKE '%NOT_NULL%'`, `CONSTRAINT_NAME = 'PK_TEST'` ordered by `POSITION`, and `CONSTRAINT_NAME = 'UQ_EMAIL'`), and appends expected output; includes cleanup `DROP TABLE IF EXISTS TEST_USER_CONS_COLUMNS;`.

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~20 minutes

Poem

🐰 I hopped through catalog rows tonight,
Found constraints tucked out of sight,
Columns named and positions neat,
A tiny view makes metadata sweet,
Tests pass — I twirl my whiskers with delight!

🚥 Pre-merge checks | ✅ 3

✅ Passed checks (3 passed)

Check name	Status	Explanation
Description Check	✅ Passed	Check skipped - CodeRabbit’s high-level summary is enabled.
Title check	✅ Passed	The title accurately describes the main change: adding a new SYS.USER_CONS_COLUMNS view and corresponding test case, which matches all three file modifications.
Docstring Coverage	✅ Passed	No functions found in the changed files to evaluate docstring coverage. Skipping docstring coverage check.

_{✏️ Tip: You can configure your own custom pre-merge checks in the settings.}

✨ Finishing Touches

🧪 Generate unit tests (beta)

Create PR with unit tests

Warning

There were issues while running some tools. Please review the errors and either fix the tool's configuration or disable the tool if it's a critical failure.

🔧 SQLFluff (4.1.0)

contrib/ivorysql_ora/sql/ora_sysview.sql

User Error: Specified path does not exist. Check it/they exist(s): contrib/ivorysql_ora/sql/ora_sysview.sql.

contrib/ivorysql_ora/src/sysview/sysview--1.0.sql

User Error: Specified path does not exist. Check it/they exist(s): contrib/ivorysql_ora/src/sysview/sysview--1.0.sql.

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

coderabbitai

Actionable comments posted: 2

🧹 Nitpick comments (1)

contrib/ivorysql_ora/sql/ora_sysview.sql (1)
94-106: Broaden this test to cover multi-column constraint mappings.

This smoke test validates the happy path, but it does not exercise the conkey expansion behavior for multi-column constraints (or non-PK constraints beyond NOT NULL). Add at least one composite constraint case and assert one row per constrained column.

As per coding guidelines, **/sql/*.sql: Test SQL files. Ensure comprehensive coverage of features.
🤖 Prompt for AI Agents
Verify each finding against the current code and only fix it if needed.

In `@contrib/ivorysql_ora/sql/ora_sysview.sql` around lines 94 - 106, The test
only covers a single-column NOT NULL constraint; extend TEST_USER_CONS_COLUMNS
to include at least one composite constraint (e.g., CONSTRAINT <name> UNIQUE
(col1, col2) or a composite PRIMARY KEY) and corresponding additional columns so
that conkey expansion is exercised; then run the SELECT from
SYS.USER_CONS_COLUMNS and assert that it returns one row per constrained column
(i.e., one row for each column in the composite constraint) by ordering on
CONSTRAINT_NAME, COLUMN_NAME as before; update the CREATE TABLE and any
INSERT/cleanup statements to add the new columns/constraint and ensure the DROP
TABLE remains at the end.

🤖 Prompt for all review comments with AI agents

Verify each finding against the current code and only fix it if needed.

Inline comments:
In `@contrib/ivorysql_ora/expected/ora_sysview.out`:
- Around line 188-190: Reorder the three expected rows so they match the query's
ORDER BY CONSTRAINT_NAME, COLUMN_NAME: place both
TEST_USER_CONS_COLUMNS_NOT_NULL rows before TEST_USER_CONS_COLUMNS_PKEY, and
within TEST_USER_CONS_COLUMNS_NOT_NULL sort by COLUMN_NAME (ID then NAME) so the
final sequence is NOT_NULL/ID, NOT_NULL/NAME, then PKEY/ID; update the three
lines in ora_sysview.out accordingly.

In `@contrib/ivorysql_ora/src/sysview/sysview--1.0.sql`:
- Around line 1277-1298: Add an explicit permission grant so non-owner sessions
can query the new view: after the CREATE OR REPLACE VIEW SYS.USER_CONS_COLUMNS
(the view built from PG_CONSTRAINT / PG_CLASS / PG_ATTRIBUTE), issue a GRANT
SELECT on SYS.USER_CONS_COLUMNS to the appropriate role (e.g., PUBLIC or the
intended user role) so the view is readable by non-owners.

---

Nitpick comments:
In `@contrib/ivorysql_ora/sql/ora_sysview.sql`:
- Around line 94-106: The test only covers a single-column NOT NULL constraint;
extend TEST_USER_CONS_COLUMNS to include at least one composite constraint
(e.g., CONSTRAINT <name> UNIQUE (col1, col2) or a composite PRIMARY KEY) and
corresponding additional columns so that conkey expansion is exercised; then run
the SELECT from SYS.USER_CONS_COLUMNS and assert that it returns one row per
constrained column (i.e., one row for each column in the composite constraint)
by ordering on CONSTRAINT_NAME, COLUMN_NAME as before; update the CREATE TABLE
and any INSERT/cleanup statements to add the new columns/constraint and ensure
the DROP TABLE remains at the end.

🪄 Autofix (Beta)

Fix all unresolved CodeRabbit comments on this PR:

Push a commit to this branch (recommended)
Create a new PR with the fixes

ℹ️ Review info

⚙️ Run configuration

Configuration used: Path: .coderabbit.yaml

Review profile: CHILL

Plan: Pro

Run ID: bc84a642-7239-4a27-ad2d-1aa97c805b97

📥 Commits

Reviewing files that changed from the base of the PR and between 276bd7b and ac98e24.

📒 Files selected for processing (3)

contrib/ivorysql_ora/expected/ora_sysview.out
contrib/ivorysql_ora/sql/ora_sysview.sql
contrib/ivorysql_ora/src/sysview/sysview--1.0.sql

OreoYang · 2026-04-13T00:39:06Z

what's the relationship between #1270 and this one?

bigplaice · 2026-04-13T08:08:23Z

+		SYS.ORA_CASE_TRANS(PG_GET_USERBYID(C.RELOWNER)::VARCHAR2)::VARCHAR2(128) AS OWNER,
+		SYS.ORA_CASE_TRANS(C.RELNAME::VARCHAR2)::VARCHAR2(128) AS TABLE_NAME,
+		SYS.ORA_CASE_TRANS(CON.CONNAME::VARCHAR2)::VARCHAR2(128) AS CONSTRAINT_NAME,
+		SYS.ORA_CASE_TRANS(ATT.ATTNAME::VARCHAR2)::VARCHAR2(128) AS COLUMN_NAME


need support position.

bigplaice · 2026-04-13T09:49:14Z

please rework per the comments from coderabbitai and me.

bigplaice · 2026-04-14T05:53:03Z

For issue 1010:
#1010

Add SYS.USER_CONS_COLUMNS view and test case

ac98e24

coderabbitai bot reviewed Apr 10, 2026

View reviewed changes

Comment thread contrib/ivorysql_ora/expected/ora_sysview.out Outdated

Comment thread contrib/ivorysql_ora/src/sysview/sysview--1.0.sql

fix: update expected output for USER_CONS_COLUMNS

f012182

bigplaice reviewed Apr 13, 2026

View reviewed changes

3296442201 requested a review from bigplaice April 14, 2026 08:33

fix: add composite constraint test, grant public access

0b66659

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add SYS.USER_CONS_COLUMNS view and test case#1289

Add SYS.USER_CONS_COLUMNS view and test case#1289
3296442201 wants to merge 3 commits intoIvorySQL:masterfrom
3296442201:master

3296442201 commented Apr 10, 2026 •

edited by coderabbitai bot

Loading

Uh oh!

coderabbitai bot commented Apr 10, 2026 •

edited

Loading

Walkthrough

Changes

Estimated code review effort

Poem

Uh oh!

coderabbitai bot left a comment

Uh oh!

Uh oh!

Uh oh!

OreoYang commented Apr 13, 2026

Uh oh!

bigplaice Apr 13, 2026

Uh oh!

bigplaice commented Apr 13, 2026

Uh oh!

bigplaice commented Apr 14, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

3296442201 commented Apr 10, 2026 • edited by coderabbitai bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary by CodeRabbit

Uh oh!

coderabbitai bot commented Apr 10, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Walkthrough

Changes

Estimated code review effort

Poem

Uh oh!

coderabbitai bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

OreoYang commented Apr 13, 2026

Uh oh!

bigplaice Apr 13, 2026

Choose a reason for hiding this comment

Uh oh!

bigplaice commented Apr 13, 2026

Uh oh!

bigplaice commented Apr 14, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

3296442201 commented Apr 10, 2026 •

edited by coderabbitai bot

Loading

coderabbitai bot commented Apr 10, 2026 •

edited

Loading