INT UNSIGNED NOT NULL DEFAULT '0',
	subfolders MEDIUMTEXT,
	pmfolders MEDIUMTEXT,
	buddylist MEDIUMTEXT,
	ignorelist MEDIUMTEXT,
	signature MEDIUMTEXT,
	searchprefs MEDIUMTEXT,
	rank MEDIUMTEXT,
	PRIMARY KEY (userid)
)
";
$schema['CREATE']['explain']['usertextfield'] = sprintf($vbphrase['create_table'], TABLE_PREFIX . "usertextfield");



$schema['CREATE']['query']['usertitle'] = "
CREATE TABLE " . TABLE_PREFIX . "usertitle (
	usertitleid SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	minposts INT UNSIGNED NOT NULL DEFAULT '0',
	title CHAR(250) NOT NULL DEFAULT '',
	PRIMARY KEY (usertitleid)
)
";
$schema['CREATE']['explain']['usertitle'] = sprintf($vbphrase['create_table'], TABLE_PREFIX . "usertitle");



$schema['CREATE']['query']['word'] = "
CREATE TABLE " . TABLE_PREFIX . "word (
	wordid INT UNSIGNED NOT NULL AUTO_INCREMENT,
	title CHAR(50) NOT NULL DEFAULT '',
	PRIMARY KEY (wordid),
	UNIQUE KEY title (title)
)
";
$schema['CREATE']['explain']['word'] = sprintf($vbphrase['create_table'], TABLE_PREFIX . "word");


$schema['CREATE']['query']['sigpic'] = "
CREATE TABLE " . TABLE_PREFIX . "sigpic (
	userid int(10) unsigned NOT NULL default '0',
	filedata mediumblob,
	dateline int(10) unsigned NOT NULL default '0',
	filename varchar(100) NOT NULL default '',
	visible smallint(6) NOT NULL default '1',
	filesize int(10) unsigned NOT NULL default '0',
	width smallint(5) unsigned NOT NULL default '0',
	height smallint(5) unsigned NOT NULL default '0',
	PRIMARY KEY  (userid)
)
";
$schema['CREATE']['explain']['sigpic'] = sprintf($vbphrase['create_table'], TABLE_PREFIX . "sigpic");

$schema['CREATE']['query']['profilevisitor'] = "
CREATE TABLE " . TABLE_PREFIX . "profilevisitor (
	userid INT UNSIGNED NOT NULL DEFAULT '0',
	visitorid INT UNSIGNED NOT NULL DEFAULT '0',
	dateline INT UNSIGNED NOT NULL DEFAULT '0',
	visible INT UNSIGNED NOT NULL DEFAULT '1',
	PRIMARY KEY (visitorid, userid),
	KEY userid (userid, visible, dateline)
)
";
$schema['CREATE']['explain']['profilevisitor'] = sprintf($vbphrase['create_table'], TABLE_PREFIX . "profilevisitor");



// ***************************************************************************************************************************

$altertabletype = array(
	'session'   => $tabletype,
	'cpsession' => $tabletype,

	// innodb tables are limited to about 10 varchar/blob/text fields http://bugs.mysql.com/bug.php?id=10035
	'language'  => 'MYISAM',
	'userfield' => 'MYISAM',
);

foreach ($altertabletype AS $table => $type)
{
	$schema['ALTER']['query']["$table"] = "ALTER TABLE " . TABLE_PREFIX . "$table $enginetype = $type";
	$schema['ALTER']['explain']["$table"] = sprintf($install_phrases['alter_table_type_x'], $table, $type);
}

// ***************************************************************************************************************************

$schema['INSERT']['query']['adminutil'] = "INSERT INTO " . TABLE_PREFIX . "adminutil (title, text) VALUES ('datastorelock', '0')";

$schema['INSERT']['explain']['adminutil'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "adminutil");



