selects.php

<?php
/*
	Question2Answer by Gideon Greenspan and contributors
	http://www.question2answer.org/

	File: qa-include/qa-db-selects.php
	Description: Builders of selectspec arrays (see qa-db.php) used to specify database SELECTs


	This program is free software; you can redistribute it and/or
	modify it under the terms of the GNU General Public License
	as published by the Free Software Foundation; either version 2
	of the License, or (at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	More about this license: http://www.question2answer.org/license.php
*/

if (!defined('QA_VERSION')) { // don't allow this page to be requested directly from browser
	header('Location: ../');
	exit;
}

require_once QA_INCLUDE_DIR.'db/maxima.php';


/**
 * Return the results of all the SELECT operations specified by the supplied selectspec parameters, while also
 * performing all pending selects that have not yet been executed. If only one parameter is supplied, return its
 * result, otherwise return an array of results indexed as per the parameters.
 */
function qa_db_select_with_pending() // any number of parameters read via func_get_args()
{
	require_once QA_INCLUDE_DIR . 'app/options.php';

	global $qa_db_pending_selectspecs, $qa_db_pending_results;

	$selectspecs = func_get_args();
	$singleresult = (count($selectspecs) == 1);
	$outresults = array();

	foreach ($selectspecs as $key => $selectspec) { // can pass null parameters
		if (empty($selectspec)) {
			unset($selectspecs[$key]);
			$outresults[$key] = null;
		}
	}

	if (is_array($qa_db_pending_selectspecs)) {
		foreach ($qa_db_pending_selectspecs as $pendingid => $selectspec) {
			if (!isset($qa_db_pending_results[$pendingid]))
				$selectspecs['pending_' . $pendingid] = $selectspec;
		}
	}

	$outresults = $outresults + qa_db_multi_select($selectspecs);

	if (is_array($qa_db_pending_selectspecs)) {
		foreach ($qa_db_pending_selectspecs as $pendingid => $selectspec) {
			if (!isset($qa_db_pending_results[$pendingid])) {
				$qa_db_pending_results[$pendingid] = $outresults['pending_' . $pendingid];
				unset($outresults['pending_' . $pendingid]);
			}
		}
	}

	return $singleresult ? $outresults[0] : $outresults;
}


/**
 * Queue a $selectspec for running later, with $pendingid (used for retrieval)
 * @param $pendingid
 * @param $selectspec
 */
function qa_db_queue_pending_select($pendingid, $selectspec)
{
	global $qa_db_pending_selectspecs;

	$qa_db_pending_selectspecs[$pendingid] = $selectspec;
}


/**
 * Get the result of the queued SELECT query identified by $pendingid. Run the query if it hasn't run already. If
 * $selectspec is supplied, it doesn't matter if this hasn't been queued before - it will be queued and run now.
 * @param $pendingid
 * @param $selectspec
 * @return
 */
function qa_db_get_pending_result($pendingid, $selectspec = null)
{
	global $qa_db_pending_selectspecs, $qa_db_pending_results;

	if (isset($selectspec))
		qa_db_queue_pending_select($pendingid, $selectspec);
	elseif (!isset($qa_db_pending_selectspecs[$pendingid]))
		qa_fatal_error('Pending query was never set up: ' . $pendingid);

	if (!isset($qa_db_pending_results[$pendingid]))
		qa_db_select_with_pending();

	return $qa_db_pending_results[$pendingid];
}


/**
 * Remove the results of queued SELECT query identified by $pendingid if it has already been run. This means it will
 * run again if its results are requested via qa_db_get_pending_result()
 * @param $pendingid
 */
function qa_db_flush_pending_result($pendingid)
{
	global $qa_db_pending_results;
	unset($qa_db_pending_results[$pendingid]);
}


/**
 * Modify a selectspec to count the number of items. This assumes the original selectspec does not have a LIMIT clause.
 * Currently works with message inbox/outbox functions and user-flags function.
 * @param $selectSpec
 * @return mixed
 */
function qa_db_selectspec_count($selectSpec)
{
	$selectSpec['columns'] = array('count' => 'COUNT(*)');
	$selectSpec['single'] = true;
	unset($selectSpec['arraykey']);

	return $selectSpec;
}


/**
 * Return the common selectspec used to build any selectspecs which retrieve posts from the database.
 * If $voteuserid is set, retrieve the vote made by a particular that user on each post.
 * If $full is true, get full information on the posts, instead of just information for listing pages.
 * If $user is true, get information about the user who wrote the post (or cookie if anonymous).
 * @param $voteuserid
 * @param bool $full
 * @param bool $user
 * @return array
 */
function qa_db_posts_basic_selectspec($voteuserid = null, $full = false, $user = true)
{
	if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }

	$selectspec = array(
		'columns' => array(
			'^posts.postid', '^posts.categoryid', '^posts.type', 'basetype' => 'LEFT(^posts.type, 1)', 'hidden' => "INSTR(^posts.type, '_HIDDEN')>0",
			'^posts.acount', '^posts.selchildid', '^posts.closedbyid', '^posts.upvotes', '^posts.downvotes', '^posts.netvotes', '^posts.views', '^posts.hotness',
			'^posts.flagcount', '^posts.title', '^posts.tags', 'created' => 'UNIX_TIMESTAMP(^posts.created)', '^posts.name',
			'categoryname' => '^categories.title', 'categorybackpath' => "^categories.backpath",
			'categoryids' => "CONCAT_WS(',', ^posts.catidpath1, ^posts.catidpath2, ^posts.catidpath3, ^posts.categoryid)",
		),

		'arraykey' => 'postid',
		'source' => '^posts LEFT JOIN ^categories ON ^categories.categoryid=^posts.categoryid',
		'arguments' => array(),
	);

	if (isset($voteuserid)) {
		require_once QA_INCLUDE_DIR . 'app/updates.php';

		$selectspec['columns']['uservote'] = '^uservotes.vote';
		$selectspec['columns']['userflag'] = '^uservotes.flag';
		$selectspec['columns']['userfavoriteq'] = '^userfavorites.entityid<=>^posts.postid';
		$selectspec['source'] .= ' LEFT JOIN ^uservotes ON ^posts.postid=^uservotes.postid AND ^uservotes.userid=$';
		$selectspec['source'] .= ' LEFT JOIN ^userfavorites ON ^posts.postid=^userfavorites.entityid AND ^userfavorites.userid=$ AND ^userfavorites.entitytype=$';
		array_push($selectspec['arguments'], $voteuserid, $voteuserid, QA_ENTITY_QUESTION);
	}

	if ($full) {
		$selectspec['columns']['content'] = '^posts.content';
		$selectspec['columns']['notify'] = '^posts.notify';
		$selectspec['columns']['updated'] = 'UNIX_TIMESTAMP(^posts.updated)';
		$selectspec['columns']['updatetype'] = '^posts.updatetype';
		$selectspec['columns'][] = '^posts.format';
		$selectspec['columns'][] = '^posts.lastuserid';
		$selectspec['columns']['lastip'] = '^posts.lastip';
		$selectspec['columns'][] = '^posts.parentid';
		$selectspec['columns']['lastviewip'] = '^posts.lastviewip';
	}

	if ($user) {
		$selectspec['columns'][] = '^posts.userid';
		$selectspec['columns'][] = '^posts.cookieid';
		$selectspec['columns']['createip'] = '^posts.createip';
		$selectspec['columns'][] = '^userpoints.points';

		if (!QA_FINAL_EXTERNAL_USERS) {
			$selectspec['columns'][] = '^users.flags';
			$selectspec['columns'][] = '^users.level';
			$selectspec['columns']['email'] = '^users.email';
			$selectspec['columns']['handle'] = '^users.handle';
			$selectspec['columns']['avatarblobid'] = 'BINARY ^users.avatarblobid';
			$selectspec['columns'][] = '^users.avatarwidth';
			$selectspec['columns'][] = '^users.avatarheight';
			$selectspec['source'] .= ' LEFT JOIN ^users ON ^posts.userid=^users.userid';

			if ($full) {
				$selectspec['columns']['lasthandle'] = 'lastusers.handle';
				$selectspec['source'] .= ' LEFT JOIN ^users AS lastusers ON ^posts.lastuserid=lastusers.userid';
			}
		}

		$selectspec['source'] .= ' LEFT JOIN ^userpoints ON ^posts.userid=^userpoints.userid';
	}

	return $selectspec;
}


/**
 * Supplement a selectspec returned by qa_db_posts_basic_selectspec() to get information about another post (answer or
 * comment) which is related to the main post (question) retrieved. Pass the name of table which will contain the other
 * post in $poststable. Set $fromupdated to true to get information about when this other post was edited, rather than
 * created. If $full is true, get full information on this other post.
 * @param $selectspec
 * @param $poststable
 * @param bool $fromupdated
 * @param bool $full
 */
function qa_db_add_selectspec_opost(&$selectspec, $poststable, $fromupdated = false, $full = false)
{
	$selectspec['arraykey'] = 'opostid';

	$selectspec['columns']['obasetype'] = 'LEFT(' . $poststable . '.type, 1)';
	$selectspec['columns']['ohidden'] = "INSTR(" . $poststable . ".type, '_HIDDEN')>0";
	$selectspec['columns']['opostid'] = $poststable . '.postid';
	$selectspec['columns']['ouserid'] = $poststable . ($fromupdated ? '.lastuserid' : '.userid');
	$selectspec['columns']['ocookieid'] = $poststable . '.cookieid';
	$selectspec['columns']['oname'] = $poststable . '.name';
	$selectspec['columns']['oip'] = $poststable . ($fromupdated ? '.lastip' : '.createip');
	$selectspec['columns']['otime'] = 'UNIX_TIMESTAMP(' . $poststable . ($fromupdated ? '.updated' : '.created') . ')';
	$selectspec['columns']['oflagcount'] = $poststable . '.flagcount';

	if ($fromupdated)
		$selectspec['columns']['oupdatetype'] = $poststable . '.updatetype';

	if ($full) {
		$selectspec['columns']['ocontent'] = $poststable . '.content';
		$selectspec['columns']['oformat'] = $poststable . '.format';
	}

	if ($fromupdated || $full)
		$selectspec['columns']['oupdated'] = 'UNIX_TIMESTAMP(' . $poststable . '.updated)';
}


