From ebd964e9a6d4b5975a806a4354f4df5ab28146d0 Mon Sep 17 00:00:00 2001 From: stuconnolly Date: Sun, 13 Jun 2010 14:22:16 +0000 Subject: Exporter ehancements: - Make sure the export button is disabled when the global structure, content and drop options are enabled, but the desellect all button is checked. - Replace the export dialog toolbar with a tabview, reducing the dialogs overall size. - Add the option to SQL dumps to specify when a new INSERT statement should be created, either after a certain amount of data or after a specific number of rows (defaults to every 250KiB). Known issue: incorrect VALUES clause generation of last row when creating a new INSERT after a specific number of rows. --- Source/SPConstants.h | 17 +++-- Source/SPExportController.h | 15 ++-- Source/SPExportController.m | 177 ++++++++++++++++++++++--------------------- Source/SPExportInitializer.m | 3 + Source/SPSQLExporter.h | 16 +++- Source/SPSQLExporter.m | 33 +++++--- 6 files changed, 149 insertions(+), 112 deletions(-) (limited to 'Source') diff --git a/Source/SPConstants.h b/Source/SPConstants.h index 5b37e5fb..99d16583 100644 --- a/Source/SPConstants.h +++ b/Source/SPConstants.h @@ -53,13 +53,13 @@ typedef NSUInteger SPConnectionType; // Export type constants enum { - SPSQLExport = 1, - SPCSVExport = 2, - SPXMLExport = 3, + SPSQLExport = 0, + SPCSVExport = 1, + SPXMLExport = 2, + SPDotExport = 3, SPPDFExport = 4, SPHTMLExport = 5, - SPExcelExport = 6, - SPDotExport = 7 + SPExcelExport = 6 }; typedef NSUInteger SPExportType; @@ -71,6 +71,13 @@ enum { }; typedef NSUInteger SPExportSource; +// SQL export INSERT statment divider constants +enum { + SPSQLInsertEveryNDataBytes = 0, + SPSQLInsertEveryNRows = 1 +}; +typedef NSUInteger SPSQLExportInsertDivider; + // Table row count query usage levels typedef enum { SPRowCountFetchNever = 0, diff --git a/Source/SPExportController.h b/Source/SPExportController.h index 476ffda4..df9fac0a 100644 --- a/Source/SPExportController.h +++ b/Source/SPExportController.h @@ -27,6 +27,7 @@ #import "SPConstants.h" +@protocol NSTabViewDelegate; @class MCPConnection, BWAnchoredButtonBar; /** @@ -36,7 +37,7 @@ * * Export controller. */ -@interface SPExportController : NSWindowController +@interface SPExportController : NSWindowController { // Controllers IBOutlet id tableDocumentInstance; @@ -46,11 +47,12 @@ IBOutlet id tableDataInstance; // Export window + IBOutlet NSView *exporterView; IBOutlet NSButton *exportButton; - IBOutlet NSToolbar *exportToolbar; IBOutlet NSTextField *exportPathField; IBOutlet NSTableView *exportTableList; - IBOutlet NSTabView *exportTabBar; + IBOutlet NSTabView *exportTypeTabBar; + IBOutlet NSTabView *exportOptionsTabBar; IBOutlet NSPopUpButton *exportInputPopUpButton; IBOutlet NSButton *exportFilePerTableCheck; IBOutlet NSButton *exportSelectAllTablesButton; @@ -92,6 +94,8 @@ IBOutlet NSButton *exportSQLIncludeContentCheck; IBOutlet NSButton *exportSQLIncludeErrorsCheck; IBOutlet NSButton *exportSQLBLOBFieldsAsHexCheck; + IBOutlet NSTextField *exportSQLInsertNValueTextField; + IBOutlet NSPopUpButton *exportSQLInsertDividerPopUpButton; // Excel IBOutlet NSMatrix *exportExcelSheetOrFilePerTableMatrix; @@ -203,11 +207,6 @@ */ NSUserDefaults *prefs; - /** - * Current toolbar item - */ - NSToolbarItem *currentToolbarItem; - /** * Previous connection encoding */ diff --git a/Source/SPExportController.m b/Source/SPExportController.m index 4efb54a0..f279802d 100644 --- a/Source/SPExportController.m +++ b/Source/SPExportController.m @@ -105,14 +105,13 @@ */ - (void)awakeFromNib { - // Set the current toolbar item - currentToolbarItem = [[exportToolbar items] objectAtIndex:0]; - - // Upon awakening select the SQL tab - [exportToolbar setSelectedItemIdentifier:[currentToolbarItem itemIdentifier]]; - // Select the 'selected tables' option [exportInputPopUpButton selectItemAtIndex:SPTableExport]; + + // Select the SQL tab + [[exportTypeTabBar tabViewItemAtIndex:0] setView:exporterView]; + + [exportSQLInsertNValueTextField setIntegerValue:250]; } #pragma mark - @@ -131,6 +130,11 @@ */ - (void)exportTables:(NSArray *)exportTables asFormat:(SPExportType)format { + NSLog(@"%d", format); + + // Select the correct tab + [exportTypeTabBar selectTabViewItemAtIndex:format]; + // Set the default export filename [self _updateDisplayedExportFilename]; @@ -138,9 +142,6 @@ if ([exportFiles count] > 0) [exportFiles removeAllObjects]; - // Select the correct tab according to the supplied export type - [exportToolbar setSelectedItemIdentifier:[[[exportToolbar items] objectAtIndex:(format - 1)] itemIdentifier]]; - // Select the 'selected tables' source option [exportInputPopUpButton selectItemAtIndex:SPTableExport]; @@ -172,7 +173,7 @@ } // Ensure interface validation - [self switchTab:[[exportToolbar items] objectAtIndex:(format - 1)]]; + [self switchTab:self]; NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDesktopDirectory, NSUserDomainMask, YES); @@ -300,55 +301,51 @@ * Change the selected toolbar item. */ - (IBAction)switchTab:(id)sender -{ - if ([sender isKindOfClass:[NSToolbarItem class]]) { - - currentToolbarItem = sender; - - // Determine what data to use (filtered result, custom query result or selected table(s)) for the export operation - exportSource = (exportType == SPDotExport) ? SPTableExport : [exportInputPopUpButton indexOfSelectedItem]; - - // Determine the export type - exportType = [sender tag]; - - NSString *label = [[currentToolbarItem label] uppercaseString]; - - [exportTabBar selectTabViewItemWithIdentifier:[label lowercaseString]]; - - BOOL isSQL = (exportType == SPSQLExport); - BOOL isCSV = (exportType == SPCSVExport); - BOOL isXML = (exportType == SPXMLExport); - BOOL isHTML = (exportType == SPHTMLExport); - BOOL isPDF = (exportType == SPPDFExport); - BOOL isDot = (exportType == SPDotExport); - - BOOL enable = (isCSV || isXML || isHTML || isPDF || isDot); - - [exportFilePerTableCheck setHidden:(isSQL || isDot)]; - [exportTableList setEnabled:(!isDot)]; - [exportSelectAllTablesButton setEnabled:(!isDot)]; - [exportDeselectAllTablesButton setEnabled:(!isDot)]; - [exportRefreshTablesButton setEnabled:(!isDot)]; - - [[[exportInputPopUpButton menu] itemAtIndex:SPTableExport] setEnabled:(!isDot)]; +{ + // Selected export format + NSString *type = [[[exportTypeTabBar selectedTabViewItem] identifier] lowercaseString]; + + // Determine the export type + exportType = [exportTypeTabBar indexOfTabViewItemWithIdentifier:type]; - [exportInputPopUpButton setEnabled:(!isDot)]; - - // Enable/disable the 'filtered result' and 'query result' options - // Note that the result count check is always greater than one as the first row is always the field names - [[[exportInputPopUpButton menu] itemAtIndex:SPFilteredExport] setEnabled:((enable) && ([[tableContentInstance currentResult] count] > 1))]; - [[[exportInputPopUpButton menu] itemAtIndex:SPQueryExport] setEnabled:((enable) && ([[customQueryInstance currentResult] count] > 1))]; + // Determine what data to use (filtered result, custom query result or selected table(s)) for the export operation + exportSource = (exportType == SPDotExport) ? SPTableExport : [exportInputPopUpButton indexOfSelectedItem]; + + [exportOptionsTabBar selectTabViewItemWithIdentifier:type]; - [[exportTableList tableColumnWithIdentifier:@"structure"] setHidden:(isSQL) ? (![exportSQLIncludeStructureCheck state]) : YES]; - [[exportTableList tableColumnWithIdentifier:@"drop"] setHidden:(isSQL) ? (![exportSQLIncludeDropSyntaxCheck state]) : YES]; - - [[[exportTableList tableColumnWithIdentifier:@"content"] headerCell] setStringValue:(enable) ? @"" : @"C"]; - - [exportCSVNULLValuesAsTextField setStringValue:[prefs stringForKey:SPNullValue]]; - [exportXMLNULLValuesAsTextField setStringValue:[prefs stringForKey:SPNullValue]]; - - if (!showCustomFilenameView) [self _updateDisplayedExportFilename]; - } + BOOL isSQL = (exportType == SPSQLExport); + BOOL isCSV = (exportType == SPCSVExport); + BOOL isXML = (exportType == SPXMLExport); + BOOL isHTML = (exportType == SPHTMLExport); + BOOL isPDF = (exportType == SPPDFExport); + BOOL isDot = (exportType == SPDotExport); + + BOOL enable = (isCSV || isXML || isHTML || isPDF || isDot); + + [exportFilePerTableCheck setHidden:(isSQL || isDot)]; + [exportTableList setEnabled:(!isDot)]; + [exportSelectAllTablesButton setEnabled:(!isDot)]; + [exportDeselectAllTablesButton setEnabled:(!isDot)]; + [exportRefreshTablesButton setEnabled:(!isDot)]; + + [[[exportInputPopUpButton menu] itemAtIndex:SPTableExport] setEnabled:(!isDot)]; + + [exportInputPopUpButton setEnabled:(!isDot)]; + + // Enable/disable the 'filtered result' and 'query result' options + // Note that the result count check is always greater than one as the first row is always the field names + [[[exportInputPopUpButton menu] itemAtIndex:SPFilteredExport] setEnabled:((enable) && ([[tableContentInstance currentResult] count] > 1))]; + [[[exportInputPopUpButton menu] itemAtIndex:SPQueryExport] setEnabled:((enable) && ([[customQueryInstance currentResult] count] > 1))]; + + [[exportTableList tableColumnWithIdentifier:@"structure"] setHidden:(isSQL) ? (![exportSQLIncludeStructureCheck state]) : YES]; + [[exportTableList tableColumnWithIdentifier:@"drop"] setHidden:(isSQL) ? (![exportSQLIncludeDropSyntaxCheck state]) : YES]; + + [[[exportTableList tableColumnWithIdentifier:@"content"] headerCell] setStringValue:(enable) ? @"" : @"C"]; + + [exportCSVNULLValuesAsTextField setStringValue:[prefs stringForKey:SPNullValue]]; + [exportXMLNULLValuesAsTextField setStringValue:[prefs stringForKey:SPNullValue]]; + + if (!showCustomFilenameView) [self _updateDisplayedExportFilename]; } /** @@ -457,7 +454,7 @@ } // For SQL only, add procedures and functions - if ([[[currentToolbarItem label] lowercaseString] isEqualToString:@"sql"]) { + if ([[[[exportTypeTabBar selectedTabViewItem] identifier] lowercaseString] isEqualToString:@"sql"]) { NSArray *procedures = [tablesListInstance allProcedureNames]; for (id procName in procedures) @@ -499,14 +496,9 @@ [self refreshTableList:self]; // Determine whether the structure and drop items should also be toggled - for (NSToolbarItem *item in [exportToolbar items]) - { - if ([[item itemIdentifier] isEqualToString:[exportToolbar selectedItemIdentifier]] && [item tag] == SPSQLExport) { - if ([exportSQLIncludeStructureCheck state]) toggleStructure = YES; - if ([exportSQLIncludeDropSyntaxCheck state]) toggleDropTable = YES; - - break; - } + if (exportType == SPSQLExport) { + if ([exportSQLIncludeStructureCheck state]) toggleStructure = YES; + if ([exportSQLIncludeDropSyntaxCheck state]) toggleDropTable = YES; } for (NSMutableArray *table in tables) @@ -627,18 +619,13 @@ } #pragma mark - -#pragma mark Toolbar delegate methods +#pragma mark Tabview delegate methods -- (NSArray *)toolbarSelectableItemIdentifiers:(NSToolbar *)toolbar +- (void)tabView:(NSTabView *)tabView didSelectTabViewItem:(NSTabViewItem *)tabViewItem { - NSMutableArray *items = [NSMutableArray array]; + [tabViewItem setView:exporterView]; - for (NSToolbarItem *item in [toolbar items]) - { - [items addObject:[item itemIdentifier]]; - } - - return items; + [self switchTab:self]; } #pragma mark - @@ -773,6 +760,21 @@ if ((!contentEnabled) && (!structureEnabled) && (!dropEnabled)) { enable = NO; } + // If they are all checked, check to see if any of the tables are checked + else if (contentEnabled && structureEnabled && dropEnabled) { + + // Only enable the button if at least one table is selected + for (NSArray *table in tables) + { + if ([NSArrayObjectAtIndex(table, 1) boolValue] || + [NSArrayObjectAtIndex(table, 2) boolValue] || + [NSArrayObjectAtIndex(table, 3) boolValue]) + { + enable = YES; + break; + } + } + } // Disable if structure is unchecked, but content and drop are as dropping a table then trying to insert // into it is obviously an error. else if (contentEnabled && (!structureEnabled) && (dropEnabled)) { @@ -825,7 +827,7 @@ NSUInteger buttonMask = [exportCustomFilenameViewButton autoresizingMask]; NSUInteger textFieldMask = [exportCustomFilenameViewLabelButton autoresizingMask]; NSUInteger customFilenameViewMask = [exportCustomFilenameView autoresizingMask]; - NSUInteger tabBarMask = [exportTabBar autoresizingMask]; + NSUInteger tabBarMask = [exportOptionsTabBar autoresizingMask]; NSRect frame = [[self window] frame]; @@ -833,7 +835,7 @@ [exportFilePerTableCheck setAutoresizingMask:NSViewNotSizable | NSViewMaxYMargin]; [exportTablelistScrollView setAutoresizingMask:NSViewNotSizable | NSViewMaxYMargin]; [exportTableListButtonBar setAutoresizingMask:NSViewNotSizable | NSViewMaxYMargin]; - [exportTabBar setAutoresizingMask:NSViewNotSizable | NSViewMaxYMargin]; + [exportOptionsTabBar setAutoresizingMask:NSViewNotSizable | NSViewMaxYMargin]; [exportCustomFilenameViewButton setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; [exportCustomFilenameViewLabelButton setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; [exportCustomFilenameView setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; @@ -859,7 +861,7 @@ [exportCustomFilenameViewButton setAutoresizingMask:buttonMask]; [exportCustomFilenameViewLabelButton setAutoresizingMask:textFieldMask]; [exportCustomFilenameView setAutoresizingMask:customFilenameViewMask]; - [exportTabBar setAutoresizingMask:tabBarMask]; + [exportOptionsTabBar setAutoresizingMask:tabBarMask]; } /** @@ -868,18 +870,20 @@ */ - (void)_resizeWindowForAdvancedOptionsViewByHeightDelta:(NSInteger)delta { - NSUInteger scrollMask = [exportTablelistScrollView autoresizingMask]; - NSUInteger buttonBarMask = [exportTableListButtonBar autoresizingMask]; - NSUInteger tabBarMask = [exportTabBar autoresizingMask]; - NSUInteger buttonMask = [exportAdvancedOptionsViewButton autoresizingMask]; - NSUInteger textFieldMask = [exportAdvancedOptionsViewLabelButton autoresizingMask]; - NSUInteger advancedViewMask = [exportAdvancedOptionsView autoresizingMask]; + NSUInteger scrollMask = [exportTablelistScrollView autoresizingMask]; + NSUInteger buttonBarMask = [exportTableListButtonBar autoresizingMask]; + NSUInteger tabBarMask = [exportTypeTabBar autoresizingMask]; + NSUInteger optionsTabBarMask = [exportOptionsTabBar autoresizingMask]; + NSUInteger buttonMask = [exportAdvancedOptionsViewButton autoresizingMask]; + NSUInteger textFieldMask = [exportAdvancedOptionsViewLabelButton autoresizingMask]; + NSUInteger advancedViewMask = [exportAdvancedOptionsView autoresizingMask]; NSRect frame = [[self window] frame]; [exportTablelistScrollView setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; [exportTableListButtonBar setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; - [exportTabBar setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; + [exportTypeTabBar setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; + [exportOptionsTabBar setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; [exportAdvancedOptionsViewButton setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; [exportAdvancedOptionsViewLabelButton setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; [exportAdvancedOptionsView setAutoresizingMask:NSViewNotSizable | NSViewMinYMargin]; @@ -900,7 +904,8 @@ [exportTablelistScrollView setAutoresizingMask:scrollMask]; [exportTableListButtonBar setAutoresizingMask:buttonBarMask]; - [exportTabBar setAutoresizingMask:tabBarMask]; + [exportTypeTabBar setAutoresizingMask:tabBarMask]; + [exportOptionsTabBar setAutoresizingMask:optionsTabBarMask]; [exportAdvancedOptionsViewButton setAutoresizingMask:buttonMask]; [exportAdvancedOptionsViewLabelButton setAutoresizingMask:textFieldMask]; [exportAdvancedOptionsView setAutoresizingMask:advancedViewMask]; diff --git a/Source/SPExportInitializer.m b/Source/SPExportInitializer.m index c0d6e043..d912f87a 100644 --- a/Source/SPExportInitializer.m +++ b/Source/SPExportInitializer.m @@ -270,6 +270,9 @@ [sqlExporter setSqlOutputCompressFile:[exportCompressOutputFile state]]; [sqlExporter setSqlOutputIncludeErrors:[exportSQLIncludeErrorsCheck state]]; + [sqlExporter setSqlInsertAfterNValue:[exportSQLInsertNValueTextField integerValue]]; + [sqlExporter setSqlInsertDivider:[exportSQLInsertDividerPopUpButton indexOfSelectedItem]]; + // Set generic properties [sqlExporter setConnection:connection]; [sqlExporter setExportOutputEncoding:[connection encoding]]; diff --git a/Source/SPSQLExporter.h b/Source/SPSQLExporter.h index 12ed686e..1dcacf4b 100644 --- a/Source/SPSQLExporter.h +++ b/Source/SPSQLExporter.h @@ -26,6 +26,7 @@ #import #import "SPExporter.h" +#import "SPConstants.h" #import "SPSQLExporterProtocol.h" @class SPTableData; @@ -90,12 +91,22 @@ * Compress output */ BOOL sqlOutputCompressFile; + + /** + * New INSERT statement divider + */ + SPSQLExportInsertDivider sqlInsertDivider; /** * Number of tables processed by exporter */ NSUInteger sqlCurrentTableExportIndex; - + + /** + * The value after which a new INSERT statement should be created. + */ + NSUInteger sqlInsertAfterNValue; + /** * Table information fetcher and parser */ @@ -119,6 +130,9 @@ @property(readwrite, assign) BOOL sqlOutputCompressFile; @property(readwrite, assign) NSUInteger sqlCurrentTableExportIndex; +@property(readwrite, assign) NSUInteger sqlInsertAfterNValue; + +@property(readwrite, assign) SPSQLExportInsertDivider sqlInsertDivider; /** * Initialise an instance of SPSQLExporter using the supplied delegate. diff --git a/Source/SPSQLExporter.m b/Source/SPSQLExporter.m index 1c1ebadc..dd43a600 100644 --- a/Source/SPSQLExporter.m +++ b/Source/SPSQLExporter.m @@ -54,6 +54,8 @@ @synthesize sqlOutputIncludeErrors; @synthesize sqlOutputCompressFile; @synthesize sqlCurrentTableExportIndex; +@synthesize sqlInsertAfterNValue; +@synthesize sqlInsertDivider; /** * Initialise an instance of SPSQLExporter using the supplied delegate. @@ -65,6 +67,9 @@ [self setDelegate:exportDelegate]; [self setSqlExportCurrentTable:nil]; + + [self setSqlInsertDivider:SPSQLInsertEveryNDataBytes]; + [self setSqlInsertAfterNValue:250000]; } return self; @@ -92,7 +97,7 @@ SPTableType tableType = SPTableTypeTable; id createTableSyntax = nil; - NSUInteger i, j, t, s, rowCount, queryLength, lastProgressValue; + NSUInteger i, j, k, t, s, rowCount, queryLength, lastProgressValue; BOOL sqlOutputIncludeStructure; BOOL sqlOutputIncludeContent; @@ -311,11 +316,10 @@ [[self exportOutputFileHandle] 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 exportOutputFileHandle] 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; + j, k = 0; sqlExportPool = [[NSAutoreleasePool alloc] init]; @@ -335,10 +339,13 @@ } j++; + k++; + [sqlString setString:@""]; // Update the progress NSUInteger progress = (j * ([self exportMaxProgress] / rowCount)); + if (progress > lastProgressValue) { [self setExportProgressValue:progress]; lastProgressValue = progress; @@ -393,7 +400,7 @@ if ([cellValue length] == 0) { [sqlString appendString:@"''"]; } - else { + else { // If this is a numeric column type, add the number directly. if ([NSArrayObjectAtIndex(tableColumnNumericStatus, t) boolValue]) { [sqlString appendString:cellValue]; @@ -415,12 +422,14 @@ // Close this VALUES group and set up the next one if appropriate if (j != rowCount) { - - // Add a new INSERT starter command every ~250k of data - if (queryLength > 250000) { - [sqlString appendString:[NSString stringWithFormat:@");\n\nINSERT INTO %@ (%@)\nVALUES\n\t(", - [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]]]; - queryLength = 0; + + // If required start a new INSERT statment + if ((([self sqlInsertDivider] == SPSQLInsertEveryNDataBytes) && (queryLength >= ([self sqlInsertAfterNValue] * 1024))) || + (([self sqlInsertDivider] == SPSQLInsertEveryNRows) && (k == [self sqlInsertAfterNValue]))) + { + [sqlString appendString:[NSString stringWithFormat:@");\n\nINSERT INTO %@ (%@)\nVALUES\n\t(", [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]]]; + + queryLength, k = 0; // Use the opportunity to drain and reset the autorelease pool [sqlExportPool release]; @@ -433,7 +442,7 @@ else { [sqlString appendString:@")"]; } - + // Write this row to the file [[self exportOutputFileHandle] writeData:[sqlString dataUsingEncoding:NSUTF8StringEncoding]]; } -- cgit v1.2.3