Skip to content

Identifier names, explained

Tables, columns, schemas and other database objects have names. In SQL, one name can be written in many ways: orders, ORDERS, "ORDERS", [orders], `orders`. Sometimes two spellings name the same object. Sometimes they name two different objects. The answer depends on the database, on the kind of object, and sometimes on a database setting.

GSP Java has one name service that knows these rules for every database that GSP supports. It answers four questions:

Are these two names the same object? Which key should I use for this name in a map? How does the database store this name? How do I write this name back into SQL?

This page explains the name service, IdentifierService, and the APIs that work with it. Each section shows a small SQL example, the Java code, and the real output of that code. The complete API reference is at the end of the page.

Who this page is for

You build your own application on GSP Java. Your code compares, stores, looks up or prints the names of tables, columns and other database objects. This page tells you which API to call, what it does, and what you need to know about database names to use it well. You do not need to know how GSP works inside.

Verified against
GSP Java version 4.2.10 (TBaseType.versionid), released 2026-09-24
Page last verified 2026-09-24
Samples 19 Java programs, each compiled and run against the 4.2.10 jar
Java versions the samples give the same output on JDK 8, 17 and 21

Every block of program output on this page was copied from a real run of that build. None of it is written by hand. See Refreshing this page for how to bring the page forward to a newer release.

To run a sample, put the GSP jar on the class path (on Windows, use ; instead of : between the entries). The lineage sample in section 9.4 also needs the JAXB API jar (javax.xml.bind):

1
2
javac -cp gsqlparser-4.2.10.jar SameName.java
java  -cp gsqlparser-4.2.10.jar:. SameName

1. What you need to know about names

1.1 Identifiers and qualified names

  • An identifier is the name of a database object as it appears in SQL. Tables, views, columns, schemas, databases, functions, procedures and aliases all have identifiers. This page also says simply name.
  • A qualified name has several parts with dots between them: hr.emp (schema and table), sales.dbo.orders (database, schema and table), e.ename (table alias and column). Each part is called a segment.
  • A simple name has only one segment, for example emp.

1.2 Unquoted and quoted names

You can write a name in two ways:

  • Unquoted. The SQL standard calls this a regular identifier: Store, order_date. It can contain only letters, digits and a few other characters such as _. In most databases it cannot be a reserved word such as DATE.
  • Quoted. The SQL standard calls this a delimited identifier. The name is written between two delimiters, which are special quote characters: "Order Date", [Order Date], `Order Date`. A quoted name can contain spaces, dots and other characters, and it can be a reserved word.

The delimiters are not part of the name. "Order Date" names an object called Order Date. To put the closing delimiter inside a quoted name, you write it twice: "a""b" is the name a"b, and [a]]b] is the name a]b. BigQuery uses a backslash instead: `a\`b`.

Each database has its own delimiters. This table shows the delimiters that GSP 4.2.10 reads, and the delimiter that GSP writes when it adds one. The first column shows the EDbVendor constants that you pass to GSP.

EDbVendor GSP reads GSP writes
dbvoracle, dbvdb2, dbvsnowflake, dbvredshift, dbvteradata, dbvpresto, dbvtrino, dbvvertica, dbvgreenplum, dbvgaussdb, dbvduckdb, dbvhana, dbvnetezza, dbvexasol, dbvfirebird, dbvdameng, dbvinformix, dbvopenedge, dbvansi, dbvgeneric, dbvodbc "x" "x"
dbvpostgresql, dbvedb "x", U&"x" "x"
dbvmssql, dbvazuresql [x], "x" [x]
dbvaccess [x], "x" [x]
dbvmysql, dbvoceanbase `x` (also "x" when ANSI_QUOTES is on, see 8.1) `x`
dbvdoris, dbvstarrocks, dbvbigquery, dbvhive, dbvimpala, dbvflink, dbvcouchbase `x` `x`
dbvsparksql, dbvdatabricks, dbvathena `x`, "x" `x`
dbvclickhouse "x", `x` "x"
dbvsqlite "x", [x], `x` "x"
dbvsybase, dbvsybasease, dbvsybaseiq, dbvsqlanywhere "x", [x] "x"
dbvdax table names 'x', column names [x] the same
dbvmdx [x] [x]
dbvpowerquery #"x" #"x"
dbvsoql none: SOQL has no quoted names the name without delimiters

For a SQL Server or Azure SQL column, GSP also reads the old alias form 'x' (as in SELECT a AS 'total') as the name x. GSP only reads this form. It never writes it.

1.3 Case folding

Many databases change the letters of an unquoted name to one case before they store it. This is called case folding. We say that the database folds the name to upper case or to lower case. A quoted name is usually stored exactly as it is written.

1
2
3
4
5
6
-- Oracle
CREATE TABLE Store   (id NUMBER);   -- stored as STORE
CREATE TABLE "Store" (id NUMBER);   -- stored as Store: a second, different table
SELECT * FROM store;                -- finds STORE
SELECT * FROM "STORE";              -- finds STORE
SELECT * FROM "Store";              -- finds Store
  • Oracle, DB2, Snowflake and the SQL standard fold unquoted names to upper case.
  • PostgreSQL, Redshift, Hive and many others fold unquoted names to lower case.
  • SQL Server, ClickHouse and BigQuery do not fold names, and MySQL does not fold column names. They keep the case that you wrote.

1.4 Case-sensitive, case-insensitive and collation

The second question is how a database compares two names:

  • Case-sensitive: Orders and orders are two different names. ClickHouse and Flink work like this, and so do BigQuery table names.
  • Case-insensitive: Orders and orders are the same name. MySQL column names, Hive names and BigQuery column names work like this.
  • Collation-based: SQL Server and Azure SQL compare names with the database collation. A collation is a set of rules for sorting and comparing text. The default collation, SQL_Latin1_General_CP1_CI_AS, is case-insensitive (CI). A collation such as Latin1_General_CS_AS is case-sensitive (CS).

In a database that folds names, the fold decides most answers. Oracle folds store and STORE to STORE, so they are one name. "Store" stays Store, so it is a different name.

1.5 The rules GSP uses

For each database, GSP keeps a rule with four parts: how an unquoted name folds, how an unquoted name is compared, how a quoted name folds, and how a quoted name is compared. The class IdentifierRules holds one rule. The class IdentifierProfile holds all the rules of one database. You can print them:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile;
import gudusoft.gsqlparser.sqlenv.IdentifierRules;

