onetforums_mysql_4_1_to_5_0.sql

来自「tapestry的例子」· SQL 代码 · 共 55 行

/**
  * Database script upgrading from onet forums 4.1 to 5.0
  * MYSQL script
  * Creation Date:2006/11/27 
  */
DROP INDEX onet_ix_fome_th ON onetForumMessage;
DROP INDEX onet_ix_fome_fxc ON onetForumMessage; 

CREATE INDEX onet_ix_fome_th ON onetForumMessage (threadID DESC);
CREATE INDEX onet_ix_fome_fxc ON onetForumMessage (threadID,modValue,delValue,key_id);
CREATE INDEX onet_ix_fome_fxh ON onetForumMessage (threadID,userID,modValue,delValue,creationTime ASC);

ALTER TABLE onetForumThread ADD COLUMN pollValue INTEGER NOT NULL DEFAULT 0 AFTER delValue;
ALTER TABLE onetForumThread ADD COLUMN pointType INTEGER NOT NULL DEFAULT 0 AFTER viewCount;
ALTER TABLE onetForumThread ADD COLUMN pointValue INTEGER NOT NULL DEFAULT 0 AFTER pointType;
CREATE INDEX onet_ix_foth_fxf ON onetForumThread (pointType,pointValue);

CREATE TABLE onetRate(
    messageID BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    rateType INTEGER NOT NULL,
    rateCount INTEGER NOT NULL,
    CONSTRAINT onet_pk_rate PRIMARY KEY(messageID,rateType)
)ENGINE=InnoDB;
CREATE INDEX onet_ix_mid ON onetRate(messageID);

CREATE TABLE onetRateUser(
    messageID BIGINT NOT NULL,
    userID BIGINT NOT NULL,
    CONSTRAINT onet_pk_raua PRIMARY KEY(messageID,userID)
)ENGINE=InnoDB;
CREATE INDEX onet_ix_raus ON onetRateUser(messageID);

CREATE TABLE onetForumThreadCom (
	key_id BIGINT NOT NULL,
	forumID BIGINT NOT NULL,
	modValue INTEGER NOT NULL,
	commendCount INTEGER NOT NULL,
	firstTime BIGINT NOT NULL,
	lastTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fotc PRIMARY KEY(key_id)
)ENGINE=InnoDB;
CREATE INDEX onet_ix_ftc_fxa ON onetForumThreadCom(forumID,modValue,lastTime);

/** Upgrading poll data */
UPDATE onetForumThread SET pollValue=1 WHERE onetForumThread.key_id IN (SELECT key_id FROM onetPoll);

/** Remove current skin/template settings */
DELETE FROM onetStyleProp;
DELETE FROM onetStyle;
DELETE FROM onetSkinProp;
DELETE FROM onetSkin;
DELETE FROM onetLayout;
DELETE FROM onetProperty WHERE propName = 'system.skins.initialized';