|
|
| (78 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| == Introduction ==
| |
| === Previous Design/Implementation ===
| |
|
| |
|
| == Data model representation ==
| |
|
| |
| === Atomic (Simple) Types ===
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
| ==Table Test 1 ==
| |
|
| |
|
| |
| {| border="1" cellspacing="0"
| |
| |+ [http://msdn.microsoft.com/en-us/library/ms187752.aspx 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 ====
| |
| {| border="1" cellspacing="0"
| |
| ! 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 ====
| |
| {| border="1" cellspacing="0"
| |
| ! 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 ====
| |
| {| border="1" cellspacing="0"
| |
| ! 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 ====
| |
| {| border="1" cellspacing="0"
| |
| ! 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 ====
| |
| {| border="1" cellspacing="0"
| |
| ! 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 ====
| |
| {| border="1" cellspacing="0"
| |
| ! 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 ====
| |
| {| border="1" cellspacing="0"
| |
| ! 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 ====
| |
| {| border="1" cellspacing="0"
| |
| ! Data Type !! Description !! Standardization || Logical DAP data type association.
| |
| |-
| |
| |
| |
| |-
| |
| |
| |
| |-
| |
| |
| |
| |}
| |
|
| |
| == Desired Features ==
| |
|
| |
| == Implementation Target ==
| |
