1: <?php
  2: /**
  3:  * Magento
  4:  *
  5:  * NOTICE OF LICENSE
  6:  *
  7:  * This source file is subject to the Open Software License (OSL 3.0)
  8:  * that is bundled with this package in the file LICENSE.txt.
  9:  * It is also available through the world-wide-web at this URL:
 10:  * http://opensource.org/licenses/osl-3.0.php
 11:  * If you did not receive a copy of the license and are unable to
 12:  * obtain it through the world-wide-web, please send an email
 13:  * to license@magentocommerce.com so we can send you a copy immediately.
 14:  *
 15:  * DISCLAIMER
 16:  *
 17:  * Do not edit or add to this file if you wish to upgrade Magento to newer
 18:  * versions in the future. If you wish to customize Magento for your
 19:  * needs please refer to http://www.magentocommerce.com for more information.
 20:  *
 21:  * @category    Mage
 22:  * @package     Mage_Backup
 23:  * @copyright   Copyright (c) 2012 Magento Inc. (http://www.magentocommerce.com)
 24:  * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 25:  */
 26: 
 27: class Mage_Backup_Model_Resource_Helper_Mysql4 extends Mage_Core_Model_Resource_Helper_Mysql4
 28: {
 29:     /**
 30:      * Tables foreign key data array
 31:      * [tbl_name] = array(create foreign key strings)
 32:      *
 33:      * @var array
 34:      */
 35:     protected $_foreignKeys    = array();
 36: 
 37:     /**
 38:      * Retrieve SQL fragment for drop table
 39:      *
 40:      * @param string $tableName
 41:      * @return string
 42:      */
 43:     public function getTableDropSql($tableName)
 44:     {
 45:         $quotedTableName = $this->_getReadAdapter()->quoteIdentifier($tableName);
 46:         return sprintf('DROP TABLE IF EXISTS %s;', $quotedTableName);
 47:     }
 48: 
 49:     /**
 50:      * Retrieve foreign keys for table(s)
 51:      *
 52:      * @param string|null $tableName
 53:      * @return string|false
 54:      */
 55:     public function getTableForeignKeysSql($tableName = null)
 56:     {
 57:         $sql = false;
 58: 
 59:         if ($tableName === null) {
 60:             $sql = '';
 61:             foreach ($this->_foreignKeys as $table => $foreignKeys) {
 62:                 $sql .= $this->_buildForeignKeysAlterTableSql($table, $foreignKeys);
 63:             }
 64:         } else if (isset($this->_foreignKeys[$tableName])) {
 65:             $foreignKeys = $this->_foreignKeys[$tableName];
 66:             $sql = $this->_buildForeignKeysAlterTableSql($tableName, $foreignKeys);
 67:         }
 68: 
 69:         return $sql;
 70:     }
 71: 
 72:     /**
 73:      * Build sql that will add foreign keys to it
 74:      *
 75:      * @param string $tableName
 76:      * @param array $foreignKeys
 77:      * @return string
 78:      */
 79:     protected function _buildForeignKeysAlterTableSql($tableName, $foreignKeys)
 80:     {
 81:         if (!is_array($foreignKeys) || empty($foreignKeys)) {
 82:             return '';
 83:         }
 84: 
 85:         return sprintf("ALTER TABLE %s\n  %s;\n",
 86:             $this->_getReadAdapter()->quoteIdentifier($tableName),
 87:             join(",\n  ", $foreignKeys)
 88:         );
 89:     }
 90: 
 91:      /**
 92:      * Get create script for table
 93:      *
 94:      * @param string $tableName
 95:      * @param boolean $addDropIfExists
 96:      * @return string
 97:      */
 98:     public function getTableCreateScript($tableName, $addDropIfExists = false)
 99:     {
100:         $script = '';
101:         $quotedTableName = $this->_getReadAdapter()->quoteIdentifier($tableName);
102: 
103:         if ($addDropIfExists) {
104:             $script .= 'DROP TABLE IF EXISTS ' . $quotedTableName .";\n";
105:         }
106:         //TODO fix me
107:         $sql     = 'SHOW CREATE TABLE ' . $quotedTableName;
108:         $data    = $this->_getReadAdapter()->fetchRow($sql);
109:         $script .= isset($data['Create Table']) ? $data['Create Table'].";\n" : '';
110: 
111:         return $script;
112:     }
113:     /**
114:      * Retrieve SQL fragment for create table
115:      *
116:      * @param string $tableName
117:      * @param bool $withForeignKeys
118:      * @return string
119:      */
120:     public function getTableCreateSql($tableName, $withForeignKeys = false)
121:     {
122:         $adapter         = $this->_getReadAdapter();
123:         $quotedTableName = $adapter->quoteIdentifier($tableName);
124:         $query           = 'SHOW CREATE TABLE ' . $quotedTableName;
125:         $row             = $adapter->fetchRow($query);
126: 
127:         if (!$row || !isset($row['Table']) || !isset($row['Create Table'])) {
128:             return false;
129:         }
130: 
131:         $regExp  = '/,\s+CONSTRAINT `([^`]*)` FOREIGN KEY \(`([^`]*)`\) '
132:             . 'REFERENCES `([^`]*)` \(`([^`]*)`\)'
133:             . '( ON DELETE (RESTRICT|CASCADE|SET NULL|NO ACTION))?'
134:             . '( ON UPDATE (RESTRICT|CASCADE|SET NULL|NO ACTION))?/';
135:         $matches = array();
136:         preg_match_all($regExp, $row['Create Table'], $matches, PREG_SET_ORDER);
137: 
138:         if (is_array($matches)) {
139:             foreach ($matches as $match) {
140:                 $this->_foreignKeys[$tableName][] = sprintf('ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)%s%s',
141:                     $adapter->quoteIdentifier($match[1]),
142:                     $adapter->quoteIdentifier($match[2]),
143:                     $adapter->quoteIdentifier($match[3]),
144:                     $adapter->quoteIdentifier($match[4]),
145:                     isset($match[5]) ? $match[5] : '',
146:                     isset($match[7]) ? $match[7] : ''
147:                 );
148:             }
149:         }
150: 
151:         if ($withForeignKeys) {
152:             $sql = $row['Create Table'];
153:         } else {
154:             $sql = preg_replace($regExp, '', $row['Create Table']);
155:         }
156: 
157:         return $sql . ';';
158:     }
159:     /**
160:      * Returns SQL header data, move from original resource model
161:      *
162:      * @return string
163:      */
164:     public function getHeader()
165:     {
166:         $dbConfig = $this->_getReadAdapter()->getConfig();
167: 
168:         $versionRow = $this->_getReadAdapter()->fetchRow('SHOW VARIABLES LIKE \'version\'');
169:         $hostName   = !empty($dbConfig['unix_socket']) ? $dbConfig['unix_socket']
170:             : (!empty($dbConfig['host']) ? $dbConfig['host'] : 'localhost');
171: 
172:         $header = "-- Magento DB backup\n"
173:             . "--\n"
174:             . "-- Host: {$hostName}    Database: {$dbConfig['dbname']}\n"
175:             . "-- ------------------------------------------------------\n"
176:             . "-- Server version: {$versionRow['Value']}\n\n"
177:             . "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n"
178:             . "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n"
179:             . "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n"
180:             . "/*!40101 SET NAMES utf8 */;\n"
181:             . "/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n"
182:             . "/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n"
183:             . "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n"
184:             . "/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\n";
185: 
186:         return $header;
187:     }
188: 
189:     /**
190:      * Returns SQL footer data, move from original resource model
191:      *
192:      * @return string
193:      */
194:     public function getFooter()
195:     {
196:         $footer = "\n/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\n"
197:             . "/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; \n"
198:             . "/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\n"
199:             . "/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n"
200:             . "/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\n"
201:             . "/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\n"
202:             . "/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\n"
203:             . "\n-- Dump completed on " . Mage::getSingleton('core/date')->gmtDate() . " GMT";
204: 
205:         return $footer;
206:     }
207: 
208:     /**
209:      * Retrieve before insert data SQL fragment
210:      *
211:      * @param string $tableName
212:      * @return string
213:      */
214:     public function getTableDataBeforeSql($tableName)
215:     {
216:         $quotedTableName = $this->_getReadAdapter()->quoteIdentifier($tableName);
217:         return "\n--\n"
218:             . "-- Dumping data for table {$quotedTableName}\n"
219:             . "--\n\n"
220:             . "LOCK TABLES {$quotedTableName} WRITE;\n"
221:             . "/*!40000 ALTER TABLE {$quotedTableName} DISABLE KEYS */;\n";
222:     }
223: 
224:     /**
225:      * Retrieve after insert data SQL fragment
226:      *
227:      * @param string $tableName
228:      * @return string
229:      */
230:     public function getTableDataAfterSql($tableName)
231:     {
232:         $quotedTableName = $this->_getReadAdapter()->quoteIdentifier($tableName);
233:         return "/*!40000 ALTER TABLE {$quotedTableName} ENABLE KEYS */;\n"
234:             . "UNLOCK TABLES;\n";
235:     }
236: 
237:     /**
238:      * Return table part data SQL insert
239:      *
240:      * @param string $tableName
241:      * @param int $count
242:      * @param int $offset
243:      * @return string
244:      */
245:     public function getPartInsertSql($tableName, $count = null, $offset = null)
246:     {
247:         $sql = null;
248:         $adapter = $this->_getWriteAdapter();
249:         $select = $adapter->select()
250:             ->from($tableName)
251:             ->limit($count, $offset);
252:         $query  = $adapter->query($select);
253: 
254:         while ($row = $query->fetch()) {
255:             if ($sql === null) {
256:                 $sql = sprintf('INSERT INTO %s VALUES ', $adapter->quoteIdentifier($tableName));
257:             } else {
258:                 $sql .= ',';
259:             }
260: 
261:             $sql .= $this->_quoteRow($tableName, $row);
262:         }
263: 
264:         if ($sql !== null) {
265:             $sql .= ';' . "\n";
266:         }
267: 
268:         return $sql;
269:     }
270:     /**
271:      * Return table data SQL insert
272:      *
273:      * @param string $tableName
274:      * @return string
275:      */
276:     public function getInsertSql($tableName)
277:     {
278:         return $this->getPartInsertSql($tableName);
279:     }
280:     /**
281:      * Quote Table Row
282:      *
283:      * @param string $tableName
284:      * @param array $row
285:      * @return string
286:      */
287:     protected function _quoteRow($tableName, array $row)
288:     {
289:         $adapter   = $this->_getReadAdapter();
290:         $describe  = $adapter->describeTable($tableName);
291:         $dataTypes = array('bigint', 'mediumint', 'smallint', 'tinyint');
292:         $rowData   = array();
293:         foreach ($row as $k => $v) {
294:             if ($v === null) {
295:                 $value = 'NULL';
296:             } elseif (in_array(strtolower($describe[$k]['DATA_TYPE']), $dataTypes)) {
297:                 $value = $v;
298:             } else {
299:                 $value = $adapter->quoteInto('?', $v);
300:             }
301:             $rowData[] = $value;
302:         }
303: 
304:         return sprintf('(%s)', implode(',', $rowData));
305:     }
306: 
307:     /**
308:      * Turn on serializable mode
309:      */
310:     public function turnOnSerializableMode()
311:     {
312:         $this->_getReadAdapter()->query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
313:     }
314: 
315:     /**
316:      * Turn on read committed mode
317:      */
318:     public function turnOnReadCommittedMode()
319:     {
320:         $this->_getReadAdapter()->query("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");
321:     }
322: }
323: