WrenAI icon indicating copy to clipboard operation
WrenAI copied to clipboard
verified publisher icon Canner

Add UI functionality to capture Oracle data source

Open narsik123 opened this issue 8 months ago • 14 comments

Added Oracle data source functionality in UI to leverage existing Wren Engine code

Summary by CodeRabbit

  • New Features

    • Added support for Oracle as a data source throughout the application.
    • Introduced a new form component for configuring Oracle database connections, with validation and SSL options.
    • Oracle is now selectable in data source options, with dedicated connection documentation and visual assets.

  • Bug Fixes

    • None.

  • Documentation

    • None.

narsik123 avatar Apr 26 '25 11:04 narsik123

Walkthrough

The changes introduce Oracle as a supported data source throughout the codebase. This includes updates to enums, type definitions, and configuration mappings to recognize Oracle in both backend and frontend components. A new React component for Oracle connection properties is added, and utility functions are extended to handle Oracle-specific logic, images, and labels. The GraphQL schema and server-side data source handling are updated to process Oracle connection information. These updates collectively enable the application to support Oracle databases for configuration, connection, and display.

Changes

File(s) Change Summary
.../src/apollo/client/graphql/types.ts
.../src/apollo/server/types/dataSource.ts
.../src/apollo/server/schema.ts
.../src/utils/enum/dataSources.ts		 Added ORACLE member to relevant enums (DataSourceName, DATA_SOURCES).
.../src/apollo/server/adaptors/ibisAdaptor.ts
.../src/apollo/server/mdl/type.ts		 Added ORACLE to SupportedDataSource and WrenEngineDataSourceType enums; updated mapping for Oracle.
.../src/apollo/server/repositories/projectRepository.ts Added ORACLE_CONNECTION_INFO interface and included it in the connection info union type.
.../src/apollo/server/dataSource.ts Added Oracle entry to the dataSource object with decryption and mapping logic for Oracle connection info.
.../src/apollo/server/mdl/mdlBuilder.ts Updated internal logic to map Oracle data source type in MDLBuilder.
.../src/components/pages/setup/dataSources/OracleProperties.tsx Introduced new OracleProperties React component for Oracle connection form fields and validation.
.../src/components/pages/setup/utils.tsx Added Oracle configuration to DATA_SOURCE_OPTIONS with guide URL and enabled status.
.../src/utils/dataSourceType.ts Extended utility functions to support Oracle image, name, and properties component.

Sequence Diagram(s)

sequenceDiagram
    participant User
    participant UI
    participant Backend
    participant OracleDB

    User->>UI: Selects Oracle as data source
    UI->>UI: Renders OracleProperties form
    User->>UI: Fills Oracle connection info
    UI->>Backend: Submits Oracle connection info
    Backend->>Backend: Decrypts and validates info
    Backend->>OracleDB: Attempts connection using provided info
    OracleDB-->>Backend: Responds with connection status
    Backend-->>UI: Returns success or error
    UI-->>User: Displays result

Poem

In fields of code where data flows,
A new path for Oracle now grows.
With forms and enums, types anew,
The rabbit hops where schemas grew.
From backend roots to frontend bloom,
Oracle joins the data room!
🐇✨

📜 Recent review details

Configuration used: CodeRabbit UI Review profile: CHILL Plan: Pro

📥 Commits

Reviewing files that changed from the base of the PR and between e9b1cbb7ebd5c13b488bb9210f529e636ee31b50 and b1001b73cd33aa20613f48d27acb5560c477a2ac.

📒 Files selected for processing (4)
  • wren-ui/src/apollo/client/graphql/__types__.ts (1 hunks)
  • wren-ui/src/apollo/server/adaptors/ibisAdaptor.ts (2 hunks)
  • wren-ui/src/apollo/server/repositories/projectRepository.ts (2 hunks)
  • wren-ui/src/apollo/server/schema.ts (1 hunks)
🚧 Files skipped from review as they are similar to previous changes (4)
  • wren-ui/src/apollo/server/schema.ts
  • wren-ui/src/apollo/client/graphql/types.ts
  • wren-ui/src/apollo/server/repositories/projectRepository.ts
  • wren-ui/src/apollo/server/adaptors/ibisAdaptor.ts
✨ Finishing Touches
  • [ ] 📝 Generate Docstrings

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
🪧 Tips

Chat

There are 3 ways to chat with CodeRabbit:

  • Review comments: Directly reply to a review comment made by CodeRabbit. Example:
    • I pushed a fix in commit <commit_id>, please review it.
    • Explain this complex logic.
    • Open a follow-up GitHub issue for this discussion.
  • Files and specific lines of code (under the "Files changed" tab): Tag @coderabbitai in a new review comment at the desired location with your query. Examples:
    • @coderabbitai explain this code block.
    • @coderabbitai modularize this function.
  • PR comments: Tag @coderabbitai in a new PR comment to ask questions about the PR branch. For the best results, please provide a very specific query, as very limited context is provided in this mode. Examples:
    • @coderabbitai gather interesting stats about this repository and render them as a table. Additionally, render a pie chart showing the language distribution in the codebase.
    • @coderabbitai read src/utils.ts and explain its main purpose.
    • @coderabbitai read the files in the src/scheduler package and generate a class diagram using mermaid and a README in the markdown format.
    • @coderabbitai help me debug CodeRabbit configuration file.

