search

ArcadeData / arcadedb

ArcadeDB Multi-Model Database, one DBMS that supports SQL, Cypher, Gremlin, HTTP/JSON, MongoDB and Redis. ArcadeDB is a conceptual fork of OrientDB, the first Multi-Model DBMS. ArcadeDB supports Vector Embeddings.
https://arcadedb.com
Apache License 2.0
469 stars 57 forks source link

Connecting via DbSchema (Postgres) errors #1600

Open gramian opened 1 month ago

gramian commented 1 month ago

ArcadeDB Version:

ArcadeDB Server v24.5.1-SNAPSHOT (build 7872d1acb3616c2866613dabcb7d899322bb3348/1715204385992/main)

OS and JDK Version:

Running on Mac OS X 12.7.4 - OpenJDK 64-Bit Server VM 17.0.11 (Homebrew)

I am trying to connect via DbSchema (@wise-coders) to ArcadeDB using the Postgres driver.

Expected behavior

Successful connection

Actual behavior

ArcadeDB:

SEVER [PostgresNetworkExecutor] PSQL: Closing connection with clientUnexpected message type '' for message any
com.arcadedb.postgres.PostgresProtocolException: Unexpected message type '' for message any
    at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1062)
    at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:149)

DbSchema:

Syntax error on executing query: Encountered ")" ")" at line 1, column 166.

org.postgresql.util.PSQLException: ERROR: Syntax error on executing query: Encountered "(" "(" at line 1, column 116.

  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
  at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:341)
  at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:326)
  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:302)
  at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:251)
  at org.postgresql.jdbc.PgDatabaseMetaData.getSchemas(PgDatabaseMetaData.java:1602)
  at org.postgresql.jdbc.PgDatabaseMetaData.getSchemas(PgDatabaseMetaData.java:1583)
  at com.wisecoders.dbs.dbms.Dbms.listSchemasAndCatalogs(Dbms.java:293)
  at com.wisecoders.dbs.dbms.Dbms.loadSchemasAndCatalogs(Dbms.java:271)
  at com.wisecoders.dbs.dbms.reverseEngineer.tasks.FxImportSelectionTask.a(FxImportSelectionTask.java:42)
  at com.wisecoders.dbs.dbms.reverseEngineer.tasks.FxImportSelectionTask.call(FxImportSelectionTask.java:20)

Steps to reproduce

  1. I started ArcadeDB with Postgres Plugin
  2. In DbSchema I set up the connection using sslMode=disable and preferQueryMode=simple.

I think maybe DbSchema sends somekind of initial command to test if the connection works and this happens to be empty and thus causing the error (just a guess).

gramian commented 1 month ago

Using the debug setting I got this query was send from DbSchema:

SELECT nspname AS TABLE_SCHEM, NULL AS TABLE_CATALOG FROM pg_catalog.pg_namespace  WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_'  OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_'  OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_'))  ORDER BY TABLE_SCHEM
wise-coders commented 1 month ago

We reverse engineer the schema by calling methods from the JDBC driver. It looks like ArcadeDB has different system tables as Postgres. Do you know which queries we can use to list the schemes, tables, columns, foreign keys? Or generate the table DDL CREATE TABLE statement? We would need this to make DbSchema work with ArcadeDB. A sample Groovy script to use for tests can be found in Tools / Automation Scripts / Schema Sync / List Databases using JDBC Driver.

gramian commented 1 month ago

Hi, there is:

The corresponding DDL command would be CREATE DOCUMENT TYPE.