jbogard / respawn Goto Github PK

So was trying to use Respawn to run tests in an AWS RDS environment and everything works fine on my local, but every time we deployed our build and ran our tests we'd get the exception about circular dependencies. The problem is that in RDS when run this query:

select s.name, t.name
from sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.principal_id = '1'

It would come back empty; however, the query to get relationships would bring back data. The check for circular dependencies would then trigger.
In RDS, for whatever reason, the principal_id comes back NULL. Does that check need to be here, I got our build working without that check, but I don't rightly know the long term consequences. I can submit a PR, let me know.

I'm trying to connect to my mysql database using official adminer and mysql images from docker hub.

Here is my docker-compose.yml file configuration:

version: '3'
services:
  mysql:
    image: mysql
    restart: always
    volumes: 
      - mysql:/var/lib/mysql
    environment: 
      - MYSQL_ALLOW_EMPTY_PASSWORD= 1
      - MYSQL_DATABASE= db
    ports: 
      - 3306:3306
      - 33060:33060
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
    depends_on: 
      - mysql
volumes:
  mysql:

Whenever I want to login to the MySQL using Adminer I face with the following problem:

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

SQLSTATE[HY000] [2002] No such file or directory

Here is the inputs I've used trying to connect to MySQL from Adminer interface:

#first try
System: MySQL
Server: localhost
Username: root
Database: db

#second try
System: MySQL
Server: mysql #container-name
Username: root
Database: db

Why limit Respawn to only deleting data within tables? It seems like it'd be easy to have a flag that would tell a checkpoint to delete the tables instead of the data. This would work better for some of my projects where we use EF code first migrations to not only create tables, but insert and massage data as well.

For that matter, I have C# code in other projects that goes as far as to delete and recreate a database using a SQL create script... and still in other projects, I detach and reattach databases instead.

Have you considered taking Respawn to that level?

Hey @jbogard, you are always super busy but ...

I was wondering you might be able to roll in some of the PR's in here, there's a couple which look like useful additions to an already super-useful library :-)

I was wondering if there's been any thought put into being able to control the deletion of table data?

I currently have a case where i would like to keep certain rows in one table between checkpoint resets but I would still like any new rows to be removed. I could handle this by copy-pasting the SqlServerDbAdapter and tweak the BuildDeleteCommandText(GraphBuilder graph) method but it feels a bit clunky. Any thoughts/suggestions on this?

Currently the graph builder considers only the name of the FK when adding it to a HashSet, however (at least in MsSql) the name should be unique within the scope of parent table only. If multiple FK has the same name, the graph ordering can fail (the last FK with the same name wins, the others are not considered.)

According to your documentation when I create a Checkpoint I can return to that state when calling the Reset method. However when I read your code in the master branch it seems that you just delete everything in the tables concerned.

If that is the case I suggest you change the documentation.

If this was really about returning to the state at the checkpoint it would be really interesting. I suspect it could be done by Creating a wrapper around the DbConnection, like it is done with the StackExchange MiniProfiler and creating reverse commands of every insert/update and delete statement. It would require "selects" though in order to get the content of the rows before the change. For small databases an alternate approach could be to load the entire database into memory at the checkpoint and dumping it to the tables afterwards.

About your approach with reading Foreign Keys I am not sure a brute force approach is not just as fast or at least only slightly slower, I once did that with a Firebird database. Just deleting everything from all tables. Some would then throw exceptions, I would catch those and delete them again in a second run and so forth.

If not, is support for it planned?

I have a temporal table where both table and history table are in non-default (dbo) schema:

CREATE SCHEMA [TableSchema] AUTHORIZATION [dbo];
GO

CREATE SCHEMA [HistorySchema] AUTHORIZATION [dbo];
GO

create table TableSchema.Foo (
	Value [int] not null primary key clustered,
    ValidFrom datetime2 generated always as row start, 
    ValidTo datetime2 generated always as row end,
    period for system_time(ValidFrom, ValidTo)
) with (system_versioning = on (history_table = HistorySchema.FooHistory))
GO