Support

Need help? Create a ticket on our support page for assistance with any issues or questions.

Note: Be mindful of the bot's finite context window. It's strongly recommended to break down tasks such as reading entire modules into smaller chunks. For a focused discussion, use review comments to chat about specific files and their changes, instead of using the PR comments.

CodeRabbit Commands (Invoked using PR comments)

  • @coderabbitai pause to pause the reviews on a PR.
  • @coderabbitai resume to resume the paused reviews.
  • @coderabbitai review to trigger an incremental review. This is useful when automatic reviews are disabled for the repository.
  • @coderabbitai full review to do a full review from scratch and review all the files again.
  • @coderabbitai summary to regenerate the summary of the PR.
  • @coderabbitai generate docstrings to generate docstrings for this PR.
  • @coderabbitai generate sequence diagram to generate a sequence diagram of the changes in this PR.
  • @coderabbitai resolve resolve all the CodeRabbit review comments.
  • @coderabbitai configuration to show the current CodeRabbit configuration for the repository.
  • @coderabbitai help to get help.

Other keywords and placeholders

  • Add @coderabbitai ignore anywhere in the PR description to prevent this PR from being reviewed.
  • Add @coderabbitai summary to generate the high-level summary at a specific location in the PR description.
  • Add @coderabbitai anywhere in the PR title to generate the title automatically.

CodeRabbit Configuration File (.coderabbit.yaml)

  • You can programmatically configure CodeRabbit by adding a .coderabbit.yaml file to the root of your repository.
  • Please see the configuration documentation for more information.
  • If your editor has YAML language server enabled, you can add the path at the top of this file to enable auto-completion and validation: # yaml-language-server: $schema=https://coderabbit.ai/integrations/schema.v2.json

Documentation and Community

  • Visit our Documentation for detailed information on how to use CodeRabbit.
  • Join our Discord Community to get help, request features, and share feedback.
  • Follow us on X/Twitter for updates and announcements.

coderabbitai[bot] avatar Apr 26 '25 11:04 coderabbitai[bot]

Hey @narsik123 thanks for the contribution! This is really exciting.

See if @fredalai or @andreashimin could take a look ?

wwwy3y3 avatar Apr 28 '25 06:04 wwwy3y3

@fredalai or @andreashimin could take a look ?

Please let me know to integrate and test with your Oracle instance.

narsik123 avatar Apr 29 '25 04:04 narsik123

@narsik123 ,@douenergy I merged this pull request and tried to run it, however it doesn't work, reason : https://github.com/Canner/wren-engine/blob/main/ibis-server/app/model/metadata/oracle.py "SYSTEM" user hardcoded in this file: WHERE t.owner = 'SYSTEM' ORDER BY t.table_name, c.column_id;. so if I have HR db, user will be HR, but above query doesn't bring tables because, t.owner="SYSTEM"

numberonewastefellow avatar Apr 30 '25 04:04 numberonewastefellow

@narsik123 ,@douenergy I merged this pull request and tried to run it, however it doesn't work, reason : https://github.com/Canner/wren-engine/blob/main/ibis-server/app/model/metadata/oracle.py "SYSTEM" user hardcoded in this file: WHERE t.owner = 'SYSTEM' ORDER BY t.table_name, c.column_id;. so if I have HR db, user will be HR, but above query doesn't bring tables because, t.owner="SYSTEM"

thank you @numberonewastefellow. @chilijung @wwwy3y3 @goldmedal this would warrant a review of wren-engine code. Can I get some help in testing and if needed addressing the functionality in the engine.

narsik123 avatar Apr 30 '25 12:04 narsik123

@narsik123 i can help you to test, by the way did you tested oracle UI with connection to oracle db? or your just pushing UI changes? I'm working actively fixing engine bugs.

numberonewastefellow avatar May 01 '25 07:05 numberonewastefellow

@narsik123 i can help you to test, by the way did you tested oracle UI with connection to oracle db? or your just pushing UI changes? I'm working actively fixing engine bugs.

The Ui changes are functional. I'm able to fetch the tables and populate the model. But as you noted only the "system" tables are selected!!

narsik123 avatar May 01 '25 10:05 narsik123

