Type Mappings

typemappings.txt

Default Type Mappings for languages and frameworks:

Type	C++	Python	Java	    	JDBC		SQLAlchemy[1]		VOTable
boolean	bool		bool		boolean	BOOLEAN		BOOLEAN				boolean
byte		int8		int		byte		TINYINT		SMALLINT[2]			unsignedByte
short	int16	int		short	SMALLINT	SMALLINT			short
int		int32	int		int		INTEGER		INTEGER				int
long		int64	int		long		BIGINT		BIGINT				long
float	float	float	float	FLOAT		FLOAT				float
double	double	float	double	DOUBLE		FLOAT(precision=53)	double
char		string	str		String	CHAR		CHAR				char[] [3]
string	string	str		String	VARCHAR	VARCHAR			char[] [3]
unicode	string	str		String	NVARCHAR	NVARCHAR			unicodeChar[] [3]
text		string	str		String	CLOB		CLOB				unicodeChar[] [3]
binary	string	bytes	byte[]	BLOB		BLOB				unsignedByte[] [3]


Preferred Use Of Names:
boolean == bool
byte == int8 == tinyint [2]
short == int16 == smallint
int == int32
long == int64 == bigint
string == varchar
unicode == nvarchar
text == clob
binary == blob

[1] This is the default SQLAlchemy Mapping
[2] SQLAlchemy has no "TinyInteger", so you need to override, or the default is SMALLINT
[3] The length is an additional parameter elsewhere for VOTable types

Default Type Mappings for each database:

MySQLDatabase
boolean: BIT(1)
byte: TINYINT
short: SMALLINT
int: INT
long: BIGINT
float: FLOAT
double: DOUBLE
decimal: DECIMAL
number: numeric
char: CHAR
string: VARCHAR
nchar: NCHAR
unicode: NVARCHAR
text: LONGTEXT
binary: LONGBLOB
datetime: datetime
time: time
timestamp: timestamp
date: date
uuid: char(36)

SQLiteDatabase
boolean: BOOLEAN
byte: TINYINT
short: SMALLINT
int: INTEGER
long: BIGINT
float: FLOAT
double: DOUBLE
decimal: DECIMAL
number: NUMBER
char: CHAR
string: VARCHAR
nchar: NCHAR
unicode: NVARCHAR
text: TEXT
binary: BLOB
datetime: TEXT
time: time
timestamp: TEXT
date: date
uuid: TEXT

OracleDatabase
boolean: NUMBER(1)
byte: NUMBER(3)
short: NUMBER(5)
int: INTEGER
long: NUMBER(38, 0)
float: FLOAT
double: FLOAT(24)
decimal: DECIMAL
number: NUMBER
char: CHAR
string: VARCHAR2
nchar: NCHAR
unicode: NVARCHAR2
text: CLOB
binary: BLOB
datetime: TIMESTAMP
time: DATE
timestamp: TIMESTAMP
date: date
uuid: RAW(16)

PostgresDatabase
boolean: BOOLEAN
byte: SMALLINT
short: SMALLINT
int: INT
long: BIGINT
float: FLOAT
double: DOUBLE PRECISION
decimal: DECIMAL
number: numeric
char: CHAR
string: VARCHAR
nchar: NCHAR
unicode: VARCHAR
text: TEXT
binary: BYTEA
datetime: TIMESTAMP WITHOUT TIME ZONE
time: TIME WITHOUT TIME ZONE
timestamp: TIMESTAMP WITHOUT TIME ZONE
date: date
uuid: UUID

Native-Only Types:
json == text
bit ==> binary
array ==> binary

Additional Notes:
* Discourage use of nchar
* Scalar string types (char(1), nchar(1) will map to a string type of length 1, not a primitive char type)
* Not sure how to make sure unsigned ints, especially uint64, can be represented in database
* decimal and number types should be treated as implementation and name a nominal type (int, float, double, etc...)
* not sure how to represent timestamps and datetimes just yet
* binary, by default, maps to a BLOB