protected override void AddValidFrom(DiscoveredTable table, IQuerySyntaxHelper syntaxHelper)
{
// MySql changed how they do default date fields between 5.5 and 5.6
//https://dba.stackexchange.com/a/132954
if (UseOldDateTimeDefaultMethod(table))
{
table.AddColumn(SpecialFieldNames.ValidFrom, "TIMESTAMP DEFAULT CURRENT_TIMESTAMP", true, UserSettings.ArchiveTriggerTimeout);
}
else
{
table.AddColumn(SpecialFieldNames.ValidFrom, "DATETIME DEFAULT CURRENT_TIMESTAMP", true, UserSettings.ArchiveTriggerTimeout);
}
}
public void TabTestWithOverrideHeaders_IncludePath(bool columnExistsInRaw)
{
string filename = Path.Combine(LoadDirectory.ForLoading.FullName, "bob.csv");
var sw = new StreamWriter(filename);
sw.WriteLine("Face\tBasher");
sw.WriteLine("Candy\tCrusher");
sw.Flush();
sw.Close();
sw.Dispose();
if (columnExistsInRaw)
{
_table.AddColumn("FilePath", new DatabaseTypeRequest(typeof(string), 500), true, 30);
}
var attacher = new AnySeparatorFileAttacher();
attacher.Initialize(LoadDirectory, _database);
attacher.Separator = "\\t";
attacher.FilePattern = "bob*";
attacher.TableName = "Bob";
attacher.ForceHeaders = "name\tname2";
attacher.AddFilenameColumnNamed = "FilePath";
if (!columnExistsInRaw)
{
var ex = Assert.Throws <FlatFileLoadException>(() => attacher.Attach(new ThrowImmediatelyDataLoadJob(), new GracefulCancellationToken()));
Assert.AreEqual("AddFilenameColumnNamed is set to 'FilePath' but the column did not exist in RAW", ex.InnerException.Message);
return;
}
var exitCode = attacher.Attach(new ThrowImmediatelyDataLoadJob(), new GracefulCancellationToken());
Assert.AreEqual(ExitCodeType.Success, exitCode);
using (var con = _database.Server.GetConnection())
{
con.Open();
var r = _database.Server.GetCommand("Select name,name2,FilePath from Bob", con).ExecuteReader();
Assert.IsTrue(r.Read());
Assert.AreEqual("Face", r["name"]);
Assert.AreEqual("Basher", r["name2"]);
Assert.AreEqual(filename, r["FilePath"]);
Assert.IsTrue(r.Read());
Assert.AreEqual("Candy", r["name"]);
Assert.AreEqual("Crusher", r["name2"]);
}
attacher.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener());
File.Delete(filename);
}
private void CreateIsolationTable(DiscoveredTable toCreate, TableInfo tableInfo)
{
var from = tableInfo.Discover(DataAccessContext.DataLoad);
//create a RAW table schema called TableName_Isolation
var cloner = new TableInfoCloneOperation(null, null, LoadBubble.Live);
cloner.CloneTable(from.Database, toCreate.Database, from, toCreate.GetRuntimeName(), true, true, true, tableInfo.PreLoadDiscardedColumns);
if (!toCreate.Exists())
{
throw new Exception(string.Format("Table '{0}' did not exist after issuing create command", toCreate));
}
//Add the data load run id
toCreate.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), false, 10);
}
private void CreateIsolationTable(DiscoveredTable toCreate, TableInfo tableInfo)
{
var from = tableInfo.Discover(DataAccessContext.DataLoad);
//create a RAW table schema called TableName_Isolation
var cloner = new TableInfoCloneOperation(new HICDatabaseConfiguration(toCreate.Database.Server), tableInfo, LoadBubble.Live, _job ?? (IDataLoadEventListener) new ThrowImmediatelyDataLoadEventListener());
cloner.CloneTable(from.Database, toCreate.Database, from, toCreate.GetRuntimeName(), true, true, true, tableInfo.PreLoadDiscardedColumns);
if (!toCreate.Exists())
{
throw new Exception($"Table '{toCreate}' did not exist after issuing create command");
}
//Add the data load run id
toCreate.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), false, 10);
}
public void AlterTest_InvalidThenRecreateItAndItsValidAgain(DatabaseType dbType)
{
CreateWithPks_Valid(dbType);
_table.AddColumn("fish", new DatabaseTypeRequest(typeof(int)), true, 500);
_archiveTable.AddColumn("fish", new DatabaseTypeRequest(typeof(int)), true, 500);
//still not valid because trigger SQL is missing it in the column list
var ex = Assert.Throws <ExpectedIdenticalStringsException>(() => GetImplementer().CheckUpdateTriggerIsEnabledAndHasExpectedBody());
Assert.IsNotNull(ex.Message);
string problemsDroppingTrigger, thingsThatWorkedDroppingTrigger;
var implementer = GetImplementer();
implementer.DropTrigger(out problemsDroppingTrigger, out thingsThatWorkedDroppingTrigger);
implementer.CreateTrigger(new ThrowImmediatelyCheckNotifier());
Assert.AreEqual(true, implementer.CheckUpdateTriggerIsEnabledAndHasExpectedBody());
}
public virtual string CreateTrigger(ICheckNotifier notifier, int timeout = 30)
{
if (!_primaryKeys.Any())
{
throw new TriggerException("There must be at least 1 primary key");
}
//if _Archive exists skip creating it
bool skipCreatingArchive = _archiveTable.Exists();
//check _Archive does not already exist
foreach (string forbiddenColumnName in new[] { "hic_validTo", "hic_userID", "hic_status" })
{
if (_columns.Any(c => c.GetRuntimeName().Equals(forbiddenColumnName, StringComparison.CurrentCultureIgnoreCase)))
{
throw new TriggerException("Table " + _table + " already contains a column called " + forbiddenColumnName + " this column is reserved for Archiving");
}
}
bool b_mustCreate_validFrom = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase));
bool b_mustCreate_dataloadRunId = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase)) && _createDataLoadRunIdAlso;
//forces column order dataloadrunID then valid from (doesnt prevent these being in the wrong place in the record but hey ho - possibly not an issue anyway since probably the 3 values in the archive are what matters for order - see the Trigger which populates *,X,Y,Z where * is all columns in mane table
if (b_mustCreate_dataloadRunId && !b_mustCreate_validFrom)
{
throw new TriggerException("Cannot create trigger because table contains " + SpecialFieldNames.ValidFrom + " but not " + SpecialFieldNames.DataLoadRunID + " (ID must be placed before valid from in column order)");
}
//must add validFrom outside of transaction if we want SMO to pick it up
if (b_mustCreate_dataloadRunId)
{
_table.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), true, timeout);
}
var syntaxHelper = _server.GetQuerySyntaxHelper();
var dateTimeDatatype = syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime)));
var nowFunction = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate);
//must add validFrom outside of transaction if we want SMO to pick it up
if (b_mustCreate_validFrom)
{
_table.AddColumn(SpecialFieldNames.ValidFrom, string.Format(" {0} DEFAULT {1}", dateTimeDatatype, nowFunction), true, timeout);
}
//if we created columns we need to update _column
if (b_mustCreate_dataloadRunId || b_mustCreate_validFrom)
{
_columns = _table.DiscoverColumns();
}
string sql = WorkOutArchiveTableCreationSQL();
if (!skipCreatingArchive)
{
using (var con = _server.GetConnection())
{
con.Open();
var cmdCreateArchive = _server.GetCommand(sql, con);
cmdCreateArchive.ExecuteNonQuery();
_archiveTable.AddColumn("hic_validTo", new DatabaseTypeRequest(typeof(DateTime)), true, timeout);
_archiveTable.AddColumn("hic_userID", new DatabaseTypeRequest(typeof(string), 128), true, timeout);
_archiveTable.AddColumn("hic_status", new DatabaseTypeRequest(typeof(string), 1), true, timeout);
}
}
return(sql);
}
private void AddColumnToTable(DiscoveredTable table, string desiredColumnName, string desiredColumnType, IDataLoadEventListener listener)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, string.Format("Adding column '{0}' with datatype '{1}' to table '{2}'", desiredColumnName, desiredColumnType, table.GetFullyQualifiedName())));
table.AddColumn(desiredColumnName, desiredColumnType, true, 500);
}