If I try to reset the database:

var checkpoint = new Checkpoint();
checkpoint.CheckTemporalTables = true;
await checkpoint.Reset(_connection);

I get a SQL exception:

Cannot find the object "Foo" because it does not exist or you do not have permissions.

When passing a MySQL connection string with the port identifier we get the error:

Keyword not supported: 'port'

This happens even though I specified

_checkpoint = new Checkpoint() { DbAdapter = DbAdapter.MySql, TablesToIgnore = new [] { "__EFMigrationsHistory" } };

Heres how I fixed it

public class RespawnMySql : Checkpoint { public override async Task Reset(string connectionString) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { await connection.OpenAsync(); await base.Reset((DbConnection) connection); } } }

Hi,

I have tried and tried and tried. Your unittests works fine, the Adminer tool starts too, after running docker-compose up -d and accessing localhost:8080, but no matter what I do I cannot make Adminer access neither MySql nor PostGres.

Have you actually had the Adminer tool work in this configuration ?

For instance spying on your MySql unittests connectionstring I enter

Server = 127.0.0.1:8082
User = root
Password = testytest
Database = MySqlTests

But I get SQLSTATE[HY000] [2002] Connection refused - I even tried to disable my firewall.

As mentioned I have tried every other configuration known to man. Is it supposed to work ?

Evening,

I'm having some issues using Respawn due to the fact that Azure SQL is running on SQL Server Major Version 12.x.
The issue arises due to the utilizing of temporal tables (which apply to SQL Server 2016 (13.x+), Azure SQL Database & Azure SQL Managed Instance), and that we're using actual Az DBs in our build tests and not containerized DBs.

Setting CheckTemporalTables = true is unfortunately overridden in DoesDbSupportTemporalTables due to SQL version:

private bool DoesDbSupportsTemporalTables(DbConnection connection)
{
      if (! DbAdapter.SupportsTemporalTables) return false;
      
      const int SqlServer2016MajorBuildVersion = 13;
      var serverVersion = connection.ServerVersion;
      var serverVersionDetails = serverVersion.Split(new[] { "." }, StringSplitOptions.None);
      var versionNumber = int.Parse(serverVersionDetails[0]);
      return versionNumber >= SqlServer2016MajorBuildVersion;
}

I'm not sure what the preferred fix would be: create a AzureSqlServer DbAdapter or adding additional flow control logic to the DoesDbSupportsTemporalTables method?

I can tell that manually disabling ("ALTER TABLE base.XXX SET ( SYSTEM_VERSIONING = OFF )") prior to running my tests and then reenabling (ALTER TABLE base.XXX SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = base.XXXHistory)) works without any problems. Unfortunately these manual actions do defeats the purpose of automated testing and it alters the environment for the integration testing in a significant way.

Hoping somebody with more knowledge of the code is able to come with a good fix for this issue.

Hi,

There are now two SqlClient from some time now, see this link.

• The old : System.Data.SqlClient
• The new : Microsoft.Data.SqlClient

Now that most NetCore projects use Microsoft.Data.SqlClient this create warning MSB3277 with System.Data.SqlClient at build time, but it works at runtime.

Some library like Dapper or EntityFrameworkCore are agnostic to that and don't force to use one of them.
Respawn mainly use DbConnection that is referenced in System.Data.Common

The only place where the SqlClient is needed is to instantiate a SqlConnection from a connection string here.

So either this overload need to be removed in the next major version or the connection need to be initialized by reflection.

Hi,

I saw that 3 commits currently are in the master branch, but not yet available in the NuGet package.
This forces us to use a custom built dll and add it to the repo, which is really ugly.

Would it be possible to Release 3.0.2 with the reseed functionality.

Thanks in advance.

