Permalink
Fetching contributors…
Cannot retrieve contributors at this time
1523 lines (1295 sloc) 47.1 KB
<?php
/**
* @copyright 2009-2016 Vanilla Forums Inc.
* @license http://opensource.org/licenses/gpl-2.0.php GNU GPL2
* @package VanillaPorter
*/
/**
* Object for exporting other database structures into a format that can be imported.
*/
class ExportModel {
/** Character constants. */
const COMMENT = '//';
const DELIM = ',';
const ESCAPE = '\\';
const NEWLINE = "\n";
const NULL = '\N';
const QUOTE = '"';
/** @var bool */
public $captureOnly = false;
/** @var array Any comments that have been written during the export. */
public $comments = array();
/** @var ExportController * */
public $controller = null;
/** @var string The charcter set to set as the connection anytime the database connects. */
public $characterSet = 'utf8';
/** @var int The chunk size when exporting large tables. */
public $chunkSize = 100000;
/** @var array * */
public $currentRow = null;
/** @var string Where we are sending this export: 'file' or 'database'. * */
public $destination = 'file';
/** @var string * */
public $destPrefix = 'GDN_z';
/** @var array * */
public static $escapeSearch = array();
/** @var array * */
public static $escapeReplace = array();
/** @var object File pointer */
public $file = null;
/** @var string A prefix to put into an automatically generated filename. */
public $filenamePrefix = '';
/** @var string Database host. * */
public $_host = 'localhost';
/** @var bool Whether mb_detect_encoding() is available. * */
public static $mb = false;
/** @var object PDO instance */
protected $_PDO = null;
/** @var string Database password. * */
protected $_password;
/** @var string The path to the export file. */
public $path = '';
/**
* @var string The database prefix. When you pass a sql string to ExportTable() it will replace occurances of :_ with this property.
* @see ExportModel::ExportTable()
*/
public $prefix = '';
/** @var array * */
public $queries = array();
/** @var array * */
protected $_queryStructures = array();
/** @var array Tables to limit the export to. A full export is an empty array. */
public $restrictedTables = array();
/** @var string The path to the source of the export in the case where a file is being converted. */
public $sourcePath = '';
/** @var string */
public $sourcePrefix = '';
/** @var bool * */
public $scriptCreateTable = true;
/** @var array Structures that define the format of the export tables. */
protected $_structures = array();
/** @var bool Whether to limit results to the $testLimit. */
public $testMode = false;
/** @var int How many records to limit when $testMode is enabled. */
public $testLimit = 10;
/** @var bool Whether or not to use compression when creating the file. */
protected $_useCompression = true;
/** @var string Database username. */
protected $_username;
/**
* Setup.
*/
public function __construct() {
self::$mb = function_exists('mb_detect_encoding');
// Set the search and replace to escape strings.
self::$escapeSearch = array(self::ESCAPE, self::DELIM, self::NEWLINE, self::QUOTE); // escape must go first
self::$escapeReplace = array(
self::ESCAPE . self::ESCAPE,
self::ESCAPE . self::DELIM,
self::ESCAPE . self::NEWLINE,
self::ESCAPE . self::QUOTE
);
// Load structure.
$this->_structures = vanillaStructure();
}
/**
* Create the export file and begin the export.
*
* @param string $path The path to the export file.
* @param string $source The source program that created the export. This may be used by the import routine to do additional processing.
* @param array $header
* @return resource Pointer to the file created.
*/
public function beginExport($path = '', $source = '', $header = array()) {
$this->comments = array();
$this->beginTime = microtime(true);
// Allow us to define where the output file goes.
if ($path) {
$this->path = $path;
} elseif ($this->controller->param('destpath')) {
$this->path = $this->controller->param('destpath');
if (strstr($this->path, '/') !== false && substr($this->path, 1, -1) != '/') {
// We're using slash paths but didn't include a final slash.
$this->path .= '/';
}
}
// Allow the $path parameter to override this default naming.
if (!$path) {
$this->path .= 'export_' . ($this->filenamePrefix ? $this->filenamePrefix . '_' : '') . date('Y-m-d_His') . '.txt' . ($this->useCompression() ? '.gz' : '');
}
// Start the file pointer.
$fp = $this->_openFile();
// Build meta info about where this file came from.
$comment = 'Vanilla Export: ' . $this->version();
if ($source) {
$comment .= self::DELIM . ' Source: ' . $source;
}
foreach ($header as $key => $value) {
$comment .= self::DELIM . " $key: $value";
}
// Add meta info to the output.
if ($this->captureOnly) {
$this->comment($comment);
} else {
fwrite($fp, $comment . self::NEWLINE . self::NEWLINE);
}
$this->comment('Export Started: ' . date('Y-m-d H:i:s'));
return $fp;
}
/**
* Write a comment to the export file.
*
* @param string $message The message to write.
* @param bool $echo Whether or not to echo the message in addition to writing it to the file.
*/
public function comment($message, $echo = true) {
if ($this->destination == 'file') {
$char = self::COMMENT;
} else {
$char = '--';
}
$comment = $char . ' ' . str_replace(self::NEWLINE, self::NEWLINE . self::COMMENT . ' ',
$message) . self::NEWLINE;
fwrite($this->file, $comment);
if ($echo) {
if (defined('CONSOLE')) {
echo $comment;
} else {
$this->comments[] = $message;
}
}
}
/**
* End the export and close the export file.
*
* This method must be called if BeginExport() has been called or else the export file will not be closed.
*/
public function endExport() {
$this->endTime = microtime(true);
$this->totalTime = $this->endTime - $this->beginTime;
$this->comment($this->path);
$this->comment('Export Completed: ' . date('Y-m-d H:i:s'));
$this->comment(sprintf('Elapsed Time: %s', self::formatElapsed($this->totalTime)));
if ($this->testMode || $this->controller->param('dumpsql') || $this->captureOnly) {
$queries = implode("\n\n", $this->queries);
if ($this->destination == 'database') {
fwrite($this->file, $queries);
} else {
$this->comment($queries, true);
}
}
if ($this->useCompression() && function_exists('gzopen')) {
gzclose($this->file);
} else {
fclose($this->file);
}
}
/**
* Export a table to the export file.
*
* @param string $tableName the name of the table to export. This must correspond to one of the accepted Vanilla tables.
* @param mixed $query The query that will fetch the data for the export this can be one of the following:
* - <b>String</b>: Represents a string of SQL to execute.
* - <b>PDOStatement</b>: Represents an already executed query result set.
* - <b>Array</b>: Represents an array of associative arrays or objects containing the data in the export.
* @param array $mappings Specifies mappings, if any, between the source and the export where the keys represent the source columns and the values represent Vanilla columns.
* - If you specify a Vanilla column then it must be in the export structure contained in this class.
* - If you specify a MySQL type then the column will be added.
* - If you specify an array you can have the following keys: Column, and Type where Column represents the new column name and Type represents the MySQL type.
* For a list of the export tables and columns see $this->Structure().
*/
public function exportTable($tableName, $query, $mappings = array()) {
if (!empty($this->restrictedTables) && !in_array(strtolower($tableName), $this->restrictedTables)) {
$this->comment("Skipping table: $tableName");
} else {
$beginTime = microtime(true);
$rowCount = $this->_exportTable($tableName, $query, $mappings);
$endTime = microtime(true);
$elapsed = self::formatElapsed($beginTime, $endTime);
$this->comment("Exported Table: $tableName ($rowCount rows, $elapsed)");
fwrite($this->file, self::NEWLINE);
}
}
/**
*
*
* @param $tableName
* @param $query
* @param array $mappings
*/
protected function _exportTableImport($tableName, $query, $mappings = array()) {
// Backup the settings.
$destinationBak = $this->destination;
$this->destination = 'file';
$_fileBak = $this->file;
$path = dirname(__FILE__) . '/' . $tableName . '.csv';
$this->comment("Exporting To: $path");
$fp = fopen($path, 'wb');
$this->file = $fp;
// First export the file to a file.
$this->_exportTable($tableName, $query, $mappings, array('NoEndline' => true));
// Now define a table to import into.
$this->_createExportTable($tableName, $query, $mappings);
// Now load the data.
$sql = "load data local infile '$path' into table {$this->destDb}.{$this->destPrefix}$tableName
character set utf8
columns terminated by ','
optionally enclosed by '\"'
escaped by '\\\\'
lines terminated by '\\n'
ignore 2 lines";
$this->query($sql);
// Restore the settings.
$this->destination = $destinationBak;
$this->file = $_fileBak;
}
/**
* Convert database blobs into files.
*
* @param $sql
* @param $blobColumn
* @param $pathColumn
* @param bool $thumbnail
*/
public function exportBlobs($sql, $blobColumn, $pathColumn, $thumbnail = false) {
$this->comment('Exporting blobs...');
$result = $this->query($sql);
$count = 0;
while ($row = mysql_fetch_assoc($result)) {
// vBulletin attachment hack (can't do this in MySQL)
if (strpos($row[$pathColumn], '.attach') && strpos($row[$pathColumn], 'attachments/') !== false) {
$pathParts = explode('/', $row[$pathColumn]); // 3 parts
// Split up the userid into a path, digit by digit
$n = strlen($pathParts[1]);
$dirParts = array();
for ($i = 0; $i < $n; $i++) {
$dirParts[] = $pathParts[1]{$i};
}
$pathParts[1] = implode('/', $dirParts);
// Rebuild full path
$row[$pathColumn] = implode('/', $pathParts);
}
$path = $row[$pathColumn];
// Build path
if (!file_exists(dirname($path))) {
$r = mkdir(dirname($path), 0777, true);
if (!$r) {
die("Could not create " . dirname($path));
}
}
if ($thumbnail) {
$picPath = str_replace('/avat', '/pavat', $path);
$fp = fopen($picPath, 'wb');
} else {
$fp = fopen($path, 'wb');
}
if (!is_resource($fp)) {
die("Could not open $path.");
}
fwrite($fp, $row[$blobColumn]);
fclose($fp);
$this->status('.');
if ($thumbnail) {
if ($thumbnail === true) {
$thumbnail = 50;
}
$thumbPath = str_replace('/avat', '/navat', $path);
generateThumbnail($picPath, $thumbPath, $thumbnail, $thumbnail);
}
$count++;
}
$this->status("$count Blobs.\n");
$this->comment("$count Blobs.", false);
}
/**
* Process for writing an entire single table to file.
*
* @see ExportTable()
* @param $tableName
* @param $query
* @param array $mappings
* @param array $options
* @return int
*/
protected function _exportTable($tableName, $query, $mappings = array(), $options = array()) {
$fp = $this->file;
// Make sure the table is valid for export.
if (!array_key_exists($tableName, $this->_structures)) {
$this->comment("Error: $tableName is not a valid export."
. " The valid tables for export are " . implode(", ", array_keys($this->_structures)));
fwrite($fp, self::NEWLINE);
return;
}
if ($this->destination == 'database') {
$this->_exportTableDB($tableName, $query, $mappings);
return;
}
// Check for a chunked query.
$query = str_replace('{from}', -2000000000, $query);
$query = str_replace('{to}', 2000000000, $query);
if (strpos($query, '{from}') !== false) {
$this->_exportTableDBChunked($tableName, $query, $mappings);
return;
}
// If we are in test mode then limit the query.
if ($this->testMode && $this->testLimit) {
$query = rtrim($query, ';');
if (stripos($query, 'select') !== false && stripos($query, 'limit') === false) {
$query .= " limit {$this->testLimit}";
}
}
$structure = $this->_structures[$tableName];
$lastID = 0;
$IDName = 'NOTSET';
$firstQuery = true;
$data = $this->query($query);
// Loop through the data and write it to the file.
$rowCount = 0;
if ($data !== false) {
while (($row = mysql_fetch_assoc($data)) !== false) {
$row = (array)$row; // export%202010-05-06%20210937.txt
$this->currentRow =& $row;
$rowCount++;
if ($firstQuery) {
// Get the export structure.
$exportStructure = $this->getExportStructure($row, $structure, $mappings, $tableName);
$revMappings = $this->flipMappings($mappings);
$this->writeBeginTable($fp, $tableName, $exportStructure);
$firstQuery = false;
}
$this->writeRow($fp, $row, $exportStructure, $revMappings);
}
}
if ($data !== false) {
mysql_free_result($data);
}
unset($data);
if (!isset($options['NoEndline'])) {
$this->writeEndTable($fp);
}
mysql_close();
return $rowCount;
}
/**
*
*
* @param $tableName
* @param $query
* @param array $mappings
*/
protected function _createExportTable($tableName, $query, $mappings = array()) {
if (!$this->scriptCreateTable) {
return;
}
// Limit the query to grab any additional columns.
$queryStruct = rtrim($query, ';') . ' limit 1';
$structure = $this->_structures[$tableName];
$data = $this->query($queryStruct, true);
// $mb = function_exists('mb_detect_encoding');
// Loop through the data and write it to the file.
if ($data === false) {
return;
}
// Get the export structure.
while (($row = mysql_fetch_assoc($data)) !== false) {
$row = (array)$row;
// Get the export structure.
$exportStructure = $this->getExportStructure($row, $structure, $mappings, $tableName);
break;
}
mysql_close($data);
// Build the create table statement.
$columnDefs = array();
foreach ($exportStructure as $columnName => $type) {
$columnDefs[] = "`$columnName` $type";
}
$destDb = '';
if (isset($this->destDb)) {
$destDb = $this->destDb . '.';
}
$this->query("drop table if exists {$destDb}{$this->destPrefix}$tableName");
$createSql = "create table {$destDb}{$this->destPrefix}$tableName (\n " . implode(",\n ",
$columnDefs) . "\n) engine=innodb";
$this->query($createSql);
}
/**
*
*
* @see _exportTable()
* @param $tableName
* @param $query
* @param array $mappings
*/
protected function _exportTableDB($tableName, $query, $mappings = array()) {
if ($this->hasFilter($mappings) || strpos($query, 'union all') !== false) {
$this->_exportTableImport($tableName, $query, $mappings);
return;
}
// Check for a chunked query.
if (strpos($query, '{from}') !== false) {
$this->_exportTableDBChunked($tableName, $query, $mappings);
return;
}
$destDb = '';
if (isset($this->destDb)) {
$destDb = $this->destDb . '.';
}
// Limit the query to grab any additional columns.
$queryStruct = $this->getQueryStructure($query, $tableName);
$structure = $this->_structures[$tableName];
$exportStructure = $this->getExportStructure($queryStruct, $structure, $mappings, $tableName);
$mappings = $this->flipMappings($mappings);
// Build the create table statement.
$columnDefs = array();
foreach ($exportStructure as $columnName => $type) {
$columnDefs[] = "`$columnName` $type";
}
if ($this->scriptCreateTable) {
$this->query("drop table if exists {$destDb}{$this->destPrefix}$tableName");
$createSql = "create table {$destDb}{$this->destPrefix}$tableName (\n " . implode(",\n ",
$columnDefs) . "\n) engine=innodb";
$this->query($createSql);
}
$query = rtrim($query, ';');
// Build the insert statement.
if ($this->testMode && $this->testLimit) {
$query .= " limit {$this->testLimit}";
}
$insertColumns = array();
$selectColumns = array();
foreach ($exportStructure as $columnName => $type) {
$insertColumns[] = '`' . $columnName . '`';
if (isset($mappings[$columnName])) {
$selectColumns[$columnName] = $mappings[$columnName];
} else {
$selectColumns[$columnName] = $columnName;
}
}
$query = replaceSelect($query, $selectColumns);
$insertSql = "replace {$destDb}{$this->destPrefix}$tableName"
. " (\n " . implode(",\n ", $insertColumns) . "\n)\n"
. $query;
$this->query($insertSql);
}
/**
*
*
* @see _exportTableDB()
* @param $tableName
* @param $query
* @param array $mappings
*/
protected function _exportTableDBChunked($tableName, $query, $mappings = array()) {
// Grab the table name from the first from.
if (preg_match('`\sfrom\s([^\s]+)`', $query, $matches)) {
$from = $matches[1];
} else {
trigger_error("Could not figure out table for $tableName chunking.", E_USER_WARNING);
return;
}
$sql = "show table status like '{$from}';";
$r = $this->query($sql, true);
$row = mysql_fetch_assoc($r);
mysql_free_result($r);
$max = $row['Auto_increment'];
if (!$max) {
$max = 2000000;
}
for ($i = 0; $i < $max; $i += $this->chunkSize) {
$from = $i;
$to = $from + $this->chunkSize - 1;
$sql = str_replace(array('{from}', '{to}'), array($from, $to), $query);
$this->_exportTableDB($tableName, $sql, $mappings);
}
}
/**
*
*
* @param $columns
* @return array
*/
public function fixPermissionColumns($columns) {
$result = array();
foreach ($columns as $index => $value) {
if (is_string($value) && strpos($value, '.') !== false) {
$value = array('Column' => $value, 'Type' => 'tinyint(1)');
}
$result[$index] = $value;
}
return $result;
}
/**
*
*
* @param $mappings
* @return array
*/
public function flipMappings($mappings) {
$result = array();
foreach ($mappings as $column => $mapping) {
if (is_string($mapping)) {
$result[$mapping] = array('Column' => $column);
} else {
$col = $mapping['Column'];
$mapping['Column'] = $column;
$result[$col] = $mapping;
}
}
return $result;
}
/**
* For outputting how long the export took.
*
* @param int $start
* @param int $end
* @return string
*/
public static function formatElapsed($start, $end = null) {
if ($end === null) {
$elapsed = $start;
} else {
$elapsed = $end - $start;
}
$m = floor($elapsed / 60);
$s = $elapsed - $m * 60;
$result = sprintf('%02d:%05.2f', $m, $s);
return $result;
}
/**
*
*
* @param $value
* @return int|mixed|string
*/
public static function formatValue($value) {
// Format the value for writing.
if (is_null($value)) {
$value = self::NULL;
} elseif (is_numeric($value)) {
// Do nothing, formats as is.
} elseif (is_string($value)) {
if (self::$mb && mb_detect_encoding($value) != 'UTF-8') {
$value = utf8_encode($value);
}
$value = str_replace(array("\r\n", "\r"), array(self::NEWLINE, self::NEWLINE), $value);
$value = self::QUOTE
. str_replace(self::$escapeSearch, self::$escapeReplace, $value)
. self::QUOTE;
} elseif (is_bool($value)) {
$value = $value ? 1 : 0;
} else {
// Unknown format.
$value = self::NULL;
}
return $value;
}
/**
* Execute an sql statement and return the result.
*
* @param type $sql
* @param type $indexColumn
* @return type
*/
public function get($sql, $indexColumn = false) {
$r = $this->_query($sql, true);
$result = array();
while ($row = mysql_fetch_assoc($r)) {
if ($indexColumn) {
$result[$row[$indexColumn]] = $row;
} else {
$result[] = $row;
}
}
return $result;
}
/**
* Determine the character set of the origin database.
*
* @param string $table
* @return string|bool Character set name or false.
*/
public function getCharacterSet($table) {
// First get the collation for the database.
$data = $this->query("show table status like ':_{$table}';");
if (!$data) {
return false;
}
if ($statusRow = mysql_fetch_assoc($data)) {
$collation = $statusRow['Collation'];
} else {
return false;
}
// Grab the character set from the database.
$data = $this->query("show collation like '$collation'");
if (!$data) {
return false;
}
if ($collationRow = mysql_fetch_assoc($data)) {
$characterSet = $collationRow['Charset'];
if (!defined('PORTER_CHARACTER_SET')) {
define('PORTER_CHARACTER_SET', $characterSet);
}
return $characterSet;
}
return false;
}
/**
*
*
* @return array
*/
public function getDatabasePrefixes() {
// Grab all of the tables.
$data = $this->query('show tables');
if ($data === false) {
return array();
}
// Get the names in an array for easier parsing.
$tables = array();
while (($row = mysql_fetch_array($data, MYSQL_NUM)) !== false) {
$tables[] = $row[0];
}
sort($tables);
$prefixes = array();
// Loop through each table and get its prefixes.
foreach ($tables as $table) {
$pxFound = false;
foreach ($prefixes as $pxIndex => $px) {
$newPx = $this->_getPrefix($table, $px);
if (strlen($newPx) > 0) {
$pxFound = true;
if ($newPx != $px) {
$prefixes[$pxIndex] = $newPx;
}
break;
}
}
if (!$pxFound) {
$prefixes[] = $table;
}
}
return $prefixes;
}
/**
*
*
* @param $a
* @param $b
* @return string
*/
protected function _getPrefix($a, $b) {
$length = min(strlen($a), strlen($b));
$prefix = '';
for ($i = 0; $i < $length; $i++) {
if ($a[$i] == $b[$i]) {
$prefix .= $a[$i];
} else {
break;
}
}
return $prefix;
}
/**
*
*
* @param $row
* @param $tableOrStructure
* @param $mappings
* @param string $tableName
* @return array
*/
public function getExportStructure($row, $tableOrStructure, &$mappings, $tableName = '_') {
$exportStructure = array();
if (is_string($tableOrStructure)) {
$structure = $this->_structures[$tableOrStructure];
} else {
$structure = $tableOrStructure;
}
// See what columns to add to the end of the structure.
foreach ($row as $column => $x) {
if (array_key_exists($column, $mappings)) {
$mapping = $mappings[$column];
if (is_string($mapping)) {
if (array_key_exists($mapping, $structure)) {
// This an existing column.
$destColumn = $mapping;
$destType = $structure[$destColumn];
} else {
// This is a created column.
$destColumn = $column;
$destType = $mapping;
}
} elseif (is_array($mapping)) {
if (!isset($mapping['Column'])) {
trigger_error("Mapping for $column does not have a 'Column' defined.", E_USER_ERROR);
}
$destColumn = $mapping['Column'];
if (isset($mapping['Type'])) {
$destType = $mapping['Type'];
} elseif (isset($structure[$destColumn])) {
$destType = $structure[$destColumn];
} else {
$destType = 'varchar(255)';
}
// $mappings[$column] = $destColumn;
}
} elseif (array_key_exists($column, $structure)) {
$destColumn = $column;
$destType = $structure[$column];
// Verify column doesn't exist in Mapping array's Column element
$mappingExists = false;
foreach ($mappings as $testMapping) {
if ($testMapping == $column) {
$mappingExists = true;
} elseif (is_array($testMapping) && array_key_exists('Column',
$testMapping) && ($testMapping['Column'] == $column)
) {
$mappingExists = true;
}
}
// Also add the column to the mapping.
if (!$mappingExists) {
$mappings[$column] = $destColumn;
}
} else {
$destColumn = '';
$destType = '';
}
// Check to see if we have to add the column to the export structure.
if ($destColumn && !array_key_exists($destColumn, $exportStructure)) {
// TODO: Make sure $destType is a valid MySQL type.
$exportStructure[$destColumn] = $destType;
}
}
// Add filtered mappings since filters can add new columns.
foreach ($mappings as $source => $options) {
if (!is_array($options)) {
// Force the mappings into the expanded array syntax for easier processing later.
$mappings[$source] = array('Column' => $options);
continue;
}
if (!isset($options['Column'])) {
trigger_error("No column for $tableName(source).$source.", E_USER_NOTICE);
continue;
}
$destColumn = $options['Column'];
if (!array_key_exists($source, $row) && !isset($options['Type'])) {
trigger_error("No column for $tableName(source).$source.", E_USER_NOTICE);
}
if (isset($exportStructure[$destColumn])) {
continue;
}
if (isset($structure[$destColumn])) {
$destType = $structure[$destColumn];
} elseif (isset($options['Type'])) {
$destType = $options['Type'];
} else {
trigger_error("No column for $tableName.$destColumn.", E_USER_NOTICE);
continue;
}
$exportStructure[$destColumn] = $destType;
$mappings[$source] = $destColumn;
}
return $exportStructure;
}
/**
*
*
* @param $query
* @param bool $key
* @return array
*/
public function getQueryStructure($query, $key = false) {
$queryStruct = rtrim($query, ';') . ' limit 1';
if (!$key) {
$key = md5($queryStruct);
}
if (isset($this->_queryStructures[$key])) {
return $this->_queryStructures[$key];
}
$r = $this->query($queryStruct, true);
$i = 0;
$result = array();
while ($i < mysql_num_fields($r)) {
$meta = mysql_fetch_field($r, $i);
$result[$meta->name] = $meta->table;
$i++;
}
$this->_queryStructures[$key] = $result;
return $result;
}
/**
*
*
* @param $sql
* @param $default
* @return mixed
*/
public function getValue($sql, $default) {
$data = $this->get($sql);
if (count($data) > 0) {
$data = array_shift($data); // first row
$result = array_shift($data); // first column
return $result;
} else {
return $default;
}
}
/**
*
*
* @param $structure
* @param $globalStructure
* @return string
*/
protected function _getTableHeader($structure, $globalStructure) {
$tableHeader = '';
foreach ($structure as $column => $type) {
if (strlen($tableHeader) > 0) {
$tableHeader .= self::DELIM;
}
if (array_key_exists($column, $globalStructure)) {
$tableHeader .= $column;
} else {
$tableHeader .= $column . ':' . $type;
}
}
return $tableHeader;
}
/**
* Are there any filters set on this table?
*
* @param $mappings
* @return bool
*/
public function hasFilter(&$mappings) {
foreach ($mappings as $column => $info) {
if (is_array($info) && isset($info['Filter'])) {
return true;
}
}
return false;
}
/**
* Do standard HTML decoding in SQL to speed things up.
*
* @param string $tableName
* @param string $columnName
* @param string $PK
*/
public function HTMLDecoderDb($tableName, $columnName, $PK) {
$common = array('&amp;' => '&', '&lt;' => '<', '&gt;' => '>', '&apos;' => "'", '&quot;' => '"', '&#39;' => "'");
foreach ($common as $from => $to) {
$fromQ = mysql_escape_string($from);
$toQ = mysql_escape_string($to);
$sql = "update :_{$tableName} set $columnName = replace($columnName, '$fromQ', '$toQ') where $columnName like '%$fromQ%'";
$this->query($sql);
}
// Now decode the remaining rows.
$sql = "select * from :_$tableName where $columnName like '%&%;%'";
$result = $this->query($sql, true);
while ($row = mysql_fetch_assoc($result)) {
$from = $row[$columnName];
$to = HTMLDecoder($from);
if ($from != $to) {
$toQ = mysql_escape_string($to);
$sql = "update :_{$tableName} set $columnName = '$toQ' where $PK = {$row[$PK]}";
$this->query($sql, true);
}
}
}
/**
* Determine if an index exists in a table
*
* @param $indexName Name of the index to verify
* @param $table Name of the table the target index exists in
* @return bool True if index exists, false otherwise
*/
public function indexExists($indexName, $table) {
$indexName = mysql_real_escape_string($indexName);
$table = mysql_real_escape_string($table);
$result = $this->query("show index from `{$table}` WHERE Key_name = '{$indexName}'", true);
return $result && mysql_num_rows($result);
}
/**
*
*
* @return resource
*/
protected function _openFile() {
$this->path = str_replace(' ', '_', $this->path);
if ($this->useCompression()) {
$fp = gzopen($this->path, 'wb');
} else {
$fp = fopen($this->path, 'wb');
}
$this->file = $fp;
return $fp;
}
/**
* Execute a SQL query on the current connection.
*
* Wrapper for _Query().
*
* @param string $query The sql to execute.
* @return resource The query cursor.
*/
public function query($query, $buffer = false) {
if (!preg_match('`limit 1;$`', $query)) {
$this->queries[] = $query;
}
if ($this->destination == 'database' && $this->captureOnly) {
if (!preg_match('`^\s*select|show|describe|create`', $query)) {
return 'SKIPPED';
}
}
return $this->_query($query, $buffer);
}
/**
* Execute a SQL query on the current connection.
*
* @see Query()
* @param $sql
* @param bool $buffer
* @return resource
*/
protected function _query($sql, $buffer = false) {
if (isset($this->_lastResult) && is_resource($this->_lastResult)) {
mysql_free_result($this->_lastResult);
}
$sql = str_replace(':_', $this->prefix, $sql); // replace prefix.
if ($this->sourcePrefix) {
$sql = preg_replace("`\b{$this->sourcePrefix}`", $this->prefix, $sql); // replace prefix.
}
$sql = rtrim($sql, ';') . ';';
$connection = @mysql_connect($this->_host, $this->_username, $this->_password);
mysql_select_db($this->_dbName);
mysql_query("set names {$this->characterSet}");
if ($buffer) {
$result = mysql_query($sql, $connection);
} else {
$result = mysql_unbuffered_query($sql, $connection);
if (is_resource($result)) {
$this->_lastResult = $result;
}
}
if ($result === false) {
echo '<pre>',
htmlspecialchars($sql),
htmlspecialchars(mysql_error($connection)),
'</pre>';
trigger_error(mysql_error($connection));
}
return $result;
}
/**
* Send multiple SQL queries.
*
* @param string|array $sqlList An array of single query strings or a string of queries terminated with semi-colons.
*/
public function queryN($sqlList) {
if (!is_array($sqlList)) {
$sqlList = explode(';', $sqlList);
}
foreach ($sqlList as $sql) {
$sql = trim($sql);
if ($sql) {
$this->query($sql);
}
}
}
/**
* Using RestrictedTables, determine if a table should be exported or not
*
* @param string $tableName Name of the table to check
* @return bool True if table should be exported, false otherwise
*/
public function shouldExport($tableName) {
return empty($this->restrictedTables) || in_array(strtolower($tableName), $this->restrictedTables);
}
/**
* Set database connection details.
*
* @param null $host
* @param null $username
* @param null $password
* @param null $dbName
*/
public function setConnection($host = null, $username = null, $password = null, $dbName = null) {
$this->_host = $host;
$this->_username = $username;
$this->_password = $password;
$this->_dbName = $dbName;
}
/**
* Echo a status message to the console.
*
* @param $msg
*/
public function status($msg) {
if (defined('CONSOLE')) {
echo $msg;
}
}
/**
* Returns an array of all the expected export tables and expected columns in the exports.
*
* When exporting tables using ExportTable() all of the columns in this structure will always be exported
* in the order here, regardless of how their order in the query.
*
* @return array
* @see vnExport::ExportTable()
*/
public function structures($newStructures = false) {
if (is_array($newStructures)) {
$this->_structures = $newStructures;
}
return $this->_structures;
}
/**
* Whether or not to use compression on the output file.
*
* @param bool $value The value to set or NULL to just return the value.
* @return bool
*/
public function useCompression($value = null) {
if ($value !== null) {
$this->_useCompression = $value;
}
return $this->_useCompression && $this->destination == 'file' && function_exists('gzopen');
}
/**
* Returns the version of export file that will be created with this export.
* The version is used when importing to determine the format of this file.
*
* @return string
*/
public function version() {
return APPLICATION_VERSION;
}
/**
* Checks whether or not a table and columns exist in the database.
*
* @param string $table The name of the table to check.
* @param array $columns An array of column names to check.
* @return bool|array The method will return one of the following
* - true: If table and all of the columns exist.
* - false: If the table does not exist.
* - array: The names of the missing columns if one or more columns don't exist.
*/
public function exists($table, $columns = array()) {
static $_exists = array();
if (!isset($_exists[$table])) {
$result = $this->query("show table status like ':_$table'", true);
if (!$result) {
$_exists[$table] = false;
} elseif (!mysql_fetch_assoc($result)) {
$_exists[$table] = false;
} else {
mysql_free_result($result);
$desc = $this->query('describe :_' . $table);
if ($desc === false) {
$_exists[$table] = false;
} else {
if (is_string($desc)) {
die($desc);
}
$cols = array();
while (($TD = mysql_fetch_assoc($desc)) !== false) {
$cols[$TD['Field']] = $TD;
}
mysql_free_result($desc);
$_exists[$table] = $cols;
}
}
}
if ($_exists[$table] == false) {
return false;
}
$columns = (array)$columns;
if (count($columns) == 0) {
return true;
}
$missing = array();
$cols = array_keys($_exists[$table]);
foreach ($columns as $column) {
if (!in_array($column, $cols)) {
$missing[] = $column;
}
}
return count($missing) == 0 ? true : $missing;
}
/**
* Checks all required source tables are present.
*
* @param array $requiredTables
* @return array|string
*/
public function verifySource($requiredTables) {
$missingTables = false;
$countMissingTables = 0;
$missingColumns = array();
foreach ($requiredTables as $reqTable => $reqColumns) {
$tableDescriptions = $this->query('describe :_' . $reqTable);
//echo 'describe '.$prefix.$reqTable;
if ($tableDescriptions === false) { // Table doesn't exist
$countMissingTables++;
if ($missingTables !== false) {
$missingTables .= ', ' . $reqTable;
} else {
$missingTables = $reqTable;
}
} else {
// Build array of columns in this table
$presentColumns = array();
while (($TD = mysql_fetch_assoc($tableDescriptions)) !== false) {
$presentColumns[] = $TD['Field'];
}
// Compare with required columns
foreach ($reqColumns as $reqCol) {
if (!in_array($reqCol, $presentColumns)) {
$missingColumns[$reqTable][] = $reqCol;
}
}
mysql_free_result($tableDescriptions);
}
}
// Return results
if ($missingTables === false) {
if (count($missingColumns) > 0) {
$result = array();
// Build a string of missing columns.
foreach ($missingColumns as $table => $columns) {
$result[] = "The $table table is missing the following column(s): " . implode(', ', $columns);
}
return implode("<br />\n", $result);
} else {
return true;
} // Nothing missing!
} elseif ($countMissingTables == count($requiredTables)) {
$result = 'The required tables are not present in the database. Make sure you entered the correct database name and prefix and try again.';
// Guess the prefixes to notify the user.
$prefixes = $this->getDatabasePrefixes();
if (count($prefixes) == 1) {
$result .= ' Based on the database you provided, your database prefix is probably ' . implode(', ',
$prefixes);
} elseif (count($prefixes) > 0) {
$result .= ' Based on the database you provided, your database prefix is probably one of the following: ' . implode(', ',
$prefixes);
}
return $result;
} else {
return 'Missing required database tables: ' . $missingTables;
}
}
/**
* Start table write to file.
*
* @param $fp
* @param $tableName
* @param $exportStructure
*/
public function writeBeginTable($fp, $tableName, $exportStructure) {
$tableHeader = '';
foreach ($exportStructure as $key => $value) {
if (is_numeric($key)) {
$column = $value;
$type = '';
} else {
$column = $key;
$type = $value;
}
if (strlen($tableHeader) > 0) {
$tableHeader .= self::DELIM;
}
if ($type) {
$tableHeader .= $column . ':' . $type;
} else {
$tableHeader .= $column;
}
}
fwrite($fp, 'Table: ' . $tableName . self::NEWLINE);
fwrite($fp, $tableHeader . self::NEWLINE);
}
/**
* End table write to file.
*
* @param $fp
*/
public function writeEndTable($fp) {
fwrite($fp, self::NEWLINE);
}
/**
* Write a table's row to file.
*
* @param $fp
* @param $row
* @param $exportStructure
* @param $revMappings
*/
public function writeRow($fp, $row, $exportStructure, $revMappings) {
$this->currentRow =& $row;
// Loop through the columns in the export structure and grab their values from the row.
$exRow = array();
foreach ($exportStructure as $field => $type) {
// Get the value of the export.
$value = null;
if (isset($revMappings[$field]) && isset($row[$revMappings[$field]['Column']])) {
// The column is mapped.
$value = $row[$revMappings[$field]['Column']];
} elseif (array_key_exists($field, $row)) {
// The column has an exact match in the export.
$value = $row[$field];
}
// Check to see if there is a callback filter.
$filtered = false;
if (isset($revMappings[$field]['Filter'])) {
$callback = $revMappings[$field]['Filter'];
$row2 =& $row;
$value = call_user_func($callback, $value, $field, $row2, $field);
$row = $this->currentRow;
$filtered = true;
}
// Format the value for writing.
if (is_null($value)) {
$value = self::NULL;
} elseif (is_integer($value)) {
// Do nothing, formats as is.
// Only allow ints because PHP allows weird shit as numeric like "\n\n.1"
} elseif (is_string($value) || is_numeric($value)) {
// Check to see if there is a callback filter.
if (!isset($revMappings[$field])) {
//$value = call_user_func($Filters[$field], $value, $field, $row);
} else {
if (self::$mb && mb_detect_encoding($value) != 'UTF-8') {
$value = utf8_encode($value);
}
}
$value = str_replace(array("\r\n", "\r"), array(self::NEWLINE, self::NEWLINE), $value);
$value = self::QUOTE
. str_replace(self::$escapeSearch, self::$escapeReplace, $value)
. self::QUOTE;
} elseif (is_bool($value)) {
$value = $value ? 1 : 0;
} else {
// Unknown format.
$value = self::NULL;
}
$exRow[] = $value;
}
// Write the data.
fwrite($fp, implode(self::DELIM, $exRow));
// End the record.
fwrite($fp, self::NEWLINE);
}
/**
* SQL to get the file extension from a string.
*
* @param string $columnName
* @return string SQL.
*/
public static function fileExtension($columnName) {
return "right($columnName, instr(reverse($columnName), '.') - 1)";
}
}
?>