dotnetnuke.schema.sqldataprovider

SharpNuke源代码

/************************************************************/
/*****              SqlDataProvider                     *****/
/*****	    Version 3.1.0 Schema Install Script         *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
CREATE TABLE {databaseOwner}[{objectQualifier}PortalAlias]
(
[PortalAliasID] [int] NOT NULL IDENTITY(1, 1),
[PortalID] [int] NOT NULL,
[HTTPAlias] [nvarchar] (200) NULL
)

GO

ALTER TABLE {databaseOwner}[{objectQualifier}PortalAlias] ADD CONSTRAINT [PK_{objectQualifier}PortalAlias] PRIMARY KEY CLUSTERED  ([PortalAliasID])
GO

CREATE procedure {databaseOwner}{objectQualifier}UpdatePortalAliasOnInstall

@PortalAlias nvarchar(200)

as

update {databaseOwner}{objectQualifier}PortalAlias 
set    HTTPAlias = @PortalAlias
where  HTTPAlias = '_default'

GO

CREATE TABLE {databaseOwner}[{objectQualifier}SearchItemWord]
(
[SearchItemWordID] [int] NOT NULL IDENTITY(1, 1),
[SearchItemID] [int] NOT NULL,
[SearchWordsID] [int] NOT NULL,
[Occurrences] [int] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}SearchItemWord] ADD CONSTRAINT [PK_{objectQualifier}SearchItemWords] PRIMARY KEY CLUSTERED  ([SearchItemWordID])
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWord
	@SearchItemWordID int
	
AS

SELECT
	[SearchItemWordID],
	[SearchItemID],
	[SearchWordsID],
	[Occurrences]
FROM
	{databaseOwner}{objectQualifier}SearchItemWord
WHERE
	[SearchItemWordID] = @SearchItemWordID

GO

CREATE TABLE {databaseOwner}[{objectQualifier}Folders]
(
[FolderID] [int] NOT NULL IDENTITY(1, 1),
[PortalID] [int] NULL,
[FolderPath] [varchar] (300) NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Folders] ADD CONSTRAINT [PK_{objectQualifier}Folders] PRIMARY KEY CLUSTERED  ([FolderID])

GO
CREATE TABLE {databaseOwner}[{objectQualifier}FolderPermission]
(
[FolderPermissionID] [int] NOT NULL IDENTITY(1, 1),
[FolderID] [int] NOT NULL,
[PermissionID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[AllowAccess] [bit] NOT NULL
)

GO

ALTER TABLE {databaseOwner}[{objectQualifier}FolderPermission] ADD CONSTRAINT [PK_{objectQualifier}FolderPermission] PRIMARY KEY CLUSTERED  ([FolderPermissionID])
GO

CREATE TABLE {databaseOwner}[{objectQualifier}EventLog]
(
[LogGUID] [varchar] (36) NOT NULL,
[LogTypeKey] [nvarchar] (35) NOT NULL,
[LogConfigID] [int] NULL,
[LogUserID] [int] NULL,
[LogUserName] [nvarchar] (50) NULL,
[LogPortalID] [int] NULL,
[LogPortalName] [nvarchar] (100) NULL,
[LogCreateDate] [datetime] NOT NULL,
[LogServerName] [nvarchar] (50) NOT NULL,
[LogProperties] [ntext] NOT NULL,
[LogNotificationPending] [bit] NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}EventLog] ADD CONSTRAINT [PK_EventLogMaster] PRIMARY KEY CLUSTERED  ([LogGUID])
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteEventLog
	@LogGUID varchar(36)
AS
DELETE FROM {databaseOwner}{objectQualifier}EventLog
WHERE LogGUID = @LogGUID or @LogGUID IS NULL


GO
CREATE TABLE {databaseOwner}[{objectQualifier}UserRoles]
(
[UserRoleID] [int] NOT NULL IDENTITY(1, 1),
[UserID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[IsTrialUsed] [bit] NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}UserRoles] ADD CONSTRAINT [PK_{objectQualifier}UserRoles] PRIMARY KEY CLUSTERED  ([UserRoleID])
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}UserRoles_1] ON {databaseOwner}[{objectQualifier}UserRoles] ([UserID])
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}UserRoles] ON {databaseOwner}[{objectQualifier}UserRoles] ([RoleID])
GO


create procedure {databaseOwner}{objectQualifier}DeleteUserRole

@UserId int,
@RoleId int

as

delete
from {objectQualifier}UserRoles
where  UserId = @UserId
and    RoleId = @RoleId

GO
CREATE TABLE {databaseOwner}[{objectQualifier}SearchItem]
(
[SearchItemID] [int] NOT NULL IDENTITY(1, 1),
[Title] [nvarchar] (200) NOT NULL,
[Description] [nvarchar] (2000) NOT NULL,
[Author] [int] NULL,
[PubDate] [datetime] NOT NULL,
[ModuleId] [int] NOT NULL,
[SearchKey] [nvarchar] (100) NOT NULL,
[Guid] [varchar] (200) NULL,
[HitCount] [int] NULL,
[ImageFileId] [int] NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}SearchItem] ADD CONSTRAINT [PK_{objectQualifier}SearchItem] PRIMARY KEY CLUSTERED  ([SearchItemID])
CREATE UNIQUE NONCLUSTERED INDEX [IX_{objectQualifier}SearchItem] ON {databaseOwner}[{objectQualifier}SearchItem] ([ModuleId], [SearchKey])
GO
CREATE TABLE {databaseOwner}[{objectQualifier}Skins]
(
[SkinID] [int] NOT NULL IDENTITY(1, 1),
[PortalID] [int] NULL,
[SkinRoot] [nvarchar] (50) NOT NULL,
[SkinSrc] [nvarchar] (200) NOT NULL,
[SkinType] [int] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Skins] ADD CONSTRAINT [PK_{objectQualifier}Skins] PRIMARY KEY CLUSTERED  ([SkinID])
GO

create procedure {databaseOwner}{objectQualifier}GetSkin

@SkinRoot               nvarchar(50),
@PortalID		int,
@SkinType               int

as
	
select *
from	{objectQualifier}Skins
where   SkinRoot = @SkinRoot
and     SkinType = @SkinType
and     ( PortalID is null or PortalID = @PortalID )
order by PortalID desc








GO
CREATE TABLE {databaseOwner}[{objectQualifier}PortalDesktopModules]
(
[PortalDesktopModuleID] [int] NOT NULL IDENTITY(1, 1),
[PortalID] [int] NOT NULL,
[DesktopModuleID] [int] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}PortalDesktopModules] ADD CONSTRAINT [PK_{objectQualifier}PortalDesktopModules] PRIMARY KEY CLUSTERED  ([PortalDesktopModuleID])
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddFolderPermission
	@FolderID int,
	@PermissionID int,
	@RoleID int,
	@AllowAccess bit
AS

INSERT INTO {databaseOwner}{objectQualifier}FolderPermission (
	[FolderID],
	[PermissionID],
	[RoleID],
	[AllowAccess]
) VALUES (
	@FolderID,
	@PermissionID,
	@RoleID,
	@AllowAccess
)

select SCOPE_IDENTITY()








GO
CREATE TABLE {databaseOwner}[{objectQualifier}EventLogConfig]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[LogTypeKey] [nvarchar] (35) NULL,
[LogTypePortalID] [int] NULL,
[LoggingIsActive] [bit] NOT NULL,
[KeepMostRecent] [int] NOT NULL,
[EmailNotificationIsActive] [bit] NOT NULL,
[NotificationThreshold] [int] NULL,
[NotificationThresholdTime] [int] NULL,
[NotificationThresholdTimeType] [int] NULL,
[MailFromAddress] [nvarchar] (50) NOT NULL,
[MailToAddress] [nvarchar] (50) NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}EventLogConfig] ADD CONSTRAINT [PK_EventLogConfig] PRIMARY KEY CLUSTERED  ([ID])
CREATE UNIQUE NONCLUSTERED INDEX [LogTypeKey_LogTypePortalID] ON {databaseOwner}[{objectQualifier}EventLogConfig] ([LogTypeKey], [LogTypePortalID])
GO
CREATE TABLE {databaseOwner}[{objectQualifier}SearchIndexer]
(
[SearchIndexerID] [int] NOT NULL IDENTITY(1, 1),
[SearchIndexerAssemblyQualifiedName] [char] (200) NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}SearchIndexer] ADD CONSTRAINT [PK_{objectQualifier}SearchIndexer] PRIMARY KEY CLUSTERED  ([SearchIndexerID])
GO

create procedure {databaseOwner}{objectQualifier}GetSearchIndexers

as

select {objectQualifier}SearchIndexer.*
from {objectQualifier}SearchIndexer








GO
CREATE TABLE {databaseOwner}[{objectQualifier}SearchItemWordPosition]
(
[SearchItemWordPositionID] [int] NOT NULL IDENTITY(1, 1),
[SearchItemWordID] [int] NOT NULL,
[ContentPosition] [int] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}SearchItemWordPosition] ADD CONSTRAINT [PK_{objectQualifier}SearchItemWordPosition] PRIMARY KEY CLUSTERED  ([SearchItemWordPositionID])
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}ListSearchItemWordPosition
AS

SELECT
	[SearchItemWordPositionID],
	[SearchItemWordID],
	[ContentPosition]
FROM
	{databaseOwner}{objectQualifier}SearchItemWordPosition







GO
CREATE TABLE {databaseOwner}[{objectQualifier}TabPermission]
(
[TabPermissionID] [int] NOT NULL IDENTITY(1, 1),
[TabID] [int] NOT NULL,
[PermissionID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[AllowAccess] [bit] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}TabPermission] ADD CONSTRAINT [PK_{objectQualifier}TabPermission] PRIMARY KEY CLUSTERED  ([TabPermissionID])
GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteTabPermission
	@TabPermissionID int
AS

DELETE FROM {databaseOwner}{objectQualifier}TabPermission
WHERE
	[TabPermissionID] = @TabPermissionID







GO


CREATE procedure {databaseOwner}{objectQualifier}GetPortalAlias

@HTTPAlias nvarchar(200),
@PortalID int

as

select *
from {databaseOwner}{objectQualifier}PortalAlias
where HTTPAlias = @HTTPAlias 
and PortalID = @PortalID









GO
CREATE TABLE {databaseOwner}[{objectQualifier}ModuleSettings]
(
[ModuleID] [int] NOT NULL,
[SettingName] [nvarchar] (50) NOT NULL,
[SettingValue] [nvarchar] (2000) NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}ModuleSettings] ADD CONSTRAINT [PK_{objectQualifier}ModuleSettings] PRIMARY KEY CLUSTERED  ([ModuleID], [SettingName])
GO

create procedure {databaseOwner}{objectQualifier}AddModuleSetting

@ModuleId      int,
@SettingName   nvarchar(50),
@SettingValue  nvarchar(2000)

as

insert into {objectQualifier}ModuleSettings ( 
  ModuleId, 
  SettingName, 
  SettingValue 
) 
values ( 
  @ModuleId, 
  @SettingName, 
  @SettingValue 
)


GO
CREATE TABLE {databaseOwner}[{objectQualifier}EventLogTypes]
(
[LogTypeKey] [nvarchar] (35) NOT NULL,
[LogTypeFriendlyName] [nvarchar] (50) NOT NULL,
[LogTypeDescription] [nvarchar] (128) NOT NULL,
[LogTypeOwner] [nvarchar] (100) NOT NULL,
[LogTypeCSSClass] [nvarchar] (40) NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}EventLogTypes] ADD CONSTRAINT [PK_EventLogTypes] PRIMARY KEY CLUSTERED  ([LogTypeKey])
GO
CREATE TABLE {databaseOwner}[{objectQualifier}Files]
(
[FileId] [int] NOT NULL IDENTITY(1, 1),
[PortalId] [int] NULL,
[FileName] [nvarchar] (100) NOT NULL,
[Extension] [nvarchar] (100) NOT NULL,
[Size] [int] NOT NULL,
[Width] [int] NULL,
[Height] [int] NULL,
[ContentType] [nvarchar] (200) NOT NULL,
[Folder] [nvarchar] (200) NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Files] ADD CONSTRAINT [PK_{objectQualifier}File] PRIMARY KEY CLUSTERED  ([FileId])
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}Files] ON {databaseOwner}[{objectQualifier}Files] ([PortalId])
GO
CREATE TABLE {databaseOwner}[{objectQualifier}SearchCommonWords]
(
[CommonWordID] [int] NOT NULL IDENTITY(1, 1),
[CommonWord] [nvarchar] (255) NOT NULL,
[Locale] [nvarchar] (10) NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}SearchCommonWords] ADD CONSTRAINT [PK_{objectQualifier}SearchCommonWords] PRIMARY KEY CLUSTERED  ([CommonWordID])
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddSearchCommonWord
	@CommonWord nvarchar(255),
	@Locale nvarchar(10)
AS

INSERT INTO {databaseOwner}{objectQualifier}SearchCommonWords (
	[CommonWord],
	[Locale]
) VALUES (
	@CommonWord,
	@Locale
)

select SCOPE_IDENTITY()







GO
CREATE TABLE {databaseOwner}[{objectQualifier}ModulePermission]
(
[ModulePermissionID] [int] NOT NULL IDENTITY(1, 1),
[ModuleID] [int] NOT NULL,
[PermissionID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[AllowAccess] [bit] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}ModulePermission] ADD CONSTRAINT [PK_{objectQualifier}ModulePermission] PRIMARY KEY CLUSTERED  ([ModulePermissionID])
GO
CREATE TABLE {databaseOwner}[{objectQualifier}ScheduleHistory]
(
[ScheduleHistoryID] [int] NOT NULL IDENTITY(1, 1),
[ScheduleID] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[Succeeded] [bit] NULL,
[LogNotes] [ntext] NULL,
[NextStart] [datetime] NULL,
[Server] [nvarchar] (150) NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}ScheduleHistory] ADD CONSTRAINT [PK_{objectQualifier}ScheduleHistory] PRIMARY KEY CLUSTERED  ([ScheduleHistoryID])
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddFolder
	@PortalID int,
	@FolderPath varchar(300)
AS
	IF NOT EXISTS (SELECT 1 FROM {databaseOwner}{objectQualifier}Folders WHERE PortalID = @PortalID and FolderPath = @FolderPath)
	BEGIN
		INSERT INTO {databaseOwner}{objectQualifier}Folders
		(PortalID, FolderPath)
		VALUES
		(@PortalID, @FolderPath)
		
		SELECT SCOPE_IDENTITY()
	END







GO

CREATE procedure {databaseOwner}{objectQualifier}GetPortalAliasByPortalAliasID

@PortalAliasID int

as

select *
from {databaseOwner}{objectQualifier}PortalAlias
where PortalAliasID = @PortalAliasID








GO
CREATE TABLE {databaseOwner}[{objectQualifier}Schedule]
(
[ScheduleID] [int] NOT NULL IDENTITY(1, 1),
[TypeFullName] [varchar] (200) NOT NULL,
[TimeLapse] [int] NOT NULL,
[TimeLapseMeasurement] [varchar] (2) NOT NULL,
[RetryTimeLapse] [int] NOT NULL,
[RetryTimeLapseMeasurement] [varchar] (2) NOT NULL,
[RetainHistoryNum] [int] NOT NULL,
[AttachToEvent] [varchar] (50) NOT NULL,
[CatchUpEnabled] [bit] NOT NULL,
[Enabled] [bit] NOT NULL,
[ObjectDependencies] [varchar] (300) NOT NULL,
[Servers] [nvarchar] (150) NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Schedule] ADD CONSTRAINT [PK_{objectQualifier}Schedule] PRIMARY KEY CLUSTERED  ([ScheduleID])
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteSchedule
@ScheduleID int
AS
DELETE FROM {databaseOwner}{objectQualifier}Schedule
WHERE ScheduleID = @ScheduleID







GO
CREATE TABLE {databaseOwner}[{objectQualifier}Permission]
(
[PermissionID] [int] NOT NULL IDENTITY(1, 1),
[PermissionCode] [varchar] (50) NOT NULL,
[ModuleDefID] [int] NOT NULL,
[PermissionKey] [varchar] (20) NOT NULL,
[PermissionName] [varchar] (50) NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Permission] ADD CONSTRAINT [PK_{objectQualifier}Permission] PRIMARY KEY CLUSTERED  ([PermissionID])
GO

create procedure {databaseOwner}{objectQualifier}GetTables

as

/* Be carefull when changing this procedure as the GetSearchTables() function 
   in SearchDB.vb is only looking at the first column (to support databases that cannot return 
   a TableName column name (like MySQL))
*/

