SQL Dialects - Design Specification

High-Level Algorithm

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

• 1 - InterBase parser processes as it did in InterBase V5. e.g.: a string constant could be delimited either by single quotes or double quotes
• 2 - transitional flagger. InterBase V6 flags ambiguous SQL constructs and issues an error or warning message. e.g.: InterBase V6 flags any thing that is delimited by double quotes and issues an error message stating that 'a string constant must be delimited by single quotes' accompanied by a SQLCODE -104, SQLSTATE 42501.
• 3 - It processes anything delimited by single quotes as string constants and any thing delimited by double quotes as SQL delimited identifiers.

A new field - RDB$FIELD_PRECISION is added to RDB$FIELDS system table and is to be used by the 64-bit numeric project.

RDB$FIELD_PRECISION is only available in ODS 10 databases. When an application connects to a pre ODS 10 database, RDB$FIELD_PRECISION is removed from internal memory. When backing up and restoring a pre ODS 10 database, RDB$FIELD_PRECISION is added to the ODS 10 database.

One of the requirements of this project is that existing applications should not break. This requires that GBAK must also address this issue.

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_fetch2
8. 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 that specified dialect.

New 'SHOW SQL DIALECT' statement is added to InterBase V6 ISQL to show the current Client SQL Dialect setting and Database SQL Dialect value.

New 'isc_dbp_SQL_dialect' parameter is added to the InterBase V6 and to be used with isc_create_database API to create a database.

New 'isc_info_db_SQL_dialect' parameter is added to the InterBase V6 and to be used with isc_database_info API to query the database's dialect.

A new command option -set_db_SQL_dialect is added to InterBase V6 GFIX. This command line option can be used to set a ODS 10 database speaking only SQL dialect 1 or 3.

Data Structures/Class Hierarchy

New #define constructs in ibase.h:

#define SQLDA_VERSION1               1
#define SQLDA_VERSION2               2
#define SQL_DIALECT_V5               1
#define SQL_DIALECT_V6_TRANSITION    2
#define SQL_DIALECT_V6               3
#define SQL_DIALECT_CURRENT          SQL_DIALECT_V6
#define isc_dpb_SQL_dialect          63
#define isc_info_db_SQL_dialect      62

error messages:

Error Code                  Number     SQLCODE Message
isc_invalid_string_constant 335544763  -104    a string constant is delimited by double quotes

Internal Data Structures

burp/burp.h:

add att_SQL_dialect and att_field_precision to the att_type enumeration constant

dsql/dsql.h:

add dbb_db_SQL_dialect to the DBB database block definition

isql/isql.h:

add EXTERN USHORT SQL_dialect

jrd/jrd.h:

add the following to bit values for dbb_flags:

#define DBB_DB_SQL_dialect     0X1000L

jrd/names.h:

add NAME ("RDB$FIELD_PRECISION", nam_f_precision)

jrd/ods.h:

1. add "#define ODS_VERSION10 10"
2. add "#define ODS_CURRENT10 0"
3. add "#define ODS_10_0 ENCODE_ODS (ODS_VERSION10, 0)"
4. change "#define ODS_VERSION ODS_VERSION9" to "#define ODS_VERSION ODS_VERSION10"
5. change "#define ODS_CURRENT ODS_CURRENT9" to "#define ODS_CURRENT ODS_CURRENT10"
6. change "#define ODS_CURRENT_VERSION ODS_9_1" to "#define ODS_CURRENT_VERSION ODS_10_0"
7. add #define hdr_SQL_dialect 256 as one of the header page flag

dsql/parse_proto.h:

change dsql_yyparse (void) to dsql_yyparse (USHORT, USHORT)

dsql/prepa_proto.h:

add one more parameter USHORT to the proto type definition of PREPARSE_execute function

jrd/relations.h:

add FIELD(f_fld_precision, nam_f_precision, fld_f_position, 1, 0, 0, 0) to RELATION(nam_fields, ...)/RDB$FIELDS

dsql/parse.sed:

1. add s/yylex(void)/yylex (USHORT, USHORT)/g
2. add s/yylex()/yylex(client_dialect, db_dialect)/g
3. add s/dsql_yyparse(void)/dsql_yyparse(client_dialect, db_dialect)/g
4. add s/dsql_yyparse()/dsql_yyparse(client_dialect, db_dialect)/g
5. add s/YYLEX()/YYLEX(client_dialect, db_dialect)/g

Detailed Algorithm

isql/isql.e:

Replace all hard coded dialect 1 parameter with SQL_dialect (where the SQL_dialect is the client SQL dialect set by user) for the following APIs:

1. isc_dsql_describe
2. isc_dsql_execute
3. isc_dsql_execute2
4. isc_dsql_execute_immediate
5. isc_dsql_exec_immed2
6. isc_dsql_fetch
7. isc_dsql_fetch2
8. isc_dsql_prepare

ISQL_get_version:

1. add isc_info_db_SQL_dialect to db_version_info parameter block.
2. Query the database information by executing isc_database_info with the updated db_version_info parameter block
3. Process the isc_database_info output buffer to extract the ODS version info and database SQL dialect info.
4. Store the database dialect info into dialect_spoken

frontend:

if the input parameters are "SET" "SQL" "DIALECT"
    {
      if the dialect < 1 or dialect > 3
         issue error message
      otherwise
         save the dialect info into SQL_dialect
 }

create_db:

