/// <summary>
/// Builds a script that will create a new table from the specified <see cref="LogTableDefinition" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" />.</param>
/// <returns>A <see cref="SqlCommand" /> for a CREATE TABLE script based on the specified <see cref="LogTableDefinition" />.</returns>
/// <exception cref="ArgumentNullException"><paramref name="table" /> is null.</exception>
public static SqlCommand BuildCreateTable(LogTableDefinition table)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
StringBuilder script = new StringBuilder();
script.AppendLine($"CREATE TABLE [dbo].[{table.Name}](");
// Build column definitions
foreach (LogTableColumn column in table)
{
string nullConstraint = column.AllowNulls ? "NULL" : "NOT NULL";
script.AppendLine($"[{column.Name}] {column.DataType} {nullConstraint},");
}
// Add primary key constraint
script.AppendLine($"CONSTRAINT [PK_{table.Name}] PRIMARY KEY CLUSTERED([{table.PrimaryKey}] ASC)");
script.AppendLine($"WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]");
// Close script and return
script.AppendLine(") ON[PRIMARY]");
return(new SqlCommand(script.ToString()));
}
/// <summary>
/// Builds a script that will insert data from the specified <see cref="LogTableRecord" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" /> for the database table.</param>
/// <param name="record">The <see cref="LogTableRecord" /> containing the data to insert.</param>
/// <returns>A <see cref="SqlCommand" /> for an INSERT script that inserts the data from the specified <see cref="LogTableRecord" />.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public static SqlCommand BuildInsert(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
SqlParameterHelper helper = new SqlParameterHelper(table, record);
StringBuilder script = new StringBuilder();
script.Append($"INSERT INTO [dbo].[{table.Name}](");
script.Append(string.Join(", ", helper.GetColumnNames()));
script.Append(") VALUES (");
script.Append(string.Join(", ", helper.GetColumnParams()));
script.Append(")");
// Add parameters for all values
SqlCommand command = new SqlCommand(script.ToString());
command.Parameters.AddRange(helper.GetSqlParameters().ToArray());
return(command);
}
/// <summary>
/// Builds a script that will update data from the specified <see cref="LogTableRecord" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" /> for the database table.</param>
/// <param name="record">The <see cref="LogTableRecord" /> containing the data to update.</param>
/// <returns>A <see cref="SqlCommand" /> for an UPDATE script that updates the data from the specified <see cref="LogTableRecord" />.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public static SqlCommand BuildUpdate(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
SqlParameterHelper helper = new SqlParameterHelper(table, record);
StringBuilder script = new StringBuilder();
script.Append($"UPDATE [dbo].[{table.Name}] SET ");
script.Append(string.Join(", ", helper.GetUpdateAssignments()));
script.Append(" WHERE ");
script.Append(helper.GetPrimaryKeyWhereClause());
// Add parameters for all values
SqlCommand command = new SqlCommand(script.ToString());
command.Parameters.AddRange(helper.GetSqlParameters().ToArray());
return(command);
}
/// <summary>
/// Updates data from the specified <see cref="LogTableRecord" /> into the table designated by the <see cref="LogTableDefinition" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" />.</param>
/// <param name="record">The <see cref="LogTableRecord" />.</param>
/// <returns><c>true</c> if submission was successful, <c>false</c> otherwise.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public bool Update(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
LogTrace($"UPDATE request received for {table.Name} (primary key = {record[table.PrimaryKey]})");
DataLogDatabaseResult result = _writer.Update(table, record);
bool updateSuccessful = result.Success;
// If the operation failed, try the alternate (if one exists)
if (!updateSuccessful && _alternateWriter != null)
{
LogTrace($"UPDATE failed. Attempting update to alternate database.");
DataLogDatabaseResult alternateResult = _alternateWriter.Update(table, record);
updateSuccessful = alternateResult.Success;
}
// If the operation hasn't succeeded, check to see if we should add this to the cache.
if (!updateSuccessful)
{
_cache?.Add(table, record, false);
}
return(updateSuccessful);
}
private static bool CreateTable(SqlConnection connection, LogTableDefinition table)
{
try
{
using (SqlCommand create = DataLogSqlBuilder.BuildCreateTable(table))
{
LogTrace($"SQL command: {create.CommandText}");
create.Connection = connection;
create.ExecuteNonQuery();
}
if (table.Any(n => n.Name == _sessionIdColumn))
{
using (SqlCommand key = DataLogSqlBuilder.BuildForeignKey(_sessionSummaryTable, _sessionIdColumn, table.Name, _sessionIdColumn))
{
LogTrace($"Building foreign key: {key.CommandText}");
key.Connection = connection;
key.ExecuteNonQuery();
}
}
return(true);
}
catch (SqlException ex)
{
LogError($"Table {table.Name} could not be created: {ex.Message}");
return(false);
}
}
public DataLogCacheData(LogTableDefinition table, LogTableRecord record, bool isInsert)
{
Table = table;
Record = record;
IsInsert = isInsert;
Retries = 0;
}
public DataLogDatabaseResult Insert(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
LogTrace($"INSERT {table.Name} (primary key = {record[table.PrimaryKey]})");
DataLogDatabaseResult result;
using (SqlCommand insert = DataLogSqlBuilder.BuildInsert(table, record))
{
result = new DataLogDatabaseResult(table.Name, insert);
try
{
using (SqlConnection connection = new SqlConnection(_connectionString.ToString()))
{
connection.Open();
insert.Connection = connection;
try
{
insert.ExecuteNonQuery();
}
catch (SqlException ex) when(ex.Message.Contains("Invalid object"))
{
// Table doesn't exist - create it and try again
LogDebug($"Generating new table {table.Name}");
if (CreateTable(connection, table))
{
LogDebug($"Table {table.Name} created.");
LogTrace($"Retrying INSERT {table.Name}");
insert.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
result.Error = ex.Message;
}
}
if (!result.Success)
{
LogWarn($"INSERT {table.Name} failed: {result.Error}");
LogTrace($"SQL command: " + result.Command);
LogTrace($"SQL parameters: {string.Join("; ", result.Parameters.Select(n => $"{n.Key}={n.Value}"))}");
}
return(result);
}
public SqlParameterHelper(LogTableDefinition table, LogTableRecord record)
{
_primaryKey = table.PrimaryKey;
_record = record;
// Ignore any columns that have no associated value in the LogTableRecord
foreach (LogTableColumn column in table.Columns)
{
if (_record.ContainsKey(column.Name))
{
_columns.Add(column);
}
}
}
/// <summary>
/// Updates data from the specified <see cref="LogTableRecord" /> into the table designated by the <see cref="LogTableDefinition" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" />.</param>
/// <param name="record">The <see cref="LogTableRecord" />.</param>
/// <returns><c>true</c> if submission was successful, <c>false</c> otherwise.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public bool Update(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
return(Channel.Update(table, record));
}
public DataLogDatabaseResult Update(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
LogTrace($"UPDATE {table.Name} (primary key = {record[table.PrimaryKey]})");
DataLogDatabaseResult result;
using (SqlCommand update = DataLogSqlBuilder.BuildUpdate(table, record))
{
result = new DataLogDatabaseResult(table.Name, update);
try
{
using (SqlConnection connection = new SqlConnection(_connectionString.ToString()))
{
connection.Open();
update.Connection = connection;
int affectedRows = update.ExecuteNonQuery();
if (affectedRows == 0)
{
result.Error = "No rows were affected.";
}
}
}
catch (Exception ex)
{
result.Error = ex.Message;
}
}
if (!result.Success)
{
LogWarn($"UPDATE {table.Name} failed: {result.Error}");
LogTrace($"SQL command: " + result.Command);
LogTrace($"SQL parameters: {string.Join("; ", result.Parameters.Select(n => $"{n.Key}={n.Value}"))}");
}
return(result);
}
// update 3.3.1 notes:
// Add CruiseID fields to Log and Stem tables
// Change is fully backwards compatible with prior versions
private void UpdateTo_3_3_1(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.3.1";
var fKeys = db.ExecuteScalar <string>("PRAGMA foreign_keys;");
db.Execute("PRAGMA foreign_keys=OFF;");
db.BeginTransaction();
try
{
// need to drop any views associated with tables we are rebuilding
db.Execute("DROP VIEW LogGradeError;");
var logTableDef = new LogTableDefinition();
var stemTableDef = new StemTableDefinition();
db.Execute("DROP TABLE Log_Tombstone;");
db.Execute("DROP TABLE Stem_Tombstone;");
db.Execute(logTableDef.CreateTombstoneTable);
db.Execute(stemTableDef.CreateTombstoneTable);
RebuildTable(db, logTableDef, customFieldMaps: new KeyValuePair <string, string>[]
{
new KeyValuePair <string, string>("CruiseID", "(SELECT CruiseID FROM Tree WHERE Tree.TreeID = Log.TreeID)"),
});
RebuildTable(db, stemTableDef, customFieldMaps: new KeyValuePair <string, string>[]
{
new KeyValuePair <string, string>("CruiseID", "(SELECT CruiseID FROM Tree WHERE Tree.TreeID = Stem.TreeID)"),
});
var lgeViewDef = new LogGradeErrorViewDefinition();
db.Execute(lgeViewDef.CreateView);
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
db.Execute($"PRAGMA foreign_keys={fKeys};");
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
/// <summary>
/// Inserts data from the specified <see cref="LogTableRecord" /> into the table designated by the <see cref="LogTableDefinition" />.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" />.</param>
/// <param name="record">The <see cref="LogTableRecord" />.</param>
/// <returns><c>true</c> if submission was successful, <c>false</c> otherwise.</returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public bool Insert(LogTableDefinition table, LogTableRecord record)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
LogTrace($"INSERT request received for {table.Name} (primary key = {record[table.PrimaryKey]})");
DataLogDatabaseResult result = _writer.Insert(table, record);
bool insertSuccessful = result.Success;
// If the operation failed, try the alternate (if one exists)
if (!insertSuccessful && _alternateWriter != null)
{
LogTrace($"INSERT failed. Attempting insert to alternate database.");
DataLogDatabaseResult alternateResult = _alternateWriter.Insert(table, record);
insertSuccessful = alternateResult.Success;
}
// If the operation hasn't succeeded, check to see if we should add this to the cache.
if (!insertSuccessful && _cache != null)
{
if (result.Error.Contains("Violation of PRIMARY KEY constraint", StringComparison.OrdinalIgnoreCase))
{
// Bypass the cache for primary key violations
result.Retries = -1;
CacheOperationsRetried?.Invoke(this, new DataLogCacheEventArgs(new[] { result }));
}
else
{
_cache.Add(table, record, true);
}
}
return(insertSuccessful);
}
/// <summary>
/// Adds the specified log table data to the cache.
/// </summary>
/// <param name="table">The <see cref="LogTableDefinition" />.</param>
/// <param name="record">The <see cref="LogTableRecord" />.</param>
/// <param name="isInsert">if set to <c>true</c> this data should be processed as an insert.</param>
/// <exception cref="ArgumentNullException">
/// <paramref name="table" /> is null.
/// <para>or</para>
/// <paramref name="record" /> is null.
/// </exception>
public void Add(LogTableDefinition table, LogTableRecord record, bool isInsert)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
string operation = isInsert ? "INSERT" : "UPDATE";
string fileName = $"{table.Name} {operation} {record[table.PrimaryKey]}.xml";
FileInfo file = new FileInfo(Path.Combine(_cacheLocation.FullName, fileName));
LogTrace($"Adding cache file {file.Name}");
DataLogCacheData cacheData = new DataLogCacheData(table, record, isInsert);
WriteCacheData(file, cacheData);
}
