handle mysql timestamp and auto update in Schema API [#1408736]

In D6 I did it like this:

function server_install() {
  drupal_install_schema('server');
  db_query('ALTER TABLE {server} ADD `timestamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP');
  db_query('ALTER TABLE {server} ADD INDEX ( `timestamp` )');
}

From the D7 documentation it looks like you could do it something like below but I haven't yet been able to figure out the missing bits. Note that I took out a few fields etc. from below so that only what's needed is there for the question.

function server_schema() {
  $schema['server'] = array(
    'description' => '...',
    'fields' => array(
    'user_id' => array(
      '...' => '...',
      '...' => '...',
    ),
    'timestamp' => array(
      'mysql_type' => 'timestamp',
      'description' => 'Timestamp field',
      'not null' => TRUE,
    ),
    ),
    'primary key' => array('user_id', 'type'),
    'indexes' => array(
      'timestamp' => array('timestamp'),
    ),
  );
}

Some help much appreciated!

Comments

Comment #1

Reg CreditAttribution: Reg commented 14 March 2012 at 06:24

Version:

7.x-dev

» 8.x-dev

Just found out that things are being done in Drupal 8.x and then getting back ported now.

Comment #2

joachim CreditAttribution: joachim commented 14 March 2012 at 08:29

I'm not sure that's supported by the schema API: http://drupal.org/node/159605

Comment #3

Reg CreditAttribution: Reg commented 15 March 2012 at 00:41

How would I put a suggestion or request in to make it supported?

Comment #4

joachim CreditAttribution: joachim commented 15 March 2012 at 10:43

Title:	How to handle mysql timestamp and auto update in D7?	» handle mysql timestamp and auto update in Schema API
Component:	install system	» database system
Category:	support	» feature

Like this I guess :)

Comment #5

Reg CreditAttribution: Reg commented 15 March 2012 at 22:11

Okay, I'm laughing now, thanks !

Comment #6

15 March 2012 at 22:11

Version:

8.0.x-dev

» 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Comment #7

15 March 2012 at 22:11

Version:

8.1.x-dev

» 8.2.x-dev

Drupal 8.1.9 was released on September 7 and is the final bugfix release for the Drupal 8.1.x series. Drupal 8.1.x will not receive any further development aside from security fixes. Drupal 8.2.0-rc1 is now available and sites should prepare to upgrade to 8.2.0.

Bug reports should be targeted against the 8.2.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Comment #8

AaronBauman

he/him

CreditAttribution: AaronBauman at Message Agency commented 27 December 2016 at 21:21

Issue summary:	View changes
Status:	Active	» Closed (duplicate)
Parent issue:		» #215821: Impossible to create datetime fields with default value of 'now()' using schema-api

looks like same as #215821: Impossible to create datetime fields with default value of 'now()' using schema-api

Comment #9

Zac_JH CreditAttribution: Zac_JH commented 18 March 2019 at 14:00

Not sure if useful or not, but I solve the problem by using the hook_install: (there is some specific code for dealing with storing original creation date and updating updated dates)

// add timestamps as needed
function jh_gi_install(){
  $s=jh_gi_schema();
  
  foreach($s as $table_name=>$dta){
    foreach($dta['fields'] as $col_name=>$col_dta) if(isset($col_dta['mysql_type']) && $col_dta['mysql_type']=='TIMESTAMP') {
      // add current time to fields
      if($col_name=='updtd'){
        db_query('alter table '.$table_name.' drop column '.$col_name,array());
        db_query('alter table '.$table_name.' add column '.$col_name.' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',array());
      }elseif($col_name=='crtd'){
        db_query('alter table '.$table_name.' drop column '.$col_name,array());
        db_query('alter table '.$table_name.' add column '.$col_name.' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',array());
      }
    }
  }
}

NOTE: Updating the column fails, but removing and adding works fine and keeps the schema infomation correct