public override string DropIndex(DatabaseTable databaseTable, DatabaseIndex index)
{
return string.Format(CultureInfo.InvariantCulture,
"DROP INDEX {0}{1};",
SchemaPrefix(index.SchemaOwner),
Escape(index.Name));
}
public override string DropIndex(DatabaseTable databaseTable, DatabaseIndex index)
{
//no "ON table" syntax
return string.Format(CultureInfo.InvariantCulture,
"DROP INDEX {0}.{1}",
databaseTable.Name,
Escape(index.Name));
}
private static bool ColumnsEqual(DatabaseIndex first, DatabaseIndex second)
{
if (first.Columns == null && second.Columns == null) return true; //same, both null
if (first.Columns == null || second.Columns == null) return false; //one is null, they are different
//the two sequences have the same names
var columnNames1 = first.Columns.OrderBy(c => c.Ordinal).Select(c => c.Name);
var columnNames2 = second.Columns.OrderBy(c => c.Ordinal).Select(c => c.Name);
return columnNames1.SequenceEqual(columnNames2);
}
private static void ConvertIndexes(DataTable dt, ICollection<DatabaseIndex> indexes)
{
if (dt == null) return;
//Npgsql
if (dt.Columns.Count == 0) return;
var indexKeyMap = new IndexKeyMap(dt);
foreach (DataRowView row in dt.DefaultView)
{
string name = row[indexKeyMap.Key].ToString();
if (string.IsNullOrEmpty(name)) continue; //all indexes should have a name
string schema = !String.IsNullOrEmpty(indexKeyMap.SchemaKey) ? row[indexKeyMap.SchemaKey].ToString() : String.Empty;
var tableName = row[indexKeyMap.TableKey].ToString();
var c = indexes.FirstOrDefault(f => f.Name == name && f.SchemaOwner == schema && f.TableName.Equals(tableName, StringComparison.OrdinalIgnoreCase));
if (c == null)
{
c = new DatabaseIndex();
c.Name = name;
c.SchemaOwner = schema;
c.TableName = tableName;
if (indexKeyMap.Typekey != null)
c.IndexType = row[indexKeyMap.Typekey].ToString();
if (FindBoolean(row, indexKeyMap.UniqueKey, "UNIQUE"))
{
c.IsUnique = true;
c.IndexType = "UNIQUE";
}
if (FindBoolean(row, indexKeyMap.PrimaryKey, String.Empty))
c.IndexType = "PRIMARY"; //primary keys should be unique too
indexes.Add(c);
}
if (string.IsNullOrEmpty(indexKeyMap.ColumnKey)) continue;
string colName = row[indexKeyMap.ColumnKey].ToString();
if (string.IsNullOrEmpty(colName)) continue;
var column = new DatabaseColumn();
column.Name = colName;
if (!string.IsNullOrEmpty(indexKeyMap.OrdinalKey))
{
int ordinal = Convert.ToInt32(row[indexKeyMap.OrdinalKey], CultureInfo.CurrentCulture);
column.Ordinal = ordinal;
}
if (ContainsColumn(c.Columns, colName)) continue;
c.Columns.Add(column);
if (c.Columns.Count > 1 && column.Ordinal != 0)
{
//the order of the datatable may be wrong
c.Columns.Sort((x, y) => x.Ordinal.CompareTo(y.Ordinal));
}
}
}
/// <summary>
/// Adds the index.
/// </summary>
/// <param name="databaseTable">The database table.</param>
/// <param name="indexName">Name of the index.</param>
/// <param name="columns">The columns.</param>
/// <returns></returns>
public static DatabaseTable AddIndex(this DatabaseTable databaseTable, string indexName, IEnumerable<DatabaseColumn> columns)
{
if (databaseTable == null) throw new ArgumentNullException("databaseTable", "databaseTable must not be null");
if (!columns.Any()) throw new ArgumentException("columns is empty", "columns");
var index = new DatabaseIndex
{
Name = indexName,
TableName = databaseTable.Name,
SchemaOwner = databaseTable.SchemaOwner,
IndexType = "NONCLUSTERED"
};
index.Columns.AddRange(columns);
databaseTable.AddIndex(index);
return databaseTable;
}
/// <summary>
/// Adds the index with the specified name. If the index with the same name exists, add the column to the index.
/// </summary>
/// <param name="databaseColumn">The database column.</param>
/// <param name="indexName">Name of the index.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">databaseColumn;databaseColumn must not be null</exception>
public static DatabaseColumn AddIndex(this DatabaseColumn databaseColumn, string indexName)
{
if (databaseColumn == null) throw new ArgumentNullException("databaseColumn", "databaseColumn must not be null");
var databaseTable = databaseColumn.Table;
if (databaseTable == null) throw new ArgumentException("databaseColumn has no table");
var index = databaseTable.Indexes.Find(x => Equals(x.Name, indexName));
if (index == null)
{
index = new DatabaseIndex
{
Name = indexName,
TableName = databaseTable.Name,
SchemaOwner = databaseTable.SchemaOwner,
IndexType = "NONCLUSTERED"
};
databaseTable.AddIndex(index);
}
index.Columns.Add(databaseColumn);
databaseColumn.IsIndexed = true;
return databaseColumn;
}
/// <summary>
/// Adds an index.
/// </summary>
/// <param name="index">The index.</param>
public void AddIndex(DatabaseIndex index)
{
Indexes.Add(index);
foreach (DatabaseColumn column in index.Columns)
{
string name = column.Name;
foreach (DatabaseColumn col in Columns)
{
if (col.Name.Equals(name, StringComparison.OrdinalIgnoreCase))
{
col.IsIndexed = true;
break;
}
}
}
}
private bool IndexColumnsNotInTable(DatabaseIndex index)
{
foreach (var column in index.Columns)
{
if (!Table.Columns.Any(c => c.Name == column.Name))
return true;
}
return false;
}
public string AddIndex(DatabaseTable databaseTable, DatabaseIndex index)
{
if (index.Columns.Count == 0)
{
//IndexColumns errors
return "-- add index " + index.Name + " (unknown columns)";
}
//we could plug in "CLUSTERED" or "PRIMARY XML" from index.IndexType here
var indexType = index.IsUnique ? "UNIQUE " : string.Empty;
return string.Format(CultureInfo.InvariantCulture,
"CREATE {0}INDEX {1} ON {2}({3})",
indexType, //must have trailing space
Escape(index.Name),
TableName(databaseTable),
GetColumnList(index.Columns.Select(i => i.Name))) + LineEnding();
}
public string AddIndex(DatabaseTable databaseTable, DatabaseIndex index)
{
return _migration.AddIndex(databaseTable, index);
}
public override string DropIndex(DatabaseTable databaseTable, DatabaseIndex index)
{
return string.Format(CultureInfo.InvariantCulture,
"DROP INDEX IF EXISTS {0} CASCADE;",
Escape(index.Name));
}
public void BuildDropIndex(DatabaseTable databaseTable, DatabaseIndex index)
{
try
{
var txt = _migrationGenerator.DropIndex(databaseTable, index);
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
public static DatabaseIndex CreateUniqueIndex(DatabaseColumn column, string name)
{
//a unique index isn't exactly the same as a unique constraint (except in MySql)
var index = new DatabaseIndex
{
Name = "UI_" + name,
IsUnique = true
};
index.Columns.Add(column);
return index;
}
public override string DropIndex(DatabaseTable databaseTable, DatabaseIndex index)
{
//no schema on index name, only on table
return string.Format(CultureInfo.InvariantCulture,
"DROP INDEX {0} ON {1};",
Escape(index.Name),
TableName(databaseTable));
}