[Mono-list] MySql Data Types.
Venu
venu@myvenu.com
Mon, 18 Nov 2002 19:49:44 -0800
On Mon, 2002-11-18 at 15:02, Daniel Morgan wrote:
> 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
Looks they are all fine ..
> ...
>
> 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 *
All above looks perfect to me. If you have any questions let me know ..
Thanks
>
> -----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...
--
Regards, Venu
For technical support contracts, go to https://order.mysql.com
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Venu <venu@mysql.com>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
/_/ /_/\_, /___/\___\_\___/ California, USA
<___/ www.mysql.com