PDO for Fun and Profit (mostly fun)

Connecting and retrieving information from the database is usually the least sexy part of writing an app and so gets left behind a lot. The following classes, which are going to be part of the forum script, (more or less) should take some of the dreariness out of CRUD; that’s Create, Read, Update and Delete. The cornerstones of our universe. Shout out to Kevin Worthington. (Do people still say “shout out”? Man, I feel old.)

Let’s begin with the (semi) universal PDO connector. If you use Postgres, you can include your entire connection string in one line including username and password :

//PostgreSQL :
define( 'DBH', 'pgsql:host=localhost;dbname=hitlist;user=MichaelCorleone;password=IKnowItWasYouFredo' );

However the MySQL PDO driver will complain if you just include a single string like that as it demands the username and password separately. So, you’ll have to do the following :

//MySQL PDO connection (usually)
define( 'DBH', 'mysql:host=localhost;dbname=hitlist' );
define( 'DB_USER', 'MichaelCorleone' );
define( 'DB_PASS', 'IKnowItWasYouFredo' );

$dbh = new PDO( DBH, DB_USER, DB_PASS );

This is annoying.

There’s also the problem of security. If you just leave the connection strings in your script and for some reason the script gets served in plain-text (this happens far more often than you might think) there go your keys to the castle. The safest place to store connection strings is in your php.ini.

You can put the DSN name in a php.ini config as follows :

php.dsn.mydsn ='mysql:host=localhost;dbname=hitlist;username=MichaelCorleone;password=IKnowItWasYouFredo'

Now it’s the same as the (sane) Postgresql driver. But now you need a way to grab this complete DSN and dissect the username and password as necessary.

I wrote a handy class for this.

<?php
/**
 * PDO Connector class
 * Modifies the DSN to parse username and password individually.
 * Optionally, gets the DSN directly from php.ini.
 *
 * @author Eksith Rodrigo <reksith at gmail.com>
 * @license http://opensource.org/licenses/ISC ISC License
 * @version 0.2
 */

namespace Models;
 
class Cxn {
	
	/**
	 * @var object PDO connection.
	 */
	protected $db;
	
	
	public function __construct( $dbh ) {
		$this->connect( $dbh );
	}
	
	
	public function __destruct() {
		$this->db = null;
	}
	
	
	public function getDb() {
		
		if ( is_object( $this->db ) ) {
			return $this->db;
		} else {
			die('There was a database problem');
		}
	}

	private function connect( $dbh ) {
		if ( !empty( $this->db ) && is_object( $this->db ) ) {
			return;
		}
		
		try {
			$settings = array(
				\PDO::ATTR_TIMEOUT		=> "5",
				\PDO::ATTR_ERRMODE		=> 
						\PDO::ERRMODE_EXCEPTION,
				\PDO::ATTR_DEFAULT_FETCH_MODE	=> 
						\PDO::FETCH_ASSOC,
				\PDO::ATTR_PERSISTENT		=> true
			);
			
			$this->_dsn( $dbh, $username, $password );
			if ( !defined( 'DBTYPE' ) ) {
				$this->setDbType( $dbh );
			}
			
			if ( 'mysql' === DBTYPE || 'postgres' === DBTYPE) {
				/**
				 * Might be slightly slower, but more secure to 
				 * disable emulation
				 */
				$settings[\PDO::ATTR_EMULATE_PERPARES] = false;
			}
			
			$this->db = new \PDO( $dbh, $username, $password, 
						$settings );
			
			
		} catch ( \PDOException $e ) {
			exit( $e->getMessage() );
		}
	}
	
	/**
	 * Extract the username and password from the DSN and rebuild the 
	 * connection string
	 * 
	 * @param string $dsn Full connection string or DSN identifyer in php.ini
	 * @param string $username Optional username. If empty, it will extract from DSN
	 * @param string $password Also optional and will extract from DSN as above
	 */
	private function _dsn( &$dsn, &$username = '', &$password = '' ) {
		
		/**
		 * No host name with ':' would mean this is a DSN name in php.ini
		 */
		if ( false === strrpos( $dsn, ':' ) ) {
			
			/**
			 * We need get_cfg_var() here because ini_get doesn't work
			 * https://bugs.php.net/bug.php?id=54276
			 */
			$dsn = get_cfg_var( "php.dsn.$dsn" );
		}
		
		/**
		 * Some people use spaces to separate parameters in DSN strings 
		 * and this is NOT standard
		 */
		$d = explode( ';', $dsn );
		$m = count( $d );
		$s = '';
		
		for( $i = 0; $i < $m; $i++ ) {
			$n = explode( '=', $d[$i] );
			
			/**
			 * Empty parameter? Continue
			 */
			if ( count( $n ) <= 1 ) {
				$s .= implode( '', $n ) . ';';
				continue;
			}
			
			/**
			 * Username or password?
			 */
			switch( trim( $n[0] ) ) {
				case 'uid':
				case 'user':
				case 'username':
					$username = trim( $n[1] );
					break;
				
				case 'pwd':
				case 'pass':
				case 'password':
					$password = trim( $n[1] );
					break;
				
				/**
				 * Some other parameter? Leave as-is
				 */
				default:
					$s .= implode( '=', $n ) . ';';
			}
		}
		
		$dsn = rtrim( $s, ';' );
	}
	
	/**
	 * Sets the DBTYPE defined variable.
	 * Useful for database specific SQL.
	 * Expand as necessary.
	 */
	private function setDbType( $dsn ) {
		
		if ( 0 === strpos( $dsn, 'mysql' ) ) {
			
			define( 'DBTYPE', 'mysql' );
			
		} elseif ( 0 === strpos( $dsn, 'postgres' ) ) {
			
			define( 'DBTYPE', 'postgres' );
			
		} elseif ( 0 === strpos( $dsn, 'sqlite' ) ) {
			
			define( 'DBTYPE', 'sqlite' );
			
		} else {
			define( 'DBTYPE', 'other' );
		}
	}
}

Using this is pretty simple :

$cxn = new Cxn( 'mydsn' ); // Where php.dsn.mydsn is in php.ini
$db = $cxn->getDb();
$st = $db->prepare( 'SELECT * FROM whacks' );

$st->execute();
$whacks = $st->fetchAll();

Of course you want your family to be completely clean in a few years or Kay will try to leave you and take the kids with her. To prevent this and hide any whacks you do commit from Kay, we need some OOP and I’ve used this class on a previous project (not specifically to whack anyone) where I needed object/model separation. Hence the namespace will again be “Models”, same as above.

<?php
/* 
Example MySQL schema: 
CREATE TABLE whacks (
	id int(10) unsigned NOT NULL auto_increment,
	name varchar(100) NOT NULL,
	method varchar(100) NOT NULL,
	reason text NOT NULL,
	created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at datetime NOT NULL,
	whacked_at timestamp NOT NULL,
	PRIMARY KEY (id)
);
*/

namespace Models;

class Whack extends base {
	
	/**
	 * @var string Keep your friends close, but your enemies closer 
	 */
	public $name	= '';
	
	
	/**
	 * @var string Stupid thugs. People behaving like that with guns.
	 */
	public $method	= '';
	
	
	
