/// <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';"); }