// Do not change this query, without modifying the datastore query below.
$schema['INSERT']['query']['attachmenttype'] = "
INSERT INTO " . TABLE_PREFIX . "attachmenttype (extension, mimetype, size, width, height, enabled, display, thumbnail) VALUES
('gif', '" . $db->escape_string(serialize(array('Content-type: image/gif'))) . "', '20000', '620', '280', '1', '0', '1'),
('jpeg', '" . $db->escape_string(serialize(array('Content-type: image/jpeg'))) . "', '20000', '620', '280', '1', '0', '1'),
('jpg', '" . $db->escape_string(serialize(array('Content-type: image/jpeg'))) . "', '100000', '0', '0', '1', '0', '1'),
('jpe', '" . $db->escape_string(serialize(array('Content-type: image/jpeg'))) . "', '20000', '620', '280', '1', '0', '1'),
('txt', '" . $db->escape_string(serialize(array('Content-type: plain/text'))) . "', '20000', '0', '0', '1', '2', '0'),
('png', '" . $db->escape_string(serialize(array('Content-type: image/png'))) . "', '20000', '620', '280', '1', '0', '1'),
('doc', '" . $db->escape_string(serialize(array('Content-type: application/msword'))) . "', '20000', '0', '0', '1', '0', '0'),
('pdf', '" . $db->escape_string(serialize(array('Content-type: application/pdf'))) . "', '20000', '0', '0', '1', '0', '1'),
('bmp', '" . $db->escape_string(serialize(array('Content-type: image/bitmap'))) . "', '20000', '620', '280', '1', '0', '0'),
('psd', '" . $db->escape_string(serialize(array('Content-type: unknown/unknown'))) . "', '20000', '0', '0', '1', '0', '1'),
('zip', '" . $db->escape_string(serialize(array('Content-type: application/zip'))) . "', '100000', '0', '0', '1', '0', '0')
";

$schema['INSERT']['explain']['attachmenttype'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "attachmenttype");



$schema['INSERT']['query']['attachmentcache'] = "INSERT INTO " . TABLE_PREFIX . "datastore (title, data, unserialize) VALUES ('attachmentcache', '" . $db->escape_string(serialize(array())) . "', 1)";
$schema['INSERT']['explain']['attachmentcache'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "datastore");



$schema['INSERT']['query']['bookmarksite'] = "
	INSERT INTO " . TABLE_PREFIX . "bookmarksite
		(title, active, displayorder, iconpath, url)
	VALUES
		('Digg',        1, 10, 'bookmarksite_digg.gif',        'http://digg.com/submit?phase=2&url={URL}&title={TITLE}'),
		('del.icio.us', 1, 20, 'bookmarksite_delicious.gif',   'http://del.icio.us/post?url={URL}&title={TITLE}'),
		('StumbleUpon', 1, 30, 'bookmarksite_stumbleupon.gif', 'http://www.stumbleupon.com/submit?url={URL}&title={TITLE}'),
		('Google',      1, 40, 'bookmarksite_google.gif',      'http://www.google.com/bookmarks/mark?op=edit&output=popup&bkmk={URL}&title={TITLE}')
";
$schema['INSERT']['explain']['bookmarksite'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "bookmarksite");



$schema['INSERT']['query']['calendar'] = "
INSERT INTO " . TABLE_PREFIX . "calendar (title, description, displayorder, neweventemail, moderatenew, startofweek, options, cutoff, eventcount, birthdaycount, startyear, endyear) VALUES
('" . $db->escape_string($install_phrases['default_calendar']) . "', '', 1, '" . serialize(array()) . "', 0, 1, 631, 40, 4, 4, " . (date('Y') - 3) . ", " . (date('Y') + 3) . ")
";

$schema['INSERT']['explain']['calendar'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "calendar");



$schema['INSERT']['query']['cron'] = "
INSERT INTO " . TABLE_PREFIX . "cron
	(nextrun, weekday, day, hour, minute, filename, loglevel, varname, volatile, product)
