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
486 stars 60 forks source link

Console LOAD command does not parse comments in OSQL script files correctly #1422

Open ironluca opened 8 months ago

ironluca commented 8 months ago

client-platform-base-db-schema.txt Parsing of comments from script files loaded with "load" command from 'console.sh' is not correct, it takes the comments as OSQL statements and generates exception (as highlighted below). The script part executes perfectly and creates the necessary vertex and edge types.

The above is not a show stopper as the script part executes fine.

`{test_db}> /The vertex class that represents a generalized root or conceptual staring point of a graph ERROR: com.arcadedb.remote.RemoteException: Error on executing remote operation /The vertex class that represents a generalized root or conceptual staring point of a graph (cause:com.arcadedb.query.sql.parser.TokenMgrError detail:Lexical error at line 1, column 93. Encountered: after : "") at com.arcadedb.remote.RemoteHttpComponent.manageException(RemoteHttpComponent.java:446) at com.arcadedb.remote.RemoteHttpComponent.httpCommand(RemoteHttpComponent.java:170) at com.arcadedb.remote.RemoteDatabase.databaseCommand(RemoteDatabase.java:461) at com.arcadedb.remote.RemoteDatabase.command(RemoteDatabase.java:408) at com.arcadedb.console.Console.executeSQL(Console.java:602) at com.arcadedb.console.Console.execute(Console.java:273) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.executeLoad(Console.java:699) at com.arcadedb.console.Console.execute(Console.java:267) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.interactiveMode(Console.java:131) at com.arcadedb.console.Console.execute(Console.java:185) at com.arcadedb.console.Console.main(Console.java:148)

{test_db}> (graphs don't have a starting point, this is just a conceptual start vertex for complete graph traversal). ERROR: com.arcadedb.remote.RemoteException: Error on executing remote operation (graphs don't have a starting point, this is just a conceptual start vertex for complete graph traversal). (cause:com.arcadedb.query.sql.parser.ParseException detail:Encountered "(" "(" at line 1, column 1. Was expecting one of: ... ... ) at com.arcadedb.remote.RemoteHttpComponent.manageException(RemoteHttpComponent.java:446) at com.arcadedb.remote.RemoteHttpComponent.httpCommand(RemoteHttpComponent.java:170) at com.arcadedb.remote.RemoteDatabase.databaseCommand(RemoteDatabase.java:461) at com.arcadedb.remote.RemoteDatabase.command(RemoteDatabase.java:408) at com.arcadedb.console.Console.executeSQL(Console.java:602) at com.arcadedb.console.Console.execute(Console.java:273) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.executeLoad(Console.java:699) at com.arcadedb.console.Console.execute(Console.java:267) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.interactiveMode(Console.java:131) at com.arcadedb.console.Console.execute(Console.java:185) at com.arcadedb.console.Console.main(Console.java:148)

{test_db}>
{test_db}> Usually two objects of this class will be created, one sits at the top of the hierarchy of the ERROR: com.arcadedb.remote.RemoteException: Error on executing remote operation Usually two objects of this class will be created, one sits at the top of the hierarchy of the (cause:com.arcadedb.query.sql.parser.ParseException detail:Encountered "Usually" at line 1, column 1. Was expecting one of: ... ... ) at com.arcadedb.remote.RemoteHttpComponent.manageException(RemoteHttpComponent.java:446) at com.arcadedb.remote.RemoteHttpComponent.httpCommand(RemoteHttpComponent.java:170) at com.arcadedb.remote.RemoteDatabase.databaseCommand(RemoteDatabase.java:461) at com.arcadedb.remote.RemoteDatabase.command(RemoteDatabase.java:408) at com.arcadedb.console.Console.executeSQL(Console.java:602) at com.arcadedb.console.Console.execute(Console.java:273) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.executeLoad(Console.java:699) at com.arcadedb.console.Console.execute(Console.java:267) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.interactiveMode(Console.java:131) at com.arcadedb.console.Console.execute(Console.java:185) at com.arcadedb.console.Console.main(Console.java:148)

{test_db}> ontology def model and another at the top of the hierarchy of the ontology data model. ERROR: com.arcadedb.remote.RemoteException: Error on executing remote operation ontology def model and another at the top of the hierarchy of the ontology data model. (cause:com.arcadedb.query.sql.parser.ParseException detail:Encountered "ontology" at line 1, column 1. Was expecting one of: ... ... ) at com.arcadedb.remote.RemoteHttpComponent.manageException(RemoteHttpComponent.java:446) at com.arcadedb.remote.RemoteHttpComponent.httpCommand(RemoteHttpComponent.java:170) at com.arcadedb.remote.RemoteDatabase.databaseCommand(RemoteDatabase.java:461) at com.arcadedb.remote.RemoteDatabase.command(RemoteDatabase.java:408) at com.arcadedb.console.Console.executeSQL(Console.java:602) at com.arcadedb.console.Console.execute(Console.java:273) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.executeLoad(Console.java:699) at com.arcadedb.console.Console.execute(Console.java:267) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.interactiveMode(Console.java:131) at com.arcadedb.console.Console.execute(Console.java:185) at com.arcadedb.console.Console.main(Console.java:148)

{test_db}>
{test_db}> In addition, this class can be used by applications to declare conceptual application root vertices ERROR: com.arcadedb.remote.RemoteException: Error on executing remote operation In addition, this class can be used by applications to declare conceptual application root vertices (cause:com.arcadedb.query.sql.parser.ParseException detail:Encountered "In" at line 1, column 1. Was expecting one of: ... ... ) at com.arcadedb.remote.RemoteHttpComponent.manageException(RemoteHttpComponent.java:446) at com.arcadedb.remote.RemoteHttpComponent.httpCommand(RemoteHttpComponent.java:170) at com.arcadedb.remote.RemoteDatabase.databaseCommand(RemoteDatabase.java:461) at com.arcadedb.remote.RemoteDatabase.command(RemoteDatabase.java:408) at com.arcadedb.console.Console.executeSQL(Console.java:602) at com.arcadedb.console.Console.execute(Console.java:273) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.executeLoad(Console.java:699) at com.arcadedb.console.Console.execute(Console.java:267) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.interactiveMode(Console.java:131) at com.arcadedb.console.Console.execute(Console.java:185) at com.arcadedb.console.Console.main(Console.java:148)

{test_db}> in the data model. The name property of this class are unique and cannot be repeated. ERROR: com.arcadedb.remote.RemoteException: Error on executing remote operation in the data model. The name property of this class are unique and cannot be repeated. (cause:com.arcadedb.query.sql.parser.ParseException detail:Encountered "in" at line 1, column 1. Was expecting one of: ... ... ) at com.arcadedb.remote.RemoteHttpComponent.manageException(RemoteHttpComponent.java:446) at com.arcadedb.remote.RemoteHttpComponent.httpCommand(RemoteHttpComponent.java:170) at com.arcadedb.remote.RemoteDatabase.databaseCommand(RemoteDatabase.java:461) at com.arcadedb.remote.RemoteDatabase.command(RemoteDatabase.java:408) at com.arcadedb.console.Console.executeSQL(Console.java:602) at com.arcadedb.console.Console.execute(Console.java:273) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.executeLoad(Console.java:699) at com.arcadedb.console.Console.execute(Console.java:267) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.interactiveMode(Console.java:131) at com.arcadedb.console.Console.execute(Console.java:185) at com.arcadedb.console.Console.main(Console.java:148)

{test_db}> / ERROR: com.arcadedb.remote.RemoteException: Error on executing remote operation / (cause:com.arcadedb.query.sql.parser.ParseException detail:Encountered "" "" at line 1, column 1. Was expecting one of: ... ... ) at com.arcadedb.remote.RemoteHttpComponent.manageException(RemoteHttpComponent.java:446) at com.arcadedb.remote.RemoteHttpComponent.httpCommand(RemoteHttpComponent.java:170) at com.arcadedb.remote.RemoteDatabase.databaseCommand(RemoteDatabase.java:461) at com.arcadedb.remote.RemoteDatabase.command(RemoteDatabase.java:408) at com.arcadedb.console.Console.executeSQL(Console.java:602) at com.arcadedb.console.Console.execute(Console.java:273) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.executeLoad(Console.java:699) at com.arcadedb.console.Console.execute(Console.java:267) at com.arcadedb.console.Console.parse(Console.java:742) at com.arcadedb.console.Console.interactiveMode(Console.java:131) at com.arcadedb.console.Console.execute(Console.java:185) at com.arcadedb.console.Console.main(Console.java:148)

{test_db}> CREATE VERTEX TYPE GeneralizedInformationRoot IF NOT EXISTS +---------+--------------------------+ |NAME |VALUE | +---------+--------------------------+ |operation|create vertex type | |typeName |GeneralizedInformationRoot| +---------+--------------------------+ Command executed in 84ms {test_db}> {test_db}> CREATE PROPERTY GeneralizedInformationRoot.uniqueId IF NOT EXISTS STRING (MANDATORY TRUE, DEFAULT "uuid()", READONLY TRUE, NOTNULL TRUE) +------------+--------------------------+ |NAME |VALUE | +------------+--------------------------+ |operation |create property | |typeName |GeneralizedInformationRoot| |propertyName|uniqueId | |MANDATORY |true | |DEFAULT |uuid() | |READONLY |true | |NOTNULL |true | +------------+--------------------------+ Command executed in 45ms `

