02.02.00.sqldataprovider

SharpNuke源代码

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

ALTER TABLE {databaseOwner}{objectQualifier}Schedule
	ALTER COLUMN [TypeFullName] [varchar] (200) NOT NULL
GO

ALTER TABLE {databaseOwner}{objectQualifier}Schedule
	ALTER COLUMN [TimeLapseMeasurement] [varchar] (2)  NOT NULL 
GO

ALTER TABLE {databaseOwner}{objectQualifier}Schedule
	ALTER COLUMN [RetryTimeLapseMeasurement] [varchar] (2)  NOT NULL 
GO

ALTER TABLE {databaseOwner}{objectQualifier}Schedule
	ALTER COLUMN [AttachToEvent] [varchar] (50)  NOT NULL 
GO

ALTER TABLE {databaseOwner}{objectQualifier}Schedule
	ALTER COLUMN [ObjectDependencies] [varchar] (300)  NOT NULL 
GO

ALTER TABLE {databaseOwner}{objectQualifier}ScheduleItemSettings
	ALTER COLUMN [SettingName] [nvarchar] (50) NOT NULL
GO

ALTER TABLE {databaseOwner}{objectQualifier}ScheduleItemSettings
	ALTER COLUMN [SettingValue] [nvarchar] (256) NOT NULL 
GO

ALTER TABLE {databaseOwner}{objectQualifier}Tabs ADD
	Url nvarchar(255) NULL
GO


ALTER TABLE {databaseOwner}{objectQualifier}Modules ADD
	InheritViewPermissions bit NULL
GO

UPDATE {databaseOwner}{objectQualifier}Modules
SET InheritViewPermissions = 1
WHERE IsNull(AuthorizedViewRoles, '') = ''

GO

UPDATE {databaseOwner}{objectQualifier}Modules
SET InheritViewPermissions = 0
WHERE InheritViewPermissions IS NULL

GO


drop procedure {databaseOwner}{objectQualifier}AddTab
GO

create procedure {databaseOwner}{objectQualifier}AddTab

@PortalId           int,
@TabName            nvarchar(50),
@IsVisible          bit,
@DisableLink        bit,
@ParentId           int,
@IconFile           nvarchar(100),
@Title              nvarchar(200),
@Description        nvarchar(500),
@KeyWords           nvarchar(500),
@Url                nvarchar(255)

as

insert into {objectQualifier}Tabs (
    PortalId,
    TabName,
    IsVisible,
    DisableLink,
    ParentId,
    IconFile,
    Title,
    Description,
    KeyWords,
    IsDeleted,
    Url
)
values (
    @PortalId,
    @TabName,
    @IsVisible,
    @DisableLink,
    @ParentId,
    @IconFile,
    @Title,
    @Description,
    @KeyWords,
    0,
    @Url
)

select SCOPE_IDENTITY()


GO
drop procedure {databaseOwner}{objectQualifier}UpdateTab
GO


create procedure {databaseOwner}{objectQualifier}UpdateTab

@TabId              int,
@TabName            nvarchar(50),
@IsVisible          bit,
@DisableLink        bit,
@ParentId           int,
@IconFile           nvarchar(100),
@Title              nvarchar(200),
@Description        nvarchar(500),
@KeyWords           nvarchar(500),
@IsDeleted          bit,
@Url                nvarchar(255)

as

update {objectQualifier}Tabs
set    TabName            = @TabName,
       IsVisible          = @IsVisible,
       DisableLink        = @DisableLink,
       ParentId           = @ParentId,
       IconFile           = @IconFile,
       Title              = @Title,
       Description        = @Description,
       KeyWords           = @KeyWords,
       IsDeleted          = @IsDeleted,
       Url                = @Url
where  TabId = @TabId

GO


ALTER TABLE {databaseOwner}{objectQualifier}Portals ADD
	DefaultLanguage nvarchar(6) NOT NULL CONSTRAINT DF_{objectQualifier}Portals_DefaultLanguage DEFAULT 'en-US'
