Enterprise Architect DDL模版--MySQL篇

Enterprise Architect生成的SQL语句往往不能更好的适用于实际的MySQL语句规范，经常要修改后才可以用，本文用于记录常用的SQL语句所对应的DDL模板，如有我未使用到或者遗漏的欢迎提问或补充！

目录

使用说明

DDl Script File

DDL Create Table 

DDL Drop Table

DDL Name

DDL Column Definition

DDL Data Type

DDL Column Comment

字段说明

---

 

使用说明

菜单栏>>Package(包)>>Database Engineering(数据库工程)>>Edit DDL Templates(DDL模版编辑器)

别忘了先将Language(语言)切换成MySQL

DDl Script File

%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%

DDL Create Table 

%if ddlOptionGenerateTable != "T"%
%endTemplate%

%PI="\n"%

$SnapShotName = %DDLName("EA", "TABLE", "INCLUDE_OWNER")%
%EXECUTE_CURRENT($SnapShotName, "Create Table", 590)%

CREATE TABLE %DDLName("EA", "TABLE", "INCLUDE_OWNER", "INCLUDE_SURROUND")%

$tableContent = %list="DDLColumnDefinition" @separator="\n" @indent="\t"%
$tableContent += "\n"

$tableContent += %list="DDLTableConstraint" @separator="\n" @indent="\t" constraintProperty:"TYPE" == "PK"%

%if ddlOptionGenerateSeparateConstraint != "T"%
$tableContent += %list="DDLTableConstraint" @separator="\n" @indent="\t" constraintProperty:"TYPE" != "PK" and constraintProperty:"TYPE" != "FK" and constraintProperty:"TYPE" != "trigger"%
$tableContent += "\n"
$tableContent += %list="DDLTableConstraint" @separator="\n" @indent="\t" constraintProperty:"TYPE" == "FK"%
$tableContent += "\n"
%endIf%

$tableContent = %REMOVE_LAST_SEPARATOR($tableContent, ",")%
$tableContent = %TRIM($tableContent, "\n")%
(
$tableContent
)

%PI("I", "")%

%if tableProperty:"Engine" != ""%
 ENGINE=%tableProperty:"Engine"%
%endIf%

$tableComment = %DDLTableLevelComment%
%if $tableComment != ""%
 $tableComment
%endIf%

%DDLScriptSeparator%
;

DDL Drop Table

%if ddlOptionGenerateDrop != "T"%
%endTemplate%

%if ddlOptionGenerateTable != "T"%
%endTemplate%

%PI=" "%

$SnapShotName = %DDLName("EA", "TABLE", "INCLUDE_OWNER")%
%EXECUTE_CURRENT($SnapShotName, "Drop Table", 730)%

DROP TABLE

%if ddlOptionGenerateDropIfExist == "T"%
IF EXISTS
%endIf%

%DDLName("EA", "TABLE", "INCLUDE_OWNER", "INCLUDE_SURROUND")% CASCADE

%PI("I", "")%
%DDLScriptSeparator%
;

DDL Name

%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

DDL Column Definition

%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", "")%
,

DDL Data Type

%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%

DDL Column Comment

%if ddlOptionCommentLevel != "Column" and ddlOptionCommentLevel != "All"%
$columnComment = %ddlColumnAlias%
%if $columnComment!=""%
COMMENT '%REPLACE($columnComment, "'", "''")%'
%endIf%

 

字段说明

DDL 模板字段说明

%ddlColumnAlias%	字段别名