post-create.php

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

	File: qa-include/qa-db-post-create.php
	Description: Database functions for creating a question, answer or comment


	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;
}


/**
 * Create a new post in the database and return its ID (based on auto-incrementing)
 * @param $type
 * @param $parentid
 * @param $userid
 * @param $cookieid
 * @param $ip
 * @param $title
 * @param $content
 * @param $format
 * @param $tagstring
 * @param $notify
 * @param $categoryid
 * @param $name
 * @return mixed
 */
function qa_db_post_create($type, $parentid, $userid, $cookieid, $ip, $title, $content, $format, $tagstring, $notify, $categoryid = null, $name = null)
{
	qa_db_query_sub(
		'INSERT INTO ^posts (categoryid, type, parentid, userid, cookieid, createip, title, content, format, tags, notify, name, created) ' .
		'VALUES (#, $, #, $, #, $, $, $, $, $, $, $, NOW())',
		$categoryid, $type, $parentid, $userid, $cookieid, @inet_pton($ip), $title, $content, $format, $tagstring, $notify, $name
	);

	return qa_db_last_insert_id();
}


/**
 * Recalculate the full category path (i.e. columns catidpath1/2/3) for posts from $firstpostid to $lastpostid (if specified)
 * @param $firstpostid
 * @param $lastpostid
 */
function qa_db_posts_calc_category_path($firstpostid, $lastpostid = null)
{
	if (!isset($lastpostid))
		$lastpostid = $firstpostid;

	qa_db_query_sub(
		"UPDATE ^posts AS x, (SELECT ^posts.postid, " .
		"COALESCE(parent2.parentid, parent1.parentid, parent0.parentid, parent0.categoryid) AS catidpath1, " .
		"IF (parent2.parentid IS NOT NULL, parent1.parentid, IF (parent1.parentid IS NOT NULL, parent0.parentid, IF (parent0.parentid IS NOT NULL, parent0.categoryid, NULL))) AS catidpath2, " .
		"IF (parent2.parentid IS NOT NULL, parent0.parentid, IF (parent1.parentid IS NOT NULL, parent0.categoryid, NULL)) AS catidpath3 " .
		"FROM ^posts LEFT JOIN ^categories AS parent0 ON ^posts.categoryid=parent0.categoryid LEFT JOIN ^categories AS parent1 ON parent0.parentid=parent1.categoryid LEFT JOIN ^categories AS parent2 ON parent1.parentid=parent2.categoryid WHERE ^posts.postid BETWEEN # AND #) AS a SET x.catidpath1=a.catidpath1, x.catidpath2=a.catidpath2, x.catidpath3=a.catidpath3 WHERE x.postid=a.postid",
		$firstpostid, $lastpostid
	); // requires QA_CATEGORY_DEPTH=4
}


/**
 * Get the full category path (including categoryid) for $postid
 * @param $postid
 * @return array|null
 */
function qa_db_post_get_category_path($postid)
{
	return qa_db_read_one_assoc(qa_db_query_sub(
		'SELECT categoryid, catidpath1, catidpath2, catidpath3 FROM ^posts WHERE postid=#',
		$postid
	)); // requires QA_CATEGORY_DEPTH=4
}


/**
 * Update the cached number of answers for $questionid in the database, along with the highest netvotes of any of its answers
 * @param $questionid
 */
function qa_db_post_acount_update($questionid)
{
	if (qa_should_update_counts()) {
		qa_db_query_sub(
			"UPDATE ^posts AS x, (SELECT COUNT(*) AS acount, COALESCE(GREATEST(MAX(netvotes), 0), 0) AS amaxvote FROM ^posts WHERE parentid=# AND type='A') AS a SET x.acount=a.acount, x.amaxvote=a.amaxvote WHERE x.postid=#",
			$questionid, $questionid
		);
	}
}


/**
 * Recalculate the number of questions for each category in $path retrieved via qa_db_post_get_category_path()
 * @param $path
 */
