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