dotnetnuke.schema.sqldataprovider

SharpNuke源代码

select
	[SearchItemID],
	[Title],
	[Description],
	[Author],
	[PubDate],
	[ModuleId],
	[SearchKey],
	[Guid],
	[HitCount],
	ImageFileId
from
	{objectQualifier}SearchItem
where
	[ModuleID] = @ModuleID AND
	[SearchKey] = @SearchKey








GO


create procedure {databaseOwner}{objectQualifier}UpdateModuleSetting

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

as

update {objectQualifier}ModuleSettings
set SettingValue = @SettingValue
where ModuleId = @ModuleId
and SettingName = @SettingName








GO

create procedure {databaseOwner}{objectQualifier}AddPortalDesktopModule

@PortalId int,
@DesktopModuleId int

as

insert into {objectQualifier}PortalDesktopModules ( 
  PortalId,
  DesktopModuleId
)
values (
  @PortalId,
  @DesktopModuleId
)

select SCOPE_IDENTITY()








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateEventLogConfig
	@ID int,
	@LogTypeKey nvarchar(35),
	@LogTypePortalID int,
	@LoggingIsActive bit,
	@KeepMostRecent int,
	@EmailNotificationIsActive bit,
	@NotificationThreshold int,
	@NotificationThresholdTime int,
	@NotificationThresholdTimeType int,
	@MailFromAddress nvarchar(50),
	@MailToAddress nvarchar(50)
AS
UPDATE {databaseOwner}{objectQualifier}EventLogConfig
SET 	LogTypeKey = @LogTypeKey,
	LogTypePortalID = @LogTypePortalID,
	LoggingIsActive = @LoggingIsActive,
	KeepMostRecent = @KeepMostRecent,
	EmailNotificationIsActive = @EmailNotificationIsActive,
	NotificationThreshold = @NotificationThreshold,
	NotificationThresholdTime = @NotificationThresholdTime,
	NotificationThresholdTimeType = @NotificationThresholdTimeType,
	MailFromAddress = @MailFromAddress,
	MailToAddress = @MailToAddress
WHERE	ID = @ID

GO

CREATE procedure {databaseOwner}{objectQualifier}DeletePortalAlias
@PortalAliasID int

as

DELETE FROM {databaseOwner}{objectQualifier}PortalAlias 
WHERE PortalAliasID = @PortalAliasID
	







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleByEvent
@EventName varchar(50),
@Server varchar(150)
AS
SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled
FROM {databaseOwner}{objectQualifier}Schedule S
WHERE S.AttachToEvent = @EventName
AND (S.Servers LIKE ',%' + @Server + '%,' or S.Servers IS NULL)
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled


GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteFolderPermissionsByFolderPath
	@PortalID int,
	@FolderPath varchar(300)
AS
DECLARE @FolderID int
SELECT @FolderID = FolderID FROM {databaseOwner}{objectQualifier}Folders
WHERE FolderPath = @FolderPath
AND ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))

DELETE FROM {databaseOwner}{objectQualifier}FolderPermission
WHERE
	[FolderID] = @FolderID







GO

create procedure {databaseOwner}{objectQualifier}UpdateTabModuleSetting

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

as

update {objectQualifier}TabModuleSettings
set    SettingValue = @SettingValue
where  TabModuleId = @TabModuleId
and    SettingName = @SettingName








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddModulePermission
	@ModuleID int,
	@PermissionID int,
	@RoleID int,
	@AllowAccess bit
AS

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

select SCOPE_IDENTITY()








GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteTabPermissionsByTabID
	@TabID int
AS

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







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateScheduleHistory
@ScheduleHistoryID int,
@EndDate datetime,
@Succeeded bit,
@LogNotes ntext,
@NextStart datetime
AS
UPDATE {databaseOwner}{objectQualifier}ScheduleHistory
SET	EndDate = @EndDate,
	Succeeded = @Succeeded,
	LogNotes = @LogNotes,
	NextStart = @NextStart
WHERE ScheduleHistoryID = @ScheduleHistoryID








GO

create procedure {databaseOwner}{objectQualifier}GetAffiliates

@VendorId int

as

select AffiliateId,
       StartDate,
       EndDate,
       CPC,
       Clicks,
       'CPCTotal' = Clicks * CPC,
       CPA,
       Acquisitions,
       'CPATotal' = Acquisitions * CPA
from   {objectQualifier}Affiliates
where  VendorId = @VendorId
order  by StartDate desc








GO
CREATE TABLE {databaseOwner}[{objectQualifier}Classification]
(
[ClassificationId] [int] NOT NULL IDENTITY(1, 1),
[ClassificationName] [nvarchar] (200) NOT NULL,
[ParentId] [int] NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Classification] ADD CONSTRAINT [PK_{objectQualifier}VendorCategory] PRIMARY KEY CLUSTERED  ([ClassificationId])
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}Classification] ON {databaseOwner}[{objectQualifier}Classification] ([ParentId])
GO


CREATE procedure {databaseOwner}{objectQualifier}GetPortalByAlias

@HTTPAlias nvarchar(200)

as

