T4 Sql generator for .dbproj files

Sample.tt

<#@ template debug="True" language="C#" hostspecific="True" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Data.Entity.Design" #>
<#@ import namespace="System.Globalization" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data.Entity.Design.PluralizationServices" #>
<#@ output extension=".txt" #>
<# 
    DTE Dte; 
    var doMultiFile=true;
    //this relies on the nuget packages: T4EnvDte and T4MultiFile
#>
<#@ include file="MultipleOutputHelper.ttinclude" #>
<#@ include file="EnvDteHelper.ttinclude"#>
<#@ include file="SqlGeneration.ttinclude" #> 
<# 
    var manager = Manager.Create(Host, GenerationEnvironment);
    var projects = RecurseSolutionProjects(Dte); 

    var targetProjectName = "ApplicationDatabase";

    var toGen = new [] 
        {
            new TableInfo{
                Schema="Accounts",
                Name="Account",
                Columns = new []{
                    new ColumnInfo{ Name = "AccountID", Type = typeof(int), Attributes = new []{"identity","primary key"}},
                    new ColumnInfo{ Name = "AccountTypeId", Type = typeof(string), Length=50,
                        FKey= new FKeyInfo{Schema="Accounts",Table="AccountType",Column="AccountTypeId"},
                        GenerateReferenceTable=true,
                        ReferenceValuesWithComment = new []{"Patient", "Payer", "System", "ThirdParty"}.ToDictionary(f => f, f => (string)null),
                    },
                    new ColumnInfo{ Name = "Name", Type = typeof(string), Length=50},
                    CreateFKeyedColumn<int>("PayerID", new FKeyInfo{ Schema="dbo", Table="Payers" }, true),

                }
            },
            new TableInfo{
                Schema="Accounts",
                Name="JournalEntry",
                Columns = new []{
                    new ColumnInfo{ Name = "JournalEntryID", Type = typeof(int), Attributes = new []{"identity","primary key"}},
                    CreateFKeyedColumn<int>("CreditAccountID", new FKeyInfo{Schema="Accounts",Table="Account",Column="AccountID"}),
                    CreateFKeyedColumn<int>("DebitAccountID", new FKeyInfo{Schema="Accounts",Table="Account",Column="AccountID"}),
                    new ColumnInfo{ Name = "Amount", Type = typeof(decimal), Precision=8,Scale=2},
                    
                    CreateFKeyedColumn<int>("ChargeID", new FKeyInfo{Schema="dbo",Table="Charge"}, true),
                    CreateFKeyedColumn<int>("PaymentID", new FKeyInfo{Schema="dbo",Table="Payment"}, true),
                    CreateFKeyedColumn<int>("PaymentItemID", new FKeyInfo{Schema="Payments",Table="PaymentItem"}, true),
                    CreateFKeyedColumn<int>("AppointmentID", new FKeyInfo{Schema="dbo",Table="Appointments"}, true),

                    CreateUserIdColumn(null, true, "null to allow system inserts/adjustments that aren't done by a user"),
                    new ColumnInfo{
                        Name="Entered", Type = typeof(DateTime),
                    },
                    new ColumnInfo{
                        Name="Comments", Type = typeof(string),
                        UseMax=true,
                        AllowNull=true,
                    },
                }
            },
        };
#>
Main File Output
<# foreach(var proj in projects){ #>
<#= proj.Name#>
<# } #>
<#
	var targetProject = projects.First(p => p.Name==targetProjectName);
    var targetProjectFolder = Path.GetDirectoryName(targetProject.FullName);
    GenerateTablesAndReferenceTables(manager, targetProject, targetProjectFolder, toGen);
    manager.Process(doMultiFile); 
#>

SqlGenerator.ttinclude

