basic constraint editing working with db for add/delete

4 files changed, 157 insertions, 27 deletions

diff --git a/Interfaces/English.lproj/DBView.xib b/Interfaces/English.lproj/DBView.xib
index 7f321dcc..80c0fc45 100644
--- a/Interfaces/English.lproj/DBView.xib
+++ b/Interfaces/English.lproj/DBView.xib
@@ -8,7 +8,7 @@
 		<string key="IBDocument.HIToolboxVersion">353.00</string>
 		<object class="NSMutableArray" key="IBDocument.EditedObjectIDs">
 			<bool key="EncodedWithXMLCoder">YES</bool>
-			<integer value="5652"/>
+			<integer value="25"/>
 		</object>
 		<object class="NSArray" key="IBDocument.PluginDependencies">
 			<bool key="EncodedWithXMLCoder">YES</bool>
@@ -37,7 +37,7 @@
 				<string key="NSWindowContentMaxSize">{3.40282e+38, 3.40282e+38}</string>
 				<string key="NSWindowContentMinSize">{780, 480}</string>
 				<object class="NSView" key="NSWindowView" id="579726586">
-					<nil key="NSNextResponder"/>
+					<reference key="NSNextResponder"/>
 					<int key="NSvFlags">256</int>
 					<object class="NSMutableArray" key="NSSubviews">
 						<bool key="EncodedWithXMLCoder">YES</bool>
@@ -3675,7 +3675,7 @@
 																			<object class="NSTableView" id="952375340">
 																				<reference key="NSNextResponder" ref="705580858"/>
 																				<int key="NSvFlags">4352</int>
-																				<string key="NSFrameSize">{688, 450}</string>
+																				<string key="NSFrameSize">{688, 459}</string>
 																				<reference key="NSSuperview" ref="705580858"/>
 																				<bool key="NSEnabled">YES</bool>
 																				<object class="NSTableHeaderView" key="NSHeaderView" id="370758200">
@@ -3898,7 +3898,7 @@
 																				<bool key="NSAllowsTypeSelect">YES</bool>
 																			</object>
 																		</object>
-																		<string key="NSFrame">{{1, 17}, {688, 450}}</string>
+																		<string key="NSFrame">{{1, 17}, {688, 459}}</string>
 																		<reference key="NSSuperview" ref="717203719"/>
 																		<reference key="NSNextKeyView" ref="952375340"/>
 																		<reference key="NSDocView" ref="952375340"/>
@@ -3940,7 +3940,7 @@
 																	</object>
 																	<reference ref="793765259"/>
 																</object>
-																<string key="NSFrame">{{5, 40}, {690, 468}}</string>
+																<string key="NSFrame">{{5, 31}, {690, 477}}</string>
 																<reference key="NSSuperview" ref="49633996"/>
 																<reference key="NSNextKeyView" ref="705580858"/>
 																<int key="NSsFlags">562</int>
@@ -3954,7 +3954,7 @@
 															<object class="NSButton" id="448462855">
 																<reference key="NSNextResponder" ref="49633996"/>
 																<int key="NSvFlags">292</int>
-																<string key="NSFrame">{{5, 16}, {32, 25}}</string>
+																<string key="NSFrame">{{5, 8}, {32, 25}}</string>
 																<reference key="NSSuperview" ref="49633996"/>
 																<bool key="NSEnabled">YES</bool>
 																<object class="NSButtonCell" key="NSCell" id="796363844">
@@ -3975,7 +3975,7 @@
 															<object class="NSButton" id="962247647">
 																<reference key="NSNextResponder" ref="49633996"/>
 																<int key="NSvFlags">292</int>
-																<string key="NSFrame">{{67, 16}, {32, 25}}</string>
+																<string key="NSFrame">{{67, 8}, {32, 25}}</string>
 																<reference key="NSSuperview" ref="49633996"/>
 																<bool key="NSEnabled">YES</bool>
 																<object class="NSButtonCell" key="NSCell" id="815097407">
@@ -3996,7 +3996,7 @@
 															<object class="NSButton" id="349122210">
 																<reference key="NSNextResponder" ref="49633996"/>
 																<int key="NSvFlags">292</int>
-																<string key="NSFrame">{{36, 16}, {32, 25}}</string>
+																<string key="NSFrame">{{36, 8}, {32, 25}}</string>
 																<reference key="NSSuperview" ref="49633996"/>
 																<bool key="NSEnabled">YES</bool>
 																<object class="NSButtonCell" key="NSCell" id="693201961">
@@ -4029,7 +4029,7 @@
 																		<string>NeXT TIFF v4.0 pasteboard type</string>
 																	</object>
 																</object>