GO

ALTER TABLE {databaseOwner}{objectQualifier}Portals ADD
	TimezoneOffset int NOT NULL CONSTRAINT DF_{objectQualifier}Portals_TimeZoneOffset DEFAULT -8
GO

drop procedure {databaseOwner}{objectQualifier}UpdatePortalInfo
GO

create procedure {databaseOwner}{objectQualifier}UpdatePortalInfo

@PortalId           int,
@PortalName         nvarchar(128),
@PortalAlias        nvarchar(200),
@LogoFile           nvarchar(50),
@FooterText         nvarchar(100),
@ExpiryDate         datetime,
@UserRegistration   int,
@BannerAdvertising  int,
@Currency           char(3),
@AdministratorId    int,
@HostFee            money,
@HostSpace          int,
@PaymentProcessor   nvarchar(50),
@ProcessorUserId    nvarchar(50),
@ProcessorPassword  nvarchar(50),
@Description        nvarchar(500),
@KeyWords           nvarchar(500),
@BackgroundFile     nvarchar(50),
@SiteLogHistory     int,
@HomeTabId          int,
@LoginTabId         int,
@UserTabId          int,
@DefaultLanguage    nvarchar(6),
@TimeZoneOffset	    int

as

update {objectQualifier}Portals
set    PortalName = @PortalName,
       PortalAlias = isnull(@PortalAlias,PortalAlias),
       LogoFile = @LogoFile,
       FooterText = @FooterText,
       ExpiryDate = @ExpiryDate,
       UserRegistration = @UserRegistration,
       BannerAdvertising = @BannerAdvertising,
       Currency = @Currency,
       AdministratorId = @AdministratorId,
       HostFee = @HostFee,
       HostSpace = @HostSpace,
       PaymentProcessor = @PaymentProcessor,
       ProcessorUserId = @ProcessorUserId,
       ProcessorPassword = @ProcessorPassword,
       Description = @Description,
       KeyWords = @KeyWords,
       BackgroundFile = @BackgroundFile,
       SiteLogHistory = @SiteLogHistory,
       HomeTabId = @HomeTabId,
       LoginTabId = @LoginTabId,
       UserTabId = @UserTabId,
       DefaultLanguage = @DefaultLanguage,
       TimeZoneOffset = @TimeZoneOffset
where  PortalId = @PortalId
GO

drop procedure {databaseOwner}{objectQualifier}GetTabByName
GO

create procedure {databaseOwner}{objectQualifier}GetTabByName

@TabName  nvarchar(50),
@PortalId int

as

select *
from   {objectQualifier}Tabs
where  TabName = @TabName
and    ((PortalId = @PortalId) or (@PortalId is null AND PortalId is null))
order by TabID

GO

ALTER TABLE {databaseOwner}{objectQualifier}Portals ADD
	AdminTabId int NULL
GO

update {objectQualifier}Portals
set    AdminTabId = ( select TabID from {objectQualifier}Tabs where {objectQualifier}Tabs.PortalID = {objectQualifier}Portals.PortalID and {objectQualifier}Tabs.TabName = 'Admin' )
GO

drop procedure {databaseOwner}{objectQualifier}UpdatePortalSetup
GO

create procedure {databaseOwner}{objectQualifier}UpdatePortalSetup

@PortalId            int,
@AdministratorId     int,
@AdministratorRoleId int,
@RegisteredRoleId    int,
@HomeTabId           int,
@LoginTabId          int,
@UserTabId           int,
@AdminTabId          int

as

update {objectQualifier}Portals
set    AdministratorId = @AdministratorId, 
       AdministratorRoleId = @AdministratorRoleId, 
       RegisteredRoleId = @RegisteredRoleId,
       HomeTabId = @HomeTabId,
       LoginTabId = @LoginTabId,
       UserTabId = @UserTabId,
       AdminTabId = @AdminTabId
