Now You Don't Need To Deal With SQL

Imagine this: You want to create some new functionality that involves a new entity. You write your test and your entity in 30 seconds using active record and some Resharper templates. When you run your test, a new table is there for you to store your new entity and your test passes.

How is that possible? Code generators do this. More importantly, how is it maintainable? Most code generators start with the DB. I don't even want to look at the db when I'm flying through my solution with Resharper. Here's how to roll your own "maintainable db generation".

Dealing with the database is a pain when you're trying to develop something really fast. Active Record goes a long way for getting rid of 80% of the pain.

There are 3 things to consider in automating this:

1. What tables and columns exist in your schema right now?
2. What entities do you have in your domain right now?
3. How will this change to the db rolled out and then persisted?

1. The answer is obvious by running this query:

select table_name, column_name from INFORMATION_SCHEMA.Columns

This will give you all the columns and the tables they belong to in your schema. For dbs without the ability to query their own schemas, you can parse the version script or another artifact that reflects the schema.

2. The entities in your assembly that you are interested in derive from ActiveRecordBase. You can use reflection to find all of them:

Type[] types = type.Assembly.GetTypes();
foreach(Type type1 in types) {
object[] attributes = type1.GetCustomAttributes(false);
foreach(object attribute in attributes) {
if (attribute is ActiveRecordAttribute) {
Console.Out.WriteLine(type1.Name);
PropertyInfo[] properties = type1.GetProperties();
foreach(PropertyInfo propertyInfo in properties) {
object[] propertyAttributes = propertyInfo.GetCustomAttributes(false);
foreach(object propertyAttribute in propertyAttributes) {
if (propertyAttribute is PropertyAttribute) {
Console.Out.WriteLine("--" + propertyInfo.Name);
break;
} } } } } }


The above code is missing a lot, but it will get you started. You will need to check the types, nullability, BelongsTo, HasMany, many-to-many, nested and others.

3. Comparing the information from 1 and 2 you will get the additions to the entities that don't yet exist in the db yet. The differences can be mapped to the following addition to the version script - which may look something like this:

IF EXISTS (SELECT version from [dbo].[version] where version = 39)
BEGIN

ALTER TABLE [distributors] ADD [accountID] nvarchar(50) NULL
CREATE TABLE [dbo].[config](
[id] [int] IDENTITY(1,1) NOT NULL,
[configName] [varchar](50) NOT NULL,
[configDescription] [varchar](100) NOT NULL,
[configValue] [varchar](100) NOT NULL,
CONSTRAINT [PK_config] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

UPDATE [dbo].[version] SET version = 40 where version = 39
END
GO


The version script runs anytime the test project recompiles (the post compile event in the project properties).

I will have a working prototype in the next couple of days. I want to hit the low hanging fruit first. This will take care of basic additions for now. The alterations and other concerns will have to wait. I'll add them as the needs arise.

Let me know if you have done anything like this or have any comments or suggestions.