Support for Delimited SQL Identifiers - Design Specification

High-Level Design

InterBase V6.0 with Client SQL Dialect 3 supports SQL delimited identifiers. With this feature, the following SQL identifiers could be delimited:

  1. table names
  2. column names
  3. constraint names
  4. correlation/alias names
  5. domain names
  6. procedure names
  7. trigger names
  8. index name
  9. exception names
  10. view names
  11. generator names
  12. role names
  13. statement names (handle by GPRE)
  14. cursor names (handle by GPRE)
  15. event names (handle by GPRE)
  16. UDF/Filter names (N/A)
  17. file names (N/A)

High-Level Algorithm

The delimited identifier support is activated/deactivated by -sqldialect n command line option or by 'SET SQL DIALECT n'/'EXEC SQL SET SQL DIALECT n' statement where n is:

  1. 1 - it processes string constants the way it is (a string constant can be delimited either by single quotes or double quotes)
  2. 2 - transitional flagger. It flags anything that is delimited by double quotes
  3. 3 - It processes any thing delimited by single quotes as string constants and any thing delimited by double quotes as SQL delimited identifiers.

The following APIs also must be able to speak the old and new dialect:

  1. isc_dsql_describe
  2. isc_dsql_describe_bind
  3. isc_dsql_execute, isc_dsql_execute2
  4. isc_dsql_execute_immediate
  5. isc_dsql_execute_immed2
  6. isc_dsql_fetch
  7. isc_dsql_prepare

To support this feature, GPRE needs to to know what dialect the user intends to speak and generates the API that speaks the specified dialect.

SQL identifiers can be ordinary identifiers or delimited identifiers. An ordinary identifier is an identifier as it is today. An ordinary identifier is not case sensitive and the InterBase engine will upper case it and store it into the system tables. A delimited identifier is an identifier delimited by double quotes. A delimited identifier consists of a sequence of any characters including blanks and double quote. When a double quote is part of the delimited identifier, the double quote needs to be "escaped" by another double quote. When storing this kind of delimited identifier, the InterBase engine will strip off the "escape" double quote and store it as is, into the system tables. On the other hand, when extracting this kind of delimited identifier, the InterBase engine will put back the "escape" double quote. A delimited identifier is case sensitive. The InterBase engine will not upper case any delimited identifier.

A string constant is delimited by single quotes except when the client SQL dialect is 1. But it is a good programming practice to modify any string constants that are delimited by double quotes to be delimited by single quotes. If single quote is part of the string, the the single quote should be "escaped" by another single quote. The InterBase engine will strip off the "escape" single quote when processing any string constants.

When extracting metadata from SQL Dialect 1 databases, the InterBase engine will generate the SET SQL DIALECT 1 statement at the beginning of generated SQL script file. Otherwise it will generate the SET SQL DIALECT 3 statement.

When extracting metadata from a SQL Dialect 1 databases, the InterBase engine will not modify any string constants that are delimited by double quotes. It is the users responsibility to make the necessary modifications to the generated SQL script file before creating a SQL Dialect 3 database.

By specifying "-sql_dialect n" where n is 1, 2, or 3 in GFIX, one can modify the database SQL dialect. The purpose of this new command line option in GFIX is to make it easy for customers to migrate to InterBase V6.0.

Detailed Design

Internal Data Structures


EXTERN SSHORT  SQL_dialect  -- global variable that holds the Client SQL dialect information
EXTERN SSHORT  db_SQL_dialect -- global variable that holds the Database SQL dialect information


define STR_delimited_id   0x1L -- to indicate the tokenized string is a delimited identifier and not a string constant.


#define isc_spb_prp_set_sql_dialect 14


#define sw_set_db_dialect    0x80000000L -- bit map value for setting new DB SQL dialect

IN_SW_ALICE_SET_DB_SQL_DIALECT, isc_spb_prp_set_sql_dialect, "sql_dialect", sw_set_db_dialect, 0, 0, FALSE, 111, 0, NULL


USHORT  ua_db_SQL_dialect --- to store specified DB dialect

Detailed Algorithm


modify this routine to allow blanks within delimited identifier. When invoking show_metadata, passes one more parameter --- lparms

Introduces copy_str function --- copying and/or modifying the data in the input buffer and stores into the output buffer