Actual behavior

<add here>

Steps to reproduce

<add here>

<An SQL script to reproduce the problem or a JUnit test case will increase **A LOT** the chance to have a quick fix>

gramian commented 8 months ago

Hi, The ArcadeDB console works line-by-line, so mulitline comments using /* ... */ do not work; for multiline comments each line needs to be started by --. The same is true in general, meaning also SQL statements cannot span multiple lines.

tmcsys commented 8 months ago

Am I reading this issue correctly?

Multiline comments ‘C’ and Java-type comments are fairly common in the SQL world. I was surprised to find that they did not work in ArcadeDB.

Would be fairly trivial for a line-by-line parser. Stop parsing at /; resume at /. ANTLR 101.

Comments below are for PostgreSQL Comments.

Syntax Using -- symbol

The syntax for creating a SQL comment in PostgreSQL using -- symbol is:

-- comment goes here In PostgreSQL, a comment started with -- symbol is similar to a comment starting with # symbol. When using the -- symbol, the comment must be at the end of a line in your SQL statement with a line break after it. This method of commenting can only span a single line within your SQL and must be at the end of the line.

Syntax Using / and / symbols

The syntax for creating a SQL comment in PostgreSQL using / and / symbols is:

/ comment goes here \/ In PostgreSQL, a comment that starts with /* symbol and ends with / and can be anywhere in your SQL statement. This method of commenting can span several lines within your SQL.

lvca commented 8 months ago

I see we support multi-line comments, but from the grammar, I see the starting sequence is /**. Check this:

https://github.com/orientechnologies/orientdb/blob/34aa90e7970dd52c9937ddd87ee501e08bd75568/core/src/main/grammar/OrientSQL.jjt#L73

lvca commented 8 months ago

Just tried both and work from studio:

/*
This is a test
*/