-																<string key="NSFrame">{{99, 17}, {596, 23}}</string>
+																<string key="NSFrame">{{99, 9}, {596, 23}}</string>
 																<reference key="NSSuperview" ref="49633996"/>
 																<bool key="NSEnabled">YES</bool>
 																<object class="NSImageCell" key="NSCell" id="889065570">
@@ -4091,6 +4091,7 @@
 						</object>
 					</object>
 					<string key="NSFrameSize">{944, 550}</string>
+					<reference key="NSSuperview"/>
 				</object>
 				<string key="NSScreenRect">{{0, 0}, {1440, 878}}</string>
 				<string key="NSMinSize">{780, 502}</string>
@@ -8673,7 +8674,7 @@ IGRvIHlvdSB3YW50IHRvIGFkZCBmb3IgdGhpcyBmaWVsZD8</string>
 				<nil key="NSViewClass"/>
 				<string key="NSWindowContentMaxSize">{3.40282e+38, 3.40282e+38}</string>
 				<object class="NSView" key="NSWindowView" id="842408319">
-					<reference key="NSNextResponder"/>
+					<nil key="NSNextResponder"/>
 					<int key="NSvFlags">256</int>
 					<object class="NSMutableArray" key="NSSubviews">
 						<bool key="EncodedWithXMLCoder">YES</bool>
@@ -9334,7 +9335,6 @@ IGRvIHlvdSB3YW50IHRvIGFkZCBmb3IgdGhpcyBmaWVsZD8</string>
 						</object>
 					</object>
 					<string key="NSFrameSize">{473, 314}</string>
-					<reference key="NSSuperview"/>
 				</object>
 				<string key="NSScreenRect">{{0, 0}, {1440, 878}}</string>
 				<string key="NSMaxSize">{3.40282e+38, 3.40282e+38}</string>
@@ -20214,11 +20214,11 @@ IGRvIHlvdSB3YW50IHRvIGFkZCBmb3IgdGhpcyBmaWVsZD8</string>
 						<object class="NSMutableArray" key="children">
 							<bool key="EncodedWithXMLCoder">YES</bool>
 							<reference ref="717203719"/>
+							<reference ref="970233701"/>
 							<reference ref="448462855"/>
 							<reference ref="962247647"/>
 							<reference ref="349122210"/>
 							<reference ref="620090308"/>
-							<reference ref="970233701"/>
 						</object>
 						<reference key="parent" ref="986490149"/>
 					</object>
@@ -23479,8 +23479,8 @@ IGRvIHlvdSB3YW50IHRvIGFkZCBmb3IgdGhpcyBmaWVsZD8</string>
 					</object>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
-					<string>{{101, 288}, {944, 550}}</string>
-					<string>{{101, 288}, {944, 550}}</string>
+					<string>{{61, 288}, {944, 550}}</string>
+					<string>{{61, 288}, {944, 550}}</string>
 					<reference ref="9"/>
 					<reference ref="9"/>
 					<string>{{62, 352}, {845, 504}}</string>
@@ -24764,7 +24764,6 @@ Y2hhbmdlIHRoZSBvcmRlcg</string>
 							<string>connect:</string>
 							<string>connectSheetAddToFavorites:</string>
 							<string>connectToDB:</string>
-							<string>copyColumnNames:</string>
 							<string>copyCreateTableSyntax:</string>
 							<string>editFavorites:</string>
 							<string>export:</string>
@@ -24822,7 +24821,6 @@ Y2hhbmdlIHRoZSBvcmRlcg</string>
 							<string>id</string>
 							<string>id</string>
 							<string>id</string>
-							<string>id</string>
 						</object>
 					</object>
 					<object class="NSMutableDictionary" key="outlets">
