/// <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);
}
/// <summary> /// 生成 SQL 语句 /// </summary> /// <param name="tableMetadata">表元数据</param> /// <returns></returns> protected abstract SqlStatements GenerateSqlStatements(TableMetadata tableMetadata);
protected virtual string GenerateFileName(TableMetadata tableMetadata)
{
return(string.IsNullOrWhiteSpace(tableMetadata.Schema.Database)
? $"{tableMetadata.Schema.Table}"
: $"{tableMetadata.Schema.Database}.{tableMetadata.Schema.Table}");
}
/// <summary>
/// 生成创建数据库的 SQL 语句
/// </summary>
/// <param name="tableMetadata">表元数据</param>
/// <returns>SQL 语句</returns>
protected virtual string GenerateCreateDatabaseSql(TableMetadata tableMetadata)
{
return(string.IsNullOrWhiteSpace(tableMetadata.Schema.Database)
? ""
: $"CREATE SCHEMA IF NOT EXISTS {Escape}{GetNameSql(tableMetadata.Schema.Database)}{Escape} DEFAULT CHARACTER SET utf8mb4;");
}
/// <summary>
/// 获取数据文件路径
/// </summary>
/// <param name="dfc">数据上下文件</param>
/// <param name="tableMetadata">表元数据</param>
/// <param name="extension">文件扩展名</param>
/// <returns></returns>
protected virtual string GetDataFile(DataFlowContext dfc, TableMetadata tableMetadata, string extension)
{
return(Path.Combine(GetDataFolder(dfc.Response.Request.OwnerId),
$"{GenerateFileName(tableMetadata)}.{extension}"));
}
protected override async Task StorageAsync(DataFlowContext context, TableMetadata tableMetadata, IList data)
{
var streamWriter = _streamWriters.GetOrAdd(tableMetadata.TypeName,
s => OpenWrite(context, tableMetadata, "json"));
await streamWriter.WriteLineAsync(JsonConvert.SerializeObject(data));
}
/// <summary>
/// 获取数据文件路径
/// </summary>
/// <param name="context">数据上下文件</param>
/// <param name="tableMetadata">表元数据</param>
/// <param name="extension">文件扩展名</param>
/// <returns></returns>
protected virtual string GetDataFile(DataContext context, TableMetadata tableMetadata, string extension)
{
return(Path.Combine(GetDataFolder(context.Request.Owner),
$"{GenerateFileName(tableMetadata)}.{extension}"));
}
protected abstract Task StorageAsync(DataContext context, TableMetadata tableMetadata, IList data);
protected virtual string GenerateCreateDatabaseSql(TableMetadata tableMetadata)
{
return(string.IsNullOrWhiteSpace(tableMetadata.Schema.Database)
? ""
: $"CREATE DATABASE {Escape}{GetNameSql(tableMetadata.Schema.Database)}{Escape} with encoding 'UTF-8';");
}