转载声明,转自Enterprise Architect 修改模板,适应mysql生成ddl
在使用Enterprise Architect 12创建MySQL数据时碰到诸多问题,均在上方文章中找到对应信息,转载用于备忘。
1、MySQL ODBC版本问题
无关MySQL数据库版本位数(32/64),Enterprise Architect 12只支持MySQL ODBC 32位的版本,使用64位版本会报ODBC配置错误。
2、生成DDL后的SQL文件无法导入MySQL中
主要碰到2个问题:
1、SET FOREIGN_KEY_CHECKS=0后面没有分号,一开始就会报错。
2、时间类字段(datetime,timestamp)不需要长度,而生成的文件中指定长度为0,即DATETIME(0),导入过程中会报错。
解决办法——修改生成DDL模板。
菜单栏–>包–>数据库工程–>修改ddl模板–>找相应的类并替换
在导出时 格式–>评语水平–>全部
图片引自enterprise architect 12生成DDL问题
DDLColumnDefinition
%PI=" "%
%DDLName("EA", "COLUMN", "", "INCLUDE_SURROUND")%
%DDLDataType%
$Type = %columnProperty:"TYPE"%
$Default = %columnProperty:"DEFAULT"%
$Find = ""
%if $Type != "ENUM" and $Type != "SET"%
%if $Default != ""%
$Default = "DEFAULT " + $Default
%endIf%
%endIf%
%if columnBoolProperty:"ZEROFILL" == "T"%
ZEROFILL
%endIf%
%if columnBoolProperty:"UNSIGNED" == "T"%
UNSIGNED
%endIf%
%if columnBoolProperty:"NULLABLE" == "T"%
%if ddlOptionGenerateNullable == "T"%
NULL
%endIf%
%else%
NOT NULL
%endIf%
%if columnBoolProperty:"AutoNum" == "T"%
AUTO_INCREMENT
%else%
%if $Default != ""%
$Default
%endIf%
%endIf%
%DDLColumnComment%
%PI("I", "")%
,
DDLDataType
%PI=""%
$Type = %columnProperty:"TYPE"%
$DBMS = %ddlTableDBMS%
$Size = %DDL_DATATYPE_SIZE($DBMS, $Type)%
$LENGTH = %columnProperty:"LENGTH"%
$Type
%if $Size=="1" and $LENGTH != "0" and $LENGTH != ""%
(
$LENGTH
)
%elseIf $Size=="2"%
(
%if columnProperty:"PRECISION"==""%
0
%else%
%columnProperty:"PRECISION"%
%endIf%
%if columnProperty:"SCALE" != ""%
,%columnProperty:"SCALE"%
%endIf%
)
%endIf%
DDLName
%PI=""%
%if $parameter1 == "EA"%
%if $parameter3 == "INCLUDE_OWNER"%
$owner=%ddlOwner%
%if $parameter2 == "REFERENCE_TABLE"%
$owner=%ddlReferenceTableOwner%
%endIf%
%endIf%
%if $parameter2 == "OWNER"%
$name=%ddlOwner%
%elseIf $parameter2 == "TABLE"%
$name=%ddlTableName%
%if ddlUseAlias == "T"%
$alias=%ddlTableAlias%
%endIf%
%elseIf $parameter2 == "PROCEDURE"%
$name=%ddlProcedureName%
%if ddlUseAlias == "T"%
$alias=%ddlProcedureAlias%
%endIf%
%elseIf $parameter2 == "FUNCTION"%
$name=%ddlFunctionName%
%if ddlUseAlias == "T"%
$alias=%ddlFunctionAlias%
%endIf%
%elseIf $parameter2 == "VIEW"%
$name=%ddlViewName%
%if ddlUseAlias == "T"%
$alias=%ddlViewAlias%
%endIf%
%elseIf $parameter2 == "SEQUENCE"%
$name=%ddlSequenceName%
%if ddlUseAlias == "T"%
$alias=%ddlSequenceAlias%
%endIf%
%elseIf $parameter2 == "PACKAGE"%
$name=%ddlPackageName%
%if ddlUseAlias == "T"%
$alias=%ddlPackageAlias%
%endIf%
%elseIf $parameter2 == "COLUMN"%
$name=%ddlColumnName%
%if ddlUseAlias == "T"%
$alias=%ddlColumnAlias%
%endIf%
%elseIf $parameter2 == "CONSTRAINT"%
$name=%ddlConstraintName%
%if ddlUseAlias == "T"%
$alias=%ddlConstraintAlias%
%endIf%
%elseIf $parameter2 == "CONSTRAINT_COLUMN"%
$name=%ddlConstraintColumnName%
%if ddlUseAlias == "T"%
$alias=%ddlConstraintColumnAlias%
%endIf%
%elseIf $parameter2 == "REFERENCE_TABLE"%
$name=%ddlReferenceTableName%
%if ddlUseAlias == "T"%
$alias=%ddlReferenceTableAlias%
%endIf%
%elseIf $parameter2 == "REFERENCE_COLUMN"%
$name=%ddlReferenceColumnName%
%if ddlUseAlias == "T"%
$alias=%ddlReferenceColumnAlias%
%endIf%
%endIf%
%elseIf $parameter1 == "ODBC"%
%if $parameter3 == "INCLUDE_OWNER"%
$owner=%ddlSchemaOwner%
%endIf%
%if $parameter2 == "OWNER"%
$name=%ddlSchemaOwner%
%elseIf $parameter2 == "TABLE"%
$name=%ddlSchemaTableName%
%elseIf $parameter2 == "COLUMN"%
$name=%ddlSchemaColumnName%
%elseIf $parameter2 == "CONSTRAINT"%
$name=%ddlSchemaConstraintName%
%endIf%
%endIf%
%if $parameter4 == "INCLUDE_SURROUND"%
$left=%DDLLeftSurround%
$right=%DDLRightSurround%
%if $name != ""%
$name = $left + $name + $right
%endIf%
%if $alias != ""%
$alias = $left + $alias + $right
%endIf%
%if $owner != ""%
$owner = $left + $owner + $right
%endIf%
%endIf%
%if ddlOptionUseAlias == "T" and $alias != ""%
$fullName = $alias
%else%
$fullName = $name
%endIf%
$fullName
DDlScriptFile
%PI="\n\n"%
%DDLScriptHeader%
%DDLUseDatabase%
$comment = ""
$start = ""
$end = ""
%if ddlOptionCommentLevel=="All"%
$comment = "T"
$start = "/*"
$end = "*/"
%endIf%
$fkCheckOff = "SET FOREIGN_KEY_CHECKS=0;"
$fkCheckOff
%EXECUTE_STRING("Foreign Key Checks", "Set FK Check off", "820", $fkCheckOff)%
$script = %list="DDLDropView" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Drop Views $end
%endIf%
$script
%endIf%
$script = %list="DDLDropTable" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Drop Tables $end
%endIf%
$script
%endIf%
$script = %list="DDLDropProcedure" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Drop Stored Procedures $end
%endIf%
$script
%endIf%
$script = %list="DDLDropFunction" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Drop Functions $end
%endIf%
$script
%endIf%
$script = %list="DDLDropSequence" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Drop Sequences $end
%endIf%
$script
%endIf%
$script = %list="DDLCreateSequence" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Create Sequences $end
%endIf%
$script
%endIf%
$script = %list="DDLCreateFunction" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Create Functions $end
%endIf%
$script
%endIf%
$script = %list="DDLCreateTable" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Create Tables $end
%endIf%
$script
%endIf%
$script = %list="DDLCreateTableConstraints" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Create Primary Keys, Indexes, Uniques, Checks $end
%endIf%
$script
%endIf%
$script = %list="DDLCreateForeignKeys" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Create Foreign Key Constraints $end
%endIf%
$script
%endIf%
$fkCheckOff = "SET FOREIGN_KEY_CHECKS=1;"
$fkCheckOff
%EXECUTE_STRING("Foreign Key Checks", "Set FK Check on", "300", $fkCheckOff)%
$script = %list="DDLCreateView" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Create Views $end
%endIf%
$script
%endIf%
$script = %list="DDLCreateProcedure" @separator="\n\n"%
%if $script != ""%
%if $comment == "T"%
$start Create Stored Procedures $end
%endIf%
$script
%endIf%
3、SQL文件编码不正确
这个问题造成创建的数据库中注释显示为乱码。我没有找到好的办法,只是用notepad++将编码转为utf-8无BOM格式编码,之后再生成数据库便可以正常显示。(数据库编码格式设置的为utf-8)