Support for Delimited SQL Identifiers - Design Specification

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.

Internal Data Structures

isql/isql.h:

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

dsql/.h:

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

jrd/ibase.h:

#define isc_spb_prp_set_sql_dialect 14

alice/aliceswi.h:

#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

alice/alice.h:

USHORT  ua_db_SQL_dialect --- to store specified DB dialect

Detailed Algorithm

isql/isql.e:

ISQL_blankterm:

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

isql/show.e:

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

isql/extract.e:

processing the extract function

dsql/dsql.c:

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

dsql/parse.y:

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

dsql/metd.e:

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

dsql/gen.c:

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.

dsql/hsh.c:

modify hash function no to upper case any SQL identifier

dsql/preparse.c:

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

jrd/jrd.c:

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

jrd/grant.gdl:

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

10. obj_user_group --- 12

jrd/dfw.e:

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

jrd/grant.e:

modify terminate function to allow blanks as part of the string

jrd/met.e:

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

jrd/scl.e:

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

jrd/par.c:

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

jrd/pag.c:

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

alice/alice.c:

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

alice/exe.c:

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

New/Affected Modules

isql:

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

dsql:

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

jrd:

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