(********************************************************************)
(*                                                                  *)
(*  db_prop.s7i   Collection of database properties.                *)
(*  Copyright (C) 2014, 2017 - 2019  Thomas Mertes                  *)
(*                                                                  *)
(*  This file is part of the Seed7 Runtime Library.                 *)
(*                                                                  *)
(*  The Seed7 Runtime Library is free software; you can             *)
(*  redistribute it and/or modify it under the terms of the GNU     *)
(*  Lesser General Public License as published by the Free Software *)
(*  Foundation; either version 2.1 of the License, or (at your      *)
(*  option) any later version.                                      *)
(*                                                                  *)
(*  The Seed7 Runtime Library is distributed in the hope that it    *)
(*  will be useful, but WITHOUT ANY WARRANTY; without even the      *)
(*  implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR *)
(*  PURPOSE.  See the GNU Lesser General Public License for more    *)
(*  details.                                                        *)
(*                                                                  *)
(*  You should have received a copy of the GNU Lesser General       *)
(*  Public License along with this program; if not, write to the    *)
(*  Free Software Foundation, Inc., 51 Franklin Street,             *)
(*  Fifth Floor, Boston, MA  02110-1301, USA.                       *)
(*                                                                  *)
(********************************************************************)


const func set of char: forbiddenInFieldName (in dbCategory: databaseKind) is func
  result
    var set of char: forbidden is (set of char).EMPTY_SET;
  begin
    case databaseKind of
      when {DB_MYSQL}:      forbidden := {'\0;', ' ', '\t', '\n'};
      when {DB_SQLITE}:     forbidden := {'\0;'};
      when {DB_POSTGRESQL}: forbidden := {'\0;'};
      when {DB_OCI}:        forbidden := {'\0;', '\"'};
      when {DB_FIRE}:       forbidden := (set of char).EMPTY_SET;
      when {DB_SQL_SERVER}: forbidden := {'\0;'};
      when {DB_DB2}:        forbidden := (set of char).EMPTY_SET;
    end case;
  end func;


const func boolean: allowedInFieldName (in dbCategory: databaseKind, in char: ch) is
    return ch not in forbiddenInFieldName(databaseKind);


const func boolean: transactionLocks (in dbCategory: databaseKind) is func
  result
    var boolean: transactionLocks is FALSE;
  begin
    case databaseKind of
      when {DB_MYSQL}:      transactionLocks := FALSE;
      when {DB_SQLITE}:     transactionLocks := FALSE;
      when {DB_POSTGRESQL}: transactionLocks := FALSE;
      when {DB_OCI}:        transactionLocks := FALSE;
      when {DB_FIRE}:       transactionLocks := FALSE;
      when {DB_SQL_SERVER}: transactionLocks := TRUE;
      when {DB_DB2}:        transactionLocks := TRUE;
    end case;
  end func;


(**
 *  Determine the SQL datatype capable to store 8-bit integers.
 *  This 8-bit integer datatype might be signed or unsigned.
 *  @return the name of the SQL datatype for 8-bit integers.
 *)
const func string: int8Type (in dbCategory: databaseKind) is func
  result
    var string: int8Type is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      int8Type := "TINYINT";
      when {DB_SQLITE}:     int8Type := "TINYINT";
      when {DB_POSTGRESQL}: int8Type := "SMALLINT";
      when {DB_OCI}:        int8Type := "SMALLINT";
      when {DB_FIRE}:       int8Type := "SMALLINT";
      when {DB_SQL_SERVER}: int8Type := "TINYINT";
      when {DB_DB2}:        int8Type := "SMALLINT";
    end case;
  end func;


(**
 *  Determine the maximum value for the 8-bit integer datatype.
 *  This maximum shows if the 8-bit integer datatype is signed or unsigned.
 *  @return the maximum value for the 8-bit integer datatype.
 *)
const func integer: maxInt8Value (in dbCategory: databaseKind) is func
  result
    var integer: maxInt8Value is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      maxInt8Value := 127;
      when {DB_SQLITE}:     maxInt8Value := 127;
      when {DB_POSTGRESQL}: maxInt8Value := 127;
      when {DB_OCI}:        maxInt8Value := 127;
      when {DB_FIRE}:       maxInt8Value := 127;
      when {DB_SQL_SERVER}: maxInt8Value := 255;
      when {DB_DB2}:        maxInt8Value := 127;
    end case;
  end func;


(**
 *  Determine the SQL datatype capable to store 64-bit signed integers.
 *  @return the name of the SQL datatype for 64-bit integers.
 *)
const func string: int64Type (in dbCategory: databaseKind) is func
  result
    var string: int64Type is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      int64Type := "BIGINT";
      when {DB_SQLITE}:     int64Type := "BIGINT";
      when {DB_POSTGRESQL}: int64Type := "BIGINT";
      when {DB_OCI}:        int64Type := "INTEGER";
      when {DB_FIRE}:       int64Type := "BIGINT";
      when {DB_SQL_SERVER}: int64Type := "BIGINT";
      when {DB_DB2}:        int64Type := "BIGINT";
    end case;
  end func;


