00001 <?php
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030
00031
00032
00033
00034 class Mage_Backup_Model_Mysql4_Db
00035 {
00036
00037
00038
00039
00040
00041 protected $_read;
00042
00043
00044
00045
00046
00047
00048
00049 protected $_foreignKeys = array();
00050
00051
00052
00053
00054
00055 public function __construct()
00056 {
00057 $this->_read = Mage::getSingleton('core/resource')->getConnection('backup_read');
00058 }
00059
00060
00061
00062
00063
00064 public function crear()
00065 {
00066 $this->_foreignKeys = array();
00067 }
00068
00069
00070
00071
00072
00073
00074 public function getTables()
00075 {
00076 return $this->_read->listTables();
00077 }
00078
00079
00080
00081
00082
00083
00084
00085 public function getTableDropSql($tableName)
00086 {
00087 $quotedTableName = $this->_read->quoteIdentifier($tableName);
00088 return 'DROP TABLE IF EXISTS ' . $quotedTableName . ';';
00089 }
00090
00091
00092
00093
00094
00095
00096
00097
00098 public function getTableCreateSql($tableName, $withForeignKeys = false)
00099 {
00100 $quotedTableName = $this->_read->quoteIdentifier($tableName);
00101 $sql = 'SHOW CREATE TABLE ' . $quotedTableName;
00102 $row = $this->_read->fetchRow($sql);
00103
00104 if (!$row) {
00105 return false;
00106 }
00107
00108 $regExp = '/,\s+CONSTRAINT `([^`]*)` FOREIGN KEY \(`([^`]*)`\) '
00109 . 'REFERENCES `([^`]*)` \(`([^`]*)`\)'
00110 . '( ON DELETE (RESTRICT|CASCADE|SET NULL|NO ACTION))?'
00111 . '( ON UPDATE (RESTRICT|CASCADE|SET NULL|NO ACTION))?/';
00112 $matches = array();
00113 preg_match_all($regExp, $row['Create Table'], $matches, PREG_SET_ORDER);
00114
00115 foreach ($matches as $match) {
00116 $this->_foreignKeys[$tableName][] = sprintf('ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)%s%s',
00117 $this->_read->quoteIdentifier($match[1]),
00118 $this->_read->quoteIdentifier($match[2]),
00119 $this->_read->quoteIdentifier($match[3]),
00120 $this->_read->quoteIdentifier($match[4]),
00121 isset($match[5]) ? $match[5] : '',
00122 isset($match[7]) ? $match[7] : ''
00123 );
00124 }
00125
00126 if ($withForeignKeys) {
00127 return $row['Create Table'] . ';';
00128 }
00129 else {
00130 return preg_replace($regExp, '', $row['Create Table']) . ';';
00131 }
00132 }
00133
00134
00135
00136
00137
00138
00139
00140 public function getTableForeignKeysSql($tableName = null)
00141 {
00142 if (is_null($tableName)) {
00143 $sql = '';
00144 foreach ($this->_foreignKeys as $table => $foreignKeys) {
00145 $sql .= sprintf("ALTER TABLE %s\n %s;\n",
00146 $this->_read->quoteIdentifier($table),
00147 join(",\n ", $foreignKeys)
00148 );
00149 }
00150 return $sql;
00151 }
00152 if (isset($this->_foreignKeys[$tableName]) && ($foreignKeys = $this->_foreignKeys[$tableName])) {
00153
00154 }
00155 return false;
00156 }
00157
00158
00159
00160
00161
00162
00163
00164 public function getTableStatus($tableName)
00165 {
00166 $sql = $this->_read->quoteInto('SHOW TABLE STATUS LIKE ?', $tableName);
00167 $row = $this->_read->fetchRow($sql);
00168
00169 if ($row) {
00170 $statusObject = new Varien_Object();
00171 $statusObject->setIdFieldName('name');
00172 foreach ($row as $field => $value) {
00173 $statusObject->setData(strtolower($field), $value);
00174 }
00175
00176 $cntRow = $this->_read->fetchRow( $this->_read->select()->from($tableName, 'COUNT(*) as rows'));
00177 $statusObject->setRows($cntRow['rows']);
00178
00179 return $statusObject;
00180 }
00181
00182 return false;
00183 }
00184
00185
00186
00187
00188
00189
00190
00191
00192 protected function _quoteRow($tableName, array $row)
00193 {
00194 $describe = $this->_read->describeTable($tableName);
00195 $rowData = array();
00196 foreach ($row as $k => $v) {
00197 if (is_null($v)) {
00198 $value = 'NULL';
00199 }
00200 elseif (in_array(strtolower($describe[$k]['DATA_TYPE']), array('bigint','mediumint','smallint','tinyint'))) {
00201 $value = $v;
00202 }
00203 else {
00204 $value = $this->_read->quoteInto('?', $v);
00205 }
00206 $rowData[] = $value;
00207 }
00208 return '('.join(',', $rowData).')';
00209 }
00210
00211
00212
00213
00214
00215
00216
00217
00218
00219 public function getTableDataSql($tableName, $count, $offset = 0)
00220 {
00221 $sql = null;
00222 $quotedTableName = $this->_read->quoteIdentifier($tableName);
00223 $select = $this->_read->select()
00224 ->from($tableName)
00225 ->limit($count, $offset);
00226 $query = $this->_read->query($select);
00227
00228 while ($row = $query->fetch()) {
00229 if (is_null($sql)) {
00230 $sql = 'INSERT INTO ' . $quotedTableName . ' VALUES ';
00231 }
00232 else {
00233 $sql .= ',';
00234 }
00235
00236 $sql .= $this->_quoteRow($tableName, $row);
00237 }
00238
00239 if (!is_null($sql)) {
00240 $sql .= ';' . "\n";
00241 }
00242
00243 return $sql;
00244 }
00245
00246
00247
00248
00249
00250
00251
00252
00253 public function getTableCreateScript($tableName, $addDropIfExists=false)
00254 {
00255 $script = '';
00256 if ($this->_read) {
00257 $quotedTableName = $this->_read->quoteIdentifier($tableName);
00258
00259 if ($addDropIfExists) {
00260 $script .= 'DROP TABLE IF EXISTS ' . $quotedTableName .";\n";
00261 }
00262 $sql = 'SHOW CREATE TABLE ' . $quotedTableName;
00263 $data = $this->_read->fetchRow($sql);
00264 $script.= isset($data['Create Table']) ? $data['Create Table'].";\n" : '';
00265 }
00266
00267 return $script;
00268 }
00269
00270
00271
00272
00273
00274
00275 public function getTableHeader($tableName)
00276 {
00277 $quotedTableName = $this->_read->quoteIdentifier($tableName);
00278 return "\n--\n"
00279 . "-- Table structure for table {$quotedTableName}\n"
00280 . "--\n\n";
00281 }
00282
00283 public function getTableDataDump($tableName, $step=100)
00284 {
00285 $sql = '';
00286 if ($this->_read) {
00287 $quotedTableName = $this->_read->quoteIdentifier($tableName);
00288 $colunms = $this->_read->fetchRow('SELECT * FROM '.$quotedTableName.' LIMIT 1');
00289 if ($colunms) {
00290 $arrSql = array();
00291
00292 $colunms = array_keys($colunms);
00293 $quote = $this->_read->getQuoteIdentifierSymbol();
00294 $sql = 'INSERT INTO ' . $quotedTableName . ' (' .$quote . implode($quote.', '.$quote,$colunms).$quote.')';
00295 $sql.= ' VALUES ';
00296
00297 $startRow = 0;
00298 $select = $this->_read->select();
00299 $select->from($tableName)
00300 ->limit($step, $startRow);
00301 while ($data = $this->_read->fetchAll($select)) {
00302 $dataSql = array();
00303 foreach ($data as $row) {
00304 $dataSql[] = $this->_read->quoteInto('(?)', $row);
00305 }
00306 $arrSql[] = $sql.implode(', ', $dataSql).';';
00307 $startRow += $step;
00308 $select->limit($step, $startRow);
00309 }
00310
00311 $sql = implode("\n", $arrSql)."\n";
00312 }
00313
00314 }
00315
00316 return $sql;
00317 }
00318
00319
00320
00321
00322 public function getHeader()
00323 {
00324 $dbConfig = $this->_read->getConfig();
00325
00326 $versionRow = $this->_read->fetchRow('SHOW VARIABLES LIKE \'version\'');
00327
00328 $header = "-- Magento DB backup\n"
00329 . "--\n"
00330 . "-- Host: {$dbConfig['host']} Database: {$dbConfig['dbname']}\n"
00331 . "-- ------------------------------------------------------\n"
00332 . "-- Server version: {$versionRow['Value']}\n\n"
00333 . "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n"
00334 . "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n"
00335 . "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n"
00336 . "/*!40101 SET NAMES utf8 */;\n"
00337 . "/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n"
00338 . "/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n"
00339 . "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n"
00340 . "/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\n";
00341
00342 return $header;
00343 }
00344
00345
00346
00347
00348 public function getFooter()
00349 {
00350 $footer = "\n/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\n"
00351 . "/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; \n"
00352 . "/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\n"
00353 . "/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n"
00354 . "/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\n"
00355 . "/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\n"
00356 . "/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\n"
00357 . "\n-- Dump completed on " . Mage::getSingleton('core/date')->gmtDate() . " GMT";
00358
00359 return $footer;
00360 }
00361
00362
00363
00364
00365
00366
00367
00368 public function getTableDataBeforeSql($tableName)
00369 {
00370 $quotedTableName = $this->_read->quoteIdentifier($tableName);
00371 return "\n--\n"
00372 . "-- Dumping data for table {$quotedTableName}\n"
00373 . "--\n\n"
00374 . "LOCK TABLES {$quotedTableName} WRITE;\n"
00375 . "/*!40000 ALTER TABLE {$quotedTableName} DISABLE KEYS */;\n";
00376 }
00377
00378
00379
00380
00381
00382
00383
00384 public function getTableDataAfterSql($tableName)
00385 {
00386 $quotedTableName = $this->_read->quoteIdentifier($tableName);
00387 return "/*!40000 ALTER TABLE {$quotedTableName} ENABLE KEYS */;\n"
00388 . "UNLOCK TABLES;\n";
00389 }
00390
00391 public function beginTransaction()
00392 {
00393 $this->_read->beginTransaction();
00394 }
00395
00396 public function commitTransaction()
00397 {
00398 $this->_read->commit();
00399 }
00400
00401 public function rollBackTransaction()
00402 {
00403 $this->_read->rollBack();
00404 }
00405 }