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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
|
<?php
/**
* Piwik - free/libre analytics platform
*
* @link https://matomo.org
* @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
*/
namespace Piwik\Updater\Migration\Db;
use Piwik\Common;
use Piwik\Container\StaticContainer;
/**
* Provides database migrations.
*
* @api
*/
class Factory
{
/**
* @var \DI\Container
*/
private $container;
/**
* @ignore
*/
public function __construct()
{
$this->container = StaticContainer::getContainer();
}
/**
* Performs a custom SQL query during the update.
*
* Example:
* $factory->sql("DELETE * FROM table_name WHERE plugin_name = 'MyPluginName'");
*
* @param string $sql The SQL query that should be executed. Make sure to prefix a table name via
* {@link Piwik\Commin::prefixTable()}.
* @param int|int[] $errorCodesToIgnore Any given MySQL server error code will be ignored. For a list of all
* possible error codes have a look at {@link \Piwik\Updater\Migration\Db}.
* If no error should be ignored use an empty array or `false`.
* @return Sql
*/
public function sql($sql, $errorCodesToIgnore = array())
{
if ($errorCodesToIgnore === false) {
$errorCodesToIgnore = array();
}
return $this->container->make('Piwik\Updater\Migration\Db\Sql', array(
'sql' => $sql, 'errorCodesToIgnore' => $errorCodesToIgnore
));
}
/**
* Performs a custom SQL query that uses bound parameters during the update.
*
* You can replace values with a question mark and then pass the actual value via `$bind` for better security.
*
* Example:
* $factory->boundSql('DELETE * FROM table_name WHERE idsite = ?, array($idSite = 1));
*
* @param string $sql The SQL query that should be executed. Make sure to prefix a table name via
* {@link Piwik\Commin::prefixTable()}.
* @param array $bind An array of values that need to be replaced with the question marks in the SQL query.
* @param int|int[] $errorCodesToIgnore Any given MySQL server error code will be ignored. For a list of all
* possible error codes have a look at {@link \Piwik\Updater\Migration\Db}.
* If no error should be ignored use `false`.
* @return BoundSql
*/
public function boundSql($sql, $bind, $errorCodesToIgnore = array())
{
if ($errorCodesToIgnore === false) {
$errorCodesToIgnore = array();
}
return $this->container->make('Piwik\Updater\Migration\Db\BoundSql', array(
'sql' => $sql, 'errorCodesToIgnore' => $errorCodesToIgnore, 'bind' => $bind
));
}
/**
* Creates a new database table.
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param array $columnNames An array of column names and their type they should use. For example:
* array('column_name_1' => 'VARCHAR(200) NOT NULL', 'column_name_2' => 'INT(10) DEFAULT 0')
* @param string|string[] $primaryKey Optional. One or multiple columns that shall define the primary key.
* @return CreateTable
*/
public function createTable($table, $columnNames, $primaryKey = array())
{
$table = $this->prefixTable($table);
if (!empty($primaryKey) && !is_array($primaryKey)) {
$primaryKey = array($primaryKey);
}
return $this->container->make('Piwik\Updater\Migration\Db\CreateTable', array(
'table' => $table, 'columnNames' => $columnNames, 'primaryKey' => $primaryKey
));
}
/**
* Drops an existing database table.
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @return DropTable
*/
public function dropTable($table)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\DropTable', array(
'table' => $table
));
}
/**
* Adds a new database table column to an existing table.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param string $columnName The name of the column that shall be added, eg 'my_column_name'.
* @param string $columnType The column type it should have, eg 'VARCHAR(200) NOT NULL'.
* @param string|null $placeColumnAfter If specified, the added column will be added after this specified column
* name. If you specify a column be sure it actually exists and can be added
* after this column.
* @return AddColumn
*/
public function addColumn($table, $columnName, $columnType, $placeColumnAfter = null)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\AddColumn', array(
'table' => $table, 'columnName' => $columnName, 'columnType' => $columnType, 'placeColumnAfter' => $placeColumnAfter
));
}
/**
* Adds multiple new database table columns to an existing table at once.
*
* Adding multiple columns at the same time can lead to performance improvements compared to adding each new column
* separately.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param array $columns An array of column name to column type pairs,
* eg array('my_column_name' => 'VARCHAR(200) NOT NULL', 'column2' => '...')
* @param string|null $placeColumnAfter If specified, the first added column will be added after this specified column
* name. All following columns will be added after the previous specified in
* $columns. If you specify a column be sure it actually exists and can be added
* after this column.
* @return AddColumns
*/
public function addColumns($table, $columns, $placeColumnAfter = null)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\AddColumns', array(
'table' => $table, 'columns' => $columns, 'placeColumnAfter' => $placeColumnAfter
));
}
/**
* Drops an existing database table column.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param string $columnName The name of the column that shall be dropped, eg 'my_column_name'.
* @return DropColumn
*/
public function dropColumn($table, $columnName)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\DropColumn', array(
'table' => $table, 'columnName' => $columnName
));
}
/**
* Changes the column name and column type of an existing database table column.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param string $oldColumnName The current name of the column that shall be renamed/changed, eg 'column_name'.
* @param string $newColumnName The new name of the column, eg 'new_column_name'.
* @param string $columnType The updated type the new column should have, eg 'VARCHAR(200) NOT NULL'.
*
* @return ChangeColumn
*/
public function changeColumn($table, $oldColumnName, $newColumnName, $columnType)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\ChangeColumn', array(
'table' => $table, 'oldColumnName' => $oldColumnName,
'newColumnName' => $newColumnName, 'columnType' => $columnType
));
}
/**
* Changes the type of an existing database table column.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param string $columnName The name of the column that shall be changed, eg 'my_column_name'.
* @param string $columnType The updated type the column should have, eg 'VARCHAR(200) NOT NULL'.
*
* @return ChangeColumnType
*/
public function changeColumnType($table, $columnName, $columnType)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\ChangeColumnType', array(
'table' => $table, 'columnName' => $columnName, 'columnType' => $columnType
));
}
/**
* Changes the type of multiple existing database table columns at the same time.
*
* Changing multiple columns at the same time can lead to performance improvements compared to changing the type
* of each column separately.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param array $columns An array of column name to column type pairs,
* eg array('my_column_name' => 'VARCHAR(200) NOT NULL', 'column2' => '...')
*
* @return ChangeColumnTypes
*/
public function changeColumnTypes($table, $columns)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\ChangeColumnTypes', array(
'table' => $table, 'columns' => $columns
));
}
/**
* Adds an index to an existing database table.
*
* This is equivalent to an `ADD INDEX indexname (column_name_1, column_name_2)` in SQL.
* It adds a normal index, no unique index.
*
* Note: If no indexName is specified, it will automatically generate a name for this index if which is basically:
* `'index_' . implode('_', $columnNames)`. If a column name is eg `column1(10)` then only the first part (`column1`)
* will be used. For example when using columns `array('column1', 'column2(10)')` then the index name will be
* `index_column1_column2`.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param string[]|string $columnNames Either one or multiple column names, eg array('column_name_1', 'column_name_2').
* A column name can be appended by a number bracket eg "column_name_1(10)".
* @param string $indexName If specified, the given index name will be used instead of the automatically generated one.
* @return AddIndex
*/
public function addIndex($table, $columnNames, $indexName = '')
{
$table = $this->prefixTable($table);
if (!is_array($columnNames)) {
$columnNames = array($columnNames);
}
return $this->container->make('Piwik\Updater\Migration\Db\AddIndex', array(
'table' => $table, 'columnNames' => $columnNames, 'indexName' => $indexName
));
}
/**
* Adds a unique key to an existing database table.
*
* This is equivalent to an `ADD UNIQUE KEY indexname (column_name_1, column_name_2)` in SQL.
*
* Note: If no indexName is specified, it will automatically generate a name for this index if which is basically:
* `'index_' . implode('_', $columnNames)`. If a column name is eg `column1(10)` then only the first part (`column1`)
* will be used. For example when using columns `array('column1', 'column2(10)')` then the index name will be
* `index_column1_column2`.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param string[]|string $columnNames Either one or multiple column names, eg array('column_name_1', 'column_name_2').
* A column name can be appended by a number bracket eg "column_name_1(10)".
* @param string $indexName If specified, the given unique key name will be used instead of the automatically generated one.
* @return AddIndex
*/
public function addUniqueKey($table, $columnNames, $indexName = '')
{
$table = $this->prefixTable($table);
if (!is_array($columnNames)) {
$columnNames = array($columnNames);
}
return $this->container->make('Piwik\Updater\Migration\Db\AddUniqueKey', array(
'table' => $table, 'columnNames' => $columnNames, 'indexName' => $indexName
));
}
/**
* Drops an existing index from a database table.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param string $indexName The name of the index that shall be dropped.
* @return DropIndex
*/
public function dropIndex($table, $indexName)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\DropIndex', array(
'table' => $table, 'indexName' => $indexName
));
}
/**
* Drops an existing index from a database table.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @return DropIndex
*/
public function dropPrimaryKey($table)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\DropPrimaryKey', array(
'table' => $table
));
}
/**
* Adds a primary key to an existing database table.
*
* This is equivalent to an `ADD PRIMARY KEY(column_name_1, column_name_2)` in SQL.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param string[]|string $columnNames Either one or multiple column names, eg array('column_name_1', 'column_name_2')
* @return AddPrimaryKey
*/
public function addPrimaryKey($table, $columnNames)
{
$table = $this->prefixTable($table);
if (!is_array($columnNames)) {
$columnNames = array($columnNames);
}
return $this->container->make('Piwik\Updater\Migration\Db\AddPrimaryKey', array(
'table' => $table, 'columnNames' => $columnNames
));
}
/**
* Inserts a new record / row into an existing database table.
*
* Make sure to specify all columns that need to be defined in order to insert a value successfully. There could
* be for example columns that are not nullable and therefore need a value.
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param array $columnValuePairs An array containing column => value pairs. For example:
* array('column_name_1' => 'value1', 'column_name_2' => 'value2')
* @return Insert
*/
public function insert($table, $columnValuePairs)
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\Insert', array(
'table' => $table, 'columnValuePairs' => $columnValuePairs
));
}
/**
* Performs a batch insert into a specific table using either LOAD DATA INFILE or plain INSERTs,
* as a fallback. On MySQL, LOAD DATA INFILE is 20x faster than a series of plain INSERTs.
*
* Please note that queries for batch inserts are currently not shown to an end user and should therefore not be
* returned in an `Updates::getMigrations` method. Instead it needs to be execute directly in `Updates::doUpdate`
* via `$updater->executeMigration($factory->dbBatchInsert(...));`
*
* @param string $table Unprefixed database table name, eg 'log_visit'.
* @param string[] $columnNames An array of unquoted column names, eg array('column_name1', 'column_name_2')
* @param array $values An array of data to be inserted, eg array(array('row1column1', 'row1column2'),array('row2column1', 'row2column2'))
* @param bool $throwException Whether to throw an exception that was caught while trying LOAD DATA INFILE, or not.
* @param string $charset The charset to use, defaults to utf8
* @return BatchInsert
*/
public function batchInsert($table, $columnNames, $values, $throwException = false, $charset = 'utf8')
{
$table = $this->prefixTable($table);
return $this->container->make('Piwik\Updater\Migration\Db\BatchInsert', array(
'table' => $table, 'columnNames' => $columnNames, 'values' => $values,
'throwException' => $throwException, 'charset' => $charset
));
}
private function prefixTable($table)
{
return Common::prefixTable($table);
}
}
|