const func boolean: withRangeCheck (in dbCategory: databaseKind) is func
  result
    var boolean: withRangeCheck is FALSE;
  begin
    case databaseKind of
      when {DB_MYSQL}:      withRangeCheck := FALSE;
      when {DB_SQLITE}:     withRangeCheck := FALSE;
      when {DB_POSTGRESQL}: withRangeCheck := TRUE;
      when {DB_OCI}:        withRangeCheck := FALSE;
      when {DB_FIRE}:       withRangeCheck := TRUE;
      when {DB_SQL_SERVER}: withRangeCheck := TRUE;
      when {DB_DB2}:        withRangeCheck := TRUE;
    end case;
  end func;


const func string: bigIntType (in dbCategory: databaseKind) is func
  result
    var string: bigIntType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      bigIntType := "DECIMAL(65)";
      when {DB_SQLITE}:     bigIntType := "DECIMAL";
      when {DB_POSTGRESQL}: bigIntType := "DECIMAL";
      when {DB_OCI}:        bigIntType := "DECIMAL";
      when {DB_FIRE}:       bigIntType := "DECIMAL(18)";
      when {DB_SQL_SERVER}: bigIntType := "DECIMAL(38)";
      when {DB_DB2}:        bigIntType := "DECIMAL(31)";
    end case;
  end func;


const func integer: sqlIntLiteralBits (in dbCategory: databaseKind) is func
  result
    var integer: sqlIntLiteralBits is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      sqlIntLiteralBits := 216;
      when {DB_SQLITE}:     sqlIntLiteralBits := 64;
      when {DB_POSTGRESQL}: sqlIntLiteralBits := 435411;
      when {DB_OCI}:        sqlIntLiteralBits := 127;
      when {DB_FIRE}:       sqlIntLiteralBits := 64;
      when {DB_SQL_SERVER}: sqlIntLiteralBits := 127;
      when {DB_DB2}:        sqlIntLiteralBits := 103;
    end case;
  end func;


const func integer: bigIntTypeBits (in dbCategory: databaseKind) is func
  result
    var integer: bigIntTypeBits is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      bigIntTypeBits := 216;
      when {DB_SQLITE}:     bigIntTypeBits := integer.last;
      when {DB_POSTGRESQL}: bigIntTypeBits := 435411;
      when {DB_OCI}:        bigIntTypeBits := 127;
      when {DB_FIRE}:       bigIntTypeBits := 64;
      when {DB_SQL_SERVER}: bigIntTypeBits := 127;
      when {DB_DB2}:        bigIntTypeBits := 103;
    end case;
  end func;


(**
 *  Determine the SQL datatype capable to store single precision floats.
 *  @return the name of the SQL datatype for single precision floats.
 *)
const func string: floatType (in dbCategory: databaseKind) is func
  result
    var string: floatType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      floatType := "FLOAT";
      when {DB_SQLITE}:     floatType := "REAL";
      when {DB_POSTGRESQL}: floatType := "REAL";
      when {DB_OCI}:        floatType := "FLOAT";
      when {DB_FIRE}:       floatType := "FLOAT";
      when {DB_SQL_SERVER}: floatType := "REAL";
      when {DB_DB2}:        floatType := "REAL";
    end case;
  end func;


const func boolean: compareFloatAsDecimalString (in dbCategory: databaseKind) is func
  result
    var boolean: compareFloatAsDecimalString is FALSE;
  begin
    case databaseKind of
      when {DB_MYSQL}:      compareFloatAsDecimalString := FALSE;
      when {DB_SQLITE}:     compareFloatAsDecimalString := FALSE;
      when {DB_POSTGRESQL}: compareFloatAsDecimalString := FALSE;
      when {DB_OCI}:        compareFloatAsDecimalString := TRUE;
      when {DB_FIRE}:       compareFloatAsDecimalString := FALSE;
      when {DB_SQL_SERVER}: compareFloatAsDecimalString := FALSE;
      when {DB_DB2}:        compareFloatAsDecimalString := FALSE;
    end case;
  end func;


(**
 *  Determine the SQL datatype capable to store double precision floats.
 *  @return the name of the SQL datatype for double precision floats.
 *)
const func string: doubleType (in dbCategory: databaseKind) is func
  result
    var string: doubleType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      doubleType := "DOUBLE";
      when {DB_SQLITE}:     doubleType := "DOUBLE";
      when {DB_POSTGRESQL}: doubleType := "DOUBLE PRECISION";
      when {DB_OCI}:        doubleType := "DOUBLE PRECISION";
      when {DB_FIRE}:       doubleType := "DOUBLE PRECISION";
      when {DB_SQL_SERVER}: doubleType := "DOUBLE PRECISION";
      when {DB_DB2}:        doubleType := "DOUBLE";
    end case;
  end func;


