private void DropOldRelations(DbSchemeCommit dbSchemeCommit)
{
_progressHandler.SetMessage("DropOldRelations", type: MessageType.InProgress);
foreach (string tableName in _db.List(ETabloid.ApplicationTables))
{
_entitronFKs.AddRange(new DBTable(_db)
{
Name = tableName
}.ForeignKeys);
}
// foreign keys in database, but not in scheme
IEnumerable <DBForeignKey> deletedFK = _entitronFKs
.Where(fk => !dbSchemeCommit.Relations.Any(rel => fk.Compare(rel)));
//dropping old FKs
foreach (DBForeignKey fk in deletedFK)
{
fk.SourceTable.ForeignKeys.Remove(fk);
}
_db.SaveChanges();
_progressHandler.SetMessage("DropOldRelations", type: MessageType.Success);
}
/// <summary>
/// </summary>
/// <param name="dbSchemeCommit"></param>
public void GenerateDatabase(DbSchemeCommit dbSchemeCommit, COREobject core)
{
if (dbSchemeCommit != null)
{
try
{
_db = core.Entitron;
_ent = core.Context;
_app = core.Application;
_progressHandler.SetMessage("DropOldRelations", "Drop old relations");
_progressHandler.SetMessage("GenerateTables", "Generate tables");
_progressHandler.SetMessage("GenerateRelation", "Generate relations");
_progressHandler.SetMessage("GenerateView", "Generate views");
_progressHandler.SetMessage("DroppingOldTables", "Drop old tables");
DropOldRelations(dbSchemeCommit);
GenerateTables(dbSchemeCommit);
GenerateRelation(dbSchemeCommit);
GenerateView(dbSchemeCommit);
DroppingOldTables(dbSchemeCommit);
_progressHandler.SetMessage("", type: MessageType.Success);
}
catch (Exception)
{
throw;
}
finally
{
Entitron.ClearCache();
}
}
}
private void DroppingOldTables(DbSchemeCommit dbSchemeCommit)
{
_progressHandler.SetMessage("DroppingOldTables", type: MessageType.InProgress);
//list of tables, which are in database, but not in scheme
IEnumerable <string> deletedTables = _db.List(ETabloid.ApplicationTables)
.Except(dbSchemeCommit.Tables.Select(x => x.Name));
_progressHandler.SetMessage("DroppingOldTables", progressSteps: deletedTables.Count());
//dropping old tables(must be here, after dropping all constraints)
foreach (string deleteTable in deletedTables)
{
_db.TableDrop(new DBTable(_db)
{
Name = deleteTable
});
_ent.ColumnMetadata.RemoveRange(_app.ColumnMetadata.Where(c => c.TableName == deleteTable));
_progressHandler.IncrementProgress();
}
_db.SaveChanges();
_progressHandler.SetMessage("DroppingOldTables", type: MessageType.Success);
}
private void GenerateRelation(DbSchemeCommit dbSchemeCommit)
{
_progressHandler.SetMessage("GenerateRelation", type: MessageType.InProgress);
// foreign keys in scheme, but not in database
IEnumerable <DbRelation> newFK = dbSchemeCommit.Relations
.Where(rel => !_entitronFKs.Any(fk => fk.Compare(rel)));
//adding new FKs
foreach (DbRelation designerFK in newFK)
{
DBTable sourceTable = _db.Table(designerFK.SourceTable.Name);
DBTable targetTable = _db.Table(designerFK.TargetTable.Name);
sourceTable.ForeignKeys.Add(new DBForeignKey(_db)
{
SourceTable = sourceTable,
TargetTable = targetTable,
SourceColumn = designerFK.SourceColumn.Name,
TargetColumn = designerFK.TargetColumn.Name
});
}
_db.SaveChanges();
_progressHandler.SetMessage("GenerateRelation", type: MessageType.Success);
}
private void BuildEntitron()
{
progressHandler.SetActiveSection(EModule.Entitron);
if (masterApp.EntitronChangedSinceLastBuild || _rebuildInAction)
{
if (masterApp.DbSchemeLocked)
{
throw new Exception("Database is locked - another process is currently working with it");
}
progressHandler.SetMessage(message: "Actualizing database", type: MessageType.InProgress);
try
{
masterApp.DbSchemeLocked = true;
masterContext.SaveChanges();
var dbSchemeCommit = masterApp.DatabaseDesignerSchemeCommits.OrderByDescending(o => o.Timestamp).FirstOrDefault();
if (dbSchemeCommit == null)
{
dbSchemeCommit = new DbSchemeCommit();
}
if (!dbSchemeCommit.IsComplete)
{
throw new Exception("Database scheme is not saved correctly!");
}
new DatabaseGenerateService(progressHandler).GenerateDatabase(dbSchemeCommit, core);
masterApp.DbSchemeLocked = false;
masterApp.EntitronChangedSinceLastBuild = false;
masterContext.SaveChanges();
progressHandler.SetMessage("final", "Database actualised", MessageType.Success);
progressHandler.SetMessage(type: MessageType.Success);
}
catch (Exception)
{
throw;
}
finally
{
masterApp.DbSchemeLocked = false;
masterContext.SaveChanges();
}
}
else
{
progressHandler.SetMessage(type: MessageType.Info, message: "Database is not necessary to actualize");
}
}
private static void SetAttributesRequestCommitRelations(DbSchemeCommit requestedCommit, AjaxTransferDbScheme result)
{
foreach (var relation in requestedCommit.Relations)
{
result.Relations.Add(new AjaxTransferDbRelation
{
SourceColumn = relation.SourceColumnId,
SourceTable = relation.SourceTableId,
TargetColumn = relation.TargetColumnId,
TargetTable = relation.TargetTableId,
Type = relation.Type
});
}
}
private static void SetAttributesRequestCommitViews(DbSchemeCommit requestedCommit, AjaxTransferDbScheme result)
{
foreach (var view in requestedCommit.Views)
{
result.Views.Add(new AjaxTransferDbView
{
Id = view.Id,
Name = view.Name,
PositionX = view.PositionX,
PositionY = view.PositionY,
Query = view.Query,
});
}
}
/// <exception cref="InstanceNotFoundException">Not found commit for commitId</exception>
private AjaxTransferDbScheme GetCommit(int appId, int commitId = -1)
{
var context = COREobject.i.Context;
var result = new AjaxTransferDbScheme();
var requestedCommit = FetchDbSchemeCommit(appId, commitId, context);
int?lockedForUserId = context.Applications.Find(appId).SchemeLockedForUserId;
result.SchemeLockedForUserId = lockedForUserId; //set the lockedForUserId for ajax model
if (lockedForUserId != null)
{
result.SchemeLockedForUserName = context.Users.SingleOrDefault(u => u.Id == result.SchemeLockedForUserId).DisplayName;
}
if (commitId == -1 && requestedCommit != null)
{
DbSchemeCommit sharedCommit = FetchDbSchemeCommit(Application.SystemApp().Id, commitId, context);
AjaxTransferDbScheme sharedScheme = new AjaxTransferDbScheme();
SetAttributesRequestCommitTables(sharedCommit, sharedScheme);
SetAttributesRequestCommitRelations(sharedCommit, sharedScheme);
SetAttributesRequestCommitViews(sharedCommit, sharedScheme);
sharedScheme.CurrentSchemeCommitId = context.Applications.Find(appId).DatabaseDesignerSchemeCommits.OrderByDescending(s => s.Timestamp).FirstOrDefault().Id;
result.Shared = sharedScheme;
if (requestedCommit == null)
{
return(result);
}
}
//Latest commit was requested, but there are no commits yet. Returning an empty commit.
if (requestedCommit == null)
{
return(new AjaxTransferDbScheme()
{
CurrentSchemeCommitId = null
});
}
result.CurrentSchemeCommitId = context.Applications.Find(appId).DatabaseDesignerSchemeCommits.OrderByDescending(s => s.Timestamp).FirstOrDefault().Id;
SetAttributesRequestCommitTables(requestedCommit, result);
SetAttributesRequestCommitRelations(requestedCommit, result);
SetAttributesRequestCommitViews(requestedCommit, result);
return(result);
}
private static void SetAttributesRequestCommitTables(DbSchemeCommit requestedCommit, AjaxTransferDbScheme result)
{
foreach (var table in requestedCommit.Tables)
{
var ajaxTable = new AjaxTransferDbTable
{
Id = table.Id,
Name = table.Name,
PositionX = table.PositionX,
PositionY = table.PositionY
};
foreach (var column in table.Columns)
{
ajaxTable.Columns.Add(new AjaxTransferDbColumn
{
AllowNull = column.AllowNull,
ColumnLength = column.ColumnLength,
ColumnLengthIsMax = column.ColumnLengthIsMax,
DefaultValue = column.DefaultValue,
Id = column.Id,
Name = column.Name,
DisplayName = column.DisplayName,
PrimaryKey = column.PrimaryKey,
Type = column.Type,
Unique = column.Unique,
});
}
foreach (var index in table.Indices)
{
ajaxTable.Indices.Add(new AjaxTransferDbIndex
{
ColumnNames = index.ColumnNames.Split(',').ToList(),
Id = index.Id,
Name = index.Name,
Unique = index.Unique,
});
}
result.Tables.Add(ajaxTable);
}
}
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);
}
}
private void GenerateTables(DbSchemeCommit dbSchemeCommit)
{
_progressHandler.SetMessage("GenerateTables", type: MessageType.InProgress, progressSteps: dbSchemeCommit.Tables.Count);
foreach (DbTable efTable in dbSchemeCommit.Tables)
{
//// Table ////
DBTable entitronTable = _db.Table(efTable.Name);
// new
if (!_db.Exists(entitronTable.Name, ETabloid.ApplicationTables))
{
// columns
foreach (DbColumn column in efTable.Columns)
{
AddColumn(entitronTable, efTable, column);
}
// add indexes
foreach (DBIndex index in MergeSchemeIndexUnique(entitronTable, efTable))
{
entitronTable.Indexes.Add(index);
}
// create table & columns & costraints & indexes
entitronTable.Create();
}
// update existing
else
{
/// columns
UpdateColumns(entitronTable, efTable);
/// Indexes
List <DBIndex> designerIndexes = MergeSchemeIndexUnique(entitronTable, efTable);
foreach (DBIndex designerIndex in designerIndexes)
{
// create
DBIndex entitronIndex = entitronTable.Indexes.SingleOrDefault(ei => ei.Columns.Count == designerIndex.Columns.Count && ei.Columns.All(eic => designerIndex.Columns.Contains(eic)));
if (entitronIndex == null)
{
entitronTable.Indexes.Add(designerIndex);
}
// modify - isUnique changed
else if (entitronIndex.isUnique != designerIndex.isUnique)
{
entitronTable.Indexes.Remove(entitronIndex);
entitronTable.Indexes.Add(designerIndex);
}
}
// drop
IEnumerable <DBIndex> deletedIndeces = entitronTable.Indexes.Where(ei => !designerIndexes.Any(di => di.Columns.Count == ei.Columns.Count && ei.Columns.All(eic => di.Columns.Contains(eic)))).ToList();
foreach (DBIndex index in deletedIndeces)
{
entitronTable.Indexes.Remove(index);
}
}
_progressHandler.IncrementProgress();
} //end foreach efTable
/// SAVE
_db.SaveChanges();
_ent.SaveChanges();
_progressHandler.SetMessage("GenerateTables", type: MessageType.Success);
}
private void GenerateView(DbSchemeCommit dbSchemeCommit)
{
_progressHandler.SetMessage("GenerateView", type: MessageType.InProgress, progressSteps: dbSchemeCommit.Views.Count);
List <Exception> errors = new List <Exception>();
Queue <DbView> que = new Queue <DbView>(dbSchemeCommit.Views);
DbView firstError = null;
while (que.Any())
{
DbView efView = que.Dequeue();
DBView newView = new DBView(_db)
{
Name = efView.Name,
Sql = efView.Query
};
try
{
if (!_db.Exists(efView.Name, ETabloid.Views))
{
newView.Create();
}
else
{
newView.Alter();
}
_db.SaveChanges();
firstError = null;
}
catch (Exception ex)
{
if (firstError == null)
{
firstError = efView;
}
else if (firstError == efView)
{
firstError = null;
errors.Add(ex);
continue;
}
que.Enqueue(efView);
}
_progressHandler.IncrementProgress();
}
if (errors.Any())
{
throw new OmniusMultipleException(errors);
}
//list of views, which are in database, but not in scheme
List <string> deleteViews = _db.List(ETabloid.Views)
.Except(dbSchemeCommit.Views.Select(x => x.Name)).ToList();
//dropping views
foreach (string viewName in deleteViews)
{
_db.ViewDrop(viewName);
}
_db.SaveChanges();
_progressHandler.SetMessage("GenerateView", type: MessageType.Success);
}
