Implementing Database Changes

To make a database change to a DNN module, you need:

1. a SQL script, which is packaged as part of a xx.yy.zz.SqlDataProvider file. Depending on where we are in a release, there may be an existing .SqlDataProvider file if there are previous pull requests for the in-process version. Otherwise, you need to create an empty file. Don't get hung up on the xx.yy.zz versioning, as the project owners will review and adjust if needed.

   In the SqlDataProvider file, include the SQL commands required to update/implement the database change.

   Wrap in an "IF EXIST ..." statement to allow the script to be run repeatedly if the module is reinstalled.

   You need to be aware of the {databaseOwner} and {objectQualifier} substitution tokens within the file.

   • {databaseOwner} is substituted when the module is installed with the schema + ".", so typically {databaseOwner} is replaced by "[dbo]."
   • {objectQualifier} is a legacy DNN feature that can be used separate object names within a schema; this used to be important when DNN was installed in hosting environments where a database schema was shared by multiple tenants. It's not typically used anymore but we keep it for legacy compatibility.

   Here's a sample:

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'{databaseOwner}{objectQualifier}activeforums_Replies') AND name = N'idx_{objectQualifier}activeforums_Replies_Opt3')

DROP INDEX [idx_{objectQualifier}activeforums_Replies_Opt3] ON {databaseOwner}{objectQualifier}activeforums_Replies

GO

CREATE NONCLUSTERED INDEX [idx_{objectQualifier}activeforums_Replies_Opt3] ON {databaseOwner}{objectQualifier}activeforums_Replies

( [ContentId] ASC, [ReplyId] ASC )

WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

GO

2. a manifest that is associated with the release. Don't worry about creating the manifest, as there might be multiple database changes for a given version, and the manifest only needs to reflect (once) the fact that the xx.yy.zz.SqlDataProvider file needs to be installed. The project owners will update the manifest as part of the release process.

3. Depending on what is being done in the database change, it's a best practice in DNN module development to also update the Uninstall.SqlDataProvider to remove any objects that are newly-created as part of your change. In other words, if you add an index or stored procedured, you should also make sure that it gets uninstalled as part of the module uninstall.

You can see this stack overflow page for good example https://stackoverflow.com/questions/2095318/how-to-add-database-script-in-dnn-custom-module-development-package

And here is a pull request within our project that includes the .SqlDataProvider file, but no manifest https://github.com/DNNCommunity/Dnn.CommunityForums/pull/1104

Lastly, here is a wiki on DNN module development: https://www.dnnsoftware.com/wiki/module-development (some of this is old but still applicable).