cs_1.0_to_1.1_upgrade.sql

解压即可使用

/*   Please Read  

For interim builds of CS, you may have an incorrect PK on aspnet_Membership. You should first try to delete:

Alter table aspnet_Membership Drop Constraint PK_aspnet_Membership

*/

if exists (select * from dbo.sysindexes where name = N'IX_aspnet_Membership' and id = object_id(N'[dbo].[aspnet_Membership]'))
drop index [dbo].[aspnet_Membership].[IX_aspnet_Membership]
Go
if exists (select * from dbo.sysindexes where name = N'aspnet_Membership_index' and id = object_id(N'[dbo].[aspnet_Membership]'))
drop index [dbo].[aspnet_Membership].[aspnet_Membership_index]
GO



Print 'Creating New User Tabel'
Create TABLE [dbo].[cs_Users] (
	[MembershipID] UNIQUEIDENTIFIER  NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId) ,
	[UserID] [int] IDENTITY (2100, 1) NOT NULL PRIMARY KEY CLUSTERED ,
	[ForceLogin] [bit] NOT NULL CONSTRAINT [DF_cs_User_ForceLogin] DEFAULT (0),
	[UserAccountStatus] [smallint] NOT NULL CONSTRAINT [DF_cs_User_Approved] DEFAULT (1),
	[AppUserToken] [varchar] (128) NULL ,
	[LastActivity] [datetime] NOT NULL CONSTRAINT [DF_cs_User_LastActivity] DEFAULT (getdate()),
	[LastAction] [nvarchar] (1024)  NULL CONSTRAINT [DF_cs_User_LastAction] DEFAULT (''),
	
)
Go        
Print 'Updated New User Table'
Set Identity_Insert cs_Users On
Insert cs_Users (MembershipID, UserID, ForceLogin, UserAccountStatus, AppUserToken, LastActivity, LastAction)
Select UserID, cs_UserID, cs_ForceLogin, cs_UserAccountStatus, cs_AppUserToken, cs_LastActivity, cs_LastAction FROM aspnet_Membership
Set Identity_Insert cs_Users OFF
Go

Alter table [dbo].[aspnet_Membership] Add [ApplicationID] UNIQUEIDENTIFIER
Go
Update aspnet_Membership 
Set [ApplicationID] = aspnet_Users.ApplicationID
From aspnet_Users
where aspnet_Membership.UserID = aspnet_Users.UserID
Go
Alter table [dbo].[aspnet_Membership] Alter Column [ApplicationID] UNIQUEIDENTIFIER NOT NULL
Go
ALTER TABLE [dbo].[aspnet_Membership] ADD Constraint [FK_aspnet_Applications] FOREIGN KEY
	(
		[ApplicationId]
	) REFERENCES [aspnet_Applications] (
		[ApplicationId]
	)
GO

ALTER TABLE cs_UserProfile ADD Constraint [FK_cs_Users] FOREIGN KEY  ([UserID]) REFERENCES [cs_Users] ([UserID])


GO



--If this fails, it is OK
Print 'If this single part fails it is OK'

if exists (select * from dbo.sysindexes where name = N'aspnet_Membership_index' and id = object_id(N'[dbo].[aspnet_Membership_index]'))
drop index [dbo].[aspnet_Membership].[aspnet_Membership_index]
Go

Go

 CREATE  CLUSTERED  INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership]([ApplicationId], [LoweredEmail]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
Print 'End Fail Safe'

Print 'Removing obsolete fields from the membership table'
-- drop any defaults from the CS columns on the aspnet_membership table
IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_Approved')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_Approved
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_ForceLogin')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_ForceLogin
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_ForcLogin')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_ForcLogin
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_LastAction')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_LastAction
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_membership_LastActivity')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_LastActivity
END

IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = 'D' and d.name = 'DF_aspnet_Membership_PasswordFormat')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_Membership_PasswordFormat
END