public class ShowRules {
    public static void main(String[] args) {
        EDbVendor[] vendors = {
            EDbVendor.dbvoracle, EDbVendor.dbvpostgresql, EDbVendor.dbvmssql,
            EDbVendor.dbvmysql, EDbVendor.dbvbigquery, EDbVendor.dbvdatabricks
        };
        for (EDbVendor vendor : vendors) {
            IdentifierProfile profile = IdentifierProfile.forVendor(
                    vendor, IdentifierProfile.VendorFlags.defaults());
            IdentifierRules tables  = profile.getRules(ESQLDataObjectType.dotTable);
            IdentifierRules columns = profile.getRules(ESQLDataObjectType.dotColumn);
            System.out.println(vendor);
            System.out.println("  table : " + tables);
            System.out.println("  column: " + columns);
        }
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
dbvoracle
  table : IdentifierRules{unquoted=UPPER/INSENSITIVE, quoted=NONE/SENSITIVE}
  column: IdentifierRules{unquoted=UPPER/INSENSITIVE, quoted=NONE/SENSITIVE}
dbvpostgresql
  table : IdentifierRules{unquoted=LOWER/INSENSITIVE, quoted=NONE/SENSITIVE}
  column: IdentifierRules{unquoted=LOWER/INSENSITIVE, quoted=NONE/SENSITIVE}
dbvmssql
  table : IdentifierRules{unquoted=NONE/COLLATION_BASED, quoted=NONE/COLLATION_BASED}
  column: IdentifierRules{unquoted=NONE/COLLATION_BASED, quoted=NONE/COLLATION_BASED}
dbvmysql
  table : IdentifierRules{unquoted=LOWER/INSENSITIVE, quoted=NONE/INSENSITIVE}
  column: IdentifierRules{unquoted=NONE/INSENSITIVE, quoted=NONE/INSENSITIVE}
dbvbigquery
  table : IdentifierRules{unquoted=NONE/SENSITIVE, quoted=NONE/SENSITIVE}
  column: IdentifierRules{unquoted=NONE/INSENSITIVE, quoted=NONE/INSENSITIVE}
dbvdatabricks
  table : IdentifierRules{unquoted=LOWER/INSENSITIVE, quoted=NONE/INSENSITIVE}
  column: IdentifierRules{unquoted=LOWER/INSENSITIVE, quoted=NONE/INSENSITIVE}

Read the first rule like this: in Oracle, an unquoted table name folds to upper case (UPPER) and the comparison ignores case (INSENSITIVE). A quoted table name keeps its case (NONE) and the comparison respects case (SENSITIVE).

These are the rules of every database in GSP 4.2.10, with the default settings described in section 8.1:

EDbVendor Unquoted name: fold / compare Quoted name: fold / compare
dbvoracle, dbvdb2, dbvsnowflake, dbvhana, dbvnetezza, dbvexasol, dbvfirebird, dbvdameng, dbvansi, dbvgeneric, dbvodbc UPPER / INSENSITIVE NONE / SENSITIVE
dbvpostgresql, dbvgreenplum, dbvgaussdb, dbvedb, dbvinformix, dbvopenedge, dbvsoql LOWER / INSENSITIVE NONE / SENSITIVE
dbvredshift LOWER / INSENSITIVE LOWER / INSENSITIVE
dbvpresto, dbvtrino, dbvathena, dbvvertica LOWER / INSENSITIVE NONE / SAME_AS_UNQUOTED (that is, INSENSITIVE)
dbvhive, dbvsparksql, dbvimpala, dbvdatabricks, dbvteradata, dbvduckdb, dbvsqlite, dbvsybase, dbvsybasease, dbvsybaseiq, dbvsqlanywhere, dbvaccess, dbvdax, dbvmdx LOWER / INSENSITIVE NONE / INSENSITIVE
dbvmssql, dbvazuresql NONE / COLLATION_BASED NONE / COLLATION_BASED
dbvmysql, dbvoceanbase, dbvdoris, dbvstarrocks: table, database and other names LOWER / INSENSITIVE NONE / INSENSITIVE
dbvmysql, dbvoceanbase, dbvdoris, dbvstarrocks: column, function and procedure names NONE / INSENSITIVE NONE / INSENSITIVE
dbvbigquery: table, dataset, project, function and procedure names NONE / SENSITIVE NONE / SENSITIVE
dbvbigquery: column names NONE / INSENSITIVE NONE / INSENSITIVE
dbvclickhouse, dbvcouchbase, dbvflink, dbvpowerquery NONE / SENSITIVE NONE / SENSITIVE

Notes:

  • Informix, OpenEdge and SOQL use the same rule as PostgreSQL.
  • For Sybase (dbvsybase, dbvsybasease), GSP assumes a server with a case-insensitive sort order. A server with a binary (case-sensitive) sort order needs your own rule (section 8.3).
  • Some rows depend on a database setting: MySQL lower_case_table_names, the SQL Server collation, Snowflake QUOTED_IDENTIFIERS_IGNORE_CASE and Redshift enable_case_sensitive_identifier. Section 8 shows how to tell GSP the setting of your database.

1.6 Object types

Most name APIs take an ESQLDataObjectType value: the kind of object that the name refers to. The kind matters because some databases use different rules for different kinds of objects. A MySQL table name and a MySQL column name follow different rules, and so do BigQuery table and column names. In DAX, even the delimiter depends on the kind of object.

Pass the kind of the object that the name refers to, not the place where you found the name. Compare a table alias as dotTable and a column alias as dotColumn. A view is also a dotTable.

GSP puts the object types into three groups. By default, all types in one group use the same rule:

Group (IdentifierProfile.ObjectGroup) Object types (ESQLDataObjectType)
NAME_GROUP dotCatalog (a database, or a BigQuery project), dotSchema, dotTable, dotTrigger, dotSynonyms, dotSequence, dotIndex, dotConstraint, dotServer, dotDblink, dotDataType, dotParameter, dotUnknown
COLUMN_GROUP dotColumn
ROUTINE_GROUP dotFunction, dotProcedure, dotOraclePackage, dotRoutine

1.7 SQL text and stored names

A name reaches your program in one of two forms:

  • SQL text. The name as it is written in a SQL statement, with its delimiters: emp, "Order Items", [Order Items]. GSP calls this the lexical form, IdentifierInputForm.SQL_LEXICAL.
  • Stored name. The name as the database keeps it in its catalog, that is, in its system tables such as Oracle ALL_TABLES or INFORMATION_SCHEMA.COLUMNS. A stored name has no delimiters, and its case is already final: EMP, Order Items. GSP calls this form IdentifierInputForm.CATALOG_STORED.

The same text can mean two different names in the two forms. In Oracle, the SQL text Emp means the stored name EMP. But the stored name Emp belongs to an object that you can only write as "Emp" in SQL. Always know which form you have. Section 10 and section 11 show the APIs that take the form as a parameter.

1.8 Display name and lookup spelling

A stored name is also the best display name, the text that you show to people. Show Order Date, not [Order Date]. This is what the database itself shows in its catalog, and GSP does not add delimiters to a display name.

To write a name back into SQL, you need a lookup spelling: SQL text that finds the object again. Order Date needs delimiters: "Order Date" in Oracle, [Order Date] in SQL Server. An Oracle column stored as Amount needs them too. Without delimiters, Amount folds to AMOUNT, and that is a different column.

Keep the display name and the lookup spelling as two different strings. Section 6 and section 7 show the APIs.

2. Which API do I need?

I want to ... Use Section
know if two simple names name the same object SQLUtil.sameName 3
compare names that can have dots (hr.emp) SQLUtil.compareIdentifier 4
use a name as a key in a HashMap or a HashSet SQLUtil.canonKey, which returns a CanonKey 5.1
use a qualified name as a key SQLUtil.qualifiedCanonKeyFromLexical, IdentifierService.qualifiedCanonKeyFromStored 4.2
remove duplicate names, or keep a set of names NameService.distinctCopy, NameService.createSet 5.2
get a name without delimiters and with the case folded IdentifierService.normalizeStatic 6.1
check, remove or add delimiters IdentifierCodec.isQuoted, decodeLexical, encodeStored 6.2
write correct SQL text from stored names SqlNameGenerator.generate 7
describe database settings such as the collation or lower_case_table_names IdentifierProfile, IdentifierProfile.VendorFlags, IdentifierProfile.Builder 8
use those settings in a parse or a lineage run TGSqlParser.setIdentifierProfile, Option.setIdentifierProfile 9
save the identity of a name in a file or a database, or share it with other programs IdentifierService.persistentKeyV1 10
match names from SQL with my own catalog IdentifierService.canonKeyOf, TSQLEnv.toLexical 11

SQLUtil.sameName, SQLUtil.compareIdentifier and SQLUtil.canonKey use the default rules of each database. When your database uses other settings, build an IdentifierProfile (section 8) and use the service of that profile.

3. Are two names the same? SQLUtil.sameName

SQLUtil.sameName(vendor, objectType, name1, name2) answers one question: do two simple names name the same object in this database? Pass each name exactly as it is written in the SQL text, with its delimiters. GSP reads the delimiters itself.

1
2
3
4
-- PostgreSQL
CREATE TABLE Store (id INT);   -- stored as store
SELECT * FROM "store";         -- finds it
SELECT * FROM "Store";         -- does not find it: "Store" is another name

The sample asks the same kind of question for several databases:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.util.SQLUtil;

public class SameName {
    static void check(EDbVendor vendor, ESQLDataObjectType type, String a, String b) {
        boolean same = SQLUtil.sameName(vendor, type, a, b);
        System.out.printf("%-14s %-10s %-10s %-10s -> %s%n", vendor, type, a, b, same);
    }

    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        ESQLDataObjectType column = ESQLDataObjectType.dotColumn;

        // Oracle: an unquoted name is stored in UPPER case
        check(EDbVendor.dbvoracle, table, "Store", "STORE");
        check(EDbVendor.dbvoracle, table, "Store", "\"STORE\"");
        check(EDbVendor.dbvoracle, table, "Store", "\"Store\"");

        // PostgreSQL: an unquoted name is stored in lower case
        check(EDbVendor.dbvpostgresql, table, "Store", "\"store\"");
        check(EDbVendor.dbvpostgresql, table, "Store", "\"Store\"");

        // SQL Server: the collation decides (default: case-insensitive)
        check(EDbVendor.dbvmssql, table, "[Store]", "store");

        // MySQL: table names depend on lower_case_table_names (default 1),
        // column names are always case-insensitive
        check(EDbVendor.dbvmysql, table, "`Orders`", "orders");
        check(EDbVendor.dbvmysql, column, "`Amount`", "AMOUNT");

        // BigQuery: table names are case-sensitive, column names are not
        check(EDbVendor.dbvbigquery, table, "Orders", "orders");
        check(EDbVendor.dbvbigquery, column, "Amount", "amount");

        // Databricks: back-ticks do not change the case rule
        check(EDbVendor.dbvdatabricks, table, "`Orders`", "orders");

        // ClickHouse: names are always case-sensitive
        check(EDbVendor.dbvclickhouse, column, "userId", "userid");
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
dbvoracle      dotTable   Store      STORE      -> true
dbvoracle      dotTable   Store      "STORE"    -> true
dbvoracle      dotTable   Store      "Store"    -> false
dbvpostgresql  dotTable   Store      "store"    -> true
dbvpostgresql  dotTable   Store      "Store"    -> false
dbvmssql       dotTable   [Store]    store      -> true
dbvmysql       dotTable   `Orders`   orders     -> true
dbvmysql       dotColumn  `Amount`   AMOUNT     -> true
dbvbigquery    dotTable   Orders     orders     -> false
dbvbigquery    dotColumn  Amount     amount     -> true
dbvdatabricks  dotTable   `Orders`   orders     -> true
dbvclickhouse  dotColumn  userId     userid     -> false

How GSP decides. For each name, GSP removes the delimiters and then applies the rule for that name's own quote state: it folds the name, or it ignores the case, or it uses the collation. The result is the canonical form of the name: one standard form that all spellings of the same name share. Two names are the same when their canonical forms are equal. Because every name goes through the same steps, the answers always agree with each other: when a equals b and b equals c, then a equals c. This makes the answers safe for maps and sets (section 5).

Things to know:

  • Pass one segment in each argument. For names with dots, use compareIdentifier (section 4).
  • Two null names are equal. A null name and a name that is not null are not equal. The vendor must not be null: GSP throws a NullPointerException.
  • sameName always uses the default rules of the vendor. IdentifierService.areEqualStatic(vendor, objectType, name1, name2) is the same method.

4. Qualified names

4.1 SQLUtil.compareIdentifier

SQLUtil.compareIdentifier(vendor, objectType, name1, name2) compares names that can have dots. It splits both names into segments and checks that they have the same number of segments. Then it compares each segment with the rule of its own kind: a schema segment with the schema rule, a column segment with the column rule, and so on.

1
2
3
4
-- Oracle
SELECT ename FROM hr.emp;          -- the table EMP in the schema HR
SELECT ename FROM "HR"."EMP";      -- the same table
SELECT ename FROM "hr"."emp";      -- a table emp in a schema hr: another object

objectType is the kind of the whole name. GSP finds the kind of each segment from the number of segments:

objectType 1 segment 2 segments 3 segments 4 segments
dotTable, and also dotFunction, dotProcedure, dotOraclePackage, dotTrigger, dotSynonyms table schema.table database.schema.table
dotColumn column table.column schema.table.column database.schema.table.column
dotSchema schema database.schema

MySQL, Teradata, Hive and Impala have no schema level. For them, two segments of a table name are database.table, and three segments of a column name are database.table.column.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.util.SQLUtil;

public class QualifiedNames {
    static void compare(EDbVendor vendor, ESQLDataObjectType type, String a, String b) {
        boolean same = SQLUtil.compareIdentifier(vendor, type, a, b);
        System.out.printf("%-10s %-10s %-22s %-24s -> %s%n", vendor, type, a, b, same);
    }

    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        ESQLDataObjectType column = ESQLDataObjectType.dotColumn;

        compare(EDbVendor.dbvoracle, table, "hr.emp", "HR.EMP");
        compare(EDbVendor.dbvoracle, table, "hr.emp", "\"HR\".\"EMP\"");
        compare(EDbVendor.dbvoracle, table, "hr.emp", "\"hr\".\"emp\"");
        compare(EDbVendor.dbvoracle, table, "hr.emp", "emp");
        compare(EDbVendor.dbvoracle, column, "hr.emp.ename", "HR.EMP.ENAME");
        compare(EDbVendor.dbvoracle, table, "\"A.B\"", "a.b");
        compare(EDbVendor.dbvmssql, table, "Sales.dbo.Orders", "[sales].[DBO].[orders]");
        compare(EDbVendor.dbvmssql, table, "Sales..Orders", "Sales.dbo.Orders");

        // sameName is for ONE segment. With a dotted name it can give a wrong answer:
        System.out.println();
        System.out.println("sameName          \"HR\".emp vs HR.EMP -> "
                + SQLUtil.sameName(EDbVendor.dbvoracle, table, "\"HR\".emp", "HR.EMP"));
        System.out.println("compareIdentifier \"HR\".emp vs HR.EMP -> "
                + SQLUtil.compareIdentifier(EDbVendor.dbvoracle, table, "\"HR\".emp", "HR.EMP"));
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
dbvoracle  dotTable   hr.emp                 HR.EMP                   -> true
dbvoracle  dotTable   hr.emp                 "HR"."EMP"               -> true
dbvoracle  dotTable   hr.emp                 "hr"."emp"               -> false
dbvoracle  dotTable   hr.emp                 emp                      -> false
dbvoracle  dotColumn  hr.emp.ename           HR.EMP.ENAME             -> true
dbvoracle  dotTable   "A.B"                  a.b                      -> false
dbvmssql   dotTable   Sales.dbo.Orders       [sales].[DBO].[orders]   -> true
dbvmssql   dotTable   Sales..Orders          Sales.dbo.Orders         -> false

sameName          "HR".emp vs HR.EMP -> false
compareIdentifier "HR".emp vs HR.EMP -> true

What the output shows:

  • Each segment keeps its own quote state. "HR"."EMP" is hr.emp, but "hr"."emp" is not.
  • hr.emp and emp are different. GSP never guesses a missing schema. If you know the default schema of the session, add it yourself (section 11).
  • In SQL Server, Sales..Orders has an empty middle segment. GSP does not replace it with dbo, because the real default schema depends on the user.
  • "A.B" is one name that contains a dot. It is not the qualified name a.b.
  • sameName gives a wrong answer for a qualified name. When a name can have dots, always use compareIdentifier.

4.2 Keys for qualified names, and some limits

SQLUtil.qualifiedCanonKeyFromLexical(vendor, objectType, name) returns a QualifiedCanonKey. This is a key for a qualified name in SQL text that you can use in a HashMap or a HashSet. It holds one CanonKey (section 5) for each segment, and the kind of the whole name. For a name in the stored form, call qualifiedCanonKeyFromStored(storedSegments, objectType) on an IdentifierService, or the static IdentifierService.qualifiedCanonKeyFromStoredStatic(vendor, objectType, storedSegments), with one list entry for each segment.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.QualifiedCanonKey;
import gudusoft.gsqlparser.sqlenv.QualifiedNameHierarchy;
import gudusoft.gsqlparser.util.SQLUtil;

import java.util.HashMap;
import java.util.Map;

public class QualifiedKeys {
    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;

        // 1. A map keyed by qualified table names (Oracle)
        Map<QualifiedCanonKey, String> owners = new HashMap<>();
        owners.put(SQLUtil.qualifiedCanonKeyFromLexical(EDbVendor.dbvoracle, table, "hr.emp"), "HR team");
        System.out.println(owners.get(SQLUtil.qualifiedCanonKeyFromLexical(
                EDbVendor.dbvoracle, table, "\"HR\".\"EMP\"")));
        System.out.println(owners.get(SQLUtil.qualifiedCanonKeyFromLexical(
                EDbVendor.dbvoracle, table, "hr.\"Emp\"")));

        // 2. What is inside a key
        QualifiedCanonKey key = SQLUtil.qualifiedCanonKeyFromLexical(
                EDbVendor.dbvoracle, ESQLDataObjectType.dotColumn, "hr.emp.ename");
        System.out.println(key.size() + " levels, leaf = " + key.getLeafKey());

        // 3. BigQuery: a whole path inside one pair of back-ticks
        String quotedPath = "`myproj.sales.Orders`";
        String plainPath  = "myproj.sales.Orders";
        System.out.println("compareIdentifier: " + SQLUtil.compareIdentifier(
                EDbVendor.dbvbigquery, table, quotedPath, plainPath));
        System.out.println("qualified keys   : " + SQLUtil.qualifiedCanonKeyFromLexical(
                EDbVendor.dbvbigquery, table, quotedPath).equals(
                SQLUtil.qualifiedCanonKeyFromLexical(EDbVendor.dbvbigquery, table, plainPath)));

        // 4. Object types without a name hierarchy
        ESQLDataObjectType sequence = ESQLDataObjectType.dotSequence;
        System.out.println("compareIdentifier(seq1, seq1): "
                + SQLUtil.compareIdentifier(EDbVendor.dbvoracle, sequence, "seq1", "seq1"));
        System.out.println("sameName(seq1, SEQ1)         : "
                + SQLUtil.sameName(EDbVendor.dbvoracle, sequence, "seq1", "SEQ1"));
        System.out.println("hasHierarchyMapping          : "
                + QualifiedNameHierarchy.hasHierarchyMapping(EDbVendor.dbvoracle, sequence));
    }
}

1
2
3
4
5
6
7
8
HR team
null
3 levels, leaf = CanonKey{dbvoracle/COLUMN_GROUP/ENAME}
compareIdentifier: false
qualified keys   : true
compareIdentifier(seq1, seq1): false
sameName(seq1, SEQ1)         : true
hasHierarchyMapping          : false
  • The map finds "HR"."EMP", because it is the same table as hr.emp. It does not find hr."Emp", which is another table.
  • getLeafKey() returns the key of the last segment, here the column ENAME.
  • A BigQuery path in one pair of back-ticks. BigQuery allows a whole path in one pair of back-ticks: `myproj.sales.Orders`. compareIdentifier does not split such a path, so it answers false. The qualified key splits it correctly. For BigQuery, compare qualified names by their keys.
  • Object types without a name hierarchy. compareIdentifier and the qualified keys know the name hierarchy of these object types only: dotCatalog, dotSchema, dotTable, dotColumn, dotFunction, dotProcedure, dotOraclePackage, dotTrigger and dotSynonyms. For every other type, for example dotSequence, dotIndex, dotConstraint or dotRoutine, compareIdentifier returns false even for two equal names, and qualifiedCanonKeyFromLexical throws an UnsupportedOperationException. When your code can get any object type, check first with QualifiedNameHierarchy.hasHierarchyMapping(vendor, objectType). For a simple name of such a type, use sameName.
  • A QualifiedCanonKey never fills in a missing segment. Keys with a different number of segments are never equal.

5. Keys for maps and sets

5.1 CanonKey

To put names into a HashMap or a HashSet, you need a key that is equal exactly when the names are the same object. SQLUtil.canonKey(vendor, objectType, name) returns such a key, a CanonKey. Two keys are equal exactly when sameName returns true for the two names.

Do not build keys yourself with toUpperCase() or toLowerCase(), or by removing the delimiters. This PostgreSQL script creates two tables, and a hand-made key mixes them up:

1
2
3
-- PostgreSQL
CREATE TABLE "Store" (id INT);   -- stored as Store
CREATE TABLE store (id INT);     -- stored as store: a second table

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.CanonKey;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierService;
import gudusoft.gsqlparser.util.SQLUtil;

import java.util.HashMap;
import java.util.Locale;
import java.util.Map;

public class NameKeys {
    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        ESQLDataObjectType column = ESQLDataObjectType.dotColumn;
        EDbVendor pg = EDbVendor.dbvpostgresql;

        // PostgreSQL:  CREATE TABLE "Store" (...);  CREATE TABLE store (...);
        // These are two different tables.

        // Wrong: strip the quotes and change the case yourself
        String quoted = "\"Store\"";
        String wrong1 = quoted.substring(1, quoted.length() - 1).toUpperCase(Locale.ROOT);
        String wrong2 = "store".toUpperCase(Locale.ROOT);
        System.out.println("hand-made keys: " + wrong1 + " / " + wrong2
                + " -> equal = " + wrong1.equals(wrong2));

        // Right: canonical keys
        Map<CanonKey, String> tables = new HashMap<>();
        tables.put(SQLUtil.canonKey(pg, table, "\"Store\""), "table created as \"Store\"");
        tables.put(SQLUtil.canonKey(pg, table, "store"), "table created as store");
        System.out.println("map size = " + tables.size());
        System.out.println("lookup STORE     -> " + tables.get(SQLUtil.canonKey(pg, table, "STORE")));
        System.out.println("lookup \"Store\"   -> " + tables.get(SQLUtil.canonKey(pg, table, "\"Store\"")));
        System.out.println("lookup \"STORE\"   -> " + tables.get(SQLUtil.canonKey(pg, table, "\"STORE\"")));

        // Case mapping in Java depends on the locale. toUpperCase() without an
        // argument uses the default locale of the JVM.
        @SuppressWarnings("deprecation")   // this constructor is deprecated on JDK 19+
        Locale turkish = new Locale("tr", "TR");
        System.out.println("\"title\".toUpperCase(turkish) = " + "title".toUpperCase(turkish));
        System.out.println("SQLUtil.sameName(title, TITLE) = "
                + SQLUtil.sameName(EDbVendor.dbvoracle, column, "title", "TITLE"));

        // keyForMap is text, not identity: it does not work for every vendor
        IdentifierService mssql = IdentifierService.defaultServiceFor(EDbVendor.dbvmssql);
        System.out.println("SQL Server keyForMap: " + mssql.keyForMap("Store", table)
                + " / " + mssql.keyForMap("[STORE]", table)
                + "   canonKey equal = " + mssql.canonKey("Store", table).equals(mssql.canonKey("[STORE]", table)));
        IdentifierService mysql = IdentifierService.defaultServiceFor(EDbVendor.dbvmysql);
        System.out.println("MySQL column keyForMap: " + mysql.keyForMap("Amount", column)
                + " / " + mysql.keyForMap("AMOUNT", column)
                + "   canonKey equal = " + mysql.canonKey("Amount", column).equals(mysql.canonKey("AMOUNT", column)));
    }
}

1
2
3
4
5
6
7
8
9
hand-made keys: STORE / STORE -> equal = true
map size = 2
lookup STORE     -> table created as store
lookup "Store"   -> table created as "Store"
lookup "STORE"   -> null
"title".toUpperCase(turkish) = TİTLE
SQLUtil.sameName(title, TITLE) = true
SQL Server keyForMap: Store / STORE   canonKey equal = true
MySQL column keyForMap: Amount / AMOUNT   canonKey equal = true

What the output shows:

  • The two hand-made keys are equal, so a map keeps only one of the two tables.
  • With CanonKey, the map has two entries. STORE finds the table store, because the unquoted STORE folds to store. "STORE" finds nothing, because no table is stored as STORE.
  • Case mapping in Java depends on the locale. String.toUpperCase() without an argument uses the default locale of the Java virtual machine, and in a Turkish locale title becomes TİTLE. A key built with toUpperCase() can therefore change when the same program runs on another computer. The name service does not depend on the locale or on the JDK version.
  • IdentifierService.keyForMap returns text, not identity. In SQL Server, and in every rule that ignores case without folding (MySQL column names, for example), one name can give two different texts. Use canonKey.

Rules for CanonKey:

  • A CanonKey is for use inside one running program only. Do not save it in a file or a database, and do not send it to another program. For that, use a persistent key (section 10).
  • A CanonKey records the vendor and the object group, but not the object type inside the group. A table key and a schema key with the same text can be equal (see the last line of the output in section 5.2). Use one map for each kind of object, or use a QualifiedCanonKey, which also records the object type.

5.2 Sets and lists of names: NameService

NameService has ready-made helpers that use the same rules as canonKey.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.NameService;
import gudusoft.gsqlparser.util.SQLUtil;

import java.util.Arrays;
import java.util.List;
import java.util.Set;

public class NameSets {
    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        NameService oracle = new NameService(EDbVendor.dbvoracle);

        List<String> fromScripts = Arrays.asList("emp", "EMP", "\"EMP\"", "\"Emp\"", "dept", "Dept");

        // Remove duplicates; the first spelling of each name is kept
        System.out.println("distinct : " + oracle.distinctCopy(table, fromScripts));

        // A Set that follows Oracle's rules for table names
        Set<String> seen = oracle.createSet(table);
        seen.addAll(fromScripts);
        System.out.println("size     : " + seen.size());
        System.out.println("has Emp  : " + seen.contains("Emp"));      // unquoted Emp is EMP
        System.out.println("has \"emp\": " + seen.contains("\"emp\""));  // a different table

        // Position of a name in a list
        System.out.println("indexOf \"DEPT\": " + oracle.indexOf(table, fromScripts, "\"DEPT\""));

        // A CanonKey does not record the object type inside a group:
        System.out.println("table EMP key == schema EMP key: "
                + SQLUtil.canonKey(EDbVendor.dbvoracle, table, "EMP").equals(
                  SQLUtil.canonKey(EDbVendor.dbvoracle, ESQLDataObjectType.dotSchema, "EMP")));
    }
}

1
2
3
4
5
6
distinct : [emp, "Emp", dept]
size     : 3
has Emp  : true
has "emp": false
indexOf "DEPT": 4
table EMP key == schema EMP key: true
  • distinctCopy removes duplicates and keeps the first spelling of each name.
  • createSet returns a Set<String> whose add, contains and remove use the rules of the database. It does not accept null.
  • indexOf works like List.indexOf, with the rules of the database.
  • new NameService(vendor) uses the default rules. NameService.forIdentifierService(service) uses the rules of any service, for example a service built from a profile (section 8).

6. Stored form, delimiters and display

6.1 The normalized form: normalizeStatic

IdentifierService.normalizeStatic(vendor, objectType, name) removes the delimiters from one segment and applies the fold of the name's quote state. For most databases, the result is the name as the database stores it, so it is also a good display name.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierService;

public class StoredForms {
    static void show(EDbVendor vendor, ESQLDataObjectType type, String sqlSpelling) {
        String normalized = IdentifierService.normalizeStatic(vendor, type, sqlSpelling);
        System.out.printf("%-14s %-10s %-10s -> %s%n", vendor, type, sqlSpelling, normalized);
    }

    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        ESQLDataObjectType column = ESQLDataObjectType.dotColumn;
        show(EDbVendor.dbvoracle, table, "Store");
        show(EDbVendor.dbvoracle, table, "\"Store\"");
        show(EDbVendor.dbvpostgresql, table, "Store");
        show(EDbVendor.dbvpostgresql, table, "\"Store\"");
        show(EDbVendor.dbvmssql, table, "[Store]");
        show(EDbVendor.dbvmysql, table, "Orders");
        show(EDbVendor.dbvmysql, column, "Amount");
        show(EDbVendor.dbvmysql, column, "AMOUNT");
        show(EDbVendor.dbvbigquery, table, "`Orders`");
        show(EDbVendor.dbvmssql, column, "[a]]b]");
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
dbvoracle      dotTable   Store      -> STORE
dbvoracle      dotTable   "Store"    -> Store
dbvpostgresql  dotTable   Store      -> store
dbvpostgresql  dotTable   "Store"    -> Store
dbvmssql       dotTable   [Store]    -> Store
dbvmysql       dotTable   Orders     -> orders
dbvmysql       dotColumn  Amount     -> Amount
dbvmysql       dotColumn  AMOUNT     -> AMOUNT
dbvbigquery    dotTable   `Orders`   -> Orders
dbvmssql       dotColumn  [a]]b]     -> a]b
  • The normalized form is not a key. The same object can give two different texts: the MySQL column Amount gives Amount, and AMOUNT gives AMOUNT. Use canonKey for keys.
  • normalizeStatic works on one segment. For a qualified name, call normalizeQualifiedName(name, objectType) on a service, for example on IdentifierService.defaultServiceFor(vendor).

