Add basic support for JSON type (part of #2199)

5 files changed, 43 insertions, 18 deletions

diff --git a/Source/SPEditorTokens.l b/Source/SPEditorTokens.l
index 2ac99be8..d3fac252 100644
--- a/Source/SPEditorTokens.l
+++ b/Source/SPEditorTokens.l
@@ -61,7 +61,7 @@ numeric	([+-]?(([0-9]+\.[0-9]+)|([0-9]*\.[0-9]+)|([0-9]+))(e[+-]?[0-9]+)?)
 ops		"+"|"-"|"*"|"/"
 word	[a-z_\.0-9\x80-\xEF@]
 variable	@{1,2}[a-z_\.0-9\x80-\xEF$]+
-keyworda	(G(R(OUP{s}BY|ANT(S)?)|E(NERAL|T_FORMAT|OMETRY(COLLECTION)?)|LOBAL)|B(Y(TE)?|TREE|I(GINT|N(LOG|ARY)|T)|O(TH|OL(EAN)?)|E(GIN|TWEEN|FORE)|LOB|ACKUP{s}TABLE)|H(IGH_PRIORITY|O(ST(S)?|UR(_(MI(NUTE|CROSECOND)|SECOND))?)|ELP|A(SH|NDLER|VING))|C(R(OSS|EATE)|H(ECK(SUM)?|A(R(SET|ACTER)?|NGE(D)?|IN))|IPHER|O(M(M(IT(TED)?|ENT)|P(RESSED|LETION|ACT))|N(S(TRAINT(_(SCHEMA|NAME|CATALOG))?|ISTENT)|NECTION|CURRENT|T(RIBUTORS|INUE|AINS)|DITION|VERT)|DE|L(UMN(S|_(NAME|FORMAT))?|LATE)|ALESCE{s}PARTITION)|U(R(RENT_(TIME(STAMP)?|DATE|USER)|SOR(_NAME)?)|BE)|L(IENT|OSE|ASS_ORIGIN)|A(S(CADE(D)?|E)|CHE{s}INDEX|TALOG_NAME|LL))|I(GNORE(_SERVER_IDS)?|MPORT{s}TABLESPACE|S(SUER|OLATION)?|N(S(TALL({s}PLUGIN)?|E(RT(_METHOD)?|NSITIVE))|N(O(BASE|DB)|ER)|T(1|2|8|3|O({s}(DUMP|OUT)FILE)?|4|E(RVAL|GER))?|ITIAL_SIZE|OUT|DEX(ES)?|VOKER|FILE)?|TERATE|O_THREAD|DENTIFIED|F)|D(ROP|YNAMIC|I(RECTORY|S(CARD{s}TABLESPACE|TINCT(ROW)?|K|ABLE{s}KEYS)|V)|O(UBLE)?|U(MPFILE|PLICATE|AL)|E(S(C(RIBE)?|_KEY_FILE)|C(IMAL|LARE)?|TERMINISTIC|F(INER|AULT)|L(ETE|AY(_KEY_WRITE|ED))|ALLOCATE)|A(Y(_(MI(NUTE|CROSECOND)|SECOND|HOUR))?|T(E(TIME)?|A(BASE(S)?|FILE)?)))|JOIN|E(RRORS|X(TEN(T_SIZE|DED)|I(STS|T)|P(LAIN|ANSION)|ECUTE)|SCAPE(D{s}BY)?|N(GINE(S)?|CLOSED{s}BY|D(S)?|UM|ABLE{s}KEYS)|VE(RY|NT)|LSE(IF)?|ACH)|K(ILL({s}(CONNECTION|QUERY))?|EY(S|_BLOCK_SIZE)?)|F(R(OM|AC_SECOND)|I(RST|XED|LE)|O(R(CE|EIGN)?|UND)|U(NCTION|LL(TEXT)?)|ETCH|L(OAT(8|4)?|USH)|A(ST|LSE))|A(G(GREGATE|AINST)|S(C(II)?|ENSITIVE)?|N(Y|D|ALYZE)|C(CESSIBLE|TION)|T|DD|UT(HORS|O(_INCREMENT|EXTEND_SIZE))|VG(_ROW_LENGTH)?|FTER|L(GORITHM|TER|L)))
+keyworda	(G(R(OUP{s}BY|ANT(S)?)|E(NERAL|T_FORMAT|OMETRY(COLLECTION)?)|LOBAL)|B(Y(TE)?|TREE|I(GINT|N(LOG|ARY)|T)|O(TH|OL(EAN)?)|E(GIN|TWEEN|FORE)|LOB|ACKUP{s}TABLE)|H(IGH_PRIORITY|O(ST(S)?|UR(_(MI(NUTE|CROSECOND)|SECOND))?)|ELP|A(SH|NDLER|VING))|C(R(OSS|EATE)|H(ECK(SUM)?|A(R(SET|ACTER)?|NGE(D)?|IN))|IPHER|O(M(M(IT(TED)?|ENT)|P(RESSED|LETION|ACT))|N(S(TRAINT(_(SCHEMA|NAME|CATALOG))?|ISTENT)|NECTION|CURRENT|T(RIBUTORS|INUE|AINS)|DITION|VERT)|DE|L(UMN(S|_(NAME|FORMAT))?|LATE)|ALESCE{s}PARTITION)|U(R(RENT_(TIME(STAMP)?|DATE|USER)|SOR(_NAME)?)|BE)|L(IENT|OSE|ASS_ORIGIN)|A(S(CADE(D)?|E)|CHE{s}INDEX|TALOG_NAME|LL))|I(GNORE(_SERVER_IDS)?|MPORT{s}TABLESPACE|S(SUER|OLATION)?|N(S(TALL({s}PLUGIN)?|E(RT(_METHOD)?|NSITIVE))|N(O(BASE|DB)|ER)|T(1|2|8|3|O({s}(DUMP|OUT)FILE)?|4|E(RVAL|GER))?|ITIAL_SIZE|OUT|DEX(ES)?|VOKER|FILE)?|TERATE|O_THREAD|DENTIFIED|F)|D(ROP|YNAMIC|I(RECTORY|S(CARD{s}TABLESPACE|TINCT(ROW)?|K|ABLE{s}KEYS)|V)|O(UBLE)?|U(MPFILE|PLICATE|AL)|E(S(C(RIBE)?|_KEY_FILE)|C(IMAL|LARE)?|TERMINISTIC|F(INER|AULT)|L(ETE|AY(_KEY_WRITE|ED))|ALLOCATE)|A(Y(_(MI(NUTE|CROSECOND)|SECOND|HOUR))?|T(E(TIME)?|A(BASE(S)?|FILE)?)))|J(OI|SO)N|E(RRORS|X(TEN(T_SIZE|DED)|I(STS|T)|P(LAIN|ANSION)|ECUTE)|SCAPE(D{s}BY)?|N(GINE(S)?|CLOSED{s}BY|D(S)?|UM|ABLE{s}KEYS)|VE(RY|NT)|LSE(IF)?|ACH)|K(ILL({s}(CONNECTION|QUERY))?|EY(S|_BLOCK_SIZE)?)|F(R(OM|AC_SECOND)|I(RST|XED|LE)|O(R(CE|EIGN)?|UND)|U(NCTION|LL(TEXT)?)|ETCH|L(OAT(8|4)?|USH)|A(ST|LSE))|A(G(GREGATE|AINST)|S(C(II)?|ENSITIVE)?|N(Y|D|ALYZE)|C(CESSIBLE|TION)|T|DD|UT(HORS|O(_INCREMENT|EXTEND_SIZE))|VG(_ROW_LENGTH)?|FTER|L(GORITHM|TER|L)))
 keywordl	(R(TREE|IGHT|O(UTINE|W(S|_FORMAT)?|LL(BACK|UP))|E(GEXP|MOVE{s}PARTITIONING|BUILD{s}PARTITION|S(T(RICT|ORE{s}TABLE)|IGNAL|UME|ET)|NAME|COVER|TURN(S)?|ORGANIZE{s}PARTITION|D(O(_BUFFER_SIZE|FILE)|UNDANT)|P(EAT(ABLE)?|L(ICATION|ACE)|AIR)|VOKE|QUIRE|FERENCES|L(OAD|EASE|AY_(THREAD|LOG_(POS|FILE)))|A(D(S|_(ONLY|WRITE))?|L))|LIKE|ANGE)|M(YSQL_ERRNO|I(GRATE|N(_ROWS|UTE(_(MICROSECOND|SECOND))?)|CROSECOND|DDLEINT)|O(NTH|D(IF(Y|IES)|E)?)|U(TEX|LTI(PO(INT|LYGON)|LINESTRING))|E(RGE|MORY|SSAGE_TEXT|DIUM(BLOB|TEXT|INT)?)|A(X(_(ROWS|SIZE|CONNECTIONS_PER_HOUR|U(SER_CONNECTIONS|PDATES_PER_HOUR)|QUERIES_PER_HOUR)|VALUE)|STER(_(S(SL(_(C(IPHER|ERT|A(PATH)?)|VERIFY_SERVER_CERT|KEY))?|ERVER_ID)|H(OST|EARTBEAT_PERIOD)|CONNECT_RETRY|USER|P(ORT|ASSWORD)|LOG_(POS|FILE)))?|TCH))|N(CHAR|O(NE|_W(RITE_TO_BINLOG|AIT)|T|DEGROUP)?|DB(CLUSTER)?|U(MERIC|LL)|E(XT|W)|VARCHAR|A(ME(S)?|T(IONAL|URAL)))|O(R(DER{s}BY)?|N(({s}DUPLICATE{s}KEY{s}UPDATE)?|E(_SHOT)?|LINE)?|UT(ER|FILE)?|P(TI(MIZE|ON(S|ALLY)?)|EN)|FF(SET|LINE)|WNER|LD_PASSWORD)|P(R(I(MARY|VILEGES)|OCE(SS|DURE{s}(ANALYSE)?)|E(SERVE|CISION|PARE|V))|HASE|O(RT|INT|LYGON)|URGE|A(R(SER|TI(TION(S|ING)?|AL))|SSWORD|CK_KEYS))|QU(ICK|ERY|ARTER)|L(I(MIT|ST|NE(S(TRING)?|AR)|KE)|O(G(S|FILE({s}GROUP))|NG(BLOB|TEXT)?|C(K(S)?|AL(TIME(STAMP)?)?)|OP|W_PRIORITY|AD{s}(DATA|INDEX{s}INTO{s}CACHE|XML))|E(SS|VEL|FT|A(DING|VE(S)?))|A(ST|NGUAGE)))
 keywords	(X(OR|509|A)|S(MALLINT|SL|H(OW({s}(E(NGINE(S)?|RRORS)|M(ASTER|UTEX)|BINLOG|GRANTS|INNODB|P(RIVILEGES|ROFILE(S)?|ROCEDURE{s}CODE)|SLAVE{s}(HOSTS|STATUS)|TRIGGERS|VARIABLES|WARNINGS|(FULL{s})?PROCESSLIST|FIELDS|PLUGIN(S)?|STORAGE{s}ENGINES|TABLE{s}TYPES|CO(LUMNS|LLATION)|BINLOG{s}EVENTS))?|UTDOWN|ARE)|NAPSHOT|CHE(MA(S|_NAME)?|DULE(R)?)|T(R(ING|AIGHT_JOIN)|O(RAGE|P)|A(RT(S|ING{s}BY)?|TUS))|I(GN(ED|AL)|MPLE)|O(ME|NAME|CKET|UNDS)|U(B(CLASS_ORIGIN|JECT|PARTITION(S)?)|SPEND|PER)|P(ECIFIC|ATIAL)|E(R(IAL(IZABLE)?|VER)|SSION|NSITIVE|C(OND(_MICROSECOND)?|URITY)|T({s}(PASSWORD|NAMES|ONE_SHOT))?|PARATOR|LECT)|QL(STATE|_(MAX_JOIN_SIZE|B(IG_(RESULT|SELECTS|TABLES)|UFFER_RESULT)|S(MALL_RESULT|ELECT_LIMIT|LAVE_SKIP_COUNTER|AFE_UPDATES)|NO_CACHE|CA(CHE|LC_FOUND_ROWS)|T(SI_(M(INUTE|ONTH)|SECOND|HOUR|YEAR|DAY|QUARTER|FRAC_SECOND|WEEK)|HREAD)|QUOTE_SHOW_CREATE|WARNINGS|LO(G_(BIN|OFF|UPDATE)|W_PRIORITY_UPDATES)|AUTO_IS_NULL)|EXCEPTION|WARNING)?|L(OW|AVE)|AVEPOINT)|YEAR(_MONTH)?|T(R(IGGER(S)?|U(NCATE|E)|A(NSACTION|ILING))|H(EN|AN)|YPE|I(ME(STAMP(DIFF|ADD)?)?|NY(BLOB|TEXT|INT))|O|E(RMINATED{s}BY|XT|MP(TABLE|ORARY))|ABLE(S(PACE)?|_NAME)?)|ZEROFILL|U(S(ING|E(R(_RESOURCES)?|_FRM)?|AGE)|N(SIGNED|COMMITTED|TIL|I(NSTALL({s}PLUGIN)?|CODE|ON|QUE)|D(O(_BUFFER_SIZE|FILE)?|EFINED)|KNOWN|LOCK)|TC_(TIME(STAMP)?|DATE)|P(GRADE|DATE))|V(IEW|A(R(BINARY|YING|CHAR(ACTER)?|IABLES)|LUE(S)?))|W(R(ITE|APPER)|H(ILE|E(RE|N))|ITH({s}PARSER)?|ORK|EEK|A(RNINGS|IT)))
 
@@ -348,6 +348,7 @@ ISOLATION
 ISSUER
 ITERATE
 JOIN
+JSON
 KEY
 KEYS
 KEY_BLOCK_SIZE
diff --git a/Source/SPTableData.m b/Source/SPTableData.m
index 62732b51..689ecc8f 100644
--- a/Source/SPTableData.m
+++ b/Source/SPTableData.m
@@ -1267,7 +1267,8 @@
 	} else if ([detailString isEqualToString:@"ENUM"] || [detailString isEqualToString:@"SET"]) {
 		[fieldDetails setObject:@"enum" forKey:@"typegrouping"];
 	} else if ([detailString isEqualToString:@"TINYTEXT"] || [detailString isEqualToString:@"TEXT"]
-				|| [detailString isEqualToString:@"MEDIUMTEXT"] || [detailString isEqualToString:@"LONGTEXT"]) {
+				|| [detailString isEqualToString:@"MEDIUMTEXT"] || [detailString isEqualToString:@"LONGTEXT"]
+	            || [detailString isEqualToString:@"JSON"]) { // JSON is seen as a text type by us, but works a bit different (e.g. encoding is always "utf8mb4")
 		[fieldDetails setObject:@"textdata" forKey:@"typegrouping"];
 	} else if ([detailString isEqualToString:@"POINT"] || [detailString isEqualToString:@"GEOMETRY"]
 				|| [detailString isEqualToString:@"LINESTRING"] || [detailString isEqualToString:@"POLYGON"]
diff --git a/Source/SPTableStructure.m b/Source/SPTableStructure.m
index 81d0c531..2347f12b 100644
--- a/Source/SPTableStructure.m
+++ b/Source/SPTableStructure.m
@@ -194,6 +194,7 @@ static inline SPFieldTypeHelp *MakeFieldTypeHelp(NSString *typeName,NSString *ty
 		SPMySQLLongBlobType,
 		SPMySQLBinaryType,
 		SPMySQLVarBinaryType,
+		SPMySQLJsonType,
 		SPMySQLEnumType,
 		SPMySQLSetType,
 		@"--------",
@@ -959,7 +960,10 @@ static inline SPFieldTypeHelp *MakeFieldTypeHelp(NSString *typeName,NSString *ty
 	if(!specialFieldTypes) {
 
 
-		if ([fieldValidation isFieldTypeString:theRowType]) {
+		if ([theRowType isEqualToString:@"JSON"]) {
+			// we "see" JSON as a string, but it is not internally to MySQL and so doesn't allow CHARACTER SET/BINARY/COLLATE either.
+		}
+		else if ([fieldValidation isFieldTypeString:theRowType]) {
 			BOOL charsetSupport = [[tableDocumentInstance serverSupport] supportsPost41CharacterSetHandling];
 
 			// Add CHARSET
@@ -1002,8 +1006,9 @@ static inline SPFieldTypeHelp *MakeFieldTypeHelp(NSString *typeName,NSString *ty
 		// Don't provide any defaults for auto-increment fields
 		if (![theRowExtra isEqualToString:@"AUTO_INCREMENT"]) {
 			NSArray *matches;
+			NSString *defaultValue = [theRow objectForKey:@"default"];
 			// If a NULL value has been specified, and NULL is allowed, specify DEFAULT NULL
-			if ([[theRow objectForKey:@"default"] isEqualToString:[prefs objectForKey:SPNullValue]]) 
+			if ([defaultValue isEqualToString:[prefs objectForKey:SPNullValue]])
 			{
 				if ([[theRow objectForKey:@"null"] integerValue] == 1) {
 					[queryString appendString:@"\n DEFAULT NULL"];
@@ -1011,7 +1016,7 @@ static inline SPFieldTypeHelp *MakeFieldTypeHelp(NSString *typeName,NSString *ty
 			}
 			// Otherwise, if CURRENT_TIMESTAMP was specified for timestamps/datetimes, use that
 			else if ([theRowType isInArray:@[@"TIMESTAMP",@"DATETIME"]] &&
-					[(matches = [[[theRow objectForKey:@"default"] uppercaseString] captureComponentsMatchedByRegex:SPCurrentTimestampPattern]) count])
+					[(matches = [[defaultValue uppercaseString] captureComponentsMatchedByRegex:SPCurrentTimestampPattern]) count])
 			{
 				[queryString appendString:@"\n DEFAULT CURRENT_TIMESTAMP"];
 				NSString *userLen = [matches objectAtIndex:1];
@@ -1023,17 +1028,17 @@ static inline SPFieldTypeHelp *MakeFieldTypeHelp(NSString *typeName,NSString *ty
 			}
 			// If the field is of type BIT, permit the use of single qoutes and also don't quote the default value.
 			// For example, use DEFAULT b'1' as opposed to DEFAULT 'b\'1\'' which results in an error.
-			else if ([(NSString *)[theRow objectForKey:@"default"] length] && [theRowType isEqualToString:@"BIT"]) {
-				[queryString appendFormat:@"\n DEFAULT %@", [theRow objectForKey:@"default"]];
+			else if ([defaultValue length] && [theRowType isEqualToString:@"BIT"]) {
+				[queryString appendFormat:@"\n DEFAULT %@", defaultValue];
 			}
 			// Suppress appending DEFAULT clause for any numerics, date, time fields if default is empty to avoid error messages;
-			// also don't specify a default for TEXT/BLOB or geometry fields to avoid strict mode errors
-			else if (![(NSString *)[theRow objectForKey:@"default"] length] && ([fieldValidation isFieldTypeNumeric:theRowType] || [fieldValidation isFieldTypeDate:theRowType] || [theRowType hasSuffix:@"TEXT"] || [theRowType hasSuffix:@"BLOB"] || [fieldValidation isFieldTypeGeometry:theRowType])) {
+			// also don't specify a default for TEXT/BLOB, JSON or geometry fields to avoid strict mode errors
+			else if (![defaultValue length] && ([fieldValidation isFieldTypeNumeric:theRowType] || [fieldValidation isFieldTypeDate:theRowType] || [theRowType hasSuffix:@"TEXT"] || [theRowType hasSuffix:@"BLOB"] || [theRowType isEqualToString:@"JSON"] || [fieldValidation isFieldTypeGeometry:theRowType])) {
 				;
 			}
 			// Otherwise, use the provided default
 			else {
-				[queryString appendFormat:@"\n DEFAULT %@", [mySQLConnection escapeAndQuoteString:[theRow objectForKey:@"default"]]];
+				[queryString appendFormat:@"\n DEFAULT %@", [mySQLConnection escapeAndQuoteString:defaultValue]];
 			}
 		}
 
@@ -1718,6 +1723,12 @@ static inline SPFieldTypeHelp *MakeFieldTypeHelp(NSString *typeName,NSString *ty
 				NSLocalizedString(@"A byte array with variable length. The actual number of bytes is further limited by the values of other fields in the row.",@"description of varbinary")
 			),
 			MakeFieldTypeHelp(
+				SPMySQLJsonType,
+				@"JSON",
+				NSLocalizedString(@"Limited to @@max_allowed_packet", @"range for json type"),
+				NSLocalizedString(@"A data type that validates JSON data on INSERT and internally stores it in a binary format that is both, more compact and faster to access than textual JSON.\nAvailable from MySQL 5.7.8.", @"description of json")
+			),
+			MakeFieldTypeHelp(
 				SPMySQLEnumType,
 				@"ENUM('member',...)",
 				[NSString stringWithFormat:NSLocalizedString(@"Up to %@ distinct members (<%@ in practice)\n1-2 bytes storage", @"range for enum type"),FN(@(65535)),FN(@3000)],
diff --git a/Source/SPTableStructureDelegate.m b/Source/SPTableStructureDelegate.m
index a7fa9337..755cc2b8 100644
--- a/Source/SPTableStructureDelegate.m
+++ b/Source/SPTableStructureDelegate.m
@@ -540,13 +540,14 @@ static void _BuildMenuWithPills(NSMenu *menu,struct _cmpMap *map,size_t mapEntri
 			rowType = [[rowType stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]] uppercaseString];
 		}
 		
-		// Only string fields allow encoding settings
+		// Only string fields allow encoding settings, but JSON only uses utf8mb4
 		if (([[tableColumn identifier] isEqualToString:@"encoding"])) {
-			[aCell setEnabled:([fieldValidation isFieldTypeString:rowType] && [[tableDocumentInstance serverSupport] supportsPost41CharacterSetHandling])];
+			[aCell setEnabled:(![rowType isEqualToString:@"JSON"] && [fieldValidation isFieldTypeString:rowType] && [[tableDocumentInstance serverSupport] supportsPost41CharacterSetHandling])];
 		}
 		
 		// Only string fields allow collation settings and string field is not set to BINARY since BINARY sets the collation to *_bin
 		else if ([[tableColumn identifier] isEqualToString:@"collation"]) {
+			// JSON always uses utf8mb4_bin which is already covered by this logic
  			[aCell setEnabled:([fieldValidation isFieldTypeString:rowType] && [[row objectForKey:@"binary"] integerValue] == 0 && [[tableDocumentInstance serverSupport] supportsPost41CharacterSetHandling])];
 		}
 		
@@ -557,12 +558,13 @@ static void _BuildMenuWithPills(NSMenu *menu,struct _cmpMap *map,size_t mapEntri
 		
 		// Check if BINARY is allowed
 		else if ([[tableColumn identifier] isEqualToString:@"binary"]) {
-			[aCell setEnabled:([fieldValidation isFieldTypeAllowBinary:rowType])];
+			// JSON always uses utf8mb4_bin
+			[aCell setEnabled:(![rowType isEqualToString:@"JSON"] && [fieldValidation isFieldTypeAllowBinary:rowType])];
 		}
 		
-		// TEXT, BLOB, and GEOMETRY fields don't allow a DEFAULT
+		// TEXT, BLOB, GEOMETRY and JSON fields don't allow a DEFAULT
 		else if ([[tableColumn identifier] isEqualToString:@"default"]) {
-			[aCell setEnabled:([rowType hasSuffix:@"TEXT"] || [rowType hasSuffix:@"BLOB"] || [fieldValidation isFieldTypeGeometry:rowType]) ? NO : YES];
+			[aCell setEnabled:([rowType hasSuffix:@"TEXT"] || [rowType hasSuffix:@"BLOB"] || [rowType isEqualToString:@"JSON"] || [fieldValidation isFieldTypeGeometry:rowType]) ? NO : YES];
 		}
 		
 		// Check allow NULL
@@ -572,10 +574,11 @@ static void _BuildMenuWithPills(NSMenu *menu,struct _cmpMap *map,size_t mapEntri
 							   [[[tableDataInstance statusValueForKey:@"Engine"] uppercaseString] isEqualToString:@"CSV"]) ? NO : YES];
 		}
 		
-		// TEXT, BLOB, date, and GEOMETRY fields don't allow a length
+		// TEXT, BLOB, date, GEOMETRY and JSON fields don't allow a length
 		else if ([[tableColumn identifier] isEqualToString:@"length"]) {
 			[aCell setEnabled:([rowType hasSuffix:@"TEXT"] || 
-							   [rowType hasSuffix:@"BLOB"] || 
+							   [rowType hasSuffix:@"BLOB"] ||
+			                   [rowType isEqualToString:@"JSON"] ||
 							   ([fieldValidation isFieldTypeDate:rowType] && ![[tableDocumentInstance serverSupport] supportsFractionalSeconds] && ![rowType isEqualToString:@"YEAR"]) || 
 							   [fieldValidation isFieldTypeGeometry:rowType]) ? NO : YES];
 		}
@@ -659,6 +662,7 @@ static void _BuildMenuWithPills(NSMenu *menu,struct _cmpMap *map,size_t mapEntri
 	NSInteger pos = [typeSuggestions indexOfObject:[cell stringValue]];
 	if(pos != NSNotFound) {
 		[cell selectItemAtIndex:pos];
+		[cell scrollItemAtIndexToTop:pos];
 	}
 	
 	//set up the help window to the right position
diff --git a/Source/SPTableStructureLoading.m b/Source/SPTableStructureLoading.m
index 57191b4b..b7f8e748 100644
--- a/Source/SPTableStructureLoading.m
+++ b/Source/SPTableStructureLoading.m
@@ -157,7 +157,15 @@
 	{
 		NSString *type = [[[theField objectForKey:@"type"] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]] uppercaseString];
 
-		if ([fieldValidation isFieldTypeString:type]) {
+		if([type isEqualToString:@"JSON"]) {
+			// MySQL 5.7 manual:
+			// "MySQL handles strings used in JSON context using the utf8mb4 character set and utf8mb4_bin collation.
+			//  Strings in other character set are converted to utf8mb4 as necessary."
+			[theField setObject:@"utf8mb4" forKey:@"encodingName"];
+			[theField setObject:@"utf8mb4_bin" forKey:@"collationName"];
+			[theField setObject:@1 forKey:@"binary"];
+		}
+		else if ([fieldValidation isFieldTypeString:type]) {
 			// The MySQL 4.1 manual says:
 			//
 			// MySQL chooses the column character set and collation in the following manner: