Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Some problems with CREATE statements parsing. #1437

Unanswered
LonwoLonwo asked this question in Q&A
Discussion options

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...

You must be logged in to vote

Replies: 4 comments 7 replies

Comment options

@manticore-projects
Hello! I hope you'll give me a hand :)

You must be logged in to vote
0 replies
Comment options

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.

You must be logged in to vote
7 replies
Comment options

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%")
);
Comment options

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.

Comment options

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:

  1. uk NOT NULL UNIQUE -- where is the column type?
  2. name TEXT, -- quite sure that name is a reserved keyword and thus needs to be quoted
  3. CHECK (name LIKE "%dbeaver%") -- CHECK is not allowed here

How many of those DDLs do you need to parse?

Comment options

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%")
)
;
Comment options

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.

Comment options

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

You must be logged in to vote
0 replies
Comment options

Greetings.

I have fixed the Online SQL Formatter, you can try your samples.

Cheers

You must be logged in to vote
0 replies
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Category
Q&A
Labels
None yet

AltStyle によって変換されたページ (->オリジナル) /