* Add a JSON formatter

* MySQL JSON type columns are now automatically formatted when opening them in the Field Editor

4 files changed, 511 insertions, 3 deletions

diff --git a/Source/SPFieldEditorController.h b/Source/SPFieldEditorController.h
index ec36c9d2..0c0d0a0e 100644
--- a/Source/SPFieldEditorController.h
+++ b/Source/SPFieldEditorController.h
@@ -188,6 +188,7 @@
 
 	NSInteger editSheetReturnCode;
 	BOOL _isGeometry;
+	BOOL _isJSON;
 	NSUndoManager *esUndoManager;
 
 	NSDictionary *editedFieldInfo;
diff --git a/Source/SPFieldEditorController.m b/Source/SPFieldEditorController.m
index a32f32dc..19668d74 100644
--- a/Source/SPFieldEditorController.m
+++ b/Source/SPFieldEditorController.m
@@ -37,6 +37,7 @@
 #include <objc/objc-runtime.h>
 #import "SPCustomQuery.h"
 #import "SPTableContent.h"
+#import "SPJSONFormatter.h"
 
 #import <SPMySQL/SPMySQL.h>
 
@@ -237,6 +238,7 @@ typedef enum {
 	callerInstance = sender;
 
 	_isGeometry = ([[fieldType uppercaseString] isEqualToString:@"GEOMETRY"]) ? YES : NO;
+	_isJSON     = ([[fieldType uppercaseString] isEqualToString:SPMySQLJsonType]);
 
 	// Set field label
 	NSMutableString *label = [NSMutableString string];
@@ -250,7 +252,7 @@ typedef enum {
 		[label appendString:fieldType];
 
 	//skip length for JSON type since it's a constant and MySQL doesn't display it either
-	if (maxTextLength > 0 && ![[fieldType uppercaseString] isEqualToString:SPMySQLJsonType])
+	if (maxTextLength > 0 && !_isJSON)
 		[label appendFormat:@"(%lld) ", maxTextLength];
 
 	if (!_allowNULL)
@@ -353,7 +355,8 @@ typedef enum {
 
 		encoding = anEncoding;
 
-		_isBlob = isFieldBlob;
+		// we don't want the hex/image controls for JSON
+		_isBlob = (!_isJSON && isFieldBlob);
 		
 		BOOL isBinary = ([[fieldType uppercaseString] isEqualToString:@"BINARY"] || [[fieldType uppercaseString] isEqualToString:@"VARBINARY"]);
 
@@ -443,7 +446,18 @@ typedef enum {
 			[editTextScrollView setHidden:NO];
 		}
 		else {
-			stringValue = [sheetEditData retain];
+			// If the input is a JSON type column we can format it.
+			// Since MySQL internally stores JSON in binary, it does not retain any formatting
+			do {
+				if(_isJSON) {
+					NSString *formatted = [SPJSONFormatter stringByFormattingString:sheetEditData];
+					if(formatted) {
+						stringValue = [formatted retain];
+						break;
+					}
+				}
+				stringValue = [sheetEditData retain];
+			} while(0);
 
 			[hexTextView setString:@""];
 
@@ -652,6 +666,12 @@ typedef enum {
 	if(callerInstance) {
 		id returnData = ( editSheetReturnCode && _isEditable ) ? (_isGeometry) ? [editTextView string] : sheetEditData : nil;
 		
+		//for MySQLs JSON type remove the formatting again, since it won't be stored anyway
+		if(_isJSON) {
+			NSString *unformatted = [SPJSONFormatter stringByUnformattingString:returnData];
+			if(unformatted) returnData = unformatted;
+		}
+		
 #ifdef SP_CODA /* patch */
 		if ( [callerInstance isKindOfClass:[SPCustomQuery class]] )
 			[(SPCustomQuery*)callerInstance processFieldEditorResult:returnData contextInfo:contextInfo];
diff --git a/Source/SPJSONFormatter.h b/Source/SPJSONFormatter.h
new file mode 100644
index 00000000..fd0b7afd
--- /dev/null
+++ b/Source/SPJSONFormatter.h
@@ -0,0 +1,123 @@
+//
+//  SPJSONFormatter.h
+//  sequel-pro
+//
+//  Created by Max Lohrmann on 10.02.17.
+//  Copyright (c) 2017 Max Lohrmann. All rights reserved.
+//
+//  Permission is hereby granted, free of charge, to any person
+//  obtaining a copy of this software and associated documentation
+//  files (the "Software"), to deal in the Software without
+//  restriction, including without limitation the rights to use,
+//  copy, modify, merge, publish, distribute, sublicense, and/or sell
+//  copies of the Software, and to permit persons to whom the
+//  Software is furnished to do so, subject to the following
+//  conditions:
+//
+//  The above copyright notice and this permission notice shall be
+//  included in all copies or substantial portions of the Software.
+//
+//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
+//  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
+//  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
+//  NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
+//  HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
+//  WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
+//  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
+//  OTHER DEALINGS IN THE SOFTWARE.
+//
+//  More info at <https://github.com/sequelpro/sequelpro>
+
+#import <Foundation/Foundation.h>
+
+typedef NS_ENUM(UInt8, SPJSONToken) {
+	JSON_TOK_EOF,
+	JSON_TOK_CURLY_BRACE_OPEN,
+	JSON_TOK_CURLY_BRACE_CLOSE,
+	JSON_TOK_SQUARE_BRACE_OPEN,
+	JSON_TOK_SQUARE_BRACE_CLOSE,
+	JSON_TOK_DOUBLE_QUOTE,
+	JSON_TOK_COLON,
+	JSON_TOK_COMMA,
+	JSON_TOK_OTHER,
+	JSON_TOK_STRINGDATA
+};
+
+typedef NS_ENUM(UInt8, SPJSONContext) {
+	JSON_ROOT_CONTEXT,
+	JSON_STRING_CONTEXT
+};
+
+typedef struct {
+	const char *str;
+	size_t len;
+	size_t pos;
+	SPJSONContext ctxt;
+} SPJSONTokenizerState;
+
+typedef struct {
+	SPJSONToken tok;
+	size_t pos;
+	size_t len;
+} SPJSONTokenInfo;
+
+/**
+ * Initializes a caller defined SPJSONTokenizerState structure to the string that is passed.
+ * The string is not retained. The caller is responsible for making sure it stays around as long 
+ * as the tokenizer is used!
+ *
+ * @return 0 on success, -1 if an argument was NULL.
+ */
+int SPJSONTokenizerInit(NSString *input, SPJSONTokenizerState *stateInfo);
+
+/**
+ * This function returns the token that is at the current position of the input string or following
+ * it most closely and forward the input string accordingly.
+ * 
+ * The JSON_TOK_EOF token is a zero length token that is returned after the last character in the input
+ * string has been read and tokenized. Any call to this function after JSON_TOK_EOF has been returned
+ * will return the same.
+ *
+ * JSON_TOK_OTHER and JSON_TOK_STRINGDATA are variable length tokens (but never 0) that represent whitespace,
+ * numbers, true/false/null and the contents of strings (without the double quotes).
+ *
+ * The remaining tokens correspond to the respective control characters in JSON and are always a single
+ * character long.
+ *
+ * The token/position/length information will be assigned to the tokenMatch argument given by the caller.
+ *
+ * @return  1 If a token was successfully matched
+ *          0 If the matched token was JSON_TOK_EOF (tokenMatch will still be set, like for 1)
+ *         -1 If the passed arguments were invalid (tokenMatch will not be updated)
+ *
+ * DO NOT try to build a parser/syntax validator based on this code! It is much too lenient for those purposes!
+ */
+int SPJSONTokenizerGetNextToken(SPJSONTokenizerState *stateInfo, SPJSONTokenInfo *tokenMatch);
+
+
+@interface SPJSONFormatter : NSObject
+
+/**
+ * This method will return a formatted copy of the input string.
+ *
+ *  - A line break is inserted after every ",".
+ *  - There will be a line break after every "{" and "[" (except if they are empty) and the indent
+ *    of the following lines is increased by 1.
+ *  - There will be a line break before "]" and "}" (except if they are empty) and the indent of this line
+ *    and the following lines will be decreased by 1.
+ *  - A line break will be inserted after "]" and "}", except if a "," follows.
+ *  - Indenting is done using a single "\t" character per level.
+ *
+ * @return The formatted string or nil if formatting failed.
+ */
++ (NSString *)stringByFormattingString:(NSString *)input;
+
+/**
+ * This method will return a compact copy of the input string.
+ * All whitespace (outside of strings) will be removed (except for a single space after ":" and ",")
+ *
+ * @return The unformatted string or nil if unformatting failed.
+ */
++ (NSString *)stringByUnformattingString:(NSString *)input;
+
+@end
diff --git a/Source/SPJSONFormatter.m b/Source/SPJSONFormatter.m
new file mode 100644
index 00000000..05cc2992
--- /dev/null
+++ b/Source/SPJSONFormatter.m
@@ -0,0 +1,364 @@
+//
+//  SPJSONFormatter.m
+//  sequel-pro
+//
+//  Created by Max Lohrmann on 10.02.17.
+//  Copyright (c) 2017 Max Lohrmann. All rights reserved.
+//
+//  Permission is hereby granted, free of charge, to any person
+//  obtaining a copy of this software and associated documentation
+//  files (the "Software"), to deal in the Software without
+//  restriction, including without limitation the rights to use,
+//  copy, modify, merge, publish, distribute, sublicense, and/or sell
+//  copies of the Software, and to permit persons to whom the
+//  Software is furnished to do so, subject to the following
+//  conditions:
+//
+//  The above copyright notice and this permission notice shall be
+//  included in all copies or substantial portions of the Software.
+//
+//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
+//  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
+//  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
+//  NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
+//  HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
+//  WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
+//  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
+//  OTHER DEALINGS IN THE SOFTWARE.
+//
+//  More info at <https://github.com/sequelpro/sequelpro>
+
+#import "SPJSONFormatter.h"
+
+
+static char GetNextANSIChar(SPJSONTokenizerState *stateInfo);
+
+
+@implementation SPJSONFormatter
+
++ (NSString *)stringByFormattingString:(NSString *)input
+{
+	SPJSONTokenizerState stateInfo;
+	if(SPJSONTokenizerInit(input,&stateInfo) == -1) return nil;
+	
+	NSUInteger idLevel = 0;
+	
+	NSCharacterSet *wsNlCharset = [NSCharacterSet whitespaceAndNewlineCharacterSet];
+	NSMutableString *formatted = [[NSMutableString alloc] init];
+	
+	SPJSONToken prevTokenType = JSON_TOK_EOF;
+	SPJSONTokenInfo curToken;
+	if(SPJSONTokenizerGetNextToken(&stateInfo,&curToken) == -1) {
+		[formatted release];
+		return nil;
+	}
+	
+	BOOL needIndent = NO;
+	SPJSONTokenInfo nextToken;
+	do {
+		//we need to know the next token to do meaningful formatting
+		if(SPJSONTokenizerGetNextToken(&stateInfo,&nextToken) == -1) {
+			[formatted release];
+			return nil;
+		}
+		
+		if(curToken.tok == JSON_TOK_SQUARE_BRACE_CLOSE || curToken.tok == JSON_TOK_CURLY_BRACE_CLOSE)
+			idLevel--;
+		
+		//if this token is a "]" or "}" and there was no "[" or "{" directly before it, add a linebreak before
+		if(prevTokenType != JSON_TOK_CURLY_BRACE_OPEN && prevTokenType != JSON_TOK_SQUARE_BRACE_OPEN && (curToken.tok == JSON_TOK_SQUARE_BRACE_CLOSE || curToken.tok == JSON_TOK_CURLY_BRACE_CLOSE)) {
+			[formatted appendString:@"\n"];
+			needIndent = YES;
+		}
+		
+		//if this token is on a new line indent it
+		if(needIndent && idLevel > 0) {
+			//32 tabs pool (with fallback for even deeper nesting)
+			static NSString *tabs = @"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
+			NSUInteger myIdLevel = idLevel;
+			while(myIdLevel > [tabs length]) {
+				[formatted appendString:tabs];
+				myIdLevel -= [tabs length];
+			}
+			[formatted appendString:[tabs substringWithRange:NSMakeRange(0, myIdLevel)]];
+			needIndent = NO;
+		}
+		
+		//save ourselves the overhead of creating an NSString if we already know what it will contain
+		NSString *curTokenString;
+		id freeMe = nil;
+		switch (curToken.tok) {
+			case JSON_TOK_CURLY_BRACE_OPEN:
+				curTokenString = @"{";
+				break;
+				
+			case JSON_TOK_CURLY_BRACE_CLOSE:
+				curTokenString = @"}";
+				break;
+				
+			case JSON_TOK_SQUARE_BRACE_OPEN:
+				curTokenString = @"[";
+				break;
+				
+			case JSON_TOK_SQUARE_BRACE_CLOSE:
+				curTokenString = @"]";
+				break;
+				
+			case JSON_TOK_DOUBLE_QUOTE:
+				curTokenString = @"\"";
+				break;
+				
+			case JSON_TOK_COLON:
+				curTokenString = @": "; //add a space after ":" for readability
+				break;
+				
+			case JSON_TOK_COMMA:
+				curTokenString = @",";
+				break;
+				
+			//JSON_TOK_OTHER
+			//JSON_TOK_STRINGDATA
+			default:
+				curTokenString = [[NSString alloc] initWithBytesNoCopy:(void *)(&stateInfo.str[curToken.pos]) length:curToken.len encoding:NSUTF8StringEncoding freeWhenDone:NO];
+				//for everything except strings get rid of surrounding whitespace
+				if(curToken.tok != JSON_TOK_STRINGDATA) {
+					NSString *newTokenString = [[curTokenString stringByTrimmingCharactersInSet:wsNlCharset] retain];
+					[curTokenString release];
+					curTokenString = newTokenString;
+				}
+				freeMe = curTokenString;
+		}
+		
+		[formatted appendString:curTokenString];
+		
+		if(freeMe) [freeMe release];
+		
+		//if the current token is a "[", "{" or "," and the next token is not a "]" or "}" add a line break afterwards
+		if(
+		   curToken.tok == JSON_TOK_COMMA ||
+		   (curToken.tok == JSON_TOK_CURLY_BRACE_OPEN && nextToken.tok != JSON_TOK_CURLY_BRACE_CLOSE) ||
+		   (curToken.tok == JSON_TOK_SQUARE_BRACE_OPEN && nextToken.tok != JSON_TOK_SQUARE_BRACE_CLOSE)
+		) {
+			[formatted appendString:@"\n"];
+			needIndent = YES;
+		}
+		
+		if(curToken.tok == JSON_TOK_CURLY_BRACE_OPEN || curToken.tok == JSON_TOK_SQUARE_BRACE_OPEN)
+			idLevel++;
+		
+		prevTokenType = curToken.tok;
+		curToken = nextToken;
+	} while(curToken.tok != JSON_TOK_EOF); //SPJSONTokenizerGetNextToken() will always return JSON_TOK_EOF once it has reached that state
+	
+	return [formatted autorelease];
+}
+
++ (NSString *)stringByUnformattingString:(NSString *)input
+{
+	SPJSONTokenizerState stateInfo;
+	if(SPJSONTokenizerInit(input,&stateInfo) == -1) return nil;
+	
+	NSCharacterSet *wsNlCharset = [NSCharacterSet whitespaceAndNewlineCharacterSet];
+	NSMutableString *unformatted = [[NSMutableString alloc] init];
+	
+	do {
+		SPJSONTokenInfo curToken;
+		if(SPJSONTokenizerGetNextToken(&stateInfo,&curToken) == -1) {
+			[unformatted release];
+			return nil;
+		}
+		
+		if(curToken.tok == JSON_TOK_EOF) break;
+		
+		//save ourselves the overhead of creating an NSString from input if we already know what it will contain
+		NSString *curTokenString;
+		id freeMe = nil;
+		switch (curToken.tok) {
+			case JSON_TOK_CURLY_BRACE_OPEN:
+				curTokenString = @"{";
+				break;
+				
+			case JSON_TOK_CURLY_BRACE_CLOSE:
+				curTokenString = @"}";
+				break;
+				
+			case JSON_TOK_SQUARE_BRACE_OPEN:
+				curTokenString = @"[";
+				break;
+				
+			case JSON_TOK_SQUARE_BRACE_CLOSE:
+				curTokenString = @"]";
+				break;
+				
+			case JSON_TOK_DOUBLE_QUOTE:
+				curTokenString = @"\"";
+				break;
+				
+			case JSON_TOK_COLON:
+				curTokenString = @": "; //add a space after ":" to match MySQL
+				break;
+				
+			case JSON_TOK_COMMA:
+				curTokenString = @", "; //add a space after "," to match MySQL
+				break;
+				
+			//JSON_TOK_OTHER
+			//JSON_TOK_STRINGDATA
+			default:
+				curTokenString = [[NSString alloc] initWithBytesNoCopy:(void *)(&stateInfo.str[curToken.pos]) length:curToken.len encoding:NSUTF8StringEncoding freeWhenDone:NO];
+				//for everything except strings get rid of surrounding whitespace
+				if(curToken.tok != JSON_TOK_STRINGDATA) {
+					NSString *newTokenString = [[curTokenString stringByTrimmingCharactersInSet:wsNlCharset] retain];
+					[curTokenString release];
+					curTokenString = newTokenString;
+				}
+				freeMe = curTokenString;
+		}
+		
+		[unformatted appendString:curTokenString];
+		
+		if(freeMe) [freeMe release];
+		
+	} while(1);
+	
+	return [unformatted autorelease];
+}
+
+
+@end
+
+/**
+ * This function returns the char at the current position in the input string and forwards the read pointer to the next char.
+ * If the character is part of an UTF8 multibyte sequence, the function will skip forward until a single byte character is found again
+ * or EOF is reached (whichever comes first).
+ *
+ * stateInfo MUST be valid or this will crash!
+ *
+ * @return Either a char in the range 0-127 or -1 if EOF is reached.
+ */
+char GetNextANSIChar(SPJSONTokenizerState *stateInfo) {
+	do {
+		if(stateInfo->pos >= stateInfo->len)
+			return -1;
+		char val = stateInfo->str[stateInfo->pos++];
+		// all utf8 multibyte characters start with the most significant bit being 1 for all of their bytes
+		// but since all JSON control characters are in the single byte ANSI compatible plane, we can just ignore any MB chars
+		if((val & 0x80) == 0)
+			return val;
+	} while(1);
+}
+
+int SPJSONTokenizerInit(NSString *input, SPJSONTokenizerState *stateInfo) {
+	if(!input || ![input respondsToSelector:@selector(UTF8String)] || stateInfo == NULL)
+		return -1;
+	
+	stateInfo->ctxt = JSON_ROOT_CONTEXT;
+	stateInfo->pos = 0;
+	stateInfo->str = [input UTF8String];
+	stateInfo->len = strlen(stateInfo->str); //we deem -[NSString UTF8String] to be a safe source
+	
+	return 0;
+}
+
+int SPJSONTokenizerGetNextToken(SPJSONTokenizerState *stateInfo, SPJSONTokenInfo *tokenMatch) {
+	if(tokenMatch == NULL || stateInfo == NULL || stateInfo->str == NULL)
+		return -1;
+	
+	size_t posBefore = stateInfo->pos;
+	do {
+		char c = GetNextANSIChar(stateInfo);
+		if(stateInfo->ctxt == JSON_STRING_CONTEXT) {
+			//the only characters inside a string that are relevant to us are backslash and doublequote
+			if(c == '"' || c == -1) {
+				//if the string has contents, return that first
+				if((stateInfo->pos - posBefore) > 1) {
+					tokenMatch->tok = JSON_TOK_STRINGDATA;
+					tokenMatch->pos = posBefore;
+					if(c == '"')
+						stateInfo->pos--; //rewind to read it again
+					tokenMatch->len = stateInfo->pos - posBefore;
+					return 1;
+				}
+				//string is terminated by EOF (invalid JSON)
+				if(c == -1) {
+					//switch to root context and try again to reach EOF branch below
+					stateInfo->ctxt = JSON_ROOT_CONTEXT;
+					continue;
+				}
+				stateInfo->ctxt = JSON_ROOT_CONTEXT;
+				tokenMatch->tok = JSON_TOK_DOUBLE_QUOTE;
+				tokenMatch->pos = posBefore;
+				tokenMatch->len = stateInfo->pos - posBefore;
+				return 1;
+			}
+			else if(c == '\\') {
+				//for backslash we need to skip the next byte
+				// We don't care for the value of the next byte since we don't really want to parse JSON, but only format it.
+				// Thus we only have to pay attention to differntiate backslash-dquote and dquote.
+				stateInfo->pos++;
+			}
+		}
+		else if(c == -1) {
+			//if there is still unreturned input, return that first
+			if(posBefore < stateInfo->len) {
+				tokenMatch->tok = JSON_TOK_OTHER;
+				tokenMatch->pos = posBefore;
+				tokenMatch->len = stateInfo->pos - posBefore;
+				return 1;
+			}
+			tokenMatch->tok = JSON_TOK_EOF;
+			tokenMatch->pos = stateInfo->pos; //EOF sits after the last character
+			tokenMatch->len = 0; // EOF has no length
+			return 0;
+		}
+		else {
+			SPJSONToken tokFound = JSON_TOK_EOF;
+			
+			switch(c) {
+				case '"':
+					stateInfo->ctxt = JSON_STRING_CONTEXT;
+					tokFound = JSON_TOK_DOUBLE_QUOTE;
+					break;
+					
+				case '{':
+					tokFound = JSON_TOK_CURLY_BRACE_OPEN;
+					break;
+					
+				case '}':
+					tokFound = JSON_TOK_CURLY_BRACE_CLOSE;
+					break;
+					
+				case '[':
+					tokFound = JSON_TOK_SQUARE_BRACE_OPEN;
+					break;
+					
+				case ']':
+					tokFound = JSON_TOK_SQUARE_BRACE_CLOSE;
+					break;
+					
+				case ':':
+					tokFound = JSON_TOK_COLON;
+					break;
+					
+				case ',':
+					tokFound = JSON_TOK_COMMA;
+					break;
+			}
+			
+			//if we found a token, but had to walk more than 1 char there was something else
+			//between the previous token and this token, which we should report first
+			if(tokFound != JSON_TOK_EOF && (stateInfo->pos - posBefore) > 1) {
+				stateInfo->ctxt = JSON_ROOT_CONTEXT;
+				stateInfo->pos--; //rewind so we will read the token again next time
+				tokFound = JSON_TOK_OTHER;
+			}
+			
+			if(tokFound != JSON_TOK_EOF) {
+				tokenMatch->tok = tokFound;
+				tokenMatch->pos = posBefore;
+				tokenMatch->len = stateInfo->pos - posBefore;
+				return 1;
+			}
+		}
+	} while(1);
+}