Context Navigation

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

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

Rev	Line
[10511]	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: