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