Bug #54837 Forward engineering model should generate logical not physical types
Submitted: 27 Jun 2010 11:54 Modified: 28 Jun 2010 23:52
Reporter: Karsten Wutzke Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.24 RC OS:Any
Assigned to: CPU Architecture:Any
Tags: Logical, physical, types

[27 Jun 2010 11:54] Karsten Wutzke
Description:
Forward engineering a model should generate LOGICAL and not physical types.

I flagged this as a bug, because it is a widely-demanded feature to create standard SQL compliant code whenever possible.

It doesn't make much sense to model BOOLEANs, INTEGER, SMALLINT etc. in WB when after all the efforts to keep the model from using too many proprietary types still results in the physical types TINYINT(1), INT(11), SMALLINT(6) etc.

We want logical, standard SQL compliant types:

http://forums.mysql.com/read.php?155,202406,202594#msg-202594

How to repeat:
Model table with BOOLEAN for example, then fw engineer

Suggested fix:
Leave all types as is.
[27 Jun 2010 12:40] Valeriy Kravchuk
I agree that silent substitution of physical types is a bug of a kind.
[27 Jun 2010 13:16] Alfredo Kojima
The problem is that even if the forward engineered types are kept intact, MySQL will use the physical 
types internally:

mysql> create table a (b boolean);
Query OK, 0 rows affected (0.13 sec)

mysql> show columns from a;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| b     | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

That will confuse things during Synchronization, for example... Meaning that either
BOOLEAN will always match TINYINT(1) no matter what is your intention or the sync will always
show false mismatches for BOOLEAN columns.
[28 Jun 2010 9:27] Mike Lischke
In addition to Alfredo's comment, keep in mind logical types are an abstraction, i.e. they might not even exist in the target db's supported type list (think of user defined types in WB). Hence WB *has* to convert them to phyiscal types in any case. As desirable as it seems, but it is fundamentally not possible to avoid the conversion from logical to physical types.
[28 Jun 2010 23:52] Karsten Wutzke
Well, but I'd really like to see the logical types in code. IMO MySQL should do the conversion to physical then, but not in code.

I probably don't fully understand the sync issues. If TINYINT(1) is reserved for BOOLEANs anyway, the conversion to and from can be done without problems AFAIK.