Some problems with CREATE statements parsing.

[LonwoLonwo]

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)

I think, that the main issue can be with square brackets. But a special option in the demo version didn't help...

[LonwoLonwo]

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

[manticore-projects]

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.

[LonwoLonwo]

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

[LonwoLonwo]

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.

[manticore-projects]

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?

[manticore-projects]

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

[LonwoLonwo]

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.

[manticore-projects]

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

[manticore-projects]

Greetings.

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

Cheers