You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@nifi.apache.org by "nathluu (via GitHub)" <gi...@apache.org> on 2023/05/14 02:08:15 UTC
[GitHub] [nifi] nathluu opened a new pull request, #7245: NIFI-8831 Support using MSSQL as nifi registry relation database engine
nathluu opened a new pull request, #7245:
URL: https://github.com/apache/nifi/pull/7245
<!-- Licensed to the Apache Software Foundation (ASF) under one or more -->
<!-- contributor license agreements. See the NOTICE file distributed with -->
<!-- this work for additional information regarding copyright ownership. -->
<!-- The ASF licenses this file to You under the Apache License, Version 2.0 -->
<!-- (the "License"); you may not use this file except in compliance with -->
<!-- the License. You may obtain a copy of the License at -->
<!-- http://www.apache.org/licenses/LICENSE-2.0 -->
<!-- Unless required by applicable law or agreed to in writing, software -->
<!-- distributed under the License is distributed on an "AS IS" BASIS, -->
<!-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -->
<!-- See the License for the specific language governing permissions and -->
<!-- limitations under the License. -->
# Summary
[NIFI-8831](https://issues.apache.org/jira/browse/NIFI-8831)
# Tracking
Please complete the following tracking steps prior to pull request creation.
### Issue Tracking
- [x] [Apache NiFi Jira](https://issues.apache.org/jira/browse/NIFI) issue created
### Pull Request Tracking
- [x] Pull Request title starts with Apache NiFi Jira issue number, such as `NIFI-00000`
- [x] Pull Request commit message starts with Apache NiFi Jira issue number, as such `NIFI-00000`
### Pull Request Formatting
- [x] Pull Request based on current revision of the `main` branch
- [x] Pull Request refers to a feature branch with one commit containing changes
# Verification
Please indicate the verification steps performed prior to pull request creation.
### Build
- [ ] Build completed using `mvn clean install -P contrib-check`
- [ ] JDK 11
- [ ] JDK 17
### Licensing
- [ ] New dependencies are compatible with the [Apache License 2.0](https://apache.org/licenses/LICENSE-2.0) according to the [License Policy](https://www.apache.org/legal/resolved.html)
- [ ] New dependencies are documented in applicable `LICENSE` and `NOTICE` files
### Documentation
- [ ] Documentation formatting appears as expected in rendered files
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@nifi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [nifi] ChrisSamo632 commented on a diff in pull request #7245: NIFI-8831 Support using MSSQL as nifi registry relation database engine
Posted by "ChrisSamo632 (via GitHub)" <gi...@apache.org>.
ChrisSamo632 commented on code in PR #7245:
URL: https://github.com/apache/nifi/pull/7245#discussion_r1229022728
##########
nifi-registry/nifi-registry-core/nifi-registry-framework/src/main/java/org/apache/nifi/registry/security/authorization/database/DatabaseUserGroupProvider.java:
##########
@@ -121,7 +129,9 @@ public void checkInheritability(final String proposedFingerprint) throws Authori
@Override
public User addUser(final User user) throws AuthorizationAccessException {
Validate.notNull(user);
- final String sql = "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
+ final String sql = (databaseType instanceof SQLServerDatabaseType)
+ ? "INSERT INTO UGP_USER(IDENTIFIER, [IDENTITY]) VALUES (?, ?)"
+ : "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
Review Comment:
ANSI Mode would need to be enabled for MySQL - see https://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table/2889884#2889884, otherwise backticks would be necessary to escape column names of any reserved/keywords we're used in MySQL (I guess IDENTITY isn't such a word for existing supported DBs but is for MSSQL)
If we were to start requiring that mode to be set (I don't know whether other database types have similar settings) then document updates would be a good idea, but arguably this becomes a potentially breaking change for some users
Renaming the column to avoid keywords is another option, but again incompatible with existing deployments
I don't know which would be the preferred approach in that case. Maybe @bbende will have a preference
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@nifi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [nifi] nathluu commented on a diff in pull request #7245: NIFI-8831 Support using MSSQL as nifi registry relation database engine
Posted by "nathluu (via GitHub)" <gi...@apache.org>.
nathluu commented on code in PR #7245:
URL: https://github.com/apache/nifi/pull/7245#discussion_r1228835737
##########
nifi-registry/nifi-registry-core/nifi-registry-framework/src/main/java/org/apache/nifi/registry/security/authorization/database/DatabaseUserGroupProvider.java:
##########
@@ -121,7 +129,9 @@ public void checkInheritability(final String proposedFingerprint) throws Authori
@Override
public User addUser(final User user) throws AuthorizationAccessException {
Validate.notNull(user);
- final String sql = "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
+ final String sql = (databaseType instanceof SQLServerDatabaseType)
+ ? "INSERT INTO UGP_USER(IDENTIFIER, [IDENTITY]) VALUES (?, ?)"
+ : "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
Review Comment:
Hi @ChrisSamo632,
I tried your suggestion on branch **nathluu/registry-mssql-b** but it does not work.
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO UGP_USER(IDENTIFIER, "IDENTITY") VALUES (?, ?)]; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"IDENTITY") VALUES ('76137000-a35a-3bb2-94d7-2f5bac309242', 'CN=user2, OU=nifi')' at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1015)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1025)
at org.apache.nifi.registry.security.authorization.database.DatabaseUserGroupProvider.addUser(DatabaseUserGroupProvider.java:133)
at org.apache.nifi.registry.security.authorization.database.DatabaseUserGroupProvider.onConfigured(DatabaseUserGroupProvider.java:97)
at org.apache.nifi.registry.security.authorization.AuthorizerFactory.getAuthorizer(AuthorizerFactory.java:191)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@nifi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [nifi] bbende commented on a diff in pull request #7245: NIFI-8831 Support using MSSQL as nifi registry relation database engine
Posted by "bbende (via GitHub)" <gi...@apache.org>.
bbende commented on code in PR #7245:
URL: https://github.com/apache/nifi/pull/7245#discussion_r1193943130
##########
nifi-registry/nifi-registry-core/nifi-registry-framework/src/main/java/org/apache/nifi/registry/security/authorization/database/DatabaseUserGroupProvider.java:
##########
@@ -121,7 +129,9 @@ public void checkInheritability(final String proposedFingerprint) throws Authori
@Override
public User addUser(final User user) throws AuthorizationAccessException {
Validate.notNull(user);
- final String sql = "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
+ final String sql = (databaseType instanceof SQLServerDatabaseType)
+ ? "INSERT INTO UGP_USER(IDENTIFIER, [IDENTITY]) VALUES (?, ?)"
+ : "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
Review Comment:
In general I think the application shouldn't really be checking the type of database, with the exception of the Flyway stuff that sets up the DB. It is not clear to me what the different SQL here is doing, but can it be done in a general way that conforms to standard SQL?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@nifi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [nifi] exceptionfactory commented on pull request #7245: NIFI-8831 Support using MSSQL as nifi registry relation database engine
Posted by "exceptionfactory (via GitHub)" <gi...@apache.org>.
exceptionfactory commented on PR #7245:
URL: https://github.com/apache/nifi/pull/7245#issuecomment-1719937808
Thanks again for the work on this feature @nathluu. Based on the discussion, I created a new Jira issue [NIFI-12059](https://issues.apache.org/jira/browse/NIFI-12059) describing a general need for Registry framework adjustments, along with a potential solution. Once such a solution is in place, then we should be in a better position in implement support for additional database vendors, so I'm closing this pull request for now.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@nifi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [nifi] ChrisSamo632 commented on a diff in pull request #7245: NIFI-8831 Support using MSSQL as nifi registry relation database engine
Posted by "ChrisSamo632 (via GitHub)" <gi...@apache.org>.
ChrisSamo632 commented on code in PR #7245:
URL: https://github.com/apache/nifi/pull/7245#discussion_r1228740757
##########
nifi-registry/nifi-registry-core/nifi-registry-framework/src/main/java/org/apache/nifi/registry/security/authorization/database/DatabaseUserGroupProvider.java:
##########
@@ -121,7 +129,9 @@ public void checkInheritability(final String proposedFingerprint) throws Authori
@Override
public User addUser(final User user) throws AuthorizationAccessException {
Validate.notNull(user);
- final String sql = "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
+ final String sql = (databaseType instanceof SQLServerDatabaseType)
+ ? "INSERT INTO UGP_USER(IDENTIFIER, [IDENTITY]) VALUES (?, ?)"
+ : "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
Review Comment:
```suggestion
final String sql = "INSERT INTO UGP_USER(IDENTIFIER, \"IDENTITY\") VALUES (?, ?)";
```
Consider escaping with double quotes instead, which is the ANSI/ISO standard, so hopefully works across all supported database types
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@nifi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [nifi] nathluu commented on a diff in pull request #7245: NIFI-8831 Support using MSSQL as nifi registry relation database engine
Posted by "nathluu (via GitHub)" <gi...@apache.org>.
nathluu commented on code in PR #7245:
URL: https://github.com/apache/nifi/pull/7245#discussion_r1194519284
##########
nifi-registry/nifi-registry-core/nifi-registry-framework/src/main/java/org/apache/nifi/registry/security/authorization/database/DatabaseUserGroupProvider.java:
##########
@@ -121,7 +129,9 @@ public void checkInheritability(final String proposedFingerprint) throws Authori
@Override
public User addUser(final User user) throws AuthorizationAccessException {
Validate.notNull(user);
- final String sql = "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
+ final String sql = (databaseType instanceof SQLServerDatabaseType)
+ ? "INSERT INTO UGP_USER(IDENTIFIER, [IDENTITY]) VALUES (?, ?)"
+ : "INSERT INTO UGP_USER(IDENTIFIER, IDENTITY) VALUES (?, ?)";
Review Comment:
Because IDENTITY is a keyword in MSSQL but not on H2, mysql, mariaDB and postgresql so for MSSQL it must be enclosed by [].
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@nifi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [nifi] exceptionfactory closed pull request #7245: NIFI-8831 Support using MSSQL as nifi registry relation database engine
Posted by "exceptionfactory (via GitHub)" <gi...@apache.org>.
exceptionfactory closed pull request #7245: NIFI-8831 Support using MSSQL as nifi registry relation database engine
URL: https://github.com/apache/nifi/pull/7245
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@nifi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org