01.00.10.sqldataprovider

SharpNuke源代码

/************************************************************/
/*****              Upgrade Script 1.0.10                *****/
/************************************************************/

IF (SELECT COL_LENGTH('[dbo].ModuleEvents', 'AltText')) IS NULL
  ALTER TABLE [dbo].ModuleEvents ADD AltText nvarchar(50) NULL 
GO

if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].AddModuleEvent') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].AddModuleEvent
GO

create procedure [dbo].AddModuleEvent

@ModuleID    int,
@Description nvarchar(2000),
@DateTime    datetime,
@Title       nvarchar(100),
@ExpireDate  datetime = null,
@UserName    nvarchar(200),
@Every       int,
@Period      char(1),
@IconFile    nvarchar(256),
@AltText     nvarchar(50)
as

insert ModuleEvents ( 
  ModuleID,
  Description,
  DateTime,
  Title,
  ExpireDate,
  CreatedByUser,
  CreatedDate,
  Every,
  Period,
  IconFile,
  AltText
)
values (
  @ModuleID,
  @Description,
  @DateTime,
  @Title,
  @ExpireDate,
  @UserName,
  getdate(),
  @Every,
  @Period,
  @IconFile,
  @AltText
)
GO

if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].GetModuleEvents') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].GetModuleEvents
GO

create procedure [dbo].GetModuleEvents

@ModuleID int,
@StartDate datetime = null,
@EndDate datetime = null

as

declare @MaxWidth int

if @StartDate is null
begin
  select @MaxWidth = max(Width)
  from   ModuleEvents
  left outer join Files on ModuleEvents.IconFile = Files.FileName
  where  ModuleID = @ModuleID
  and    (ExpireDate > getdate() or ExpireDate is null)

  select ItemID,
         Description,
         DateTime,
         Title,
         ExpireDate,
         CreatedByUser,
         CreatedDate,
         IconFile,
         AltText,
         'MaxWidth' = @MaxWidth
  from   ModuleEvents
  where  ModuleID = @ModuleID
  and    (ExpireDate > getdate() or ExpireDate is null)
  order by DateTime
end
else
begin
  select ItemID,
         Description,
         DateTime,
         Title,
         ExpireDate,
         CreatedByUser,
         CreatedDate,
         Every,
         Period,
         IconFile,
         AltText
  from   ModuleEvents
  where  ModuleID = @ModuleID
  and    ( (Period is null and (DateTime >= @StartDate and DateTime <= @EndDate)) or Period is not null )
  order by DateTime
end

GO

if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].GetSingleModuleEvent') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].GetSingleModuleEvent
GO

create procedure [dbo].GetSingleModuleEvent

@ItemID   int,
@ModuleId int

as

select ItemID,
       Description,
       DateTime,
       Title,
       ExpireDate,
       'CreatedByUser' = FirstName + ' ' + LastName,
       ModuleEvents.CreatedDate,
       Every,
       Period,
       IconFile,
       AltText
from   ModuleEvents
left outer join Users on ModuleEvents.CreatedByUser = Users.UserId
where  ItemID = @ItemID
and    ModuleId = @ModuleId

GO

if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].UpdateModuleEvent') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].UpdateModuleEvent
GO

create procedure [dbo].UpdateModuleEvent

@ItemId      int,
@Description nvarchar(2000),
@DateTime    datetime,
@Title       nvarchar(100),
@ExpireDate  datetime = null,
@UserName    nvarchar(200),
@Every       int,
@Period      char(1),
@IconFile    nvarchar(256),
@AltText     nvarchar(50)

as

update ModuleEvents
set    Description = @Description,
       DateTime = @DateTime,
       Title = @Title,
       ExpireDate = @ExpireDate,
       CreatedByUser = @UserName,
       CreatedDate = getdate(),
       Every = @Every,
       Period = @Period,
       IconFile = @IconFile,
       AltText = @AltText
where  ItemId = @ItemId
GO

update ModuleSettings 
set SettingValue = '<table cellpadding="2" cellspacing="0" summary="Module Design Table" width="100%"[COLOR][BORDER]><tr><td[ALIGN]>[MODULE]</td></tr></table>'
where SettingName = 'container'
GO

ALTER TABLE [dbo].UserDefinedData
	DROP CONSTRAINT FK_UserDefinedData_UserDefinedRows
GO