Introduces get_str function --- scanning a string constant from input buffer


allowing us to show delimited meta data

Introduces remove_delimited_double_quotes function --- stripping of the delimited double quotes of a delimited meta data and the its escape double quote


processing the extract function


passes the client SQL dialect, database SQL dialect and statement ambiguous flag to dsql_yyparse function


passes the client SQL dialect, database SQL dialect and statement ambiguous flag to yylex function.

yylex function:

set the statement ambiguous flag to ON when processing any string that is delimited by double quotes.

if the client SQL dialect is SQL_DIALECT_V6_TRANSITIONAL or SQL_DIALECT_V6 then marks the tokenized string flag as STR_delimited_id and returns it as SYMBOL


replace all "for (p=SQL-identifier, *p && *p !=BLANK; p++) ;" with invoking metd_exact_name function

introduce metd_exact_name function to allow blanks as part of the delimited SQL identifier


modify GEN_statement function when encounter nod_exception_stmt checks the tokenized string for STR_delimited_id. If it is not, then upper case the ordinary SQL identifier. Otherwise it is a delimited SQL identifier and do not upper case it.


modify hash function no to upper case any SQL identifier


modify PREPARSE_execute function to build DPB stream for dialect/isc_dpb_SQL_dialect right after building the DPB stream for isc_dpb_overwrite


modify get_option function to extract the DB SQL dialect information from the dpb and stored when opcode isc_dpb_set_db_SQL_dialect is found

modify GDS_ATTACH_DATABASE function to invoke PAG_set_db_SQL_dialect function when option.dpb_set_db_SQL_dialect is not empty


modify grant_trigger system trigger to upper case the new.rdb$user only when the new.rdb$user_type is:

  1. obj_relation --- 0
  2. obj_computed --- 3
  3. obj_validation --- 4
  4. obj_expression_index --- 6
  5. obj_exception --- 7
  6. obj_user --- 8
  7. obj_field --- 9
  8. obj_index --- 10
  9. obj_count --- 11
  1. obj_user_group --- 12


modify name_length function to calculate the length of name that has blanks as part of the name


modify terminate function to allow blanks as part of the string


modify MET_exact_name function to allow blanks as part of the string

modify name_length function to calculate the length of name that has blanks as part of the name


modify SCl_get_class function to allow blanks as part of the class name by invoking MET_exact_name function


modify par_condition and par_conditions function by removing the logic of upper casing SQD identifier when the BLR type is blr_exception


add new function PAG_set_db_SQL_dialect to replace the old DB SQL dialect with the new DB SQL dialect


modify ALICE_gfix function to process the new command line option -sql_dialect n


modify build_dpb function to pass down the specified new DB SQL dialect when sw_set_db_dialect switch was set.

New/Affected Modules


  1. isql.h
  2. show_proto.h
  3. isql.e
  4. show.e
  5. extract.e


  1. dsql.h
  2. parse_proto.h
  3. parse.y
  4. parse.sed
  5. metd.e
  6. dsql.c
  7. gen.c
  8. hsh.c
  9. preparse.c


  1. met_proto.h
  2. trig.h
  3. grant.gdl
  4. dfw.e
  5. grant.e
  6. met.e
  7. scl.e
  8. par.c

Testing Considerations

  1. create/alter/connect database --- InterBase treats database name as file name and processed as a string constant, therefore it must be delimited by single quotes.
  2. create shadow --- InterBase treats file names as string constants, therefore it must be delimited by single quotes.
  3. create/alter/drop/show domains
  4. create/alter/drop/show tables
  5. constraint name
  6. correlation/alias names
  7. select/insert/update/delete with delimited table/column name
  8. create/alter/drop/show exceptions
  9. create/alter/drop/show procedures
  10. create/alter/drop/show triggers
  11. create/alter/drop/show/set statistic indexes
  12. create/alter/drop views
  13. create/set generators
  14. create/drop roles
  15. grant/revoke
  16. isql -extract
  17. declare tables (handled by GPRE)
  18. declare/prepare/describe/execute statements (handled by GPRE)
  19. declare/open/fetch/close cursors (handled by GPRE)
  20. events (handled by GPRE)
  21. set transactions (handled by GPRE)
  22. UDF/filters (N/A)