/// <summary>
///
/// </summary>
/// <param name="connectionString"></param>
/// <param name="tableName"></param>
/// <param name="schemaName"></param>
/// <param name="columnOptions"></param>
/// <param name="formatProvider"></param>
/// <param name="autoCreateSqlTable"></param>
public DbSinkTraits(ProviderType type, Func <IDbConnection> func, string tableName, string schemaName, ColumnOptions columnOptions, IFormatProvider formatProvider, bool autoCreateSqlTable)
{
//if (string.IsNullOrWhiteSpace(connectionString))
// throw new ArgumentNullException(nameof(connectionString));
if (string.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
_type = type;
//this.connectionString = connectionString;
this.tableName = tableName;
this.schemaName = schemaName;
this.columnOptions = columnOptions ?? new ColumnOptions();
this.formatProvider = formatProvider;
standardColumnNames = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
foreach (var stdCol in this.columnOptions.Store)
{
var col = this.columnOptions.GetStandardColumnOptions(stdCol);
standardColumnNames.Add(col.ColumnName);
}
additionalColumnNames = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
if (this.columnOptions.AdditionalColumns != null)
{
foreach (var col in this.columnOptions.AdditionalColumns)
{
additionalColumnNames.Add(col.ColumnName);
}
}
if (this.columnOptions.Store.Contains(StandardColumn.LogEvent))
{
jsonLogEventFormatter = new JsonLogEventFormatter(null);
}
eventTable = CreateDataTable();
if (autoCreateSqlTable)
{
try
{
SqlTableCreator tableCreator = new SqlTableCreator(type, func, this.schemaName, this.tableName, eventTable, this.columnOptions);
tableCreator.CreateTable(); // return code ignored, 0 = failure?
}
catch (Exception ex)
{
Console.WriteLine($"Exception creating table {tableName}:\n{ex}");
}
}
}
/// <summary>
/// Populate ColumnOptions properties and collections from app config
/// </summary>
internal static ColumnOptions ConfigureColumnOptions(MSSqlServerConfigurationSection config, ColumnOptions columnOptions)
{
var opts = columnOptions ?? new ColumnOptions();
AddRmoveStandardColumns();
AddAdditionalColumns();
ReadStandardColumns();
ReadMiscColumnOptions();
return(opts);
void AddRmoveStandardColumns()
{
// add standard columns
if (config.AddStandardColumns.Count > 0)
{
foreach (StandardColumnConfig col in config.AddStandardColumns)
{
if (Enum.TryParse(col.Name, ignoreCase: true, result: out StandardColumn stdcol) &&
!opts.Store.Contains(stdcol))
{
opts.Store.Add(stdcol);
}
}
}
// remove standard columns
if (config.RemoveStandardColumns.Count > 0)
{
foreach (StandardColumnConfig col in config.RemoveStandardColumns)
{
if (Enum.TryParse(col.Name, ignoreCase: true, result: out StandardColumn stdcol) &&
opts.Store.Contains(stdcol))
{
opts.Store.Remove(stdcol);
}
}
}
}
void AddAdditionalColumns()
{
if (config.Columns.Count > 0)
{
foreach (ColumnConfig c in config.Columns)
{
if (!string.IsNullOrWhiteSpace(c.ColumnName))
{
if (opts.AdditionalColumns == null)
{
opts.AdditionalColumns = new Collection <SqlColumn>();
}
opts.AdditionalColumns.Add(c.AsSqlColumn());
}
}
}
}
void ReadStandardColumns()
{
SetCommonColumnOptions(config.Exception, opts.Exception);
SetCommonColumnOptions(config.Id, opts.Id);
SetCommonColumnOptions(config.Level, opts.Level);
SetCommonColumnOptions(config.LogEvent, opts.LogEvent);
SetCommonColumnOptions(config.Message, opts.Message);
SetCommonColumnOptions(config.MessageTemplate, opts.MessageTemplate);
SetCommonColumnOptions(config.PropertiesColumn, opts.Properties);
SetCommonColumnOptions(config.TimeStamp, opts.TimeStamp);
SetProperty.IfProvided <bool>(config.Level, "StoreAsEnum", (val) => opts.Level.StoreAsEnum = val);
SetProperty.IfProvided <bool>(config.LogEvent, "ExcludeStandardColumns", (val) => opts.LogEvent.ExcludeStandardColumns = val);
SetProperty.IfProvided <bool>(config.LogEvent, "ExcludeAdditionalProperties", (val) => opts.LogEvent.ExcludeAdditionalProperties = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "ExcludeAdditionalProperties", (val) => opts.Properties.ExcludeAdditionalProperties = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "DictionaryElementName", (val) => opts.Properties.DictionaryElementName = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "ItemElementName", (val) => opts.Properties.ItemElementName = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "OmitDictionaryContainerElement", (val) => opts.Properties.OmitDictionaryContainerElement = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "OmitSequenceContainerElement", (val) => opts.Properties.OmitSequenceContainerElement = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "OmitStructureContainerElement", (val) => opts.Properties.OmitStructureContainerElement = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "OmitElementIfEmpty", (val) => opts.Properties.OmitElementIfEmpty = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "PropertyElementName", (val) => opts.Properties.PropertyElementName = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "RootElementName", (val) => opts.Properties.RootElementName = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "SequenceElementName", (val) => opts.Properties.SequenceElementName = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "StructureElementName", (val) => opts.Properties.StructureElementName = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "UsePropertyKeyAsElementName", (val) => opts.Properties.UsePropertyKeyAsElementName = val);
SetProperty.IfProvided <bool>(config.TimeStamp, "ConvertToUtc", (val) => opts.TimeStamp.ConvertToUtc = val);
// Standard Columns are subclasses of the SqlColumn class
void SetCommonColumnOptions(ColumnConfig source, SqlColumn target)
{
SetProperty.IfProvidedNotEmpty <string>(source, "ColumnName", (val) => target.ColumnName = val);
SetProperty.IfProvided <string>(source, "DataType", (val) => target.SetDataTypeFromConfigString(val));
SetProperty.IfProvided <bool>(source, "AllowNull", (val) => target.AllowNull = val);
SetProperty.IfProvided <int>(source, "DataLength", (val) => target.DataLength = val);
SetProperty.IfProvided <bool>(source, "NonClusteredIndex", (val) => target.NonClusteredIndex = val);
}
}
void ReadMiscColumnOptions()
{
SetProperty.IfProvided <bool>(config, "DisableTriggers", (val) => opts.DisableTriggers = val);
SetProperty.IfProvided <bool>(config, "ClusteredColumnstoreIndex", (val) => opts.ClusteredColumnstoreIndex = val);
string pkName = null;
SetProperty.IfProvidedNotEmpty <string>(config, "PrimaryKeyColumnName", (val) => pkName = val);
if (pkName != null)
{
if (opts.ClusteredColumnstoreIndex)
{
throw new ArgumentException("SQL Clustered Columnstore Indexes and primary key constraints are mutually exclusive.");
}
foreach (var standardCol in opts.Store)
{
var stdColOpts = opts.GetStandardColumnOptions(standardCol);
if (pkName.Equals(stdColOpts.ColumnName, StringComparison.InvariantCultureIgnoreCase))
{
opts.PrimaryKey = stdColOpts;
break;
}
}
if (opts.PrimaryKey == null && opts.AdditionalColumns != null)
{
foreach (var col in opts.AdditionalColumns)
{
if (pkName.Equals(col.ColumnName, StringComparison.InvariantCultureIgnoreCase))
{
opts.PrimaryKey = col;
break;
}
}
}
if (opts.PrimaryKey == null)
{
throw new ArgumentException($"Could not match the configured primary key column name \"{pkName}\" with a data column in the table.");
}
}
}
}
/// <summary>
/// Create or add to the ColumnOptions object and apply any configuration changes to it.
/// </summary>
/// <param name="columnOptions">An optional externally-created ColumnOptions object to be updated with additional configuration values.</param>
/// <param name="config">A configuration section typically named "columnOptionsSection" (see docs).</param>
/// <returns></returns>
public static ColumnOptions ConfigureColumnOptions(ColumnOptions columnOptions, IConfigurationSection config)
{
// Do not use configuration binding (ie GetSection.Get<ColumnOptions>). That will create a new
// ColumnOptions object which would overwrite settings if the caller passed in a ColumnOptions
// object via the extension method's columnOptions parameter.
var opts = columnOptions ?? new ColumnOptions();
if (config == null || !config.GetChildren().Any())
{
return(opts);
}
AddRemoveStandardColumns();
AddAdditionalColumns();
ReadStandardColumns();
ReadMiscColumnOptions();
return(opts);
void AddRemoveStandardColumns()
{
// add standard columns
var addStd = config.GetSection("addStandardColumns");
if (addStd.GetChildren().Any())
{
foreach (var col in addStd.GetChildren().ToList())
{
if (Enum.TryParse(col.Value, ignoreCase: true, result: out StandardColumn stdcol) &&
!opts.Store.Contains(stdcol))
{
opts.Store.Add(stdcol);
}
}
}
// remove standard columns
var removeStd = config.GetSection("removeStandardColumns");
if (removeStd.GetChildren().Any())
{
foreach (var col in removeStd.GetChildren().ToList())
{
if (Enum.TryParse(col.Value, ignoreCase: true, result: out StandardColumn stdcol) &&
opts.Store.Contains(stdcol))
{
opts.Store.Remove(stdcol);
}
}
}
}
void AddAdditionalColumns()
{
var newcols =
config.GetSection("additionalColumns").Get <List <SqlColumn> >()
?? config.GetSection("customColumns").Get <List <SqlColumn> >(); // backwards-compatibility
if (newcols != null)
{
foreach (var c in newcols)
{
if (!string.IsNullOrWhiteSpace(c.ColumnName))
{
if (opts.AdditionalColumns == null)
{
opts.AdditionalColumns = new Collection <SqlColumn>();
}
opts.AdditionalColumns.Add(c);
}
}
}
}
void ReadStandardColumns()
{
var section = config.GetSection("id");
if (section != null)
{
SetCommonColumnOptions(opts.Id);
#pragma warning disable 618 // deprecated: BigInt property
SetProperty.IfNotNull <bool>(section["bigInt"], (val) => opts.Id.BigInt = val);
#pragma warning restore 618
}
section = config.GetSection("level");
if (section != null)
{
SetCommonColumnOptions(opts.Level);
SetProperty.IfNotNull <bool>(section["storeAsEnum"], (val) => opts.Level.StoreAsEnum = val);
}
section = config.GetSection("properties");
if (section != null)
{
SetCommonColumnOptions(opts.Properties);
SetProperty.IfNotNull <bool>(section["excludeAdditionalProperties"], (val) => opts.Properties.ExcludeAdditionalProperties = val);
SetProperty.IfNotNull <string>(section["dictionaryElementName"], (val) => opts.Properties.DictionaryElementName = val);
SetProperty.IfNotNull <string>(section["itemElementName"], (val) => opts.Properties.ItemElementName = val);
SetProperty.IfNotNull <bool>(section["omitDictionaryContainerElement"], (val) => opts.Properties.OmitDictionaryContainerElement = val);
SetProperty.IfNotNull <bool>(section["omitSequenceContainerElement"], (val) => opts.Properties.OmitSequenceContainerElement = val);
SetProperty.IfNotNull <bool>(section["omitStructureContainerElement"], (val) => opts.Properties.OmitStructureContainerElement = val);
SetProperty.IfNotNull <bool>(section["omitElementIfEmpty"], (val) => opts.Properties.OmitElementIfEmpty = val);
SetProperty.IfNotNull <string>(section["propertyElementName"], (val) => opts.Properties.PropertyElementName = val);
SetProperty.IfNotNull <string>(section["rootElementName"], (val) => opts.Properties.RootElementName = val);
SetProperty.IfNotNull <string>(section["sequenceElementName"], (val) => opts.Properties.SequenceElementName = val);
SetProperty.IfNotNull <string>(section["structureElementName"], (val) => opts.Properties.StructureElementName = val);
SetProperty.IfNotNull <bool>(section["usePropertyKeyAsElementName"], (val) => opts.Properties.UsePropertyKeyAsElementName = val);
// TODO PropertiesFilter would need a compiled Predicate<string> (high Roslyn overhead, see Serilog Config repo #106)
}
section = config.GetSection("timeStamp");
if (section != null)
{
SetCommonColumnOptions(opts.TimeStamp);
SetProperty.IfNotNull <bool>(section["convertToUtc"], (val) => opts.TimeStamp.ConvertToUtc = val);
}
section = config.GetSection("logEvent");
if (section != null)
{
SetCommonColumnOptions(opts.LogEvent);
SetProperty.IfNotNull <bool>(section["excludeAdditionalProperties"], (val) => opts.LogEvent.ExcludeAdditionalProperties = val);
SetProperty.IfNotNull <bool>(section["ExcludeStandardColumns"], (val) => opts.LogEvent.ExcludeStandardColumns = val);
}
section = config.GetSection("message");
if (section != null)
{
SetCommonColumnOptions(opts.Message);
}
section = config.GetSection("exception");
if (section != null)
{
SetCommonColumnOptions(opts.Exception);
}
section = config.GetSection("messageTemplate");
if (section != null)
{
SetCommonColumnOptions(opts.MessageTemplate);
}
// Standard Columns are subclasses of the SqlColumn class
void SetCommonColumnOptions(SqlColumn target)
{
SetProperty.IfNotNullOrEmpty <string>(section["columnName"], (val) => target.ColumnName = val);
SetProperty.IfNotNull <string>(section["dataType"], (val) => target.SetDataTypeFromConfigString(val));
SetProperty.IfNotNull <bool>(section["allowNull"], (val) => target.AllowNull = val);
SetProperty.IfNotNull <int>(section["dataLength"], (val) => target.DataLength = val);
SetProperty.IfNotNull <bool>(section["nonClusteredIndex"], (val) => target.NonClusteredIndex = val);
}
}
void ReadMiscColumnOptions()
{
SetProperty.IfNotNull <bool>(config["disableTriggers"], (val) => opts.DisableTriggers = val);
SetProperty.IfNotNull <bool>(config["clusteredColumnstoreIndex"], (val) => opts.ClusteredColumnstoreIndex = val);
string pkName = config["primaryKeyColumnName"];
if (!string.IsNullOrEmpty(pkName))
{
if (opts.ClusteredColumnstoreIndex)
{
throw new ArgumentException("SQL Clustered Columnstore Indexes and primary key constraints are mutually exclusive.");
}
foreach (var standardCol in opts.Store)
{
var stdColOpts = opts.GetStandardColumnOptions(standardCol);
if (pkName.Equals(stdColOpts.ColumnName, StringComparison.InvariantCultureIgnoreCase))
{
opts.PrimaryKey = stdColOpts;
break;
}
}
if (opts.PrimaryKey == null && opts.AdditionalColumns != null)
{
foreach (var col in opts.AdditionalColumns)
{
if (pkName.Equals(col.ColumnName, StringComparison.InvariantCultureIgnoreCase))
{
opts.PrimaryKey = col;
break;
}
}
}
if (opts.PrimaryKey == null)
{
throw new ArgumentException($"Could not match the configured primary key column name \"{pkName}\" with a data column in the table.");
}
}
}
}
public SqlTableCreator(ProviderType type, Func <IDbConnection> func, string schemaName, string tableName, DataTable dataTable, ColumnOptions columnOptions)
{
this._type = type;
this._dbFunc = func;
this.schemaName = schemaName;
this.tableName = tableName;
this.dataTable = dataTable;
this.columnOptions = columnOptions;
}
