sjp / schematic Goto Github PK

This should be enabled for all builds once OpenCover supports .NET Core test projects reliably on AppVeyor.

Additionally we need to fix up the test reporting on AppVeyor, it says that all tests have passed and always in 0ms when this is not the case.

This will enable us to process multiple errors rather than stopping at the first error.

Often there is more than one problem if there is one to begin with so this is a useful feature to have.

This should not be used for applying schema commands because they are strictly sequential and any error is probably serious (and should not be skipped).

Create providers for common implementations, e.g. log4net, serilog, etc.

Create a common interface to log across all providers. These should be set statically for configuring and enabling logging.

These tasks should include the following:

• Generation of schema declarations from an existing database.
• Generation of POCO classes for various ORMs, e.g. Dapper, ServiceStack.OrmLite, Entity Framework.
• A linter for schema that hints at improvements you could make. For example, adding a missing index on a foreign key. Configurability is key, as we want to be able to ignore, warn or error out on a lint message.
• Create a schema project.
• Update a schema project using an existing database.
• Apply a model to a database.

This must run in a plugin architecture, perhaps following the same behaviour as dotnet, or maybe even integrating with dotnet.

This feature will have to wait until Oracle adds support for .NET Core.

Additionally, there will be some extra complications that we will need to ensure work properly. For starters, we need to ensure that all identifiers are no more than 30 characters (and ensure these are in the right charset).

Furthermore, we should ensure that quoting is only performed when required. This is necessary because Oracle is case insensitive unless you quote using the " character, which makes writing ad-hoc queries more difficult and time-consuming. Code already exists to do this in the ServiceStack.OrmLite project, in OracleOrmLiteDialectProvider (see WillQuote()).

A problem that exists for some vendors is that some features only become available in more recent versions.

An example of this is PostgreSQL, where included columns for indexes are only available in v11, and some extra metadata for sequences is available in v10. This means that in order to introduce these features currently we break support for anything lower than the newest version.

A solution to this problem is to add a layer that retrieves a version-specific implementer. e.g. If you're on PostgreSQL v9.6, you get an object provider which supports that behaviour, and if you're on v11, you get an object provider which supports v11. The good thing is that this should only take one query to perform as these table/view/sequence/etc providers can be cached.

This would make things like comparing objects very simple.

For example, we could check that two primary key objects are the same using the following (option unwrapping omitted):

var primaryKeyA = tableA.PrimaryKey;
var primaryKeyB = tableB.PrimaryKey;

var keysEqual = primaryKeyA.Equals(primaryKeyB);
var keysEqualOp = primaryKeyA == primaryKeyB; // optional operator overload

Or even possibly entire tables (option unwrapping omitted):

var tableA = Database.GetTable("test_table_a");
var tableB = Database.GetTable("test_table_b");

var tablesEqual = tableA.Equals(tableB);
var tablesEqualOp = tableA == tableB; // optional operator overload

Comparing tables would have to dig through the entire structure of the tables, e.g. keys, indexes, columns, but otherwise would be very useful.

A possible downside to this is assuming core objects to be IEquatable<T>, meaning that unless implementers are using objects from the core project, then they will have to implement it themselves to get correct behaviour. A solution to this would be to add IEquatable<T> to the interface, but it feels to me like overkill or a mixing of concerns.

These are different from regular views, as the results of the view are cached and regularly kept up-to-date.

Compare this to regular views which are (more or less) just a compiled/parsed equivalent of an ad-hoc query.

In some providers (e.g. Oracle), materialized views store their data in user-visible tables. This means that being ignorant of materialized views can lead to tables being revealed to the user when (for schema purposes) they should be hidden.

In the process of creating this, the regular view objects can be cleaned up. They will no longer need index objects because those are required only for materialized views (as indexed views are SQL Server's way of saying materialized views).

Like what is currently supported for EF Core, Dapper and ServiceStack.OrmLite, we want to support bootstrapping NHibernate projects.

Create a test data generator.

This should be based on the schema that we already know, i.e. it will have knowledge of types, length, nullability.

The types of things that should be generated are names, credit cards, addresses, currency, etc.

There are generators like Bogus that might be useful in implementing this.

When .NET Standard 2.0 is available, use the GetSchema() methods on SqlConnection.

This will give us much better performance as it will greatly reduce the number of queries sent to the database, without losing any fidelity.

This should apply to other vendors too, not just SQL Server.

I would like to be able to create scripts so that if I wanted to, I can create a script per version of software without having to use the schema tooling.

This makes the scripting more accessible in environments where there are a limited number of upgraded or customised database environments.

This will enable the libraries to be used in more contexts without potentially blocking the thread.

For example, if we were to analyse schema in a GUI in a background task, ConfigureAwait(false) ensures that this does not block any message pumps.

Using an existing library for reflection should improve performance, primarily by caching.

Something like Fasterflect or FastMember, Sigil, etc