public override string BuildCreateIndexCommand(IndexSchema schema)
{
var sb = new StringBuilder();
sb.AppendFormat(
@"CREATE {0}INDEX {1} ON {2}",
(schema.Unique ? "UNIQUE " : string.Empty),
Api.CommandBuilder.QuoteIdentifier(schema.IndexName),
Api.CommandBuilder.QuoteIdentifier(schema.TableName)
);
bool firstTime = true;
sb.Append("(");
foreach(var column in schema.Columns) {
sb.AppendLine();
sb.AppendFormat(
@" {0}{1}",
(firstTime ? " " : ","),
Api.CommandBuilder.QuoteIdentifier(column.ColumnName)
);
if(firstTime) { firstTime = false; }
}
sb.AppendLine();
sb.Append(")");
return sb.ToString();
}
private void CompareIndexes(TableIndex tableIndex, IndexSchema indexSchema)
{
Assert.AreEqual(tableIndex.Name, indexSchema.Name);
Assert.AreEqual(tableIndex.IsPrimaryKey, indexSchema.IsPrimaryKey);
Assert.AreEqual(tableIndex.IsUnique, indexSchema.IsUnique);
Assert.AreEqual(false, indexSchema.IsClustered);
Assert.AreEqual(tableIndex.Columns.Length, indexSchema.MemberColumns.Count);
for (int idx = 0; idx < tableIndex.Columns.Length; idx++) {
Assert.AreEqual(tableIndex.Columns[idx],
indexSchema.MemberColumns[idx].Name);
}
}
public string IXColumnName(IndexSchema index)
{
string Name = String.Empty;
for(int x=0;x < index.MemberColumns.Count;x++)
{
Name += GetPropertyName(index.MemberColumns[x].Name);
}
return Name;
}
protected override string MakeDdlIndexDrop(IndexSchema index, TableSchema table)
{
return string.Format(@"DROP INDEX {0}",
MakeDdlElementName(index.Name));
}
protected override bool CheckIndexTypeForRecreate(IndexSchema eIndex)
{
return false;
}
//http://msdn2.microsoft.com/en-us/library/aa225939(SQL.80).aspx
public override void DropIndex(IndexSchema index)
{
ExecuteNonQuery(string.Concat("DROP INDEX '", index.TableName, ".", index.Name, "'"));
}
public IndexSchema[] GetTableIndexes(string connectionString, TableSchema table)
{
// In our implementation, return an "index" for every uniquenessConstraint
if (_cachedConnectionString != connectionString)
{
_cachedConnectionString = connectionString;
_doc.Load(connectionString);
}
XmlNodeList indexes = _doc.SelectNodes("dcl:schema/dcl:table[@name='" + table.Name + "']/dcl:uniquenessConstraint", _manager);
int indexCount = indexes.Count;
IndexSchema[] ret = new IndexSchema[indexCount];
for (int i = 0; i < indexCount; ++i)
{
string indexName = "";
bool isPrimary = false;
string[] sourceColumns;
XmlNode index = indexes[i];
XmlAttribute indexNameAttribute = index.Attributes["name"];
if (indexNameAttribute != null)
{
indexName = indexNameAttribute.Value;
}
XmlAttribute isPrimaryAttribute = index.Attributes["isPrimary"];
if (isPrimaryAttribute != null)
{
isPrimary = bool.Parse(isPrimaryAttribute.Value);
}
XmlNodeList columnRefNodeList = index.SelectNodes("dcl:columnRef", _manager);
int columnRefNodeListCount = columnRefNodeList.Count;
sourceColumns = new string[columnRefNodeListCount];
for (int j = 0; j < columnRefNodeListCount; ++j)
{
XmlNode columnRefNode = columnRefNodeList[j];
XmlAttribute sourceNameAttribute = columnRefNode.Attributes["name"];
if (sourceNameAttribute != null)
{
sourceColumns[j] = sourceNameAttribute.Value;
}
}
ret[i] = new IndexSchema(table, indexName, isPrimary, true, true, sourceColumns);
}
return ret;
}
protected virtual string MakeDdlIndexDrop(IndexSchema index, TableSchema table)
{
return string.Format(@"DROP INDEX {0} ON {1}",
MakeDdlElementName(index.Name), MakeDdlElementName(table.Name));
}
protected override TableSchema CreateTableSchema(IDbConnection conn, string tableName, string tschma)
{
TableSchema res = new TableSchema();
res.TableName = tableName;
res.TableSchemaName = tschma;
res.DataAction = TablesToLoad.Find((TableToLoad t) => t.SqlServerFullName.ToLower().Equals((tschma + "." + tableName).ToLower())).SQLiteDataAction;
res.Columns = new List <ColumnSchema>();
SqlCommand cmd = new SqlCommand(@"SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE, " +
@" (columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')) AS [IDENT], " +
@"CHARACTER_MAXIMUM_LENGTH AS CSIZE " +
"FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" + tschma + "' AND TABLE_NAME = '" + tableName + "' ORDER BY " +
"ORDINAL_POSITION ASC", (SqlConnection)conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
object tmp = reader["COLUMN_NAME"];
if (tmp is DBNull)
{
continue;
}
string colName = (string)reader["COLUMN_NAME"];
tmp = reader["COLUMN_DEFAULT"];
string colDefault;
if (tmp is DBNull)
{
colDefault = string.Empty;
}
else
{
colDefault = (string)tmp;
}
tmp = reader["IS_NULLABLE"];
bool isNullable = ((string)tmp == "YES");
string dataType = (string)reader["DATA_TYPE"];
bool isIdentity = false;
if (reader["IDENT"] != DBNull.Value)
{
isIdentity = ((int)reader["IDENT"]) == 1 ? true : false;
}
int length = reader["CSIZE"] != DBNull.Value ? Convert.ToInt32(reader["CSIZE"]) : 0;
ValidateDataType(dataType);
// Note that not all data type names need to be converted because
// SQLite establishes type affinity by searching certain strings
// in the type name. For example - everything containing the string
// 'int' in its type name will be assigned an INTEGER affinity
if (dataType == "timestamp")
{
dataType = "blob";
}
else if (dataType == "datetime" || dataType == "smalldatetime")
{
dataType = "datetime";
}
else if (dataType == "decimal")
{
dataType = "numeric";
}
else if (dataType == "money" || dataType == "smallmoney")
{
dataType = "numeric";
}
else if (dataType == "binary" || dataType == "varbinary" ||
dataType == "image")
{
dataType = "blob";
}
else if (dataType == "tinyint")
{
dataType = "smallint";
}
else if (dataType == "bigint")
{
dataType = "integer";
}
else if (dataType == "sql_variant")
{
dataType = "blob";
}
else if (dataType == "xml")
{
dataType = "varchar";
}
else if (dataType == "uniqueidentifier")
{
dataType = "guid";
}
else if (dataType == "ntext")
{
dataType = "text";
}
else if (dataType == "nchar")
{
dataType = "char";
}
else if (dataType == "datetime2")
{
dataType = "datetime2";
}
else if (dataType == "date")
{
dataType = "date";
}
else if (dataType == "time")
{
dataType = "time";
}
if (dataType == "bit" || dataType == "int")
{
if (colDefault == "('False')")
{
colDefault = "(0)";
}
else if (colDefault == "('True')")
{
colDefault = "(1)";
}
}
colDefault = FixDefaultValueString(colDefault);
ColumnSchema col = new ColumnSchema();
col.ColumnName = colName;
col.ColumnType = dataType;
col.Length = length;
col.IsNullable = isNullable;
col.IsIdentity = isIdentity;
col.DefaultValue = AdjustDefaultValue(colDefault);
res.Columns.Add(col);
}
}
// Find PRIMARY KEY information
SqlCommand cmd2 = new SqlCommand(@"EXEC sp_pkeys '" + tableName + "'", (SqlConnection)conn);
using (SqlDataReader reader = cmd2.ExecuteReader())
{
res.PrimaryKey = new List <string>();
while (reader.Read())
{
string colName = (string)reader["COLUMN_NAME"];
res.PrimaryKey.Add(colName);
}
}
// Find COLLATE information for all columns in the table
SqlCommand cmd4 = new SqlCommand(
@"EXEC sp_tablecollations '" + tschma + "." + tableName + "'", (SqlConnection)conn);
using (SqlDataReader reader = cmd4.ExecuteReader())
{
while (reader.Read())
{
bool? isCaseSensitive = null;
string colName = (string)reader["name"];
if (reader["tds_collation"] != DBNull.Value)
{
byte[] mask = (byte[])reader["tds_collation"];
if ((mask[2] & 0x10) != 0)
{
isCaseSensitive = false;
}
else
{
isCaseSensitive = true;
}
}
if (isCaseSensitive.HasValue)
{
// Update the corresponding column schema.
foreach (ColumnSchema csc in res.Columns)
{
if (csc.ColumnName == colName)
{
csc.IsCaseSensitivite = isCaseSensitive;
break;
}
}
}
}
}
try
{
// Find index information
SqlCommand cmd3 = new SqlCommand(
@"exec sp_helpindex '" + tschma + "." + tableName + "'", (SqlConnection)conn);
using (SqlDataReader reader = cmd3.ExecuteReader())
{
res.Indexes = new List <IndexSchema>();
while (reader.Read())
{
string indexName = (string)reader["index_name"];
string desc = (string)reader["index_description"];
string keys = (string)reader["index_keys"];
// Don't add the index if it is actually a primary key index
if (desc.Contains("primary key"))
{
continue;
}
IndexSchema index = BuildIndexSchema(indexName, desc, keys);
res.Indexes.Add(index);
}
}
}
catch (Exception ex)
{
//Logging.Log(LogLevel.Warn, "failed to read index information for table [" + tableName + "]");
}
return(res);
}
private static TableSchema CreateTableSchema(SqlConnection conn, string tableName, string tschma)
{
TableSchema tableSchema = new TableSchema();
tableSchema.TableName = tableName;
tableSchema.TableSchemaName = tschma;
tableSchema.Columns = new List <ColumnSchema>();
using (SqlDataReader sqlDataReader = new SqlCommand("SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE, (columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')) AS [IDENT], CHARACTER_MAXIMUM_LENGTH AS CSIZE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "' ORDER BY ORDINAL_POSITION ASC", conn).ExecuteReader())
{
while (sqlDataReader.Read())
{
object obj = sqlDataReader["COLUMN_NAME"];
if (!(obj is DBNull))
{
string columnName = (string)sqlDataReader["COLUMN_NAME"];
obj = sqlDataReader["COLUMN_DEFAULT"];
string text = (!(obj is DBNull)) ? ((string)obj) : string.Empty;
obj = sqlDataReader["IS_NULLABLE"];
bool isNullable = (string)obj == "YES";
string text2 = (string)sqlDataReader["DATA_TYPE"];
bool isIdentity = false;
if (sqlDataReader["IDENT"] != DBNull.Value)
{
isIdentity = (((int)sqlDataReader["IDENT"] == 1) ? true : false);
}
int length = (sqlDataReader["CSIZE"] != DBNull.Value) ? Convert.ToInt32(sqlDataReader["CSIZE"]) : 0;
ValidateDataType(text2);
if (text2 == "timestamp")
{
text2 = "blob";
}
else
{
switch (text2)
{
case "datetime":
case "smalldatetime":
case "date":
case "datetime2":
case "time":
text2 = "datetime";
break;
case "decimal":
text2 = "numeric";
break;
case "money":
case "smallmoney":
text2 = "numeric";
break;
case "binary":
case "varbinary":
case "image":
text2 = "blob";
break;
case "tinyint":
text2 = "smallint";
break;
case "bigint":
text2 = "integer";
break;
case "sql_variant":
text2 = "blob";
break;
case "xml":
text2 = "varchar";
break;
case "uniqueidentifier":
text2 = "guid";
break;
case "ntext":
text2 = "text";
break;
case "nchar":
text2 = "char";
break;
}
}
if (text2 == "bit" || text2 == "int")
{
if (text == "('False')")
{
text = "(0)";
}
else if (text == "('True')")
{
text = "(1)";
}
}
text = FixDefaultValueString(text);
ColumnSchema columnSchema = new ColumnSchema();
columnSchema.ColumnName = columnName;
columnSchema.ColumnType = text2;
columnSchema.Length = length;
columnSchema.IsNullable = isNullable;
columnSchema.IsIdentity = isIdentity;
columnSchema.DefaultValue = AdjustDefaultValue(text);
tableSchema.Columns.Add(columnSchema);
}
}
}
using (SqlDataReader sqlDataReader2 = new SqlCommand("EXEC sp_pkeys '" + tableName + "'", conn).ExecuteReader())
{
tableSchema.PrimaryKey = new List <string>();
while (sqlDataReader2.Read())
{
string item = (string)sqlDataReader2["COLUMN_NAME"];
tableSchema.PrimaryKey.Add(item);
}
}
using (SqlDataReader sqlDataReader3 = new SqlCommand("EXEC sp_tablecollations '" + tschma + "." + tableName + "'", conn).ExecuteReader())
{
while (sqlDataReader3.Read())
{
bool? isCaseSensitivite = null;
string b = (string)sqlDataReader3["name"];
if (sqlDataReader3["tds_collation"] != DBNull.Value)
{
isCaseSensitivite = (((((byte[])sqlDataReader3["tds_collation"])[2] & 0x10) != 0) ? new bool?(false) : new bool?(true));
}
if (isCaseSensitivite.HasValue)
{
foreach (ColumnSchema column in tableSchema.Columns)
{
if (column.ColumnName == b)
{
column.IsCaseSensitivite = isCaseSensitivite;
break;
}
}
}
}
}
try
{
using (SqlDataReader sqlDataReader4 = new SqlCommand("exec sp_helpindex '" + tschma + "." + tableName + "'", conn).ExecuteReader())
{
tableSchema.Indexes = new List <IndexSchema>();
while (sqlDataReader4.Read())
{
string indexName = (string)sqlDataReader4["index_name"];
string text3 = (string)sqlDataReader4["index_description"];
string keys = (string)sqlDataReader4["index_keys"];
if (!text3.Contains("primary key"))
{
IndexSchema item2 = BuildIndexSchema(indexName, text3, keys);
tableSchema.Indexes.Add(item2);
}
}
return(tableSchema);
}
}
catch (Exception)
{
_log.Warn("failed to read index information for table [" + tableName + "]");
return(tableSchema);
}
}
private void AddColumnToIndex(IndexSchema index, DataRow row)
{
index.Columns.Add(
(string)row[IndexesQueryNames.ColumnName],
(bool)row[IndexesQueryNames.IsDesc] ? SortOrder.Descending : SortOrder.Ascending);
}
protected override bool CheckIndexTypeForRecreate(IndexSchema eIndex)
{
return(eIndex.Unique);
}
protected override string MakeDdlIndexCreate(IndexSchema eindex, TableSchema etable)
{
var stat = new StringBuilder();
if (eindex.PrimaryKey || eindex.NullAllowances != IndexNullAllowance.Allow)
{
stat.Append(@" WITH");
if (eindex.PrimaryKey)
stat.Append(@" PRIMARY");
switch (eindex.NullAllowances)
{
case IndexNullAllowance.Disallow:
stat.Append(@" DISALLOW NULL");
break;
case IndexNullAllowance.Ignore:
stat.Append(@" IGNORE NULL");
break;
}
}
return string.Format(@"CREATE {0} INDEX {1} ON {2} ({3}) {4}",
eindex.Unique ? @"UNIQUE" : string.Empty,
MakeDdlElementName(eindex.Name),
MakeDdlElementName(etable.Name),
ParseColumnListIndex(eindex.Columns).JoinStrings(@", "),
stat);
}
private static List<IndexSchema> GetIndexes(Table xTable)
{
var indexes = new List<IndexSchema>();
for (var j = 0; j < xTable.Indexes.Count; j++)
{
var xIndex = xTable.Indexes[j];
var cols = new StringBuilder();
for (var k = 0; k < xIndex.Columns.Count; k++)
{
cols.Append(xIndex.Columns[k].Name);
cols.Append(xIndex.Columns[k].SortOrder == SortOrderEnum.adSortAscending
? string.Empty
: " DESC");
cols.Append(k == xIndex.Columns.Count - 1 ? string.Empty : ", ");
}
var index = new IndexSchema
{
Name = xIndex.Name,
Columns = cols.ToString(),
Clustered = xIndex.Clustered
};
switch (xIndex.IndexNulls)
{
case AllowNullsEnum.adIndexNullsAllow:
index.NullAllowances = IndexNullAllowance.Allow;
break;
case AllowNullsEnum.adIndexNullsDisallow:
index.NullAllowances = IndexNullAllowance.Disallow;
break;
default:
index.NullAllowances = IndexNullAllowance.Ignore;
break;
}
index.PrimaryKey = xIndex.PrimaryKey;
index.Unique = xIndex.Unique;
indexes.Add(index);
}
return indexes;
}
public abstract string BuildCreateIndexCommand(IndexSchema schema);
/// <summary> /// Проверка необходимости пересоздания связанных Foreign Key /// при удалении индекса /// </summary> /// <param name="eIndex"></param> /// <returns></returns> protected abstract bool CheckIndexTypeForRecreate(IndexSchema eIndex);
protected override string MakeDdlIndexCreate(IndexSchema index, TableSchema table)
{
return
@"CREATE {0} {1} INDEX {2} ON {3} ({4})"
.FormatWith(
index.Unique ? @" UNIQUE" : string.Empty,
index.Clustered ? @" CLUSTERED" : string.Empty,
MakeDdlElementName(index.Name),
MakeDdlElementName(table.Name),
ParseColumnListIndex(index.Columns).JoinStrings(@", "));
}
//http://dev.mysql.com/doc/refman/5.1/en/drop-index.html
public override void DropIndex (IndexSchema index)
{
ExecuteNonQuery (string.Concat("DROP INDEX ", index.Name, " ON ", index.TableName, ";"));
}
//http://www.sqlite.org/lang_dropindex.html
public override void DropIndex(IndexSchema index)
{
ExecuteNonQuery("DROP INDEX IF EXISTS " + index.Name);
}
//http://www.sqlite.org/lang_dropindex.html
public override void DropIndex (IndexSchema index)
{
ExecuteNonQuery ("DROP INDEX IF EXISTS " + index.Name);
}
public override string BuildDropIndexCommand(IndexSchema schema)
{
throw new NotImplementedException();
}
//http://msdn2.microsoft.com/en-US/library/aa238878(SQL.80).aspx
public override void RenameIndex(IndexSchema index, string name)
{
Rename(index.Name, name, "INDEX");
index.Name = name;
}
private static List<IndexSchema> GetIndexes(DbConnection con, TableSchema eTable)
{
var aStore = new List<IndexSchema>();
var aHash = new List<string>();
string[] restrict4 = {null, null, null, null};
string[] restrict5 = {null, null, null, null, null};
// INDEX_TYPE = 0 - ascending, 1 - descending
restrict4[2] = eTable.Name;
restrict5[2] = eTable.Name;
var dtShema = con.GetSchema("Indexes", restrict4);
aStore.Clear();
aHash.Clear();
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var cName = cRow["INDEX_NAME"].ToString();
if (eTable.IsKeyExist(cName, ConstraintType.Unique) ||
eTable.IsKeyExist(cName, ConstraintType.KeyPrimary) ||
eTable.IsKeyExist(cName, ConstraintType.KeyForeign) ||
Convert.ToBoolean(cRow["PRIMARY_KEY"], CultureInfo.InvariantCulture))
continue;
if (aHash.Contains(cName))
continue;
var eIndex = new IndexSchema();
var columns = String.Empty;
aHash.Add(cName);
eIndex.Name = cName;
eIndex.Unique = Convert.ToBoolean(cRow["UNIQUE"], CultureInfo.InvariantCulture);
if (cRow["TYPE"] == DBNull.Value)
eIndex.Sort = SortOrder.Ascending;
else
eIndex.Sort = Convert.ToInt32(cRow["TYPE"], CultureInfo.InvariantCulture) == 0
? SortOrder.Ascending
: SortOrder.Descending;
eIndex.IsActive = true;
// !Convert.ToBoolean(cRow["IS_INACTIVE"], CultureInfo.InvariantCulture);
restrict5[3] = cName;
var dtIndexColumns = con.GetSchema("IndexColumns", restrict5);
var first = true;
for (var y = 0; y < dtIndexColumns.Rows.Count; y++)
{
if (first)
first = false;
else
columns += ", ";
columns += dtIndexColumns.Rows[y]["COLUMN_NAME"];
}
eIndex.Columns = columns;
aStore.Add(eIndex);
}
return aStore;
}
protected override string MakeDdlIndexCreate(IndexSchema index, TableSchema table)
{
var stat = new StringBuilder();
if (index.Unique)
stat.Append(@" UNIQUE");
switch (index.Sort)
{
case SortOrder.Descending:
stat.Append(@" DESC");
break;
case SortOrder.Ascending:
stat.Append(@" ASC");
break;
}
return string.Format(@"CREATE {0} INDEX {1} ON {2} ({3})",
stat,
MakeDdlElementName(index.Name),
MakeDdlElementName(table.Name),
ParseColumnListIndex(index.Columns).JoinStrings(@", "));
}
private static List<IndexSchema> GetIndexes(SqlConnection con, TableSchema eTable)
{
var indexes = new List<IndexSchema>();
var aHash = new Dictionary<string, bool>();
string[] restrict4 = {null, null, null, null};
// Indexes
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = eTable.Name;
restrict4[3] = null;
var dtShema = SqlSchemaFactory.GetSchema(con, "Indexes", restrict4);
for (var i = 0; i < dtShema.Rows.Count; i++)
{
var row = dtShema.Rows[i];
if (Convert.ToBoolean(row["IS_STATISTICS"], CultureInfo.InvariantCulture) ||
Convert.ToBoolean(row["IS_AUTOSTATISTICS"], CultureInfo.InvariantCulture) ||
Convert.ToBoolean(row["IS_HYPOTTETICAL"], CultureInfo.InvariantCulture))
continue;
var cName = row["INDEX_NAME"].ToString();
if (eTable.IsKeyExist(cName, ConstraintType.Unique) ||
eTable.IsKeyExist(cName, ConstraintType.KeyPrimary) ||
eTable.IsKeyExist(cName, ConstraintType.KeyForeign))
continue;
if (aHash.ContainsKey(cName))
continue;
var eIndex = new IndexSchema();
aHash.Add(cName, true);
eIndex.Name = cName;
eIndex.Unique = Convert.ToBoolean(row["IS_UNIQUE"], CultureInfo.InvariantCulture);
eIndex.Clustered = Convert.ToBoolean(row["IS_CLUSTERED"], CultureInfo.InvariantCulture);
//eIndex.isActive = !Convert.ToBoolean(cRow["IS_INACTIVE"], CultureInfo.InvariantCulture);
var dtv = dtShema.DefaultView;
dtv.RowFilter = string.Format("INDEX_NAME = '{0}'", cName);
dtv.Sort = "COLUMN_ORDINAL_POSITION ASC";
var columns = "";
for (var y = 0; y < dtv.Count; y++)
columns += (dtv[y]["COLUMN_NAME"] +
(Convert.ToBoolean(dtv[y]["IS_DESCENDING"], CultureInfo.InvariantCulture) ? " DESC" : "") +
", ");
columns = columns.Remove(columns.Length - 2, 2);
eIndex.Columns = columns;
indexes.Add(eIndex);
}
return indexes;
}
/// <summary>
/// Check that a given index has all it's columns into the primary key.
/// </summary>
/// <param name="index">The index to check.</param>
public bool IsPrimaryKey(IndexSchema index)
{
foreach(ColumnSchema col in index.MemberColumns)
{
if (!col.IsPrimaryKeyMember)
return false;
}
return true;
}
protected override string MakeDdlIndexDrop(IndexSchema index, TableSchema table)
{
return $@"DROP INDEX {MakeDdlElementName(index.Name)}";
}
public string IXColumnNames(IndexSchema index)
{
string Name = String.Empty;
for(int x=0;x < index.MemberColumns.Count;x++)
{
Name += ", " + GetPrivateName(index.MemberColumns[x].Name);
}
return Name.Substring(2);
}
private void AddColumnToIndex(IndexSchema index, DataRow row)
{
index.Columns.Add(
row.Field <string>(IndexesSchemaNames.ColumnName),
row.Field <short>(IndexesSchemaNames.Collation) == 2 ? SortOrder.Descending : SortOrder.Ascending);
}
//http://msdn2.microsoft.com/en-US/library/aa238878(SQL.80).aspx
public override void RenameIndex (IndexSchema index, string name)
{
Rename (index.Name, name, "INDEX");
index.Name = name;
private static List <IndexSchema> GetIndexes(DbConnection con, TableSchema eTable)
{
var aStore = new List <IndexSchema>();
var aHash = new List <string>();
string[] restrict4 = { null, null, null, null };
string[] restrict5 = { null, null, null, null, null };
// INDEX_TYPE = 0 - ascending, 1 - descending
restrict4[2] = eTable.Name;
restrict5[2] = eTable.Name;
var dtShema = con.GetSchema("Indexes", restrict4);
aStore.Clear();
aHash.Clear();
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var cName = cRow["INDEX_NAME"].ToString();
if (eTable.IsKeyExist(cName, ConstraintType.Unique) ||
eTable.IsKeyExist(cName, ConstraintType.KeyPrimary) ||
eTable.IsKeyExist(cName, ConstraintType.KeyForeign) ||
Convert.ToBoolean(cRow["PRIMARY_KEY"], CultureInfo.InvariantCulture))
{
continue;
}
if (aHash.Contains(cName))
{
continue;
}
var eIndex = new IndexSchema();
var columns = String.Empty;
aHash.Add(cName);
eIndex.Name = cName;
eIndex.Unique = Convert.ToBoolean(cRow["UNIQUE"], CultureInfo.InvariantCulture);
if (cRow["TYPE"] == DBNull.Value)
{
eIndex.Sort = SortOrder.Ascending;
}
else
{
eIndex.Sort = Convert.ToInt32(cRow["TYPE"], CultureInfo.InvariantCulture) == 0
? SortOrder.Ascending
: SortOrder.Descending;
}
eIndex.IsActive = true;
// !Convert.ToBoolean(cRow["IS_INACTIVE"], CultureInfo.InvariantCulture);
restrict5[3] = cName;
var dtIndexColumns = con.GetSchema("IndexColumns", restrict5);
var first = true;
for (var y = 0; y < dtIndexColumns.Rows.Count; y++)
{
if (first)
{
first = false;
}
else
{
columns += ", ";
}
columns += dtIndexColumns.Rows[y]["COLUMN_NAME"];
}
eIndex.Columns = columns;
aStore.Add(eIndex);
}
return(aStore);
}
public abstract string BuildDropIndexCommand(IndexSchema schema);
public override string BuildDropIndexCommand(IndexSchema schema)
{
string sql = String.Format(
@"DROP INDEX {0};",
Api.CommandBuilder.QuoteIdentifier(schema.IndexName)
);
return sql;
}
protected abstract string MakeDdlIndexCreate(IndexSchema index, TableSchema table);
//http://dev.mysql.com/doc/refman/5.1/en/create-index.html
public override void CreateIndex(IndexSchema index)
{
throw new NotImplementedException();
}
//http://dev.mysql.com/doc/refman/5.1/en/create-index.html
public override void CreateIndex (IndexSchema index)
{
throw new NotImplementedException ();
}
public virtual Task CreateIndex(IDbConnection connection, string objectName, IndexSchema indexSchema)
{
throw new NotImplementedException();
}
private static List<IndexSchema> GetIndexes(DbConnection con, TableSchema eTable)
{
var aStore = new List<IndexSchema>();
string[] restrict3 = {null, null, eTable.Name};
string[] restrict4 = {null, null, eTable.Name, null};
// INDEX_TYPE = 0 - ascending, 1 - descending
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = eTable.Name;
restrict4[3] = null;
var dtShema = con.GetSchema("Indexes", restrict3);
aStore.Clear();
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var cName = cRow["INDEX_NAME"].ToString();
if (eTable.IsKeyExist(cName, ConstraintType.Unique) ||
eTable.IsKeyExist(cName, ConstraintType.KeyPrimary) ||
eTable.IsKeyExist(cName, ConstraintType.KeyForeign))
continue;
var eIndex = new IndexSchema();
var columns = "";
eIndex.Name = cName;
eIndex.Unique = Convert.ToBoolean(cRow["IS_UNIQUE"], CultureInfo.InvariantCulture);
if (cRow["INDEX_TYPE"] == DBNull.Value)
eIndex.Sort = SortOrder.Ascending;
else
eIndex.Sort = Convert.ToInt32(cRow["INDEX_TYPE"], CultureInfo.InvariantCulture) == 0
? SortOrder.Ascending
: SortOrder.Descending;
eIndex.IsActive = !Convert.ToBoolean(cRow["IS_INACTIVE"], CultureInfo.InvariantCulture);
restrict4[3] = cName;
var dtShemaCols = con.GetSchema("IndexColumns", restrict4);
var dtv = dtShemaCols.DefaultView;
//dtv.RowFilter = "INDEX_NAME = '" + cName + "'";
dtv.Sort = "ORDINAL_POSITION ASC";
for (var y = 0; y < dtv.Count; y++)
columns += (dtv[y]["COLUMN_NAME"] + ", ");
columns = columns.Remove(columns.Length - 2, 2);
eIndex.Columns = columns;
aStore.Add(eIndex);
}
return aStore;
}
public static void AssertIndexSchemasEqual(IndexSchema a, IndexSchema b)
{
Assert.AreEqual(a.Name, b.Name, "Index names don't match");
}
public IndexSchema[] GetTableIndexes(string connectionString, TableSchema table)
{
DataTable dt = GetSchemaData(connectionString,
Indexes,
null /*restrictions[0] - catalog*/,
null /*restrictions[1] - unused*/,
table.Name /*restrictions[2] - table*/,
null /*restrictions[3] - unused*/,
null /*restrictions[4] - index*/);
IndexSchema[] indexes = new IndexSchema[dt.Rows.Count];
int indexIdx = 0;
foreach (DataRow dr in dt.Rows)
{
//Get the list of columns in this index
DataTable cols = GetSchemaData(connectionString,
IndexColumns,
null /*restrictions[0] - catalog*/,
null /*restrictions[1] - unused*/,
table.Name /*restrictions[2] - table*/,
(String)dr[IndexesNameColumn] /*restrictions[3] - index*/,
null /*restrictions[4] - column*/);
string[] columns = new string[cols.Rows.Count];
int colIdx = 0;
foreach (DataRow col in cols.Rows)
{
columns[colIdx++] = (String)col[IndexColumnsNameColumn];
}
indexes[indexIdx++] = new IndexSchema(table,
(String)dr[IndexesNameColumn],
dr.IsNull(IndexesIsPkeyColumn) ? false : (bool)dr[IndexesIsPkeyColumn],
dr.IsNull(IndexesIsUniqueColumn) ? false : (bool)dr[IndexesIsUniqueColumn],
dr.IsNull(IndexesIsClusteredColumn) ? false : (bool)dr[IndexesIsClusteredColumn],
columns);
}
return indexes;
}
public override string BuildCreateIndexCommand(IndexSchema schema)
{
throw new NotSupportedException();
}