const func boolean: compareDoubleAsDecimalString (in dbCategory: databaseKind) is func
  result
    var boolean: compareDoubleAsDecimalString is FALSE;
  begin
    case databaseKind of
      when {DB_MYSQL}:      compareDoubleAsDecimalString := FALSE;
      when {DB_SQLITE}:     compareDoubleAsDecimalString := FALSE;
      when {DB_POSTGRESQL}: compareDoubleAsDecimalString := FALSE;
      when {DB_OCI}:        compareDoubleAsDecimalString := TRUE;
      when {DB_FIRE}:       compareDoubleAsDecimalString := FALSE;
      when {DB_SQL_SERVER}: compareDoubleAsDecimalString := FALSE;
      when {DB_DB2}:        compareDoubleAsDecimalString := FALSE;
    end case;
  end func;


const func string: bigRatType (in dbCategory: databaseKind) is func
  result
    var string: bigRatType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      bigRatType := "DECIMAL(65,30)";
      when {DB_SQLITE}:     bigRatType := "NUMBER";
      when {DB_POSTGRESQL}: bigRatType := "DECIMAL";
      when {DB_OCI}:        bigRatType := "NUMBER";
      when {DB_FIRE}:       bigRatType := "DECIMAL(18, 9)";
      when {DB_SQL_SERVER}: bigRatType := "NUMERIC(38,15)";
      when {DB_DB2}:        bigRatType := "DECIMAL(31,15)";
    end case;
  end func;


(**
 *  Determine the SQL datatype capable to store the biggest decimal integers.
 *  @return the name of the SQL datatype for the biggest decimal integers.
 *)
const func string: decimalIntType (in dbCategory: databaseKind) is func
  result
    var string: decimalType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      decimalType := "DECIMAL(65)";   # DECIMAL(p), 1 <= p <= 65
      when {DB_SQLITE}:     decimalType := "NUMBER";
      when {DB_POSTGRESQL}: decimalType := "DECIMAL(100)";  # DECIMAL(p), 1 <= p <= 1000
      when {DB_OCI}:        decimalType := "NUMBER(38)";    # NUMBER(p),  1 <= p <= 38
      when {DB_FIRE}:       decimalType := "DECIMAL(18)";   # DECIMAL(p), 1 <= p <= 18
      when {DB_SQL_SERVER}: decimalType := "DECIMAL(38)";   # DECIMAL(p), 1 <= p <= 38
      when {DB_DB2}:        decimalType := "DECIMAL(31)";   # DECIMAL(p), 1 <= p <= 31
    end case;
  end func;


(**
 *  Determine the maximum precision of the decimal datatype.
 *  @return the precision (number of digits) of the decimal datatype.
 *)
const func integer: maxDecimalPrecision (in dbCategory: databaseKind) is func
  result
    var integer: precision is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      precision := 65;
      when {DB_SQLITE}:     precision := 100;  # NUMBER works without precision.
      when {DB_POSTGRESQL}: precision := 100;  # PostgreSQL is tested with maximum precision of 100.
      when {DB_OCI}:        precision := 38;
      when {DB_FIRE}:       precision := 18;
      when {DB_SQL_SERVER}: precision := 38;
      when {DB_DB2}:        precision := 31;
    end case;
  end func;


const func integer: maxDecimalLiteralPrecision (in dbCategory: databaseKind) is func
  result
    var integer: precision is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      precision := 65;
      when {DB_SQLITE}:     precision := 13;
      when {DB_POSTGRESQL}: precision := 100;
      when {DB_OCI}:        precision := 38;
      when {DB_FIRE}:       precision := 18;
      when {DB_SQL_SERVER}: precision := 38;
      when {DB_DB2}:        precision := 31;
    end case;
  end func;


(**
 *  Determine the SQL datatype capable to store the biggest numeric integers.
 *  @return the name of the SQL datatype for the biggest numeric integers.
 *)
const func string: numericIntType (in dbCategory: databaseKind) is func
  result
    var string: numericType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      numericType := "NUMERIC(65)";   # NUMERIC(p), 1 <= p <= 65
      when {DB_SQLITE}:     numericType := "NUMBER";
      when {DB_POSTGRESQL}: numericType := "NUMERIC(100)";  # NUMERIC(p), 1 <= p <= 1000
      when {DB_OCI}:        numericType := "NUMBER(38)";    # NUMBER(p),  1 <= p <= 38
      when {DB_FIRE}:       numericType := "NUMERIC(18)";   # NUMERIC(p), 1 <= p <= 18
      when {DB_SQL_SERVER}: numericType := "NUMERIC(38)";   # NUMERIC(p), 1 <= p <= 38
      when {DB_DB2}:        numericType := "NUMERIC(31)";   # NUMERIC(p), 1 <= p <= 31
     end case;
  end func;


(**
 *  Determine the maximum precision of the numeric datatype.
 *  @return the precision (number of digits) of the numeric datatype.
 *)
const func integer: maxNumericPrecision (in dbCategory: databaseKind) is func
  result
    var integer: precision is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      precision := 65;
      when {DB_SQLITE}:     precision := 100;  # NUMBER works without precision.
      when {DB_POSTGRESQL}: precision := 100;  # PostgreSQL is tested with maximum precision of 100.
      when {DB_OCI}:        precision := 38;
      when {DB_FIRE}:       precision := 18;
      when {DB_SQL_SERVER}: precision := 38;
      when {DB_DB2}:        precision := 31;
     end case;
  end func;


