In my previous post I described creating a Codesmith template to generate SQL code to implement foreign key constraints in a SQLite database using triggers as SQL does not natively support the foreign key constraint when specifying the column during table creation.

After I had produced the template I started reading about T4 which is Text Template Transformation Toolkit. T4 is built in to Visual Studio, the obvious advantage being that you can use T4 in to Visual Studio 2005 and 2008 without having to install anything, though there are some pieces that you may choose to add to VS after you get going with T4.

Although T4 is built in I think you will find the whole T4 experience easier if you download the T4 editor from Claris, the free version adds syntax highlighting for T4 like this

 T4 template in Visual Studio

I would also suggest reading Oleg Sych’s posts as an introduction to T4 as they will really help you get started if you have not used T4 before. As you can see in the screen shot at its simplest form we can use a T4 template, the file extension TT is used for T4 templates, to produce another file. In this case we are using a TT file to produce an SQL file, the transformation process to produce the SQL file is triggered whenever you save the TT file or when you build the project. The resulting SQL text can be copied into the script that I use to generate my database.

Converting the Codesmith template was relatively straightforward. The main difference with my T4 template from the Codesmith template is that the layout is upside down in that the SQL template code is at the top and then the C# code and then the parameter definitions.

 

 

<#@ template language="C#" hostspecific="True" debug="True" #>
<#@ output extension="sql" #>

/* start triggers to support 
  CREATE TABLE <#= FullTableName() #>
  (
    <#= ttp_foreignKeyColumn #> INTEGER
    CONSTRAINT <#= ForeignKeyName("") #>
    REFERENCES <#= FullOtherTableName() #>
      (<#= ttp_otherKeyColumn #>),
  }
*/

-- Foreign Key Preventing insert
DROP TRIGGER IF EXISTS <#= ForeignKeyName("_ins") #>;
CREATE TRIGGER <#= ForeignKeyName("_ins") #>

... extra T4 template to generate SQL removed for readability ...

/* end triggers to support <%= ForeignKeyName("") %> */

<#+
  private string FullTableName()
  {
    return string.Format("[{0}]",ttp_tableForForeignKey);
  }

  private string FullOtherTableName()
  {
    return string.Format("[{0}]",ttp_otherTable);
  }

  private string ForeignKeyName(string strAppend)
  {
    return string.Format("fk_{0}_{1}_{2}_{3}{4}",
        ttp_tableForForeignKey,
        ttp_foreignKeyColumn,
        ttp_otherTable,
        ttp_otherKeyColumn,
        strAppend
      );
  }
#>

<#+
  string ttp_tableForForeignKey = "tab_table";
  string ttp_foreignKeyColumn = "oth_id";
  string ttp_otherTable = "oth_other_table";
  string ttp_otherKeyColumn = "id";
  bool ttp_cascadeDelete = false;
#>

You can download the complete set of templates both Codesmith and T4.

Using the templates are quite different, in Codesmith the parameters are specified in an XML file and either the UI is used or the command line to generate the SQL. In T4 the parameters are specified in C# (though I guess I could write a loader that would read them from an XML file) like this (the following is what is in books_simple.tt)

<#
	ttp_tableForForeignKey = "bok_books";
	ttp_foreignKeyColumn = "bok_aut_id";
	ttp_otherTable = "aut_author";
	ttp_otherKeyColumn = "aut_id";
	ttp_cascadeDelete = false;
#>
<#@ include file="sqlite_fk.tt" #>

Building the project causes the SQL to be generated. If you wanted then you can get a MSBUILD task to automate the T4 generation so you can add it into your existing build script. There is a command line version of the transformer in “C:\Program Files\Common Files\Microsoft Shared\TextTemplating” that you can call from script to generate the SQL.

This does work but I have to say that I found the primitive method of specifying parameters just be using variables at the bottom the the template to be cumbersome and fragile. A better solution was to download the T4Toolbox, from Oleg Sych, and create a reusable template with a structured parameter passing mechanism like this.