-- drop the special columns in aspnet_Membership
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_UserID' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_UserID]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_ForceLogin' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_ForceLogin]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_UserAccountStatus' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_UserAccountStatus]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_AppUserToken' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_AppUserToken]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_LastActivity' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_LastActivity]
END

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'cs_LastAction' and TABLE_NAME = 'aspnet_Membership' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_LastAction]
END

IF EXISTS(SELECT * FROM sysobjects where type = 'FN' and name = 'GetAnonymousUserID')
BEGIN
	DROP FUNCTION GetAnonymousUserID
end

IF EXISTS(SELECT * FROM sysobjects where type = 'FN' and name = 'HasReadPost')
begin
	DROP FUNCTION HasReadPost
end


ALTER TABLE [dbo].[aspnet_Profile] ALTER COLUMN [LastUpdatedDate] [datetime] NOT NULL

Go

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO


PRINT N'Dropping index [aspnet_UsersInRoles_index2] from [dbo].[aspnet_UsersInRoles]'
GO
DROP INDEX [dbo].[aspnet_UsersInRoles].[aspnet_UsersInRoles_index2]


GO
PRINT N'Dropping index [aspnet_Membership_index] from [dbo].[aspnet_Membership]'
GO
--DROP INDEX [dbo].[aspnet_Membership].[aspnet_Membership_index]
GO


PRINT N'Dropping index [aspnet_Roles_index1] from [dbo].[aspnet_Roles]'
GO
DROP INDEX [dbo].[aspnet_Roles].[aspnet_Roles_index1]


GO
PRINT N'Dropping index [aspnet_Users_Index] from [dbo].[aspnet_Users]'
GO
DROP INDEX [dbo].[aspnet_Users].[aspnet_Users_Index]


GO
PRINT N'Dropping index [aspnet_UsersInRoles_index1] from [dbo].[aspnet_UsersInRoles]'
GO
DROP INDEX [dbo].[aspnet_UsersInRoles].[aspnet_UsersInRoles_index1]


GO
PRINT N'Dropping [dbo].[vw_aspnet_SchemaVersions]'
GO
DROP VIEW [dbo].[vw_aspnet_SchemaVersions]


GO
PRINT N'Altering [dbo].[aspnet_Profile]'
GO
ALTER TABLE [dbo].[aspnet_Profile] ALTER COLUMN [LastUpdatedDate] [datetime] NOT NULL



GO

PRINT N'Creating [dbo].[aspnet_GetUtcDate]'
GO

CREATE PROCEDURE [dbo].aspnet_GetUtcDate
    @TimeZoneAdjustment INT,
    @DateNow            DATETIME OUTPUT
AS
BEGIN
    SELECT @DateNow = GETUTCDATE()
END
                                                                                                                                                                                                                                         


GO
PRINT N'Altering [dbo].[aspnet_RegisterSchemaVersion]'
GO

ALTER PROCEDURE [dbo].aspnet_RegisterSchemaVersion
    @Feature                   NVARCHAR(128),
    @CompatibleSchemaVersion   NVARCHAR(128),
    @IsCurrentVersion          BIT,
    @RemoveIncompatibleSchema  BIT
AS
BEGIN
    IF( @RemoveIncompatibleSchema = 1 )
    BEGIN
        DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
    END
    ELSE
    BEGIN
        IF( @IsCurrentVersion = 1 )
        BEGIN
            UPDATE dbo.aspnet_SchemaVersions
            SET IsCurrentVersion = 0
            WHERE Feature = LOWER( @Feature )
        END
    END

    INSERT  dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
    VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
END



GO
PRINT N'Altering [dbo].[vw_aspnet_Profiles]'
GO

  ALTER VIEW [dbo].[vw_aspnet_Profiles]
  AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
      [DataSize]=  DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
  FROM [dbo].[aspnet_Profile]
  


GO
PRINT N'Altering [dbo].[aspnet_CheckSchemaVersion]'
GO

