Relational Databases Edit on GitHub

New for the Storyteller 4.2 release cycle is an addon library distributed via Nuget called Storyteller.RDBMS that adds recipes for driving and verifying the state of relational databases within your system under test. As of now, the Storyteller.RDBMS library comes out of the box for support for Sql Server and Postgresql, but you can add adapters for other database engines.

See also Jeremy's blog post A Concept for Integrated Database Testing within Storyteller for more information and a contextual sample for this add on. The complete samples for this add on are in the DatabaseSamples project within GitHub.

To get started, just install the Storyteller.RDBMS library to your Storyteller specification pojrect via Nuget. The next step is to write DatabaseFixture classes in your specification project to drive or check your database.

You have two varieties out of the box, the Postgresql version:

public abstract class PostgresqlFixture : DatabaseFixture
{
    public PostgresqlFixture() : base(new PostgresDialect())
    {
    }
}

or the Sql Server version:

public abstract class SqlServerFixture : DatabaseFixture
{
    protected SqlServerFixture() : base(new SqlServerDialect())
    {
    }
}

As part of your effort, one way or another you need to tell the current ISpecContext for the currently executing specification what the connection string is to the database you need to connect to. In the sample project, I did that with the little DatabaseExtension in the ISystem for that project:

public class DatabaseSystem : SimpleSystem
{
    public static readonly string ConnectionString = "Host=localhost;Port=5432;Database=postgres;Username=postgres";


    protected override void configureCellHandling(CellHandling handling)
    {
        // Add the DatabaseExtension just to let DatabaseFixture's connect
        // to the underlying database
        handling.Extensions.Add(new DatabaseExtension(ConnectionString));
    }

    // The Warmup() hook might be a good place to set up baseline data sets or database
    // state
    public override Task Warmup()
    {
        return Task.Factory.StartNew(async () =>
        {
            var stream = GetType().GetTypeInfo().Assembly.GetManifestResourceStream("DatabaseSamples.Postgres.sql");
            var sql = stream.ReadAllText();

            using (var conn = new NpgsqlConnection(ConnectionString))
            {
                await conn.OpenAsync();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;

                await cmd.ExecuteScalarAsync();
            }
        });


    }
}

Alternatively, if you need to connect to different databases within a specification suite, you can also set the connection string during tests like so:

public class SampleDbFixture : PostgresqlFixture
{
    public override void SetUp()
    {
        Context.ConnectionString("some connection string");
    }

    public override void TearDown()
    {
        // Clean up any active connections used by this Fixture
        Context.DisposeActiveCommandRunner();
    }
}

Do note however that the connection string is global for the

Connecting to other Database Engines

To connect to another database engine, you need two pieces. First, an implementation of the ISqlDialect interface:

public interface ISqlDialect
{
    IDbConnection NewConnection();

    ParameterCell ToParameterCell(Cell cell, ParameterDirection direction);

    long RowCount(IDbConnection conn, string dbObject);
}

You will probably need to implement a custom ParameterCell. Here's the version for Sql Server:

public class SqlServerParameterCell : ParameterCell
{
    public SqlServerParameterCell(Cell cell, string parameterName, ParameterDirection direction) : base(cell, parameterName, direction)
    {
    }

    protected override void configureParameter(IDbDataParameter param)
    {
        // Nothing to do here for Sql Server
    }

    public override string ReplaceWithCellInput(string format)
    {
        // A parameter named "foo" would be referred to as
        // @foo in sql commands
        return format.Replace("@" + Cell.Key, FormattedName());
    }
}

and the version for Postgresql is a bit more complicated:

public class NpgsqlParameterCell : ParameterCell
{
    public NpgsqlParameterCell(Cell cell, string parameterName, ParameterDirection direction, NpgsqlDbType? dbType = null)
        : base(cell, parameterName, direction)
    {
        NpgsqlDbType = dbType;
    }

    public NpgsqlDbType? NpgsqlDbType { get; }

    protected override void configureParameter(IDbDataParameter param)
    {
        if (NpgsqlDbType != null)
        {
            param.As<NpgsqlParameter>().NpgsqlDbType = NpgsqlDbType.Value;
        }
    }

    public override string ReplaceWithCellInput(string format)
    {
        return format.Replace(":" + Cell.Key, FormattedName());
    }
}

Calling Stored Procedures

To call a stored procedure from Storyteller, there's a little helper method to declare grammars that can execute a stored procedure with parameters and optionally check the single value result of the function. It does not yet support output parameters.

As an example, let's say you have this very small Postgresql database taken from the "Hilo" identity support in the Marten library:

