public async Task <IActionResult> Read(ConnectRequest request)
{
var loginId = ((Login)HttpContext.Items["Login"]).LoginId;
int connectionId = request.ConnectionId;
string password = request.Password;
//check if user is allowed to connect to database
var connectionProps = await ctx.ConnectionTables.Where(c => c.ConnectionId == connectionId && c.LoginId == loginId).FirstOrDefaultAsync();
if (connectionProps == null)
{
return(BadRequest(new
{
error = true,
message = "User not authorized to connect to specified database."
}));
}
//get stereotypes
var stereotypes = await ctx.Stereotypes.ToListAsync();
var generationModes = await ctx.GenerationModes.ToListAsync();
//get generation modes
//get stereotype mappings
var mappings = await ctx.NameMappings.ToListAsync();
string connectionString = $"Host={connectionProps.Host}; Username={connectionProps.Username}; Password={password}; Database={connectionProps.Database}";
//using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
//{
// try
// {
// connection.Open();
// }
// catch (Exception)
// {
// return BadRequest(new
// {
// error = true,
// message = "Could not connect to database"
// });
// }
// List<DatabaseTable> tables = new List<DatabaseTable>();
// //sql to get table names
// String sql = "SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'; ";
// using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
// {
// using (NpgsqlDataReader reader = await command.ExecuteReaderAsync())
// {
// while (await reader.ReadAsync())
// {
// tables.Add(new DatabaseTable
// {
// Name = reader[1].ToString(),
// DatabaseColumns = new List<DatabaseColumn>(),
// ConnectionId = connectionId,
// NumberOfColumnsToGenerate = 0
// });
// }
// }
// }
// //columns
// foreach (DatabaseTable table in tables)
// {
// sql = $@"SELECT c.column_name, c.data_type
// FROM information_schema.columns as c
// WHERE c.table_name = '{table.Name}'";
// using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
// {
// using (NpgsqlDataReader reader = await command.ExecuteReaderAsync())
// {
// while (await reader.ReadAsync())
// {
// Console.WriteLine(reader[1]);
// var mapping = mappings.Where(mapping => mapping.Name.Trim() == reader[1].ToString().Trim()).FirstOrDefault();
// tables.Find(t => t == table).DatabaseColumns.Add(new DatabaseColumn
// {
// Name = reader[0].ToString(),
// IsUnique = 0,
// IsNullable = 0,
// StereotypeName = stereotypes.Find(s => s.StereotypeId == mapping.StereotypeId).Name,
// StereotypeId = mapping.StereotypeId
// }
// );
// }
// }
// }
// }
// //primary keys
// sql = $@"SELECT tc.table_name, c.column_name, c.data_type
// FROM information_schema.table_constraints tc
// JOIN information_schema.constraint_column_usage AS ccu USING(constraint_schema, constraint_name)
// JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema
// AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
// WHERE constraint_type = 'PRIMARY KEY' OR constraint_type = 'UNIQUE'; ";
// using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
// {
// using (NpgsqlDataReader reader = await command.ExecuteReaderAsync())
// {
// while (await reader.ReadAsync())
// {
// var tempColumn = tables.Find(t => t.Name == reader[0].ToString()).DatabaseColumns.Where(c => c.Name == reader[1].ToString()).FirstOrDefault();
// //since we just read metadata about the table it should exist if it does not something has gone wrong
// if (tempColumn == null)
// throw new Exception($"Read from database that column {tempColumn.Name} is primary key of table {reader[0].ToString()}, but based on previous query that column should not exist.");
// tempColumn.IsUnique = 1;
// }
// }
// }
// //foreign keys
// sql = $@"SELECT
// tc.table_name,
// kcu.column_name,
// ccu.table_name AS foreign_table_name,
// ccu.column_name AS foreign_column_name
// FROM
// information_schema.table_constraints AS tc
// JOIN information_schema.key_column_usage AS kcu
// ON tc.constraint_name = kcu.constraint_name
// AND tc.table_schema = kcu.table_schema
// JOIN information_schema.constraint_column_usage AS ccu
// ON ccu.constraint_name = tc.constraint_name
// AND ccu.table_schema = tc.table_schema
// WHERE tc.constraint_type = 'FOREIGN KEY';";
// using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
// {
// using (NpgsqlDataReader reader = await command.ExecuteReaderAsync())
// {
// while (await reader.ReadAsync())
// {
// DatabaseColumn temp = tables.Find(t => t.Name == reader[0].ToString()).DatabaseColumns.Where(c => c.Name == reader[1].ToString()).FirstOrDefault();
// temp.ForeignTableName = reader[2].ToString();
// temp.ForeignColumnName = reader[3].ToString();
// // If it's a foreign key give it foreign key generation options
// temp.StereotypeId = GenerationModes.FOREIGN_KEY_STEREOTYPE_ID;
// temp.StereotypeName = stereotypes.Find(s => s.StereotypeId == GenerationModes.FOREIGN_KEY_STEREOTYPE_ID).Name;
// }
// }
// }
// // Check for nullables.
// sql = $@"select c.table_name,
// c.column_name,
// c.is_nullable
// from information_schema.columns c
// join information_schema.tables t
// on c.table_schema = t.table_schema
// and c.table_name = t.table_name
// where c.table_schema not in ('pg_catalog', 'information_schema')
// and t.table_type = 'BASE TABLE'
// order by table_name,
// column_name;";
// using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
// {
// using (NpgsqlDataReader reader = await command.ExecuteReaderAsync())
// {
// while (await reader.ReadAsync())
// {
// //Console.WriteLine(reader[2].ToString());
// //Console.WriteLine(reader[0].ToString());
// //Console.WriteLine(reader[1].ToString());
// if(reader[2].ToString() == "YES")
// {
// DatabaseColumn temp = tables.Find(t => t.Name == reader[0].ToString()).DatabaseColumns.Where(c => c.Name == reader[1].ToString()).FirstOrDefault();
// temp.IsNullable = 1;
// }
// }
// }
// }
// // Modify database data with data stored in database
// await getSavedData(connectionId, tables);
// // If no generation mode is specified give it one that fits with stereotype
// foreach(DatabaseTable table in tables)
// {
// foreach(DatabaseColumn column in table.DatabaseColumns)
// {
// if(column.GenerationModeId == null)
// {
// GenerationMode mode = generationModes.Where(m => m.StereotypeId == column.StereotypeId).FirstOrDefault();
// if(mode == null)
// {
// return BadRequest(new
// {
// error = true,
// message = $"No generation mode for stereotype id ${column.StereotypeId}"
// });
// }
// column.GenerationModeId = mode.GenerationModeId;
// }
// }
// }
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}."
}));
}
DatabaseLoaderFactory dataHandlerFactory = new DatabaseLoaderFactory(connectionProps, connectionId, stereotypes, generationModes, mappings, ctx, request.Password);
IDatabaseLoader databaseHandler = dataHandlerFactory.GetLoader(databasePlatform.Name);
List <DatabaseTable> tables = await databaseHandler.GetTables();
return(Ok(new
{
database = connectionProps.Database,
host = connectionProps.Host,
username = connectionProps.Username,
tables = tables,
error = false
}));
//}
}
private static void BackupOrRestore(bool isBackup, ConfigPair storageConfig, ConfigPair databaseConfig, List <ConfigPair> pipelineConfig, IUpdateNotification updateNotifier)
{
string deviceSetName = Guid.NewGuid().ToString();
IBackupStorage storage = storageConfig.TransformationType.GetConstructor(new Type[0]).Invoke(new object[0]) as IBackupStorage;
IBackupDatabase databaseComp = databaseConfig.TransformationType.GetConstructor(new Type[0]).Invoke(new object[0]) as IBackupDatabase;
try
{
int numDevices = storage.GetNumberOfDevices(storageConfig.Parameters);
string instanceName = databaseComp.GetInstanceName(databaseConfig.Parameters);
string clusterNetworkName = databaseComp.GetClusterNetworkName(databaseConfig.Parameters);
SqlPlatform sqlPlatform = VirtualBackupDeviceFactory.DiscoverSqlPlatform(instanceName,
clusterNetworkName);
//NotifyWhenReady notifyWhenReady = new NotifyWhenReady(deviceName, isBackup);))
using (IVirtualDeviceSet deviceSet = VirtualBackupDeviceFactory.NewVirtualDeviceSet(sqlPlatform))
{
using (SqlThread sql = new SqlThread())
{
bool sqlStarted = false;
bool sqlFinished = false;
ParallelExecutionException exceptions = new ParallelExecutionException();
try
{
IStreamNotification streamNotification = new InternalStreamNotification(updateNotifier);
long estimatedTotalBytes;
List <string> deviceNames = sql.PreConnect(clusterNetworkName, instanceName, deviceSetName, numDevices, databaseComp, databaseConfig.Parameters, isBackup, updateNotifier, out estimatedTotalBytes);
using (DisposableList <Stream> fileStreams = new DisposableList <Stream>(isBackup ? storage.GetBackupWriter(storageConfig.Parameters) : storage.GetRestoreReader(storageConfig.Parameters, out estimatedTotalBytes)))
using (DisposableList <Stream> topOfPilelines = new DisposableList <Stream>(CreatePipeline(pipelineConfig, fileStreams, isBackup, streamNotification, estimatedTotalBytes)))
{
VirtualDeviceSetConfig config = new VirtualDeviceSetConfig();
config.Features = FeatureSet.PipeLike;
config.DeviceCount = (uint)topOfPilelines.Count;
deviceSet.CreateEx(instanceName, deviceSetName, config);
sql.BeginExecute();
sqlStarted = true;
deviceSet.GetConfiguration(TimeSpan.FromMinutes(1));
List <IVirtualDevice> devices = new List <IVirtualDevice>();
foreach (string devName in deviceNames)
{
devices.Add(deviceSet.OpenDevice(devName));
}
using (DisposableList <DeviceThread> threads = new DisposableList <DeviceThread>(devices.Count))
{
for (int i = 0; i < devices.Count; i++)
{
DeviceThread dThread = new DeviceThread();
threads.Add(dThread);
dThread.Initialize(isBackup, topOfPilelines[i], devices[i], deviceSet);
}
foreach (DeviceThread dThread in threads)
{
dThread.BeginCopy();
}
updateNotifier.OnStart();
//Console.WriteLine(string.Format("{0} started", isBackup ? "Backup" : "Restore"));
Exception sqlE = sql.EndExecute();
sqlFinished = true;
if (sqlE != null)
{
exceptions.Exceptions.Add(sqlE);
}
foreach (DeviceThread dThread in threads)
{
Exception devE = dThread.EndCopy();
if (devE != null)
{
exceptions.Exceptions.Add(devE);
}
}
}
}
}
catch (Exception e)
{
exceptions.Exceptions.Add(e);
}
finally
{
if (sqlStarted && !sqlFinished)
{
Exception sqlE = sql.EndExecute();
sqlFinished = true;
if (sqlE != null)
{
exceptions.Exceptions.Add(sqlE);
}
}
}
if (exceptions.HasExceptions)
{
throw exceptions;
}
}
}
}
catch
{
storage.CleanupOnAbort();
throw;
}
}
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"
}));
}
