DB_Table Data Types
Description
These are the supported data types in DB_Table:
- Integers of 2-, 4-, and 8-bytes ('smallint', 'integer', and 'bigint')
- Fixed-point decimals ('decimal')
- Floating-point decimals of single or double precision ('float' and 'double')
- Booleans for true/false values ('boolean')
- Character strings of fixed or variable length ('char' and 'varchar')
- Dates in ISO "yyyy-mm-dd" format ('date')
- Times in ISO "hh:ii:ss" format ('time')
- Timestamps in ISO "yyyy-mm-dd hh:ii:ss" format ('timestamp')
- Character large objects (CLOB) for text or ascii data ('clob')
DB_Table abstracts data types for you, so your data is always stored the same way, regardless of the database backend. In some cases, particularly with date, time, and timestamp, the native database format is ignored completely and data is stored as a fixed-length character string.
DB_Table does no support binary large objects (BLOBs), but character large objects (CLOBS) are available.
Integers
There are three sizes of integer columns:
- 'smallint' can store values from (-2^15) to +(2^15); that is, from -32778 to 32767
- 'integer' can store values from (-2^31) to +(2^31); that is, from -2,147,483,648 to +2,147,483,647
- 'bigint' can store values from (-2^63) to +(2^63); that is, from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807
The $col definition, you need only specify the column type; no size or scope is needed.
Integer column declaration
<?php
var $col = array(
// unique row ID
'fieldname' => array(
'type' => 'integer'
)
);
?>
Fixed-Point Decimal Numbers
To define a fixed-point column in DB_Table, use the 'decimal' datatype, and indicate both the 'size' (width) and 'scope' (number of decimal places). For example, to define a 5-digit number that has 2 decimal places:
Decimal column declaration
<?php
var $col = array(
// unique row ID
'fieldname' => array(
'type' => 'decimal',
'size' => 5,
'scope' => 2
),
);
?>
For the above example, standard SQL requires that the column be able to store any value with 5 digits and 2 decimals. In this case, therefore, the range of values that can be stored in the column is from -999.99 to 999.99. DB_Table attempts to enforce this behavior regardless of the RDBMS backend behavior.
Floating-Point Numbers
To define a floating-point column in DB_Table, use the 'single' or 'double' datatype.
- 'single' is a single-precision floating point number
- 'double' is a double-precision floating point number
You need only specify the column type; no size or scope is needed.
Floating-point column declaration
<?php
var $col = array(
// unique row ID
'fieldname' => array(
'type' => 'double'
),
);
?>
Boolean
A boolean value is a true/false (1 or 0) value. You need only specify the column type; no size or scope is needed.
Boolean column declaration
<?php
var $col = array(
// unique row ID
'fieldname' => array(
'type' => 'boolean'
)
);
?>
Boolean values are stored as fixed-point decimals of size 1, scope 0.
If the column is not required, a NULL value stored therein may be treated as a third value, which allows the boolean column to be treated as a ternary value instead of a binary value (i.e., NULL|0|1 instead of 0|1).
Strings
To define a fixed-length character string column, use the 'char' datatype, and indicate the exact size of the string.
To define a variable-length character string column, use the 'varchar' datatype, and indicate the maximum size of the string.
For example, to define a 64-character variable-length string:
Variable length string column declaration
<?php
var $col = array(
// unique row ID
'fieldname' => array(
'type' => 'varchar',
'size' => 64
)
);
?>
You must specify a 'size' element, but no scope is needed. The maximum size is 255 characters.
Date
To define an ISO-standard date column, use the 'date' datatype.
Date column declaration
<?php
var $col = array(
// unique row ID
'fieldname' => array(
'type' => 'date'
)
);
?>
You need only specify the column type; no size or scope is needed.
Values for 'date' are always stored as 10-character strings, in the format "yyyy-mm-dd".
Time
To define an ISO standard time with hour, minutes, and seconds, use the 'time' data type.
Time column declaration
<?php
var $col = array(
// unique row ID
'fieldname' => array(
'type' => 'time'
)
);
?>
You need only specify the column type; no size or scope is needed.
A 'time' value is always stored as an 8-character string, in the format "hh:ii:ss".
Timestamp
To define an ISO standard date-and-time column, use the 'timestamp' data type.
Timestamp column declaration
<?php
var $col = array(
// unique row ID
'fieldname' => array(
'type' => 'timestamp'
)
);
?>
You need only specify the column type; no size or scope is needed.
Values for 'timestamp' are always stored as an 19-character strings, in the format "yyyy-mm-dd hh:ii:ss" (24-hour clock).
Note: If you want to store a Unix timestamp, use the 'integer' datatype; Unix timestampes are 4-byte integers, which maps perfectly to the DB_Table 'integer' datatype.
Character Large Object (CLOB)
CLOB Column Declaration
<?php
var $col = array(
// unique row ID
'fieldname' => array(
'type' => 'clob'
)
);
?>
You need only specify the column type; no size or scope is needed.
Values for 'clob' are always stored as the large possible native text type (e.g., LONGTEXT) or native CLOB type.