From 5841f3d9952a1c4d7c73d39e369ec7c78effb356 Mon Sep 17 00:00:00 2001 From: Bibiko Date: Sat, 9 Oct 2010 19:40:48 +0000 Subject: =?UTF-8?q?=E2=80=A2=20CSV=20Import=20(incl.=20import=20from=20cli?= =?UTF-8?q?pboard)=20-=20if=20mapped=20target=20table=20field=20is=20of=20?= =?UTF-8?q?type=20GEOMETRY=20import=20csv=20data=20via=20GeomFromText()=20?= =?UTF-8?q?-=20replaced=20succeeding=20appendString=20lines=20by=20appendF?= =?UTF-8?q?ormat=20since=20tests=20have=20shown=20that=20allocating=20memo?= =?UTF-8?q?ry=20once=20is=20faster=20than=20do=20it=20several=20times?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- Source/SPDataImport.h | 4 +++ Source/SPDataImport.m | 74 ++++++++++++++++++++++++++++----------------------- 2 files changed, 45 insertions(+), 33 deletions(-) (limited to 'Source') diff --git a/Source/SPDataImport.h b/Source/SPDataImport.h index 97ba134f..6b4a622b 100644 --- a/Source/SPDataImport.h +++ b/Source/SPDataImport.h @@ -101,6 +101,10 @@ BOOL progressCancelled; BOOL _mainNibLoaded; + NSDictionary *targetTableDetails; + NSMutableArray *geometryFields; + NSMutableIndexSet *geometryFieldsMapIndex; + NSSavePanel *currentExportPanel; } diff --git a/Source/SPDataImport.m b/Source/SPDataImport.m index f0d1a853..00876c8d 100644 --- a/Source/SPDataImport.m +++ b/Source/SPDataImport.m @@ -67,6 +67,8 @@ if ((self = [super init])) { nibObjectsToRelease = [[NSMutableArray alloc] init]; + geometryFields = [[NSMutableArray alloc] init]; + geometryFieldsMapIndex = [[NSMutableIndexSet alloc] init]; fieldMappingArray = nil; fieldMappingGlobalValueArray = nil; fieldMappingTableColumnNames = nil; @@ -82,6 +84,7 @@ insertRemainingRowsAfterUpdate = NO; numberOfImportDataColumns = 0; selectedTableTarget = nil; + targetTableDetails = nil; prefs = nil; lastFilename = nil; @@ -682,6 +685,9 @@ fieldMappingArray = nil; fieldMappingGlobalValueArray = nil; + [geometryFields removeAllObjects]; + [geometryFieldsMapIndex removeAllIndexes]; + // Start the notification timer to allow notifications to be shown even if frontmost for long queries [[SPGrowlController sharedGrowlController] setVisibilityForNotificationName:@"Import Finished"]; @@ -709,7 +715,7 @@ [[singleProgressBar onMainThread] setIndeterminate:YES]; [[singleProgressBar onMainThread] setUsesThreadedAnimation:YES]; [[singleProgressBar onMainThread] startAnimation:self]; - + // Open the progress sheet [[NSApp onMainThread] beginSheet:singleProgressSheet modalForWindow:[tableDocumentInstance parentWindow] modalDelegate:self didEndSelector:nil contextInfo:nil]; [[singleProgressSheet onMainThread] makeKeyWindow]; @@ -887,14 +893,19 @@ // Set up the field names import string for INSERT or REPLACE INTO [insertBaseString appendString:csvImportHeaderString]; if(!importMethodIsUpdate) { - [insertBaseString appendString:[selectedTableTarget backtickQuotedString]]; - [insertBaseString appendString:@" ("]; + NSString *fieldName; + [insertBaseString appendFormat:@"%@ (", [selectedTableTarget backtickQuotedString]]; insertBaseStringHasEntries = NO; for (i = 0; i < [fieldMappingArray count]; i++) { if ([NSArrayObjectAtIndex(fieldMapperOperator, i) integerValue] == 0) { - if (insertBaseStringHasEntries) [insertBaseString appendString:@","]; - else insertBaseStringHasEntries = YES; - [insertBaseString appendString:[NSArrayObjectAtIndex(fieldMappingTableColumnNames, i) backtickQuotedString]]; + if (insertBaseStringHasEntries) + [insertBaseString appendString:@","]; + else + insertBaseStringHasEntries = YES; + // Store column index for each geometry field to be able to apply GeomFromText() while importing + if([geometryFields containsObject:fieldName = NSArrayObjectAtIndex(fieldMappingTableColumnNames, i) ]) + [geometryFieldsMapIndex addIndex:i]; + [insertBaseString appendString:[fieldName backtickQuotedString]]; } } [insertBaseString appendString:@") VALUES\n"]; @@ -949,8 +960,7 @@ } else { if(insertRemainingRowsAfterUpdate) { [insertRemainingBaseString setString:@"INSERT INTO "]; - [insertRemainingBaseString appendString:[selectedTableTarget backtickQuotedString]]; - [insertRemainingBaseString appendString:@" ("]; + [insertRemainingBaseString appendFormat:@"%@ (", [selectedTableTarget backtickQuotedString]]; insertBaseStringHasEntries = NO; for (i = 0; i < [fieldMappingArray count]; i++) { if ([NSArrayObjectAtIndex(fieldMapperOperator, i) integerValue] == 0) { @@ -1145,19 +1155,6 @@ } fieldMappingImportArrayIsPreview = dataIsPreviewData; - // If there's no tables to select, error - // if (![[tablesListInstance allTableNames] count]) { - // [self closeAndStopProgressSheet]; - // SPBeginAlertSheet(NSLocalizedString(@"Error", @"error"), - // NSLocalizedString(@"OK", @"OK button"), - // nil, nil, - // [tableDocumentInstance parentWindow], self, - // nil, nil, - // NSLocalizedString(@"Can't import CSV data into a database without any tables!", @"error text when trying to import csv data, but we have no tables in the db") - // ); - // return FALSE; - // } - // Set the import array if (fieldMappingImportArray) [fieldMappingImportArray release]; fieldMappingImportArray = [[NSArray alloc] initWithArray:importData]; @@ -1213,6 +1210,17 @@ return FALSE; } + // Store target table definitions + SPTableData *selectedTableData = [[SPTableData alloc] init]; + [selectedTableData setConnection:mySQLConnection]; + targetTableDetails = [selectedTableData informationForTable:selectedTableTarget]; + [selectedTableData release]; + + // Store all field names which are of typegrouping 'geometry' + for(NSDictionary *field in [targetTableDetails objectForKey:@"columns"]) + if([[field objectForKey:@"typegrouping"] isEqualToString:@"geometry"]) + [geometryFields addObject:[field objectForKey:@"name"]]; + [importFieldNamesSwitch setState:[fieldMapperController importFieldNamesHeader]]; [prefs setBool:[importFieldNamesSwitch state] forKey:SPCSVImportFirstLineIsHeader]; @@ -1275,9 +1283,7 @@ if (cellData == [NSNull null]) { [setString appendString:@"NULL"]; } else { - [setString appendString:@"'"]; - [setString appendString:[mySQLConnection prepareString:cellData]]; - [setString appendString:@"'"]; + [setString appendFormat:@"'%@'", [mySQLConnection prepareString:cellData]]; } } } @@ -1290,8 +1296,7 @@ // - check for global values if(fieldMappingArrayHasGlobalVariables && mapColumn >= numberOfImportDataColumns) { // Global variables are coming wrapped in ' ' if there're not marked as SQL - [whereString appendString:@"="]; - [whereString appendString:NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + [whereString appendFormat:@"=%@", NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; } else { cellData = NSArrayObjectAtIndex(csvRowArray, mapColumn); @@ -1302,10 +1307,7 @@ if (cellData == [NSNull null]) { [whereString appendString:@" IS NULL"]; } else { - [whereString appendString:@"="]; - [whereString appendString:@"'"]; - [whereString appendString:[mySQLConnection prepareString:cellData]]; - [whereString appendString:@"'"]; + [whereString appendFormat:@"='%@'", [mySQLConnection prepareString:cellData]]; } } } @@ -1350,9 +1352,12 @@ if (cellData == [NSNull null]) { [valueString appendString:@"NULL"]; } else { - [valueString appendString:@"'"]; - [valueString appendString:[mySQLConnection prepareString:cellData]]; - [valueString appendString:@"'"]; + // Apply GeomFromText() for each geometry field + if([geometryFieldsMapIndex containsIndex:i]) { + [valueString appendFormat:@"GeomFromText('%@')", [mySQLConnection prepareString:cellData]]; + } else { + [valueString appendFormat:@"'%@'", [mySQLConnection prepareString:cellData]]; + } } } } @@ -1497,6 +1502,9 @@ - (void)dealloc { if (fieldMappingImportArray) [fieldMappingImportArray release]; + if (geometryFields) [geometryFields release]; + if (geometryFieldsMapIndex) [geometryFieldsMapIndex release]; + if (lastFilename) [lastFilename release]; if (prefs) [prefs release]; if(selectedTableTarget) [selectedTableTarget release]; -- cgit v1.2.3