Finding the current DB type in a Mango plugin

I was recently burned because I wrote some SQL that was specific to MS SQL Server in one of my Mango plugins. It didn't occur to me at the time that my code might not work against other databases, because 99% of the time I have control of both my code and the DBMS that it runs against. That's not the case when writing a plugin and releasing it into the wild -- anything can happen. Now that I've figured out how to properly account for multiple database types inside a Mango plugin, I thought I would share that knowledge with you so you don't make the same mistake I did.

Mango's plugin API provides a couple of hooks into its core, which prove to be very useful here. Whether you're building your plugin by following the instructions on mangoblog.org, or by taking an existing plugin and modifying it to do what you need, you will have an Init function that takes 2 arguments: blogManager and preferencesManager. The BlogManager argument is your starting point for interacting with Mango and provides access to a series of other "managers" and "interfaces" that allow you to interact with Mango via the API instead of modifying the database directly -- ensuring the most possible backwards compatability when Mango changes.

Hint: You can dump an instance of blogManager to see available functions, then dump the return value of those functions to see what they return.

In your init function, you need to save a reference to the BlogManager so that you can use it later, when an event is dispatched:

Now that we have a reference to BlogManager, we need to use it when an event is dispatched to find out which database type the current user is running. To do that, we need to use the QueryInterface object, returned from blogManager.getQueryInterface(). This object has a method called getDBType() that will return a string describing the current DBMS. Mango supports MySQL and MS SQL Server 2000 and 2005.

DBMS	Returns
MS SQL Server 2000	mssql
MS SQL Server 2005 (Express)	mssql_2005
MySQL	mysql

Now that we know what values we can expect from this function, we can safely write custom SQL per potential database type. I've started writing a fail-safe default case as well; so that even if it's not the best way to go about things, at least the plugin will work. Here's some example code showing how to use the reference we saved to BlogManager to find out the database type, and then to use that to write separate SQL statements depending on the DB type, and a default.

Note that I'm not querying the database directly, I'm using the QueryAdapter's makeQuery() function to execute the SQL. This is considered the best practice. When I use a CFOutput or CFLoop to process the results of the query, I limit the rows using from="1" and to="#variables.intPopularPostCount#" so that even in the default case the correct number of rows is used.

Any questions?

Posted in Best Practices | Mango | 1 Response December 09 2008

An update for Mango-Lightbox2

As was recently pointed out to me, a bug surfaced where my Lightbox2 plugin wasn't making the necessary configuration change for the advanced tab of the TinyMCE hyperlink dialogue to display in recent versions of Mango.

The bug was basically that some of Mango's code had been moved to a new file. My plugin slightly modifies this code to add the advanced tab, but couldn't find it. I've asked Laura to consider making this some sort of configuration that can be accessed programatically, but for now editing the file will have to do.

The new version of the plugin addresses this bug and is available on the RIAForge project page (or my project page), and in the SVN Repository.

Posted in My projects | Mango | 4 Responses December 09 2008

A fix for my Mango Popular Posts Pod

Some people reported problems with my Mango Popular Posts Pod Plugin, and although I haven't verified this yet, I am almost certain I know what the problem is. In my test and production environments, I use SQL Server. In the query that looks up most-commented posts to display, I am using a SELECT TOP statement, which is not valid in all SQL DBMS'. Specifically, it's not valid in MySQL, which is a pretty common DBMS in use on the web. (If you aren't sure if you're using MySQL, and your hosting is pretty cheap, chances are good you are using it...)

I plan on chaning the plugin to work universally, but for now, here's a quick fix you can apply to the code yourself to get it working.

Open up Handler.cfc and change lines 147-152 from this:

to this:

That should get it running for you, in MySQL, for now. I will also look into Oracle and PostgreSQL when I implement a universal fix.

Sorry for the delay, I've been working on a pretty neat side-project that's been taking up almost every waking moment (and then some).

Posted in Mango | My projects | 2 Responses December 02 2008