Beispiel #1
0
        static void Main(string[] args)
        {
            int result = 2;

            while (result == 2)
            {
                IGarageManager          garageManager      = new GarageManagerFactory().Create();
                IConsoleInputOutput     consoleInputOutput = new ConsoleInputOutput();
                IVehicleManager         vehicleManager     = VehicleManagerFactory.Create(garageManager);
                IVehicleParser          vehicleParser      = new VehicleParser();
                IGarageParser           garageParser       = new GarageParser();
                IFileInputOutput        file                   = new FileInputOutput();
                ICsvImporter            csvImporter            = new CsvImporter(file, vehicleParser);
                ICommandExecuterFactory commandExecuterFactory = new CommandExecuterFactory(vehicleManager, consoleInputOutput, vehicleParser, garageManager, garageParser, csvImporter, file);
                var commandLineParser = new CommandLineParser(consoleInputOutput, CommandDictionaryFactory.Create(), vehicleManager, commandExecuterFactory);
                while ((result = commandLineParser.ReadCommand()) == 1)
                {
                    ;
                }
                if (result == 2)
                {
                    consoleInputOutput.WriteInfo("Daten werden wiederhergestellt.");
                }
            }
        }
        public void ReadCommandTest_FindCarsByLicensePlateWasCalled()
        {
            bool isCalled           = false;
            var  consoleMock        = new ConsoleMock("select 1;", null, null);
            var  vehicleManagerMock = new vehicleManagerMock((s) =>
            {
                if (s == "1")
                {
                    isCalled = true;
                }
            });

            vehicleManagerMock.FindCarsByLicensePlateReturn = new Dictionary <Vehicle, ParkingPlaceOutput>();
            var factory = new CommandExecuterFactory(vehicleManagerMock, consoleMock, null, null, null, null, null);
            var sut     = new CommandLineParser(consoleMock, CommandDictionaryFactory.Create(), vehicleManagerMock, factory);

            sut.ReadCommand();

            Assert.IsTrue(isCalled);
        }
Beispiel #3
0
        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"
            }));
        }