SELECT FROM Action
/**
This is a test
*/

SELECT FROM Action
gramian commented 8 months ago

I think the question was specifically about the console which AFAIK does not. I guess in a single line a /* ... */ would work as it would be handled by the query engine.

lvca commented 8 months ago

Ok, the issue with your script was with the create index command: ArcadeDB doesn't support hash indexes.

Testing this works:

/*The vertex class that represents a generalized root or conceptual staring point of a graph 
  (graphs don't have a starting point, this is just a conceptual start vertex for complete graph traversal).

  Usually two objects of this class will be created, one sits at the top of the hierarchy of the 
  ontology def model and another at the top of the hierarchy of the ontology data model.

  In addition, this class can be used by applications to declare conceptual application root vertices 
  in the data model. The name property of this class are unique and cannot be repeated.
*/

CREATE VERTEX TYPE GeneralizedInformationRoot IF NOT EXISTS;

CREATE PROPERTY GeneralizedInformationRoot.uniqueId IF NOT EXISTS STRING (MANDATORY TRUE, DEFAULT "uuid()", READONLY TRUE, NOTNULL TRUE);

CREATE PROPERTY GeneralizedInformationRoot.creationTime IF NOT EXISTS LONG (MANDATORY TRUE, DEFAULT sysdate().asLong(), READONLY TRUE, NOTNULL TRUE);

CREATE PROPERTY GeneralizedInformationRoot.name IF NOT EXISTS STRING (MANDATORY TRUE, READONLY TRUE, NOTNULL TRUE);

/*Create the edge class that represents a generalized connection between vertices. This class is generally 
  used to connection GeneralizedInformationRoot to other non-root verices as a starting point
*/
CREATE EDGE TYPE GeneralizedInformationConnection IF NOT EXISTS;

CREATE PROPERTY GeneralizedInformationConnection.uniqueId IF NOT EXISTS STRING (MANDATORY TRUE, DEFAULT "uuid()", READONLY TRUE, NOTNULL TRUE);

CREATE PROPERTY GeneralizedInformationConnection.creationTime IF NOT EXISTS LONG (MANDATORY TRUE, DEFAULT sysdate().asLong(), READONLY TRUE, NOTNULL TRUE);
lvca commented 8 months ago

@gramian reports the issue is still present with the console, reopening it.

gramian commented 8 months ago

@lvca Currently the console parses a script line-by-line. If the whole script is passed as one to the SQL engine, it can take care of the multiline comments. WDYT?

Points to consider:

lvca commented 3 months ago

The console accepts additional commands, like exit, list databases, and more. I think it's safer to state only the comments that are accepted in the console.