6.2 Delimiters: IdentifierCodec

1
2
-- SQL Server
CREATE TABLE t ([a]]b] INT, [Order Date] DATE);   -- the columns a]b and Order Date

IdentifierCodec knows the delimiters of each database and how to escape them. It only adds and removes delimiters. It never changes the case of a name: folding is the job of normalize. Every method takes an IdentifierProfile and an object type. Get the default profile of a database with IdentifierProfile.forVendor(vendor, IdentifierProfile.VendorFlags.defaults()).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierCodec;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile;
import gudusoft.gsqlparser.util.SQLUtil;

public class QuoteCodec {
    static IdentifierProfile profile(EDbVendor vendor) {
        return IdentifierProfile.forVendor(vendor, IdentifierProfile.VendorFlags.defaults());
    }

    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        ESQLDataObjectType column = ESQLDataObjectType.dotColumn;
        IdentifierProfile oracle = profile(EDbVendor.dbvoracle);
        IdentifierProfile mssql = profile(EDbVendor.dbvmssql);
        IdentifierProfile mysql = profile(EDbVendor.dbvmysql);

        // Is the spelling a quoted (delimited) name?
        System.out.println(IdentifierCodec.isQuoted(oracle, column, "\"Amount\""));  // true
        System.out.println(IdentifierCodec.isQuoted(mysql, column, "\"Amount\""));   // false: a string in MySQL
        System.out.println(IdentifierCodec.isQuoted(mysql, column, "`Amount`"));     // true

        // SQL spelling -> stored name
        System.out.println(IdentifierCodec.decodeLexical(mssql, column, "[a]]b]"));      // a]b
        System.out.println(IdentifierCodec.decodeLexical(oracle, column, "\"a\"\"b\""));  // a"b

        // stored name -> SQL spelling (always delimited)
        System.out.println(IdentifierCodec.encodeStored(mssql, column, "a]b"));      // [a]]b]
        System.out.println(IdentifierCodec.encodeStored(oracle, column, "Amount"));  // "Amount"

        // A broken spelling is refused, not guessed
        try {
            IdentifierCodec.decodeLexical(mssql, table, "[a.b[");
        } catch (IdentifierCodec.MalformedIdentifierException e) {
            System.out.println("refused: " + e.getMessage());
        }