VALUES
(1053271660, -1, -1,  0, 'a:1:{i:0;i:1;}',           './includes/cron/birthday.php',        1, 'birthday',        1, 'vbulletin'),
(1053532560, -1, -1, -1, 'a:1:{i:0;i:56;}',          './includes/cron/threadviews.php',     0, 'threadviews',     1, 'vbulletin'),
(1053531900, -1, -1, -1, 'a:1:{i:0;i:25;}',          './includes/cron/promotion.php',       1, 'promotion',       1, 'vbulletin'),
(1053271720, -1, -1,  0, 'a:1:{i:0;i:2;}',           './includes/cron/digestdaily.php',     1, 'digestdaily',     1, 'vbulletin'),
(1053991800,  1, -1,  0, 'a:1:{i:0;i:30;}',          './includes/cron/digestweekly.php',    1, 'digestweekly',    1, 'vbulletin'),
(1053271820, -1, -1,  0, 'a:1:{i:0;i:2;}',           './includes/cron/subscriptions.php',   1, 'subscriptions',   1, 'vbulletin'),
(1053533100, -1, -1, -1, 'a:1:{i:0;i:5;}',           './includes/cron/cleanup.php',         0, 'cleanup',         1, 'vbulletin'),
(1053533200, -1, -1, -1, 'a:1:{i:0;i:10;}',          './includes/cron/attachmentviews.php', 0, 'attachmentviews', 1, 'vbulletin'),
(1053990180, -1, -1,  0, 'a:1:{i:0;i:3;}',           './includes/cron/activate.php',        1, 'activate',        1, 'vbulletin'),
(1053271600, -1, -1, -1, 'a:1:{i:0;i:15;}',          './includes/cron/removebans.php',      1, 'removebans',      1, 'vbulletin'),
(1053531600, -1, -1, -1, 'a:1:{i:0;i:20;}',          './includes/cron/cleanup2.php',        0, 'cleanup2',        1, 'vbulletin'),
(1053271600, -1, -1,  0, 'a:1:{i:0;i:0;}',           './includes/cron/stats.php',           0, 'stats',           1, 'vbulletin'),
(1053271600, -1, -1, -1, 'a:2:{i:0;i:25;i:1;i:55;}', './includes/cron/reminder.php',        0, 'reminder',        1, 'vbulletin'),
(1053533100, -1, -1,  0, 'a:1:{i:0;i:10;}',          './includes/cron/dailycleanup.php',    0, 'dailycleanup',    1, 'vbulletin'),
(1053271600, -1, -1, -1, 'a:2:{i:0;i:20;i:1;i:50;}', './includes/cron/infractions.php',     1, 'infractions',     1, 'vbulletin'),
(1053271600, -1, -1, -1, 'a:1:{i:0;i:10;}',          './includes/cron/ccbill.php',          1, 'ccbill',          1, 'vbulletin'),
(1053271600, -1, -1, -1, 'a:6:{i:0;i:0;i:1;i:10;i:2;i:20;i:3;i:30;i:4;i:40;i:5;i:50;}', './includes/cron/rssposter.php', 1, 'rssposter',1, 'vbulletin')
";

$schema['INSERT']['explain']['cron'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "cron");



$schema['INSERT']['query']['datastore'] = "INSERT INTO " . TABLE_PREFIX . "datastore (title, data, unserialize) VALUES ('products', '" . $db->escape_string(serialize(array('vbulletin' => '1'))) . "', 1)";

$schema['INSERT']['explain']['datastore'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "datastore");



// this query is used by the 370b6 upgrade script, so the REPLACE avoids errors
$schema['INSERT']['query']['faq'] = "
REPLACE INTO " . TABLE_PREFIX . "faq (faqname, faqparent, displayorder, volatile)
VALUES
('vb3_board_faq','faqroot',200,1),
('vb3_board_usage','vb3_board_faq',10,1),
('vb3_forums_threads_posts','vb3_board_usage',1,1),
('vb3_register','vb3_board_usage',2,1),
('vb3_search','vb3_board_usage',3,1),
('vb3_announcements','vb3_board_usage',4,1),
('vb3_thread_display','vb3_board_usage',5,1),
('vb3_new_posts','vb3_board_usage',6,1),
('vb3_rating_threads','vb3_board_usage',7,1),
('vb3_thread_tools','vb3_board_usage',8,1),
('vb3_tags','vb3_board_usage',9,1),
('vb3_cookies','vb3_board_usage',10,1),
('vb3_lost_passwords','vb3_board_usage',11,1),
('vb3_calendar','vb3_board_usage',12,1),
('vb3_members_list','vb3_board_usage',13,1),
('vb3_notifications','vb3_board_usage',14,1),
('vb3_quick_links','vb3_board_usage',15,1),
('vb3_contact_members','vb3_board_usage',16,1),
('vb3_rss_podcasting','vb3_board_usage',18,1),
('vb3_user_profile','vb3_board_faq',20,1),
('vb3_public_profile','vb3_user_profile',1,1),
('vb3_user_cp','vb3_user_profile',2,1),
('vb3_changing_details','vb3_user_profile',3,1),
('vb3_other_settings','vb3_user_profile',5,1),
('vb3_profile_custom','vb3_user_profile',6,1),
('vb3_social_groups','vb3_user_profile',7,1),
('vb3_friends_contacts','vb3_user_profile',8,1),
('vb3_albums','vb3_user_profile',9,1),
('vb3_private_messages','vb3_user_profile',10,1),
('vb3_subscriptions','vb3_user_profile',11,1),
('vb3_reputation','vb3_user_profile',12,1),
('vb3_reading_posting','vb3_board_faq',30,1),
('vb3_posting','vb3_reading_posting',1,1),
('vb3_replying','vb3_reading_posting',2,1),
('vb3_editing_deleting','vb3_reading_posting',3,1),
('vb3_polls','vb3_reading_posting',4,1),
('vb3_attachments','vb3_reading_posting',5,1),
('vb3_smilies','vb3_reading_posting',6,1),
('vb3_mods_admins','vb3_reading_posting',8,1),
('vb3_troublesome_users','vb3_board_usage',17,1),
('vb3_message_icons','vb3_reading_posting',7,1),
('vb3_signatures_avatars','vb3_user_profile',4,1)
";
$schema['INSERT']['explain']['faq'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "faq");