/**
 * Supplement a selectspec returned by qa_db_posts_basic_selectspec() to get information about the author of another
 * post (answer or comment) which is related to the main post (question) retrieved. Pass the name of table which will
 * contain the other user's details in $userstable and the name of the table which will contain the other user's points
 * in $pointstable.
 * @param $selectspec
 * @param $userstable
 * @param $pointstable
 */
function qa_db_add_selectspec_ousers(&$selectspec, $userstable, $pointstable)
{
	if (!QA_FINAL_EXTERNAL_USERS) {
		$selectspec['columns']['oflags'] = $userstable . '.flags';
		$selectspec['columns']['olevel'] = $userstable . '.level';
		$selectspec['columns']['oemail'] = $userstable . '.email';
		$selectspec['columns']['ohandle'] = $userstable . '.handle';
		$selectspec['columns']['oavatarblobid'] = 'BINARY ' . $userstable . '.avatarblobid'; // cast to BINARY due to MySQL bug which renders it signed in a union
		$selectspec['columns']['oavatarwidth'] = $userstable . '.avatarwidth';
		$selectspec['columns']['oavatarheight'] = $userstable . '.avatarheight';
	}

	$selectspec['columns']['opoints'] = $pointstable . '.points';
}


/**
 * Given $categoryslugs in order of the hierarchiy, return the equivalent value for the backpath column in the categories table
 * @param $categoryslugs
 * @return string
 */
function qa_db_slugs_to_backpath($categoryslugs)
{
	if (!is_array($categoryslugs)) // accept old-style string arguments for one category deep
		$categoryslugs = array($categoryslugs);

	return implode('/', array_reverse($categoryslugs));
}


/**
 * Return SQL code that represents the constraint of a post being in the category with $categoryslugs, or any of its subcategories
 * @param $categoryslugs
 * @param $arguments
 * @return string
 */
function qa_db_categoryslugs_sql_args($categoryslugs, &$arguments)
{
	if (!is_array($categoryslugs)) // accept old-style string arguments for one category deep
		$categoryslugs = strlen($categoryslugs) ? array($categoryslugs) : array();

	$levels = count($categoryslugs);

	if ($levels > 0 && $levels <= QA_CATEGORY_DEPTH) {
		$arguments[] = qa_db_slugs_to_backpath($categoryslugs);
		return (($levels == QA_CATEGORY_DEPTH) ? 'categoryid' : ('catidpath' . $levels)) . '=(SELECT categoryid FROM ^categories WHERE backpath=$ LIMIT 1) AND ';
	}

	return '';
}


/**
 * Return the selectspec to retrieve questions (of type $specialtype if provided, or 'Q' by default) sorted by $sort,
 * restricted to $createip (if not null) and the category for $categoryslugs (if not null), with the corresponding vote
 * made by $voteuserid (if not null) and including $full content or not. Return $count (if null, a default is used)
 * questions starting from offset $start.
 * @param $voteuserid
 * @param $sort
 * @param $start
 * @param $categoryslugs
 * @param $createip
 * @param bool $specialtype
 * @param bool $full
 * @param $count
 * @return array
 */
function qa_db_qs_selectspec($voteuserid, $sort, $start, $categoryslugs = null, $createip = null, $specialtype = false, $full = false, $count = null)
{
	if ($specialtype == 'Q' || $specialtype == 'Q_QUEUED')
		$type = $specialtype;
	else
		$type = $specialtype ? 'Q_HIDDEN' : 'Q'; // for backwards compatibility

	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	switch ($sort) {
		case 'acount':
		case 'flagcount':
		case 'netvotes':
		case 'views':
			$sortsql = 'ORDER BY ^posts.' . $sort . ' DESC, ^posts.created DESC';
			break;

		case 'created':
		case 'hotness':
			$sortsql = 'ORDER BY ^posts.' . $sort . ' DESC';
			break;

		default:
			qa_fatal_error('qa_db_qs_selectspec() called with illegal sort value');
			break;
	}

	$selectspec = qa_db_posts_basic_selectspec($voteuserid, $full);

	$selectspec['source'] .= " JOIN (SELECT postid FROM ^posts WHERE " .
		qa_db_categoryslugs_sql_args($categoryslugs, $selectspec['arguments']) .
		(isset($createip) ? "createip=$ AND " : "") .
		"type=$ " . $sortsql . " LIMIT #,#) y ON ^posts.postid=y.postid";

	if (isset($createip))
		$selectspec['arguments'][] = @inet_pton($createip);

	array_push($selectspec['arguments'], $type, $start, $count);

	$selectspec['sortdesc'] = $sort;

	return $selectspec;
}


/**
 * Return the selectspec to retrieve recent questions (of type $specialtype if provided, or 'Q' by default) which,
 * depending on $by, either (a) have no answers, (b) have on selected answers, or (c) have no upvoted answers. The
 * questions are restricted to the category for $categoryslugs (if not null), and will have the corresponding vote made
 * by $voteuserid (if not null) and will include $full content or not. Return $count (if null, a default is used)
 * questions starting from offset $start.
 * @param $voteuserid
 * @param $by
 * @param $start
 * @param $categoryslugs
 * @param bool $specialtype
 * @param bool $full
 * @param $count
 * @return array
 */