ALTER TABLE [dbo].UserDefinedData WITH NOCHECK ADD CONSTRAINT
	FK_UserDefinedData_UserDefinedRows FOREIGN KEY
	(
	UserDefinedRowId
	) REFERENCES [dbo].UserDefinedRows
	(
	UserDefinedRowId
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].GetUserDefinedRows') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].GetUserDefinedRows
GO

create procedure [dbo].GetUserDefinedRows 

@ModuleId int 

as 

select UserDefinedRows.UserDefinedRowId, 
       UserDefinedFields.FieldTitle, 
       'FieldValue' = 
         Case charindex('http:', UserDefinedData.FieldValue) 
           When 0 Then
             Case charindex('@', UserDefinedData.FieldValue) 
               When 0 Then 
                 UserDefinedData.FieldValue 
               Else 
                 '<a href=mailto:' + UserDefinedData.FieldValue + '>' + UserDefinedData.FieldValue + '</a>' 
               End 
           Else 
             '<a href=' + UserDefinedData.FieldValue + '>' + UserDefinedData.FieldValue + '</a>' 
         End 
from   UserDefinedRows
left outer join UserDefinedData on UserDefinedRows.UserDefinedRowId = UserDefinedData.UserDefinedRowId
inner join UserDefinedFields on UserDefinedData.UserDefinedFieldId = UserDefinedFields.UserDefinedFieldId 
where  UserDefinedRows.ModuleId = @ModuleId
order by UserDefinedRows.UserDefinedRowId

GO

if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].UpdateClicks') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].UpdateClicks
GO

create   procedure [dbo].UpdateClicks

@TableName nvarchar(50),
@KeyField  nvarchar(50),
@ItemId    int,
@UserId    int = null

as

/* 
The following if...then ensures that 1) the TableName evaluates to a UserTable
and 2) that the KeyField is an actual column in the named table.  This check
is necessary to prevent SQL Injection attacks.  The ItemID is not subject
to attack since it must be an integer value.
*/
if (OBJECTPROPERTY(object_id(@TableName) , N'IsUserTable') = 1) and (COl_LENGTH(@TableName, @KeyField) > 0)
begin

	declare @SQL nvarchar(200)
	
	select @SQL = 'update ' + @TableName + ' set Clicks = Clicks + 1 where ' + @KeyField + ' = ' + convert(varchar,@ItemId)
	
	EXEC sp_executesql @SQL
	
	insert into ClickLog (
	  TableName,
	  ItemId,
	  DateTime,
	  UserId
	)
	values (
	  @TableName,
	  @ItemId,
	  getdate(),
	  @UserId
	)

end
GO

IF NOT EXISTS(SELECT 1 FROM [dbo].HostSettings WHERE SettingName = 'DisablePageTitleVersion')
  INSERT INTO [dbo].HostSettings VALUES ('DisablePageTitleVersion', 'N')
GO

IF (SELECT COL_LENGTH('[dbo].Tabs', 'DisableLink')) IS NULL
  ALTER TABLE [dbo].Tabs ADD DisableLink bit NOT NULL DEFAULT (0)
GO

if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[AddTab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].[AddTab]
GO

CREATE procedure [dbo].AddTab

@PortalID           int,
@TabName            nvarchar(50),
@ShowMobile         bit,
@MobileTabName      nvarchar(50),
@AuthorizedRoles    nvarchar (256),
@LeftPaneWidth      nvarchar(5),
@RightPaneWidth     nvarchar(5),
@IsVisible          bit,
@DisableLink        bit,
@ParentId           int,
@IconFile           nvarchar(100),
@AdministratorRoles nvarchar (256),
@TabID              int OUTPUT

as

if @ParentId is not null
begin
  select @IsVisible = 1
end

insert into Tabs (
    PortalID,
    TabName,
    ShowMobile,
    MobileTabName,
    AuthorizedRoles,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    DisableLink,
    ParentId,
    IconFile,
    AdministratorRoles
)
values (
    @PortalID,
    @TabName,
    @ShowMobile,
    @MobileTabName,
    @AuthorizedRoles,
    @LeftPaneWidth,
    @RightPaneWidth,
    @IsVisible,
    @DisableLink,
    @ParentId,
    @IconFile,
    @AdministratorRoles
)

select @TabID = @@IDENTITY

GO


if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[UpdateTab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].[UpdateTab]
GO

CREATE procedure [dbo].UpdateTab