	/**
	 * @var string I don't feel I have to wipe everybody out, Tom. 
	 *		Just my enemies.
	 */
	public $reason = '';
	
	
	/**
	 * @var string He's has been dying from the same heart attack 
	 *		for the last twenty years.
	 */
	public $whacked_at= '';
	
	
	public function __construct( array $data = null ) {
		
		if ( empty( $data ) ) {
			return;
		}
		
		foreach ( $data as $field => $value ) {
			$this->$field = $value;
		}
	}
	
	
	public function save() {
		$row		= 0;
		$params = array(
			'name'		=> $this->name,
			'method'	=> $this->method
		);
		
		
		/**
		 * I don't want anything to happen to him while my mother's alive
		 */
		if ( !empty( $this->whacked_at ) ) {
			$this->whacked_at = parent::_myTime( $this->whacked_at );
			$params['whacked_at'] = $this->whacked_at;
		}
		
		/**
		 * Sal, Tom, the boss says he'll come in a separate car.
		 */
		if ( isset( $this->id ) ) {
			$params['id'] = $this->id;
			parent::edit( 'whacks', $params );
		} else {
			parent::put( 'whacks', $params );
		}
	}
	
	
	/**
	 * I want you to find out what he's got under his fingernails.
	 */
	public static function find( $filter = array() ) {
		$sql = "SELECT id, name, method, reason, created_at, whacked_at 
				FROM whacks";
		
		// Filtering cleanup
		$filter = parent::filterConfig( $filter );
		$params = array();
		
		// Get by id
		if ( $filter['id'] > 0 ) {
			$sql .= " WHERE id = :id";
			$param['id'] = $filter['id'];
		}
		
		// Get by name
		if ( isset( $filter['name'] ) ) {
			if ( false !== strrpos( $sql, 'WHERE' ) ) {
				$sql .= " OR";
			} else {
				$sql .= " WHERE";
			}
			$sql .= " name = :name";
			$param['name'] = $filter['name'];
		}
		
		// Get 10, 20 results etc...
		if ( isset( $filter['limit'] ) ) {
			$sql .= " LIMIT :limit";
			$param['limit'] = $filter['limit'];
		}
		
		// Pagination
		if ( isset( $filter['offset'] ) ) {
			$sql .= " OFFSET :offset";
			$param['offset'] = $filter['offset'];
		}
		
		// Many whacks. Family's been busy.
		if ( isset( $filter['limit'] ) && $filter['limit'] > 1 )
			return parent::find( $sql, $params );
		}
		
		// One whack (note the 'true' for a single object)
		return parent::find( $sql, $params, true );
	}
	
	
	/**
	 * Tessio? I always thought it would be Clemenza.
	 */
	public static function delete( $params = array() ) {
		parent::delete( 'whacks', $params );
	}
}

You’ll notice there’s a base class this inherits from. The base is what’s referred to in all those “parent::” methods for creating (put), editing and, of course, deleting because no self-respecting member of the family will be too stubborn, lest he wishes to get gunned down on the causeway.

Here’s the parent class (You don’t need to manually assign the DSN to this; just have it as a defined variable) :

<?php
/**
 * Base object modal for finding, editing, deleting objects by table
 * This class handles connecting to a database if necessary and so it 
 * depends on the 'Cxn' class and requires DBH to be defined.
 * 
 * @author Eksith Rodrigo <reksith at gmail.com>
 * @license http://opensource.org/licenses/ISC ISC License
 * @uses Cxn
 */

namespace Models;

abstract class base {
	
	/**
	 * @var int Class object unique identifier (every class should have one)
	 */
	public $id;
	
	
	/**
	 * @var int Class object creation date. Should not modified.
	 */
	public $created_at;
	
	
	/**
	 * @var int Class object edited/saved date. Must be modified.
	 */
	public $updated_at;
	
	
	/**
	 * @var int Special status. Relevance will differ per object.
	 * @example A Post with status = -1 may be 'hidden' from view
	 */
	public $status;
	
	
	/**
	 * @var object PDO connection.
	 */
	protected static $db = null;
	
	
	/**
	 * @var int Total number of queries executed.
	 */
	public static $qcount = 0;
	
	
	/**
	 * Checks PDO connection or assigns to self::$db if it hasn't been set 
	 * and a new one has been passed.
	 * 
	 * @param object $db PDO connection
	 * @return bool True of the variable was set. False on failure.
	 */
	protected static function _db( $db = null ) {
		
		if ( is_object( self::$db ) ) {
			return true;
		} elseif ( is_object( $db ) ) {
			self::$db = $db;
			return true;
		}
		
		return false;
	}
	
	
	/**
	 * Clean up
	 */
	public function __destruct() {
		
		if ( is_object( self::$db ) ) {
			self::$db = null;
		}
	}
	
	
	/**
	 * Setup local Cxn instance with PDO 
	 * Note: This depends on the Cxn class and also 
	 * DBH (your connection string) being set
	 */
	public static function init() {
		
		if ( !self::_db() ) {
			$db = new Cxn( DBH );
			self::_db( $db->getDb() );
		}
	}
	
	
	/**
	 * Find objects using the given sql and parameters
	 * 
	 * @param string $sql Database query
	 * @param array $params Selector parameters in 'column' => 'value' format
	 * @return object Of the same type as the class calling this or null on failure
	 * @return mixed Single object or array of the same type as the class calling 
	 * 			this or null on failure
	 */
	protected static function find( $sql, $params, $single = false ) {
		
		self::init();
		
		$class	= get_called_class();
		$stmt	= self::$db->prepare( $sql );
		
		if ( $stmt->execute( $params ) ) {
			self::$qcount++;
			if ( $single ) {
				return $stmt->fetchObject( $class );
			} else {
				return $stmt->fetchAll( 
					\PDO::FETCH_CLASS, $class );
			}
		}
		return null;
	}
	
	
	/**
	 * Insert a single row into a table
	 * 
	 * @param string $table Database table name
	 * @param array $params Insert values in 'column' => 'value' format
	 * @return int The ID of the newly inserted row or 0 on failure
	 */
	protected static function put( $table, $params ) {
		
		self::init();
		
		$sql	= self::_insertStatement( $table, $params );
		$stmt	= self::$db->prepare( $sql );
		
		if ( $stmt->execute( $params ) ) {
			self::$qcount++;
			if ( 'postgres' === DBTYPE ) {
				return self::$db->lastInsertId( 'id' );
			}
			return self::$db->lastInsertId();
		}
		
		return 0;
	}
	
	
	/**
	 * Update records in a single table
	 * 
	 * @param string $table Database table name
	 * @param array $params Column parameters (id required)
	 * @return int Number of rows affected
	 */
	protected static function edit( $table, $params ) {
		
		if ( !isset( $params['id'] ) ) {
			return 0;
		}
		
		$id	= $params['id'];
		unset( $params['id'] );
				
		$sql = self::_updateStatement(
			$table, $params, "$table.id = :id"
		);
		
		self::init();
		$params['id']	= $id;
		$stmt		= self::$db->prepare( $sql );
	
		if ( $stmt->execute( $params ) ) {
			self::$qcount++;
			return $stmt->rowCount();
		}
		return 0;
	}
	
	
	/**
	 * Delete from a single table based on parameters
	 * 
	 * @param string $table Table name (only one) to delete from
	 * @param array $params Delete selectors
	 * 
	 * @example Deleting a post with ID = 223
	 * 		base::delete( 'posts', array( 'id' = 223 ) );
	 * 
	 * @return int Number of rows affected/deleted
	 */
	protected static function delete( $table, $params ) {
		
		self::init();
		
		$sql	= self::_deleteStatement( $table, $params );
		$stmt	= self::$db->prepare( $sql );
		
		if ( $stmt->execute( $params ) ) {
			self::$qcount++;
			return $stmt->rowCount();
		}
		return 0;
	}
	
	
	/**
	 * Add parameters to conditional IN/NOT IN ( x,y,z ) query
	 */
	protected function _addParams( $t, &$values, &$params = array(), &$in = '' ) {
		
		$vc = count( $values );
		for ( $i = 0; $i < $vc; $i++ ) {
			$in			= $in . ":val_{$i},";
			$params["val_{$i}"]	= array( $values[$i], $t );
		}
		$in = rtrim( $in, ',' );
	}
	
	
	/**
	 * Prepares parameters for SELECT, UPDATE or INSERT SQL statements.
	 * 
	 * E.G. For INSERT
	 * :name, :email, :password etc...
	 * 
	 * For UPDATE or DELETE
	 * name = :name, email = :email, password = :password etc...
	 */
	protected static function _setParams( 
		$fields = array(), 
		$mode = 'select', 
		$table = '' 
	) {
		$columns = is_array( $fields ) ? 
				array_keys( $fields ) : 
				array_map( 'trim', explode( ',', $fields ) );
		
		switch( $mode ) {
			case 'select':
				return implode( ', ', $columns );
				
			case 'insert':
				return ':' . implode( ', :', $columns );
			
			case 'update':
			case 'delete':
				$v = array_map( 
					function( $field ) use ( $table ) {
						
						if ( empty( $field ) ) { 
							return '';
						}
						return "$field = :$field";
					}, $columns );
				return implode( ', ', $v );
		}
	}
	
	
	/**
	 * Prepares SQL INSERT query with parameters matching field names
	 * 
	 * @param string $table Table name
	 * @param string|array $fields A comma delimited string of fields or 
	 * 		an array
	 * 
	 * @example field => value pairs (the 'field' key will be extracted as 
	 * 		the parameter)
	 * 		_insertStatement(
	 * 			'posts', 
	 * 			array( 
	 * 				'title' => 'Test title', 
	 * 				'author' => 'Guest'
	 * 			) 
	 * 		);
	 * 
	 * @return string;
	 */
	protected static function _insertStatement( $table, $fields ) {
		
		$cols = self::_setParams( $fields, 'select', $table );
		$vals = self::_setParams( $fields, 'insert', $table );
		return	"INSERT INTO $table ( $cols ) VALUES ( $vals );";
	}
	
	
	/**
	 * Prepares sql UPDATE query with parameters matching field names
	 * 
	 * @param string $table Table name
	 * @param string|array $fields A single field or comma delimited string 
	 * 		of fields or an array
	 * 
	 * @example field => value pairs (the 'field' key will be extracted as 
	 * 		the parameter)
	 * 		_updateStatement(
	 * 			'posts', 
	 * 			array( 
	 * 				'title' => 'Changed title', 
	 * 				'author' => 'Edited Guest' 
	 * 			),
	 * 			array( 'id' => 223 )
	 * 		);
	 * @return string;
	 */
	protected static function _updateStatement( 
		$table, 
		$fields = null, 
		$cond = ''
	) {
		$params = self::_setParams( $fields, 'update', $table );
		$sql	= "UPDATE $table SET $params";
		
		if ( !empty( $cond ) ) {
			$sql .= " WHERE $cond";
		}
		
		return $sql . ';';
	}
	
	
	/**
	 * Prepares sql DELETE query with parameters matching field names
	 * 
	 * @param string $table Table name
	 * @param string|array $fields A comma delimited string of fields or an 
	 * 		array of field => value pairs (the 'field' key will be 
	 * 		extracted as the parameter)
	 * @return string;
	 */
	protected static function _deleteStatement( 
		$table, 
		$fields = null, 
		$limit = null
	) {
		
		$params	= self::_setParams( $fields, 'delete', $table );
		$sql	= "DELETE FROM $table WHERE ( $params )";
		
		
		/**
	 	 * Limit must consist of an integer and not start with a '0'
	 	 */
		if ( null !== $limit && preg_match('/^([1-9][0-9]?+){1,2}$/', 
			$limit ) ) {
			$sql .= " LIMIT $limit";
		}
		
		return $sql . ';';
	}
	
	
	/**
	 * Pagination offset calculator
	 * Hard limit of 300 set for page since we rarely browse that many 
	 * casually. That's what searching is for ( also reduces abuse )
	 * 
	 * @param int $page Currently requested index (starting from 1)
	 * @param int $limit Maximum number of records per page
	 * @return int Offset
	 */
	protected static function _offset( $page, $limit ) {
		
		$page	= ( isset( $page ) )? ( int ) $page - 1 : 0;
		$limit	= ( isset( $limit ) )? ( int ) $limit : 0;
		
		if ( empty( $page ) || $page < 0 || 
			empty( $limit ) || $limit < 0
		) {
			return 0; 
		}
		return ( $page > 300 ) ? 0 : $page * $limit;
	}
	
	
	/**
	 * Convert a unix timestamp a datetime-friendly timestamp
	 * 
	 * @param int $time Unix timestamp
	 * @return string 'Year-month-date Hour:minute:second' format
	 */
	protected static function _myTime( $time ) {
		return gmdate( 'Y-m-d H:i:s', $time );
	}
	
	
	/**
	 * Sets filter configuration ( pagination, limit, id etc... )
	 */
	public static function filterConfig( &$filter = array() ) {
		
		$filter['id']		= isset( $filter['id'] ) ? 
						$filter['id'] : 0;
						
		$filter['limit']	= isset( $filter['limit'] ) ? 
						$filter['limit'] : 1;
						
		$filter['page']		= isset( $filter['page'] ) ? 
						$filter['page'] : 1;
						
		$filter['search']	= isset( $filter['search'] ) ? 
						$filter['search'] : '';
		
		$offset			= self::_offset( 
						$filter['page'] , 
						$filter['limit']
					);
		if ( $offset > 0 ) {
			$filter['offset'] = $offset;
		}
	}
}

This class was built to help me with a bunch of other classes as well, so I added a quick filterConfig there for a lot of the default parameters I’d use, including for offset calculation for pagination.

Putting it all together, you might use something like this to add a new entry :

 // Make sure, your full DSN is set in php.ini
// under pdo.dsn.mywhacks = 'sqlite:data/hitlist.sqlite' or something
define( 'DBH', 'mywhacks' );

$whack = new \Models\Whack();
$whack->name = "Carlo";
$whack->method = "garrote in the car";
$whack->reason = "Fingered Sonny for the Barzini people.";

// Baptism is already over so why wait? 
$whack->save();

To change something :

$fredo = \Models\Whack::find( array( 'name' => 'Fredo' ) );
$fredo->method = "shot while fishing";
$fredo->reason = "Almost got Michael killed. Took sides against the family.";

$fredo->whacked_at = time(); // Mamma's dead now
$fredo->save();

To delete :

\Models\Whack::delete( array( 'name' => 'Clemenza' ) );

And that’s about it.

Forum schema

I’ve been working slowly, but steadily, on the forum lately and thought I’d put up the schema I’ve been testing against for the past few weeks. This is for SQLite since it’s very simple to setup, change things and if necessary, completely scrap and start over without having to mess with a DB server.

I’ve been using the SQLite Manager plugin for Firefox to make quick changes to the schema on the go. I’ll post snippets of the rest of the script in increments when I have time to clean up and maybe I’ll post the whole thing on GitHub for the alpha release.

