While recuperating from massive quantities of turkey and trimmings, I began work on a CodeSmith custom schema provider for MySQL 5.0. 

As Josh frequently says, “Nerd alert! Nerd alert!”

The journey has been quite a learning experience so far.  Creating a custom schema provider requires that you create a class that implements the IDbSchemaProvider interface.  This interface exposes a couple of properties and several methods required to query definitions for all tables, views, stored procedures, keys, indexes, and so forth.  Since I’m targeting MySQL 5.0, I’m using the new INFORMATION_SCHEMA support wherever possible.  The provider also depends on the latest MySQL Connector/Net.

Download ChristianASPNet.MySQLSchemaProvider version 0.90

Instructions:

• Make sure you have MySQL Connector/Net 1.0.7 or later installed
• Make sure CodeSmith is not running
• Extract and copy the ChristianASPNet.MySQLSchemaProvider.dll assembly to /Program Files/CodeSmith/v3.0/SchemaProviders (or wherever you have CodeSmith installed)
  

The next time you run CodeSmith and create or modify a data source, MySQLSchemaProvider should now show up in the list of available Provider Types.

UPDATE (12/23/05): Apparently you will need CodeSmith version 3.1.4 or later to use the assembly in the download.  You may be able to use a previous version of CodeSmith 3.xx if you compile the source yourself.  Also, be sure to use the following format for your MySQL provider connection string.

Server=localhost;Port=3306;Database=databaseName;Uid=userName;Pwd=userPassword

UPDATE (12/29/05): Download the latest version 0.91.

For more nerdy details, read on...

I’ve also included a simple SchemaTest.cst template that I created as I was writing the custom provider.  It doesn’t do anything special or even demonstrate best practices.  It’s just an ugly test harness. I include it in case you are interested in having a quick way to query the objects in your MySQL database.

There are a few methods I have not been able to implement (yet).  GetCommandParameters() returns parameter definitions for a given stored procedure.  MySQL 5.0 INFORMATION_SCHEMA does not support the PARAMETERS table at this time.  I tried to use MySQL Connector/Net’s MySqlCommandBuilder.DeriveParameters() method, which is supposed to work in version 1.0.7, but threw a NULL reference exception in my testing.

GetCommandResultSchemas() returns the schema of the result sets that are output from a given stored procedure.  However, if the stored procedure requires parameters, then we’re back to the previous issue.

I also chose not to implement GetExtendedProperties(), since I don’t yet know what would be relevant to a MySQL developer.  I am a MySQL newbie, after all.

Happy templating!