456 lines
17 KiB
PHP
456 lines
17 KiB
PHP
<?php
|
|
namespace Misuzu\Forum;
|
|
|
|
use InvalidArgumentException;
|
|
use RuntimeException;
|
|
use stdClass;
|
|
use Index\DateTime;
|
|
use Index\Data\DbStatementCache;
|
|
use Index\Data\DbTools;
|
|
use Index\Data\IDbConnection;
|
|
use Index\Net\IPAddress;
|
|
use Misuzu\Pagination;
|
|
use Misuzu\Users\UserInfo;
|
|
|
|
class ForumPosts {
|
|
private IDbConnection $dbConn;
|
|
private DbStatementCache $cache;
|
|
|
|
public function __construct(IDbConnection $dbConn) {
|
|
$this->dbConn = $dbConn;
|
|
$this->cache = new DbStatementCache($dbConn);
|
|
}
|
|
|
|
public function countPosts(
|
|
ForumCategoryInfo|string|null $categoryInfo = null,
|
|
ForumTopicInfo|string|null $topicInfo = null,
|
|
UserInfo|string|null $userInfo = null,
|
|
ForumPostInfo|string|null $upToPostInfo = null,
|
|
?bool $deleted = null
|
|
): int {
|
|
if($categoryInfo instanceof ForumCategoryInfo)
|
|
$categoryInfo = $categoryInfo->getId();
|
|
if($topicInfo instanceof ForumTopicInfo)
|
|
$topicInfo = $topicInfo->getId();
|
|
if($userInfo instanceof UserInfo)
|
|
$userInfo = $userInfo->getId();
|
|
if($upToPostInfo instanceof ForumPostInfo)
|
|
$upToPostInfo = $upToPostInfo->getId();
|
|
|
|
$hasCategoryInfo = $categoryInfo !== null;
|
|
$hasTopicInfo = $topicInfo !== null;
|
|
$hasUserInfo = $userInfo !== null;
|
|
$hasUpToPostInfo = $upToPostInfo !== null;
|
|
$hasDeleted = $deleted !== null;
|
|
|
|
$args = 0;
|
|
$query = 'SELECT COUNT(*) FROM msz_forum_posts';
|
|
if($hasCategoryInfo) {
|
|
++$args;
|
|
$query .= ' WHERE forum_id = ?';
|
|
}
|
|
if($hasTopicInfo)
|
|
$query .= sprintf(' %s topic_id = ?', ++$args > 1 ? 'AND' : 'WHERE');
|
|
if($hasUserInfo)
|
|
$query .= sprintf(' %s user_id = ?', ++$args > 1 ? 'AND' : 'WHERE');
|
|
if($hasUpToPostInfo)
|
|
$query .= sprintf(' %s post_id < ?', ++$args > 1 ? 'AND' : 'WHERE');
|
|
if($hasDeleted)
|
|
$query .= sprintf(' %s post_deleted %s NULL', ++$args > 1 ? 'AND' : 'WHERE', $deleted ? 'IS NOT' : 'IS');
|
|
|
|
$args = 0;
|
|
$stmt = $this->cache->get($query);
|
|
if($hasCategoryInfo)
|
|
$stmt->addParameter(++$args, $categoryInfo);
|
|
if($hasTopicInfo)
|
|
$stmt->addParameter(++$args, $topicInfo);
|
|
if($hasUserInfo)
|
|
$stmt->addParameter(++$args, $userInfo);
|
|
if($hasUpToPostInfo)
|
|
$stmt->addParameter(++$args, $upToPostInfo);
|
|
$stmt->execute();
|
|
|
|
$result = $stmt->getResult();
|
|
return $result->next() ? $result->getInteger(0) : 0;
|
|
}
|
|
|
|
public function getPosts(
|
|
ForumCategoryInfo|string|array|null $categoryInfo = null,
|
|
ForumTopicInfo|string|null $topicInfo = null,
|
|
UserInfo|string|null $userInfo = null,
|
|
ForumPostInfo|string|null $upToPostInfo = null,
|
|
ForumPostInfo|string|null $afterPostInfo = null,
|
|
?int $newerThanDays = null,
|
|
?array $searchQuery = null,
|
|
?bool $deleted = null,
|
|
?Pagination $pagination = null
|
|
): iterable {
|
|
// remove this hack when search server
|
|
$hasSearchQuery = $searchQuery !== null;
|
|
$doSearchOrder = false;
|
|
if($hasSearchQuery) {
|
|
if(!empty($searchQuery['type'])
|
|
&& $searchQuery['type'] !== 'forum'
|
|
&& $searchQuery['type'] !== 'forum:post')
|
|
return [];
|
|
|
|
$userInfo = null;
|
|
$deleted = false;
|
|
$pagination = null;
|
|
$doSearchOrder = true;
|
|
$afterPostInfo = null;
|
|
$newerThanDays = null;
|
|
|
|
if(!empty($searchQuery['author']))
|
|
$userInfo = $searchQuery['author'];
|
|
|
|
if(!empty($searchQuery['after']))
|
|
$afterPostInfo = $searchQuery['after'];
|
|
|
|
$searchQuery = $searchQuery['query_string'];
|
|
$hasSearchQuery = !empty($searchQuery);
|
|
}
|
|
|
|
if($categoryInfo instanceof ForumCategoryInfo)
|
|
$categoryInfo = $categoryInfo->getId();
|
|
if($topicInfo instanceof ForumTopicInfo)
|
|
$topicInfo = $topicInfo->getId();
|
|
if($userInfo instanceof UserInfo)
|
|
$userInfo = $userInfo->getId();
|
|
if($upToPostInfo instanceof ForumPostInfo)
|
|
$upToPostInfo = $upToPostInfo->getId();
|
|
if($afterPostInfo instanceof ForumPostInfo)
|
|
$afterPostInfo = $afterPostInfo->getId();
|
|
|
|
$hasCategoryInfo = $categoryInfo !== null;
|
|
$hasTopicInfo = $topicInfo !== null;
|
|
$hasUserInfo = $userInfo !== null;
|
|
$hasUpToPostInfo = $upToPostInfo !== null;
|
|
$hasAfterPostInfo = $afterPostInfo !== null;
|
|
$hasNewerThanDays = $newerThanDays !== null;
|
|
$hasDeleted = $deleted !== null;
|
|
$hasPagination = $pagination !== null;
|
|
|
|
$args = 0;
|
|
$query = 'SELECT post_id, topic_id, forum_id, user_id, INET6_NTOA(post_ip), post_text, post_parse, post_display_signature, UNIX_TIMESTAMP(post_created), UNIX_TIMESTAMP(post_edited), UNIX_TIMESTAMP(post_deleted) FROM msz_forum_posts';
|
|
if($hasCategoryInfo) {
|
|
++$args;
|
|
if(is_array($categoryInfo))
|
|
$query .= sprintf(' WHERE forum_id IN (%s)', DbTools::prepareListString($categoryInfo));
|
|
else
|
|
$query .= ' WHERE forum_id = ?';
|
|
}
|
|
if($hasTopicInfo)
|
|
$query .= sprintf(' %s topic_id = ?', ++$args > 1 ? 'AND' : 'WHERE');
|
|
if($hasUserInfo)
|
|
$query .= sprintf(' %s user_id = ?', ++$args > 1 ? 'AND' : 'WHERE');
|
|
if($hasUpToPostInfo)
|
|
$query .= sprintf(' %s post_id < ?', ++$args > 1 ? 'AND' : 'WHERE');
|
|
if($hasAfterPostInfo)
|
|
$query .= sprintf(' %s post_id > ?', ++$args > 1 ? 'AND' : 'WHERE');
|
|
if($hasNewerThanDays)
|
|
$query .= sprintf(' %s post_created > NOW() - INTERVAL ? DAY', ++$args > 1 ? 'AND' : 'WHERE');
|
|
if($hasSearchQuery)
|
|
$query .= sprintf(' %s MATCH(post_text) AGAINST (? IN NATURAL LANGUAGE MODE)', ++$args > 1 ? 'AND' : 'WHERE');
|
|
if($hasDeleted)
|
|
$query .= sprintf(' %s post_deleted %s NULL', ++$args > 1 ? 'AND' : 'WHERE', $deleted ? 'IS NOT' : 'IS');
|
|
if($doSearchOrder) {
|
|
$query .= ' ORDER BY post_id ASC LIMIT 20';
|
|
} else {
|
|
$query .= ' ORDER BY post_id ASC';
|
|
if($hasPagination)
|
|
$query .= ' LIMIT ? OFFSET ?';
|
|
}
|
|
|
|
$args = 0;
|
|
$stmt = $this->cache->get($query);
|
|
if($hasCategoryInfo) {
|
|
if(is_array($categoryInfo)) {
|
|
foreach($categoryInfo as $categoryInfoEntry)
|
|
$stmt->addParameter(++$args, $categoryInfoEntry instanceof ForumCategoryInfo ? $categoryInfoEntry->getId() : (string)$categoryInfoEntry);
|
|
} else
|
|
$stmt->addParameter(++$args, $categoryInfo);
|
|
}
|
|
if($hasTopicInfo)
|
|
$stmt->addParameter(++$args, $topicInfo);
|
|
if($hasUserInfo)
|
|
$stmt->addParameter(++$args, $userInfo);
|
|
if($hasUpToPostInfo)
|
|
$stmt->addParameter(++$args, $upToPostInfo);
|
|
if($hasAfterPostInfo)
|
|
$stmt->addParameter(++$args, $afterPostInfo);
|
|
if($hasNewerThanDays)
|
|
$stmt->addParameter(++$args, $newerThanDays);
|
|
if($hasSearchQuery)
|
|
$stmt->addParameter(++$args, $searchQuery);
|
|
if($hasPagination) {
|
|
$stmt->addParameter(++$args, $pagination->getRange());
|
|
$stmt->addParameter(++$args, $pagination->getOffset());
|
|
}
|
|
$stmt->execute();
|
|
|
|
return $stmt->getResult()->getIterator(ForumPostInfo::fromResult(...));
|
|
}
|
|
|
|
public function getPost(
|
|
?string $postId = null,
|
|
ForumTopicInfo|string|null $topicInfo = null,
|
|
ForumCategoryInfo|string|array|null $categoryInfos = null,
|
|
UserInfo|string|null $userInfo = null,
|
|
bool $getLast = false,
|
|
?bool $deleted = null
|
|
): ForumPostInfo {
|
|
$hasPostId = $postId !== null;
|
|
$hasTopicInfo = $topicInfo !== null;
|
|
$hasCategoryInfos = $categoryInfos !== null;
|
|
$hasUserInfo = $userInfo !== null;
|
|
$hasDeleted = $deleted !== null;
|
|
|
|
if(!$hasPostId && !$hasTopicInfo && !$hasCategoryInfos && !$hasUserInfo)
|
|
throw new InvalidArgumentException('At least one of the four first arguments must be specified.');
|
|
|
|
$values = [];
|
|
$query = 'SELECT post_id, topic_id, forum_id, user_id, INET6_NTOA(post_ip), post_text, post_parse, post_display_signature, UNIX_TIMESTAMP(post_created), UNIX_TIMESTAMP(post_edited), UNIX_TIMESTAMP(post_deleted) FROM msz_forum_posts';
|
|
if($hasPostId) {
|
|
$query .= ' WHERE post_id = ?';
|
|
$values[] = $postId;
|
|
} elseif($hasUserInfo) {
|
|
$query .= ' WHERE user_id = ?';
|
|
$values[] = $userInfo instanceof UserInfo ? $userInfo->getId() : $userInfo;
|
|
$query .= sprintf(' ORDER BY post_id %s', $getLast ? 'DESC' : 'ASC');
|
|
} elseif($hasTopicInfo) {
|
|
if($topicInfo instanceof ForumTopicInfo)
|
|
$topicInfo = $topicInfo->getId();
|
|
|
|
$query .= sprintf(' WHERE post_id = (SELECT %s(post_id) FROM msz_forum_posts WHERE topic_id = ?', $getLast ? 'MAX' : 'MIN');
|
|
if($hasDeleted)
|
|
$query .= sprintf(' AND post_deleted %s NULL', $deleted ? 'IS NOT' : 'IS');
|
|
$query .= ')';
|
|
|
|
$values[] = $topicInfo;
|
|
} elseif($hasCategoryInfos) {
|
|
if(!is_array($categoryInfos))
|
|
$categoryInfos = [$categoryInfos];
|
|
|
|
$query .= sprintf(
|
|
' WHERE post_id = (SELECT %s(post_id) FROM msz_forum_posts WHERE forum_id IN (%s)',
|
|
$getLast ? 'MAX' : 'MIN',
|
|
DbTools::prepareListString($categoryInfos)
|
|
);
|
|
if($hasDeleted)
|
|
$query .= sprintf(' AND post_deleted %s NULL', $deleted ? 'IS NOT' : 'IS');
|
|
$query .= ')';
|
|
|
|
foreach($categoryInfos as $categoryInfo) {
|
|
if($categoryInfo instanceof ForumCategoryInfo)
|
|
$values[] = $categoryInfo->getId();
|
|
elseif(is_string($categoryInfo) || is_int($categoryInfo))
|
|
$values[] = (string)$categoryInfo;
|
|
else
|
|
throw new InvalidArgumentException('$categoryInfos contains an invalid item.');
|
|
}
|
|
}
|
|
|
|
$args = 0;
|
|
$stmt = $this->cache->get($query);
|
|
foreach($values as $value)
|
|
$stmt->addParameter(++$args, $value);
|
|
$stmt->execute();
|
|
|
|
$result = $stmt->getResult();
|
|
if(!$result->next())
|
|
throw new RuntimeException('Forum post not found.');
|
|
|
|
return ForumPostInfo::fromResult($result);
|
|
}
|
|
|
|
public function createPost(
|
|
ForumTopicInfo|string $topicInfo,
|
|
UserInfo|string|null $userInfo,
|
|
IPAddress|string $remoteAddr,
|
|
string $body,
|
|
int $bodyParser,
|
|
bool $displaySignature,
|
|
ForumCategoryInfo|string|null $categoryInfo = null
|
|
): ForumPostInfo {
|
|
if($categoryInfo instanceof ForumCategoryInfo)
|
|
$categoryInfo = $categoryInfo->getId();
|
|
|
|
if($topicInfo instanceof ForumTopicInfo) {
|
|
$categoryInfo ??= $topicInfo->getCategoryId();
|
|
$topicInfo = $topicInfo->getId();
|
|
} elseif($categoryInfo === null)
|
|
throw new InvalidArgumentException('$categoryInfo may only be null if $topicInfo is an instance of ForumTopicInfo.');
|
|
|
|
if($userInfo instanceof UserInfo)
|
|
$userInfo = $userInfo->getId();
|
|
|
|
if($remoteAddr instanceof IPAddress)
|
|
$remoteAddr = (string)$remoteAddr;
|
|
|
|
$stmt = $this->cache->get('INSERT INTO msz_forum_posts (topic_id, forum_id, user_id, post_ip, post_text, post_parse, post_display_signature) VALUES (?, ?, ?, INET6_ATON(?), ?, ?, ?)');
|
|
$stmt->addParameter(1, $topicInfo);
|
|
$stmt->addParameter(2, $categoryInfo);
|
|
$stmt->addParameter(3, $userInfo);
|
|
$stmt->addParameter(4, $remoteAddr);
|
|
$stmt->addParameter(5, $body);
|
|
$stmt->addParameter(6, $bodyParser);
|
|
$stmt->addParameter(7, $displaySignature ? 1 : 0);
|
|
$stmt->execute();
|
|
|
|
return $this->getPost(postId: (string)$this->dbConn->getLastInsertId());
|
|
}
|
|
|
|
public function updatePost(
|
|
ForumPostInfo|string $postInfo,
|
|
IPAddress|string|null $remoteAddr = null,
|
|
?string $body = null,
|
|
?int $bodyParser = null,
|
|
?bool $displaySignature = null,
|
|
bool $bumpEdited = true
|
|
): void {
|
|
if($postInfo instanceof ForumPostInfo)
|
|
$postInfo = $postInfo->getId();
|
|
|
|
$fields = [];
|
|
$values = [];
|
|
|
|
if($remoteAddr !== null) {
|
|
if($remoteAddr instanceof IPAddress)
|
|
$remoteAddr = (string)$remoteAddr;
|
|
|
|
$fields[] = 'post_ip = INET6_ATON(?)';
|
|
$values[] = $remoteAddr;
|
|
}
|
|
|
|
if($body !== null) {
|
|
$fields[] = 'post_text = ?';
|
|
$values[] = $body;
|
|
}
|
|
|
|
if($bodyParser !== null) {
|
|
$fields[] = 'post_parse = ?';
|
|
$values[] = $bodyParser;
|
|
}
|
|
|
|
if($displaySignature !== null) {
|
|
$fields[] = 'post_display_signature = ?';
|
|
$values[] = $displaySignature ? 1 : 0;
|
|
}
|
|
|
|
if(empty($fields))
|
|
return;
|
|
|
|
if($bumpEdited)
|
|
$fields[] = 'post_edited = NOW()';
|
|
|
|
$args = 0;
|
|
$stmt = $this->cache->get(sprintf('UPDATE msz_forum_posts SET %s WHERE post_id = ?', implode(', ', $fields)));
|
|
foreach($values as $value)
|
|
$stmt->addParameter(++$args, $value);
|
|
$stmt->addParameter(++$args, $postInfo);
|
|
$stmt->execute();
|
|
}
|
|
|
|
public function deletePost(ForumPostInfo|string $postInfo): void {
|
|
if($postInfo instanceof ForumPostInfo)
|
|
$postInfo = $postInfo->getId();
|
|
|
|
$stmt = $this->cache->get('UPDATE msz_forum_posts SET post_deleted = COALESCE(post_deleted, NOW()) WHERE post_id = ?');
|
|
$stmt->addParameter(1, $postInfo);
|
|
$stmt->execute();
|
|
}
|
|
|
|
public function restorePost(ForumPostInfo|string $postInfo): void {
|
|
if($postInfo instanceof ForumPostInfo)
|
|
$postInfo = $postInfo->getId();
|
|
|
|
$stmt = $this->cache->get('UPDATE msz_forum_posts SET post_deleted = NULL WHERE post_id = ?');
|
|
$stmt->addParameter(1, $postInfo);
|
|
$stmt->execute();
|
|
}
|
|
|
|
public function nukePost(ForumPostInfo|string $postInfo): void {
|
|
if($postInfo instanceof ForumPostInfo)
|
|
$postInfo = $postInfo->getId();
|
|
|
|
$stmt = $this->cache->get('DELETE FROM msz_forum_posts WHERE post_id = ?');
|
|
$stmt->addParameter(1, $postInfo);
|
|
$stmt->execute();
|
|
}
|
|
|
|
public function getUserLastPostCreatedTime(UserInfo|string $userInfo): int {
|
|
if($userInfo instanceof UserInfo)
|
|
$userInfo = $userInfo->getId();
|
|
|
|
// intentionally including deleted posts
|
|
$stmt = $this->cache->get('SELECT UNIX_TIMESTAMP(MAX(post_created)) FROM msz_forum_posts WHERE user_id = ?');
|
|
$stmt->addParameter(1, $userInfo);
|
|
$stmt->execute();
|
|
|
|
$result = $stmt->getResult();
|
|
if(!$result->next())
|
|
return 0;
|
|
|
|
return $result->getInteger(0);
|
|
}
|
|
|
|
public function getUserLastPostCreatedAt(UserInfo|string $userInfo): DateTime {
|
|
return DateTime::fromUnixTimeSeconds($this->getUserLastPostCreatedTime($userInfo));
|
|
}
|
|
|
|
public function generatePostRankings(
|
|
int $year = 0,
|
|
int $month = 0,
|
|
array $exceptCategoryInfos = [],
|
|
array $exceptTopicInfos = []
|
|
): array {
|
|
$hasYear = $year > 0;
|
|
$hasMonth = $hasYear && $month > 0;
|
|
$hasExcludedCategoryInfos = !empty($exceptCategoryInfos);
|
|
$hasExcludedTopicInfos = !empty($exceptTopicInfos);
|
|
|
|
$query = 'SELECT user_id, COUNT(*) AS posts_count FROM msz_forum_posts WHERE post_deleted IS NULL';
|
|
if($hasYear)
|
|
$query .= sprintf(
|
|
' AND DATE(post_created) BETWEEN "%1$04d-%2$02d-01" AND "%1$04d-%3$02d-31"',
|
|
$year,
|
|
$hasMonth ? $month : 1,
|
|
$hasMonth ? $month : 12
|
|
);
|
|
if($hasExcludedCategoryInfos)
|
|
$query .= sprintf(' AND forum_id NOT IN (%s)', DbTools::prepareListString($exceptCategoryInfos));
|
|
if($hasExcludedTopicInfos)
|
|
$query .= sprintf(' AND topic_id NOT IN (%s)', DbTools::prepareListString($exceptTopicInfos));
|
|
$query .= ' GROUP BY user_id HAVING posts_count > 0 ORDER BY posts_count DESC';
|
|
|
|
$args = 0;
|
|
$stmt = $this->cache->get($query);
|
|
foreach($exceptCategoryInfos as $exceptCategoryInfo)
|
|
$stmt->addParameter(++$args, $exceptCategoryInfo instanceof ForumCategoryInfo ? $exceptCategoryInfo->getId() : $exceptCategoryInfo);
|
|
foreach($exceptTopicInfos as $exceptTopicInfo)
|
|
$stmt->addParameter(++$args, $exceptTopicInfo instanceof ForumTopicInfo ? $exceptTopicInfo->getId() : $exceptTopicInfo);
|
|
$stmt->execute();
|
|
|
|
$result = $stmt->getResult();
|
|
$rankings = [];
|
|
$rankNo = 0;
|
|
$lastPostsCount = PHP_INT_MAX;
|
|
|
|
while($result->next()) {
|
|
$rankings[] = $ranking = new stdClass;
|
|
$ranking->userId = $result->getString(0);
|
|
$ranking->postsCount = $result->getInteger(1);
|
|
|
|
if($lastPostsCount > $ranking->postsCount) {
|
|
++$rankNo;
|
|
$lastPostsCount = $ranking->postsCount;
|
|
}
|
|
|
|
$ranking->position = $rankNo;
|
|
}
|
|
|
|
return $rankings;
|
|
}
|
|
}
|