In my spare time here in Baghdad I’ve been playing around more with SQLite, my favorite lightweight open-source database engine. I wanted to generate some data access objects using CodeSmith, but unfortunately CodeSmith can’t use the generic GetSchema() functionality in ADO.NET 2.0.

Instead, CodeSmith has its own chinsy provider model to expose schema information from a database system for the purposes of code generation based on that schema. It ships with SQL Server and ADOX support, and leaves the remaining database systems as exercises to an intrepid reader.

Thankfully, the schema provider model is open and documented, and the vendor encourages development and sharing of providers. There is an existing SQLite 3 provider but it has a few drawbacks:

• Uses the Finisar .NET wrapper on SQLite, which is not particularly well supported, and was written for .NET FW 1.1
• Doesn’t expose foreign key information, so templates which rely on entity relationship information (that is, almost all the templates I’m interested in) won’t work
• Isn’t written by me :)

So, over the last couple of afternoons, I threw together my own SQLite 3.x schema provider using the outstanding .NET 2.0-only SQLite wrapper, SQLite.NET. SQLite.NET does most of the heavy lifting with its GetSchema() support; all I had to do was translate it into CodeSmith’s schema object model, and voila!.

Note that I built this against the 15 Feb 06 release of SQLite.NET, 1.0.26.2. This version has a primary key reporting bug in it for which I have submitted a patch to the author. He’s incorporated the fix and tested it, but is holding back for a few days on updating the official binary release. So, if you download this within a few days of its posting date, you may want to use the CVS binaries for SQLite.NET.

The SVN repository for this project is:

 http://bonzo.celatrix.com/svn/projects/Sqlite3SchemaProvider/trunk

The code is released into the public domain; do with it what you will, but I hereby disclaim any and all liability for its misuse.

You can download a buddy build source or binary tarball based on my patched SQLite.NET 1.0.26.2, though I urge you to fetch the source from SVN and build it yourself. I built it with VS2k5 Pro and CodeSmith Pro 3.2.

To install, copy the Sqlite3SchemaProvider.dll and System.Data.SQLite.dll files from binary tarball or the output folder if you build yourself, and place them in the SchemaProviders folder in the CodeSmith install directory. Restart CodeSmith, and the SQLite3 Schema Provider should appear in the list of available providers.

Any problems email me: anelson - at - apocryph - org