post-create.php 13.7 KB
Newer Older
Scott committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
<?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
*/

Scott committed
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
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()) {
Scott committed
99
		qa_db_query_sub(
Scott committed
100 101
			"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
Scott committed
102 103
		);
	}
Scott committed
104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127
}


/**
 * 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
Scott committed
128 129

		qa_db_query_sub(
Scott committed
130 131
			"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
Scott committed
132 133
		); // requires QA_CATEGORY_DEPTH=4
	}
Scott committed
134 135 136 137 138 139 140 141 142 143 144 145 146 147 148
}


/**
 * 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);
Scott committed
149

Scott committed
150 151 152 153
		qa_db_query_sub(
			'INSERT INTO ^titlewords (postid, wordid) VALUES #',
			$rowstoadd
		);
Scott committed
154
	}
Scott committed
155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171
}


/**
 * 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);
Scott committed
172

Scott committed
173 174 175 176
		qa_db_query_sub(
			'INSERT INTO ^contentwords (postid, wordid, count, type, questionid) VALUES #',
			$rowstoadd
		);
Scott committed
177
	}
Scott committed
178
}
Scott committed
179 180


Scott committed
181 182 183 184 185 186 187 188 189 190 191
/**
 * 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);
Scott committed
192

Scott committed
193 194 195 196
		qa_db_query_sub(
			'INSERT INTO ^tagwords (postid, wordid) VALUES #',
			$rowstoadd
		);
Scott committed
197
	}
Scott committed
198
}
Scott committed
199 200


Scott committed
201 202 203 204 205 206 207 208 209 210 211 212
/**
 * 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
		);
Scott committed
213
	}
Scott committed
214 215 216 217 218 219 220 221 222 223 224 225 226 227
}


/**
 * 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');
Scott committed
228 229
	}

Scott committed
230 231
	return array();
}
Scott committed
232 233


Scott committed
234 235 236 237 238 239 240 241
/**
 * 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);
Scott committed
242

Scott committed
243 244 245 246
	$wordstoadd = array();
	foreach ($words as $word) {
		if (!isset($wordtoid[$word]))
			$wordstoadd[] = $word;
Scott committed
247 248
	}

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

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

Scott committed
254 255
		$rowstoadd = array();
		foreach ($words as $word) {
Scott committed
256
			if (!isset($wordtoid[$word]))
Scott committed
257 258
				$rowstoadd[] = array($word);
		}
Scott committed
259

Scott committed
260
		qa_db_query_sub('INSERT IGNORE INTO ^words (word) VALUES $', $rowstoadd);
Scott committed
261

Scott committed
262
		qa_db_query_sub('UNLOCK TABLES');
Scott committed
263

Scott committed
264
		$wordtoid = qa_db_word_mapto_ids($words); // do it one last time
Scott committed
265 266
	}

Scott committed
267 268
	return $wordtoid;
}
Scott committed
269

Scott committed
270 271 272 273 274 275 276 277 278 279 280 281

/**
 * 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
		);
Scott committed
282
	}
Scott committed
283
}
Scott committed
284 285


Scott committed
286 287 288 289 290 291 292 293 294 295 296
/**
 * 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
		);
Scott committed
297
	}
Scott committed
298
}
Scott committed
299 300


Scott committed
301 302 303 304 305 306 307 308 309 310 311
/**
 * 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
		);
Scott committed
312
	}
Scott committed
313
}
Scott committed
314 315


Scott committed
316 317 318 319 320 321 322 323 324 325 326
/**
 * 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
		);
Scott committed
327
	}
Scott committed
328
}
Scott committed
329 330


Scott committed
331 332 333 334 335 336 337 338 339 340 341 342
/**
 * 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)"
		);
Scott committed
343
	}
Scott committed
344
}
Scott committed
345 346


Scott committed
347 348 349 350 351 352 353 354 355 356 357 358
/**
 * 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)"
		);
Scott committed
359
	}
Scott committed
360
}
Scott committed
361 362


Scott committed
363 364 365 366 367 368 369 370 371 372 373 374
/**
 * 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)"
		);
Scott committed
375
	}
Scott committed
376
}
Scott committed
377 378


Scott committed
379 380 381 382 383 384 385 386 387 388 389 390
/**
 * 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)"
		);
Scott committed
391
	}
Scott committed
392
}
Scott committed
393 394


Scott committed
395 396 397 398 399 400 401 402 403 404 405 406
/**
 * 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)"
		);
Scott committed
407
	}
Scott committed
408
}
Scott committed
409 410


Scott committed
411 412 413 414 415 416 417 418 419 420 421 422
/**
 * 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)"
		);
Scott committed
423
	}
Scott committed
424
}
Scott committed
425 426


Scott committed
427 428 429 430 431 432 433 434 435 436 437 438
/**
 * 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)"
		);
Scott committed
439
	}
Scott committed
440
}
Scott committed
441 442


Scott committed
443 444 445 446 447 448 449 450 451 452 453 454
/**
 * 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)"
		);
Scott committed
455
	}
Scott committed
456
}