SQL 92 Date Support in Gpre - Design Specification

High-Level Algorithm

gpre's list of keywords is enriched with few more so that it can parse and generate code for the new sql date, time, timestamp data types and their support functions and constants. Based on the criteria specified in the functional specification, gpre will emit appropriate host variables, blrs and dyns to correctly represent these new data types. Also while evaluating expressions gpre will generate the appropriate resultant/blr as specified by the sql date, time functional specification.

Internal Data Structures

There are no new data structures but there are quite a few new keywords and enums defined. Two new variables sw_ods_version and sw_server_version are defined to store the compile time database's ods and compile time server's version.

New keywords are: KW_TIME, KW_TIMESTAMP, KW_EXTRACT, KW_YEAR, KW_MONTH, KW_DAY, KW_WEEKDAY, KW_YEARDAY, KW_HOUR, KW_MINUTE, KW_SECOND, KW_CURRENT_TIME. KW_CURRENT_DATE, KW_CURRENT_TIMESTAMP.

New nod_types are nod_extract, nod_current_time, nod_current_date, nod_current_timestamp and nod_extract_part.

New blrs and dtypes were already created.

New flags REF_date, REF_time, REF_timestamp are created to support literal.

Detailed Algorithm

The complete gpre support for DATE, TIME and TIMESTAMP support can be divided into following parts

• Being able to parse all of the new keywords.
• To generate the appropriate field dtypes.
• Based on the dtypes emit blrs if it were a data related operation or emit dyns if it were a metadata related operation
• Perform other functions like creating appropriate host variable definitions, casting of expressions, conversion of literal, and making sure that the evaluation of expressions results in appropriate data types.

Most of the parsing work is done in the file sql.c/sqe.c. To enable the parser to parse and process the new data types and their supporting constants, They have to be defined as keywords for gpre. hsh.h and words.h contain the list of keywords that gpre uses to parse an embedded sql file.

Following is the list of new keywords in gpre.

• KW_TIME
• KW_TIMESTAMP ==> These two can be either column definitions or Literal
• KW_EXTRACT ==> This is the new SQL function EXTRACT()
• KW_MONTH
• KW_DAY
• KW_HOUR
• KW_MINUTE
• KW_SECOND
• KW_WEEKDAY
• KW_YEARDAY ==> All of the above till KW_YEAR are extract parts of the function. These make sense only in the context of EXTRACT()
• KW_CURRENT_DATE
• KW_CURRENT_TIME
• KW_CURRENT_TIMESTAMP ==> These three are constants that can be use as default values, inputs etc.

Once the parser recognizes the new keyword next step is to make sense out of it. For example if the parser sees DATE then it has to figure out if it were a literal or a column definition. If it is a column definition then gpre assigns a dtype (data type representing the column internally) to it. This is decided based on the server version, ods version and sql dialect. The function resolve_fldtype(), shown below, does exactly that.

static USHORT resolve_fldtype (KWWORDS typ)
{
char mesg[100];

switch (typ)
    {
    case  KW_DATE:
 if ((sw_ods_version < 10) && (sw_server_version < 6))
     switch (sw_sql_dialect)
  {
  case 1: return dtype_timestamp;
  case 2:
      sprintf (mesg, "Encountered column type DATE which is ambiguous in dialect %d\n", sw_sql_dialect);
      PAR_error (mesg);
      return;
  case 3:
      sprintf (mesg, "Encountered column type DATE which is not supported in ods version %d\n", sw_ods_version);
      PAR_error (mesg);
      return;
  }
 else if ((sw_ods_version==10) && (sw_server_version==6))
     switch (sw_sql_dialect)
  {
  case 1: return dtype_timestamp;
  case 2:
      sprintf (mesg, "Encountered column type DATE which is ambiguous in dialect %d\n", sw_sql_dialect);
      PAR_error (mesg);
      return;
  case 3: return dtype_sql_date;
  }
 break;

    case  KW_TIME:
 if ((sw_ods_version < 10) && (sw_server_version < 6))
     switch (sw_sql_dialect)
  {
  case 1:
  case 2:
  case 3:
      sprintf (mesg, "Encountered column type TIME which is not supported by pre 6.0 server\n");
      PAR_error (mesg);
      return;
  }
 else if ((sw_ods_version==10) && (sw_server_version==6))
     switch (sw_sql_dialect)
  {
  case 1:
  case 2:
  case 3: return dtype_sql_time;
  }
 break;

    case  KW_TIMESTAMP:
 return dtype_timestamp;
 break;
......
  }
}

After the dtype has been figured out, gpre then generates the blrs and dyns based on it. gpre generate blrs to handle data manipulation. blrs are generated as a part of request compilation after the first pass. cmp.c contains the bulk of functions that generate blr.

In case of a literal, par_primitive_value():sqe.c calls EXP_literal():exp.c to extract the string literal. EXP_literal() also creates a node i.e. nod_literal, stuffs in the string value and sets a flag indicating the type. The function cmp_literal() handles the appropriate conversion of the string literal. Based on the literal type this function creates descriptors and calls MOVG_mov() to convert the strings. MOVG_mov() is a wrapper function for CVT_mov() which in turn handles the actual conversion.

Function EXTRACT() and its constants are parsed by par_udf():sqe.c. par_udf() creates the nod_extract which has two node arguments. The first is a node called nod_extract_part which specifies what part to extract and the second one is value which could be any valid sql expression. par_udf() calls SQE_value():sqe.c to parse this second argument. The functions CME_expr() and CME_get_dtype() uses this nod_extract to validate and generate the proper blrs.

The constants are also parsed by par_udf(). par_udf() then create the appropriate node which is then used by CME_expr() and CME_get_dtype() to validate and generate the proper blrs. Constants can also be a part of column default definitions. This is taken care in the function par_field():sql.c

File cmd.c handles all of the dyn generation. Various specialized functions take care of generating the dyn for create, alter, drop etc. calls. The function put_dtype() is altered to take care of the new data types.

New/Affected Modules

All modules, new and affected, are from the component gpre.