Here we detail some scripts which could be useful for converting your database to version 6.0 status
CREATE TABLE [GXA0017] (
[PageImportId] INT NOT NULL,
[PageImportName] NVARCHAR(100) NOT NULL,
[PageImportTitle] NCHAR(100) NOT NULL,
[PageImportType] SMALLINT NOT NULL,
[PageImportFlag] BIT NOT NULL,
[PageImportPageId] INT NULL)
Run conversion program for table ImportTempTable
CALL sp_rename('[GXA0017]', 'PageImport')
ALTER TABLE [PageImport]
ADD PRIMARY KEY ( [PageImportId] )
CREATE NONCLUSTERED INDEX [IIMPORTTEMPTABLE1] ON [PageImport] (
[PageImportPageId])
CREATE TABLE `PageImport` (`PageImportId` int NOT NULL , `PageImportName` national varchar(100) NOT NULL , `PageImportTitle` national varchar(100) NOT NULL , `PageImportType` smallint NOT NULL , `PageImportFlag` BOOL NOT NULL , `PageImportPageId` int , PRIMARY KEY(`PageImportId`)) ENGINE=InnoDB
CREATE INDEX `IIMPORTTEMPTABLE1` ON `PageImport` (`PageImportPageId` )
CREATE TABLE [PageToEdit] (
[PageId] INT NOT NULL,
[PageVersionId1] INT NOT NULL,
[PageToEditId1] INT NOT NULL,
[PageToEditUserId] INT NOT NULL,
[PageToEditStartTime] DATETIME NOT NULL,
[PageToEditEndTime] DATETIME NULL,
PRIMARY KEY ( [PageId],[PageVersionId1],[PageToEditId1] ))
CREATE NONCLUSTERED INDEX [IPAGETOEDIT2] ON [PageToEdit] (
[PageToEditUserId])
CREATE TABLE `PageToEdit` (
`PageId` INT NOT NULL,
`PageVersionId1` INT NOT NULL,
`PageToEditId1` INT NOT NULL,
`PageToEditUserId` INT NOT NULL,
`PageToEditStartTime` DATETIME NOT NULL,
`PageToEditEndTime` DATETIME,
PRIMARY KEY ( `PageId`,`PageVersionId1`,`PageToEditId1` ))
ENGINE = INNODB
CREATE INDEX `IPAGETOEDIT2` ON `PageToEdit` (`PageToEditUserId` )
CREATE TABLE [TPageFeedback] (
[PageFeedbackId] INT NOT NULL IDENTITY ( 1 , 1 ),
[PageId] INT NOT NULL,
[PageVersionId1] INT NOT NULL,
[UserId] INT NULL,
[PageFeedbackComment] NVARCHAR(256) NULL,
[PageFeedbackType] SMALLINT NULL,
[PageFeedbackDate] DATETIME NOT NULL,
[PageFeedbackIsResolved] BIT NOT NULL,
[PageFeedbackSolutionComment] NVARCHAR(256) NULL,
[PageFeedbackSolutionAuthor] INT NULL,
PRIMARY KEY ( [PageFeedbackId] ))
CREATE NONCLUSTERED INDEX [ITPAGEFEEDBACK1] ON [TPageFeedback] (
[UserId])
CREATE NONCLUSTERED INDEX [ITPAGEFEEDBACK3] ON [TPageFeedback] (
[PageFeedbackSolutionAuthor])
CREATE NONCLUSTERED INDEX [ITPAGEFEEDBACK2] ON [TPageFeedback] (
[PageId],
[PageVersionId1])
CREATE TABLE `TPageFeedback` (`PageFeedbackId` int NOT NULL AUTO_INCREMENT, `PageId` int NOT NULL , `PageVersionId1` int NOT NULL , `UserId` int , `PageFeedbackComment` national varchar(256) , `PageFeedbackType` smallint , `PageFeedbackDate` datetime NOT NULL , `PageFeedbackIsResolved` BOOL NOT NULL , `PageFeedbackSolutionComment` national varchar(256) , `PageFeedbackSolutionAuthor` int , PRIMARY KEY(`PageFeedbackId`)) ENGINE=InnoDB
CREATE INDEX `ITPAGEFEEDBACK1` ON `TPageFeedback` (`UserId` )
CREATE INDEX `ITPAGEFEEDBACK3` ON `TPageFeedback` (`PageFeedbackSolutionAuthor` )
CREATE INDEX `ITPAGEFEEDBACK2` ON `TPageFeedback` (`PageId` ,`PageVersionId1` )
CREATE TABLE [WikiParametersWikiDyn] (
[WikiParametersId] INT NOT NULL,
[WikiDynParameterId] NCHAR(60) NOT NULL,
[WikiDynParameterValue] NVARCHAR(200) NOT NULL,
PRIMARY KEY ( [WikiParametersId],[WikiDynParameterId] ))
CREATE TABLE `WikiParametersWikiDyn` (`WikiParametersId` int NOT NULL , `WikiDynParameterId` national char(60) NOT NULL , `WikiDynParameterValue` national varchar(200) NOT NULL , PRIMARY KEY(`WikiParametersId`, `WikiDynParameterId`)) ENGINE=InnoDB
PageVersionUserIP attribute is added
ALTER TABLE [PageVersion]
ADD [PageVersionUserIP] NCHAR(45) NULL
mySQL
ALTER TABLE `PageVersion`
ADD( `PageVersionUserIP` NATIONAL CHAR(45))
UserWebUserId, UserBindDate, UserIsBinded and UserLocalName are added
ALTER TABLE [User]
ADD [UserLocalName] NVARCHAR(100) NOT NULL CONSTRAINT UserLocalNameUser_DEFAULT DEFAULT '',
[UserIsBinded] BIT NOT NULL CONSTRAINT UserIsBindedUser_DEFAULT DEFAULT Convert(INT,1),
[UserBindDate] DATETIME NOT NULL CONSTRAINT UserBindDateUser_DEFAULT DEFAULT Convert(DATETIME,'17530101',112),
[UserWebUserId] NCHAR(50) NOT NULL CONSTRAINT UserWebUserIdUser_DEFAULT DEFAULT ''
ALTER TABLE [User]
DROP CONSTRAINT UserLocalNameUser_DEFAULT
ALTER TABLE [User]
DROP CONSTRAINT UserIsBindedUser_DEFAULT
ALTER TABLE [User]
DROP CONSTRAINT UserBindDateUser_DEFAULT
ALTER TABLE [User]
DROP CONSTRAINT UserWebUserIdUser_DEFAULT
CREATE NONCLUSTERED INDEX [IWEBUSER] ON [User] (
[UserWebUserId])
ALTER TABLE `User` ADD (`UserLocalName` national varchar(100) NOT NULL DEFAULT '', `UserIsBinded` BOOL NOT NULL DEFAULT 1, `UserBindDate` datetime NOT NULL DEFAULT '1000-01-01', `UserWebUserId` national char(50) NOT NULL DEFAULT '')
ALTER TABLE `User`
ALTER COLUMN `UserLocalName` DROP DEFAULT
ALTER TABLE `User`
ALTER COLUMN `UserIsBinded` DROP DEFAULT
ALTER TABLE `User`
ALTER COLUMN `UserBindDate` DROP DEFAULT
ALTER TABLE `User`
ALTER COLUMN `UserWebUserId` DROP DEFAULT
CREATE INDEX `IWEBUSER` ON `User` (`UserWebUserId` )
WikiIndexSource, WikiIndexUser, WikiIndexPwd and WikiEnableSocialOptions are added
ALTER TABLE [WikiParameters]
ADD [WikiEnableSocialOptions] BIT NOT NULL CONSTRAINT WikiEnableSocialOptionsWikiParameters_DEFAULT DEFAULT Convert(BIT,0),
[WikiIndexPwd] NVARCHAR(500) NOT NULL CONSTRAINT WikiIndexPwdWikiParameters_DEFAULT DEFAULT '',
[WikiIndexUser] NVARCHAR(500) NOT NULL CONSTRAINT WikiIndexUserWikiParameters_DEFAULT DEFAULT '',
[WikiIndexSource] NVARCHAR(500) NOT NULL CONSTRAINT WikiIndexSourceWikiParameters_DEFAULT DEFAULT ''
ALTER TABLE [WikiParameters]
DROP CONSTRAINT WikiEnableSocialOptionsWikiParameters_DEFAULT
ALTER TABLE [WikiParameters]
DROP CONSTRAINT WikiIndexPwdWikiParameters_DEFAULT
ALTER TABLE [WikiParameters]
DROP CONSTRAINT WikiIndexUserWikiParameters_DEFAULT
ALTER TABLE [WikiParameters]
DROP CONSTRAINT WikiIndexSourceWikiParameters_DEFAULT
ALTER TABLE `WikiParameters` ADD (`WikiEnableSocialOptions` BOOL NOT NULL DEFAULT 0, `WikiIndexPwd` national varchar(500) NOT NULL DEFAULT '', `WikiIndexUser` national varchar(500) NOT NULL DEFAULT '', `WikiIndexSource` national varchar(500) NOT NULL DEFAULT '')
ALTER TABLE `WikiParameters`
ALTER COLUMN `WikiEnableSocialOptions` DROP DEFAULT
ALTER TABLE `WikiParameters`
ALTER COLUMN `WikiIndexPwd` DROP DEFAULT
ALTER TABLE `WikiParameters`
ALTER COLUMN `WikiIndexUser` DROP DEFAULT
ALTER TABLE `WikiParameters`
ALTER COLUMN `WikiIndexSource` DROP DEFAULT
.
|