where  PortalId = @PortalId

GO

drop procedure {databaseOwner}{objectQualifier}GetPortalSettings
GO

drop procedure {databaseOwner}{objectQualifier}GetPortal
GO

create procedure {databaseOwner}{objectQualifier}GetPortal

@PortalId  int

as

select {objectQualifier}Portals.*,
       'Email' = ( select Email from {objectQualifier}Users where {objectQualifier}Users.UserId = {objectQualifier}Portals.AdministratorId ),
       'SuperUserId' = ( select UserId from {objectQualifier}Users where IsSuperUser = 1 ),
       'SuperTabId' = ( select TabId from {objectQualifier}Tabs where PortalId is null and ParentId is null )
from {objectQualifier}Portals
where  PortalId = @PortalId

GO

drop procedure {databaseOwner}{objectQualifier}GetTabModules
GO

drop procedure {databaseOwner}{objectQualifier}GetRoleMembership
GO

create procedure {databaseOwner}{objectQualifier}GetRoleMembership
    
@PortalId int,
@RoleId   int,
@UserId   int

as

select {objectQualifier}UserRoles.UserRoleId,
       {objectQualifier}UserRoles.UserId,
       'FullName' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Users.Email,
       {objectQualifier}UserRoles.RoleId,
       {objectQualifier}Roles.RoleName,
       {objectQualifier}UserRoles.ExpiryDate
from {objectQualifier}UserRoles
inner join {objectQualifier}Users On {objectQualifier}Users.UserId = {objectQualifier}UserRoles.UserId
inner join {objectQualifier}Roles On {objectQualifier}Roles.RoleId = {objectQualifier}UserRoles.RoleId
inner join {objectQualifier}UserPortals On {objectQualifier}Users.UserId = {objectQualifier}UserPortals.UserId and {objectQualifier}UserPortals.PortalId = @PortalId
where  {objectQualifier}Roles.PortalId = @PortalId
and    {objectQualifier}UserPortals.Authorized = 1
and    ( ( @RoleId is null and {objectQualifier}UserRoles.UserId = @UserId )
or       ( @UserId is null and {objectQualifier}UserRoles.RoleId = @RoleId ) )
order by FullName

GO

ALTER TABLE {databaseOwner}{objectQualifier}Modules ADD
	Header ntext NULL,
	Footer ntext NULL,
	StartDate datetime NULL,
	EndDate datetime NULL
GO

drop procedure {databaseOwner}{objectQualifier}AddModule
GO

create procedure {databaseOwner}{objectQualifier}AddModule
    
@TabId                         int,
@ModuleDefId                   int,
@ModuleOrder                   int,
@PaneName                      nvarchar(50),
@ModuleTitle                   nvarchar(256),
@AuthorizedEditRoles           nvarchar(256),
@CacheTime                     int,
@AuthorizedViewRoles           nvarchar(256),
@Alignment                     nvarchar(10),
@Color                         nvarchar(20),
@Border                        nvarchar(1),
@IconFile                      nvarchar(100),
@AllTabs                       bit,
@ShowTitle                     bit,
@Personalize                   int,
@Header                        ntext,
@Footer                        ntext,
@StartDate                     datetime,
@EndDate                       datetime

as

insert into {objectQualifier}Modules ( 
  TabId,
  ModuleDefId,
  ModuleOrder,
  PaneName,
  ModuleTitle,
  AuthorizedEditRoles,
  CacheTime,
  AuthorizedViewRoles,
  Alignment,
  Color,
  Border,
  IconFile,
  AllTabs,
  ShowTitle,
  Personalize,
  IsDeleted,
  Header,
  Footer, 
  StartDate,
  EndDate
)
values (
  @TabId,
  @ModuleDefId,
  @ModuleOrder,
  @PaneName,
  @ModuleTitle,
  @AuthorizedEditRoles,
  @CacheTime,
  @AuthorizedViewRoles,
  @Alignment,
  @Color,
  @Border,
  @IconFile,
  @AllTabs,
  @ShowTitle,
  @Personalize,
  0,
  @Header,
  @Footer, 
  @StartDate,
  @EndDate
)