<#+
public class sqlite_fk_relationship
{
  public string TableForForeignKey { get; set; }
  public string ForeignKeyColumn { get; set; }
  public string OtherTable { get; set; }
  public string OtherKeyColumn { get; set; }
  public bool CascadeDelete { get; set; }

  public string FullTableName()
  {
    return string.Format("[{0}]",TableForForeignKey);
  }

  public string FullOtherTableName()
  {
    return string.Format("[{0}]",OtherTable);
  }

  public string ForeignKeyName(string strAppend)
  {
    return string.Format("fk_{0}_{1}_{2}_{3}{4}",
				TableForForeignKey,
				ForeignKeyColumn,
				OtherTable,
				OtherKeyColumn,
				strAppend);
  }
}

public class sqlite_fk_template : Template
{
  public sqlite_fk_relationship[] Relationships =
    new sqlite_fk_relationship[] {
		new sqlite_fk_relationship {
			TableForForeignKey = "tab_table",
			ForeignKeyColumn = "oth_id",
			OtherTable = "oth_other_table",
			OtherKeyColumn = "id",
			CascadeDelete = false
		}
	};

  protected override void RenderCore()
  {
  foreach (sqlite_fk_relationship Relationship in Relationships)
  {
#>
  /* start triggers to support 
  CREATE TABLE <#= Relationship.FullTableName() #>
  (
  <#= Relationship.ForeignKeyColumn #> INTEGER
    CONSTRAINT <#= Relationship.ForeignKeyName("") #>
    REFERENCES <#= Relationship.FullOtherTableName() #>
        (<#= Relationship.OtherKeyColumn #>),
  }
  */

-- Foreign Key Preventing insert
DROP TRIGGER IF EXISTS <#= Relationship.ForeignKeyName("_ins") #>;
CREATE TRIGGER <#= Relationship.ForeignKeyName("_ins") #>

... extra T4 template to generate SQL removed for readability .....

/* end triggers to support <#= Relationship.ForeignKeyName("") #> */

<#+
    }
  }
}
#>

This template can then be used like this

<#@ template language="C#v3.5" hostspecific="True" #>
<#@ output extension="sql" #>
<#@ include file="T4Toolbox.tt" #>
<#@ include file="sqlite_fk_template.tt" #>
<#
  sqlite_fk_template template = new sqlite_fk_template();

  sqlite_fk_relationship[] rels = new sqlite_fk_relationship[] {

        new sqlite_fk_relationship {
         TableForForeignKey = "bok_books",
         ForeignKeyColumn = "bok_aut_id",
         OtherTable = "aut_author",
         OtherKeyColumn = "aut_id",
         CascadeDelete = false
        },

        new sqlite_fk_relationship {
          TableForForeignKey = "bok_books",
          ForeignKeyColumn = "bok_pub_id",
          OtherTable = "pub_publisher",
          OtherKeyColumn = "pub_id",
          CascadeDelete = false
        },

        new sqlite_fk_relationship {
          TableForForeignKey = "bok_books",
          ForeignKeyColumn = "bok_bty_id",
          OtherTable = "bty_book_type",
          OtherKeyColumn = "bty_id",
          CascadeDelete = false
        },

        new sqlite_fk_relationship {
          TableForForeignKey = "bok_books",
          ForeignKeyColumn = "bok_rat_id",
          OtherTable = "rat_rating",
          OtherKeyColumn = "rat_id",
          CascadeDelete = false
        }

      };

  template.Relationships = rels;

  template.Render();
#>

The main points to note here are

  1. the template language needs to be C#v3.5 as we are making use of new language features such as automatic properties and object initializes.
  2. the file that contains the template (that is the class sqlite_fk_template that derives from Template in the T4Toolbox) is only ever used from another file and is not passed to the T4 generator on its own, hence it does not have a template directive at the top.
  3. the parameters to the template are now type safe structures and the template generates all the constraints for the whole database rather than just one constraint.
  4. In this instance we generate all the SQL into one file, if we wanted to we could easily generate each constraint into its own file, Oleg Sych has an example of how to do this.