02.01.01.sqldataprovider

SharpNuke源代码

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

update {objectQualifier}Tabs set AuthorizedRoles = '-1;0;' where TabName = 'Home' and AuthorizedRoles = '-1;'
GO

update {objectQualifier}Tabs set AdministratorRoles = '0;' where TabName = 'Home' and AdministratorRoles is null
GO

drop procedure {databaseOwner}{objectQualifier}AddDesktopModule
GO

create procedure {databaseOwner}{objectQualifier}AddDesktopModule
    
@FriendlyName   nvarchar(128),
@Description    nvarchar(2000),
@Version        nvarchar(8),
@IsPremium      bit,
@IsAdmin        bit

as

insert into {objectQualifier}DesktopModules (
  FriendlyName,
  Description,
  Version,
  IsPremium,
  IsAdmin
)
values (
  @FriendlyName,
  @Description,
  @Version,
  @IsPremium,
  @IsAdmin
)

select SCOPE_IDENTITY()

GO

drop procedure {databaseOwner}{objectQualifier}UpdateDesktopModule
GO

create procedure {databaseOwner}{objectQualifier}UpdateDesktopModule

@DesktopModuleId int,    
@FriendlyName    nvarchar(128),
@Description     nvarchar(2000),
@Version         nvarchar(8),
@IsPremium       bit,
@IsAdmin         bit

as

update {objectQualifier}DesktopModules
set    FriendlyName    = @FriendlyName,
       Description     = @Description,
       Version         = @Version,
       IsPremium       = @IsPremium,
       IsAdmin         = @IsAdmin
where  DesktopModuleId = @DesktopModuleId

GO

CREATE TABLE {databaseOwner}{objectQualifier}SystemMessages
	(
	MessageID int NOT NULL IDENTITY (1, 1),
	PortalID int NULL,
	MessageName nvarchar(50) NOT NULL,
	MessageValue ntext NOT NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE {databaseOwner}{objectQualifier}SystemMessages ADD CONSTRAINT
	PK_SystemMessages PRIMARY KEY CLUSTERED 
	(
	MessageID
	) ON [PRIMARY]

GO
ALTER TABLE {databaseOwner}{objectQualifier}SystemMessages ADD CONSTRAINT
	IX_SystemMessages UNIQUE NONCLUSTERED 
	(
	MessageName,
	PortalID
	) ON [PRIMARY]

GO

ALTER TABLE {databaseOwner}{objectQualifier}SystemMessages WITH NOCHECK ADD CONSTRAINT
	FK_SystemMessages_Portals FOREIGN KEY
	(
	PortalID
	) REFERENCES {databaseOwner}{objectQualifier}Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

create procedure {databaseOwner}{objectQualifier}AddSystemMessage

@PortalID     int,
@MessageName  nvarchar(50),
@MessageValue ntext

as

insert into {objectQualifier}SystemMessages (
  PortalID,
  MessageName,
  MessageValue
)
values (
  @PortalID,
  @MessageName,
  @MessageValue
)

GO

create procedure {databaseOwner}{objectQualifier}UpdateSystemMessage

@PortalID     int,
@MessageName  nvarchar(50),
@MessageValue ntext

as

update {objectQualifier}SystemMessages
set    MessageValue = @MessageValue
where  ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
and    MessageName = @MessageName

GO

create procedure {databaseOwner}{objectQualifier}DeleteSystemMessage

@PortalID     int,
@MessageName  nvarchar(50)

as

delete
from   {objectQualifier}SystemMessages
where  PortalID = @PortalID
and    MessageName = @MessageName

GO

create procedure {databaseOwner}{objectQualifier}GetSystemMessage

@PortalID     int,
@MessageName  nvarchar(50)

as

select MessageValue
from   {objectQualifier}SystemMessages
where  ((PortalID = @PortalID) or (PortalID is null and @PortalID is null)) 
and    MessageName = @MessageName

GO

create procedure {databaseOwner}{objectQualifier}GetSystemMessages

as

select MessageName
from   {objectQualifier}SystemMessages
where  PortalID is null

GO

CREATE TABLE {databaseOwner}{objectQualifier}Urls
	(
	UrlID int NOT NULL IDENTITY (1, 1),
	PortalID int NULL,
	Url nvarchar(255) NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}Urls ADD CONSTRAINT
	PK_Urls PRIMARY KEY CLUSTERED 
	(
	UrlID
	) ON [PRIMARY]

GO

ALTER TABLE {databaseOwner}{objectQualifier}Urls ADD CONSTRAINT
	IX_Urls UNIQUE NONCLUSTERED 
	(
	Url,
	PortalID
	) ON [PRIMARY]

GO

ALTER TABLE {databaseOwner}{objectQualifier}Urls WITH NOCHECK ADD CONSTRAINT
	FK_Urls_Portals FOREIGN KEY
	(
	PortalID
	) REFERENCES {databaseOwner}{objectQualifier}Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

CREATE TABLE {databaseOwner}{objectQualifier}UrlTracking
	(
	UrlTrackingID int NOT NULL IDENTITY (1, 1),
	PortalID int NULL,
	Url nvarchar(255) NOT NULL,
        UrlType char(1) NOT NULL,
	Clicks int NOT NULL,
        LastClick datetime NULL,
        CreatedDate datetime NOT NULL,
        LogActivity bit NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}UrlTracking ADD CONSTRAINT
	PK_UrlTracking PRIMARY KEY CLUSTERED 
	(
	UrlTrackingID
	) ON [PRIMARY]

GO

ALTER TABLE {databaseOwner}{objectQualifier}UrlTracking ADD CONSTRAINT
	IX_UrlTracking UNIQUE NONCLUSTERED 
	(
	Url,
	PortalID
	) ON [PRIMARY]

GO

ALTER TABLE {databaseOwner}{objectQualifier}UrlTracking WITH NOCHECK ADD CONSTRAINT
	FK_UrlTracking_Portals FOREIGN KEY
	(
	PortalID
	) REFERENCES {databaseOwner}{objectQualifier}Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

CREATE TABLE {databaseOwner}{objectQualifier}UrlLog
	(
	UrlLogID int NOT NULL IDENTITY (1, 1),
	UrlTrackingID int NOT NULL,
	ClickDate datetime NOT NULL,
	UserID int NULL,
        ModuleID int NULL
	)  ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}UrlLog ADD CONSTRAINT
	PK_{objectQualifier}UrlLog PRIMARY KEY CLUSTERED 
	(
	UrlLogID
	) ON [PRIMARY]

