How to properly create a tinyint column with Drupal Schema API

Dev

How to properly create a tinyint column with Drupal Schema API

by Logan Lynn

 

Recently, I’ve been building a custom module to use on a customer’s site.  When I tried to install it I got the following error message:

 

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘NOT NULL DEFAULT 1, PRIMARY KEY (uhid) ) /*!40100 DEFAULT CHARACTER SET UTF8 */’ at line 10 query: CREATE TABLE user_history ( `uhid` INT auto_increment DEFAULT NULL, `nid` INT unsigned NOT NULL DEFAULT 0, `uid` INT unsigned NOT NULL DEFAULT 0, `chapter_status` VARCHAR(32) NOT NULL DEFAULT ”, `title` VARCHAR(32) NOT NULL DEFAULT ”, `type` VARCHAR(32) DEFAULT ”, `start_date` INT DEFAULT NULL, `end_date` INT DEFAULT NULL, `enabled` NOT NULL DEFAULT 1, PRIMARY KEY (mhid) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /home/trunk/project/htdocs/includes/database.inc on line 529.

 

I immediately noticed the last line of the query:  `enabled` NOT NULL DEFAULT 1

 

A datatype had not been assigned to the column I was trying to create. 

 

Taking a look at my code I noticed the following:

 

function member_history_schema(){

$schema[‘user_history’] = array(

‘description’ => t(‘Stores user history data’),

‘fields’ => array(

‘uhid’ => array(

‘type’ => ‘serial’,

‘description’ => t(‘primary key’),

),

‘nid’ => array(

‘type’ => ‘int’,

‘unsigned’ => TRUE,

‘not null’ => TRUE,

‘default’ => 0,

‘description’ => t(“The {node}.nid of the user’s history”),

),

‘uid’ => array(

‘type’ => ‘int’,

‘unsigned’ => TRUE,

‘not null’ => TRUE,

‘default’ => 0,

‘description’ => t(“The {user}.uid of the {user}”),

),

‘status’ => array(

‘type’ => ‘varchar’,

‘length’ => 32,

‘not null’ => TRUE,

‘default’ => ”,

),

‘title’ => array(

‘type’ => ‘varchar’,

‘length’ => 32,

‘not null’ => TRUE,

‘default’ => ”,

),

‘type’ => array(

‘type’ => ‘varchar’,

‘length’ => 32, 

‘not_null’ => TRUE,

‘default’ => ”,

),

‘start_date’ => array(

‘type’ =>’int’,

‘description’ => t(‘The start date the {user} status.’),

),

‘end_date’ => array(

‘type’ =>’int’,

‘description’ => t(‘The end date the {user} status.’),

),

‘enabled’ => array(

‘type’ => ‘tinyint’,

‘not null’ => TRUE,

‘default’ => 1,

),

),

‘primary key’ => array(‘uhid’)

);

return $schema;

}

 

I instantly realized my mistake, I was attempting to create a boolean field and chose to use tinyint as the datatype column. When I read the following drupal page http://drupal.org/node/159605 I misunderstood how it was saying to declare the type (I must not have been paying attention at all, because now it makes sense).  I should have read this page http://api.drupal.org/api/group/schemaapi/6. Specifically, the part where they explain the ‘size’ parameter.

 

So instead of: 

 

‘enabled’ => array(

‘type’ => ‘tinyint’,

‘not null’ => TRUE,

‘default’ => 1,

),

 

I should have done the following:

 

‘enabled’ => array(

‘type’ => ‘int’,

‘size’ => ‘tiny’,

‘not null’ => TRUE,

‘default’ => 1,

),

 

This certainly was a mistake on my part, but sometimes I find the Drupal documentation confusing.

 

Moral of the story: declare boolean fields as int with a size of tiny.

More About the Author

Logan Lynn

Business Intelligence Consultant
Creating hidden fields using Drupal Form API Recently I was trying to create a hidden field on a form I’ve been working with and couldn’t find anything in the Drupal ...
How to properly create a tinyint column with Drupal Schema API   Recently, I’ve been building a custom module to use on a customer’s site.  When I tried to install it I got the following ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072