aboutsummaryrefslogtreecommitdiffstats
path: root/Source
diff options
context:
space:
mode:
authorBibiko <bibiko@eva.mpg.de>2009-06-08 22:40:15 +0000
committerBibiko <bibiko@eva.mpg.de>2009-06-08 22:40:15 +0000
commit1df329b21dbc9a94ee36ad4f0ebb5cda88e914dd (patch)
treee7a61f14749401a33bd5e4dcd39e893589f5deeb /Source
parent56d7a059dc5ddd905ab58a58c2878eef7fe9c9ea (diff)
downloadsequelpro-1df329b21dbc9a94ee36ad4f0ebb5cda88e914dd.tar.gz
sequelpro-1df329b21dbc9a94ee36ad4f0ebb5cda88e914dd.tar.bz2
sequelpro-1df329b21dbc9a94ee36ad4f0ebb5cda88e914dd.zip
• added: "Copy as SQL INSERT" ^⌥⌘C
- up to now only available for the table content pane - supports blobs - if a primary key is given it also support (not yet loaded) blobs and long text data fields
Diffstat (limited to 'Source')
-rw-r--r--Source/CMCopyTable.h18
-rw-r--r--Source/CMCopyTable.m249
-rw-r--r--Source/TableContent.m6
3 files changed, 184 insertions, 89 deletions
diff --git a/Source/CMCopyTable.h b/Source/CMCopyTable.h
index 0676267d..5da66ec2 100644
--- a/Source/CMCopyTable.h
+++ b/Source/CMCopyTable.h
@@ -35,8 +35,13 @@
@interface CMCopyTable : NSTableView
{
- NSArray* columnDefinitions;
- NSString* selectedTable;
+ id tableInstance; // the table content view instance
+ id tableData; // the actual table data source
+ id mySQLConnection; // current MySQL connection
+ NSArray* columnDefinitions; // array of NSDictionary containing info about columns
+ NSString* selectedTable; // the name of the current selected table
+
+ NSUserDefaults *prefs;
}
@@ -90,9 +95,16 @@
*/
- (NSString *)draggedRowsAsTabString:(NSArray *)rows;
+/*
+ * Generate a string in form of INSERT INTO <table> VALUES () of
+ * currently selected rows. Support blob data as well.
+ */
- (NSString *)selectedRowsAsSqlInserts;
-- (void)setTableInfoWithColumns:(NSArray *)columnDefs withTable:(NSString *)aTable;
+/*
+ * Set all necessary data from the table content view.
+ */
+- (void)setTableInstance:(id)anInstance withTableData:(id)theTableData withColumns:(NSArray *)columnDefs withTableName:(NSString *)aTableName withConnection:(id)aMySqlConnection;
@end
diff --git a/Source/CMCopyTable.m b/Source/CMCopyTable.m
index 7bb66da7..2f7bb929 100644
--- a/Source/CMCopyTable.m
+++ b/Source/CMCopyTable.m
@@ -24,6 +24,8 @@
#import "CMCopyTable.h"
#import "SPArrayAdditions.h"
+#import "CMMCPConnection.h"
+#import "TableContent.h"
int MENU_EDIT_COPY_WITH_COLUMN = 2001;
int MENU_EDIT_COPY_AS_SQL = 2002;
@@ -32,8 +34,10 @@ int MENU_EDIT_COPY_AS_SQL = 2002;
- (void)copy:(id)sender
{
+ prefs = [[NSUserDefaults standardUserDefaults] retain];
+
NSString *tmp = nil;
-
+
if([sender tag] == MENU_EDIT_COPY_AS_SQL) {
tmp = [self selectedRowsAsSqlInserts];
if ( nil != tmp )
@@ -150,107 +154,180 @@ int MENU_EDIT_COPY_AS_SQL = 2002;
}
/*
- * Return selected rows as SQL INSERT INTO foo VALUES baz
- * string.
+ * Return selected rows as SQL INSERT INTO `foo` VALUES (baz) string.
+ * If no selected table name is given `<table>` will be used instead.
*/
- (NSString *)selectedRowsAsSqlInserts
{
- if ( [self numberOfSelectedRows] > 0 )
- {
- NSArray *columns = [self tableColumns];
- int numColumns = [columns count];
- id dataSource = [self dataSource];
- id enumObj;
- NSMutableString *result = [NSMutableString stringWithCapacity:numColumns];
+ if ( [self numberOfSelectedRows] < 1 ) return nil;
- // Create an array of table column names
- NSMutableArray *tbHeader = [NSMutableArray arrayWithCapacity:numColumns];
- enumerate(columns, enumObj) [tbHeader addObject:[[enumObj headerCell] stringValue]];
+ NSArray *columns = [self tableColumns];
+ int numColumns = [columns count];
- // Create an hash of header name and typegrouping
- NSMutableDictionary *headerType = [NSMutableDictionary dictionaryWithCapacity:numColumns];
- enumerate(columnDefinitions, enumObj)
- [headerType setObject:[enumObj objectForKey:@"typegrouping"] forKey:[enumObj objectForKey:@"name"]];
+ NSTableColumn *col = nil;
+ // NSIndexSet *rowIndexes = [self selectedRowIndexes];
+ NSString *spNULL = [prefs objectForKey:@"NullValue"];
+ NSMutableString *value = [NSMutableString stringWithCapacity:10];
+ NSDictionary *dbDataRow;
+ id enumObj;
+ id rowData = nil;
+ id rowEnumObject = nil;
+
+ long row;
+ long rowCounter = 0;
+ long penultimateRowIndex = [[self selectedRowIndexes] count];
+ int c;
+ int valueLength = 0;
- // Create array of types according to the column order
- NSMutableArray *types = [NSMutableArray arrayWithCapacity:numColumns];
- enumerate(tbHeader, enumObj)
- {
- NSString *t = [headerType objectForKey:enumObj];
+ NSMutableString *result = [NSMutableString stringWithCapacity:numColumns];
- if([t isEqualToString:@"bit"] || [t isEqualToString:@"integer"] || [t isEqualToString:@"float"])
- [types addObject:[NSNumber numberWithInt:0]];
- else if([t isEqualToString:@"blobdata"])
- [types addObject:[NSNumber numberWithInt:2]];
- else
- [types addObject:[NSNumber numberWithInt:1]];
+ // Create an array of table column names
+ NSMutableArray *tbHeader = [NSMutableArray arrayWithCapacity:numColumns];
+ enumerate(columns, enumObj)
+ [tbHeader addObject:[[enumObj headerCell] stringValue]];
- }
-
- [result appendString:[NSString stringWithFormat:@"INSERT INTO `%@` (%@)\nVALUES\n",
- (selectedTable == nil)?@"<table>":selectedTable, [tbHeader componentsJoinedAndBacktickQuoted]]];
+ // Create an hash of header name and typegrouping
+ NSMutableDictionary *headerType = [NSMutableDictionary dictionaryWithCapacity:numColumns];
+ enumerate(columnDefinitions, enumObj)
+ [headerType setObject:[enumObj objectForKey:@"typegrouping"] forKey:[enumObj objectForKey:@"name"]];
- int c;
- id rowData = nil;
- NSTableColumn *col = nil;
- NSIndexSet *rowIndexes = [self selectedRowIndexes];
- unsigned row = [rowIndexes firstIndex];
+ // Create array of types according to the column order
+ NSMutableArray *types = [NSMutableArray arrayWithCapacity:numColumns];
+ enumerate(tbHeader, enumObj)
+ {
+ NSString *t = [headerType objectForKey:enumObj];
+ if([t isEqualToString:@"bit"] || [t isEqualToString:@"integer"] || [t isEqualToString:@"float"])
+ [types addObject:[NSNumber numberWithInt:0]]; // numeric
+ else if([t isEqualToString:@"blobdata"])
+ [types addObject:[NSNumber numberWithInt:2]]; // blob data
+ else if([t isEqualToString:@"textdata"])
+ [types addObject:[NSNumber numberWithInt:3]]; // long text data
+ else
+ [types addObject:[NSNumber numberWithInt:1]]; // string (fallback coevally)
+ }
- while ( row != NSNotFound )
- {
- [result appendString:@"\t("];
- rowData = nil;
- for ( c = 0; c < numColumns; c++)
- {
- col = [columns objectAtIndex:c];
- rowData = [dataSource tableView:self
- objectValueForTableColumn:col
- row:row ];
+ [result appendString:[NSString stringWithFormat:@"INSERT INTO `%@` (%@)\nVALUES\n",
+ (selectedTable == nil)?@"<table>":selectedTable, [tbHeader componentsJoinedAndBacktickQuoted]]];
- if ( nil != rowData ) {
+ //this is really deprecated in 10.3, but the new method is really weird
+ NSEnumerator *enumerator = [self selectedRowEnumerator];
- switch([[types objectAtIndex:c] intValue]) {
- case 0: // numeric
- [result appendString:[NSString stringWithFormat:@"%@,", [rowData description]]];
+ while ( rowEnumObject = [enumerator nextObject] )
+ {
+ [value appendString:@"\t("];
+ rowData = nil;
+ row = [rowEnumObject intValue];
+ rowCounter++;
+ for ( c = 0; c < numColumns; c++ )
+ {
+ col = [columns objectAtIndex:c];
+ rowData = [[tableData objectAtIndex:row] objectForKey:[tbHeader objectAtIndex:c]];
+
+ // Check for NULL value - TODO this is not safe!!
+ if([[rowData description] isEqualToString:spNULL]){
+ [value appendString:@"NULL, "];
+ continue;
+ }
+ else if ( rowData != nil ) {
+ // check column type and insert the data accordingly
+ switch([[types objectAtIndex:c] intValue]) {
+ case 0: // numeric
+ [value appendString:[NSString stringWithFormat:@"%@, ", [rowData description]]];
break;
- case 1: // string
- [result appendString:[NSString stringWithFormat:@"'%@',", [[rowData description] stringByReplacingOccurrencesOfString:@"'" withString:@"\'"] ] ];
+ case 1: // string
+ [value appendString:[NSString stringWithFormat:@"'%@', ",
+ [mySQLConnection prepareString:[rowData description]]]];
break;
- case 2: //blob
- [result appendString:[NSString stringWithFormat:@"X'%@',", @"<BLOB>"]];
- }
+ case 2: // blob
+ if ([prefs boolForKey:@"LoadBlobsAsNeeded"]) {
+
+ // Abort if there are no indices on this table or if there's no table name given.
+ if (![[tableInstance argumentForRow:row] length] || selectedTable == nil)
+ return nil;
+
+ //if we have indexes, use argumentForRow
+ dbDataRow = [[mySQLConnection queryString:
+ [NSString stringWithFormat:@"SELECT * FROM `%@` WHERE %@",
+ selectedTable, [tableInstance argumentForRow:row]]] fetchRowAsDictionary];
+ if([[dbDataRow objectForKey:[tbHeader objectAtIndex:c]] isKindOfClass:[NSNull class]])
+ [value appendString:@"NULL, "];
+ else
+ [value appendString:[NSString stringWithFormat:@"X'%@', ",
+ [mySQLConnection prepareBinaryData:[dbDataRow objectForKey:[tbHeader objectAtIndex:c]]]]];
+ } else {
+ [value appendString:[NSString stringWithFormat:@"X'%@', ", [mySQLConnection prepareBinaryData:rowData]]];
+ }
+ break;
+ case 3: // long text data
+ if ([prefs boolForKey:@"LoadBlobsAsNeeded"]) {
+
+ // Abort if there are no indices on this table or if there's no table name given.
+ if (![[tableInstance argumentForRow:row] length] || selectedTable == nil)
+ return nil;
+
+ //if we have indexes, use argumentForRow
+ dbDataRow = [[mySQLConnection queryString:
+ [NSString stringWithFormat:@"SELECT * FROM `%@` WHERE %@",
+ selectedTable, [tableInstance argumentForRow:row]]] fetchRowAsDictionary];
+ if([[dbDataRow objectForKey:[tbHeader objectAtIndex:c]] isKindOfClass:[NSNull class]])
+ [value appendString:@"NULL, "];
+ else
+ [value appendString:[NSString stringWithFormat:@"'%@', ",
+ [mySQLConnection prepareString:[[dbDataRow objectForKey:[tbHeader objectAtIndex:c]] description]]]];
+ } else {
+ [value appendString:[NSString stringWithFormat:@"'%@', ",
+ [[rowData description] stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"] ] ];
+ }
+ break;
+ default:
+ return nil;
}
- else
- [result appendString:@"'',"];
+ }
+ else
+ // TODO is this necessary? or better to return nil?
+ [value appendString:@"'', "];
- } //end for each column
-
- if ( [result length] )
- {
- [result deleteCharactersInRange:NSMakeRange([result length]-1, 1)];
+ } //end for each column
+
+ // delete last ', '
+ if ( [value length] > 2 )
+ [value deleteCharactersInRange:NSMakeRange([value length]-2, 2)];
+
+ valueLength += [value length];
+
+ // Close this VALUES group and set up the next one if appropriate
+ if ( rowCounter != penultimateRowIndex ) {
+ // Add a new INSERT starter command every ~250k of data.
+ if ( valueLength > 250000 ) {
+ [result appendString:value];
+ [result appendString:[NSString stringWithFormat:@");\n\nINSERT INTO `%@` (%@)\nVALUES\n",
+ (selectedTable == nil)?@"<table>":selectedTable, [tbHeader componentsJoinedAndBacktickQuoted]]];
+ [value setString:@""];
+ valueLength = 0;
+ } else {
+ [value appendString:@"),\n"];
}
- [result appendString: [ NSString stringWithFormat:@"),\n"]];
-
- row = [rowIndexes indexGreaterThanIndex: row];
-
- } //end for each row
-
- if ( [result length] > 3 )
- {
- [result deleteCharactersInRange:NSMakeRange([result length]-2, 2)];
+ } else {
+ [value appendString:@"),\n"];
+ [result appendString:value];
}
-
- [result appendString:@";\n"];
-
- return result;
- }
- else
- {
- return nil;
- }
+
+ // next selected row
+ // row = [rowIndexes indexGreaterThanIndex: row];
+
+ } //end for each row
+
+ // delete last ",/n"
+ if ( [result length] > 3 )
+ [result deleteCharactersInRange:NSMakeRange([result length]-2, 2)];
+
+ [result appendString:@";\n"];
+
+ return result;
}
+
//get dragged rows a string of newline separated lines of tab separated fields
//the value in each field is from the objects description method
- (NSString *)draggedRowsAsTabString:(NSArray *)rows
@@ -310,10 +387,16 @@ int MENU_EDIT_COPY_AS_SQL = 2002;
}
}
-- (void)setTableInfoWithColumns:(NSArray *)columnDefs withTable:(NSString *)aTable
+/*
+ * Init self with data coming from the table content view. Mainly used for copying data properly.
+ */
+- (void)setTableInstance:(id)anInstance withTableData:(id)theTableData withColumns:(NSArray *)columnDefs withTableName:(NSString *)aTableName withConnection:(id)aMySqlConnection
{
columnDefinitions = [[NSArray arrayWithArray:columnDefs] retain];
- selectedTable = aTable;
+ selectedTable = aTableName;
+ tableData = theTableData;
+ mySQLConnection = aMySqlConnection;
+ tableInstance = anInstance;
}
@end
diff --git a/Source/TableContent.m b/Source/TableContent.m
index 7c548307..855c44ca 100644
--- a/Source/TableContent.m
+++ b/Source/TableContent.m
@@ -154,9 +154,6 @@
theColumns = [tableDataInstance columns];
columnNames = [tableDataInstance columnNames];
- // Init copyTable with necessary information for copying selected rows as SQL INSERT
- [tableContentView setTableInfoWithColumns:theColumns withTable:selectedTable];
-
// Retrieve the total number of rows of the current table
// to adjustify "Limit From:"
maxNumRowsOfCurrentTable = [[[tableDataInstance statusValues] objectForKey:@"Rows"] intValue];
@@ -347,6 +344,9 @@
// Reload the table data
[tableContentView reloadData];
+ // Init copyTable with necessary information for copying selected rows as SQL INSERT
+ [tableContentView setTableInstance:self withTableData:filteredResult withColumns:theColumns withTableName:selectedTable withConnection:mySQLConnection];
+
// Post the notification that the query is finished
[[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryHasBeenPerformed" object:self];
}