aboutsummaryrefslogtreecommitdiffstats
path: root/TableDump.m
diff options
context:
space:
mode:
authorrowanbeentje <rowan@beent.je>2008-12-06 22:34:18 +0000
committerrowanbeentje <rowan@beent.je>2008-12-06 22:34:18 +0000
commitf4ba50508f2c89b3ca5df46a7ec09e0002f7e2e5 (patch)
tree1231d2377cde94c021d34394620a0dbbc3e185bd /TableDump.m
parent1a7130fea502cf627897984fc5a8453e9df9ab00 (diff)
downloadsequelpro-f4ba50508f2c89b3ca5df46a7ec09e0002f7e2e5.tar.gz
sequelpro-f4ba50508f2c89b3ca5df46a7ec09e0002f7e2e5.tar.bz2
sequelpro-f4ba50508f2c89b3ca5df46a7ec09e0002f7e2e5.zip
- Identified and corrected a major n^2 bottleneck in working with all query result sets - when iterating through a mysql result set, all the data up to each row was seeked through again. With this fixed SP can work with very large result sets at much, much higher speeds.
- Fixed incorrect query splitting in splitQueries - improves custom queries and imports. - CSV export now exports NULLs as an empty cell (eg "1,,3"). This resolved Issue #67. Correspondingly, CSV import now also accepts this syntax. - CSV and SQL exports no longer quote numeric values for file size and neatness improvements. - Multi-table exports now show progress through the tables in the text description, and use the progress bar for export of each table, for improved feedback. - The old "Export Table Content" menu has been split into two - "Current Browse View" which matches the old behaviour, and a new "Current Table" entry. This addresses Issue #97. - Exports now write data to a file stream as data is processed for export, and also parse data from the database as a stream where possible. This leads to lower memory usage, speed improvements, and much more accurate and constant feedback. - The export processes have been restructured and rewritten for further speed and memory improvements. - SQL exports now correctly export BLOB and TEXT contents ready for re-import into all systems. - SQL exports now group multiple VALUES for each INSERT INTO command for much, much faster imports of exported data.
Diffstat (limited to 'TableDump.m')
-rw-r--r--TableDump.m1378
1 files changed, 827 insertions, 551 deletions
diff --git a/TableDump.m b/TableDump.m
index 0fbd48d0..a40458d0 100644
--- a/TableDump.m
+++ b/TableDump.m
@@ -45,15 +45,15 @@
[tables removeAllObjects];
queryResult = (CMMCPResult *)[mySQLConnection listTables];
+ if ([queryResult numOfRows]) [queryResult dataSeek:0];
for ( i = 0 ; i < [queryResult numOfRows] ; i++ ) {
- [queryResult dataSeek:i];
[tables addObject:[NSMutableArray arrayWithObjects:
[NSNumber numberWithBool:YES], [[queryResult fetchRowAsArray] objectAtIndex:0], nil]];
}
- [exportDumpTableView reloadData];
- [exportMultipleCSVTableView reloadData];
- [exportMultipleXMLTableView reloadData];
+ [exportDumpTableView reloadData];
+ [exportMultipleCSVTableView reloadData];
+ [exportMultipleXMLTableView reloadData];
}
@@ -102,159 +102,198 @@
NSSavePanel *savePanel = [NSSavePanel savePanel];
NSString *currentDate = [[NSDate date] descriptionWithCalendarFormat:@"%d.%m.%Y" timeZone:nil locale:nil];
- switch ( tag ) {
- case 5:
+ switch ( tag ) {
+ case 5:
// export dump
- [self reloadTables:self];
- file = [NSString stringWithFormat:@"%@_dump %@.sql", [tableDocumentInstance database], currentDate];
- [savePanel setAccessoryView:exportDumpView];
- contextInfo = @"exportDump";
+ [self reloadTables:self];
+ file = [NSString stringWithFormat:@"%@_dump %@.sql", [tableDocumentInstance database], currentDate];
+ [savePanel setAccessoryView:exportDumpView];
+ contextInfo = @"exportDump";
break;
- case 6:
- // export table content as CSV
- file = [NSString stringWithFormat:@"%@.csv", [tableDocumentInstance table]];
- [savePanel setAccessoryView:exportCSVView];
- contextInfo = @"exportTableContentAsCSV";
+
+ // Export the full resultset for the currently selected table to a file in CSV format
+ case 6:
+ file = [NSString stringWithFormat:@"%@.csv", [tableDocumentInstance table]];
+ [savePanel setAccessoryView:exportCSVView];
+ contextInfo = @"exportTableContentAsCSV";
break;
- case 7:
- // export table content as XML
- file = [NSString stringWithFormat:@"%@.xml", [tableDocumentInstance table]];
- contextInfo = @"exportTableContentAsXML";
+
+ // Export the full resultset for the currently selected table to a file in XML format
+ case 7:
+ file = [NSString stringWithFormat:@"%@.xml", [tableDocumentInstance table]];
+ contextInfo = @"exportTableContentAsXML";
break;
- case 8:
- // export custom result as CSV
- file = @"customresult.csv";
- [savePanel setAccessoryView:exportCSVView];
- contextInfo = @"exportCustomResultAsCSV";
+
+ // Export the current "browse" view to a file in CSV format
+ case 8:
+ file = [NSString stringWithFormat:@"%@ view.csv", [tableDocumentInstance table]];
+ [savePanel setAccessoryView:exportCSVView];
+ contextInfo = @"exportBrowseViewAsCSV";
break;
- case 9:
- // export custom result as XML
- file = @"customresult.xml";
- contextInfo = @"exportCustomResultAsXML";
+
+ // Export the current "browse" view to a file in XML format
+ case 9:
+ file = [NSString stringWithFormat:@"%@ view.xml", [tableDocumentInstance table]];
+ contextInfo = @"exportBrowseViewAsXML";
break;
- case 10:
- // export multiple tables as CSV
- [self reloadTables:self];
- file = [NSString stringWithFormat:@"%@.csv", [tableDocumentInstance database]];
- [savePanel setAccessoryView:exportMultipleCSVView];
- contextInfo = @"exportMultipleTablesAsCSV";
+
+ // Export the current custom query result set to a file in CSV format
+ case 10:
+ file = @"customresult.csv";
+ [savePanel setAccessoryView:exportCSVView];
+ contextInfo = @"exportCustomResultAsCSV";
break;
- case 11:
- // export multiple tables as XML
- [self reloadTables:self];
- file = [NSString stringWithFormat:@"%@.xml", [tableDocumentInstance database]];
- [savePanel setAccessoryView:exportMultipleXMLView];
- contextInfo = @"exportMultipleTablesAsXML";
+
+ // Export the current custom query result set to a file in XML format
+ case 11:
+ file = @"customresult.xml";
+ contextInfo = @"exportCustomResultAsXML";
break;
- default:
- NSLog(@"ERROR: unknown export item with tag %d", tag);
- return;
+
+ // Export multiple tables to a file in CSV format
+ case 12:
+ [self reloadTables:self];
+ file = [NSString stringWithFormat:@"%@.csv", [tableDocumentInstance database]];
+ [savePanel setAccessoryView:exportMultipleCSVView];
+ contextInfo = @"exportMultipleTablesAsCSV";
break;
- }
+
+ // Export multiple tables to a file in XML format
+ case 13:
+ [self reloadTables:self];
+ file = [NSString stringWithFormat:@"%@.xml", [tableDocumentInstance database]];
+ [savePanel setAccessoryView:exportMultipleXMLView];
+ contextInfo = @"exportMultipleTablesAsXML";
+ break;
+ default:
+ NSLog(@"ERROR: unknown export item with tag %d", tag);
+ return;
+ break;
+ }
- //open savePanel
- [savePanel beginSheetForDirectory:[prefs objectForKey:@"savePath"]
- file:file modalForWindow:tableWindow modalDelegate:self
- didEndSelector:@selector(savePanelDidEnd:returnCode:contextInfo:) contextInfo:contextInfo];
+ // Open the savePanel
+ [savePanel beginSheetForDirectory:[prefs objectForKey:@"savePath"]
+ file:file modalForWindow:tableWindow modalDelegate:self
+ didEndSelector:@selector(savePanelDidEnd:returnCode:contextInfo:) contextInfo:contextInfo];
}
-- (void)savePanelDidEnd:(NSSavePanel *)sheet returnCode:(int)returnCode contextInfo:(NSString *)contextInfo
/*
- saves the export file
+ Save the export file; open a file handle, pass it to the appropriate data-writing function for streaming the export, and close the handle.
*/
+- (void)savePanelDidEnd:(NSSavePanel *)sheet returnCode:(int)returnCode contextInfo:(NSString *)contextInfo
{
- NSString *testString = @"";
- NSError **errorStr;
- id fileContent;
+ NSFileHandle *fileHandle = nil;
BOOL success;
-
- [sheet orderOut:self];
-
- if ( returnCode != NSOKButton )
- return;
- //save path to preferences
- [prefs setObject:[sheet directory] forKey:@"savePath"];
+ [sheet orderOut:self];
- //error if file exists and is not writable
+ if ( returnCode != NSOKButton )
+ return;
+
+ // Save path to preferences
+ [prefs setObject:[sheet directory] forKey:@"savePath"];
+
+ // Error if the file already exists and is not writable, and get a fileHandle to it.
if ( [[NSFileManager defaultManager] fileExistsAtPath:[sheet filename]] ) {
- if ( ![[NSFileManager defaultManager] isWritableFileAtPath:[sheet filename]] ) {
+ if ( ![[NSFileManager defaultManager] isWritableFileAtPath:[sheet filename]]
+ || !(fileHandle = [NSFileHandle fileHandleForWritingAtPath:[sheet filename]]) ) {
NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, nil, nil, nil,
NSLocalizedString(@"Couldn't replace the file. Be sure that you have the necessary privileges.", @"message of panel when file cannot be replaced"));
return;
}
+
+ // Truncate the file to zero bytes
+ [fileHandle truncateFileAtOffset:0];
+
+ // Otherwise attempt to create a file
} else {
- //error if file cannot be written
- // MLT: Need to use make use of NSError here
- if ( ![testString writeToFile:[sheet filename] atomically:YES encoding:NSUTF8StringEncoding error:nil] ) {
+ if ( ![[NSFileManager defaultManager] createFileAtPath:[sheet filename] contents:[NSData data] attributes:nil] ) {
NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, nil, nil, nil,
NSLocalizedString(@"Couldn't write to file. Be sure that you have the necessary privileges.", @"message of panel when file cannot be written"));
return;
}
+
+ // Retrieve a filehandle for the file, attempting to delete it on failure.
+ fileHandle = [NSFileHandle fileHandleForWritingAtPath:[sheet filename]];
+ if ( !fileHandle ) {
+ [[NSFileManager defaultManager] removeFileAtPath:[sheet filename] handler:nil];
+ NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, nil, nil, nil,
+ NSLocalizedString(@"Couldn't write to file. Be sure that you have the necessary privileges.", @"message of panel when file cannot be written"));
+ return;
+ }
}
- if ( [contextInfo isEqualToString:@"exportDump"] ) {
- //export dump of selected database
- fileContent = [self dumpForSelectedTables];
-
- } else if ( [contextInfo isEqualToString:@"exportCustomResultAsCSV"] ) {
- //export custom query result as csv
- fileContent = [self csvForArray:[customQueryInstance currentResult] useFirstLine:[exportFieldNamesSwitch state]
- terminatedBy:[exportFieldsTerminatedField stringValue]
- enclosedBy:[exportFieldsEnclosedField stringValue]
- escapedBy:[exportFieldsEscapedField stringValue]
- lineEnds:[exportLinesTerminatedField stringValue]
- silently:NO];
-
- } else if ( [contextInfo isEqualToString:@"exportTableContentAsCSV"] ) {
- //export table content as csv
- fileContent = [self csvForArray:[tableContentInstance currentResult] useFirstLine:[exportFieldNamesSwitch state]
- terminatedBy:[exportFieldsTerminatedField stringValue]
- enclosedBy:[exportFieldsEnclosedField stringValue]
- escapedBy:[exportFieldsEscapedField stringValue]
- lineEnds:[exportLinesTerminatedField stringValue]
- silently:NO];
-
- } else if ( [contextInfo isEqualToString:@"exportMultipleTablesAsCSV"] ) {
- //export multiple tables as CSV
- fileContent = [self stringForSelectedTablesWithType:@"csv"];
-
- } else if ( [contextInfo isEqualToString:@"exportCustomResultAsXML"] ) {
- //export custom query result as XML
- fileContent = [self xmlForArray:[customQueryInstance currentResult]
- tableName:@"custom"
- withHeader:YES
- silently:NO];
-
- } else if ( [contextInfo isEqualToString:@"exportTableContentAsXML"] ) {
- //export table content as XML
- fileContent = [self xmlForArray:[tableContentInstance currentResult]
- tableName:[tableDocumentInstance table]
- withHeader:YES
- silently:NO];
-
- } else if ( [contextInfo isEqualToString:@"exportMultipleTablesAsXML"] ) {
- //export multiple tables as XML
- fileContent = [self stringForSelectedTablesWithType:@"xml"];
-
- } else {
- //unknown operation
- NSLog(@"unknown operation %@", [contextInfo description]);
- fileContent = @"";
- }
+ // Export the tables selected in the MySQL export sheet to a file
+ if ( [contextInfo isEqualToString:@"exportDump"] ) {
+ success = [self dumpSelectedTablesAsSqlToFileHandle:fileHandle];
- if ( [fileContent respondsToSelector:@selector(writeToFile:atomically:encoding:error:)] ) {
- // mac os 10.4 or later
- success = [fileContent writeToFile:[sheet filename] atomically:YES encoding:[CMMCPConnection encodingForMySQLEncoding:[[tableDocumentInstance encoding] cString]] error:errorStr];
+ // Export the full resultset for the currently selected table to a file in CSV format
+ } else if ( [contextInfo isEqualToString:@"exportTableContentAsCSV"] ) {
+ success = [self exportTables:[NSArray arrayWithObject:[tableDocumentInstance table]] toFileHandle:fileHandle usingFormat:@"csv"];
+
+ // Export the full resultset for the currently selected table to a file in XML format
+ } else if ( [contextInfo isEqualToString:@"exportTableContentAsXML"] ) {
+ success = [self exportTables:[NSArray arrayWithObject:[tableDocumentInstance table]] toFileHandle:fileHandle usingFormat:@"xml"];
+
+ // Export the current "browse" view to a file in CSV format
+ } else if ( [contextInfo isEqualToString:@"exportBrowseViewAsCSV"] ) {
+ success = [self writeCsvForArray:[tableContentInstance currentResult] orQueryResult:nil
+ toFileHandle:fileHandle
+ outputFieldNames:[exportFieldNamesSwitch state]
+ terminatedBy:[exportFieldsTerminatedField stringValue]
+ enclosedBy:[exportFieldsEnclosedField stringValue]
+ escapedBy:[exportFieldsEscapedField stringValue]
+ lineEnds:[exportLinesTerminatedField stringValue]
+ silently:NO];
+
+ // Export the current "browse" view to a file in XML format
+ } else if ( [contextInfo isEqualToString:@"exportBrowseViewAsXML"] ) {
+ success = [self writeXmlForArray:[tableContentInstance currentResult] orQueryResult:nil
+ toFileHandle:fileHandle
+ tableName:[tableDocumentInstance table]
+ withHeader:YES
+ silently:NO];
+
+ // Export the current custom query result set to a file in CSV format
+ } else if ( [contextInfo isEqualToString:@"exportCustomResultAsCSV"] ) {
+ success = [self writeCsvForArray:[customQueryInstance currentResult] orQueryResult:nil
+ toFileHandle:fileHandle
+ outputFieldNames:[exportFieldNamesSwitch state]
+ terminatedBy:[exportFieldsTerminatedField stringValue]
+ enclosedBy:[exportFieldsEnclosedField stringValue]
+ escapedBy:[exportFieldsEscapedField stringValue]
+ lineEnds:[exportLinesTerminatedField stringValue]
+ silently:NO];
+
+ // Export the current custom query result set to a file in XML format
+ } else if ( [contextInfo isEqualToString:@"exportCustomResultAsXML"] ) {
+ success = [self writeXmlForArray:[customQueryInstance currentResult] orQueryResult:nil
+ toFileHandle:fileHandle
+ tableName:@"custom"
+ withHeader:YES
+ silently:NO];
+
+ // Export multiple tables to a file in CSV format
+ } else if ( [contextInfo isEqualToString:@"exportMultipleTablesAsCSV"] ) {
+ success = [self exportSelectedTablesToFileHandle:fileHandle usingFormat:@"csv"];
+
+ // Export multiple tables to a file in XML format
+ } else if ( [contextInfo isEqualToString:@"exportMultipleTablesAsXML"] ) {
+ success = [self exportSelectedTablesToFileHandle:fileHandle usingFormat:@"xml"];
+
+ // Unknown operation
} else {
- // mac os pre 10.4
- success = [fileContent writeToFile:[sheet filename] atomically:YES];
+ NSLog(@"Unknown export operation: %@", [contextInfo description]);
+ return;
}
- if ( !success ) {
- NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, nil, nil, nil,
- NSLocalizedString(@"Couldn't write to file. Be sure that you have the necessary privileges.", @"message of panel when file cannot be written"));
- }
+ // Close the file handle
+ [fileHandle closeFile];
+
+ if ( !success ) {
+ NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, nil, nil, nil,
+ NSLocalizedString(@"Couldn't write to file. Be sure that you have the necessary privileges.", @"message of panel when file cannot be written"));
+ }
// Export finished Growl notification
[[SPGrowlController sharedGrowlController] notifyWithTitle:@"Export Finished"
@@ -270,10 +309,10 @@
invoked when user clicks on an import menuItem
*/
{
- NSOpenPanel *openPanel = [NSOpenPanel openPanel];
- [openPanel setAccessoryView:importCSVView];
+ NSOpenPanel *openPanel = [NSOpenPanel openPanel];
+ [openPanel setAccessoryView:importCSVView];
- // Show openPanel
+ // Show openPanel
[openPanel beginSheetForDirectory:[prefs objectForKey:@"openPath"]
file:nil
modalForWindow:tableWindow
@@ -319,13 +358,13 @@
{
NSString *dumpFile;
NSError **errorStr;
- NSMutableString *errors = [NSMutableString string];
+ NSMutableString *errors = [NSMutableString string];
NSString *fileType = [[importFormatPopup selectedItem] title];
- [sheet orderOut:self];
+ [sheet orderOut:self];
- if ( returnCode != NSOKButton )
- return;
+ if ( returnCode != NSOKButton )
+ return;
//save path to preferences
[prefs setObject:[sheet directory] forKey:@"openPath"];
@@ -359,104 +398,103 @@
[singleProgressBar setDoubleValue:0];
[singleProgressBar displayIfNeeded];
- if ( [fileType isEqualToString:@"SQL"] ) {
+ if ( [fileType isEqualToString:@"SQL"] ) {
//import dump file
NSArray *queries;
int i;
-
+
//open progress sheet
- [NSApp beginSheet:singleProgressSheet
+ [NSApp beginSheet:singleProgressSheet
modalForWindow:tableWindow
modalDelegate:self
didEndSelector:nil
contextInfo:nil];
- [singleProgressBar setIndeterminate:YES];
- [singleProgressBar setUsesThreadedAnimation:YES];
- [singleProgressBar startAnimation:self];
+ [singleProgressBar setIndeterminate:YES];
+ [singleProgressBar setUsesThreadedAnimation:YES];
+ [singleProgressBar startAnimation:self];
//get array with an object for each mysql-query
- queries = [self splitQueries:dumpFile];
-
- [singleProgressBar stopAnimation:self];
- [singleProgressBar setUsesThreadedAnimation:NO];
- [singleProgressBar setIndeterminate:NO];
+ queries = [self splitQueries:dumpFile];
+
+ [singleProgressBar stopAnimation:self];
+ [singleProgressBar setUsesThreadedAnimation:NO];
+ [singleProgressBar setIndeterminate:NO];
//perform all mysql-queries
- for ( i = 0 ; i < [queries count] ; i++ ) {
- [singleProgressBar setDoubleValue:((i+1)*100/[queries count])];
- [singleProgressBar displayIfNeeded];
- [mySQLConnection queryString:[queries objectAtIndex:i]];
-
+ for ( i = 0 ; i < [queries count] ; i++ ) {
+ [singleProgressBar setDoubleValue:((i+1)*100/[queries count])];
+ [singleProgressBar displayIfNeeded];
+ [mySQLConnection queryString:[queries objectAtIndex:i]];
+
if (![[mySQLConnection getLastErrorMessage] isEqualToString:@""] && ![[mySQLConnection getLastErrorMessage] isEqualToString:@"Query was empty"]) {
- [errors appendString:[NSString stringWithFormat:NSLocalizedString(@"[ERROR in query %d] %@\n", @"error text when multiple custom query failed"), (i+1),[mySQLConnection getLastErrorMessage]]];
+ [errors appendString:[NSString stringWithFormat:NSLocalizedString(@"[ERROR in query %d] %@\n", @"error text when multiple custom query failed"), (i+1),[mySQLConnection getLastErrorMessage]]];
}
- }
+ }
//close progress sheet
- [NSApp endSheet:singleProgressSheet];
- [singleProgressSheet orderOut:nil];
+ [NSApp endSheet:singleProgressSheet];
+ [singleProgressSheet orderOut:nil];
//display errors
- if ( [errors length] ) {
- [errorsView setString:errors];
- [NSApp beginSheet:errorsSheet
+ if ( [errors length] ) {
+ [errorsView setString:errors];
+ [NSApp beginSheet:errorsSheet
modalForWindow:tableWindow
modalDelegate:self
didEndSelector:nil
contextInfo:nil];
- [NSApp runModalForWindow:errorsSheet];
-
- [NSApp endSheet:errorsSheet];
- [errorsSheet orderOut:nil];
- }
+ [NSApp runModalForWindow:errorsSheet];
+
+ [NSApp endSheet:errorsSheet];
+ [errorsSheet orderOut:nil];
+ }
////////////////
// IMPORT CSV //
////////////////
- } else if ( [fileType isEqualToString:@"CSV"] ) {
+ } else if ( [fileType isEqualToString:@"CSV"] ) {
//import csv file
- int code;
- NSPopUpButtonCell *buttonCell = [[NSPopUpButtonCell alloc] init];
-
+ int code;
+ NSPopUpButtonCell *buttonCell = [[NSPopUpButtonCell alloc] init];
+
//open progress sheet
- [NSApp beginSheet:singleProgressSheet
+ [NSApp beginSheet:singleProgressSheet
modalForWindow:tableWindow
modalDelegate:self
didEndSelector:nil
contextInfo:nil];
- [singleProgressBar setIndeterminate:YES];
- [singleProgressBar setUsesThreadedAnimation:YES];
- [singleProgressBar startAnimation:self];
+ [singleProgressBar setIndeterminate:YES];
+ [singleProgressBar setUsesThreadedAnimation:YES];
+ [singleProgressBar startAnimation:self];
//put file in array
- if ( importArray )
- [importArray release];
+ if ( importArray )
+ [importArray release];
- importArray = [[self arrayForCSV:dumpFile
+ importArray = [[self arrayForCSV:dumpFile
terminatedBy:[importFieldsTerminatedField stringValue]
enclosedBy:[importFieldsEnclosedField stringValue]
escapedBy:[importFieldsEscapedField stringValue]
lineEnds:[importLinesTerminatedField stringValue]] retain];
//close progress sheet
- [NSApp endSheet:singleProgressSheet];
- [singleProgressSheet orderOut:nil];
- [singleProgressBar stopAnimation:self];
- [singleProgressBar setUsesThreadedAnimation:NO];
- [singleProgressBar setIndeterminate:NO];
+ [NSApp endSheet:singleProgressSheet];
+ [singleProgressSheet orderOut:nil];
+ [singleProgressBar stopAnimation:self];
+ [singleProgressBar setUsesThreadedAnimation:NO];
+ [singleProgressBar setIndeterminate:NO];
CMMCPResult *theResult;
int i;
theResult = (CMMCPResult *) [mySQLConnection listTables];
-
+ if ([theResult numOfRows]) [theResult dataSeek:0];
for ( i = 0 ; i < [theResult numOfRows] ; i++ ) {
- [theResult dataSeek:i];
[fieldMappingPopup addItemWithTitle:[[theResult fetchRowAsArray] objectAtIndex:0]];
}
@@ -468,44 +506,44 @@
[tableListView selectRowIndexes:[NSIndexSet indexSetWithIndex:[[tablesListInstance tables] indexOfObject:[fieldMappingPopup titleOfSelectedItem]]] byExtendingSelection:NO];
- //set up tableView
- currentRow = 0;
- fieldMappingArray = nil;
- [self setupFieldMappingArray];
+ //set up tableView
+ currentRow = 0;
+ fieldMappingArray = nil;
+ [self setupFieldMappingArray];
[rowDownButton setEnabled:NO];
[rowUpButton setEnabled:([importArray count] > 1)];
[recordCountLabel setStringValue:[NSString stringWithFormat:@"%i of %i records", currentRow+1, [importArray count]]];
- //set up tableView buttons
- [buttonCell setControlSize:NSSmallControlSize];
- [buttonCell setFont:[NSFont labelFontOfSize:[NSFont smallSystemFontSize]]];
+ //set up tableView buttons
+ [buttonCell setControlSize:NSSmallControlSize];
+ [buttonCell setFont:[NSFont labelFontOfSize:[NSFont smallSystemFontSize]]];
[buttonCell setBordered:NO];
- [buttonCell addItemWithTitle:NSLocalizedString(@"Do not import", @"text for csv import drop downs")];
- [buttonCell addItemsWithTitles:[importArray objectAtIndex:currentRow]];
-
- [[fieldMappingTableView tableColumnWithIdentifier:@"value"] setDataCell:buttonCell];
+ [buttonCell addItemWithTitle:NSLocalizedString(@"Do not import", @"text for csv import drop downs")];
+ [buttonCell addItemsWithTitles:[tableSourceInstance fieldNames]];
- //show fieldMapping sheet
- [NSApp beginSheet:fieldMappingSheet
+ [[fieldMappingTableView tableColumnWithIdentifier:@"1"] setDataCell:buttonCell];
+
+ // show fieldMapping sheet
+ [NSApp beginSheet:fieldMappingSheet
modalForWindow:tableWindow
modalDelegate:self
didEndSelector:nil
contextInfo:nil];
- code = [NSApp runModalForWindow:fieldMappingSheet];
+ code = [NSApp runModalForWindow:fieldMappingSheet];
- [NSApp endSheet:fieldMappingSheet];
- [fieldMappingSheet orderOut:nil];
-
- if ( code ) {
+ [NSApp endSheet:fieldMappingSheet];
+ [fieldMappingSheet orderOut:nil];
+
+ if ( code ) {
//import array into db
- NSMutableString *fNames = [NSMutableString string];
- // NSMutableArray *fValuesIndexes = [NSMutableArray array];
- NSMutableString *fValues = [NSMutableString string];
- int i,j;
-
+ NSMutableString *fNames = [NSMutableString string];
+ //NSMutableArray *fValuesIndexes = [NSMutableArray array];
+ NSMutableString *fValues = [NSMutableString string];
+ int i,j;
+
//open progress sheet
- [NSApp beginSheet:singleProgressSheet
+ [NSApp beginSheet:singleProgressSheet
modalForWindow:tableWindow
modalDelegate:self
didEndSelector:nil
@@ -522,14 +560,14 @@
}
//import array
- for ( i = 0 ; i < [importArray count] ; i++ ) {
- //show progress bar
- [singleProgressBar setDoubleValue:((i+1)*100/[importArray count])];
- [singleProgressBar displayIfNeeded];
+ for ( i = 0 ; i < [importArray count] ; i++ ) {
+ //show progress bar
+ [singleProgressBar setDoubleValue:((i+1)*100/[importArray count])];
+ [singleProgressBar displayIfNeeded];
if ( !([importFieldNamesSwitch state] && (i == 0)) ) {
//put values in string
- [fValues setString:@""];
+ [fValues setString:@""];
for ( j = 0 ; j < [fieldMappingArray count] ; j++ ) {
@@ -542,47 +580,47 @@
} else {
[fValues appendString:[NSString stringWithFormat:@"'%@'",[mySQLConnection prepareString:[[importArray objectAtIndex:i] objectAtIndex:([[fieldMappingArray objectAtIndex:j] intValue] - 1)]]]];
}
- }
- }
+ }
+ }
//perform query
- [mySQLConnection queryString:[NSString stringWithFormat:@"INSERT INTO `%@` (%@) VALUES (%@)",
+ [mySQLConnection queryString:[NSString stringWithFormat:@"INSERT INTO `%@` (%@) VALUES (%@)",
[fieldMappingPopup titleOfSelectedItem],
fNames,
fValues]];
- if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
+ if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
[errors appendString:[NSString stringWithFormat:NSLocalizedString(@"[ERROR in line %d] %@\n", @"error text when reading of csv file gave errors"), (i+1),[mySQLConnection getLastErrorMessage]]];
}
- }
- }
+ }
+ }
//close progress sheet
- [NSApp endSheet:singleProgressSheet];
- [singleProgressSheet orderOut:nil];
+ [NSApp endSheet:singleProgressSheet];
+ [singleProgressSheet orderOut:nil];
}
[tableContentInstance reloadTableValues:self];
//display errors
- if ( [errors length] ) {
- [errorsView setString:errors];
- [NSApp beginSheet:errorsSheet
+ if ( [errors length] ) {
+ [errorsView setString:errors];
+ [NSApp beginSheet:errorsSheet
modalForWindow:tableWindow
modalDelegate:self
didEndSelector:nil
contextInfo:nil];
- [NSApp runModalForWindow:errorsSheet];
-
- [NSApp endSheet:errorsSheet];
- [errorsSheet orderOut:nil];
- }
+ [NSApp runModalForWindow:errorsSheet];
+
+ [NSApp endSheet:errorsSheet];
+ [errorsSheet orderOut:nil];
+ }
//free arrays
- fieldMappingArray = nil;
- importArray = nil;
- }
+ fieldMappingArray = nil;
+ importArray = nil;
+ }
// Import finished Growl notification
[[SPGrowlController sharedGrowlController] notifyWithTitle:@"Import Finished"
@@ -653,133 +691,219 @@
#pragma mark -
#pragma mark format methods
-- (NSString *)dumpForSelectedTables
+
/*
- returns a dump string for the selected tables
+ Dump the selected tables to a file handle in SQL format.
*/
+- (BOOL)dumpSelectedTablesAsSqlToFileHandle:(NSFileHandle *)fileHandle
{
- int i,j,k,t,rowCount,tableCount;
+ int i,j,t,rowCount, progressBarWidth, lastProgressValue, queryLength;
CMMCPResult *queryResult;
NSString *tableName;
NSArray *fieldNames;
NSArray *theRow;
- NSMutableString *rowValue = [NSMutableString string];
- NSMutableArray *fieldValues;
- NSMutableString *dump = [NSMutableString string];
+ NSMutableArray *selectedTables = [NSMutableArray array];
+ NSMutableString *headerString = [NSMutableString string];
+ NSMutableString *cellValue = [NSMutableString string];
+ NSMutableString *sqlString = [NSMutableString string];
NSMutableString *errors = [NSMutableString string];
+ NSStringEncoding connectionEncoding = [mySQLConnection encoding];
+ NSScanner *sqlNumericTester;
id createTableSyntax;
- //reset interface
+ // Reset the interface
[errorsView setString:@""];
[errorsView displayIfNeeded];
[singleProgressText setStringValue:NSLocalizedString(@"Dumping...", @"text showing that app is writing dump")];
[singleProgressText displayIfNeeded];
+ progressBarWidth = (int)[singleProgressBar bounds].size.width;
[singleProgressBar setDoubleValue:0];
[singleProgressBar displayIfNeeded];
- //open progress sheet
+ // Open the progress sheet
[NSApp beginSheet:singleProgressSheet
- modalForWindow:tableWindow modalDelegate:self
- didEndSelector:nil contextInfo:nil];
-
- //count tables
- tableCount = 0;
+ modalForWindow:tableWindow modalDelegate:self
+ didEndSelector:nil contextInfo:nil];
+
+ // Copy over the selected table names into a table in preparation for iteration
for ( i = 0 ; i < [tables count] ; i++ ) {
if ( [[[tables objectAtIndex:i] objectAtIndex:0] boolValue] ) {
- tableCount++;
+ [selectedTables addObject:[NSString stringWithString:[[tables objectAtIndex:i] objectAtIndex:1]]];
}
}
- k = 0;
-
- //add header of dump-file
- // [dump appendString:[NSString stringWithFormat:@"# Tables dumped %@\n# Created by Sequel Pro (Copyright (c) 2002-2003 Lorenz Textor)\n#\n# Host: %@ Database: %@\n# ******************************\n\n", [NSDate date], [tableDocumentInstance host], [tableDocumentInstance database]]];
- [dump appendString:@"# Sequel Pro dump\n"];
- [dump appendString:[NSString stringWithFormat:@"# Version %@\n",
+
+ // Add the dump header to the dump file.
+ [headerString setString:@"# Sequel Pro dump\n"];
+ [headerString appendString:[NSString stringWithFormat:@"# Version %@\n",
[[NSBundle mainBundle] objectForInfoDictionaryKey:@"CFBundleVersion"]]];
- [dump appendString:@"# http://code.google.com/p/sequel-pro\n#\n"];
- [dump appendString:[NSString stringWithFormat:@"# Host: %@ (MySQL %@)\n",
+ [headerString appendString:@"# http://code.google.com/p/sequel-pro\n#\n"];
+ [headerString appendString:[NSString stringWithFormat:@"# Host: %@ (MySQL %@)\n",
[tableDocumentInstance host], [tableDocumentInstance mySQLVersion]]];
- [dump appendString:[NSString stringWithFormat:@"# Database: %@\n", [tableDocumentInstance database]]];
- [dump appendString:[NSString stringWithFormat:@"# Generation Time: %@\n", [NSDate date]]];
- [dump appendString:@"# ************************************************************\n\n"];
-
- for ( i = 0 ; i < [tables count] ; i++ ) {
- if ( [[[tables objectAtIndex:i] objectAtIndex:0] boolValue] ) {
- k++;
- //set progressbar and text
- tableName = [[tables objectAtIndex:i] objectAtIndex:1];
- [singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Dumping table %@...", @"text showing that app is dumping table"), tableName]];
- [singleProgressText displayIfNeeded];
- //add name of table
- [dump appendString:[NSString stringWithFormat:@"# Dump of table %@\n# ------------------------------------------------------------\n\n", tableName]];
- //add drop table
- if ( [addDropTableSwitch state] == NSOnState )
- [dump appendString:[NSString stringWithFormat:@"DROP TABLE IF EXISTS `%@`;\n\n", tableName]];
- //add create syntax for table
- if ( [addCreateTableSwitch state] == NSOnState ) {
- queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE `%@`", tableName]];
- if ( [queryResult numOfRows] ) {
- createTableSyntax = [[queryResult fetchRowAsDictionary] objectForKey:@"Create Table"];
- if ( [createTableSyntax isKindOfClass:[NSData class]] ) {
- createTableSyntax = [[[NSString alloc] initWithData:createTableSyntax encoding:[mySQLConnection encoding]] autorelease];
- }
- [dump appendString:[NSString stringWithFormat:@"%@;\n\n", createTableSyntax]];
+ [headerString appendString:[NSString stringWithFormat:@"# Database: %@\n", [tableDocumentInstance database]]];
+ [headerString appendString:[NSString stringWithFormat:@"# Generation Time: %@\n", [NSDate date]]];
+ [headerString appendString:@"# ************************************************************\n\n"];
+ [fileHandle writeData:[headerString dataUsingEncoding:connectionEncoding]];
+
+ // Loop through the selected tables
+ for ( i = 0 ; i < [selectedTables count] ; i++ ) {
+ lastProgressValue = 0;
+
+ // Update the progress text and reset the progress bar to indeterminate status while fetching data
+ tableName = [selectedTables objectAtIndex:i];
+ [singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Table %i of %i (%@): Fetching data...", @"text showing that app is fetching data for table dump"), (i+1), [selectedTables count], tableName]];
+ [singleProgressText displayIfNeeded];
+ [singleProgressBar setIndeterminate:YES];
+ [singleProgressBar setUsesThreadedAnimation:YES];
+ [singleProgressBar startAnimation:self];
+
+ // Add the name of table
+ [fileHandle writeData:[[NSString stringWithFormat:@"# Dump of table %@\n# ------------------------------------------------------------\n\n", tableName]
+ dataUsingEncoding:connectionEncoding]];
+
+
+ // Add a "drop table" command if specified in the export dialog
+ if ( [addDropTableSwitch state] == NSOnState )
+ [fileHandle writeData:[[NSString stringWithFormat:@"DROP TABLE IF EXISTS `%@`;\n\n", tableName]
+ dataUsingEncoding:connectionEncoding]];
+
+ // Add the create syntax for the table if specified in the export dialog
+ if ( [addCreateTableSwitch state] == NSOnState ) {
+ queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE `%@`", tableName]];
+ if ( [queryResult numOfRows] ) {
+ createTableSyntax = [[queryResult fetchRowAsDictionary] objectForKey:@"Create Table"];
+ if ( [createTableSyntax isKindOfClass:[NSData class]] ) {
+ createTableSyntax = [[[NSString alloc] initWithData:createTableSyntax encoding:connectionEncoding] autorelease];
}
- if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
- [errors appendString:[NSString stringWithFormat:@"%@\n", [mySQLConnection getLastErrorMessage]]];
- if ( [addErrorsSwitch state] == NSOnState ) {
- [dump appendString:[NSString stringWithFormat:@"# Error: %@\n", [mySQLConnection getLastErrorMessage]]];
- }
+ [fileHandle writeData:[createTableSyntax dataUsingEncoding:connectionEncoding]];
+ [fileHandle writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:connectionEncoding]];
+ }
+ if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
+ [errors appendString:[NSString stringWithFormat:@"%@\n", [mySQLConnection getLastErrorMessage]]];
+ if ( [addErrorsSwitch state] == NSOnState ) {
+ [fileHandle writeData:[[NSString stringWithFormat:@"# Error: %@\n", [mySQLConnection getLastErrorMessage]] dataUsingEncoding:connectionEncoding]];
}
}
- //add table content
- if ( [addTableContentSwitch state] == NSOnState ) {
- queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM `%@`", tableName]];
- fieldNames = [queryResult fetchFieldNames];
- rowCount = [queryResult numOfRows];
+ }
+
+ // Add the table content if required
+ if ( [addTableContentSwitch state] == NSOnState ) {
+ queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM `%@`", tableName]];
+ fieldNames = [queryResult fetchFieldNames];
+ rowCount = [queryResult numOfRows];
+
+ // Update the progress text and set the progress bar back to determinate
+ [singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Table %i of %i (%@): Dumping...", @"text showing that app is writing data for table dump"), (i+1), [selectedTables count], tableName]];
+ [singleProgressText displayIfNeeded];
+ [singleProgressBar stopAnimation:self];
+ [singleProgressBar setUsesThreadedAnimation:NO];
+ [singleProgressBar setIndeterminate:NO];
+ [singleProgressBar setDoubleValue:0];
+ [singleProgressBar displayIfNeeded];
+
+ if (rowCount) {
+ [queryResult dataSeek:0];
+ queryLength = 0;
+
+ // Construct the start of the insertion command
+ [fileHandle writeData:[[NSString stringWithFormat:@"INSERT INTO `%@` (`%@`)\nVALUES\n\t(",
+ tableName, [fieldNames componentsJoinedByString:@"`,`"]] dataUsingEncoding:connectionEncoding]];
+
+ // Iterate through the rows to construct a VALUES group for each
for ( j = 0 ; j < rowCount ; j++ ) {
- [queryResult dataSeek:j];
theRow = [queryResult fetchRowAsArray];
- fieldValues = [NSMutableArray array];
+ [sqlString setString:@""];
+
+ // Update the progress bar
+ [singleProgressBar setDoubleValue:((j+1)*100/rowCount)];
+ if ((int)[singleProgressBar doubleValue] > lastProgressValue) {
+ lastProgressValue = (int)[singleProgressBar doubleValue];
+ [singleProgressBar displayIfNeeded];
+ }
+
for ( t = 0 ; t < [theRow count] ; t++ ) {
- if ( [[theRow objectAtIndex:t] isKindOfClass:[NSData class]] ) {
- //escape special characters
- [rowValue setString:[mySQLConnection prepareBinaryData:[theRow objectAtIndex:t]]];
+
+ // Add NULL values directly to the output row
+ if ( [[theRow objectAtIndex:t] isMemberOfClass:[NSNull class]] ) {
+ [sqlString appendString:@"NULL"];
+
+ // Add data types directly as hex data
+ } else if ( [[theRow objectAtIndex:t] isKindOfClass:[NSData class]] ) {
+ [sqlString appendString:@"X'"];
+ [sqlString appendString:[mySQLConnection prepareBinaryData:[theRow objectAtIndex:t]]];
+ [sqlString appendString:@"'"];
+
} else {
- [rowValue setString:[[theRow objectAtIndex:t] description]];
- //escape special characters
- [rowValue setString:[mySQLConnection prepareString:rowValue]];
+ [cellValue setString:[[theRow objectAtIndex:t] description]];
+
+ // Add empty strings as a pair of quotes
+ if ([cellValue length] == 0) {
+ [sqlString appendString:@"''"];
+
+ } else {
+
+ // Test whether this cell contains a number
+ sqlNumericTester = [NSScanner scannerWithString:cellValue];
+
+ // If it does contain a number, add the number directly
+ if ([sqlNumericTester scanFloat:nil] && [sqlNumericTester isAtEnd]) {
+ [sqlString appendString:cellValue];
+
+ // Otherwise add a quoted string with special characters escaped
+ } else {
+ [sqlString appendString:@"'"];
+ [sqlString appendString:[mySQLConnection prepareString:cellValue]];
+ [sqlString appendString:@"'"];
+ }
+ }
}
- if ( [[theRow objectAtIndex:t] isMemberOfClass:[NSNull class]] ) {
- [fieldValues addObject:@"NULL"];
+
+ // Add the field separator if this isn't the last cell in the row
+ if (t != [theRow count] - 1) [sqlString appendString:@","];
+ }
+
+ queryLength += [sqlString length];
+
+ // Close this VALUES group and set up the next one if appropriate
+ if (j != rowCount - 1) {
+
+ // Add a new INSERT starter command every ~250k of data.
+ if (queryLength > 250000) {
+ [sqlString appendString:[NSString stringWithFormat:@");\n\nINSERT INTO `%@` (`%@`)\nVALUES\n\t(",
+ tableName, [fieldNames componentsJoinedByString:@"`,`"]]];
+ queryLength = 0;
} else {
- // [fieldValues addObject:[NSString stringWithFormat:@"\"%@\"", rowValue]];
- [fieldValues addObject:[NSString stringWithFormat:@"'%@'", rowValue]];
+ [sqlString appendString:@"),\n\t("];
}
+ } else {
+ [sqlString appendString:@")"];
}
- [dump appendString:[NSString stringWithFormat:@"INSERT INTO `%@` (`%@`) VALUES (%@);\n",
- tableName, [fieldNames componentsJoinedByString:@"`,`"],
- [fieldValues componentsJoinedByString:@","]]];
+
+ // Write this row to the file
+ [fileHandle writeData:[sqlString dataUsingEncoding:connectionEncoding]];
}
+
+ // Complete the command
+ [fileHandle writeData:[[NSString stringWithString:@";\n\n"] dataUsingEncoding:connectionEncoding]];
+
if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
[errors appendString:[NSString stringWithFormat:@"%@\n", [mySQLConnection getLastErrorMessage]]];
if ( [addErrorsSwitch state] == NSOnState ) {
- [dump appendString:[NSString stringWithFormat:@"# Error: %@\n", [mySQLConnection getLastErrorMessage]]];
+ [fileHandle writeData:[[NSString stringWithFormat:@"# Error: %@\n", [mySQLConnection getLastErrorMessage]]
+ dataUsingEncoding:connectionEncoding]];
}
}
}
- //set progressbar and text
- [singleProgressBar setDoubleValue:(k*100/tableCount)];
- [singleProgressBar displayIfNeeded];
- [dump appendString:@"\n\n"];
}
+
+ // Add an additional separator between tables
+ [fileHandle writeData:[[NSString stringWithString:@"\n\n"] dataUsingEncoding:connectionEncoding]];
}
-
- //close progress sheet
+
+ // Close the progress sheet
[NSApp endSheet:singleProgressSheet];
[singleProgressSheet orderOut:nil];
- //show errors sheet if there have been errors
+ // Show errors sheet if there have been errors
if ( [errors length] ) {
[errorsView setString:errors];
[NSApp beginSheet:errorsSheet
@@ -791,123 +915,199 @@
[errorsSheet orderOut:nil];
}
- return [NSString stringWithString:dump];
+ return TRUE;
}
-- (NSString *)csvForArray:(NSArray *)array useFirstLine:(BOOL)firstLine terminatedBy:(NSString *)terminated
- enclosedBy:(NSString *)enclosed escapedBy:(NSString *)escaped lineEnds:(NSString *)lineEnds silently:(BOOL)silently;
/*
- takes an array and returns it as a csv string
+ Takes an array and writes it in CSV format to the supplied NSFileHandle
*/
+- (BOOL)writeCsvForArray:(NSArray *)array orQueryResult:(CMMCPResult *)queryResult toFileHandle:(NSFileHandle *)fileHandle outputFieldNames:(BOOL)outputFieldNames terminatedBy:(NSString *)fieldSeparatorString
+ enclosedBy:(NSString *)enclosingString escapedBy:(NSString *)escapeString lineEnds:(NSString *)lineEndString silently:(BOOL)silently;
{
- NSMutableString *string = [NSMutableString string];
- NSMutableString *rowValue = [NSMutableString string];
- NSMutableArray *tempRow = [NSMutableArray array];
- NSMutableString *tempTerminated, *tempLineEnds;
- int i,j;
-
- //repare tabs and line ends
- tempTerminated = [NSMutableString stringWithString:terminated];
- [tempTerminated replaceOccurrencesOfString:@"\\t" withString:@"\t"
- options:NSLiteralSearch
- range:NSMakeRange(0, [tempTerminated length])];
- [tempTerminated replaceOccurrencesOfString:@"\\n" withString:@"\n"
- options:NSLiteralSearch
- range:NSMakeRange(0, [tempTerminated length])];
- [tempTerminated replaceOccurrencesOfString:@"\\r" withString:@"\r"
- options:NSLiteralSearch
- range:NSMakeRange(0, [tempTerminated length])];
- terminated = [NSString stringWithString:tempTerminated];
- tempLineEnds = [NSMutableString stringWithString:lineEnds];
- [tempLineEnds replaceOccurrencesOfString:@"\\t" withString:@"\t"
- options:NSLiteralSearch
- range:NSMakeRange(0, [tempLineEnds length])];
- [tempLineEnds replaceOccurrencesOfString:@"\\n" withString:@"\n"
- options:NSLiteralSearch
- range:NSMakeRange(0, [tempLineEnds length])];
- [tempLineEnds replaceOccurrencesOfString:@"\\r" withString:@"\r"
- options:NSLiteralSearch
- range:NSMakeRange(0, [tempLineEnds length])];
- lineEnds = [NSString stringWithString:tempLineEnds];
-
+ NSStringEncoding tableEncoding = [CMMCPConnection encodingForMySQLEncoding:[[tableDocumentInstance encoding] cString]];
+ NSMutableString *csvCell = [NSMutableString string];
+ NSMutableArray *csvRow = [NSMutableArray array];
+ NSMutableString *csvString = [NSMutableString string];
+ NSString *nullString = [NSString stringWithString:[prefs objectForKey:@"nullValue"]];
+ NSString *escapedEscapeString, *escapedFieldSeparatorString, *escapedEnclosingString, *escapedLineEndString;
+ NSString *dataConversionString;
+ NSScanner *csvNumericTester;
+ BOOL quoteFieldSeparators = [enclosingString isEqualToString:@""];
+ BOOL csvCellIsNumeric;
+ int i, j, startingRow, totalRows, progressBarWidth, lastProgressValue;
+
+ if (queryResult != nil && [queryResult numOfRows]) [queryResult dataSeek:0];
+
+ // Detect and restore special characters being used as terminating or line end strings
+ NSMutableString *tempSeparatorString = [NSMutableString stringWithString:fieldSeparatorString];
+ [tempSeparatorString replaceOccurrencesOfString:@"\\t" withString:@"\t"
+ options:NSLiteralSearch
+ range:NSMakeRange(0, [tempSeparatorString length])];
+ [tempSeparatorString replaceOccurrencesOfString:@"\\n" withString:@"\n"
+ options:NSLiteralSearch
+ range:NSMakeRange(0, [tempSeparatorString length])];
+ [tempSeparatorString replaceOccurrencesOfString:@"\\r" withString:@"\r"
+ options:NSLiteralSearch
+ range:NSMakeRange(0, [tempSeparatorString length])];
+ fieldSeparatorString = [NSString stringWithString:tempSeparatorString];
+ NSMutableString *tempLineEndString = [NSMutableString stringWithString:lineEndString];
+ [tempLineEndString replaceOccurrencesOfString:@"\\t" withString:@"\t"
+ options:NSLiteralSearch
+ range:NSMakeRange(0, [tempLineEndString length])];
+ [tempLineEndString replaceOccurrencesOfString:@"\\n" withString:@"\n"
+ options:NSLiteralSearch
+ range:NSMakeRange(0, [tempLineEndString length])];
+ [tempLineEndString replaceOccurrencesOfString:@"\\r" withString:@"\r"
+ options:NSLiteralSearch
+ range:NSMakeRange(0, [tempLineEndString length])];
+ lineEndString = [NSString stringWithString:tempLineEndString];
+
+ // Updating the progress bar can take >20% of processing time - store details to only update when required
+ progressBarWidth = (int)[singleProgressBar bounds].size.width;
+ lastProgressValue = 0;
+ [singleProgressBar setDoubleValue:0];
+ [singleProgressBar displayIfNeeded];
+
if ( !silently ) {
- //reset interface
- [singleProgressText setStringValue:NSLocalizedString(@"Writing...", @"text showing that app is writing text file")];
+
+ // Set the progress text
+ [singleProgressText setStringValue:NSLocalizedString(@"Exporting...", @"text showing that app is exporting to text file")];
[singleProgressText displayIfNeeded];
- [singleProgressBar setDoubleValue:0];
- [singleProgressBar displayIfNeeded];
- //open progress sheet
+
+
+ // Open progress sheet
[NSApp beginSheet:singleProgressSheet
modalForWindow:tableWindow modalDelegate:self
didEndSelector:nil contextInfo:nil];
}
- for ( i = 0 ; i < [array count] ; i++ ) {
- if ( !silently ) {
- // [singleProgressText setStringValue:[NSString stringWithFormat:@"Writing row %d of %d", i+1, [array count]]];
- // [singleProgressText displayIfNeeded];
- [singleProgressBar setDoubleValue:((i+1)*100/[array count])];
+ // Set up escaped versions of strings for substitution within the loop
+ escapedEscapeString = [NSString stringWithFormat:@"%@%@", escapeString, escapeString];
+ escapedFieldSeparatorString = [NSString stringWithFormat:@"%@%@", escapeString, fieldSeparatorString];
+ escapedEnclosingString = [NSString stringWithFormat:@"%@%@", escapeString, enclosingString];
+ escapedLineEndString = [NSString stringWithFormat:@"%@%@", escapeString, lineEndString];
+
+ // Determine the total number of rows and starting row depending on supplied data format
+ if (array == nil) {
+ startingRow = outputFieldNames ? -1 : 0;
+ totalRows = [queryResult numOfRows];
+ } else {
+ startingRow = outputFieldNames ? 0 : 1;
+ totalRows = [array count];
+ }
+
+ // Walk through the supplied data constructing the CSV string
+ for ( i = startingRow ; i < totalRows ; i++ ) {
+
+ // Update the progress bar
+ [singleProgressBar setDoubleValue:((i+1)*100/totalRows)];
+ if ((int)[singleProgressBar doubleValue] > lastProgressValue) {
+ lastProgressValue = (int)[singleProgressBar doubleValue];
[singleProgressBar displayIfNeeded];
}
- if ( (i > 0) || ((i == 0) && firstLine) ) {
- [tempRow removeAllObjects];
- for ( j = 0 ; j < [[array objectAtIndex:i] count] ; j++ ) {
- //escape "enclosed by" character
- /*
- [rowValue setString:@""];
- scanner = [NSScanner scannerWithString:[[[array objectAtIndex:i] objectAtIndex:j] description]];
- [scanner setCharactersToBeSkipped:nil];
- while ( ![scanner isAtEnd] ) {
- if ( [scanner scanUpToString:enclosed intoString:&tempString] ) {
- [rowValue appendString:tempString];
- }
- if ( [scanner scanString:enclosed intoString:nil] ) {
- [rowValue appendString:[NSString stringWithFormat:@"%@%@", escaped, enclosed]];
- }
- }
- */
- [rowValue setString:[[[array objectAtIndex:i] objectAtIndex:j] description]];
- if ( [rowValue isEqualToString:[prefs objectForKey:@"nullValue"]] ) {
- [tempRow addObject:@"NULL"];
+
+ // Retrieve the row from the supplied data
+ if (array == nil) {
+
+ // Header row
+ if (i == -1) {
+ [csvRow setArray:[queryResult fetchFieldNames]];
+ } else {
+ [csvRow setArray:[queryResult fetchRowAsArray]];
+ }
+ } else {
+ [csvRow setArray:[array objectAtIndex:i]];
+ }
+
+ [csvString setString:@""];
+ for ( j = 0 ; j < [csvRow count] ; j++ ) {
+
+ // For NULL objects supplied from a queryResult, no data is added to the cell
+ if ([[csvRow objectAtIndex:j] isKindOfClass:[NSNull class]]) {
+ [csvString appendString:fieldSeparatorString];
+ continue;
+ }
+
+ // Retrieve the contents of this cell
+ if ([[csvRow objectAtIndex:j] isKindOfClass:[NSData class]]) {
+ dataConversionString = [[NSString alloc] initWithData:[csvRow objectAtIndex:j] encoding:tableEncoding];
+ [csvCell setString:[NSString stringWithString:dataConversionString]];
+ [dataConversionString release];
+ } else {
+ [csvCell setString:[[csvRow objectAtIndex:j] description]];
+ }
+
+ // For NULL values supplied via an array no cell needs to be written.
+ if ( [csvCell isEqualToString:nullString] ) {
+
+ // Add empty strings as a pair of enclosing characters.
+ } else if ( [csvCell length] == 0 ) {
+ [csvString appendString:enclosingString];
+ [csvString appendString:enclosingString];
+
+ } else {
+
+ // Test whether this cell contains a number
+ if ([[csvRow objectAtIndex:j] isKindOfClass:[NSData class]]) {
+ csvCellIsNumeric = FALSE;
} else {
- [rowValue replaceOccurrencesOfString:escaped
- withString:[NSString stringWithFormat:@"%@%@", escaped, escaped]
- options:NSLiteralSearch
- range:NSMakeRange(0,[rowValue length])];
- if ( ![escaped isEqualToString:enclosed] ) {
- [rowValue replaceOccurrencesOfString:enclosed
- withString:[NSString stringWithFormat:@"%@%@", escaped, enclosed]
- options:NSLiteralSearch
- range:NSMakeRange(0,[rowValue length])];
- }
- [rowValue replaceOccurrencesOfString:lineEnds
- withString:[NSString stringWithFormat:@"%@%@", escaped, lineEnds]
- options:NSLiteralSearch
- range:NSMakeRange(0,[rowValue length])];
- if ( [enclosed isEqualToString:@""] ) {
- [rowValue replaceOccurrencesOfString:terminated
- withString:[NSString stringWithFormat:@"%@%@", escaped, terminated]
- options:NSLiteralSearch
- range:NSMakeRange(0,[rowValue length])];
- }
- [tempRow addObject:[NSString stringWithFormat:@"%@%@%@", enclosed, rowValue, enclosed]];
+ csvNumericTester = [NSScanner scannerWithString:csvCell];
+ csvCellIsNumeric = [csvNumericTester scanFloat:nil] && [csvNumericTester isAtEnd];
+ }
+
+ // Escape any occurrences of the escaping character
+ [csvCell replaceOccurrencesOfString:escapeString
+ withString:escapedEscapeString
+ options:NSLiteralSearch
+ range:NSMakeRange(0,[csvCell length])];
+
+ // Escape any occurrences of the enclosure string
+ if ( ![escapeString isEqualToString:enclosingString] ) {
+ [csvCell replaceOccurrencesOfString:enclosingString
+ withString:escapedEnclosingString
+ options:NSLiteralSearch
+ range:NSMakeRange(0,[csvCell length])];
+ }
+
+ // If the string isn't quoted or otherwise enclosed, escape occurrences of the
+ // field separators and the line ending separator.
+ if ( quoteFieldSeparators || csvCellIsNumeric ) {
+ [csvCell replaceOccurrencesOfString:fieldSeparatorString
+ withString:escapedFieldSeparatorString
+ options:NSLiteralSearch
+ range:NSMakeRange(0,[csvCell length])];
+ [csvCell replaceOccurrencesOfString:lineEndString
+ withString:escapedLineEndString
+ options:NSLiteralSearch
+ range:NSMakeRange(0,[csvCell length])];
+ }
+
+ // Write out the cell data by appending strings - this is significantly faster than stringWithFormat.
+ if (csvCellIsNumeric) {
+ [csvString appendString:csvCell];
+ } else {
+ [csvString appendString:enclosingString];
+ [csvString appendString:csvCell];
+ [csvString appendString:enclosingString];
}
}
- [string appendString:[tempRow componentsJoinedByString:terminated]];
- [string appendString:lineEnds];
+ [csvString appendString:fieldSeparatorString];
}
+
+ // Append the line ending to the string for this row
+ [csvString appendString:lineEndString];
+
+ // Write it to the fileHandle
+ [fileHandle writeData:[csvString dataUsingEncoding:tableEncoding]];
}
- /*
- //remove last line end
- [string deleteCharactersInRange:NSMakeRange(([string length]-[lineEnds length]),([lineEnds length]))];
- */
+
+ // Close the progress sheet if it's present
if ( !silently ) {
- //close progress sheet
[NSApp endSheet:singleProgressSheet];
[singleProgressSheet orderOut:nil];
}
- return [NSString stringWithString:string];
+ return TRUE;
}
- (NSArray *)arrayForCSV:(NSString *)csv terminatedBy:(NSString *)terminated
@@ -1002,7 +1202,7 @@
}
}
for ( i = 0 ; i < [tempRowArray count] ; i++ ) {
- if ( [[tempRowArray objectAtIndex:i] isEqualToString:@"NULL"] || [[tempRowArray objectAtIndex:i] isEqualToString:@"\\N"] || [[tempRowArray objectAtIndex:i] isEqualToString:[prefs objectForKey:@"nullValue"]] ) {
+ if ( [[tempRowArray objectAtIndex:i] isEqualToString:@"NULL"] || [[tempRowArray objectAtIndex:i] isEqualToString:@""] || [[tempRowArray objectAtIndex:i] isEqualToString:@"\\N"] || [[tempRowArray objectAtIndex:i] isEqualToString:[prefs objectForKey:@"nullValue"]] ) {
//put nsnull object to array if field contains un-enclosed NULL string
[tempRowArray replaceObjectAtIndex:i withObject:[NSNull null]];
@@ -1045,202 +1245,272 @@
}
-- (NSString *)xmlForArray:(NSArray *)array tableName:(NSString *)table withHeader:(BOOL)header silently:(BOOL)silently
/*
- takes an array and returns it as a xml string
+ Takes an array and writes it in XML format to the supplied NSFileHandle
*/
+- (BOOL)writeXmlForArray:(NSArray *)array orQueryResult:(CMMCPResult *)queryResult toFileHandle:(NSFileHandle *)fileHandle tableName:(NSString *)table withHeader:(BOOL)header silently:(BOOL)silently
{
- NSMutableString *string = [NSMutableString string];
- int i,j;
+ NSStringEncoding tableEncoding = [CMMCPConnection encodingForMySQLEncoding:[[tableDocumentInstance encoding] cString]];
+ NSMutableArray *xmlTags = [NSMutableArray array];
+ NSMutableArray *xmlRow = [NSMutableArray array];
+ NSMutableString *xmlString = [NSMutableString string];
+ NSMutableString *xmlItem = [NSMutableString string];
+ NSString *dataConversionString;
+ int i,j, startingRow, totalRows, progressBarWidth, lastProgressValue;
+
+ if (queryResult != nil && [queryResult numOfRows]) [queryResult dataSeek:0];
+
+ // Updating the progress bar can take >20% of processing time - store details to only update when required
+ progressBarWidth = (int)[singleProgressBar bounds].size.width;
+ lastProgressValue = 0;
+ [singleProgressBar setDoubleValue:0];
+ [singleProgressBar displayIfNeeded];
+
+ // Set up an array of encoded field names as opening and closing tags
+ if (array == nil) {
+ [xmlRow setArray:[queryResult fetchFieldNames]];
+ } else {
+ [xmlRow setArray:[array objectAtIndex:0]];
+ }
+ for ( j = 0; j < [xmlRow count]; j++ ) {
+ [xmlTags addObject:[NSMutableArray array]];
+ [[xmlTags objectAtIndex:j] addObject:[NSString stringWithFormat:@"\t\t<%@>",
+ [self htmlEscapeString:[[xmlRow objectAtIndex:j] description]]]];
+ [[xmlTags objectAtIndex:j] addObject:[NSString stringWithFormat:@"</%@>\n",
+ [self htmlEscapeString:[[xmlRow objectAtIndex:j] description]]]];
+ }
if ( !silently ) {
- //reset interface
+
+ // Set the progress text
[singleProgressText setStringValue:NSLocalizedString(@"Writing...", @"text showing that app is writing text file")];
[singleProgressText displayIfNeeded];
- [singleProgressBar setDoubleValue:0];
- [singleProgressBar displayIfNeeded];
- //open progress sheet
+
+ // Open progress sheet
[NSApp beginSheet:singleProgressSheet
modalForWindow:tableWindow modalDelegate:self
didEndSelector:nil contextInfo:nil];
}
+ // Output the XML header if required
if ( header ) {
- //add header
- [string appendString:@"<?xml version=\"1.0\"?>\n\n"];
- [string appendString:@"<!--\n-\n"];
- [string appendString:@"- Sequel Pro dump\n"];
- [string appendString:[NSString stringWithFormat:@"- Version %@\n",
+ [xmlString setString:@"<?xml version=\"1.0\"?>\n\n"];
+ [xmlString appendString:@"<!--\n-\n"];
+ [xmlString appendString:@"- Sequel Pro dump\n"];
+ [xmlString appendString:[NSString stringWithFormat:@"- Version %@\n",
[[NSBundle mainBundle] objectForInfoDictionaryKey:@"CFBundleVersion"]]];
- [string appendString:@"- http://code.google.com/p/sequel-pro\n-\n"];
- [string appendString:[NSString stringWithFormat:@"- Host: %@ (MySQL %@)\n",
+ [xmlString appendString:@"- http://code.google.com/p/sequel-pro\n-\n"];
+ [xmlString appendString:[NSString stringWithFormat:@"- Host: %@ (MySQL %@)\n",
[tableDocumentInstance host], [tableDocumentInstance mySQLVersion]]];
- [string appendString:[NSString stringWithFormat:@"- Database: %@\n", [tableDocumentInstance database]]];
- [string appendString:[NSString stringWithFormat:@"- Generation Time: %@\n", [NSDate date]]];
- [string appendString:@"-\n-->\n\n"];
+ [xmlString appendString:[NSString stringWithFormat:@"- Database: %@\n", [tableDocumentInstance database]]];
+ [xmlString appendString:[NSString stringWithFormat:@"- Generation Time: %@\n", [NSDate date]]];
+ [xmlString appendString:@"-\n-->\n\n"];
+ [fileHandle writeData:[xmlString dataUsingEncoding:tableEncoding]];
}
+
+ // Write an opening tag in the form of the table name
+ [fileHandle writeData:[[NSString stringWithFormat:@"\t<%@>\n",
+ [self htmlEscapeString:table]]
+ dataUsingEncoding:tableEncoding]];
+
+ // Determine the total number of rows and starting row depending on supplied data format
+ if (array == nil) {
+ startingRow = 0;
+ totalRows = [queryResult numOfRows];
+ } else {
+ startingRow = 1;
+ totalRows = [array count];
+ }
+
+ // Walk through the array, contructing the XML string.
+ for ( i = 1 ; i < totalRows ; i++ ) {
- //add table name
- [string appendString:[NSString stringWithFormat:@"\t<%@>\n", [self htmlEscapeString:table]]];
-
- for ( i = 1 ; i < [array count] ; i++ ) {
- if ( !silently ) {
- [singleProgressBar setDoubleValue:((i+1)*100/[array count])];
+ // Update the progress bar
+ [singleProgressBar setDoubleValue:((i+1)*100/totalRows)];
+ if ((int)[singleProgressBar doubleValue] > lastProgressValue) {
+ lastProgressValue = (int)[singleProgressBar doubleValue];
[singleProgressBar displayIfNeeded];
}
- //add rows
- [string appendString:@"\t<row>\n"];
- for ( j = 0 ; j < [[array objectAtIndex:i] count] ; j++ ) {
- [string appendString:[NSString stringWithFormat:@"\t\t<%@>%@</%@>\n",
- [self htmlEscapeString:[[[array objectAtIndex:0] objectAtIndex:j] description]],
- [self htmlEscapeString:[[[array objectAtIndex:i] objectAtIndex:j] description]],
- [self htmlEscapeString:[[[array objectAtIndex:0] objectAtIndex:j] description]]]];
+
+ // Retrieve the row from the supplied data
+ if (array == nil) {
+ [xmlRow setArray:[queryResult fetchRowAsArray]];
+ } else {
+ [xmlRow setArray:[array objectAtIndex:i]];
}
- [string appendString:@"\t</row>\n"];
+
+ // Construct the row
+ [xmlString setString:@"\t<row>\n"];
+ for ( j = 0 ; j < [xmlRow count] ; j++ ) {
+
+ // Retrieve the contents of this tag
+ if ([[xmlRow objectAtIndex:j] isKindOfClass:[NSData class]]) {
+ dataConversionString = [[NSString alloc] initWithData:[xmlRow objectAtIndex:j] encoding:tableEncoding];
+ [xmlItem setString:[NSString stringWithString:dataConversionString]];
+ [dataConversionString release];
+ } else {
+ [xmlItem setString:[[xmlRow objectAtIndex:j] description]];
+ }
+
+ // Add the opening and closing tag and the contents to the XML string
+ [xmlString appendString:[[xmlTags objectAtIndex:j] objectAtIndex:0]];
+ [xmlString appendString:[self htmlEscapeString:xmlItem]];
+ [xmlString appendString:[[xmlTags objectAtIndex:j] objectAtIndex:1]];
+ }
+ [xmlString appendString:@"\t</row>\n"];
+
+ // Write the row to the filehandle
+ [fileHandle writeData:[xmlString dataUsingEncoding:tableEncoding]];
}
- //end table name
- [string appendString:[NSString stringWithFormat:@"\t</%@>", [self htmlEscapeString:table]]];
-
+
+ // Write the closing tag for the table
+ [fileHandle writeData:[[NSString stringWithFormat:@"\t</%@>",
+ [self htmlEscapeString:table]]
+ dataUsingEncoding:tableEncoding]];
+
+ // Close the progress sheet if appropriate
if ( !silently ) {
- //close progress sheet
[NSApp endSheet:singleProgressSheet];
[singleProgressSheet orderOut:nil];
}
- return [NSString stringWithString:string];
+ return TRUE;
+}
+
+/*
+ Processes the selected tables within the multiple table export accessory view and passes them
+ to be exported.
+ */
+- (BOOL)exportSelectedTablesToFileHandle:(NSFileHandle *)fileHandle usingFormat:(NSString *)type
+{
+ int i;
+ NSMutableArray *selectedTables = [NSMutableArray array];
+
+ // Extract the table names of the selected tables
+ for ( i = 0 ; i < [tables count] ; i++ ) {
+ if ( [[[tables objectAtIndex:i] objectAtIndex:0] boolValue] ) {
+ [selectedTables addObject:[NSString stringWithString:[[tables objectAtIndex:i] objectAtIndex:1]]];
+ }
+ }
+
+ return [self exportTables:selectedTables toFileHandle:fileHandle usingFormat:type];
}
-- (NSString *)stringForSelectedTablesWithType:(NSString *)type
/*
- returns a csv/xml string for the selected tables
- type has to be "csv" or "xml"
+ Walks through the selected tables and exports them to a file handle. The export type must be
+ "csv" for CSV format, and "xml" for XML format.
*/
+- (BOOL)exportTables:(NSArray *)selectedTables toFileHandle:(NSFileHandle *)fileHandle usingFormat:(NSString *)type
{
- int i,j,k,t,rowCount,tableCount;
+ int i;
CMMCPResult *queryResult;
NSString *tableName;
- NSArray *fieldNames;
- NSArray *theRow;
- NSMutableArray *tableArray = [NSMutableArray array];
- NSMutableString *rowValue = [NSMutableString string];
- NSMutableArray *fieldValues;
- NSMutableString *dump = [NSMutableString string];
+ NSMutableString *infoString = [NSMutableString string];
NSMutableString *errors = [NSMutableString string];
-
- //reset interface
+ NSStringEncoding connectionEncoding = [mySQLConnection encoding];
+
+ // Reset the interface
[errorsView setString:@""];
[errorsView displayIfNeeded];
[singleProgressText setStringValue:NSLocalizedString(@"Writing...", @"text showing that app is writing text file")];
[singleProgressText displayIfNeeded];
[singleProgressBar setDoubleValue:0];
[singleProgressBar displayIfNeeded];
-
- //open progress sheet
+
+ // Open the progress sheet
[NSApp beginSheet:singleProgressSheet
- modalForWindow:tableWindow modalDelegate:self
- didEndSelector:nil contextInfo:nil];
-
- //count tables
- tableCount = 0;
- for ( i = 0 ; i < [tables count] ; i++ ) {
- if ( [[[tables objectAtIndex:i] objectAtIndex:0] boolValue] ) {
- tableCount++;
- }
- }
- k = 0;
-
- //add header of dump-file
+ modalForWindow:tableWindow modalDelegate:self
+ didEndSelector:nil contextInfo:nil];
+
+
+ // Add the dump header to the dump file, dependant on export type.
if ( [type isEqualToString:@"csv"] ) {
- [dump appendString:[NSString stringWithFormat:@"Host: %@ Database: %@ Generation Time: %@\n\n",
+ [infoString setString:[NSString stringWithFormat:@"Host: %@ Database: %@ Generation Time: %@\n\n",
[tableDocumentInstance host], [tableDocumentInstance database], [NSDate date]]];
} else if ( [type isEqualToString:@"xml"] ) {
- [dump appendString:@"<?xml version=\"1.0\"?>\n\n"];
- [dump appendString:@"<!--\n-\n"];
- [dump appendString:@"- Sequel Pro dump\n"];
- [dump appendString:[NSString stringWithFormat:@"- Version %@\n",
- [[NSBundle mainBundle] objectForInfoDictionaryKey:@"CFBundleVersion"]]];
- [dump appendString:@"- http://code.google.com/p/sequel-pro\n-\n"];
- [dump appendString:[NSString stringWithFormat:@"- Host: %@ (MySQL %@)\n",
- [tableDocumentInstance host], [tableDocumentInstance mySQLVersion]]];
- [dump appendString:[NSString stringWithFormat:@"- Database: %@\n", [tableDocumentInstance database]]];
- [dump appendString:[NSString stringWithFormat:@"- Generation Time: %@\n", [NSDate date]]];
- [dump appendString:@"-\n-->\n\n\n"];
- [dump appendString:[NSString stringWithFormat:@"<%@>\n\n\n",
- [self htmlEscapeString:[tableDocumentInstance database]]]];
+ [infoString setString:@"<?xml version=\"1.0\"?>\n\n"];
+ [infoString appendString:@"<!--\n-\n"];
+ [infoString appendString:@"- Sequel Pro dump\n"];
+ [infoString appendString:[NSString stringWithFormat:@"- Version %@\n",
+ [[NSBundle mainBundle] objectForInfoDictionaryKey:@"CFBundleVersion"]]];
+ [infoString appendString:@"- http://code.google.com/p/sequel-pro\n-\n"];
+ [infoString appendString:[NSString stringWithFormat:@"- Host: %@ (MySQL %@)\n",
+ [tableDocumentInstance host], [tableDocumentInstance mySQLVersion]]];
+ [infoString appendString:[NSString stringWithFormat:@"- Database: %@\n", [tableDocumentInstance database]]];
+ [infoString appendString:[NSString stringWithFormat:@"- Generation Time: %@\n", [NSDate date]]];
+ [infoString appendString:@"-\n-->\n\n\n"];
+ [infoString appendString:[NSString stringWithFormat:@"<%@>\n\n\n",
+ [self htmlEscapeString:[tableDocumentInstance database]]]];
}
- for ( i = 0 ; i < [tables count] ; i++ ) {
- if ( [[[tables objectAtIndex:i] objectAtIndex:0] boolValue] ) {
- k++;
-
- //set progressbar and text
- tableName = [[tables objectAtIndex:i] objectAtIndex:1];
- [singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Writing table %@...", @"text showing that app is writing table to text file"), tableName]];
- [singleProgressText displayIfNeeded];
-
- //add name of table
- if ( [type isEqualToString:@"csv"] ) {
- [dump appendString:[NSString stringWithFormat:@"Table %@\n\n", tableName]];
- }
-
- //add table content
- queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM `%@`", tableName]];
- fieldNames = [queryResult fetchFieldNames];
- rowCount = [queryResult numOfRows];
- [tableArray removeAllObjects];
-
- //add field names
- [tableArray addObject:fieldNames];
- for ( j = 0 ; j < rowCount ; j++ ) {
- [queryResult dataSeek:j];
- theRow = [queryResult fetchRowAsArray];
- fieldValues = [NSMutableArray array];
- for ( t = 0 ; t < [theRow count] ; t++ ) {
- if ( [[theRow objectAtIndex:t] isKindOfClass:[NSData class]] ) {
- //convert data to string
- [rowValue setString:[[NSString alloc] initWithData:[theRow objectAtIndex:t]
- encoding:[mySQLConnection encoding]]];
- } else if ( [[theRow objectAtIndex:t] isMemberOfClass:[NSNull class]] ) {
- [rowValue setString:[prefs objectForKey:@"nullValue"]];
- } else {
- [rowValue setString:[[theRow objectAtIndex:t] description]];
- }
- [fieldValues addObject:[NSString stringWithString:rowValue]];
- }
- [tableArray addObject:[NSArray arrayWithArray:fieldValues]];
- }
- if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
- [errors appendString:[NSString stringWithFormat:@"%@\n", [mySQLConnection getLastErrorMessage]]];
- }
- if ( [type isEqualToString:@"csv"] ) {
- [dump appendString:[self csvForArray:tableArray
- useFirstLine:[exportMultipleFieldNamesSwitch state]
- terminatedBy:[exportMultipleFieldsTerminatedField stringValue]
- enclosedBy:[exportMultipleFieldsEnclosedField stringValue]
- escapedBy:[exportMultipleFieldsEscapedField stringValue]
- lineEnds:[exportMultipleLinesTerminatedField stringValue]
- silently:YES]];
- } else if ( [type isEqualToString:@"xml"] ) {
- [dump appendString:[self xmlForArray:tableArray tableName:tableName withHeader:NO silently:YES]];
- }
-
- //set progressbar and text
- [singleProgressBar setDoubleValue:(k*100/tableCount)];
- [singleProgressBar displayIfNeeded];
- [dump appendString:@"\n\n\n"];
+ [fileHandle writeData:[infoString dataUsingEncoding:connectionEncoding]];
+
+ // Loop through the selected tables
+ for ( i = 0 ; i < [selectedTables count] ; i++ ) {
+
+ // Update the progress text and reset the progress bar to indeterminate status
+ tableName = [selectedTables objectAtIndex:i];
+ [singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Table %i of %i (%@): fetching data...", @"text showing that app is fetching data for table dump"), (i+1), [selectedTables count], tableName]];
+ [singleProgressText displayIfNeeded];
+ [singleProgressBar setIndeterminate:YES];
+ [singleProgressBar setUsesThreadedAnimation:YES];
+ [singleProgressBar startAnimation:self];
+
+ // For CSV exports, output the name of the table
+ if ( [type isEqualToString:@"csv"] ) {
+ [fileHandle writeData:[[NSString stringWithFormat:@"Table %@\n\n", tableName] dataUsingEncoding:connectionEncoding]];
}
+
+ // Retrieve all the content within this table
+ queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM `%@`", tableName]];
+
+ // Note any errors during retrieval
+ if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
+ [errors appendString:[NSString stringWithFormat:@"%@\n", [mySQLConnection getLastErrorMessage]]];
+ }
+
+ // Update the progress text and set the progress bar back to determinate
+ [singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Table %i of %i (%@): Writing...", @"text showing that app is writing data for table export"), (i+1), [selectedTables count], tableName]];
+ [singleProgressText displayIfNeeded];
+ [singleProgressBar stopAnimation:self];
+ [singleProgressBar setUsesThreadedAnimation:NO];
+ [singleProgressBar setIndeterminate:NO];
+ [singleProgressBar setDoubleValue:0];
+ [singleProgressBar displayIfNeeded];
+
+ // Use the appropriate export method to write the data to file
+ if ( [type isEqualToString:@"csv"] ) {
+ [self writeCsvForArray:nil orQueryResult:queryResult
+ toFileHandle:fileHandle
+ outputFieldNames:[exportMultipleFieldNamesSwitch state]
+ terminatedBy:[exportMultipleFieldsTerminatedField stringValue]
+ enclosedBy:[exportMultipleFieldsEnclosedField stringValue]
+ escapedBy:[exportMultipleFieldsEscapedField stringValue]
+ lineEnds:[exportMultipleLinesTerminatedField stringValue]
+ silently:YES];
+ } else if ( [type isEqualToString:@"xml"] ) {
+ [self writeXmlForArray:nil orQueryResult:queryResult
+ toFileHandle:fileHandle
+ tableName:tableName
+ withHeader:NO
+ silently:YES];
+ }
+
+ // Add a spacer to the file
+ [fileHandle writeData:[[NSString stringWithString:@"\n\n\n"] dataUsingEncoding:connectionEncoding]];
}
-
- //write xml end
+
+ // For XML output, close the database tag
if ( [type isEqualToString:@"xml"] ) {
- [dump appendString:[NSString stringWithFormat:@"</%@>",
- [self htmlEscapeString:[tableDocumentInstance database]]]];
+ [fileHandle writeData:[[NSString stringWithFormat:@"</%@>",
+ [self htmlEscapeString:[tableDocumentInstance database]]]
+ dataUsingEncoding:connectionEncoding]];
}
-
- //close progress sheet
+
+ // Close the progress sheet
[NSApp endSheet:singleProgressSheet];
[singleProgressSheet orderOut:nil];
- //show errors sheet if there have been errors
+ // Show the errors sheet if there have been errors
if ( [errors length] ) {
[errorsView setString:errors];
[NSApp beginSheet:errorsSheet
@@ -1252,7 +1522,7 @@
[errorsSheet orderOut:nil];
}
- return [NSString stringWithString:dump];
+ return TRUE;
}
- (NSString *)htmlEscapeString:(NSString *)string
@@ -1383,23 +1653,29 @@
//we are in a string
//look for end of string
for ( ; i < [queries length] ; i++ ) {
- if ( (([queries characterAtIndex:i] == '`') && (stringType == '`')) ||
- (([queries characterAtIndex:i] == stringType) && ([queries characterAtIndex:i-1] != '\\')) ) {
- //back-tick or no backslash before string end -> end of string
+
+ // For the backtick character treat the string as ended
+ if ( ([queries characterAtIndex:i] == '`') && (stringType == '`') ) {
+
inString = NO;
break;
+
+ // Otherwise, prepare to treat the string as ended after a stringType....
} else if ( [queries characterAtIndex:i] == stringType ) {
- //check if string end isn't escaped
- escaped = YES;
- j = 2;
+
+ // ...but only if the stringType isn't escaped with an *odd* number of escaping characters.
+ escaped = NO;
+ j = 1;
currentLineLength = i - lineStart;
- while ( ((currentLineLength-j)>0) && ([queries characterAtIndex:i-j] == '\\') ) {
+ while ( ((currentLineLength-j) > 0) && ([queries characterAtIndex:i-j] == '\\') ) {
escaped = !escaped;
j++;
}
+
+ // If an odd number have been found, it really is the end of the string.
if ( !escaped ) {
- //it's really the end of the string
inString = NO;
+ break;
}
}
}