[Feature]: Add all the supported server-side Appsmith data types

[subrata71]

Is there an existing issue for this?

• [X] I have searched the existing issues

SubTasks

No response

[subrata71]

MySQL r2dbc has support for the following data types.

Data Type Mapping

The default built-in Codecs reference table shows the type mapping between MySQL and Java data types:

MySQL Type	Signed/Unsigned	Support Data Type
INT	UNSIGNED	Long, BigInteger
INT	SIGNED	Integer, Long, BigInteger
TINYINT	UNSIGNED	Short, Integer, Long, BigInteger, Boolean (Size is 1)
TINYINT	SIGNED	Byte, Short, Integer, Long, BigInteger, Boolean (Size is 1)
SMALLINT	UNSIGNED	Integer, Long, BigInteger
SMALLINT	SIGNED	Short, Integer, Long, BigInteger
MEDIUMINT	SIGNED/UNSIGNED	Integer, Long, BigInteger
BIGINT	UNSIGNED	BigInteger, Long (Not check overflow)
BIGINT	SIGNED	Long, BigInteger
FLOAT	SIGNED / UNSIGNED	Float, BigDecimal
DOUBLE	SIGNED / UNSIGNED	Double, BigDecimal
DECIMAL	SIGNED / UNSIGNED	BigDecimal, Float (Size less than 7), Double (Size less than 16)
BIT	-	ByteBuffer, BitSet, Boolean (Size is 1), byte[]
DATETIME / TIMESTAMP	-	LocalDateTime, ZonedDateTime, OffsetDateTime, Instant
DATE	-	LocalDate
TIME	-	LocalTime, Duration, OffsetTime
YEAR	-	Short, Integer, Long, BigInteger, Year
VARCHAR / NVARCHAR	-	String
VARBINARY	-	ByteBuffer, Blob, byte[]
CHAR / NCHAR	-	String
ENUM	-	String, Enum<?>
SET	-	String[], String, Set<String> and Set<Enum<?>> (Set<T> need use ParameterizedType)
BLOBs (LONGBLOB, etc.)	-	ByteBuffer, Blob, byte[]
TEXTs (LONGTEXT, etc.)	-	String, Clob
JSON	-	String, Clob
GEOMETRY	-	byte[], Blob

[subrata71]

Going through the RFC written by Nidhi about two months ago in order to verify whether this can be a good starting point or not.

Reference RFC

https://github.com/appsmithorg/appsmith/pull/14726

[subrata71]

Should the undefined value be treated as null? Wrote a document in Notion addressing this.

[subrata71]

For MySQL Date and Timestamp/Datetime data types the following supported formats are introduced in the respective MySQLDateType and MySQLDatetimeType Appsmith data types.

MySQL Date

select cast('20220822' as date)

select cast('2012-12-31' as date)

select cast('2012/12/31' as date)

select cast('070523' as date)

MySQL Datetime (1000-01-01 00:00:00' to '9999-12-31 23:59:59')

select cast('2012-12-31T11:30:45Z' as datetime)

select cast('2012-12-31 11:30:45' as datetime)

select cast('2012/12/31 11:30:45' as datetime)

select cast('20121231113045' as datetime)

select cast('121231113045' as datetime)

MySQL Timestamp ('1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC)

select timestamp('2012-12-31 11:30:45')

select timestamp('2012/12/31 11:30:45')

select timestamp('20121231113045')

select timestamp('121231113045')

[subrata71]

Mapping of data types among JS type (client-side data type), Java type, and the respective plugin type.

MySQL

JS Type	Java Type	Plugin Type
NULL	null	NULL
Number	int	Int signed
Number	long	Int unsigned
Number	int	TINYINT signed
Number	int	TINYINT unsigned
Number	int	SMALLINT signed
Number	Int	SMALLINT unsigned
Number	Int	MEDIUMINT sigend
Number	Int	MEDIUMINT unsigned
Number	long	BIGINT signed
Number	long	BIGINT unsigned
Number	float	FLOAT signed
Number	float	FLOAT unsigned
Number	double	DOUBLE signed
Number	double	DOUBLE unsgined
Number	Double/bigdecimal	DECIMAL
Number	Int	BIT
Number	Int	YEAR
Boolean	Int	TINYINT
String	String	VARCHAR/NVARCHAR/CHAR
String	String	ENUM
String	String	TEXT/LONGTEXT
Object	LocalDateTime	DATETIME/TIMESTAMP
Object	LocalDate	DATE
Object	LocalTime	TIME
Object	String	JSON