diff options
| author | Andreas Fischer <bantu@phpbb.com> | 2013-03-21 23:04:17 +0100 |
|---|---|---|
| committer | Andreas Fischer <bantu@phpbb.com> | 2013-03-21 23:12:31 +0100 |
| commit | 16a60253721330323ae201032f0b852697ce2a00 (patch) | |
| tree | 40d49af492a555950fee122f82932199904ffe1b /phpBB/includes | |
| parent | 8b464e87f0a41422dddaa647ac42ab9c7950bb4b (diff) | |
| download | forums-16a60253721330323ae201032f0b852697ce2a00.tar forums-16a60253721330323ae201032f0b852697ce2a00.tar.gz forums-16a60253721330323ae201032f0b852697ce2a00.tar.bz2 forums-16a60253721330323ae201032f0b852697ce2a00.tar.xz forums-16a60253721330323ae201032f0b852697ce2a00.zip | |
[ticket/11469] Add SQL insert buffer allowing easier handling of multi inserts.
1. Tries to prevent going over max packet size by flushing to the database
after a certain number of rows have been added.
2. Because of 1., it is less likely to reach a connection timeout when
inserting a huge number of rows.
3. By flushing the buffer when a certain size is reached, memory usage should
be lower compared to building the whole insert row set first.
PHPBB3-11469
Diffstat (limited to 'phpBB/includes')
| -rw-r--r-- | phpBB/includes/db/sql_insert_buffer.php | 111 |
1 files changed, 111 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..8d4b03ef53 --- /dev/null +++ b/phpBB/includes/db/sql_insert_buffer.php @@ -0,0 +1,111 @@ +<?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. +* +* @package dbal +*/ +class phpbb_db_sql_insert_buffer +{ + /** @var phpbb_db_driver */ + protected $db; + + /** @var bool */ + protected $db_supports_multi_insert; + + /** @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->db_supports_multi_insert = $db->multi_insert; + $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 null + */ + public function insert(array $row) + { + if (!$this->db_supports_multi_insert) + { + $this->db->sql_multi_insert($this->table_name, array($row)); + } + + $this->buffer[] = $row; + + if (sizeof($this->buffer) >= $this->max_buffered_rows) + { + $this->flush(); + } + } + + /** + * 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 null + */ + public function insert_all(array $rows) + { + foreach ($rows as $row) + { + $this->insert($row); + } + } + + /** + * Flushes the buffer content to the DB and clears the buffer. + * + * @return null + */ + public function flush() + { + if (!empty($this->buffer)) + { + $this->db->sql_multi_insert($this->table_name, $this->buffer); + $this->buffer = array(); + } + } +} |