diff --git a/Source/SPTableData.m b/Source/SPTableData.m
index 23f3f396..57421d0e 100644
--- a/Source/SPTableData.m
+++ b/Source/SPTableData.m
@@ -441,13 +441,15 @@
 			}
 			// primary key
 			else if( [[parts objectAtIndex:0] hasPrefix:@"PRIMARY"] ) {
-				NSLog( @"pkey is %@", [[parts objectAtIndex:2] stringByTrimmingCharactersInSet:junk] );				
+				//NSLog( @"pkey is %@", [[parts objectAtIndex:2] stringByTrimmingCharactersInSet:junk] );				
 			}
 			// key
 			else if( [[parts objectAtIndex:0] hasPrefix:@"KEY"] ) {
+				/*
 				NSLog( @"key %@.%@", 
 					  [[parts objectAtIndex:1] stringByTrimmingCharactersInSet:junk],
 					  [[parts objectAtIndex:2] stringByTrimmingCharactersInSet:junk] );				
+				 */
 			}
 			// who knows
 			else {
diff --git a/Source/TableRelations.h b/Source/TableRelations.h
index 9e385e95..60f00074 100644
--- a/Source/TableRelations.h
+++ b/Source/TableRelations.h
@@ -3,7 +3,23 @@
 //  sequel-pro
 //
 //  Created by J Knight on 13/05/09.
-//  Copyright 2009 TalonEdge Ltd.. All rights reserved.
+//  Copyright 2009 J Knight. All rights reserved.
+//
+//  This program is free software; you can redistribute it and/or modify
+//  it under the terms of the GNU General Public License as published by
+//  the Free Software Foundation; either version 2 of the License, or
+//  (at your option) any later version.
+//
+//  This program is distributed in the hope that it will be useful,
+//  but WITHOUT ANY WARRANTY; without even the implied warranty of
+//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+//  GNU General Public License for more details.
+//
+//  You should have received a copy of the GNU General Public License
+//  along with this program; if not, write to the Free Software
+//  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+//
+//  More info at <http://code.google.com/p/sequel-pro/>
 //
 
 #import <Cocoa/Cocoa.h>
diff --git a/Source/TableRelations.m b/Source/TableRelations.m
index 60f19aca..322ab4e1 100644
--- a/Source/TableRelations.m
+++ b/Source/TableRelations.m
@@ -1,9 +1,25 @@
 //
-//  TableRelations.m
+//  TableRelations.h
 //  sequel-pro
 //
 //  Created by J Knight on 13/05/09.
-//  Copyright 2009 TalonEdge Ltd.. All rights reserved.
+//  Copyright 2009 J Knight. All rights reserved.
+//
+//  This program is free software; you can redistribute it and/or modify
+//  it under the terms of the GNU General Public License as published by
+//  the Free Software Foundation; either version 2 of the License, or
+//  (at your option) any later version.
+//
+//  This program is distributed in the hope that it will be useful,
+//  but WITHOUT ANY WARRANTY; without even the implied warranty of
+//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+//  GNU General Public License for more details.
+//
+//  You should have received a copy of the GNU General Public License
+//  along with this program; if not, write to the Free Software
+//  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+//
+//  More info at <http://code.google.com/p/sequel-pro/>
 //
 
 #import "TableRelations.h"
@@ -15,6 +31,9 @@
 
 @implementation TableRelations
 
+/*
+ * init
+ */
 - (id)init
 {
 	if (![super init])
@@ -25,6 +44,9 @@
 	return self;
 }
 
+/*
+ * dealloc
+ */
 - (void)dealloc
 {	
 	[relData release], relData = nil;
@@ -32,6 +54,9 @@
 	[super dealloc];
 }
 
+/*
+ * awakeFromNib
+ */
 - (void)awakeFromNib
 {
 	[[NSNotificationCenter defaultCenter] addObserver:self
@@ -40,22 +65,68 @@
 											   object:tableList];
 }
 
+/*
+ * setConnection
+ * set the database connection
+ */
 - (void)setConnection:(CMMCPConnection *)theConnection
 {
 	mySQLConnection = theConnection;			
 }
 
+/*
+ * closeRelationSheet
+ * happens if the user hits cancel
+ */
 - (IBAction)closeRelationSheet:(id)sender
 {
-	[NSApp stopModalWithCode:1];
+	[NSApp stopModalWithCode:0];
 }
 
+/*
+ * addRelation
+ * attempt to add the relations from the relationSheet data
+ */
 - (IBAction)addRelation:(id)sender
 {
-	[NSApp stopModalWithCode:0];	
+	// 0 = success,
+	int retCode = 0;
+	NSString *thisTable = [tablesListInstance tableName];
+	NSString *thisColumn = [columnSelect titleOfSelectedItem];
+	NSString *thatTable = [refTableSelect titleOfSelectedItem];
+	NSString *thatColumn = [refColumnSelect titleOfSelectedItem];
+	NSString *onUpdate = [onUpdateSelect titleOfSelectedItem];
+	NSString *onDelete = [onDeleteSelect titleOfSelectedItem];
+	NSString *query = [NSString stringWithFormat:
+					   @"ALTER TABLE `%@` ADD FOREIGN KEY (`%@`) REFERENCES `%@` (`%@`)", 
+					   thisTable,
+					   thisColumn,
+					   thatTable,
+					   thatColumn];
+	
+	if( [onDelete length] ) {
+		query = [query stringByAppendingString:[NSString stringWithFormat:@" ON DELETE %@", onDelete]];
+	}
+	if( [onUpdate length] ) {
+			query = [query stringByAppendingString:[NSString stringWithFormat:@" ON UPDATE %@", onUpdate]];
+	}
+	
+	NSLog( query );
+
+	[mySQLConnection queryString:query];
+	
+	if ( ! [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
+		NSLog(@"error: %@", [mySQLConnection getLastErrorMessage]);
+		retCode = 1;
+	} 
+		
+	[NSApp stopModalWithCode:retCode];	
 }
 
-// user choose a reference table
+/*
+ * chooseRefTable
+ * update the columns select when the user chooses a reference table
+ */
 - (IBAction)chooseRefTable:(id)sender
 {
 	NSString *table = [refTableSelect titleOfSelectedItem];
@@ -72,6 +143,10 @@
 	[colNames release];
 }
 
+/*
+ * addRow
+ * called when the user indicated they want to add a relation
+ */
 - (IBAction)addRow:(id)sender
 {
 	// set up the controls
@@ -84,7 +159,6 @@
 	NSArray *types = [tablesListInstance tableTypes];
 	NSMutableArray *validTables = [[NSMutableArray alloc] init];
 	for( int i = 0; i < [tables count]; i++ ) {
-		NSLog( @"%@ %@", [tables objectAtIndex:i], [types objectAtIndex:i] );
 		if( [[types objectAtIndex:i] intValue] == SP_TABLETYPE_TABLE ) {
 			[validTables addObject:[tables objectAtIndex:i]];
 		}
@@ -100,31 +174,66 @@
 		  contextInfo:nil];
 	
 	
-	[NSApp runModalForWindow:relationSheet];
+	int code = [NSApp runModalForWindow:relationSheet];
 	
 	[NSApp endSheet:relationSheet];
 	[relationSheet orderOut:nil];
+
+	// 0 indicates success
+	if( code ) {
+		NSRunAlertPanel(@"Error Adding Relation",
+						[NSString stringWithFormat:@"There was a problem adding the relation.\n%@",[mySQLConnection getLastErrorMessage]],
+						@"Dang!", nil, nil );		
+	} else {
+		[self refresh:nil];		
+	}
 }
 
+/*
+ * removeRow
+ * called when rows are selected and the user wants to remove those relations
+ */
 - (IBAction)removeRow:(id)sender
 {
 	if ( [relationsView numberOfSelectedRows] ) {
 		int resp = NSRunAlertPanel(@"Remove Relations",
-		@"Are you sure you want to remove the selected relations?",
+								   @"Are you sure you want to remove the selected relations?",
 								   @"OK", @"Cancel", nil );
 		if( resp == NSAlertDefaultReturn ) {
-			
+			NSString *thisTable = [tablesListInstance tableName];
+			NSIndexSet *selectedSet = [relationsView selectedRowIndexes];
+			unsigned int row = [selectedSet lastIndex];
+			while( row != NSNotFound ) {
+				NSArray *relName = [[relData objectAtIndex:row] objectForKey:@"name"];
+				NSString *query = [NSString stringWithFormat:@"ALTER TABLE `%@` DROP FOREIGN KEY `%@`",
+								   thisTable, relName];
+				//NSLog( query );
+				
+				[mySQLConnection queryString:query];
+				
+				if ( ! [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
+					NSLog(@"error: %@", [mySQLConnection getLastErrorMessage]);
+				} 
+				row = [selectedSet indexLessThanIndex:row];
+			}
+			[self refresh:nil];
 		}
 	}
 }
 
+/*
+ * refresh
+ * called to refesh the relations list
+ */
 - (IBAction)refresh:(id)sender
 {
 
 	[relData removeAllObjects];
 	
 	if( [tablesListInstance tableType] == SP_TABLETYPE_TABLE ) {
+		// update the top label
 		[labelText setStringValue:[NSString stringWithFormat:@"Relations for table: %@",[tablesListInstance tableName]]];			
+		
 		[tableDataInstance updateInformationForCurrentTable];
 		NSArray *constraints = [tableDataInstance getConstraints];
 		for( int i = 0; i < [constraints count]; i++ ) {
@@ -140,6 +249,7 @@
 			
 		}
 	} else {
+		// update the top label
 		[labelText setStringValue:@""];		
 	}
 	
@@ -147,6 +257,10 @@
 
 }
 
+/*
+ * tableChanged
+ * notification from the tableList when the users click a table
+ */
 - (void)tableChanged:(NSNotification *)notification
 {
 	if( [tablesListInstance tableType] == SP_TABLETYPE_TABLE ) {