function qa_db_unanswered_qs_selectspec($voteuserid, $by, $start, $categoryslugs = null, $specialtype = false, $full = false, $count = null)
{
	if ($specialtype == 'Q' || $specialtype == 'Q_QUEUED')
		$type = $specialtype;
	else
		$type = $specialtype ? 'Q_HIDDEN' : 'Q'; // for backwards compatibility

	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	switch ($by) {
		case 'selchildid':
			$bysql = 'selchildid IS NULL';
			break;

		case 'amaxvote':
			$bysql = 'amaxvote=0';
			break;

		default:
			$bysql = 'acount=0';
			break;
	}

	$selectspec = qa_db_posts_basic_selectspec($voteuserid, $full);

	$selectspec['source'] .= " JOIN (SELECT postid FROM ^posts WHERE " . qa_db_categoryslugs_sql_args($categoryslugs, $selectspec['arguments']) . "type=$ AND " . $bysql . " AND closedbyid IS NULL ORDER BY ^posts.created DESC LIMIT #,#) y ON ^posts.postid=y.postid";

	array_push($selectspec['arguments'], $type, $start, $count);

	$selectspec['sortdesc'] = 'created';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the antecedent questions for recent answers (of type $specialtype if provided, or
 * 'A' by default), restricted to $createip (if not null) and the category for $categoryslugs (if not null), with the
 * corresponding vote on those questions made by $voteuserid (if not null). Return $count (if null, a default is used)
 * questions starting from offset $start. The selectspec will also retrieve some information about the answers
 * themselves (including the content if $fullanswers is true), in columns named with the prefix 'o'.
 * @param $voteuserid
 * @param $start
 * @param $categoryslugs
 * @param $createip
 * @param bool $specialtype
 * @param bool $fullanswers
 * @param $count
 * @return array
 */
function qa_db_recent_a_qs_selectspec($voteuserid, $start, $categoryslugs = null, $createip = null, $specialtype = false, $fullanswers = false, $count = null)
{
	if ($specialtype == 'A' || $specialtype == 'A_QUEUED')
		$type = $specialtype;
	else
		$type = $specialtype ? 'A_HIDDEN' : 'A'; // for backwards compatibility

	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	$selectspec = qa_db_posts_basic_selectspec($voteuserid);

	qa_db_add_selectspec_opost($selectspec, 'aposts', false, $fullanswers);
	qa_db_add_selectspec_ousers($selectspec, 'ausers', 'auserpoints');

	$selectspec['source'] .= " JOIN ^posts AS aposts ON ^posts.postid=aposts.parentid" .
		(QA_FINAL_EXTERNAL_USERS ? "" : " LEFT JOIN ^users AS ausers ON aposts.userid=ausers.userid") .
		" LEFT JOIN ^userpoints AS auserpoints ON aposts.userid=auserpoints.userid" .
		" JOIN (SELECT postid FROM ^posts WHERE " .
		qa_db_categoryslugs_sql_args($categoryslugs, $selectspec['arguments']) .
		(isset($createip) ? "createip=$ AND " : "") .
		"type=$ ORDER BY ^posts.created DESC LIMIT #,#) y ON aposts.postid=y.postid" .
		($specialtype ? '' : " WHERE ^posts.type='Q'");

	if (isset($createip))
		$selectspec['arguments'][] = @inet_pton($createip);

	array_push($selectspec['arguments'], $type, $start, $count);

	$selectspec['sortdesc'] = 'otime';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the antecedent questions for recent comments (of type $specialtype if provided, or
 * 'C' by default), restricted to $createip (if not null) and the category for $categoryslugs (if not null), with the
 * corresponding vote on those questions made by $voteuserid (if not null). Return $count (if null, a default is used)
 * questions starting from offset $start. The selectspec will also retrieve some information about the comments
 * themselves (including the content if $fullcomments is true), in columns named with the prefix 'o'.
 * @param $voteuserid
 * @param $start
 * @param $categoryslugs
 * @param $createip
 * @param bool $specialtype
 * @param bool $fullcomments
 * @param $count
 * @return array
 */
function qa_db_recent_c_qs_selectspec($voteuserid, $start, $categoryslugs = null, $createip = null, $specialtype = false, $fullcomments = false, $count = null)
{
	if ($specialtype == 'C' || $specialtype == 'C_QUEUED')
		$type = $specialtype;
	else
		$type = $specialtype ? 'C_HIDDEN' : 'C'; // for backwards compatibility

	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	$selectspec = qa_db_posts_basic_selectspec($voteuserid);

	qa_db_add_selectspec_opost($selectspec, 'cposts', false, $fullcomments);
	qa_db_add_selectspec_ousers($selectspec, 'cusers', 'cuserpoints');

	$selectspec['source'] .= " JOIN ^posts AS parentposts ON" .
		" ^posts.postid=(CASE LEFT(parentposts.type, 1) WHEN 'A' THEN parentposts.parentid ELSE parentposts.postid END)" .
		" JOIN ^posts AS cposts ON parentposts.postid=cposts.parentid" .
		(QA_FINAL_EXTERNAL_USERS ? "" : " LEFT JOIN ^users AS cusers ON cposts.userid=cusers.userid") .
		" LEFT JOIN ^userpoints AS cuserpoints ON cposts.userid=cuserpoints.userid" .
		" JOIN (SELECT postid FROM ^posts WHERE " .
		qa_db_categoryslugs_sql_args($categoryslugs, $selectspec['arguments']) .
		(isset($createip) ? "createip=$ AND " : "") .
		"type=$ ORDER BY ^posts.created DESC LIMIT #,#) y ON cposts.postid=y.postid" .
		($specialtype ? '' : " WHERE ^posts.type='Q' AND ((parentposts.type='Q') OR (parentposts.type='A'))");

	if (isset($createip))
		$selectspec['arguments'][] = @inet_pton($createip);

	array_push($selectspec['arguments'], $type, $start, $count);

	$selectspec['sortdesc'] = 'otime';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the antecedent questions for recently edited posts, restricted to edits by $lastip
 * (if not null), the category for $categoryslugs (if not null) and only visible posts (if $onlyvisible), with the
 * corresponding vote on those questions made by $voteuserid (if not null). Return $count (if null, a default is used)
 * questions starting from offset $start. The selectspec will also retrieve some information about the edited posts
 * themselves (including the content if $fulledited is true), in columns named with the prefix 'o'.
 * @param $voteuserid
 * @param $start
 * @param $categoryslugs
 * @param $lastip
 * @param bool $onlyvisible
 * @param bool $fulledited
 * @param $count
 * @return array
 */
function qa_db_recent_edit_qs_selectspec($voteuserid, $start, $categoryslugs = null, $lastip = null, $onlyvisible = true, $fulledited = false, $count = null)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	$selectspec = qa_db_posts_basic_selectspec($voteuserid);

	qa_db_add_selectspec_opost($selectspec, 'editposts', true, $fulledited);
	qa_db_add_selectspec_ousers($selectspec, 'editusers', 'edituserpoints');

	$selectspec['source'] .= " JOIN ^posts AS parentposts ON" .
		" ^posts.postid=IF(LEFT(parentposts.type, 1)='Q', parentposts.postid, parentposts.parentid)" .
		" JOIN ^posts AS editposts ON parentposts.postid=IF(LEFT(editposts.type, 1)='Q', editposts.postid, editposts.parentid)" .
		(QA_FINAL_EXTERNAL_USERS ? "" : " LEFT JOIN ^users AS editusers ON editposts.lastuserid=editusers.userid") .
		" LEFT JOIN ^userpoints AS edituserpoints ON editposts.lastuserid=edituserpoints.userid" .
		" JOIN (SELECT postid FROM ^posts WHERE " .
		qa_db_categoryslugs_sql_args($categoryslugs, $selectspec['arguments']) .
		(isset($lastip) ? "lastip=$ AND " : "") .
		($onlyvisible ? "type IN ('Q', 'A', 'C')" : "1") .
		" ORDER BY ^posts.updated DESC LIMIT #,#) y ON editposts.postid=y.postid" .
		($onlyvisible ? " WHERE parentposts.type IN ('Q', 'A', 'C') AND ^posts.type IN ('Q', 'A', 'C')" : "");

	if (isset($lastip))
		$selectspec['arguments'][] = @inet_pton($lastip);

	array_push($selectspec['arguments'], $start, $count);

	$selectspec['sortdesc'] = 'otime';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the antecedent questions for the most flagged posts, with the corresponding vote
 * on those questions made by $voteuserid (if not null). Return $count (if null, a default is used) questions starting
 * from offset $start. The selectspec will also retrieve some information about the flagged posts themselves (including
 * the content if $fullflagged is true).
 * @param $voteuserid
 * @param $start
 * @param bool $fullflagged
 * @param $count
 * @return array
 */
function qa_db_flagged_post_qs_selectspec($voteuserid, $start, $fullflagged = false, $count = null)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	$selectspec = qa_db_posts_basic_selectspec($voteuserid);

	qa_db_add_selectspec_opost($selectspec, 'flagposts', false, $fullflagged);
	qa_db_add_selectspec_ousers($selectspec, 'flagusers', 'flaguserpoints');

	$selectspec['source'] .= " JOIN ^posts AS parentposts ON" .
		" ^posts.postid=IF(LEFT(parentposts.type, 1)='Q', parentposts.postid, parentposts.parentid)" .
		" JOIN ^posts AS flagposts ON parentposts.postid=IF(LEFT(flagposts.type, 1)='Q', flagposts.postid, flagposts.parentid)" .
		(QA_FINAL_EXTERNAL_USERS ? "" : " LEFT JOIN ^users AS flagusers ON flagposts.userid=flagusers.userid") .
		" LEFT JOIN ^userpoints AS flaguserpoints ON flagposts.userid=flaguserpoints.userid" .
		" JOIN (SELECT postid FROM ^posts WHERE flagcount>0 AND type IN ('Q', 'A', 'C') ORDER BY ^posts.flagcount DESC, ^posts.created DESC LIMIT #,#) y ON flagposts.postid=y.postid";

	array_push($selectspec['arguments'], $start, $count);

	$selectspec['sortdesc'] = 'oflagcount';
	$selectspec['sortdesc_2'] = 'otime';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the posts in $postids, with the corresponding vote on those posts made by
 * $voteuserid (if not null). Returns full information if $full is true.
 * @param $voteuserid
 * @param $postids
 * @param bool $full
 * @return array
 */
function qa_db_posts_selectspec($voteuserid, $postids, $full = false)
{
	$selectspec = qa_db_posts_basic_selectspec($voteuserid, $full);

	$selectspec['source'] .= " WHERE ^posts.postid IN (#)";
	$selectspec['arguments'][] = $postids;

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the basetype for the posts in $postids, as an array mapping postid => basetype
 * @param $postids
 * @return array
 */
function qa_db_posts_basetype_selectspec($postids)
{
	return array(
		'columns' => array('postid', 'basetype' => 'LEFT(type, 1)'),
		'source' => "^posts WHERE postid IN (#)",
		'arguments' => array($postids),
		'arraykey' => 'postid',
		'arrayvalue' => 'basetype',
	);
}


/**
 * Return the selectspec to retrieve the basetype for the posts in $postids, as an array mapping postid => basetype
 * @param $voteuserid
 * @param $postids
 * @param bool $full
 * @return array
 */
function qa_db_posts_to_qs_selectspec($voteuserid, $postids, $full = false)
{
	$selectspec = qa_db_posts_basic_selectspec($voteuserid, $full);

	$selectspec['columns']['obasetype'] = 'LEFT(childposts.type, 1)';
	$selectspec['columns']['opostid'] = 'childposts.postid';

	$selectspec['source'] .= " JOIN ^posts AS parentposts ON" .
		" ^posts.postid=IF(LEFT(parentposts.type, 1)='Q', parentposts.postid, parentposts.parentid)" .
		" JOIN ^posts AS childposts ON parentposts.postid=IF(LEFT(childposts.type, 1)='Q', childposts.postid, childposts.parentid)" .
		" WHERE childposts.postid IN (#)";

	$selectspec['arraykey'] = 'opostid';
	$selectspec['arguments'][] = $postids;

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the full information for $postid, with the corresponding vote made by $voteuserid (if not null)
 * @param $voteuserid
 * @param $postid
 * @return array
 */
function qa_db_full_post_selectspec($voteuserid, $postid)
{
	$selectspec = qa_db_posts_basic_selectspec($voteuserid, true);

	$selectspec['source'] .= " WHERE ^posts.postid=#";
	$selectspec['arguments'][] = $postid;
	$selectspec['single'] = true;

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the full information for all posts whose parent is $parentid, with the
 * corresponding vote made by $voteuserid (if not null)
 * @param $voteuserid
 * @param $parentid
 * @return array
 */
function qa_db_full_child_posts_selectspec($voteuserid, $parentid)
{
	$selectspec = qa_db_posts_basic_selectspec($voteuserid, true);

	$selectspec['source'] .= " WHERE ^posts.parentid=#";
	$selectspec['arguments'][] = $parentid;

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the full information for all posts whose parent is an answer which
 * has $questionid as its parent, with the corresponding vote made by $voteuserid (if not null)
 * @param $voteuserid
 * @param $questionid
 * @return array
 */
function qa_db_full_a_child_posts_selectspec($voteuserid, $questionid)
{
	$selectspec = qa_db_posts_basic_selectspec($voteuserid, true);

	$selectspec['source'] .= " JOIN ^posts AS parents ON ^posts.parentid=parents.postid WHERE parents.parentid=# AND LEFT(parents.type, 1)='A'";
	$selectspec['arguments'][] = $questionid;

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the question for the parent of $postid (where $postid is of a follow-on question or comment),
 * i.e. the parent of $questionid's parent if $questionid's parent is an answer, otherwise $questionid's parent itself.
 * @param $postid
 * @return array
 */
function qa_db_post_parent_q_selectspec($postid)
{
	$selectspec = qa_db_posts_basic_selectspec();

	$selectspec['source'] .= " WHERE ^posts.postid=(SELECT IF(LEFT(parent.type, 1)='A', parent.parentid, parent.postid) FROM ^posts AS child LEFT JOIN ^posts AS parent ON parent.postid=child.parentid WHERE child.postid=# AND parent.type IN('Q','A'))";
	$selectspec['arguments'] = array($postid);
	$selectspec['single'] = true;

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the post (either duplicate question or explanatory note) which has closed $questionid, if any
 * @param $questionid
 * @return array
 */
function qa_db_post_close_post_selectspec($questionid)
{
	$selectspec = qa_db_posts_basic_selectspec(null, true);

	$selectspec['source'] .= " WHERE ^posts.postid=(SELECT closedbyid FROM ^posts WHERE postid=#)";
	$selectspec['arguments'] = array($questionid);
	$selectspec['single'] = true;

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the posts that have been closed as a duplicate of this question, if any
 * @param $questionid int The canonical question.
 * @return array
 */
function qa_db_post_duplicates_selectspec($questionid)
{
	$selectspec = qa_db_posts_basic_selectspec(null, true);

	$selectspec['source'] .= " WHERE ^posts.closedbyid=#";
	$selectspec['arguments'] = array($questionid);

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the metadata value for $postid with key $title
 * @param $postid
 * @param $title
 * @return array
 */
function qa_db_post_meta_selectspec($postid, $title)
{
	$selectspec = array(
		'columns' => array('title', 'content'),
		'source' => "^postmetas WHERE postid=# AND " . (is_array($title) ? "title IN ($)" : "title=$"),
		'arguments' => array($postid, $title),
		'arrayvalue' => 'content',
	);

	if (is_array($title))
		$selectspec['arraykey'] = 'title';
	else
		$selectspec['single'] = true;

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the most closely related questions to $questionid, with the corresponding vote
 * made by $voteuserid (if not null). Return $count (if null, a default is used) questions. This works by looking for
 * other questions which have title words, tag words or an (exact) category in common.
 * @param $voteuserid
 * @param $questionid
 * @param $count
 * @return array
 */
function qa_db_related_qs_selectspec($voteuserid, $questionid, $count = null)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	$selectspec = qa_db_posts_basic_selectspec($voteuserid);

	$selectspec['columns'][] = 'score';

	// added LOG(postid)/1000000 here to ensure ordering is deterministic even if several posts have same score

	$selectspec['source'] .= " JOIN (SELECT postid, SUM(score)+LOG(postid)/1000000 AS score FROM ((SELECT ^titlewords.postid, LOG(#/titlecount) AS score FROM ^titlewords JOIN ^words ON ^titlewords.wordid=^words.wordid JOIN ^titlewords AS source ON ^titlewords.wordid=source.wordid WHERE source.postid=# AND titlecount<#) UNION ALL (SELECT ^posttags.postid, 2*LOG(#/tagcount) AS score FROM ^posttags JOIN ^words ON ^posttags.wordid=^words.wordid JOIN ^posttags AS source ON ^posttags.wordid=source.wordid WHERE source.postid=# AND tagcount<#) UNION ALL (SELECT ^posts.postid, LOG(#/^categories.qcount) FROM ^posts JOIN ^categories ON ^posts.categoryid=^categories.categoryid AND ^posts.type='Q' WHERE ^categories.categoryid=(SELECT categoryid FROM ^posts WHERE postid=#) AND ^categories.qcount<#)) x WHERE postid!=# GROUP BY postid ORDER BY score DESC LIMIT #) y ON ^posts.postid=y.postid";

	array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ, $questionid, QA_IGNORED_WORDS_FREQ, QA_IGNORED_WORDS_FREQ,
		$questionid, QA_IGNORED_WORDS_FREQ, QA_IGNORED_WORDS_FREQ, $questionid, QA_IGNORED_WORDS_FREQ, $questionid, $count);

	$selectspec['sortdesc'] = 'score';

	if (!isset($voteuserid)) {
		$selectspec['caching'] = array(
			'key' => __FUNCTION__ . ":$questionid:$count",
			'ttl' => qa_opt('caching_q_time'),
		);
	}

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the top question matches for a search, with the corresponding vote made by
 * $voteuserid (if not null) and including $full content or not. Return $count (if null, a default is used) questions
 * starting from offset $start. The search is performed for any of $titlewords in the title, $contentwords in the
 * content (of the question or an answer or comment for whom that is the antecedent question), $tagwords in tags, for
 * question author usernames which match a word in $handlewords or which match $handle as a whole. The results also
 * include a 'score' column based on the matching strength and post hotness, and a 'matchparts' column that tells us
 * where the score came from (since a question could get weight from a match in the question itself, and/or weight from
 * a match in its answers, comments, or comments on answers). The 'matchparts' is a comma-separated list of tuples
 * matchtype:matchpostid:matchscore to be used with qa_search_set_max_match().
 * @param $voteuserid
 * @param $titlewords
 * @param $contentwords
 * @param $tagwords
 * @param $handlewords
 * @param $handle
 * @param $start
 * @param bool $full
 * @param $count
 * @return array
 */
function qa_db_search_posts_selectspec($voteuserid, $titlewords, $contentwords, $tagwords, $handlewords, $handle, $start, $full = false, $count = null)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	// add LOG(postid)/1000000 here to ensure ordering is deterministic even if several posts have same score
	// The score also gives a bonus for hot questions, where the bonus scales linearly with hotness. The hottest
	// question gets a bonus equivalent to a matching unique tag, and the least hot question gets zero bonus.

	$selectspec = qa_db_posts_basic_selectspec($voteuserid, $full);

	$selectspec['columns'][] = 'score';
	$selectspec['columns'][] = 'matchparts';
	$selectspec['source'] .= " JOIN (SELECT questionid, SUM(score)+2*(LOG(#)*(^posts.hotness-(SELECT MIN(hotness) FROM ^posts WHERE type='Q'))/((SELECT MAX(hotness) FROM ^posts WHERE type='Q')-(SELECT MIN(hotness) FROM ^posts WHERE type='Q')))+LOG(questionid)/1000000 AS score, GROUP_CONCAT(CONCAT_WS(':', matchposttype, matchpostid, ROUND(score,3))) AS matchparts FROM (";
	$selectspec['sortdesc'] = 'score';
	array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ);

	$selectparts = 0;

	if (!empty($titlewords)) {
		// At the indexing stage, duplicate words in title are ignored, so this doesn't count multiple appearances.

		$selectspec['source'] .= ($selectparts++ ? " UNION ALL " : "") .
			"(SELECT postid AS questionid, LOG(#/titlecount) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM ^titlewords JOIN ^words ON ^titlewords.wordid=^words.wordid WHERE word IN ($) AND titlecount<#)";

		array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ, $titlewords, QA_IGNORED_WORDS_FREQ);
	}

	if (!empty($contentwords)) {
		// (1-1/(1+count)) weights words in content based on their frequency: If a word appears once in content
		// it's equivalent to 1/2 an appearance in the title (ignoring the contentcount/titlecount factor).
		// If it appears an infinite number of times, it's equivalent to one appearance in the title.
		// This will discourage keyword stuffing while still giving some weight to multiple appearances.
		// On top of that, answer matches are worth half a question match, and comment/note matches half again.

		$selectspec['source'] .= ($selectparts++ ? " UNION ALL " : "") .
			"(SELECT questionid, (1-1/(1+count))*LOG(#/contentcount)*(CASE ^contentwords.type WHEN 'Q' THEN 1.0 WHEN 'A' THEN 0.5 ELSE 0.25 END) AS score, ^contentwords.type AS matchposttype, ^contentwords.postid AS matchpostid FROM ^contentwords JOIN ^words ON ^contentwords.wordid=^words.wordid WHERE word IN ($) AND contentcount<#)";

		array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ, $contentwords, QA_IGNORED_WORDS_FREQ);
	}

	if (!empty($tagwords)) {
		// Appearances in the tag words count like 2 appearances in the title (ignoring the tagcount/titlecount factor).
		// This is because tags express explicit semantic intent, whereas titles do not necessarily.

		$selectspec['source'] .= ($selectparts++ ? " UNION ALL " : "") .
			"(SELECT postid AS questionid, 2*LOG(#/tagwordcount) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM ^tagwords JOIN ^words ON ^tagwords.wordid=^words.wordid WHERE word IN ($) AND tagwordcount<#)";

		array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ, $tagwords, QA_IGNORED_WORDS_FREQ);
	}

	if (!empty($handlewords)) {
		if (QA_FINAL_EXTERNAL_USERS) {
			require_once QA_INCLUDE_DIR . 'app/users.php';

			$userids = qa_get_userids_from_public($handlewords);

			if (count($userids)) {
				$selectspec['source'] .= ($selectparts++ ? " UNION ALL " : "") .
					"(SELECT postid AS questionid, LOG(#/qposts) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM ^posts JOIN ^userpoints ON ^posts.userid=^userpoints.userid WHERE ^posts.userid IN ($) AND type='Q')";

				array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ, $userids);
			}

		} else {
			$selectspec['source'] .= ($selectparts++ ? " UNION ALL " : "") .
				"(SELECT postid AS questionid, LOG(#/qposts) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM ^posts JOIN ^users ON ^posts.userid=^users.userid JOIN ^userpoints ON ^userpoints.userid=^users.userid WHERE handle IN ($) AND type='Q')";

			array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ, $handlewords);
		}
	}

	if (strlen($handle)) { // to allow searching for multi-word usernames (only works if search query contains full username and nothing else)
		if (QA_FINAL_EXTERNAL_USERS) {
			$userids = qa_get_userids_from_public(array($handle));

			if (count($userids)) {
				$selectspec['source'] .= ($selectparts++ ? " UNION ALL " : "") .
					"(SELECT postid AS questionid, LOG(#/qposts) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM ^posts JOIN ^userpoints ON ^posts.userid=^userpoints.userid WHERE ^posts.userid=$ AND type='Q')";

				array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ, reset($userids));
			}

		} else {
			$selectspec['source'] .= ($selectparts++ ? " UNION ALL " : "") .
				"(SELECT postid AS questionid, LOG(#/qposts) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM ^posts JOIN ^users ON ^posts.userid=^users.userid JOIN ^userpoints ON ^userpoints.userid=^users.userid WHERE handle=$ AND type='Q')";

			array_push($selectspec['arguments'], QA_IGNORED_WORDS_FREQ, $handle);
		}
	}

	if ($selectparts == 0)
		$selectspec['source'] .= '(SELECT NULL as questionid, 0 AS score, NULL AS matchposttype, NULL AS matchpostid FROM ^posts WHERE postid IS NULL)';

	$selectspec['source'] .= ") x LEFT JOIN ^posts ON ^posts.postid=questionid GROUP BY questionid ORDER BY score DESC LIMIT #,#) y ON ^posts.postid=y.questionid";

	array_push($selectspec['arguments'], $start, $count);

	return $selectspec;
}


/**
 * Processes the matchparts column in $question which was returned from a search performed via qa_db_search_posts_selectspec()
 * Returns the id of the strongest matching answer or comment, or null if the question itself was the strongest match
 * @param $question
 * @param $type
 * @param $postid
 * @return null
 */
function qa_search_set_max_match($question, &$type, &$postid)
{
	$type = 'Q';
	$postid = $question['postid'];
	$bestscore = null;

	$matchparts = explode(',', $question['matchparts']);
	foreach ($matchparts as $matchpart) {
		if (sscanf($matchpart, '%1s:%f:%f', $matchposttype, $matchpostid, $matchscore) == 3) {
			if (!isset($bestscore) || $matchscore > $bestscore) {
				$bestscore = $matchscore;
				$type = $matchposttype;
				$postid = $matchpostid;
			}
		}
	}

	return null;
}


/**
 * Return a selectspec to retrieve the full information on the category whose id is $slugsorid (if $isid is true),
 * otherwise whose backpath matches $slugsorid
 * @param $slugsorid
 * @param $isid
 * @return array
 */
function qa_db_full_category_selectspec($slugsorid, $isid)
{
	if ($isid)
		$identifiersql = 'categoryid=#';
	else {
		$identifiersql = 'backpath=$';
		$slugsorid = qa_db_slugs_to_backpath($slugsorid);
	}

	return array(
		'columns' => array('categoryid', 'parentid', 'title', 'tags', 'qcount', 'content', 'backpath'),
		'source' => '^categories WHERE ' . $identifiersql,
		'arguments' => array($slugsorid),
		'single' => 'true',
	);
}


/**
 * Return the selectspec to retrieve ($full or not) info on the categories which "surround" the central category specified
 * by $slugsorid, $isid and $ispostid. The "surrounding" categories include all categories (even unrelated) at the
 * top level, any ancestors (at any level) of the category, the category's siblings and sub-categories (to one level).
 * The central category is specified as follows. If $isid AND $ispostid then $slugsorid is the ID of a post with the category.
 * Otherwise if $isid then $slugsorid is the category's own id. Otherwise $slugsorid is the full backpath of the category.
 * @param $slugsorid
 * @param $isid
 * @param bool $ispostid
 * @param bool $full
 * @return array
 */
function qa_db_category_nav_selectspec($slugsorid, $isid, $ispostid = false, $full = false)
{
	if ($isid) {
		if ($ispostid)
			$identifiersql = 'categoryid=(SELECT categoryid FROM ^posts WHERE postid=#)';
		else
			$identifiersql = 'categoryid=#';

	} else {
		$identifiersql = 'backpath=$';
		$slugsorid = qa_db_slugs_to_backpath($slugsorid);
	}

	$parentselects = array( // requires QA_CATEGORY_DEPTH=4
		'SELECT NULL AS parentkey', // top level
		'SELECT grandparent.parentid FROM ^categories JOIN ^categories AS parent ON ^categories.parentid=parent.categoryid JOIN ^categories AS grandparent ON parent.parentid=grandparent.categoryid WHERE ^categories.' . $identifiersql, // 2 gens up
		'SELECT parent.parentid FROM ^categories JOIN ^categories AS parent ON ^categories.parentid=parent.categoryid WHERE ^categories.' . $identifiersql,
		// 1 gen up
		'SELECT parentid FROM ^categories WHERE ' . $identifiersql, // same gen
		'SELECT categoryid FROM ^categories WHERE ' . $identifiersql, // gen below
	);

	$selectspec = array(
		'columns' => array('^categories.categoryid', '^categories.parentid', 'title' => '^categories.title', 'tags' => '^categories.tags', '^categories.qcount', '^categories.position'),
		'source' => '^categories JOIN (' . implode(' UNION ', $parentselects) . ') y ON ^categories.parentid<=>parentkey' . ($full ? ' LEFT JOIN ^categories AS child ON child.parentid=^categories.categoryid GROUP BY ^categories.categoryid' : '') . ' ORDER BY ^categories.position',
		'arguments' => array($slugsorid, $slugsorid, $slugsorid, $slugsorid),
		'arraykey' => 'categoryid',
		'sortasc' => 'position',
	);

	if ($full) {
		$selectspec['columns']['childcount'] = 'COUNT(child.categoryid)';
		$selectspec['columns']['content'] = '^categories.content';
		$selectspec['columns']['backpath'] = '^categories.backpath';
	}

	return $selectspec;
}


/**
 * Return the selectspec to retrieve information on all subcategories of $categoryid (used for Ajax navigation of hierarchy)
 * @param $categoryid
 * @return array
 */
function qa_db_category_sub_selectspec($categoryid)
{
	return array(
		'columns' => array('categoryid', 'title', 'tags', 'qcount', 'position'),
		'source' => '^categories WHERE parentid<=># ORDER BY position',
		'arguments' => array($categoryid),
		'arraykey' => 'categoryid',
		'sortasc' => 'position',
	);
}


/**
 * Return the selectspec to retrieve a single category as specified by its $slugs (in order of hierarchy)
 * @param $slugs
 * @return array
 */
function qa_db_slugs_to_category_id_selectspec($slugs)
{
	return array(
		'columns' => array('categoryid'),
		'source' => '^categories WHERE backpath=$',
		'arguments' => array(qa_db_slugs_to_backpath($slugs)),
		'arrayvalue' => 'categoryid',
		'single' => true,
	);
}


/**
 * Return the selectspec to retrieve the list of custom pages or links, ordered for display
 * @param $onlynavin
 * @param $onlypageids
 * @return array
 */
function qa_db_pages_selectspec($onlynavin = null, $onlypageids = null)
{
	$selectspec = array(
		// +0 required to work around MySQL bug where by permit value is mis-read as signed, e.g. -106 instead of 150
		'columns' => array('pageid', 'title', 'flags', 'permit' => 'permit+0', 'nav', 'tags', 'position', 'heading'),
		'arraykey' => 'pageid',
		'sortasc' => 'position',
	);

	if (isset($onlypageids)) {
		$selectspec['source'] = '^pages WHERE pageid IN (#)';
		$selectspec['arguments'] = array($onlypageids);

	} elseif (isset($onlynavin)) {
		$selectspec['source'] = '^pages WHERE nav IN ($) ORDER BY position';
		$selectspec['arguments'] = array($onlynavin);

	} else
		$selectspec['source'] = '^pages ORDER BY position';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the list of widgets, ordered for display
 */
function qa_db_widgets_selectspec()
{
	return array(
		'columns' => array('widgetid', 'place', 'position', 'tags', 'title'),
		'source' => '^widgets ORDER BY position',
		'sortasc' => 'position',
	);
}


/**
 * Return the selectspec to retrieve the full information about a custom page
 * @param $slugorpageid
 * @param $ispageid
 * @return array
 */
function qa_db_page_full_selectspec($slugorpageid, $ispageid)
{
	return array(
		'columns' => array('pageid', 'title', 'flags', 'permit', 'nav', 'tags', 'position', 'heading', 'content'),
		'source' => '^pages WHERE ' . ($ispageid ? 'pageid' : 'tags') . '=$',
		'arguments' => array($slugorpageid),
		'single' => true,
	);
}


/**
 * Return the selectspec to retrieve the most recent questions with $tag, with the corresponding vote on those
 * questions made by $voteuserid (if not null) and including $full content or not. Return $count (if null, a default is
 * used) questions starting from $start.
 * @param $voteuserid
 * @param $tag
 * @param $start
 * @param bool $full
 * @param $count
 * @return array
 */
function qa_db_tag_recent_qs_selectspec($voteuserid, $tag, $start, $full = false, $count = null)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	require_once QA_INCLUDE_DIR . 'util/string.php';

	$selectspec = qa_db_posts_basic_selectspec($voteuserid, $full);

	// use two tests here - one which can use the index, and the other which narrows it down exactly - then limit to 1 just in case
	$selectspec['source'] .= " JOIN (SELECT postid FROM ^posttags WHERE wordid=(SELECT wordid FROM ^words WHERE word=$ AND word=$ COLLATE utf8_bin LIMIT 1) ORDER BY postcreated DESC LIMIT #,#) y ON ^posts.postid=y.postid";
	array_push($selectspec['arguments'], $tag, qa_strtolower($tag), $start, $count);
	$selectspec['sortdesc'] = 'created';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the number of questions tagged with $tag (single value)
 * @param $tag
 * @return array
 */
function qa_db_tag_word_selectspec($tag)
{
	return array(
		'columns' => array('wordid', 'word', 'tagcount'),
		'source' => '^words WHERE word=$ AND word=$ COLLATE utf8_bin',
		'arguments' => array($tag, qa_strtolower($tag)),
		'single' => true,
	);
}


/**
 * Return the selectspec to retrieve recent questions by the user identified by $identifier, where $identifier is a
 * handle if we're using internal user management, or a userid if we're using external users. Also include the
 * corresponding vote on those questions made by $voteuserid (if not null). Return $count (if null, a default is used)
 * questions.
 * @param $voteuserid
 * @param $identifier
 * @param $count
 * @param int $start
 * @return array
 */
function qa_db_user_recent_qs_selectspec($voteuserid, $identifier, $count = null, $start = 0)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	$selectspec = qa_db_posts_basic_selectspec($voteuserid);

	$selectspec['source'] .= " WHERE ^posts.userid=" . (QA_FINAL_EXTERNAL_USERS ? "$" : "(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)") . " AND type='Q' ORDER BY ^posts.created DESC LIMIT #,#";
	array_push($selectspec['arguments'], $identifier, $start, $count);
	$selectspec['sortdesc'] = 'created';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the antecedent questions for recent answers by the user identified by $identifier
 * (see qa_db_user_recent_qs_selectspec() comment), with the corresponding vote on those questions made by $voteuserid
 * (if not null). Return $count (if null, a default is used) questions. The selectspec will also retrieve some
 * information about the answers themselves, in columns named with the prefix 'o'.
 * @param $voteuserid
 * @param $identifier
 * @param $count
 * @param int $start
 * @return array
 */
function qa_db_user_recent_a_qs_selectspec($voteuserid, $identifier, $count = null, $start = 0)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	$selectspec = qa_db_posts_basic_selectspec($voteuserid);

	qa_db_add_selectspec_opost($selectspec, 'aposts');

	$selectspec['columns']['oupvotes'] = 'aposts.upvotes';
	$selectspec['columns']['odownvotes'] = 'aposts.downvotes';
	$selectspec['columns']['onetvotes'] = 'aposts.netvotes';

	$selectspec['source'] .= " JOIN ^posts AS aposts ON ^posts.postid=aposts.parentid" .
		" JOIN (SELECT postid FROM ^posts WHERE " .
		" userid=" . (QA_FINAL_EXTERNAL_USERS ? "$" : "(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)") .
		" AND type='A' ORDER BY created DESC LIMIT #,#) y ON aposts.postid=y.postid WHERE ^posts.type='Q'";

	array_push($selectspec['arguments'], $identifier, $start, $count);
	$selectspec['sortdesc'] = 'otime';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the antecedent questions for recent comments by the user identified by $identifier
 * (see qa_db_user_recent_qs_selectspec() comment), with the corresponding vote on those questions made by $voteuserid
 * (if not null). Return $count (if null, a default is used) questions. The selectspec will also retrieve some
 * information about the comments themselves, in columns named with the prefix 'o'.
 * @param $voteuserid
 * @param $identifier
 * @param $count
 * @return array
 */
function qa_db_user_recent_c_qs_selectspec($voteuserid, $identifier, $count = null)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	$selectspec = qa_db_posts_basic_selectspec($voteuserid);

	qa_db_add_selectspec_opost($selectspec, 'cposts');

	$selectspec['source'] .= " JOIN ^posts AS parentposts ON" .
		" ^posts.postid=(CASE parentposts.type WHEN 'A' THEN parentposts.parentid ELSE parentposts.postid END)" .
		" JOIN ^posts AS cposts ON parentposts.postid=cposts.parentid" .
		" JOIN (SELECT postid FROM ^posts WHERE " .
		" userid=" . (QA_FINAL_EXTERNAL_USERS ? "$" : "(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)") .
		" AND type='C' ORDER BY created DESC LIMIT #) y ON cposts.postid=y.postid WHERE ^posts.type='Q' AND parentposts.type IN ('Q', 'A')";

	array_push($selectspec['arguments'], $identifier, $count);
	$selectspec['sortdesc'] = 'otime';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the antecedent questions for recently edited posts by the user identified by
 * $identifier (see qa_db_user_recent_qs_selectspec() comment), with the corresponding vote on those questions made by
 * $voteuserid (if not null). Return $count (if null, a default is used) questions. The selectspec will also retrieve
 * some information about the edited posts themselves, in columns named with the prefix 'o'.
 * @param $voteuserid
 * @param $identifier
 * @param $count
 * @return array
 */
function qa_db_user_recent_edit_qs_selectspec($voteuserid, $identifier, $count = null)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_QS_AS) : QA_DB_RETRIEVE_QS_AS;

	$selectspec = qa_db_posts_basic_selectspec($voteuserid);

	qa_db_add_selectspec_opost($selectspec, 'editposts', true);

	$selectspec['source'] .= " JOIN ^posts AS parentposts ON" .
		" ^posts.postid=IF(LEFT(parentposts.type, 1)='Q', parentposts.postid, parentposts.parentid)" .
		" JOIN ^posts AS editposts ON parentposts.postid=IF(LEFT(editposts.type, 1)='Q', editposts.postid, editposts.parentid)" .
		" JOIN (SELECT postid FROM ^posts WHERE " .
		" lastuserid=" . (QA_FINAL_EXTERNAL_USERS ? "$" : "(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)") .
		" AND type IN ('Q', 'A', 'C') ORDER BY updated DESC LIMIT #) y ON editposts.postid=y.postid " .
		" WHERE parentposts.type IN ('Q', 'A', 'C') AND ^posts.type IN ('Q', 'A', 'C')";

	array_push($selectspec['arguments'], $identifier, $count);
	$selectspec['sortdesc'] = 'otime';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve the most popular tags. Return $count (if null, a default is used) tags, starting
 * from offset $start. The selectspec will produce a sorted array with tags in the key, and counts in the values.
 * @param $start
 * @param $count
 * @return array
 */
function qa_db_popular_tags_selectspec($start, $count = null)
{
	$count = isset($count) ? $count : QA_DB_RETRIEVE_TAGS;

	return array(
		'columns' => array('word', 'tagcount'),
		'source' => '^words JOIN (SELECT wordid FROM ^words WHERE tagcount>0 ORDER BY tagcount DESC LIMIT #,#) y ON ^words.wordid=y.wordid',
		'arguments' => array($start, $count),
		'arraykey' => 'word',
		'arrayvalue' => 'tagcount',
		'sortdesc' => 'tagcount',
	);
}


/**
 * Return the selectspec to retrieve the list of user profile fields, ordered for display
 */
function qa_db_userfields_selectspec()
{
	return array(
		'columns' => array('fieldid', 'title', 'content', 'flags', 'permit', 'position'),
		'source' => '^userfields',
		'arraykey' => 'title',
		'sortasc' => 'position',
	);
}


/**
 * Return the selecspec to retrieve a single array with details of the account of the user identified by
 * $useridhandle, which should be a userid if $isuserid is true, otherwise $useridhandle should be a handle.
 * @param $useridhandle
 * @param $isuserid
 * @return array
 */
function qa_db_user_account_selectspec($useridhandle, $isuserid)
{
	return array(
		'columns' => array(
			'^users.userid', 'passsalt', 'passcheck' => 'HEX(passcheck)', 'passhash', 'email', 'level', 'emailcode', 'handle',
			'created' => 'UNIX_TIMESTAMP(created)', 'sessioncode', 'sessionsource', 'flags', 'loggedin' => 'UNIX_TIMESTAMP(loggedin)',
			'loginip', 'written' => 'UNIX_TIMESTAMP(written)', 'writeip',
			'avatarblobid' => 'BINARY avatarblobid', // cast to BINARY due to MySQL bug which renders it signed in a union
			'avatarwidth', 'avatarheight', 'points', 'wallposts',
		),

		'source' => '^users LEFT JOIN ^userpoints ON ^userpoints.userid=^users.userid WHERE ^users.' . ($isuserid ? 'userid' : 'handle') . '=$',
		'arguments' => array($useridhandle),
		'single' => true,
	);
}


/**
 * Return the selectspec to retrieve all user profile information of the user identified by
 * $useridhandle (see qa_db_user_account_selectspec() comment), as an array of [field] => [value]
 * @param $useridhandle
 * @param $isuserid
 * @return array
 */
function qa_db_user_profile_selectspec($useridhandle, $isuserid)
{
	return array(
		'columns' => array('title', 'content'),
		'source' => '^userprofile WHERE userid=' . ($isuserid ? '$' : '(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)'),
		'arguments' => array($useridhandle),
		'arraykey' => 'title',
		'arrayvalue' => 'content',
	);
}


/**
 * Return the selectspec to retrieve all notices for the user $userid
 * @param $userid
 * @return array
 */
function qa_db_user_notices_selectspec($userid)
{
	return array(
		'columns' => array('noticeid', 'content', 'format', 'tags', 'created' => 'UNIX_TIMESTAMP(created)'),
		'source' => '^usernotices WHERE userid=$ ORDER BY created',
		'arguments' => array($userid),
		'sortasc' => 'created',
	);
}


/**
 * Return the selectspec to retrieve all columns from the userpoints table for the user identified by $identifier
 * (see qa_db_user_recent_qs_selectspec() comment), as a single array
 * @param $identifier
 * @param bool $isuserid
 * @return array
 */
function qa_db_user_points_selectspec($identifier, $isuserid = QA_FINAL_EXTERNAL_USERS)
{
	return array(
		'columns' => array('points', 'qposts', 'aposts', 'cposts', 'aselects', 'aselecteds', 'qupvotes', 'qdownvotes', 'aupvotes', 'adownvotes', 'qvoteds', 'avoteds', 'upvoteds', 'downvoteds', 'bonus'),
		'source' => '^userpoints WHERE userid=' . ($isuserid ? '$' : '(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)'),
		'arguments' => array($identifier),
		'single' => true,
	);
}


/**
 * Return the selectspec to calculate the rank in points of the user identified by $identifier
 * (see qa_db_user_recent_qs_selectspec() comment), as a single value
 * @param $identifier
 * @param bool $isuserid
 * @return array
 */
function qa_db_user_rank_selectspec($identifier, $isuserid = QA_FINAL_EXTERNAL_USERS)
{
	return array(
		'columns' => array('rank' => '1+COUNT(*)'),
		'source' => '^userpoints WHERE points>COALESCE((SELECT points FROM ^userpoints WHERE userid=' . ($isuserid ? '$' : '(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)') . '), 0)',
		'arguments' => array($identifier),
		'arrayvalue' => 'rank',
		'single' => true,
	);
}


/**
 * Return the selectspec to get the top scoring users, with handles if we're using internal user management. Return
 * $count (if null, a default is used) users starting from the offset $start.
 * @param $start
 * @param $count
 * @return array
 */
function qa_db_top_users_selectspec($start, $count = null)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_USERS) : QA_DB_RETRIEVE_USERS;

	if (QA_FINAL_EXTERNAL_USERS)
		return array(
			'columns' => array('userid', 'points'),
			'source' => '^userpoints ORDER BY points DESC LIMIT #,#',
			'arguments' => array($start, $count),
			'arraykey' => 'userid',
			'sortdesc' => 'points',
		);

	else
		return array(
			'columns' => array('^users.userid', 'handle', 'points', 'flags', '^users.email', 'avatarblobid' => 'BINARY avatarblobid', 'avatarwidth', 'avatarheight'),
			'source' => '^users JOIN (SELECT userid FROM ^userpoints ORDER BY points DESC LIMIT #,#) y ON ^users.userid=y.userid JOIN ^userpoints ON ^users.userid=^userpoints.userid',
			'arguments' => array($start, $count),
			'arraykey' => 'userid',
			'sortdesc' => 'points',
		);
}


/**
 * Return the selectspec to get the newest users. Return $count (if null, a default is used) users starting from the
 * offset $start. This query must not be run when using external users
 * @param $start
 * @param $count
 * @return array
 */
function qa_db_newest_users_selectspec($start, $count = null)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_USERS) : QA_DB_RETRIEVE_USERS;

	return array(
		'columns' => array('userid', 'handle', 'flags', 'email', 'created' => 'UNIX_TIMESTAMP(created)', 'avatarblobid' => 'BINARY avatarblobid', 'avatarwidth', 'avatarheight'),
		'source' => '^users ORDER BY created DESC, userid DESC LIMIT #,#',
		'arguments' => array($start, $count),
		'sortdesc' => 'created',
		'sortdesc_2' => 'userid',
	);
}


