aboutsummaryrefslogtreecommitdiffstats
path: root/Source/SPSQLExporter.m
diff options
context:
space:
mode:
authorstuconnolly <stuart02@gmail.com>2010-08-12 12:56:20 +0000
committerstuconnolly <stuart02@gmail.com>2010-08-12 12:56:20 +0000
commitfc02f913371d522a025c47824fafeba8e3174da1 (patch)
tree086561fd418bf35139dda47d698d7923196a4f30 /Source/SPSQLExporter.m
parent50f15c41936be3fb62384834bb3629e09018e67b (diff)
downloadsequelpro-fc02f913371d522a025c47824fafeba8e3174da1.tar.gz
sequelpro-fc02f913371d522a025c47824fafeba8e3174da1.tar.bz2
sequelpro-fc02f913371d522a025c47824fafeba8e3174da1.zip
Various export enhancements and fixes, including:
- A new SPExportFile class, providing an abstract interface to the handling and creation of export files. - Enables the centralisation of all file/file handle creation logic as well as better support for handling situations where files fail to be created, including files that already exist at the export location. - New SPExportFileHandleStatus constants to support the reporting of file handle creation. - Update SPExporter to use the new file class instead of directly using an instance of SPFileHandle. - Add the necessary logic to deal with files that already exist on disk, by providing the user with 3 options: cancel the export, ignore the files in question or overwrite them. We might want to enhance this to make new files sequential in name to prevent overwriting. Fixes issue #742. - New SPExportFileUtilities category, which centralises all the logic relating to writing export type headers as well as dealing with problems occurred during file/file handle creation. - Improve feedback given on the export progress sheet during export initialisation. - Tidy up and improve comments.
Diffstat (limited to 'Source/SPSQLExporter.m')
-rw-r--r--Source/SPSQLExporter.m441
1 files changed, 228 insertions, 213 deletions
diff --git a/Source/SPSQLExporter.m b/Source/SPSQLExporter.m
index 83f10b46..c53a08ea 100644
--- a/Source/SPSQLExporter.m
+++ b/Source/SPSQLExporter.m
@@ -58,19 +58,23 @@
/**
* Initialise an instance of SPSQLExporter using the supplied delegate.
+ *
+ * @param exportDelegate The exporter delegate
+ *
+ * @return The initialised instance
*/
- (id)initWithDelegate:(NSObject *)exportDelegate
{
if ((self = [super init])) {
SPExportDelegateConformsToProtocol(exportDelegate, @protocol(SPSQLExporterProtocol));
-
+
[self setDelegate:exportDelegate];
[self setSqlExportCurrentTable:nil];
-
+
[self setSqlInsertDivider:SPSQLInsertEveryNDataBytes];
[self setSqlInsertAfterNValue:250000];
}
-
+
return self;
}
@@ -83,34 +87,34 @@
NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
sqlTableDataInstance = [[[SPTableData alloc] init] autorelease];
[sqlTableDataInstance setConnection:connection];
-
+
MCPResult *queryResult;
MCPStreamingResult *streamingResult;
-
+
NSArray *row;
NSString *tableName;
NSDictionary *tableDetails;
NSMutableArray *tableColumnNumericStatus;
SPTableType tableType = SPTableTypeTable;
-
+
id createTableSyntax = nil;
NSUInteger i, j, k, t, s, rowCount, queryLength, lastProgressValue;
-
+
BOOL sqlOutputIncludeStructure;
BOOL sqlOutputIncludeContent;
BOOL sqlOutputIncludeDropSyntax;
-
+
NSMutableArray *tables = [NSMutableArray array];
NSMutableArray *procs = [NSMutableArray array];
NSMutableArray *funcs = [NSMutableArray array];
-
+
NSMutableString *metaString = [NSMutableString string];
NSMutableString *cellValue = [NSMutableString string];
NSMutableString *errors = [[NSMutableString alloc] init];
NSMutableString *sqlString = [[NSMutableString alloc] init];
-
+
NSMutableDictionary *viewSyntaxes = [NSMutableDictionary dictionary];
-
+
// Check that we have all the required info before starting the export
if ((![self sqlExportTables]) || ([[self sqlExportTables] count] == 0) ||
(![self sqlDatabaseHost]) || ([[self sqlDatabaseHost] isEqualToString:@""]) ||
@@ -122,20 +126,20 @@
[pool release];
return;
}
-
+
// Inform the delegate that the export process is about to begin
[delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBegin:) withObject:self waitUntilDone:NO];
-
+
// Mark the process as running
[self setExportProcessIsRunning:YES];
-
+
// Clear errors
[self setSqlExportErrors:@""];
-
+
// Copy over the selected item names into tables in preparation for iteration
NSMutableArray *targetArray;
-
- for (NSArray *item in [self sqlExportTables])
+
+ for (NSArray *item in [self sqlExportTables])
{
// Check for cancellation flag
if ([self isCancelled]) {
@@ -144,7 +148,7 @@
[pool release];
return;
}
-
+
switch ([NSArrayObjectAtIndex(item, 4) intValue]) {
case SPTableTypeProc:
targetArray = procs;
@@ -156,10 +160,10 @@
targetArray = tables;
break;
}
-
+
[targetArray addObject:item];
}
-
+
// If required write the UTF-8 Byte Order Mark (BOM)
if ([self sqlOutputIncludeUTF8BOM]) {
[metaString setString:@"\xef\xbb\xbf"];
@@ -168,7 +172,7 @@
else {
[metaString setString:@"# ************************************************************\n"];
}
-
+
// Add the dump header to the dump file
[metaString appendString:@"# Sequel Pro SQL dump\n"];
[metaString appendString:[NSString stringWithFormat:@"# Version %@\n#\n", [[NSBundle mainBundle] objectForInfoDictionaryKey:@"CFBundleVersion"]]];
@@ -177,152 +181,157 @@
[metaString appendString:[NSString stringWithFormat:@"# Database: %@\n", [self sqlDatabaseName]]];
[metaString appendString:[NSString stringWithFormat:@"# Generation Time: %@\n", [NSDate date]]];
[metaString appendString:@"# ************************************************************\n\n\n"];
-
+
// Add commands to store the client encodings used when importing and set to UTF8 to preserve data
[metaString appendString:@"/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n"];
[metaString appendString:@"/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n"];
[metaString appendString:@"/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n"];
[metaString appendString:@"/*!40101 SET NAMES utf8 */;\n"];
-
+
[metaString appendString:@"/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n"];
[metaString appendString:@"/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n"];
[metaString appendString:@"/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\n\n\n"];
-
- [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:[self exportOutputEncoding]]];
-
+
+ [[self exportOutputFile] writeData:[metaString dataUsingEncoding:[self exportOutputEncoding]]];
+
// Loop through the selected tables
- for (NSArray *table in tables)
+ for (NSArray *table in tables)
{
// Check for cancellation flag
if ([self isCancelled]) {
[pool release];
return;
}
-
+
[self setSqlCurrentTableExportIndex:[self sqlCurrentTableExportIndex]+1];
tableName = NSArrayObjectAtIndex(table, 0);
-
+
sqlOutputIncludeStructure = [NSArrayObjectAtIndex(table, 1) boolValue];
sqlOutputIncludeContent = [NSArrayObjectAtIndex(table, 2) boolValue];
sqlOutputIncludeDropSyntax = [NSArrayObjectAtIndex(table, 3) boolValue];
-
+
// Set the current table
[self setSqlExportCurrentTable:tableName];
-
+
// Inform the delegate that we are about to start fetcihing data for the current table
[delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginFetchingData:) withObject:self waitUntilDone:NO];
-
+
lastProgressValue = 0;
-
+
// Add the name of table
- [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Dump of table %@\n# ------------------------------------------------------------\n\n", tableName] dataUsingEncoding:[self exportOutputEncoding]]];
-
+ [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"# Dump of table %@\n# ------------------------------------------------------------\n\n", tableName] dataUsingEncoding:[self exportOutputEncoding]]];
+
// Determine whether this table is a table or a view via the CREATE TABLE command, and keep the create table syntax
queryResult = [connection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE %@", [tableName backtickQuotedString]]];
-
+
[queryResult setReturnDataAsStrings:YES];
-
+
if ([queryResult numOfRows]) {
tableDetails = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]];
-
+
if ([tableDetails objectForKey:@"Create View"]) {
[viewSyntaxes setValue:[[[[tableDetails objectForKey:@"Create View"] copy] autorelease] createViewSyntaxPrettifier] forKey:tableName];
createTableSyntax = [self _createViewPlaceholderSyntaxForView:tableName];
tableType = SPTableTypeView;
- }
+ }
else {
createTableSyntax = [[[tableDetails objectForKey:@"Create Table"] copy] autorelease];
tableType = SPTableTypeTable;
}
-
+
[tableDetails release];
}
-
+
if ([connection queryErrored]) {
[errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]];
- [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n\n\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]];
+
+ [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"# Error: %@\n\n\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]];
+
continue;
}
-
+
// Add a 'DROP TABLE' command if required
if (sqlOutputIncludeDropSyntax)
- [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"DROP %@ IF EXISTS %@;\n\n", ((tableType == SPTableTypeTable) ? @"TABLE" : @"VIEW"), [tableName backtickQuotedString]]
+ [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"DROP %@ IF EXISTS %@;\n\n", ((tableType == SPTableTypeTable) ? @"TABLE" : @"VIEW"), [tableName backtickQuotedString]]
dataUsingEncoding:[self exportOutputEncoding]]];
-
+
+
// Add the create syntax for the table if specified in the export dialog
if (sqlOutputIncludeStructure && createTableSyntax) {
-
+
if ([createTableSyntax isKindOfClass:[NSData class]]) {
createTableSyntax = [[[NSString alloc] initWithData:createTableSyntax encoding:[self exportOutputEncoding]] autorelease];
}
-
- [[self exportOutputFileHandle] writeData:[createTableSyntax dataUsingEncoding:NSUTF8StringEncoding]];
- [[self exportOutputFileHandle] writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
+
+ [[self exportOutputFile] writeData:[createTableSyntax dataUsingEncoding:NSUTF8StringEncoding]];
+ [[self exportOutputFile] writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
}
-
+
// Add the table content if required
if (sqlOutputIncludeContent && (tableType == SPTableTypeTable)) {
-
+
// Retrieve the table details via the data class, and use it to build an array containing column numeric status
tableDetails = [NSDictionary dictionaryWithDictionary:[sqlTableDataInstance informationForTable:tableName]];
-
+
NSUInteger colCount = [[tableDetails objectForKey:@"columns"] count];
-
+
tableColumnNumericStatus = [NSMutableArray arrayWithCapacity:colCount];
-
- for (j = 0; j < colCount; j++)
+
+ for (j = 0; j < colCount; j++)
{
// Check for cancellation flag
if ([self isCancelled]) {
[pool release];
return;
}
-
+
NSString *tableColumnTypeGrouping = [NSArrayObjectAtIndex([tableDetails objectForKey:@"columns"], j) objectForKey:@"typegrouping"];
-
+
[tableColumnNumericStatus addObject:[NSNumber numberWithBool:([tableColumnTypeGrouping isEqualToString:@"bit"] || [tableColumnTypeGrouping isEqualToString:@"integer"] || [tableColumnTypeGrouping isEqualToString:@"float"])]];
}
-
+
// Retrieve the number of rows in the table for progress bar drawing
NSArray *rowArray = [[connection queryString:[NSString stringWithFormat:@"SELECT COUNT(1) FROM %@", [tableName backtickQuotedString]]] fetchRowAsArray];
+
if ([connection queryErrored] || ![rowArray count]) {
[errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]];
[[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n\n\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]];
+
continue;
}
-
+
rowCount = [NSArrayObjectAtIndex(rowArray, 0) integerValue];
-
+
// Set up a result set in streaming mode
streamingResult = [[connection streamingQueryString:[NSString stringWithFormat:@"SELECT * FROM %@", [tableName backtickQuotedString]] useLowMemoryBlockingStreaming:([self exportUsingLowMemoryBlockingStreaming])] retain];
-
+
NSArray *fieldNames = [streamingResult fetchFieldNames];
-
+
// Inform the delegate that we are about to start writing data for the current table
[delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginWritingData:) withObject:self waitUntilDone:NO];
-
+
if (rowCount) {
queryLength = 0;
-
+
// Lock the table for writing and disable keys if supported
[metaString setString:@""];
[metaString appendString:[NSString stringWithFormat:@"LOCK TABLES %@ WRITE;\n", [tableName backtickQuotedString]]];
[metaString appendString:[NSString stringWithFormat:@"/*!40000 ALTER TABLE %@ DISABLE KEYS */;\n\n", [tableName backtickQuotedString]]];
-
- [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:[self exportOutputEncoding]]];
-
+
+ [[self exportOutputFile] writeData:[metaString dataUsingEncoding:[self exportOutputEncoding]]];
+
// Construct the start of the insertion command
- [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"INSERT INTO %@ (%@)\nVALUES\n\t(", [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]] dataUsingEncoding:NSUTF8StringEncoding]];
-
+ [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"INSERT INTO %@ (%@)\nVALUES\n\t(", [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]] dataUsingEncoding:NSUTF8StringEncoding]];
+
// Iterate through the rows to construct a VALUES group for each
j = 0, k = 0;
-
+
NSAutoreleasePool *sqlExportPool = [[NSAutoreleasePool alloc] init];
-
+
// Inform the delegate that we are about to start writing the data to disk
[delegate performSelectorOnMainThread:@selector(sqlExportProcessWillBeginWritingData:) withObject:self waitUntilDone:NO];
-
- while (row = [streamingResult fetchNextRowAsArray])
+
+ while (row = [streamingResult fetchNextRowAsArray])
{
// Check for cancellation flag
if ([self isCancelled]) {
@@ -330,42 +339,42 @@
[streamingResult cancelResultLoad];
[sqlExportPool release];
[pool release];
-
+
return;
}
-
+
j++;
k++;
-
+
[sqlString setString:@""];
-
- // Update the progress
+
+ // Update the progress
NSUInteger progress = (j * ([self exportMaxProgress] / rowCount));
-
+
if (progress > lastProgressValue) {
[self setExportProgressValue:progress];
lastProgressValue = progress;
-
+
// Inform the delegate that the export's progress has been updated
[delegate performSelectorOnMainThread:@selector(sqlExportProcessProgressUpdated:) withObject:self waitUntilDone:NO];
}
-
- for (t = 0; t < colCount; t++)
+
+ for (t = 0; t < colCount; t++)
{
// Check for cancellation flag
if ([self isCancelled]) {
[sqlExportPool release];
[pool release];
-
+
return;
}
-
+
id object = NSArrayObjectAtIndex(row, t);
-
+
// Add NULL values directly to the output row
if ([object isMemberOfClass:[NSNull class]]) {
[sqlString appendString:@"NULL"];
- }
+ }
// If the field is off type BIT, the values need a binary prefix of b'x'.
else if ([[NSArrayObjectAtIndex([tableDetails objectForKey:@"columns"], t) objectForKey:@"type"] isEqualToString:@"BIT"]) {
[sqlString appendString:@"b'"];
@@ -374,38 +383,38 @@
}
// Add data types directly as hex data
else if ([object isKindOfClass:[NSData class]]) {
-
+
if ([self sqlOutputEncodeBLOBasHex]) {
[sqlString appendString:@"X'"];
[sqlString appendString:[connection prepareBinaryData:object]];
}
else {
[sqlString appendString:@"'"];
-
+
NSString *data = [[NSString alloc] initWithData:object encoding:[self exportOutputEncoding]];
-
+
if (data == nil) {
data = [[NSString alloc] initWithData:object encoding:NSASCIIStringEncoding];
}
-
+
[sqlString appendString:data];
-
+
[data release];
}
-
+
[sqlString appendString:@"'"];
- }
+ }
else {
[cellValue setString:[object description]];
-
+
// Add empty strings as a pair of quotes
if ([cellValue length] == 0) {
[sqlString appendString:@"''"];
- }
- else {
+ }
+ else {
if ([NSArrayObjectAtIndex(tableColumnNumericStatus, t) boolValue]) {
[sqlString appendString:cellValue];
- }
+ }
// Otherwise add a quoted string with special characters escaped
else {
[sqlString appendString:@"'"];
@@ -414,16 +423,16 @@
}
}
}
-
+
// Add the field separator if this isn't the last cell in the row
if (t != ([row count] - 1)) [sqlString appendString:@","];
}
-
+
queryLength += [sqlString length];
-
+
// Close this VALUES group and set up the next one if appropriate
if (j != rowCount) {
-
+
// If required start a new INSERT statment
if ((([self sqlInsertDivider] == SPSQLInsertEveryNDataBytes) && (queryLength >= ([self sqlInsertAfterNValue] * 1024))) ||
(([self sqlInsertDivider] == SPSQLInsertEveryNRows) && (k == [self sqlInsertAfterNValue])))
@@ -433,79 +442,79 @@
[sqlString appendString:@" ("];
[sqlString appendString:[fieldNames componentsJoinedAndBacktickQuoted]];
[sqlString appendString:@")\nVALUES\n\t("];
-
+
queryLength = 0, k = 0;
-
+
// Use the opportunity to drain and reset the autorelease pool
[sqlExportPool release];
sqlExportPool = [[NSAutoreleasePool alloc] init];
- }
+ }
else {
[sqlString appendString:@"),\n\t("];
}
- }
+ }
else {
[sqlString appendString:@")"];
}
-
+
// Write this row to the file
- [[self exportOutputFileHandle] writeData:[sqlString dataUsingEncoding:NSUTF8StringEncoding]];
+ [[self exportOutputFile] writeData:[sqlString dataUsingEncoding:NSUTF8StringEncoding]];
}
-
+
// Complete the command
- [[self exportOutputFileHandle] writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
-
+ [[self exportOutputFile] writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
+
// Unlock the table and re-enable keys if supported
[metaString setString:@""];
[metaString appendString:[NSString stringWithFormat:@"/*!40000 ALTER TABLE %@ ENABLE KEYS */;\n", [tableName backtickQuotedString]]];
[metaString appendString:@"UNLOCK TABLES;\n"];
-
- [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
-
+
+ [[self exportOutputFile] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
+
// Drain the autorelease pool
[sqlExportPool release];
}
-
+
if ([connection queryErrored]) {
[errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]];
-
+
if ([self sqlOutputIncludeErrors]) {
- [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]]
+ [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]]
dataUsingEncoding:NSUTF8StringEncoding]];
}
}
-
+
// Release the result set
[streamingResult release];
}
-
+
queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW TRIGGERS WHERE `Table` = %@ */;", [tableName tickQuotedString]]];
-
+
[queryResult setReturnDataAsStrings:YES];
-
+
if ([queryResult numOfRows]) {
-
+
[metaString setString:@"\n"];
[metaString appendString:@"DELIMITER ;;\n"];
-
- for (s = 0; s < [queryResult numOfRows]; s++)
+
+ for (s = 0; s < [queryResult numOfRows]; s++)
{
// Check for cancellation flag
if ([self isCancelled]) {
[pool release];
return;
}
-
+
NSDictionary *triggers = [[NSDictionary alloc] initWithDictionary:[queryResult fetchRowAsDictionary]];
-
+
// Definer is user@host but we need to escape it to `user`@`host`
NSArray *triggersDefiner = [[triggers objectForKey:@"Definer"] componentsSeparatedByString:@"@"];
-
- NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@",
+
+ NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@",
[NSArrayObjectAtIndex(triggersDefiner, 0) backtickQuotedString],
[NSArrayObjectAtIndex(triggersDefiner, 1) backtickQuotedString]
];
-
+
[metaString appendString:[NSString stringWithFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\" */;;\n", [triggers objectForKey:@"sql_mode"]]];
[metaString appendString:@"/*!50003 CREATE */ "];
[metaString appendString:[NSString stringWithFormat:@"/*!50017 DEFINER=%@ */ ", escapedDefiner]];
@@ -516,82 +525,82 @@
[[triggers objectForKey:@"Table"] backtickQuotedString],
[triggers objectForKey:@"Statement"]
]];
-
+
[triggers release];
}
-
+
[metaString appendString:@"DELIMITER ;\n"];
[metaString appendString:@"/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;\n"];
-
- [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
+
+ [[self exportOutputFile] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
}
-
+
if ([connection queryErrored]) {
[errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]];
-
+
if ([self sqlOutputIncludeErrors]) {
- [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]]
+ [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]]
dataUsingEncoding:NSUTF8StringEncoding]];
}
}
-
+
// Add an additional separator between tables
- [[self exportOutputFileHandle] writeData:[[NSString stringWithString:@"\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
+ [[self exportOutputFile] writeData:[[NSString stringWithString:@"\n\n"] dataUsingEncoding:NSUTF8StringEncoding]];
}
-
+
// Process any deferred views, adding commands to delete the placeholder tables and add the actual views
- for (tableName in viewSyntaxes)
+ for (tableName in viewSyntaxes)
{
// Check for cancellation flag
if ([self isCancelled]) {
[pool release];
return;
}
-
+
[metaString setString:@"\n\n"];
// Add the name of table
[metaString appendFormat:@"# Replace placeholder table for %@ with correct view syntax\n# ------------------------------------------------------------\n\n", tableName];
[metaString appendFormat:@"DROP TABLE %@;\n", [tableName backtickQuotedString]];
[metaString appendFormat:@"%@;\n", [viewSyntaxes objectForKey:tableName]];
-
- [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
+
+ [[self exportOutputFile] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
}
-
+
// Export procedures and functions
- for (NSString *procedureType in [NSArray arrayWithObjects:@"PROCEDURE", @"FUNCTION", nil])
+ for (NSString *procedureType in [NSArray arrayWithObjects:@"PROCEDURE", @"FUNCTION", nil])
{
// Check for cancellation flag
if ([self isCancelled]) {
[pool release];
return;
}
-
+
// Retrieve the array of selected procedures or functions, and skip export if not selected
NSMutableArray *items;
-
+
if ([procedureType isEqualToString:@"PROCEDURE"]) items = procs;
else items = funcs;
-
+
if ([items count] == 0) continue;
-
+
// Retrieve the definitions
queryResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW %@ STATUS WHERE `Db` = %@ */;", procedureType,
[[self sqlDatabaseName] tickQuotedString]]];
-
+
[queryResult setReturnDataAsStrings:YES];
-
+
if ([queryResult numOfRows]) {
-
+
[metaString setString:@"\n"];
[metaString appendString:@"--\n"];
[metaString appendString:[NSString stringWithFormat:@"-- Dumping routines (%@) for database %@\n", procedureType,
[[self sqlDatabaseName] tickQuotedString]]];
-
+
[metaString appendString:@"--\n"];
[metaString appendString:@"DELIMITER ;;\n\n"];
-
+
// Loop through the definitions, exporting if enabled
- for (s = 0; s < [queryResult numOfRows]; s++)
+ for (s = 0; s < [queryResult numOfRows]; s++)
{
// Check for cancellation flag
if ([self isCancelled]) {
@@ -612,7 +621,7 @@
[pool release];
return;
}
-
+
if ([NSArrayObjectAtIndex(item, 0) isEqualToString:procedureName]) {
itemFound = YES;
sqlOutputIncludeStructure = [NSArrayObjectAtIndex(item, 1) boolValue];
@@ -634,46 +643,46 @@
[metaString appendString:[NSString stringWithFormat:@"/*!50003 DROP %@ IF EXISTS %@ */;;\n", procedureType,
[procedureName backtickQuotedString]]];
}
-
+
// Only continue if the 'CREATE SYNTAX' is required
if (!sqlOutputIncludeStructure) {
[proceduresList release];
continue;
}
-
+
// Definer is user@host but we need to escape it to `user`@`host`
NSArray *procedureDefiner = [[proceduresList objectForKey:@"Definer"] componentsSeparatedByString:@"@"];
-
- NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@",
+
+ NSString *escapedDefiner = [NSString stringWithFormat:@"%@@%@",
[NSArrayObjectAtIndex(procedureDefiner, 0) backtickQuotedString],
[NSArrayObjectAtIndex(procedureDefiner, 1) backtickQuotedString]
];
-
+
MCPResult *createProcedureResult = [connection queryString:[NSString stringWithFormat:@"/*!50003 SHOW CREATE %@ %@ */;;", procedureType,
[procedureName backtickQuotedString]]];
[createProcedureResult setReturnDataAsStrings:YES];
if ([connection queryErrored]) {
[errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]];
-
+
if ([self sqlOutputIncludeErrors]) {
- [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]];
+ [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]];
}
[proceduresList release];
continue;
}
-
+
NSDictionary *procedureInfo = [[NSDictionary alloc] initWithDictionary:[createProcedureResult fetchRowAsDictionary]];
-
+
[metaString appendString:[NSString stringWithFormat:@"/*!50003 SET SESSION SQL_MODE=\"%@\"*/;;\n", [procedureInfo objectForKey:@"sql_mode"]]];
-
+
NSString *createProcedure = [procedureInfo objectForKey:[NSString stringWithFormat:@"Create %@", [procedureType capitalizedString]]];
-
+
// A NULL result indicates a permission problem
if ([createProcedure isNSNull]) {
NSString *errorString = [NSString stringWithFormat:NSLocalizedString(@"Could not export the %@ '%@' because of a permisions error.\n", @"Procedure/function export permission error"), procedureType, procedureName];
[errors appendString:errorString];
if ([self sqlOutputIncludeErrors]) {
- [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", errorString] dataUsingEncoding:NSUTF8StringEncoding]];
+ [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"# Error: %@\n", errorString] dataUsingEncoding:NSUTF8StringEncoding]];
}
[proceduresList release];
[procedureInfo release];
@@ -682,7 +691,7 @@
NSRange procedureRange = [createProcedure rangeOfString:procedureType options:NSCaseInsensitiveSearch];
NSString *procedureBody = [createProcedure substringFromIndex:procedureRange.location];
-
+
// /*!50003 CREATE*/ /*!50020 DEFINER=`sequelpro`@`%`*/ /*!50003 PROCEDURE `p`()
// BEGIN
// /* This procedure does nothing */
@@ -690,62 +699,64 @@
//
// Build the CREATE PROCEDURE string to include MySQL Version limiters
[metaString appendString:[NSString stringWithFormat:@"/*!50003 CREATE*/ /*!50020 DEFINER=%@*/ /*!50003 %@ */;;\n\n", escapedDefiner, procedureBody]];
-
+
[procedureInfo release];
[proceduresList release];
-
+
[metaString appendString:@"/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;;\n"];
}
-
+
[metaString appendString:@"DELIMITER ;\n"];
-
- [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
+
+ [[self exportOutputFile] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
}
-
+
if ([connection queryErrored]) {
[errors appendString:[NSString stringWithFormat:@"%@\n", [connection getLastErrorMessage]]];
-
+
if ([self sqlOutputIncludeErrors]) {
- [[self exportOutputFileHandle] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]];
+ [[self exportOutputFile] writeData:[[NSString stringWithFormat:@"# Error: %@\n", [connection getLastErrorMessage]] dataUsingEncoding:NSUTF8StringEncoding]];
}
}
-
+
}
-
+
// Restore unique checks, foreign key checks, and other settings saved at the start
[metaString setString:@"\n"];
[metaString appendString:@"/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\n"];
[metaString appendString:@"/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\n"];
[metaString appendString:@"/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n"];
-
+
// Restore the client encoding to the original encoding before import
[metaString appendString:@"/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n"];
[metaString appendString:@"/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\n"];
[metaString appendString:@"/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\n"];
-
+
// Write footer-type information to the file
- [[self exportOutputFileHandle] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
-
+ [[self exportOutputFile] writeData:[metaString dataUsingEncoding:NSUTF8StringEncoding]];
+
// Set export errors
[self setSqlExportErrors:errors];
-
+
[errors release];
[sqlString release];
-
+
// Close the file
- [[self exportOutputFileHandle] closeFile];
-
+ [[self exportOutputFile] close];
+
// Mark the process as not running
[self setExportProcessIsRunning:NO];
-
+
// Inform the delegate that the export process is complete
[delegate performSelectorOnMainThread:@selector(sqlExportProcessComplete:) withObject:self waitUntilDone:NO];
-
+
[pool release];
}
/**
* Returns whether or not any export errors occurred by examing the length of the errors string.
+ *
+ * @return A BOOL indicating the occurrence of errors
*/
- (BOOL)didExportErrorsOccur
{
@@ -753,85 +764,89 @@
}
/**
- * Retrieve information for a view and use that to construct a CREATE TABLE string for an equivalent basic
- * table. Allows the construction of placeholder tables to resolve view interdependencies in dumps.
+ * Retrieve information for a view and use that to construct a CREATE TABLE string for an equivalent basic
+ * table. Allows the construction of placeholder tables to resolve view interdependencies within dumps.
+ *
+ * @param viewName The name of the view for which the placeholder is to be created for.
+ *
+ * @return The CREATE TABLE placeholder syntax
*/
- (NSString *)_createViewPlaceholderSyntaxForView:(NSString *)viewName
{
NSInteger i, j;
NSMutableString *placeholderSyntax;
-
+
// Get structured information for the view via the SPTableData parsers
NSDictionary *viewInformation = [sqlTableDataInstance informationForView:viewName];
-
+
if (!viewInformation) return nil;
-
+
NSArray *viewColumns = [viewInformation objectForKey:@"columns"];
// Set up the start of the placeholder string and initialise an empty field string
placeholderSyntax = [[NSMutableString alloc] initWithFormat:@"CREATE TABLE %@ (\n", [viewName backtickQuotedString]];
-
+
NSMutableString *fieldString = [[NSMutableString alloc] init];
-
+
// Loop through the columns, creating an appropriate column definition for each and appending it to the syntax string
- for (i = 0; i < [viewColumns count]; i++)
+ for (i = 0; i < [viewColumns count]; i++)
{
NSDictionary *column = NSArrayObjectAtIndex(viewColumns, i);
-
+
[fieldString setString:[[column objectForKey:@"name"] backtickQuotedString]];
-
+
// Add the type and length information as appropriate
if ([column objectForKey:@"length"]) {
[fieldString appendFormat:@" %@(%@)", [column objectForKey:@"type"], [column objectForKey:@"length"]];
- }
+ }
else if ([column objectForKey:@"values"]) {
[fieldString appendFormat:@" %@(", [column objectForKey:@"type"]];
-
- for (j = 0; j < [[column objectForKey:@"values"] count]; j++)
+
+ for (j = 0; j < [[column objectForKey:@"values"] count]; j++)
{
[fieldString appendFormat:@"'%@'%@", [connection prepareString:NSArrayObjectAtIndex([column objectForKey:@"values"], j)], ((j + 1) == [[column objectForKey:@"values"] count]) ? @"" : @","];
}
-
+
[fieldString appendString:@")"];
- }
+ }
else {
[fieldString appendFormat:@" %@", [column objectForKey:@"type"]];
}
-
+
// Field specification details
if ([[column objectForKey:@"unsigned"] integerValue] == 1) [fieldString appendString:@" UNSIGNED"];
if ([[column objectForKey:@"zerofill"] integerValue] == 1) [fieldString appendString:@" ZEROFILL"];
if ([[column objectForKey:@"binary"] integerValue] == 1) [fieldString appendString:@" BINARY"];
if ([[column objectForKey:@"null"] integerValue] == 0) [fieldString appendString:@" NOT NULL"];
-
+
// Provide the field default if appropriate
if ([column objectForKey:@"default"]) {
-
+
// Some MySQL server versions show a default of NULL for NOT NULL columns - don't export those
if ([column objectForKey:@"default"] == [NSNull null]) {
if ([[column objectForKey:@"null"] integerValue]) {
[fieldString appendString:@" DEFAULT NULL"];
}
- }
+ }
else if ([[column objectForKey:@"type"] isEqualToString:@"TIMESTAMP"] && [column objectForKey:@"default"] != [NSNull null] && [[[column objectForKey:@"default"] uppercaseString] isEqualToString:@"CURRENT_TIMESTAMP"]) {
[fieldString appendString:@" DEFAULT CURRENT_TIMESTAMP"];
- }
+ }
else {
[fieldString appendFormat:@" DEFAULT '%@'", [connection prepareString:[column objectForKey:@"default"]]];
}
}
-
+
// Extras aren't required for the temp table
// Add the field string to the syntax string
[placeholderSyntax appendFormat:@" %@%@\n", fieldString, (i == [viewColumns count] - 1) ? @"" : @","];
}
-
+
// Append the remainder of the table string
[placeholderSyntax appendString:@") ENGINE=MyISAM"];
-
+
// Clean up and return
[fieldString release];
-
+
return [placeholderSyntax autorelease];
}
@@ -845,7 +860,7 @@
[sqlDatabaseName release], sqlDatabaseName = nil;
[sqlExportCurrentTable release], sqlExportCurrentTable = nil;
[sqlDatabaseVersion release], sqlDatabaseVersion = nil;
-
+
[super dealloc];
}