DROP TABLE IF EXISTS public.mt_hilo CASCADE;
CREATE TABLE public.mt_hilo (
	entity_name			varchar CONSTRAINT pk_mt_hilo PRIMARY KEY,
	hi_value			bigint default 0
);

CREATE OR REPLACE FUNCTION public.mt_get_next_hi(entity varchar) RETURNS int AS $$
DECLARE
	current_value bigint;
	next_value bigint;
BEGIN
	select hi_value into current_value from public.mt_hilo where entity_name = entity;
	IF current_value is null THEN
		insert into public.mt_hilo (entity_name, hi_value) values (entity, 0);
		next_value := 0;
	ELSE
		next_value := current_value + 1;
		update public.mt_hilo set hi_value = next_value where entity_name = entity;
	END IF;

With that, we can write a grammar within a DatabaseFixture to exercise the mt_get_next_hi function and verify the results:

public IGrammarSource GetNextHi(string entity)
{
    return Sproc("mt_get_next_hi")
        // Specify how this grammar will appear within the Specification
        // HTML
        .Format("Get the next Hi value for entity {entity} should be {result}")

        // Do an assertion against the value returned from the function
        // as an integer
        .CheckResult<int>();
}

A couple things to note because this is definitely non-idiomatic .Net:

• You definitely have to return IGrammarSource from the method to tell Storyteller to build the underlying IGrammar using the signature of the method
• The method is only executed once when the Fixture class is first created in order to build out the runtime IGrammar
• The method parameters are only used to determine the name and type of the grammar cells, and that's why you never see the parameters used anywhere

Here's a specification that uses the GetNextHi grammar (and for the record, the CSS is a wee bit better in the real client):

You can also expose that grammar above in a table grammar like this:

public IGrammar GetNextHiTable()
{
    return this["GetNextHi"].AsTable("Retrieving the Next Hi Number");
}

Parameterized Sql

You can also use parameterized Sql instead of stored procedures in a very similar way like this:

[ExposeAsTable("Insert rows into mt_hilo")]
public IGrammarSource InsertRows(string entity, int hiValue)
{
    return Sql("insert into mt_hilo (entity_name, hi_value) values (:entity, :hiValue)");
}

The only real difference between Sql() and Sproc() is the ADO.Net CommandType on the underlying command.

Verifying Tabular Data

You can also utilize Storyteller's set verification functionality against database results. To check the results from a Sql statement in a set verification table, use the VerifyRows() syntax shown below:

public RowVerification CheckTheRows()
{
    // The Sql to be executed
    return VerifyRows("select entity_name, hi_value from mt_hilo")
        .Titled("The rows in mt_hilo should be")

        // Specify which fields in the result set to verify
        // and what the underlying .Net type is to better
        // establish equality
        .AddField<string>("entity_name")
        .AddField<long>("hi_value");

}

Used in a specification, that grammar looks like this:

VerifyRows() has additional options for calling stored procedures and forcing the set verification to consider the order of the results (it does not validate order by default).

If you need to pass parameters into the query, you can use the Parameter() method in the VerifyRows fluent interface like this:

public RowVerification HigherThan(int page)
{
    return VerifyRows("select entity_name from mt_hilo where hi_value > :page")
        .Titled("Entities past a certain page threshold")

        // This specifies how the sentence grammar that actually
        // executes the Sql statement will appear in the specification
        // html
        .FetchFormat("find entities where hi_value > {page}")

        // Specify which fields to verify by name and the .Net type
        // Storyteller should use to do a value comparison
        .AddField<string>("entity_name");
}

Behind the scenes this builds a paragraph grammar that has:

1. A sentence grammar that actually executes the Sql statement
2. A set verification grammar that validates the expected results from running the Sql statement

Used in a specification, the grammar above looks like this:

Finally, if you need to apply a parameter to the fetching Sql command that is not an input to the grammar, you have this syntax:

public RowVerification HigherThanPage()
{
    return VerifyRows("select entity_name from mt_hilo where hi_value > :page")
        .Titled("These entities have more")

        // This specifies how the command finds the value of the
        // page parameter in the sql statement
        .Parameter("page", c => c.State.Retrieve<int>())

        .AddField<string>("entity_name");
}

Used in a specification, it looks like this:

Checking that a Sql Statement Returns No Data

For a shorthand way of asserting that a table or view returns no rows, you can use this syntax to create a fact grammar:

public IGrammar NoRows()
{
    return NoRowsIn("There should be no rows in the mt_hilo table", "public.mt_hilo");
}

That grammar is used in this specification:

Transactions