public static bool SwitchToMo(
this Table self,
Database inMemDatabase,
Database traditional,
Configuration.Configuration cnf,
Options.Options o,
ref string error,
ILog logger,
SqlServerMoFeatures enumFeatures)
{
var schemaName = self.Schema;
if (inMemDatabase.Schemas.Contains(schemaName) == false)
{
var hr = new Schema(inMemDatabase, schemaName);
inMemDatabase.Schemas.Add(hr);
inMemDatabase.Schemas[schemaName].Create();
}
if (inMemDatabase.Tables.Contains(self.Name, schemaName))
{
logger.Log("\t" + "Already exists", self.FName());
return(true);
}
var hasIdentities = false;
var newTable = new Table(inMemDatabase, self.Name, schemaName)
{
// default true
IsMemoryOptimized = true,
// default schema and data
Durability = DurabilityType.SchemaAndData
};
// Add columns
foreach (Column c in self.Columns)
{
var newColumn = new Column(newTable, c.Name);
newColumn.CopyPropertiesFrom(c);
SupportUnsupported(newColumn, c, traditional, logger, ref error, ref hasIdentities, true);
newTable.Columns.Add(newColumn);
}
//Add indexes
var hasPrimaryKey = false;
foreach (Index i in self.Indexes)
{
if (i.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
hasPrimaryKey = true;
var idx = new Index(newTable, i.Name);
if (o.UseHashIndexes == IndexDecision.Hash)
{
idx.IndexType = IndexType.NonClusteredHashIndex;
idx.BucketCount = self.RowCount != 0 ? (int)self.RowCount * 2 : 64;
}
else if (o.UseHashIndexes == IndexDecision.Range)
{
idx.IndexType = IndexType.NonClusteredIndex;
}
else
{
if (self.ExtendedProperties[cnf.EpName] != null)
{
var value = self.ExtendedProperties[cnf.EpName].Value.ToString().ToUpper();
if (value == "HASH")
{
idx.IndexType = IndexType.NonClusteredHashIndex;
idx.BucketCount = self.RowCount == 0 ? 64 : (int)self.RowCount;
}
else
{
idx.IndexType = IndexType.NonClusteredIndex;
}
}
else
{
idx.IndexType = IndexType.NonClusteredIndex;
}
}
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
foreach (IndexedColumn ic in i.IndexedColumns)
{
idx.IndexedColumns.Add(new IndexedColumn(idx, ic.Name));
}
newTable.Indexes.Add(idx);
}
}
var noIndexes = hasPrimaryKey ? 1 : 0;
foreach (Index i in self.Indexes)
{
if (i.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
continue;
}
if (i.IndexType == IndexType.NonClusteredIndex)
{
// Limit the total number of indexes to 8 for SQLServer2016
if (enumFeatures == SqlServerMoFeatures.SqlServer2016 && noIndexes == 8)
{
logger.LogWarErr("Error:Create index failed",
$"Could not create in-memory index {i.Name} because it exceeds the maximum of 8 allowed per table or view.");
continue;
}
Index idx = new Index(newTable, i.Name)
{
IndexType = IndexType.NonClusteredIndex,
IndexKeyType = IndexKeyType.None
};
idx.IsUnique = i.IsUnique;
bool hasColumns = false;
foreach (IndexedColumn ic in i.IndexedColumns)
{
if (ic.IsIncluded)
{
continue;
}
// nvarchar(max) is not allowed
if (newTable.Columns[ic.Name].DataType.MaximumLength == -1)
{
logger.LogWarErr("Warning:Create index",
$"Could not include {ic.Name} in index {idx.Name} The column has nvarchar(max) type which is not allowed!");
continue;
}
idx.IndexedColumns.Add(new IndexedColumn(idx, ic.Name));
hasColumns = true;
}
if (hasColumns)
{
newTable.Indexes.Add(idx);
noIndexes++;
}
}
}
if (hasPrimaryKey == false)
{
error = $"Error:Table :{self.FName()} has no primary key";
//logger.LogWarErr(error, self.FName());
return(false);
}
//if (inMemDatabase.Tables.Contains(newTable.Name, schemaName))
// inMemDatabase.Tables[newTable.Name, schemaName].Drop();
// Add checks
foreach (Check ch in self.Checks)
{
var newch = new Check(newTable, ch.Name);
newch.CopyPropertiesFrom(ch);
if (newch.Text.ToLower().Contains("upper") || newch.Text.ToLower().Contains("like") ||
newch.Text.ToLower().Contains("charindex"))
{
logger.LogWarErr($"Warning {newch.Name}",
$" can not apply constraint on table {self.FName()} because it contains forbidden functions ");
continue;
}
newTable.Checks.Add(newch);
}
// Skip triggers
foreach (Trigger tr in self.Triggers)
{
logger.LogWarErr($"Warning {tr.Name}", $" can not create trigger on table {self.FName()}.Please, create trigger manually! ");
}
try
{
logger.Log("Create table ", newTable.FName());
newTable.Create();
}
catch (Exception ex)
{
error = string.Join($"{Environment.NewLine}\t", ex.CollectThemAll(ex1 => ex1.InnerException)
.Select(ex1 => ex1.Message));
if (Debugger.IsAttached)
{
Debugger.Break();
}
return(false);
}
// if copy data is checked
if (o.CopyData)
{
if (inMemDatabase.Tables.Contains(cnf.HelperTableName, cnf.HelperSchema))
{
inMemDatabase.Tables[cnf.HelperTableName, cnf.HelperSchema].Drop();
}
try
{
logger.Log("Insert data ", newTable.FName());
//Insert into
traditional.ExecuteNonQuery(self.InsertIntoStm(inMemDatabase.Name, cnf.FullName));
//Insert statement
var test = inMemDatabase.Tables[cnf.HelperTableName, cnf.HelperSchema];
inMemDatabase.ExecuteNonQuery(newTable.FullInsertStm(test.SelectStm(), hasIdentities,
cnf.FullName));
logger.Log("OK ", newTable.FName());
//
}
catch (Exception ex)
{
if (inMemDatabase.Tables.Contains(newTable.Name, schemaName))
{
inMemDatabase.Tables[newTable.Name, schemaName].Drop();
}
logger.Log("Error", self.FName());
error = string.Join($"{Environment.NewLine}\t", ex.CollectThemAll(ex1 => ex1.InnerException)
.Select(ex1 => ex1.Message));
if (Debugger.IsAttached)
{
Debugger.Break();
}
}
}
newTable = null;
return(error == "");
}
/// <summary>
///
/// </summary>
/// <param name="self"></param>
/// <param name="dbInMemory"></param>
/// <param name="logger"></param>
/// <param name="cnf"></param>
/// <param name="o"></param>
/// <param name="enumFeatures"></param>
/// <returns></returns>
public static bool SwitchToMo(this Database self,
Database dbInMemory,
ILog logger,
Configuration.Configuration cnf,
Options.Options o,
SqlServerMoFeatures enumFeatures)
{
logger.SetOverall("1/6");
logger.CurrentItem = 1;
logger.Counter = self.Tables.Count;
logger.SetValue(logger.CurrentItem);
logger.SetMaxValue(logger.Counter);
var tables = self.Tables;
foreach (Table tbl in tables)
{
if (o.Schemas.Count > 0 && !o.Schemas.Contains(tbl.SchemaLName()))
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
if (o.Tables.Count > 0 && !o.Tables.Contains(tbl.LName()))
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
//cnnInMem.Connect();
//serverInMem = new Server(cnnInMem);
//dbInMemory = serverInMem.Databases[i.inMemoryDataBaseName];
logger.SetValue(logger.CurrentItem);
string error = string.Empty;
if (tbl.SwitchToMo(dbInMemory, self, cnf, o, ref error, logger, enumFeatures) == false)
{
logger.LogWarErr("TABLE:Error ", error);
}
//Thread.Sleep(50);
logger.CurrentItem++;
//cnnInMem.Disconnect();
}
logger.SetOverall("2/6");
//switch relations
logger.CurrentItem = 1;
logger.Counter = tables.Count;
logger.SetMaxValue(logger.Counter);
foreach (Table tbl in tables)
{
if (o.Schemas.Count > 0 && !o.Schemas.Contains(tbl.SchemaLName()))
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
if (o.Tables.Count > 0 && !o.Tables.Contains(tbl.LName()))
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
logger.SetValue(logger.CurrentItem);
var error = string.Empty;
if (tbl.SwitchRelationsToMo(dbInMemory, ref error, logger) == false)
{
logger.LogWarErr("RELATION:Error", error);
}
logger.CurrentItem++;
}
//3/6
logger.SetOverall("3/6");
//switch relations
logger.CurrentItem = 1;
logger.Counter = self.UserDefinedTableTypes.Count;
logger.SetValue(logger.CurrentItem);
logger.SetMaxValue(logger.Counter);
var udtts = self.UserDefinedTableTypes;
foreach (UserDefinedTableType tbl in udtts)
{
if (o.Schemas.Count > 0 && !o.Schemas.Contains(tbl.Schema.ToLower()))
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
if (o.Tables.Count > 0 && !o.Tables.Contains(tbl.Name.ToLower()))
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
logger.SetValue(logger.CurrentItem);
var error = string.Empty;
if (tbl.SwitchToMo(dbInMemory, self, ref error, logger) == false)
{
logger.LogWarErr("UDT:Error", error);
}
logger.CurrentItem++;
}
logger.SetOverall("4/6");
// user defined function
//IEnumerable<UserDefinedFunction> udfs = ConvertToGenericList<UserDefinedFunction>(self.UserDefinedFunctions).Where(a => a.IsSystemObject == false);
var udfs = self.UserDefinedFunctions;
logger.CurrentItem = 1;
logger.Counter = udfs.Count;
logger.SetValue(logger.CurrentItem);
logger.SetMaxValue(logger.Counter);
foreach (UserDefinedFunction sp in udfs)
{
if (sp.IsSystemObject)
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
if (o.Schemas.Count > 0 && !o.Schemas.Contains(sp.Schema.ToLower()))
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
logger.SetValue(logger.CurrentItem);
string error = string.Empty;
if (sp.SwitchToMo(dbInMemory, ref error, logger) == false)
{
logger.LogWarErr("UDF:Error", error);
}
logger.CurrentItem++;
}
logger.SetOverall("5/6");
// STORED PROCEDURES
//IEnumerable<StoredProcedure> sps = ConvertToGenericList<StoredProcedure>(self.StoredProcedures).Where(a => a.IsSystemObject == false);
var sps = self.StoredProcedures;
logger.CurrentItem = 1;
logger.Counter = sps.Count;
logger.SetValue(logger.CurrentItem);
logger.SetMaxValue(logger.Counter);
foreach (StoredProcedure sp in sps)
{
if (sp.IsSystemObject)
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
if (o.Schemas.Count > 0 && !o.Schemas.Contains(sp.Schema.ToLower()))
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
logger.SetValue(logger.CurrentItem);
string error = string.Empty;
if (sp.SwitchToMo(dbInMemory, ref error, logger) == false)
{
logger.LogWarErr("SP:Error", error);
}
logger.CurrentItem++;
}
logger.SetOverall("6/6");
var vs = self.Views;
// VIEWS
//IEnumerable<View> vs = ConvertToGenericList<View>(self.Views).Where(a => a.IsSystemObject == false);
logger.CurrentItem = 1;
logger.Counter = vs.Count;
logger.SetValue(logger.CurrentItem);
logger.SetMaxValue(logger.Counter);
foreach (View sp in vs)
{
if (sp.IsSystemObject)
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
if (o.Schemas.Count > 0 && !o.Schemas.Contains(sp.Schema.ToLower()))
{
logger.CurrentItem++;
logger.SetValue(logger.CurrentItem);
continue;
}
logger.SetValue(logger.CurrentItem);
string error = string.Empty;
if (sp.SwitchToMo(dbInMemory, ref error, logger) == false)
{
logger.LogWarErr("VIEW:Error", error);
}
logger.CurrentItem++;
}
logger.SetOverall(string.Empty);
logger.Log(string.Empty, string.Empty);
tables = null;
udtts = null;
vs = null;
udfs = null;
sps = null;
return(true);
}
