public async Task DatabaseAbstractionTestAsync()
{
var queryDispatcher = new QueryDispatcher(new QueryExecutor(new CommandPreparer(), new ConnectionFactory(ConnectionString.Default)));
var readerCollection = new ReaderCollection(new[] { Reader.Create(new TestItemReader()) });
var database = new Database(queryDispatcher, readerCollection);
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS database_abstraction_test;",
"CREATE TABLE database_abstraction_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO database_abstraction_test VALUES (1, 'James');",
"INSERT INTO database_abstraction_test VALUES (2, 'Madison');",
"INSERT INTO database_abstraction_test VALUES (3, 'University');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var item =
await database
.CreateQuery(@"
SELECT id, value
FROM database_abstraction_test
WHERE id = :id;" )
.SetParameter("id", 1)
.FirstAsync <TestItem>();
Assert.NotNull(item);
Assert.Equal(item.Id, 1);
Assert.Equal(item.Value, "James");
}
public async Task AnyQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS any_query_test;",
"CREATE TABLE any_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO any_query_test VALUES (1, 'This');",
"INSERT INTO any_query_test VALUES (2, 'is');",
"INSERT INTO any_query_test VALUES (3, 'a');",
"INSERT INTO any_query_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var listQuery = "SELECT id, value FROM any_query_test;";
var isAny = await queryDispatcher.AnyAsync(new Query(listQuery));
Assert.True(isAny);
}
public async Task CountQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var arrangeQueries = new String[]
{
"DROP TABLE IF EXISTS count_query_test;",
"CREATE TABLE count_query_test (value TEXT NOT NULL);",
"INSERT INTO count_query_test VALUES ('Sigma');",
"INSERT INTO count_query_test VALUES ('Nu');",
"INSERT INTO count_query_test VALUES ('Iota');",
"INSERT INTO count_query_test VALUES ('Delta');",
"INSERT INTO count_query_test VALUES ('697');",
};
foreach (var arrangeQuery in arrangeQueries)
{
await queryDispatcher.ExecuteAsync(new Query(arrangeQuery));
}
var countQuery = "SELECT COUNT(*) FROM count_query_test;";
var count = await queryDispatcher.ExecuteScalarAsync <Int64>(new Query(countQuery));
Assert.Equal(count, 5);
}
public async Task FirstQueryThrowsIfEmptyTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS first_query_throws_if_empty_test;",
"CREATE TABLE first_query_throws_if_empty_test (id INT NOT NULL, value TEXT NOT NULL);"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var listQuery = @"
SELECT id, value
FROM first_query_throws_if_empty_test;" ;
await Assert.ThrowsAsync <InvalidOperationException>(async() =>
{
await queryDispatcher.FirstAsync(new Query(listQuery), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
});
}
public async Task EmptyListQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS empty_list_query_test;",
"CREATE TABLE empty_list_query_test (id INT NOT NULL, value TEXT NOT NULL);"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var listQuery = "SELECT id, value FROM empty_list_query_test;";
var items = await queryDispatcher.ListAsync(new Query(listQuery), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
Assert.NotNull(items);
Assert.Empty(items);
}
public async Task InsertQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var queries = new String[]
{
"DROP TABLE IF EXISTS insert_query_test;",
"CREATE TABLE insert_query_test (value TEXT NOT NULL);",
"INSERT INTO insert_query_test VALUES ('StraightSql');"
};
foreach (var query in queries)
{
await queryDispatcher.ExecuteAsync(new Query(query));
}
var countQuery = "SELECT COUNT(*) FROM insert_query_test;";
var count = await queryDispatcher.CountAsync(new Query(countQuery));
Assert.Equal(count, 1);
}
public async Task ExecuteAsync_WhenQueryHandlerIsNotRegistered_Throws()
{
var serviceLocator = Substitute.For <IServiceProvider>();
var queryProcessor = new QueryDispatcher(serviceLocator);
var exception = await Assert.ThrowsAsync <QueryHandlerNotRegisteredException>(
() => queryProcessor.ExecuteAsync(new TestQuery("1")));
exception.Query.Should().BeOfType <TestQuery>();
}
public async Task ExecuteAsync_WhenQueryIsNull_Throws()
{
var serviceLocator = Substitute.For <IServiceProvider>();
var queryProcessor = new QueryDispatcher(serviceLocator);
var exception = await Assert.ThrowsAsync <ArgumentNullException>(
() => queryProcessor.ExecuteAsync(null));
exception.Should().NotBeNull();
}
public async Task ExecuteAsync_WhenSecuredHandlerIsRegistered_ExecutesHandler()
{
var handler = new TestSecuredQueryHandler();
var serviceLocator = Substitute.For <IServiceProvider>();
serviceLocator.GetService(typeof(IQueryHandler <TestQuery, Result>))
.Returns(handler);
var queryProcessor = new QueryDispatcher(serviceLocator);
var result = await queryProcessor.ExecuteAsync(new TestQuery("1"));
result.Should().Be(new Result("1"));
}
public async Task ListQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS list_query_test;",
"CREATE TABLE list_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO list_query_test VALUES (1, 'This');",
"INSERT INTO list_query_test VALUES (2, 'is');",
"INSERT INTO list_query_test VALUES (3, 'a');",
"INSERT INTO list_query_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var listQuery = "SELECT id, value FROM list_query_test;";
var items = await queryDispatcher.ListAsync(new Query(listQuery), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
Assert.NotNull(items);
Assert.NotEmpty(items);
Assert.Equal(items.Count, 4);
Assert.Equal(items[0].id, 1);
Assert.Equal(items[1].id, 2);
Assert.Equal(items[2].id, 3);
Assert.Equal(items[3].id, 4);
Assert.Equal(items[0].value, "This");
Assert.Equal(items[1].value, "is");
Assert.Equal(items[2].value, "a");
Assert.Equal(items[3].value, "test");
}
public async Task ContextualizedQueryBuilderTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS contextualized_query_builder_query_test;",
"CREATE TABLE contextualized_query_builder_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO contextualized_query_builder_query_test VALUES (1, 'James');",
"INSERT INTO contextualized_query_builder_query_test VALUES (2, 'Frank');",
"INSERT INTO contextualized_query_builder_query_test VALUES (3, 'Hopkins');",
"INSERT INTO contextualized_query_builder_query_test VALUES (4, 'Greenfield');",
"INSERT INTO contextualized_query_builder_query_test VALUES (5, 'Quarles');",
"INSERT INTO contextualized_query_builder_query_test VALUES (6, 'James');",
"INSERT INTO contextualized_query_builder_query_test VALUES (7, 'McIlvaine');",
"INSERT INTO contextualized_query_builder_query_test VALUES (8, 'Riley');",
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var readerCollection = new ReaderCollection(new[]
{
Reader.Create(new TestItemReader())
});
var contextualizedQueryBuilder = new ContextualizedQueryBuilder(queryDispatcher, readerCollection);
var item =
await contextualizedQueryBuilder
.SetQuery(@"
SELECT id, value
FROM contextualized_query_builder_query_test
WHERE value = :value;" )
.SetParameter("value", "Hopkins")
.Build()
.FirstAsync <TestItem>();
Assert.NotNull(item);
Assert.Equal(item.Id, 3);
Assert.Equal(item.Value, "Hopkins");
}
public async Task ParameterizedQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS parameterized_query_test;",
"CREATE TABLE parameterized_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO parameterized_query_test VALUES (1, 'This');",
"INSERT INTO parameterized_query_test VALUES (2, 'is');",
"INSERT INTO parameterized_query_test VALUES (3, 'a');",
"INSERT INTO parameterized_query_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var query = @"
SELECT id, value
FROM parameterized_query_test
WHERE id > :id
ORDER BY id ASC;" ;
var parameters = new NpgsqlParameter[]
{
new NpgsqlParameter("id", 1)
};
var item = await queryDispatcher.FirstAsync(new Query(query, new Dictionary <String, String>(), parameters), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
Assert.NotNull(item);
Assert.Equal(item.id, 2);
Assert.Equal(item.value, "is");
}
public async Task QueryBuilderTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS query_builder_query_test;",
"CREATE TABLE query_builder_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO query_builder_query_test VALUES (1, 'This');",
"INSERT INTO query_builder_query_test VALUES (2, 'is');",
"INSERT INTO query_builder_query_test VALUES (3, 'a');",
"INSERT INTO query_builder_query_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var query =
new QueryBuilder()
.SetQuery(@"
SELECT id, value
FROM query_builder_query_test
WHERE id > :id
ORDER BY id ASC;" )
.SetParameter("id", 3)
.Build();
var item = await queryDispatcher.FirstAsync(query, row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
Assert.NotNull(item);
Assert.Equal(item.id, 4);
Assert.Equal(item.value, "test");
}
public async Task ContextualizedQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS contextualized_query_test;",
"CREATE TABLE contextualized_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO contextualized_query_test VALUES (1, 'Sigma');",
"INSERT INTO contextualized_query_test VALUES (2, 'Nu');",
"INSERT INTO contextualized_query_test VALUES (3, 'Iota');",
"INSERT INTO contextualized_query_test VALUES (4, 'Delta');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var query =
new QueryBuilder()
.SetQuery(@"
SELECT id, value
FROM contextualized_query_test
WHERE value = :greekLetter;" )
.SetParameter("greekLetter", "Delta")
.Build();
var readerCollection = new ReaderCollection(new[]
{
Reader.Create(new TestItemReader())
});
var contextualizedQuery = new ContextualizedQuery(query, queryDispatcher, readerCollection);
var item = await contextualizedQuery.FirstAsync <TestItem>();
Assert.NotNull(item);
Assert.Equal(item.Id, 4);
Assert.Equal(item.Value, "Delta");
}
public async Task LiteralQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS literal_query_test;",
"CREATE TABLE literal_query_test (id INT NOT NULL);",
"INSERT INTO literal_query_test VALUES (1);",
"INSERT INTO literal_query_test VALUES (10);",
"INSERT INTO literal_query_test VALUES (5);",
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var query = @"
SELECT id
FROM literal_query_test
ORDER BY id :order" ;
var literals = new Dictionary <String, String>()
{
{ "order", "DESC" }
};
var item = await queryDispatcher.FirstAsync(new Query(query, literals, new NpgsqlParameter[0]), row =>
{
return(new
{
id = row.ReadInt32("id")
});
});
Assert.NotNull(item);
Assert.Equal(item.id, 10);
}
public async Task ReaderTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS reader_test;",
"CREATE TABLE reader_test (id INT NOT NULL, first_name TEXT NOT NULL, middle_name TEXT, last_name TEXT NOT NULL);",
"INSERT INTO reader_test VALUES (417, 'Brendan', 'James', 'Baker');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var readerCollection = new ReaderCollection(new[]
{
Reader.Create(new PersonReader())
});
var contextualizedQueryBuilder = new ContextualizedQueryBuilder(queryDispatcher, readerCollection);
var item =
await contextualizedQueryBuilder
.SetQuery(@"
SELECT id, first_name, middle_name, last_name
FROM reader_test
WHERE last_name = :last_name;" )
.SetParameter("last_name", "Baker")
.Build()
.SingleAsync <Person>();
Assert.NotNull(item);
Assert.Equal(item.Id, 417);
Assert.Equal(item.FirstName, "Brendan");
Assert.Equal(item.MiddleName, "James");
Assert.Equal(item.LastName, "Baker");
}
public async Task FirstQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS first_query_test;",
"CREATE TABLE first_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO first_query_test VALUES (1, 'This');",
"INSERT INTO first_query_test VALUES (2, 'is');",
"INSERT INTO first_query_test VALUES (3, 'a');",
"INSERT INTO first_query_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var listQuery = @"
SELECT id, value
FROM first_query_test
WHERE id > 2
ORDER BY id ASC;" ;
var item = await queryDispatcher.FirstAsync(new Query(listQuery), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
Assert.NotNull(item);
Assert.Equal(item.id, 3);
Assert.Equal(item.value, "a");
}
public async Task SingleQueryThrowsIfMultipleTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS single_query_throws_if_multiple_test;",
"CREATE TABLE single_query_throws_if_multiple_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO single_query_throws_if_multiple_test VALUES (1, 'This');",
"INSERT INTO single_query_throws_if_multiple_test VALUES (2, 'is');",
"INSERT INTO single_query_throws_if_multiple_test VALUES (3, 'a');",
"INSERT INTO single_query_throws_if_multiple_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var listQuery = @"
SELECT id, value
FROM single_query_throws_if_multiple_test;" ;
await Assert.ThrowsAsync <InvalidOperationException>(async() =>
{
await queryDispatcher.SingleAsync(new Query(listQuery), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
});
}
public async Task SingleOrDefaultIsDefaultQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS single_or_default_is_default_query_test;",
"CREATE TABLE single_or_default_is_default_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO single_or_default_is_default_query_test VALUES (1, 'This');",
"INSERT INTO single_or_default_is_default_query_test VALUES (2, 'is');",
"INSERT INTO single_or_default_is_default_query_test VALUES (3, 'a');",
"INSERT INTO single_or_default_is_default_query_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var listQuery = @"
SELECT id, value
FROM single_or_default_is_default_query_test;" ;
var item = await queryDispatcher.SingleOrDefaultAsync(new Query(listQuery), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
Assert.Null(item);
}