@narsik123 after populating tables, did you treid to run query and see search results? i don't see this: ERROR: It seems there is missing implementation in either IBIS Server or wren engine: ibis-server-1 | 2025-05-01 10:05:35.451 | [18e14a4c-6596-4811-982a-ffaec1e6e801] | ERROR | main.custom_http_error_handler:84 - Request failed ibis-server-1 | Traceback (most recent call last): ibis-server-1 | File "/app/mdl/rewriter.py", line 66, in _extract_manifest ibis-server-1 | extractor = get_manifest_extractor(manifest_str) ibis-server-1 | File "/app/mdl/core.py", line 14, in get_manifest_extractor wren-ui-1 | [2025-05-01T10:05:35.477] [DEBUG] IbisAdaptor - Dry run error: Exception: <class 'oracledb.exceptions.DatabaseError'>, message: ORA-00923: FROM keyword not found where expected ibis-server-1 | return wren_core.ManifestExtractor(manifest_str) wren-ui-1 | Help: https://docs.oracle.com/error-help/db/ora-00923/ ###ibis-server-1 | Exception: Serde JSON error: unknown variant ORACLE, expected one of BIGQUERY, bigquery, CLICKHOUSE, clickhouse, CANNER, canner, TRINO, trino, MSSQL, mssql, MYSQL, mysql, POSTGRES, postgres, SNOWFLAKE, snowflake, DATAFUSION, datafusion, DUCKDB, duckdb, LOCALFILE, local_file, S3FILE, s3_file, GCSFILE, gcs_file, MINIOFILE, minio_file at line 1 column 59 ### wren-ui-1 | sendEvent ibis_dry_run_failed { ibis-server-1 | wren-ui-1 | correlationId: '18e14a4c-6596-4811-982a-ffaec1e6e801', ibis-server-1 | During handling of the above exception, another exception occurred: wren-ui-1 | processTime: '0.20843257696833462', ibis-server-1 | wren-ui-1 | error: "Exception: <class 'oracledb.exceptions.DatabaseError'>, message: ORA-00923: FROM keyword not found where expected\n" + ibis-server-1 | Traceback (most recent call last): wren-ui-1 | 'Help: https://docs.oracle.com/error-help/db/ora-00923/', ibis-server-1 | File "/app/routers/v3/connector.py", line 70, in query wren-ui-1 | dataSource: 'ORACLE', ibis-server-1 | rewritten_sql = await Rewriter( wren-ui-1 | sql: 'SELECT COUNT(DISTINCT "DEPARTMENT_ID") AS "TotalDepartments" FROM "TESTUSER_DEPARTMENTS"' ibis-server-1 | File "/app/.venv/lib/python3.11/site-packages/opentelemetry/util/_decorator.py", line 71, in async_wrapper wren-ui-1 | } UNKNOWN false ibis-server-1 | return await func(*args, **kwargs) # type: ignore wren-ui-1 | [2025-05-01T10:05:35.479] [ERROR] APOLLO - == original error == ibis-server-1 | File "/app/mdl/rewriter.py", line 54, in rewrite wren-ui-1 | [2025-05-01T10:05:35.479] [ERROR] APOLLO - AxiosError: Request failed with status code 422 ibis-server-1 | self._extract_manifest(self.manifest_str, sql) or self.manifest_str wren-ui-1 | at settle (file:///app/node_modules/axios/lib/core/settle.js:19:12) ibis-server-1 | File "/usr/local/lib/python3.11/contextlib.py", line 81, in inner wren-ui-1 | at IncomingMessage.handleStreamEnd (file:///app/node_modules/axios/lib/adapters/http.js:599:11) ibis-server-1 | return func(*args, **kwds) wren-ui-1 | at IncomingMessage.emit (node:events:529:35) ibis-server-1 | File "/app/mdl/rewriter.py", line 71, in _extract_manifest wren-ui-1 | at endReadableNT (node:internal/streams/readable:1400:12) ibis-server-1 | self._rewriter.handle_extract_exception(e) wren-ui-1 | at process.processTicksAndRejections (node:internal/process/task_queues:82:21) ibis-server-1 | File "/app/mdl/rewriter.py", line 125, in handle_extract_exception wren-ui-1 | at Axios.request (file:///app/node_modules/axios/lib/core/Axios.js:45:41) ibis-server-1 | raise RewriteError(str(e)) wren-ui-1 | at process.processTicksAndRejections (node:internal/process/task_queues:95:5) ibis-server-1 | app.mdl.rewriter.RewriteError: Serde JSON error: unknown variant ORACLE, expected one of BIGQUERY, bigquery, CLICKHOUSE, clickhouse, CANNER, canner, TRINO, trino, MSSQL, mssql, MYSQL, mysql, POSTGRES, postgres, SNOWFLAKE, snowflake, DATAFUSION, datafusion, DUCKDB, duckdb, LOCALFILE, local_file, S3FILE, s3_file, GCSFILE, gcs_file, MINIOFILE, minio_file at line 1 column 59 wren-ui-1 | at async f.dryRun (/app/.next/server/chunks/980.js:1:2407) ibis-server-1 | wren-ui-1 | at async c.ibisDryRun (/app/.next/server/chunks/980.js:8:39552) ibis-server-1 | During handling of the above exception, another exception occurred: wren-ui-1 | at async c.preview (/app/.next/server/chunks/980.js:8:38981) ibis-server-1 | wren-ui-1 | at async N.previewSql (/app/.next/server/pages/api/graphql.js:1:83704) ibis-server-1 | Traceback (most recent call last): ibis-server-1 | File "/app/model/connector.py", line 61, in dry_run ibis-server-1 | self._connector.dry_run(sql) ibis-server-1 | File "/usr/local/lib/python3.11/contextlib.py", line 81, in inner ibis-server-1 | return func(*args, **kwds) ibis-server-1 | File "/app/model/connector.py", line 77, in dry_run ibis-server-1 | self.connection.sql(sql) ibis-server-1 | File "/app/.venv/lib/python3.11/site-packages/ibis/backends/sql/init.py", line 179, in sql ibis-server-1 | schema = self._get_schema_using_query(query) ibis-server-1 | File "/app/.venv/lib/python3.11/site-packages/ibis/backends/oracle/init.py", line 584, in _get_schema_using_query ibis-server-1 | results = con.execute(metadata_query).fetchall() ibis-server-1 | File "/app/.venv/lib/python3.11/site-packages/oracledb/cursor.py", line 708, in execute ibis-server-1 | impl.execute(self) ibis-server-1 | File "src/oracledb/impl/thin/cursor.pyx", line 194, in oracledb.thin_impl.ThinCursorImpl.execute

