Exemplo n.º 1
0
        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
            }));
            //}
        }