const func integer: maxNumericLiteralPrecision (in dbCategory: databaseKind) is func
  result
    var integer: precision is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      precision := 65;
      when {DB_SQLITE}:     precision := 13;
      when {DB_POSTGRESQL}: precision := 100;
      when {DB_OCI}:        precision := 38;
      when {DB_FIRE}:       precision := 18;
      when {DB_SQL_SERVER}: precision := 38;
      when {DB_DB2}:        precision := 31;
    end case;
  end func;


(**
 *  Determine the decimal datatype with the highest precision and a given scale.
 *  @return the name of the decimal datatype.
 *)
const func string: decimalType (in dbCategory: databaseKind, in integer: scale) is func
  result
    var string: decimalType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      decimalType := "DECIMAL(65," <& scale <& ")";   # DECIMAL(p,s), 1 <= p <= 65, 0 <= s <= 30
      when {DB_SQLITE}:     decimalType := "NUMBER";
      when {DB_POSTGRESQL}: decimalType := "DECIMAL(100," <& scale <& ")";  # DECIMAL(p,s), 1 <= p <= 1000, 0 <= s <= p
      when {DB_OCI}:        decimalType := "NUMBER(38," <& scale <& ")";    # NUMBER(p,s),  1 <= p <= 38, -84 <= s <= 127
      when {DB_FIRE}:       decimalType := "DECIMAL(18," <& scale <& ")";   # DECIMAL(p,s), 1 <= p <= 18, 0 <= s <= p
      when {DB_SQL_SERVER}: decimalType := "DECIMAL(38," <& scale <& ")";   # DECIMAL(p,s), 1 <= p <= 38, 0 <= s <= p
      when {DB_DB2}:        decimalType := "DECIMAL(31," <& scale <& ")";   # DECIMAL(p,s), 1 <= p <= 31, 0 <= s <= p
    end case;
  end func;


(**
 *  Determine the minimum scale allowed for the decimal datatype.
 *  @return the minimum scale allowed for the decimal datatype.
 *)
const func integer: minDecimalScale (in dbCategory: databaseKind) is func
  result
    var integer: scale is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      scale := 0;
      when {DB_SQLITE}:     scale := -5;   # NUMBER works without scale.
      when {DB_POSTGRESQL}: scale := 0;
      when {DB_OCI}:        scale := -84;
      when {DB_FIRE}:       scale := 0;
      when {DB_SQL_SERVER}: scale := 0;
      when {DB_DB2}:        scale := 0;
    end case;
  end func;


(**
 *  Determine the maximum scale allowed for the decimal datatype.
 *  In several databases the scale must be less or equal to the precision.
 *  This function returns the maximum scale for the maximum precision.
 *  @return the maximum scale allowed for the decimal datatype.
 *)
const func integer: maxDecimalScale (in dbCategory: databaseKind) is func
  result
    var integer: scale is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      scale := 30;
      when {DB_SQLITE}:     scale := 10;   # NUMBER works without scale.
      when {DB_POSTGRESQL}: scale := 100;  # PostgreSQL is tested with maximum precision of 100.
      when {DB_OCI}:        scale := 127;  # The scale can be bigger than the precision.
      when {DB_FIRE}:       scale := 18;
      when {DB_SQL_SERVER}: scale := 38;
      when {DB_DB2}:        scale := 30;
    end case;
  end func;


(**
 *  Determine the numeric datatype with the highest precision and a given scale.
 *  @return the name of the numeric datatype.
 *)
const func string: numericType (in dbCategory: databaseKind, in integer: scale) is func
  result
    var string: numericType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      numericType := "NUMERIC(65," <& scale <& ")";   # NUMERIC(p,s), 1 <= p <= 65, 0 <= s <= 30
      when {DB_SQLITE}:     numericType := "NUMBER";
      when {DB_POSTGRESQL}: numericType := "NUMERIC(100," <& scale <& ")";  # NUMERIC(p,s), 1 <= p <= 1000, 0 <= s <= p
      when {DB_OCI}:        numericType := "NUMBER(38," <& scale <& ")";    # NUMBER(p,s),  1 <= p <= 38, -84 <= s <= 127
      when {DB_FIRE}:       numericType := "NUMERIC(18," <& scale <& ")";   # NUMERIC(p,s), 1 <= p <= 18, 0 <= s <= p
      when {DB_SQL_SERVER}: numericType := "NUMERIC(38," <& scale <& ")";   # NUMERIC(p,s), 1 <= p <= 38, 0 <= s <= p
      when {DB_DB2}:        numericType := "NUMERIC(31," <& scale <& ")";   # NUMERIC(p,s), 1 <= p <= 31, 0 <= s <= p
     end case;
  end func;


(**
 *  Determine the minimum scale allowed for the numeric datatype.
 *  @return the minimum scale allowed for the numeric datatype.
 *)