Hi, I'd like to know if anyone here is using Respawn for testes using "real world" databases.
What I mean by that is that Respawn seems to work only with simple schemas - where we don't have lots of relationships between entities.

Respawn examines the SQL metadata intelligently to build a deterministic order of tables to delete based on foreign key relationships between tables. It navigates these relationships to build a DELETE script starting with the tables with no relationships and moving inwards until all tables are accounted for.

For me, It doesn't seem that it's acting intelligently at all. When I try to restore the database after inserting Foo it calls me out about Bar and Baz:

There is a dependency involving the DB tables [...] and we can't safely build the list of tables to delete.

A simplistic sample of the schema would be:

class Foo 
{
  int? BarId { get; set;}
}

class Bar
{
  ICollection<Foo> Foos { get; set; }
}

class Baz
{
  int? Foo { get; set; }
}

Same as: #12. In those cases, what would be the solution?

As expected, Respawn cannot do anything without tables for reset.

However, it should fail gracefully and with a useful error message that is the case. Currently it throws
BeginExecuteNonQuery: CommandText property has not been initialized.

It is an unlikely scenario and I only found this due to an EF Core not finding the migrations assembly from the context of the project context.

I'll put together a PR soon for this.

Hi

Is there a way to persist the checkpoint (the database with a known set of data), similar to nDbUnit (but without any manual need to create a xsd like file).

if not, please consider this a feature request 👍

want to be able to check-in the persisted file, into source code along with component/service tests. this will allow for test runners (inc CI server, or tester) to use their own db (which may have no data or tables present)

Reset fails when clearing tables named with reserved words (e.g. User, Group) in combination when these tables are also temporal tables.

Generated SQL has syntax errors.

Hi Jimmy, and again: great lib :)

There seems to be an issue with PostgresDbAdapter.BuildReseedSql

The issue boils down to a mismatch between the C# & PgSQL table name concatenation:

Here's the GetFullName:

public string GetFullName(char quoteIdentifier) =>
            Schema == null
                ? $"{quoteIdentifier}{Name}{quoteIdentifier}"
                : $"{quoteIdentifier}{Schema}{quoteIdentifier}.{quoteIdentifier}{Name}{quoteIdentifier}";

//for table {"schema":"public","table": "a"}
var tableNames = string.Join(", ", tablesToDelete.Select(t => $"'{t.GetFullName(QuoteCharacter)}'"));
//Given quote char " yielding:'"public"."a"'

--Given schema:table 'public:a'
//....
where AND '""' || table_schema || '"".""' || table_name || '""' IN ({tableNames})
--Here the where resolves to '"public"".""a"' IN ({tableNames})

To test it run the following:

create table public.a (id int generated always as identity(start with 1 increment by 1), val int);
create table public.b (id serial, val int);

select
    isc.table_schema,
    isc.table_name,
    '"' || isc.table_schema || '"".""' || isc.table_name || '"' wrong_but_currently_implemented_format,
    '"' || isc.table_schema || '"."' || isc.table_name || '"' suggested_fix
FROM information_schema.columns isc
where isc.table_schema in('public')

I'd be happy to PR the quote-fix if you'd like!

/Tobias

The project have an implicit dependency to System.Data, which could easily be added to the nuspec file. The dependencies is then automatically fetched from nuget.

The actual dependency should be System.Data.Common