        // Display name and lookup spelling of the Oracle column created as "Amount"
        String display = IdentifierCodec.decodeLexical(oracle, column, "\"Amount\"");
        String lookup = IdentifierCodec.encodeStored(oracle, column, display);
        System.out.println("display = " + display + ", lookup = " + lookup);
        System.out.println("bare display finds it:  " + SQLUtil.sameName(EDbVendor.dbvoracle, column, display, "\"Amount\""));
        System.out.println("lookup spelling finds it: " + SQLUtil.sameName(EDbVendor.dbvoracle, column, lookup, "\"Amount\""));
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
true
false
true
a]b
a"b
[a]]b]
"Amount"
refused: Quoted identifier is not terminated by ']': [a.b[
display = Amount, lookup = "Amount"
bare display finds it:  false
lookup spelling finds it: true
Method What it does
isQuoted(profile, objectType, text) true when the text starts with a delimiter that this database reads for this kind of object. With the default MySQL settings, "Amount" is a string, not a name, so the answer is false.
decodeLexical(profile, objectType, sqlText) SQL text to stored name. It removes the delimiters and resolves escaped characters. Unquoted text comes back unchanged (not folded). Broken text such as [a.b[ throws IdentifierCodec.MalformedIdentifierException.
encodeStored(profile, objectType, storedName) Stored name to SQL text. It always adds delimiters, even when the name does not need them. (SOQL has no delimiters, so there the name comes back unchanged.)
isVendorValidSpelling(profile, objectType, sqlText) true when the characters and the delimiters are valid for this database. It does not check reserved words or length limits.
hasQuotedForm(profile, objectType) false only for a database without delimiters (SOQL).
requiresDelimiters(profile, objectType) true only when a name must always be delimited: DAX column names such as [Amount].

The last three lines of the output show the display name and the lookup spelling of an Oracle column created as "Amount" (section 1.8). The display name Amount, written without delimiters, does not find the column. The lookup spelling "Amount" finds it.

6.3 Display names in GSP reports: DisplayNameMode

GSP has two reports that list the tables and columns of a query: TGetTableColumn, and TSQLResolver2ResultFormatter, which prints the result of the resolver (section 9). Both can print names in three modes (gudusoft.gsqlparser.resolver2.format.DisplayNameMode):

Mode What it prints
DISPLAY the name without delimiters, in the case that was written
SQL_RENDER valid SQL text for the name, with delimiters only where they are needed (section 7)
CANONICAL the folded form: in Oracle, unquoted names in upper case; quoted names keep their case 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.resolver2.format.DisplayNameMode;
import gudusoft.gsqlparser.util.TGetTableColumn;

public class DisplayModes {
    public static void main(String[] args) {
        String sql = "SELECT OrderID, \"Order Date\" FROM SalesOrder";
        for (DisplayNameMode mode : DisplayNameMode.values()) {
            TGetTableColumn report = new TGetTableColumn(EDbVendor.dbvoracle);
            report.isConsole = false;           // collect the report in outList
            report.setDisplayNameMode(mode);
            report.runText(sql);
            System.out.println("== " + mode);
            System.out.print(report.outList);
        }
    }
}

1
2
3
4
5
6
7
== DISPLAY
Tables:
SalesOrder

Fields:
SalesOrder.Order Date
SalesOrder.OrderID

1
2
3
4
5
6
7
== SQL_RENDER
Tables:
SalesOrder

Fields:
SalesOrder."Order Date"
SalesOrder.OrderID

1
2
3
4
5
6
7
== CANONICAL
Tables:
SALESORDER

Fields:
SALESORDER.Order Date
SALESORDER.ORDERID
  • TGetTableColumn uses SQL_RENDER by default. setQuotePolicy(SqlNameGenerator.QuotePolicy) chooses how many delimiters SQL_RENDER writes.
  • TSQLResolver2ResultFormatter has the same setDisplayNameMode and setQuotePolicy methods. Its default comes from TSQLResolverConfig.getDisplayNameMode(), which is DISPLAY unless you change it with TSQLResolverConfig.setDisplayNameMode(DisplayNameMode).
  • When one object is written in several ways, DisplayNamePolicy decides which spelling a report prints: PREFER_DEFINITION_SITE (the default), PREFER_FIRST_OCCURRENCE or PREFER_METADATA.

7. Write names back into SQL: SqlNameGenerator

SqlNameGenerator writes SQL text from stored names. You give it one Segment for each part of the name: the stored name and its kind (Segment.catalog, Segment.schema, Segment.table, Segment.column, and so on). It returns a Result: either the SQL text, or the reason why it cannot write one.

There are two quote policies (SqlNameGenerator.QuotePolicy):

  • ALWAYS delimits every segment. This is always correct. generate(vendor, segments...) without a policy uses ALWAYS.
  • WHEN_NEEDED writes a segment without delimiters only when GSP can prove that the bare word names the same object. Otherwise it delimits the segment.

GSP writes a segment bare only when all of these are true:

  1. The kind of object has a bare form. A DAX column is always written [Name].
  2. The stored name does not start like a quoted name. (A SQL Server table stored as [x], with the brackets as part of its name, is written [[x]]].)
  3. The characters are valid for an unquoted name.
  4. The bare word names the same object as the delimited spelling. (In Oracle, a bare Amount folds to AMOUNT.)
  5. The word is not in GSP's keyword list for the database.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.SqlNameGenerator;
import gudusoft.gsqlparser.sqlenv.SqlNameGenerator.QuotePolicy;
import gudusoft.gsqlparser.sqlenv.SqlNameGenerator.Result;
import gudusoft.gsqlparser.sqlenv.SqlNameGenerator.Segment;

public class GenerateNames {
    static void print(String label, Result r) {
        System.out.printf("%-28s %s%n", label, r.isOk() ? r.getSql()
                : "FAILED " + r.getFailure() + " at segment " + r.getFailedSegmentIndex());
    }

    public static void main(String[] args) {
        QuotePolicy needed = QuotePolicy.WHEN_NEEDED;
        EDbVendor oracle = EDbVendor.dbvoracle;

        // Stored names, as the Oracle catalog keeps them
        print("Oracle HR / Order Items", SqlNameGenerator.generate(oracle, needed,
                Segment.schema("HR"), Segment.table("Order Items")));
        print("  same, ALWAYS", SqlNameGenerator.generate(oracle, QuotePolicy.ALWAYS,
                Segment.schema("HR"), Segment.table("Order Items")));
        print("Oracle column AMOUNT", SqlNameGenerator.generate(oracle, needed, Segment.column("AMOUNT")));
        print("Oracle column Amount", SqlNameGenerator.generate(oracle, needed, Segment.column("Amount")));
        print("Oracle column DATE", SqlNameGenerator.generate(oracle, needed, Segment.column("DATE")));

        EDbVendor mssql = EDbVendor.dbvmssql;
        print("SQL Server Sales/dbo/...", SqlNameGenerator.generate(mssql, needed,
                Segment.catalog("Sales"), Segment.schema("dbo"), Segment.table("Order Details")));
        print("SQL Server column *", SqlNameGenerator.generate(mssql, needed, Segment.column("*")));
        print("SQL Server column 1", SqlNameGenerator.generate(mssql, needed, Segment.column("1")));
        print("SQL Server column a.b", SqlNameGenerator.generate(mssql, needed, Segment.column("a.b")));
        print("MySQL t / date", SqlNameGenerator.generate(EDbVendor.dbvmysql, needed,
                Segment.table("t"), Segment.column("date")));

        // Failures are reported, never guessed
        print("Oracle empty schema", SqlNameGenerator.generate(oracle, needed,
                Segment.schema(""), Segment.table("EMP")));
        print("MySQL name with NUL", SqlNameGenerator.generate(EDbVendor.dbvmysql, needed,
                Segment.column("a\u0000b")));

        // Re-spell a name that was read from SQL text
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        print("respell Sales, ALWAYS", SqlNameGenerator.respell(oracle, QuotePolicy.ALWAYS, table, "Sales"));
        print("respell \"SALES\", WHEN_NEEDED", SqlNameGenerator.respell(oracle, needed, table, "\"SALES\""));
        print("respell \"Sales\", WHEN_NEEDED", SqlNameGenerator.respell(oracle, needed, table, "\"Sales\""));
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
Oracle HR / Order Items      HR."Order Items"
  same, ALWAYS               "HR"."Order Items"
Oracle column AMOUNT         AMOUNT
Oracle column Amount         "Amount"
Oracle column DATE           "DATE"
SQL Server Sales/dbo/...     Sales.dbo.[Order Details]
SQL Server column *          [*]
SQL Server column 1          [1]
SQL Server column a.b        [a.b]
MySQL t / date               t.`date`
Oracle empty schema          FAILED EMPTY_SEGMENT at segment 0
MySQL name with NUL          FAILED NOT_REPRESENTABLE at segment 0
respell Sales, ALWAYS        "SALES"
respell "SALES", WHEN_NEEDED SALES
respell "Sales", WHEN_NEEDED "Sales"
  • The Oracle column stored as Amount must be delimited. The column stored as AMOUNT can stay bare.
  • DATE is a keyword, so GSP delimits it.
  • The SQL Server columns *, 1 and a.b are displayed bare (section 1.8), but written as [*], [1] and [a.b].
  • When GSP cannot prove that a result is correct, it fails with a SqlNameGenerator.Failure value: NO_SEGMENTS, NULL_SEGMENT, EMPTY_SEGMENT (GSP never fills in a missing part), NO_QUOTED_FORM, NOT_REPRESENTABLE or ROUND_TRIP_MISMATCH. getDetail() explains the failure. getSqlOrThrow() throws an IllegalStateException instead of returning a failure.
  • respell(vendor, policy, objectType, sqlText) rewrites a name that you read from SQL text. With WHEN_NEEDED, a name written without delimiters stays as it was written: GSP does not change its case, because SQL text does not show the stored case. A delimited name is decoded and then written like a stored name.
  • SqlNameGenerator always uses the default rules of the database. It has no profile parameter.

8. Describe your database settings: IdentifierProfile

The rules in section 1.5 assume the default settings of each database. Some databases have settings that change the rules. When your database uses other settings, describe them in an IdentifierProfile, and compare names with a service built from that profile.

8.1 VendorFlags

IdentifierProfile.VendorFlags holds five settings. Each setting is used by only some databases:

Field Default Used by Meaning
mysqlLowerCaseTableNames 1 dbvmysql, dbvoceanbase The server variable lower_case_table_names. 0: table and database names are case-sensitive. 1: they are stored in lower case and compared case-insensitively. 2: they are stored as written and compared case-insensitively. Column names are always case-insensitive.
defaultCollation "SQL_Latin1_General_CP1_CI_AS" dbvmssql, dbvazuresql The collation. A name that contains _CS_ (or ends with _CS) makes names case-sensitive. See section 8.2.
redshiftEnableCaseSensitive false dbvredshift The setting enable_case_sensitive_identifier.
snowflakeQuotedIdentifiersIgnoreCase false dbvsnowflake The parameter QUOTED_IDENTIFIERS_IGNORE_CASE. With true, quoted names also fold to upper case and are compared case-insensitively.
mysqlAnsiQuotes false dbvmysql, dbvoceanbase The sql_mode option ANSI_QUOTES. With true, "x" is a quoted name, not a string. It changes the delimiters only, not the case rules.

Doris and StarRocks do not use mysqlLowerCaseTableNames or mysqlAnsiQuotes.

Create the flags with new VendorFlags(mysqlLowerCaseTableNames, defaultCollation, redshiftEnableCaseSensitive, snowflakeQuotedIdentifiersIgnoreCase). A fifth argument sets mysqlAnsiQuotes; without it, mysqlAnsiQuotes is false. One VendorFlags object holds the settings of all databases, so the simplest way is to copy the values of VendorFlags.defaults() and change only the one you need, as the sample does. Then create the profile with IdentifierProfile.forVendor(vendor, flags), and a service for it with IdentifierService.forProfile(profile).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile.VendorFlags;
import gudusoft.gsqlparser.sqlenv.IdentifierRules;
import gudusoft.gsqlparser.sqlenv.IdentifierRules.CaseCompare;
import gudusoft.gsqlparser.sqlenv.IdentifierRules.CaseFold;
import gudusoft.gsqlparser.sqlenv.IdentifierService;

public class CustomProfiles {
    static void compare(String label, IdentifierProfile profile, ESQLDataObjectType type, String a, String b) {
        IdentifierService configured = IdentifierService.forProfile(profile);
        IdentifierService standard = IdentifierService.defaultServiceFor(profile.getVendor());
        System.out.printf("%-30s %-9s vs %-7s default=%-5s configured=%s%n", label, a, b,
                standard.areEqual(a, b, type), configured.areEqual(a, b, type));
    }

    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        ESQLDataObjectType column = ESQLDataObjectType.dotColumn;
        VendorFlags d = VendorFlags.defaults();

        // MySQL on Linux: lower_case_table_names = 0
        IdentifierProfile mysqlLinux = IdentifierProfile.forVendor(EDbVendor.dbvmysql,
                new VendorFlags(0, d.defaultCollation, d.redshiftEnableCaseSensitive,
                        d.snowflakeQuotedIdentifiersIgnoreCase));
        compare("MySQL lower_case_table_names=0", mysqlLinux, table, "Orders", "orders");
        compare("  (columns do not change)", mysqlLinux, column, "Amount", "amount");

        // SQL Server with a case-sensitive collation
        IdentifierProfile mssqlCs = IdentifierProfile.forVendor(EDbVendor.dbvmssql,
                new VendorFlags(d.mysqlLowerCaseTableNames, "Latin1_General_CS_AS",
                        d.redshiftEnableCaseSensitive, d.snowflakeQuotedIdentifiersIgnoreCase));
        compare("SQL Server _CS_AS collation", mssqlCs, column, "Amount", "AMOUNT");

        // Snowflake with QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE
        IdentifierProfile snowflakeQiic = IdentifierProfile.forVendor(EDbVendor.dbvsnowflake,
                new VendorFlags(d.mysqlLowerCaseTableNames, d.defaultCollation,
                        d.redshiftEnableCaseSensitive, true));
        compare("Snowflake quoted ignore case", snowflakeQiic, table, "\"Store\"", "STORE");

        // Redshift with enable_case_sensitive_identifier = true
        IdentifierProfile redshiftCs = IdentifierProfile.forVendor(EDbVendor.dbvredshift,
                new VendorFlags(d.mysqlLowerCaseTableNames, d.defaultCollation,
                        true, d.snowflakeQuotedIdentifiersIgnoreCase));
        compare("Redshift case-sensitive ids", redshiftCs, table, "\"Orders\"", "orders");
        compare("  (unquoted names too)", redshiftCs, table, "Orders", "orders");

        // MySQL with sql_mode ANSI_QUOTES: "x" is a name, not a string
        IdentifierProfile mysqlAnsi = IdentifierProfile.forVendor(EDbVendor.dbvmysql,
                new VendorFlags(d.mysqlLowerCaseTableNames, d.defaultCollation,
                        d.redshiftEnableCaseSensitive, d.snowflakeQuotedIdentifiersIgnoreCase, true));
        compare("MySQL ANSI_QUOTES", mysqlAnsi, table, "\"Orders\"", "orders");

        // Your own rule for one object type: BigQuery table names case-insensitive
        IdentifierProfile bigQuery = IdentifierProfile.builder(EDbVendor.dbvbigquery)
                .withObjectRules(table, new IdentifierRules(
                        CaseFold.NONE, CaseCompare.INSENSITIVE,     // unquoted names
                        CaseFold.NONE, CaseCompare.INSENSITIVE))    // quoted names
                .build();
        compare("BigQuery, tables insensitive", bigQuery, table, "Orders", "orders");
        compare("  (schemas do not change)", bigQuery, ESQLDataObjectType.dotSchema, "Sales", "sales");
    }
}

1
2
3
4
5
6
7
8
9
MySQL lower_case_table_names=0 Orders    vs orders  default=true  configured=false
  (columns do not change)      Amount    vs amount  default=true  configured=true
SQL Server _CS_AS collation    Amount    vs AMOUNT  default=true  configured=false
Snowflake quoted ignore case   "Store"   vs STORE   default=false configured=true
Redshift case-sensitive ids    "Orders"  vs orders  default=true  configured=false
  (unquoted names too)         Orders    vs orders  default=true  configured=false
MySQL ANSI_QUOTES              "Orders"  vs orders  default=false configured=true
BigQuery, tables insensitive   Orders    vs orders  default=false configured=true
  (schemas do not change)      Sales     vs sales   default=false configured=false
  • default= is the answer of IdentifierService.defaultServiceFor(vendor), the service with the default rules. SQLUtil and the static methods always use this service. configured= is the answer of IdentifierService.forProfile(profile).
  • Redshift: with redshiftEnableCaseSensitive = true, GSP 4.2.10 compares all names case-sensitively, unquoted names too: Orders and orders are two names. If you need unquoted names to fold to lower case while quoted names are case-sensitive, write that rule yourself (section 8.3).

8.2 SQL Server collations

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile.VendorFlags;
import gudusoft.gsqlparser.sqlenv.IdentifierService;

public class SqlServerCollations {
    public static void main(String[] args) {
        ESQLDataObjectType column = ESQLDataObjectType.dotColumn;
        VendorFlags d = VendorFlags.defaults();
        String[] collations = {
            "SQL_Latin1_General_CP1_CI_AS",   // the default
            "Latin1_General_CS_AS",
            "Latin1_General_BIN2",
            "Latin1_General_CI_AI",
            "",
            null
        };
        for (String collation : collations) {
            IdentifierService names = IdentifierService.forProfile(IdentifierProfile.forVendor(
                    EDbVendor.dbvmssql,
                    new VendorFlags(d.mysqlLowerCaseTableNames, collation,
                            d.redshiftEnableCaseSensitive, d.snowflakeQuotedIdentifiersIgnoreCase)));
            System.out.printf("%-30s Amount=AMOUNT: %-5s  resume=résumé: %s%n",
                    collation == null ? "null" : "\"" + collation + "\"",
                    names.areEqual("Amount", "AMOUNT", column),
                    names.areEqual("resume", "résumé", column));
        }
    }
}

1
2
3
4
5
6
"SQL_Latin1_General_CP1_CI_AS" Amount=AMOUNT: true   resume=résumé: false
"Latin1_General_CS_AS"         Amount=AMOUNT: false  resume=résumé: false
"Latin1_General_BIN2"          Amount=AMOUNT: false  resume=résumé: false
"Latin1_General_CI_AI"         Amount=AMOUNT: true   resume=résumé: false
""                             Amount=AMOUNT: false  resume=résumé: false
null                           Amount=AMOUNT: false  resume=résumé: false
  • A collation with _CI_ in its name is case-insensitive. A collation with _CS_ is case-sensitive.
  • A collation name without _CI or _CS, such as Latin1_General_BIN2, compares names case-sensitively.
  • GSP does not model accent-insensitive collations (_AI). resume and résumé stay two different names, even under Latin1_General_CI_AI.
  • Always pass a real collation name. In 4.2.10, an empty or null collation makes SQL Server names case-sensitive, and that is not the SQL Server default.

8.3 Your own rules: IdentifierRules and the profile builder

When no flag describes your database, write the rule yourself. new IdentifierRules(unquotedFold, unquotedCompare, quotedFold, quotedCompare) takes:

  • IdentifierRules.CaseFold: NONE (keep the case), UPPER or LOWER.
  • IdentifierRules.CaseCompare: SENSITIVE, INSENSITIVE, COLLATION_BASED (compare with the collation; SQL Server and Azure SQL), or SAME_AS_UNQUOTED (for the quoted part only: compare quoted names like unquoted names).

Then start a builder from the default rules of the database with IdentifierProfile.builder(vendor) or IdentifierProfile.builder(vendor, flags), and replace what you need:

Builder method What it replaces
withObjectRules(objectType, rules) the rule of one object type, for example only dotTable
withNameRules(rules), withColumnRules(rules), withRoutineRules(rules) the rule of one group: NAME_GROUP, COLUMN_GROUP or ROUTINE_GROUP
withRules(group, rules) the rule of one group
withFlags(flags) the flags. GSP computes the default rules again from the new flags and keeps your replacements.
withoutObjectRules(objectType), withoutRules(group) removes a replacement that you made before

A rule for one object type wins over a rule for its group. A rule for a group wins over the default rule. build() returns a profile that cannot change. profile.toBuilder() starts a new builder from an existing profile. The last two lines of the sample in section 8.1 make BigQuery table names case-insensitive, while schema names keep the default rule.

For example, a Sybase ASE server with a binary (case-sensitive) sort order needs NONE / SENSITIVE rules for the groups that you use, because GSP's default Sybase rule is case-insensitive.

A profile states your assumption about the database. GSP does not read these settings from a live server. A statement in the script does not change the profile either: for example, BigQuery CREATE SCHEMA ... OPTIONS(is_case_insensitive=...) is parsed, but it does not change the rules.

9. Use a profile for a whole analysis

A profile changes the answers only where you give it to GSP. There are four places. Use one profile for one analysis. When two places get profiles with different rules, GSP throws an exception instead of choosing one of them.

Two terms used in this section: the resolver is the part of the parser that finds the table and the column of each column reference, and resolver2 is the name of its current version. Data lineage tells which source columns feed each target column; DataFlowAnalyzer computes it.

Where API Use it when
parser TGSqlParser.setIdentifierProfile(profile) you parse SQL with TGSqlParser
catalog new TSQLEnv(vendor, profile) you give GSP the tables and columns of your database with TGSqlParser.setSqlEnv
resolver2 TSQLResolverConfig.createForProfile(profile) or setIdentifierProfile(profile) you pass your own resolver configuration with TGSqlParser.setResolver2Config
lineage Option.setIdentifierProfile(profile) you run DataFlowAnalyzer

9.1 Parser: TGSqlParser.setIdentifierProfile

1
2
3
-- SQL Server
CREATE TABLE sales (Amount INT, Qty INT);
SELECT AMOUNT, Qty FROM sales;

With the default collation, AMOUNT is the column Amount. With a case-sensitive collation, the table has no column AMOUNT, and SQL Server rejects the query.

The sample reads the answer of the resolver. getResolution().getStatus() is EXACT_MATCH when the resolver found exactly one column, and NOT_FOUND when no table in the query has the column.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TResultColumnList;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile.VendorFlags;
import gudusoft.gsqlparser.sqlenv.IdentifierService;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;

public class ParserWithProfile {
    static void run(String label, IdentifierProfile profile) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvmssql);
        if (profile != null) {
            parser.setIdentifierProfile(profile);      // before parse()
        }
        parser.sqltext = "CREATE TABLE sales (Amount INT, Qty INT);\n"
                       + "SELECT AMOUNT, Qty FROM sales;";
        if (parser.parse() != 0) {
            System.out.println(parser.getErrormessage());
            return;
        }
        System.out.println("== " + label);
        TSelectSqlStatement select = (TSelectSqlStatement) parser.sqlstatements.get(1);
        TResultColumnList columns = select.getResultColumnList();
        for (int i = 0; i < columns.size(); i++) {
            TObjectName column = columns.getResultColumn(i).getExpr().getObjectOperand();
            System.out.println(column + " -> " + column.getResolution().getStatus()
                    + " (" + column.getResolution() + ")");
        }
        // The service this parse used: compare your own names the same way
        IdentifierService used = parser.getSqlEnv().getIdentifierService();
        System.out.println("Amount = AMOUNT ? "
                + used.areEqual("Amount", "AMOUNT", ESQLDataObjectType.dotColumn));
    }

    public static void main(String[] args) {
        run("default collation (case-insensitive)", null);

        VendorFlags d = VendorFlags.defaults();
        IdentifierProfile caseSensitive = IdentifierProfile.forVendor(EDbVendor.dbvmssql,
                new VendorFlags(d.mysqlLowerCaseTableNames, "Latin1_General_CS_AS",
                        d.redshiftEnableCaseSensitive, d.snowflakeQuotedIdentifiersIgnoreCase));
        run("Latin1_General_CS_AS (case-sensitive)", caseSensitive);
    }
}

The program prints both runs:

1
2
3
4
== default collation (case-insensitive)
AMOUNT -> EXACT_MATCH (Exact match: Amount from sales)
Qty -> EXACT_MATCH (Exact match: Qty from sales)
Amount = AMOUNT ? true

1
2
3
4
== Latin1_General_CS_AS (case-sensitive)
AMOUNT -> NOT_FOUND (Column 'AMOUNT' not found in any visible table)
Qty -> EXACT_MATCH (Exact match: Qty from sales)
Amount = AMOUNT ? false

Rules:

  • Call setIdentifierProfile before parse(). GSP fixes the profile when the parse starts. A call after parse() throws an IllegalStateException. prepareForReuse() resets the parser, and after that you can set a profile again.
  • The profile must be for the same vendor as the parser. Otherwise GSP throws an IllegalArgumentException.
  • After parse(), parser.getSqlEnv().getIdentifierService() is the service that the parse used. Use it when your own code must compare names in the same way.
  • TObjectName.getNormalizedTableString() and the other getNormalized...String() methods always use the default rules, not your profile. With a profile, normalize with parser.getSqlEnv().getIdentifierService().normalize(name, objectType).

9.2 Your own catalog: TSQLEnv

Create your TSQLEnv with the profile: new TSQLEnv(vendor, profile). The environment keeps this profile for its whole life, because it builds its indexes with it. A parser that gets the environment with setSqlEnv uses the profile of the environment, so you do not need setIdentifierProfile too. If you call both, the two profiles must have the same rules. Otherwise parse() throws an IllegalStateException.

  • Compare names under the profile of the environment with env.getIdentifierService(). In 4.2.10, the instance methods compareIdentifier(objectType, name1, name2), compareTable and compareColumn of TSQLEnv use the default rules of the vendor, not the profile of the environment.
  • When you load stored names into a TSQLEnv, convert them first (section 11.2).

9.3 resolver2 configuration

Usually you do nothing here: parse() gives the profile of the parser to resolver2. You need to act only when you pass your own TSQLResolverConfig with setResolver2Config. Then leave its profile unset, or set the same profile. A profile with different rules makes parse() throw an IllegalStateException.

9.4 Lineage: Option.setIdentifierProfile

Option holds the settings of a DataFlowAnalyzer run.

1
2
-- SQL Server
INSERT INTO tgt (a, b) SELECT Amount, AMOUNT FROM src;

With the default collation, Amount and AMOUNT are one column of src. With a case-sensitive collation, they are two columns.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.dlineage.DataFlowAnalyzer;
import gudusoft.gsqlparser.dlineage.dataflow.model.Option;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.column;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.dataflow;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.relationship;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.sourceColumn;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.table;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile.VendorFlags;

public class LineageWithProfile {
    static void run(String label, IdentifierProfile profile) {
        Option option = new Option();
        option.setVendor(EDbVendor.dbvmssql);
        option.setSimpleOutput(true);
        option.setOutput(false);
        option.setIdentifierProfile(profile);          // null = vendor default

        String sql = "INSERT INTO tgt (a, b) SELECT Amount, AMOUNT FROM src;";
        DataFlowAnalyzer analyzer = new DataFlowAnalyzer(sql, option);
        analyzer.generateDataFlow();
        dataflow df = analyzer.getDataFlow();

        System.out.println("== " + label);
        for (table t : df.getTables()) {
            StringBuilder line = new StringBuilder("table " + t.getName() + ":");
            for (column c : t.getColumns()) {
                line.append(' ').append(c.getName());
            }
            System.out.println(line);
        }
        for (relationship r : df.getRelationships()) {
            if (!"fdd".equals(r.getType())) continue;
            StringBuilder line = new StringBuilder(
                    r.getTarget().getParent_name() + "." + r.getTarget().getColumn() + " <-");
            for (sourceColumn s : r.getSources()) {
                line.append(' ').append(s.getParent_name()).append('.').append(s.getColumn());
            }
            System.out.println(line);
        }
    }

    public static void main(String[] args) {
        run("default collation (case-insensitive)", null);

        VendorFlags d = VendorFlags.defaults();
        run("Latin1_General_CS_AS (case-sensitive)", IdentifierProfile.forVendor(EDbVendor.dbvmssql,
                new VendorFlags(d.mysqlLowerCaseTableNames, "Latin1_General_CS_AS",
                        d.redshiftEnableCaseSensitive, d.snowflakeQuotedIdentifiersIgnoreCase)));
    }
}

The program prints both runs:

1
2
3
4
5
== default collation (case-insensitive)
table tgt: a b
table src: Amount
tgt.a <- src.Amount
tgt.b <- src.Amount

1
2
3
4
5
== Latin1_General_CS_AS (case-sensitive)
table tgt: a b
table src: Amount AMOUNT
tgt.a <- src.Amount
tgt.b <- src.AMOUNT
  • Set the profile before generateDataFlow(). The whole run uses the profile that the option had when the run started.
  • The profile must be for the same vendor as the option (Option.setVendor). Otherwise the run fails with an IllegalStateException.

9.5 Azure SQL

GSP parses Azure SQL with its SQL Server parser: a TGSqlParser created for dbvazuresql works as dbvmssql. For this reason:

  • Parser: build the profile for EDbVendor.dbvmssql. A dbvazuresql profile is refused with an IllegalArgumentException.
  • Lineage: in 4.2.10, an Option with the vendor dbvazuresql accepts no profile. If you need one, run the analysis with EDbVendor.dbvmssql and a dbvmssql profile.

9.6 What a profile does not change

  • SQLUtil.sameName, SQLUtil.compareIdentifier, SQLUtil.canonKey, the static IdentifierService methods, new NameService(vendor) and SqlNameGenerator always use the default rules of the vendor.
  • A profile changes only the analysis that you give it to. Two analyses can run at the same time with different profiles.

10. Save the identity of a name: persistent keys

A CanonKey is valid only inside one running program. When you must save the identity of a name, for example in a database table, in a file, or in a cache that several programs share, use a persistent key. The method persistentKeyV1(objectType, name, form) of an IdentifierService returns a PersistentIdentifierKey. As long as the rule stays the same, the same name gives the same key in every Java version, on every operating system and in every locale.

A persistent key has three parts. Save all three, and compare all three:

  • getPolicyId(): an ID of the rules that made the key, for example pk1-ry85qY4x....
  • getGroup(): the object group.
  • getPayload(): the canonical text of the name.

The third parameter says where the name came from: IdentifierInputForm.SQL_LEXICAL for SQL text, or IdentifierInputForm.CATALOG_STORED for a name read from the catalog of the database (section 1.7).

1
2
3
-- Oracle
CREATE TABLE emp   (empno NUMBER);   -- ALL_TABLES.TABLE_NAME = 'EMP'
CREATE TABLE "Emp" (id NUMBER);      -- ALL_TABLES.TABLE_NAME = 'Emp'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ApproximateIdentifierKey;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierCodec;
import gudusoft.gsqlparser.sqlenv.IdentifierService;
import gudusoft.gsqlparser.sqlenv.PersistentIdentifierKey;

import static gudusoft.gsqlparser.sqlenv.IdentifierInputForm.CATALOG_STORED;
import static gudusoft.gsqlparser.sqlenv.IdentifierInputForm.SQL_LEXICAL;

public class PersistentKeys {
    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        IdentifierService oracle = IdentifierService.defaultServiceFor(EDbVendor.dbvoracle);

        // A name from SQL text and a name from the Oracle catalog (ALL_TABLES.TABLE_NAME)
        PersistentIdentifierKey fromSql     = oracle.persistentKeyV1(table, "emp", SQL_LEXICAL);
        PersistentIdentifierKey fromCatalog = oracle.persistentKeyV1(table, "EMP", CATALOG_STORED);
        System.out.println(fromSql);
        System.out.println("emp (SQL) == EMP (catalog): " + fromSql.equals(fromCatalog));

        // The same text means different names in the two forms
        PersistentIdentifierKey quoted = oracle.persistentKeyV1(table, "\"Emp\"", SQL_LEXICAL);
        PersistentIdentifierKey stored = oracle.persistentKeyV1(table, "Emp", CATALOG_STORED);
        PersistentIdentifierKey bare   = oracle.persistentKeyV1(table, "Emp", SQL_LEXICAL);
        System.out.println("\"Emp\" (SQL) == Emp (catalog): " + quoted.equals(stored));
        System.out.println("Emp (SQL)   == Emp (catalog): " + bare.equals(stored));

        // Store the three parts, rebuild the key later
        PersistentIdentifierKey reloaded = new PersistentIdentifierKey(
                fromSql.getPolicyId(), fromSql.getGroup(), fromSql.getPayload());
        System.out.println("reloaded equals original: " + reloaded.equals(fromSql));

        // A broken quoted spelling is refused
        try {
            oracle.persistentKeyV1(table, "\"Emp", SQL_LEXICAL);
        } catch (IdentifierCodec.MalformedIdentifierException e) {
            System.out.println("refused: " + e.getMessage());
        }

        // SQL Server with a case-insensitive collation: no exact persistent key
        IdentifierService mssql = IdentifierService.defaultServiceFor(EDbVendor.dbvmssql);
        try {
            mssql.persistentKeyV1(table, "Orders", SQL_LEXICAL);
        } catch (UnsupportedOperationException e) {
            System.out.println("UnsupportedOperationException: " + e.getMessage());
        }
        ApproximateIdentifierKey approx = mssql.approximateKeyV1(table, "[Orders]", SQL_LEXICAL);
        System.out.println(approx);

        // An approximate key can merge two different names: never use it as identity
        System.out.println("approximate \"Emp\" == EMP: " + oracle.approximateKeyV1(table, "\"Emp\"", SQL_LEXICAL)
                .equals(oracle.approximateKeyV1(table, "EMP", CATALOG_STORED)));

        // For logs: what policy produced the key
        System.out.println(oracle.describePolicyV1(table));
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
PersistentIdentifierKey{pk1-ry85qY4xz78n1Dv7SeFD4W6JsaS0fUCTPayZ-tCwrMY/NAME_GROUP/EMP}
emp (SQL) == EMP (catalog): true
"Emp" (SQL) == Emp (catalog): true
Emp (SQL)   == Emp (catalog): false
reloaded equals original: true
refused: Quoted identifier is not terminated by '"': "Emp
UnsupportedOperationException: persistentKeyV1: this cell compares via collation 'SQL_Latin1_General_CP1_CI_AS' - collator weights are not portably reproducible; use approximateKeyV1 (no areEqual biconditional)
ApproximateIdentifierKey{ak1-AXl2mKbB9x7hOACdDOUnr1R9yLKFXL2418xSmqyb4TU/NAME_GROUP/orders}
approximate "Emp" == EMP: true
policyV1{vendor=oracle, group=ng, cell=UPPER/INSENSITIVE|NONE/SENSITIVE, codec=1, mapRev=1, ucd=16.0.0/alg1, persistent=pk1-ry85qY4xz78n1Dv7SeFD4W6JsaS0fUCTPayZ-tCwrMY, approximate=ak1-BHuYf-DvhbOBUh-jACJwZK1QvywJvDQsu7IXY3-9uDM}

What the output shows:

  • The SQL text emp and the stored name EMP give equal keys. The SQL text "Emp" and the stored name Emp give equal keys. The SQL text Emp, which folds to EMP, and the stored name Emp do not.
  • new PersistentIdentifierKey(policyId, group, payload) rebuilds a saved key.
  • In SQL text, a broken quoted name throws IdentifierCodec.MalformedIdentifierException. The persistent API never guesses.
  • SQL Server and Azure SQL throw an UnsupportedOperationException, because a collation comparison cannot be saved as portable text. Only a collation with _CS_ in its name gives persistent keys. A custom profile whose unquoted and quoted parts mix a collation with a fold throws IdentifierService.MixedKindProfileException, which is also an UnsupportedOperationException.
  • approximateKeyV1(objectType, name, form) returns an ApproximateIdentifierKey for every rule, SQL Server too. It removes the delimiters and ignores case. It is not an identity: in Oracle it gives the same key to "Emp" and EMP, which are two tables. Use it only to group or sort names, never to decide that two names are the same object.
  • describePolicyV1(objectType) returns a readable description for logs. Do not use its text as a key.

A newer GSP version can change a policy ID, for example when it corrects the rule of a database. Never compare the payloads of keys with different policy IDs. When you load a saved key whose policy ID is not the current one for that vendor and object group, make a new key from the original name.

11. From SQL to your catalog: end-to-end examples

11.1 Match table names from the AST with your own list

Your application often has its own list of tables, read from the catalog of the database. This example parses Oracle SQL, reads each table name from the AST (the tree of objects that TGSqlParser builds from the SQL text), and looks it up in such a list. It builds the keys one segment at a time with IdentifierService.canonKeyOf(text, objectType, form): stored names from the catalog with CATALOG_STORED, and names from the AST with SQL_LEXICAL. When the SQL has no schema, the example uses the default schema of the session, which you must know.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.sqlenv.CanonKey;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierService;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static gudusoft.gsqlparser.sqlenv.IdentifierInputForm.CATALOG_STORED;
import static gudusoft.gsqlparser.sqlenv.IdentifierInputForm.SQL_LEXICAL;

public class MatchCatalog {
    static final ESQLDataObjectType SCHEMA = ESQLDataObjectType.dotSchema;
    static final ESQLDataObjectType TABLE = ESQLDataObjectType.dotTable;

    public static void main(String[] args) {
        EDbVendor vendor = EDbVendor.dbvoracle;
        IdentifierService names = IdentifierService.defaultServiceFor(vendor);

        // 1. Your catalog: names exactly as Oracle stores them (ALL_TABLES.OWNER, TABLE_NAME)
        String[][] allTables = {{"HR", "EMP"}, {"HR", "Order Items"}, {"SALES", "EMP"}};
        Map<List<CanonKey>, String> catalog = new HashMap<>();
        for (String[] row : allTables) {
            List<CanonKey> key = Arrays.asList(
                    names.canonKeyOf(row[0], SCHEMA, CATALOG_STORED),
                    names.canonKeyOf(row[1], TABLE, CATALOG_STORED));
            catalog.put(key, row[0] + "." + row[1]);
        }

        // 2. The SQL to check. The session's default schema is HR.
        String defaultSchema = "HR";   // a stored name, like the catalog rows
        TGSqlParser parser = new TGSqlParser(vendor);
        parser.sqltext = "SELECT e.ename, i.qty FROM hr.emp e JOIN \"HR\".\"Order Items\" i ON i.empno = e.empno;\n"
                       + "SELECT * FROM emp;\n"
                       + "SELECT * FROM hr.\"Emp\";\n"
                       + "SELECT * FROM Sales.Emp;";
        if (parser.parse() != 0) {
            System.out.println(parser.getErrormessage());
            return;
        }

        // 3. Look up every table reference, one name part at a time
        for (int i = 0; i < parser.sqlstatements.size(); i++) {
            TCustomSqlStatement stmt = parser.sqlstatements.get(i);
            for (int j = 0; j < stmt.getTables().size(); j++) {
                TTable table = stmt.getTables().getTable(j);
                TObjectName name = table.getTableName();
                CanonKey schemaKey = name.getSchemaString().isEmpty()
                        ? names.canonKeyOf(defaultSchema, SCHEMA, CATALOG_STORED)
                        : names.canonKeyOf(name.getSchemaString(), SCHEMA, SQL_LEXICAL);
                CanonKey tableKey = names.canonKeyOf(name.getTableString(), TABLE, SQL_LEXICAL);
                String found = catalog.get(Arrays.asList(schemaKey, tableKey));
                System.out.printf("%-22s -> %s%n", name, found == null ? "NOT IN CATALOG" : found);
            }
        }
    }
}

1
2
3
4
5
hr.emp                 -> HR.EMP
"HR"."Order Items"     -> HR.Order Items
emp                    -> HR.EMP
hr."Emp"               -> NOT IN CATALOG
Sales.Emp              -> SALES.EMP
  • TObjectName.getSchemaString() and getTableString() return each segment as SQL text, with its delimiters. Pass them to the name service as they are.
  • emp has no schema, so the example uses HR, the default schema of the session.
  • hr."Emp" is not in the catalog: the quoted name Emp is not the table EMP.
  • Sales.Emp finds SALES.EMP, not HR.EMP: the schema segment decides.

11.2 Give your catalog to GSP: TSQLEnv.toLexical

You can also give your catalog to GSP, so that the resolver finds the columns for you. TSQLEnv.addTable and TSQLTable.addColumn read every name as SQL text. So convert a stored name first, or GSP folds it: in Oracle, the stored column Amount would become AMOUNT. The TSQLEnv method toLexical(objectType, name, IdentifierInputForm.CATALOG_STORED) converts a stored name into SQL text with the rules of the environment.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TResultColumnList;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierInputForm;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.sqlenv.TSQLTable;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;

public class CatalogIntoEnv {
    // Rows read from ALL_TAB_COLUMNS: OWNER, TABLE_NAME, COLUMN_NAME (stored names)
    static final String[][] COLUMNS = {{"HR", "EMP", "EMPNO"}, {"HR", "EMP", "Amount"}};

    static void run(final boolean convert) {
        TSQLEnv env = new TSQLEnv(EDbVendor.dbvoracle) {
            @Override
            public void initSQLEnv() {
                for (String[] row : COLUMNS) {
                    String schema = row[0], table = row[1], column = row[2];
                    if (convert) {   // stored name -> SQL spelling
                        schema = toLexical(ESQLDataObjectType.dotSchema, schema, IdentifierInputForm.CATALOG_STORED);
                        table  = toLexical(ESQLDataObjectType.dotTable, table, IdentifierInputForm.CATALOG_STORED);
                        column = toLexical(ESQLDataObjectType.dotColumn, column, IdentifierInputForm.CATALOG_STORED);
                    }
                    TSQLTable t = addTable(schema + "." + table, false);
                    t.addColumn(column);
                }
            }
        };
        env.initSQLEnv();

        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.setSqlEnv(env);
        parser.sqltext = "SELECT empno, \"Amount\", amount FROM hr.emp";
        parser.parse();

        System.out.println(convert ? "== with toLexical" : "== names passed as they are");
        TResultColumnList list = ((TSelectSqlStatement) parser.sqlstatements.get(0)).getResultColumnList();
        for (int i = 0; i < list.size(); i++) {
            TObjectName c = list.getResultColumn(i).getExpr().getObjectOperand();
            System.out.println(c + " -> " + c.getResolution().getStatus());
        }
    }

    public static void main(String[] args) {
        run(false);
        run(true);
    }
}

1
2
3
4
== names passed as they are
empno -> EXACT_MATCH
"Amount" -> NOT_FOUND
amount -> EXACT_MATCH

1
2
3
4
== with toLexical
empno -> EXACT_MATCH
"Amount" -> EXACT_MATCH
amount -> NOT_FOUND

Without the conversion, both answers about the column Amount are wrong: the legal reference "Amount" is not found, and amount, which Oracle rejects, is found. With toLexical, both answers are right. The static TSQLEnv.toLexical(vendor, objectType, name, form) does the same with the default rules of the vendor.

12. Common mistakes

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.util.SQLUtil;

public class CommonMistakes {
    static void show(String label, Object result) {
        System.out.printf("%-40s %s%n", label, result);
    }

    public static void main(String[] args) {
        ESQLDataObjectType table = ESQLDataObjectType.dotTable;
        ESQLDataObjectType column = ESQLDataObjectType.dotColumn;
        EDbVendor oracle = EDbVendor.dbvoracle;
        EDbVendor pg = EDbVendor.dbvpostgresql;

        // Oracle: "STORE" and store are the SAME table
        show("\"STORE\" vs store, equalsIgnoreCase", "\"STORE\"".equalsIgnoreCase("store"));
        show("\"STORE\" vs store, sameName", SQLUtil.sameName(oracle, table, "\"STORE\"", "store"));

        // Oracle: "Store" and STORE are DIFFERENT tables
        String quoted = "\"Store\"";
        String stripped = quoted.substring(1, quoted.length() - 1);   // do not do this
        show("\"Store\" vs STORE, strip, then sameName", SQLUtil.sameName(oracle, table, stripped, "STORE"));
        show("\"Store\" vs STORE, sameName", SQLUtil.sameName(oracle, table, "\"Store\"", "STORE"));

        // PostgreSQL: "Amount" and amount are DIFFERENT columns,
        // but the legacy "normal name" is the same text for both
        show("legacy normal name of \"Amount\"", SQLUtil.getIdentifierNormalColumnName(pg, "\"Amount\""));
        show("legacy normal name of amount", SQLUtil.getIdentifierNormalColumnName(pg, "amount"));
        show("\"Amount\" vs amount, sameName", SQLUtil.sameName(pg, column, "\"Amount\"", "amount"));

        // An empty string equals a quoted empty name: check for "no name" first
        show("empty vs \"\", sameName", SQLUtil.sameName(oracle, table, "", "\"\""));
    }
}

1
2
3
4
5
6
7
8
"STORE" vs store, equalsIgnoreCase       false
"STORE" vs store, sameName               true
"Store" vs STORE, strip, then sameName   true
"Store" vs STORE, sameName               false
legacy normal name of "Amount"           AMOUNT
legacy normal name of amount             AMOUNT
"Amount" vs amount, sameName             false
empty vs "", sameName                    true

A checklist:

  1. Do not compare names with equals or equalsIgnoreCase. Use sameName or compareIdentifier.
  2. Do not remove delimiters or change the case before you call GSP. Pass the text exactly as it was written: the quote state is part of the name.
  3. Do not use sameName for names with dots. Use compareIdentifier.
  4. Do not use normalize, keyForMap or the legacy "normal name" methods (SQLUtil.getIdentifierNormalName, getIdentifierNormalTableName, getIdentifierNormalColumnName, and the deprecated SQLUtil.normalizeIdentifier) as keys. Two different names can get the same text, and one name can get two texts. Use canonKey.
  5. Do not save a CanonKey. Use persistentKeyV1.
  6. Know the form of each name: SQL text (SQL_LEXICAL) or stored name (CATALOG_STORED).
  7. Pass the right object type: the kind of object that the name refers to.
  8. When your database does not use the default settings, use the profile everywhere: in your own comparisons (IdentifierService.forProfile(profile)) and in the analysis (section 9).
  9. For SQL Server, pass a real collation name, never null or an empty string.
  10. If your code uses an empty string to mean "no name", check for it before you compare names: an empty string and the quoted empty name "" are equal.

13. API reference

All classes are in the package gudusoft.gsqlparser.sqlenv, unless another package is given. Only public API is listed.

13.1 SQLUtil (gudusoft.gsqlparser.util)

Method Returns Description
sameName(EDbVendor dbVendor, ESQLDataObjectType objectType, String ident1, String ident2) boolean Whether two single-segment names name the same object, under the default rules of the vendor. Two null names are equal. dbVendor must not be null.
compareIdentifier(EDbVendor dbVendor, ESQLDataObjectType sqlDataObjectType, String identifier1, String identifier2) boolean Compares names that can have dots, segment by segment (section 4.1). false when the numbers of segments differ, when a name is null, and for object types without a name hierarchy.
canonKey(EDbVendor dbVendor, ESQLDataObjectType objectType, String identifier) CanonKey In-memory key of one segment. Key equality is sameName. null for a null name.
qualifiedCanonKeyFromLexical(EDbVendor dbVendor, ESQLDataObjectType objectType, String qualifiedName) QualifiedCanonKey In-memory key of a qualified name in SQL text. null for a null or empty name. Throws UnsupportedOperationException for an object type without a name hierarchy.
parseNames(String nameString) List<String> Splits a qualified name at the dots. A delimited segment stays whole, with its delimiters.
parseNames(String nameString, EDbVendor vendor) List<String> The same, with the rules of a vendor. With dbvbigquery, a path in one pair of back-ticks is split into its parts.

Legacy methods: getIdentifierNormalName, getIdentifierNormalTableName, getIdentifierNormalColumnName and the deprecated normalizeIdentifier return a legacy display text. They are not an identity (section 12).

13.2 IdentifierService

Static methods. They use the default rules of the vendor.

Method Returns Description
defaultServiceFor(EDbVendor dbVendor) IdentifierService The service with the default rules of the vendor. Never null.
forProfile(IdentifierProfile profile) IdentifierService A service for your profile. It adds the collator (the Java object that compares text by a collation) that SQL Server and Azure SQL need.
areEqualStatic(EDbVendor dbVendor, ESQLDataObjectType objectType, String ident1, String ident2) boolean The same as SQLUtil.sameName.
canonKeyStatic(EDbVendor dbVendor, ESQLDataObjectType objectType, String identifier) CanonKey The same as SQLUtil.canonKey.
normalizeStatic(EDbVendor dbVendor, ESQLDataObjectType objectType, String identifier) String The normalized form of one segment (section 6.1). null and "" come back unchanged.
qualifiedCanonKeyFromLexicalStatic(EDbVendor dbVendor, ESQLDataObjectType objectType, String qualifiedName) QualifiedCanonKey The same as SQLUtil.qualifiedCanonKeyFromLexical.
qualifiedCanonKeyFromStoredStatic(EDbVendor dbVendor, ESQLDataObjectType objectType, List<String> storedSegments) QualifiedCanonKey Key of a qualified name given as stored segments, outermost first.
collatorProviderFor(EDbVendor vendor) CollatorProvider A new collator provider for dbvmssql and dbvazuresql, otherwise null. You need it only with the constructor.

Constructor. IdentifierService(IdentifierProfile profile, CollatorProvider collatorProvider). Prefer forProfile(profile), which chooses the collator provider for you.

Instance methods. They use the profile of the service. Note the order of the parameters: the comparison methods take the names first and the object type last, and the persistent-key methods take the object type first.

Method Returns Description
areEqual(String ident1, String ident2, ESQLDataObjectType objectType) boolean Whether two single-segment names name the same object.
canonKey(String identifier, ESQLDataObjectType objectType) CanonKey In-memory key of one segment in SQL text. null for a null name.
canonKeyOf(String text, ESQLDataObjectType objectType, IdentifierInputForm form) CanonKey In-memory key of one segment in the stated form. null for null text. Throws IllegalArgumentException when objectType or form is null.
normalize(String identifier, ESQLDataObjectType objectType) String The normalized form of one segment.
normalizeSegment(String segment, ESQLDataObjectType objectType) String The same as normalize.
normalizeQualifiedName(String qualifiedName, ESQLDataObjectType objectType) String Normalizes each segment of a qualified name and joins them with dots. For display only. For SQL Server, an empty middle segment (db..t) becomes dbo.
keyForMap(String identifier, ESQLDataObjectType objectType) String A text key of one segment. Not an identity for SQL Server or for rules that ignore case without folding; use canonKey.
parseQualifiedName(String qualifiedName) List<String> Splits a qualified name with the rules of the vendor of the service.
expandVendorSpecific(List<String> segments, EDbVendor vendor) List<String> For SQL Server and Azure SQL, replaces an empty middle segment with dbo. Other vendors: the list comes back unchanged.
expandVendorSpecific(List<String> segments, EDbVendor vendor, String defaultSchema) List<String> The same, with your default schema instead of dbo.
qualifiedCanonKeyFromLexical(String qualifiedName, ESQLDataObjectType objectType) QualifiedCanonKey Key of a qualified name in SQL text. null for null or "". Throws UnsupportedOperationException for an object type without a name hierarchy.
qualifiedCanonKeyFromStored(List<String> storedSegments, ESQLDataObjectType objectType) QualifiedCanonKey Key of a qualified name given as stored segments, outermost first. A stored segment can contain a dot.
persistentKeyV1(ESQLDataObjectType objectType, String name, IdentifierInputForm form) PersistentIdentifierKey Key of one segment that you can save (section 10). null for a null name. Throws IdentifierCodec.MalformedIdentifierException for broken SQL text, and UnsupportedOperationException for collation rules.
approximateKeyV1(ESQLDataObjectType objectType, String name, IdentifierInputForm form) ApproximateIdentifierKey A key that works for every rule: it removes the delimiters and ignores case. Not an identity.
describePolicyV1(ESQLDataObjectType objectType) String A readable description of the persistent and approximate policies, for logs.
getProfile() IdentifierProfile The profile of the service.
getCollatorProvider() CollatorProvider The collator provider, or null.

Other public methods, buildCompositeKey(...), canUseCompositeKey(String), keysForHierarchy(String, List<ESQLDataObjectType>) and assertSingleSegmentOrThrow(String, ESQLDataObjectType), are not for general use. buildCompositeKey joins the segments as they are, without the name rules, so its result is not an identity key. Use QualifiedCanonKey instead.

Nested class. IdentifierService.MixedKindProfileException extends UnsupportedOperationException. getUnquotedKind() and getQuotedKind() return the IdentifierRules.CaseCompare of the two parts of the rule.

13.3 IdentifierProfile

An IdentifierProfile holds the rules of one database and cannot change after it is built.

Method Returns Description
forVendor(EDbVendor vendor, VendorFlags flags) (static) IdentifierProfile The default rules of the vendor for these flags.
forVendorOr(IdentifierProfile configured, EDbVendor vendor) (static) IdentifierProfile configured when it is for vendor, otherwise the default profile of vendor.
builder(EDbVendor vendor) (static) IdentifierProfile.Builder A builder that starts from the default rules and VendorFlags.defaults().
builder(EDbVendor vendor, VendorFlags flags) (static) IdentifierProfile.Builder A builder that starts from the default rules for these flags.
toBuilder() IdentifierProfile.Builder A builder that starts from this profile.
getRules(ESQLDataObjectType objectType) IdentifierRules The rule of one object type. For null, the rule of NAME_GROUP.
ruleDomainOf(ESQLDataObjectType objectType) IdentifierRules The rule of one object type with SAME_AS_UNQUOTED replaced by the unquoted compare.
groupOf(ESQLDataObjectType objectType) IdentifierProfile.ObjectGroup The group of an object type (section 1.6).
getVendor() EDbVendor The vendor.
getFlags() VendorFlags The flags.
getObjectRuleOverrides() Map<ESQLDataObjectType, IdentifierRules> The rules that you set with withObjectRules.
hasSameEffectivePolicyAs(IdentifierProfile other) boolean Whether two profiles give the same answer to every name question. equals is the same test.
getFingerprint() long A hash of the profile. Two different profiles can have the same fingerprint.

IdentifierProfile.ObjectGroup: NAME_GROUP, COLUMN_GROUP, ROUTINE_GROUP.

IdentifierProfile.VendorFlags (section 8.1):

Member Description
VendorFlags(int mysqlLowerCaseTableNames, String defaultCollation, boolean redshiftEnableCaseSensitive, boolean snowflakeQuotedIdentifiersIgnoreCase) Constructor. mysqlAnsiQuotes is false.
VendorFlags(int mysqlLowerCaseTableNames, String defaultCollation, boolean redshiftEnableCaseSensitive, boolean snowflakeQuotedIdentifiersIgnoreCase, boolean mysqlAnsiQuotes) Constructor with all five settings.
defaults() (static) 1, "SQL_Latin1_General_CP1_CI_AS", false, false, false.
mysqlLowerCaseTableNames, defaultCollation, redshiftEnableCaseSensitive, snowflakeQuotedIdentifiersIgnoreCase, mysqlAnsiQuotes Public final fields.

IdentifierProfile.Builder (section 8.3):

Method Description
withObjectRules(ESQLDataObjectType objectType, IdentifierRules rules) Sets the rule of one object type. The last call for a type wins.
withoutObjectRules(ESQLDataObjectType objectType) Removes the rule of one object type.
withRules(IdentifierProfile.ObjectGroup group, IdentifierRules rules) Sets the rule of one group.
withNameRules(IdentifierRules rules), withColumnRules(IdentifierRules rules), withRoutineRules(IdentifierRules rules) Set the rule of NAME_GROUP, COLUMN_GROUP or ROUTINE_GROUP.
withoutRules(IdentifierProfile.ObjectGroup group) Removes the rule of one group.
withFlags(VendorFlags flags) Replaces the flags and computes the default rules again. Your own rules stay.
build() Returns the profile.

All builder methods except build() return the builder. They throw a NullPointerException for a null argument.

13.4 IdentifierRules

Member Description
IdentifierRules(CaseFold unquotedFold, CaseCompare unquotedCompare, CaseFold quotedFold, CaseCompare quotedCompare) Constructor. No argument can be null.
unquotedFold, unquotedCompare, quotedFold, quotedCompare Public final fields.
resolvedCell() The same rule with SAME_AS_UNQUOTED replaced by the unquoted compare.
IdentifierRules.CaseFold NONE, UPPER, LOWER.
IdentifierRules.CaseCompare SENSITIVE, INSENSITIVE, COLLATION_BASED, SAME_AS_UNQUOTED.

Static factory methods return the rule that GSP uses for a database: forOracle(), forDameng(), forDB2(), forHANA(), forFirebird(), forAnsi(), forGeneric(), forSnowflake(), forSnowflake(boolean quotedIdentifiersIgnoreCase), forPostgreSQL(), forGaussDB(), forRedshift(), forPresto(), forVertica(), forAthena(), forHive(), forDatabricks(), forTeradata(), forDuckDB(), forSqlite(), forSybase(), forSqlAnywhere(), forAccess(), forDax(), forMdx(), forPowerQuery(), forFlink(), forCouchbase(), forSQLServer(), forSQLServer(String collation), forMySQL(int lowerCaseTableNames), forMySQLColumn(), forMySQLRoutine(), forDoris(), forDoris(int lowerCaseTableNames), forDorisColumn(), forStarrocks(), forStarrocks(int lowerCaseTableNames), forStarrocksColumn(), forBigQueryTable(), forBigQueryColumn(). Use them as a starting point for your own rules.

13.5 IdentifierCodec

All methods are static. profile and objectType must not be null.

Member Returns Description
decodeLexical(IdentifierProfile profile, ESQLDataObjectType objectType, String lexical) String SQL text to stored name. Unquoted text comes back unchanged. Throws MalformedIdentifierException for broken delimited text.
encodeStored(IdentifierProfile profile, ESQLDataObjectType objectType, String stored) String Stored name to SQL text, always delimited (SOQL: unchanged). Works for every string.
isQuoted(IdentifierProfile profile, ESQLDataObjectType objectType, String s) boolean Whether s starts with a delimiter of this database and object type. false for null and "". It does not check that the rest of the text is correct.
isVendorValidSpelling(IdentifierProfile profile, ESQLDataObjectType objectType, String lexical) boolean Whether the characters and the delimiters are valid. Reserved words and length limits are not checked. false for null and "".
hasQuotedForm(IdentifierProfile profile, ESQLDataObjectType objectType) boolean Whether the database has any delimiter for this object type.
requiresDelimiters(IdentifierProfile profile, ESQLDataObjectType objectType) boolean Whether a name must always be delimited (DAX columns).
decodeClickHouseStringLiteral(String rawLiteral) String Decodes a ClickHouse string literal (not a name). null when the literal is broken.
CODEC_REVISION int The revision of the delimiter table (1 in 4.2.10).
IdentifierCodec.MalformedIdentifierException Extends IllegalArgumentException.

13.6 CanonKey and QualifiedCanonKey

Both are in-memory keys with equals and hashCode. Neither has a public constructor: get them from SQLUtil or IdentifierService. Do not save them.

CanonKey method Returns Description
getVendor() EDbVendor The vendor of the rule.
getGroup() IdentifierProfile.ObjectGroup The object group.
getCanonText() String The canonical text. For a collation key, it is for information only.
isCollationBased() boolean Whether the key compares with a collation (SQL Server, Azure SQL).
getRuleDomain() IdentifierRules The rule that made the key.
getCollatorDomainId() String For a collation key, an ID of its collation and collator; otherwise null.
sameRulePolicyAs(CanonKey other) boolean Whether both keys were made for the same vendor under profiles with the same rules.
isRuleCompatibleWith(CanonKey other) boolean Whether two keys can be parts of one qualified key.
describeRuleDomain() String A description for logs.
QualifiedCanonKey method Returns Description
getSegmentKeys() List<CanonKey> One key for each segment, outermost first.
getLeafKey() CanonKey The key of the last segment, for example the column of t.c.
getTerminalType() ESQLDataObjectType The kind of the whole name.
size() int The number of segments.
getVendor() EDbVendor The vendor.

13.7 QualifiedNameHierarchy

Method Returns Description
hasHierarchyMapping(EDbVendor vendor, ESQLDataObjectType objectType) (static) boolean Whether qualified names of this object type can be compared and keyed (section 4.2). Call it before qualifiedCanonKeyFromLexical when your code can get any object type.

13.8 Persistent keys

Type Members
PersistentIdentifierKey PersistentIdentifierKey(String policyId, IdentifierProfile.ObjectGroup group, String payload) rebuilds a saved key. getPolicyId() (starts with pk1-), getGroup(), getPayload(). equals and hashCode use all three parts.
ApproximateIdentifierKey The same constructor and getters. The policy ID starts with ak1-. It is never equal to a PersistentIdentifierKey.
IdentifierInputForm SQL_LEXICAL: SQL text. CATALOG_STORED: a name read from the catalog of the database.

13.9 SqlNameGenerator

Method (all static) Returns Description
generate(EDbVendor vendor, Segment... segments) Result SQL text from stored segments, every segment delimited (ALWAYS).
generate(EDbVendor vendor, List<Segment> segments) Result The same with a list.
generate(EDbVendor vendor, QuotePolicy policy, Segment... segments) Result SQL text with a quote policy.
generate(EDbVendor vendor, QuotePolicy policy, List<Segment> segments) Result The same with a list.
respell(EDbVendor vendor, QuotePolicy policy, ESQLDataObjectType role, String lexical) Result Rewrites one name read from SQL text (section 7).
Nested type Members
SqlNameGenerator.QuotePolicy ALWAYS, WHEN_NEEDED
SqlNameGenerator.Segment of(ESQLDataObjectType role, String stored), server(String), catalog(String), schema(String), table(String), column(String), function(String), procedure(String), getRole(), getStored()
SqlNameGenerator.Result isOk(), getSql() (null on failure), getFailure() (null on success), getFailedSegmentIndex() (-1 when no segment is at fault), getDetail(), getSqlOrThrow()
SqlNameGenerator.Failure NO_SEGMENTS, NULL_SEGMENT, EMPTY_SEGMENT, NO_QUOTED_FORM, NOT_REPRESENTABLE, ROUND_TRIP_MISMATCH

13.10 NameService

Method Returns Description
NameService(EDbVendor vendor) Constructor with the default rules of the vendor.
forIdentifierService(IdentifierService service) (static) NameService A name service with the rules of any service.
equals(ESQLDataObjectType type, String name1, String name2) boolean The same as areEqual of the service.
indexOf(ESQLDataObjectType type, Iterable<String> names, String target) int The position of the first matching name, or -1.
distinctCopy(ESQLDataObjectType type, Iterable<String> names) List<String> The names without duplicates, first spelling kept. null elements are skipped.
createSet(ESQLDataObjectType type) Set<String> A set that uses the name rules. It does not accept null.
isCaseSensitive(ESQLDataObjectType type) boolean Whether unquoted names of this type are compared case-sensitively.
getIdentifierService(), getIdentifierProfile(), getVendor() The service, the profile and the vendor.

13.11 ESQLDataObjectType

dotUnknown, dotCatalog, dotSchema, dotTable, dotColumn, dotRoutine, dotOraclePackage, dotProcedure, dotFunction, dotTrigger, dotSynonyms, dotDblink, dotDataType, dotParameter, dotServer, dotSequence, dotIndex, dotConstraint. The groups are in section 1.6. There is no separate type for views: use dotTable.

13.12 Analysis entry points

Class Method Description
TGSqlParser (gudusoft.gsqlparser) setIdentifierProfile(IdentifierProfile profile) Sets the profile of the next parse. Call it before parse(). null means the default rules.
TGSqlParser getIdentifierProfile() The profile of the parse. null before the first parse when no profile and no TSQLEnv were given.
TGSqlParser getSqlEnv() After parse(), the environment of the parse. Its getIdentifierService() is the service that the parse used.
TGSqlParser setSqlEnv(TSQLEnv sqlEnv), setResolver2Config(TSQLResolverConfig config), prepareForReuse() See section 9.
TSQLEnv TSQLEnv(EDbVendor dbVendor), TSQLEnv(EDbVendor dbVendor, IdentifierProfile identifierProfile) Constructors of this abstract class (implement initSQLEnv()). The profile must be for dbVendor.
TSQLEnv getIdentifierService(), getIdentifierProfile() The service and the profile of the environment.
TSQLEnv toLexical(ESQLDataObjectType objectType, String name, IdentifierInputForm form) Converts a CATALOG_STORED name into SQL text with the rules of the environment. A SQL_LEXICAL name comes back unchanged.
TSQLEnv toLexical(EDbVendor vendor, ESQLDataObjectType objectType, String name, IdentifierInputForm form) (static) The same with the default rules of the vendor.
TSQLEnv compareIdentifier(EDbVendor dbVendor, ESQLDataObjectType objectType, String ident1, String ident2) (static) The same as SQLUtil.sameName.
TSQLResolverConfig (gudusoft.gsqlparser.resolver2) createForProfile(IdentifierProfile profile) (static), setIdentifierProfile(IdentifierProfile profile), getIdentifierProfile(), identifierService() The profile of resolver2 (section 9.3).
Option (gudusoft.gsqlparser.dlineage.dataflow.model) setIdentifierProfile(IdentifierProfile identifierProfile), getIdentifierProfile() The profile of a lineage run (section 9.4). null means the default rules.

13.13 Display modes

Type or method Description
DisplayNameMode (gudusoft.gsqlparser.resolver2.format) DISPLAY, SQL_RENDER, CANONICAL (section 6.3).
DisplayNamePolicy (gudusoft.gsqlparser.resolver2.format) PREFER_DEFINITION_SITE, PREFER_FIRST_OCCURRENCE, PREFER_METADATA.
TGetTableColumn.setDisplayNameMode(DisplayNameMode), setQuotePolicy(SqlNameGenerator.QuotePolicy), setDisplayNamePolicy(DisplayNamePolicy) Settings of the TGetTableColumn report (gudusoft.gsqlparser.util).
TSQLResolver2ResultFormatter.setDisplayNameMode(DisplayNameMode), setQuotePolicy(SqlNameGenerator.QuotePolicy) Settings of the resolver2 report.
TSQLResolverConfig.setDisplayNameMode(DisplayNameMode), setDisplayNamePolicy(DisplayNamePolicy) Defaults for the resolver2 report.

Refreshing this page

This page is written by hand, but every output on it is measured. To bring it forward to a newer GSP release:

1. Read the version from TBaseType.versionid and TBaseType.releaseDate in gsp_java_core/src/main/java/gudusoft/gsqlparser/TBaseType.java.

2. Run every sample again. Each Java block on this page is a complete program. Compile it against the new jar, run it, and paste the new output. Do not edit an output block by hand. The policy IDs in section 10 change when GSP changes the rules of that vendor or object group.

3. Measure the two tables again. The delimiter table in section 1.2 and the rule table in section 1.5 come from the program below. It prints one line for each EDbVendor value. Compare its output with the two tables, and look for new vendors.

The program that measures the tables 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.IdentifierCodec;
import gudusoft.gsqlparser.sqlenv.IdentifierProfile;

import java.util.ArrayList;
import java.util.List;

public class RuleTables {
    static final String[] FORMS = {"\"x\"", "[x]", "`x`", "'x'", "#\"x\"", "U&\"x\""};

    static String delimiters(IdentifierProfile p, ESQLDataObjectType type) {
        List<String> read = new ArrayList<>();
        for (String form : FORMS) {
            if (IdentifierCodec.isQuoted(p, type, form)) read.add(form);
        }
        return read + " writes " + IdentifierCodec.encodeStored(p, type, "x");
    }

    public static void main(String[] args) {
        for (EDbVendor vendor : EDbVendor.values()) {
            IdentifierProfile p = IdentifierProfile.forVendor(vendor, IdentifierProfile.VendorFlags.defaults());
            System.out.println(vendor
                    + " | table " + p.getRules(ESQLDataObjectType.dotTable)
                    + " | column " + p.getRules(ESQLDataObjectType.dotColumn)
                    + " | routine " + p.getRules(ESQLDataObjectType.dotFunction)
                    + " | table delimiters " + delimiters(p, ESQLDataObjectType.dotTable)
                    + " | column delimiters " + delimiters(p, ESQLDataObjectType.dotColumn));
        }
    }
}

4. Check the statements that name a version. Sections 8.1, 8.2, 9.2 and 9.5 describe the behavior of 4.2.10. When a newer release changes it, update the text.

5. Update the stamp at the top: the version, its release date, today's date, and the number of samples. The stamp says which build the samples were run on. Change it only after you have run the samples on the new build.

6. Verify before you publish:

1
2
3
python3 ../gsp_dotnet/site-docs/tools/check_api_symbols.py \
    --docs site-docs/docs --source gsp_java_core/src/main/java/gudusoft/gsqlparser --lang java
mkdocs build -f site-docs/mkdocs.yml

See also