const func integer: minNumericScale (in dbCategory: databaseKind) is func
  result
    var integer: scale is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      scale := 0;
      when {DB_SQLITE}:     scale := -5;   # NUMBER works without scale.
      when {DB_POSTGRESQL}: scale := 0;
      when {DB_OCI}:        scale := -84;
      when {DB_FIRE}:       scale := 0;
      when {DB_SQL_SERVER}: scale := 0;
      when {DB_DB2}:        scale := 0;
    end case;
  end func;


(**
 *  Determine the maximum scale allowed for the numeric datatype.
 *  In several databases the scale must be less or equal to the precision.
 *  This function returns the maximum scale for the maximum precision.
 *  @return the maximum scale allowed for the numeric datatype.
 *)
const func integer: maxNumericScale (in dbCategory: databaseKind) is func
  result
    var integer: scale is 0;
  begin
    case databaseKind of
      when {DB_MYSQL}:      scale := 30;
      when {DB_SQLITE}:     scale := 10;   # NUMBER works without scale.
      when {DB_POSTGRESQL}: scale := 100;  # PostgreSQL is tested with maximum precision of 100.
      when {DB_OCI}:        scale := 127;  # The scale can be bigger than the precision.
      when {DB_FIRE}:       scale := 18;
      when {DB_SQL_SERVER}: scale := 38;
      when {DB_DB2}:        scale := 30;
    end case;
  end func;


const func char: maxChar1FieldCharacter (in dbCategory: databaseKind) is func
  result
    var char: maxChar1FieldCharacter is char.last;
  begin
    case databaseKind of
      when {DB_MYSQL}:      maxChar1FieldCharacter := '\8888;'; # Code page 1252
      when {DB_SQLITE}:     maxChar1FieldCharacter := char.last;
      when {DB_POSTGRESQL}: maxChar1FieldCharacter := char.last;
      when {DB_OCI}:        maxChar1FieldCharacter := char.last;
      when {DB_FIRE}:       maxChar1FieldCharacter := '\127;';
      when {DB_SQL_SERVER}: maxChar1FieldCharacter := char.last;
      when {DB_DB2}:        maxChar1FieldCharacter := char.last;
    end case;
  end func;


const func boolean: charFieldPreservesTrailingSpaces (in dbCategory: databaseKind) is func
  result
    var boolean: charFieldPreservesTrailingSpaces is FALSE;
  begin
    case databaseKind of
      when {DB_MYSQL}:      charFieldPreservesTrailingSpaces := FALSE;
      when {DB_SQLITE}:     charFieldPreservesTrailingSpaces := TRUE;
      when {DB_POSTGRESQL}: charFieldPreservesTrailingSpaces := FALSE;
      when {DB_OCI}:        charFieldPreservesTrailingSpaces := FALSE;
      when {DB_FIRE}:       charFieldPreservesTrailingSpaces := FALSE;
      when {DB_SQL_SERVER}: charFieldPreservesTrailingSpaces := FALSE;
      when {DB_DB2}:        charFieldPreservesTrailingSpaces := FALSE;
    end case;
  end func;


const func boolean: nullAllowedInStringLiteral (in dbCategory: databaseKind) is func
  result
    var boolean: nullAllowedInStringLiteral is FALSE;
  begin
    case databaseKind of
      when {DB_MYSQL}:      nullAllowedInStringLiteral := TRUE;
      when {DB_SQLITE}:     nullAllowedInStringLiteral := FALSE;
      when {DB_POSTGRESQL}: nullAllowedInStringLiteral := FALSE;
      when {DB_OCI}:        nullAllowedInStringLiteral := TRUE;
      when {DB_FIRE}:       nullAllowedInStringLiteral := TRUE;
      when {DB_SQL_SERVER}: nullAllowedInStringLiteral := FALSE;
      when {DB_DB2}:        nullAllowedInStringLiteral := TRUE;
    end case;
  end func;


const func boolean: nullAllowedInString (in dbCategory: databaseKind) is func
  result
    var boolean: nullAllowedInString is FALSE;
  begin
    case databaseKind of
      when {DB_MYSQL}:      nullAllowedInString := TRUE;
      when {DB_SQLITE}:     nullAllowedInString := TRUE;
      when {DB_POSTGRESQL}: nullAllowedInString := FALSE;
      when {DB_OCI}:        nullAllowedInString := TRUE;
      when {DB_FIRE}:       nullAllowedInString := TRUE;
      when {DB_SQL_SERVER}: nullAllowedInString := FALSE;
      when {DB_DB2}:        nullAllowedInString := TRUE;
    end case;
  end func;


(**
 *  Determine the VARCHAR datatype of the database.
 *  @return the name of the VARCHAR datatype.
 *)
const func string: varcharType (in dbCategory: databaseKind) is func
  result
    var string: varcharType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      varcharType := "VARCHAR";
      when {DB_SQLITE}:     varcharType := "VARCHAR";
      when {DB_POSTGRESQL}: varcharType := "VARCHAR";
      when {DB_OCI}:        varcharType := "VARCHAR2";
      when {DB_FIRE}:       varcharType := "VARCHAR";
      when {DB_SQL_SERVER}: varcharType := "VARCHAR";
      when {DB_DB2}:        varcharType := "VARCHAR";
    end case;
  end func;