In newer projects (and .NET Core projects) you might not have a machine dependency to System.* assemblies anymore (and shouldn't)

Hello! Thanks for writing this tool, having written a bunch of DB-related tests in the past I know how difficult it is to set up and tear down the tables and this makes things so much easier.

I am using Respawn version 3.3.0 with the Postgres adapter.

I'm running into a problem where the checkpoint tries to delete some tables internal to Postgres. The relevant SQL lines are as follows:

TRUNCATE TABLE "information_schema"."sql_sizing_profiles",
               "information_schema"."sql_sizing",
               "information_schema"."sql_languages",
               "information_schema"."sql_implementation_info",
               "information_schema"."sql_features",
               "information_schema"."sql_packages",
               "pg_catalog"."pg_subscription_rel",
               "pg_catalog"."pg_publication_rel",
               "pg_catalog"."pg_publication",
               "pg_catalog"."pg_sequence",
               "pg_catalog"."pg_transform",
               "pg_catalog"."pg_range",
               "pg_catalog"."pg_partitioned_table",
               "pg_catalog"."pg_collation",
               "pg_catalog"."pg_shseclabel",
               "pg_catalog"."pg_seclabel",
               "pg_catalog"."pg_init_privs",
               "pg_catalog"."pg_default_acl",
               "pg_catalog"."pg_replication_origin",
               "pg_catalog"."pg_policy",
               "pg_catalog"."pg_foreign_table",
               "pg_catalog"."pg_foreign_data_wrapper",
               "pg_catalog"."pg_extension",
               "pg_catalog"."pg_ts_template",
               "pg_catalog"."pg_ts_parser",
               "pg_catalog"."pg_ts_dict",
               "pg_catalog"."pg_ts_config_map",
               "pg_catalog"."pg_ts_config",
               "pg_catalog"."pg_shdescription",
               "pg_catalog"."pg_shdepend",
               "pg_catalog"."pg_auth_members",
               "pg_catalog"."pg_pltemplate",
               "pg_catalog"."pg_tablespace",
               "pg_catalog"."pg_db_role_setting",
               "pg_catalog"."pg_database",
               "pg_catalog"."pg_depend",
               "pg_catalog"."pg_conversion",
               "pg_catalog"."pg_namespace",
               "pg_catalog"."pg_enum",
               "pg_catalog"."pg_cast",
               "pg_catalog"."pg_description",
               "pg_catalog"."pg_event_trigger",
               "pg_catalog"."pg_trigger",
               "pg_catalog"."pg_rewrite",
               "pg_catalog"."pg_statistic_ext",
               "pg_catalog"."pg_aggregate",
               "pg_catalog"."pg_largeobject_metadata",
               "pg_catalog"."pg_language",
               "pg_catalog"."pg_amproc",
               "pg_catalog"."pg_amop",
               "pg_catalog"."pg_am",
               "pg_catalog"."pg_opclass",
               "pg_catalog"."pg_opfamily",
               "pg_catalog"."pg_operator",
               "pg_catalog"."pg_index",
               "pg_catalog"."pg_inherits",
               "pg_catalog"."pg_constraint",
               "pg_catalog"."pg_attrdef",
               "pg_catalog"."pg_class",
               "pg_catalog"."pg_proc",
               "pg_catalog"."pg_attribute",
               "pg_catalog"."pg_subscription",
               -- Some of my tables in the lines following --
               "pg_catalog"."pg_foreign_server",
               -- Some more of my tables  --
               "pg_catalog"."pg_type",
               -- More of my tables afterwards-- CASCADE;

My calling code:

var checkpoint = new Checkpoint
{
    DbAdapter = DbAdapter.Postgres
};

using var connection = _dbContext.Database.GetDbConnection();
await connection.OpenAsync();
await checkpoint.Reset(connection);

Is there a configuration step I missed?

Thanks!

When following connection string

.\SQLEXPRESS;Database=MyDb;Integrated Security=SSPI

I am getting following error

Format of the initialization string does not conform to specification starting at index 0.

NB: My application is using identical connstring to access database without any problems

I have a 51-table database that takes 13.5 seconds to clear (on Windows, in a Linux Docker container I see similar results), even if it is empty already.

After inspecting the DeleteSql, I found the following:

ALTER TABLE "public"."table1" DISABLE TRIGGER ALL;
ALTER TABLE "public"."table2" DISABLE TRIGGER ALL;
truncate table "public"."table1" cascade;
truncate table "public"."table2" cascade;
ALTER TABLE "public"."table1" ENABLE TRIGGER ALL;
ALTER TABLE "public"."table2" ENABLE TRIGGER ALL;

After some experimentation with the Postgres docs (https://www.postgresql.org/docs/9.1/static/sql-truncate.html) at hand. I found that the following is much faster:

ALTER TABLE "public"."table1" DISABLE TRIGGER ALL;
ALTER TABLE "public"."table2" DISABLE TRIGGER ALL;
truncate table "public"."table1", truncate table "public"."table2";
ALTER TABLE "public"."table1" ENABLE TRIGGER ALL;
ALTER TABLE "public"."table2" ENABLE TRIGGER ALL;

The cascade option is not necessary in this case since the truncation of the tables is done in a single truncate statement.

It 'only' took 2.5 seconds, which is over 5 times faster!

I think it is still rather slow for a database with 51 tables that are already empty but at least it'd be a lot faster this way than it is at the moment.

Hi there,

I get this error when resetting the checkpoint : "There is a circular dependency between the DB tables and we can't safely build the list of tables to delete."

I have 2 tables that refer to each other but with different columns :

table users
table companies

A user has a FK with a company, and a company has a creator column, which is a FK with a user.

Both FK are nullable, so we can create a user without company, then create a company with the user as creator, then update the user with the company key.

Is it something Respawn does not support ?

I have digged into the SQL request generated by Respawn to get all the FK from the database. And I don't see any filter for nullable FK

Thanks for any help.

I may have just stepped into a (smallish) landmine. I have an application that relies on 2 databases (multi-tenancy): one for the central config and another for a tenant.

During my integration test fixture setup, I was trying to figure out why I was getting odd SqlExceptions when I called the Checkpoint.Reset(connString) twice but using different connection strings, telling me it couldn't find the tables to be deleted in my existing schema.

Turns out the DeleteSql string is stored/cached in the instance of Checkpoint once you call Reset(string), so if you have multiple different DBs to reset, you basically need separate instances of Checkpoint. This is not as intuitive to find out given that the Reset(string) API expects a connection string. My assumption was that since I am passing in a connection, it will analyze and rebuild the DeleteSql statement upon the call to Reset(). Not so....upon further review of the source. Lesson learned.

I know in the grand scheme of things, it's not a big deal and we can just have multiple Checkpoint instances, but this may be nice to mention in the README or if you REEEEEEALLY want to make this change, I'd recommend either moving the connection string parameter to the constructor of Checkpoint instead of the Reset(), or re-write the internals to support not "caching" the deletesql, or you can get fancy and introduce a dictionary of connection strings and tie deletesql stnts to each connection (like a "connection pool" for sql string of sorts).

I am a lazy dev, so I'd settle for an update to the README with a callout for the future me 😁

Test class using xunit, with a private static checkpoint for a sql server database:

private static Checkpoint checkpoint = new Checkpoint { TablesToIgnore = new[] { "schema_version" }, SchemasToExclude = new[] { "dbo" } };

In the test I call reset
[Fact] public async Task MyTest() { checkpoint.Reset("Data Source=localhost;Initial Catalog=Guides;Integrated Security=True;MultipleActiveResultSets=False;Min Pool Size=3;Application Name=Guides.Api"); . . .}

Reset command is deleting all records from the two tables in the 'guides' schema. Tables have a 1 to many relationship.

Expected only to delete records after checkpoint taken.

Behaviour occurred when using both 0.2.0 and 0.3.0 (preview)

Am I misusing the checkpoint?

Option for restarting auto incremented primary key for truncated tables.

I'm trying to use Respawn in our IntegrationTests.
I only need one table to be ignored. The rest can be wiped.

These are my checkpoint settings:

private static Checkpoint checkpointProduction = new Checkpoint()
            {
                DbAdapter = DbAdapter.SqlServer,
                TablesToIgnore = new string[] { "MaterialTypes" },
            };

When I start the tests everything is wiped, including the table in TablesToIgnore.
Am I doing something wrong, or didn't I understand the workings of TablesToIgnore?

Extra info:
[Production].[dbo].[MaterialTypes] is the complete name of the table.
I tried dbo.MaterialTypes, MaterialTypes, Production.dbo.MaterialTypes, [Production].[dbo].[MaterialTypes] and several other possibilities. It was always wiped.

Kind regards

Hi, I have finally gotten back to writing the Pull Request for Firebird. I was sidetracked for a long time by Adminer not playing along with Firebird. I finally succeeded with that, and have gotten around to coding the firebird adapter.

But I immediately started feeling a bit dirty :) Now I know it is probably a non-issue since Respawn is only meant to be used from Tests, but still I wonder why you have opened it up to SQL injection. I am talking about the BuildTableCommandText for instance. Where you build the SQL as a string.

How about returning SQL + Parameters instead - in a Tuple for instance ? Or will it be too cumbersome ?

Targeting .NET Standard 2.1 prevents Respawn from being used in .NET Framework, since .NET Standard 2.1 is only implemented on .NET Core 3.0 and later frameworks.

Maybe it would be a good idea to target .NET Standard 2.0 instead? This will make it possible for Respawn 5 to be used on .NET Framework too.

Only a few things would have to be changed in the code:

• await using should be using (because there's no IAsyncDisposable in .NET Standard 2.0
• A few methods are missing async versions and must use sync (e.g. connection.BeginTransactionAsync()
• HashCode class is not available, and so it's necessary to manually compute the hash of Schema and Name in the Table class

but since the asyncness of these things is probably not critical, I believe it could work. 🙂

(I'll submit a PR in a few seconds with these changes applied – feel free to reject it if you don't feel like accepting this "downgrade"*)

*) Yeah it's a "downgrade" in a sense, but I think it's a good thing to avoid narrowing the reach of the library.

Would it be possible to add functionality to allow specific relationships to be ignored, in order to allow Respawn to be used when the database schema contains circular table relationships? This would be useful in test cases where the relationships causing the circular dependencies aren't relevant.

Some considerations:

• How would the relationships be specified - by foreign key constraint name?
• When would the relationships be specified - at checkpoint creation or during reset?

Related:
https://jimmybogard.com/respawn-vs-sql-server-snapshots/#comment-3552291593

Hello!

I am setting up some tests in a similar paradigm as outlined by you in your article about subcutaneous tests. Calling Respawn's Reset is wiping my entire database.

The connection string that I use to create is the same I use to reset it and it's a SQL db.

[Theory, AutoData]
public async void Should_GetAnEventById(Create.Parameter eventParam)
{
    //Arrange
    var newCheckpoint = new Checkpoint();
    var eventId = await CreateEvent(eventParam);

    //Act
    var result = await Mediator.Send(new GetEventRequest(eventId));

    //Assert
    //Some asserts here
    await newCheckpoint.Reset(ConnectionString.Value.ConnectionString);
}

I expected the checkpoint to save a snapshot of the database, so to speak, and then be able to revert to it. Instead it is wiped entirely. The test passes.

Am I doing something wrong? Apologies if this is not a good avenue to ask this. Thanks for your time!

On both my main project, and a brand new project, I'm getting the following error when attempting to use Respawn:

System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
    at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, ref String keyname, ref String keyvalue)
   at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
   at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
   at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, ref DbConnectionOptions userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString, SqlCredential credential)
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
   at Respawn.Checkpoint.Reset(String nameOrConnectionString)
   at WebApplication1.Tests.UnitTest1.TestMethod1() in UnitTest1.cs: line 28