function qa_db_category_path_qcount_update($path)
{
	qa_db_ifcategory_qcount_update($path['categoryid']); // requires QA_CATEGORY_DEPTH=4
	qa_db_ifcategory_qcount_update($path['catidpath1']);
	qa_db_ifcategory_qcount_update($path['catidpath2']);
	qa_db_ifcategory_qcount_update($path['catidpath3']);
}


/**
 * Update the cached number of questions for category $categoryid in the database, including its subcategories
 * @param $categoryid
 */
function qa_db_ifcategory_qcount_update($categoryid)
{
	if (qa_should_update_counts() && isset($categoryid)) {
		// This seemed like the most sensible approach which avoids explicitly calculating the category's depth in the hierarchy

		qa_db_query_sub(
			"UPDATE ^categories SET qcount=GREATEST( (SELECT COUNT(*) FROM ^posts WHERE categoryid=# AND type='Q'), (SELECT COUNT(*) FROM ^posts WHERE catidpath1=# AND type='Q'), (SELECT COUNT(*) FROM ^posts WHERE catidpath2=# AND type='Q'), (SELECT COUNT(*) FROM ^posts WHERE catidpath3=# AND type='Q') ) WHERE categoryid=#",
			$categoryid, $categoryid, $categoryid, $categoryid, $categoryid
		); // requires QA_CATEGORY_DEPTH=4
	}
}


/**
 * Add rows into the database title index, where $postid contains the words $wordids - this does the same sort
 * of thing as qa_db_posttags_add_post_wordids() in a different way, for no particularly good reason.
 * @param $postid
 * @param $wordids
 */
function qa_db_titlewords_add_post_wordids($postid, $wordids)
{
	if (count($wordids)) {
		$rowstoadd = array();
		foreach ($wordids as $wordid)
			$rowstoadd[] = array($postid, $wordid);

		qa_db_query_sub(
			'INSERT INTO ^titlewords (postid, wordid) VALUES #',
			$rowstoadd
		);
	}
}


/**
 * Add rows into the database content index, where $postid (of $type, with the antecedent $questionid)
 * has words as per the keys of $wordidcounts, and the corresponding number of those words in the values.
 * @param $postid
 * @param $type
 * @param $questionid
 * @param $wordidcounts
 */
function qa_db_contentwords_add_post_wordidcounts($postid, $type, $questionid, $wordidcounts)
{
	if (count($wordidcounts)) {
		$rowstoadd = array();
		foreach ($wordidcounts as $wordid => $count)
			$rowstoadd[] = array($postid, $wordid, $count, $type, $questionid);

		qa_db_query_sub(
			'INSERT INTO ^contentwords (postid, wordid, count, type, questionid) VALUES #',
			$rowstoadd
		);
	}
}


/**
 * Add rows into the database index of individual tag words, where $postid contains the words $wordids
 * @param $postid
 * @param $wordids
 */
function qa_db_tagwords_add_post_wordids($postid, $wordids)
{
	if (count($wordids)) {
		$rowstoadd = array();
		foreach ($wordids as $wordid)
			$rowstoadd[] = array($postid, $wordid);

		qa_db_query_sub(
			'INSERT INTO ^tagwords (postid, wordid) VALUES #',
			$rowstoadd
		);
	}
}


/**
 * Add rows into the database index of whole tags, where $postid contains the tags $wordids
 * @param $postid
 * @param $wordids
 */
function qa_db_posttags_add_post_wordids($postid, $wordids)
{
	if (count($wordids)) {
		qa_db_query_sub(
			'INSERT INTO ^posttags (postid, wordid, postcreated) SELECT postid, wordid, created FROM ^words, ^posts WHERE postid=# AND wordid IN ($)',
			$postid, $wordids
		);
	}
}


/**
 * Return an array mapping each word in $words to its corresponding wordid in the database
 * @param $words
 * @return array
 */
function qa_db_word_mapto_ids($words)
{
	if (count($words)) {
		return qa_db_read_all_assoc(qa_db_query_sub(
			'SELECT wordid, word FROM ^words WHERE word IN ($)', $words
		), 'word', 'wordid');
	}

	return array();
}


