freepost codebase git repo
Clone
HTTPS:
git clone https://vervis.peers.community/repos/Avlxv
SSH:
git clone USERNAME@vervis.peers.community:Avlxv
Branches
Tags
master
::
database.php
<?php
require_once 'config.php';
class Database
{
/***** STATIC *****/
// How many items to display in homepage
const HOMEPAGE_RESULTS = 50;
protected static function get_random_string ($length = 10) {
$characters = '0123456789abcdefghijklmnopqrstuvwxyz';
$characters_length = strlen ($characters);
$hash_id = '';
for ($i = 0; $i < $length; $i++)
$hash_id .= $characters[mt_rand (0, $characters_length - 1)];
return $hash_id;
}
/***** INSTANCE *****/
// Reference to a database connection
protected $database;
function __construct ()
{
$this->database = NULL;
}
function connect ()
{
try {
$this->database = new PDO (
Config::$DATABASE['dsn'],
Config::$DATABASE['username'],
Config::$DATABASE['password']);
$this->database->setAttribute (
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
return true;
} catch(PDOException $e) {
return false;
}
}
function disconnect ()
{
$this->database = NULL;
}
/**
* Retrieve a user
*/
function get_user ($username)
{
$user = NULL;
if (is_null ($this->database))
return $user;
$query = $this->database->prepare (
'SELECT * ' .
'FROM `user` ' .
'WHERE `username` = ?');
$query->execute (array ($username));
$user = $query->fetch(PDO::FETCH_ASSOC);
return $user;
}
/**
* Check if user exists
*/
function user_exists ($username)
{
$user = self::get_user ($username);
return !is_null ($user) && !empty ($user);
}
/**
* Check user login credentials
*
* @return NULL if bad credentials, otherwise return the user
*/
function check_user_credentials ($username, $password)
{
try {
$this->database->beginTransaction();
$user = NULL;
if (is_null ($this->database))
return $user;
$query = $this->database->prepare (
'SELECT * ' .
'FROM `user`' .
'WHERE ' .
'`username` = ? AND ' .
'`password` = SHA2(CONCAT(?, `salt`), 512) AND ' .
'`isActive` = 1');
$query->execute (array ($username, $password));
$user = $query->fetch (PDO::FETCH_ASSOC);
/* This bit of code is only needed to maintain interoperability
* with the old version of freepost, such that users are not
* required to create a new account.
*
* Once all hash/salt have been updated for all users, this code can
* be safely removed.
*
* The old credentials are OK; update with a new
* hash/salt to update users with the new freepost!
*/
if (!is_null ($user) && !empty ($user) && array_key_exists ('salt', $user) && $user['salt'] == '')
{
// Create a salt for user password
$salt = self::get_random_string (16);
// Update hash/salt for the user
$query = $this->database->prepare (
'UPDATE `user`' .
'SET `password` = SHA2(?, 512), `salt` = ? ' .
'WHERE `id` = ?');
$query->execute (array ($password . $salt, $salt, $user['id']));
// Refetch the user again
$user = self::get_user ($username);
}
/////////////////////////////////////////////////////////////////////
$this->database->commit ();
return $user;
} catch(PDOException $ex) {
$this->database->rollBack();
return NULL;
}
}
/**
* Retrieve a $user from database using remember_me token
*/
function get_remember_me ($token)
{
$user = array();
if (is_null ($this->database))
return $user;
$query = $this->database->prepare(
'SELECT U.* ' .
'FROM `user` AS U ' .
'JOIN `remember_me` AS R ON R.`userId` = U.`id`' .
'WHERE R.`token` = ? AND R.`expires` > NOW()');
$query->execute (array (hash ('sha512', $token)));
$user = $query->fetch (PDO::FETCH_ASSOC);
return $user;
}
/**
* Set a new remember_me token to database
*
* @return secret token (cleartext)
*/
function set_remember_me ($user_id)
{
/* Set remember me token.
* The cleartext token is stored as a user cookie, while in our
* database we store hash(token).
*/
// Delete all previous remember_me tokens for $user
self::delete_remember_me ($user_id);
// Create a new secret token
$token = self::get_random_string (128);
$query = $this->database->prepare (
'INSERT INTO `remember_me` (`token`, `userId`, `expires`)' .
'VALUES (?, ?, NOW() + INTERVAL 30 DAY)');
$query->execute (array (hash('sha512', $token), $user_id));
return $token;
}
/**
* Delete $user "remember_me" token
*/
function delete_remember_me ($user_id)
{
// Delete all previous remember_me tokens for $user
$query = $this->database->prepare(
'DELETE FROM `remember_me`' .
'WHERE `userId` = ?');
$query->execute (array ($user_id));
}
/**
* Retrieve a post
*/
function get_post ($hash_id)
{
$post = array();
if (is_null($this->database))
return $post;
$query = $this->database->prepare(
'SELECT P.*, U.`username` ' .
'FROM `post` AS P ' .
'JOIN `user` AS U ON P.`userId` = U.`id` ' .
'WHERE P.`hashId` = ?');
$query->execute (array ($hash_id));
$post = $query->fetch(PDO::FETCH_ASSOC);
return $post;
}
/**
* Retrieve all comments for a specific post
*/
function get_post_comments ($post_id)
{
$comments = array ();
if (is_null ($this->database))
return $comments;
$query = $this->database->prepare (
'SELECT C.*, U.`username`' .
'FROM `comment` AS C ' .
'JOIN `user` AS U ON C.`userId` = U.`id`' .
'WHERE C.`postId` = ? ' .
'ORDER BY C.`vote` DESC, C.`created` ASC');
$query->execute (array ($post_id));
$comments = $query->fetchAll (PDO::FETCH_ASSOC);
// Group comments by parentId
$comments_group = array();
foreach ($comments as $comment)
if (is_null ($comment['parentId']))
{
$comments_group[0][] = $comment;
}
else
{
$comments_group[$comment['parentId']][] = $comment;
}
return $comments_group;
}
/**
* Retrieve a single comment
*/
function get_comment ($hash_id)
{
$comment = array ();
if (is_null ($this->database))
return $comment;
$query = $this->database->prepare (
'SELECT ' .
'C.*,' .
'P.`hashId` AS `postHashId`,' .
'P.`title` AS `postTitle`,' .
'U.`username`' .
'FROM `comment` AS C ' .
'JOIN `user` AS U ON C.`userId` = U.`id` ' .
'JOIN `post` AS P ON P.`id` = C.`postId` ' .
'WHERE C.`hashId` = ?');
$query->execute (array($hash_id));
$comment = $query->fetch (PDO::FETCH_ASSOC);
return $comment;
}
/**
* Return the number of unread messages
*/
function count_unread_messages ($user_id)
{
if (is_null ($this->database))
return 0;
$query = $this->database->prepare (
'SELECT COUNT(1)' .
'FROM `comment`' .
'WHERE `parentUserId` = :user_id AND `userId` != :user_id AND `read` = 0');
$query->execute (array ('user_id' => $user_id));
return $query->fetchColumn();
}
/**
* Get posts by rating (for homepage)
*/
function get_hot_posts ($page = 0)
{
$submissions = array();
$page = intval ($page);
if (is_null ($this->database))
return $submissions;
if ($page < 0)
$page = 0;
$query = $this->database->prepare (
'SELECT P.*, U.`username`' .
'FROM `post` AS P ' .
'JOIN `user` AS U ON P.`userId` = U.`id`' .
'ORDER BY P.`dateCreated` DESC, P.`vote` DESC, P.`commentsCount` DESC ' .
'LIMIT :limit OFFSET :offset');
$query->bindValue (':limit', Database::HOMEPAGE_RESULTS, PDO::PARAM_INT);
$query->bindValue (':offset', $page * Database::HOMEPAGE_RESULTS, PDO::PARAM_INT);
$query->execute ();
$submissions = $query->fetchAll (PDO::FETCH_ASSOC);
return $submissions;
}
/**
* Get posts by date (for homepage)
*/
function get_new_posts ($page = 0)
{
$submissions = array();
$page = intval ($page);
if (is_null ($this->database))
return $submissions;
if ($page < 0)
$page = 0;
$query = $this->database->prepare (
'SELECT P.*, U.`username`' .
'FROM `post` AS P ' .
'JOIN `user` AS U ON P.`userId` = U.`id`' .
'ORDER BY P.`created` DESC ' .
'LIMIT :limit OFFSET :offset');
$query->bindValue (':limit', Database::HOMEPAGE_RESULTS, PDO::PARAM_INT);
$query->bindValue (':offset', $page * Database::HOMEPAGE_RESULTS, PDO::PARAM_INT);
$query->execute ();
$submissions = $query->fetchAll (PDO::FETCH_ASSOC);
return $submissions;
}
/**
* Get user posts (used to show user activity)
*/
function get_user_posts ($user_id)
{
$posts = array();
if (is_null ($this->database))
return $posts;
$query = $this->database->prepare (
'SELECT * ' .
'FROM `post`' .
'WHERE `userId` = ? ' .
'ORDER BY `created` DESC ' .
'LIMIT 50');
$query->execute (array($user_id));
$posts = $query->fetchAll(PDO::FETCH_ASSOC);
return $posts;
}
/**
* Get user comments (used to show user activity)
*/
function get_user_comments ($user_id)
{
$comments = array();
if (is_null ($this->database))
return $comments;
$query = $this->database->prepare (
'SELECT ' .
'C.*,' .
'P.`title` AS `postTitle`,' .
'P.`hashId` AS `postHashId`' .
'FROM `comment` AS C ' .
'JOIN `post` AS P ON P.`id` = C.`postId`' .
'WHERE C.`userId` = ? ' .
'ORDER BY C.`created` DESC ' .
'LIMIT 50');
$query->execute (array($user_id));
$comments = $query->fetchAll(PDO::FETCH_ASSOC);
return $comments;
}
/**
* Get user replies (used to show user activity)
*/
function get_user_replies ($user_id)
{
$replies = array();
if (is_null ($this->database))
return $replies;
$query = $this->database->prepare (
'SELECT ' .
'C.*,' .
'P.`title` AS `postTitle`,' .
'P.`hashId` AS `postHashId`,' .
'U.`username` AS `username`' .
'FROM `comment` AS C ' .
'JOIN `post` AS P ON P.`id` = C.`postId`' .
'JOIN `user` AS U ON U.`id` = C.`userId`' .
'WHERE C.`parentUserId` = :user_id AND C.`userId` != :user_id ' .
'ORDER BY C.`created` DESC ' .
'LIMIT 50');
$query->execute (array ('user_id' => $user_id));
$replies = $query->fetchAll(PDO::FETCH_ASSOC);
return $replies;
}
/**
* Set user replies as read
*/
function set_replies_as_read ($user_id)
{
$query = $this->database->prepare (
'UPDATE `comment`' .
'SET `read` = 1 ' .
'WHERE `parentUserId` = ? AND `read` = 0');
$query->execute (array ($user_id));
}
/**
* Retrieve a list of votes for a range of posts.
*
* @param posts_id list of IDs (eg. "2,4,5").
* NOTE: Because arrays can't be used with PDO, $posts_id
* is a string that's concatenated to the SQL query. For
* this reason is the responsibility of the caller to
* check that $posts_id is a valid string of integers
* separated by commans (beware of SQL injection).
*/
function get_posts_votes ($posts_id, $user_id)
{
$votes = array();
if (is_null ($this->database) || is_null ($posts_id) || is_null ($user_id))
return $votes;
// Run a test anyway to make sure $posts_id is a valid string
$posts_id_array = explode (',', $posts_id);
foreach ($posts_id_array as $post_id)
if (!is_numeric ($post_id))
return $votes;
// Retrieve the votes
$query = $this->database->prepare (
'SELECT * ' .
'FROM `vote_post`' .
'WHERE `postId` IN(' . $posts_id . ') AND `userId` = ?');
$query->execute (array ($user_id));
$votes = $query->fetchAll(PDO::FETCH_ASSOC);
// Create an array of votes with `postId` as key
$sorted_votes = array();
foreach ($votes as $vote)
$sorted_votes[$vote['postId']] = $vote;
return $sorted_votes;
}
/**
* Retrieve a list of votes for a range of comments.
*
* @param comments_id list of IDs (eg. "2,4,5").
* NOTE: Because arrays can't be used with PDO, $comments_id
* is a string that's concatenated to the SQL query. For
* this reason is the responsibility of the caller to
* check that $comments_id is a valid string of integers
* separated by commans (beware of SQL injection).
*/
function get_comments_votes ($comments_id, $user_id)
{
$votes = array();
if (is_null ($this->database) || is_null ($comments_id) || is_null ($user_id))
return $votes;
// Run a test anyway to make sure $posts_id is a valid string
$comments_id_array = explode (',', $comments_id);
foreach ($comments_id_array as $comment_id)
if (!is_numeric ($comment_id))
return $votes;
// Retrieve the votes
$query = $this->database->prepare (
'SELECT * ' .
'FROM `vote_comment`' .
'WHERE `commentId` IN(' . $comments_id . ') AND `userId` = ?');
$query->execute (array ($user_id));
$votes = $query->fetchAll (PDO::FETCH_ASSOC);
// Create an array of votes with `commentId` as key
$sorted_votes = array();
foreach ($votes as $vote)
$sorted_votes[$vote['commentId']] = $vote;
return $sorted_votes;
}
/**
* Create new user account
*/
function new_user ($username, $password)
{
$user = NULL;
try {
// Create a hash_id for the new post
$new_hash_id = self::get_random_string();
// Create a salt for user password
$salt = self::get_random_string (16);
$this->database->beginTransaction();
// Create the new user
$query = $this->database->prepare(
'INSERT INTO `user` (`hashId`, `isActive`, `password`, `registered`, `salt`, `username`)' .
'VALUES (?, 1, SHA2(?, 512), NOW(), ?, ?)');
$query->execute(array($new_hash_id, $password . $salt, $salt, $username));
// Retrieve the new user
$user = self::get_user ($username);
$this->database->commit();
return $user;
} catch(PDOException $ex) {
$this->database->rollBack();
}
}
/**
* Submit a new post/link
*/
function new_post ($title, $link, $text, $user_id)
{
// Create a hash_id for the new post
$new_hash_id = self::get_random_string();
try {
$this->database->beginTransaction();
// Create the new post
$query = $this->database->prepare(
'INSERT INTO `post` (`hashId`, `created`, `dateCreated`, `title`, `link`, `text`, `vote`, `commentsCount`, `userId`)' .
'VALUES (?, NOW(), CURDATE(), ?, ?, ?, 0, 0, ?)');
$query->execute(array($new_hash_id, $title, $link, $text, $user_id));
// Retrieve the id of the new post
$post_id = $this->database->lastInsertId();
$this->database->commit();
} catch(PDOException $ex) {
$this->database->rollBack();
return NULL;
}
// Automatically upvote this post (start from +1)
self::upvote_post ($new_hash_id, $user_id);
return $new_hash_id;
}
/**
* Submit a new comment to a post
*/
function new_comment ($comment, $post_hash_id, $user_id)
{
$new_hash_id = NULL;
try {
$new_hash_id = self::get_random_string();
$this->database->beginTransaction();
// Retrieve the post
$query = $this->database->prepare(
'SELECT * ' .
'FROM `post`' .
'WHERE `hashId` = ?');
$query->execute(array($post_hash_id));
$post = $query->fetch(PDO::FETCH_ASSOC);
// Add new comment
$query = $this->database->prepare(
'INSERT INTO `comment` (`hashId`, `created`, `dateCreated`, `read`, `text`, `vote`, `parentId`, `parentUserId`, `postId`, `userId`)' .
'VALUES (?, NOW(), CURDATE(), 0, ?, 1, ?, ?, ?, ?)');
$query->execute(array($new_hash_id, $comment, NULL, $post['userId'], $post['id'], $user_id));
$comment_id = $this->database->lastInsertId();
// Add vote
$query = $this->database->prepare(
'INSERT INTO `vote_comment` (`vote`, `datetime`, `commentId`, `userId`)' .
'VALUES (1, NOW(), ?, ?)');
$query->execute(array($comment_id, $user_id));
// Increase comments count for post
$query = $this->database->prepare (
'UPDATE `post`' .
'SET `commentsCount` = `commentsCount` + 1 ' .
'WHERE `hashId` = ?');
$query->execute (array ($post_hash_id));
$this->database->commit();
return $new_hash_id;
} catch(PDOException $ex) {
$this->database->rollBack();
return NULL;
}
}
/**
* Submit a new reply to a comment
*/
function new_reply ($comment, $parent_comment_hash_id, $user_id)
{
try {
$this->database->beginTransaction();
// Retrieve the parent comment
$query = $this->database->prepare (
'SELECT * ' .
'FROM `comment`' .
'WHERE `hashId` = ?');
$query->execute (array ($parent_comment_hash_id));
$parent_comment = $query->fetch (PDO::FETCH_ASSOC);
// Retrieve the post
$query = $this->database->prepare (
'SELECT * ' .
'FROM `post`' .
'WHERE `id` = ?');
$query->execute (array ($parent_comment['postId']));
$post = $query->fetch(PDO::FETCH_ASSOC);
// Add the new comment
$comment_hash_id = self::get_random_string();
$query = $this->database->prepare (
'INSERT INTO `comment` (`hashId`, `created`, `dateCreated`, `read`, `text`, `vote`, `parentId`, `parentUserId`, `postId`, `userId`)' .
'VALUES (?, NOW(), CURDATE(), 0, ?, 1, ?, ?, ?, ?)');
$query->execute (array ($comment_hash_id, $comment, $parent_comment['id'], $parent_comment['userId'], $post['id'], $user_id));
$comment_id = $this->database->lastInsertId();
// Add first vote for user comment
$query = $this->database->prepare (
'INSERT INTO `vote_comment` (`vote`, `datetime`, `commentId`, `userId`)' .
'VALUES (1, NOW(), ?, ?)');
$query->execute (array ($comment_id, $user_id));
// Increase comments count for post
$query = $this->database->prepare (
'UPDATE `post`' .
'SET `commentsCount` = `commentsCount` + 1 ' .
'WHERE `hashId` = ?');
$query->execute (array ($post['hashId']));
$this->database->commit ();
// Return the hash_id of both post and comment
return array (
'post' => $post['hashId'],
'comment' => $comment_hash_id);
} catch(PDOException $ex) {
$this->database->rollBack();
return NULL;
}
}
/**
* Update a post text
*/
function edit_post ($text, $post_hash_id, $user_id)
{
$query = $this->database->prepare (
'UPDATE `post`' .
'SET `text` = ? ' .
'WHERE `hashId` = ? AND `userId` = ?');
$query->execute (array ($text, $post_hash_id, $user_id));
$affected_rows = $query->rowCount();
return $affected_rows;
}
/**
* Update a comment text
*/
function edit_comment ($text, $comment_hash_id, $user_id)
{
$query = $this->database->prepare (
'UPDATE `comment`' .
'SET `text` = ? ' .
'WHERE `hashId` = ? AND `userId` = ?');
$query->execute (array ($text, $comment_hash_id, $user_id));
$affected_rows = $query->rowCount();
return $affected_rows;
}
/**
* Update user information
*/
function edit_user ($about, $email, $user_id)
{
$query = $this->database->prepare (
'UPDATE `user`' .
'SET `about` = :about, `email` = :email ' .
'WHERE `id` = :user_id');
$query->bindValue (':about', $about, PDO::PARAM_STR);
$query->bindValue (':user_id', $user_id, PDO::PARAM_INT);
if (NULL == $email || '' == $email)
$query->bindValue (':email', NULL, PDO::PARAM_NULL);
else
$query->bindValue (':email', $email, PDO::PARAM_STR);
$query->execute ();
}
/**
* Tell if a user has voted a post
*/
function voted_post ($post_id, $user_id)
{
if (is_null($this->database))
return false;
$query = $this->database->prepare(
'SELECT * ' .
'FROM `vote_post`' .
'WHERE `postId` = ? and `userId` = ?');
$query->execute (array ($post_id, $user_id));
$vote = $query->fetch (PDO::FETCH_ASSOC);
if (is_null ($vote) || empty ($vote))
return false;
return $vote;
}
/**
* Tell if a user has voted a comment
*/
function voted_comment ($comment_id, $user_id)
{
if (is_null($this->database))
return false;
$query = $this->database->prepare(
'SELECT * ' .
'FROM `vote_comment`' .
'WHERE `commentId` = ? and `userId` = ?');
$query->execute (array ($comment_id, $user_id));
$vote = $query->fetch (PDO::FETCH_ASSOC);
if (is_null ($vote) || empty ($vote))
return false;
return $vote;
}
/**
* Upvote a post
*/
function upvote_post ($post_hash_id, $user_id)
{
try {
$this->database->beginTransaction();
// Get the post
$post = self::get_post ($post_hash_id);
// Already voted?
$vote = self::voted_post ($post['id'], $user_id);
if (false == $vote)
{
// Cast upvote
$query = $this->database->prepare(
'INSERT INTO `vote_post` (`vote`, `datetime`, `postId`, `userId`)' .
'VALUES (1, NOW(), ?, ?)');
$query->execute (array ($post['id'], $user_id));
// Add +1 to post
$query = $this->database->prepare (
'UPDATE `post`' .
'SET `vote` = `vote` + 1 ' .
'WHERE `id` = ?');
$query->execute (array ($post['id']));
} elseif ($vote['vote'] == 1) {
// Already upvoted before. Remove upvote.
$query = $this->database->prepare(
'DELETE FROM `vote_post`' .
'WHERE `postId` = ? AND `userId` = ?');
$query->execute (array ($post['id'], $user_id));
// Remove upvote from post
$query = $this->database->prepare (
'UPDATE `post`' .
'SET `vote` = `vote` - 1 ' .
'WHERE `id` = ?');
$query->execute (array ($post['id']));
} elseif ($vote['vote'] == -1) {
// Already downvoted before. Change to upvote.
$query = $this->database->prepare(
'UPDATE `vote_post`' .
'SET `vote` = 1 ' .
'WHERE `postId` = ? AND `userId` = ?');
$query->execute (array ($post['id'], $user_id));
/* Update post vote count
* +2 because of the previous downvote
*/
$query = $this->database->prepare (
'UPDATE `post`' .
'SET `vote` = `vote` + 2 ' .
'WHERE `id` = ?');
$query->execute (array ($post['id']));
}
$this->database->commit ();
} catch(PDOException $ex) {
$this->database->rollBack();
}
}
/**
* Downvote a post
*/
function downvote_post ($post_hash_id, $user_id)
{
try {
$this->database->beginTransaction();
// Get the post
$post = self::get_post ($post_hash_id);
// Already voted?
$vote = self::voted_post ($post['id'], $user_id);
if (false == $vote)
{
// Cast downvote
$query = $this->database->prepare(
'INSERT INTO `vote_post` (`vote`, `datetime`, `postId`, `userId`)' .
'VALUES (-1, NOW(), ?, ?)');
$query->execute (array ($post['id'], $user_id));
// Add -1 to post
$query = $this->database->prepare (
'UPDATE `post`' .
'SET `vote` = `vote` - 1 ' .
'WHERE `id` = ?');
$query->execute (array ($post['id']));
} elseif ($vote['vote'] == -1) {
// Already downvoted before. Remove downvote.
$query = $this->database->prepare(
'DELETE FROM `vote_post`' .
'WHERE `postId` = ? AND `userId` = ?');
$query->execute (array ($post['id'], $user_id));
// Remove downvote from post
$query = $this->database->prepare (
'UPDATE `post`' .
'SET `vote` = `vote` + 1 ' .
'WHERE `id` = ?');
$query->execute (array ($post['id']));
} elseif ($vote['vote'] == 1) {
// Already upvoted before. Change to downvote.
$query = $this->database->prepare(
'UPDATE `vote_post`' .
'SET `vote` = -1 ' .
'WHERE `postId` = ? AND `userId` = ?');
$query->execute (array ($post['id'], $user_id));
/* Update post vote count
* -2 because of the previous upvote
*/
$query = $this->database->prepare (
'UPDATE `post`' .
'SET `vote` = `vote` - 2 ' .
'WHERE `id` = ?');
$query->execute (array ($post['id']));
}
$this->database->commit ();
} catch(PDOException $ex) {
$this->database->rollBack();
}
}
/**
* Upvote a comment
*/
function upvote_comment ($comment_hash_id, $user_id)
{
try {
$this->database->beginTransaction();
// Get the comment
$comment = self::get_comment ($comment_hash_id);
// Already voted?
$vote = self::voted_comment ($comment['id'], $user_id);
if (false == $vote)
{
// Cast upvote
$query = $this->database->prepare(
'INSERT INTO `vote_comment` (`vote`, `datetime`, `commentId`, `userId`)' .
'VALUES (1, NOW(), ?, ?)');
$query->execute (array ($comment['id'], $user_id));
// Add +1 to comment
$query = $this->database->prepare (
'UPDATE `comment`' .
'SET `vote` = `vote` + 1 ' .
'WHERE `id` = ?');
$query->execute (array ($comment['id']));
} elseif ($vote['vote'] == 1) {
// Already upvoted before. Remove upvote.
$query = $this->database->prepare(
'DELETE FROM `vote_comment`' .
'WHERE `commentId` = ? AND `userId` = ?');
$query->execute (array ($comment['id'], $user_id));
// Remove upvote from comment
$query = $this->database->prepare (
'UPDATE `comment`' .
'SET `vote` = `vote` - 1 ' .
'WHERE `id` = ?');
$query->execute (array ($comment['id']));
} elseif ($vote['vote'] == -1) {
// Already downvoted before. Change to upvote.
$query = $this->database->prepare(
'UPDATE `vote_comment`' .
'SET `vote` = 1 ' .
'WHERE `commentId` = ? AND `userId` = ?');
$query->execute (array ($comment['id'], $user_id));
/* Update comment vote count
* +2 because of the previous downvote
*/
$query = $this->database->prepare (
'UPDATE `comment`' .
'SET `vote` = `vote` + 2 ' .
'WHERE `id` = ?');
$query->execute (array ($comment['id']));
}
$this->database->commit ();
} catch(PDOException $ex) {
$this->database->rollBack();
}
}
/**
* Downvote a comment
*/
function downvote_comment ($comment_hash_id, $user_id)
{
try {
$this->database->beginTransaction();
// Get the comment
$comment = self::get_comment ($comment_hash_id);
// Already voted?
$vote = self::voted_comment ($comment['id'], $user_id);
if (false == $vote)
{
// Cast downvote
$query = $this->database->prepare(
'INSERT INTO `vote_comment` (`vote`, `datetime`, `commentId`, `userId`)' .
'VALUES (-1, NOW(), ?, ?)');
$query->execute (array ($comment['id'], $user_id));
// Add -1 to comment
$query = $this->database->prepare (
'UPDATE `comment`' .
'SET `vote` = `vote` - 1 ' .
'WHERE `id` = ?');
$query->execute (array ($comment['id']));
} elseif ($vote['vote'] == -1) {
// Already downvoted before. Remove downvote.
$query = $this->database->prepare(
'DELETE FROM `vote_comment`' .
'WHERE `commentId` = ? AND `userId` = ?');
$query->execute (array ($comment['id'], $user_id));
// Remove downvote from comment
$query = $this->database->prepare (
'UPDATE `comment`' .
'SET `vote` = `vote` + 1 ' .
'WHERE `id` = ?');
$query->execute (array ($comment['id']));
} elseif ($vote['vote'] == 1) {
// Already upvoted before. Change to downvote.
$query = $this->database->prepare(
'UPDATE `vote_comment`' .
'SET `vote` = -1 ' .
'WHERE `commentId` = ? AND `userId` = ?');
$query->execute (array ($comment['id'], $user_id));
/* Update comment vote count
* -2 because of the previous upvote
*/
$query = $this->database->prepare (
'UPDATE `comment`' .
'SET `vote` = `vote` - 2 ' .
'WHERE `id` = ?');
$query->execute (array ($comment['id']));
}
$this->database->commit ();
} catch(PDOException $ex) {
$this->database->rollBack();
}
}
/**
* Reset a user password. This function adds a new passwordResetCode
* Lately, the new password is updated with password_reset_validate()
*/
function password_reset ($user_hash_id)
{
try {
$this->database->beginTransaction();
// Generate a new secret token (used for validation)
$token = self::get_random_string (32);
/* Add the new secret token to the database.
* Check that the last resetToken wasn't set less than 5 minutes
* ago. This is to prevent spam from other users who might have
* inserted the same email.
*
* 1435 = 24h x 60' - 5'
*/
$query = $this->database->prepare (
'UPDATE `user`' .
'SET `passwordResetToken` = ?, `passwordResetTokenExpire` = NOW() + INTERVAL 1 DAY ' .
'WHERE' .
'`hashId` = ? AND ' .
'(`passwordResetTokenExpire` IS NULL OR TIMESTAMPDIFF(MINUTE, NOW(), `passwordResetTokenExpire`) < 1435)');
$query->execute (array ($token, $user_hash_id));
$affected_rows = $query->rowCount ();
$this->database->commit ();
return $affected_rows ? $token : NULL;
} catch(PDOException $ex) {
$this->database->rollBack();
return NULL;
}
}
/**
* Check if a token (sent by email for password reset) is valid
*/
function password_reset_validate ($token, $new_password)
{
if (is_null ($this->database))
return false;
$query = $this->database->prepare (
'UPDATE `user`' .
'SET `password` = SHA2(CONCAT(?, `salt`), 512), `passwordResetToken` = NULL, `passwordResetTokenExpire` = NULL ' .
'WHERE `passwordResetToken` = ? AND `passwordResetTokenExpire` > NOW()');
$query->execute (array ($new_password, $token));
return true;
}
}