@douenergy can you please check this error?

numberonewastefellow avatar May 01 '25 10:05 numberonewastefellow

connector.py

That explains. I still rely on the docker image for ibis which likely relies on v2 connector.py and hence did not encounter this issue. Is there a reason that the main branch of WrenAI in GitHub still points to [wren-engine @ 922ec70]

narsik123 avatar May 01 '25 14:05 narsik123

@narsik123 @numberonewastefellow I think we're missing some enum variants in wren-core for serde_json deserialization. I've opened an issue to track the missing variants

https://github.com/Canner/wren-engine/issues/1177

Thanks for reporting this! 🙌

douenergy avatar May 02 '25 12:05 douenergy

@narsik123 @numberonewastefellow I think we're missing some enum variants in wren-core for serde_json deserialization. I've opened an issue to track the missing variants

Canner/wren-engine#1177

Thanks for reporting this! 🙌

@douenergy @narsik123 @numberonewastefellow I have fixed it by https://github.com/Canner/wren-engine/pull/1178. I prepared an image oracle-ds-1 for testing before this PR is merged and released.

docker pull ghcr.io/canner/wren-engine-ibis:oracle-ds-1

goldmedal avatar May 02 '25 15:05 goldmedal

To, @goldmedal, @narsik123 @douenergy

@goldmedal i merged your changes manually, not able to run, I also tried with your image: oracle-ds-1, in both cases same error.
Main error: ORA-00923: FROM keyword not found where expected.