/**
 * Return an array mapping each word in $words to its corresponding wordid in the database, adding any that are missing
 * @param $words
 * @return array
 */
function qa_db_word_mapto_ids_add($words)
{
	$wordtoid = qa_db_word_mapto_ids($words);

	$wordstoadd = array();
	foreach ($words as $word) {
		if (!isset($wordtoid[$word]))
			$wordstoadd[] = $word;
	}

	if (count($wordstoadd)) {
		qa_db_query_sub('LOCK TABLES ^words WRITE'); // to prevent two requests adding the same word

		$wordtoid = qa_db_word_mapto_ids($words); // map it again in case table content changed before it was locked

		$rowstoadd = array();
		foreach ($words as $word) {
			if (!isset($wordtoid[$word]))
				$rowstoadd[] = array($word);
		}

		qa_db_query_sub('INSERT IGNORE INTO ^words (word) VALUES $', $rowstoadd);

		qa_db_query_sub('UNLOCK TABLES');

		$wordtoid = qa_db_word_mapto_ids($words); // do it one last time
	}

	return $wordtoid;
}


/**
 * Update the titlecount column in the database for the words in $wordids, based on how many posts they appear in the title of
 * @param $wordids
 */
function qa_db_word_titlecount_update($wordids)
{
	if (qa_should_update_counts() && count($wordids)) {
		qa_db_query_sub(
			'UPDATE ^words AS x, (SELECT ^words.wordid, COUNT(^titlewords.wordid) AS titlecount FROM ^words LEFT JOIN ^titlewords ON ^titlewords.wordid=^words.wordid WHERE ^words.wordid IN (#) GROUP BY wordid) AS a SET x.titlecount=a.titlecount WHERE x.wordid=a.wordid',
			$wordids
		);
	}
}


/**
 * Update the contentcount column in the database for the words in $wordids, based on how many posts they appear in the content of
 * @param $wordids
 */
function qa_db_word_contentcount_update($wordids)
{
	if (qa_should_update_counts() && count($wordids)) {
		qa_db_query_sub(
			'UPDATE ^words AS x, (SELECT ^words.wordid, COUNT(^contentwords.wordid) AS contentcount FROM ^words LEFT JOIN ^contentwords ON ^contentwords.wordid=^words.wordid WHERE ^words.wordid IN (#) GROUP BY wordid) AS a SET x.contentcount=a.contentcount WHERE x.wordid=a.wordid',
			$wordids
		);
	}
}


/**
 * Update the tagwordcount column in the database for the individual tag words in $wordids, based on how many posts they appear in the tags of
 * @param $wordids
 */
function qa_db_word_tagwordcount_update($wordids)
{
	if (qa_should_update_counts() && count($wordids)) {
		qa_db_query_sub(
			'UPDATE ^words AS x, (SELECT ^words.wordid, COUNT(^tagwords.wordid) AS tagwordcount FROM ^words LEFT JOIN ^tagwords ON ^tagwords.wordid=^words.wordid WHERE ^words.wordid IN (#) GROUP BY wordid) AS a SET x.tagwordcount=a.tagwordcount WHERE x.wordid=a.wordid',
			$wordids
		);
	}
}


/**
 * Update the tagcount column in the database for the whole tags in $wordids, based on how many posts they appear as tags of
 * @param $wordids
 */
function qa_db_word_tagcount_update($wordids)
{
	if (qa_should_update_counts() && count($wordids)) {
		qa_db_query_sub(
			'UPDATE ^words AS x, (SELECT ^words.wordid, COUNT(^posttags.wordid) AS tagcount FROM ^words LEFT JOIN ^posttags ON ^posttags.wordid=^words.wordid WHERE ^words.wordid IN (#) GROUP BY wordid) AS a SET x.tagcount=a.tagcount WHERE x.wordid=a.wordid',
			$wordids
		);
	}
}


