2 files changed, 266 insertions, 0 deletions
diff --git a/phpBB/includes/db/sql_insert_buffer.php b/phpBB/includes/db/sql_insert_buffer.php
new file mode 100644
index 0000000000..c18f908429
--- /dev/null
+++ b/phpBB/includes/db/sql_insert_buffer.php
@@ -0,0 +1,150 @@
+<?php
+/**
+*
+* @package dbal
+* @copyright (c) 2013 phpBB Group
+* @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License v2
+*
+*/
+
+/**
+* @ignore
+*/
+if (!defined('IN_PHPBB'))
+{
+	exit;
+}
+
+/**
+* Collects rows for insert into a database until the buffer size is reached.
+* Then flushes the buffer to the database and starts over again.
+*
+* Benefits over collecting a (possibly huge) insert array and then using
+* $db->sql_multi_insert() include:
+*
+*  - Going over max packet size of the database connection is usually prevented
+*    because the data is submitted in batches.
+*
+*  - Reaching database connection timeout is usually prevented because
+*    submission of batches talks to the database every now and then.
+*
+*  - Usage of less PHP memory because data no longer needed is discarded on
+*    buffer flush.
+*
+* Attention:
+* Please note that users of this class have to call flush() to flush the
+* remaining rows to the database after their batch insert operation is
+* finished.
+*
+* Usage:
+* <code>
+*	$buffer = new phpbb_db_sql_insert_buffer($db, 'test_table', 1234);
+*
+*	while (do_stuff())
+*	{
+*		$buffer->insert(array(
+*			'column1' => 'value1',
+*			'column2' => 'value2',
+*		));
+*	}
+*
+*	$buffer->flush();
+* </code>
+*
+* @package dbal
+*/
+class phpbb_db_sql_insert_buffer
+{
+	/** @var phpbb_db_driver */
+	protected $db;
+
+	/** @var string */
+	protected $table_name;
+
+	/** @var int */
+	protected $max_buffered_rows;
+
+	/** @var array */
+	protected $buffer = array();
+
+	/**
+	* @param phpbb_db_driver $db
+	* @param string          $table_name
+	* @param int             $max_buffered_rows
+	*/
+	public function __construct(phpbb_db_driver $db, $table_name, $max_buffered_rows = 500)
+	{
+		$this->db = $db;
+		$this->table_name = $table_name;
+		$this->max_buffered_rows = $max_buffered_rows;
+	}
+
+	/**
+	* Inserts a single row into the buffer if multi insert is supported by the
+	* database (otherwise an insert query is sent immediately). Then flushes
+	* the buffer if the number of rows in the buffer is now greater than or
+	* equal to $max_buffered_rows.
+	*
+	* @param array $row
+	*
+	* @return bool		True when some data was flushed to the database.
+	*					False otherwise.
+	*/
+	public function insert(array $row)
+	{
+		$this->buffer[] = $row;
+
+		// Flush buffer if it is full or when DB does not support multi inserts.
+		// In the later case, the buffer will always only contain one row.
+		if (!$this->db->multi_insert || sizeof($this->buffer) >= $this->max_buffered_rows)
+		{
+			return $this->flush();
+		}
+
+		return false;
+	}
+
+	/**
+	* Inserts a row set, i.e. an array of rows, by calling insert().
+	*
+	* Please note that it is in most cases better to use insert() instead of
+	* first building a huge rowset. Or at least sizeof($rows) should be kept
+	* small.
+	*
+	* @param array $rows 
+	*
+	* @return bool		True when some data was flushed to the database.
+	*					False otherwise.
+	*/
+	public function insert_all(array $rows)
+	{
+		// Using bitwise |= because PHP does not have logical ||=
+		$result = 0;
+
+		foreach ($rows as $row)
+		{
+			$result |= (int) $this->insert($row);
+		}
+
+		return (bool) $result;
+	}
+
+	/**
+	* Flushes the buffer content to the DB and clears the buffer.
+	*
+	* @return bool		True when some data was flushed to the database.
+	*					False otherwise.
+	*/
+	public function flush()
+	{
+		if (!empty($this->buffer))
+		{
+			$this->db->sql_multi_insert($this->table_name, $this->buffer);
+			$this->buffer = array();
+
+			return true;
+		}
+
+		return false;
+	}
+}
diff --git a/tests/dbal/sql_insert_buffer_test.php b/tests/dbal/sql_insert_buffer_test.php
new file mode 100644
index 0000000000..45339a6b50
--- /dev/null
+++ b/tests/dbal/sql_insert_buffer_test.php
@@ -0,0 +1,116 @@
+<?php
+/**
+*
+* @package testing
+* @copyright (c) 2013 phpBB Group
+* @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License v2
+*
+*/
+
+class phpbb_dbal_sql_insert_buffer_test extends phpbb_database_test_case
+{
+	protected $db;
+	protected $buffer;
+
+	public function setUp()
+	{
+		parent::setUp();
+
+		$this->db = $this->new_dbal();
+		$this->buffer = new phpbb_db_sql_insert_buffer($this->db, 'phpbb_config', 2);
+		$this->assert_config_count(2);
+	}
+
+	public function getDataSet()
+	{
+		return $this->createXMLDataSet(dirname(__FILE__) . '/fixtures/config.xml');
+	}
+
+	public function test_multi_insert_disabled_insert_and_flush()
+	{
+		$this->db->multi_insert = false;
+		$this->assertTrue($this->buffer->insert($this->get_row(1)));
+		$this->assert_config_count(3);
+		$this->assertFalse($this->buffer->flush());
+		$this->assert_config_count(3);
+	}
+
+	public function test_multi_insert_enabled_insert_and_flush()
+	{
+		$this->check_multi_insert_support();
+		$this->assertFalse($this->buffer->insert($this->get_row(1)));
+		$this->assert_config_count(2);
+		$this->assertTrue($this->buffer->flush());
+		$this->assert_config_count(3);
+	}
+
+	public function test_multi_insert_disabled_insert_with_flush()
+	{
+		$this->db->multi_insert = false;
+		$this->assertTrue($this->buffer->insert($this->get_row(1)));
+		$this->assert_config_count(3);
+		$this->assertTrue($this->buffer->insert($this->get_row(2)));
+		$this->assert_config_count(4);
+	}
+
+	public function test_multi_insert_enabled_insert_with_flush()
+	{
+		$this->check_multi_insert_support();
+		$this->assertFalse($this->buffer->insert($this->get_row(1)));
+		$this->assert_config_count(2);
+		$this->assertTrue($this->buffer->insert($this->get_row(2)));
+		$this->assert_config_count(4);
+	}
+
+	public function test_multi_insert_disabled_insert_all_and_flush()
+	{
+		$this->db->multi_insert = false;
+		$this->assertTrue($this->buffer->insert_all($this->get_rows(3)));
+		$this->assert_config_count(5);
+	}
+
+	public function test_multi_insert_enabled_insert_all_and_flush()
+	{
+		$this->check_multi_insert_support();
+		$this->assertTrue($this->buffer->insert_all($this->get_rows(3)));
+		$this->assert_config_count(4);
+		$this->assertTrue($this->buffer->flush());
+		$this->assert_config_count(5);
+	}
+
+	protected function assert_config_count($num_configs)
+	{
+		$sql = 'SELECT COUNT(*) AS num_configs
+			FROM phpbb_config';
+		$result = $this->db->sql_query($sql);
+		$this->assertEquals($num_configs, $this->db->sql_fetchfield('num_configs'));
+		$this->db->sql_freeresult($result);
+	}
+
+	protected function check_multi_insert_support()
+	{
+		if (!$this->db->multi_insert)
+		{
+			$this->markTestSkipped('Database does not support multi_insert');
+		}
+	}
+
+	protected function get_row($rownum)
+	{
+		return array(
+			'config_name'	=> "name$rownum",
+			'config_value'	=> "value$rownum",
+			'is_dynamic'	=> '0',
+		);
+	}
+
+	protected function get_rows($n)
+	{
+		$result = array();
+		for ($i = 0; $i < $n; ++$i)
+		{
+			$result[] = $this->get_row($i);
+		}
+		return $result;
+	}
+}