#!/usr/bin/php
<?php

if (!@include_once(dirname(dirname(__FILE__)).'/application/bootstrap_cli.php')) {
	echo "Please create a 'application/bootstrap-cli.php' file  to setup the include path and error handling\n";
	exit(-1);
}
$to_file = true;
if (isset($argv[1])) {
	if (in_array($argv[1], array('to-file', 'to-lob'))) {
		$to_file = $argv[1]=='to-file';
	} else {
		print "\nPlease use ".basename(__FILE__)." [to-file|to-lob]\n";
		print " - to-file (default): Will store any lobdata as a file\n";
		print " - to-lob           : Will store any file lobdata as a lobdata only\n\n";
		die();
	}
}
print "Updating Lob Fields:\n";


$db = &Objlib::GET(Objlib::TYPE_DB);
if (!isset($db->lob_data_path)) {
	die("The databases lob_data_path must be set\n");
}
require_once 'PHPOF2/DBTable.php';
// Go through all tables
$res = $db->query('show tables');
while ($row=$res->fetchRow()) {
	$table = new PHPOF2_DBTable($db, $row[0]);
	echo "Processing Table: $table->name\n";
	$table_row = $table->createRowObject();
	
	//Process Lob fields
	foreach ($table->getFieldListLOB() as $lob_field) {
		$non_lob_fields = $table->getPrimaryKeys()+$table->getFieldListNormal();
		
		$sql = "SELECT " . implode(',', $non_lob_fields) . " FROM $table->name WHERE $lob_field IS NOT NULL AND SUBSTRING(CONVERT($lob_field  USING utf8), 1, ".strlen(PHPOF2_DBRow::LOB_FILE_MARKER).")".($to_file?'!=':'=').$db->quote(PHPOF2_DBRow::LOB_FILE_MARKER);
		$lob_rows = $db->query($sql);
		echo "- Lob Field: $lob_field, Rows to process(".$lob_rows->numRows() . ")\n";
		while ($lob_rows->fetchInToObject($table_row)) {
			$lob_data = $table_row->getLobData($lob_field);
			$table_row->storeLobData($lob_data, $lob_field, !$to_file);
		}
	}
}

