Menu ▾ ▴

#7 Handle database object names with special characters

Main_team
open
nobody
None
3
2016-02-24
2016-02-24
Vladimir Alarcon
No

Some RDBMS (most of them) allow the user to create object names (including schemas, tables, columns, constraints, views, procedures, triggers, and other) with special characters.

This happens more frequently with column names, but it can actually happen with tables, views also. It happens with less frequency on constraints, procedures, triggers, schemas as well as other less frequently used database objects.

At least the following cases need to be considered:

  1. Use of SQL standard reserved words (probably SQL2003).

  2. Use of RDBMS-specific reserved words.

  3. Use of lower case ASCII characters.

  4. Use of mixed case ASCII characters.

  5. Use of non-ASCII characters. This can be specially problematic, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character for getters and setters. Another option is to use the underscore symbol for this purpose (the only other valid character for identifiers in Java, apart from the alphanumeric characters). If a new escaping strategy is considered, it will need to be backward compatible to the existing codes. This needs to be studied.

Discussion

  • Vladimir Alarcon

    Vladimir Alarcon - 2016-02-24
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -1,8 +1,8 @@
 Some (most of them) RDBMS allow the user to create object names, including schemas, tables, columns, constraints, views, procedures, and other) with special names.

 By special I include at least the following cases:
-* Use of SQL (2003) standard reserved words.
-* Use of RDBMS-specific reserved words.
-* Use of lower case ASCII characters
-* Use of mixed case ASCII characters
-* Use of non-ASCII characters. This can be specially difficult, since the DAO java classes need to name the properties somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol "____" for this purpose.
+ 1. Use of SQL (2003) standard reserved words.
+ 2. Use of RDBMS-specific reserved words.
+ 3. Use of lower case ASCII characters
+ 4. Use of mixed case ASCII characters
+ 5. Use of non-ASCII characters. This can be specially difficult, since the DAO java classes need to name the properties somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol "____" for this purpose.
     

  • Vladimir Alarcon

    Vladimir Alarcon - 2016-02-24
    • summary: Handle database object names with special values --> Handle database object names with special characters
     

  • Vladimir Alarcon

    Vladimir Alarcon - 2016-02-24
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -1,8 +1,13 @@
 Some (most of them) RDBMS allow the user to create object names, including schemas, tables, columns, constraints, views, procedures, and other) with special names.

 By special I include at least the following cases:
- 1. Use of SQL (2003) standard reserved words.
- 2. Use of RDBMS-specific reserved words.
- 3. Use of lower case ASCII characters
- 4. Use of mixed case ASCII characters
- 5. Use of non-ASCII characters. This can be specially difficult, since the DAO java classes need to name the properties somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol "____" for this purpose.
+
+1. Use of SQL (2003) standard reserved words.
+
+2. Use of RDBMS-specific reserved words.
+
+3. Use of lower case ASCII characters
+
+4. Use of mixed case ASCII characters
+
+5. Use of non-ASCII characters. This can be specially difficult, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol for this purpose. A new escaping strategy would need to be backwards compatible somehow. This needs to be studied.
     

  • Vladimir Alarcon

    Vladimir Alarcon - 2016-02-24
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -10,4 +10,4 @@

 4. Use of mixed case ASCII characters

-5. Use of non-ASCII characters. This can be specially difficult, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol for this purpose. A new escaping strategy would need to be backwards compatible somehow. This needs to be studied.
+5. Use of non-ASCII characters. This can be specially problematic, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol for this purpose. A new escaping strategy would need to be backwards compatible somehow. This needs to be studied.
     

  • Vladimir Alarcon

    Vladimir Alarcon - 2016-02-24
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -2,12 +2,12 @@

 By special I include at least the following cases:

-1. Use of SQL (2003) standard reserved words.
+1. Use of SQL standard reserved words (probably SQL2003).

 2. Use of RDBMS-specific reserved words.

-3. Use of lower case ASCII characters
+3. Use of lower case ASCII characters.

-4. Use of mixed case ASCII characters
+4. Use of mixed case ASCII characters.

 5. Use of non-ASCII characters. This can be specially problematic, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol for this purpose. A new escaping strategy would need to be backwards compatible somehow. This needs to be studied.
     

  • Vladimir Alarcon

    Vladimir Alarcon - 2016-02-24
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -1,6 +1,8 @@
-Some (most of them) RDBMS allow the user to create object names, including schemas, tables, columns, constraints, views, procedures, and other) with special names.
+Some RDBMS (most of them) allow the user to create object names (including schemas, tables, columns, constraints, views, procedures, triggers, and other) with special characters. 

-By special I include at least the following cases:
+This happens more frequently with column names, but it can actually happen with tables, views also. It happens with less frequency on constraints, procedures, triggers, schemas as well as other less frequently used database objects.
+
+At least the following cases need to be considered:

 1. Use of SQL standard reserved words (probably SQL2003).

@@ -10,4 +12,4 @@

 4. Use of mixed case ASCII characters.

-5. Use of non-ASCII characters. This can be specially problematic, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol for this purpose. A new escaping strategy would need to be backwards compatible somehow. This needs to be studied.
+5. Use of non-ASCII characters. This can be specially problematic, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol for this purpose. If a new escaping strategy is considered, it will need to be backward compatible to the existing codes. This needs to be studied.
     

  • Vladimir Alarcon

    Vladimir Alarcon - 2016-02-24
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -12,4 +12,4 @@

 4. Use of mixed case ASCII characters.

-5. Use of non-ASCII characters. This can be specially problematic, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol for this purpose. If a new escaping strategy is considered, it will need to be backward compatible to the existing codes. This needs to be studied.
+5. Use of non-ASCII characters. This can be specially problematic, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol for this purpose (the only other valid character for identifiers in Java, apart from the alphanumeric characters). If a new escaping strategy is considered, it will need to be backward compatible to the existing codes. This needs to be studied.
     

  • Vladimir Alarcon

    Vladimir Alarcon - 2016-02-24
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -12,4 +12,4 @@

 4. Use of mixed case ASCII characters.

-5. Use of non-ASCII characters. This can be specially problematic, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character. Another option is to use the underscore symbol for this purpose (the only other valid character for identifiers in Java, apart from the alphanumeric characters). If a new escaping strategy is considered, it will need to be backward compatible to the existing codes. This needs to be studied.
+5. Use of non-ASCII characters. This can be specially problematic, since the DAO java classes need to name the properties (and their setters and getters) somehow, and the names will probably need to be escaped. Currently, Empusa escapes them using the "$" symbol (a valid Java ASCII character for identifiers), but it looks MyBatis does not allows the usage of this character for getters and setters. Another option is to use the underscore symbol for this purpose (the only other valid character for identifiers in Java, apart from the alphanumeric characters). If a new escaping strategy is considered, it will need to be backward compatible to the existing codes. This needs to be studied.
     

Log in to post a comment.