/**
 * Return the selectspec to get information about users at a certain privilege level or higher
 * @param $level
 * @return array
 */
function qa_db_users_from_level_selectspec($level)
{
	return array(
		'columns' => array('^users.userid', 'handle', 'level'),
		'source' => '^users WHERE level>=# ORDER BY level DESC',
		'arguments' => array($level),
		'sortdesc' => 'level',
	);
}


/**
 * Return the selectspec to get information about users with the $flag bit set (unindexed query)
 * @param $flag
 * @param int $start
 * @param $limit
 * @return array
 */
function qa_db_users_with_flag_selectspec($flag, $start = 0, $limit = null)
{
	$source = '^users WHERE (flags & #)';
	$arguments = array($flag);

	if (isset($limit)) {
		$limit = min($limit, QA_DB_RETRIEVE_USERS);
		$source .= ' LIMIT #,#';
		array_push($arguments, $start, $limit);
	}

	return array(
		'columns' => array('^users.userid', 'handle', 'flags', 'level'),
		'source' => $source,
		'arguments' => $arguments,
	);
}


/**
 * Return columns for standard messages selectspec
 */
function qa_db_messages_columns()
{
	return array(
		'messageid', 'fromuserid', 'touserid', 'content', 'format',
		'created' => 'UNIX_TIMESTAMP(^messages.created)',

		'fromflags' => 'ufrom.flags', 'fromlevel' => 'ufrom.level',
		'fromemail' => 'ufrom.email', 'fromhandle' => 'ufrom.handle',
		'fromavatarblobid' => 'BINARY ufrom.avatarblobid', // cast to BINARY due to MySQL bug which renders it signed in a union
		'fromavatarwidth' => 'ufrom.avatarwidth', 'fromavatarheight' => 'ufrom.avatarheight',

		'toflags' => 'uto.flags', 'tolevel' => 'uto.level',
		'toemail' => 'uto.email', 'tohandle' => 'uto.handle',
		'toavatarblobid' => 'BINARY uto.avatarblobid', // cast to BINARY due to MySQL bug which renders it signed in a union
		'toavatarwidth' => 'uto.avatarwidth', 'toavatarheight' => 'uto.avatarheight',
	);
}