GO

ALTER TABLE {databaseOwner}{objectQualifier}UrlLog WITH NOCHECK ADD CONSTRAINT
	FK_{objectQualifier}UrlLog_{objectQualifier}UrlTracking FOREIGN KEY
	(
	UrlTrackingID
	) REFERENCES {databaseOwner}{objectQualifier}UrlTracking
	(
	UrlTrackingID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

create procedure {databaseOwner}{objectQualifier}AddUrl

@PortalID     int,
@Url          nvarchar(255)

as

insert into {objectQualifier}Urls (
  PortalID,
  Url
)
values (
  @PortalID,
  @Url
)

GO

create procedure {databaseOwner}{objectQualifier}DeleteUrl

@PortalID     int,
@Url          nvarchar(255)

as

delete
from   {objectQualifier}Urls
where  PortalID = @PortalID
and    Url = @Url

GO

create procedure {databaseOwner}{objectQualifier}GetUrl

@PortalID     int,
@Url          nvarchar(255)

as

select *
from   {objectQualifier}Urls
where  PortalID = @PortalID
and    Url = @Url

GO

create procedure {databaseOwner}{objectQualifier}GetUrls

@PortalID     int

as

select *
from   {objectQualifier}Urls
where  PortalID = @PortalID
order by Url

GO

create procedure {databaseOwner}{objectQualifier}AddUrlTracking

@PortalID     int,
@Url          nvarchar(255),
@UrlType      char(1),
@Clicks       int,
@LastClick    datetime,
@CreatedDate  datetime,
@LogActivity  bit

as

insert into {objectQualifier}UrlTracking (
  PortalID,
  Url,
  UrlType,
  Clicks,
  LastClick,
  CreatedDate,
  LogActivity
)
values (
  @PortalID,
  @Url,
  @UrlType,