$schema['INSERT']['query']['forum'] = "
INSERT INTO " . TABLE_PREFIX . "forum
	(forumid, styleid, title, description, options, displayorder, replycount, lastpost, lastposter,
	lastthread, lastthreadid, lasticonid, threadcount, daysprune, newpostemail, newthreademail,
	parentid, parentlist, password, link, childlist, title_clean, description_clean)
VALUES
	(1, 0, '" . $db->escape_string($install_phrases['category_title']) . "', '" . $db->escape_string($install_phrases['category_desc']) . "',
	'86017', '1', '0', '0', '', '', '0', '0', '0', '-1', '', '', '-1', '1,-1', '', '', '1,2,-1',
	'" . $db->escape_string($install_phrases['category_title']) . "', '" . $db->escape_string($install_phrases['category_desc']) . "'),

	(2, 0, '" . $db->escape_string($install_phrases['forum_title']) . "', '" . $db->escape_string($install_phrases['forum_desc']) . "',
	'89799', '1', '0', '0', '', '', '0', '0', '0', '-1', '', '', '1', '2,1,-1', '', '', '2,-1',
	'" . $db->escape_string($install_phrases['forum_title']) . "', '" . $db->escape_string($install_phrases['forum_desc']) . "')
";

$schema['INSERT']['explain']['forum'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "forum");


$schema['INSERT']['query']['icon'] = "
INSERT INTO " . TABLE_PREFIX . "icon (title, iconpath, imagecategoryid, displayorder) VALUES
('{$install_phrases['posticon_1']}', 'images/icons/icon1.gif', '2', '1'),
('{$install_phrases['posticon_2']}', 'images/icons/icon2.gif', '2', '1'),
('{$install_phrases['posticon_3']}', 'images/icons/icon3.gif', '2', '1'),
('{$install_phrases['posticon_4']}', 'images/icons/icon4.gif', '2', '1'),
('{$install_phrases['posticon_5']}', 'images/icons/icon5.gif', '2', '1'),
('{$install_phrases['posticon_6']}', 'images/icons/icon6.gif', '2', '1'),
('{$install_phrases['posticon_7']}', 'images/icons/icon7.gif', '2', '1'),
('{$install_phrases['posticon_8']}', 'images/icons/icon8.gif', '2', '1'),
('{$install_phrases['posticon_9']}', 'images/icons/icon9.gif', '2', '1'),
('{$install_phrases['posticon_10']}', 'images/icons/icon10.gif', '2', '1'),
('{$install_phrases['posticon_11']}', 'images/icons/icon11.gif', '2', '1'),
('{$install_phrases['posticon_12']}', 'images/icons/icon12.gif', '2', '1'),
('{$install_phrases['posticon_13']}', 'images/icons/icon13.gif', '2', '1'),
('{$install_phrases['posticon_14']}', 'images/icons/icon14.gif', '2', '1')
";

$schema['INSERT']['explain']['icon'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "icon");



$schema['INSERT']['query']['imagecategory'] = "
INSERT INTO " . TABLE_PREFIX . "imagecategory (title, imagetype, displayorder) VALUES
('{$install_phrases['generic_smilies']}', 3, 1),
('{$install_phrases['generic_icons']}', 2, 1),
('{$install_phrases['generic_avatars']}', 1, 1)
";
$schema['INSERT']['explain']['imagecategory'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "imagecategory");



