qa-db-post-create.php 12.8 KB
Newer Older
Gideon Greenspan committed
1
<?php
Scott Vivian committed
2

Gideon Greenspan committed
3
/*
Gideon Greenspan committed
4
	Question2Answer by Gideon Greenspan and contributors
Gideon Greenspan committed
5 6 7

	http://www.question2answer.org/

Scott Vivian committed
8

Gideon Greenspan committed
9 10 11 12 13 14 15 16 17
	File: qa-include/qa-db-post-create.php
	Version: See define()s at top of qa-include/qa-base.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.
Scott Vivian committed
18

Gideon Greenspan committed
19 20 21 22 23 24 25 26 27 28 29 30 31 32
	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;
	}


Gideon Greenspan committed
33
	function qa_db_post_create($type, $parentid, $userid, $cookieid, $ip, $title, $content, $format, $tagstring, $notify, $categoryid=null, $name=null)
Gideon Greenspan committed
34 35 36 37 38
/*
	Create a new post in the database and return its ID (based on auto-incrementing)
*/
	{
		qa_db_query_sub(
Gideon Greenspan committed
39 40 41
			'INSERT INTO ^posts (categoryid, type, parentid, userid, cookieid, createip, title, content, format, tags, notify, name, created) '.
			'VALUES (#, $, #, $, #, INET_ATON($), $, $, $, $, $, $, NOW())',
			$categoryid, $type, $parentid, $userid, $cookieid, $ip, $title, $content, $format, $tagstring, $notify, $name
Gideon Greenspan committed
42
		);
Scott Vivian committed
43

Gideon Greenspan committed
44 45 46
		return qa_db_last_insert_id();
	}

Scott Vivian committed
47

Gideon Greenspan committed
48 49 50 51 52 53 54
	function qa_db_posts_calc_category_path($firstpostid, $lastpostid=null)
/*
	Recalculate the full category path (i.e. columns catidpath1/2/3) for posts from $firstpostid to $lastpostid (if specified)
*/
	{
		if (!isset($lastpostid))
			$lastpostid=$firstpostid;
Scott Vivian committed
55

Gideon Greenspan committed
56 57 58 59 60 61 62 63 64
		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
	}
Scott Vivian committed
65 66


Gideon Greenspan committed
67 68 69 70 71 72 73 74 75 76
	function qa_db_post_get_category_path($postid)
/*
	Get the full category path (including categoryid) for $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
	}
Scott Vivian committed
77 78


Gideon Greenspan committed
79 80 81 82 83 84 85 86 87 88 89
	function qa_db_post_acount_update($questionid)
/*
	Update the cached number of answers for $questionid in the database, along with the highest netvotes of any of its answers
*/
	{
		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
			);
	}
Scott Vivian committed
90 91


Gideon Greenspan committed
92 93 94 95 96 97 98 99 100 101
	function qa_db_category_path_qcount_update($path)
/*
	Recalculate the number of questions for each category in $path retrieved via qa_db_post_get_category_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']);
	}
Scott Vivian committed
102 103


Gideon Greenspan committed
104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
	function qa_db_ifcategory_qcount_update($categoryid)
/*
	Update the cached number of questions for category $categoryid in the database, including its subcategories
*/
	{
		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
		}
	}

Scott Vivian committed
119

Gideon Greenspan committed
120 121 122 123 124 125 126 127 128 129
	function qa_db_titlewords_add_post_wordids($postid, $wordids)
/*
	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.
*/
	{
		if (count($wordids)) {
			$rowstoadd=array();
			foreach ($wordids as $wordid)
				$rowstoadd[]=array($postid, $wordid);
Scott Vivian committed
130

Gideon Greenspan committed
131 132 133 134 135 136 137
			qa_db_query_sub(
				'INSERT INTO ^titlewords (postid, wordid) VALUES #',
				$rowstoadd
			);
		}
	}

Scott Vivian committed
138

Gideon Greenspan committed
139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
	function qa_db_contentwords_add_post_wordidcounts($postid, $type, $questionid, $wordidcounts)
/*
	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.
*/
	{
		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
			);
		}
	}
Scott Vivian committed
156 157


Gideon Greenspan committed
158 159 160 161 162 163 164 165 166
	function qa_db_tagwords_add_post_wordids($postid, $wordids)
/*
	Add rows into the database index of individual tag words, where $postid contains the words $wordids
*/
	{
		if (count($wordids)) {
			$rowstoadd=array();
			foreach ($wordids as $wordid)
				$rowstoadd[]=array($postid, $wordid);
Scott Vivian committed
167

Gideon Greenspan committed
168 169 170 171 172 173 174
			qa_db_query_sub(
				'INSERT INTO ^tagwords (postid, wordid) VALUES #',
				$rowstoadd
			);
		}
	}

Scott Vivian committed
175

Gideon Greenspan committed
176 177 178 179 180 181 182 183 184 185 186 187
	function qa_db_posttags_add_post_wordids($postid, $wordids)
/*
	Add rows into the database index of whole tags, where $postid contains the tags $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 Vivian committed
188

Gideon Greenspan committed
189 190 191 192 193 194 195 196 197 198 199 200 201
	function qa_db_word_mapto_ids($words)
/*
	Return an array mapping each word in $words to its corresponding wordid in the database
*/
	{
		if (count($words))
			return qa_db_read_all_assoc(qa_db_query_sub(
				'SELECT wordid, word FROM ^words WHERE word IN ($)', $words
			), 'word', 'wordid');
		else
			return array();
	}

Scott Vivian committed
202

Gideon Greenspan committed
203 204 205 206 207 208
	function qa_db_word_mapto_ids_add($words)
