Refs #3115. Moved oauth_lib to oauth_api.

git-svn-id: http://code.elgg.org/elgg/trunk@8715 36083f99-b078-4883-b0ff-0f9b5a30f544

1 files changed, 1879 insertions, 0 deletions

diff --git a/mod/oauth_api/vendors/oauth/library/store/OAuthStoreMySQL.php b/mod/oauth_api/vendors/oauth/library/store/OAuthStoreMySQL.php
new file mode 100644
index 000000000..a1b04c5c8
--- /dev/null
+++ b/mod/oauth_api/vendors/oauth/library/store/OAuthStoreMySQL.php
@@ -0,0 +1,1879 @@
+<?php
+
+/**
+ * Storage container for the oauth credentials, both server and consumer side.
+ * Based on MySQL
+ * 
+ * @version $Id: OAuthStoreMySQL.php 64 2009-08-16 19:37:00Z marcw@pobox.com $
+ * @author Marc Worrell <marcw@pobox.com>
+ * @date  Nov 16, 2007 4:03:30 PM
+ * 
+ * 
+ * The MIT License
+ * 
+ * Copyright (c) 2007-2008 Mediamatic Lab
+ * 
+ * Permission is hereby granted, free of charge, to any person obtaining a copy
+ * of this software and associated documentation files (the "Software"), to deal
+ * in the Software without restriction, including without limitation the rights
+ * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+ * copies of the Software, and to permit persons to whom the Software is
+ * furnished to do so, subject to the following conditions:
+ * 
+ * The above copyright notice and this permission notice shall be included in
+ * all copies or substantial portions of the Software.
+ * 
+ * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+ * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+ * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
+ * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+ * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+ * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
+ * THE SOFTWARE.
+ */
+
+
+require_once dirname(__FILE__) . '/OAuthStoreAbstract.class.php';
+
+
+class OAuthStoreMySQL extends OAuthStoreAbstract
+{
+	/**
+	 * The MySQL connection 
+	 */
+	protected $conn;
+
+	/**
+	 * Maximum delta a timestamp may be off from a previous timestamp.
+	 * Allows multiple consumers with some clock skew to work with the same token.
+	 * Unit is seconds, default max skew is 10 minutes.
+	 */
+	protected $max_timestamp_skew = 600;
+
+	/**
+	 * Default ttl for request tokens
+	 */
+	protected $max_request_token_ttl = 3600;
+	
+
+	/**
+	 * Construct the OAuthStoreMySQL.
+	 * In the options you have to supply either:
+	 * - server, username, password and database (for a mysql_connect)
+	 * - conn (for the connection to be used)
+	 * 
+	 * @param array options
+	 */
+	function __construct ( $options = array() )
+	{
+		if (isset($options['conn']))
+		{
+			$this->conn = $options['conn'];
+		}
+		else
+		{
+			if (isset($options['server']))
+			{
+				$server   = $options['server'];
+				$username = $options['username'];
+				
+				if (isset($options['password']))
+				{
+					$this->conn = mysql_connect($server, $username, $options['password']);
+				}
+				else
+				{
+					$this->conn = mysql_connect($server, $username);
+				}
+			}
+			else
+			{
+				// Try the default mysql connect
+				$this->conn = mysql_connect();
+			}
+
+			if ($this->conn === false)
+			{
+				throw new OAuthException('Could not connect to MySQL database: ' . mysql_error());
+			}
+
+			if (isset($options['database']))
+			{
+				if (!mysql_select_db($options['database'], $this->conn))
+				{
+					$this->sql_errcheck();
+				}
+			}
+			$this->query('set character set utf8');
+		}
+	}
+
+
+	/**
+	 * Find stored credentials for the consumer key and token. Used by an OAuth server
+	 * when verifying an OAuth request.
+	 * 
+	 * @param string consumer_key
+	 * @param string token
+	 * @param string token_type		false, 'request' or 'access'
+	 * @exception OAuthException when no secrets where found
+	 * @return array	assoc (consumer_secret, token_secret, osr_id, ost_id, user_id)
+	 */
+	public function getSecretsForVerify ( $consumer_key, $token, $token_type = 'access' )
+	{
+		if ($token_type === false)
+		{
+			$rs = $this->query_row_assoc('
+						SELECT	osr_id, 
+								osr_consumer_key		as consumer_key,
+								osr_consumer_secret		as consumer_secret
+						FROM oauth_server_registry
+						WHERE osr_consumer_key	= \'%s\'
+						  AND osr_enabled		= 1
+						', 
+						$consumer_key);
+			
+			if ($rs)
+			{
+				$rs['token'] 		= false;
+				$rs['token_secret']	= false;
+				$rs['user_id']		= false;
+				$rs['ost_id']		= false;
+			}
+		}
+		else
+		{
+			$rs = $this->query_row_assoc('
+						SELECT	osr_id, 
+								ost_id,
+								ost_usa_id_ref			as user_id,
+								osr_consumer_key		as consumer_key,
+								osr_consumer_secret		as consumer_secret,
+								ost_token				as token,
+								ost_token_secret		as token_secret
+						FROM oauth_server_registry
+								JOIN oauth_server_token
+								ON ost_osr_id_ref = osr_id
+						WHERE ost_token_type	= \'%s\'
+						  AND osr_consumer_key	= \'%s\'
+						  AND ost_token			= \'%s\'
+					 	  AND osr_enabled		= 1
+						  AND ost_token_ttl     >= NOW()
+						',
+						$token_type, $consumer_key, $token);
+		}
+		
+		if (empty($rs))
+		{
+			throw new OAuthException('The consumer_key "'.$consumer_key.'" token "'.$token.'" combination does not exist or is not enabled.');
+		}
+		return $rs;
+	}
+
+
+	/**
+	 * Find the server details for signing a request, always looks for an access token.
+	 * The returned credentials depend on which local user is making the request.
+	 * 
+	 * The consumer_key must belong to the user or be public (user id is null)
+	 * 
+	 * For signing we need all of the following:
+	 * 
+	 * consumer_key			consumer key associated with the server
+	 * consumer_secret		consumer secret associated with this server
+	 * token				access token associated with this server
+	 * token_secret			secret for the access token
+	 * signature_methods	signing methods supported by the server (array)
+	 * 
+	 * @todo filter on token type (we should know how and with what to sign this request, and there might be old access tokens)
+	 * @param string uri	uri of the server
+	 * @param int user_id	id of the logged on user
+	 * @param string name	(optional) name of the token (case sensitive)
+	 * @exception OAuthException when no credentials found
+	 * @return array
+	 */
+	public function getSecretsForSignature ( $uri, $user_id, $name = '' )
+	{
+		// Find a consumer key and token for the given uri
+		$ps		= parse_url($uri);
+		$host	= isset($ps['host']) ? $ps['host'] : 'localhost';
+		$path	= isset($ps['path']) ? $ps['path'] : '';
+		
+		if (empty($path) || substr($path, -1) != '/')
+		{
+			$path .= '/';
+		}
+
+		// The owner of the consumer_key is either the user or nobody (public consumer key)
+		$secrets = $this->query_row_assoc('
+					SELECT	ocr_consumer_key		as consumer_key,
+							ocr_consumer_secret		as consumer_secret,
+							oct_token				as token,
+							oct_token_secret		as token_secret,
+							ocr_signature_methods	as signature_methods
+					FROM oauth_consumer_registry
+						JOIN oauth_consumer_token ON oct_ocr_id_ref = ocr_id
+					WHERE ocr_server_uri_host = \'%s\'
+					  AND ocr_server_uri_path = LEFT(\'%s\', LENGTH(ocr_server_uri_path))
+					  AND (ocr_usa_id_ref = %s OR ocr_usa_id_ref IS NULL)
+					  AND oct_usa_id_ref	  = %d
+					  AND oct_token_type      = \'access\'
+					  AND oct_name			  = \'%s\'
+					  AND oct_token_ttl       >= NOW()
+					ORDER BY ocr_usa_id_ref DESC, ocr_consumer_secret DESC, LENGTH(ocr_server_uri_path) DESC
+					LIMIT 0,1
+					', $host, $path, $user_id, $user_id, $name
+					);
+		
+		if (empty($secrets))
+		{
+			throw new OAuthException('No server tokens available for '.$uri);
+		}
+		$secrets['signature_methods'] = explode(',', $secrets['signature_methods']);
+		return $secrets;
+	}
+
+
+	/**
+	 * Get the token and token secret we obtained from a server.
+	 * 
+	 * @param string	consumer_key
+	 * @param string 	token
+	 * @param string	token_type
+	 * @param int		user_id			the user owning the token
+	 * @param string	name			optional name for a named token
+	 * @exception OAuthException when no credentials found
+	 * @return array
+	 */
+	public function getServerTokenSecrets ( $consumer_key, $token, $token_type, $user_id, $name = '' )
+	{
+		if ($token_type != 'request' && $token_type != 'access')
+		{
+			throw new OAuthException('Unkown token type "'.$token_type.'", must be either "request" or "access"');
+		}
+
+		// Take the most recent token of the given type
+		$r = $this->query_row_assoc('
+					SELECT	ocr_consumer_key		as consumer_key,
+							ocr_consumer_secret		as consumer_secret,
+							oct_token				as token,
+							oct_token_secret		as token_secret,
+							oct_name				as token_name,
+							ocr_signature_methods	as signature_methods,
+							ocr_server_uri			as server_uri,
+							ocr_request_token_uri	as request_token_uri,
+							ocr_authorize_uri		as authorize_uri,
+							ocr_access_token_uri	as access_token_uri,
+							IF(oct_token_ttl >= \'9999-12-31\', NULL, UNIX_TIMESTAMP(oct_token_ttl) - UNIX_TIMESTAMP(NOW())) as token_ttl
+					FROM oauth_consumer_registry
+							JOIN oauth_consumer_token
+							ON oct_ocr_id_ref = ocr_id
+					WHERE ocr_consumer_key = \'%s\'
+					  AND oct_token_type   = \'%s\'
+					  AND oct_token        = \'%s\'
+					  AND oct_usa_id_ref   = %d
+					  AND oct_token_ttl    >= NOW()
+					', $consumer_key, $token_type, $token, $user_id
+					);
+					
+		if (empty($r))
+		{
+			throw new OAuthException('Could not find a "'.$token_type.'" token for consumer "'.$consumer_key.'" and user '.$user_id);
+		}
+		if (isset($r['signature_methods']) && !empty($r['signature_methods']))
+		{
+			$r['signature_methods'] = explode(',',$r['signature_methods']);
+		}
+		else
+		{
+			$r['signature_methods'] = array();
+		}
+		return $r;		
+	}
+
+
+	/**
+	 * Add a request token we obtained from a server.
+	 * 
+	 * @todo remove old tokens for this user and this ocr_id
+	 * @param string consumer_key	key of the server in the consumer registry
+	 * @param string token_type		one of 'request' or 'access'
+	 * @param string token
+	 * @param string token_secret
+	 * @param int 	 user_id			the user owning the token
+	 * @param array  options			extra options, name and token_ttl
+	 * @exception OAuthException when server is not known
+	 * @exception OAuthException when we received a duplicate token
+	 */
+	public function addServerToken ( $consumer_key, $token_type, $token, $token_secret, $user_id, $options = array() )
+	{
+		if ($token_type != 'request' && $token_type != 'access')
+		{
+			throw new OAuthException('Unknown token type "'.$token_type.'", must be either "request" or "access"');
+		}
+
+		// Maximum time to live for this token
+		if (isset($options['token_ttl']) && is_numeric($options['token_ttl']))
+		{
+			$ttl = 'DATE_ADD(NOW(), INTERVAL '.intval($options['token_ttl']).' SECOND)';
+		}
+		else if ($token == 'request')
+		{
+			$ttl = 'DATE_ADD(NOW(), INTERVAL '.$this->max_request_token_ttl.' SECOND)';
+		}
+		else
+		{
+			$ttl = "'9999-12-31'";
+		}
+		
+		$ocr_id = $this->query_one('
+					SELECT ocr_id
+					FROM oauth_consumer_registry
+					WHERE ocr_consumer_key = \'%s\'
+					', $consumer_key);
+					
+		if (empty($ocr_id))
+		{
+			throw new OAuthException('No server associated with consumer_key "'.$consumer_key.'"');
+		}
+		
+		// Named tokens, unique per user/consumer key
+		if (isset($options['name']) && $options['name'] != '')
+		{
+			$name = $options['name'];
+		}
+		else
+		{
+			$name = '';
+		}
+
+		// Delete any old tokens with the same type and name for this user/server combination
+		$this->query('
+					DELETE FROM oauth_consumer_token
+					WHERE oct_ocr_id_ref = %d
+					  AND oct_usa_id_ref = %d
+					  AND oct_token_type = LOWER(\'%s\')
+					  AND oct_name       = \'%s\'
+					',
+					$ocr_id,
+					$user_id,
+					$token_type,
+					$name);
+
+		// Insert the new token
+		$this->query('
+					INSERT IGNORE INTO oauth_consumer_token
+					SET oct_ocr_id_ref	= %d,
+						oct_usa_id_ref  = %d,
+						oct_name		= \'%s\',
+						oct_token		= \'%s\',
+						oct_token_secret= \'%s\',
+						oct_token_type	= LOWER(\'%s\'),
+						oct_timestamp	= NOW(),
+						oct_token_ttl	= '.$ttl.'
+					',
+					$ocr_id,
+					$user_id,
+					$name,
+					$token,
+					$token_secret,
+					$token_type);
+		
+		if (!$this->query_affected_rows())
+		{
+			throw new OAuthException('Received duplicate token "'.$token.'" for the same consumer_key "'.$consumer_key.'"');
+		}
+	}
+
+
+	/**
+	 * Delete a server key.  This removes access to that site.
+	 * 
+	 * @param string consumer_key
+	 * @param int user_id	user registering this server
+	 * @param boolean user_is_admin
+	 */
+	public function deleteServer ( $consumer_key, $user_id, $user_is_admin = false )
+	{
+		if ($user_is_admin)
+		{
+			$this->query('
+					DELETE FROM oauth_consumer_registry
+					WHERE ocr_consumer_key = \'%s\'
+					  AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
+					', $consumer_key, $user_id);
+		}
+		else
+		{
+			$this->query('
+					DELETE FROM oauth_consumer_registry
+					WHERE ocr_consumer_key = \'%s\'
+					  AND ocr_usa_id_ref   = %d
+					', $consumer_key, $user_id);
+		}
+	}
+	
+	
+	/**
+	 * Get a server from the consumer registry using the consumer key
+	 * 
+	 * @param string consumer_key
+	 * @param int user_id
+	 * @param boolean user_is_admin (optional)
+	 * @exception OAuthException when server is not found
+	 * @return array
+	 */	
+	public function getServer ( $consumer_key, $user_id, $user_is_admin = false )
+	{
+		$r = $this->query_row_assoc('
+				SELECT	ocr_id					as id,
+						ocr_usa_id_ref			as user_id,
+						ocr_consumer_key 		as consumer_key,
+						ocr_consumer_secret 	as consumer_secret,
+						ocr_signature_methods	as signature_methods,
+						ocr_server_uri			as server_uri,
+						ocr_request_token_uri	as request_token_uri,
+						ocr_authorize_uri		as authorize_uri,
+						ocr_access_token_uri	as access_token_uri
+				FROM oauth_consumer_registry
+				WHERE ocr_consumer_key = \'%s\'
+				  AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
+				',	$consumer_key, $user_id);
+		
+		if (empty($r))
+		{
+			throw new OAuthException('No server with consumer_key "'.$consumer_key.'" has been registered (for this user)');
+		}
+			
+		if (isset($r['signature_methods']) && !empty($r['signature_methods']))
+		{
+			$r['signature_methods'] = explode(',',$r['signature_methods']);
+		}
+		else
+		{
+			$r['signature_methods'] = array();
+		}
+		return $r;
+	}
+
+
+
+	/**
+	 * Find the server details that might be used for a request
+	 * 
+	 * The consumer_key must belong to the user or be public (user id is null)
+	 * 
+	 * @param string uri	uri of the server
+	 * @param int user_id	id of the logged on user
+	 * @exception OAuthException when no credentials found
+	 * @return array
+	 */
+	public function getServerForUri ( $uri, $user_id )
+	{
+		// Find a consumer key and token for the given uri
+		$ps		= parse_url($uri);
+		$host	= isset($ps['host']) ? $ps['host'] : 'localhost';
+		$path	= isset($ps['path']) ? $ps['path'] : '';
+		
+		if (empty($path) || substr($path, -1) != '/')
+		{
+			$path .= '/';
+		}
+
+		// The owner of the consumer_key is either the user or nobody (public consumer key)
+		$server = $this->query_row_assoc('
+					SELECT	ocr_id					as id,
+							ocr_usa_id_ref			as user_id,
+							ocr_consumer_key		as consumer_key,
+							ocr_consumer_secret		as consumer_secret,
+							ocr_signature_methods	as signature_methods,
+							ocr_server_uri			as server_uri,
+							ocr_request_token_uri	as request_token_uri,
+							ocr_authorize_uri		as authorize_uri,
+							ocr_access_token_uri	as access_token_uri
+					FROM oauth_consumer_registry
+					WHERE ocr_server_uri_host = \'%s\'
+					  AND ocr_server_uri_path = LEFT(\'%s\', LENGTH(ocr_server_uri_path))
+					  AND (ocr_usa_id_ref = %s OR ocr_usa_id_ref IS NULL)
+					ORDER BY ocr_usa_id_ref DESC, consumer_secret DESC, LENGTH(ocr_server_uri_path) DESC
+					LIMIT 0,1
+					', $host, $path, $user_id
+					);
+		
+		if (empty($server))
+		{
+			throw new OAuthException('No server available for '.$uri);
+		}
+		$server['signature_methods'] = explode(',', $server['signature_methods']);
+		return $server;
+	}
+
+
+	/**
+	 * Get a list of all server token this user has access to.
+	 * 
+	 * @param int usr_id
+	 * @return array
+	 */
+	public function listServerTokens ( $user_id )
+	{
+		$ts = $this->query_all_assoc('
+					SELECT	ocr_consumer_key		as consumer_key,
+							ocr_consumer_secret		as consumer_secret,
+							oct_id					as token_id,
+							oct_token				as token,
+							oct_token_secret		as token_secret,
+							oct_usa_id_ref			as user_id,
+							ocr_signature_methods	as signature_methods,
+							ocr_server_uri			as server_uri,
+							ocr_server_uri_host		as server_uri_host,
+							ocr_server_uri_path		as server_uri_path,
+							ocr_request_token_uri	as request_token_uri,
+							ocr_authorize_uri		as authorize_uri,
+							ocr_access_token_uri	as access_token_uri,
+							oct_timestamp			as timestamp
+					FROM oauth_consumer_registry
+							JOIN oauth_consumer_token
+							ON oct_ocr_id_ref = ocr_id
+					WHERE oct_usa_id_ref = %d
+					  AND oct_token_type = \'access\'
+					  AND oct_token_ttl  >= NOW()
+					ORDER BY ocr_server_uri_host, ocr_server_uri_path
+					', $user_id);
+		return $ts;
+	}
+
+
+	/**
+	 * Count how many tokens we have for the given server
+	 * 
+	 * @param string consumer_key
+	 * @return int
+	 */
+	public function countServerTokens ( $consumer_key )
+	{
+		$count = $this->query_one('
+					SELECT COUNT(oct_id)
+					FROM oauth_consumer_token
+							JOIN oauth_consumer_registry
+							ON oct_ocr_id_ref = ocr_id
+					WHERE oct_token_type   = \'access\'
+					  AND ocr_consumer_key = \'%s\'
+					  AND oct_token_ttl    >= NOW()
+					', $consumer_key);
+		
+		return $count;
+	}
+
+
+	/**
+	 * Get a specific server token for the given user
+	 * 
+	 * @param string consumer_key
+	 * @param string token
+	 * @param int user_id
+	 * @exception OAuthException when no such token found
+	 * @return array
+	 */
+	public function getServerToken ( $consumer_key, $token, $user_id )
+	{
+		$ts = $this->query_row_assoc('
+					SELECT	ocr_consumer_key		as consumer_key,
+							ocr_consumer_secret		as consumer_secret,
+							oct_token				as token,
+							oct_token_secret		as token_secret,
+							oct_usa_id_ref			as usr_id,
+							ocr_signature_methods	as signature_methods,
+							ocr_server_uri			as server_uri,
+							ocr_server_uri_host		as server_uri_host,
+							ocr_server_uri_path		as server_uri_path,
+							ocr_request_token_uri	as request_token_uri,
+							ocr_authorize_uri		as authorize_uri,
+							ocr_access_token_uri	as access_token_uri,
+							oct_timestamp			as timestamp
+					FROM oauth_consumer_registry
+							JOIN oauth_consumer_token
+							ON oct_ocr_id_ref = ocr_id
+					WHERE ocr_consumer_key = \'%s\'
+					  AND oct_usa_id_ref   = %d
+					  AND oct_token_type   = \'access\'
+					  AND oct_token        = \'%s\'
+					  AND oct_token_ttl    >= NOW()
+					', $consumer_key, $user_id, $token);
+		
+		if (empty($ts))
+		{
+			throw new OAuthException('No such consumer key ('.$consumer_key.') and token ('.$token.') combination for user "'.$user_id.'"');
+		}
+		return $ts;
+	}
+
+
+	/**
+	 * Delete a token we obtained from a server.
+	 * 
+	 * @param string consumer_key
+	 * @param string token
+	 * @param int user_id
+	 * @param boolean user_is_admin
+	 */
+	public function deleteServerToken ( $consumer_key, $token, $user_id, $user_is_admin = false )
+	{
+		if ($user_is_admin)
+		{
+			$this->query('
+				DELETE oauth_consumer_token 
+				FROM oauth_consumer_token
+						JOIN oauth_consumer_registry
+						ON oct_ocr_id_ref = ocr_id
+				WHERE ocr_consumer_key	= \'%s\'
+				  AND oct_token			= \'%s\'
+				', $consumer_key, $token);
+		}
+		else
+		{
+			$this->query('
+				DELETE oauth_consumer_token 
+				FROM oauth_consumer_token
+						JOIN oauth_consumer_registry
+						ON oct_ocr_id_ref = ocr_id
+				WHERE ocr_consumer_key	= \'%s\'
+				  AND oct_token			= \'%s\'
+				  AND oct_usa_id_ref	= %d
+				', $consumer_key, $token, $user_id);
+		}
+	}
+
+
+	/**
+	 * Set the ttl of a server access token.  This is done when the
+	 * server receives a valid request with a xoauth_token_ttl parameter in it.
+	 * 
+	 * @param string consumer_key
+	 * @param string token
+	 * @param int token_ttl
+	 */
+	public function setServerTokenTtl ( $consumer_key, $token, $token_ttl )
+	{
+		if ($token_ttl <= 0)
+		{
+			// Immediate delete when the token is past its ttl
+			$this->deleteServerToken($consumer_key, $token, 0, true);
+		}
+		else
+		{
+			// Set maximum time to live for this token
+			$this->query('
+						UPDATE oauth_consumer_token, oauth_consumer_registry
+						SET ost_token_ttl = DATE_ADD(NOW(), INTERVAL %d SECOND)
+						WHERE ocr_consumer_key	= \'%s\'
+						  AND oct_ocr_id_ref    = ocr_id
+						  AND oct_token 	    = \'%s\'
+						', $token_ttl, $consumer_key, $token);
+		}
+	}
+
+
+	/**
+	 * Get a list of all consumers from the consumer registry.
+	 * The consumer keys belong to the user or are public (user id is null)
+	 * 
+	 * @param string q	query term
+	 * @param int user_id
+	 * @return array
+	 */	
+	public function listServers ( $q = '', $user_id )
+	{
+		$q    = trim(str_replace('%', '', $q));
+		$args = array();
+
+		if (!empty($q))
+		{
+			$where = ' WHERE (	ocr_consumer_key like \'%%%s%%\'
+						  	 OR ocr_server_uri like \'%%%s%%\'
+						  	 OR ocr_server_uri_host like \'%%%s%%\'
+						  	 OR ocr_server_uri_path like \'%%%s%%\')
+						 AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
+					';
+			
+			$args[] = $q;
+			$args[] = $q;
+			$args[] = $q;
+			$args[] = $q;
+			$args[] = $user_id;
+		}
+		else
+		{
+			$where  = ' WHERE ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL';
+			$args[] = $user_id;
+		}
+
+		$servers = $this->query_all_assoc('
+					SELECT	ocr_id					as id,
+							ocr_usa_id_ref			as user_id,
+							ocr_consumer_key 		as consumer_key,
+							ocr_consumer_secret 	as consumer_secret,
+							ocr_signature_methods	as signature_methods,
+							ocr_server_uri			as server_uri,
+							ocr_server_uri_host		as server_uri_host,
+							ocr_server_uri_path		as server_uri_path,
+							ocr_request_token_uri	as request_token_uri,
+							ocr_authorize_uri		as authorize_uri,
+							ocr_access_token_uri	as access_token_uri
+					FROM oauth_consumer_registry
+					'.$where.'
+					ORDER BY ocr_server_uri_host, ocr_server_uri_path
+					', $args);
+		return $servers;
+	}
+
+
+	/**
+	 * Register or update a server for our site (we will be the consumer)
+	 * 
+	 * (This is the registry at the consumers, registering servers ;-) )
+	 * 
+	 * @param array server
+	 * @param int user_id	user registering this server
+	 * @param boolean user_is_admin
+	 * @exception OAuthException when fields are missing or on duplicate consumer_key
+	 * @return consumer_key
+	 */
+	public function updateServer ( $server, $user_id, $user_is_admin = false )
+	{
+		foreach (array('consumer_key', 'server_uri') as $f)
+		{
+			if (empty($server[$f]))
+			{
+				throw new OAuthException('The field "'.$f.'" must be set and non empty');
+			}
+		}
+		
+		if (!empty($server['id']))
+		{
+			$exists = $this->query_one('
+						SELECT ocr_id
+						FROM oauth_consumer_registry
+						WHERE ocr_consumer_key = \'%s\'
+						  AND ocr_id <> %d
+						  AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
+						', $server['consumer_key'], $server['id'], $user_id);
+		}
+		else
+		{
+			$exists = $this->query_one('
+						SELECT ocr_id
+						FROM oauth_consumer_registry
+						WHERE ocr_consumer_key = \'%s\'
+						  AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
+						', $server['consumer_key'], $user_id);
+		}
+
+		if ($exists)
+		{
+			throw new OAuthException('The server with key "'.$server['consumer_key'].'" has already been registered');
+		}
+
+		$parts = parse_url($server['server_uri']);
+		$host  = (isset($parts['host']) ? $parts['host'] : 'localhost');
+		$path  = (isset($parts['path']) ? $parts['path'] : '/');
+
+		if (isset($server['signature_methods']))
+		{
+			if (is_array($server['signature_methods']))
+			{
+				$server['signature_methods'] = strtoupper(implode(',', $server['signature_methods']));
+			}
+		}	
+		else
+		{
+			$server['signature_methods'] = '';
+		}
+
+		// When the user is an admin, then the user can update the user_id of this record
+		if ($user_is_admin && array_key_exists('user_id', $server))
+		{
+			if (is_null($server['user_id']))
+			{
+				$update_user =  ', ocr_usa_id_ref = NULL';
+			}
+			else
+			{
+				$update_user =  ', ocr_usa_id_ref = '.intval($server['user_id']);
+			}
+		}
+		else
+		{
+			$update_user = '';
+		}
+		
+		if (!empty($server['id']))
+		{
+			// Check if the current user can update this server definition
+			if (!$user_is_admin)
+			{
+				$ocr_usa_id_ref = $this->query_one('
+									SELECT ocr_usa_id_ref
+									FROM oauth_consumer_registry
+									WHERE ocr_id = %d
+									', $server['id']);
+				
+				if ($ocr_usa_id_ref != $user_id)
+				{
+					throw new OAuthException('The user "'.$user_id.'" is not allowed to update this server');
+				}
+			}
+			
+			// Update the consumer registration	
+			$this->query('
+					UPDATE oauth_consumer_registry
+					SET ocr_consumer_key    	= \'%s\',
+						ocr_consumer_secret 	= \'%s\',
+						ocr_server_uri	    	= \'%s\',
+						ocr_server_uri_host 	= \'%s\',
+						ocr_server_uri_path 	= \'%s\',
+						ocr_timestamp       	= NOW(),
+						ocr_request_token_uri	= \'%s\',
+						ocr_authorize_uri		= \'%s\',
+						ocr_access_token_uri	= \'%s\',
+						ocr_signature_methods	= \'%s\'
+						'.$update_user.'
+					WHERE ocr_id = %d
+					', 
+					$server['consumer_key'],
+					$server['consumer_secret'],
+					$server['server_uri'],
+					strtolower($host),
+					$path,
+					isset($server['request_token_uri']) ? $server['request_token_uri'] : '',
+					isset($server['authorize_uri'])     ? $server['authorize_uri']     : '',
+					isset($server['access_token_uri'])  ? $server['access_token_uri']  : '',
+					$server['signature_methods'],
+					$server['id']
+					);
+		}
+		else
+		{
+			if (empty($update_user))
+			{
+				// Per default the user owning the key is the user registering the key
+				$update_user =  ', ocr_usa_id_ref = '.intval($user_id);
+			}
+
+			$this->query('
+					INSERT INTO oauth_consumer_registry
+					SET ocr_consumer_key    	= \'%s\',
+						ocr_consumer_secret 	= \'%s\',
+						ocr_server_uri	    	= \'%s\',
+						ocr_server_uri_host 	= \'%s\',
+						ocr_server_uri_path 	= \'%s\',
+						ocr_timestamp       	= NOW(),
+						ocr_request_token_uri	= \'%s\',
+						ocr_authorize_uri		= \'%s\',
+						ocr_access_token_uri	= \'%s\',
+						ocr_signature_methods	= \'%s\'
+						'.$update_user, 
+					$server['consumer_key'],
+					$server['consumer_secret'],
+					$server['server_uri'],
+					strtolower($host),
+					$path,
+					isset($server['request_token_uri']) ? $server['request_token_uri'] : '',
+					isset($server['authorize_uri'])     ? $server['authorize_uri']     : '',
+					isset($server['access_token_uri'])  ? $server['access_token_uri']  : '',
+					$server['signature_methods']
+					);
+		
+			$ocr_id = $this->query_insert_id();
+		}
+		return $server['consumer_key'];
+	}
+
+
+	/**
+	 * Insert/update a new consumer with this server (we will be the server)
+	 * When this is a new consumer, then also generate the consumer key and secret.
+	 * Never updates the consumer key and secret.
+	 * When the id is set, then the key and secret must correspond to the entry
+	 * being updated.
+	 * 
+	 * (This is the registry at the server, registering consumers ;-) )
+	 * 
+	 * @param array consumer
+	 * @param int user_id	user registering this consumer
+	 * @param boolean user_is_admin
+	 * @return string consumer key
+	 */
+	public function updateConsumer ( $consumer, $user_id, $user_is_admin = false )
+	{
+		if (!$user_is_admin)
+		{
+			foreach (array('requester_name', 'requester_email') as $f)
+			{
+				if (empty($consumer[$f]))
+				{
+					throw new OAuthException('The field "'.$f.'" must be set and non empty');
+				}
+			}
+		}
+		
+		if (!empty($consumer['id']))
+		{
+			if (empty($consumer['consumer_key']))
+			{
+				throw new OAuthException('The field "consumer_key" must be set and non empty');
+			}
+			if (!$user_is_admin && empty($consumer['consumer_secret']))
+			{
+				throw new OAuthException('The field "consumer_secret" must be set and non empty');
+			}
+
+			// Check if the current user can update this server definition
+			if (!$user_is_admin)
+			{
+				$osr_usa_id_ref = $this->query_one('
+									SELECT osr_usa_id_ref
+									FROM oauth_server_registry
+									WHERE osr_id = %d
+									', $consumer['id']);
+				
+				if ($osr_usa_id_ref != $user_id)
+				{
+					throw new OAuthException('The user "'.$user_id.'" is not allowed to update this consumer');
+				}
+			}
+			else
+			{
+				// User is an admin, allow a key owner to be changed or key to be shared
+				if (array_key_exists('user_id',$consumer))
+				{
+					if (is_null($consumer['user_id']))
+					{
+						$this->query('
+							UPDATE oauth_server_registry
+							SET osr_usa_id_ref = NULL
+							WHERE osr_id = %d
+							', $consumer['id']);
+					}
+					else
+					{
+						$this->query('
+							UPDATE oauth_server_registry
+							SET osr_usa_id_ref = %d
+							WHERE osr_id = %d
+							', $consumer['user_id'], $consumer['id']);	
+					}
+				}
+			}
+			
+			$this->query('
+				UPDATE oauth_server_registry
+				SET osr_requester_name		= \'%s\',
+					osr_requester_email		= \'%s\',
+					osr_callback_uri		= \'%s\',
+					osr_application_uri		= \'%s\',
+					osr_application_title	= \'%s\',
+					osr_application_descr	= \'%s\',
+					osr_application_notes	= \'%s\',
+					osr_application_type	= \'%s\',
+					osr_application_commercial = IF(%d,1,0),
+					osr_timestamp			= NOW()
+				WHERE osr_id              = %d
+				  AND osr_consumer_key    = \'%s\'
+				  AND osr_consumer_secret = \'%s\'
+				',
+				$consumer['requester_name'],
+				$consumer['requester_email'],
+				isset($consumer['callback_uri']) 		? $consumer['callback_uri'] 			 : '',
+				isset($consumer['application_uri']) 	? $consumer['application_uri'] 			 : '',
+				isset($consumer['application_title'])	? $consumer['application_title'] 		 : '',
+				isset($consumer['application_descr'])	? $consumer['application_descr'] 		 : '',
+				isset($consumer['application_notes'])	? $consumer['application_notes'] 		 : '',
+				isset($consumer['application_type']) 	? $consumer['application_type'] 		 : '',
+				isset($consumer['application_commercial']) ? $consumer['application_commercial'] : 0,
+				$consumer['id'],
+				$consumer['consumer_key'],
+				$consumer['consumer_secret']
+				);
+				
+
+			$consumer_key = $consumer['consumer_key'];
+		}
+		else
+		{
+			$consumer_key	= $this->generateKey(true);
+			$consumer_secret= $this->generateKey();
+
+			// When the user is an admin, then the user can be forced to something else that the user
+			if ($user_is_admin && array_key_exists('user_id',$consumer))
+			{
+				if (is_null($consumer['user_id']))
+				{
+					$owner_id = 'NULL';
+				}
+				else
+				{
+					$owner_id = intval($consumer['user_id']);
+				}
+			}
+			else
+			{
+				// No admin, take the user id as the owner id.
+				$owner_id = intval($user_id);
+			}
+
+			$this->query('
+				INSERT INTO oauth_server_registry
+				SET osr_enabled				= 1,
+					osr_status				= \'active\',
+					osr_usa_id_ref			= %s,
+					osr_consumer_key		= \'%s\',
+					osr_consumer_secret		= \'%s\',
+					osr_requester_name		= \'%s\',
+					osr_requester_email		= \'%s\',
+					osr_callback_uri		= \'%s\',
+					osr_application_uri		= \'%s\',
+					osr_application_title	= \'%s\',
+					osr_application_descr	= \'%s\',
+					osr_application_notes	= \'%s\',
+					osr_application_type	= \'%s\',
+					osr_application_commercial = IF(%d,1,0),
+					osr_timestamp			= NOW(),
+					osr_issue_date			= NOW()
+				',
+				$owner_id,
+				$consumer_key,
+				$consumer_secret,
+				$consumer['requester_name'],
+				$consumer['requester_email'],
+				isset($consumer['callback_uri']) 		? $consumer['callback_uri'] 			 : '',
+				isset($consumer['application_uri']) 	? $consumer['application_uri'] 			 : '',
+				isset($consumer['application_title'])	? $consumer['application_title'] 		 : '',
+				isset($consumer['application_descr'])	? $consumer['application_descr'] 		 : '',
+				isset($consumer['application_notes'])	? $consumer['application_notes'] 		 : '',
+				isset($consumer['application_type']) 	? $consumer['application_type'] 		 : '',
+				isset($consumer['application_commercial']) ? $consumer['application_commercial'] : 0
+				);
+		}
+		return $consumer_key;
+
+	}
+
+
+
+	/**
+	 * Delete a consumer key.  This removes access to our site for all applications using this key.
+	 * 
+	 * @param string consumer_key
+	 * @param int user_id	user registering this server
+	 * @param boolean user_is_admin
+	 */
+	public function deleteConsumer ( $consumer_key, $user_id, $user_is_admin = false )
+	{
+		if ($user_is_admin)
+		{
+			$this->query('
+					DELETE FROM oauth_server_registry
+					WHERE osr_consumer_key = \'%s\'
+					  AND (osr_usa_id_ref = %d OR osr_usa_id_ref IS NULL)
+					', $consumer_key, $user_id);
+		}
+		else
+		{
+			$this->query('
+					DELETE FROM oauth_server_registry
+					WHERE osr_consumer_key = \'%s\'
+					  AND osr_usa_id_ref   = %d
+					', $consumer_key, $user_id);
+		}
+	}	
+	
+	
+	
+	/**
+	 * Fetch a consumer of this server, by consumer_key.
+	 * 
+	 * @param string consumer_key
+	 * @param int user_id
+	 * @param boolean user_is_admin (optional)
+	 * @exception OAuthException when consumer not found
+	 * @return array
+	 */
+	public function getConsumer ( $consumer_key, $user_id, $user_is_admin = false )
+	{
+		$consumer = $this->query_row_assoc('
+						SELECT	*
+						FROM oauth_server_registry
+						WHERE osr_consumer_key = \'%s\'
+						', $consumer_key);
+		
+		if (!is_array($consumer))
+		{
+			throw new OAuthException('No consumer with consumer_key "'.$consumer_key.'"');
+		}
+
+		$c = array();
+		foreach ($consumer as $key => $value)
+		{
+			$c[substr($key, 4)] = $value;
+		}
+		$c['user_id'] = $c['usa_id_ref'];
+
+		if (!$user_is_admin && !empty($c['user_id']) && $c['user_id'] != $user_id)
+		{
+			throw new OAuthException('No access to the consumer information for consumer_key "'.$consumer_key.'"');
+		}
+		return $c;
+	}
+
+
+	/**
+	 * Fetch the static consumer key for this provider.  The user for the static consumer 
+	 * key is NULL (no user, shared key).  If the key did not exist then the key is created.
+	 * 
+	 * @return string
+	 */
+	public function getConsumerStatic ()
+	{
+		$consumer = $this->query_one('
+						SELECT osr_consumer_key
+						FROM oauth_server_registry
+						WHERE osr_consumer_key LIKE \'sc-%%\'
+						  AND osr_usa_id_ref IS NULL
+						');
+
+		if (empty($consumer))
+		{
+			$consumer_key = 'sc-'.$this->generateKey(true);
+			$this->query('
+				INSERT INTO oauth_server_registry
+				SET osr_enabled				= 1,
+					osr_status				= \'active\',
+					osr_usa_id_ref			= NULL,
+					osr_consumer_key		= \'%s\',
+					osr_consumer_secret		= \'\',
+					osr_requester_name		= \'\',
+					osr_requester_email		= \'\',
+					osr_callback_uri		= \'\',
+					osr_application_uri		= \'\',
+					osr_application_title	= \'Static shared consumer key\',
+					osr_application_descr	= \'\',
+					osr_application_notes	= \'Static shared consumer key\',
+					osr_application_type	= \'\',
+					osr_application_commercial = 0,
+					osr_timestamp			= NOW(),
+					osr_issue_date			= NOW()
+				',
+				$consumer_key
+				);
+			
+			// Just make sure that if the consumer key is truncated that we get the truncated string
+			$consumer = $this->getConsumerStatic();
+		}
+		return $consumer;
+	}
+
+
+	/**
+	 * Add an unautorized request token to our server.
+	 * 
+	 * @param string consumer_key
+	 * @param array options		(eg. token_ttl)
+	 * @return array (token, token_secret)
+	 */
+	public function addConsumerRequestToken ( $consumer_key, $options = array() )
+	{
+		$token  = $this->generateKey(true);
+		$secret = $this->generateKey();
+		$osr_id	= $this->query_one('
+						SELECT osr_id
+						FROM oauth_server_registry
+						WHERE osr_consumer_key = \'%s\'
+						  AND osr_enabled      = 1
+						', $consumer_key);
+
+		if (!$osr_id)
+		{
+			throw new OAuthException('No server with consumer_key "'.$consumer_key.'" or consumer_key is disabled');
+		}
+
+		if (isset($options['token_ttl']) && is_numeric($options['token_ttl']))
+		{
+			$ttl = intval($options['token_ttl']);
+		}
+		else
+		{
+			$ttl = $this->max_request_token_ttl;
+		}
+
+		$this->query('
+				INSERT INTO oauth_server_token
+				SET ost_osr_id_ref		= %d,
+					ost_usa_id_ref		= 1,
+					ost_token			= \'%s\',
+					ost_token_secret	= \'%s\',
+					ost_token_type		= \'request\',
+					ost_token_ttl       = DATE_ADD(NOW(), INTERVAL %d SECOND)
+				ON DUPLICATE KEY UPDATE
+					ost_osr_id_ref		= VALUES(ost_osr_id_ref),
+					ost_usa_id_ref		= VALUES(ost_usa_id_ref),
+					ost_token			= VALUES(ost_token),
+					ost_token_secret	= VALUES(ost_token_secret),
+					ost_token_type		= VALUES(ost_token_type),
+					ost_token_ttl       = VALUES(ost_token_ttl),
+					ost_timestamp		= NOW()
+				', $osr_id, $token, $secret, $ttl);
+		
+		return array('token'=>$token, 'token_secret'=>$secret, 'token_ttl'=>$ttl);
+	}
+	
+	
+	/**
+	 * Fetch the consumer request token, by request token.
+	 * 
+	 * @param string token
+	 * @return array  token and consumer details
+	 */
+	public function getConsumerRequestToken ( $token )
+	{
+		$rs = $this->query_row_assoc('
+				SELECT	ost_token			as token,
+						ost_token_secret	as token_secret,
+						osr_consumer_key	as consumer_key,
+						osr_consumer_secret	as consumer_secret,
+						ost_token_type		as token_type
+				FROM oauth_server_token
+						JOIN oauth_server_registry
+						ON ost_osr_id_ref = osr_id
+				WHERE ost_token_type = \'request\'
+				  AND ost_token      = \'%s\'
+				  AND ost_token_ttl  >= NOW()
+				', $token);
+		
+		return $rs;
+	}
+	
+
+	/**
+	 * Delete a consumer token.  The token must be a request or authorized token.
+	 * 
+	 * @param string token
+	 */
+	public function deleteConsumerRequestToken ( $token )
+	{
+		$this->query('
+					DELETE FROM oauth_server_token
+					WHERE ost_token 	 = \'%s\'
+					  AND ost_token_type = \'request\'
+					', $token);
+	}
+	
+
+	/**
+	 * Upgrade a request token to be an authorized request token.
+	 * 
+	 * @param string token
+	 * @param int	 user_id  user authorizing the token
+	 * @param string referrer_host used to set the referrer host for this token, for user feedback
+	 */
+	public function authorizeConsumerRequestToken ( $token, $user_id, $referrer_host = '' )
+	{
+		$this->query('
+					UPDATE oauth_server_token
+					SET ost_authorized    = 1,
+						ost_usa_id_ref    = %d,
+						ost_timestamp     = NOW(),
+						ost_referrer_host = \'%s\'
+					WHERE ost_token      = \'%s\'
+					  AND ost_token_type = \'request\'
+					', $user_id, $referrer_host, $token);
+	}
+
+
+	/**
+	 * Count the consumer access tokens for the given consumer.
+	 * 
+	 * @param string consumer_key
+	 * @return int
+	 */
+	public function countConsumerAccessTokens ( $consumer_key )
+	{
+		$count = $this->query_one('
+					SELECT COUNT(ost_id)
+					FROM oauth_server_token
+							JOIN oauth_server_registry
+							ON ost_osr_id_ref = osr_id
+					WHERE ost_token_type   = \'access\'
+					  AND osr_consumer_key = \'%s\'
+					  AND ost_token_ttl    >= NOW()
+					', $consumer_key);
+		
+		return $count;
+	}
+
+
+	/**
+	 * Exchange an authorized request token for new access token.
+	 * 
+	 * @param string token
+	 * @param array options		options for the token, token_ttl
+	 * @exception OAuthException when token could not be exchanged
+	 * @return array (token, token_secret)
+	 */
+	public function exchangeConsumerRequestForAccessToken ( $token, $options = array() )
+	{
+		$new_token  = $this->generateKey(true);
+		$new_secret = $this->generateKey();
+
+		// Maximum time to live for this token
+		if (isset($options['token_ttl']) && is_numeric($options['token_ttl']))
+		{
+			$ttl_sql = 'DATE_ADD(NOW(), INTERVAL '.intval($options['token_ttl']).' SECOND)';
+		}
+		else
+		{
+			$ttl_sql = "'9999-12-31'";
+		}
+
+		$this->query('
+					UPDATE oauth_server_token
+					SET ost_token			= \'%s\',
+						ost_token_secret	= \'%s\',
+						ost_token_type		= \'access\',
+						ost_timestamp		= NOW(),
+						ost_token_ttl       = '.$ttl_sql.'
+					WHERE ost_token      = \'%s\'
+					  AND ost_token_type = \'request\'
+					  AND ost_authorized = 1
+					  AND ost_token_ttl  >= NOW()
+					', $new_token, $new_secret, $token);
+		
+		if ($this->query_affected_rows() != 1)
+		{
+			throw new OAuthException('Can\'t exchange request token "'.$token.'" for access token. No such token or not authorized');
+		}
+
+		$ret = array('token' => $new_token, 'token_secret' => $new_secret);
+		$ttl = $this->query_one('
+					SELECT	IF(ost_token_ttl >= \'9999-12-31\', NULL, UNIX_TIMESTAMP(ost_token_ttl) - UNIX_TIMESTAMP(NOW())) as token_ttl
+					FROM oauth_server_token
+					WHERE ost_token = \'%s\'', $new_token);
+
+		if (is_numeric($ttl))
+		{
+			$ret['token_ttl'] = intval($ttl);
+		}
+		return $ret;
+	}
+
+
+	/**
+	 * Fetch the consumer access token, by access token.
+	 * 
+	 * @param string token
+	 * @param int user_id
+	 * @exception OAuthException when token is not found
+	 * @return array  token and consumer details
+	 */
+	public function getConsumerAccessToken ( $token, $user_id )
+	{
+		$rs = $this->query_row_assoc('
+				SELECT	ost_token				as token,
+						ost_token_secret		as token_secret,
+						ost_referrer_host		as token_referrer_host,
+						osr_consumer_key		as consumer_key,
+						osr_consumer_secret		as consumer_secret,
+						osr_application_uri		as application_uri,
+						osr_application_title	as application_title,
+						osr_application_descr	as application_descr
+				FROM oauth_server_token
+						JOIN oauth_server_registry
+						ON ost_osr_id_ref = osr_id
+				WHERE ost_token_type = \'access\'
+				  AND ost_token      = \'%s\'
+				  AND ost_usa_id_ref = %d
+				  AND ost_token_ttl  >= NOW()
+				', $token, $user_id);
+		
+		if (empty($rs))
+		{
+			throw new OAuthException('No server_token "'.$token.'" for user "'.$user_id.'"');
+		}
+		return $rs;
+	}
+
+
+	/**
+	 * Delete a consumer access token.
+	 * 
+	 * @param string token
+	 * @param int user_id
+	 * @param boolean user_is_admin
+	 */
+	public function deleteConsumerAccessToken ( $token, $user_id, $user_is_admin = false )
+	{
+		if ($user_is_admin)
+		{
+			$this->query('
+						DELETE FROM oauth_server_token
+						WHERE ost_token 	 = \'%s\'
+						  AND ost_token_type = \'access\'
+						', $token);
+		}
+		else
+		{
+			$this->query('
+						DELETE FROM oauth_server_token
+						WHERE ost_token 	 = \'%s\'
+						  AND ost_token_type = \'access\'
+						  AND ost_usa_id_ref = %d
+						', $token, $user_id);
+		}
+	}
+
+
+	/**
+	 * Set the ttl of a consumer access token.  This is done when the
+	 * server receives a valid request with a xoauth_token_ttl parameter in it.
+	 * 
+	 * @param string token
+	 * @param int ttl
+	 */
+	public function setConsumerAccessTokenTtl ( $token, $token_ttl )
+	{
+		if ($token_ttl <= 0)
+		{
+			// Immediate delete when the token is past its ttl
+			$this->deleteConsumerAccessToken($token, 0, true);
+		}
+		else
+		{
+			// Set maximum time to live for this token
+			$this->query('
+						UPDATE oauth_server_token
+						SET ost_token_ttl = DATE_ADD(NOW(), INTERVAL %d SECOND)
+						WHERE ost_token 	 = \'%s\'
+						  AND ost_token_type = \'access\'
+						', $token_ttl, $token);
+		}
+	}
+
+
+	/**
+	 * Fetch a list of all consumer keys, secrets etc.
+	 * Returns the public (user_id is null) and the keys owned by the user
+	 * 
+	 * @param int user_id
+	 * @return array
+	 */
+	public function listConsumers ( $user_id )
+	{
+		$rs = $this->query_all_assoc('
+				SELECT	osr_id					as id,
+						osr_usa_id_ref			as user_id,
+						osr_consumer_key 		as consumer_key,
+						osr_consumer_secret		as consumer_secret,
+						osr_enabled				as enabled,
+						osr_status 				as status,
+						osr_issue_date			as issue_date,
+						osr_application_uri		as application_uri,
+						osr_application_title	as application_title,
+						osr_application_descr	as application_descr,
+						osr_requester_name		as requester_name,
+						osr_requester_email		as requester_email
+				FROM oauth_server_registry
+				WHERE (osr_usa_id_ref = %d OR osr_usa_id_ref IS NULL)
+				ORDER BY osr_application_title
+				', $user_id);
+		return $rs;
+	}
+
+
+	/**
+	 * Fetch a list of all consumer tokens accessing the account of the given user.
+	 * 
+	 * @param int user_id
+	 * @return array
+	 */
+	public function listConsumerTokens ( $user_id )
+	{
+		$rs = $this->query_all_assoc('
+				SELECT	osr_consumer_key 		as consumer_key,
+						osr_consumer_secret		as consumer_secret,
+						osr_enabled				as enabled,
+						osr_status 				as status,
+						osr_application_uri		as application_uri,
+						osr_application_title	as application_title,
+						osr_application_descr	as application_descr,
+						ost_timestamp			as timestamp,	
+						ost_token				as token,
+						ost_token_secret		as token_secret,
+						ost_referrer_host		as token_referrer_host
+				FROM oauth_server_registry
+					JOIN oauth_server_token
+					ON ost_osr_id_ref = osr_id
+				WHERE ost_usa_id_ref = %d
+				  AND ost_token_type = \'access\'
+				  AND ost_token_ttl  >= NOW()
+				ORDER BY osr_application_title
+				', $user_id);
+		return $rs;
+	}
+
+
+	/**
+	 * Check an nonce/timestamp combination.  Clears any nonce combinations
+	 * that are older than the one received.
+	 * 
+	 * @param string	consumer_key
+	 * @param string 	token
+	 * @param int		timestamp
+	 * @param string 	nonce
+	 * @exception OAuthException	thrown when the timestamp is not in sequence or nonce is not unique
+	 */
+	public function checkServerNonce ( $consumer_key, $token, $timestamp, $nonce )
+	{
+		$r = $this->query_row('
+							SELECT MAX(osn_timestamp), MAX(osn_timestamp) > %d + %d
+							FROM oauth_server_nonce
+							WHERE osn_consumer_key = \'%s\'
+							  AND osn_token        = \'%s\'
+							', $timestamp, $this->max_timestamp_skew, $consumer_key, $token);
+
+		if (!empty($r) && $r[1])
+		{
+			throw new OAuthException('Timestamp is out of sequence. Request rejected. Got '.$timestamp.' last max is '.$r[0].' allowed skew is '.$this->max_timestamp_skew);
+		}
+		
+		// Insert the new combination
+		$this->query('
+				INSERT IGNORE INTO oauth_server_nonce
+				SET osn_consumer_key	= \'%s\',
+					osn_token			= \'%s\',
+					osn_timestamp		= %d,
+					osn_nonce			= \'%s\'
+				', $consumer_key, $token, $timestamp, $nonce);
+		
+		if ($this->query_affected_rows() == 0)
+		{
+			throw new OAuthException('Duplicate timestamp/nonce combination, possible replay attack.  Request rejected.');
+		}
+
+		// Clean up all timestamps older than the one we just received
+		$this->query('
+				DELETE FROM oauth_server_nonce
+				WHERE osn_consumer_key	= \'%s\'
+				  AND osn_token			= \'%s\'
+				  AND osn_timestamp     < %d - %d
+				', $consumer_key, $token, $timestamp, $this->max_timestamp_skew);
+	}
+
+
+	/**
+	 * Add an entry to the log table
+	 * 
+	 * @param array keys (osr_consumer_key, ost_token, ocr_consumer_key, oct_token)
+	 * @param string received
+	 * @param string sent
+	 * @param string base_string
+	 * @param string notes
+	 * @param int (optional) user_id
+	 */
+	public function addLog ( $keys, $received, $sent, $base_string, $notes, $user_id = null )
+	{
+		$args = array();
+		$ps   = array();
+		foreach ($keys as $key => $value)
+		{
+			$args[] = $value;
+			$ps[]   = "olg_$key = '%s'";
+		}
+
+		if (!empty($_SERVER['REMOTE_ADDR']))
+		{
+			$remote_ip = $_SERVER['REMOTE_ADDR'];
+		}	
+		else if (!empty($_SERVER['REMOTE_IP']))
+		{
+			$remote_ip = $_SERVER['REMOTE_IP'];
+		}
+		else
+		{
+			$remote_ip = '0.0.0.0';
+		}
+
+		// Build the SQL
+		$ps[] = "olg_received  	= '%s'";						$args[] = $this->makeUTF8($received);
+		$ps[] = "olg_sent   	= '%s'";						$args[] = $this->makeUTF8($sent);
+		$ps[] = "olg_base_string= '%s'";						$args[] = $base_string;
+		$ps[] = "olg_notes   	= '%s'";						$args[] = $this->makeUTF8($notes);
+		$ps[] = "olg_usa_id_ref = NULLIF(%d,0)";				$args[] = $user_id;
+		$ps[] = "olg_remote_ip  = IFNULL(INET_ATON('%s'),0)";	$args[] = $remote_ip;
+
+		$this->query('INSERT INTO oauth_log SET '.implode(',', $ps), $args);
+	}
+	
+	
+	/**
+	 * Get a page of entries from the log.  Returns the last 100 records
+	 * matching the options given.
+	 * 
+	 * @param array options
+	 * @param int user_id	current user
+	 * @return array log records
+	 */
+	public function listLog ( $options, $user_id )
+	{
+		$where = array();
+		$args  = array();
+		if (empty($options))
+		{
+			$where[] = 'olg_usa_id_ref = %d';
+			$args[]  = $user_id;
+		}
+		else
+		{
+			foreach ($options as $option => $value)
+			{
+				if (strlen($value) > 0)
+				{
+					switch ($option)
+					{
+					case 'osr_consumer_key':
+					case 'ocr_consumer_key':
+					case 'ost_token':
+					case 'oct_token':
+						$where[] = 'olg_'.$option.' = \'%s\'';
+						$args[]  = $value;	
+						break;				
+					}
+				}
+			}
+			
+			$where[] = '(olg_usa_id_ref IS NULL OR olg_usa_id_ref = %d)';
+			$args[]  = $user_id;
+		}
+
+		$rs = $this->query_all_assoc('
+					SELECT olg_id,
+							olg_osr_consumer_key 	AS osr_consumer_key,
+							olg_ost_token			AS ost_token,
+							olg_ocr_consumer_key	AS ocr_consumer_key,
+							olg_oct_token			AS oct_token,
+							olg_usa_id_ref			AS user_id,
+							olg_received			AS received,
+							olg_sent				AS sent,
+							olg_base_string			AS base_string,
+							olg_notes				AS notes,
+							olg_timestamp			AS timestamp,
+							INET_NTOA(olg_remote_ip) AS remote_ip
+					FROM oauth_log
+					WHERE '.implode(' AND ', $where).'
+					ORDER BY olg_id DESC
+					LIMIT 0,100', $args);
+
+		return $rs;
+	}
+
+
+
+	/**
+	 * Initialise the database
+	 */
+	public function install ()
+	{
+		require_once dirname(__FILE__) . '/mysql/install.php';
+	}
+	
+	
+	/* ** Some simple helper functions for querying the mysql db ** */
+
+	/**
+	 * Perform a query, ignore the results
+	 * 
+	 * @param string sql
+	 * @param vararg arguments (for sprintf)
+	 */
+	protected function query ( $sql )
+	{
+		$sql = $this->sql_printf(func_get_args());
+		if (!($res = mysql_query($sql, $this->conn)))
+		{
+			$this->sql_errcheck($sql);
+		}
+		if (is_resource($res))
+		{
+			mysql_free_result($res);
+		}
+	}
+	
+
+	/**
+	 * Perform a query, ignore the results
+	 * 
+	 * @param string sql
+	 * @param vararg arguments (for sprintf)
+	 * @return array
+	 */
+	protected function query_all_assoc ( $sql )
+	{
+		$sql = $this->sql_printf(func_get_args());
+		if (!($res = mysql_query($sql, $this->conn)))
+		{
+			$this->sql_errcheck($sql);
+		}
+		$rs = array();
+		while ($row  = mysql_fetch_assoc($res))
+		{
+			$rs[] = $row;
+		}
+		mysql_free_result($res);
+		return $rs;
+	}
+	
+	
+	/**
+	 * Perform a query, return the first row
+	 * 
+	 * @param string sql
+	 * @param vararg arguments (for sprintf)
+	 * @return array
+	 */
+	protected function query_row_assoc ( $sql )
+	{
+		$sql = $this->sql_printf(func_get_args());
+		if (!($res = mysql_query($sql, $this->conn)))
+		{
+			$this->sql_errcheck($sql);
+		}
+		if ($row = mysql_fetch_assoc($res))
+		{
+			$rs = $row;
+		}
+		else
+		{
+			$rs = false;
+		}
+		mysql_free_result($res);
+		return $rs;
+	}
+
+	
+	/**
+	 * Perform a query, return the first row
+	 * 
+	 * @param string sql
+	 * @param vararg arguments (for sprintf)
+	 * @return array
+	 */
+	protected function query_row ( $sql )
+	{
+		$sql = $this->sql_printf(func_get_args());
+		if (!($res = mysql_query($sql, $this->conn)))
+		{
+			$this->sql_errcheck($sql);
+		}
+		if ($row = mysql_fetch_array($res))
+		{
+			$rs = $row;
+		}
+		else
+		{
+			$rs = false;
+		}
+		mysql_free_result($res);
+		return $rs;
+	}
+	
+		
+	/**
+	 * Perform a query, return the first column of the first row
+	 * 
+	 * @param string sql
+	 * @param vararg arguments (for sprintf)
+	 * @return mixed
+	 */
+	protected function query_one ( $sql )
+	{
+		$sql = $this->sql_printf(func_get_args());
+		if (!($res = mysql_query($sql, $this->conn)))
+		{
+			$this->sql_errcheck($sql);
+		}
+		$val = @mysql_result($res, 0, 0);
+		mysql_free_result($res);
+		return $val;
+	}
+	
+	
+	/**
+	 * Return the number of rows affected in the last query
+	 */
+	protected function query_affected_rows ()
+	{
+		return mysql_affected_rows($this->conn);
+	}
+
+
+	/**
+	 * Return the id of the last inserted row
+	 * 
+	 * @return int
+	 */
+	protected function query_insert_id ()
+	{
+		return mysql_insert_id($this->conn);
+	}
+	
+	
+	protected function sql_printf ( $args )
+	{
+		$sql  = array_shift($args);
+		if (count($args) == 1 && is_array($args[0]))
+		{
+			$args = $args[0];
+		}
+		$args = array_map(array($this, 'sql_escape_string'), $args);
+		return vsprintf($sql, $args);
+	}
+	
+	
+	protected function sql_escape_string ( $s )
+	{
+		if (is_string($s))
+		{
+			return mysql_real_escape_string($s, $this->conn);
+		}
+		else if (is_null($s))
+		{
+			return NULL;
+		}
+		else if (is_bool($s))
+		{
+			return intval($s);
+		}
+		else if (is_int($s) || is_float($s))
+		{
+			return $s;
+		}
+		else
+		{
+			return mysql_real_escape_string(strval($s), $this->conn);
+		}
+	}
+	
+	
+	protected function sql_errcheck ( $sql )
+	{
+		if (mysql_errno($this->conn))
+		{
+			$msg =  "SQL Error in OAuthStoreMySQL: ".mysql_error($this->conn)."\n\n" . $sql;
+			throw new OAuthException($msg);
+		}
+	}
+}
+
+
+/* vi:set ts=4 sts=4 sw=4 binary noeol: */
+
+?>
\ No newline at end of file