Reseed not properly deleting data from database after a test

[BenjaminDagg]

With reseed and xunit the data inserted in the prepare database phase and data inserted during tests are not being deleted from the database when cleanup database is called at the end of the test. The record created during prepare database is not removed from the database after cleanup is called. If I call restore database in the dispose method the records are properly deleted after tests.

SeedActions

        {
            var deviceLogTable = new ObjectName("DeviceActivityLog");
            string deviceQuery = $"DELETE FROM [dbo].[DeviceActivityLog] where DeviceId = {TestData.DeviceId.ToString()}";

            var actions = _reseeder.Generate(
                dbConnection, SeedMode.Basic(
                    BasicInsertDefinition.Script(),
                    CleanupDefinition.Script(
                        CleanupMode.Delete(),
                        CleanupTarget.Including(
                            configure: c => c.IncludeTables(new ObjectName("DeviceActivityLog")),
                            customScripts: new[]
                            {
                                (deviceLogTable, deviceQuery)

                            }
                            
                        )),
                    DataProviders.Inline(builder => builder


                         //Add barcode
                         .AddEntities(
                            new Entity("DeviceActivityLog",
                            new[]
                            {
                                new Property("DeviceActivityLogId",TestData.DeviceActivityLogId.ToString()),
                                new Property("ActivityUnique",TestData.DeviceActivityUnique.ToString()),
                                new Property("Timestamp","2022-05-31 19:56:39.000"),
                                new Property("DeviceActivityTypeId","23"),
                                new Property("EntityId",TestData.LocationId.ToString()),
                                new Property("DeviceId",TestData.DeviceId.ToString()),
                                new Property("TransAmount","0.00"),
                                new Property("TicketPrice","0.00"),
                                new Property("ActivityLogFileName","220531195639"),
                                new Property("IsError","1"),
                                new Property("DrawerNumber",""),
                                new Property("ErrorMessage",""),
                            }
                          ))

                         .Build())));
            return actions;
        }

Base test class. Constructor gets called before each test where I call Restore Database.

[Collection("Database Collection")]
    public class BaseApiTest
    {
        protected readonly TestFixture fixture;
        protected readonly ITestOutputHelper testOutput;
        public DatabaseHelper databaseHelper;
        


        //constructor is called before each test
        public BaseApiTest(TestFixture _fixture, ITestOutputHelper testOutputHelper)
        {
            fixture = _fixture;
            testOutput = testOutputHelper;

            fixture.Databases.ForEach(db => db.RestoreDatabase());

        }



        //Returns authenticated HTTP client with  bearer token
        public HttpClient GetClient()
        {
            var client = fixture.Client;

            return client;
        }
    }

Test Fixture where I can Cleanup Database in dispose which is called after all tests have run.

public TestFixture()
        {
            Client = new HttpClient();

            TmsConnection = new SqlConnection(TmsConnectionString);
            TmsConnection.Open();
            DbTMS = new DatabaseHelperTMS(TmsConnection);

            LoggingConnection = new SqlConnection(LoggingConnectionString);
            LoggingConnection.Open();
            DbLogging = new DatabaseHelperLogging(LoggingConnection);
            
            Databases = new List<IDatabaseSeedManager>() { DbTMS, DbLogging };
            Databases.ForEach(db => db.PrepareDatabaseSeed());
        }


        //runs after class is created before any tests run
        //Get bearer token to authenticate client
        public async Task InitializeAsync()
        {
            await AuthenticateClient();
            
        }


        public async Task DisposeAsync()
        {
            
        }


        public void Dispose()
        {
            Databases.ForEach(db => db.CleanDatabase());
        }

[Remco4EF]

It's not so strange that data is not deleted.
All delete scripts are generated correctly. But only inserted to the Seedstage delete
AddCleanupActions. Not in CleanUpDb as well...

[Remco4EF]

I've added a simple fix to also delete data on cleanup.
#38

[uladz-zubrycki]

@BenjaminDagg Hello. I somehow haven't received any notifications from Github, sorry for that. Will take a look this week.

Initially this behavior is by design: no matter what data we have in db, each test will get what it needs as db is restored to initial state before test logic is executed. And this is totally sufficient for my personal use cases. I agree though that it's sort of counter-intuitive to keep any data in db after tests are done.

Could you please describe why it's important for you to delete this data, so that I have better context?

[Remco4EF]

Apparently i was in the same misperception as @BenjaminDagg.
Your readme shows

• Execute scripts to delete all the data and insert it again.
  And in the section Data cleanup as well.

What your're saying makes sense to me but i'm wondering why cleanup doesn't cleanup.
I'm using Reseed in e2e testing on a heavy data driven application. I want a clean database for testing. For that i'm using TestContainers. I do not want to create a new database for each test for performance considerations.
So tables A,B and C are identical for each test but D is either empty or has 1 or more records. D in several states causes test to be not isolated. We do not want that of course.

When testing a certain service, all tests for that service need the same base data to be present in the database.
But after testing each fact, other data might have been changed, wich should be deleted imho. so you can start the next test right away.

[uladz-zubrycki]

@Remco4EF Thank you for the response, appreciate that.

Your readme shows

Likely a bad naming issue, where "cleanup" term is present in both stage name as CleanupDatabase and data deletion step as CleanupDefinition (and related CleanupMode and CleanupTarget).

By design CleanupDatabase stage is the opposite for PrepareDatabase stage: it removes any db objects, which are needed for Reseed library internally.

I'm going to check and maybe reconsider this decision by adding data deletion to CleanupDatabase as well (what you already partially did in your PR).

---

So tables A,B and C are identical for each test but D is either empty or has 1 or more records. D in several states causes test to be not isolated. We do not want that of course.

Could you please provide some minimal example for that? I'm not sure I'm 100% following.

Since idea of this library is to actually guarantee that each test has same data and acts in isolation. This is achieved by describing the desired data state (in xml or inlined) to which db is restored by execution of deletion scripts followed by insertion.

[Remco4EF]

@uladz-zubrycki I Agree.. naming is key and that threw me in the wrong direction.
Like you indicated, we must guarantee each test starts with same data. Guaranteeing this by clearing tables before restoring data is one method.

regarding your question:
We have a material planning generator. It is fed with some parameters (A,B,C) per item. These are somewhat fixed.
Which means we could reuse the same database.
Planning is generated upon stock and forecast (D). This can vary. (e.g. 1 forecast, 2 forecasts etc).

I've solved this with a second reseeder instance / action scripts which fill table D.
That works for now.
The second instance is only used in applicable cases.

all in all... I understand this is an edge case for using Reseed.

[uladz-zubrycki]

@Remco4EF Could you still please provide a minimal example to look into and reproduce your issue?

A few runnable listings of Reseed configuration and data setup or a whole solution as an archive. Thanks again.