MySQL: use varchar for longer string properties

[brentrobinson]

In pre-5.0.3 versions of MySQL, the limit for a varchar column is 255 characters. Consequently, the MySQL Provider's FdoIApplySchema implementation does the following when creating a column for a new string data property:

• if the length is 255 or less, a varchar column is created
• otherwise, a text column is created.

In version 5.0.3, the varchar limit was increased to 65535 characters. This means that the MySQL provider can also increase the threshold for creating a text column. This would resolve a current defect where an ApplySchema execution will fail if a new string property is has length 256 or greater, and a default value, since default values are not allowed on MySQL text columns.

One caveat is that the total number of bytes for all non-BLOB columns in a MySQL table cannot exceed 65535 bytes. This means that the above varchar vs. text threshold cannot be completely increased to 65535.

[brentrobinson]

Revision: 5687 Author: brentrobinson Date: 1:25:43 PM, Tuesday, September 28, 2010 Message: Ticket#701: Modified ApplySchema implementation to create varchar columns for string data properties with default values, when the length is 512 characters or less. The previous threshold was 255 characters. The threshold is still 255 for properties without default values. This is a cautious first step that allows the MySQL provider to handle larger string properties that have default values (see ticket for more details). A larger fix might be done in a later submission to raise the threshold higher and also raise it for properties without defaults.

---

Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/ColumnChar.cpp Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/ColumnChar.h Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/DbObject.cpp Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/Mgr.cpp Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/Mgr.h Modified : /trunk/Providers/GenericRdbms/Src/UnitTest/MySql/MySqlFdoApplySchemaTest.cpp Modified : /trunk/Providers/GenericRdbms/Src/UnitTest/MySql/MySqlFdoApplySchemaTest.h

[brentrobinson]

Revision: 5736 Author: brentrobinson Date: 1:20:30 PM, Monday, October 04, 2010 Message: Ticket#701: Continuation of revision 5687 to allow more string properties to be represented as varchar columns. Before a table is created or modified, the total row size is check. If it is under the maximum row size then all new string columns are assigned the varchar type. Otherwise, the larger string columns (that do not have a default value) are assigned the text type until the row size is under the maximum.

---

Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/ColumnChar.cpp Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/ColumnGeom.h Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/Database.cpp Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/Database.h Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/Table.cpp Modified : /trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/Table.h Modified : /trunk/Providers/GenericRdbms/Src/ODBC/SchemaMgr/Ph/Database.cpp Modified : /trunk/Providers/GenericRdbms/Src/ODBC/SchemaMgr/Ph/Database.h Modified : /trunk/Providers/GenericRdbms/Src/PostGis/SchemaMgr/Ph/Database.cpp Modified : /trunk/Providers/GenericRdbms/Src/PostGis/SchemaMgr/Ph/Database.h Modified : /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/Database.cpp Modified : /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/Database.h Modified : /trunk/Providers/GenericRdbms/Src/UnitTest/LogicalPhysicalBender.xslt Modified : /trunk/Providers/GenericRdbms/Src/UnitTest/MySql/MySqlFdoApplySchemaTest.cpp Modified : /trunk/Providers/GenericRdbms/Src/UnitTest/MySql/MySqlFdoApplySchemaTest.h Modified : /trunk/Providers/GenericRdbms/Src/UnitTest/MySql/MySqlSchemaMgrTests.cpp Modified : /trunk/Utilities/SchemaMgr/Inc/Sm/Ph/Database.h Modified : /trunk/Utilities/SchemaMgr/Src/Sm/Ph/Database.cpp