ChinookDatabase.tt

<#@ template language="C#" debug="true" hostSpecific="true" #>
<#@ output extension=".txt" #>
<#@ include file="EF.Utility.CS.ttinclude"#>
<#@ include file="..\_T4Templates\Chinook.ttinclude"#>
<#@ include file="..\_T4Templates\Manager.ttinclude"#>
<#

var options = new 
{
	DatabaseName = "Chinook",
	PopulateDatabase = true,
	Version = DataSetHelper.GetVersionNumber(),
	EFVersion = new Version("2.0.0.0"),
	ModelFile = Path.Combine(Path.GetDirectoryName(Host.TemplateFile), @"..\Chinook.edmx"),
	DataSource = Path.Combine(Path.GetDirectoryName(Host.TemplateFile), @"_Xml\ChinookData.xml"),
	DdlStrategies = new IDdlStrategy[] 
							{
								new SqlServerStrategy { IsIdentityEnabled = false, Encoding = Encoding.Unicode },
								new SqlServerStrategy { IsIdentityEnabled = true, Encoding = Encoding.Unicode },
								new SqlServerCompactStrategy { IsIdentityEnabled = false },
								new SqlServerCompactStrategy { IsIdentityEnabled = true },
								new SqliteStrategy { IsIdentityEnabled = false },
								new SqliteStrategy { IsIdentityEnabled = true, PrimaryKeyDef = KeyDefinition.OnCreateTableColumn },
								new MySqlStrategy { IsIdentityEnabled = false },
								new MySqlStrategy { IsIdentityEnabled = true },
								new OracleStrategy { IsIdentityEnabled = false, Encoding = Encoding.UTF8 },
								new EffiProzStrategy { IsIdentityEnabled = false },
								new EffiProzStrategy { IsIdentityEnabled = true },
								new PostgreSqlStrategy { IsIdentityEnabled = false, Encoding = Encoding.Default },
								new Db2Strategy { IsIdentityEnabled = false, Encoding = Encoding.Default }
						   	},
	OutputFiles = new List<OutputFile>()
};


// Read the existing store item collection from the edmx file.
IList<EdmSchemaError> existingSsdlErrors;
var existingStore = EdmExtension.CreateStoreItemCollection(EdmHelper.GetSsdlFromEdmx(options.ModelFile), 
														   options.EFVersion,
														   out existingSsdlErrors);

var fileManager = Manager.Create(Host, GenerationEnvironment);