2025-05-05 11:47:26.048 | [7084a25a-f643-4be0-a55c-d724e2e5cbee] | INFO | init.dispatch:29 - Request ended 2025-05-05 11:47:26.939 | [656d1131-f77f-492d-82f6-649d07cf79a8] | INFO | init.dispatch:14 - POST /v3/connector/oracle/query 2025-05-05 11:47:26.939 | [656d1131-f77f-492d-82f6-649d07cf79a8] | INFO | init.dispatch:15 - Request params: {'dryRun': 'true'} 2025-05-05 11:47:26.941 | [656d1131-f77f-492d-82f6-649d07cf79a8] | INFO | init.dispatch:22 - Request body: {"sql":"SELECT "TOPIC" FROM "SYSTEM_HELP" GROUP BY "TOPIC" ORDER BY COUNT(*) DESC","connectionInfo":"REDACTED","manifestStr":"eyJzY2hlbWEiOiJwdWJsaWMiLCJjYXRhbG9nIjoid3JlbmFpIiwiZGF0YVNvdXJjZSI6Ik9SQUNMRSIsIm1vZGVscyI6W3sibmFtZSI6IlNZU1RFTV9IRUxQIiwiY29sdW1ucyI6W3sibmFtZSI6IlRPUElDIiwidHlwZSI6IlZBUkNIQVIiLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjp0cnVlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiVE9QSUMifX0seyJuYW1lIjoiU0VRIiwidHlwZSI6IkRFQ0lNQUwiLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjp0cnVlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiU0VRIn19LHsibmFtZSI6IklORk8iLCJ0eXBlIjoiVkFSQ0hBUiIsImlzQ2FsY3VsYXRlZCI6ZmFsc2UsIm5vdE51bGwiOmZhbHNlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiSU5GTyJ9fV0sInRhYmxlUmVmZXJlbmNlIjp7ImNhdGFsb2ciOm51bGwsInNjaGVtYSI6IlNZU1RFTSIsInRhYmxlIjoiSEVMUCJ9LCJjYWNoZWQiOmZhbHNlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiU1lTVEVNLkhFTFAifSwicHJpbWFyeUtleSI6IiJ9XSwicmVsYXRpb25zaGlwcyI6W10sInZpZXdzIjpbXX0="} 2025-05-05 11:47:26.969 | [656d1131-f77f-492d-82f6-649d07cf79a8] | DEBUG | rewriter.rewrite:56 - Extracted manifest: eyJjYXRhbG9nIjoid3JlbmFpIiwic2NoZW1hIjoicHVibGljIiwibW9kZWxzIjpbeyJuYW1lIjoiU1lTVEVNX0hFTFAiLCJyZWZTcWwiOm51bGwsImJhc2VPYmplY3QiOm51bGwsInRhYmxlUmVmZXJlbmNlIjp7ImNhdGFsb2ciOm51bGwsInNjaGVtYSI6IlNZU1RFTSIsInRhYmxlIjoiSEVMUCJ9LCJjb2x1bW5zIjpbeyJuYW1lIjoiVE9QSUMiLCJ0eXBlIjoiVkFSQ0hBUiIsInJlbGF0aW9uc2hpcCI6bnVsbCwiaXNDYWxjdWxhdGVkIjpmYWxzZSwibm90TnVsbCI6dHJ1ZSwiZXhwcmVzc2lvbiI6IiIsImlzSGlkZGVuIjpmYWxzZSwicmxzIjpudWxsLCJjbHMiOm51bGx9LHsibmFtZSI6IlNFUSIsInR5cGUiOiJERUNJTUFMIiwicmVsYXRpb25zaGlwIjpudWxsLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjp0cnVlLCJleHByZXNzaW9uIjoiIiwiaXNIaWRkZW4iOmZhbHNlLCJybHMiOm51bGwsImNscyI6bnVsbH0seyJuYW1lIjoiSU5GTyIsInR5cGUiOiJWQVJDSEFSIiwicmVsYXRpb25zaGlwIjpudWxsLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjpmYWxzZSwiZXhwcmVzc2lvbiI6IiIsImlzSGlkZGVuIjpmYWxzZSwicmxzIjpudWxsLCJjbHMiOm51bGx9XSwicHJpbWFyeUtleSI6IiIsImNhY2hlZCI6ZmFsc2UsInJlZnJlc2hUaW1lIjpudWxsfV0sInJlbGF0aW9uc2hpcHMiOltdLCJtZXRyaWNzIjpbXSwidmlld3MiOltdLCJkYXRhU291cmNlIjoiT1JBQ0xFIn0= 2025-05-05 11:47:26.985 | [656d1131-f77f-492d-82f6-649d07cf79a8] | DEBUG | rewriter.rewrite:58 - Planned SQL: SELECT "SYSTEM_HELP"."TOPIC" FROM (SELECT "SYSTEM_HELP"."TOPIC" FROM (SELECT "__source"."TOPIC" AS "TOPIC" FROM "SYSTEM"."HELP" AS "__source") AS "SYSTEM_HELP") AS "SYSTEM_HELP" GROUP BY "SYSTEM_HELP"."TOPIC" ORDER BY count(1) DESC NULLS FIRST 2025-05-05 11:47:26.987 | [656d1131-f77f-492d-82f6-649d07cf79a8] | DEBUG | rewriter.rewrite:60 - Dialect SQL: SELECT "SYSTEM_HELP"."TOPIC" FROM (SELECT "SYSTEM_HELP"."TOPIC" FROM (SELECT "__source"."TOPIC" AS "TOPIC" FROM "SYSTEM"."HELP" "__source") "SYSTEM_HELP") "SYSTEM_HELP" GROUP BY "SYSTEM_HELP"."TOPIC" ORDER BY COUNT(1) DESC 2025-05-05 11:47:27.039 | [656d1131-f77f-492d-82f6-649d07cf79a8] | WARNING | connector.query:149 - Failed to execute v3 query, try to fallback to v2: Exception: <class 'oracledb.exceptions.DatabaseError'>, message: ORA-00923: FROM keyword not found where expected Help: https://docs.oracle.com/error-help/db/ora-00923/

