diff options
author | Bibiko <bibiko@eva.mpg.de> | 2010-09-06 12:24:46 +0000 |
---|---|---|
committer | Bibiko <bibiko@eva.mpg.de> | 2010-09-06 12:24:46 +0000 |
commit | 3f6aa841dd77af2cf8f2818c91cd186aec2ca5e0 (patch) | |
tree | 8bb42187b5ca5d706717a5d59e0e5165920034c1 | |
parent | 1356bb72bcdde43d979b897ccea7b0b9ef8e0f63 (diff) | |
download | sequelpro-3f6aa841dd77af2cf8f2818c91cd186aec2ca5e0.tar.gz sequelpro-3f6aa841dd77af2cf8f2818c91cd186aec2ca5e0.tar.bz2 sequelpro-3f6aa841dd77af2cf8f2818c91cd186aec2ca5e0.zip |
• CSV Import into new table:
- do not create a field in beforehand if user set it to "Do Not Import"
- added ROUGH field type guessing by parsing the up to 100 first rows distinguishing text versa integer and size:
-- for integer distinguishes between INT and BIGINT due to number length
-- for text distinguishes between VARCHAR(255), VARCHAR(32767) [for MySQL >= 5 only], and TEXT due to maximum string length
-rw-r--r-- | Source/SPFieldMapperController.m | 83 |
1 files changed, 79 insertions, 4 deletions
diff --git a/Source/SPFieldMapperController.m b/Source/SPFieldMapperController.m index 7df1ea81..fe60c5d3 100644 --- a/Source/SPFieldMapperController.m +++ b/Source/SPFieldMapperController.m @@ -431,8 +431,11 @@ NSInteger columnIndex = 0; NSInteger numberOfColumns = [fieldMappingTableColumnNames count]; for(columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) { - [createString appendFormat:@"\t%@ %@", [[fieldMappingTableColumnNames objectAtIndex:columnIndex] backtickQuotedString], [fieldMappingTableTypes objectAtIndex:columnIndex]]; - if(columnIndex < numberOfColumns-1) [createString appendString:@", \n"]; + // add to the new table only those fields which are markes as "Do Import" + if([fieldMappingOperatorArray objectAtIndex:columnIndex] == doImport) { + [createString appendFormat:@"\t%@ %@", [[fieldMappingTableColumnNames objectAtIndex:columnIndex] backtickQuotedString], [fieldMappingTableTypes objectAtIndex:columnIndex]]; + if(columnIndex < numberOfColumns-1) [createString appendString:@", \n"]; + } } [createString appendString:@")"]; @@ -780,21 +783,93 @@ [newTableButton setHidden:YES]; [newTableNameTextField selectText:nil]; + // Check length and type of fieldMappingImportArray 65,535 + NSInteger maxLengthOfSourceColumns [numberOfImportColumns]; + NSInteger typeOfSourceColumns [numberOfImportColumns]; // 0=text 1=integer + NSInteger columnCounter; + + for(columnCounter = 0; columnCounter < numberOfImportColumns; columnCounter++) { + maxLengthOfSourceColumns[columnCounter] = 0; + typeOfSourceColumns[columnCounter] = 1; + } + + BOOL skipFirstRow = importFieldNamesHeader; + + for(NSArray* row in fieldMappingImportArray) { + if(skipFirstRow) { + skipFirstRow = NO; + continue; + } + columnCounter = 0; + for(NSString* col in row) { + if(maxLengthOfSourceColumns[columnCounter] < [col length]) { + maxLengthOfSourceColumns[columnCounter] = [col length]; + } + if(typeOfSourceColumns[columnCounter] == 1) { + if(![[[NSNumber numberWithLongLong:[col longLongValue]] stringValue] isEqualToString:col]) + typeOfSourceColumns[columnCounter] = 0; + } + columnCounter++; + } + } + + columnCounter = 0; [fieldMappingTableColumnNames removeAllObjects]; [fieldMappingTableDefaultValues removeAllObjects]; [fieldMappingTableTypes removeAllObjects]; + BOOL serverGreaterThanVersion4 = ([mySQLConnection serverMajorVersion] >= 5) ? YES : NO; if([importFieldNamesHeaderSwitch state] == NSOnState) { for(id h in NSArrayObjectAtIndex(fieldMappingImportArray, 0)) { [fieldMappingTableColumnNames addObject:h]; [fieldMappingTableDefaultValues addObject:@""]; - [fieldMappingTableTypes addObject:@"VARCHAR(255)"]; + if(typeOfSourceColumns[columnCounter] == 1) { // integer type + if(maxLengthOfSourceColumns[columnCounter] < 9) + [fieldMappingTableTypes addObject:@"INT(11)"]; + else + [fieldMappingTableTypes addObject:@"BIGINT(11)"]; + } else { + if(serverGreaterThanVersion4) { + if(maxLengthOfSourceColumns[columnCounter] < 256) + [fieldMappingTableTypes addObject:@"VARCHAR(255)"]; + else if(maxLengthOfSourceColumns[columnCounter] < 32768) + [fieldMappingTableTypes addObject:@"VARCHAR(32767)"]; + else + [fieldMappingTableTypes addObject:@"TEXT"]; + } else { + if(maxLengthOfSourceColumns[columnCounter] < 256) + [fieldMappingTableTypes addObject:@"VARCHAR(255)"]; + else + [fieldMappingTableTypes addObject:@"TEXT"]; + } + } + columnCounter++; } } else { NSInteger i = 0; for(id h in NSArrayObjectAtIndex(fieldMappingImportArray, 0)) { [fieldMappingTableColumnNames addObject:[NSString stringWithFormat:@"col_%ld", i++]]; [fieldMappingTableDefaultValues addObject:@""]; - [fieldMappingTableTypes addObject:@"VARCHAR(255)"]; + if(typeOfSourceColumns[columnCounter] == 1) { // integer type + if(maxLengthOfSourceColumns[columnCounter] < 9) + [fieldMappingTableTypes addObject:@"INT(11)"]; + else + [fieldMappingTableTypes addObject:@"BIGINT(11)"]; + } else { + if(serverGreaterThanVersion4) { + if(maxLengthOfSourceColumns[columnCounter] < 256) + [fieldMappingTableTypes addObject:@"VARCHAR(255)"]; + else if(maxLengthOfSourceColumns[columnCounter] < 32768) + [fieldMappingTableTypes addObject:@"VARCHAR(32767)"]; + else + [fieldMappingTableTypes addObject:@"TEXT"]; + } else { + if(maxLengthOfSourceColumns[columnCounter] < 256) + [fieldMappingTableTypes addObject:@"VARCHAR(255)"]; + else + [fieldMappingTableTypes addObject:@"TEXT"]; + } + } + columnCounter++; } } |