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?
I'm no expert on the API, but
I'm no expert on the API, but I suspect that if you're writing a module for your own personal use you could manually alter the table definition AFTER calling drupal_install_schema (either through your MYSQL interface of choice or using db_query("ALTER TABLE..."). Then as long as your inputs match the allowed ENUM values (and the datatype as defined by the schema function, which would still be checked for drupal_write_record calls and such). You could even make it more publically-accessible by using a $GLOBALS['db_type'] block (IIRC) and only switching it if it's a 'mysql' database.
Alternatively, if you want to keep your database size down and don't mind occupying some PHP memory space, have you considered a tinyint field with some constants?
--Andy
Developing Drupal websites for Livelink New Media
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',
);
?>