/**
 * If $fromidentifier is not null, return the selectspec to get recent private messages which have been sent from
 * the user identified by $fromidentifier+$fromisuserid to the user identified by $toidentifier+$toisuserid (see
 * qa_db_user_recent_qs_selectspec() comment). If $fromidentifier is null, then get recent wall posts
 * for the user identified by $toidentifier+$toisuserid. Return $count (if null, a default is used) messages.
 * @param $fromidentifier
 * @param $fromisuserid
 * @param $toidentifier
 * @param $toisuserid
 * @param $count
 * @param int $start
 * @return array
 */
function qa_db_recent_messages_selectspec($fromidentifier, $fromisuserid, $toidentifier, $toisuserid, $count = null, $start = 0)
{
	$count = isset($count) ? min($count, QA_DB_RETRIEVE_MESSAGES) : QA_DB_RETRIEVE_MESSAGES;

	if (isset($fromidentifier)) {
		$fromsub = $fromisuserid ? '$' : '(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)';
		$where = 'fromuserid=' . $fromsub . " AND type='PRIVATE'";
	} else
		$where = "type='PUBLIC'";
	$tosub = $toisuserid ? '$' : '(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)';

	$source = '^messages LEFT JOIN ^users ufrom ON fromuserid=ufrom.userid LEFT JOIN ^users uto ON touserid=uto.userid WHERE ' . $where . ' AND touserid=' . $tosub . ' ORDER BY ^messages.created DESC LIMIT #,#';

	$arguments = isset($fromidentifier) ? array($fromidentifier, $toidentifier, $start, $count) : array($toidentifier, $start, $count);

	return array(
		'columns' => qa_db_messages_columns(),
		'source' => $source,
		'arguments' => $arguments,
		'arraykey' => 'messageid',
		'sortdesc' => 'created',
	);
}


