[Mono-list] MySql Data Types.

[Daniel Morgan]

Ben,

Maybe someone from MySQL AB can tell us the correct mapping of data types.
What is found in the MySQL client mysql_comm.h file, what is written in
their docs,
and how to map that to a .NET data type properly.  Because what is written
in their docs is what people will typically use to create a table.

I've even considered "peeking" at other MySQL provider implementations
(Java, C, C++, or C#) to see how they do it.  The only problem is these
other implementations are either LGPL or GPL while the MySQL provider in
Mono is X11/MIT.

I'm not sure exactly how the types are to be mapped.  I made a guess when I
was creating the provider.

Here is my latest assumptions:

MySQL Type listed in  MySQL enum          DbType      MySqlType .NET Type
doc to be used in     enum_field_type     enum        .NET Type  in Mono
creating a table      in MySQL C API      in Mono     in Mono
Notes
====================  ===============     =========== ========= =========
==============
tinyint               FIELD_TYPE_TINY     Byte        TinyInt   Byte      1
byte
smallint              FIELD_TYPE_SHORT    Int16       SmallInt  Int16     2
bytes
mediumint             FIELD_TYPE_INT24    Int32       MediumInt Int32     3
bytes
int, integer          FIELD_TYPE_LONG     Int32       Int       Int32     4
bytes
bigint                FIELD_TYPE_LONGLONG Int64       BigInt    Int64     8
bytes
...

AFAIK, FIELD_TYPE_LONG maps to 4 bytes, not 8 bytes.

From the MySQL docs:

Column type  Storage required
-----------  ----------------
TINYINT  1 byte
SMALLINT  2 bytes
MEDIUMINT  3 bytes
INT  4 bytes
INTEGER  4 bytes
BIGINT  8 bytes
FLOAT(X)  4 if X <= 24 or 8 if 25 <= X <= 53
FLOAT  4 bytes
DOUBLE  8 bytes
DOUBLE PRECISION  8 bytes
REAL  8 bytes
DECIMAL(M,D)  M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
NUMERIC(M,D)  M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if

DATE  3 bytes
DATETIME  8 bytes
TIMESTAMP  4 bytes
TIME  3 bytes
YEAR  1 byte

CHAR(M)  M bytes, 1 <= M <= 255
VARCHAR(M)  L+1 bytes, where L <= M and 1 <= M <= 255
TINYBLOB, TINYTEXT  L+1 bytes, where L < 2^8
BLOB, TEXT  L+2 bytes, where L < 2^16
MEDIUMBLOB, MEDIUMTEXT  L+3 bytes, where L < 2^24
LONGBLOB, LONGTEXT  L+4 bytes, where L < 2^32
ENUM('value1','value2',...)  1 or 2 bytes, depending on the number of
enumeration values (65535 values maximum)
SET('value1','value2',...)  1, 2, 3, 4 or 8 bytes, depending on the number
of set members (64 members maximum)

The MySQL types used in the MySqlTest were based on the MySQL documentation.

System.String is a series of unicode characters (System.Char), not bytes.
For the Binary Large Objects (BLOBs) types, it could be mapped to an array
of System.Byte.
For the Character Large Objects (CLOBs) types, it could be mapped to an
array of System.Char, or it could be mapped to System.String.
There are various classes in System.Text that can be used for encoding.

This is what is in the mysql_comm.h file:
enum enum_field_types { FIELD_TYPE_DECIMAL, FIELD_TYPE_TINY,
			FIELD_TYPE_SHORT,  FIELD_TYPE_LONG,
			FIELD_TYPE_FLOAT,  FIELD_TYPE_DOUBLE,
			FIELD_TYPE_NULL,   FIELD_TYPE_TIMESTAMP,
			FIELD_TYPE_LONGLONG,FIELD_TYPE_INT24,
			FIELD_TYPE_DATE,   FIELD_TYPE_TIME,
			FIELD_TYPE_DATETIME, FIELD_TYPE_YEAR,
			FIELD_TYPE_NEWDATE,
			FIELD_TYPE_ENUM=247,
			FIELD_TYPE_SET=248,
			FIELD_TYPE_TINY_BLOB=249,
			FIELD_TYPE_MEDIUM_BLOB=250,
			FIELD_TYPE_LONG_BLOB=251,
			FIELD_TYPE_BLOB=252,
			FIELD_TYPE_VAR_STRING=253,
			FIELD_TYPE_STRING=254
};

#define FIELD_TYPE_CHAR FIELD_TYPE_TINY		/* For compability */
#define FIELD_TYPE_INTERVAL FIELD_TYPE_ENUM	/* For compability *

-----Original Message-----
From: mono-list-admin@ximian.com [mailto:mono-list-admin@ximian.com]On
Behalf Of Ben Clewett
Sent: Monday, November 18, 2002 10:21 AM
To: Daniel Morgan
Cc: Mono-List; Venu
Subject: Re: [Mono-list] MySql Data Types.


Daniel,

The plot thickens.  In the MySqlTest, the following var types are used,
which do not appear in MySqlEnumFieldTypes:

MySQL Var	.NET Var
=========	========
smallint	Int16
mediumint	Int64
int		Int32
integer		Int32
bigint		Int64
real		Double
numeric		Decimal
char		String
tinytext	String
text		String
mediumtext	String
longtext	String

And the folling appear in MySqlEnumFieldTypes, but are not used in the
MySqlTest:

MySQL Var		DbType Conversion
=========		================
FIELD_TYPE_SHORT	Int16
FIELD_TYPE_LONG		Int64
FIELD_TYPE_LONGLONG	Int64
FIELD_TYPE_INT24	Int64
FIELD_TYPE_DATETIME	DateTime
FIELD_TYPE_YEAR		Int16
FIELD_TYPE_NEWDATE	Date

Although some of these are probably a re-naming of the first, it's
confusing, especially considering the number of different integers.  For
instance 'mediumint' is Int64.  Should this map to LONG, or LONGLONG...

Also I note that FIELD_TYPE_ENUM is an Int32 in the Mono code, yet the
underlying data type MySQL interfaces is a String.  This I would guess
is that FIELD_TYPE_ENUM should be created as a mirror Enumerated list as
used internally with MySQL.  Although I can forsee terrible bugs when
the Enumerated String list is edited in SQL and not the code, and
visa-versa.

The 'blob' type is also slightly worrying, as this uses String.  But
since this is I belive supposed to be 8-bit clean binary, then using
16-bit unclean Unicode might cause problems, eg, the '\0' value.  But I
might be missunderstanding the System.String data type.

There is definitelly some fun to be had here...