public static LoggerConfiguration Db(this LoggerSinkConfiguration loggerConfiguration,
Action <DbOptions> dbAction = null, ITableCreator tableCreator = null,
LogEventLevel restrictedToMinimumLevel = LevelAlias.Minimum,
int batchPostingLimit = DbSink.DefaultBatchPostingLimit,
TimeSpan?period = null,
IConfigurationSection columnOptionsSection = null
)
{
if (loggerConfiguration == null)
{
throw new ArgumentNullException("loggerConfiguration");
}
var defaultedPeriod = period ?? DbSink.DefaultPeriod;
DbOptions opt = new DbOptions();
dbAction?.Invoke(opt);
opt.ColumnOptions = ApplyMicrosoftExtensionsConfiguration.ConfigureColumnOptions(opt.ColumnOptions, columnOptionsSection);
return(loggerConfiguration.Sink(new DbSink(batchPostingLimit, defaultedPeriod, opt, tableCreator ?? new DefaultTableCreator()), restrictedToMinimumLevel));
}
private string GetSqliteCreateTableString(DbOptions opt, DataTable columnList)
{
var sql = new StringBuilder();
var i = 0;
sql.AppendLine($"CREATE TABLE IF NOT EXISTS [{opt.TableName}] ( ");
sql.AppendLine("[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,");
foreach (DataColumn column in columnList.Columns)
{
i++;
var common = (SqlColumn)column.ExtendedProperties["SqlColumn"];
if (common != null && common == opt.ColumnOptions.PrimaryKey)
{
continue;
}
sql.Append($"[{column.ColumnName}] ");
sql.Append(GetDbFieldType(common.DataType));
sql.Append(common.AllowNull ? " NULL" : " NOT NULL");
if (columnList.Columns.Count > i)
{
sql.Append(",");
}
sql.AppendLine();
}
sql.AppendLine(");");
Console.WriteLine(sql.ToString());
return(sql.ToString());
}
public DbSinkCore(DbOptions opt) : this(opt, null)
{
}
private string GetMsSqlFromDataTable(DbOptions opt, DataTable columnList)
{
var sql = new StringBuilder();
var ix = new StringBuilder();
int indexCount = 1;
string schemaName = opt.SchemaName;
string tableName = opt.TableName;
var columnOptions = opt.ColumnOptions;
//start schema check and DDL(wrap in EXEC to make a separate batch)
sql.AppendLine($"IF(NOT EXISTS(SELECT * FROM sys.schemas WHERE name = '{schemaName}'))");
sql.AppendLine("BEGIN");
sql.AppendLine($"EXEC('CREATE SCHEMA [{schemaName}] AUTHORIZATION [dbo]')");
sql.AppendLine("END");
// start table-creatin batch and DDL
sql.AppendLine($"IF NOT EXISTS (SELECT s.name, t.name FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = '{schemaName}' AND t.name = '{tableName}')");
sql.AppendLine("BEGIN");
sql.AppendLine($"CREATE TABLE [{schemaName}].[{tableName}] ( ");
// build column list
int i = 1;
foreach (DataColumn column in columnList.Columns)
{
var common = (SqlColumn)column.ExtendedProperties["SqlColumn"];
sql.Append(GetColumnDDL(common));
if (columnList.Columns.Count > i++)
{
sql.Append(",");
}
sql.AppendLine();
// collect non-PK indexes for separate output after the table DDL
if (common != null && common.NonClusteredIndex && common != columnOptions.PrimaryKey)
{
ix.AppendLine($"CREATE NONCLUSTERED INDEX [IX{indexCount++}_{tableName}] ON [{schemaName}].[{tableName}] ([{common.ColumnName}]);");
}
}
// primary key constraint at the end of the table DDL
if (columnOptions.PrimaryKey != null)
{
var clustering = (columnOptions.PrimaryKey.NonClusteredIndex ? "NON" : string.Empty);
sql.AppendLine($" CONSTRAINT [PK_{tableName}] PRIMARY KEY {clustering}CLUSTERED ([{columnOptions.PrimaryKey.ColumnName}])");
}
// end of CREATE TABLE
sql.AppendLine(");");
// CCI is output separately after table DDL
if (columnOptions.ClusteredColumnstoreIndex)
{
sql.AppendLine($"CREATE CLUSTERED COLUMNSTORE INDEX [CCI_{tableName}] ON [{schemaName}].[{tableName}]");
}
// output any extra non-clustered indexes
sql.Append(ix);
// end of batch
sql.AppendLine("END");
return(sql.ToString());
}