/**
 * Get selectspec for messages *to* specified user. $type is either 'public' or 'private'.
 * $toidentifier is a handle or userid depending on the value of $toisuserid.
 * Returns $limit messages, or all of them if $limit is null (used in qa_db_selectspec_count).
 * @param $type
 * @param $toidentifier
 * @param $toisuserid
 * @param int $start
 * @param $limit
 * @return array
 */
function qa_db_messages_inbox_selectspec($type, $toidentifier, $toisuserid, $start = 0, $limit = null)
{
	$type = strtoupper($type);

	$where = 'touserid=' . ($toisuserid ? '$' : '(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)') . ' AND type=$ AND tohidden=0';
	$source = '^messages LEFT JOIN ^users ufrom ON fromuserid=ufrom.userid LEFT JOIN ^users uto ON touserid=uto.userid WHERE ' . $where . ' ORDER BY ^messages.created DESC';
	$arguments = array($toidentifier, $type);

	if (isset($limit)) {
		$limit = min($limit, QA_DB_RETRIEVE_MESSAGES);
		$source .= ' LIMIT #,#';
		$arguments[] = $start;
		$arguments[] = $limit;
	}

	return array(
		'columns' => qa_db_messages_columns(),
		'source' => $source,
		'arguments' => $arguments,
		'arraykey' => 'messageid',
		'sortdesc' => 'created',
	);
}


