DAP Relational Database Server II

Introduction

Previous Design/Implementation

Mapping from JDBC Types to DODS Types:

JDBC Type	DAP Type
TINYINT	DByte
SMALLINT	DInt16
INTEGER	DInt32
BIGINT	DInt32 **NO SENSIBLE MAPPING (Need DInt64)
REAL	DFloat32
FLOAT	DFloat64
DOUBLE	DFloat64
DECIMAL	DFloat64 **NO SENSIBLE MAPPING (Need Some Kind Monsterous Floating point value)
NUMERIC	DFloat64 **NO SENSIBLE MAPPING (ibid)
BIT	DBoolean
CHAR	DString
VARCHAR	DString
LONGVARCHAR	Implemented to be read into a DString, although it is a "BLOB" type and might be better represented as a DArray(of bytes).
BINARY	DArray(of bytes)
VARBINARY	DArray(of bytes)
LONGVARBINARY	DArray(of bytes)
DATE	DString
TIME	DString
TIMESTAMP	DString

And are handled in this (the read()) method for each of the correspoonding DODS data types.

These read() methods must be sure to read from the current column in the sqlResponse object, and then bump the column counter when finished in order for the sqlResponse object to be ready for the next invocation of a read() method.

Data model representation

This implementation is intended to be reading data from a JDBC connection to a relational database. It is important to note that their are several layers of type translation happening in this:

Database -> JDBC -> Java -> DODS

The Database types are the native types for the particular database that is being read from. The translation from Database->JDBC is handled before we get to the data (most likely by the JDBC Drivers). Our mapping of JDBC type to DODS types (the intermediate Java types happen in the process) looks like this:

SQL Implementation Data Types

Transact-SQL (Microsoft)

Type	Description	Storage Bytes	DAP equiv.
bigint	Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).	8 bytes	none
int	Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).	4 bytes
numeric	Functionally equivalent to decimal.
decimal	Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
bit	Integer data with either a 1 or 0 value
smallint	Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767).	2 bytes
tinyint	Integer data from 0 through 255	1 bytes
smallmoney	Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
money	Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
float	Floating precision number data with the following valid values: -1.79E + 308 through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.	4 or 8 bytes
real	Floating precision number data with the following valid values: -3.40E + 38 through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.	4 bytes
date
datetimeoffset
datetime2
smalldatetime	Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
datetime	Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
time
char	Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar	Variable-length non-Unicode data with a maximum of 8,000 characters.
next	Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.
ntext	Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
binary	Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary	Variable-length binary data with a maximum length of 8,000 bytes.
image	Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.
cursor	A reference to a cursor.
timestamp	A database-wide unique number that gets updated every time a row gets updated.
hierarchyid
uniquieidentifier	A globally unique identifier (GUID).
sql_variant
xml
table

PostgreSQL Data Types

Boolean and Binary Types

Data Type	Description	Standardization	Logical DAP data type association.
boolean, bool	A single true or false value.	SQL99	Boolean
bit(n)	An n -length bit string (exactly n binary bits).	SQL92	None
bit varying(n), varbit(n)	A variable n -length bit string (up to n binary bits)	SQL92	None

Character Types

Data Type	Description	Storage	Standardization	Logical DAP data type association.
character (n ), char(n )	A fixed n -length character string.	(4+n) bytes	SQL89	String
character varying(n), varchar(n)	A variable length character string of up to n characters.	Up to (4+n) bytes	SQL92	String
text	A variable length character string, of unlimited length.	Variable	PostgreSQL-specific	String

Numeric Types

Data Type	Description	Storage	Standardization	Logical DAP data type association.
smallint, int2	A signed 2-byte integer	2 bytes	SQL89	Int16
integer, int, int4	A signed, fixed-precision 4-byte number.	4 bytes	SQL92	Int32
bigint, int8	A signed 8-byte integer, up to 18 digits in length.	8 bytes	PostgreSQL-specific	None (Need Int64)
real, float4	A 4-byte floating point number.	4 bytes	SQL89	Float32
double precision, float8, float	An 8-byte floating point number	8 bytes	SQL89	Float64
numeric(p,s), decimal(p,s)	An exact numeric type with arbitrary precision p, and scale s.	Variable	SQL99	None
money	A fixed precision, U.S.-style currency.	4 bytes	PostgreSQL-specific, deprecated.	None
serial	An auto-incrementing 4-byte integer.	4 bytes	PostgreSQL-specific.	None

Date and Time Types

Data Type	Description	Storage	Standardization	Logical DAP data type association.
date	A calendar date (day, month, year).	4 bytes	SQL92	None
time	The time of day.	4 bytes	SQL92	None
time with time zone	The time of day, including time zone information.	4 bytes	SQL92	None
timestamp (includes time zone)	Both date and time.	8 bytes	SQL92	None
interval	An arbitrary specified length of time	12 bytes	SQL92	None

Geometric Types

Data Type	Description	Storage	Standardization	Logical DAP data type association.
box	A rectangular box in a 2D plane.	32 bytes	PostgreSQL-specific	None
line	An infinite line in a 2D plane.	?? bytes	PostgreSQL-specific	None
lineseg	A finite line segment in a 2D plane.	32 bytes	PostgreSQL-specific	None
circle	A circle with center and radius.	24 bytes	PostgreSQL-specific	None
path	Open and closed geometric paths in a two-dimensional plane .	4+32*n bytes	PostgreSQL-specific	None
point	geometric point in a 2D plane	16 bytes	PostgreSQL-specific	None
polygon	A closed geometric path in a 2D plane	4+32*n bytes	PostgreSQL-specific	None

Network Types

Data Type	Description	Standardization	Logical DAP data type association.
cdir	An IP network specification	PostgreSQL-specific	None
inet	A network IP address, with optional subnet bits.	PostgreSQL-specific	None
macaddr	A MAC address (e.g., an Ethernet card's hardware address).	PostgreSQL-specific	None

System Types

Data Type	Description	Standardization	Logical DAP data type association.
oid	An object (row) identifier.	PostgreSQL-specific	None
xid	A transaction identifier	PostgreSQL-specific	None

Template

Types

Data Type	Description	Standardization	Logical DAP data type association.