-
-
Notifications
You must be signed in to change notification settings - Fork 1.4k
Some problems with CREATE statements parsing. #1437
-
Hello!
This week I got the task of obtaining metadata for the SQLite database. But this is a pretty specific database that does not want to return me metadata in the form of a table or something else. It remains only to parse tables DDL. And then I was faced with the fact that half of our standard Chinook.db tables do not want to be parsed correctly.
Maybe you can help me to figure out, what can I Do with these problems.
net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "CREATE" "CREATE"
at line 1, column 1.
Was expecting one of:
"("
"ALTER"
"CALL"
"COMMENT"
"COMMIT"
"DECLARE"
"DELETE"
"DESCRIBE"
"DROP"
"EXEC"
"EXECUTE"
"EXPLAIN"
"GRANT"
"INSERT"
"MERGE"
"PURGE"
"RENAME"
"RESET"
"ROLLBACK"
"SAVEPOINT"
"SET"
"SHOW"
"TRUNCATE"
"UPDATE"
"UPSERT"
"USE"
"VALUES"
"WITH"
<K_SELECT>
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:190)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:63)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:38)
at org.jkiss.dbeaver.ext.sqlite.model.SQLiteMetaModel.getTableDDL(SQLiteMetaModel.java:75)
2021年12月09日 18_14_13-Interactive Demo — JSQLFormatter 0 1 9 documentation — Mozilla Firefox
2021年12月09日 18_14_55-Interactive Demo — JSQLFormatter 0 1 9 documentation — Mozilla Firefox
2021年12月09日 18_15_06-Interactive Demo — JSQLFormatter 0 1 9 documentation — Mozilla Firefox
2021年12月09日 18_15_21-Interactive Demo — JSQLFormatter 0 1 9 documentation — Mozilla Firefox
I think, that the main issue can be with square brackets. But a special option in the demo version didn't help...
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 4 comments 7 replies
-
@manticore-projects
Hello! I hope you'll give me a hand :)
Beta Was this translation helpful? Give feedback.
All reactions
-
Hello!
This week I got the task of obtaining metadata for the SQLite database.
Congrats! :-D
But this is a pretty specific database that does not want to return me metadata in the form of a table or something else. It remains only to > parse tables DDL. And then I was faced with the fact that half of our standard Chinook.db tables do not want to be parsed correctly.
Please share one or 2 sample DDLs with me and I will see what I can do.
At a first glance, I am not sure if we support quoting withing the CREATE TABLE
statement properly, but I would like to check this out.
Just share the ling from the JSQLFormatter UI please.
Beta Was this translation helpful? Give feedback.
All reactions
-
DDL examples:
CREATE TABLE [Album] ( [AlbumId] INTEGER NOT NULL, [Title] NVARCHAR(160) NOT NULL, [ArtistId] INTEGER NOT NULL, Column1 BLOB NULL, CONSTRAINT [PK_Album] PRIMARY KEY ([AlbumId]), FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId]) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE [Employee] ( [EmployeeId] INTEGER NOT NULL, [LastName] NVARCHAR(20) NOT NULL, [FirstName] NVARCHAR(20) NOT NULL, [Title] NVARCHAR(30), [ReportsTo] INTEGER, [BirthDate] DATETIME, [HireDate] DATETIME, [Address] NVARCHAR(70), [City] NVARCHAR(40), [State] NVARCHAR(40), [Country] NVARCHAR(40), [PostalCode] NVARCHAR(10), [Phone] NVARCHAR(24), [Fax] NVARCHAR(24), [Email] NVARCHAR(60), CONSTRAINT [PK_Employee] PRIMARY KEY ([EmployeeId]), FOREIGN KEY ([ReportsTo]) REFERENCES [Employee] ([EmployeeId]) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE dbeaver_corp_products_keys ( id INTEGER NOT NULL PRIMARY KEY, uk NOT NULL UNIQUE, name TEXT, CHECK (name LIKE "%dbeaver%") );
Beta Was this translation helpful? Give feedback.
All reactions
-
SELECT
m.name as table_name,
p.name as column_name
FROM
sqlite_master AS m
JOIN
pragma_table_info(m.name) AS p
ORDER BY
m.name,
p.cid
Yes, we use sqlite_master in our SQLite queries, but, unfortunately, it returns columns, indexes, tables, triggers, but not the keys.
Beta Was this translation helpful? Give feedback.
All reactions
-
Good evening.
The first 2 queries parse without any problem, when SquaredBracketQuotation
is activated. (I will fix the hiccup in the SQL Editor Panel soonest, the Error Markers are incorrect).
However, that last query is simply not supported by JSQLParser regarding:
uk NOT NULL UNIQUE
-- where is the column type?name TEXT,
-- quite sure thatname
is a reserved keyword and thus needs to be quotedCHECK (name LIKE "%dbeaver%")
--CHECK
is not allowed here
How many of those DDLs do you need to parse?
Beta Was this translation helpful? Give feedback.
All reactions
-
This works:
CREATE TABLE dbeaver_corp_products_keys ( id INTEGER NOT NULL PRIMARY KEY , uk VARCHAR (1) NOT NULL UNIQUE , name TEXT -- Unsupported: -- , CHECK (name LIKE "%dbeaver%") ) ;
Beta Was this translation helpful? Give feedback.
All reactions
-
o! yes! "uk" was without data type, thanks.
And I need to parse any DDLs. To show users the right key objects.
Thanks for the help. I will try to use SquaredBracketQuotation in my situation.
Beta Was this translation helpful? Give feedback.
All reactions
-
On your Database MetaData implementation, maybe have a look at SQLSheet which emulates an Excel Spreadsheet based database.
There I have built such JDBC compliant MetaData based on the Spread Sheets and Column information. You could take that as a template and add the Index/Foreign Key information accordingly.
https://github.com/panchmp/sqlsheet/blob/master/src/main/java/com/sqlsheet/XlsDatabaseMetaData.java
Beta Was this translation helpful? Give feedback.
All reactions
-
Beta Was this translation helpful? Give feedback.
All reactions
-
🚀 1