<?php if (!defined('VB_ENTRY')) die('Access denied.');
/*======================================================================*\
|| #################################################################### ||
|| # vBulletin 5.0.0 Beta 10
|| # ---------------------------------------------------------------- # ||
|| # Copyright 2000-2012 vBulletin Solutions Inc. All Rights Reserved. ||
|| # This file may not be redistributed in whole or significant part. # ||
|| # ---------------- VBULLETIN IS NOT FREE SOFTWARE ---------------- # ||
|| # http://www.vbulletin.com | http://www.vbulletin.com/license.html # ||
|| #################################################################### ||
\*======================================================================*/

/**
*
* @package vBulletin
* @version $Revision: 28823 $
* @since $Date: 2008-12-16 17:43:04 +0000 (Tue, 16 Dec 2008) $
* @copyright vBulletin Solutions Inc.
*/
class vBInstall_dB_MYSQL_QueryDefs extends vB_dB_QueryDefs
{

	/**
	* This class is called by the new vB_dB_Assertor database class
	* It does the actual execution. See the vB_dB_Assertor class for more information
	*
	* Note that there is no install package. Therefore the ONLY thing that should be in this are queries unique to
	* the install/upgrade process. Especially there should be no table definitions unless they are vB3/4 tables not used
	* in vB5.
	*
	**/

	/*Properties====================================================================*/

	//type-specific

	protected $db_type = 'MYSQL';

	protected $table_data = array(
		'socialgroup' => array('key'=> 'groupid','structure' => array( 'groupid', 'socialgroupcategoryid', 'name', 'description',
			'creatoruserid', 'dateline', 'members', 'picturecount', 'lastpost', 'lastposter', 'lastposterid', 'lastgmid',
			'visible', 'deleted', 'moderation', 'type', 'moderatedmembers', 'options', 'lastdiscussionid', 'discussions', 'lastdiscussion',
			'lastupdate', 'transferowner')),
		'socialgroupcategory' => array('key'=> 'socialgroupcategoryid','structure' => array('socialgroupcategoryid',
			'creatoruserid', 'title', 'description', 'displayorder', 'lastupdate', 'groups')),
		'socialgroupmember' => array('key'=> false,'structure' => array('userid', 'groupid', 'dateline', 'type')),
		'groupmessage' => array('key' => 'gmid','structure' => array('gmid', 'discussionid', 'postuserid',
			'postusername', 'dateline', 'state', 'title', 'pagetext', 'ipaddress', 'allowsmilie', 'reportthreadid')),
		'discussion' => array('key'=> 'discussionid','structure' => array('discussionid', 'groupid',
			'firstpostid', 'lastpostid', 'lastpost', 'lastposter', 'lastposterid', 'visible', 'deleted', 'moderation', 'subscribers')),
		'socialgroupicon' => array('key'=> false,'structure' => array('groupid', 'userid', 'filedata', 'extension',
			'dateline', 'width', 'height', 'thumbnail_filedata mediumblob', 'thumbnail_width', 'thumbnail_height')),
	);

