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(); }