(**
 *  Determine the BLOB datatype of the database.
 *  @return the name of the BLOB datatype.
 *)
const func string: blobType (in dbCategory: databaseKind) is func
  result
    var string: blobType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      blobType := "LONGBLOB";
      when {DB_SQLITE}:     blobType := "BLOB";
      when {DB_POSTGRESQL}: blobType := "BYTEA";
      when {DB_OCI}:        blobType := "BLOB";
      when {DB_FIRE}:       blobType := "BLOB";
      when {DB_SQL_SERVER}: blobType := "VARBINARY(MAX)";
      when {DB_DB2}:        blobType := "BLOB(2G)";
    end case;
  end func;


(**
 *  Determine the CBLOB datatype of the database.
 *  @return the name of the CBLOB datatype.
 *)
const func string: clobType (in dbCategory: databaseKind) is func
  result
    var string: clobType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      clobType := "LONGTEXT";
      when {DB_SQLITE}:     clobType := "BLOB";
      when {DB_POSTGRESQL}: clobType := "TEXT";
      when {DB_OCI}:        clobType := "CLOB";
      when {DB_FIRE}:       clobType := "BLOB SUB_TYPE TEXT";
      when {DB_SQL_SERVER}: clobType := "VARCHAR(MAX)";
      when {DB_DB2}:        clobType := "CLOB(2G)";
    end case;
  end func;


const func time: minDate (in dbCategory: databaseKind) is func
  result
    var time: minDate is time.value;
  begin
    case databaseKind of
      when {DB_MYSQL}:      minDate := time("0-1-1 0:0:0");
      when {DB_SQLITE}:     minDate := time("0-1-1 0:0:0");
      when {DB_POSTGRESQL}: minDate := time("-4712-1-1 0:0:0");
      when {DB_OCI}:        minDate := time("-4711-1-1 0:0:0");
      when {DB_FIRE}:       minDate := time("0-1-1 0:0:0");
      when {DB_SQL_SERVER}: minDate := time("1-1-1 0:0:0");
      when {DB_DB2}:        minDate := time("1-1-1 0:0:0");
    end case;
  end func;


const func string: toDate (in dbCategory: databaseKind, in string: dateStri) is func
  result
    var string: toDate is "";
  local
    var time: aTime is time.value;
  begin
    case databaseKind of
      when {DB_MYSQL}:
          aTime := time(dateStri);
          toDate := "'" <& aTime.year lpad0 4 <& "-" <&
                    aTime.month <& "-" <& aTime.day <& "'";
      when {DB_SQLITE}:     toDate := "'" & dateStri & "'";
      when {DB_POSTGRESQL}:
          aTime := time(dateStri);
          if aTime.year <= 0 then
            toDate := "make_date(" <& pred(aTime.year) <& ", " <&
                      aTime.month <& ", " <& aTime.day <& ")";
          else
            toDate := "make_date(" <& aTime.year <& ", " <&
                      aTime.month <& ", " <& aTime.day <& ")";
          end if;
      when {DB_OCI}:
          if dateStri[1] = '-' or dateStri[1] = '0' then
            aTime := time(dateStri);
            toDate := "to_date('BC " <&
                succ(-aTime.year) <& "-" <& aTime.month <& "-" <& aTime.day <&
                "', 'BC YYYY-MM-DD')";
          else
            toDate := "to_date('" <& dateStri <& "', 'YYYY-MM-DD')";
          end if;
      when {DB_FIRE}:       toDate := "'" & dateStri & "'";
      when {DB_SQL_SERVER}:
          aTime := time(dateStri);
          toDate := "'" <& aTime.year lpad0 4 <& "-" <&
                    aTime.month <& "-" <& aTime.day <& "'";
      when {DB_DB2}:
          if dateStri[1] = '-' or dateStri[1] = '0' then
            aTime := time(dateStri);
            toDate := "to_date('" <&
                pred(aTime.year) <& "-" <& aTime.month <& "-" <& aTime.day <&
                "', 'YYYY-MM-DD')";
          else
            toDate := "to_date('" <& dateStri <& "', 'YYYY-MM-DD')";
          end if;
    end case;
  end func;


const func string: timeType (in dbCategory: databaseKind) is func
  result
    var string: timeType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      timeType := "TIME";
      when {DB_SQLITE}:     timeType := "TIME";
      when {DB_POSTGRESQL}: timeType := "TIME";
      when {DB_OCI}:        timeType := "DATE";
      when {DB_FIRE}:       timeType := "TIME";
      when {DB_SQL_SERVER}: timeType := "TIME";
      when {DB_DB2}:        timeType := "TIME";
    end case;
  end func;