2025-05-05 11:47:27.067 | [656d1131-f77f-492d-82f6-649d07cf79a8] | DEBUG | rewriter.rewrite:56 - Extracted manifest: eyJjYXRhbG9nIjoid3JlbmFpIiwic2NoZW1hIjoicHVibGljIiwibW9kZWxzIjpbeyJuYW1lIjoiU1lTVEVNX0hFTFAiLCJyZWZTcWwiOm51bGwsImJhc2VPYmplY3QiOm51bGwsInRhYmxlUmVmZXJlbmNlIjp7ImNhdGFsb2ciOm51bGwsInNjaGVtYSI6IlNZU1RFTSIsInRhYmxlIjoiSEVMUCJ9LCJjb2x1bW5zIjpbeyJuYW1lIjoiVE9QSUMiLCJ0eXBlIjoiVkFSQ0hBUiIsInJlbGF0aW9uc2hpcCI6bnVsbCwiaXNDYWxjdWxhdGVkIjpmYWxzZSwibm90TnVsbCI6dHJ1ZSwiZXhwcmVzc2lvbiI6IiIsImlzSGlkZGVuIjpmYWxzZSwicmxzIjpudWxsLCJjbHMiOm51bGx9LHsibmFtZSI6IlNFUSIsInR5cGUiOiJERUNJTUFMIiwicmVsYXRpb25zaGlwIjpudWxsLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjp0cnVlLCJleHByZXNzaW9uIjoiIiwiaXNIaWRkZW4iOmZhbHNlLCJybHMiOm51bGwsImNscyI6bnVsbH0seyJuYW1lIjoiSU5GTyIsInR5cGUiOiJWQVJDSEFSIiwicmVsYXRpb25zaGlwIjpudWxsLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjpmYWxzZSwiZXhwcmVzc2lvbiI6IiIsImlzSGlkZGVuIjpmYWxzZSwicmxzIjpudWxsLCJjbHMiOm51bGx9XSwicHJpbWFyeUtleSI6IiIsImNhY2hlZCI6ZmFsc2UsInJlZnJlc2hUaW1lIjpudWxsfV0sInJlbGF0aW9uc2hpcHMiOltdLCJtZXRyaWNzIjpbXSwidmlld3MiOltdLCJkYXRhU291cmNlIjoiT1JBQ0xFIn0= 2025-05-05 11:47:27.090 | [656d1131-f77f-492d-82f6-649d07cf79a8] | DEBUG | rewriter.rewrite:58 - Planned SQL: WITH "SYSTEM_HELP" AS ( SELECT "SYSTEM_HELP"."TOPIC" "TOPIC" , "SYSTEM_HELP"."SEQ" "SEQ" , "SYSTEM_HELP"."INFO" "INFO" FROM ( SELECT "SYSTEM_HELP"."TOPIC" "TOPIC" , "SYSTEM_HELP"."SEQ" "SEQ" , "SYSTEM_HELP"."INFO" "INFO" FROM ( SELECT "TOPIC" "TOPIC" , "SEQ" "SEQ" , "INFO" "INFO" FROM "SYSTEM"."HELP" "SYSTEM_HELP" ) "SYSTEM_HELP" ) "SYSTEM_HELP" ) SELECT "TOPIC" FROM "SYSTEM_HELP" GROUP BY "TOPIC" ORDER BY COUNT() DESC 2025-05-05 11:47:27.093 | [656d1131-f77f-492d-82f6-649d07cf79a8] | DEBUG | rewriter.rewrite:60 - Dialect SQL: WITH "SYSTEM_HELP" AS (SELECT "SYSTEM_HELP"."TOPIC" AS "TOPIC", "SYSTEM_HELP"."SEQ" AS "SEQ", "SYSTEM_HELP"."INFO" AS "INFO" FROM (SELECT "SYSTEM_HELP"."TOPIC" AS "TOPIC", "SYSTEM_HELP"."SEQ" AS "SEQ", "SYSTEM_HELP"."INFO" AS "INFO" FROM (SELECT "TOPIC" AS "TOPIC", "SEQ" AS "SEQ", "INFO" AS "INFO" FROM "SYSTEM"."HELP" "SYSTEM_HELP") "SYSTEM_HELP") "SYSTEM_HELP") SELECT "TOPIC" FROM "SYSTEM_HELP" GROUP BY "TOPIC" ORDER BY COUNT() DESC NULLS LAST 2025-05-05 11:47:27.155 | [656d1131-f77f-492d-82f6-649d07cf79a8] | ERROR | main.custom_http_error_handler:84 - Request failed Traceback (most recent call last): File "/app/model/connector.py", line 61, in dry_run self._connector.dry_run(sql) File "/usr/local/lib/python3.11/contextlib.py", line 81, in inner return func(*args, **kwds) File "/app/model/connector.py", line 77, in dry_run self.connection.sql(sql) File "/app/.venv/lib/python3.11/site-packages/ibis/backends/sql/init.py", line 179, in sql schema = self._get_schema_using_query(query) File "/app/.venv/lib/python3.11/site-packages/ibis/backends/oracle/init.py", line 584, in _get_schema_using_query results = con.execute(metadata_query).fetchall() File "/app/.venv/lib/python3.11/site-packages/oracledb/cursor.py", line 708, in execute impl.execute(self) File "src/oracledb/impl/thin/cursor.pyx", line 194, in oracledb.thin_impl.ThinCursorImpl.execute File "src/oracledb/impl/thin/protocol.pyx", line 447, in oracledb.thin_impl.Protocol._process_single_message File "src/oracledb/impl/thin/protocol.pyx", line 448, in oracledb.thin_impl.Protocol._process_single_message File "src/oracledb/impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_message File "src/oracledb/impl/thin/messages.pyx", line 103, in oracledb.thin_impl.Message._check_and_raise_exception oracledb.exceptions.DatabaseError: ORA-00923: FROM keyword not found where expected Help: https://docs.oracle.com/error-help/db/ora-00923/

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "/app/routers/v3/connector.py", line 78, in query connector.dry_run(rewritten_sql) File "/app/model/connector.py", line 63, in dry_run raise QueryDryRunError(f"Exception: {type(e)}, message: {e!s}") app.model.connector.QueryDryRunError: Exception: <class 'oracledb.exceptions.DatabaseError'>, message: ORA-00923: FROM keyword not found where expected Help: https://docs.oracle.com/error-help/db/ora-00923/

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "/app/model/connector.py", line 61, in dry_run self._connector.dry_run(sql) File "/usr/local/lib/python3.11/contextlib.py", line 81, in inner return func(*args, **kwds) File "/app/model/connector.py", line 77, in dry_run self.connection.sql(sql) File "/app/.venv/lib/python3.11/site-packages/ibis/backends/sql/init.py", line 179, in sql schema = self._get_schema_using_query(query) File "/app/.venv/lib/python3.11/site-packages/ibis/backends/oracle/init.py", line 584, in _get_schema_using_query results = con.execute(metadata_query).fetchall() File "/app/.venv/lib/python3.11/site-packages/oracledb/cursor.py", line 708, in execute impl.execute(self) File "src/oracledb/impl/thin/cursor.pyx", line 194, in oracledb.thin_impl.ThinCursorImpl.execute File "src/oracledb/impl/thin/protocol.pyx", line 447, in oracledb.thin_impl.Protocol._process_single_message File "src/oracledb/impl/thin/protocol.pyx", line 448, in oracledb.thin_impl.Protocol._process_single_message File "src/oracledb/impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_message File "src/oracledb/impl/thin/messages.pyx", line 103, in oracledb.thin_impl.Message._check_and_raise_exception oracledb.exceptions.DatabaseError: ORA-00923: FROM keyword not found where expected Help: https://docs.oracle.com/error-help/db/ora-00923/

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File "/app/.venv/lib/python3.11/site-packages/starlette/_exception_handler.py", line 42, in wrapped_app await app(scope, receive, sender) File "/app/.venv/lib/python3.11/site-packages/starlette/routing.py", line 73, in app response = await f(request) File "/app/.venv/lib/python3.11/site-packages/fastapi/routing.py", line 301, in app raw_response = await run_endpoint_function( File "/app/.venv/lib/python3.11/site-packages/fastapi/routing.py", line 212, in run_endpoint_function return await dependant.call(**values) File "/app/routers/v3/connector.py", line 153, in query return await v2.connector.query( File "/app/routers/v2/connector.py", line 90, in query connector.dry_run(rewritten_sql) File "/app/model/connector.py", line 63, in dry_run raise QueryDryRunError(f"Exception: {type(e)}, message: {e!s}") app.model.connector.QueryDryRunError: Exception: <class 'oracledb.exceptions.DatabaseError'>, message: ORA-00923: FROM keyword not found where expected Help: https://docs.oracle.com/error-help/db/ora-00923/ 2025-05-05 11:47:27.158 | [656d1131-f77f-492d-82f6-649d07cf79a8] | INFO | init.dispatch:29 - Request ended INFO 172.19.0.7:38758 - "POST /v3/connector/oracle/query?dryRun=true HTTP/1.1" 422

numberonewastefellow avatar May 05 '25 12:05 numberonewastefellow

2025-05-05 11:47:26.048 | [7084a25a-f643-4be0-a55c-d724e2e5cbee] | INFO | init.dispatch:29 - Request ended 2025-05-05 11:47:26.939 | [656d1131-f77f-492d-82f6-649d07cf79a8] | INFO | init.dispatch:14 - POST /v3/connector/oracle/query 2025-05-05 11:47:26.939 | [656d1131-f77f-492d-82f6-649d07cf79a8] | INFO | init.dispatch:15 - Request params: {'dryRun': 'true'} 2025-05-05 11:47:26.941 | [656d1131-f77f-492d-82f6-649d07cf79a8] | INFO | init.dispatch:22 - Request body: {"sql":"SELECT "TOPIC" FROM "SYSTEM_HELP" GROUP BY "TOPIC" ORDER BY COUNT(*) DESC","connectionInfo":"REDACTED","manifestStr":"eyJzY2hlbWEiOiJwdWJsaWMiLCJjYXRhbG9nIjoid3JlbmFpIiwiZGF0YVNvdXJjZSI6Ik9SQUNMRSIsIm1vZGVscyI6W3sibmFtZSI6IlNZU1RFTV9IRUxQIiwiY29sdW1ucyI6W3sibmFtZSI6IlRPUElDIiwidHlwZSI6IlZBUkNIQVIiLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjp0cnVlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiVE9QSUMifX0seyJuYW1lIjoiU0VRIiwidHlwZSI6IkRFQ0lNQUwiLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjp0cnVlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiU0VRIn19LHsibmFtZSI6IklORk8iLCJ0eXBlIjoiVkFSQ0hBUiIsImlzQ2FsY3VsYXRlZCI6ZmFsc2UsIm5vdE51bGwiOmZhbHNlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiSU5GTyJ9fV0sInRhYmxlUmVmZXJlbmNlIjp7ImNhdGFsb2ciOm51bGwsInNjaGVtYSI6IlNZU1RFTSIsInRhYmxlIjoiSEVMUCJ9LCJjYWNoZWQiOmZhbHNlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiU1lTVEVNLkhFTFAifSwicHJpbWFyeUtleSI6IiJ9XSwicmVsYXRpb25zaGlwcyI6W10sInZpZXdzIjpbXX0="}

I tried to use the same request body to add a test case for reproducing in ibis-server/tests/routers/v3/connector/oracle/test_query.py. It works well.

async def test_query_1(client, connection_info):
    manifest_str = "eyJzY2hlbWEiOiJwdWJsaWMiLCJjYXRhbG9nIjoid3JlbmFpIiwiZGF0YVNvdXJjZSI6Ik9SQUNMRSIsIm1vZGVscyI6W3sibmFtZSI6IlNZU1RFTV9IRUxQIiwiY29sdW1ucyI6W3sibmFtZSI6IlRPUElDIiwidHlwZSI6IlZBUkNIQVIiLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjp0cnVlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiVE9QSUMifX0seyJuYW1lIjoiU0VRIiwidHlwZSI6IkRFQ0lNQUwiLCJpc0NhbGN1bGF0ZWQiOmZhbHNlLCJub3ROdWxsIjp0cnVlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiU0VRIn19LHsibmFtZSI6IklORk8iLCJ0eXBlIjoiVkFSQ0hBUiIsImlzQ2FsY3VsYXRlZCI6ZmFsc2UsIm5vdE51bGwiOmZhbHNlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiSU5GTyJ9fV0sInRhYmxlUmVmZXJlbmNlIjp7ImNhdGFsb2ciOm51bGwsInNjaGVtYSI6IlNZU1RFTSIsInRhYmxlIjoiSEVMUCJ9LCJjYWNoZWQiOmZhbHNlLCJwcm9wZXJ0aWVzIjp7ImRpc3BsYXlOYW1lIjoiU1lTVEVNLkhFTFAifSwicHJpbWFyeUtleSI6IiJ9XSwicmVsYXRpb25zaGlwcyI6W10sInZpZXdzIjpbXX0="
    response = await client.post(
        url=f"{base_url}/query",
        json={
            "connectionInfo": connection_info,
            "manifestStr": manifest_str,
            "sql": 'SELECT "TOPIC" FROM "SYSTEM_HELP" GROUP BY "TOPIC" ORDER BY COUNT(*) DESC',
        },
        headers={
            X_WREN_FALLBACK_DISABLE: "true",
        },
    )
    assert response.status_code == 200
    result = response.json()
    print(result)

I noticed your SQL in the stdout log doesn't have the escaping character for the double-quotes.

 Request body: {"sql":"SELECT "TOPIC" FROM "SYSTEM_HELP" GROUP BY "TOPIC" ORDER BY COUNT(*) DESC"

I have no idea how to reproduce the same case. However, it should have the escaping character for the quotes like:

Request body: {"sql":"SELECT \"TOPIC\" FROM \"SYSTEM_HELP\" GROUP BY \"TOPIC\" ORDER BY COUNT(*) DESC"}

Another question, what's your oracle version?

goldmedal avatar May 06 '25 01:05 goldmedal

Hi @narsik123, Just a quick reminder — as of now, there’s nothing blocking this PR. The only thing left is removing the SSL form field. Once that’s done, we’re good to merge! Let me know if you need anything. Thanks! :D

Hi @fredalai I had to force a push to get this change committed. Can you please verify and merge

narsik123 avatar May 21 '25 03:05 narsik123

Hi @narsik123, I saw that you’ve removed the SSL form field — thanks for taking care of that!

I did notice, though, that there’s still a corresponding part that hasn’t been removed yet. Would you mind cleaning that up as well, just to keep things tidy?

That should be the last step — once it’s done, we’ll be ready to merge 🙌

I have removed the ssl form field and the structures for passing it too

narsik123 avatar May 28 '25 01:05 narsik123