I was able to reproduce the problem with a brand new project which I have uploaded here to assist. https://app.box.com/s/fxv84233wn6pmuli0lho50vl9l3y6dik

The connection string in the example looks like this:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-WebApplication1-20150626084006;Integrated Security=True" providerName="System.Data.SqlClient"/>

</connectionStrings>

However, I have tried different connection strings, including a standard SQL connection string, and I always get the same result.

Please let me know if you need anything more from me to assist.

Thanks!

This join should also be filtering on: AND a.r_owner=b.owner
If you have the same constraint in two different schemas the query will return a relationship between the tables on different schemas. Adding a test for this would probably be a good idea, but I'm not too sure on how to test it.

As I am implementing the Firebird Adapter I have noticed that you disable all foreign keys in the other adapters, while deleting. I kind of thought that the point was to delete the tables in the correct order so you did not have to disable foreign keys ?

The problem is that in Firebird it is not possible to disable foreign keys. One can however delete them and recreate them.

But is it not better to delete tables in the correct order ?

We unfortunately have a large number of integration tests which recycle an ASP.NET application pool mid test for testing configuration changes. Can a Respawn Checkpoint be serialized or saved in some fashion to be used on the new process at the end of the test?

Here are the errors I get when trying to open the solution in VS2015:

...\Respawn\Respawn.csproj : error : The default XML namespace of the project must be the MSBuild XML namespace. If the project is authored in the MSBuild 2003 format, please add xmlns="http://schemas.microsoft.com/developer/msbuild/2003" to the element. If the project has been authored in the old 1.0 or 1.2 format, please convert it to MSBuild 2003 format. C:\Users\jmiller\Source\Repos\Respawn\Respawn\Respawn.csproj

