protected virtual string GenerateCreateTableSql(TableMetadata tableMetadata)
{
var isAutoIncrementPrimary = tableMetadata.IsAutoIncrementPrimary;
var tableSql = GenerateTableSql(tableMetadata);
var builder = new StringBuilder($"CREATE TABLE IF NOT EXISTS {tableSql} (");
foreach (var column in tableMetadata.Columns)
{
var isPrimary = tableMetadata.IsPrimary(column.Key);
if (isPrimary)
{
var primarySql = $"CONSTRAINT {GetTableName(tableMetadata).ToUpper()}_PK PRIMARY KEY, ";
builder.Append(isAutoIncrementPrimary
? $"{GetNameSql(column.Value.Name)} SERIAL {primarySql}"
: $"{GenerateColumnSql(column.Value, true)} {(tableMetadata.Primary.Count > 1 ? "" : primarySql)}");
}
else
{
builder.Append($"{GenerateColumnSql(column.Value, false)}, ");
}
}
builder.Remove(builder.Length - 2, 2);
if (tableMetadata.Primary != null && tableMetadata.Primary.Count > 1)
{
builder.Append(
$", CONSTRAINT {GetTableName(tableMetadata).ToUpper()}_PK PRIMARY KEY ({string.Join(", ", tableMetadata.Primary.Select(c => $"{Escape}{GetNameSql(c)}{Escape}"))})");
}
if (tableMetadata.Indexes.Count > 0)
{
foreach (var index in tableMetadata.Indexes.Where(x => x.IsUnique))
{
var name = index.Name;
var columnNames = string.Join(", ", index.Columns.Select(c => $"{Escape}{GetNameSql(c)}{Escape}"));
builder.Append(
$", CONSTRAINT {Escape}{name}{Escape} UNIQUE ({columnNames})");
}
}
builder.Append(");");
if (tableMetadata.Indexes.Count > 0)
{
foreach (var index in tableMetadata.Indexes.Where(x => x.IsUnique))
{
var name = index.Name;
var columnNames = string.Join(", ", index.Columns.Select(c => $"{Escape}{GetNameSql(c)}{Escape}"));
builder.Append(
$"CREATE INDEX {name} ON {tableSql} ({columnNames});");
}
}
var sql = builder.ToString();
return(sql);
}
/// <summary>
/// 生成创建表的 SQL 语句
/// </summary>
/// <param name="tableMetadata">表元数据</param>
/// <returns>SQL 语句</returns>
protected virtual string GenerateCreateTableSql(TableMetadata tableMetadata)
{
var isAutoIncrementPrimary = tableMetadata.IsAutoIncrementPrimary;
var tableSql = GenerateTableSql(tableMetadata);
var builder = new StringBuilder($"CREATE TABLE IF NOT EXISTS {tableSql} (");
foreach (var column in tableMetadata.Columns)
{
var isPrimary = tableMetadata.IsPrimary(column.Key);
var columnSql = GenerateColumnSql(column.Value, isPrimary);
if (isPrimary)
{
builder.Append(isAutoIncrementPrimary
? $"{columnSql} AUTO_INCREMENT PRIMARY KEY, "
: $"{columnSql} {(tableMetadata.Primary.Count > 1 ? "" : "PRIMARY KEY")}, ");
}
else
{
builder.Append($"{columnSql}, ");
}
}
builder.Remove(builder.Length - 2, 2);
if (tableMetadata.Primary != null && tableMetadata.Primary.Count > 1)
{
builder.Append(
$", PRIMARY KEY ({string.Join(", ", tableMetadata.Primary.Select(c => $"{Escape}{GetNameSql(c)}{Escape}"))})");
}
if (tableMetadata.Indexes.Count > 0)
{
foreach (var index in tableMetadata.Indexes)
{
var name = index.Name;
var columnNames = string.Join(", ", index.Columns.Select(c => $"{Escape}{GetNameSql(c)}{Escape}"));
builder.Append($", {(index.IsUnique ? "UNIQUE" : "")} KEY {Escape}{name}{Escape} ({columnNames})");
}
}
builder.Append(")");
var sql = builder.ToString();
return(sql);
}
/// <summary>
/// 生成创建表的 SQL 语句
/// </summary>
/// <param name="tableMetadata">表元数据</param>
/// <returns>SQL 语句</returns>
protected virtual string GenerateCreateTableSql(TableMetadata tableMetadata)
{
var isAutoIncrementPrimary = tableMetadata.IsAutoIncrementPrimary;
var tableName = GetNameSql(tableMetadata.Schema.Table);
var database = GetNameSql(tableMetadata.Schema.Database);
var builder = string.IsNullOrWhiteSpace(database)
? new StringBuilder($"IF OBJECT_ID('{tableName}', 'U') IS NULL BEGIN CREATE table {tableName} (")
: new StringBuilder(
$"USE {database}; IF OBJECT_ID('{tableName}', 'U') IS NULL BEGIN CREATE table {tableName} (");
foreach (var column in tableMetadata.Columns)
{
var isPrimary = tableMetadata.IsPrimary(column.Key);
var columnSql = GenerateColumnSql(column.Value, isPrimary);
if (isAutoIncrementPrimary && isPrimary)
{
builder.Append($"{columnSql} IDENTITY(1,1), ");
}
else
{
builder.Append($"{columnSql}, ");
}
}
builder.Remove(builder.Length - 2, 2);
if (tableMetadata.HasPrimary)
{
var primaryKeys = string.Join(", ", tableMetadata.Primary.Select(c => $"[{GetNameSql(c)}]"));
builder.Append(
$", CONSTRAINT [PK_{tableName}] PRIMARY KEY CLUSTERED ({primaryKeys}) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]) ON[PRIMARY];");
}
else
{
builder.Append(") ON [PRIMARY];");
}
if (tableMetadata.Indexes.Count > 0)
{
foreach (var index in tableMetadata.Indexes)
{
var name = index.Name;
var columnNames = string.Join(", ", index.Columns.Select(c => $"[{GetNameSql(c)}]"));
if (index.IsUnique)
{
builder.Append(
$"CREATE UNIQUE NONCLUSTERED INDEX [INDEX_{name}] ON {tableName} ({columnNames}) {(StorageType == StorageType.InsertIgnoreDuplicate ? "WITH (IGNORE_DUP_KEY = ON)" : "")};");
}
else
{
builder.Append(
$"CREATE NONCLUSTERED INDEX [INDEX_{name}] ON {tableName} ({columnNames});");
}
}
}
builder.AppendLine(" END");
var sql = builder.ToString();
return(sql);
}