/**
 * Get selectspec for messages *from* specified user. $type is either 'public' or 'private'.
 * $fromidentifier is a handle or userid depending on the value of $fromisuserid.
 * Returns $limit messages, or all of them if $limit is null (used in qa_db_selectspec_count).
 * @param $type
 * @param $fromidentifier
 * @param $fromisuserid
 * @param int $start
 * @param $limit
 * @return array
 */
function qa_db_messages_outbox_selectspec($type, $fromidentifier, $fromisuserid, $start = 0, $limit = null)
{
	$type = strtoupper($type);

	$where = 'fromuserid=' . ($fromisuserid ? '$' : '(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)') . ' AND type=$ AND fromhidden=0';
	$source = '^messages LEFT JOIN ^users ufrom ON fromuserid=ufrom.userid LEFT JOIN ^users uto ON touserid=uto.userid WHERE ' . $where . ' ORDER BY ^messages.created DESC';
	$arguments = array($fromidentifier, $type);

	if (isset($limit)) {
		$limit = min($limit, QA_DB_RETRIEVE_MESSAGES);
		$source .= ' LIMIT #,#';
		$arguments[] = $start;
		$arguments[] = $limit;
	}

	return array(
		'columns' => qa_db_messages_columns(),
		'source' => $source,
		'arguments' => $arguments,
		'arraykey' => 'messageid',
		'sortdesc' => 'created',
	);
}


/**
 * Return the selectspec to retrieve whether or not $userid has favorited entity $entitytype identifier by $identifier.
 * The $identifier should be a handle, word, backpath or postid for users, tags, categories and questions respectively.
 * @param $userid
 * @param $entitytype
 * @param $identifier
 * @return array
 */
function qa_db_is_favorite_selectspec($userid, $entitytype, $identifier)
{
	require_once QA_INCLUDE_DIR . 'app/updates.php';

	$selectspec = array(
		'columns' => array('flags' => 'COUNT(*)'),
		'source' => '^userfavorites WHERE userid=$ AND entitytype=$',
		'arrayvalue' => 'flags',
		'single' => true,
	);

	switch ($entitytype) {
		case QA_ENTITY_USER:
			$selectspec['source'] .= ' AND entityid=(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)';
			break;

		case QA_ENTITY_TAG:
			$selectspec['source'] .= ' AND entityid=(SELECT wordid FROM ^words WHERE word=$ LIMIT 1)';
			break;

		case QA_ENTITY_CATEGORY:
			$selectspec['source'] .= ' AND entityid=(SELECT categoryid FROM ^categories WHERE backpath=$ LIMIT 1)';
			$identifier = qa_db_slugs_to_backpath($identifier);
			break;

		default:
			$selectspec['source'] .= ' AND entityid=$';
			break;
	}

	$selectspec['arguments'] = array($userid, $entitytype, $identifier);

	return $selectspec;
}


/**
 * Return the selectspec to retrieve an array of $userid's favorited questions, with the usual information.
 * Returns $limit questions, or all of them if $limit is null (used in qa_db_selectspec_count).
 * @param $userid
 * @param $limit
 * @param int $start
 * @return array
 */
