Data types
Last modified: February 19, 2008 - 03:55
The following table shows all the legal combinations of the 'type' and 'size' fields of a column specification along with the underlying MySQL and PostgreSQL data types used by each combination.
The maximum size or range of each underlying type is also shown. For integer types, the number of storage bytes is given; the maximum range depends on whether the field is signed or unsigned. For varchar, text, and blob types, the maximum size is given.
| type | size | mysql type & size/range | pgsql type & size/range |
|---|---|---|---|
| serial | tiny | tinyint, 1 B | serial, 4 B |
| serial | small | smallint, 2 B | serial, 4 B |
| serial | medium | mediumint, 3 B | serial, 4 B |
| serial | big | bigint, 8 B | bigserial, 8 B |
| serial | normal | int, 4 B | serial, 4 B |
| int | tiny | tinyint, 1 B | smallint, 2 B |
| int | small | smallint, 2 B | smallint, 2 B |
| int | medium | mediumint, 3 B | int, 4 B |
| int | big | bigint, 8 B | bigint, 8 B |
| int | normal | int, 4 B | int, 4 B |
| float | tiny | float, 4 B | real, 6 digits |
| float | small | float, 4 B | real, 6 digits |
| float | medium | float, 4 B | real, 6 digits |
| float | big | double, 8 B | double precision, 15 digits |
| float | normal | float, 4 B | real, 6 digits |
| numeric | normal | numeric, 65 digits | numeric, 1000 digits |
| varchar | normal | varchar, 255 B or 64 KB (1) | varchar, 1 GB |
| char | normal | char, 255 B | varchar, 1 GB |
| text | tiny | tinytext, 256 B | text, unlimited |
| text | small | tinytext, 256 B | text, unlimited |
| text | medium | mediumtext, 16 MB | text, unlimited |
| text | big | longtext, 4 GB | text, unlimited |
| text | normal | text, 16 KB | text, unlimited |
| blob | big | longblob, 4 GB | bytea, 4 GB |
| blob | normal | blob, 16 KB | bytea, 4 GB |
| datetime | normal | datetime, years 1001 to 9999 | timestamp, years 4713 BC to 5874897 AD |
(1) MySQL 5.0.3 or later (see http://dev.mysql.com/doc/refman/5.0/en/char.html).
For numeric fields, you must
For numeric fields, you must specify both precision and scale to generate the field.
For example, to create the equivalent to the MySql statement
`price` DECIMAL(10, 2) NOT NULL DEFAULT 0,use the following
'price' => array('type' => 'numeric', 'size' => 'normal', 'not null' => TRUE, 'default' => 0, 'precision' => 10, 'scale' => 2)creating SET type
is there a way of creating a SET type?
my db structure has line
gender SET('F','M', '?') NOT NULL,Try a tinyint field with some constants
Define a couple constants and use a tiny int field
<?phpdefine('GENDER_UNKNOWN', 0);
define('GENDER_MALE', 1);
define('GENDER_FEMALE', 2);
?>
And use these in all database calls.
Drupal can not handle NULLs, but setting the default to zero simulates the same thing.
Question: Auto Increment
I am learning how to make modules using the manual and It lead me to this Schema page
I am receiving this error:
user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: CREATE TABLE mytable ( `id` INT unsigned NOT NULL auto_increment ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /database.inc on line 515.How do I indicate that it is also a primary key?
I tried the following below but it did not work :( (note: I did not put the whole schema array)
'id'=> array( 'description' => t('id'),'type' => 'serial'
,'unsigned' => TRUE
,'not null' => TRUE
)//array
'id'=> array( 'description' => t('id'),'type' => 'serial'
,'unsigned' => TRUE
,'not null' => TRUE
,'primary key' => TRUE
)//array
'id'=> array( 'description' => t('id'),'type' => 'int'
,'auto_increment' => TRUE
,'unsigned' => TRUE
,'not null' => TRUE
,'' => 'primary key'
)//array
Read the reference page
Add the 'primary array' cell
<?php$schema['table1']
= array(
'description' => t('The base table for nodes.')
,'fields'
=> array(
'id'
=> array( 'description' => t('id')
,'type' => 'serial'
,'unsigned' => TRUE
,'not null' => TRUE
)//array
)//array
,'primary key' => array('id')
);//array
?>
geometry
Support for geometry data types and spatial indexes?
Example: MySQL
coord geometry NOT NULL,
SPATIAL INDEX(coord),
Enum and Set fields
I suppose the ENUM and SET field types are not included in the API because some of the databases supported by Drupal in the backend do not support those field types?
I work exclusively with MySQL, as do most of the PHP developers I know - it would be nice to still have a way to use the new API but be able to use those field types. My easy solution for now was to just make the queries myself without using the API, but that's not a permanent solution.
Any thoughts on the matter?
Custom types
Per Barry, at http://groups.drupal.org/node/7558#comment-26055, to add a custom type not already defined by schema API, like geospacial fields, you define your schema like the following, providing the right 'real' field type for alternative databases:
<?php$schemea['table']['fields']['foo'] = array(
'type' => 'varchar',
'mysql_type' => 'MAGIC_GEO_TYPE',
'pgsql_type' => 'whatever',
);
?>