Maybe this will serve as a good starting point for someone else looking for ideas as well.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	username VARCHAR NOT NULL UNIQUE, 
	password TEXT NOT NULL, 
	email VARCHAR NOT NULL UNIQUE, 
	token VARCHAR NOT NULL,
	bio TEXT, 
	pubkey TEXT, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	updated_at DATETIME NOT NULL , 
	status INTEGER NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX users_name_idx ON users (username, email);
CREATE INDEX users_activity_idx ON users (created_at, updated_at);
CREATE INDEX users_status_idx ON users (status);



DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	title VARCHAR,
	url VARCHAR,
	author VARCHAR NOT NULL, 
	user_id INTEGER NOT NULL DEFAULT 0, 
	summary TEXT NOT NULL, 
	body TEXT NULL, 
	reply_count INTEGER NOT NULL DEFAULT 0, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	updated_at DATETIME NOT NULL, 
	lastreply_at DATETIME NOT NULL, 
	status INTEGER NOT NULL  DEFAULT 0
);

CREATE INDEX posts_activity_idx ON posts (created_at, updated_at, lastreply_at);
CREATE INDEX posts_author_idx ON posts (author, user_id);
CREATE INDEX posts_url_idx ON posts (url) WHERE url IS NOT NULL;
CREATE INDEX posts_status_idx ON posts (status);



DROP TABLE IF EXISTS posts_family;
CREATE TABLE posts_family(
	parent_id INTEGER NOT NULL, 
	child_id INTEGER NOT NULL, 
	last_author VARCHAR NOT NULL, 
	last_id INTEGER NOT NULL, 
	lastreply_at DATETIME NOT NULL
);

CREATE INDEX posts_family_idx ON posts_family (parent_id, child_id);
CREATE INDEX posts_family_activity_idx ON posts_family 
	(last_author, last_id, lastreply_at);