const func string: toTime (in dbCategory: databaseKind, in string: timeStri) is func
  result
    var string: toTime is "";
  local
    var time: aTime is time.value;
  begin
    case databaseKind of
      when {DB_MYSQL}:      toTime := "'" & timeStri & "'";
      when {DB_SQLITE}:     toTime := "'" & timeStri & "'";
      when {DB_POSTGRESQL}: toTime := "'" & timeStri & "'";
      when {DB_OCI}:
        toTime := "to_date('BC 1-01-01 " <& timeStri <& "', 'BC YYYY-MM-DD HH24:MI:SS')";
      when {DB_FIRE}:       toTime := "'" & timeStri & "'";
      when {DB_SQL_SERVER}: toTime := "'" & timeStri & "'";
      when {DB_DB2}:        toTime := "'" & timeStri & "'";
        aTime := time(timeStri);
        toTime := "time(to_timestamp('" <&
                  aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <&
                  "." <& aTime.micro_second lpad0 6 <&
                  "', 'HH24:MI:SS.FF'))";
    end case;
  end func;


const func string: dateTimeType (in dbCategory: databaseKind) is func
  result
    var string: dateTimeType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      dateTimeType := "DATETIME";
      when {DB_SQLITE}:     dateTimeType := "DATETIME";
      when {DB_POSTGRESQL}: dateTimeType := "TIMESTAMP";
      when {DB_OCI}:        dateTimeType := "DATE";
      when {DB_FIRE}:       dateTimeType := "DATETIME";
      when {DB_SQL_SERVER}: dateTimeType := "DATETIME2";
      when {DB_DB2}:        dateTimeType := "TIMESTAMP";
    end case;
  end func;


const func string: toDateTime (in dbCategory: databaseKind, in string: dateTime) is func
  result
    var string: toDateTime is "";
  local
    var time: aTime is time.value;
  begin
    case databaseKind of
      when {DB_MYSQL}:
          aTime := time(dateTime);
          toDateTime := "'" <& aTime.year lpad0 4 <& "-" <&
              aTime.month <& "-" <& aTime.day <& " " <&
              aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <& "'";
      when {DB_SQLITE}:     toDateTime := "'" & dateTime & "'";
      when {DB_POSTGRESQL}:
          aTime := time(dateTime);
          if aTime.year <= 0 then
            # make_timestamp does not work for BC.
            toDateTime := "make_date(" <& pred(aTime.year) <& ", " <&
                aTime.month <& ", " <& aTime.day <& ") + time '" <&
                aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <& "'";
          else
            toDateTime := "make_timestamp(" <&
                aTime.year <& ", " <& aTime.month <& ", " <& aTime.day <& ", " <&
                aTime.hour <& ", " <& aTime.minute <& ", " <& aTime.second <& ")";
          end if;
      when {DB_OCI}:
          if dateTime[1] = '-' or dateTime[1] = '0' then
            aTime := time(dateTime);
            toDateTime := "to_date('BC " <&
                succ(-aTime.year) <& "-" <& aTime.month <& "-" <& aTime.day <&
                " " <& aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <&
                "', 'BC YYYY-MM-DD HH24:MI:SS')";
          else
            toDateTime := "to_date('" <& dateTime <& "', 'YYYY-MM-DD HH24:MI:SS')";
          end if;
      when {DB_FIRE}:       toDateTime := "'" & dateTime & "'";
      when {DB_SQL_SERVER}:
          aTime := time(dateTime);
          toDateTime := "'" <& aTime.year lpad0 4 <& "-" <&
              aTime.month <& "-" <& aTime.day <& " " <&
              aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <& "'";
      when {DB_DB2}:
          if dateTime[1] = '-' or dateTime[1] = '0' then
            aTime := time(dateTime);
            toDateTime := "to_date('" <&
                pred(aTime.year) <& "-" <& aTime.month <& "-" <& aTime.day <&
                " " <& aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <&
                "', 'YYYY-MM-DD HH24:MI:SS')";
          else
            toDateTime := "to_date('" <& dateTime <& "', 'YYYY-MM-DD HH24:MI:SS')";
          end if;
    end case;
  end func;


const func string: timeStampType (in dbCategory: databaseKind) is func
  result
    var string: timeStampType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      timeStampType := "TIMESTAMP(6) NULL";
      when {DB_SQLITE}:     timeStampType := "VARCHAR";
      when {DB_POSTGRESQL}: timeStampType := "TIMESTAMP";
      when {DB_OCI}:        timeStampType := "TIMESTAMP";
      when {DB_FIRE}:       timeStampType := "TIMESTAMP";
      when {DB_SQL_SERVER}: timeStampType := "DATETIME2";
      when {DB_DB2}:        timeStampType := "TIMESTAMP";
    end case;
  end func;


const func time: minTimeStamp (in dbCategory: databaseKind) is func
  result
    var time: minTimeStamp is time.value;
  begin
    case databaseKind of
      when {DB_MYSQL}:      minTimeStamp := time("1970-1-2 0:0:0");
      when {DB_SQLITE}:     minTimeStamp := time("0-1-1 0:0:0");
      when {DB_POSTGRESQL}: minTimeStamp := time("-4712-1-1 0:0:0");
      when {DB_OCI}:        minTimeStamp := time("0-1-1 0:0:0");
      when {DB_FIRE}:       minTimeStamp := time("0-1-1 0:0:0");
      when {DB_SQL_SERVER}: minTimeStamp := time("1-1-1 0:0:0");
      when {DB_DB2}:        minTimeStamp := time("1-1-1 0:0:0");
    end case;
  end func;


