Skip to content

Customizing Identifier Case Rules

GSP Java uses IdentifierRules to control how SQL identifiers (table names, column names, etc.) are normalized and compared. Each database vendor has built-in defaults, but you can override them when your database instance uses non-default settings — for example, a case-sensitive SQL Server collation or Snowflake's QUOTED_IDENTIFIERS_IGNORE_CASE parameter.

Quick Start

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile.VendorFlags;
import gudusoft.gsqlparser.sqlenv.IdentifierRules;
import gudusoft.gsqlparser.sqlenv.IdentifierRules.CaseFold;
import gudusoft.gsqlparser.sqlenv.IdentifierRules.CaseCompare;

// Case-sensitive SQL Server (CS collation)
IdentifierProfile profile = IdentifierProfile
    .builder(EDbVendor.dbvmssql,
             new VendorFlags(0, "Latin1_General_CS_AS", false, false))
    .build();

Default Behavior

For most use cases, the built-in defaults are sufficient:

1
2
3
4
5
// Uses default rules for SQL Server (case-insensitive, CI collation)
IdentifierProfile profile = IdentifierProfile.forVendor(
    EDbVendor.dbvmssql,
    VendorFlags.defaults()
);

Each vendor's defaults follow the database's standard behavior:

Vendor Unquoted Identifiers Quoted Identifiers
Oracle, DB2, Snowflake Fold to UPPER, case-insensitive Preserve case, case-sensitive
PostgreSQL, Hive, Teradata Fold to lower, case-insensitive Preserve case, case-sensitive
SQL Server Preserve case, collation-based Preserve case, collation-based
MySQL Depends on lower_case_table_names Preserve case, case-insensitive
BigQuery Preserve case, table names sensitive / column names insensitive

Using the Builder for Custom Rules

The IdentifierProfile.Builder lets you start from vendor defaults and override specific object groups.

Override a Single Object Group

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// SQL Server with case-sensitive column comparison
IdentifierRules csSensitive = new IdentifierRules(
    CaseFold.NONE,           // preserve original case
    CaseCompare.SENSITIVE,   // case-sensitive comparison
    CaseFold.NONE,
    CaseCompare.SENSITIVE
);

IdentifierProfile profile = IdentifierProfile
    .builder(EDbVendor.dbvmssql)
    .withColumnRules(csSensitive)   // override column rules only
    .build();
// NAME_GROUP and ROUTINE_GROUP still use SQL Server defaults

Override All Object Groups

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
IdentifierRules custom = new IdentifierRules(
    CaseFold.LOWER,
    CaseCompare.INSENSITIVE,
    CaseFold.NONE,
    CaseCompare.SENSITIVE
);

IdentifierProfile profile = IdentifierProfile
    .builder(EDbVendor.dbvmssql)
    .withNameRules(custom)       // catalog, schema, table, view
    .withColumnRules(custom)     // column
    .withRoutineRules(custom)    // function, procedure
    .build();

Use VendorFlags with the Builder

VendorFlags control vendor-specific parameters that affect default rule selection:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// MySQL on Linux (lower_case_table_names = 0, case-sensitive tables)
IdentifierProfile mysqlProfile = IdentifierProfile
    .builder(EDbVendor.dbvmysql,
             new VendorFlags(0, null, false, false))
    .build();

// Snowflake with QUOTED_IDENTIFIERS_IGNORE_CASE = true
IdentifierProfile sfProfile = IdentifierProfile
    .builder(EDbVendor.dbvsnowflake,
             new VendorFlags(0, null, false, true))
    .build();

Derive a Variant from an Existing Profile

Use toBuilder() to create a modified copy without re-specifying everything:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
IdentifierProfile base = IdentifierProfile.forVendor(
    EDbVendor.dbvoracle, VendorFlags.defaults());

// Same as Oracle, but with case-sensitive column comparison
IdentifierProfile variant = base.toBuilder()
    .withColumnRules(new IdentifierRules(
        CaseFold.UPPER,
        CaseCompare.SENSITIVE,
        CaseFold.NONE,
        CaseCompare.SENSITIVE))
    .build();