--- DB.php.orig	2009-09-14 06:49:32.000000000 +0100
+++ DB.php	2009-09-14 11:46:08.000000000 +0100
@@ -48,7 +48,7 @@
 	{
 		// Options that are specific to PHPOF2_DB and are not to be passed to MDB2
 		// (MDB2 throws errors if it gets options it doesn't know about)
-		$private_option_list = array('audit_driver', 'audit_driver_options');
+		$private_option_list = array('audit_driver', 'audit_driver_options', 'lob_data_path');
 		$private_options = array();
 		if (is_array($options)) {
 			foreach ($options as $key => $val) {
@@ -81,6 +81,11 @@
 		$mdb2 = parent::connect( $dsn, $options );
 		if (PEAR::isError($mdb2)) {
     		throw new Exception($mdb2->getMessage());
+		}		
+		
+		// Enable lob storge
+		if (isset($private_options['lob_data_path'])) {
+			$mdb2->lob_data_path = $private_options['lob_data_path'];
 		}
 		
 		// Enable audit driver if required

--- DBRow.php.orig	2009-09-14 06:49:42.000000000 +0100
+++ DBRow.php	2009-09-14 13:34:51.000000000 +0100
@@ -51,6 +51,7 @@
 	const LOB_GET = 'lob_get';
 	const LOB_CHECKONLY = 'lob_check';
 
+	const LOB_FILE_MARKER = '!#FILE#!:';
 	const COMPKEY_SEPARATOR = '#!#'; // The separator used when serializing composite keys
 
 	// for move();
@@ -346,7 +347,7 @@
 	 * @return mixed
 	*/
 	function getLOBData ($column_name, $query=PHPOF2_DBRow::LOB_GET)
-	{
+	{	
 		$sql = "SELECT $column_name".
 				 ' FROM '.$this->table->name.
 				 ' WHERE '.$this->getPrikeyWhereClause();
@@ -360,7 +361,23 @@
 					return true;
 				}
 			} else {
-				return call_user_func(array($this->driver, 'retrieveLOBData'), $this, $column_name, $resultarray);
+				$data = call_user_func(array($this->driver, 'retrieveLOBData'), $this, $column_name, $resultarray);
+
+				//Check for a file marker
+				if (substr($data, 0, strlen(self::LOB_FILE_MARKER))===self::LOB_FILE_MARKER) {
+					if (!isset($this->db->lob_data_path)) {
+						throw new Exception("DB File can not be found 'lob_data_path' option not set.");
+					}
+					$file_name = $this->db->lob_data_path . DIRECTORY_SEPARATOR . substr($data, strlen(self::LOB_FILE_MARKER));
+
+					if (!is_file($file_name)) {
+						throw new Exception("File $file_name does not exist.");
+					} elseif(!is_readable($file_name)) {
+						throw new Exception("File $file_name is not readable.");
+					}
+					$data = file_get_contents($file_name);
+				}
+				return $data;
 			}
 		} else {
 			return false;
@@ -393,19 +410,75 @@
 	 * @param $data_column string The name of the column which will receive the binary data from the uploaded file
 	 * @return bool
 	 */
-	public function storeLOBData(&$data, $data_column)
+	public function storeLOBData(&$data, $data_column, $force_db_store=false)
 	{
 		if (!$this->primaryKeyFilled()) {
 			throw new Exception('DBRow::storeLOBData() called on object which has not yet been stored in the database');
 			return false;
 		}
 		
-		call_user_func(array($this->driver, 'storeLOBDataFromBuffer'), $this, $data_column, $data);
+		//Check for a file marker
+		if (isset($this->db->lob_data_path)) {
+			// Remove old if force store
+			if ($force_db_store) {
+				$current_file_name = $this->getCurrentLobDataStorageFile($data_column);
+				if ($current_file_name!==false) {
+					$current_file_name = $this->db->lob_data_path . DIRECTORY_SEPARATOR . $current_file_name;
+					if (file_exists($current_file_name)) {
+						unlink($current_file_name);
+					}
+				}
+				call_user_func(array($this->driver, 'storeLOBDataFromBuffer'), $this, $data_column, $data);
+			// Store the lob data as a file
+			} else {
+				$new_file_name = false;
+				if (is_dir($this->db->lob_data_path) && is_writable($this->db->lob_data_path)) {
+					// name the new file
+					$new_file_name = md5($this->table->name . $this->serialisePriKey() . $data_column);
+				
+					$current_file_name = $this->getCurrentLobDataStorageFile($data_column);
+					
+					// Check if the key has changed if so remove old file
+					if ($current_file_name!==false && $new_file_name!=$current_file_name) {
+						$current_file_name = $this->db->lob_data_path . DIRECTORY_SEPARATOR . $current_file_name;
+						if (file_exists($current_file_name)) {
+							unlink($current_file_name);
+						}
+					}
+					$new_file_name = $this->db->lob_data_path . DIRECTORY_SEPARATOR . $new_file_name;
+				}
+				if (!file_put_contents($new_file_name, $data)) {
+					throw new Exception("File $new_file_name is not writable.");
+				} else {
+					// limit assess to the new file
+					chmod($new_file_name, 0600);
+				}
+				$db_data = self::LOB_FILE_MARKER . basename($new_file_name);
+				//Store the file name and markers
+				call_user_func(array($this->driver, 'storeLOBDataFromBuffer'), $this, $data_column, $db_data);
+			}
+		} else {
+			call_user_func(array($this->driver, 'storeLOBDataFromBuffer'), $this, $data_column, $data);
+		}
 		$this->$data_column = PHPOF2_DB::LOB_PLACEHOLDER;
 		
 		return true;
 	}
 	
+	private function getCurrentLobDataStorageFile ($data_column)
+	{
+		$sql = "SELECT $data_column".
+		 ' FROM '.$this->table->name.
+		 ' WHERE '.$this->getPrikeyWhereClause() .
+		 ' AND SUBSTRING(CONVERT(' . $data_column . ' USING utf8), 1, '.strlen(self::LOB_FILE_MARKER).')='. $this->db->quote(self::LOB_FILE_MARKER);
+		$res = $this->db->query ($sql);
+		if ($res->numRows() > 0) {
+			$resultarray = $res->fetchRow(MDB2_FETCHMODE_ASSOC);
+			return substr($resultarray[$data_column], strlen(self::LOB_FILE_MARKER));
+		}
+		return false;
+	}
+	
 	/**
 	 * Fill the properties of current object from an associative array, where
 	 * the array contains "column name" => "column content" pairs
@@ -550,6 +623,13 @@
 			// Handle LOB fields
 			foreach ($this->fields_lob as $field) {
 				if ($this->$field == PHPOF2_DB::LOB_DELETEPENDING) {
+					$file_name = $this->getCurrentLobDataStorageFile($field);
+					if ($file_name!==false && isset($this->db->lob_data_path)) {
+						$file_name = $this->db->lob_data_path . DIRECTORY_SEPARATOR . $file_name; 
+						if (file_exists($file_name)) {
+							unlink($file_name);
+						}
+					}
 					call_user_func(array($this->driver, 'removeLOBData'), $this, $field);
 					$this->$field = null;
 				}
@@ -610,6 +690,13 @@
 				// Remove any binary data from columns in the row
 				if (count($this->fields_lob) > 0) {
 					foreach ($this->fields_lob as $field) {
+						$file_name = $this->getCurrentLobDataStorageFile($field);
+						if ($file_name!==false && isset($this->db->lob_data_path)) {
+							$file_name = $this->db->lob_data_path . DIRECTORY_SEPARATOR . $file_name; 
+							if (file_exists($file_name)) {
+								unlink($file_name);
+							}
+						}
 						call_user_func(array($this->driver, 'removeLOBData'), $this, $field);
 					}
 				}