@TabID              int,
@TabName            nvarchar(50),
@ShowMobile         bit,
@MobileTabName      nvarchar(50),
@AuthorizedRoles    nvarchar(256),
@LeftPaneWidth      nvarchar(5),
@RightPaneWidth     nvarchar(5),
@IsVisible          bit,
@DisableLink        bit,
@ParentId           int,
@IconFile           nvarchar(100),
@AdministratorRoles nvarchar(256)

as

declare @PortalID int

select @PortalID = PortalID
from   Tabs
where  TabID = @TabID

if (exists ( select 1 from Tabs where ParentId = @TabId )) or (@ParentId is not null)
begin
  select @IsVisible = 1
end

update Tabs
set    TabName = @TabName,
       ShowMobile = @ShowMobile,
       MobileTabName = @MobileTabName,
       AuthorizedRoles = @AuthorizedRoles,
       LeftPaneWidth = @LeftPaneWidth,
       RightPaneWidth = @RightPaneWidth,
       IsVisible = @IsVisible,
       DisableLink = @DisableLink,
       ParentId = @ParentId,
       IconFile = @IconFile,
       AdministratorRoles = @AdministratorRoles
where  TabID = @TabID
GO

if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[GetPortalSettings]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetPortalSettings]
GO

CREATE procedure [dbo].GetPortalSettings

@PortalAlias nvarchar(200),
@TabID       int

as

declare @PortalID int
declare @VerifyTabID int

/* convert PortalAlias to PortalID */

select @PortalID = null

select @PortalID = PortalID
from   Portals
where  PortalAlias = @PortalAlias

if @PortalID is null
begin
  select @PortalID = min(PortalID)
  from   Portals
  where  PortalAlias like '%' + @PortalAlias + '%' /* multiple alias may be specified seperated by commas */
end

select @VerifyTabID = null

/* verify the TabID belongs to the portal */
if @TabID <> 0
begin
  select @VerifyTabID = Tabs.TabID
  from   Tabs
  left outer join Portals on Tabs.PortalID = Portals.PortalID
  where  TabId = @TabId
  and    ( Portals.PortalID = @PortalID or Tabs.PortalId is null )
end
else
begin
  select @VerifyTabID = null
end

/* get the TabID if none provided */
if @VerifyTabID is null
begin
  select @TabID = Tabs.TabID
  from Tabs
  inner join Portals on Tabs.PortalID = Portals.PortalID
  where Portals.PortalID = @PortalID
  and Tabs.TabOrder = 1  
end

/* First, get Out Params */
select Portals.PortalAlias,
       Portals.PortalID,
       Portals.GUID,
       Portals.PortalName,
       Portals.LogoFile,
       Portals.FooterText,
       Portals.ExpiryDate,
       Portals.UserRegistration,
       Portals.BannerAdvertising,
       Portals.Currency,
       Portals.AdministratorId,
       Users.Email,
       Portals.HostFee,
       Portals.HostSpace,
       Portals.AdministratorRoleId,
       Portals.RegisteredRoleId,
       Portals.Description,
       Portals.KeyWords,
       Portals.BackgroundFile,
       Portals.SiteLogHistory,
       'AdminTabId' = ( select TabID from Tabs where PortalId = @PortalId and TabName = 'Admin' ),
       'SuperUserId' = ( select UserID from Users where IsSuperUser = 1 ),
       'SuperTabId' = ( select TabID from Tabs where PortalId is null and ParentId is null ),
       Tabs.TabID,
       Tabs.TabOrder,
       Tabs.TabName,
       Tabs.MobileTabName,
       Tabs.AuthorizedRoles,
       Tabs.AdministratorRoles,
       Tabs.ShowMobile,
       Tabs.LeftPaneWidth,
       Tabs.RightPaneWidth,
       Tabs.IsVisible,
       Tabs.DisableLink,
       'ParentId' = isnull(Tabs.ParentID,-1),
       Tabs.Level,
       Tabs.IconFile,
       'AdminTabIcon' = ( select AdminTabIcon from ModuleDefinitions where ModuleDefinitions.FriendlyName = Tabs.TabName ),
       'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end
from   Tabs
inner join Portals on Portals.PortalID = @PortalID
inner join Users on Portals.AdministratorId = Users.UserId
where  TabID = @TabID

/* Get Tabs list */
select TabName,
       AuthorizedRoles,
       AdministratorRoles,