GT::SQL::Driver::Types - Column types supported by GT::SQL
my $c = $DB->creator('new_table'); $c->cols({ column_name => { type => 'INT', default => 42, not_null => 1, unsigned => 1 } # ... more columns ... });
my $e = $DB->editor('table_name'); $e->add_col(column_name2 => { type => 'CHAR', size => 10, default => 'abc' });
This module should not be used directly, however the documentation here describes the different types support by GT::SQL and any caveats associated with those types.
All types are specified as a column_name => { column definition }
pair,
where the column definition should contain at least a type
key containing
one of the TYPES outlined below. Commonly accepted attributes are:
CHAR
/VARCHAR
columns, strings containing only spaces), are considered
NULL values are are not permitted if the column is specified as not_null
.
The value passed to not_null should be true.
default
is required.
Also see the TEXT
section regarding caveats and limitations of
using default
's for TEXT
types.
Other column attributes are supported as outlined below. In addition to attributes mentioned in this document, various attributes are available that influence automatically-generated forms displayed by GT::SQL::Admin - see the GT::SQL::Creator manpage for details on these attributes.
TINYINT
type specifies an 8-bit integer able to handle values from -128
to 127. Some databases will allow larger values due to not supporting an
appropriate data type. The unsigned
column attribute may turn this into
an unsigned value supporting values from 0 to 255; due to this type being
implemented as a larger integer type in some databases (which, incidentally,
coincide with the databases not supporting an unsigned 8-bit TINYINT
) using
an unsigned
TINYINT type will result in a column able to store any value
from 0-255, unlike most of the larger integer types below.
SMALLINT
type specifies a 16-bit integer able to handle values from
-32768 to 32767. The unsigned
column attribute may turn this into an
unsigned value supporting values from 0 to 65535, however this is not
guaranteed. If you need to store values in the 32768-65535 range, a larger
type is recommended.
MEDIUMINT
type (only natively supported by MySQL) specifies a 24-bit
integer type able to hold values from -8388608 to 8388607. If the unsigned
column attribute is specified, this allows values from 0 to 16777215. Due to
this being supported with the unsigned
attribute, or implemented as a larger
data type, an unsigned
MEDIUMINT
will always supported values up to
16777215.
INT
type specifies a 32-bit integer able to hold values from -2147483648
to 2147483647. If the unsigned
column attribute is specified, the column
may support values from 0 to 4294967295, however this is not guaranteed.
If values larger than 2147483647 are needed, using the BIGINT
type below is
recommended. INTEGER
is an alias for INT
.
BIGINT
specifies a 64-bit integer value able to
hold values from -9223372036854775808 to 9223372036854775807. If specified as
unsigned
, the column may support values from 0 to 18446744073709551616,
but this is not guaranteed. If larger values are needed, use the DECIMAL
type with a scale
value of 0
.
REAL
type specifies a 32-bit floating-point (i.e. fractional) number,
accurate to 23 binary digits (which works out to approximately 6 decimal
digits). The values may be signed, and can range from at least as small as
10^-37 to at least as large as 10^37. For more precise values, the DOUBLE
type is recommended. For exact precision (i.e. for monetary values), the
(often slower) DECIMAL
type is recommended. FLOAT
is an alias for
REAL
.
DOUBLE
type specifies a 64-bit floating-point (i.e. fractional) number,
accurate to 52 binary digits (approximately 15 decimal digits). The values
may be signed, and can range from at least as small as 10^-307 to at least as
large as 10^308 (except under Oracle - see below). For exact precision (i.e.
for monetary values), the (often slower) DECIMAL
type is recommended.
Take note that Oracle doesn't properly support the full range supported by
other databases' DOUBLE
types - the smallest number supported (assuming
precision to digits) is 10^-113 - specifically, the number of digits after the
decimal place may not exceed 128 - so 1.2345678901e-117 is acceptable, while
1.23456789012e-117 is not. The larger number Oracle supports is just less than
1e+126 (i.e. 9.999...e+125), as opposed to other databases' 1e+307. If you
need to store numbers larger or smaller than this amount, you'll have to find
some other way to store your numbers (i.e. Math::BigFloat with a VARCHAR
).
DECIMAL
type is provided to support numbers of arbitrary precision. It
requires two attributes, scale
and precision
, where scale
specifies
the number of decimal places, and precision specifies the number of overall
digits. For example, 123.45
has a precision
of 5, and a scale
of 2.
42
has a precision
or 2, and a scale
of 0. scale
must be less than
precision
, and precision
must not exceed 38. Also, although the value
stored and retrieved is completely accurate within it's given precision and
scale range, the accuracy available for comparisons (i.e. column = number) is
only reliably accurate to approximately the same level as DOUBLE's - that is,
about 15 digits.
CHAR
type is used to specify a string of characters from 1 to 255
characters long. It takes a size
attribute which must be 255 or less, and
specifies the size of the column values - if not specified, 255 will be used.
This implementation's CHAR
type, for historic reasons, will not pad
inserted values with spaces, but may trim trailing spaces when retrieving
and/or comparing values. Note that this is not SQL compliant CHAR
behaviour - SQL-compliant CHAR
's are padded with spaces up to their size.
What this ends up meaning is that for everything except MySQL, CHAR
columns
will be mapped to VARCHAR
columns. Note that even MySQL, which is the only
database for which CHAR
's are not automatically mapped into VARCHAR
's,
will transparently convert CHAR
columns to VARCHAR
columns if any
non-fixed-size datatype (anything other than a CHAR
or numeric types) is
used in or added to the table. As a general rule, VARCHAR
is preferred over
CHAR
except when dealing with columns whose values don't vary significantly
in length and are in a table that only contains fixed-size data types
(CHAR
's and numeric types). Everywhere else, use VARCHAR
's, since that's
what you'll be getting anyway.
A binary
attribute is supported, which may indicates that comparisons
with this field should be case-sensitive. Note that this only works on
databases that actually have a case-sensitive CHAR
field - currently, only
MySQL.
VARCHAR
type is identical to the above CHAR
type except as
follows. Unlike a CHAR
, a VARCHAR
column does not take up size
bytes
of storage space - typically the storage space is only slightly larger
(typically 1 byte) than the size of the value stored. As such, VARCHAR
's
are almost always preferred over columns, except for nearly-constant sized
data, or tables with all fixed-width data types (CHAR
's, INT
's, and
non-DECIMAL
numeric types). VARCHAR
columns will not be padded with
whitespace up to size
, however trailing whitespace may
be trimmed from
values.
As with CHAR
, the binary
attribute may make the VARCHAR
values
case-sensitive for the matching purposes.
TEXT
type is similar to VARCHAR
types, except that they are always
case-insensitive for matching/equality, and can contain longer values. The
TEXT
type takes a size
attribute which contains the length required - if
not provided, a value of approximately 2 billion is used. Note that the
maximum size of the column will usually be larger than the value you specify to
size
- it simply indicates to the driver to use a field capable of at least
the size specified. The values of TEXT
fields are case-insensitive in terms
of matches and equality. The maximum size
value, and the default, is
approximately 2 billion.
Certain aliases are provided with implicit size defaults - TINYTEXT
,
SMALLTEXT
, MEDIUMTEXT
, and LONGTEXT
, which are equivelant to TEXT
with size
values of 255, 65535, 16777215, and 2147483647, respectively.
Depending on the size
value, certain databases _may_ use different
underlying types. MySQL, for example, uses the smallest possible type between
its native TINYTEXT
, TEXT
, MEDIUMTEXT
, and LONGTEXT
types. As
such, it is recommended that you use a sufficiently large size
value unless
absolutely sure that you will never need a larger value.
Also note that TEXT
types do not support normal equality operations - in
fact, the only portable things that can be done with TEXT
columns is IS
NULL
tests (in GT::SQL this means ``='' undef
) and LIKE
comparisons - but,
for portability with all supported databases, the argument of a LIKE
may not
exceed 4000 characters.
Also note that the default
value will be ignored by MySQL, which does not
support having default values on TEXT
columns. Everything else, however,
will properly support this, and the default will still be used when inserting
with GT::SQL even when using MySQL. Also note that the default value of
TEXT
types must not exceed 3998 characters, due to limits imposed by some
databases. Longer indexes may work in some cases, but are not guaranteed - for
example, a table resync on MSSQL will not work.
ENUM
type is a MySQL-only type that supports certain fixed string
values. On non-MySQL databases, it is simply mapped to a VARCHAR
column.
It requires a values
option which should have a value of an array reference
of string values that the ENUM should permit. The ENUM
type is generally
discouraged in favour of a CHAR
, VARCHAR
, or an
integral type column, all of which provide more flexibility
(i.e. if you want to add a new possible value) and are not a single
database-specific type.
All of the date/time types support by MySQL will be handled by GT::SQL, for
compatibility reasons. However, all types other than DATE and DATETIME
should be considered deprecated as cross-database compatibility is not possible
using these types. In particular, TIMESTAMP
will work exactly like a
DATETIME
on every non-MySQL database; TIME
and DATE
will work in
Postgres just like they do in MySQL; under everything else, TIME
won't work
at all, and DATE
will work like DATETIME
.
GT::SQL users are urged to at least consider using an INT column, designed to
contain Perl's time()
value, in lieu of any of the Date/time types as it avoids
many problems typically associated with storing local times - such as time zone
issues and non-local databases. That said, if you are certain you want a
Date/time type, a DATETIME is preferred as it will work (almost) the same
everywhere.
YYYY-MM-DD HH:MM:SS
format (where
'HH'
is a 24-hour hour). Inserted values may omit the seconds
(YYYY-MM-DD HH:MM
), or time (YYYY-MM-DD
) portions of the value. Omitted
values will default to 0
.
Note that DATETIME
values returned from a database may include
fractional-second precision values such as 2004-01-01 12:00:07.123
.
Currently MSSQL and Postgres exhibit this behaviour. MSSQL's DATETIME
type
always includes exactly three decimal digits, while Postgres' TIMESTAMP
type,
used for GT::SQL DATETIME
's, stores times with 6 decimal-digit precision.
Unlike MSSQL, however, Postgres will only display decimal digits if a
significant decimal value has been stored in the database. This happens with
the time_check
option, below, and when an explicit fractional second value
has been inserted into the database.
A time_check
attribute may be passed with a true value; if set, any update
to the row that doesn't explicitly set the column will have the column updated
to the database's current local time. Due to issues with times and/or
timezones, this option should be considered deprecated and discouraged - it is
recommended instead that you update the value yourself using a value that
your script thinks is local time (or, better yet, use an INT
column with
unix time values (i.e. time()
in Perl), which are timezone-independent to begin
with), rather than trying to depend on a database having the same time and time
zone as your script.
DATETIME
, except (under MySQL and Postgres) it only stores and
returns the YYYY-MM-DD
portion of the value. Note that when using this
type, care must be taken to extract only the desired portion of the output as
databases other than MySQL and Postgres map this to a DATETIME
above, which
returns 'YYYY-MM-DD HH:MM:SS' values (with a possible fractional seconds value,
in the case of MSSQL/Postgres). Using a DATETIME
or INT
field is
generally preferred, but this type may be slightly more effecient and take
slightly less space (4 bytes instead of 8 bytes) on MySQL and Postgres
databases.
Like DATETIME
, this handles a time_check
field, with the same caveats
described in the the DATETIME
time_check
description.
The alternate, deprecated date/time types supported are listed in the Deprecated types section below.
BLOB
support (TINYBLOB
, BLOB
, MEDIUMBLOB
, and LONGBLOB
)
existed in older versions of GT::SQL, however the support, where it existed at
all, was partial and incomplete. Additionally, only certain drivers (MySQL and
Oracle) supported BLOB
types at all. As such, the limited BLOB
support
present in old GT::SQL versions is still supported under MySQL and Oracle, but
any new development should avoid them. If you really need to store binary
data, it is strongly recommended that you use files, and simply store
filenames in the database.
DATETIME
(MySQL 4.1+) or an
extremely MySQL-specific YYYYMMDDhhmmss
format. Another MySQL-specific of
this data type is that the first - and ONLY the first - TIMESTAMP
column in
a row will be automatically updated to the current local timezone-dependent
date and time. Use a DATETIME
(possibly with the time_check
option)
instead.
HH:MM:SS
format. Deprecated due to non-portability and incompatibility on
other databases. If you really want to store just the time of day, either use
an INT
to store the minutes or seconds since midnight, or use a CHAR
which you update with the HH:MM:SS
value. Causes a fatal error on databases
which don't have an appropriate native type.
SMALLINT
instead. Causes a fatal error on
anything other than MySQL.
Jason Rhinelander
Copyright (c) 2004 Gossamer Threads Inc. All Rights Reserved. http://www.gossamer-threads.com/
Revision: $Id: Types.pm,v 1.2 2004/09/07 20:56:59 jagerman Exp $