Context Navigation

source: trunk/admin/include/mysqldump.php @ 26998

Last change on this file since 26998 was 10511, checked in by patdenice, 13 years ago
feature:2271 Merge autoupdate plugin into piwigo core.
File size: 11.3 KB

Line
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	?>

Note: See TracBrowser for help on using the repository browser.

Download in other formats: