01.00.06.sqldataprovider

SharpNuke源代码

/************************************************************/
/*****              Upgrade Script 1.0.6                *****/
/************************************************************/

drop procedure UpdateModuleDefinition
GO

create procedure UpdateModuleDefinition

@ModuleDefID   int,
@FriendlyName  nvarchar(128),
@DesktopSrc    nvarchar(256),
@MobileSrc     nvarchar(256),
@AdminOrder    int,
@EditSrc       nvarchar(256),
@Secure        bit,
@Description   nvarchar(2000),
@HostFee       money

as

declare @TabId int
declare @ModuleOrder int
declare @AdministratorRoleId int
declare @PortalId int
declare @TabOrder int
declare @ChildTabId int

update ModuleDefinitions
set    FriendlyName = @FriendlyName,
       DesktopSrc   = @DesktopSrc,
       MobileSrc    = @MobileSrc,
       AdminOrder   = @AdminOrder,
       EditSrc      = @EditSrc,
       Secure       = @Secure,
       Description  = @Description,
       HostFee      = @HostFee
where  ModuleDefID = @ModuleDefID

if @HostFee = 0
begin
  delete
  from   PortalModuleDefinitions
  where  ModuleDefID = @ModuleDefID
end

GO

if not exists ( select 1 from ModuleDefinitions where FriendlyName = 'Search' )
begin
  INSERT INTO [dbo].[ModuleDefinitions] ([FriendlyName], [DesktopSrc], [MobileSrc], [AdminOrder], [EditSrc], [Secure], [Description], [HostFee] ) VALUES ('Search', 'DesktopModules/Search/Search.ascx', '', NULL, 'DesktopModules/Search/EditSearch.ascx', 1, 'Search allows your users to easily locate information in your portal', 0)
end
GO

CREATE TABLE dbo.Search
	(
	SearchId int NOT NULL IDENTITY (1, 1),
	ModuleId int NOT NULL,
	TableName nvarchar(50) NOT NULL,
	TitleField nvarchar(50) NULL,
	DescriptionField nvarchar(50) NULL,
	CreatedDateField nvarchar(50) NULL,
	CreatedByUserField nvarchar(50) NULL
	)  ON [PRIMARY]
GO

ALTER TABLE Search ADD CONSTRAINT
	PK_Search PRIMARY KEY CLUSTERED 
	(
	SearchId
	) ON [PRIMARY]

GO

drop procedure GetSearch
go

create procedure GetSearch

@ModuleID int

as

select SearchId,
       TableName,
       TitleField,
       DescriptionField,
       CreatedDateField,
       CreatedByUserField
from   Search
where  ModuleID = @ModuleID
order by TableName

GO

drop procedure AddSearch
go

create procedure AddSearch

@ModuleID  int,
@TableName nvarchar(50)

as

if not exists ( select 1 from Search where ModuleId = @ModuleId and TableName = @TableName )
begin
  insert into Search (
    ModuleId,
    TableName
  )
  values (
    @ModuleId,
    @TableName
  )
end

GO

drop procedure GetSingleSearch
go

create procedure GetSingleSearch

@SearchID int,
@ModuleId int

as

select TableName,
       TitleField,
       DescriptionField,
       CreatedDateField,
       CreatedByUserField
from   Search
where  SearchId = @SearchId
and    ModuleID = @ModuleID

GO

drop procedure UpdateSearch
go

create procedure UpdateSearch

@SearchID           int,
@TitleField         nvarchar(50),
@DescriptionField   nvarchar(50),
@CreatedDateField   nvarchar(50),
@CreatedByUserField nvarchar(50)

as

update Search
set    TitleField = @TitleField,
       DescriptionField = @DescriptionField,
       CreatedDateField = @CreatedDateField,
       CreatedByUserField = @CreatedByUserField
where  SearchId = @SearchId

GO

drop procedure DeleteSearch
go

create procedure DeleteSearch

@SearchID int

as

delete
from   Search
where  SearchId = @SearchId

GO

ALTER TABLE Users
	DROP CONSTRAINT DF_Users_IsSuperUser
GO

CREATE TABLE dbo.Tmp_Users
	(
	UserID int NOT NULL IDENTITY (1, 1),
	FirstName nvarchar(50) NOT NULL,
	LastName nvarchar(50) NOT NULL,
	Street nvarchar(50) NULL,
	City nvarchar(50) NULL,
	Region nvarchar(50) NULL,
	PostalCode nvarchar(50) NULL,
	Country nvarchar(50) NULL,
	Password nvarchar(50) NOT NULL,
	Email nvarchar(100) NOT NULL,
	Unit nvarchar(50) NULL,
	IsSuperUser bit NOT NULL,
	Telephone nvarchar(50) NULL,
	Username nvarchar(100) NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE Tmp_Users ADD CONSTRAINT
	DF_Users_IsSuperUser DEFAULT (0) FOR IsSuperUser
GO

ALTER TABLE Tmp_Users ADD CONSTRAINT
	DF_Users_Username DEFAULT N'default' FOR Username
GO

SET IDENTITY_INSERT Tmp_Users ON
GO

IF EXISTS(SELECT * FROM dbo.Users)
	 EXEC('INSERT INTO dbo.Tmp_Users (UserID, FirstName, LastName, Street, City, Region, PostalCode, Country, Password,Email, Unit, IsSuperUser, Telephone) SELECT UserID, FirstName, LastName, Street, City, Region, PostalCode, Country, Password, Email, Unit, IsSuperUser, Telephone FROM dbo.Users TABLOCKX')
GO

SET IDENTITY_INSERT Tmp_Users OFF
GO

ALTER TABLE UserPortals
	DROP CONSTRAINT FK_UserPortals_Users
GO

ALTER TABLE UserRoles
	DROP CONSTRAINT FK_UserRoles_Users
GO

DROP TABLE Users
GO

EXECUTE sp_rename N'dbo.Tmp_Users', N'Users', 'OBJECT'
GO

ALTER TABLE Users ADD CONSTRAINT
	IX_Users UNIQUE NONCLUSTERED 
	(
	Email
	) ON [PRIMARY]
GO

ALTER TABLE Users ADD CONSTRAINT
	PK_Users PRIMARY KEY NONCLUSTERED 
	(
	UserID
	) ON [PRIMARY]
GO

ALTER TABLE UserRoles WITH NOCHECK ADD CONSTRAINT
	FK_UserRoles_Users FOREIGN KEY
	(
	UserID
	) REFERENCES dbo.Users
	(
	UserID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

ALTER TABLE UserPortals WITH NOCHECK ADD CONSTRAINT
	FK_UserPortals_Users FOREIGN KEY
	(
	UserId
	) REFERENCES dbo.Users
	(
	UserID
	) NOT FOR REPLICATION
GO

update Users
set    Username = Email
GO

ALTER TABLE Users
	DROP CONSTRAINT DF_Users_Username
GO

drop procedure AddUser
GO

create procedure AddUser

@PortalId       int,
@FirstName	nvarchar(50),
@LastName	nvarchar(50),
@Unit		nvarchar(50),
@Street		nvarchar(50),
@City		nvarchar(50),
@Region		nvarchar(50),
@PostalCode	nvarchar(50),
@Country	nvarchar(50),
@Telephone      nvarchar(50),
@Email		nvarchar(100),
@Username	nvarchar(100),
@Password	nvarchar(50),
@Authorized     bit,
@UserID	int	OUTPUT

as

select	@UserID = UserID
from 	Users
where	Username = @Username 
and     Password = @Password

if @UserID is null
begin
  insert into Users (
    FirstName,
    LastName,
    Unit, 
    Street, 
    City,
    Region, 
    PostalCode,
    Country,
    Telephone,
    Email,
    Username,
    Password
  )
  values (
    @FirstName,
    @LastName,
    @Unit,
    @Street,
    @City,
    @Region,
    @PostalCode,
    @Country,
    @Telephone,
    @Email,
    @Username,
    @Password
  )

  select @UserID = @@IDENTITY
end

if @@ERROR = 0
begin
  insert into UserPortals (
    UserId,
    PortalId,
    Authorized,
    CreatedDate
  )
  values (
    @UserId,
    @PortalId,
    @Authorized,
    getdate()
  )
end

GO

drop procedure UpdateUser
GO

create procedure UpdateUser

@PortalId       int,
@UserID         int,
@FirstName	nvarchar(50),
@LastName	nvarchar(50),
@Unit		nvarchar(50),
@Street		nvarchar(50),
@City	        nvarchar(50),
@Region	        nvarchar(50),
@PostalCode	nvarchar(50),
@Country	nvarchar(50),
@Telephone	nvarchar(50),
@Email		nvarchar(100),
@Username       nvarchar(100),
@Password	nvarchar(50) = null,
@Authorized     bit = null

as

update Users
set    FirstName = @FirstName,
       LastName	 = @LastName,
       Unit	 = @Unit,
       Street	 = @Street,
       City	 = @City,
       Region	 = @Region,
       PostalCode = @PostalCode,
       Country	 = @Country,
       Telephone = @Telephone,
       Email	 = @Email,
       Username	 = @Username,
       Password	 = isnull(@Password,Password)
where  UserId = @UserID

if @Authorized is not null
begin
  update UserPortals
  set    Authorized = @Authorized
  where  PortalId = @PortalId
  and    userId = @UserId
end

GO

drop procedure GetUsers
GO

create procedure GetUsers

@PortalId int,
@Filter   nvarchar(1)

as

if @PortalID is null
begin
  select Users.*,
         'FullName' = Users.FirstName + ' ' + Users.LastName
  from   Users
  order by UserID
end
else
begin
  select Users.UserID,
         Users.Username,
         Users.Email,
         'FullName' = Users.FirstName + ' ' + Users.LastName,
         Users.FirstName,
         Users.LastName,
         Users.Unit,
         Users.Street,
         Users.City,
         Users.Region,
         Users.PostalCode,
         Users.Country,
         'Authorized' = case when UserPortals.Authorized = 1 then 'Y' else 'N' end,
         UserPortals.CreatedDate,
         UserPortals.LastLoginDate
  from   Users
  inner join UserPortals on Users.UserId = UserPortals.UserId
  where  UserPortals.PortalId = @PortalId
  and    Users.FirstName like @Filter + '%'
  order  by 'FullName'
end
GO

drop procedure GetSingleUser
GO

create procedure GetSingleUser

@PortalId int,
@UserId int

as

select Users.UserID,
       Users.Username,
       Users.Password,
       Users.Email,
       'FullName' = Users.FirstName + ' ' + Users.LastName,
       Users.FirstName,
       Users.LastName,
       Users.Unit,
       Users.Street,
       Users.City,
       Users.Region,
       Users.PostalCode,
       Users.Country,
       Users.Telephone,
       Users.IsSuperUser,
       UserPortals.Authorized,
       UserPortals.CreatedDate,
       UserPortals.LastLoginDate
from   Users
left outer join UserPortals on Users.UserId = UserPortals.UserId
where  Users.UserId = @UserId
and    (UserPortals.PortalId = @PortalId or Users.IsSuperUser = 1)

GO

drop procedure GetSingleUserByEmail
GO

create procedure GetSingleUserByUsername

@PortalID int,
@Username nvarchar(100)

as
 
select Users.UserId,
       Users.Username,
       Users.Password,
       Users.Email,
       'FullName' = Users.FirstName + ' ' + Users.LastName,
       Users.FirstName,
       Users.LastName,
       Users.Unit,
       Users.Street,
       Users.City,
       Users.Region,
       Users.PostalCode,
       Users.Country,
       Users.Telephone,
       Users.IsSuperUser,
       UserPortals.Authorized,
       UserPortals.CreatedDate,
       UserPortals.LastLoginDate
from   Users
left outer join UserPortals on Users.UserId = UserPortals.UserId
where  username = @Username
and    (UserPortals.PortalId = @PortalId or Users.IsSuperUser = 1)

GO

drop procedure UserLogin
GO

create procedure UserLogin

@Username nvarchar(100),
@Password nvarchar(50),
@PortalID int

as

declare @UserId int
declare @SuperUserId int

select @SuperUserId = UserId
from   Users
where  IsSuperUser = 1