DROP TABLE IF EXISTS posts_flags;
CREATE TABLE posts_flags(
	post_id INTEGER NOT NULL, 
	user_id INTEGER NOT NULL, 
	reason VARCHAR NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	status INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX posts_flags_idx ON posts_flags (post_id, user_id);
CREATE INDEX posts_flags_created_at_idx ON posts_flags (created_at);
CREATE INDEX posts_flags_status_idx ON posts_flags (status);



DROP TABLE IF EXISTS taxonomy;
CREATE TABLE taxonomy (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	label VARCHAR,
	term VARCHAR,
	post_count INTEGER NOT NULL DEFAULT 0, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	body TEXT NULL
);

CREATE INDEX taxonomy_created_at_idx ON taxonomy (created_at);
CREATE UNIQUE INDEX taxonomy_term_idx ON taxonomy (label, term);
CREATE INDEX taxonomy_count_idx ON taxonomy (post_count);



DROP TABLE IF EXISTS taxonomy_posts;
CREATE TABLE taxonomy_posts(
	taxonomy_id INTEGER NOT NULL, 
	post_id INTEGER NOT NULL
);

CREATE UNIQUE INDEX taxonomy_posts_idx ON taxonomy_posts (taxonomy_id, post_id);



DROP TABLE IF EXISTS pms;
CREATE TABLE pms (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	title VARCHAR,
	sender_id INTEGER NOT NULL, 
	receiver_id INTEGER NOT NULL, 
	body TEXT NULL, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	status INTEGER NOT NULL  DEFAULT 0
);

CREATE INDEX pms_contact_idx ON pms (sender_id, receiver_id);
CREATE INDEX pms_created_idx ON pms (created_at);
CREATE INDEX pms_status_idx ON pms (status);



DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions(
	id VARCHAR PRIMARY KEY, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	updated_at DATETIME NOT NULL,
	skey VARCHAR NOT NULL,
	data TEXT NOT NULL
);

CREATE INDEX sessions_activity_idx ON sessions (created_at, updated_at);



DROP TABLE IF EXISTS firewall;
CREATE TABLE firewall(
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	label VARCHAR,
	response VARCHAR,
	ip VARCHAR,
	ua VARCHAR,
	protocol VARCHAR,
	method VARCHAR,
	uri VARCHAR,
	headers TEXT NOT NULL, 
	request_at DATETIME NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX firewall_ip_idx ON firewall (ip, label);
CREATE INDEX firewall_response_idx ON firewall (response);
CREATE INDEX firewall_activity_idx ON firewall (request_at, created_at);



DROP TABLE IF EXISTS blocklist;
CREATE TABLE blocklist(
	ip VARCHAR PRIMARY KEY, 
	label VARCHAR,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	expires_at DATETIME NOT NULL
);

CREATE INDEX blocklist_label_idx ON blocklist (label);
CREATE INDEX blocklist_dates_idx ON blocklist (created_at, expires_at);



DROP TABLE IF EXISTS settings;
CREATE TABLE settings(
	id VARCHAR PRIMARY KEY, 
	data TEXT NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX settings_created_idx ON settings (created_at);



DROP TABLE IF EXISTS history;
CREATE TABLE history(
	post_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL  DEFAULT 0, 
	anon_id VARCHAR, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX history_idx ON history (post_id, user_id, anon_id);
CREATE INDEX history_created_idx ON history (created_at);



DROP TABLE IF EXISTS actions;
CREATE TABLE actions(
	post_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL  DEFAULT 0, 
	anon_id VARCHAR, 
	command VARCHAR, 
	auth_key VARCHAR, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	expires_at DATETIME NOT NULL
);

CREATE UNIQUE INDEX actions_idx ON actions (post_id, user_id, anon_id);
CREATE INDEX actions_auth_key_idx ON actions (auth_key);
CREATE INDEX actions_activity_idx ON actions (created_at, expires_at);

Discussion Forum Update (tables and classes)

A bit of a long post today to show the updated table schema for the discussion forum and a few of the classes. There are quite a few improvements from the last version of the table schema in that there is no longer a PostRelations table. This was only adding more complexity to the retrival and storage methods so I opted to use a simple ParentId field to take care of the relationships. Now there is also a LastActivity field in the Posts table so that when a new post is added, only this field needs to be modified. The ContentHash field stores a rolling hash of the plain text content so measuring the quality of the post for the threshold will be more consistent.

Forum database schema

The object models also got an overhaul.

Onward to those…

Forum Tables

Just finished deciding on the initial set of tables for the discussion forum.

I wanted to keep things as simple and as flexible as possible. In that regard, I tried to normalize as much as is practical and there is one table for both topics and replies and a seperate table defining Topic > Reply (Parent > Child) relationships. The tags are also assigned by the use of two tables; one for the tag itself and another specifying the relationship.

Since tags double as forums or categories, the tags also have a description provision. I wanted to make anyone browsing a particular tag feel like they’re on a forum page.

Clockwise : Posts table stores topics and replies, PostRelations define parent > child relationships, PostTags define categories, PostTagRelations specify which tags go with which topic

I also wanted to keep track of which authors created which tag. Authors are a unique table where each entry is per post since we want to enable anonymous posting. There is a MemberId field, however, that allows an author to be identified by a registered member profile. Following the same normalization pattern, there are PostAuthors and TagAuthors tables.

Author relationship tables

And, of course, the Members table. I’m still going to be using the MembershipProvider model, but with a few customizations. This table may change in the future, but it does what I need it to do for now.

Members table

And of course, the object models… You may notice from the namespace, I decided to call this project “Road”. Sufficiently vague to be interesting ;)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Road.Models
{
	public enum TopicStatus : byte
	{
		Open, Closed, Hidden, HiddenClosed, InTagPromoted,
		InTagPromotedClosed, Promoted, PromotedClosed
	};

	public enum TagStatus : byte
	{
		Open, Closed, NoAnon, AnonModerated, Moderated
	};

	public enum ReplyStatus : byte
	{
		Open, Hidden
	}

	public class Entity
	{
		public int Id { get; set; }
		public DateTime CreatedDate { get; set; }
		public DateTime LastModified { get; set; }
	}

	public class NamedEntity : Entity
	{
		public string Slug { get; set; }
		public string Name { get; set; }
		public string DisplayName { get; set; }
	}

	public class Creator : NamedEntity
	{
		public string Email { get; set; }
		public string IP { get; set; }
		public string Web { get; set; }
		public string Bio { get; set; }
		public string Avatar { get; set; }
	}

	public class PageEntity : NamedEntity
	{
		public Creator CreatedBy { get; set; }
		public string Summary { get; set; }
		public string Body { get; set; }
	}

	public class Tag : PageEntity
	{
	}

	public class Topic : PageEntity
	{
		public LazyList<Tag> Tags { get; set; }
		public PagedList<Reply> Replies { get; set; }
		public int ViewCount { get; set; }
		public int ReplyCount { get; set; }

		public float? Threshold { get; set; }
		public TopicStatus Status { get; set; }

		public Topic()
		{
			this.Id = 0;
			this.Status = TopicStatus.Open;
			this.ViewCount = 0;
			this.ReplyCount = 0;
			this.Threshold = 0;
		}
	}

	public class Reply : PageEntity
	{
		public int TopicId { get; set; }
		public float? Threshold { get; set; }
		public ReplyStatus Status { get; set; }

		public Reply()
		{
			this.Id = 0;
			Status = ReplyStatus.Open;
			this.Threshold = 0;
		}
	}
}

Note: The LazyList is a lazy loading helper class. There are several examples on the web if you Google the term so I haven’t decided which to use or if I’ll write my own. PagedList is an oldie, but goodie I’ve been using for quite some time. There are many examples of that on the web too.

“Threshold” on both Topic and Reply classes are quality measurements. I want to implement some sort of spam/quality filter that will set the threshold on each as they are entered into the database and allow the user to toggle which range to see. Anonymous users won’t be able to toggle the threshold so any search bots would also be spared any spam.

These few days, I’ve been running all over the place so once I settle down, next update on this, I’ll start posting some actual code.

Forum Core.class.php progress update

Just to show that I haven’t been twiddling my thumbs all this time, I thought I’d post what I have so far on the forum script I’ve been working on. Last Monday, I posted the updated database SQL.

Now there are 12 tables in all. I forgot to include the “usersgroups” table and now there’s a “floodcheck” table as well. Originally, I planned to use a purely cookie or session based check for flooding, but these can be easily spoofed. Flood checking will now take place in the database class.

I’ve written up the basics on Database.class.php (the parent class for all database abstraction), MySQL.class.php, config.php (I’ve finalised on the presets) and index.php, but this should give you an idea of where I’m going with this.

Note: The license is MIT, but this is only because Google Code doesn’t allow the ISC license or Public Domain dedications at this time. I plan to release a separate version of the code (functionally identical to this one) without any license and with my usual disclaimer.

WARNING: This code is highly experimental and will contain omissions, exceptions and egregious coding errors. This is just a progress update, so please feel free to treat it as such.

For some reason the sourcecode formatting puts the empty() function twice as, “emptyempty”. This is a WordPress formatting issue. The plain version doesn’t have the double entry.

/**
* Core class.
* Used for core forum functions, formatting and security.
*
* @author Jayanath Rodrigo
* @package Core
* @access public
* @version 0.1
* @license http://www.opensource.org/licenses/mit-license.php MIT License
*/

final class Core
{
	static private $instance;	// Singleton instance for this class
	private $cookies;		// Boolean to hold cookie capability (true/false)
	private $props;			// Presets array passed during object construction
	private $db;			// Database object passed during object construction
	
	
	/**
	* Core constructor. 
	* This is a private function. Use "getInstance" below to get a fresh object.
	*
	* @param array $p the presets in the core passed by reference.
	* @param object $d the database object (MySQL, SQLite et al) passed by reference.
	*/
	private function __construct(&$p, &$d)
	{
		$props = $p;
		$db = $d;
		$cookies = $this->cookiesEnabled();
	}
	
	
	/**
	* Singleton instance.
	*
	* @param array $p The presets in the core passed by reference.
	* @param object $d The database object (MySQL, SQLite et al) passed by reference.
	* @return object Core Singleton instance
	*/
	static function getInstance(&$p, &$d)
	{
		if(!isset(self::$instance))
			self::$instance = new Core($p, $d);
		
		return self::$instance;
	}
	
	
	
	/******************************************************************************
		Posting status and user input
	******************************************************************************/
	
	/**
	* Checks if user is currently browsing the index page.
	*
	* @return bool true If on the index page. Defaults to false.
	*/
	public function browsingIndex()
	{
		if(!isset($_GET['forum']) && !isset($_GET['topic']) && !isset($_GET['page']) && !isset($_GET['section']))
			return true;
			
		return false;
	}
	
	
	/**
	* Checks if user is currently browsing a specific forum.
	*
	* @return bool true If on a forum page. Defaults to false.
	*/
	public function browsingForum()
	{
		if(isset($_GET['forum']) && !isset($_GET['topic']) && !isset($_GET['section']))
			return true;
			
		return false;
	}
	
	
	/**
	* Checks if user is currently browsing a specific topic.
	*
	* @return bool true If on a topic page. Defaults to false.
	*/
	public function browsingTopic()
	{
		if(isset($_GET['forum']) && isset($_GET['topic']) && !isset($_GET['section']))
			return true;
			
		return false;
	}
	
	
	/**
	* Checks if user is currently browsing a specific section. I.E. Plugin module.
	*
	* @return bool true If on a section page. Defaults to false.
	*/
	public function browsingSection()
	{
		if(!isset($_GET['forum']) && !isset($_GET['topic']) && isset($_GET['section']))
			return true;
			
		return false;
	}
	
	
	/**
	* Gets the current page index.
	*
	* @return int $p Filtered output defaults to 1 (first page).
	*/
	public function currentPage()
	{
		$p = 1;
		if(isset($_GET['page']))
			$p = $this->getDefaultInt($_GET['page'], 1);
		
		return $p;
	}
	
	
	/**
	* Status: Posting a new forum topic.
	*
	* @return bool true If $_POST array key "forum" is present and "topic" is absent. Defaults to false.
	*/
	public function postingNewTopic()
	{
		if(isset($_POST['forum']) && !isset($_POST['topic']))
			return true;
		
		return false;
	}
	
	
	/**
	* Status: Posting a new topic reply.
	*
	* @return bool true If $_POST array keys "forum" and "topic" are present. Defaults to false.
	*/
	public function postingNewReply()
	{
		if(isset($_POST['forum']) && isset($_POST['topic']))
			return true;
		
		return false;
	}
	
	
	/**
	* Status: User is attempting to login.
	*
	* @return bool true If $_POST array keys "username" and "password" are present. Defaults to false.
	*/
	public function checkLogin()
	{
		if(isset($_POST['username']) && isset($_POST['password']))
			return true;
		
		return false;
	}
	
	
	/**
	* Status: User is attempting to login.
	*
	* @return bool true If $_GET array keys "logout" is set and the "user" session is present. Defaults to false.
	*/
	public function checkLogout()
	{
		if(isset($_GET['logout']) && !isset($_GET['login']) && isset($_SESSION['user']))
			return true;
		
		return false;
	}
	
	
	/**
	* Gets the currently requested forum ID. 
	* Priority is given to the $_POST "forum" key.
	*
	* @return int $f_id Filtered output defaults to 0 (no forum requested).
	*/
	public function postForumID()
	{
		$f_id = 0;
		if(isset($_POST['forum']))
			$f_id = $this->getDefaultInt($_POST['forum'], 0);
		elseif(isset($_GET['forum']))
			$f_id = $this->getDefaultInt($_GET['forum'], 0);
		
		return $f_id;
	}
	
	
	/**
	* Gets the currently requested topic ID. 
	* Priority is given to the $_POST "topic" key.
	*
	* @return int $t_id Filtered output defaults to 0 (no topic requested).
	*/
	public function postTopicID()
	{
		$t_id = 0;
		if(isset($_POST['topic']))
			$t_id = $this->getDefaultInt($_POST['topic'], 0);
		elseif(isset($_GET['topic']))
			$t_id = $this->getDefaultInt($_GET['topic'], 0);
		
		return $t_id;
	}
	
	
	/**
	* Gets the currently requested parent (topic or forum) ID. 
	* Priority is given to the $_POST "parent" key.
	*
	* @return int $p_id Filtered output defaults to 0 (no parent item requested).
	*/
	public function postParentID()
	{
		$p_id = 0;
		if(isset($_POST['parent']))
			$p_id = $this->getDefaultInt($_POST['parent'], 0);
		elseif(isset($_GET['parent']))
			$p_id = $this->getDefaultInt($_GET['parent'], 0);
		
		return $p_id;
	}
	
	
	/**
	* Gets the posted subject title form field data.
	*
	* @return string $_t Filtered output defaults to '' (blank string).
	*/
	public function postTitle()
	{
		$_t = '';
		if(isset($_POST['title']))
			$_t = $this->getDefaultString($_POST['title'], '');
		
		return $_t;
	}
	
	
	/**
	* Gets the posted content data (message body) form field data.
	*
	* @return string $_c Filtered output defaults to '' (blank string).
	*/
	public function postContent()
	{
		$_c = '';
		if(isset($_POST['content']))
			$_c = $this->getDefaultHtml($_POST['content'], '');
		
		return $_c;
	}
	
	
	/**
	* Gets the posted author name form field data
	*
	* @return string $_a Filtered output defaults to '' (blank string).
	*/
	public function postAuthor()
	{
		$_a = '';
		if(isset($_POST['author']))
			$_a = $this->getDefaultString($_POST['author'], '');
		
		return $_a;
	}
	
	
	/**
	* Gets the posted author email form field data.
	*
	* @return string $_e Filtered output defaults to '' (blank string).
	*/
	public function postEmail()
	{
		$_e = '';
		if(isset($_POST['email']))
			$_e = $this->getDefaultString($_POST['email'], '');
		
		return $_e;
	}
	
	
	/**
	* Gets a database compatible datetime stamp.
	*
	* @return date Current date time in "Year-Month-Date Hour:Minute:Second" format.
	*/
	public function postDate() {
		return date('Y-m-d H:i:s');
	}
	
	
	
	
	/******************************************************************************
		User status and identity
	******************************************************************************/
	
	
	/**
	* Gets user data into variables passed by reference. 
	*
	* @deprecated This function is deprecated in favor of the one with fewer parameters below it 
	* @see function getUserDataArray.
	*
	* @param int $userID user ID key. Defaults to 0 on error
	* @param string $user username. Defaults to '' on error
	* @param array $priv user privileges. Defaults to empty array on error
	* @param int $msgID message ID key. Defaults to 0 on error
	* @param string $exc error message text.
	*/
	public function getUserData(&$userID, &$user, &$priv, &$msgID, &$exc)
	{
		try
		{
			$raw = explode('::', $this->loggedInUser());
			list($userID, $user, $msgID) = explode(',' $raw[0]);
			$priv = $this->getPrivileges($raw[1]);
			
			// Filter everything
			$userID = $this->getDefaultInt($userID, 0);
			$user = $this->getDefaultString($user, '');
			$msgID = $this->getDefaultInt($msgID, 0);
		}
		catch (Exception $exc)
		{
			$userID = 0;
			$user = '';
			$priv = array();
			$msgID = 0;
		}
	}
	
	
	/**
	* Gets user data as an array.
	*
	* @param array $data user data (id, name, privileges, messageID). Defaults to error message on exception.
	* @return array|string Array with user data if successful or exception message as string on error.
	*/
	public function getUserDataArray()
	{
		$data = null;
		try
		{
			$raw = explode('::', $this->loggedInUser());
			
			// First part of "raw" (user info)
			list($userID, $user, $msgID) = explode(',' $raw[0]);
			
			$data = array(
				"id" => $userID,
				"username" => $this->getDefaultString($user, ''),
				"messageID" = $this->getDefaultInt($msgID, 0),
				"privileges" => $this->getPrivileges($raw[1])	// Second part of "raw" (privileges)
			);
		}
		catch (Exception $exc)
		{
			$data = $exc;
		}
		return $data;
	}
	
	
	/**
	* Saves user data into session and cookies.
	*
	* @deprecated This function is deprecated in favor of the one with fewer parameters below it.
	* @see function setUserDataArray
	*
	* @param int $userID Unique user key
	* @param string $name Username
	* @param array $priv Designated user parameters
	* @param int msgID Display message number
	*/ 
	public function setUserData(&$userID, &$user, &$msgID, &$priv)
	{
		$data = $this->encrypt($userID . ',' . $user . ',' . $msgID . '::' . implode('', $priv));
		
		// Destroy and restart the current session
		session_regenerate_id(true);
		
		$_SESSION['user'] = $data;
		
		if($cookies)
			setcookie("user", $data);
	}
	
	
	/**
	* Saves user data into session and cookie
	*
	* @param array $info Raw user data including id, name, message ID and privileges array
	*/
	public function setUserDataArray(&$info)
	{
		list($userID, $user, $msgID, $priv) = $info;
		
		$data = $this->encrypt($userID . ',' . $user . ',' . $msgID . '::' . implode('', $priv));
		
		// Destroy and restart the current session
		session_regenerate_id(true);
		
		$_SESSION['user'] = $data;
		
		if($cookies)
			setcookie("user", $data);
	}
	
	
	/**
	* Check if user can support cookies
	*
	* @return bool $cookies True if cookies are enabled by the browser, false if else
	*/
	public function cookiesEnabled()
	{
		if(empty($cookies))
		{
			setcookie("cookies", time());
			if(isset($_COOKIE["cookies"]))
			{
				$cookies = true;
				unset($_COOKIE["cookies"]);
			}
			else
			{
				$cookies = false;
			}
		}
		
		return $cookies;
	}
	
	
	/**
	* Check if user has a valid login session or saved cookie
	*
	* @return string $ret Formatted string pattern containing user ID, name, display name message ID and privilege flags
	*/
	public function loggedInUser()
	{
		// Prevent hijack
		if(!isset($_SESSION['user']))
			session_regenerate_id();
		
		$ret = null;
		
		if(isset($_SESSION['user']))
			$ret = $this->decrypt($_SESSION['user']);
		elseif(isset($_COOKIE["user"]))
			$ret = $this->decrypt($_COOKIE['user']);
		else
			$ret = '0,0,0::000000000000000000000';
		
		return $ret;	
	}
	
	
	/**
	* Authenticates user based on posted username and password form fields
	*
	* @return bool true If authentication went without problems. Defaults to false.
	*/
	public function login()
	{
		// Make sure the current user is logged out first
		if(isset($_SESSION['user']) || isset($_COOKIE['user']))
			$this->logout();
		
		if(isset($_POST['username']) && isset($_POST['password']))
		{
			$username = $this->getDefaultString($_POST['username'], '');
			$password = $this->getDefaultString($_POST['password'], '');
			
			// Invalid or empty form data
			if(empty($username) || empty($password))
				return false;
			
			// Connect to database and get user by name
			$data = $db->getLoginData($username);
			
			// User doesn't exist
			if(empty($data))
				return false;
			
			// Verify returned authentication data
			$encPass = $this->decrypt($data['pass']);
			$encSalt = $this->decrypt($data['salt']);
			
			// Password matches, login the user and return true
			if(str_replace($encSalt, '', $encPass) == $password)
			{
				$udata = array($data['id'], $username, 0, implode('', $data['priv']));
				$this->setUserDataArray($udata);
				
				return true;
			}
		}
		
		return false;
	}
	
	
	/**
	* Logout by removing 'user' session and cookie data.
	*/
	public function logout()
	{
		if(isset($_SESSION['user']))
			$_SESSION['user'] = null;
		
		if(isset($_COOKIE['user']))
			unset($_COOKIE['user']);
		
		// Just to be sure
		session_regenerate_id();
	}
	
	
	/**
	* Puts user privileges into an array
	*
	* @param string $priv User privilegs flags 21 characters in length (0/1)
	* @return array $privileges User privileges sorted into readable array
	*/
	public function getPrivileges(&$priv)
	{		
		// Initial array with no privileges
		$privileges = array(
			"CanReply" => 0,
			"CanCreateTopics" => 0,
			"CanUsePM" => 0,
			"CanReplyReadOnlyForums" => 0,
			"CanCreateTopicsReadOnlyForums" => 0,
			"CanEditOwnPosts" => 0,
			"CanDeleteOwnPosts" => 0,
			"CanEditOthersPosts" => 0,
			"CanDeleteOthersPosts" => 0,
			"CanMoveTopics" => 0,
			"CanLockTopics" => 0,
			"CanCreateForums"= 0,
			"CanEditForums" => 0,
			"CanDeleteForums" => 0,
			"CanCreateUsers"= 0,
			"CanBanUsers"= 0,
			"CanEditUsers"= 0,
			"CanDeleteUsers"= 0,
			"CanCreateGroups"= 0,
			"CanEditGroups"= 0,
			"CanDeleteGroups"= 0
		);
		
		// Get array from privileges string
		$pr = explode('', $priv);
		
		// If the initial array has appropriate number of flags
		if(count($pr) == 21) {
			
			// Get secured values back to privileges from source array
			$this->filterPushToArray("int", $pr, $privileges);
		}
		
		return $privileges;
	}
	
	
	/**
	* Utility function to reset default values in an array with filtered data.
	* Arrays are passed by reference.
	*
	* @param string $type Content type to return. Values "html", "string", "int".
	* @param int $count The number of items to iterate through.
	* @param array $source The raw array containing unfiltered data.
	* @param array $source Sorted destination array with filtered data.
	*/
	public function filterPushToArray($type, $count, &$source, &$dest)
	{
		$type = strtolower($type);
		
		$i = 0;
		
		// Iterate through each key and insert corresponding source array value
		foreach($dest as $key => $value)
		{
			// Get filtered content based on type
			switch($type)
			{
				case "html":
					$dest[$key] = $this->getDefaultHtml($source[i], $value);
					break;
				case "string":
					$dest[$key] = $this->getDefaultString($source[i], $value);
					break;
				case "int":
					$dest[$key] = $this->getDefaultInt($source[i], $value);
					break;
			}
			$i++;
		}
	}
	
	
	
	
	/******************************************************************************
		Formatting and presentation functions
	******************************************************************************/
	
	
	/**
	* Converts topic titles into usable URLs.
	* Will be used along with IDs.
	*
	* @param string $str The raw title
	* @return string $str The cleaned up title with special characters removed
	*/
	public function titleToSlug($str)
	{
		$str = preg_replace('/[~`!\@#\$\%\^\&\*\(\)\-\_\+={}\[\]\|:;\"\'\< \>\?,.\\\/\s+]/imu', '', $str);
		return strtolower($str);
	}
	
	
	/**
	* Check if CAPS are below a percent threshold.
	* This function is for improving readability by enforcing caps limits.
	*
	* @param string $str The posted content
	* @param int $limit The threshold limit in percent
	* @return bool true If the amout of CAPS matches are below the limit, else returns false.
	*/
	public function capsCheck($str, $limit)
	{
		$percent = round(($limit / 100) * strlen($str));
		preg_match_all('/[A-Z]/', $str, $matches);
		return ((count($matches[0]) >= $percent) ? true : false);
	}
	
	
	/**
	* Truncates strings to specified limit and return with ellipse
	* This function is for improving readability by limiting titles or summary lengths.
	*
	* @param string $str The posted content
	* @param int $limit The threshold limit in number of maximum characters. Default is 100.
	* @return string $str Formatted text limited to specified length and followed by '...' .
	*/
	public function stringTrunc($str, $limit = 100)
	{
		if (strlen($str) < = $limit)
			return $str;
		
		return substr_replace($string, '...', ($limit - strlen($str)));
	}
	
	
	
	
	/******************************************************************************
		Url authentication and flood limit
	******************************************************************************/
	
	
	/**
	* Verify a certain amount of time has passed between requests
	* Prevents abuse by checking for flood/DoS or forced entry attempts.
	*
	* @deprecated This function is deprecated in favor of a purely database based approach
	*
	* @param int $limit The threshold limit in time format.
	* @return bool true If sufficient time has passed between requests or false if it has not.
	*/
	public function floodLimit($limit)
	{
		$req = $this->postDate();
		
		if(isset($_SESSION['req']) || isset($_COOKIE['req']))
		{
			$sreq = (isset($_SESSION['req']))? strtotime($_SESSION['req']) : $req;
			$creq = (isset($_COOKIE['req']))? strtotime($_COOKIE['req']) : $req;
			
			// Compare and get the most recent time
			if($sreq < $creq)
				$sreq = $creq;
			
			return ($req < ($sreq + strtotime($limit)));
		}
		
		$_SESSION['req'] = $req;
		setcookie('req', $req);
		return false;
	}
	
	/**
	* Authenticate the requested URL as originating from the site homepage
	* Prevents outside redirect attempts by comparing the source and destination URLs (site_url)
	*
	* @param string $req The raw requested URL
	* @return bool true If the source and destination URLs match or false if they dont
	*/
	public function verifyURL($req)
	{
		if(strtolower($req) == substr(0, (strlen(strtolower($props['site_url'])) - 1)))
			return true;
		
		return false;
	}
	
	
	
	
	/*****************************************************************************************************
		Sanitized input
	*****************************************************************************************************/
	
	
	/**
	* Allow only text. HTML escaped if necessary, output unicode.
	* Prevents malicious or accidental HTML tags from being posted. Formatted strings only.
	*
	* @param string $v The content to be checked.
	* @return string $v|$d Verified and formatted content as $v or default, $d, if $v is invalid or empty.
	*/ 
	public function getDefaultString($v, $d)
	{
		$v = (empty($v) && (strtolower($v) != 'false') && ($v != '0'))? $d : $v;
		return htmlentities(iconv('UTF-8', 'UTF-8', $v), ENT_QUOTES, 'UTF-8');
	}
	
	/**
	* Allow only numbers, specifically, integers. No strings and/or HTML allowed.
	* Prevents any value other than an integer from being sent.
	*
	* @param string $v The content to be checked.
	* @return int $v|$d Verified $v if it is an integer or default, $d, if $v is invalid.
	*/ 
	public function getDefaultInt($v, $d)
	{
		return (!ctype_digit($v))? $d : $v;
	}
	
	
	/**
	* Allow only safe HTML (tags, attributes and attribute values).
	* Prevents any value other than those in the presets whitelists from being sent back.
	* 
	* This function needs more work!
	*
	* @param string $v The content to be checked.
	* @return string $v|$d The filtered and formatted allowed HTML as $v or default fallback value, $d, if $v is empty or cannot be parsed.
	*/
	public function getDefaultHtml($v, $d)
	{
		// Check if the content isn't empty or we skip all the filtering
		
		$v = (empty($v) && (strtolower($v) != 'false') && ($v != '0'))? $d : $v;
		
		// Content passed empty check
		if($v != $d)
		{
			// Get disallowed attributes from presets
			$badattr = implode('|', $props['ui_format_invalid_attributes']);
			
			// Get formatting whitelist
			$allowed = implode('|', $props['ui_format_tags']);
			
			// Remove all tags not in the formatting whitelist first, but leave the content inside
			$v = preg_replace("!<\s*?(" . $allowed  . ").*?>((.*?))?!ismu", "\3", $v); 
			
			
			// Iterate through each allowed tag
			foreach($props['ui_format_tags'] as $tag)
			{
				// Get matched tags in the content
				preg_match_all("< \s*?" . $tag[0] . "([^>]+)>!ismu", $v, $matches);
				
				// Allowed tags are present
				if(count($matches[0] > 0))
				{
					// Iterate through each match and filter attributes.
					for($i=0; $i < count($matches[0]); $i++)
					{
						// Get the specified attributes for this tag (The "([^>]+)" part in the above regular expression)
						$attr = explode(' ', $matches[1][$i]);
						
						// Filtered replacement
						$repl = "";
						
						// Each allowed attribute for this specific tag
						for($j=0; $j < count($attr); $j++)
						{
							$at = strtolower($attr[$j]);
							$atm = explode(',', $tag[1]);
							
							// Iterate through each found attribute to see if it's in the allowed list for this tag
							// Ignore bad attribute values (I.E. onclick inside "style" attribute)
							for($k=0; $k < count(atm); $k++)
							{
								if(preg_match("!^" . atm[k] . "=\"?[^(" . $badattr . ")]+\"?!ismu", $at))
									$repl .= " " . $at;
							}
						}
						
						// Replace the old tag and attributes with clean ones
						$v = str_replace($matches[0][$i], "<". $tag[0] . $repl . ">", $v);
					}
				}
			}
		}
		return $v;
	}
	
	
	
	
	/*****************************************************************************************************
		Security and encryption
	*****************************************************************************************************/
	
	/**
	* Generate a random string with a specific range (for salts etc...)
	*
	* @param int $min The minimum generated string length
	* @param int $max The maximum generated string length
	* @param bool $upper Uppercase (capital) letters are required. Defaults to true.
	* @param bool $special Special characters (punctuation etc...) are required. Defaults to true.
	* @return string $random Generated random string based on specifications.
	*/
	public function genRandom($min, $max, $upper = true, $spec = true)
	{
		$charset = "0123456789abcdefghijklmnopqrstuvwxyz";
		
		if($upper)
			$charset .= "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
		
		if($spec)
			$charset .= "~!@#$%^&*()_+`-={}|\\]?[\":;'><,./";
		
		$l = mt_rand($min, $max);
		for($i = 0; $i<$l; $i++) {
			$random .= $charset[(mt_rand(0, (strlen($charset)-1)))];
		}
		
		return $random;
	}
	
	/**
	* Encrypt in Rijndael 256 with optional Base64 encoding.
	* If Base64 is used during encryption, it must be used again for decryption.
	*
	* @param string $text The content to be encrypted.
	* @param bool $base64 Optional flag for base64 encoding the the final output. Defaults to true.
	* @return string Encrypted and optionally encoded string
	*/
	public function encrypt($text, $base64=true)
	{
		if($base64)
			return trim(base64_encode(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $salt, $text, MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND))));
		
		return trim(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $salt, $text, MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND)));
	}
	
	/**
	* Decrypt as encrypted above in Rijndael 256 with optional Base64 decoding (Default).
	* If Base64 is used to encrypt, it must be used again for decryption.
	*
	* @param string $text The content to be decrypted.
	* @param bool $base64 Optional flag for base64 decoding the input first. Defaults to true.
	* @return string Decrypted content
	*/
	public function decrypt($text, $base64=true)
	{
		if($base64)
			return trim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $salt, base64_decode($text), MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND)));
		
		return trim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $salt, $text, MCRYPT_MODE_ECB, mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB), MCRYPT_RAND)));
	}
	
	
	
	
	/*****************************************************************************************************
		Software downloading and updating
	*****************************************************************************************************/
	
	/**
	* Download a specified file to specified destination.
	* Used for updating the software or getting special content from a remote server.
	* If PHP has been compiled with cURL, it will use that. If not, it will try fopen.
	* 
	* @param string $url Download source location
	* @param string $dest Download destination (must be a writable folder)
	* @return bool $ret True If everything went OK or false if something went wrong.
	*/
	public function getFile($url, $dest)
	{
		$ret = false;
		$out = '';
		try
		{
			if(function_exists('curl_init'))
			{
				// If cURL exists, we can use it
				
				$file = curl_init($url);
				curl_setopt($file, CURLOPT_CONNECTTIMEOUT, 3);
				curl_setopt($file, CURLOPT_RETURNTRANSFER, true);
				curl_setopt($file, CURLOPT_BINARYTRANSFER, true);
				
				$out = curl_exec($file);
				
				// After everything is read, close cURL
				curl_close($file);
				
				// Empty if something went wrong
				if(empty($out))
					$ret = false;
				
				$fo = fopen($dest, 'w');
				fwrite($fo, $file);
				fclose($fo);
			}
			else
			{
				// If cURL doesn't exist, we have to stick to fopen
				
				$file = fopen($url, 'rb');
				
				// Buffer the read file
				while(!feof($file))
				{
					$out .= fread($file, 8192);
				}
				
				// Empty if something went wrong
				if(empty($out))
					$ret = false;
				
				fclose($file);
				
				$fo = fopen($dest, 'w');
				fwrite($fo, $out);
				fclose($fo);
			}
			
			// If the newly created file exists, return true
			if(file_exists($dest))
				$ret = true;
		}
		catch
		{
			// If something went wrong, return false
			$ret = false;
		}
		
		return $ret;
	}
	
	/**
	* Extract downloaded files to specified directories.
	* Obviously incomplete...
	*/
	public function pkgExtract($file, $dest)
	{
		if(function_exists('zip_open'))
		{
			// Set working path
			$path = $props['site_upload_directory'] . 'tmp';
			
			// Extract the file
			$pkg = zip_open($path, $file);
			
			// Opening successful
			if(is_resource($pkg))
			{
				while($item = zip_read($pkg))
				{
					$cpath = $path . dirname(zip_entry_name($item));
					$cname = $path . zip_entry_name($item);
				}
			}
		}
	}
}