const func string: toTimeStamp (in dbCategory: databaseKind, in string: timeStamp) is func
  result
    var string: toTimeStamp is "";
  local
    var time: aTime is time.value;
  begin
    case databaseKind of
      when {DB_MYSQL}:      toTimeStamp := "'" & timeStamp & "'";
      when {DB_SQLITE}:     toTimeStamp := "'" & timeStamp & "'";
      when {DB_POSTGRESQL}:
          aTime := time(timeStamp);
          if aTime.year <= 0 then
            # make_timestamp does not work for BC.
            toTimeStamp := "make_date(" <& pred(aTime.year) <& ", " <&
                aTime.month <& ", " <& aTime.day <& ") + time '" <&
                aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <& "." <&
                aTime.micro_second lpad0 6 <& "'";
          else
            toTimeStamp := "make_timestamp(" <&
                aTime.year <& ", " <& aTime.month <& ", " <& aTime.day <& ", " <&
                aTime.hour <& ", " <& aTime.minute <& ", " <& aTime.second <& "." <&
                aTime.micro_second lpad0 6 <& ")";
          end if;
      when {DB_OCI}:
          if timeStamp[1] = '-' or timeStamp[1] = '0' then
            aTime := time(timeStamp);
            toTimeStamp := "to_timestamp('BC " <&
                succ(-aTime.year) <& "-" <& aTime.month <& "-" <& aTime.day <&
                " " <& aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <&
                "." <& aTime.micro_second lpad0 6 <&
                "', 'BC YYYY-MM-DD HH24:MI:SS.FF')";
          else
            toTimeStamp := "to_timestamp('" <& timeStamp <& "', 'YYYY-MM-DD HH24:MI:SS.FF')";
          end if;
      when {DB_FIRE}:       toTimeStamp := "'" & timeStamp & "'";
      when {DB_SQL_SERVER}:
          aTime := time(timeStamp);
          toTimeStamp := "'" <& aTime.year lpad0 4 <& "-" <&
              aTime.month <& "-" <& aTime.day <& " " <&
              aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <&
              "." <& aTime.micro_second lpad0 6 <& "'";
      when {DB_DB2}:
          aTime := time(timeStamp);
          if aTime.year <= 0 then
            toTimeStamp := "to_timestamp('" <&
                pred(aTime.year) <& "-" <& aTime.month <& "-" <& aTime.day <&
                " " <& aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <&
                "." <& aTime.micro_second lpad0 6 <&
                "', 'YYYY-MM-DD HH24:MI:SS.FF')";
          else
            toTimeStamp := "to_timestamp('" <&
                aTime.year <& "-" <& aTime.month <& "-" <& aTime.day <&
                " " <& aTime.hour <& ":" <& aTime.minute <& ":" <& aTime.second <&
                "." <& aTime.micro_second lpad0 6 <&
                "', 'YYYY-MM-DD HH24:MI:SS.FF')";
          end if;
    end case;
  end func;


const func string: timeStampTzType (in dbCategory: databaseKind) is func
  result
    var string: timeStampTzType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      timeStampTzType := "TIMESTAMP(6) NULL";
      when {DB_SQLITE}:     timeStampTzType := "VARCHAR";
      when {DB_POSTGRESQL}: timeStampTzType := "TIMESTAMP";
      when {DB_OCI}:        timeStampTzType := "TIMESTAMP WITH TIME ZONE";
      when {DB_FIRE}:       timeStampTzType := "TIMESTAMP";
      when {DB_SQL_SERVER}: timeStampTzType := "DATETIME2";
      when {DB_DB2}:        timeStampTzType := "TIMESTAMP";
    end case;
  end func;


const func string: durationType (in dbCategory: databaseKind) is func
  result
    var string: durationType is "";
  begin
    case databaseKind of
      when {DB_MYSQL}:      durationType := "DATETIME";
      when {DB_SQLITE}:     durationType := "VARCHAR(32)";
      when {DB_POSTGRESQL}: durationType := "INTERVAL";
      when {DB_OCI}:        durationType := "INTERVAL DAY TO SECOND";
      when {DB_FIRE}:       durationType := "TIMESTAMP";
      when {DB_SQL_SERVER}: durationType := "DATETIMEOFFSET"; # "INTERVAL DAY(3) TO SECOND(6)";
      when {DB_DB2}:        durationType := "TIMESTAMP";
    end case;
  end func;


const func boolean: supportsFloatingDecimals (in dbCategory: databaseKind) is func
  result
    var boolean: supportsFloatingDecimals is FALSE;
  begin
    case databaseKind of
      when {DB_MYSQL}:      supportsFloatingDecimals := FALSE;
      when {DB_SQLITE}:     supportsFloatingDecimals := TRUE;
      when {DB_POSTGRESQL}: supportsFloatingDecimals := TRUE;
      when {DB_OCI}:        supportsFloatingDecimals := TRUE;
      when {DB_SQL_SERVER}: supportsFloatingDecimals := FALSE;
      when {DB_DB2}:        supportsFloatingDecimals := TRUE;
    end case;
  end func;