ALTER PROCEDURE [dbo].aspnet_CheckSchemaVersion
    @Feature                   NVARCHAR(128),
    @CompatibleSchemaVersion   NVARCHAR(128)
AS
BEGIN
    IF (EXISTS( SELECT  *
                FROM    dbo.aspnet_SchemaVersions
                WHERE   Feature = LOWER( @Feature ) AND
                        CompatibleSchemaVersion = @CompatibleSchemaVersion ))
        RETURN 0

    RETURN 1
END



GO
PRINT N'Creating [dbo].[aspnet_UnRegisterSchemaVersion]'
GO

CREATE PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
    @Feature                   NVARCHAR(128),
    @CompatibleSchemaVersion   NVARCHAR(128)
AS
BEGIN
    DELETE FROM dbo.aspnet_SchemaVersions
        WHERE   Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
END



GO
PRINT N'Altering [dbo].[vw_aspnet_UsersInRoles]'
GO

  ALTER VIEW [dbo].[vw_aspnet_UsersInRoles]
  AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
  FROM [dbo].[aspnet_UsersInRoles]
  


GO
PRINT N'Altering [dbo].[aspnet_Applications_CreateApplication]'
GO

ALTER PROCEDURE [dbo].aspnet_Applications_CreateApplication
    @ApplicationName      NVARCHAR(256),
    @ApplicationId        UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

    IF(@ApplicationId IS NULL)
    BEGIN
        DECLARE @TranStarted   BIT
        SET @TranStarted = 0
        
        IF( @@TRANCOUNT = 0 )
        BEGIN
	        BEGIN TRANSACTION
	        SET @TranStarted = 1
        END
        ELSE
    	    SET @TranStarted = 0
        
        SELECT  @ApplicationId = ApplicationId 
        FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK) 
        WHERE LOWER(@ApplicationName) = LoweredApplicationName

        IF(@ApplicationId IS NULL)
        BEGIN
            SELECT  @ApplicationId = NEWID()
            INSERT  dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
            VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
        END
        
        
        IF( @TranStarted = 1 )
        BEGIN
            IF(@@ERROR = 0)
            BEGIN
	        SET @TranStarted = 0
	        COMMIT TRANSACTION
            END
            ELSE
            BEGIN 
                SET @TranStarted = 0
                ROLLBACK TRANSACTION
            END
        END
    END
END



GO
PRINT N'Altering [dbo].[aspnet_Roles]'
GO
ALTER TABLE [dbo].[aspnet_Roles] ALTER COLUMN [RoleId] [uniqueidentifier] NOT NULL



GO
PRINT N'Creating index [aspnet_Roles_index1] on [dbo].[aspnet_Roles]'
GO
CREATE UNIQUE CLUSTERED INDEX [aspnet_Roles_index1] ON [dbo].[aspnet_Roles] ([ApplicationId], [LoweredRoleName])


GO
PRINT N'Altering [dbo].[aspnet_Membership_GetUserByUserId]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_GetUserByUserId
    @UserId               UNIQUEIDENTIFIER,
    @TimeZoneAdjustment   INT,
    @UpdateLastActivity   BIT = 0
AS
BEGIN
    IF ( @UpdateLastActivity = 1 )
    BEGIN
        DECLARE @DateTimeNowUTC DATETIME
        EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @DateTimeNowUTC
        FROM     dbo.aspnet_Users
        WHERE    @UserId = UserId

        IF ( @@ROWCOUNT = 0 ) -- User ID not found
            RETURN -1
    END

    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate, m.LastLoginDate, u.LastActivityDate,
            m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
            m.LastLockoutDate
    FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   @UserId = u.UserId AND u.UserId = m.UserId

    IF ( @@ROWCOUNT = 0 ) -- User ID not found
       RETURN -1

    RETURN 0
END



GO
PRINT N'Altering [dbo].[aspnet_Membership_UpdateLastLoginAndActivityDates]'
GO
ALTER PROCEDURE dbo.aspnet_Membership_UpdateLastLoginAndActivityDates