Data Mappings

Contents

Introduction

• The type of the data in the application. Here this is called the ROOT basic type and is encoded by the Dbi::DataTypes enum.
• The type as returned by TSQLColumnInfo::GetSQLType() and enumerated in TSQLServer::ESQLDataTypes:-

        kSQL_NONE = -1,      // data type unknown
        kSQL_CHAR = 1,       // CHAR(n) - string with fixed length n
        kSQL_VARCHAR = 2,    // VARCHAR(n) - string with variable length upto n
        kSQL_INTEGER = 3,    // INTEGER, INT - integer value
        kSQL_FLOAT = 4,      // FLOAT - float value
        kSQL_DOUBLE = 5,     // DOUBLE - double value
        kSQL_NUMERIC = 6,    // NUMERIC - numeric values with length and precion
        kSQL_BINARY = 7,     // BLOB - binary data
        kSQL_TIMESTAMP = 8   // TIMESTAMP 
     };
  

• In a MySQL database, using MySQL DDL. Here is a partial set:-

    1 MYSQL_TYPE_TINY 	TINYINT field
    2 MYSQL_TYPE_SHORT 	SMALLINT field
    3 MYSQL_TYPE_LONG 	INTEGER field
    9 MYSQL_TYPE_INT24 	MEDIUMINT field
    8 MYSQL_TYPE_LONGLONG 	BIGINT field
      MYSQL_TYPE_DECIMAL 	DECIMAL or NUMERIC field
  246 MYSQL_TYPE_NEWDECIMAL 	Precision math DECIMAL or NUMERIC field (MySQL 5.0.3 and up)
    4 MYSQL_TYPE_FLOAT 	FLOAT field
    5 MYSQL_TYPE_DOUBLE 	DOUBLE or REAL field
      MYSQL_TYPE_BIT 	BIT field (MySQL 5.0.3 and up)
    7 MYSQL_TYPE_TIMESTAMP 	TIMESTAMP field
   10 MYSQL_TYPE_DATE 	DATE field
   11 MYSQL_TYPE_TIME 	TIME field
   12 MYSQL_TYPE_DATETIME 	DATETIME field
   13 MYSQL_TYPE_YEAR 	YEAR field
  254 MYSQL_TYPE_STRING 	CHAR or BINARY field
  253 MYSQL_TYPE_VAR_STRING 	VARCHAR or VARBINARY field
  252 MYSQL_TYPE_BLOB 	BLOB or TEXT field (use max_length to determine the maximum length)
  248 MYSQL_TYPE_SET 	SET field
      MYSQL_TYPE_ENUM 	ENUM field
      MYSQL_TYPE_GEOMETRY 	Spatial field
    6 MYSQL_TYPE_NULL 	NULL-type field
      MYSQL_TYPE_CHAR 	Deprecated; use MYSQL_TYPE_TINY instead. 
  

• In an update file used to distribute updates. In this file it is always stored as an ASCII string but is accompanied by the MySQL SQL that encodes its DDL.

• From the MySQL database via ROOT's TSQL into the application.

• From application to MySQL database for which the data is first converted to ASCII strings which is then used to construct SQL commands to update the database.

• Between databases i.e. database -> update file -> database. In fact this uses the same mappings as the above: the update file is simply a set of SQL commands to update the database.

Conversions are handled DbiFieldType which can be created from:-

1)  DbiFieldType(Int_t type = Dbi::kInt);

    Where:-

        type corresponds to ROOT's types (Dbi:: DataTypes)
  
2)  DbiFieldType(Int_t type,
                 Int_t size,
                 const char* typeName);

    Where:-

       type           value from TSQLColumnInfo::GetSQLType()
       size           value from TSQLColumnInfo::GetLength()
       dbType         value from TSQLColumnInfo::GetTypeName()

3)  DbiFieldType(const string& sql precision);


    Where:-

        sql          A MySQL type as a string.

1)  string AsString() const;

    The C++ name, but with first letter capitalised and extended to
    include TString and Date

2)  string AsSQLString(Dbi::DbTypes dbType = Dbi::kMySQL) const;

    The MySQL type as a string.

Standard Mappings

---- DbiFieldType  -----        ----  Root   ----    TSQL    ----  MySQL  ----  
Type    Concept      Size       Type     DataTypes   type    type    len        

Bool      Bool          1       Bool          1       

Tiny      Int           1       Tiny          4       3      TINYINT     4       
UTiny     Int           1       UTiny         5       
Short     Int           2       Short         6       3      SMALLINT    6        
UShort    UInt          2       UShort        7       
Int       Int           4       Int           8       3      INT        11        
UInt      UInt          4       UInt          9       
Long      Int           8       Long         10       3      BIGINT     20        
ULong     UInt          8       ULong        11         
Float     Float         4       Float        12       4      FLOAT      12        
Double    Float         8       Double       13       5      DOUBLE     22        

Char      Char          1       Char          2       1      CHAR        1        
UChar     UChar         1       UChar         3       
String    String      255                             2      TINTTEXT   -1        
String    String    65535       String       14       2      TEXT       -1        
TString   String    65535       TString      15       

Date      Date         19       Date         16       8      DATETIME   19        
Unknown   Unknown       0       Unknown       0      -1

Char      Char          i                             1      CHAR(i)     i        
String    String        i                             2      VARCHAR(i)  i       

• Tiny is not a ROOT type but is included to simplify mapping to MySQL types.

• The are a number of MySQL character/string types. Those that we use are CHAR, CHAR(n), VARCHAR(n), TINTEXT, TINTEXT(n), TEXT and TEXT(n). They all map to just two types kSQL_CHAR and kSQL_VARCHAR. This situation is further complicated by the way the type changes when qualified by a length (n):-

  • MySQL automatically promotes type if they have insufficient capacity, for example TINYTEXT(1000) is promoted to TEXT(1000).

  • CHAR(n) is returned as kSQL_VARCHAR if n > 3.
  When creating a DbiFieldType object from a MySQL character or string its Size data member is use to preserve, as best it can ,information about the MySQL type. For example the size 65535 represents TEXT and 255 TINYTEXT.

• The DBI supports signed in application data (i.e. ROOT types) but not in database types when even if the database supports the sign attribute it is ignored.

Unsigned Values

1. Reading table row from the Database
   For unsigned integers the DbiResultSet operator >> methods store the number first in a signed value, to ensure that reading for GetString does not fail (using istringstream to write a character representation of a negative number to an unsigned integer is an error) and then, if necessary, mask off any leading sign bit extension (for example when reading from a TINYINT into a short).

2. Writing table row to the the Database
   All data is first stored as SQL commands in a DbiSqlValPacket which is then executed directly by the client . So the data must be written as signed.

   To achieve this the DbiOutRowStream operator << methods for unsigned integers first converts the value to signed (using a cast to preserve the bits) and then tests to see if the sign bit (whose position is determined by the capacity of the receiving column is set, and if so extends the sign bit.

   Historical note: the code still has partial support for unsigned database types, which explains why it still tests to see that it is signed before doing the conversion.

3. Transmitting between Databases
   This time the DbiSqlValPacket is built by the exporting database directly from TSQLStatement::GetString data so will always be returned as signed integers. After transmission to the importing database the DbiSqlValPacket is reconstructed from the text file resulting exactly the same SQL as if writing directly from the application (see the previous section).