foreach (IDdlStrategy strategy in options.DdlStrategies)
{
	//************************************************************************
	// Start of SQL file.
	//************************************************************************
	var filename = GetFileName(strategy, strategy.ScriptFileExtension);
	fileManager.StartNewFile(filename, strategy.Encoding);

	var details = (strategy.IsIdentityEnabled ? "Auto incremented primary keys." : string.Empty);
	
	// Add the script file to the package list.
	options.OutputFiles.Add(new OutputFile { Name = filename, Package = strategy.Name, Description = "SQL script to create the Chinook database. " + details });

	// If there is also a database file, then add it to the package list as well.
	if (!string.IsNullOrEmpty(strategy.DatabaseFileExtension))
	{
		var dbfilename = GetFileName(strategy, strategy.DatabaseFileExtension);
		options.OutputFiles.Add(new OutputFile { Name = dbfilename, Package = strategy.Name, Description = "Chinook database file. " + details });
	}
#>

/*******************************************************************************
   Chinook Database - Version <#= options.Version #>
   Script: <#= filename #>
   Description: Creates and populates the Chinook database.
   DB Server: <#= strategy.Name #>
   Author: Luis Rocha
   License: http://www.codeplex.com/ChinookDatabase/license
********************************************************************************/

<#
	if (strategy.IsReCreateDatabaseEnabled)
	{
		var dropdb = strategy.WriteDropDatabase(options.DatabaseName);
		if (!string.IsNullOrEmpty(dropdb)) 
		{
#>
/*******************************************************************************
   Drop database if it exists
********************************************************************************/
<#= dropdb #>
<#= strategy.WriteExecuteCommand() #>

<#
		}
		
		var createdb = strategy.WriteCreateDatabase(options.DatabaseName);
		if (!string.IsNullOrEmpty(createdb))
		{
#>
/*******************************************************************************
   Create database
********************************************************************************/
<#= createdb #>
<#= strategy.WriteExecuteCommand() #>

<#
		}
		
		var usedb = strategy.WriteUseDatabase(options.DatabaseName);
		if (!string.IsNullOrEmpty(usedb))
		{
#>
<#= usedb #>
<#= strategy.WriteExecuteCommand() #>
<#
		}
	}
	else
	{
		// Cannot recreate the database, so we need to remove foreign keys and tables one by one.
#>
/*******************************************************************************
   Drop Foreign Keys Constraints
********************************************************************************/
<#		foreach (AssociationSet associationSet in existingStore.GetAllAssociationSets()) { #>
<#= strategy.WriteDropForeignKey(associationSet) #>
<#= strategy.WriteExecuteCommand() #>
<#	    } #>

/*******************************************************************************
   Drop Tables
********************************************************************************/
<# 		foreach (var entitySet in existingStore.GetAllEntitySets()) { #>
<#= strategy.WriteDropTable(entitySet) #>
<#= strategy.WriteExecuteCommand() #>
<#		} 
	}
#>

/*******************************************************************************
   Create Tables
********************************************************************************/
<#
	foreach (var entitySet in existingStore.GetAllEntitySets())
	{
		var tableName = strategy.GetFullyQualifiedName(entitySet.GetSchemaName(), entitySet.GetTableName());
#>
CREATE TABLE <#= tableName #>
(<#

    for (int i = 0; i < entitySet.ElementType.Properties.Count; i++)
    {
        var prop = entitySet.ElementType.Properties[i];#><#= ( i>0 ? "," : "") #>
    <#= strategy.WriteCreateColumn(prop, options.EFVersion) #><#
	}

	// Create Primary Key Constraint.
	var keys = from item in entitySet.ElementType.GetKeyProperties() select item;
	if (strategy.PrimaryKeyDef==KeyDefinition.OnCreateTableBottom ||
		(strategy.PrimaryKeyDef==KeyDefinition.OnCreateTableColumn && keys.Count() > 1))
	{
		string pkName = strategy.FormatName("PK_" + entitySet.GetTableName());#>,
    CONSTRAINT <#= pkName #> PRIMARY KEY <#= strategy.GetClustered(existingStore, entitySet.ElementType)#> (<#= strategy.GetColumns(keys, ',')#>)<#
	}
	
	// Create Foreign Key depending on the strategy.
	if (strategy.ForeignKeyDef==KeyDefinition.OnCreateTableBottom)
	{
        foreach (var item in from associationSet in existingStore.GetAllAssociationSets()
                             let constraint = associationSet.ElementType.ReferentialConstraints.Single()
                             let toEnd = associationSet.AssociationSetEnds.Where(a => a.CorrespondingAssociationEndMember == constraint.ToRole).Single()
                             where toEnd.EntitySet == entitySet
                             let refEntity = associationSet.AssociationSetEnds.Where(a => a.CorrespondingAssociationEndMember == constraint.FromRole).Single().EntitySet
                             select new {constraint, refEntity})
        {#>,
    FOREIGN KEY (<#= strategy.GetColumns(item.constraint.ToProperties, ',') #>) REFERENCES <#= strategy.GetFullyQualifiedName(item.refEntity.GetSchemaName(), item.refEntity.GetTableName()) #> (<#= strategy.GetColumns(item.constraint.FromProperties, ',') #>) 
		<#= strategy.WriteForeignKeyDeleteAction(item.constraint) #> <#= strategy.WriteForeignKeyUpdateAction(item.constraint) #><#
        }
	
	}
#>

);
<#= strategy.WriteExecuteCommand() #>
<#
	}	// foreach EntityType
#>


/*******************************************************************************
   Create Primary Key Unique Indexes
********************************************************************************/
<#
   foreach (var entitySet in existingStore.GetAllEntitySets())
   {
        string tableName = strategy.GetFullyQualifiedName(entitySet.GetSchemaName(), entitySet.GetTableName());
        string pkIndexName = strategy.FormatName("IPK_" + entitySet.GetTableName());
        
        // Create Primary Key Constraint.
        if (strategy.PrimaryKeyDef==KeyDefinition.OnAlterTable)
        {
            string pkName = strategy.FormatName("PK_" + entitySet.GetTableName());
#>
ALTER TABLE <#= tableName #> ADD CONSTRAINT <#= pkName #> PRIMARY KEY (<#= strategy.GetColumns(entitySet.ElementType.GetKeyProperties(), ',')#>);
<#= strategy.WriteExecuteCommand() #>
<#            
			// Create Primary Key Unique Constraint.
			if (strategy.IsIndexEnabled)
			{
#>
CREATE UNIQUE INDEX <#= pkIndexName #> ON <#= tableName #>(<#= strategy.GetColumns(entitySet.ElementType.GetKeyProperties(), ',')#>);
<#= strategy.WriteExecuteCommand() #>
<#
			}
        }
   }
#>

/*******************************************************************************
   Create Foreign Keys
********************************************************************************/
<#
    foreach (AssociationSet associationSet in existingStore.GetAllAssociationSets())
    {
        var constraint = associationSet.ElementType.ReferentialConstraints.Single();
        var toEnd = associationSet.AssociationSetEnds.Where(a => a.CorrespondingAssociationEndMember == constraint.ToRole).Single();
        var fromEnd = associationSet.AssociationSetEnds.Where(a => a.CorrespondingAssociationEndMember == constraint.FromRole).Single();
        var toTableName = strategy.GetFullyQualifiedName(toEnd.EntitySet.GetSchemaName(), toEnd.EntitySet.GetTableName());
        var fromTableName = strategy.GetFullyQualifiedName(toEnd.EntitySet.GetSchemaName(), fromEnd.EntitySet.GetTableName());
		var constraintName = strategy.GetForeignKeyConstraintName(constraint);
		var fkName = strategy.FormatName(constraintName);
		var ifkName = strategy.FormatName("I" + constraintName);
		
		if (strategy.ForeignKeyDef==KeyDefinition.OnAlterTable)
		{
#>
ALTER TABLE <#= toTableName #> ADD CONSTRAINT <#= fkName #>
    FOREIGN KEY (<#= strategy.GetColumns(constraint.ToProperties, ',') #>) REFERENCES <#= fromTableName #> (<#= strategy.GetColumns(constraint.FromProperties, ',') #>) <#= strategy.WriteForeignKeyDeleteAction(constraint) #> <#= strategy.WriteForeignKeyUpdateAction(constraint) #>;
<#= strategy.WriteExecuteCommand() #>
<#      
		} 
		
        // if the foreign keys are part of the primary key on the dependent end, then we should not add a constraint.
        if (strategy.IsIndexEnabled &&
			!toEnd.EntitySet.ElementType.GetKeyProperties().Take(constraint.ToProperties.Count()).OrderBy(r => r.Name).SequenceEqual(constraint.ToProperties.OrderBy(r => r.Name)))
        {
#>
CREATE INDEX <#= ifkName #> ON <#= toTableName #> (<#= strategy.GetColumns(constraint.ToProperties, ',') #>);
<#= strategy.WriteExecuteCommand() #>
<#      
        }
    }
#>

/*******************************************************************************
   Populate Tables
********************************************************************************/
<#
    var ds = new ChinookDataSet();
    ds.ReadXml(options.DataSource);
    var sbFields = new StringBuilder();
    var sbValues = new StringBuilder();
	char delimiter = ',';

	foreach (DataTable table in ds.Tables)
	{
		var entitySet = (from e in existingStore.GetAllEntitySets() where e.Name == table.TableName select e).FirstOrDefault();
		if (entitySet == null) continue;
		var tableName = strategy.GetFullyQualifiedName(entitySet.GetSchemaName(), entitySet.GetTableName());
#>
<#
        foreach (DataRow row in table.Rows)
        {
            sbFields.Length = 0;
            sbValues.Length = 0;
			
            foreach (DataColumn col in table.Columns)
            {
                string value = row[col.ColumnName].ToString();
                if ((col.AutoIncrement && strategy.IsIdentityEnabled) || value.Length==0) continue;
				
                if (col.DataType == typeof(DateTime))
                {
                    value = strategy.FormatDateValue(value);
                }
                else if (col.DataType == typeof(String))
                {
                    value = strategy.FormatStringValue(value);
                }
				
                sbValues.AppendFormat("{0}{1} ", value, delimiter);
                sbFields.AppendFormat("{0}{1} ", strategy.FormatName(col.ColumnName), delimiter);
            }

            var fields = sbFields.ToString().Trim().TrimEnd(delimiter);
            var values = sbValues.ToString().Trim().TrimEnd(delimiter);
#>
<#= string.Format("INSERT INTO {0} ({1}) VALUES ({2});", tableName, fields, values) #>
<#
        }	// foreach DataRow
#>

<#
    }	// foreach DataTable
#>
<#= strategy.WriteFinishCommit() #>
<#
	
	//************************************************************************
	// End of SQL file.
	//************************************************************************
	fileManager.EndBlock();
}	// foreach options.DdlStrategies

foreach (var strategy in options.DdlStrategies.Select(s=> new { s.Name, s.CommandLineFormat }).Distinct())
{
	//************************************************************************
	// Start of Create DB Batch file.
	//************************************************************************
	var filename = string.Format("Create{0}.bat", strategy.Name);
	
	if (!string.IsNullOrEmpty(strategy.CommandLineFormat)) 
	{
		options.OutputFiles.Add(new OutputFile { Name = filename, Package = strategy.Name, Description = "Batch file to create the Chinook database." });
    	fileManager.StartNewFile(filename, Encoding.ASCII);
#>
@echo off
echo Chinook Database Version <#= DataSetHelper.GetVersionNumber() #>
echo.

if "%1"=="" goto MENU
if not exist %1 goto ERROR

set SQLFILE=%1
goto RUNSQL

:ERROR
echo The file %1 does not exist.
echo.
goto END

:MENU
echo Options:
echo.
<# 	
		// Get all files of the same strategy.
		IList<IDdlStrategy> items = options.DdlStrategies.Where(s=>s.Name==strategy.Name).ToList();
		for(int i=0; i<items.Count; i++) 
		{ 
#>
echo <#= (i+1) #>. Run <#= GetFileName(items[i], items[i].ScriptFileExtension) #>
<# 		} #>
echo <#= items.Count+1 #>. Exit
echo.
choice /c 123
<# 		for(int i=0; i<items.Count; i++) { #>
if (%ERRORLEVEL%)==(<#= (i+1) #>) set SQLFILE=<#= GetFileName(items[i],items[i].ScriptFileExtension) #>
<# 		} #>
if (%ERRORLEVEL%)==(<#= items.Count+1 #>) goto END

:RUNSQL
echo.
echo Running %SQLFILE%...
<#= string.Format(strategy.CommandLineFormat, "%SQLFILE%") #>

:END
echo.
set SQLFILE=

<#
		//************************************************************************
		// End of Create DB Batch file.
		//************************************************************************
		fileManager.EndBlock();
	}
}	// foreach options.DdlStrategies

//************************************************************************
// Start of Package file.
//************************************************************************
fileManager.StartNewFile("Package.bat", Encoding.ASCII);
#>
@echo off
set rootDir=%1
if (%rootDir%)==() set rootDir=%cd%
if exist %rootDir%\Packages\. del %rootDir%\Packages\*.* /q
if not exist %rootDir%\Packages mkdir %rootDir%\Packages

<#
var allfiles = options.OutputFiles.Select(o => o.Name);
var completePackageFile = string.Format("ChinookDatabase{0}_CompleteVersion.zip", options.Version);
#>
"C:\Program Files\7-Zip\7z.exe" a %rootDir%\Packages<#=@"\" + completePackageFile#> <#=GetValues(allfiles, ' ')#> _Xml\ChinookData.xml
<#
foreach (var package in options.OutputFiles.Select(o=>o.Package).Distinct())
{
    var files = from item in options.OutputFiles where item.Package == package select item.Name;
#>
"C:\Program Files\7-Zip\7z.exe" a %rootDir%\Packages\ChinookDatabase<#=options.Version#>_<#=package#>.zip <#=GetValues(files, ' ')#>
<#
}
//************************************************************************
// End of Package file.
//************************************************************************
fileManager.EndBlock();
	
fileManager.Process(true);

// Add files that were not generated here, but will be part of the release.
options.OutputFiles.Add(new OutputFile { Name = "ChinookData.xml", 
										 Package = "XML", 
										 Description = "XML data used to generate the Chinook database SQL scripts." });#>
Chinook Database <#= options.Version #>
* This is a sample database available in multiple formats: SQL scripts for multiple database vendors, embeded database files, and XML format.
* The Chinook data model is available [here|Chinook_Schema].
* <#=completePackageFile#> is a complete package for all supported databases/data sources. There are also packages for each specific data source.
!! Supported Database Servers
* [url:DB2|http://www-01.ibm.com/software/data/db2/express/]
* [url:EffiProz|http://effiproz.codeplex.com/]
* [url:MySQL|http://www.mysql.com/]
* [url:Oracle|http://www.oracle.com/technetwork/database/express-edition/overview/index.html]
* [url:PostgreSQL|http://www.postgresql.org/]
* [url:SQL Server|http://www.microsoft.com/sqlserver/]
* [url:SQL Server Compact|http://www.microsoft.com/sqlserver/2008/en/us/compact.aspx]
* [url:SQLite|http://www.sqlite.org/]
!! Issues Resolved
* [workitem:29374]
* [workitem:29375]
* [workitem:29377]
* [workitem:29378]
!! Installation
* [How do I create the Chinook Database?|Create_Databases]
!! Package Content
This is the list of files included in the complete version.
|| Data Source || File || Description ||
<#	foreach (var file in options.OutputFiles.OrderBy(o=>o.Package)) { #>
| <#= file.Package #> | {"<#= file.Name #>"} | <#= file.Description #> |
<#	} #>
<#+ 

public class OutputFile {
	public string Name { get; set; }
	public string Package { get; set; }
	public string Description { get; set; }
}

private static string GetFileName(IDdlStrategy strategy, string extension)
{
	var suffix = (strategy.IsIdentityEnabled ? "_AutoIncrementPKs" : string.Empty);
	return string.Format("Chinook_{0}{1}.{2}", strategy.Name, suffix, extension);
}

private static string GetValues(IEnumerable<string> values, char delimiter)
{
    var builder = new StringBuilder();
    foreach (var value in values)
    {
        builder.AppendFormat("{0}{1}", value, delimiter);
    }
    return builder.ToString().TrimEnd(delimiter);
}

#>