<#+ 
    public class FKeyInfo
    {
        public string Schema {get;set;}
        public string Table{get;set;}
        public string Column{get;set;}
    }

    public class ColumnInfo 
	{
        public string Name{get;set;}
        public Type Type {get;set;}
        public int? Length {get;set;}
        public int? Precision{get;set;}
        public int? Scale{get;set;}
        public bool UseMax {get;set;}
        public bool AllowNull{get;set;}
        public IEnumerable<string> Attributes{get;set;}
        public FKeyInfo FKey {get;set;}
        public IEnumerable<string> Comments {get;set;}
        public bool GenerateReferenceTable {get;set;}
        public IDictionary<string,string> ReferenceValuesWithComment {get;set;}
    }

    public class TableInfo
	{
        public string Name{get;set;}
		public string Schema{get;set;}
        public IEnumerable<ColumnInfo> Columns {get;set;}
    }

    void GenerateTable(Manager manager, EnvDTE.Project targetProject, string targetProjectFolder, TableInfo ti)
    {
        var targetFilename = Path.Combine(targetProjectFolder,"Schema Objects", "Schemas", ti.Schema, "Tables", ti.Name + ".table.sql");
	    WriteLine("Generating into " + targetFilename + " for project " + targetProject.Name);
        manager.StartNewFile(targetFilename,targetProject); 
#>
-- Generated file, DO NOT edit directly
CREATE TABLE [<#=ti.Schema#>].[<#= ti.Name#>] (
<#+ 
        var i = 0;
        var columnCount= ti.Columns.Count();
        var hasCombinationPK = ti.Columns.Count(ci => ci.Attributes != null && ci.Attributes.Contains("primary key")) > 1;
foreach(var ci in ti.Columns)
{
            var fKey = FormatFKey(ti.Name,ci.Name,ci.FKey);
            
            var multipleComments = ci.Comments != null && ci.Comments.Count() > 1;
            if(multipleComments)
                WriteLine("\r\n" + string.Join("\r\n", ci.Comments.Select(c => "    -- " + c)));
            var comment = ci.Comments != null && ci.Comments.Count() == 1 ? " -- " + ci.Comments.First() : string.Empty;
            if(ci.ReferenceValuesWithComment != null && ci.ReferenceValuesWithComment.Any() && (multipleComments || ci.Comments == null || !ci.Comments.Any()))
                comment = " -- " + string.Join(",", ci.ReferenceValuesWithComment.Keys);
#>
    <#= ("[" + ci.Name + "]").PadRight(32, ' ') #><#= MapTypeToSql(ci.Type,ci.Length,ci.Precision,ci.Scale,ci.UseMax).PadRight(16,' ')#><#= FormatAttributes(ci.Attributes,hasCombinationPK,fKey,ci.AllowNull) #><#= i < columnCount - 1 || hasCombinationPK ? "," : string.Empty#><#= multipleComments? Environment.NewLine : string.Empty#><#= comment #>
<#+ 
            i++;
}

if(hasCombinationPK)
    WriteLine("CONSTRAINT PK_" + ti.Name + " PRIMARY KEY ("+ string.Join(",",ti.Columns.Where(ci => ci.Attributes != null && ci.Attributes.Contains("primary key")).Select(ci => ci.Name)) + ")" ); #>
);
<#+ manager.EndBlock();
    }

    void GenerateAccountingInserts(Manager manager, EnvDTE.Project targetProject, string targetProjectFolder, IEnumerable<TableInfo> tables, string targetRelativePath)
    {
    // generate reference data
        var toGen = tables.Where(t=> t.Columns.Any(c=> c.ReferenceValuesWithComment != null && c.ReferenceValuesWithComment.Any())).ToArray();
        if(!toGen.Any())
            return;
        var targetFilename = Path.Combine(targetProjectFolder,targetRelativePath);
        manager.StartNewFile(targetFilename, targetProject); #>

-- Generated file, DO NOT edit directly
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
PRINT 'Starting Accounting Synchronization'
GO
<#+
        foreach(var tbl in toGen)
        foreach(var column in tbl.Columns.Where(c => c.ReferenceValuesWithComment != null && c.ReferenceValuesWithComment.Any()).ToArray())
        {
                var schema = column.FKey.Schema;
                var table = column.FKey.Table;
                var columnName = column.FKey.Column ?? column.Name;
#>
---------------------------------------------------
PRINT 'Synchronizing [<#= schema #>.<#= table #>]'
WITH CTE AS
(
    SELECT [<#= columnName #>]
    FROM (VALUES
<#+     
            var i = 0;
            var valueCount = column.ReferenceValuesWithComment.Keys.Count();
            foreach(var v in column.ReferenceValuesWithComment.Keys){ #>
            ('<#= v.Replace("'","''") #>')<#= i < valueCount -1 ? "," : ")" #><#= column.ReferenceValuesWithComment[v] != null? " -- " + column.ReferenceValuesWithComment[v] : string.Empty #>
<#+                    
                i++;                            
            } #>
        AS SOURCE([<#= columnName  #>])
)
MERGE INTO [<#= schema #>].[<#= columnName  #>] AS TARGET
USING CTE
ON CTE.[<#= columnName #>] = TARGET.[<#= columnName #>]
WHEN NOT MATCHED BY TARGET THEN
    INSERT([<#= columnName #>])
    VALUES([<#= columnName #>]);

PRINT 'Done Synchronizing [<#= schema #>.<#= table #>]'
GO

<#+}
        manager.EndBlock();
    }

    public static string FormatAttributes(IEnumerable<string> attributes,bool hasCombinationPK, string fKey, bool allowNull)
    {
        var isPk = attributes != null && attributes.Contains("primary key");
        var needsStarter = allowNull || !isPk || hasCombinationPK;
        var starter = (allowNull ? "null" : needsStarter ? "not null" : string.Empty) + (needsStarter ? " " : string.Empty); 
        if (attributes == null) return starter + (fKey != null? " " + fKey : null);
        var attribs = starter + string.Join(" ", hasCombinationPK && attributes != null ? attributes.Except(new []{"primary key"}) : attributes);
        if(fKey == null)
            return attribs;

        return attribs + " " + fKey;
    }

	public static string MapTypeToSql(Type type,int? length, int? precision,int? scale, bool useMax)
	{
		if(type == typeof(int))
			return "int";
        if(type == typeof(string))
            return "varchar(" + (useMax?"MAX":length.ToString()) + ")";
        if(type == typeof(decimal))
            return "decimal" + (scale.HasValue&& precision.HasValue? "("+precision+","+scale+")" : string.Empty);
        if(type == typeof(DateTime))
            return "datetime";
        if(type == typeof(bool))
            return "bit";
		return type.Name;
	}

    public static string FormatFKey(string table, string column, FKeyInfo fKey)
    {
        if(fKey == null)
            return null;
        // sprintf "CONSTRAINT [FK_%s_%s_%s_%s] FOREIGN KEY ([%s]) REFERENCES [dbo].[%s] ([%s])" tableName targetColumn fkeyTable fkeyColumn targetColumn fkeyTable fkeyColumn
        var fKeyColumn = fKey.Column ?? column;
        return "CONSTRAINT [FK_"+ table + "_" + column + "_" + fKey.Table + "_" + fKeyColumn + "] FOREIGN KEY ([" + column + "]) REFERENCES [" + fKey.Schema + "].[" + fKey.Table + "] ([" + fKeyColumn + "])";
    }

    void GenerateTablesAndReferenceTables(Manager manager, Project targetProject, string targetProjectFolder, IEnumerable<TableInfo> toGen) 
    {
        foreach(var ti in toGen)
        { 

            GenerateTable(manager, targetProject, targetProjectFolder, ti);
    
            foreach(var childCi in ti.Columns.Where(ci => ci.GenerateReferenceTable))
            {
                    var pkeyColumn = new ColumnInfo{ Name=childCi.Name, Type = childCi.Type, Attributes = new []{ "primary key" }, Length= childCi.Length, UseMax= childCi.UseMax };
                    var columns = new []{ pkeyColumn};
                    var name = childCi.FKey.Table;
                    var table = new TableInfo{ Schema=childCi.FKey.Schema, Name=name,Columns=columns};
                    GenerateTable(manager, targetProject, targetProjectFolder, table);
            }
        }
    }

    public static ColumnInfo  CreateFKeyedColumn<T>(string name, FKeyInfo fkey)
    {
        return CreateFKeyedColumn<T>(name,fkey, false);
    }

    public static ColumnInfo CreateFKeyedColumn<T>(string name, FKeyInfo fkey, bool allowNull)
    {
        return CreateFKeyedColumn<T>(name, fkey, allowNull, null);
    }

    public static ColumnInfo CreateFKeyedColumn<T>(string name, FKeyInfo fkey, bool allowNull, string comment)
    {
        return new ColumnInfo{
            Name=name, Type = typeof(T),
            AllowNull=allowNull,
            FKey=fkey,
            Comments = comment != null? new[]{ comment} : null
        };
    }

    public static ColumnInfo CreatePatientIdColumn(string prefix, bool allowNull, string comment)
    {
        return CreateFKeyedColumn<int>(prefix + "PatientID", new FKeyInfo{ Schema="dbo", Table="Patients",Column="PatientID" }, allowNull, comment);
    }

    public static ColumnInfo CreateUserIdColumn(string prefix, bool allowNull, string comment)
    {
        return CreateFKeyedColumn<int>(prefix + "UserID", new FKeyInfo{ Schema="dbo", Table="Users",Column="UserID" }, allowNull, comment);
    }

    public static ColumnInfo MakeNullable50(string name)
    {
        return new ColumnInfo{ Name = name, Type = typeof(string), AllowNull = true, Length = 50 };
    }
#>