MSSQLServer compatibility enhancements #1481
Conversation
Ignore table hints (e.g. "SELECT * FROM table WITH (NOLOCK)"), that form a valid expression in SQL Server. Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017
Add support for `create table test(id int primary key identity)` statements, which are common in the SQLServer world.
Add a dedicated example for `MSSQLServer` compatibility mode, that shows all DBMS-specific features in a central, user-friendly place.
| } | ||
|
|
||
| private void discardWithTableHints() { | ||
| if (readIf("WITH")) { |
There was a problem hiding this comment.
Use readIf(WITH) instead, WITH is a keyword with own token type.
| } | ||
| if (database.getMode().useIdentityAsAutoIncrement) { | ||
| if (readIf("NOT")) { | ||
| read("NULL"); |
There was a problem hiding this comment.
-
readIf(NOT),read(NULL) -
I guess
column.setNullable(false);should be invoked here.
| do { | ||
| readExpression(); | ||
| } while (readIf(COMMA)); | ||
| read(CLOSE_PAREN); |
There was a problem hiding this comment.
Use } while (readIfMore(true));.
| } else if (readIf(EQUAL)){ | ||
| readExpression(); | ||
| } else { | ||
| throw getSyntaxError(); |
There was a problem hiding this comment.
Just use
} else {
read(EQUAL);
readExpression();
}| private void discardWithTableHints() { | ||
| if (readIf("WITH")) { | ||
| read(OPEN_PAREN); | ||
| if (!readIf(CLOSE_PAREN)) { |
| Statement stat = conn.createStatement(); | ||
|
|
||
| stat.execute("create table parent(id int primary key, name varchar(255))"); | ||
| stat.execute("create table child(id int primary key, parent_id int, name varchar(255), foreign key (parent_id) references public.parent(id))"); |
| assertSupportedSyntax(stat, "select * from parent p join child ch on ch.parent_id = p.id"); | ||
| assertSupportedSyntax(stat, "select * from parent p with(nolock) join child ch with(nolock) on ch.parent_id = p.id"); | ||
| assertSupportedSyntax(stat, "select * from parent p with(nolock) join child ch with(nolock, index = id) on ch.parent_id = p.id"); | ||
| assertSupportedSyntax(stat, "select * from parent p with(nolock) join child ch with(nolock, index(id, name)) on ch.parent_id = p.id"); |
| private void testDiscardTableHints() throws SQLException { | ||
| deleteDb("sqlserver"); | ||
|
|
||
| Connection conn = getConnection("sqlserver;MODE=MSSQLServer"); |
There was a problem hiding this comment.
Connection should be closed somewhere. The same problem in testUseIdentityAsAutoIncrementAlias().
Also it's better to delete database not only before execution of test case, but also after it. Actually you can do it in test() method and use shared connection in both test cases, there is no reason to use a separate connection in them now.
There was a problem hiding this comment.
FYI, I actually used TestCompatibilityOracle as a reference point (and still forgot to close the connections) - it deletes the db before each test only. Might be worth checking out.
| assertSupportedSyntax(stat, "create table test2 (id int primary key not null identity)"); | ||
| } | ||
|
|
||
| private void assertSupportedSyntax(Statement stat, String sql) { |
There was a problem hiding this comment.
This method is not needed, just run the SQL directly. If it fails, exception will be visible in log. Your method only hides the stack trace, that's not a good idea.
| @@ -0,0 +1,76 @@ | |||
| package org.h2.test.db; | |||
Adjust style and remove the `SQLServerSample` as it seems redundant with the unit test present.
|
Thanks for the comments @katzyn! I've applied some changes and replied to some questions above - please take another look. |
|
Please, provide a license statement as described here: It's better to post it to the mailing list: |
Extend dictionary with `discard`, `enhancements` and `nolock` terms.
|
I've posted a license statement to the mailing list, included a changelog entry, fixed the |
|
Thank you for your contribution! |
|
Can we get a release with this fix if there isn't any? |
4 participants
Provides several enhancements for
MSSQLServercompatibility mode, as described in #1338 (discarding "table hints" & treating "identity" as anauto_incrementalias).The code base changed significantly, since my original "hack", therefore the proposed implementation is brand new, although, the original idea didn't change at all.