DB_Table Data Types

DB_Table Data Types – Abstract data types used in DB_Table column definitions

Description

These are the supported data types in DB_Table:

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.

Code generation for an existing database (Previous) MDB (Next)
Last updated: Wed, 23 Jul 2014 — Download Documentation
Do you think that something on this page is wrong? Please file a bug report or add a note.
View this page in:

User Notes:

There are no user contributed notes for this page.