	/** This is the definition for queries.
	 * **/
	protected $query_data = array(
		'getMaxPMSenderid' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(fromuserid) AS maxid FROM {TABLE_PREFIX}pmtext'),
		'getMaxPMRecipient' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(userid) AS maxid FROM {TABLE_PREFIX}pm'),
		'getMaxPMFolderUser' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(userid) AS maxid FROM {TABLE_PREFIX}messagefolder WHERE titlephrase = {titlephrase} '),
		'createPMFoldersSent' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}messagefolder(userid, titlephrase)
			SELECT distinct pmtext.fromuserid , \'sent_items\'
			FROM {TABLE_PREFIX}pmtext AS pmtext
			WHERE pmtext.fromuserid > {startat} AND pmtext.fromuserid < ({startat} + {batchsize} -1) ORDER BY pmtext.fromuserid'),
		'createPMFoldersMsg' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}messagefolder(userid, titlephrase)
			SELECT distinct pmtext.fromuserid , \'messages\'
			FROM {TABLE_PREFIX}pmtext AS pmtext
			WHERE pmtext.fromuserid > {startat} AND pmtext.fromuserid < ({startat} + {batchsize} -1) ORDER BY pmtext.fromuserid'),
		'importPMStarter' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}node(userid, authorname, parentid, contenttypeid, title, description, deleteuserid, deletereason, sticky,
			publishdate, created, oldid, oldcontenttypeid, routeid, inlist, protected, showpublished, showapproved, showopen, lastcontent)
			SELECT pmt.fromuserid, pmt.fromusername, {privateMessageChannel}, {privatemessageType}, pmt.title, pmt.title, 0, 0, 0,
			pmt.dateline, pmt.dateline, pmt.pmtextid, 9989, {pmRouteid}, 0, 1,1,1,1, pmt.dateline
			FROM {TABLE_PREFIX}pmtext AS pmt
			INNER JOIN (SELECT DISTINCT pmtextid FROM {TABLE_PREFIX}pm
				WHERE pmtextid > {startat} AND pmtextid < ({startat} + {batchsize} -1) AND parentpmid = 0
			)
			AS pm ON pm.pmtextid = pmt.pmtextid'),
		'getMaxPMStarter' =>array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(pmtextid) AS maxid
			FROM {TABLE_PREFIX}pm WHERE parentpmid = 0; '),
		'setPMStarter' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node SET starter = nodeid, lastcontentid = nodeid WHERE
 			oldcontenttypeid = {contenttypeid} AND oldid > {startat} AND oldid < ({startat} + {batchsize} -1)'),
		'importPMText' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}text(nodeid, rawtext)
			SELECT node.nodeid, pmtext.message
			FROM {TABLE_PREFIX}pmtext AS pmtext
			INNER JOIN {TABLE_PREFIX}node AS node ON node.oldid = pmtext.pmtextid AND oldcontenttypeid = {contenttypeid}
			WHERE node.oldid > {startat} AND node.oldid < ({startat} + {batchsize} -1)'),
		'importPMMessage' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}privatemessage (nodeid, msgtype)
			SELECT nodeid, \'message\'
			FROM {TABLE_PREFIX}node AS node
			WHERE node.oldid > {startat} AND node.oldid < ({startat} + {batchsize} -1) AND node.oldcontenttypeid = {contenttypeid}'),
		'importPMSent' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}sentto (nodeid, userid, folderid, msgread)
			SELECT DISTINCT node.nodeid, node.userid, f.folderid, 1
			FROM {TABLE_PREFIX}node AS node
			INNER JOIN {TABLE_PREFIX}pmtext AS pmt ON pmt.pmtextid = node.oldid AND node.oldcontenttypeid = {contenttypeid}
			INNER JOIN {TABLE_PREFIX}messagefolder AS f ON f.userid = node.userid AND f.titlephrase = \'sent_items\'
			WHERE node.oldid > {startat} AND node.oldid < ({startat} + {batchsize} -1)'),
		'importPMInbox' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}sentto (nodeid, userid, folderid, msgread)
			SELECT DISTINCT node.nodeid, pm.userid, f.folderid,
			MAX(CASE WHEN pm.messageread > 0 THEN 1 ELSE 0 END) AS msgread
			FROM {TABLE_PREFIX}node AS node
			INNER JOIN {TABLE_PREFIX}pm AS pm ON pm.pmtextid = node.oldid AND node.oldcontenttypeid = {contenttypeid}
			INNER JOIN {TABLE_PREFIX}pmtext AS pmt ON pmt.pmtextid = pm.pmtextid AND pm.userid <> pmt.fromuserid
			INNER JOIN {TABLE_PREFIX}messagefolder AS f ON f.userid = pm.userid AND f.titlephrase = \'messages\'
			WHERE node.oldid > {startat} AND node.oldid < ({startat} + {batchsize} -1)
			GROUP BY node.nodeid, pm.userid, f.folderid'),
		'getMaxPMResponse' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(pmtextid) AS maxid FROM {TABLE_PREFIX}pm WHERE parentpmid > 0'),
		'importPMResponse' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}node(userid, authorname, parentid, contenttypeid, title, description, deleteuserid, deletereason, sticky,
			publishdate, created, oldid, oldcontenttypeid, routeid, inlist, protected, starter)
			SELECT pmt.fromuserid, pmt.fromusername, node.nodeid, {privatemessageType}, pmt.title, pmt.title, 0, 0, 0,
			pmt.dateline, pmt.dateline, pmt.pmtextid, 9981, node.routeid, 0, 1, node.starter
			FROM {TABLE_PREFIX}pmtext AS pmt
			INNER JOIN
			(SELECT pmtextid, min(parentpmid) AS parentpmid FROM {TABLE_PREFIX}pm
				WHERE pmtextid > {startat} AND pmtextid < ({startat} + {batchsize} -1) GROUP BY pmtextid HAVING min(parentpmid) > 0
			)
			AS response ON response.pmtextid = pmt.pmtextid
			INNER JOIN {TABLE_PREFIX}pm AS pm ON pm.pmid = response.parentpmid
			INNER JOIN {TABLE_PREFIX}node AS node ON node.oldid = pm.pmtextid  AND node.oldcontenttypeid = 9989
			WHERE node.nodeid > {maxNodeid}'),
		'getMaxPMNodeid' =>array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(nodeid) AS maxid FROM {TABLE_PREFIX}node WHERE oldcontenttypeid in (9981, 9989)'),
		'createClosureSelf' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}closure(parent, child, depth, publishdate)
			SELECT node.nodeid, node.nodeid, 0, node.publishdate FROM {TABLE_PREFIX}node AS node
			LEFT JOIN {TABLE_PREFIX}closure AS existing on node.nodeid = existing.child AND existing.depth = 0
			WHERE node.oldcontenttypeid = {oldcontenttype} AND existing.child IS NULL'),
		'createClosurefromParent' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}closure(parent, child, depth, publishdate)
			SELECT parent.parent, node.nodeid, parent.depth + 1, node.publishdate FROM {TABLE_PREFIX}node AS node
 			 INNER JOIN {TABLE_PREFIX}closure AS parent ON parent.child = node.parentid
			LEFT JOIN {TABLE_PREFIX}closure AS existing on existing.child = node.nodeid AND existing.parent = parent.parent
			WHERE node.oldcontenttypeid = {oldcontenttype} AND existing.child IS NULL' ),
		'runClosureAgain' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT parent.parent FROM {TABLE_PREFIX}node AS node
 			 INNER JOIN {TABLE_PREFIX}closure AS parent ON parent.child = node.parentid
			LEFT JOIN {TABLE_PREFIX}closure AS existing on existing.child = node.nodeid AND existing.parent = parent.parent
			WHERE node.oldcontenttypeid = {oldcontenttype} AND existing.child IS NULL
			LIMIT 1'),
		'getMissingGroupCategories' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT cat.* FROM {TABLE_PREFIX}socialgroupcategory AS cat
			LEFT JOIN {TABLE_PREFIX}node AS node ON node.oldcontenttypeid = 9988
			AND node.oldid = cat.socialgroupcategoryid WHERE node.nodeid IS NULL'),
		'getMissingSocialGroups' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT sgroup.*, category.nodeid AS categoryid, user.userid, user.username, transfer.userid AS transferuserid,
			transfer.username AS transferusername, route.routeid
			FROM {TABLE_PREFIX}socialgroup AS sgroup
			INNER JOIN {TABLE_PREFIX}node AS category ON category.oldcontenttypeid = 9988 AND category.oldid = sgroup.socialgroupcategoryid
			INNER JOIN {TABLE_PREFIX}user AS user ON user.userid = sgroup.creatoruserid
			LEFT JOIN {TABLE_PREFIX}user AS transfer ON user.userid = sgroup.transferowner
			LEFT JOIN {TABLE_PREFIX}node AS node ON node.oldcontenttypeid = {socialgroupType} AND node.oldid = sgroup.groupid
			LEFT JOIN {TABLE_PREFIX}routenew AS route ON route.routeid = category.routeid
			WHERE node.nodeid IS NULL
			LIMIT {batch_size}'),
		'getSocialGroupsCount' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT COUNT(*) AS total
			FROM {TABLE_PREFIX}socialgroup AS sgroup
			INNER JOIN {TABLE_PREFIX}node AS category ON category.oldcontenttypeid = 9988 AND category.oldid = sgroup.socialgroupcategoryid
			LEFT JOIN {TABLE_PREFIX}node AS node ON node.oldcontenttypeid = {socialgroupType} AND node.oldid = sgroup.groupid
			WHERE node.nodeid IS NULL'),
		'getImportedGroupsCount' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT COUNT(*) AS total
			FROM {TABLE_PREFIX}node AS n
			INNER JOIN {TABLE_PREFIX}closure AS cl ON (n.nodeid = cl.child)
			WHERE cl.parent = {parentid} AND n.contenttypeid = {channeltype} AND cl.depth > 1'),
		'getMaxSGDiscussionID' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(discussionid) AS maxid FROM {TABLE_PREFIX}discussion WHERE deleted = 0'),
		'getMaxSGPhotoID' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(attachmentid) AS maxid FROM {TABLE_PREFIX}attachment WHERE contenttypeid = {grouptypeid}'),
		'getMaxSGGallery' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(galleryid) AS maxid
			FROM {TABLE_PREFIX}socialgroup AS sg
			INNER JOIN (
				SELECT contentid AS galleryid
				FROM {TABLE_PREFIX}attachment
				WHERE contenttypeid = {grouptypeid}
			)
			AS gallerycheck ON gallerycheck.galleryid = sg.groupid'
		),
		'importSGDiscussions' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => "INSERT INTO {TABLE_PREFIX}node(userid, authorname, parentid, contenttypeid, title,
			description, deleteuserid, deletereason, sticky, publishdate,
			oldid, oldcontenttypeid, routeid, inlist, protected,
			showpublished, showapproved, showopen,textcount, totalcount,
			textunpubcount, totalunpubcount, lastcontent, lastcontentauthor, lastauthorid,
			ipaddress, created)
			SELECT gm.postuserid, gm.postusername, n.nodeid AS parentid, {textTypeid}, gm.title,
			'', 0, '', 0 AS sticky, CASE WHEN (d.deleted = 0 AND d.moderation = 0) THEN gm.dateline ELSE 0 END AS publishdate,
			d.discussionid, {discussionTypeid}, n.routeid, 1, 0,
			CASE WHEN (d.moderation = 0) THEN 1 ELSE 0 END AS showpublished,
			CASE WHEN (d.moderation = 0) THEN 1 ELSE 0 END AS showapproved,
			1, d.visible, d.visible,
			d.moderation, d.moderation, d.lastpost, d.lastposter, d.lastposterid,
			gm.ipaddress, gm.dateline
			FROM {TABLE_PREFIX}discussion AS d
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = d.groupid AND n.oldcontenttypeid = {grouptypeid}
			INNER JOIN {TABLE_PREFIX}groupmessage AS gm ON gm.gmid = d.firstpostid
			WHERE d.deleted = 0 AND d.discussionid > {startat} AND d.discussionid < ({startat} + {batchsize} -1)" ),
		'importSGGalleryNode' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => "INSERT INTO {TABLE_PREFIX}node(userid, authorname, parentid, contenttypeid, title,
			description, deleteuserid, deletereason, sticky, publishdate,
			oldid, oldcontenttypeid, routeid, inlist, protected,
			showpublished, showapproved, showopen,textcount, totalcount,
			textunpubcount, totalunpubcount, lastcontent, lastcontentauthor, lastauthorid,
			ipaddress, created)
			SELECT sg.creatoruserid, user.username, n.nodeid AS parentid, {gallerytypeid}, n.title,
			n.description, 0, '', 0 AS sticky, sg.dateline,
			gallerycheck.galleryid, 9983, n.routeid, 1, 0,
			1, 1, 1, gallerycheck.pubcount, gallerycheck.pubcount,
			gallerycheck.unpubcount, gallerycheck.unpubcount, 0, '', 0,
			n.ipaddress, sg.dateline
			FROM {TABLE_PREFIX}socialgroup AS sg
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = sg.groupid AND n.oldcontenttypeid = {grouptypeid}
			INNER JOIN (
				SELECT contentid AS galleryid, SUM(CASE WHEN state = 'visible' THEN 1 ELSE 0 END) AS pubcount, SUM(CASE WHEN state = 'moderation' THEN 1 ELSE 0 END) AS unpubcount
				FROM {TABLE_PREFIX}attachment
				WHERE contenttypeid = {grouptypeid} AND contentid > {startat} AND contentid < ({startat} + {batchsize} - 1)
				GROUP BY galleryid
			)
			AS gallerycheck ON gallerycheck.galleryid = sg.groupid
			INNER JOIN {TABLE_PREFIX}user AS user ON sg.creatoruserid = user.userid"),
		'fixLastGalleryData' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
						'query_string' => "UPDATE {TABLE_PREFIX}node AS n
				INNER JOIN (SELECT nodeid, parentid, publishdate, oldid, oldcontenttypeid, authorname, userid FROM {TABLE_PREFIX}node
				WHERE oldid > {startat} AND oldid < ({startat} + {batchsize} - 1) AND oldcontenttypeid = 9987 ORDER BY publishdate DESC, nodeid DESC)
				AS photo ON photo.parentid = n.nodeid
				SET n.lastcontentid = (CASE WHEN photo.publishdate >= n.lastcontent THEN photo.nodeid ELSE n.lastcontentid END),
				n.lastcontent = (CASE WHEN photo.publishdate >= n.lastcontent THEN photo.publishdate ELSE n.lastcontent END),
				n.lastcontentauthor = (CASE WHEN photo.publishdate >= n.lastcontent THEN photo.authorname ELSE n.lastcontentauthor END),
				n.lastauthorid = (CASE WHEN photo.publishdate >= n.lastcontent THEN photo.userid ELSE n.lastauthorid END)"),
		'importSGGallery' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => "INSERT INTO {TABLE_PREFIX}gallery(nodeid, caption)
			SELECT n.nodeid, CONCAT({caption}, ' - ', n.title)
			FROM {TABLE_PREFIX}attachment AS a
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = a.contentid AND n.oldcontenttypeid = 9983
			WHERE a.contenttypeid = {grouptypeid} AND a.contentid > {startat} AND a.contentid < ({startat} + {batchsize} - 1)
			GROUP BY a.contentid"),
		'importSGText' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => "INSERT INTO {TABLE_PREFIX}text(nodeid, rawtext)
			SELECT n.nodeid, CONCAT({caption}, ' - ', n.title)
			FROM {TABLE_PREFIX}attachment AS a
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = a.contentid AND n.oldcontenttypeid = 9983
			WHERE a.contenttypeid = {grouptypeid} AND a.contentid > {startat} AND a.contentid < ({startat} + {batchsize} - 1)
			GROUP BY a.contentid"),
		'importSGPhotoNodes' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => "INSERT INTO {TABLE_PREFIX}node(userid, authorname, parentid, starter, contenttypeid, title,
			description, deleteuserid, deletereason, sticky, publishdate,
			oldid, oldcontenttypeid, routeid, inlist, protected,
			showpublished, showapproved, showopen,textcount, totalcount,
			textunpubcount, totalunpubcount, lastcontent, lastcontentauthor, lastauthorid,
			ipaddress, created)
			SELECT a.userid, u.username, n.nodeid AS parentid, n.nodeid AS starter, {phototypeid}, a.caption,
			'', 0, '', 0 AS sticky, CASE WHEN (a.state = 'visible') THEN a.dateline ELSE 0 END AS publishdate,
			a.attachmentid, 9987, 0, 0, 0,
			CASE WHEN (a.state = 'visible') THEN 1 ELSE 0 END AS showpublished,
			CASE WHEN (a.state = 'visible') THEN 1 ELSE 0 END AS showapproved,
			1, 0, 0, 0, 0, a.dateline, '', 0,
			n.ipaddress, a.dateline
			FROM {TABLE_PREFIX}attachment AS a
			INNER JOIN {TABLE_PREFIX}user AS u ON u.userid = a.userid
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = a.contentid AND n.oldcontenttypeid = 9983 AND a.contenttypeid = {grouptypeid}
			WHERE a.attachmentid > {startat} AND a.attachmentid < ({startat} + {batchsize} - 1)"),
		'importSGPhotos' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => "INSERT INTO {TABLE_PREFIX}photo(nodeid, filedataid, caption, height, width)
			SELECT n.nodeid, a.filedataid, a.caption, fd.height, fd.width
			FROM {TABLE_PREFIX}attachment AS a
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = a.attachmentid AND n.oldcontenttypeid = 9987
			INNER JOIN {TABLE_PREFIX}filedata AS fd ON a.filedataid = fd.filedataid
			WHERE a.contenttypeid = {grouptypeid} AND a.attachmentid > {startat} AND a.attachmentid < ({startat} + {batchsize} - 1)"),
		'updateDiscussionLastContentId' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => "UPDATE {TABLE_PREFIX}node AS node
			INNER JOIN {TABLE_PREFIX}discussion AS d ON d.discussionid = node.oldid
			INNER JOIN {TABLE_PREFIX}node AS lm ON lm.oldid = d.lastpostid AND lm.oldcontenttypeid = {messageTypeid}
			SET node.lastcontentid = lm.nodeid
			WHERE node.oldcontenttypeid = {discussionTypeid} AND node.nodeid > {startat}
			AND node.nodeid < ({startat} + {batchsize} -1)" ),
		'importSGDiscussionText' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => "INSERT INTO {TABLE_PREFIX}text(nodeid, rawtext)
			SELECT n.nodeid, gm.pagetext
			FROM {TABLE_PREFIX}discussion AS d
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = d.discussionid AND n.oldcontenttypeid = {discussionTypeid}
			INNER JOIN {TABLE_PREFIX}groupmessage AS gm ON gm.gmid = d.firstpostid
			WHERE d.deleted = 0 AND d.discussionid > {startat} AND d.discussionid < ({startat} + {batchsize} -1)" ),
		'importSGPosts' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => "INSERT INTO {TABLE_PREFIX}node(userid, authorname, parentid, contenttypeid, title,
			description, deleteuserid, deletereason, sticky, publishdate,
			oldid, oldcontenttypeid, routeid, inlist, protected,
			showpublished, showapproved, showopen, ipaddress, starter, created)
			SELECT gm.postuserid, gm.postusername, n.nodeid AS parentid, {textTypeid}, gm.title,
			'', 0, '', 0 AS sticky, CASE WHEN (gm.state = 'visible') THEN gm.dateline ELSE 0 END AS publishdate,
			gm.gmid, {messageTypeid}, n.routeid, 1, 0,
			CASE WHEN (gm.state = 'visible') THEN 1 ELSE 0 END AS showpublished,
			CASE WHEN (gm.state = 'visible') THEN 1 ELSE 0 END AS showapproved,
			1, gm.ipaddress, n.starter, gm.dateline
			FROM {TABLE_PREFIX}groupmessage AS gm
			INNER JOIN {TABLE_PREFIX}discussion AS d ON gm.discussionid = d.discussionid AND gm.gmid <> d.firstpostid
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = gm.discussionid AND n.oldcontenttypeid = {discussionTypeid}
			WHERE gm.gmid > {startat} AND gm.gmid < ({startat} + {batchsize} -1)"),
		'importSGPostText' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => "INSERT INTO {TABLE_PREFIX}text(nodeid, rawtext)
			SELECT n.nodeid, gm.pagetext
			FROM {TABLE_PREFIX}groupmessage AS gm
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = gm.gmid AND n.oldcontenttypeid = {messageTypeid}
			WHERE gm.gmid > {startat} AND gm.gmid < ({startat} + {batchsize} -1)" ),
		'getMaxSGPost' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => "SELECT MAX(gmid) AS maxid
			FROM {TABLE_PREFIX}discussion AS d
			INNER JOIN {TABLE_PREFIX}groupmessage AS gm ON gm.gmid = d.firstpostid"),
		'addGroupOwners' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}groupintopic(userid, groupid, nodeid)
			SELECT n.userid, {groupid}, n.nodeid
			FROM {TABLE_PREFIX}node AS n
			LEFT JOIN {TABLE_PREFIX}groupintopic AS existing ON existing.userid = n.userid AND existing.groupid = {groupid} AND existing.nodeid = n.nodeid
			WHERE n.oldcontenttypeid = {socialgroupType} AND existing.groupid IS NULL' ),
		'addGroupMembers' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}groupintopic(userid, groupid, nodeid)
			SELECT member.userid, {groupid}, n.nodeid
			FROM {TABLE_PREFIX}socialgroupmember AS member INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = member.groupid AND n.oldcontenttypeid = {socialgroupType}
			LEFT JOIN {TABLE_PREFIX}groupintopic AS existing ON existing.userid = member.userid AND existing.nodeid = n.nodeid
			WHERE existing.groupid IS NULL' ),
		'getMissingSocialGroupPhotos' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT a.filedataid, a.contentid, a.caption, n.nodeid AS parentid, 9987 AS oldcontenttypeid, a.attachmentid AS oldid,
			n.userid, n.authorname, f.height, f.width
			FROM {TABLE_PREFIX}attachment AS a
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = a.contentid AND a.contenttypeid = n.oldcontenttypeid AND a.contenttypeid = {groupcontenttype}
			INNER JOIN {TABLE_PREFIX}filedata AS f ON f.filedataid = a.filedataid
			LEFT JOIN {TABLE_PREFIX}node AS existing ON existing.oldid = a.attachmentid AND n.oldcontenttypeid = 9987
			WHERE existing.nodeid IS NULL ORDER BY a.contentid' ),
		'getMax4VM' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(vmid) AS maxid FROM {TABLE_PREFIX}visitormessage'),
		'ImportVisitorMessages' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}node(userid, authorname, parentid, contenttypeid, title,
			description, deleteuserid, deletereason, sticky, publishdate,
			oldid, oldcontenttypeid, routeid, inlist, protected,
			showpublished, showapproved, showopen, ipaddress, setfor)
			SELECT vm.postuserid, vm.postusername, {vmChannel}, {texttypeid}, vm.title,
			\'\', 0, \'\', 0, CASE WHEN vm.state=\'visible\' THEN vm.dateline ELSE 0 END AS publishdate,
			vm.vmid AS oldid, {visitorMessageType} AS oldcontenttypeid, {vmRouteid}, 1, 0,
			CASE WHEN vm.state=\'visible\' THEN 1 ELSE 0 END AS showpublished, 1, 1, vm.ipaddress, vm.userid AS setfor
			FROM {TABLE_PREFIX}visitormessage AS vm
			WHERE vm.vmid > {startat} AND vm.vmid < ({startat} + {batchsize} -1) ' ),
		'importVMText' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}text(nodeid, rawtext)
			SELECT node.nodeid, vm.pagetext AS rawtext
			FROM {TABLE_PREFIX}visitormessage AS vm
			INNER JOIN {TABLE_PREFIX}node AS node ON node.oldid = vm.vmid AND node.oldcontenttypeid = {visitorMessageType}
			WHERE vm.vmid > {startat} AND vm.vmid < ({startat} + {batchsize} -1)' ),
		'getMaxvB4Album' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(albumid) AS maxid
			FROM {TABLE_PREFIX}album' ),
		'getMaxvB5Album' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(oldid) AS maxid	FROM {TABLE_PREFIX}node WHERE oldcontenttypeid = {albumtypeid}' ),
		'importAlbumNodes' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}node (publishdate, title, userid, authorname,htmltitle,
			parentid, created, oldid, oldcontenttypeid,`open`,
			showopen, approved, showapproved, showpublished, protected,
			routeid, contenttypeid, deleteuserid, deletereason, sticky)
			SELECT al.createdate, al.title, al.userid, u.username, al.title,
			{albumChannel}, al.createdate, al.albumid, {albumtype},1,
			1, 1, 1, 1, 0,
			{routeid}, {gallerytype}, 0, \'\', 0
			FROM {TABLE_PREFIX}album AS al INNER JOIN {TABLE_PREFIX}user AS u ON u.userid = al.userid
			WHERE al.albumid > {startat} AND al.albumid < ({startat} + {batchsize})'
			),
		'importAlbums2Gallery' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}gallery(nodeid, caption)
			SELECT nodeid, title
			FROM {TABLE_PREFIX}node WHERE oldcontenttypeid = {albumtype}
			AND oldid > {startat} AND oldid < ({startat} + {batchsize})'
		),
		'getMaxvB4Photo' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(attachmentid) AS maxid
			FROM {TABLE_PREFIX}attachment WHERE contenttypeid = {albumtype}' ),
		'importPhotoNodes' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}node(publishdate, title, userid, authorname,htmltitle,
			parentid, created, oldid, oldcontenttypeid,`open`,
			showopen, approved, showapproved, showpublished, protected,
			routeid, contenttypeid, deleteuserid, deletereason, sticky )
			SELECT at.dateline,CASE when at.caption IS NULL then at.filename ELSE at.caption END,
			at.userid, u.username,	CASE when at.caption IS NULL then at.filename ELSE at.caption END,
			n.nodeid AS parentid, at.dateline, at.attachmentid, 9986, 1,
			1, 1, 1, 1, 0,
			n.routeid, {phototype}, 0, \'\', 0
			FROM {TABLE_PREFIX}attachment AS at
			INNER JOIN {TABLE_PREFIX}user AS u ON u.userid = at.userid
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = at.contentid AND n.oldcontenttypeid = {albumtype} AND at.contenttypeid = {albumtype}
			WHERE at.attachmentid > {startat} AND at.attachmentid < ({startat} + {batchsize})'
		),
		'importPhotos2Gallery' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}photo(nodeid, filedataid, caption, height, width)
			SELECT n.nodeid, at.filedataid, CASE when at.caption IS NULL then at.filename ELSE at.caption END,
				f.height, f.width
			FROM {TABLE_PREFIX}attachment AS at
			INNER JOIN {TABLE_PREFIX}node AS n ON n.oldid = at.attachmentid AND n.oldcontenttypeid = 9986
			INNER JOIN {TABLE_PREFIX}filedata AS f ON f.filedataid = at.filedataid
			WHERE n.oldid > {startat} AND oldid < ({startat} + {batchsize})'
		),
		'createGenChannel' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}channel (nodeid, guid) SELECT nodeid, {guid}
			FROM {TABLE_PREFIX}node
			WHERE oldcontenttypeid = {oldcontenttypeid}'),
		'setModeratorNodeid' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS n
			INNER JOIN {TABLE_PREFIX}moderator AS m ON m.forumid = n.oldid AND n.oldcontenttypeid ={forumTypeId}
			 AND m.nodeid IS NULL
			SET m.nodeid = n.nodeid' ),
		'setModeratorlogThreadid' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS n
			INNER JOIN {TABLE_PREFIX}moderatorlog AS m ON m.threadid = n.oldid AND n.oldcontenttypeid ={threadTypeId}
			 AND m.nodeid IS NULL
			SET m.nodeid = n.nodeid' ),
		'setAccessNodeid' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS n
			INNER JOIN {TABLE_PREFIX}access AS a ON a.forumid = n.oldid AND n.oldcontenttypeid ={forumTypeId}
			 AND a.nodeid IS NULL
			SET a.nodeid = n.nodeid'),
		'getRootForumPerms' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT fp.usergroupid, f.forumid, fp.forumpermissions FROM
			{TABLE_PREFIX}forum AS f INNER JOIN {TABLE_PREFIX}forumpermission AS fp ON fp.forumid = f.forumid
			WHERE f.parentid < 1
			ORDER BY usergroupid, forumid' ),
		'getMaxImportedPost' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(oldid) AS maxid FROM {TABLE_PREFIX}node WHERE oldcontenttypeid = {contenttypeid}'),
		'getMaxImportedSGPhoto' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(n.oldid) AS maxid
			FROM {TABLE_PREFIX}node AS n
			INNER JOIN {TABLE_PREFIX}attachment AS a ON node.oldid = a.attachmentid AND a.contenttypeid = {grouptypeid}
			WHERE n.oldcontenttypeid = {phototypeid}'
		),
		'getMaxBlogUserId' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(userid) AS maxuserId FROM {TABLE_PREFIX}node WHERE oldcontenttypeid = {contenttypeid}'),
		'getBlogs4Import' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT b.userid, u.username, b.title, b.dateline, b.blogid
			FROM {TABLE_PREFIX}blog AS b
			INNER JOIN {TABLE_PREFIX}blog_text AS bt ON bt.blogtextid = b.firstblogtextid
			INNER JOIN {TABLE_PREFIX}user u ON b.userid = u.userid
			WHERE b.userid > {maxexisting}
			GROUP BY b.userid LIMIT {blocksize}'),
		'importBlogStarters' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}node(contenttypeid, parentid, title, keywords, htmltitle,
			publishdate, userid, authorname, oldid, oldcontenttypeid,
			showpublished, inlist, routeid, showapproved, textcount,
			totalcount, textunpubcount, totalunpubcount, lastcontent, lastcontentauthor,
			lastauthorid, created)
			SELECT {texttype}, parent.nodeid, bt.title, bt.title, bt.title,
			case WHEN bt.state = \'visible\' THEN bt.dateline else 0 end, blog.userid, blog.username, bt.blogtextid, 9985,
			case WHEN bt.state = \'visible\' THEN 1 else 0 end , case WHEN bt.state = \'visible\' THEN 1 else 0 end,
			parent.routeid, 1, blog.comments_visible,
			blog.comments_visible, blog.comments_moderation, blog.comments_moderation, blog.lastcomment, blog.lastcommenter,
			bt.username, bt.dateline
			FROM {TABLE_PREFIX}blog AS blog
			INNER JOIN {TABLE_PREFIX}blog_text AS bt ON bt.blogtextid = blog.firstblogtextid
			INNER JOIN {TABLE_PREFIX}node AS parent ON parent.userid = blog.userid AND parent.parentid = {bloghome}
				AND parent.oldcontenttypeid = 9999
			LEFT JOIN {TABLE_PREFIX}blog_text AS last ON last.blogtextid = blog.lastblogtextid
			WHERE bt.blogtextid > {startat} AND bt.blogtextid <({startat} + {batchsize} -1)
			ORDER BY bt.blogtextid'),
		'setStarter' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node
			SET starter = nodeid
			WHERE oldcontenttypeid = {contenttypeid} AND oldid > {startat}'),
		'addClosureSelf' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}closure (parent, child, depth)
			SELECT node.nodeid, node.nodeid, 0
			FROM {TABLE_PREFIX}node AS node
			WHERE node.oldcontenttypeid = {contenttypeid} AND node.oldid > {startat} AND node.oldid < ({startat} + {batchsize} -1)'),
		'addClosureParents' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}closure (parent, child, depth)
			SELECT parent.parent, node.nodeid, parent.depth + 1
			FROM {TABLE_PREFIX}node AS node
			INNER JOIN {TABLE_PREFIX}closure AS parent ON parent.child = node.parentid
			WHERE node.oldcontenttypeid = {contenttypeid} AND node.oldid > {startat} AND node.oldid < ({startat} + {batchsize} -1)'),
		'getProcessedCount' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT ROW_COUNT() AS recs'),
		'importBlogResponses' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}node(contenttypeid, parentid, starter, title, keywords, htmltitle,
			publishdate, userid, authorname, oldid, oldcontenttypeid, showpublished, showapproved, inlist, routeid, created)
			SELECT DISTINCT {texttypeid}, starter.nodeid, starter.nodeid,
			CASE WHEN IFNULL(bt.title, \'\') = \'\' THEN starter.title ELSE bt.title END,
			CASE WHEN IFNULL(bt.title, \'\') = \'\' THEN starter.title ELSE bt.title END,
			CASE WHEN IFNULL(bt.title, \'\') = \'\'THEN starter.title ELSE bt.title END,
			case WHEN bt.state = \'visible\' THEN bt.dateline else 0 end, bt.userid, bt.username, bt.blogtextid, 9984,
			case WHEN bt.state = \'visible\' THEN 1 else 0 end, case WHEN bt.state = \'visible\' THEN 1 else 0 end,
			1, starter.routeid, bt.dateline
			FROM {TABLE_PREFIX}blog AS blog
			INNER JOIN {TABLE_PREFIX}blog_text AS bt ON bt.blogid = blog.blogid AND bt.blogtextid <> blog.firstblogtextid
			INNER JOIN {TABLE_PREFIX}node AS starter ON starter.oldid = blog.firstblogtextid AND starter.oldcontenttypeid = 9985
			WHERE bt.blogtextid > {startat}
			ORDER BY bt.blogtextid
			LIMIT {batchsize}'),
		'importBlogTextNoState' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}text(nodeid, rawtext)
			SELECT n.nodeid, bt.pagetext
			FROM {TABLE_PREFIX}node AS n INNER JOIN {TABLE_PREFIX}blog_text AS bt
			ON bt.blogtextid = n.oldid AND n.oldcontenttypeid = {contenttypeid}
			WHERE bt.blogtextid > {startat}'),
		'importBlogText' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}text(nodeid, rawtext, htmlstate)
			SELECT n.nodeid, bt.pagetext, htmlstate
			FROM {TABLE_PREFIX}node AS n INNER JOIN {TABLE_PREFIX}blog_text AS bt
			ON bt.blogtextid = n.oldid AND n.oldcontenttypeid = {contenttypeid}
			WHERE bt.blogtextid > {startat}'),
		'updateForumLast' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'Update {TABLE_PREFIX}node AS n INNER JOIN {TABLE_PREFIX}forum AS f ON f.forumid = n.oldid AND n.oldcontenttypeid = {forumTypeid}
			INNER JOIN {TABLE_PREFIX}node AS lp ON lp.oldid = f.lastpostid AND lp.oldcontenttypeid = {postTypeid}
			SET n.textcount = f.replycount, n.totalcount = f.replycount, n.lastcontent = f.lastpost,
			n.lastcontentauthor = f.lastposter, n.lastauthorid = f.lastposterid, n.lastcontentid = lp.nodeid'),
		'updateForumLastThreadOnly' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'Update {TABLE_PREFIX}node AS n INNER JOIN {TABLE_PREFIX}forum AS f ON f.forumid = n.oldid AND n.oldcontenttypeid = {forumTypeid}
			INNER JOIN {TABLE_PREFIX}thread AS th ON th.forumid = f.forumid AND th.firstpostid = f.lastpostid
			INNER JOIN {TABLE_PREFIX}node AS lp ON lp.oldid = th.threadid AND lp.oldcontenttypeid = {threadTypeid}
			SET n.textcount = f.replycount, n.totalcount = f.replycount, n.lastcontent = f.lastpost,
			n.lastcontentauthor = f.lastposter, n.lastauthorid = f.lastposterid, n.lastcontentid = lp.nodeid'),
		'updateThreadLast' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'Update {TABLE_PREFIX}node AS n INNER JOIN {TABLE_PREFIX}thread AS th ON th.threadid = n.oldid
			INNER JOIN {TABLE_PREFIX}node AS lp ON lp.oldid = th.lastpostid AND lp.oldcontenttypeid = {threadTypeid}
			INNER JOIN {TABLE_PREFIX}post AS last ON last.postid = th.lastpostid
			SET n.textcount = th.replycount, n.totalcount = th.replycount, n.textunpubcount = th.hiddencount, n.totalunpubcount = th.hiddencount,
			n.lastcontent = th.lastpost, n.lastcontentauthor = last.username, n.lastauthorid = last.userid, n.lastcontentid = lp.nodeid
			WHERE n.oldcontenttypeid = 2 AND n.oldid > {startat} AND n.oldid < ({startat} + {batchsize} - 1)'),
		'insertCMSArticles' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}node(contenttypeid, parentid, title, description, keywords, htmltitle, urlident,
			publishdate, oldid, oldcontenttypeid, created, inlist, routeid, showpublished, showapproved, showopen)
			SELECT {textTypeId}, node.nodeid, ni.title, ni.description, ni.keywords, ni.html_title, n.url,
		 	n.publishdate, n.nodeid, n.contenttypeid, n.publishdate, 1, node.routeid, 1, 1, 1
			FROM {TABLE_PREFIX}cms_node AS n
			INNER JOIN {TABLE_PREFIX}cms_nodeinfo AS ni ON ni.nodeid = n.nodeid
			INNER JOIN {TABLE_PREFIX}node AS node ON node.oldid = n.parentnode AND node.oldcontenttypeid = {sectionTypeId}
			WHERE n.contenttypeid = {articleTypeId} AND n.nodeid > {startat} AND n.nodeid < ({startat} + {batchsize}) ORDER BY n.nodeid'),
		'insertCMSText' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}text(nodeid, previewtext,
			previewimage, previewvideo, imageheight, imagewidth,
			rawtext, htmlstate)
			SELECT node.nodeid, a.previewtext,
			a.previewimage, a.previewvideo, a.imageheight, a.imagewidth,
			a.pagetext, a.htmlstate
			FROM {TABLE_PREFIX}cms_node AS n
			INNER JOIN {TABLE_PREFIX}cms_nodeinfo AS ni ON ni.nodeid = n.nodeid
			INNER JOIN {TABLE_PREFIX}cms_article AS a ON a.contentid = n.contentid
			INNER JOIN {TABLE_PREFIX}node AS node ON node.oldid = n.nodeid AND node.oldcontenttypeid = {articleTypeId}
			WHERE n.nodeid > {startat} AND n.nodeid < ({startat} + {batchsize}) ORDER BY n.nodeid'),
		'insertCMSTextNoState' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}text(nodeid, previewtext,
			previewimage, previewvideo, imageheight, imagewidth,
			rawtext)
			SELECT node.nodeid, a.previewtext,
			a.previewimage, a.previewvideo, a.imageheight, a.imagewidth,
			a.pagetext
			FROM {TABLE_PREFIX}cms_node AS n
			INNER JOIN {TABLE_PREFIX}cms_nodeinfo AS ni ON ni.nodeid = n.nodeid
			INNER JOIN {TABLE_PREFIX}cms_article AS a ON a.contentid = n.contentid
			INNER JOIN {TABLE_PREFIX}node AS node ON node.oldid = n.nodeid AND node.oldcontenttypeid = {articleTypeId}
			WHERE n.nodeid > {startat} AND n.nodeid < ({startat} + {batchsize}) ORDER BY n.nodeid'),
		'updateChannelRoutes' =>array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS n
			INNER JOIN {TABLE_PREFIX}routenew AS pr ON pr.routeid = n.routeid AND pr.class = \'vB5_Route_Channel\'
			INNER JOIN {TABLE_PREFIX}routenew AS cr ON cr.prefix = pr.prefix AND cr.class = \'vB5_Route_Conversation\'
			SET n.routeid = cr.routeid
			WHERE n.oldcontenttypeid = {contenttypeid} AND n.oldid > {startat} AND n.oldid < ({startat} + {batchsize} + 1)'),
		'updateChannelCounts' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS parent INNER JOIN
			(SELECT parentid AS nodeid,
			SUM(totalcount) AS totalcount,
			MAX(lastcontent) AS lastcontent
			FROM {TABLE_PREFIX}node WHERE contenttypeid = {channeltypeid} AND parentid > 0
			GROUP BY parentid
			) AS summary ON summary.nodeid = parent.nodeid
			SET parent.totalcount = summary.totalcount,
			parent.lastcontent = summary.lastcontent
			WHERE contenttypeid = {channeltypeid} ;'),
		'updateChannelLast' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS parent
			INNER JOIN {TABLE_PREFIX}node AS child ON child.parentid = parent.nodeid
			SET parent.lastcontentid = CASE WHEN child.lastcontent >= parent.lastcontent THEN child.lastcontentid ELSE parent.lastcontentid END
			WHERE parent.contenttypeid = {channeltypeid}
			 AND child.nodeid > {startat} AND child.nodeid < ({startat} + {batchsize})'),
		'updateCategoryLast' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS parent
			INNER JOIN {TABLE_PREFIX}node AS child ON child.parentid = parent.nodeid AND child.contenttypeid = {channeltypeid}
			SET parent.lastcontentid = CASE WHEN child.lastcontent >= parent.lastcontent THEN child.lastcontentid ELSE parent.lastcontentid END
			WHERE parent.contenttypeid = {channeltypeid}'),
		'getMaxNodeid' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(nodeid) AS maxid FROM {TABLE_PREFIX}node'),
		'updateModeratorPermission' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}permission AS p
 			INNER JOIN {TABLE_PREFIX}usergroup AS ug ON ug.usergroupid = p.groupid
			SET p.moderatorpermissions = p.moderatorpermissions | {modPermissions} WHERE ug.systemgroupid IN ({systemgroups}) AND p.forumpermissions > 0'),
		'hidePasswordForums' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}permission (groupid, nodeid, forumpermissions, moderatorpermissions, createpermissions, edit_time,
				require_moderate, maxtags, maxstartertags, maxothertags, maxattachments)
			SELECT pwd.usergroupid, node.nodeid, 0, 0, 0, 5, 0, 0, 0, 0, 0
			FROM (SELECT ug.usergroupid, f.forumid
			FROM {TABLE_PREFIX}forum AS f, {TABLE_PREFIX}usergroup AS ug
			WHERE f.password IS NOT NULL AND f.password <> \'\') AS pwd
			INNER JOIN {TABLE_PREFIX}node AS node ON node.oldid = pwd.forumid AND node.oldcontenttypeid = {forumTypeid}
			LEFT JOIN {TABLE_PREFIX}permission AS ex ON ex.nodeid = node.nodeid AND ex.groupid = pwd.usergroupid
			WHERE ex.nodeid IS NULL'),
		'setForumPermissions' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}permission(groupid, nodeid, forumpermissions, moderatorpermissions, createpermissions, edit_time,
				require_moderate, maxtags, maxstartertags, maxothertags, maxattachments)
				SELECT fp.usergroupid, node.nodeid, fp.forumpermissions, 0,
				CASE WHEN (fp.forumpermissions & 16 > 0)
				THEN ( 2 | 2048 | 4096 | 8192 | 16384 | 32768 | 65536 | 131072 | 262144) ELSE 0 END AS createpermissions,
				CASE WHEN p.nodeid IS NULL THEN {editTime} ELSE p.edit_time END,
				CASE WHEN p.nodeid IS NULL THEN 0 ELSE p.require_moderate END,
				CASE WHEN p.nodeid IS NULL THEN {maxtags} ELSE p.maxtags END,
				CASE WHEN p.nodeid IS NULL THEN {maxstartertags} ELSE p.maxstartertags END,
				CASE WHEN p.nodeid IS NULL THEN {maxothertags} ELSE p.maxothertags END,
				CASE WHEN p.nodeid IS NULL THEN {maxattachments} ELSE p.maxattachments END
				FROM {TABLE_PREFIX}forumpermission AS fp
				INNER JOIN {TABLE_PREFIX}node AS node ON node.oldid = fp.forumid AND node.oldcontenttypeid = {forumTypeid}
				LEFT JOIN {TABLE_PREFIX}permission AS p ON p.groupid = fp.usergroupid AND p.nodeid = 1
				LEFT JOIN {TABLE_PREFIX}permission AS ex ON ex.nodeid = node.nodeid AND ex.groupid = fp.usergroupid
				WHERE ex.nodeid IS NULL ORDER BY node.nodeid, fp.usergroupid'),
		'clearUserStyle'=> array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}user SET styleid = 0'),
		'missingClosureByType'=> array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT nodeid
			FROM {TABLE_PREFIX}node AS node
			LEFT JOIN {TABLE_PREFIX}closure AS closure ON closure.child = node.nodeid AND closure.depth = 0
			WHERE closure.parent IS NULL AND oldcontenttypeid = {oldcontenttypeid} LIMIT {batchsize} '),
		'addClosureSelfForNodes' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}closure (parent, child, depth)
			SELECT node.nodeid, node.nodeid, 0
			FROM {TABLE_PREFIX}node AS node
			WHERE node.nodeid IN ({nodeid})'),
		'addClosureParentsForNodes' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}closure (parent, child, depth)
			SELECT parent.parent, node.nodeid, parent.depth + 1
			FROM {TABLE_PREFIX}node AS node
			INNER JOIN {TABLE_PREFIX}closure AS parent ON parent.child = node.parentid
			WHERE node.nodeid IN ({nodeid})'),
		'updateBlogModerated' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'update {TABLE_PREFIX}node AS node INNER JOIN {TABLE_PREFIX}blog_text AS bt
			ON bt.blogtextid = node.oldid AND node.oldcontenttypeid = 9984
			SET node.showpublished = 0, node.showapproved = 0, node.publishdate = 0
			WHERE bt.state <> \'visible\''),
		'updateBlogCounts' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'update {TABLE_PREFIX}node AS node INNER JOIN
			(
			 select parentid, count(*) AS count, sum(showpublished) AS textcount
			 FROM {TABLE_PREFIX}node WHERE oldcontenttypeid = 9984
			 GROUP BY parentid
			) as ch ON ch.parentid = node.nodeid
			SET node.textcount = ch.textcount,node.totalcount = ch.textcount,
			node.textunpubcount = (ch.count - ch.textcount),node.totalunpubcount = (ch.count - ch.textcount)'),
	);


}