...\Respawn.Tests\Respawn.Tests.csproj : error : The default XML namespace of the project must be the MSBuild XML namespace. If the project is authored in the MSBuild 2003 format, please add xmlns="http://schemas.microsoft.com/developer/msbuild/2003" to the element. If the project has been authored in the old 1.0 or 1.2 format, please convert it to MSBuild 2003 format. C:\Users\jmiller\Source\Repos\Respawn\Respawn.Tests\Respawn.Tests.csproj

Hey!
I've added Respawn in an existing acceptance test project that dropped, recreated and migrated the database between every test. (With some 600 tests, the time saved using Respawn is almost two hours)

But in this specific project, since the all the tables were dropped, all id-columns were reset between every test. I ran into all kinds of contraint-issues and even some of the asserts expected certain id values.

Instead of fixing hundreds of failing tests I did a DBCC CHECKIDENT reseed on all tables needed. After some issues with some unexpected behavior with that command, this actually works fine. And it's fast.
The result is that the database is truly in the same state after each checkpoint.Reset()

Anyone interested in this as feature in Respawn? I can repackage this into a pull request if anyone is. (Only for SQL-Server, now at least)

I can't use TablesToInclude property of Checkpoint class with NuGet package 3.1.0, but i can downloading source code and referencing directly Respawn.dll.
I try also package 3.0.1 and 3.0.0 without success.