function qa_db_user_favorite_qs_selectspec($userid, $limit = null, $start = 0)
{
	require_once QA_INCLUDE_DIR . 'app/updates.php';

	$selectspec = qa_db_posts_basic_selectspec($userid);

	$selectspec['source'] .= ' JOIN ^userfavorites AS selectfave ON ^posts.postid=selectfave.entityid WHERE selectfave.userid=$ AND selectfave.entitytype=$ AND ^posts.type="Q" ORDER BY ^posts.created DESC';
	$selectspec['arguments'][] = $userid;
	$selectspec['arguments'][] = QA_ENTITY_QUESTION;

	if (isset($limit)) {
		$limit = min($limit, QA_DB_RETRIEVE_QS_AS);
		$selectspec['source'] .= ' LIMIT #,#';
		$selectspec['arguments'][] = $start;
		$selectspec['arguments'][] = $limit;
	}

	$selectspec['sortdesc'] = 'created';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve an array of $userid's favorited users, with information about those users' accounts.
 * Returns $limit users, or all of them if $limit is null (used in qa_db_selectspec_count).
 * @param $userid
 * @param $limit
 * @param int $start
 * @return array
 */
function qa_db_user_favorite_users_selectspec($userid, $limit = null, $start = 0)
{
	require_once QA_INCLUDE_DIR . 'app/updates.php';

	$source = '^users JOIN ^userpoints ON ^users.userid=^userpoints.userid JOIN ^userfavorites ON ^users.userid=^userfavorites.entityid WHERE ^userfavorites.userid=$ AND ^userfavorites.entitytype=$ ORDER BY ^users.handle';
	$arguments = array($userid, QA_ENTITY_USER);

	if (isset($limit)) {
		$limit = min($limit, QA_DB_RETRIEVE_USERS);
		$source .= ' LIMIT #,#';
		$arguments[] = $start;
		$arguments[] = $limit;
	}

	return array(
		'columns' => array('^users.userid', 'handle', 'points', 'flags', '^users.email', 'avatarblobid' => 'BINARY avatarblobid', 'avatarwidth', 'avatarheight'),
		'source' => $source,
		'arguments' => $arguments,
		'sortasc' => 'handle',
	);
}


/**
 * Return the selectspec to retrieve an array of $userid's favorited tags, with information about those tags.
 * Returns $limit tags, or all of them if $limit is null (used in qa_db_selectspec_count).
 * @param $userid
 * @param $limit
 * @param int $start
 * @return array
 */
function qa_db_user_favorite_tags_selectspec($userid, $limit = null, $start = 0)
{
	require_once QA_INCLUDE_DIR . 'app/updates.php';

	$source = '^words JOIN ^userfavorites ON ^words.wordid=^userfavorites.entityid WHERE ^userfavorites.userid=$ AND ^userfavorites.entitytype=$ ORDER BY ^words.tagcount DESC';
	$arguments = array($userid, QA_ENTITY_TAG);

	if (isset($limit)) {
		$limit = min($limit, QA_DB_RETRIEVE_TAGS);
		$source .= ' LIMIT #,#';
		$arguments[] = $start;
		$arguments[] = $limit;
	}

	return array(
		'columns' => array('word', 'tagcount'),
		'source' => $source,
		'arguments' => $arguments,
		'sortdesc' => 'tagcount',
	);
}


/**
 * Return the selectspec to retrieve an array of $userid's favorited categories, with information about those categories.
 * @param $userid
 * @return array
 */
function qa_db_user_favorite_categories_selectspec($userid)
{
	require_once QA_INCLUDE_DIR . 'app/updates.php';

	return array(
		'columns' => array('categoryid', 'title', 'tags', 'qcount', 'backpath', 'content'),
		'source' => "^categories JOIN ^userfavorites ON ^categories.categoryid=^userfavorites.entityid WHERE ^userfavorites.userid=$ AND ^userfavorites.entitytype=$",
		'arguments' => array($userid, QA_ENTITY_CATEGORY),
		'sortasc' => 'title',
	);
}


/**
 * Return the selectspec to retrieve information about all a user's favorited items except the questions. Depending on
 * the type of item, the array for each item will contain a userid, category backpath or tag word.
 * @param $userid
 * @return array
 */
function qa_db_user_favorite_non_qs_selectspec($userid)
{
	require_once QA_INCLUDE_DIR . 'app/updates.php';

	return array(
		'columns' => array('type' => 'entitytype', 'userid' => 'IF (entitytype=$, entityid, NULL)', 'categorybackpath' => '^categories.backpath', 'tags' => '^words.word'),
		'source' => '^userfavorites LEFT JOIN ^words ON entitytype=$ AND wordid=entityid LEFT JOIN ^categories ON entitytype=$ AND categoryid=entityid WHERE userid=$ AND entitytype!=$',
		'arguments' => array(QA_ENTITY_USER, QA_ENTITY_TAG, QA_ENTITY_CATEGORY, $userid, QA_ENTITY_QUESTION),
	);
}


/**
 * Return the selectspec to retrieve the list of recent updates for $userid. Set $forfavorites to whether this should
 * include updates on the user's favorites and $forcontent to whether it should include responses to user's content.
 * This combines events from both the user's stream and the the shared stream for any entities which the user has
 * favorited and which no longer post to user streams (see long comment in qa-db-favorites.php).
 * @param $userid
 * @param bool $forfavorites
 * @param bool $forcontent
 * @return array
 */
function qa_db_user_updates_selectspec($userid, $forfavorites = true, $forcontent = true)
{
	require_once QA_INCLUDE_DIR . 'app/updates.php';

	$selectspec = qa_db_posts_basic_selectspec($userid);

	$nonesql = qa_db_argument_to_mysql(QA_ENTITY_NONE, true);

	$selectspec['columns']['obasetype'] = 'LEFT(updateposts.type, 1)';
	$selectspec['columns']['oupdatetype'] = 'fullevents.updatetype';
	$selectspec['columns']['ohidden'] = "INSTR(updateposts.type, '_HIDDEN')>0";
	$selectspec['columns']['opostid'] = 'fullevents.lastpostid';
	$selectspec['columns']['ouserid'] = 'fullevents.lastuserid';
	$selectspec['columns']['otime'] = 'UNIX_TIMESTAMP(fullevents.updated)';
	$selectspec['columns']['opersonal'] = 'fullevents.entitytype=' . $nonesql;
	$selectspec['columns']['oparentid'] = 'updateposts.parentid';

	qa_db_add_selectspec_ousers($selectspec, 'eventusers', 'eventuserpoints');

	if ($forfavorites) { // life is hard
		$selectspec['source'] .= ' JOIN ' .
			"(SELECT entitytype, questionid, lastpostid, updatetype, lastuserid, updated FROM ^userevents WHERE userid=$" .
			($forcontent ? '' : " AND entitytype!=" . $nonesql) .
			" UNION SELECT ^sharedevents.entitytype, questionid, lastpostid, updatetype, lastuserid, updated FROM ^sharedevents JOIN ^userfavorites ON ^sharedevents.entitytype=^userfavorites.entitytype AND ^sharedevents.entityid=^userfavorites.entityid AND ^userfavorites.nouserevents=1 WHERE userid=$) fullevents ON ^posts.postid=fullevents.questionid";

		array_push($selectspec['arguments'], $userid, $userid);

	} else { // life is easy
		$selectspec['source'] .= " JOIN ^userevents AS fullevents ON ^posts.postid=fullevents.questionid AND fullevents.userid=$ AND fullevents.entitytype=" . $nonesql;
		$selectspec['arguments'][] = $userid;
	}

	$selectspec['source'] .=
		" JOIN ^posts AS updateposts ON updateposts.postid=fullevents.lastpostid" .
		" AND (updateposts.type IN ('Q', 'A', 'C') OR fullevents.entitytype=" . $nonesql . ")" .
		" AND (^posts.selchildid=fullevents.lastpostid OR NOT fullevents.updatetype<=>$) AND ^posts.type IN ('Q', 'Q_HIDDEN')" .
		(QA_FINAL_EXTERNAL_USERS ? '' : ' LEFT JOIN ^users AS eventusers ON fullevents.lastuserid=eventusers.userid') .
		' LEFT JOIN ^userpoints AS eventuserpoints ON fullevents.lastuserid=eventuserpoints.userid';
	$selectspec['arguments'][] = QA_UPDATE_SELECTED;

	unset($selectspec['arraykey']); // allow same question to be retrieved multiple times

	$selectspec['sortdesc'] = 'otime';

	return $selectspec;
}


/**
 * Return the selectspec to retrieve all of the per-hour activity limits for user $userid
 * @param $userid
 * @return array
 */
function qa_db_user_limits_selectspec($userid)
{
	return array(
		'columns' => array('action', 'period', 'count'),
		'source' => '^userlimits WHERE userid=$',
		'arguments' => array($userid),
		'arraykey' => 'action',
	);
}


/**
 * Return the selectspec to retrieve all of the per-hour activity limits for ip address $ip
 * @param $ip
 * @return array
 */
function qa_db_ip_limits_selectspec($ip)
{
	return array(
		'columns' => array('action', 'period', 'count'),
		'source' => '^iplimits WHERE ip=$',
		'arguments' => array(@inet_pton($ip)),
		'arraykey' => 'action',
	);
}


/**
 * Return the selectspec to retrieve all of the context specific (currently per-categpry) levels for the user identified by
 * $identifier, which is treated as a userid if $isuserid is true, otherwise as a handle. Set $full to true to obtain extra
 * information about these contexts (currently, categories).
 * @param $identifier
 * @param bool $isuserid
 * @param bool $full
 * @return array
 */
function qa_db_user_levels_selectspec($identifier, $isuserid = QA_FINAL_EXTERNAL_USERS, $full = false)
{
	require_once QA_INCLUDE_DIR . 'app/updates.php';

	$selectspec = array(
		'columns' => array('entityid', 'entitytype', 'level'),
		'source' => '^userlevels' . ($full ? ' LEFT JOIN ^categories ON ^userlevels.entitytype=$ AND ^userlevels.entityid=^categories.categoryid' : '') . ' WHERE userid=' . ($isuserid ? '$' : '(SELECT userid FROM ^users WHERE handle=$ LIMIT 1)'),
		'arguments' => array($identifier),
	);

	if ($full) {
		array_push($selectspec['columns'], 'title', 'backpath');
		array_unshift($selectspec['arguments'], QA_ENTITY_CATEGORY);
	}

	return $selectspec;
}