1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
<?php
/**
*
* This file is part of the phpBB Forum Software package.
*
* @copyright (c) phpBB Limited <https://www.phpbb.com>
* @license GNU General Public License, version 2 (GPL-2.0)
*
* For full copyright and license information, please see
* the docs/CREDITS.txt file.
*
*/
namespace phpbb\db;
/**
* 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>
*/
class sql_insert_buffer
{
/** @var \phpbb\db\driver\driver_interface */
protected $db;
/** @var string */
protected $table_name;
/** @var int */
protected $max_buffered_rows;
/** @var array */
protected $buffer = array();
/**
* @param \phpbb\db\driver\driver_interface $db
* @param string $table_name
* @param int $max_buffered_rows
*/
public function __construct(\phpbb\db\driver\driver_interface $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;
}
}
|
