Refactor MS SQL e2e test

[zroubalik]

Proposal

MS SQL e2e test is using image docker.io/cgillum/mssqlscalertest:latest for consumer/producer part of the test. We don't own the sources and the app is written in C#.

We should refactor the test to directly use Go library to talk to MS SQL (produce/consume load) , this way we can reduce the number of images needed for the test.

current implemenatton:

namespace MSSQLScalerTest
{
    using System;
    using System.ComponentModel;
    using System.Linq;
    using System.Threading;
    using Microsoft.Data.SqlClient;

    class Program
    {
        static int Main(string[] args)
        {
            Console.WriteLine($"Received arguments: [{string.Join(' ', args)}]");
            try
            {
                if (args.FirstOrDefault()?.ToLower() == "producer")
                {
                    Producer();
                }
                else if (args.FirstOrDefault()?.ToLower() == "consumer")
                {
                    Consumer();
                }
                else
                {
                    Console.Error.WriteLine("Expected either 'producer' or 'consumer' as a command-line argument.");
                    return -1;
                }
            }
            catch (ApplicationException e)
            {
                Console.Error.WriteLine($"ERROR: {e.Message}");
                return -2;
            }

            return 0;
        }

        static void Producer()
        {
            const int ItemCount = 10;

            using var connection = GetAndOpenConnection();

            Console.WriteLine($"Inserting {ItemCount} items into the 'tasks' table...");

            for (int i = 0; i < 10; i++)
            {
                SqlCommand insertCommand = connection.CreateCommand();
                insertCommand.CommandText = "INSERT INTO tasks ([status]) VALUES ('queued')";
                insertCommand.ExecuteNonQuery();
            }

            Console.WriteLine($"Inserting {ItemCount} records successfully!");
        }

        static void Consumer()
        {
            using SqlConnection connection = GetAndOpenConnection();
            bool waiting = false;

            while (true)
            {
                // Read and update the status in a single statement
                using SqlCommand getTaskCommand = connection.CreateCommand();
                getTaskCommand.CommandText = "UPDATE TOP (1) tasks SET [status] = 'running' OUTPUT inserted.[id] WHERE [status] = 'queued'";

                using SqlDataReader reader = getTaskCommand.ExecuteReader();
                if (reader.Read())
                {
                    waiting = false;

                    int id = (int)reader["id"];
                    Console.WriteLine($"Executing task {id}...");
                    reader.Close();

                    // Simulate work
                    Thread.Sleep(TimeSpan.FromSeconds(30));

                    using SqlCommand completeTaskCommand = connection.CreateCommand();
                    completeTaskCommand.CommandText = "UPDATE tasks SET [status] = 'complete' WHERE [id] = @id";
                    completeTaskCommand.Parameters.AddWithValue("@id", id);
                    completeTaskCommand.ExecuteNonQuery();
                }
                else
                {
                    if (!waiting)
                    {
                        Console.WriteLine($"Waiting for tasks to process...");
                    }

                    waiting = true;
                    Thread.Sleep(TimeSpan.FromSeconds(5));
                }
            }
        }

        static SqlConnection GetAndOpenConnection()
        {
            const string EnvVariableName = "SQL_CONNECTION_STRING";
            string connectionString = Environment.GetEnvironmentVariable(EnvVariableName);
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ApplicationException($"Could not find an environment variable named {EnvVariableName}!");
            }

            var connection = new SqlConnection(connectionString);

            Console.WriteLine($"Opening connection to '{connection.Database}' at '{connection.DataSource}'...");
            connection.Open();
            return connection;
        }
    }
}

Use-Case

No response

Anything else?

No response

[gkum99]

Hi @JorTurFer ,Hope you are doing great. Can you assign this issue to me.

[JorTurFer]

hey!
sure, thanks for contributing! ❤️

[gkum99]

Hi @JorTurFer ,i am unable to understand how to approach this issue. As i am new to open source. I have build keda inside vscode . As this is labelled as good first issue it means any beginner can solve it right? I just need little guidance for this issue.

[JorTurFer]

Hey @2022H1030014G ,
To do this issue, you don't need to build KEDA locally, basically this issue is for updating mssql e2e test because currently it's using a 3rd party image outside kedacore organization for enqueueing things in the database.

The idea is to figure out how to avoid the usage of that image, @zroubalik suggested using the Golang library for MSSQL but maybe it's not enough because the e2e test runner doesn't have access to the cluster services, so maybe using the mssql client inside the mssql server image it's worth to execute the needed operation.

You will need to deploy KEDA into your cluster and then try your changes in the mssql e2e test. You can deploy KEDA into your cluster just executing make deploy in the root folder (where Makefile is).