select 'TableName' = [name]
from   sysobjects 
where  xtype = 'U' 








GO
CREATE TABLE {databaseOwner}[{objectQualifier}ModuleControls]
(
[ModuleControlID] [int] NOT NULL IDENTITY(1, 1),
[ModuleDefID] [int] NULL,
[ControlKey] [nvarchar] (50) NULL,
[ControlTitle] [nvarchar] (50) NULL,
[ControlSrc] [nvarchar] (256) NULL,
[IconFile] [nvarchar] (100) NULL,
[ControlType] [int] NOT NULL,
[ViewOrder] [int] NULL,
[HelpUrl] [nvarchar] (200) NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}ModuleControls] ADD CONSTRAINT [PK_{objectQualifier}ModuleControls] PRIMARY KEY CLUSTERED  ([ModuleControlID])
GO
CREATE TABLE {databaseOwner}[{objectQualifier}Affiliates]
(
[AffiliateId] [int] NOT NULL IDENTITY(1, 1),
[VendorId] [int] NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[CPC] [float] NOT NULL,
[Clicks] [int] NOT NULL,
[CPA] [float] NOT NULL,
[Acquisitions] [int] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Affiliates] ADD CONSTRAINT [PK_{objectQualifier}Affiliates] PRIMARY KEY CLUSTERED  ([AffiliateId])
GO

create procedure {databaseOwner}{objectQualifier}UpdateAffiliateStats

@AffiliateId  int,
@Clicks       int,
@Acquisitions int

as

update {objectQualifier}Affiliates
set    Clicks = Clicks + @Clicks,
       Acquisitions = Acquisitions + @Acquisitions
where  VendorId = @AffiliateId 
and    ( StartDate < getdate() or StartDate is null )