select SCOPE_IDENTITY()

GO

drop procedure {databaseOwner}{objectQualifier}UpdateModule
GO

create procedure {databaseOwner}{objectQualifier}UpdateModule

@ModuleId            int,
@ModuleOrder         int,
@ModuleTitle         nvarchar(256),
@Alignment           nvarchar(10),
@Color               nvarchar(20),
@Border              nvarchar(1),
@IconFile            nvarchar(100),
@CacheTime           int,
@AuthorizedViewRoles nvarchar(256),
@AuthorizedEditRoles nvarchar(256),
@TabId               int,
@AllTabs             bit, 
@ShowTitle           bit,
@Personalize         int,
@IsDeleted           bit,
@Header              ntext,
@Footer              ntext,
@StartDate           datetime,
@EndDate             datetime

as

update {objectQualifier}Modules
set    ModuleOrder = @ModuleOrder,
       ModuleTitle = @ModuleTitle,
       CacheTime   = @CacheTime,
       AuthorizedViewRoles = @AuthorizedViewRoles,
       AuthorizedEditRoles = @AuthorizedEditRoles,
       Alignment = @Alignment,
       Color = @Color,
       Border = @Border,
       IconFile = @IconFile,
       TabId = @TabId,
       AllTabs = @AllTabs,
       ShowTitle = @ShowTitle,
       Personalize = @Personalize,
       IsDeleted = @IsDeleted,
       Header = @Header,
       Footer = @Footer, 
       StartDate = @StartDate,
       EndDate = @EndDate
where  ModuleId = @ModuleId

GO

ALTER TABLE {databaseOwner}{objectQualifier}ModuleControls
ADD HelpUrl [nvarchar] (200) NULL

GO

ALTER TABLE {databaseOwner}{objectQualifier}ModuleControls
ALTER COLUMN ControlKey [nvarchar] (50)
GO

ALTER  procedure {databaseOwner}{objectQualifier}UpdateModuleControl

@ModuleControlId               int,
@ModuleDefID                   int,
@ControlKey                    nvarchar(50),
@ControlTitle                  nvarchar(50),
@ControlSrc                    nvarchar(256),
@IconFile                      nvarchar(100),
@ControlType                   int,
@ViewOrder                     int,
@HelpUrl                       nvarchar(200)

as

update {objectQualifier}ModuleControls
set    ModuleDefId       = @ModuleDefId,
       ControlKey        = @ControlKey,
       ControlTitle      = @ControlTitle,
       ControlSrc        = @ControlSrc,
       IconFile          = @IconFile,
       ControlType       = @ControlType,
       ViewOrder         = ViewOrder,
       HelpUrl           = @HelpUrl
where  ModuleControlId = @ModuleControlId

GO

ALTER  procedure {databaseOwner}{objectQualifier}AddModuleControl
    
@ModuleDefID                   int,
@ControlKey                    nvarchar(50),
@ControlTitle                  nvarchar(50),
@ControlSrc                    nvarchar(256),
@IconFile                      nvarchar(100),
@ControlType                   int,
@ViewOrder                     int,
@HelpUrl                       nvarchar(200)

as

insert into {objectQualifier}ModuleControls (
  ModuleDefID,
  ControlKey,
  ControlTitle,
  ControlSrc,
  IconFile,
  ControlType,
  ViewOrder,
  HelpUrl
)
values (
  @ModuleDefID,
  @ControlKey,
  @ControlTitle,
  @ControlSrc,
  @IconFile,
  @ControlType,
  @ViewOrder,
  @HelpUrl
)