/**
 * Update the cached count in the database of the number of questions (excluding hidden/queued)
 */
function qa_db_qcount_update()
{
	if (qa_should_update_counts()) {
		qa_db_query_sub(
			"INSERT INTO ^options (title, content) " .
			"SELECT 'cache_qcount', COUNT(*) FROM ^posts " .
			"WHERE type = 'Q' " .
			"ON DUPLICATE KEY UPDATE content = VALUES(content)"
		);
	}
}


/**
 * Update the cached count in the database of the number of answers (excluding hidden/queued)
 */
function qa_db_acount_update()
{
	if (qa_should_update_counts()) {
		qa_db_query_sub(
			"INSERT INTO ^options (title, content) " .
			"SELECT 'cache_acount', COUNT(*) FROM ^posts " .
			"WHERE type = 'A' " .
			"ON DUPLICATE KEY UPDATE content = VALUES(content)"
		);
	}
}


/**
 * Update the cached count in the database of the number of comments (excluding hidden/queued)
 */
function qa_db_ccount_update()
{
	if (qa_should_update_counts()) {
		qa_db_query_sub(
			"INSERT INTO ^options (title, content) " .
			"SELECT 'cache_ccount', COUNT(*) FROM ^posts " .
			"WHERE type = 'C' " .
			"ON DUPLICATE KEY UPDATE content = VALUES(content)"
		);
	}
}


/**
 * Update the cached count in the database of the number of different tags used
 */
function qa_db_tagcount_update()
{
	if (qa_should_update_counts()) {
		qa_db_query_sub(
			"INSERT INTO ^options (title, content) " .
			"SELECT 'cache_tagcount', COUNT(*) FROM ^words " .
			"WHERE tagcount > 0 " .
			"ON DUPLICATE KEY UPDATE content = VALUES(content)"
		);
	}
}


/**
 * Update the cached count in the database of the number of unanswered questions (excluding hidden/queued)
 */
function qa_db_unaqcount_update()
{
	if (qa_should_update_counts()) {
		qa_db_query_sub(
			"INSERT INTO ^options (title, content) " .
			"SELECT 'cache_unaqcount', COUNT(*) FROM ^posts " .
			"WHERE type = 'Q' AND acount = 0 AND closedbyid IS NULL " .
			"ON DUPLICATE KEY UPDATE content = VALUES(content)"
		);
	}
}


/**
 * Update the cached count in the database of the number of questions with no answer selected (excluding hidden/queued)
 */
function qa_db_unselqcount_update()
{
	if (qa_should_update_counts()) {
		qa_db_query_sub(
			"INSERT INTO ^options (title, content) " .
			"SELECT 'cache_unselqcount', COUNT(*) FROM ^posts " .
			"WHERE type = 'Q' AND selchildid IS NULL AND closedbyid IS NULL " .
			"ON DUPLICATE KEY UPDATE content = VALUES(content)"
		);
	}
}


/**
 * Update the cached count in the database of the number of questions with no upvoted answers (excluding hidden/queued)
 */
function qa_db_unupaqcount_update()
{
	if (qa_should_update_counts()) {
		qa_db_query_sub(
			"INSERT INTO ^options (title, content) " .
			"SELECT 'cache_unupaqcount', COUNT(*) FROM ^posts " .
			"WHERE type = 'Q' AND amaxvote = 0 AND closedbyid IS NULL " .
			"ON DUPLICATE KEY UPDATE content = VALUES(content)"
		);
	}
}


/**
 * Update the cached count in the database of the number of posts which are queued for moderation
 */
function qa_db_queuedcount_update()
{
	if (qa_should_update_counts()) {
		qa_db_query_sub(
			"INSERT INTO ^options (title, content) " .
			"SELECT 'cache_queuedcount', COUNT(*) FROM ^posts " .
			"WHERE type IN ('Q_QUEUED', 'A_QUEUED', 'C_QUEUED') " .
			"ON DUPLICATE KEY UPDATE content = VALUES(content)"
		);
	}
}