1 | <?php |
---|
2 | /** |
---|
3 | * Dump MySQL database |
---|
4 | * |
---|
5 | * Here is an inline example: |
---|
6 | * <code> |
---|
7 | * $connection = @mysql_connect($dbhost,$dbuser,$dbpsw); |
---|
8 | * $dumper = new MySQLDump($dbname,'filename.sql',false,false); |
---|
9 | * $dumper->doDump(); |
---|
10 | * </code> |
---|
11 | * |
---|
12 | * Special thanks to: |
---|
13 | * - Andrea Ingaglio <andrea@coders4fun.com> helping in development of all class code |
---|
14 | * - Dylan Pugh for precious advices halfing the size of the output file and for helping in debug |
---|
15 | * |
---|
16 | * @name MySQLDump |
---|
17 | * @author Daniele Viganò - CreativeFactory.it <daniele.vigano@creativefactory.it> |
---|
18 | * @version 2.20 - 02/11/2007 |
---|
19 | * @license http://opensource.org/licenses/gpl-license.php GNU Public License |
---|
20 | */ |
---|
21 | |
---|
22 | class MySQLDump { |
---|
23 | /** |
---|
24 | * @access private |
---|
25 | */ |
---|
26 | var $database = null; |
---|
27 | |
---|
28 | /** |
---|
29 | * @access private |
---|
30 | */ |
---|
31 | var $compress = false; |
---|
32 | |
---|
33 | /** |
---|
34 | * @access private |
---|
35 | */ |
---|
36 | var $hexValue = false; |
---|
37 | |
---|
38 | /** |
---|
39 | * The output filename |
---|
40 | * @access private |
---|
41 | */ |
---|
42 | var $filename = null; |
---|
43 | |
---|
44 | /** |
---|
45 | * The pointer of the output file |
---|
46 | * @access private |
---|
47 | */ |
---|
48 | var $file = null; |
---|
49 | |
---|
50 | /** |
---|
51 | * @access private |
---|
52 | */ |
---|
53 | var $isWritten = false; |
---|
54 | |
---|
55 | /** |
---|
56 | * Class constructor |
---|
57 | * @param string $db The database name |
---|
58 | * @param string $filepath The file where the dump will be written |
---|
59 | * @param boolean $compress It defines if the output file is compress (gzip) or not |
---|
60 | * @param boolean $hexValue It defines if the outup values are base-16 or not |
---|
61 | */ |
---|
62 | function MYSQLDump($db = null, $filepath = 'dump.sql', $compress = false, $hexValue = false){ |
---|
63 | $this->compress = $compress; |
---|
64 | if ( !$this->setOutputFile($filepath) ) |
---|
65 | return false; |
---|
66 | return $this->setDatabase($db); |
---|
67 | } |
---|
68 | |
---|
69 | /** |
---|
70 | * Sets the database to work on |
---|
71 | * @param string $db The database name |
---|
72 | */ |
---|
73 | function setDatabase($db){ |
---|
74 | $this->database = $db; |
---|
75 | if ( !@mysql_select_db($this->database) ) |
---|
76 | return false; |
---|
77 | return true; |
---|
78 | } |
---|
79 | |
---|
80 | /** |
---|
81 | * Returns the database where the class is working on |
---|
82 | * @return string |
---|
83 | */ |
---|
84 | function getDatabase(){ |
---|
85 | return $this->database; |
---|
86 | } |
---|
87 | |
---|
88 | /** |
---|
89 | * Sets the output file type (It can be made only if the file hasn't been already written) |
---|
90 | * @param boolean $compress If it's true, the output file will be compressed |
---|
91 | */ |
---|
92 | function setCompress($compress){ |
---|
93 | if ( $this->isWritten ) |
---|
94 | return false; |
---|
95 | $this->compress = $compress; |
---|
96 | $this->openFile($this->filename); |
---|
97 | return true; |
---|
98 | } |
---|
99 | |
---|
100 | /** |
---|
101 | * Returns if the output file is or not compressed |
---|
102 | * @return boolean |
---|
103 | */ |
---|
104 | function getCompress(){ |
---|
105 | return $this->compress; |
---|
106 | } |
---|
107 | |
---|
108 | /** |
---|
109 | * Sets the output file |
---|
110 | * @param string $filepath The file where the dump will be written |
---|
111 | */ |
---|
112 | function setOutputFile($filepath){ |
---|
113 | if ( $this->isWritten ) |
---|
114 | return false; |
---|
115 | $this->filename = $filepath; |
---|
116 | $this->file = $this->openFile($this->filename); |
---|
117 | return $this->file; |
---|
118 | } |
---|
119 | |
---|
120 | /** |
---|
121 | * Returns the output filename |
---|
122 | * @return string |
---|
123 | */ |
---|
124 | function getOutputFile(){ |
---|
125 | return $this->filename; |
---|
126 | } |
---|
127 | |
---|
128 | /** |
---|
129 | * Writes to file the $table's structure |
---|
130 | * @param string $table The table name |
---|
131 | */ |
---|
132 | function getTableStructure($table){ |
---|
133 | if ( !$this->setDatabase($this->database) ) |
---|
134 | return false; |
---|
135 | // Structure Header |
---|
136 | $structure = "-- \n"; |
---|
137 | $structure .= "-- Table structure for table `{$table}` \n"; |
---|
138 | $structure .= "-- \n\n"; |
---|
139 | // Dump Structure |
---|
140 | $structure .= 'DROP TABLE IF EXISTS `'.$table.'`;'."\n"; |
---|
141 | $structure .= "CREATE TABLE `".$table."` (\n"; |
---|
142 | $records = @mysql_query('SHOW FIELDS FROM `'.$table.'`'); |
---|
143 | if ( @mysql_num_rows($records) == 0 ) |
---|
144 | return false; |
---|
145 | while ( $record = mysql_fetch_assoc($records) ) { |
---|
146 | $structure .= '`'.$record['Field'].'` '.$record['Type']; |
---|
147 | if ( isset($record['Default']) ) |
---|
148 | $structure .= ' DEFAULT \''.$record['Default'].'\''; |
---|
149 | if ( @strcmp($record['Null'],'YES') != 0 ) |
---|
150 | $structure .= ' NOT NULL'; |
---|
151 | elseif ( is_null($record['Default']) ) |
---|
152 | $structure .= ' DEFAULT NULL'; |
---|
153 | if ( !empty($record['Extra']) ) |
---|
154 | $structure .= ' '.$record['Extra']; |
---|
155 | $structure .= ",\n"; |
---|
156 | } |
---|
157 | $structure = @ereg_replace(",\n$", null, $structure); |
---|
158 | |
---|
159 | // Save all Column Indexes |
---|
160 | $structure .= $this->getSqlKeysTable($table); |
---|
161 | $structure .= "\n)"; |
---|
162 | |
---|
163 | //Save table engine |
---|
164 | $records = @mysql_query("SHOW TABLE STATUS LIKE '".$table."'"); |
---|
165 | |
---|
166 | if ( $record = @mysql_fetch_assoc($records) ) { |
---|
167 | if ( !empty($record['Engine']) ) |
---|
168 | $structure .= ' ENGINE='.$record['Engine']; |
---|
169 | if ( !empty($record['Auto_increment']) ) |
---|
170 | $structure .= ' AUTO_INCREMENT='.$record['Auto_increment']; |
---|
171 | } |
---|
172 | |
---|
173 | $structure .= ";\n\n-- --------------------------------------------------------\n\n"; |
---|
174 | $this->saveToFile($this->file,$structure); |
---|
175 | } |
---|
176 | |
---|
177 | /** |
---|
178 | * Writes to file the $table's data |
---|
179 | * @param string $table The table name |
---|
180 | * @param boolean $hexValue It defines if the output is base 16 or not |
---|
181 | */ |
---|
182 | function getTableData($table,$hexValue = true) { |
---|
183 | if ( !$this->setDatabase($this->database) ) |
---|
184 | return false; |
---|
185 | // Header |
---|
186 | $data = "-- \n"; |
---|
187 | $data .= "-- Dumping data for table `$table` \n"; |
---|
188 | $data .= "-- \n\n"; |
---|
189 | |
---|
190 | $records = mysql_query('SHOW FIELDS FROM `'.$table.'`'); |
---|
191 | $num_fields = @mysql_num_rows($records); |
---|
192 | if ( $num_fields == 0 ) |
---|
193 | return false; |
---|
194 | // Field names |
---|
195 | $selectStatement = "SELECT "; |
---|
196 | $insertStatement = "INSERT INTO `$table` ("; |
---|
197 | $hexField = array(); |
---|
198 | for ($x = 0; $x < $num_fields; $x++) { |
---|
199 | $record = @mysql_fetch_assoc($records); |
---|
200 | if ( ($hexValue) && ($this->isTextValue($record['Type'])) ) { |
---|
201 | $selectStatement .= 'HEX(`'.$record['Field'].'`)'; |
---|
202 | $hexField [$x] = true; |
---|
203 | } |
---|
204 | else |
---|
205 | $selectStatement .= '`'.$record['Field'].'`'; |
---|
206 | $insertStatement .= '`'.$record['Field'].'`'; |
---|
207 | $insertStatement .= ", "; |
---|
208 | $selectStatement .= ", "; |
---|
209 | } |
---|
210 | $insertStatement = @substr($insertStatement,0,-2).') VALUES'."\n"; |
---|
211 | $selectStatement = @substr($selectStatement,0,-2).' FROM `'.$table.'`'; |
---|
212 | |
---|
213 | $records = @mysql_query($selectStatement); |
---|
214 | $num_rows = @mysql_num_rows($records); |
---|
215 | $num_fields = @mysql_num_fields($records); |
---|
216 | // Dump data |
---|
217 | if ( $num_rows > 0 ) { |
---|
218 | $data .= $insertStatement; |
---|
219 | for ($i = 0; $i < $num_rows; $i++) { |
---|
220 | $record = @mysql_fetch_assoc($records); |
---|
221 | $data .= ' ('; |
---|
222 | for ($j = 0; $j < $num_fields; $j++) { |
---|
223 | $field_name = @mysql_field_name($records, $j); |
---|
224 | if ( @$hexField[$j] && (@strlen($record[$field_name]) > 0) ) |
---|
225 | $data .= "0x".$record[$field_name]; |
---|
226 | elseif (is_null($record[$field_name])) |
---|
227 | $data .= "NULL"; |
---|
228 | else |
---|
229 | $data .= "'".@str_replace('\"','"',@mysql_real_escape_string($record[$field_name]))."'"; |
---|
230 | $data .= ','; |
---|
231 | } |
---|
232 | $data = @substr($data,0,-1).")"; |
---|
233 | $data .= ( $i < ($num_rows-1) ) ? ',' : ';'; |
---|
234 | $data .= "\n"; |
---|
235 | //if data in greather than 1MB save |
---|
236 | if (strlen($data) > 1048576) { |
---|
237 | $this->saveToFile($this->file,$data); |
---|
238 | $data = ''; |
---|
239 | } |
---|
240 | } |
---|
241 | $data .= "\n-- --------------------------------------------------------\n\n"; |
---|
242 | $this->saveToFile($this->file,$data); |
---|
243 | } |
---|
244 | } |
---|
245 | |
---|
246 | /** |
---|
247 | * Writes to file all the selected database tables structure |
---|
248 | * @return boolean |
---|
249 | */ |
---|
250 | function getDatabaseStructure(){ |
---|
251 | $records = @mysql_query('SHOW TABLES'); |
---|
252 | if ( @mysql_num_rows($records) == 0 ) |
---|
253 | return false; |
---|
254 | $structure = ''; |
---|
255 | while ( $record = @mysql_fetch_row($records) ) { |
---|
256 | $structure .= $this->getTableStructure($record[0]); |
---|
257 | } |
---|
258 | return true; |
---|
259 | } |
---|
260 | |
---|
261 | /** |
---|
262 | * Writes to file all the selected database tables data |
---|
263 | * @param boolean $hexValue It defines if the output is base-16 or not |
---|
264 | */ |
---|
265 | function getDatabaseData($hexValue = true){ |
---|
266 | $records = @mysql_query('SHOW TABLES'); |
---|
267 | if ( @mysql_num_rows($records) == 0 ) |
---|
268 | return false; |
---|
269 | while ( $record = @mysql_fetch_row($records) ) { |
---|
270 | $this->getTableData($record[0],$hexValue); |
---|
271 | } |
---|
272 | } |
---|
273 | |
---|
274 | /** |
---|
275 | * Writes to file the selected database dump |
---|
276 | */ |
---|
277 | function doDump() { |
---|
278 | $this->saveToFile($this->file,"SET FOREIGN_KEY_CHECKS = 0;\n\n"); |
---|
279 | $this->getDatabaseStructure(); |
---|
280 | $this->getDatabaseData($this->hexValue); |
---|
281 | $this->saveToFile($this->file,"SET FOREIGN_KEY_CHECKS = 1;\n\n"); |
---|
282 | $this->closeFile($this->file); |
---|
283 | return true; |
---|
284 | } |
---|
285 | |
---|
286 | /** |
---|
287 | * @deprecated Look at the doDump() method |
---|
288 | */ |
---|
289 | function writeDump($filename) { |
---|
290 | if ( !$this->setOutputFile($filename) ) |
---|
291 | return false; |
---|
292 | $this->doDump(); |
---|
293 | $this->closeFile($this->file); |
---|
294 | return true; |
---|
295 | } |
---|
296 | |
---|
297 | /** |
---|
298 | * @access private |
---|
299 | */ |
---|
300 | function getSqlKeysTable ($table) { |
---|
301 | $primary = ""; |
---|
302 | $unique = array(); |
---|
303 | $index = array(); |
---|
304 | $fulltext = array(); |
---|
305 | $results = mysql_query("SHOW KEYS FROM `{$table}`"); |
---|
306 | if ( @mysql_num_rows($results) == 0 ) |
---|
307 | return false; |
---|
308 | while($row = mysql_fetch_object($results)) { |
---|
309 | if (($row->Key_name == 'PRIMARY') AND ($row->Index_type == 'BTREE')) { |
---|
310 | if ( $primary == "" ) |
---|
311 | $primary = " PRIMARY KEY (`{$row->Column_name}`"; |
---|
312 | else |
---|
313 | $primary .= ", `{$row->Column_name}`"; |
---|
314 | } |
---|
315 | if (($row->Key_name != 'PRIMARY') AND ($row->Non_unique == '0') AND ($row->Index_type == 'BTREE')) { |
---|
316 | if ( (empty($unique)) OR (empty($unique[$row->Key_name])) ) |
---|
317 | $unique[$row->Key_name] = " UNIQUE KEY `{$row->Key_name}` (`{$row->Column_name}`"; |
---|
318 | else |
---|
319 | $unique[$row->Key_name] .= ", `{$row->Column_name}`"; |
---|
320 | } |
---|
321 | if (($row->Key_name != 'PRIMARY') AND ($row->Non_unique == '1') AND ($row->Index_type == 'BTREE')) { |
---|
322 | if ( (empty($index)) OR (empty($index[$row->Key_name])) ) |
---|
323 | $index[$row->Key_name] = " KEY `{$row->Key_name}` (`{$row->Column_name}`"; |
---|
324 | else |
---|
325 | $index[$row->Key_name] .= ", `{$row->Column_name}`"; |
---|
326 | } |
---|
327 | if (($row->Key_name != 'PRIMARY') AND ($row->Non_unique == '1') AND ($row->Index_type == 'FULLTEXT')) { |
---|
328 | if ( (empty($fulltext)) OR (empty($fulltext[$row->Key_name])) ) |
---|
329 | $fulltext[$row->Key_name] = " FULLTEXT `{$row->Key_name}` (`{$row->Column_name}`"; |
---|
330 | else |
---|
331 | $fulltext[$row->Key_name] .= ", `{$row->Column_name}`"; |
---|
332 | } |
---|
333 | } |
---|
334 | $sqlKeyStatement = ''; |
---|
335 | // generate primary, unique, key and fulltext |
---|
336 | if ( $primary != "" ) { |
---|
337 | $sqlKeyStatement .= ",\n"; |
---|
338 | $primary .= ")"; |
---|
339 | $sqlKeyStatement .= $primary; |
---|
340 | } |
---|
341 | if (!empty($unique)) { |
---|
342 | foreach ($unique as $keyName => $keyDef) { |
---|
343 | $sqlKeyStatement .= ",\n"; |
---|
344 | $keyDef .= ")"; |
---|
345 | $sqlKeyStatement .= $keyDef; |
---|
346 | |
---|
347 | } |
---|
348 | } |
---|
349 | if (!empty($index)) { |
---|
350 | foreach ($index as $keyName => $keyDef) { |
---|
351 | $sqlKeyStatement .= ",\n"; |
---|
352 | $keyDef .= ")"; |
---|
353 | $sqlKeyStatement .= $keyDef; |
---|
354 | } |
---|
355 | } |
---|
356 | if (!empty($fulltext)) { |
---|
357 | foreach ($fulltext as $keyName => $keyDef) { |
---|
358 | $sqlKeyStatement .= ",\n"; |
---|
359 | $keyDef .= ")"; |
---|
360 | $sqlKeyStatement .= $keyDef; |
---|
361 | } |
---|
362 | } |
---|
363 | return $sqlKeyStatement; |
---|
364 | } |
---|
365 | |
---|
366 | /** |
---|
367 | * @access private |
---|
368 | */ |
---|
369 | function isTextValue($field_type) { |
---|
370 | switch ($field_type) { |
---|
371 | case "tinytext": |
---|
372 | case "text": |
---|
373 | case "mediumtext": |
---|
374 | case "longtext": |
---|
375 | case "binary": |
---|
376 | case "varbinary": |
---|
377 | case "tinyblob": |
---|
378 | case "blob": |
---|
379 | case "mediumblob": |
---|
380 | case "longblob": |
---|
381 | return True; |
---|
382 | break; |
---|
383 | default: |
---|
384 | return False; |
---|
385 | } |
---|
386 | } |
---|
387 | |
---|
388 | /** |
---|
389 | * @access private |
---|
390 | */ |
---|
391 | function openFile($filename) { |
---|
392 | $file = false; |
---|
393 | if ( $this->compress ) |
---|
394 | $file = @gzopen($filename, "w9"); |
---|
395 | else |
---|
396 | $file = @fopen($filename, "w"); |
---|
397 | return $file; |
---|
398 | } |
---|
399 | |
---|
400 | /** |
---|
401 | * @access private |
---|
402 | */ |
---|
403 | function saveToFile($file, $data) { |
---|
404 | if ( $this->compress ) |
---|
405 | @gzwrite($file, $data); |
---|
406 | else |
---|
407 | @fwrite($file, $data); |
---|
408 | $this->isWritten = true; |
---|
409 | } |
---|
410 | |
---|
411 | /** |
---|
412 | * @access private |
---|
413 | */ |
---|
414 | function closeFile($file) { |
---|
415 | if ( $this->compress ) |
---|
416 | @gzclose($file); |
---|
417 | else |
---|
418 | @fclose($file); |
---|
419 | } |
---|
420 | } |
---|
421 | ?> |
---|