public async Task <IActionResult> GenerateData(SaveConnectionRequest request)
{
#region CHECKING IF USER IS AUTHORIZED TO ACCES REQUESTED DATABASE
var loginId = ((Login)HttpContext.Items["Login"]).LoginId;
//check if user is allowed to connect to database
var connectionProps = await ctx.ConnectionTables.Where(c => c.ConnectionId == request.ConnectionId && c.LoginId == loginId).FirstOrDefaultAsync();
if (connectionProps == null)
{
return(BadRequest(new
{
error = true,
message = "User not authorized to connect to specified database."
}));
}
#endregion
// Only process the tables from the request that need to have values generated.
var tablesForWhichToGenerateSql = request.Tables.Where(t => t.NumberOfColumnsToGenerate > 0).ToList();
if (tablesForWhichToGenerateSql.Count == 0)
{
return(Ok(new
{
error = false,
message = "No data to generate."
}));
}
#region CHECK IF ANY TABLES THAT NEED TO BE GENERATED REFERENCE TABLES/COLUMNS THAT DO NOT EXIST IN THE TABLES THAT NEED TO BE GENERATED
foreach (DatabaseTable tableForWhichToGenerateSql in tablesForWhichToGenerateSql)
{
foreach (DatabaseColumn columnForWhichToGenerateSql in tableForWhichToGenerateSql.DatabaseColumns)
{
// If column is nullable. Then it can reference nonexisting column
if (columnForWhichToGenerateSql.IsNullable == 1)
{
continue;
}
if (columnForWhichToGenerateSql.ForeignTableName != null)
{
string foreignTableName = columnForWhichToGenerateSql.ForeignTableName;
string foreignColumnName = columnForWhichToGenerateSql.ForeignColumnName;
DatabaseTable foreignTable = tablesForWhichToGenerateSql.Where(t => t.Name == foreignTableName).FirstOrDefault();
DatabaseColumn foreignColumn = null;
if (foreignTable != null)
{
foreignColumn = foreignTable.DatabaseColumns.Where(c => c.Name == foreignColumnName).FirstOrDefault();
}
// If foreignTable is null, it means that there will be nothing for the foreign key to reference
if (foreignTable == null || foreignColumn == null)
{
return(BadRequest(new
{
error = true,
message = $"Table {tableForWhichToGenerateSql.Name} column {columnForWhichToGenerateSql.Name} references table {foreignTableName} column {foreignColumnName} which does not exist, in the data provided."
}));
}
}
}
}
#endregion
#region ORDER TABLES SO THAT IF TABLE A CONTAINS FOREIGN KEY REFERENCING TABLE B. TABLE A MUST BE FIRST IN LIST.
// Add those tables for which all referenced table are alredy in sorted list
List <DatabaseTable> sortedListOfTablesForWhichToGenerateSqlByForeignKey = new List <DatabaseTable>();
int renemberedCount;
// When this is true next loop will allow null values
bool tryToPutOneNull = false;
while (tablesForWhichToGenerateSql.Count() != 0)
{
renemberedCount = sortedListOfTablesForWhichToGenerateSqlByForeignKey.Count();
foreach (DatabaseTable tableForWhichToGenerateSql in tablesForWhichToGenerateSql)
{
List <ReferencedTableNullablePair> listOfTablesWhichThisTableReferences = new List <ReferencedTableNullablePair>();
foreach (DatabaseColumn columnForWhichToGenerateSql in tableForWhichToGenerateSql.DatabaseColumns)
{
// If column references some table
if (columnForWhichToGenerateSql.ForeignTableName != null)
{
// Get table of this name
DatabaseTable referencedTable = tablesForWhichToGenerateSql.Where(t => t.Name == columnForWhichToGenerateSql.ForeignTableName).FirstOrDefault();
// If this column refences another table add it to referenced tables
if (referencedTable != null)
{
listOfTablesWhichThisTableReferences.Add(new ReferencedTableNullablePair(referencedTable, columnForWhichToGenerateSql.IsNullable == 1));
}
}
}
// If this table references no one, the data for it can be generated
if (listOfTablesWhichThisTableReferences.Count() == 0)
{
// Add to list of sorted tables
if (!sortedListOfTablesForWhichToGenerateSqlByForeignKey.Contains(tableForWhichToGenerateSql))
{
sortedListOfTablesForWhichToGenerateSqlByForeignKey.Add(tableForWhichToGenerateSql);
}
continue;
}
// If table does reference other tables check if those tables are already in sorted list
bool allAlreadySorted = true;
for (int i = 0; i < listOfTablesWhichThisTableReferences.Count(); i++)
{
Boolean isNullable = listOfTablesWhichThisTableReferences[i].IsNullableReference;
DatabaseTable referencedTable = listOfTablesWhichThisTableReferences[i].ReferencedTable;
DatabaseTable sortedTable = sortedListOfTablesForWhichToGenerateSqlByForeignKey.Where(t => t == referencedTable).FirstOrDefault();
if (sortedTable == null)
{
// Referenced table does not exist in sorted list, so this table is not yet ready to be generated
allAlreadySorted = false;
// Unless we are allowed to pass null and this is nullable
if (tryToPutOneNull && isNullable)
{
allAlreadySorted = true;
tryToPutOneNull = false;
}
}
if (!allAlreadySorted)
{
break;
}
}
//foreach (DatabaseTable referencedTable in listOfTablesWhichThisTableReferences)
//{
// DatabaseTable sortedTable = sortedListOfTablesForWhichToGenerateSqlByForeignKey.Where(t => t == referencedTable).FirstOrDefault();
// if (sortedTable == null)
// {
// // Referenced table does not exist in sorted list, so this table is not yet ready to be generated
// allAlreadySorted = false;
// }
// if (!allAlreadySorted)
// {
// break;
// }
//}
if (allAlreadySorted)
{
if (!sortedListOfTablesForWhichToGenerateSqlByForeignKey.Contains(tableForWhichToGenerateSql))
{
sortedListOfTablesForWhichToGenerateSqlByForeignKey.Add(tableForWhichToGenerateSql);
}
}
}
/// If no new generatable tables were found we are in a loop and should return BadRequest
// If no new generatable tables were found do it again but this time allow for null values on nullables. Add only one on there then try again.
if (renemberedCount >= sortedListOfTablesForWhichToGenerateSqlByForeignKey.Count())
{
// Try to pass with nulls
if (tryToPutOneNull == false)
{
tryToPutOneNull = true;
continue;
}
// Return BadRequest
return(BadRequest(new
{
error = true,
message = $"Please remove circular refenreces and try again"
}));
}
// Remove from tablesForWhichToGenerateSql everything that is in sortedList
foreach (DatabaseTable table in sortedListOfTablesForWhichToGenerateSqlByForeignKey)
{
tablesForWhichToGenerateSql.Remove(table);
}
}
// Return sorted list
tablesForWhichToGenerateSql = sortedListOfTablesForWhichToGenerateSqlByForeignKey;
#endregion
#region ORDER COLUMN INSIDE TABLE SO IF ONE NEEDS TO BE BIGGER THEN ANOTHER THE OTHER IS GENERATED BEFORE THE ONE
foreach (DatabaseTable table in tablesForWhichToGenerateSql)
{
List <DatabaseColumn> columns = table.DatabaseColumns.ToList();
List <string> listOfColumnNamesInTable = columns.Select(c => c.Name).ToList();
foreach (DatabaseColumn column in table.DatabaseColumns)
{
if (column.RelatedColumn != null &&
listOfColumnNamesInTable.Contains(column.RelatedColumn) &&
listOfColumnNamesInTable.IndexOf(column.Name) < listOfColumnNamesInTable.IndexOf(column.RelatedColumn))
{
DatabaseColumn tmp = table.DatabaseColumns.Where(c => c.Name == column.RelatedColumn).First();
columns.Remove(tmp);
columns.Insert(0, tmp);
}
}
table.DatabaseColumns = columns;
}
#endregion
// If program reaches this point, assume tables referenced by foreign keys exist and have data. String builder which will build the SQL commands.
StringBuilder builder = new StringBuilder();
// Dictionary in which string key of format tableName:columnName references list of values to put into mentioned column in mentioned table
Dictionary <string, List <string> > valuesGeneratedForDatabase = new Dictionary <string, List <string> >();
#region GENERATE SQL COMMAND
foreach (DatabaseTable tableForWhichToGenerateSql in tablesForWhichToGenerateSql)
{
// We know that NumberOfColumnsToGenerate is greater then 0 and is not null
int numberOfRowsToGenerate = (int)tableForWhichToGenerateSql.NumberOfColumnsToGenerate;
// Part of sql command that will be used to generate each row of this table.
string baseForSqlCommand = $"INSERT INTO {tableForWhichToGenerateSql.Name} (";
// First column inside brackets does not have ',', while the others have.
bool isFirstColumn = true;
// List of values to insert into table. Each list inside this list contains string to insert into corresponding row. listOfValueLists[0] means the list of values for first column.
// listOfValueLists[0][0] means value to put on first row for first column
List <List <string> > listOfValueLists = new List <List <string> >();
foreach (DatabaseColumn columnForWhichToGenerateSql in tableForWhichToGenerateSql.DatabaseColumns)
{
if (isFirstColumn)
{
baseForSqlCommand += columnForWhichToGenerateSql.Name;
isFirstColumn = false;
}
else
{
baseForSqlCommand += $", {columnForWhichToGenerateSql.Name}";
}
// List of values to use for generating column data.
List <string> listOfValues = new List <string>();
try
{
// Append to column his table
columnForWhichToGenerateSql.Table = tableForWhichToGenerateSql;
// Generate values for this column
listOfValues = GenerationModes.Generate(ctx, numberOfRowsToGenerate, columnForWhichToGenerateSql, valuesGeneratedForDatabase, connectionProps.SqlPlatformId, tableForWhichToGenerateSql);
}
catch (GenerationException e)
{
return(BadRequest(new
{
error = true,
message = $"Error while generating data for COLUMN '{columnForWhichToGenerateSql.Name}' in TABLE '{tableForWhichToGenerateSql.Name}': " + e.Message
}));
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(BadRequest(new
{
error = true,
message = "Unexpected error ocurred"
}));
}
// Add to list of values for generating
listOfValueLists.Add(listOfValues);
// Insert valueList for given table and column int Dictionary for potential foreign key use
valuesGeneratedForDatabase[$"{tableForWhichToGenerateSql.Name}:{columnForWhichToGenerateSql.Name}"] = listOfValues;
}
// Continue base of command
baseForSqlCommand += ") VALUES(";
// Add created values to StringBuilder
for (int i = 0; i < numberOfRowsToGenerate; i++)
{
// The first values for columns does not have ',', while the others have.
isFirstColumn = true;
string rowInsertCommand = baseForSqlCommand;
// For this row for every list in listOfValueLists get the i-th element and insert it into the command.
foreach (List <string> valueList in listOfValueLists)
{
if (isFirstColumn)
{
rowInsertCommand += $"{valueList[i]} ";
isFirstColumn = false;
}
else
{
rowInsertCommand += $",{valueList[i]} ";
}
}
// Finish the command
rowInsertCommand += ");\n";
// Add line insert command to builder.
builder.Append(rowInsertCommand);
}
}
#endregion
string connectionString = $"Host={connectionProps.Host}; Username={connectionProps.Username}; Password={request.DatabasePassword}; Database={connectionProps.Database}";
SqlPlatform databasePlatform = ctx.SqlPlatforms.Where(p => p.SqlPlatformId == connectionProps.SqlPlatformId).FirstOrDefault();
if (databasePlatform == null)
{
return(Ok(new
{
error = true,
message = $"Invalid sql platform id {connectionProps.SqlPlatformId}."
}));
}
CommandExecuterFactory commandExecuterFactory = new CommandExecuterFactory(connectionProps, request.DatabasePassword);
IDatabaseCommandExecuter commandExecuter = commandExecuterFactory.GetExecuter(databasePlatform.Name);
string sql = builder.ToString();
try
{
await commandExecuter.ExecuteCommand(sql);
}
catch (Exception e)
{
Console.WriteLine(e);
return(BadRequest(new
{
error = true,
message = "Unexpected error ocurred"
}));
}
return(Ok(new
{
error = false,
message = "Data successfully generate and inserted into database"
}));
}
public async Task <IActionResult> SaveConnection(SaveConnectionRequest request)
{
//
int connectionId = request.ConnectionId;
DatabaseTable[] tablesGivenToSaveToDatabase = request.Tables;
//get connections to which to save tables
var connection = await ctx.ConnectionTables.Where(connection => connection.ConnectionId == connectionId).FirstOrDefaultAsync();
var tablesStoredInApplicationDatabaseForThisConnection = await ctx.DatabaseTables.Where(t => t.ConnectionId == connectionId).Include(t => t.DatabaseColumns).ToListAsync();
foreach (DatabaseTable tableGivenToSaveToDatabase in tablesGivenToSaveToDatabase)
{
//get corresponding table from own database
DatabaseTable tableStoredInApplicationDatabase = tablesStoredInApplicationDatabaseForThisConnection.Find(t => t == tableGivenToSaveToDatabase);
//if no corresponding table is stored in application database for given connection create new table
if (tableStoredInApplicationDatabase == null)
{
DatabaseTable newTable = new DatabaseTable
{
Name = tableGivenToSaveToDatabase.Name,
Connection = connection,
ConnectionId = connectionId,
DatabaseColumns = tableGivenToSaveToDatabase.DatabaseColumns,
NumberOfColumnsToGenerate = tableGivenToSaveToDatabase.NumberOfColumnsToGenerate
};
try
{
await ctx.AddAsync(newTable);
}
catch (Exception e)
{
return(BadRequest(new
{
error = true,
message = e.Message
}));
}
}
else
{
tableStoredInApplicationDatabase.CopyRelevantGenerationValues(tableGivenToSaveToDatabase);
var columnsStoredInApplicationDatabase = tableStoredInApplicationDatabase.DatabaseColumns;
var columnsGivenToSaveToDatabase = tableGivenToSaveToDatabase.DatabaseColumns;
// For every column given to save, check if there is corresponding table, if there is none create new one, if not modify existing column to fit new one
foreach (var columnGivenToSaveToDatabase in columnsGivenToSaveToDatabase)
{
//if there is no column add it
var columnStoredInApplicationDatabase = columnsStoredInApplicationDatabase.Where(c => c == columnGivenToSaveToDatabase).FirstOrDefault();
// If there is no corresponding column for column that needs to be saved to database add the new column to the database
if (columnStoredInApplicationDatabase == null)
{
columnsStoredInApplicationDatabase.Add(columnGivenToSaveToDatabase);
continue;
}
// If corresponding table exists modify existing one to match new one
columnStoredInApplicationDatabase.CopyRelevantGenerationValues(columnGivenToSaveToDatabase);
}
}
}
await ctx.SaveChangesAsync();
return(Ok(new
{
error = false,
message = "Successfully saved"
}));
}