1. if the client dialect was not set, then pass SQL dialect 2 as one of the parameter when invoking isc_dsql_execute_immediate.
2. otherwise pass the client SQL dialect as one of the parameter when invoking isc_dsql_execute_immediate.

parse_arg:

1. initialize SQL_dialect as SQL_DIALECT_V6_TRANSITION
2. process "-sqldialect n"
3. issues error messages/USAGE1/USAGE2 when n < 1 or n > 3
4. save the command line dialect info into SQL_dialect

isql/show.e/show_dialect:

1. display client SQL dialect setting
2. getting database SQL dialect info by passing isc_info_db_SQL_dialect parameter to isc_database_info function
3. display database SQL dialect

dsql/dsql.c

add isc_info_db_SQL_dialect to db_version_info parameter block

init:

1. get the database SQL dialect info by invoking isc_database_info function
2. get the database SQL dialect info by extract database SQL dialect in out off the isc_database_info output buffer
3. save the database SQL dialect info into dbb_db_SQL_dialect

prepare:

1. verify the client dialect that was passed in is dialect 0 - 3
2. invoke dsql_yyparse by passing client_dialect and dbb_db_SQL_dialect

dsql/parse.y/yylex:

1. change "yylex PROTO ((void))" to "yylex PROTO ((USHORT, USHORT))"
2. change "yylex (void)" to "yylex (USHORT client_dialect, USHORT db_dialect)"

jrd/why.c

GDS_DSQL_EXEC_IMM2_m:

pass one more parameter --- dialect when invoking PREPARE_execute

get_sqlda_buffer:

change "else if (dialect & 1)" to "else if (dialect > DIALECT_xsqlda)"

dsql/preparse.c:

PREPARSE_execute:

1. add one more parameter --- dialect
2. when NO_MORE_TOKEN then adds isc_dpb_SQL_dialect/dialect to DPB stream
3. invoke isc_create_database (dialect is part of the dpb stream)

dsql/utld.c:

UTLD_parse_sql_info:

change "if ((dialect & 1)==DIALECT_xsqlda)" to "if (dialect >=DIALECT_xsqlda)"

UTLD_parse_sqlda:

change "if ((dialect & 1)==DIALECT_xsqlda)" to "if (dialect >=DIALECT_xsqlda)"

jrd/ini.e:

INI_init2:

when field_name is RDB$FIELD_PRECISION and the database is ODS 10 or greater, then increment field counter n

jrd/jrd.c:

add dpb_SQL_dialect to DPB (Option block for database parameter block)

GDS_CREATE_DATABASE:

if (options.dpb_SQL_dialect !=SQL_DIALECT_V5) set dbb->dbb_flags ||=DBB_DB_SQL_dialect

get_options:

1. extracting SQL dialect info out off the DPB
2. assign it to options->dpb_SQL_dialect

jrd/pag.c:

PAG_format_header:

if (dbb->dbb_flags & DBB_DB_SQL_dialect) set header->hdr_flags |=hdr_SQL_dialect

PAG_header:

if the header ODS version is not ODS_VERSION8 or ODS_VERSION9 or ODS_VERSION10
   then
      issue gds__wrong_ods message

if (header->hdr_flags & hdr_SQL_dialect) set dbb->dbb_flags |=DBB_DB_SQL_dialect.

jrd/inf.c:

INF_database_info:

loop of processing DPB

 ...
 case isc_info_SQL_dialect
     if db >=ODS_10_0
         then
            if (dbb->dbb_flags & DBB_DB_SQL_dialect)
               then
                  passes back SQL_DIALECT_V6 as database SQL dialect
              else
                  passes back SQL_DIALECT_V5 as database SQL dialect
      else
         passes back SQL_DIALECT_V5 as database SQL dialect

end-loop

burp/backup.e:

add "#define BCK_ods10 8912

add '"RDB$FIELDS", "RDB$FIELD_PRECISION", BCK_ods10, 0, 0, 0' to rfr_table structure

add isc_info_db_SQL_dialect to db_info_items parameter block

write_database:

1. invoke gds__database_info
2. extract database SQL dialect info from the gds__database_info output buffer
3. write out the database SQL dialect into to logical database record

write_global_fields:

add "(tdgbl->BCK_capabilities & BCK_ods10)" as part of the tdgbl->BCK_capabilities test

if tdgbl->BCK_capabilities is BCK_ods8 and BCK_ods10
backup the data of RDB$FIELD_PRECISION
else
if (tdgbl->BCK_capabilities & BCK_ods10)
backup the data of RDB$FIELD_PRECISION

burp/restore.e:

create_database:

1. read database record
2. extract database SQL dialect info out off the logical database record
3. build the DPB stream with addition database SQL dialect info
4. invokes isc_create_database with DPB stream

get_global_field:

1. set RDB$FIELD_PRECISION.NULL=TRUE
2. when attribute is att_field_precision:

1. set RDB$FIELD_PRECISION.NULL=FALSE
2. RDB$FIELD_PRECISION=get_numeric ()

New/Affected Modules

dsql:

1. dsql.h
2. parse_proto.h
3. prepa_proto.h
4. parse.y
5. parse.sed
6. dsql.c
7. preparse.c
8. utld.c

isql:

1. isql.h
2. isql.e
3. show.e

jrd:

1. jrd.h
2. ods.h
3. ibase.h
4. relations.h
5. names.h
6. jrd.c
7. ini.e
8. why.c
9. pag.c
10. inf.c

burp:

1. burp.h
2. backup.e
3. restore.e