Building a module for DotNetNuke is fun. It gives me a chance to code that my job doesn't give me anymore. I enjoy the creative part of building a module.
But I don't enjoy building the sql file.
DNN uses a special system for its sql files that allow for different database situations. The {databaseowner} and {objectqualifier} tags in a sql file allow for the conditions present in a variety of hosted sites. These tags can be defined in the web.config for DNN and will have the proper values injected when the module is installed.
To quote the DNN documentation:
And elsewhere:The objectQualifier attribute is used to control the prefix applied to every object in the SQL Server database. By default this is set to an empty string , so for instance the Portals table is created as Portals. If, for example, the objectQualifier was set to dnn_ then the Portals table would be created as dnn_Portalsand stored procedures etc that referenced the Portals table wold reference it as dnn_Portals. Setting this value is recommended, as it allows DotNetNuke to exist within an environment where multiple applications might need to use the same database.
In the case of Upgrades it is important to confirm that this value is consistent with the existent installation ie. once DotNetNuke is installed the objectqualifier cannot be changed.
The databaseOwner attribute is used to identify the user that ownsthe database objects. By default it is set to dbo and in most cases this attribute should be left this way. However, some SQL Server DBAs (in particular in Shared Hosting Environments) may not grant the user you identify in the connection string db_owner privileges. In this case, set the databaseOwner attribute to your login user.
It is a requirement to have these prefixes added to your module database scripts. These prefixes allow multiple DNN implementations exist within a single database. In addition, if your module tables need to perform joins with other tables in the DNN core structure, these prefixes need to be defined in order for those SQL statements to work.
The problem is that there is no nice and easy way to clean up your SQL files from Enterprise Manager to put these values in. I've dreamed of a nice regex that would do this but I haven't seen one. After playing around a bit today, I think that I've got the beginnings of one: \['dbo\]'\.\['('<key>[FP]K_)'('<table>\w*)\]' I usually run this in The Regulator as a Replace. I use {databaseowner}${key}{objectqualifier}${table} as the "Replace with" text.
This seems to work well for table creates/drops, FK or PK drops, and stored procedure drops/creates. It will even reformat table names within your stored procedures as long as you prefix them with dbo. when you create them. It has some problems with PK creates or default values for columns. I'll have to add that in the future. I don't think that this would be too hard and it would really make for a powerful regex script that will help me out a ton!