Hey guys, curious if there's been any talk about adding an overload to the Reset() method to take in a dbcontext as opposed to a connection string.

Regardless, great library and appreciate the work!

As stated in the title.

using System;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Respawn;

public class Program
{
    public static async Task Main(string[] args)
    {
        var connection = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\bnn00\source\repos\ConsoleApp1\ConsoleApp1\Database1.mdf;Integrated Security=True";
        using var conn = new SqlConnection(connection);
        try
        {
            await conn.OpenAsync();
        }
        catch (Exception)
        {

            throw;
        }
        await checkpoint.Reset(conn);
        await conn.ExecuteAsync(@"insert into TableAAA (id) values (20)");
        var aa = conn.Query<int>(@"select id from TableAAA");
        aa.ToList().ForEach(e => Console.WriteLine(e));
    }

    private static Checkpoint checkpoint = new Checkpoint
    {
        TablesToIgnore = new[] { "TableAAA" }
    };
}

Exception Stack

System.InvalidOperationException
  HResult=0x80131509
  Message=BeginExecuteNonQuery: CommandText property has not been initialized
  Source=System.Data.SqlClient
  StackTrace: 
   at System.Data.SqlClient.SqlCommand.ValidateCommand(Boolean async, String method)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery(AsyncCallback callback, Object stateObject)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(Func`3 beginMethod, Func`2 endFunction, Action`1 endAction, Object state, TaskCreationOptions creationOptions)
   at System.Threading.Tasks.TaskFactory`1.FromAsync(Func`3 beginMethod, Func`2 endMethod, Object state)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Respawn.Checkpoint.<ExecuteDeleteSqlAsync>d__49.MoveNext() in C:\Users\bnn00\source\repos\ConsoleApp1\Respawn-master\Respawn\Checkpoint.cs:line 84
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Respawn.Checkpoint.<Reset>d__47.MoveNext() in C:\Users\bnn00\source\repos\ConsoleApp1\Respawn-master\Respawn\Checkpoint.cs:line 52
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Program.<Main>d__0.MoveNext() in C:\Users\bnn00\source\repos\ConsoleApp1\ConsoleApp1\Program.cs:line 27

Some friends and I started adding support for Oracle (link), we are almost done (3/4 tests already passing), but we hit a wall: you can't execute multiple commands via DbCommand in Oracle, this means we can't truncate multiple tables in one command, so the ExecuteDeleteSql fails for Oracle.

We can't add semi-colons to the command because it will throw an invalid character error (ORA-00911). From our research we couldn't find any way to avoid this and run multiple truncate statements. So now we want to know if you are interested in making some changes to make this work, as that would mean having something like a ExecuteDeleteSql that allowed multiple commands instead of just one.

Last October there was a merge for a PR from @vmarko-sowalabs which has not yet been reflected in a newer Nuget release. Is it possible to get a new Respawn release cut and available on Nuget which contains the commit from f29dfc0 (escaping table names in SQL Server for temporal table statements)?

I'm working on an experiment wherein I just need a list of table names in the correct order. Is that something that could b exposed by Checkpoint. I could possibly even create a PR for it if you gave me your suggestions.

Do you have any plans to make Respawn compatible with .NET Core 1.x?

Regards!

SQL server 2016 supports temporal tables. When calling Reset on Checkpoint results in error because you cannot directly delete rows from history table while SYSTEM_VERSIONING = ON.

Solution is in my opinion: add square brackets around schema and table name in the sql comment, like:

 -- reseed the identity only on tables that actually have had a value, otherwise next value will be off-by-one   			            
 -- https://stackoverflow.com/questions/472578/dbcc-checkident-sets-identity-to-0                                                      
    if (@DoReseed is not null)                                                                                                         
       SET @SQL = N'DBCC CHECKIDENT(''[' +  @Schema + '].[' + @TableName + ']'', RESEED, ' + Convert(varchar(max), @IdentityInitialSeedValue - 1) + ')' 
    else  

Repo link: https://github.com/Depact/Respawn-.Net-5-PostgreSQL-Api-Example
When I started I had some questions about what's right and what's wrong approach when use this package, so I'm open for feedback.

Hi, I have started work on the Firebird Adapter as we talked abut. But I run into problems already. First I want to test your docker-compose command to see if I can add Firebird via docker if it has been containerized. The docker-compose.yml references a tool called adminer on port 8080 - this is the standard port for RavenDB which I have installed. So I cannot run docker-compose up -d it fails on the adminer part. I don't know if this has to be 8080 ? If I change the port to 8079 I can run the docker-compose command. But I cannot access localhost:8079 in the browser like I expected.

Hi
I want use Respawn Checkpoint.Reset with PostgreSQL and get this error System.ArgumentException : Keyword not supported: 'port'.
my connection string Server=127.0.0.1;Port=5432;Database=GrodmanTestDb;UserId=postgres