Correct way to add custom column to user table?

Question

I'm looking to add a column called 'updated' directly to the users table.

The Field API and Joins are not an option for me in this case due to external constraints.

How can I safely modify the user_schema to add the 'updated' column in a custom module and safely clean it up when the module is removed?

Here is the layout of the 'updated' column based on the Schema API:

  $field = array(
      'type' => 'int',
      'not null' => TRUE,
      'default' => 0,
      'description' => 'Timestamp for last time user was updated.',
  );

Currently my attempts using hook_install are not working as I believe they should.

Answer 1

It seems that I was missing hook_schema_alter(). This fixed my issues with drupal_write_record() and views. Thank you @Clive for your comment here.

Overview of what you need to do:

• In hook_install() you need to call db_add_field() to add a field.
• In hook_schema_alter() you modify the schema so that other modules can interact with your changes.
• In hook_uninstall() you need to call db_drop_field() to clean up/remove your added field.

Actual Code:

Here is how I accomplished this. The code below belongs in your module.install file.

function hook_install() {
  $field = array(
      'type' => 'int',
      'not null' => TRUE,
      'default' => 0,
      'description' => 'Timestamp for last time user was updated.',
  );
  $index = array(
      'indexes' => array(
          'updated' => array('updated')
      ),
  );
  db_add_field('users', 'updated', $field, $index);
}
function hook_schema_alter(&$schema) {
  $schema['users']['fields']['updated'] = array(
      'type' => 'int',
      'not null' => TRUE,
      'default' => 0,
      'description' => 'Timestamp for last time user was updated.',
  );
}

function hook_uninstall() {
  db_drop_field('users', 'updated');
}