jive_forums_oracle_nntp_index.sql

Jive Forums 论坛源码 v4.2.3

/*
 * $RCSfile$
 * $Revision: 16628 $
 * $Date: 2005-06-22 15:39:42 -0700 (Wed, 22 Jun 2005) $
 *
 * Copyright (C) 1999-2005 Jive Software. All rights reserved. This software is the proprietary
 * information of Jive Software. Use is subject to license terms.
 */

/*
 * This script upgrades the values of the jiveForum.forumIndexCounter and
 * jiveMessage.forumIndex columns. This script is a special optimization
 * for Oracle installations with a large number of messages. The logic
 * executed below is also part of the upgrade process run in the admin
 * console but it's recommended you run this script if your dataset is large.
 *
 * If you have questions or need clarification, please search our support
 * forums or post a question at http://www.jivesoftware.com/jive/. 
 */

declare

	commitLimit number := 100000;
	commitCount number;
	rowsUpdated number;
	totalRowsUpdated number;
	currentForum number;
	currentSequence VARCHAR2(50);
	sql_stmt VARCHAR2(200);

	cursor jiveMessage_csr is
		SELECT messageID, forumID FROM jiveMessage  WHERE modValue > 0 ORDER BY forumID, creationDate ASC;


begin
	dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || 'starting nntp_index_upgrade');

	commitCount := 0;
	rowsUpdated := 0;
	totalRowsUpdated := 0;
	currentForum := 0;

	for jiveMessageRecd in jiveMessage_csr loop


		if ( currentForum != jiveMessageRecd.forumID ) then

			if  currentSequence IS NOT NULL then 
				execute immediate 'drop sequence ' || currentSequence;
			end if;

			currentForum := jiveMessageRecd.forumID;
			currentSequence := 'nntp_upgrade_' || to_char(currentForum);

			execute immediate 'create sequence ' || currentSequence;
		end if;

		sql_stmt := 'update jiveMessage set forumIndex = ' || currentSequence || '.nextval WHERE messageID = :1';
		execute immediate sql_stmt using jiveMessageRecd.messageID;

		rowsUpdated := rowsUpdated + 1;

		if(rowsUpdated >= commitLimit) then
			commit;
			totalRowsUpdated := totalRowsUpdated + rowsUpdated;
			rowsupdated := 0;
			commitCount := commitCount + 1;
		end if;		

	end loop;
	if(rowsUpdated > 0) then
		commit;
		totalRowsUpdated := totalRowsUpdated + rowsUpdated;
		rowsupdated := 0;
		commitCount := commitCount + 1;
	end if;

	if ( currentSequence IS NOT NULL ) then
		execute immediate 'drop sequence ' || currentSequence;
	end if;
	dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || 'ending nntp_index_upgrade');

exception
	when others then
	dbms_output.put_line(to_char(sqlcode)||' '||sqlerrm);
	raise_application_error(-20101, sqlerrm); 
	rollback;

	if currentSequence is not null then
		execute immediate 'drop sequence ' || currentSequence;
	end if;

end;
/