Common Scenarios

SQL Server with Case-Sensitive Collation

When your SQL Server instance uses a _CS_ collation (e.g., Latin1_General_CS_AS), identifiers are case-sensitive. Pass the collation name via VendorFlags:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
VendorFlags flags = new VendorFlags(
    0,                            // MySQL param (unused for MSSQL)
    "Latin1_General_CS_AS",       // SQL Server collation
    false,                        // Redshift param
    false                         // Snowflake param
);

IdentifierProfile profile = IdentifierProfile
    .builder(EDbVendor.dbvmssql, flags)
    .build();

// The builder detects _CS_ in the collation name and automatically
// uses CaseCompare.SENSITIVE instead of COLLATION_BASED

Redshift with Case-Sensitive Identifiers

1
2
3
4
5
VendorFlags flags = new VendorFlags(0, null, true, false);

IdentifierProfile profile = IdentifierProfile
    .builder(EDbVendor.dbvredshift, flags)
    .build();

Fully Custom Rules for an Unsupported Vendor Configuration

If the built-in vendor defaults don't match your environment, you can override everything:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
IdentifierRules myRules = new IdentifierRules(
    CaseFold.NONE,              // don't fold unquoted
    CaseCompare.SENSITIVE,      // case-sensitive unquoted
    CaseFold.NONE,              // don't fold quoted
    CaseCompare.INSENSITIVE     // case-insensitive quoted
);

IdentifierProfile profile = IdentifierProfile
    .builder(EDbVendor.dbvmssql)   // still need a vendor for parsing
    .withNameRules(myRules)
    .withColumnRules(myRules)
    .withRoutineRules(myRules)
    .build();

Object Groups

IdentifierProfile organizes rules into three groups. Some databases use different rules for different identifier types:

ObjectGroup Covers
NAME_GROUP Catalog, schema, table, view, procedure, trigger
COLUMN_GROUP Column names
ROUTINE_GROUP Function names

For example, BigQuery uses case-sensitive table names but case-insensitive column names. MySQL column names are always case-insensitive regardless of lower_case_table_names.

API Reference

IdentifierProfile

Method Description
forVendor(vendor, flags) Create profile with vendor defaults (unchanged, backwards-compatible)
builder(vendor) Create builder seeded from vendor defaults with VendorFlags.defaults()
builder(vendor, flags) Create builder seeded from vendor defaults with specified flags
toBuilder() Create builder pre-populated from this profile's current state
getRules(objectType) Get rules for a specific SQL object type
getVendor() Get the database vendor
getFlags() Get the vendor flags
getFingerprint() Cache-invalidation fingerprint (includes vendor, flags, and effective rules)

IdentifierProfile.Builder

Method Description
withFlags(flags) Replace VendorFlags; re-seeds default rules while preserving explicit overrides
withRules(group, rules) Override rules for a specific object group
withNameRules(rules) Convenience: override NAME_GROUP rules
withColumnRules(rules) Convenience: override COLUMN_GROUP rules
withRoutineRules(rules) Convenience: override ROUTINE_GROUP rules
build() Build an immutable IdentifierProfile

IdentifierRules

Field Type Description
unquotedFold CaseFold How unquoted identifiers are normalized (NONE, UPPER, LOWER)
unquotedCompare CaseCompare How unquoted identifiers are compared (SENSITIVE, INSENSITIVE, COLLATION_BASED)
quotedFold CaseFold How quoted identifiers are normalized
quotedCompare CaseCompare How quoted identifiers are compared

IdentifierRules is an immutable value object with proper equals() and hashCode() semantics.

Thread Safety

  • IdentifierRules is immutable and safe to share across threads.
  • IdentifierProfile is immutable after construction and safe to share.
  • Builder is NOT thread-safe — use it from a single thread, then share the built profile.

Migration Notes

Existing code using IdentifierProfile.forVendor() continues to work with zero changes. The builder API is purely additive.

1
2
3
// This still works exactly as before
IdentifierProfile profile = IdentifierProfile.forVendor(
    EDbVendor.dbvmssql, VendorFlags.defaults());