DotnetNuke SQL Scripts
Generating SQL Scripts in DotNetNuke is an important thing to understand, below we cover some of the basics that everyone generating SQL Scripts should keep in mind.
Repeatable SQL Scripts
When writing SQL Scripts for DNN you should write them in a manner that you can execute them over and over without them throwing errors. This is typically done by always checking for something before doing something. For example, if you are adding a table, you should always check to make sure that table doesn't exist before adding it. How you handle it if it does exist is up to you, you could drop it, or you could simply choose to not create it again. Here's some sample script that does just that from the http://dnnsimplearticle.codeplex.com project.
/** CREATE DNNSimpleArticle_Article Table **/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}DNNSimpleArticle_Article]') AND type in (N'U'))
DROP TABLE {databaseOwner}[{objectQualifier}DNNSimpleArticle_Article]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}DNNSimpleArticle_Article]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE {databaseOwner}[{objectQualifier}DNNSimpleArticle_Article](
[ID] [int] NOT NULL IDENTITY (1, 1),
[Title] [nvarchar](max) NULL,
[Description] [nvarchar](max) NULL,
[Body] [nvarchar](max) NULL,
[CreatedOnDate] [datetime] NULL,
[LastModifiedOnDate] [datetime] NULL,
[CreatedByUserID] [int] NULL,
[LastModifiedByUserID] [int] NULL,
[ModuleId] [int] NULL,
[ContentItemId] [int] NULL
CONSTRAINT [PK_{objectQualifier}DNNSimpleArticle_Article] PRIMARY KEY CLUSTERED
(
[ID] ASC{br}
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
Tokens
There are two common tokens in DotNetNuke SQL Scripts that you should utilize when you create SQL scripts.
databaseOwner and
objectQualifier, these tokens will cause SQL scripts to not execute in tools such as Query Analyzer or SQL Management Studio's query window, but will run on the Host/SQL page of a DotNetNuke website. DNN replaces these tokens at execution time with the values defined in the web.config file of the website.
Versions
Creating SQL Script versions is an important feature to understand when you are developing modules that are upgradeable, meaning someone who is using Version 1 of a module can easily upgrade to Version 2 of a module. More info coming soon
Sample Project
The SQL script used in the example above is from the DNNSimpleArticle project on Codeplex
http://dnnsimplearticle.codeplex.com