/*
	Return an array mapping each word in $words to its corresponding wordid in the database, adding any that are missing
*/
	{
		$wordtoid=qa_db_word_mapto_ids($words);
Scott Vivian committed
209

Gideon Greenspan committed
210 211 212 213
		$wordstoadd=array();
		foreach ($words as $word)
			if (!isset($wordtoid[$word]))
				$wordstoadd[]=$word;
Scott Vivian committed
214

Gideon Greenspan committed
215 216
		if (count($wordstoadd)) {
			qa_db_query_sub('LOCK TABLES ^words WRITE'); // to prevent two requests adding the same word
Scott Vivian committed
217

Gideon Greenspan committed
218
			$wordtoid=qa_db_word_mapto_ids($words); // map it again in case table content changed before it was locked
Scott Vivian committed
219

Gideon Greenspan committed
220 221 222 223
			$rowstoadd=array();
			foreach ($words as $word)
				if (!isset($wordtoid[$word]))
					$rowstoadd[]=array($word);
Scott Vivian committed
224

Gideon Greenspan committed
225
			qa_db_query_sub('INSERT IGNORE INTO ^words (word) VALUES $', $rowstoadd);
Scott Vivian committed
226

Gideon Greenspan committed
227
			qa_db_query_sub('UNLOCK TABLES');
Scott Vivian committed
228

Gideon Greenspan committed
229 230
			$wordtoid=qa_db_word_mapto_ids($words); // do it one last time
		}
Scott Vivian committed
231

Gideon Greenspan committed
232 233
		return $wordtoid;
	}
Scott Vivian committed
234

Gideon Greenspan committed
235 236 237 238 239 240 241 242 243 244 245 246 247

	function qa_db_word_titlecount_update($wordids)
/*
	Update the titlecount column in the database for the words in $wordids, based on how many posts they appear in the title of
*/
	{
		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 Vivian committed
248

Gideon Greenspan committed
249 250 251 252 253 254 255 256 257 258 259 260
	function qa_db_word_contentcount_update($wordids)
/*
	Update the contentcount column in the database for the words in $wordids, based on how many posts they appear in the content of
*/
	{
		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 Vivian committed
261

Gideon Greenspan committed
262 263 264 265 266 267 268 269 270 271 272 273
	function qa_db_word_tagwordcount_update($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
*/
	{
		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 Vivian committed
274

Gideon Greenspan committed
275 276 277 278 279 280 281 282 283 284 285 286
	function qa_db_word_tagcount_update($wordids)
/*
	Update the tagcount column in the database for the whole tags in $wordids, based on how many posts they appear as tags of
*/
	{
		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 Vivian committed
287

Gideon Greenspan committed
288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326
	function qa_db_qcount_update()
/*
	Update the cached count in the database of the number of questions (excluding hidden/queued)
*/
	{
		if (qa_should_update_counts())
			qa_db_query_sub("REPLACE ^options (title, content) SELECT 'cache_qcount', COUNT(*) FROM ^posts WHERE type='Q'");
	}


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


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


	function qa_db_tagcount_update()
/*
	Update the cached count in the database of the number of different tags used
*/
	{
		if (qa_should_update_counts())
			qa_db_query_sub("REPLACE ^options (title, content) SELECT 'cache_tagcount', COUNT(*) FROM ^words WHERE tagcount>0");
	}

Scott Vivian committed
327

Gideon Greenspan committed
328 329 330 331 332 333
	function qa_db_unaqcount_update()
/*
	Update the cached count in the database of the number of unanswered questions (excluding hidden/queued)
*/
	{
		if (qa_should_update_counts())
Gideon Greenspan committed
334
			qa_db_query_sub("REPLACE ^options (title, content) SELECT 'cache_unaqcount', COUNT(*) FROM ^posts WHERE type='Q' AND acount=0 AND closedbyid IS NULL");
Gideon Greenspan committed
335
	}
Scott Vivian committed
336 337


Gideon Greenspan committed
338 339 340 341 342 343
	function qa_db_unselqcount_update()
/*
	Update the cached count in the database of the number of questions with no answer selected (excluding hidden/queued)
*/
	{
		if (qa_should_update_counts())
Gideon Greenspan committed
344
			qa_db_query_sub("REPLACE ^options (title, content) SELECT 'cache_unselqcount', COUNT(*) FROM ^posts WHERE type='Q' AND selchildid IS NULL AND closedbyid IS NULL");
Gideon Greenspan committed
345
	}
Scott Vivian committed
346 347


Gideon Greenspan committed
348 349 350 351 352 353
	function qa_db_unupaqcount_update()
/*
	Update the cached count in the database of the number of questions with no upvoted answers (excluding hidden/queued)
*/
	{
		if (qa_should_update_counts())
Gideon Greenspan committed
354
			qa_db_query_sub("REPLACE ^options (title, content) SELECT 'cache_unupaqcount', COUNT(*) FROM ^posts WHERE type='Q' AND amaxvote=0 AND closedbyid IS NULL");
Gideon Greenspan committed
355
	}
Scott Vivian committed
356 357


Gideon Greenspan committed
358
	function qa_db_queuedcount_update()
Gideon Greenspan committed
359 360 361
/*
	Update the cached count in the database of the number of posts which are queued for moderation
*/
Gideon Greenspan committed
362 363 364 365
	{
		if (qa_should_update_counts())
			qa_db_query_sub("REPLACE ^options (title, content) SELECT 'cache_queuedcount', COUNT(*) FROM ^posts WHERE type IN ('Q_QUEUED', 'A_QUEUED', 'C_QUEUED')");
	}
Gideon Greenspan committed
366 367 368 369

/*
	Omit PHP closing tag to help avoid accidental output
*/