+
Skip to content

Identifier case transform #175

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 5 commits into from
Jul 25, 2020
Merged

Identifier case transform #175

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
b90061b
init identifier case config
vincentlauvlwj Jul 18, 2020
9c6dd04
Merge branch 'v3.1.x' into feature/identifier-case-transform
vincentlauvlwj Jul 18, 2020
e6a8190
add param: always quote identifiers
vincentlauvlwj Jul 19, 2020
92baecc
transform identifier case
vincentlauvlwj Jul 19, 2020
6080ccc
test always quote identifiers
vincentlauvlwj Jul 19, 2020
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
141 changes: 126 additions & 15 deletions ktorm-core/src/main/kotlin/me/liuwj/ktorm/database/Database.kt
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -96,17 +96,35 @@ import javax.sql.DataSource
* }
* ```
* More details about SQL DSL, see [Query], about sequence APIs, see [EntitySequence].
*
* @property transactionManager the transaction manager used to manage connections and transactions.
* @property dialect the dialect, auto detects an implementation by default using JDK [ServiceLoader] facility.
* @property logger the logger used to output logs, auto detects an implementation by default.
* @property exceptionTranslator function used to translate SQL exceptions so as to rethrow them to users.
*/
class Database(

/**
* The transaction manager used to manage connections and transactions.
*/
val transactionManager: TransactionManager,

/**
* The dialect, auto detects an implementation by default using JDK [ServiceLoader] facility.
*/
val dialect: SqlDialect = detectDialectImplementation(),

/**
* The logger used to output logs, auto detects an implementation by default.
*/
val logger: Logger = detectLoggerImplementation(),
val exceptionTranslator: ((SQLException) -> Throwable)? = null

/**
* Function used to translate SQL exceptions so as to rethrow them to users.
*/
val exceptionTranslator: ((SQLException) -> Throwable)? = null,

/**
* Whether we need to always quote SQL identifiers in the generated SQLs.
*
* @since 3.1.0
*/
val alwaysQuoteIdentifiers: Boolean = false
) {
/**
* The URL of the connected database.
Expand Down Expand Up @@ -143,8 +161,65 @@ class Database(
*/
lateinit var extraNameCharacters: String private set

/**
* Whether this database threats mixed case unquoted SQL identifiers as case sensitive and as a result
* stores them in mixed case.
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Typo: s/threats/treats

*/
var supportsMixedCaseIdentifiers: Boolean = false
private set

/**
* Whether this database treats mixed case unquoted SQL identifiers as case insensitive and
* stores them in mixed case.
*/
var storesMixedCaseIdentifiers: Boolean = false
private set

/**
* Whether this database treats mixed case unquoted SQL identifiers as case insensitive and
* stores them in upper case.
*/
var storesUpperCaseIdentifiers: Boolean = false
private set

/**
* Whether this database treats mixed case unquoted SQL identifiers as case insensitive and
* stores them in lower case.
*/
var storesLowerCaseIdentifiers: Boolean = false
private set

/**
* Whether this database treats mixed case quoted SQL identifiers as case sensitive and as a result
* stores them in mixed case.
*/
var supportsMixedCaseQuotedIdentifiers: Boolean = false
private set

/**
* Whether this database treats mixed case quoted SQL identifiers as case insensitive and
* stores them in mixed case.
*/
var storesMixedCaseQuotedIdentifiers: Boolean = false
private set

/**
* Whether this database treats mixed case quoted SQL identifiers as case insensitive and
* stores them in upper case.
*/
var storesUpperCaseQuotedIdentifiers: Boolean = false
private set

/**
* Whether this database treats mixed case quoted SQL identifiers as case insensitive and
* stores them in lower case.
*/
var storesLowerCaseQuotedIdentifiers: Boolean = false
private set

init {
fun Result<String?>.orEmpty() = getOrNull().orEmpty()
fun Result<Boolean>.orFalse() = getOrDefault(false)

useConnection { conn ->
val metadata = conn.metaData
Expand All @@ -155,6 +230,14 @@ class Database(
keywords = ANSI_SQL_2003_KEYWORDS + metadata.runCatching { sqlKeywords }.orEmpty().toUpperCase().split(',')
identifierQuoteString = metadata.runCatching { identifierQuoteString }.orEmpty().trim()
extraNameCharacters = metadata.runCatching { extraNameCharacters }.orEmpty()
supportsMixedCaseIdentifiers = metadata.runCatching { supportsMixedCaseIdentifiers() }.orFalse()
storesMixedCaseIdentifiers = metadata.runCatching { storesMixedCaseIdentifiers() }.orFalse()
storesUpperCaseIdentifiers = metadata.runCatching { storesUpperCaseIdentifiers() }.orFalse()
storesLowerCaseIdentifiers = metadata.runCatching { storesLowerCaseIdentifiers() }.orFalse()
supportsMixedCaseQuotedIdentifiers = metadata.runCatching { supportsMixedCaseQuotedIdentifiers() }.orFalse()
storesMixedCaseQuotedIdentifiers = metadata.runCatching { storesMixedCaseQuotedIdentifiers() }.orFalse()
storesUpperCaseQuotedIdentifiers = metadata.runCatching { storesUpperCaseQuotedIdentifiers() }.orFalse()
storesLowerCaseQuotedIdentifiers = metadata.runCatching { storesLowerCaseQuotedIdentifiers() }.orFalse()
}

if (logger.isInfoEnabled()) {
Expand Down Expand Up @@ -415,15 +498,22 @@ class Database(
*
* @param dialect the dialect, auto detects an implementation by default using JDK [ServiceLoader] facility.
* @param logger logger used to output logs, auto detects an implementation by default.
* @param alwaysQuoteIdentifiers whether we need to always quote SQL identifiers in the generated SQLs.
* @param connector the connector function used to obtain SQL connections.
* @return the new-created database object.
*/
fun connect(
dialect: SqlDialect = detectDialectImplementation(),
logger: Logger = detectLoggerImplementation(),
alwaysQuoteIdentifiers: Boolean = false,
connector: () -> Connection
): Database {
return Database(JdbcTransactionManager(connector), dialect, logger)
return Database(
transactionManager = JdbcTransactionManager(connector),
dialect = dialect,
logger = logger,
alwaysQuoteIdentifiers = alwaysQuoteIdentifiers
)
}

/**
Expand All @@ -432,14 +522,21 @@ class Database(
* @param dataSource the data source used to obtain SQL connections.
* @param dialect the dialect, auto detects an implementation by default using JDK [ServiceLoader] facility.
* @param logger logger used to output logs, auto detects an implementation by default.
* @param alwaysQuoteIdentifiers whether we need to always quote SQL identifiers in the generated SQLs.
* @return the new-created database object.
*/
fun connect(
dataSource: DataSource,
dialect: SqlDialect = detectDialectImplementation(),
logger: Logger = detectLoggerImplementation()
logger: Logger = detectLoggerImplementation(),
alwaysQuoteIdentifiers: Boolean = false
): Database {
return connect(dialect, logger) { dataSource.connection }
return Database(
transactionManager = JdbcTransactionManager { dataSource.connection },
dialect = dialect,
logger = logger,
alwaysQuoteIdentifiers = alwaysQuoteIdentifiers
)
}

/**
Expand All @@ -451,6 +548,7 @@ class Database(
* @param password the password of the database.
* @param dialect the dialect, auto detects an implementation by default using JDK [ServiceLoader] facility.
* @param logger logger used to output logs, auto detects an implementation by default.
* @param alwaysQuoteIdentifiers whether we need to always quote SQL identifiers in the generated SQLs.
* @return the new-created database object.
*/
fun connect(
Expand All @@ -459,13 +557,19 @@ class Database(
user: String? = null,
password: String? = null,
dialect: SqlDialect = detectDialectImplementation(),
logger: Logger = detectLoggerImplementation()
logger: Logger = detectLoggerImplementation(),
alwaysQuoteIdentifiers: Boolean = false
): Database {
if (driver != null && driver.isNotBlank()) {
Class.forName(driver)
}

return connect(dialect, logger) { DriverManager.getConnection(url, user, password) }
return Database(
transactionManager = JdbcTransactionManager { DriverManager.getConnection(url, user, password) },
dialect = dialect,
logger = logger,
alwaysQuoteIdentifiers = alwaysQuoteIdentifiers
)
}

/**
Expand All @@ -481,16 +585,23 @@ class Database(
* @param dataSource the data source used to obtain SQL connections.
* @param dialect the dialect, auto detects an implementation by default using JDK [ServiceLoader] facility.
* @param logger logger used to output logs, auto detects an implementation by default.
* @param alwaysQuoteIdentifiers whether we need to always quote SQL identifiers in the generated SQLs.
* @return the new-created database object.
*/
fun connectWithSpringSupport(
dataSource: DataSource,
dialect: SqlDialect = detectDialectImplementation(),
logger: Logger = detectLoggerImplementation()
logger: Logger = detectLoggerImplementation(),
alwaysQuoteIdentifiers: Boolean = false
): Database {
val transactionManager = SpringManagedTransactionManager(dataSource)
val exceptionTranslator = SQLErrorCodeSQLExceptionTranslator(dataSource)
return Database(transactionManager, dialect, logger) { exceptionTranslator.translate("Ktorm", null, it) }
val translator = SQLErrorCodeSQLExceptionTranslator(dataSource)
return Database(
transactionManager = SpringManagedTransactionManager(dataSource),
dialect = dialect,
logger = logger,
exceptionTranslator = { ex -> translator.translate("Ktorm", null, ex) },
alwaysQuoteIdentifiers = alwaysQuoteIdentifiers
)
}
}
}
37 changes: 33 additions & 4 deletions ktorm-core/src/main/kotlin/me/liuwj/ktorm/expression/SqlFormatter.kt
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -73,14 +73,43 @@ open class SqlFormatter(
}
}

open protected val String.quoted: String get() {
if (this.toUpperCase() in database.keywords || !this.isIdentifier) {
return "${database.identifierQuoteString}${this}${database.identifierQuoteString}".trim()
protected val String.quoted: String get() {
val shouldQuote = database.alwaysQuoteIdentifiers
|| !this.isIdentifier
|| this.toUpperCase() in database.keywords
|| this.isMixedCase && !database.supportsMixedCaseIdentifiers && database.supportsMixedCaseQuotedIdentifiers
Comment on lines +77 to +80
Copy link
Member Author

@vincentlauvlwj vincentlauvlwj Jul 19, 2020 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

@lyndsysimon We will quote an identifier when it meet any of these conditions:

  • The alwaysQuoteIdentifiers param is set to true.
  • The identifier is not valid.
  • The identifier is a SQL keyword.
  • The identifier is mixed case and the database doesn't support mixed case for unquoted identifiers but supports quoted ones.


if (shouldQuote) {
if (database.supportsMixedCaseQuotedIdentifiers) {
return "${database.identifierQuoteString}${this}${database.identifierQuoteString}"
} else {
if (database.storesUpperCaseQuotedIdentifiers) {
return "${database.identifierQuoteString}${this.toUpperCase()}${database.identifierQuoteString}"
}
if (database.storesLowerCaseQuotedIdentifiers) {
return "${database.identifierQuoteString}${this.toLowerCase()}${database.identifierQuoteString}"
}
return "${database.identifierQuoteString}${this}${database.identifierQuoteString}"
}
} else {
return this
if (database.supportsMixedCaseIdentifiers) {
return this
} else {
if (database.storesUpperCaseIdentifiers) {
return this.toUpperCase()
}
if (database.storesLowerCaseIdentifiers) {
return this.toLowerCase()
}
return this
}
}
}

protected val String.isMixedCase: Boolean get() {
return any { it.isUpperCase() } && any { it.isLowerCase() }
}

protected val String.isIdentifier: Boolean get() {
if (this.isEmpty()) {
return false
Expand Down
3 changes: 2 additions & 1 deletion ktorm-core/src/test/kotlin/me/liuwj/ktorm/BaseTest.kt
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -23,7 +23,8 @@ open class BaseTest {
database = Database.connect(
url = "jdbc:h2:mem:ktorm;DB_CLOSE_DELAY=-1",
driver = "org.h2.Driver",
logger = ConsoleLogger(threshold = LogLevel.TRACE)
logger = ConsoleLogger(threshold = LogLevel.TRACE),
alwaysQuoteIdentifiers = true
)

execSqlScript("init-data.sql")
Expand Down
14 changes: 7 additions & 7 deletions ktorm-core/src/test/kotlin/me/liuwj/ktorm/database/DatabaseTest.kt
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -29,14 +29,14 @@ class DatabaseTest : BaseTest() {

@Test
fun testKeywordWrapping() {
val configs = object : Table<Nothing>("t_config") {
val key = varchar("key").primaryKey()
val value = varchar("value")
val configs = object : Table<Nothing>("T_CONFIG") {
val key = varchar("KEY").primaryKey()
val value = varchar("VALUE")
}

database.useConnection { conn ->
conn.createStatement().use { statement ->
val sql = """create table t_config(`key` varchar(128) primary key, "value" varchar(128))"""
val sql = """CREATE TABLE T_CONFIG(KEY VARCHAR(128) PRIMARY KEY, VALUE VARCHAR(128))"""
statement.executeUpdate(sql)
}
}
Expand Down Expand Up @@ -76,9 +76,9 @@ class DatabaseTest : BaseTest() {
fun testRawSql() {
val names = database.useConnection { conn ->
val sql = """
select name from t_employee
where department_id = ?
order by id
select "name" from "t_employee"
where "department_id" = ?
order by "id"
"""

conn.prepareStatement(sql).use { statement ->
Expand Down
6 changes: 3 additions & 3 deletions ktorm-core/src/test/resources/drop-data.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,4 @@

drop table if exists t_department;
drop table if exists t_employee;
drop table if exists t_employee0;
drop table if exists "t_department";
drop table if exists "t_employee";
drop table if exists "t_employee0";
Loading
点击 这是indexloc提供的php浏览器服务，不要输入任何密码和下载