/// <summary> /// 获取对应表的索引 /// </summary> /// <param name="tableName">表名</param> /// <returns>对应索引集合</returns> public IList <DbIndex> GetIndexes(string tableName) { string sql = "SELECT idx.index_name, idx.table_name, idx.uniqueness, cons.constraint_type" + " FROM user_indexes idx" + " LEFT JOIN sys.all_constraints cons ON idx.index_name = cons.index_name" + " WHERE idx.table_name = '" + tableName + "'" + " ORDER BY index_name"; DataSet ds = new DataSet(); using (OracleConnection conn = new OracleConnection(DataBaseHelper.ConnectionString)) { conn.Open(); OracleDataAdapter da = new OracleDataAdapter(sql, conn); da.Fill(ds, "DbIndexs"); conn.Close(); } IList <DbIndex> indexes = new List <DbIndex>(); foreach (DataRow dr in ds.Tables["DbIndexs"].Rows) { DbIndex di = new DbIndex(); di.Name = dr["index_name"].ToString(); di.IsUnique = (dr["uniqueness"].ToString().ToUpper() == "UNIQUE"); di.ConstraintType = dr["constraint_type"].ToString(); di.IsPrimaryKey = (di.ConstraintType.ToUpper() == "P"); indexes.Add(di); } return(indexes); }
/// <summary> /// 获取对应表的索引 /// </summary> /// <param name="tableName">表名</param> /// <returns>对应索引集合</returns> public IList <DbIndex> GetIndexes(string tableName) { string sql = "SELECT idx.name as index_name, tbl.name as table_name, idx.is_unique, idx.is_primary_key" + " FROM sys.tables tbl" + " INNER JOIN sys.indexes idx ON tbl.object_id = idx.object_id and idx.index_id > 0" + " WHERE tbl.name = '" + tableName + "'"; DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(DataBaseHelper.ConnectionString)) { conn.Open(); SqlDataAdapter da = new SqlDataAdapter(sql, conn); da.Fill(ds, "DbIndexs"); conn.Close(); } IList <DbIndex> indexes = new List <DbIndex>(); foreach (DataRow dr in ds.Tables["DbIndexs"].Rows) { DbIndex di = new DbIndex(); di.Name = dr["index_name"].ToString(); di.IsUnique = (dr["is_unique"].ToString().ToLower() == "true"); di.IsPrimaryKey = (dr["is_primary_key"].ToString().ToLower() == "true"); di.ConstraintType = (di.IsPrimaryKey == true) ? "P" : ""; indexes.Add(di); } return(indexes); }
private static void GenerateIndexDescriptionSql(this DbIndex index, IndentedStringBuilder sqlBuilder, SqlVersion sqlVersion, string schema, string table) { if (string.IsNullOrEmpty(index.Name)) { return; } var description = index.Description; if (string.IsNullOrEmpty(description)) { return; } sqlBuilder.GenerateDescriptionSql(sqlVersion, schema, table, "INDEX", index.Name.FormatName(table, false), description); }
private static void MigrateDbIndexes() { Helper.Log("Start migrating database indexes"); DataTable dt = SQLSvrDbi.GetIndexes(); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { DbIndex index = new DbIndex( row["SchemaName"].ToString().ToUpper(), row["TableName"].ToString().ToUpper(), row["IndexName"].ToString().ToUpper(), row["ColumnName"].ToString().ToUpper(), bool.Parse(row["IsUnique"].ToString())); index.Migrate(); } } Helper.Log("Finished migrating database indexes"); }
/// <summary> /// 设置索引的列信息 /// </summary> /// <param name="index"></param> /// <returns></returns> public DbIndex SetIndexColumns(DbIndex index) { IList <string> columns = this.columnDao.GetIndexColumns(index.Name, index.Table.Name); index.Columns.Clear(); foreach (string colname in columns) { foreach (DbColumn dc in index.Table.Columns) { if (colname == dc.Name) { if (dc.IsPrimaryKey == false) { dc.IsPrimaryKey = index.IsPrimaryKey; } index.Columns.Add(dc); break; } } } return(index); }
public override string ToString() { return(string.Concat(new string[] { Host, ":", Port.ToString(), "::", DbIndex.ToString() })); }
public void SaveChanges(DbSchemeCommit SchemeCommit) { DBEntities e = COREobject.i.Context; foreach (DbTable schemeTable in SchemeCommit.Tables) { IEnumerable <DbTable> removeTables = SchemeCommit.Tables.Where(x1 => !e.DbTables.Any(x2 => x2.Id == x1.Id)); e.DbTables.Except <DbTable>(removeTables); //maže všechny tabulky, které se už nenachází na schématu uživatele if (e.DbTables.SingleOrDefault(x => x.Id == schemeTable.Id) == null) //pokud je ve schématu uživatele vytvořena nová tabulka { e.DbTables.Add(schemeTable); } else { DbTable DatabaseTable = e.DbTables.SingleOrDefault(x => x.Id == schemeTable.Id); if (DatabaseTable.Name != schemeTable.Name) { DatabaseTable.Name = schemeTable.Name; } foreach (DbColumn schemeColumn in schemeTable.Columns) { IEnumerable <DbColumn> removeColumns = schemeTable.Columns.Where(x1 => !DatabaseTable.Columns.Any(x2 => x2.Id == x1.Id)); DatabaseTable.Columns.Except <DbColumn>(removeColumns); //maže všechny sloupce tabulky, které se už nenachází na schématu uživatele if (DatabaseTable.Columns.SingleOrDefault(x => x.Id == schemeColumn.Id) == null) //pokud je ve schématu uživatele vytvořen nový sloupec { DatabaseTable.Columns.Add(schemeColumn); } else { DbColumn DatabaseColumn = DatabaseTable.Columns.SingleOrDefault(x => x.Id == schemeColumn.Id); if (DatabaseColumn.Name != schemeColumn.Name) { DatabaseColumn.Name = schemeColumn.Name; } if (DatabaseColumn.DisplayName != schemeColumn.DisplayName) { DatabaseColumn.DisplayName = schemeColumn.DisplayName; } if (DatabaseColumn.PrimaryKey != schemeColumn.PrimaryKey) { DatabaseColumn.PrimaryKey = schemeColumn.PrimaryKey; } if (DatabaseColumn.Type != schemeColumn.Type) { DatabaseColumn.Type = schemeColumn.Type; } if (DatabaseColumn.Unique != schemeColumn.Unique) { DatabaseColumn.Unique = schemeColumn.Unique; } if (DatabaseColumn.AllowNull != schemeColumn.AllowNull) { DatabaseColumn.AllowNull = schemeColumn.AllowNull; } if (DatabaseColumn.ColumnLength != schemeColumn.ColumnLength) { DatabaseColumn.ColumnLength = schemeColumn.ColumnLength; } if (DatabaseColumn.ColumnLengthIsMax != schemeColumn.ColumnLengthIsMax) { DatabaseColumn.ColumnLengthIsMax = schemeColumn.ColumnLengthIsMax; } if (DatabaseColumn.DefaultValue != schemeColumn.DefaultValue) { DatabaseColumn.DefaultValue = schemeColumn.DefaultValue; } } } foreach (DbIndex schemeIndex in schemeTable.Indices) { IEnumerable <DbIndex> removeIndeces = schemeTable.Indices.Where(x1 => !DatabaseTable.Indices.Any(x2 => x2.Id == x1.Id)); DatabaseTable.Indices.Except <DbIndex>(removeIndeces); //maže všechny indexy tabulky, které se už nenachází na schématu uživatele if (DatabaseTable.Indices.SingleOrDefault(x => x.Id == schemeIndex.Id) == null) //pokud je ve schématu uživatele vytvořen nový index { DatabaseTable.Indices.Add(schemeIndex); } else { DbIndex databaseIndex = DatabaseTable.Indices.SingleOrDefault(x => x.Id == schemeIndex.Id); if (databaseIndex.ColumnNames != schemeIndex.ColumnNames) { databaseIndex.ColumnNames = schemeIndex.ColumnNames; } if (databaseIndex.Name != schemeIndex.Name) { databaseIndex.Name = schemeIndex.Name; } if (databaseIndex.Unique != schemeIndex.Unique) { databaseIndex.Unique = schemeIndex.Unique; } } } } } foreach (DbRelation schemeRelation in SchemeCommit.Relations) { IEnumerable <DbRelation> removeRelations = SchemeCommit.Relations.Where(x1 => !e.DbRelation.Any(x2 => x2.Id == x1.Id)); e.DbRelation.Except <DbRelation>(removeRelations); //maže všechny vztahy, které se už nenachází ve schématu uživatele if (e.DbRelation.SingleOrDefault(x => x.Id == schemeRelation.Id) == null) //pokud je ve schématu uživatele vytvořen nový vztah { e.DbRelation.Add(schemeRelation); } else { DbRelation databaseRelation = e.DbRelation.SingleOrDefault(x => x.Id == schemeRelation.Id); databaseRelation.SourceTableId = schemeRelation.SourceTableId; databaseRelation.TargetTableId = schemeRelation.TargetTableId; databaseRelation.SourceColumnId = schemeRelation.SourceColumnId; databaseRelation.TargetColumnId = schemeRelation.TargetColumnId; databaseRelation.Type = schemeRelation.Type; } } foreach (DbView schemeView in SchemeCommit.Views) { IEnumerable <DbView> removeRelations = SchemeCommit.Views.Where(x1 => !e.DbView.Any(x2 => x2.Id == x1.Id)); e.DbView.Except <DbView>(removeRelations); //maže všechny pohledy, které se už nenachází ve schématu uživatele if (e.DbView.SingleOrDefault(x => x.Id == schemeView.Id) == null) //pokud je ve schématu uživatele vytvořen nový pohled { e.DbView.Add(schemeView); } else { DbView databaseView = e.DbView.SingleOrDefault(x => x.Id == schemeView.Id); if (databaseView.Name != schemeView.Name) { databaseView.Name = schemeView.Name; } if (databaseView.Query != schemeView.Query) { databaseView.Query = schemeView.Query; } } } }
public int SaveScheme(int appId, AjaxTransferDbScheme postData) { bool dbSchemeLocked = false; try { DbSchemeCommit commit = new DbSchemeCommit(); var context = COREobject.i.Context; var requestedApp = context.Applications.Find(appId); requestedApp.SchemeLockedForUserId = null; if (requestedApp.DbSchemeLocked) { throw new InvalidOperationException("This application's database scheme is locked because another process is currently working with it."); } requestedApp.DbSchemeLocked = dbSchemeLocked = true; requestedApp.EntitronChangedSinceLastBuild = true; requestedApp.TapestryChangedSinceLastBuild = true; context.SaveChanges(); commit.Timestamp = DateTime.UtcNow; commit.CommitMessage = postData.CommitMessage; requestedApp.DatabaseDesignerSchemeCommits.Add(commit); Dictionary <int, int> tableIdMapping = new Dictionary <int, int>(); Dictionary <int, int> columnIdMapping = new Dictionary <int, int>(); Dictionary <int, DbColumn> columnMapping = new Dictionary <int, DbColumn>(); foreach (var ajaxTable in postData.Tables) { int ajaxTableId = ajaxTable.Id; DbTable newTable = new DbTable { Name = ajaxTable.Name, PositionX = ajaxTable.PositionX, PositionY = ajaxTable.PositionY }; foreach (var column in ajaxTable.Columns) { int ajaxColumnId = column.Id; DbColumn newColumn = new DbColumn { Name = column.Name, DisplayName = column.DisplayName, Type = column.Type, PrimaryKey = column.PrimaryKey, AllowNull = column.AllowNull, DefaultValue = column.DefaultValue, ColumnLength = column.ColumnLength, ColumnLengthIsMax = column.ColumnLengthIsMax, Unique = column.Unique }; newTable.Columns.Add(newColumn); context.SaveChanges(); columnMapping.Add(ajaxColumnId, newColumn); } foreach (var index in ajaxTable.Indices) { string columnNamesString = ""; if (index.ColumnNames.Count > 0) { for (int i = 0; i < index.ColumnNames.Count - 1; i++) { columnNamesString += index.ColumnNames[i] + ","; } columnNamesString += index.ColumnNames.Last(); } DbIndex newIndex = new DbIndex { Name = index.Name, Unique = index.Unique, ColumnNames = columnNamesString }; newTable.Indices.Add(newIndex); } commit.Tables.Add(newTable); context.SaveChanges(); tableIdMapping.Add(ajaxTableId, newTable.Id); foreach (var column in ajaxTable.Columns) { DbColumn col = newTable.Columns.SingleOrDefault(x => x.Name.ToLower() == columnMapping[column.Id].Name.ToLower()); columnIdMapping.Add(column.Id, col.Id); } } foreach (var ajaxRelation in postData.Relations) { int sourceTable = tableIdMapping[ajaxRelation.SourceTable]; int targetTable = tableIdMapping[ajaxRelation.TargetTable]; int sourceColumn = columnIdMapping[ajaxRelation.SourceColumn]; int targetColumn = columnIdMapping[ajaxRelation.TargetColumn]; DbTable targetTableDb = commit.Tables.SingleOrDefault(x => x.Id == targetTable); DbTable sourceTableDb = commit.Tables.SingleOrDefault(x => x.Id == sourceTable); string name = targetTableDb.Name + targetTableDb.Columns.SingleOrDefault(x => x.Id == targetColumn).Name + "_" + sourceTableDb.Name + sourceTableDb.Columns.SingleOrDefault(x => x.Id == sourceColumn).Name; commit.Relations.Add(new DbRelation { SourceTableId = sourceTable, TargetTableId = targetTable, SourceColumnId = sourceColumn, TargetColumnId = targetColumn, Type = ajaxRelation.Type, Name = name }); } foreach (var ajaxView in postData.Views) { commit.Views.Add(new DbView { Name = ajaxView.Name, Query = ajaxView.Query, PositionX = ajaxView.PositionX, PositionY = ajaxView.PositionY }); } requestedApp.DbSchemeLocked = dbSchemeLocked = false; commit.IsComplete = true; context.SaveChanges(); return(commit.Id); } catch (Exception ex) { if (dbSchemeLocked) { var context = COREobject.i.Context; var requestedApp = context.Applications.Find(appId); requestedApp.DbSchemeLocked = false; context.SaveChanges(); } var errorMessage = "DatabaseDesigner: an error occurred when saving the database scheme (POST api/database/apps/{appId}/commits). " + $"Exception message: {ex.Message}"; Log.Error(errorMessage); throw GetHttpInternalServerErrorResponseException(errorMessage); } }