Fix to Drupal to allow NOW() and CURRENT_TIMESTAMP in new tables

Work | Jan. 8, 2009

This is why I hate CMS software — their incessant, misguided need to over-abstract every little detail. Take for example Drupal’s system for including additional tables into your existing MySQL database. Drupal would have you create a needlessly complex multidimensional array of all the elements in your new data table schema, created with a separate function and called by another custom Drupal function. Because I guess including the actual SQL to create a table was just too fucking simple.

So here’s Drupal’s way of doing it. I hope you like nested parens!


function timestamp_schema() {
$schema['timestamp'] = array(
'description' => t("Timestamps an ID"),
'fields' => array(
'id' => array(
'type' => 'int',
'length' => 11,
'not null' => true,
),
'uid' => array(
'timestamp' => array(
'type' => 'timestamp',
'not null' => true,
'default' => 'CURRENT_TIMESTAMP',
'on update' => 'NOW()',
),
),
'primary key' => array('id'),
'indexes' => array(
'id' => array('id'),
),
);

return $schema;
}

It’s a data table that stores an ID and a timestamp, which is practically useless, but here’s my point: maybe you caught that I wanted to automatically set the ‘timestamp’ value to CURRENT_TIMESTAMP or even NOW()? But when you try that, Drupal doesn’t set your timestamp to NOW() the function, it wraps it in single quotes, setting your timestamp to the string ‘NOW()’, which isn’t particularly helpful.

Well, Drupal users have already seen this problem. Oh, good. It’s on track to be fixed when Drupal 7 is released. Whenever that is. Another thing I hate about CMSs.

So my solution is this. Already there’s another stupid function you need, just to call the timestamp_scheme() method above. Drupal says make it look like this:

function timestamp_install() {
// Create tables
drupal_install_schema('timestamp');
}

I say make it look like this:

function timestamp_install() {
// Create tables
db_query("CREATE TABLE {timestamp} (
`id` INT(11) NOT NULL,
`timestamp` NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW(),
PRIMARY KEY (id),
INDEX id (id)
)");
}

Oh look, it’s a MySQL query! One you can just plug into a MySQL session with hardly any modification! And you didn’t need to spend an afternoon turning it into some bullshit array. Plus it still looks like MySQL, which you would have to know anyway to produce that mess of nested arrays above. It even supports constants like CURRENT_TIMESTAMP and functions like NOW(). Because it’s still fucking MySQL and not a dumb array.

The uninstall function is the same thing:

function timestamp_uninstall() {
// Drop tables
db_query("DROP TABLE {timestamp}");
}

Now wasn’t that easier?

Comments

18 Responses to “Fix to Drupal to allow NOW() and CURRENT_TIMESTAMP in new tables”

  1. Edwin Horneij says:

    Your problem is that you’re using Drupal. You really should switch to Joomla!

  2. Tim says:

    Say, you make a good point! Is there a way I could purchase hours of tech support so that I can be told in person the things that aren’t possible with this CMS?

  3. david says:

    it’s not drupal it’s just that every cms out their expects you to have a million people userbase. Check out normalizing a database and it will make sense why you just have id and timestamp. Every time the site is accessed by the id which is probably referenced by user_id you can get the time they last visited. It kind of seems the future of php is to abstract it to death and then pat yourself on the back for learning someone else’s abstraction method.

  4. adamo says:

    The purpose of a database abstraction layer is so the same code can be used on top of different DBMS without modification. If you allow pure MySQL code, there’s a good chance you’re going to end up writing something that doesn’t work on PostreSQL, MSSQL, etc. The Schema API arrays are really nothing hard once you get used to them, and they have their benefits. For example, one of the features of the Schema module is that it will compare module’s schema definitions with the actual database, and report any discrepancies. The timestamp thing seems like more of a MySQL issue if you read this explanation: (http://drupal.org/node/215821). Anyway all you need to do is just use PHP’s date() function and insert that value into your datetime column.

  5. Tim says:

    Thanks adamo, I see your point that abstracting does help when writing code that will work on MySQL and PostGRE, etc, and this nested array method is probably the simplest way to do it.

    I would have more faith in Drupal’s abstraction methods if they supported all the functionality you’d expect out of the original SQL language. If, as you suggest, a programmer should instead manually insert a timestamp rather than let the language take care of it automatically, I feel it’s just pushed the problem from the CMS to the developer, rather than actually solved it. Here’s to hoping it’s solved in Drupal 7!

  6. betz says:

    Drupal 7 is in codefreeze now and the issue has been moved to drupal 8.

  7. Tim says:

    FFFFFFFFF….

  8. twig says:

    lol @ tim’s reply (oct 27th)

    that was my reaction when I found the “Impossible to create datetime fields with default value of ‘now()’ using schema-api” post also.

    i bet odds are it’ll get pushed back to drupal 9 too

  9. Floris says:

    Thanks for this workaround! This is my implementation of it:

    // Install the table with a simple datime field.
    drupal_install_schema(‘voting_poll_votes’);

    // Now change the table’s and set the field’s default value to CURRENT_TIMESTAMP.
    db_query(“ALTER TABLE {voting_poll_votes}
    `vote_time` `vote_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP”);

  10. Floris says:

    Thanks for this workaround! This is my implementation of it:

    // Install the table with a simple datime field.
    drupal_install_schema(‘voting_poll_votes’);

    // Now change the table’s and set the field’s default value to CURRENT_TIMESTAMP.
    db_query(“ALTER TABLE {voting_poll_votes} CHANGE
    `vote_time` `vote_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP”);

  11. John says:

    Ok, I find some abstraction annoying, too, but you assume (or did) that everybody is using MySQL. Also, don’t you use CMS because it is a system of abstraction? Maybe you just don’t like to code.

  12. Tim says:

    I like abstraction when it makes my life easier, not harder, and in this case Drupal is making it harder for me by implementing a method that doesn’t even support all the functionality of the system it’s trying to simplify. It’s like programming with oven mitts on.

    And it’s irrelevent whether other people use MySQL or not; I’m saying that in this case, doing things the Drupal way is actually worse than doing them directly in MySQL. Case in point: Drupal’s schema also doesn’t support the ‘date’ or ‘time’ format. It’s ‘datetime’ only. Except I don’t want datetime. But with Drupal, datetime is all you get.

    It’s a half-baked layer of abstraction that makes programming more difficult by not only limiting available functionality, but by also introducing its own idiosyncrasies. Neither of those I see as an improvement.

  13. Jay Dolan says:

    Tim,

    If you look at the D7 source (schema.inc), there is a workaround for this. If you KNOW that your schema will only be deployed to MySQL, you can use:

    ‘mysql_type’ => ‘timestamp’

    Instead of:

    ‘type’ => ‘datetime’

    When declaring your columns. Hope this helps (and btw, I agree with your points).

    Jay

  14. Logan L says:

    There is some documentation on the mysql_type on the drupal site, however it could be improved. It just barely mentions it and doesn’t provide very good examples of how you can use it.

    http://drupal.org/node/146939

    Although I think I do prefer the solution provided by Tim above.

  15. Sean says:

    You have a few decent points but your arrogance and presumption as a programmer will bite you in the butt. We’re in the process of hiring right now, and can only imagine the various ways we would destroy a CV if we found the prospective employee had a blog post as narrow-minded as this.

  16. Tim says:

    Thanks for the comment! Speaking of arrogance, you presume I’d work for someone that judgmental and short-sighted. Besides, it’s only the internet. Chillax.

  17. Micheas says:

    An alternative is to allow the default value to be 0 an then run a post install hook to run the raw sql to set the proper default timestamp.

Trackbacks

Check out what others are saying about this post...

  1. […] minds to grasp them easily, and it is changing fast. While I could certainly spend some time griping and moaning about the many petty annoyances Drupal has caused me to date, its effectiveness and the enthusiasm […]