/**
* Saves a tag to the database
*
* @param int $users_id users_id
* @param string $tag
* @return int insert id
*/
function save($users_id, $tag)
{
// -------------------------------------------------------------------
// Sanitize
// -------------------------------------------------------------------
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
$tag = strip_tags($tag);
$tag = trim($tag);
if (!$tag) {
throw new Exception('Invalid tag');
}
// Clean
$clean['users_id'] = $users_id;
$clean['tag'] = $tag;
// --------------------------------------------------------------------
// Go!
// --------------------------------------------------------------------
$query = "SELECT id FROM {$this->db_table} WHERE tag = ? ";
$st = $this->db->prepare($query);
$st->execute(array($clean['tag']));
$tag = $st->fetch(PDO::FETCH_ASSOC);
if ($tag) {
// UPDATE, do nothing...
$id = $tag['id'];
} else {
// INSERT
$query = suxDB::prepareInsertQuery($this->db_table, $clean);
$st = $this->db->prepare($query);
$st->execute($clean);
if ($this->db_driver == 'pgsql') {
$id = $this->db->lastInsertId("{$this->db_table}_id_seq");
} else {
$id = $this->db->lastInsertId();
}
}
return $id;
}
/**
* 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'];
}
/**
* @param int $vector_id vector id
* @param string $md5 a has of a vector id concatenated with a document
* @param array $scores
*/
private function cache($vector_id, $md5, $scores)
{
$clean = array('bayes_vectors_id' => $vector_id, 'md5' => $md5, 'expiration' => time() + 3600 * 12, 'scores' => serialize($scores));
static $st = null;
// Static as cache, to make categorize() faster
if (!$st) {
$q = suxDB::prepareInsertQuery($this->db_table_cache, $clean);
$st = $this->db->prepare($q);
}
try {
$st->execute($clean);
} catch (Exception $e) {
// SQLSTATE 23000: Constraint violation, we don't care, carry on
if ($st->errorCode() == 23000) {
return true;
} else {
throw $e;
}
// Hot potato
}
}
/**
* trust a url
* @param int $id user id
* @param string $id url
* @return bool
*/
private function trustUrl($id, $url)
{
if (!filter_var($id, FILTER_VALIDATE_INT) || $id < 1) {
return false;
}
$url = suxFunct::canonicalizeUrl($url);
$trusted = array('users_id' => $id, 'auth_url' => $url);
$query = suxDB::prepareCountQuery($this->db_table_trust, $trusted);
$st = $this->db->prepare($query);
$st->execute($trusted);
if (!$st->fetchColumn()) {
$query = suxDB::prepareInsertQuery($this->db_table_trust, $trusted);
$st = $this->db->prepare($query);
$st->execute($trusted);
}
}
/**
* 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'];
}
/**
* Write something to the users_log table
*
* @param string $body_html
* @param int $users_id
* @param int $private
*/
function write($users_id, $body_html, $private = false)
{
// Any user
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
$private = $private ? true : false;
$clean['users_id'] = $users_id;
$clean['private'] = $private;
$clean['body_html'] = suxFunct::sanitizeHtml($body_html, -1);
// Convert and copy body to UTF-8 plaintext
$converter = new suxHtml2UTF8($clean['body_html']);
$clean['body_plaintext'] = $converter->getText();
// Timestamp
$clean['ts'] = date('Y-m-d H:i:s');
// INSERT
$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') {
$st->bindParam(':users_id', $clean['users_id'], PDO::PARAM_INT);
$st->bindParam(':private', $clean['private'], PDO::PARAM_BOOL);
$st->bindParam(':body_html', $clean['body_html'], PDO::PARAM_STR);
$st->bindParam(':body_plaintext', $clean['body_plaintext'], PDO::PARAM_STR);
$st->bindParam(':ts', $clean['ts'], PDO::PARAM_STR);
$st->execute();
} else {
$st->execute($clean);
}
}
/**
* Save relationship
*
* @param int $uid users_id
* @param int $fid the users_id of the friend
* @param string $rel relationship based on XFN
* @return bool
*/
function saveRelationship($uid, $fid, $rel)
{
// --------------------------------------------------------------------
// Sanitize
// --------------------------------------------------------------------
if (!filter_var($uid, FILTER_VALIDATE_INT) || $uid < 1) {
throw new Exception('Invalid user id');
}
if (!filter_var($fid, FILTER_VALIDATE_INT) || $fid < 1) {
throw new Exception('Invalid friend id');
}
list($identity, $friendship, $physical, $professional, $geographical, $family, $romantic) = $this->relationshipArray($rel);
if ($identity) {
$rel = $identity;
} else {
$rel = "{$friendship} {$physical} {$professional} {$geographical} {$family} {$romantic}";
$rel = preg_replace('/\\s+/', ' ', $rel);
// Normalize whitespaces
}
$rel = trim($rel);
// --------------------------------------------------------------------
// Go!
// --------------------------------------------------------------------
$st = $this->db->prepare("SELECT COUNT(*) FROM {$this->db_table} WHERE users_id = ? AND friend_users_id = ? ");
$st->execute(array($uid, $fid));
$socialnetwork = array('users_id' => $uid, 'friend_users_id' => $fid, 'relationship' => $rel);
if ($st->fetchColumn() > 0) {
// UPDATE
$query = "UPDATE {$this->db_table} SET relationship = :relationship WHERE users_id = :users_id AND friend_users_id = :friend_users_id ";
$st = $this->db->prepare($query);
return $st->execute($socialnetwork);
} else {
// INSERT
$query = suxDB::prepareInsertQuery($this->db_table, $socialnetwork);
$st = $this->db->prepare($query);
return $st->execute($socialnetwork);
}
}
/**
* @param string $users_id users id
* @param string $vector_id vector id
* @param bool $trainer
* @param bool $owner
* @return bool
*/
function shareVector($users_id, $vector_id, $trainer, $owner)
{
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
return false;
}
if (!filter_var($vector_id, FILTER_VALIDATE_INT) || $vector_id < 1) {
return false;
}
if ($users_id == $_SESSION['users_id']) {
return false;
}
// Cannot share a vector with one's self
$trainer = $trainer ? true : false;
$owner = $owner ? true : false;
// --------------------------------------------------------------------
// Go!
// --------------------------------------------------------------------
$st = $this->db->prepare("SELECT COUNT(*) FROM {$this->db_table_auth} WHERE users_id = ? AND bayes_vectors_id = ? ");
$st->execute(array($users_id, $vector_id));
$shared = array('users_id' => $users_id, 'bayes_vectors_id' => $vector_id, 'trainer' => $trainer, 'owner' => $owner);
// 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 ($st->fetchColumn() > 0) {
// Don't allow un-ownership if there is only one owner, probably due to a race condition
$st = $this->db->prepare("SELECT COUNT(*) FROM {$this->db_table_auth} WHERE bayes_vectors_id = ? ");
$st->execute(array($vector_id));
if (!$owner && $st->fetchColumn() <= 1) {
return false;
}
// UPDATE
$query = "UPDATE {$this->db_table_auth}\n SET trainer = :trainer, owner = :owner\n WHERE users_id = :users_id AND bayes_vectors_id = :bayes_vectors_id ";
$st = $this->db->prepare($query);
if ($this->db_driver == 'pgsql') {
$st->bindParam(':trainer', $shared['trainer'], PDO::PARAM_BOOL);
$st->bindParam(':owner', $shared['owner'], PDO::PARAM_BOOL);
$st->bindParam(':users_id', $shared['users_id'], PDO::PARAM_INT);
$st->bindParam(':bayes_vectors_id', $shared['bayes_vectors_id'], PDO::PARAM_INT);
return $st->execute();
} else {
return $st->execute($shared);
}
} else {
// INSERT
$query = suxDB::prepareInsertQuery($this->db_table_auth, $shared);
$st = $this->db->prepare($query);
if ($this->db_driver == 'pgsql') {
$st->bindParam(':trainer', $shared['trainer'], PDO::PARAM_BOOL);
$st->bindParam(':owner', $shared['owner'], PDO::PARAM_BOOL);
$st->bindParam(':users_id', $shared['users_id'], PDO::PARAM_INT);
$st->bindParam(':bayes_vectors_id', $shared['bayes_vectors_id'], PDO::PARAM_INT);
return $st->execute();
} else {
return $st->execute($shared);
}
}
}
/**
* Set links
*
* @param string $link name of the link table
* @param string $table1 name of the first table
* @param int $id1 a primary key
* @param string $table2 name of the second table
* @param int|array $id2 either a primary key, or an array of primary keys
* @param bool if true, use the key of $id2 as the data
*/
function saveLink($link, $table1, $id1, $table2, $id2, $onkey = false)
{
// One to many mapping
// $id1 = One
// $id2 = Many
if (!is_array($id2)) {
$tmp = $id2;
unset($id2);
$id2[] = $tmp;
}
$tid = suxDB::requestTransaction();
$this->inTransaction = true;
foreach ($id2 as $key => $val) {
$form = array();
$form["{$table1}_id"] = $id1;
if ($onkey) {
$form["{$table2}_id"] = $key;
} else {
$form["{$table2}_id"] = $val;
}
if ($form["{$table2}_id"]) {
// Make sure this doesn't already exist
$query = suxDB::prepareCountQuery($link, $form);
$st = $this->db->prepare($query);
$st->execute($form);
if (!$st->fetchColumn()) {
// It's new, insert it
$query = suxDB::prepareInsertQuery($link, $form);
$st = $this->db->prepare($query);
$st->execute($form);
}
}
}
suxDB::commitTransaction($tid);
$this->inTransaction = false;
}
/**
* 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;
}
/**
* 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'];
}
/**
* Attach an openid to a user
*
* @param string $openid_url url
* @param int $users_id users_id
*/
function attachOpenID($openid_url, $users_id = null)
{
// This user
if (!$users_id) {
if (!empty($_SESSION['users_id'])) {
$users_id = $_SESSION['users_id'];
} else {
return false;
}
}
// Any user
if (!filter_var($users_id, FILTER_VALIDATE_INT) || $users_id < 1) {
throw new Exception('Invalid user id');
}
// Canonicalize url
$openid_url = suxFunct::canonicalizeUrl($openid_url);
// Sql
$oid = array('users_id' => $users_id, 'openid_url' => $openid_url);
$query = suxDB::prepareCountQuery($this->db_table_openid, $oid);
$st = $this->db->prepare($query);
$st->execute($oid);
if (!$st->fetchColumn()) {
// Insert
$query = suxDB::prepareInsertQuery($this->db_table_openid, $oid);
$st = $this->db->prepare($query);
$st->execute($oid);
}
}