select 'PortalId' = min(PortalId)
from {databaseOwner}{objectQualifier}PortalAlias
where  HTTPAlias = @HTTPAlias








GO

create procedure {databaseOwner}{objectQualifier}GetProfile

@UserId    int, 
@PortalId  int

as

select *
from   {objectQualifier}Profile
where  UserId = @UserId 
and    PortalId = @PortalId








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateModulePermission
	@ModulePermissionID int, 
	@ModuleID int, 
	@PermissionID int, 
	@RoleID int ,
	@AllowAccess bit
AS

UPDATE {databaseOwner}{objectQualifier}ModulePermission SET
	[ModuleID] = @ModuleID,
	[PermissionID] = @PermissionID,
	[RoleID] = @RoleID,
	[AllowAccess] = @AllowAccess
WHERE
	[ModulePermissionID] = @ModulePermissionID








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}PurgeEventLog
AS
DELETE FROM {databaseOwner}{objectQualifier}EventLog
FROM {databaseOwner}{objectQualifier}EventLogConfig elc
WHERE 
    (
    SELECT COUNT(*)
    FROM {databaseOwner}{objectQualifier}EventLog el
    WHERE el.LogConfigID = elc.ID
	and {databaseOwner}{objectQualifier}EventLog.LogTypeKey = el.LogTypeKey
	and el.LogCreateDate >= {databaseOwner}{objectQualifier}EventLog.LogCreateDate
    ) > elc.KeepMostRecent
AND elc.KeepMostRecent<>-1

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetEventLogPendingNotif
	@LogConfigID int
AS
SELECT *
FROM {databaseOwner}{objectQualifier}EventLog
WHERE LogNotificationPending = 1
AND LogConfigID = @LogConfigID


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}GetPermissionByCodeAndKey
	@PermissionCode varchar(50),
	@PermissionKey varchar(20)
AS

SELECT
	P.[PermissionID],
	P.[PermissionCode],
	P.[ModuleDefID],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}Permission P
WHERE
	(P.PermissionCode = @PermissionCode or @PermissionCode IS NULL)
	AND
	(P.PermissionKey = @PermissionKey or @PermissionKey IS NULL)








GO


CREATE procedure {databaseOwner}{objectQualifier}UpdateFile

@FileId      int,
@FileName    nvarchar(100),
@Extension   nvarchar(100),
@Size        int,
@WIdth       int,
@Height      int,
@ContentType nvarchar(200),
@Folder nvarchar(200)

as

update {objectQualifier}Files
set    FileName = @FileName,
       Extension = @Extension,
       Size = @Size,
       WIdth = @WIdth,
       Height = @Height,
       ContentType = @ContentType,
       Folder=@Folder
where  FileId = @FileId







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}GetPortalAliasByPortalID

@PortalID int

as

select *
from {databaseOwner}{objectQualifier}PortalAlias
where (PortalID = @PortalID or @PortalID = -1)









GO

create procedure {databaseOwner}{objectQualifier}DeletePortalDesktopModules

@PortalId        int,
@DesktopModuleId int

as

delete
from   {objectQualifier}PortalDesktopModules
where  ((PortalId = @PortalId) or (@PortalId is null and @DesktopModuleId is not null))
and    ((DesktopModuleId = @DesktopModuleId) or (@DesktopModuleId is null and @PortalId is not null))








GO

create procedure {databaseOwner}{objectQualifier}DeleteSearchItemWords
	@SearchItemID int
AS

delete from {databaseOwner}{objectQualifier}SearchItemWord
where
	[SearchItemID] = @SearchItemID







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}UpdateSearchItem
	@SearchItemID int, 
	@Title varchar(200), 
	@Description nvarchar(2000), 
	@Author int, 
	@PubDate datetime, 
	@ModuleId int, 
	@SearchKey nvarchar(100), 
	@Guid nvarchar(200), 
	@HitCount int, 
	@ImageFileId int
AS

UPDATE {objectQualifier}SearchItem 
SET	[Title] = @Title,
	[Description] = @Description,
	[Author] = @Author,
	[PubDate] = @PubDate,
	[ModuleId] = @ModuleId,
	[SearchKey] = @SearchKey,
	[Guid] = @Guid,
	[HitCount] = @HitCount,
	ImageFileId = 	@ImageFileId
WHERE   [SearchItemID] = @SearchItemID





GO


create procedure {databaseOwner}{objectQualifier}DeleteFiles

@PortalId int

as

delete 
from   {objectQualifier}Files
where  ((PortalId = @PortalId) or (@PortalId is null and PortalId is null))








GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetFolders
	@PortalID int,
	@FolderID int,
	@FolderPath varchar(300)
AS
	SELECT *
	FROM {databaseOwner}{objectQualifier}Folders
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
	AND (FolderID = @FolderID or @FolderID = -1)
	AND (FolderPath = @FolderPath or @FolderPath = '')







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdatePermission
	@PermissionID int, 
	@PermissionCode varchar(50),
	@ModuleDefID int, 
	@PermissionKey varchar(20), 
	@PermissionName varchar(50) 
AS

UPDATE {databaseOwner}{objectQualifier}Permission SET
	[ModuleDefID] = @ModuleD