public void IndexedColumn_Churn()
{
IColumn <ByteBlock> c = new IndexedColumn(ColumnFactory.CreateSortedColumn <ByteBlock>(new ByteBlockColumn(ByteBlock.Zero), 0), new DefaultWordSplitter());
c.SetSize(4);
c[0] = "First Value";
c[1] = "Second Value";
c[2] = "this value is yet another one";
ColumnTests.AssertConsistent(c);
// Remove the last word added and ensure no problems (unique case, no swap and remove)
Assert.AreEqual("2", ColumnTests.GetMatches(c, Operator.MatchesExact, "one"));
c[2] = "this value is yet another";
ColumnTests.AssertConsistent(c);
Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.MatchesExact, "one"));
Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value"));
// Reset a value to itself (reusing the read ByteBlock); ensure no problems
c[1] = c[1];
ColumnTests.AssertConsistent(c);
Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value"));
// Set a value to null; ensure no problems
c[0] = (ByteBlock)(string)null;
ColumnTests.AssertConsistent(c);
Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.MatchesExact, "first"));
Assert.AreEqual("1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value"));
}
public void CreateTableProduct()
{
Table product = new Table(_database, "Product");
Index index = new Index(product, "barecode_Index");
index.IsClustered = true;
IndexedColumn columnIndex = new IndexedColumn(index, "barecode", true);
index.IndexedColumns.Add(columnIndex);
Column column = new Column(product, "nameProduct", DataType.VarChar(50));
product.Columns.Add(column);
column = new Column(product, "priceProduct", DataType.Float);
product.Columns.Add(column);
column = new Column(product, "descriptionProduct", DataType.VarCharMax);
product.Columns.Add(column);
column = new Column(product, "imageProduct", DataType.VarBinaryMax);
product.Columns.Add(column);
column = new Column(product, "qteEnStockProduct", DataType.Int);
product.Columns.Add(column);
product.Create();
}
private static void createIndexes(Table sourcetable, Table copiedtable)
{
foreach (Index srcind in sourcetable.Indexes)
{
if (!srcind.IsDisabled && (srcind.IsClustered ||
(!srcind.IsClustered && !srcind.IsXmlIndex)))
{
var name = copiedtable.Name + "_" + srcind.Name;
var index = new Index(copiedtable, name);
index.IndexKeyType = srcind.IndexKeyType;
index.IsClustered = srcind.IsClustered;
index.IsUnique = srcind.IsUnique;
index.CompactLargeObjects = srcind.CompactLargeObjects;
index.IgnoreDuplicateKeys = srcind.IgnoreDuplicateKeys;
index.IsFullTextKey = srcind.IsFullTextKey;
index.PadIndex = srcind.PadIndex;
index.FileGroup = srcind.FileGroup;
foreach (IndexedColumn srccol in srcind.IndexedColumns)
{
var column =
new IndexedColumn(index, srccol.Name, srccol.Descending);
column.IsIncluded = srccol.IsIncluded;
index.IndexedColumns.Add(column);
}
index.Create();
}
}
}
public IndexColumn([NotNull] IndexedColumn indexedColumn)
{
Assert.ArgumentNotNull(indexedColumn, nameof(indexedColumn));
IsIncluded = indexedColumn.IsIncluded;
IsComputed = indexedColumn.IsComputed;
Descending = indexedColumn.Descending;
}
private Index CreateIndexCopy(Table targetTable, Index index)
{
var newIndex = new Index(targetTable, Guid.NewGuid().ToString("N"))
{
BoundingBoxXMax = index.BoundingBoxXMax,
BoundingBoxXMin = index.BoundingBoxXMin,
BoundingBoxYMax = index.BoundingBoxYMax,
BoundingBoxYMin = index.BoundingBoxYMin,
BucketCount = index.BucketCount,
CellsPerObject = index.CellsPerObject,
CompactLargeObjects = index.CompactLargeObjects,
DisallowPageLocks = index.DisallowPageLocks,
DisallowRowLocks = index.DisallowRowLocks,
FileGroup = index.FileGroup,
FileStreamFileGroup = index.FileStreamFileGroup,
FileStreamPartitionScheme = index.FileStreamPartitionScheme,
FillFactor = index.FillFactor,
FilterDefinition = index.FilterDefinition,
IgnoreDuplicateKeys = index.IgnoreDuplicateKeys,
IndexKeyType = index.IndexKeyType,
IndexType = index.IndexType,
IndexedXmlPathName = index.IndexedXmlPathName,
IsClustered = index.IsClustered,
IsFullTextKey = index.IsFullTextKey,
IsMemoryOptimized = index.IsMemoryOptimized,
IsUnique = index.IsUnique,
Level1Grid = index.Level1Grid,
Level2Grid = index.Level2Grid,
Level3Grid = index.Level3Grid,
Level4Grid = index.Level4Grid,
LowPriorityAbortAfterWait = index.LowPriorityAbortAfterWait,
LowPriorityMaxDuration = index.LowPriorityMaxDuration,
MaximumDegreeOfParallelism = index.MaximumDegreeOfParallelism,
NoAutomaticRecomputation = index.NoAutomaticRecomputation,
OnlineIndexOperation = index.OnlineIndexOperation,
PadIndex = index.PadIndex,
ParentXmlIndex = index.ParentXmlIndex,
PartitionScheme = index.PartitionScheme,
SecondaryXmlIndexType = index.SecondaryXmlIndexType,
SortInTempdb = index.SortInTempdb,
SpatialIndexType = index.SpatialIndexType
};
newIndex.ExtendedProperties.Add(new ExtendedProperty(newIndex, "CopyOf", index.Name));
foreach (IndexedColumn column in index.IndexedColumns)
{
var newColumn = new IndexedColumn(newIndex, column.Name, column.Descending)
{
IsIncluded = column.IsIncluded
};
newIndex.IndexedColumns.Add(newColumn);
}
return(newIndex);
}
public static void AddColumnToIndex(Table table, Column column, Index ind)
{
string oldName = ind.Name;
var idx = new Index(table, oldName)
{
BoundingBoxYMax = ind.BoundingBoxYMax,
BoundingBoxYMin = ind.BoundingBoxYMin,
BoundingBoxXMax = ind.BoundingBoxXMax,
BoundingBoxXMin = ind.BoundingBoxXMin,
CompactLargeObjects = ind.CompactLargeObjects,
DisallowRowLocks = ind.DisallowRowLocks,
FileGroup = ind.FileGroup,
FileStreamFileGroup = ind.FileStreamFileGroup,
FileStreamPartitionScheme = ind.FileStreamPartitionScheme,
FillFactor = ind.FillFactor,
FilterDefinition = ind.FilterDefinition,
IgnoreDuplicateKeys = ind.IgnoreDuplicateKeys,
IndexKeyType = ind.IndexKeyType,
IsClustered = ind.IsClustered,
IsFullTextKey = ind.IsFullTextKey,
IsUnique = ind.IsUnique,
Level1Grid = ind.Level1Grid,
Level2Grid = ind.Level2Grid,
Level3Grid = ind.Level3Grid,
Level4Grid = ind.Level4Grid,
MaximumDegreeOfParallelism = ind.MaximumDegreeOfParallelism,
NoAutomaticRecomputation = ind.NoAutomaticRecomputation,
OnlineIndexOperation = ind.OnlineIndexOperation,
PadIndex = ind.PadIndex,
ParentXmlIndex = ind.ParentXmlIndex,
PartitionScheme = ind.PartitionScheme,
SecondaryXmlIndexType = ind.SecondaryXmlIndexType,
SortInTempdb = ind.SortInTempdb,
SpatialIndexType = ind.SpatialIndexType,
};
foreach (IndexedColumn iColumn in ind.IndexedColumns)
{
var newIdxColumn = new IndexedColumn(idx, iColumn.Name)
{
Descending = iColumn.Descending,
IsIncluded = iColumn.IsIncluded,
};
idx.IndexedColumns.Add(newIdxColumn);
}
idx.IndexedColumns.Add(new IndexedColumn(idx, column.Name));
bool oldDisallowPageLocks = ind.DisallowPageLocks;
ind.Drop();
idx.Create();
idx.DisallowPageLocks = oldDisallowPageLocks;
idx.Alter();
}
private string FormatColumn(IndexedColumn column)
{
string s = column.Name;
if (column.Order == Order.Descending)
{
s += " " + Constants.Descending;
}
return(s);
}
public static void AddClusterIndex(this Table tb, string columnName)
{
Index idx = new Index(tb, string.Format("IDX_{0}_{1}", tb.Name, columnName));
IndexedColumn indexedColumn = new IndexedColumn(idx, columnName);
idx.IndexedColumns.Add(indexedColumn);
idx.IndexKeyType = IndexKeyType.None;
idx.IsClustered = true;
idx.Create();
}
public static void AddPrimaryKey(this Table tb, string columnName)
{
//create primary key index
Index pk = new Index(tb, string.Format("PK_{0}_{1}", tb.Name, columnName));
IndexedColumn indexedColumn = new IndexedColumn(pk, columnName);
pk.IndexedColumns.Add(indexedColumn);
pk.IndexKeyType = IndexKeyType.DriPrimaryKey;
pk.IsClustered = false;
pk.Create();
}
/// <summary>
/// Create index for split table
/// </summary>
/// <param name="productionTable">Split production table</param>
/// <param name="surveyId">Survey ID</param>
/// <param name="databaseEngine">Pointer of database class</param>
/// <returns></returns>
public override MySmoObjectBase CreateSplitItem(
Table productionTable,
int surveyId,
Database databaseEngine)
{
var newIndex = new Index();
var modelIndex = (Index)SourceSmoObject;
newIndex.Parent = productionTable;
if (surveyId == 0)
{
newIndex.Name = modelIndex.Name;
}
else
{
newIndex.Name = databaseEngine.GetTemplateNameByOriginalName(
modelIndex.Name,
surveyId);
}
newIndex.IgnoreDuplicateKeys = modelIndex.IgnoreDuplicateKeys;
newIndex.IndexKeyType = modelIndex.IndexKeyType;
newIndex.IsClustered = modelIndex.IsClustered;
newIndex.IsUnique = modelIndex.IsUnique;
newIndex.NoAutomaticRecomputation = modelIndex.NoAutomaticRecomputation;
newIndex.OnlineIndexOperation = modelIndex.OnlineIndexOperation;
newIndex.DisallowPageLocks = modelIndex.DisallowPageLocks;
newIndex.DisallowRowLocks = modelIndex.DisallowRowLocks;
newIndex.SortInTempdb = modelIndex.SortInTempdb;
newIndex.IsFullTextKey = modelIndex.IsFullTextKey;
newIndex.PadIndex = modelIndex.PadIndex;
newIndex.FillFactor = modelIndex.FillFactor;
newIndex.MaximumDegreeOfParallelism = modelIndex.MaximumDegreeOfParallelism;
foreach (IndexedColumn indexedColumn in modelIndex.IndexedColumns)
{
var newIndexedColumn = new IndexedColumn(
newIndex,
indexedColumn.Name,
indexedColumn.Descending)
{
IsIncluded = indexedColumn.IsIncluded
};
newIndex.IndexedColumns.Add(newIndexedColumn);
}
return(new MySmoObjectBase(
newIndex,
newIndex.Name,
productionTable.Name));
}
private static Dictionary <string, List <ushort> > GetColumnIndexAsDictionary(IColumn <object> column)
{
IndexedColumn indexedColumn = FindColumnComponent <IndexedColumn>(column);
if (indexedColumn == null)
{
return(null);
}
else
{
return(indexedColumn.ConvertToDictionary());
}
}
public static void AddIndex(this Table tb, IEnumerable <string> columnNames)
{
Index idx = new Index(tb, string.Format("IDX_{0}_{1}", tb.Name, string.Join("_", columnNames)));
foreach (string name in columnNames)
{
IndexedColumn indexedColumn = new IndexedColumn(idx, name);
idx.IndexedColumns.Add(indexedColumn);
}
idx.IndexKeyType = IndexKeyType.None;
idx.IsClustered = false;
idx.Create();
}
public void IndexedColumn_Where()
{
IColumn <ByteBlock> c = new IndexedColumn(ColumnFactory.CreateSortedColumn <ByteBlock>(new ByteBlockColumn(ByteBlock.Zero), 0), new DefaultWordSplitter());
// Verify no error searching empty column
Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.Matches, "missing"));
c.SetSize(4);
c[0] = "First Value";
c[1] = "Second Value";
c[2] = "this is a different value";
ColumnTests.AssertConsistent(c);
// Verify passthrough to base column
Assert.AreEqual("0", ColumnTests.GetMatches(c, Operator.Equals, "First Value"));
// Verify unknown words don't match (no error)
Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.Matches, "missing"));
// Verify all match for shared words
Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.Matches, "value"));
Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value"));
// Verify prefixes match for 'Matches' and not 'MatchesExact'
Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.Matches, "val"));
Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.MatchesExact, "val"));
// Verify unique words return only those matches
Assert.AreEqual("0", ColumnTests.GetMatches(c, Operator.Matches, "first"));
Assert.AreEqual("2", ColumnTests.GetMatches(c, Operator.Matches, "diff"));
// Verify matches fallthrough to equals
Assert.AreEqual("0", ColumnTests.GetMatches(c, Operator.Matches, "First Value"));
c[0] = "Updated Value Here";
ColumnTests.AssertConsistent(c);
// Verify items no longer match for removed words
Assert.AreEqual("", ColumnTests.GetMatches(c, Operator.Matches, "first"));
// Verify items continue to match for kept words
Assert.AreEqual("0, 1, 2", ColumnTests.GetMatches(c, Operator.MatchesExact, "value"));
// Verify items match for new words
Assert.AreEqual("0", ColumnTests.GetMatches(c, Operator.MatchesExact, "updated"));
}
internal static string ToCreateScript(this IndexedColumn column, Table table)
{
if (String.IsNullOrEmpty(column.Name))
{
throw new ArgumentException($"{column} {nameof(IndexedColumn)}.{nameof(IndexedColumn.Name)} cannot be null or empty.");
}
var builder = new StringBuilder();
builder.Append($"[{column.Name}]");
if (!String.IsNullOrEmpty(column.CollationName))
{
builder.Append($"collate {column.CollationName}");
}
builder.Append($" {column.Direction.ToSqlString()}");
return(builder.ToString());
}
public IEnumerable<string> AddIndex(string tableName, IEnumerable<string> columnNames, string indexName)
{
Table table = GetTable(tableName);
Index index = new Index(table, indexName);
table.Indexes.Add(index);
foreach (string columnName in columnNames)
{
var column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName)
{
DataType = Microsoft.SqlServer.Management.Smo.DataType.Variant
};
table.Columns.Add(column);
IndexedColumn indexedColumn = new IndexedColumn(index, columnName);
index.IndexedColumns.Add(indexedColumn);
}
index.Create();
return ScriptChanges(table.Parent.Parent);
}
private static void CreatePurchaseOrderTable(Database database)
{
Table purchaseOrdersTable = new Table(database, "PurchaseOrders");
purchaseOrdersTable.Schema = "dbo";
Column idColumn = new Column(purchaseOrdersTable, "Id", DataType.Int);
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
Column customerIdColumn = new Column(purchaseOrdersTable, "CustomerId", DataType.Int);
Column orderDateTimeColumn = new Column(purchaseOrdersTable, "OrderDateTime", DataType.DateTime);
Column orderTotalColumn = new Column(purchaseOrdersTable, "OrderTotal", DataType.Money);
purchaseOrdersTable.Columns.Add(idColumn);
purchaseOrdersTable.Columns.Add(customerIdColumn);
purchaseOrdersTable.Columns.Add(orderDateTimeColumn);
purchaseOrdersTable.Columns.Add(orderTotalColumn);
ForeignKey foreignKey = new ForeignKey(purchaseOrdersTable, "FK_PurchaseOrders_CustomerId_Customers_Id");
ForeignKeyColumn foreignKeyColumn = new ForeignKeyColumn(foreignKey, "CustomerId", "Id");
foreignKey.ReferencedTable = "Customers";
foreignKey.ReferencedTableSchema = "dbo";
foreignKey.Columns.Add(foreignKeyColumn);
purchaseOrdersTable.ForeignKeys.Add(foreignKey);
Index index = new Index(purchaseOrdersTable, "PK_PurchaseOrders");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
IndexedColumn idIndexedColumn = new IndexedColumn(index, "Id");
index.IndexedColumns.Add(idIndexedColumn);
purchaseOrdersTable.Indexes.Add(index);
purchaseOrdersTable.Create();
}
public IEnumerable <string> AddIndex(string tableName, IEnumerable <string> columnNames, string indexName)
{
Table table = GetTable(tableName);
Index index = new Index(table, indexName);
table.Indexes.Add(index);
foreach (string columnName in columnNames)
{
var column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName)
{
DataType = Microsoft.SqlServer.Management.Smo.DataType.Variant
};
table.Columns.Add(column);
IndexedColumn indexedColumn = new IndexedColumn(index, columnName);
index.IndexedColumns.Add(indexedColumn);
}
index.Create();
return(ScriptChanges(table.Parent.Parent));
}
/// <summary>
/// Create indexes for WKB table DEPRECATED!!!!!
/// </summary>
/// <param name="conn"></param>
/// <param name="tableName"></param>
public static void CreateWkbIndexes(SqlConnection conn, string tableName)
{
Server srv = new Server(new ServerConnection(new SqlConnection((conn.ConnectionString))));
Database db = srv.Databases[conn.Database];
Table tbl = db.Tables[tableName];
var envelopeFields = new[] { WkbfieldsDblEnvelopeMinX, WkbfieldsDblEnvelopeMinY, WkbfieldsDblEnvelopeMaxX, WkbfieldsDblEnvelopeMaxY };
foreach (string envelopeField in envelopeFields)
{
Index idx = new Index(tbl, "IDX_" + envelopeField);
IndexedColumn indCol = new IndexedColumn(idx, envelopeField);
idx.IndexedColumns.Add(indCol);
idx.IndexKeyType = IndexKeyType.None;
idx.IsClustered = false;
idx.FillFactor = 50;
idx.Create();
idx.Alter();
}
}
private static void CreateCustomerTable(Database database)
{
Table customersTable = new Table(database, "Customers");
customersTable.Schema = "dbo";
Column idColumn = new Column(customersTable, "Id", DataType.Int);
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
Column nameColumn = new Column(customersTable, "Name", DataType.VarChar(200));
nameColumn.Nullable = false;
Column addressColumn = new Column(customersTable, "Address", DataType.VarChar(4000));
Column cityColumn = new Column(customersTable, "City", DataType.VarChar(50));
Column stateColumn = new Column(customersTable, "State", DataType.Char(2));
Column zipColumn = new Column(customersTable, "Zip", DataType.VarChar(9));
customersTable.Columns.Add(idColumn);
customersTable.Columns.Add(nameColumn);
customersTable.Columns.Add(addressColumn);
customersTable.Columns.Add(cityColumn);
customersTable.Columns.Add(stateColumn);
customersTable.Columns.Add(zipColumn);
Index index = new Index(customersTable, "PK_Customers");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
IndexedColumn idIndexedColumn = new IndexedColumn(index, "Id");
index.IndexedColumns.Add(idIndexedColumn);
customersTable.Indexes.Add(index);
customersTable.Create();
}
public static void BuildIndexes(SqlConnectionStringBuilder csb, string dbName, params TableInfo[] tables)
{
if (string.IsNullOrWhiteSpace(dbName))
{
throw new InvalidArgumentException("dbName is missing");
}
ServerConnection connection = new ServerConnection(csb.DataSource, csb.UserID, csb.Password);
Server server = new Server(connection);
// Reference the AdventureWorks2012 database.
Database database = server.Databases[dbName];
foreach (var tab in tables)
{
Table table = database.Tables[tab.Name];
// Define an Index object variable by providing the parent table and index name in the constructor.
Index idx = new Index(table, tab.Name + "_PrimaryKey");
foreach (string name in tab.Values)
{
// Add indexed columns to the index.
IndexedColumn icol = new IndexedColumn(idx, name, true);
idx.IndexedColumns.Add(icol);
}
// Set the index properties.
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
idx.IsClustered = false;
idx.FillFactor = 90;
// Create the index on the instance of SQL Server.
idx.Create();
// Modify the page locks property.
idx.DisallowPageLocks = true;
// Run the Alter method to make the change on the instance of SQL Server.
idx.Alter();
}
}
public void IndexedColumn_DictionaryConversion()
{
IndexedColumn c = new IndexedColumn(ColumnFactory.CreateSortedColumn <ByteBlock>(new ByteBlockColumn(ByteBlock.Zero), 0), new DefaultWordSplitter());
c.SetSize(4);
c[0] = "First Value";
c[1] = "Second Value";
c[2] = "this is a different value";
ColumnTests.AssertConsistent(c);
// Verify Dictionary conversion
Dictionary <string, List <ushort> > dictionary = c.ConvertToDictionary();
Assert.AreEqual(7, dictionary.Keys.Count);
Assert.AreEqual("0, 1, 2", String.Join(", ", dictionary["value"]));
Assert.AreEqual("0", String.Join(", ", dictionary["first"]));
Assert.AreEqual("1", String.Join(", ", dictionary["second"]));
Assert.AreEqual("2", String.Join(", ", dictionary["this"]));
Assert.AreEqual("2", String.Join(", ", dictionary["is"]));
Assert.AreEqual("2", String.Join(", ", dictionary["a"]));
Assert.AreEqual("2", String.Join(", ", dictionary["different"]));
}
///--------------------------------------------------------------------------------
/// <summary>This loads information from a SQL indexed column.</summary>
///
/// <param name="sqlIndexedColumn">The input sql indexed column.</param>
///--------------------------------------------------------------------------------
public void LoadColumn(IndexedColumn sqlIndexedColumn)
{
try
{
// load the basic index column information
SqlIndexedColumnName = sqlIndexedColumn.Name;
DbID = sqlIndexedColumn.ID;
IsIncluded = sqlIndexedColumn.IsIncluded;
IsComputed = sqlIndexedColumn.IsComputed;
Descending = sqlIndexedColumn.Descending;
Urn = sqlIndexedColumn.Urn;
State = sqlIndexedColumn.State.ToString();
// load information for each property
//foreach (Microsoft.SqlServer.Management.Smo.Property loopProperty in sqlIndexedColumn.Properties)
//{
// if (loopProperty.Expensive == false && loopProperty.IsNull == false && !String.IsNullOrEmpty(loopProperty.Value.ToString()))
// {
// SqlProperty property = new SqlProperty();
// property.SqlPropertyID = Guid.NewGuid();
// property.SqlIndexedColumn = this;
// property.LoadProperty(loopProperty);
// SqlPropertyList.Add(property);
// }
//}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception ex)
{
bool reThrow = BusinessConfiguration.HandleException(ex);
if (reThrow)
{
throw;
}
}
}
private static IUntypedColumn BuildByteBlock(ColumnDetails details, string[] typeComponents)
{
ByteBlock defaultValue;
Value.Create(details.Default).TryConvert <ByteBlock>(out defaultValue);
// Build the raw column
IColumn <ByteBlock> columnSoFar = new ByteBlockColumn(defaultValue);
// Wrap the column as requested (the last component is the type itself)
for (int i = typeComponents.Length - 2; i >= 0; --i)
{
string fullComponent = typeComponents[i];
string[] componentParts = fullComponent.Split('[', ']');
switch (componentParts[0])
{
case "sorted":
columnSoFar = CreateSortedColumn(columnSoFar, 0);
break;
case "indexed":
columnSoFar = new IndexedColumn(columnSoFar, BuildSplitter(componentParts.Length > 1 ? componentParts[1] : "default"));
break;
default:
throw new ArribaException(StringExtensions.Format("Column Type Wrapper '{0}' is not currently supported.", typeComponents[i]));
}
}
// De-type the column for generic use
var utc = new UntypedColumn <ByteBlock>(columnSoFar);
// Tell it the column name
utc.Name = details.Name;
return(utc);
}
private void createStgIndex(Index i, TableViewBase parent)
{
if (i.PartitionScheme == "")
{
throw (new System.NotSupportedException(
String.Format("The index '{0}' is not aligned to a Partition Scheme", i.Name)));
}
// todo: differentiate between Base Table as source, and View as source
// LZAV: Index stgIndex = new Index(parent, parent.Name + "_" + i.Name);
String indexName = parent.Name + "_" + i.Name; // LZAV
if (indexName.Length > 128) // LZAV
{
indexName = "IX_CL_" + parent.Name; // LZAV
}
Index stgIndex = new Index(parent, indexName); // LZAV
foreach (IndexedColumn iCol in i.IndexedColumns)
{
IndexedColumn stgICol = new IndexedColumn(stgIndex, iCol.Name, iCol.Descending);
stgICol.IsIncluded = iCol.IsIncluded;
stgIndex.IndexedColumns.Add(stgICol);
}
stgIndex.IndexType = i.IndexType;
stgIndex.IndexKeyType = i.IndexKeyType;
stgIndex.IsClustered = i.IsClustered;
stgIndex.IsUnique = i.IsUnique;
stgIndex.CompactLargeObjects = i.CompactLargeObjects;
stgIndex.IgnoreDuplicateKeys = i.IgnoreDuplicateKeys;
stgIndex.IsFullTextKey = i.IsFullTextKey;
stgIndex.PadIndex = i.PadIndex;
stgIndex.FileGroup = db.PartitionSchemes[i.PartitionScheme].FileGroups[partitionNumber - 1];
// add the partitioning column to the index if it is not already there
String partitionKeyName = i.PartitionSchemeParameters[0].Name;
if (stgIndex.IndexedColumns[partitionKeyName] == null)
{
IndexedColumn stgICol = new IndexedColumn(stgIndex, partitionKeyName);
// It is added as a Key to the Clustered index and as an Include column to a Nonclustered
stgICol.IsIncluded = !stgIndex.IsClustered;
stgIndex.IndexedColumns.Add(stgICol);
}
if (srv.VersionMajor >= 10)
{
// Define compression property to match by creating a Physical Partition object (not applicable to Colstore)
{
PhysicalPartition stgPartition = new PhysicalPartition(stgIndex, 1);
if (i.IndexType != IndexType.NonClusteredColumnStoreIndex)
{
stgPartition.DataCompression = i.PhysicalPartitions[partitionNumber - 1].DataCompression;
}
stgIndex.PhysicalPartitions.Add(stgPartition);
}
// Handle Filtered Index
if (i.HasFilter)
{
stgIndex.FilterDefinition = i.FilterDefinition;
}
}
scriptChunks.Add(stgIndex.Script());
if (executeCommands)
{
stgIndex.Create();
}
}
public static void AddColumnToIndex(Table table, Column column, Index ind)
{
string oldName = ind.Name;
var idx = new Index(table, oldName)
{
BoundingBoxYMax = ind.BoundingBoxYMax,
BoundingBoxYMin = ind.BoundingBoxYMin,
BoundingBoxXMax = ind.BoundingBoxXMax,
BoundingBoxXMin = ind.BoundingBoxXMin,
CompactLargeObjects = ind.CompactLargeObjects,
DisallowRowLocks = ind.DisallowRowLocks,
FileGroup = ind.FileGroup,
FileStreamFileGroup = ind.FileStreamFileGroup,
FileStreamPartitionScheme = ind.FileStreamPartitionScheme,
FillFactor = ind.FillFactor,
FilterDefinition = ind.FilterDefinition,
IgnoreDuplicateKeys = ind.IgnoreDuplicateKeys,
IndexKeyType = ind.IndexKeyType,
IsClustered = ind.IsClustered,
IsFullTextKey = ind.IsFullTextKey,
IsUnique = ind.IsUnique,
Level1Grid = ind.Level1Grid,
Level2Grid = ind.Level2Grid,
Level3Grid = ind.Level3Grid,
Level4Grid = ind.Level4Grid,
MaximumDegreeOfParallelism = ind.MaximumDegreeOfParallelism,
NoAutomaticRecomputation = ind.NoAutomaticRecomputation,
OnlineIndexOperation = ind.OnlineIndexOperation,
PadIndex = ind.PadIndex,
ParentXmlIndex = ind.ParentXmlIndex,
PartitionScheme = ind.PartitionScheme,
SecondaryXmlIndexType = ind.SecondaryXmlIndexType,
SortInTempdb = ind.SortInTempdb,
SpatialIndexType = ind.SpatialIndexType,
};
foreach (IndexedColumn iColumn in ind.IndexedColumns)
{
var newIdxColumn = new IndexedColumn(idx, iColumn.Name)
{
Descending = iColumn.Descending,
IsIncluded = iColumn.IsIncluded,
};
idx.IndexedColumns.Add(newIdxColumn);
}
idx.IndexedColumns.Add(new IndexedColumn(idx, column.Name));
bool oldDisallowPageLocks = ind.DisallowPageLocks;
ind.Drop();
idx.Create();
idx.DisallowPageLocks = oldDisallowPageLocks;
idx.Alter();
}
private void CreatePrimaryKey()
{
Index index = new Index(this.table, "PK_" + this.table.Name);
foreach (var column in this.keyColumns)
{
IndexedColumn indexedColumn = new IndexedColumn(index, column.Name);
index.IndexedColumns.Add(indexedColumn);
}
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
this.table.Indexes.Add(index);
}
public void SynchronizeTable(SchemaTable schemaTable)
{
var smoTable = GetSmoTable(schemaTable);
if (smoTable == null)
{
smoTable = new Table(GetSmoDatabase(), schemaTable.Name);
foreach (var column in schemaTable.Columns)
{
var newcol = column.GetSmoColumn(smoTable);
smoTable.Columns.Add(newcol);
}
smoTable.Create();
// primary key
if (!schemaTable.IsLogTable && !schemaTable.IsProvodkasTable)
{
var pk = new Index(smoTable, "PK_" + schemaTable.Name);
var icol = new IndexedColumn(pk, schemaTable.GetPrimaryKeyColumn().Name, false);
pk.IndexedColumns.Add(icol);
pk.IndexKeyType = IndexKeyType.DriPrimaryKey;
pk.IsClustered = true;
pk.FillFactor = 50;
pk.Create();
}
}
else
{
foreach (var schemaColumn in schemaTable.Columns)
{
if (smoTable.Columns.Contains(schemaColumn.Name))
{
if ((schemaColumn.Table.GetPrimaryKeyColumn()==null || schemaColumn.Name != schemaColumn.Table.GetPrimaryKeyColumn().Name) &&
!(schemaColumn.DataType is TimestampDataType))
{
var smoColumn = smoTable.Columns[schemaColumn.Name];
var newDataType = schemaColumn.DataType.GetSmoDataType();
if (!smoColumn.DataType.Equals(newDataType))
smoColumn.DataType = newDataType;
if (smoColumn.Nullable != !schemaColumn.IsNotNullable)
smoColumn.Nullable = !schemaColumn.IsNotNullable;
}
}
else
{
var newcol = schemaColumn.GetSmoColumn(smoTable);
smoTable.Columns.Add(newcol);
}
}
smoTable.Alter();
}
}
private void createStgIndex(Index i, TableViewBase parent)
{
if (i.PartitionScheme == "")
throw (new System.NotSupportedException(
String.Format("The index '{0}' is not aligned to a Partition Scheme", i.Name)));
// todo: differentiate between Base Table as source, and View as source
// LZAV: Index stgIndex = new Index(parent, parent.Name + "_" + i.Name);
String indexName = parent.Name + "_" + i.Name; // LZAV
if (indexName.Length > 128) // LZAV
indexName = "IX_CL_" + parent.Name; // LZAV
Index stgIndex = new Index(parent, indexName); // LZAV
foreach (IndexedColumn iCol in i.IndexedColumns)
{
IndexedColumn stgICol = new IndexedColumn(stgIndex, iCol.Name, iCol.Descending);
stgICol.IsIncluded = iCol.IsIncluded;
stgIndex.IndexedColumns.Add(stgICol);
}
stgIndex.IndexType = i.IndexType;
stgIndex.IndexKeyType = i.IndexKeyType;
stgIndex.IsClustered = i.IsClustered;
stgIndex.IsUnique = i.IsUnique;
stgIndex.CompactLargeObjects = i.CompactLargeObjects;
stgIndex.IgnoreDuplicateKeys = i.IgnoreDuplicateKeys;
stgIndex.IsFullTextKey = i.IsFullTextKey;
stgIndex.PadIndex = i.PadIndex;
stgIndex.FileGroup = db.PartitionSchemes[i.PartitionScheme].FileGroups[partitionNumber - 1];
// add the partitioning column to the index if it is not already there
String partitionKeyName = i.PartitionSchemeParameters[0].Name;
if (stgIndex.IndexedColumns[partitionKeyName] == null)
{
IndexedColumn stgICol = new IndexedColumn(stgIndex, partitionKeyName);
// It is added as a Key to the Clustered index and as an Include column to a Nonclustered
stgICol.IsIncluded = !stgIndex.IsClustered;
stgIndex.IndexedColumns.Add(stgICol);
}
if (srv.VersionMajor >= 10)
{
// Define compression property to match by creating a Physical Partition object (not applicable to Colstore)
{
PhysicalPartition stgPartition = new PhysicalPartition(stgIndex, 1);
if (i.IndexType != IndexType.NonClusteredColumnStoreIndex)
{
stgPartition.DataCompression = i.PhysicalPartitions[partitionNumber - 1].DataCompression;
}
stgIndex.PhysicalPartitions.Add(stgPartition);
}
// Handle Filtered Index
if (i.HasFilter)
{
stgIndex.FilterDefinition = i.FilterDefinition;
}
}
scriptChunks.Add(stgIndex.Script());
if (executeCommands) stgIndex.Create();
}
public int CreateTable(Type ty, CreateFlags createFlags = CreateFlags.None)
{
if (_tables == null)
{
_tables = new Dictionary <string, TableMapping>();
}
if (!_tables.TryGetValue(ty.FullName, out TableMapping value))
{
value = GetMapping(ty, createFlags);
_tables.Add(ty.FullName, value);
}
string str = "create table if not exists \"" + value.TableName + "\"(\n";
IEnumerable <string> source = from p in value.Columns
select Orm.SqlDecl(p, StoreDateTimeAsTicks);
string str2 = string.Join(",\n", source.ToArray());
str += str2;
str += ")";
int num = Execute(str);
if (num == 0)
{
MigrateTable(value);
}
Dictionary <string, IndexInfo> dictionary = new Dictionary <string, IndexInfo>();
TableMapping.Column[] columns = value.Columns;
foreach (TableMapping.Column column in columns)
{
foreach (IndexedAttribute index in column.Indices)
{
string text = index.Name ?? (value.TableName + "_" + column.Name);
if (!dictionary.TryGetValue(text, out IndexInfo value2))
{
value2 = default(IndexInfo);
IndexInfo indexInfo = value2;
indexInfo.IndexName = text;
indexInfo.TableName = value.TableName;
indexInfo.Unique = index.Unique;
indexInfo.Columns = new List <IndexedColumn>();
value2 = indexInfo;
dictionary.Add(text, value2);
}
if (index.Unique != value2.Unique)
{
throw new Exception("All the columns in an index must have the same value for their Unique property");
}
value2.Columns.Add(new IndexedColumn
{
Order = index.Order,
ColumnName = column.Name
});
}
}
foreach (string key in dictionary.Keys)
{
IndexInfo indexInfo2 = dictionary[key];
string[] array = new string[indexInfo2.Columns.Count];
if (indexInfo2.Columns.Count == 1)
{
string[] array2 = array;
IndexedColumn indexedColumn = indexInfo2.Columns[0];
array2[0] = indexedColumn.ColumnName;
}
else
{
indexInfo2.Columns.Sort((IndexedColumn lhs, IndexedColumn rhs) => lhs.Order - rhs.Order);
int j = 0;
for (int count = indexInfo2.Columns.Count; j < count; j++)
{
string[] array3 = array;
int num2 = j;
IndexedColumn indexedColumn2 = indexInfo2.Columns[j];
array3[num2] = indexedColumn2.ColumnName;
}
}
num += CreateIndex(key, indexInfo2.TableName, array, indexInfo2.Unique);
}
return(num);
}
/// <summary>
/// Создаём таблицу из схемы модуля
/// </summary>
/// <param name="module">Метаданные модуля</param>
public void CreateTableFromModuleSchema(ModuleMetadata module)
{
if (!_database.Tables.Contains(module.ModuleName))
{
try
{
// создаём таблицу
Table targetTable = new Table(_database, module.ModuleName);
//
// добавляем базовые столбцы в таблицу
//
#region Внешниый ключ
Column plowMachineIdColumn = new Column(targetTable, "PlowMachineId");
plowMachineIdColumn.DataType = DataType.UniqueIdentifier;
plowMachineIdColumn.RowGuidCol = true;
plowMachineIdColumn.Nullable = false;
ForeignKey fk = new ForeignKey(targetTable, "FK_" + module.ModuleName + "_PlowMachine");
ForeignKeyColumn fk_column = new ForeignKeyColumn(fk, "PlowMachineId");
fk_column.ReferencedColumn = "PlowMachineId";
fk.ReferencedTable = "PlowMachines";
fk.Columns.Add(fk_column);
targetTable.ForeignKeys.Add(fk);
targetTable.Columns.Add(plowMachineIdColumn);
#endregion
//
// добавляем столбцы в таблицу
//
foreach (FieldMetadata f in module.MetadataFields)
{
Column column = CreateColumn(targetTable, f);
targetTable.Columns.Add(column);
}
targetTable.Create();
#region Первичный ключ
Index idx = new Index(targetTable, "PK_" + module.ModuleName);
IndexedColumn idxc = new IndexedColumn(idx, plowMachineIdColumn.Name);
idx.IndexedColumns.Add(idxc);
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
idx.IsClustered = true;
idx.IsUnique = true;
idx.Create();
#endregion
}
catch (Microsoft.SqlServer.Management.Smo.InvalidSmoOperationException)
{
throw;
}
}
else
{
throw new InvalidOperationException("Таблица с именем '" + module.ModuleName + "' уже существует в БД.");
}
}
private static void ApplyIndexesForeignKeysChecks(Database destinationDatabase, NamedSmoObject namedSmoObject, string schema)
{
Table destinationTable = destinationDatabase.Tables[namedSmoObject.Name, schema];
#region Indexes
foreach (Index sourceIndex in (namedSmoObject as Table).Indexes)
{
string name = sourceIndex.Name;
Index index = new Index(destinationTable, name);
index.IndexKeyType = sourceIndex.IndexKeyType;
index.IsClustered = sourceIndex.IsClustered;
index.IsUnique = sourceIndex.IsUnique;
index.CompactLargeObjects = sourceIndex.CompactLargeObjects;
index.IgnoreDuplicateKeys = sourceIndex.IgnoreDuplicateKeys;
index.IsFullTextKey = sourceIndex.IsFullTextKey;
index.PadIndex = sourceIndex.PadIndex;
index.FileGroup = sourceIndex.FileGroup;
foreach (IndexedColumn sourceIndexedColumn in sourceIndex.IndexedColumns)
{
IndexedColumn column = new IndexedColumn(index, sourceIndexedColumn.Name, sourceIndexedColumn.Descending);
column.IsIncluded = sourceIndexedColumn.IsIncluded;
index.IndexedColumns.Add(column);
}
index.FileGroup = destinationTable.FileGroup ?? index.FileGroup;
index.Create();
}
#endregion
#region ForeignKeys
foreach (ForeignKey sourceFK in (namedSmoObject as Table).ForeignKeys)
{
string name = sourceFK.Name;
ForeignKey foreignkey = new ForeignKey(destinationTable, name);
foreignkey.DeleteAction = sourceFK.DeleteAction;
foreignkey.IsChecked = sourceFK.IsChecked;
foreignkey.IsEnabled = sourceFK.IsEnabled;
foreignkey.ReferencedTable = sourceFK.ReferencedTable;
foreignkey.ReferencedTableSchema = sourceFK.ReferencedTableSchema;
foreignkey.UpdateAction = sourceFK.UpdateAction;
foreach (ForeignKeyColumn sourceFKColumn in sourceFK.Columns)
{
string referencedColumn = sourceFKColumn.ReferencedColumn;
ForeignKeyColumn column = new ForeignKeyColumn(foreignkey, sourceFKColumn.Name, referencedColumn);
foreignkey.Columns.Add(column);
}
foreignkey.Create();
}
#endregion
#region Checks
foreach (Check chkConstr in (namedSmoObject as Table).Checks)
{
Check check = new Check(destinationTable, chkConstr.Name);
check.IsChecked = chkConstr.IsChecked;
check.IsEnabled = chkConstr.IsEnabled;
check.Text = chkConstr.Text;
check.Create();
}
#endregion
}
public string CreateTableBySMO(Models.Database database, string destination_tablename)
{
try
{
string conString = string.Empty;
string tablename = database.table.Substring(database.table.IndexOf('.') + 1);
if (database.authentication == "WA")
{
conString = "Data Source=" + database.servername + ";Initial Catalog =" + database.database + ";Integrated Security=True";
}
else if (database.authentication == "SQL")
{
conString = "Data Source=" + database.servername + ";Initial Catalog =" + database.database + ";Integrated Security=False; User ID = " + database.username + "; Password = "******"";
}
SqlConnection connection = new SqlConnection(conString);
var conn = new ServerConnection(connection);
//Connect to the local, default instance of SQL Server.
Server srv;
srv = new Server(conn);
//Reference the AdventureWorks2012 database.
Database testDB = srv.Databases[database.database];
Table myTable = testDB.Tables[tablename];
myTable.Refresh();
Table newTable = new Table(testDB, destination_tablename);
foreach (Column col in myTable.Columns)
{
Column newColumn = new Column(newTable, col.Name);
newColumn.DataType = col.DataType;
newColumn.Default = col.Default;
newColumn.Identity = col.Identity;
newColumn.IdentityIncrement = col.IdentityIncrement;
newColumn.IdentitySeed = col.IdentitySeed;
newColumn.Nullable = col.Nullable;
newTable.Columns.Add(newColumn);
}
newTable.Create();
#region Creating Foreign Keys
if ((myTable as Table).ForeignKeys.Count != 0)
{
foreach (ForeignKey sourcefk in (myTable as Table).ForeignKeys)
{
try
{
string name = Guid.NewGuid().ToString();
ForeignKey foreignkey = new ForeignKey(newTable, name);
foreignkey.DeleteAction = sourcefk.DeleteAction;
foreignkey.IsChecked = sourcefk.IsChecked;
foreignkey.IsEnabled = sourcefk.IsEnabled;
foreignkey.ReferencedTable = sourcefk.ReferencedTable;
foreignkey.ReferencedTableSchema = sourcefk.ReferencedTableSchema;
foreignkey.UpdateAction = sourcefk.UpdateAction;
foreach (ForeignKeyColumn scol in sourcefk.Columns)
{
string refcol = scol.ReferencedColumn;
ForeignKeyColumn column =
new ForeignKeyColumn(foreignkey, scol.Name, refcol);
foreignkey.Columns.Add(column);
}
foreignkey.Create();
}
catch (Exception ex)
{
throw;
}
}
}
#endregion
#region Creating Indexes
if ((myTable as Table).Indexes.Count != 0)
{
foreach (Index srcind in (myTable as Table).Indexes)
{
try
{
string name = Guid.NewGuid().ToString();
Index index = new Index(newTable, name);
index.IndexKeyType = srcind.IndexKeyType;
index.IsClustered = srcind.IsClustered;
index.IsUnique = srcind.IsUnique;
index.CompactLargeObjects = srcind.CompactLargeObjects;
index.IgnoreDuplicateKeys = srcind.IgnoreDuplicateKeys;
index.IsFullTextKey = srcind.IsFullTextKey;
index.PadIndex = srcind.PadIndex;
index.FileGroup = srcind.FileGroup;
foreach (IndexedColumn srccol in srcind.IndexedColumns)
{
IndexedColumn column =
new IndexedColumn(index, srccol.Name, srccol.Descending);
column.IsIncluded = srccol.IsIncluded;
index.IndexedColumns.Add(column);
}
index.FileGroup = newTable.FileGroup ?? index.FileGroup;
index.Create();
}
catch (Exception exc)
{
throw;
}
}
}
#endregion
return(newTable.Schema + "." + newTable.Name);
}
catch (Exception)
{
return(string.Empty);
}
}
private void DwGenerateTable(DsDwTableMap_M aDsDwTableMap)
{
if (aDsDwTableMap != null && aDsDwTableMap.DsDwColumnMapList != null)
{
Table aSMOTable = new Table(DwDb, aDsDwTableMap.DwTableName, aDsDwTableMap.DwSchemaName);
//Add columns to the table
foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList)
{
if (aDsDwColumnMap != null && aDsDwColumnMap.Include)
{
Column aNewColumn;
switch (aDsDwColumnMap.Transformation)
{
case DsDwColumnTransformation.SurrogateKey:
aNewColumn = new Column(aSMOTable, aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.DataType);
aNewColumn.Nullable = false;
aNewColumn.Identity = true;
aNewColumn.IdentityIncrement = 1;
aNewColumn.IdentitySeed = 1;
aSMOTable.Columns.Add(aNewColumn);
break;
case DsDwColumnTransformation.BusinesKey:
aNewColumn = new Column(aSMOTable, aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.DataType);
aNewColumn.Nullable = false;
aNewColumn.Collation = aDsDwColumnMap.DwColumn.Collation;
aSMOTable.Columns.Add(aNewColumn);
break;
default:
aNewColumn = new Column(aSMOTable, aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.DataType);
aNewColumn.Collation = aDsDwColumnMap.DwColumn.Collation;
aNewColumn.Nullable = true;
aSMOTable.Columns.Add(aNewColumn);
break;
}
}
}
aSMOTable.Create();
//Add indexes and primary key the table
foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList)
{
if (aDsDwColumnMap != null && aDsDwColumnMap.Include)
{
switch (aDsDwColumnMap.Transformation)
{
case DsDwColumnTransformation.SurrogateKey:
Index primaryKey = new Index(aSMOTable, "PK_" + aDsDwTableMap.DwTableName);
IndexedColumn indexedColumn = new IndexedColumn(primaryKey, aDsDwColumnMap.DwColumn.Name);
primaryKey.IndexedColumns.Add(indexedColumn);
primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKey.Create();
break;
case DsDwColumnTransformation.BusinesKey:
// Define an Index object variable by providing the parent table and index name in the constructor.
Index idx;
idx = new Index(aSMOTable, "IX_" + aDsDwTableMap.DwTableName + "_" + aDsDwColumnMap.DwColumn.Name);
// Add indexed columns to the index.
IndexedColumn icol1;
icol1 = new IndexedColumn(idx, aDsDwColumnMap.DwColumn.Name, true);
idx.IndexedColumns.Add(icol1);
// Set the index properties.
idx.IndexKeyType = IndexKeyType.None;
idx.IsClustered = false;
idx.FillFactor = 70;
// Create the index on the instance of SQL Server.
idx.Create();
break;
}
}
}
}
}
public void IntializeDatabase()
{
if (Logger != null)
{
Logger.Log(LogLevel.Information, $"Initializing Database {DatabaseName}");
}
var database = (SqlServerDatabase)GetDatabase();
if (!database.Schemas.Any(s => s.Name == _VersionSchema))
{
var schema = new Schema(database.SmoDatabase, "version");
if (Logger != null)
{
Logger.Log(LogLevel.Information, $"Creating version Schema in {DatabaseName}");
}
schema.Create();
if (Logger != null)
{
Logger.Log(LogLevel.Debug, $"Created version Schema in {DatabaseName}");
}
}
if (!database.Tables.Any(t => t.Name == _VersionTable && ((SqlServerTable)t).Schema == _VersionSchema))
{
var table = new Table(database.SmoDatabase, "Log", "version");
if (Logger != null)
{
Logger.Log(LogLevel.Information, $"Creating version.Log Table in {DatabaseName}");
}
var scriptColumnName = "Script";
var fileNameColumnName = "FileName";
var deployDateColumnName = "DeployDate";
var scriptColumn = new Column(table, scriptColumnName, DataType.NVarChar(2000));
scriptColumn.Nullable = false;
table.Columns.Add(scriptColumn);
var fileNameColumn = new Column(table, fileNameColumnName, DataType.NVarCharMax);
fileNameColumn.Nullable = false;
table.Columns.Add(fileNameColumn);
var deployDateColumn = new Column(table, deployDateColumnName, DataType.DateTimeOffset(7));
deployDateColumn.Nullable = false;
table.Columns.Add(deployDateColumn);
table.Create();
if (Logger != null)
{
Logger.Log(LogLevel.Debug, $"Created version.Log Table in {DatabaseName}");
}
// Define Index object on the table by supplying the Table1 as the parent table and the primary key name in the constructor.
if (Logger != null)
{
Logger.Log(LogLevel.Information, $"Creating {table.Schema}_{table.Name}_PK Primary Key in {DatabaseName}");
}
Index pk = new Index(table, $"{table.Schema}_{table.Name}_PK");
pk.IndexKeyType = IndexKeyType.DriPrimaryKey;
// Add Col1 as the Index Column
IndexedColumn idxCol1 = new IndexedColumn(pk, "Script");
pk.IndexedColumns.Add(idxCol1);
// Create the Primary Key
pk.Create();
if (Logger != null)
{
Logger.Log(LogLevel.Debug, $"Created {table.Schema}_{table.Name}_PK Primary Key in {DatabaseName}");
}
}
if (Logger != null)
{
Logger.Log(LogLevel.Information, $"Database {DatabaseName} has been initialized.");
}
}
