-
Notifications
You must be signed in to change notification settings - Fork 3
/
database_mysql_dump.inc
340 lines (300 loc) · 11.1 KB
/
database_mysql_dump.inc
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
<?php
// Some older mysql client libs are missing this constant.
if (!defined('MYSQLI_BINARY_FLAG')) {
define('MYSQLI_BINARY_FLAG', 128);
}
/**
* Dump active database.
*
* @param $filename
* The filename including path to write the dump to.
* @param $options
* An associative array of snapshot options, as described in demo_dump().
*/
function demo_dump_db($filename, $options = array()) {
// Make sure we have permission to save our backup file.
$directory = dirname($filename);
if (!file_prepare_directory($directory, FILE_CREATE_DIRECTORY)) {
return FALSE;
}
if ($fp = fopen($filename, 'wb')) {
$header = array();
$header[] = '-- Demo module database dump';
$header[] = '-- Version ' . DEMO_DUMP_VERSION;
$header[] = '-- http://drupal.org/project/demo';
$header[] = '--';
$header[] = '-- Database: ' . _demo_get_database();
$header[] = '-- Date: ' . format_date(REQUEST_TIME, 'small');
$header[] = '-- Server version: ' . db_query('SELECT version()')->fetchField();
$header[] = '-- PHP version: ' . PHP_VERSION;
$header[] = '-- Backdrop version: ' . VERSION;
// Avoid auto value for zero values (required for user id 0).
$header[] = '';
$header[] = 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";';
// Temporarily disable foreign key checks for the time of import.
$header[] = 'SET FOREIGN_KEY_CHECKS = 0;';
$header[] = '';
// Set collations for the import. PMA and mysqldump use conditional comments
// to exclude MySQL <4.1, but D6 requires >=4.1.
$header[] = 'SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;';
$header[] = 'SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;';
$header[] = 'SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;';
$header[] = 'SET NAMES utf8;';
$header[] = '';
fwrite($fp, implode("\n", $header));
foreach ($options['tables'] as $table => $dump_options) {
if (!_demo_table_is_view($table)) {
if ($dump_options['schema']) {
_demo_dump_table_schema($fp, $table);
}
if ($dump_options['data']) {
_demo_dump_table_data($fp, $table);
}
}
}
$footer = array();
$footer[] = '';
// Re-enable foreign key checks.
$footer[] = 'SET FOREIGN_KEY_CHECKS = 1;';
// Revert collations for potential subsequent database queries not belonging
// to this module.
// @todo Double-check this behavior according to the results of
// http://drupal.org/node/772678
$footer[] = 'SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;';
$footer[] = 'SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;';
$footer[] = 'SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;';
$footer[] = '';
$footer[] = '';
fwrite($fp, implode("\n", $footer));
fclose($fp);
return TRUE;
}
return FALSE;
}
/**
* Returns the name of the active database.
*/
function _demo_get_database() {
$database = array_keys(db_query('SHOW TABLES')->fetchAssoc());
$database = preg_replace('/^Tables_in_/i', '', $database[0]);
return $database;
}
/**
* Enumerate database tables.
*/
function _demo_enum_tables() {
return db_query('SHOW TABLES')->fetchCol();
}
/**
* Dump table schema.
*
* @param $fp
* The file handle of the output file.
* @param $table
* A table name to export the schema for.
*/
function _demo_dump_table_schema($fp, $table) {
$output = "\n";
$output .= "--\n";
$output .= "-- Table structure for table '$table'\n";
$output .= "--\n\n";
$data = db_query("SHOW CREATE TABLE `$table`")->fetchAssoc();
$status = db_query('SHOW TABLE STATUS LIKE :table', array(':table' => $table))->fetchAssoc();
// Capitalization of keys depends on PDO database connection options.
// Convert them to lowercase.
foreach ($data as $key => $value) {
unset($data[$key]);
$data[strtolower($key)] = $value;
}
foreach ($status as $key => $value) {
unset($status[$key]);
$status[strtolower($key)] = $value;
}
// Add IF NOT EXISTS to CREATE TABLE, replace double quotes with MySQL quotes.
$output .= preg_replace(
array('/^CREATE TABLE/', '/"/'),
array('CREATE TABLE IF NOT EXISTS', '`'),
$data['create table']
);
// @todo Rethink the following code. Perhaps try to strip + parse the existing
// table definition (after leading ")" on last line) and merge anything
// missing into it, and re-append it again. There are too many differences
// between MySQL 5.0 and 5.1+, and PHP mysql(i) and pdo_mysql extensions.
// PDO is missing the table engine.
if (!strpos($output, ' ENGINE=')) {
$output .= ' ENGINE=' . $status['engine'];
}
// Always add charset and collation info to table definitions.
// SHOW CREATE TABLE does not contain collation information, if the collation
// is equal to the default collation of the connection. Since dumps can be
// moved across servers, we need to ensure correct collations.
// Note that [DEFAULT] CHARSET or [DEFAULT] CHARACTER SET is always contained
// on MySQL 5.1, even if it is equal to the default.
// This addition assumes that a collation specified for a table is taken over
// for the table's columns. The MySQL manual does not state whether this is
// the case, but manual tests confirmed that it works that way.
// Like Backdrop core, we need to enforce UTF8 as character set and
// utf8_general_ci as default database collation, if not overridden via
// settings.php.
if (!strpos($output, 'COLLATE=')) {
// Only if the collation contains a underscore, the first string up to the
// first underscore is the character set.
// @see PMA_exportDBCreate()
if (strpos($status['collation'], '_')) {
$collate = 'COLLATE=' . $status['collation'];
}
// If there is a character set defined already, just append the collation.
// Only look for the table definition (on the last line); column definitions
// may use a special collation, which must not be changed.
if (preg_match('@^\).+(?:CHARSET|CHARACTER SET)@m', $output)) {
$output = preg_replace('@((?:DEFAULT )?(?:CHARSET|CHARACTER SET) \w+)@', '$1 ' . $collate, $output);
}
else {
$output .= ' DEFAULT CHARSET=utf8 ' . $collate;
}
}
// Add the table comment, if any.
if (!preg_match('@^\) .*COMMENT.+$@', $output) && !empty($status['comment'])) {
// On PHP 5.2.6/Win32 with PDO MySQL 5.0 with InnoDB, the table comment has
// a trailing "; InnoDB free: 84992 kB".
$status['comment'] = preg_replace('@; InnoDB free: .+$@', '', $status['comment']);
$output .= " COMMENT='" . $status['comment'] . "'";
}
// @todo Depends on whether we dump data and table existence on import.
// if (!empty($status['auto_increment'])) {
// $output .= ' AUTO_INCREMENT=' . $status['auto_increment'];
// }
$output .= ";\n";
fwrite($fp, $output);
}
/**
* Dump table data.
*
* This code has largely been stolen from the phpMyAdmin project.
*
* @param $fp
* The file handle of the output file.
* @param $table
* A table name to export the data for.
*/
function _demo_dump_table_data($fp, $table) {
$output = "\n";
$output .= "--\n";
$output .= "-- Dumping data for table `'$table'`\n";
$output .= "--\n\n";
// Dump table data.
$result = db_query("SELECT * FROM `$table`", array(), array('fetch' => PDO::FETCH_ASSOC));
// Get table fields.
if ($fields = _demo_get_fields($result)) {
// Disable indices to speed up import.
$output .= "/*!40000 ALTER TABLE `$table` DISABLE KEYS */;\n";
// Escape backslashes, PHP code, special chars
$search = array('\\', "'", "\x00", "\x0a", "\x0d", "\x1a");
$replace = array('\\\\', "''", '\0', '\n', '\r', '\Z');
$insert_cmd = "INSERT INTO `$table` VALUES\n";
$insert_buffer = '';
$current_row = 0;
$query_size = 0;
foreach ($result as $row) {
$current_row++;
$values = array();
$field = 0;
foreach ($row as $value) {
// NULL
if (!isset($value) || is_null($value)) {
$values[] = 'NULL';
}
// A number
// timestamp is numeric on some MySQL 4.1, BLOBs are sometimes numeric
else if ($fields[$field]->numeric && !$fields[$field]->timestamp && !$fields[$field]->blob) {
$values[] = $value;
}
// A true BLOB
// - mysqldump only generates hex data when the --hex-blob
// option is used, for fields having the binary attribute
// no hex is generated
// - a TEXT field returns type blob but a real blob
// returns also the 'binary' flag
else if ($fields[$field]->binary && $fields[$field]->blob) {
// Empty blobs need to be different, but '0' is also empty :-(
if (empty($value) && $value != '0') {
$values[] = "''";
}
else {
$values[] = '0x' . bin2hex($value);
}
}
// Something else -> treat as a string
else {
$values[] = "'" . str_replace($search, $replace, $value) . "'";
}
$field++;
}
if ($current_row == 1) {
$insert_buffer = $insert_cmd . '(' . implode(', ', $values) . ')';
}
else {
$insert_buffer = '(' . implode(', ', $values) . ')';
// Check if buffer size exceeds 50KB.
if ($query_size + strlen($insert_buffer) > 50000) {
// Flush to disc and start new buffer.
fwrite($fp, $output . ";\n");
$output = '';
$current_row = 1;
$query_size = 0;
$insert_buffer = $insert_cmd . $insert_buffer;
}
}
$query_size += strlen($insert_buffer);
$output .= ($current_row == 1 ? '' : ",\n") . $insert_buffer;
}
if ($current_row > 0) {
$output .= ";\n";
}
// Enable indices again.
$output .= "/*!40000 ALTER TABLE `$table` ENABLE KEYS */;\n";
}
fwrite($fp, $output);
}
/**
* Return table fields and their properties.
*/
function _demo_get_fields($result) {
$fields = array();
switch (db_driver()) {
case 'mysql':
$i = 0;
while ($meta = $result->getColumnMeta($i)) {
settype($meta, 'object');
// pdo_mysql does not add a native type for INT fields.
if (isset($meta->native_type)) {
// Enhance the field definition for mysql-extension compatibilty.
$meta->numeric = (strtolower($meta->native_type) == 'short');
$meta->blob = (strtolower($meta->native_type) == 'blob');
// Add custom properties.
$meta->timestamp = (strtolower($meta->native_type) == 'long');
}
else {
$meta->numeric = $meta->blob = $meta->timestamp = FALSE;
}
$meta->binary = (array_search('not_null', $meta->flags));
$fields[] = $meta;
$i++;
}
break;
}
return $fields;
}
/**
* Determine whether the given table is a VIEW.
*/
function _demo_table_is_view($table) {
static $tables = array();
if (!isset($tables[$table])) {
$status = db_query('SHOW TABLE STATUS LIKE :table', array(':table' => $table))->fetchAssoc();
// Capitalization of keys depends on PDO database connection options.
$comment = (isset($status['Comment']) ? $status['Comment'] : $status['comment']);
$tables[$table] = (strtoupper(substr($comment, 0, 4)) == 'VIEW');
}
return $tables[$table];
}