/// <summary>
/// 列的解析默认值设置.
/// </summary>
/// <param name="definition"></param>
/// <param name="DbParameters"></param>
/// <returns></returns>
private string ParseDefaultValue(DbTableColumnDefinition definition, ref List <IDbDataParameter> DbParameters)
{
IDescription valueDes = definition.Default as IDescription;
if (valueDes == null)
{
switch (definition.DataType)
{
case GenericDbType.Boolean:
return(string.Format("DEFAULT({0})", Convert.ToBoolean(definition.Default) ? 1 : 0));
case GenericDbType.SmallInt:
case GenericDbType.Int:
case GenericDbType.BigInt:
case GenericDbType.Single:
case GenericDbType.Money:
case GenericDbType.Double:
return(string.Format("DEFAULT({0})", definition.Default));
case GenericDbType.Binary:
case GenericDbType.VarBinary:
case GenericDbType.Image:
throw new NotSupportedException(string.Format("Data type {0} does not support setting default value.", definition.Default));
default:
return(string.Format("DEFAULT('{0}')", definition.Default.ToString()));
}
}
valueDes.DescriptionParserAdapter = this.Adapter;
return(string.Format("DEFAULT({0})", valueDes.GetParser().Parsing(ref DbParameters)));
}
/// <summary>
/// 解析指定列定义的数据类型.
/// </summary>
/// <param name="definition">列定义信息.</param>
/// <returns></returns>
private string ParseDbType(DbTableColumnDefinition definition)
{
switch (definition.DataType)
{
case GenericDbType.Char:
case GenericDbType.NChar:
return(string.Format("CHAR({0})", definition.Size));
case GenericDbType.VarChar:
case GenericDbType.NVarchar:
return(string.Format("VARCHAR({0})", definition.Size));
case GenericDbType.Text:
return("TEXT");
case GenericDbType.NText:
return("LONGTEXT");
case GenericDbType.SmallInt:
return("SMALLINT");
case GenericDbType.Int:
return("INT");
case GenericDbType.BigInt:
return("BIGINT");
case GenericDbType.Money:
return("DECIMAL");
case GenericDbType.Single:
return("FLOAT");
case GenericDbType.Double:
return("DOUBLE");
case GenericDbType.Boolean:
return("BOOL");
case GenericDbType.Binary:
return("BLOB");
case GenericDbType.VarBinary:
return("MEDIUMBLOB");
case GenericDbType.Image:
return("LONGBLOB");
case GenericDbType.Time:
return("TIME");
case GenericDbType.Date:
return("DATE");
case GenericDbType.DateTime:
return("DATETIME");
}
return(string.Empty);
}
/// <summary>
/// 解析列的外键设置.
/// </summary>
/// <param name="table">当前表名.</param>
/// <param name="definition">列定义信息.</param>
/// <param name="writer">将外键命令段写入该缓冲区.</param>
/// <returns></returns>
private void ParseForeignKey(string table, DbTableColumnDefinition definition, StringBuilder writer)
{
if (writer.Length > 0)
writer.Append(",").AppendLine();
writer.AppendFormat("\tCONSTRAINT {2}FK_{0}_{1}{3} FOREIGN KEY ({2}{1}{3})", table, definition.Name, ElemIdentifierL, ElemIdentifierR);
writer.AppendFormat(" REFERENCES {0}{2}{1} ({0}{3}{1})", ElemIdentifierL, ElemIdentifierR, definition.ForeignKey.Table, definition.ForeignKey.Column);
writer.AppendFormat(" ON DELETE {0} ON UPDATE {1}", definition.ForeignKey.OnDelete, definition.ForeignKey.OnUpdate);
}
/// <summary>
/// 解析指定列定义的数据类型.
/// </summary>
/// <param name="definition">列定义信息.</param>
/// <returns></returns>
private string ParseDbType(DbTableColumnDefinition definition)
{
switch (definition.DataType)
{
case GenericDbType.Char:
return(string.Format("CHARACTER({0})", definition.Size));
case GenericDbType.NChar:
return(string.Format("NCHAR({0})", definition.Size));
case GenericDbType.VarChar:
return(string.Format("VARCHAR({0})", definition.Size));
case GenericDbType.NVarchar:
return(string.Format("NVARCHAR({0})", definition.Size));
case GenericDbType.Text:
case GenericDbType.NText:
return("TEXT");
case GenericDbType.SmallInt:
return("SMALLINT");
case GenericDbType.Int:
return("INTEGER");
case GenericDbType.BigInt:
return("BIGINT");
case GenericDbType.Money:
return("DECIMAL(10,5)");
case GenericDbType.Single:
return("FLOAT");
case GenericDbType.Double:
return("DOUBLE");
case GenericDbType.Boolean:
return("BOOLEAN");
case GenericDbType.Binary:
case GenericDbType.VarBinary:
case GenericDbType.Image:
return("BLOB");
case GenericDbType.Time:
throw new NotSupportedException(string.Format("SQLite unsupported this type: {0}", definition.DataType));
case GenericDbType.Date:
return("DATE");
case GenericDbType.DateTime:
return("DATETIME");
}
return(string.Empty);
}
/// <summary>
/// 解析指定列定义的数据类型.
/// </summary>
/// <param name="definition">列定义信息.</param>
/// <returns></returns>
private string ParseDbType(DbTableColumnDefinition definition)
{
switch (definition.DataType)
{
case GenericDbType.Char:
case GenericDbType.NChar:
return string.Format("character({0})", definition.Size);
case GenericDbType.VarChar:
case GenericDbType.NVarchar:
return string.Format("character varying({0})", definition.Size);
case GenericDbType.Text:
case GenericDbType.NText:
return "text";
case GenericDbType.SmallInt:
return "smallint";
case GenericDbType.Int:
return "integer";
case GenericDbType.BigInt:
return "bigint";
case GenericDbType.Money:
return "decimal";
case GenericDbType.Single:
return "real";
case GenericDbType.Double:
return "double precision";
case GenericDbType.Boolean:
return "boolean";
case GenericDbType.Binary:
case GenericDbType.VarBinary:
case GenericDbType.Image:
return "bytea";
case GenericDbType.Time:
return (definition.Size > 0) ? string.Format("time({0})", definition.Size) : "TIME";
case GenericDbType.Date:
return "date";
case GenericDbType.DateTime:
return "timestamp";
}
return string.Empty;
}
public TableCreateUnitTest()
{
// create table 命令构建.
categoriesBuilder = new DbCommandBuilder();
categoriesBuilder.CreateTable(table_categories).ColumnsDefine(
DbTableColumnDefinition.New(name: "Id", dataType: GenericDbType.Int, notNull: true, identity: new DbColumnIdentity(0, 1), pk: true),
DbTableColumnDefinition.New(name: "CategoryName", dataType: GenericDbType.VarChar, size: 255, notNull: true, unique: true),
DbTableColumnDefinition.New(name: "Remark", dataType: GenericDbType.VarChar, size: 300),
DbTableColumnDefinition.New(name: "Cover", dataType: GenericDbType.Image),
DbTableColumnDefinition.New(name: "CreationDate", dataType: GenericDbType.DateTime, notNull: true, Default: Fun.Now())
);
productsBuilder = new DbCommandBuilder();
productsBuilder.CreateTable(table_products).ColumnsDefine(
DbTableColumnDefinition.New(name: "Id", dataType: GenericDbType.Int, notNull: true, identity: new DbColumnIdentity(0, 1), pk: true),
DbTableColumnDefinition.New(name: "Category", dataType: GenericDbType.Int, notNull: true, fk: new DbForeignKey(table_categories, "Id")),
DbTableColumnDefinition.New(name: "Name", dataType: GenericDbType.VarChar, size: 32, notNull: true, unique: true),
DbTableColumnDefinition.New(name: "Price", dataType: GenericDbType.Single, Default: 0f),
DbTableColumnDefinition.New(name: "Quantity", dataType: GenericDbType.SmallInt, Default: 0),
DbTableColumnDefinition.New(name: "Remark", dataType: GenericDbType.VarChar, size: 300),
DbTableColumnDefinition.New(name: "TimeOf", dataType: GenericDbType.DateTime, Default: Fun.Now()),
DbTableColumnDefinition.New(name: "Picture", dataType: GenericDbType.Image)
);
}
/// <summary>
/// 解释 CREATE TABLE 命令.
/// </summary>
/// <param name="DbParameters">用于缓存在解释过程中可能会产生的参数。</param>
/// <returns></returns>
public override string Parsing(ref List <IDbDataParameter> DbParameters)
{
TableBuildDescription tableBuild = (TableBuildDescription)this.Description;
StringBuilder buffers = new StringBuilder("CREATE TABLE ");
StringBuilder multiPk = new StringBuilder();
buffers.AppendFormat("{0}{1}{2} (", ElemIdentifierL, tableBuild.Name, ElemIdentifierR);
string columnType = null;
int pk_count = tableBuild.ColumnDefinitions.Count(def => def.PrimaryKey == true);
DbTableColumnDefinition definition = null;
for (int i = 0; i < tableBuild.ColumnDefinitions.Count; ++i)
{
definition = tableBuild.ColumnDefinitions[i];
if (string.IsNullOrEmpty(definition.Name))
{
throw new NoNullAllowedException("Undefined column name.");
}
columnType = ParseDbType(definition);
if (string.IsNullOrEmpty(columnType))
{
throw new NoNullAllowedException(string.Format("Type of undefined column: {0}", definition.Name));
}
buffers.AppendLine().AppendFormat("\t{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
buffers.AppendFormat(" {0} {1}", columnType, definition.NotNull ? "NOT NULL" : "NULL");
if (definition.Unique)
{
buffers.Append(" UNIQUE");
}
if (definition.Default != null)
{
buffers.AppendFormat(" {0}", ParseDefaultValue(definition, ref DbParameters));
}
if (definition.PrimaryKey)
{
if (pk_count > 1) // 联合主键判定.
{
if (multiPk.Length > 0)
{
multiPk.AppendFormat(",{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
}
else
{
multiPk.AppendFormat("{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
}
}
else
{
buffers.Append(" PRIMARY KEY");
}
}
if (definition.Identity != null)
{
if (!definition.PrimaryKey)
{
throw new NotSupportedException(string.Format("AUTOINCREMENT can't be applied to non-primary key column: {0}", definition.Name));
}
if (pk_count > 1)
{
throw new NotSupportedException(string.Format("AUTOINCREMENT can't be applied to multi-primary key columns.", definition.Name));
}
buffers.Append(" AUTOINCREMENT");
}
if (definition.ForeignKey != null)
{
ParseForeignKey(definition, buffers);
}
if (i < (tableBuild.ColumnDefinitions.Count - 1))
{
buffers.Append(",");
}
}
if (pk_count > 1)
{
buffers.Append(",").AppendLine().AppendFormat("\tPRIMARY KEY ({0})", multiPk.ToString());
}
buffers.AppendLine().Append(");");
return(buffers.ToString());
}
/// <summary>
/// 解析列的外键设置.
/// </summary>
/// <param name="definition">列定义信息.</param>
/// <param name="writer">将外键命令段写入该缓冲区.</param>
/// <returns></returns>
private void ParseForeignKey(DbTableColumnDefinition definition, StringBuilder writer)
{
writer.AppendFormat(" REFERENCES {0}{1}{2}", ElemIdentifierL, definition.ForeignKey.Table, ElemIdentifierR);
writer.AppendFormat(" ({0}{1}{2})", ElemIdentifierL, definition.ForeignKey.Column, ElemIdentifierR);
writer.AppendFormat(" ON DELETE {0} ON UPDATE {1}", definition.ForeignKey.OnDelete, definition.ForeignKey.OnUpdate);
}
/// <summary>
/// 解释 CREATE TABLE 命令.
/// </summary>
/// <param name="DbParameters">用于缓存在解释过程中可能会产生的参数。</param>
/// <returns></returns>
public override string Parsing(ref List <IDbDataParameter> DbParameters)
{
TableBuildDescription tableBuild = (TableBuildDescription)this.Description;
StringBuilder buffers = new StringBuilder("CREATE TABLE ");
StringBuilder multiPk = new StringBuilder();
StringBuilder fkBuffers = new StringBuilder();
buffers.AppendFormat("{0}{1}{2} (", ElemIdentifierL, tableBuild.Name, ElemIdentifierR);
string columnType = null;
int pk_count = tableBuild.ColumnDefinitions.Count(def => def.PrimaryKey == true);
DbTableColumnDefinition definition = null;
DbColumnIdentity identity = null;
for (int i = 0; i < tableBuild.ColumnDefinitions.Count; ++i)
{
definition = tableBuild.ColumnDefinitions[i];
if (string.IsNullOrEmpty(definition.Name))
{
throw new NoNullAllowedException("Undefined column name.");
}
columnType = ParseDbType(definition);
if (string.IsNullOrEmpty(columnType))
{
throw new NoNullAllowedException(string.Format("Type of undefined column: {0}", definition.Name));
}
buffers.AppendLine();
buffers.AppendFormat("\t{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
buffers.AppendFormat(" {0} {1}", columnType, definition.NotNull ? "NOT NULL" : "NULL");
if (definition.Default != null)
{
buffers.AppendFormat(" {0}", ParseDefaultValue(definition, ref DbParameters));
}
if (definition.Identity != null)
{
identity = definition.Identity;
buffers.Append(" AUTO_INCREMENT");
}
if (definition.Unique)
{
buffers.Append(" UNIQUE");
}
if (definition.PrimaryKey)
{
if (pk_count > 1) // 联合主键判定.
{
if (multiPk.Length > 0)
{
multiPk.AppendFormat(",{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
}
else
{
multiPk.AppendFormat("{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
}
}
else
{
buffers.Append(" PRIMARY KEY");
}
}
if (definition.ForeignKey != null)
{
ParseForeignKey(tableBuild.Name, definition, fkBuffers);
}
if (i < (tableBuild.ColumnDefinitions.Count - 1))
{
buffers.Append(",");
}
}
if (pk_count > 1)
{
buffers.Append(",").AppendLine();
buffers.AppendFormat("\tPRIMARY KEY ({0})", multiPk.ToString());
}
if (fkBuffers.Length > 0)
{
buffers.Append(",").AppendLine().Append(fkBuffers.ToString());
}
buffers.AppendLine();
buffers.AppendFormat(") ENGINE={0} AUTO_INCREMENT={1} DEFAULT CHARSET=utf8;", ((MySqlParserAdapter)Adapter).MysqlEngine, identity.InitValue);
return(buffers.ToString());
}
/// <summary>
/// 解析指定列定义的数据类型.
/// </summary>
/// <param name="definition">列定义信息.</param>
/// <returns></returns>
private string ParseDbType(DbTableColumnDefinition definition)
{
switch (definition.DataType)
{
case GenericDbType.Char:
return(string.Format("CHAR({0})", definition.Size));
case GenericDbType.NChar:
return(string.Format("NCHAR({0})", definition.Size));
case GenericDbType.VarChar:
return(string.Format("VARCHAR({0})", definition.Size));
case GenericDbType.NVarchar:
return(string.Format("NVARCHAR({0})", definition.Size));
case GenericDbType.Text:
return("TEXT");
case GenericDbType.NText:
return("NTEXT");
case GenericDbType.SmallInt:
return("SMALLINT");
case GenericDbType.Int:
return("INT");
case GenericDbType.BigInt:
return("BIGINT");
case GenericDbType.Money:
return("MONEY");
case GenericDbType.Single:
return("FLOAT");
case GenericDbType.Double:
return("DOUBLE");
case GenericDbType.Boolean:
return("BIT");
case GenericDbType.Binary:
return(string.Format("BINARY({0})", definition.Size));
case GenericDbType.VarBinary:
return(string.Format("VARBINARY({0})", definition.Size));
case GenericDbType.Image:
return("IMAGE");
case GenericDbType.Time:
return((definition.Size > 0) ? string.Format("TIME({0})", definition.Size) : "TIME");
case GenericDbType.Date:
return("DATE");
case GenericDbType.DateTime:
return("DATETIME");
}
return(string.Empty);
}
/// <summary>
/// 解释 CREATE TABLE 命令.
/// </summary>
/// <param name="DbParameters">用于缓存在解释过程中可能会产生的参数。</param>
/// <returns></returns>
public override string Parsing(ref List<IDbDataParameter> DbParameters)
{
TableBuildDescription tableBuild = (TableBuildDescription)this.Description;
StringBuilder tableBuffers = new StringBuilder("CREATE TABLE ");
StringBuilder sequnceBuffers = new StringBuilder();
StringBuilder pkBuffers = new StringBuilder();
StringBuilder uniqueBuffers = new StringBuilder();
StringBuilder fkBuffers = new StringBuilder();
tableBuffers.AppendFormat("{0}{1}{2} (", ElemIdentifierL, tableBuild.Name, ElemIdentifierR);
string columnType = null, seqName = null;
DbTableColumnDefinition definition = null;
for (int i = 0; i < tableBuild.ColumnDefinitions.Count; ++i)
{
definition = tableBuild.ColumnDefinitions[i];
if (string.IsNullOrEmpty(definition.Name))
throw new NoNullAllowedException("Undefined column name.");
columnType = ParseDbType(definition);
if (string.IsNullOrEmpty(columnType))
throw new NoNullAllowedException(string.Format("Type of undefined column: {0}", definition.Name));
tableBuffers.AppendLine().AppendFormat("\t{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
tableBuffers.AppendFormat(" {0} {1}", columnType, definition.NotNull ? "NOT NULL" : "NULL");
if (definition.Default != null && definition.Identity == null)
tableBuffers.AppendFormat(" {0}", ParseDefaultValue(definition, ref DbParameters));
if (definition.Identity != null)
{
seqName = string.Format("{0}_{1}_seq", tableBuild.Name, definition.Name);
AddSequnce(definition.Identity, seqName, sequnceBuffers);
tableBuffers.AppendFormat(" DEFAULT nextval('{0}{1}{2}'::regclass)", ElemIdentifierL, seqName, ElemIdentifierR);
}
if (definition.Unique)
{
if (uniqueBuffers.Length > 0)
uniqueBuffers.AppendFormat(",{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
else
uniqueBuffers.AppendFormat("{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
}
if (definition.PrimaryKey)
{
if (pkBuffers.Length > 0)
pkBuffers.AppendFormat(",{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
else
pkBuffers.AppendFormat("{0}{1}{2}", ElemIdentifierL, definition.Name, ElemIdentifierR);
}
if (definition.ForeignKey != null)
ParseForeignKey(tableBuild.Name, definition, fkBuffers);
if (i < (tableBuild.ColumnDefinitions.Count - 1))
tableBuffers.Append(",");
}
if (pkBuffers.Length > 0)
{
tableBuffers.Append(",").AppendLine();
tableBuffers.AppendFormat("\tCONSTRAINT {0}PK_{1}{2}", ElemIdentifierL, tableBuild.Name, ElemIdentifierR);
tableBuffers.AppendFormat(" PRIMARY KEY ({0})", pkBuffers.ToString());
}
if (uniqueBuffers.Length > 0)
{
tableBuffers.Append(",").AppendLine();
tableBuffers.AppendFormat("\tCONSTRAINT {0}UK_{1}_UNIQUE{2}", ElemIdentifierL, tableBuild.Name, ElemIdentifierR);
tableBuffers.AppendFormat(" UNIQUE ({0})", uniqueBuffers.ToString());
}
if (fkBuffers.Length > 0)
tableBuffers.Append(",").AppendLine().Append(fkBuffers.ToString());
tableBuffers.AppendLine().Append(");");
return sequnceBuffers.AppendLine().Append(tableBuffers.ToString()).ToString();
}