$schema['INSERT']['query']['language'] = "INSERT INTO " . TABLE_PREFIX . "language (title, languagecode, charset, decimalsep, thousandsep) VALUES ('{$install_phrases['master_language_title']}', '{$install_phrases['master_language_langcode']}', '{$install_phrases['master_language_charset']}', '{$install_phrases['master_language_decimalsep']}', '{$install_phrases['master_language_thousandsep']}')";
$schema['INSERT']['explain']['language'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "language");



$schema['INSERT']['query']['paymentapi'] = "
INSERT INTO " . TABLE_PREFIX . "paymentapi (title, currency, recurring, classname, active, settings) VALUES
('Paypal', 'usd,gbp,eur,aud,cad', 1, 'paypal', 0, '" . $db->escape_string(serialize(array(
	'ppemail' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'primaryemail' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	)
))) . "'),
('NOCHEX', 'gbp', 0, 'nochex', 0, '" . $db->escape_string(serialize(array(
	'ncxemail' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	)
))) . "'),
('Worldpay', 'usd,gbp,eur', 0, 'worldpay', 0, '" . $db->escape_string(serialize(array(
	'worldpay_instid' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'worldpay_password' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	)
))) . "'),
('Authorize.Net', 'usd,gbp,eur', 0, 'authorizenet', 0, '" . $db->escape_string(serialize(array(
	'authorize_loginid' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'txnkey' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'authorize_md5secret' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	)
))) . "'),
('2Checkout', 'usd', 0, '2checkout', 0, '" . $db->escape_string(serialize(array(
	'twocheckout_id' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'number'
	),
	'secret_word' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	)
))) . "'),
('Moneybookers', 'usd,gbp,eur,aud,cad', 0, 'moneybookers', 0, '" . $db->escape_string(serialize(array(
	'mbemail' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'mbsecret' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	)
))) . "'),
('CCBill', 'usd', 0, 'ccbill', 0, '" . $db->escape_string(serialize(array(
	'clientAccnum' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'clientSubacc' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'formName' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'secretword' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'username' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	),
	'password' => array(
		'type' => 'text',
		'value' => '',
		'validate' => 'string'
	)
))) . "')
";

$schema['INSERT']['explain']['paymentapi'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "paymentapi");



$schema['INSERT']['query']['profilefield'] = "
INSERT INTO " . TABLE_PREFIX . "profilefield (profilefieldid, required, hidden, maxlength, size, displayorder, editable, type, data, height, def, optional, searchable, memberlist, regex, form) VALUES
('1', '0', '0', '100', '25', '1', '1', 'input', '', '0', '0', '0', '1', '1', '', '0'),
('2', '0', '0', '100', '25', '2', '1', 'input', '', '0', '0', '0', '1', '1', '', '0'),
('3', '0', '0', '100', '25', '3', '1', 'input', '', '0', '0', '0', '1', '1', '', '0'),
('4', '0', '0', '100', '25', '4', '1', 'input', '', '0', '0', '0', '1', '1', '', '0')
";
$schema['INSERT']['explain']['profilefield'] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "profilefield");

if (!empty($customphrases) AND is_array($customphrases))
{
	foreach ($customphrases AS $fieldname => $phrase)
	{
		foreach ($phrase AS $varname => $text)
		{
			$schema['INSERT']['query']["$varname"] = "
			INSERT INTO " . TABLE_PREFIX . "phrase (languageid, fieldname, varname, text, product) VALUES
			(0, '$fieldname', '$varname', '" . $db->escape_string($text) . "', 'vbulletin')
			";
			$schema['INSERT']['explain']["$varname"] = sprintf($vbphrase['default_data_type'], TABLE_PREFIX . "phrase");
		}
	}
}

// *** MAKE THIS NICER ***
$schema['INSERT']['query']['phrasetype'] = "
	INSERT INTO " . TABLE_PREFIX . "phrasetype
		(fieldname, title, editrows, special)
	VALUES
		('global',           '{$phrasetype['global']}', 3, 0),
		('cpglobal',         '{$phrasetype['cpglobal']}', 3, 0),
		('cppermission',     '{$phrasetype['cppermission']}', 3, 0),
		('forum',            '{$phrasetype['forum']}', 3, 0),
		('calendar',         '{$phrasetype['calendar']}', 3, 0),
		('attachment_image', '{$phrasetype['attachment_image']}', 3, 0),
		('style',            '{$phrasetype['style']}', 3, 0),
		('logging',          '{$phrasetype['logging']}', 3, 0),
		('cphome',           '{$phrasetype['cphome']}', 3, 0),
		('promotion'