/**
* Saves a feed to the database
*
* @param int $users_id users_id
* @param array $url required keys => (url, title, body) optional keys => (id, draft)
* @param int $trusted passed on to sanitizeHtml()
* @return int insert id
*/
function saveFeed($users_id, array $url, $trusted = -1)
{
// -------------------------------------------------------------------
// Sanitize
// -------------------------------------------------------------------
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
if (!isset($url['url']) || !isset($url['title']) || !isset($url['body'])) {
throw new Exception('Invalid $url array');
}
if (!filter_var($url['url'], FILTER_VALIDATE_URL)) {
throw new Exception('Invalid url');
}
// Users id
$clean['users_id'] = $users_id;
// Canonicalize Url
$clean['url'] = suxFunct::canonicalizeUrl($url['url']);
// No HTML in title
$clean['title'] = strip_tags($url['title']);
// Sanitize HTML in body
$clean['body_html'] = suxFunct::sanitizeHtml($url['body'], $trusted);
// Convert and copy body to UTF-8 plaintext
$converter = new suxHtml2UTF8($clean['body_html']);
$clean['body_plaintext'] = $converter->getText();
// Id
if (isset($url['id'])) {
if (!filter_var($url['id'], FILTER_VALIDATE_INT) || $url['id'] < 1) {
throw new Exception('Invalid id');
} else {
$clean['id'] = $url['id'];
}
} else {
$query = "SELECT id FROM {$this->db_feeds} WHERE url = ? ";
$st = $this->db->prepare($query);
$st->execute(array($clean['url']));
$edit = $st->fetch(PDO::FETCH_ASSOC);
if ($edit) {
$clean['id'] = $edit['id'];
}
}
// Draft, boolean / tinyint
$clean['draft'] = false;
if (isset($url['draft']) && $url['draft']) {
$clean['draft'] = true;
}
// We now have the $clean[] array
// --------------------------------------------------------------------
// Go!
// --------------------------------------------------------------------
// http://bugs.php.net/bug.php?id=44597
// As of 5.2.6 you still can't use this function's $input_parameters to
// pass a boolean to PostgreSQL. To do that, you'll have to call
// bindParam() with explicit types for *each* parameter in the query.
// Annoying much? This sucks more than you can imagine.
if (isset($clean['id'])) {
// UPDATE
unset($clean['users_id']);
// Don't override the original suggestor
$query = suxDB::prepareUpdateQuery($this->db_feeds, $clean);
$st = $this->db->prepare($query);
if ($this->db_driver == 'pgsql') {
$st->bindParam(':id', $clean['id'], PDO::PARAM_INT);
$st->bindParam(':url', $clean['url'], PDO::PARAM_STR);
$st->bindParam(':title', $clean['title'], PDO::PARAM_STR);
if (isset($clean['body_html'])) {
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
}
if (isset($clean['body_plaintext'])) {
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
}
$st->bindParam(':draft', $clean['draft'], PDO::PARAM_BOOL);
$st->execute();
} else {
$st->execute($clean);
}
} else {
// INSERT
$query = suxDB::prepareInsertQuery($this->db_feeds, $clean);
$st = $this->db->prepare($query);
if ($this->db_driver == 'pgsql') {
$st->bindParam(':users_id', $clean['users_id'], PDO::PARAM_INT);
$st->bindParam(':url', $clean['url'], PDO::PARAM_STR);
$st->bindParam(':title', $clean['title'], PDO::PARAM_STR);
if (isset($clean['body_html'])) {
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
}
if (isset($clean['body_plaintext'])) {
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
}
$st->bindParam(':draft', $clean['draft'], PDO::PARAM_BOOL);
$st->execute();
} else {
$st->execute($clean);
}
if ($this->db_driver == 'pgsql') {
$clean['id'] = $this->db->lastInsertId("{$this->db_feeds}_id_seq");
} else {
$clean['id'] = $this->db->lastInsertId();
}
}
// Clear cache
$this->deleteCache($clean['url']);
return $clean['id'];
}
/**
* Saves a photo to the database
*
* @param int $users_id users_id
* @param array $album required keys => (image), conditional keys => (photoalbums_id, md5), optional keys => (description)
* @return int insert id
*/
function savePhoto($users_id, array $photo)
{
// -------------------------------------------------------------------
// Sanitize
// -------------------------------------------------------------------
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
// photo id
if (isset($photo['id'])) {
if (!filter_var($photo['id'], FILTER_VALIDATE_INT) || $photo['id'] < 1) {
throw new Exception('Invalid photo id');
} else {
$clean['id'] = $photo['id'];
}
} else {
if (!isset($photo['image']) || !isset($photo['photoalbums_id']) || !isset($photo['md5'])) {
throw new Exception('Invalid $photo array');
}
}
// Begin collecting $clean array
$clean['users_id'] = $users_id;
if (isset($photo['image'])) {
$clean['image'] = $photo['image'];
}
if (isset($photo['photoalbums_id'])) {
$clean['photoalbums_id'] = $photo['photoalbums_id'];
}
if (isset($photo['md5'])) {
$clean['md5'] = $photo['md5'];
}
// Set an empty string if empty
if (!isset($photo['description'])) {
$photo['description'] = '';
} else {
// Sanitize description
$converter = new suxHtml2UTF8($photo['description']);
$clean['description'] = $converter->getText();
}
// We now have the $clean[] array
// --------------------------------------------------------------------
// Go!
// --------------------------------------------------------------------
if (isset($clean['id'])) {
// UPDATE
unset($clean['users_id']);
// Don't override the original submitter
$query = suxDB::prepareUpdateQuery($this->db_photos, $clean);
$st = $this->db->prepare($query);
$st->execute($clean);
} else {
// INSERT
$query = suxDB::prepareInsertQuery($this->db_photos, $clean);
$st = $this->db->prepare($query);
$st->execute($clean);
if ($this->db_driver == 'pgsql') {
$clean['id'] = $this->db->lastInsertId("{$this->db_photos}_id_seq");
} else {
$clean['id'] = $this->db->lastInsertId();
}
}
return $clean['id'];
}
/**
* Saves a bookmark to the database
*
* @param int $users_id users_id
* @param array $url required keys => (url, title, body) optional keys => (id, published_on, draft)
* @param int $trusted passed on to sanitizeHtml()
* @return int insert id
*/
function save($users_id, array $url, $trusted = -1)
{
// -------------------------------------------------------------------
// Sanitize
// -------------------------------------------------------------------
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
if (!isset($url['url']) || !isset($url['title']) || !isset($url['body'])) {
throw new Exception('Invalid $url array');
}
if (!filter_var($url['url'], FILTER_VALIDATE_URL)) {
throw new Exception('Invalid url');
}
// Users id
$clean['users_id'] = $users_id;
// Canonicalize Url
$clean['url'] = suxFunct::canonicalizeUrl($url['url']);
// No HTML in title
$clean['title'] = strip_tags($url['title']);
// Sanitize HTML in body
$clean['body_html'] = suxFunct::sanitizeHtml($url['body'], $trusted);
// Convert and copy body to UTF-8 plaintext
$converter = new suxHtml2UTF8($clean['body_html']);
$clean['body_plaintext'] = $converter->getText();
// Id
if (isset($url['id'])) {
if (!filter_var($url['id'], FILTER_VALIDATE_INT) || $url['id'] < 1) {
throw new Exception('Invalid id');
} else {
$clean['id'] = $url['id'];
}
} else {
$query = "SELECT id FROM {$this->db_table} WHERE url = ? ";
$st = $this->db->prepare($query);
$st->execute(array($clean['url']));
$edit = $st->fetch(PDO::FETCH_ASSOC);
if ($edit) {
$clean['id'] = $edit['id'];
}
}
// Publish date
if (isset($url['published_on'])) {
// ISO 8601 date format
// regex must match '2008-06-18 16:53:29' or '2008-06-18T16:53:29-04:00'
$regex = '/^(\\d{4})-(0[0-9]|1[0,1,2])-([0,1,2][0-9]|3[0,1]).+(\\d{2}):(\\d{2}):(\\d{2})/';
if (!preg_match($regex, $url['published_on'])) {
throw new Exception('Invalid date');
}
$clean['published_on'] = $url['published_on'];
} else {
$clean['published_on'] = date('Y-m-d H:i:s');
}
// Draft, boolean / tinyint
$clean['draft'] = false;
if (isset($url['draft']) && $url['draft']) {
$clean['draft'] = true;
}
// We now have the $clean[] array
// --------------------------------------------------------------------
// Go!
// --------------------------------------------------------------------
// http://bugs.php.net/bug.php?id=44597
// As of 5.2.6 you still can't use this function's $input_parameters to
// pass a boolean to PostgreSQL. To do that, you'll have to call
// bindParam() with explicit types for *each* parameter in the query.
// Annoying much? This sucks more than you can imagine.
if (isset($clean['id'])) {
// UPDATE
unset($clean['users_id']);
// Don't override the original submitter
$query = suxDB::prepareUpdateQuery($this->db_table, $clean);
$st = $this->db->prepare($query);
if ($this->db_driver == 'pgsql') {
$st->bindParam(':id', $clean['id'], PDO::PARAM_INT);
$st->bindParam(':url', $clean['url'], PDO::PARAM_STR);
$st->bindParam(':title', $clean['title'], PDO::PARAM_STR);
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
$st->bindParam(':published_on', $clean['published_on'], PDO::PARAM_STR);
$st->bindParam(':draft', $clean['draft'], PDO::PARAM_BOOL);
$st->execute();
} else {
$st->execute($clean);
}
} else {
// INSERT
$query = suxDB::prepareInsertQuery($this->db_table, $clean);
$st = $this->db->prepare($query);
if ($this->db_driver == 'pgsql') {
$st->bindParam(':users_id', $clean['users_id'], PDO::PARAM_INT);
$st->bindParam(':url', $clean['url'], PDO::PARAM_STR);
$st->bindParam(':title', $clean['title'], PDO::PARAM_STR);
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
$st->bindParam(':published_on', $clean['published_on'], PDO::PARAM_STR);
$st->bindParam(':draft', $clean['draft'], PDO::PARAM_BOOL);
$st->execute();
} else {
$st->execute($clean);
}
if ($this->db_driver == 'pgsql') {
$clean['id'] = $this->db->lastInsertId("{$this->db_table}_id_seq");
} else {
$clean['id'] = $this->db->lastInsertId();
}
}
return $clean['id'];
}
/**
* Saves a message to the database
*
* @param int $users_id users_id
* @param array $msg required keys => (title, body, [forum|blog|wiki|slideshow]) optional keys => (published_on)
* @param int $trusted passed on to sanitizeHtml()
* @return int insert id
*/
function save($users_id, array $msg, $trusted = -1)
{
// -------------------------------------------------------------------
// Sanitize
// -------------------------------------------------------------------
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
if (!isset($msg['title']) || !isset($msg['body'])) {
throw new Exception('Invalid $msg array');
}
// Message id
if (isset($msg['id'])) {
if (!filter_var($msg['id'], FILTER_VALIDATE_INT) || $msg['id'] < 1) {
throw new Exception('Invalid message id');
} else {
$clean['id'] = $msg['id'];
}
}
// Users id
$clean['users_id'] = $users_id;
// Parent_id
$clean['parent_id'] = @filter_var($msg['parent_id'], FILTER_VALIDATE_INT);
if ($clean['parent_id'] === false) {
$clean['parent_id'] = 0;
}
// No HTML in title
$clean['title'] = strip_tags($msg['title']);
// Sanitize HTML in body
$clean['body_html'] = suxFunct::sanitizeHtml($msg['body'], $trusted);
// Convert and copy body to UTF-8 plaintext
$converter = new suxHtml2UTF8($clean['body_html']);
$clean['body_plaintext'] = $converter->getText();
// Image
if (isset($msg['image'])) {
$clean['image'] = filter_var($msg['image'], FILTER_SANITIZE_STRING);
}
// Publish date
if (isset($msg['published_on'])) {
// ISO 8601 date format
// regex must match '2008-06-18 16:53:29' or '2008-06-18T16:53:29-04:00'
$regex = '/^(\\d{4})-(0[0-9]|1[0,1,2])-([0,1,2][0-9]|3[0,1]).+(\\d{2}):(\\d{2}):(\\d{2})/';
if (!preg_match($regex, $msg['published_on'])) {
throw new Exception('Invalid date');
}
$clean['published_on'] = $msg['published_on'];
} else {
$clean['published_on'] = date('Y-m-d H:i:s');
}
// Draft, boolean / tinyint
$clean['draft'] = false;
if (isset($msg['draft']) && $msg['draft']) {
$clean['draft'] = true;
}
// Types of threaded messages
$clean['blog'] = false;
$clean['forum'] = false;
$clean['wiki'] = false;
$clean['slideshow'] = false;
if (isset($msg['blog']) && $msg['blog']) {
$clean['blog'] = true;
}
if (isset($msg['forum']) && $msg['forum']) {
$clean['forum'] = true;
}
if (isset($msg['wiki']) && $msg['wiki']) {
$clean['wiki'] = true;
}
if (isset($msg['slideshow']) && $msg['slideshow']) {
$clean['slideshow'] = true;
}
if (!$clean['forum'] && !$clean['blog'] && !$clean['wiki'] && !$clean['slideshow']) {
throw new Exception('No message type specified?');
}
// We now have the $clean[] array
// -------------------------------------------------------------------
// Go!
// -------------------------------------------------------------------
// Begin transaction
$tid = suxDB::requestTransaction();
$this->inTransaction = true;
if (isset($clean['id'])) {
// UPDATE
// Get $edit[] array in order to keep a history
$query = "SELECT title, image, body_html, body_plaintext FROM {$this->db_table} WHERE id = ? ";
$st = $this->db->prepare($query);
$st->execute(array($clean['id']));
$edit = $st->fetch(PDO::FETCH_ASSOC);
if (!$edit) {
throw new Exception('No message to edit?');
}
$edit['messages_id'] = $clean['id'];
$edit['users_id'] = $clean['users_id'];
$edit['edited_on'] = date('Y-m-d H:i:s');
$query = suxDB::prepareInsertQuery($this->db_table_hist, $edit);
$st = $this->db->prepare($query);
$st->execute($edit);
unset($clean['users_id']);
// Don't override the original publisher
// Update the message
$query = suxDB::prepareUpdateQuery($this->db_table, $clean);
} else {
// INSERT
/*
The first message in a thread has thread_pos = 0.
For a new message N, if there are no messages in the thread with the same
parent as N, N's thread_pos is one greater than its parent's thread_pos.
For a new message N, if there are messages in the thread with the same
parent as N, N's thread_pos is one greater than the biggest thread_pos
of all the messages with the same parent as N, recursively.
After new message N's thread_pos is determined, all messages in the same
thread with a thread_pos value greater than or equal to N's have their
thread_pos value incremented by 1 (to make room for N).
*/
if ($clean['parent_id']) {
// Get thread_id, level, and thread_pos from parent
$st = $this->db->prepare("SELECT thread_id, level, thread_pos FROM {$this->db_table} WHERE id = ? ");
$st->execute(array($clean['parent_id']));
$parent = $st->fetch(PDO::FETCH_ASSOC);
// a reply's level is one greater than its parent's
$clean['level'] = $parent['level'] + 1;
// what is the biggest thread_pos in this thread among messages with the same parent, recursively?
$clean['thread_pos'] = $this->biggestThreadPos($parent['thread_id'], $clean['parent_id']);
if ($clean['thread_pos']) {
// this thread_pos goes after the biggest existing one
$clean['thread_pos']++;
} else {
// this is the first reply, so put it right after the parent
$clean['thread_pos'] = $parent['thread_pos'] + 1;
}
// increment the thread_pos of all messages in the thread that come after this one
$st = $this->db->prepare("UPDATE {$this->db_table} SET thread_pos = thread_pos + 1 WHERE thread_id = ? AND thread_pos >= ? ");
$st->execute(array($parent['thread_id'], $clean['thread_pos']));
// the new message should be saved with the parent's thread_id
$clean['thread_id'] = $parent['thread_id'];
} else {
// The message is not a reply, so it's the start of a new thread
$clean['level'] = 0;
$clean['thread_pos'] = 0;
$clean['thread_id'] = $this->db->query("SELECT MAX(thread_id) + 1 FROM {$this->db_table} ")->fetchColumn(0);
}
// Sanity check
if (!$clean['thread_id']) {
$clean['thread_id'] = 1;
}
// Insert the message
$query = suxDB::prepareInsertQuery($this->db_table, $clean);
}
$st = $this->db->prepare($query);
// http://bugs.php.net/bug.php?id=44597
// As of 5.2.6 you still can't use this function's $input_parameters to
// pass a boolean to PostgreSQL. To do that, you'll have to call
// bindParam() with explicit types for *each* parameter in the query.
// Annoying much? This sucks more than you can imagine.
if ($this->db_driver == 'pgsql') {
if (isset($clean['id'])) {
$st->bindParam(':id', $clean['id'], PDO::PARAM_INT);
} else {
$st->bindParam(':thread_id', $clean['thread_id'], PDO::PARAM_INT);
$st->bindParam(':level', $clean['level'], PDO::PARAM_INT);
$st->bindParam(':thread_pos', $clean['thread_pos'], PDO::PARAM_INT);
}
if (isset($clean['users_id'])) {
$st->bindParam(':users_id', $clean['users_id'], PDO::PARAM_INT);
}
$st->bindParam(':title', $clean['title'], PDO::PARAM_STR);
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
$st->bindParam(':draft', $clean['draft'], PDO::PARAM_BOOL);
$st->bindParam(':parent_id', $clean['parent_id'], PDO::PARAM_INT);
if (isset($clean['image'])) {
$st->bindParam(':image', $clean['image'], PDO::PARAM_STR);
}
if (isset($clean['published_on'])) {
$st->bindParam(':published_on', $clean['published_on'], PDO::PARAM_STR);
}
$st->bindParam(':forum', $clean['forum'], PDO::PARAM_BOOL);
$st->bindParam(':blog', $clean['blog'], PDO::PARAM_BOOL);
$st->bindParam(':wiki', $clean['wiki'], PDO::PARAM_BOOL);
$st->bindParam(':slideshow', $clean['slideshow'], PDO::PARAM_BOOL);
$st->execute();
} else {
$st->execute($clean);
}
// MySQL InnoDB with transaction reports the last insert id as 0 after
// commit, the real ids are only reported before committing.
if (isset($clean['id'])) {
$insert_id = $clean['id'];
} elseif ($this->db_driver == 'pgsql') {
$insert_id = $this->db->lastInsertId("{$this->db_table}_id_seq");
} else {
$insert_id = $this->db->lastInsertId();
}
// Commit
suxDB::commitTransaction($tid);
$this->inTransaction = false;
return $insert_id;
}
/**
* Save a user's access level
*
* @param int $users_id
* @param string $module
* @param int $access
*/
function saveAccess($users_id, $module, $access)
{
if (mb_strlen($module) > $this->max_module_length) {
throw new Exception('Module name too long');
}
if (!filter_var($access, FILTER_VALIDATE_INT) || $access < 0 || $access > $this->max_access) {
throw new Exception('Invalid access integer');
}
if (!$users_id) {
return false;
}
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
$clean['users_id'] = $users_id;
$clean['module'] = $module;
$clean['accesslevel'] = $access;
$query = "SELECT id FROM {$this->db_table_access} WHERE users_id = ? AND module = ? ";
$st = $this->db->prepare($query);
$st->execute(array($clean['users_id'], $clean['module']));
$edit = $st->fetch(PDO::FETCH_ASSOC);
if ($edit['id']) {
// UPDATE
$clean['id'] = $edit['id'];
$query = suxDB::prepareUpdateQuery($this->db_table_access, $clean);
$st = $this->db->prepare($query);
$st->execute($clean);
} else {
// INSERT
$query = suxDB::prepareInsertQuery($this->db_table_access, $clean);
$st = $this->db->prepare($query);
$st->execute($clean);
}
}