NAnt task to drop/create sqlite database from ActiveRecord

I really like to use ActiveRecord when building small apps or internal tools. It offers a lot of great features of his older brother NHibernate, without freightening everybody on your team.

I also really like the ability to generate the database schema based on the domain entities, so you don’t have to worry about SQL and DDL.
Combine that with SQLite, and you can develop on your local machine without an installed database server (like Sql Server). Makes it really easy  to setup a new development machine (checkout the code, build and run – it should works).

Unfortunately, the only way to do it provided by the framework is on the application code. So you end with something like that in your initializing file:

ActiveRecordStarter.Initialize(typeof(ADomainEntity).Assembly, ActiveRecordSectionHandler.Instance);
#if DEBUG
    // ActiveRecordStarter.CreateSchema();
#end

The problems with this approach are multiples, just to name a few:

• you could potentially erase all your production data if you forget to comment line 3 (thus the #if DEBUG statement – just in case)
• it’s not very handy : each time you want to drop/create the schema, you have to uncomment the line, run the application, stop it, comment the line.

That’s why I created a NAnt target, allowing the previous code to never appear in the application. You just use the target on your local machine when you want to drop and create the schema.

A really cool feature of NAnt is the ability to use the script task: it allows you to use your favorite .Net language to code an inline task. In that case we use the ability to dynamically load the Domain Model assembly to feed the ActiveRecordStarter with the correct information.

<script language="C#">
  <references>
	<include name="System.Data.dll" />
	<include name="Dependencies\SQLite.NET-0.21\SQLite.NET.dll" />
	<include name="Dependencies\Castle-net-2.0-release-2007-9-20\Castle.Core.dll"/>
	<include name="Dependencies\Castle-net-2.0-release-2007-9-20\Castle.ActiveRecord.dll"/>
  </references>
  <imports>
	<import namespace="System.IO" />
	<import namespace="System.Collections" />
	<import namespace="System.Reflection" />
	<import namespace="Castle.ActiveRecord" />
	<import namespace="Castle.ActiveRecord.Framework.Config" />
  </imports>
  <code>
	<![CDATA[
		public static void ScriptMain(Project project) {
			string dbFile = project.Properties["db.sqlite.path"];
			string connectionString = string.Format("Data Source={0};Version=3;New=True", dbFile);
			string modelAssemblyFilePath = Path.Combine(
                                project.BaseDirectory,
                                @"PathToTheModelAssembly.dll"
                            );

			// Create DB and Schema
			IDictionary props = new Hashtable();
			props.Add("hibernate.connection.driver_class", "NHibernate.Driver.SQLiteDriver");
			props.Add("hibernate.dialect", "NHibernate.Dialect.SQLiteDialect");
			props.Add("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");
			props.Add("hibernate.connection.connection_string", connectionString);

			InPlaceConfigurationSource config = new InPlaceConfigurationSource();
			config.Add(typeof(ActiveRecordBase), props);

			project.Log(Level.Info, "Loading ActiveRecord configuration...");
			ActiveRecordStarter.Initialize(Assembly.LoadFrom(modelAssemblyFilePath), config);

			project.Log(Level.Info, "Dumping schema...");
			ActiveRecordStarter.CreateSchema();
		}
	  ]]>
  </code>
</script>

In a nutshell:

• lines 3-6: don’t forget to add the necessary dependencies, otherwise compilation will fail. I use SQLLite.Net library to connect to the sqlite database
• line 18: notice the ability to use a NAnt property to define the sqlite file name
• line 19: the “New=true” parameter will tell the provider to create the needed database file (you may need to add a task before this one to drop an existing file)
• line 20: we specify the .net assembly that contains the ActiveRecord domain
• line 26-33: we configure ActiveRecord using code, not a config file (much easier when using NAnt)

You can also define another target, this time to generate a .sql file containing the schema for Sql Server, for example:

<script language="C#">
  <references>
	<include name="System.Data.dll" />
	<include name="Dependencies\Castle-net-2.0-release-2007-9-20\Castle.Core.dll"/>
	<include name="Dependencies\Castle-net-2.0-release-2007-9-20\Castle.ActiveRecord.dll"/>
  </references>
  <imports>
	<import namespace="System.IO" />
	<import namespace="System.Collections" />
	<import namespace="System.Reflection" />
	<import namespace="Castle.ActiveRecord" />
	<import namespace="Castle.ActiveRecord.Framework.Config" />
  </imports>
  <code>
	<![CDATA[
		public static void ScriptMain(Project project) {
			string schemaFile = project.Properties["schema.file"];
			string modelAssemblyFilePath = Path.Combine(
                                project.BaseDirectory,
                                @"PathToTheModelAssembly.dll"
                            );

			// Create DB and Schema
			IDictionary props = new Hashtable();
			props.Add("hibernate.connection.driver_class", "NHibernate.Driver.SqlClientDriver");
			props.Add("hibernate.dialect", "NHibernate.Dialect.MsSql2005Dialect");
			props.Add("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");

			InPlaceConfigurationSource config = new InPlaceConfigurationSource();
			config.Add(typeof(ActiveRecordBase), props);

			project.Log(Level.Info, "Loading ActiveRecord configuration...");
			ActiveRecordStarter.Initialize(Assembly.LoadFrom(modelAssemblyFilePath), config);

			project.Log(Level.Info, "Creating file...");
			ActiveRecordStarter.GenerateCreationScripts(schemaFile);
		}
	  ]]>
  </code>
</script>

I believe a similar approach could be used in conjunction with NHibernate schema generator.