Use AUTO_INCREMENT and similar instead of sequences

The following queries remove all auto_increments and add in UNSIGNED attributes where necessary:

ALTER TABLE access CHANGE aid aid TINYINT(10) UNSIGNED NOT NULL;
ALTER TABLE accesslog CHANGE aid aid INT(10) UNSIGNED NOT NULL;
ALTER TABLE aggregator_category CHANGE cid cid INT(10) UNSIGNED NOT NULL;

ALTER TABLE aggregator_category_feed CHANGE fid fid INT(10) UNSIGNED NOT NULL, 
CHANGE cid cid INT(10) UNSIGNED NOT NULL;

ALTER TABLE aggregator_category_item CHANGE iid iid INT(10) UNSIGNED NOT NULL, 
CHANGE cid cid INT(10) UNSIGNED NOT NULL;

ALTER TABLE aggregator_feed CHANGE fid fid INT(10) UNSIGNED NOT NULL;

ALTER TABLE aggregator_item CHANGE iid iid INT(10) UNSIGNED NOT NULL, 
CHANGE fid fid INT(10) UNSIGNED NOT NULL;

ALTER TABLE authmap CHANGE aid aid INT(10) UNSIGNED NOT NULL, 
CHANGE uid uid INT(10) UNSIGNED NOT NULL;

ALTER TABLE boxes CHANGE bid bid TINYINT(4) UNSIGNED NOT NULL;

ALTER TABLE comments CHANGE cid cid INT(10) NOT NULL, 
CHANGE pid pid INT(10) UNSIGNED NOT NULL, 
CHANGE nid nid INT(10) UNSIGNED NOT NULL, 
CHANGE uid uid INT(10) UNSIGNED NOT NULL;

ALTER TABLE filter_formats CHANGE format format INT(4) NOT NULL;

ALTER TABLE history CHANGE uid uid INT(10) UNSIGNED NOT NULL, 
CHANGE nid nid INT(10) UNSIGNED NOT NULL;

ALTER TABLE locales_source CHANGE lid lid INT(11) UNSIGNED NOT NULL;
ALTER TABLE locales_target CHANGE lid lid INT(11) UNSIGNED NOT NULL;
ALTER TABLE moderation_filters CHANGE fid fid INT(10) UNSIGNED NOT NULL;
ALTER TABLE moderation_votes CHANGE mid mid INT(10) UNSIGNED NOT NULL;

ALTER TABLE node CHANGE nid nid INT(10) UNSIGNED NOT NULL, 
CHANGE uid uid INT(10) UNSIGNED NOT NULL;

ALTER TABLE node_comment_statistics CHANGE nid nid INT(10) UNSIGNED NOT NULL;
ALTER TABLE node_counter CHANGE nid nid INT(11) UNSIGNED NOT NULL;
ALTER TABLE node_revisions CHANGE uid uid INT(10) UNSIGNED NOT NULL;
ALTER TABLE poll_choices CHANGE chid chid INT(10) UNSIGNED NOT NULL;
ALTER TABLE profile_fields CHANGE fid fid INT(10) UNSIGNED NOT NULL;
ALTER TABLE role CHANGE rid rid INT(10) UNSIGNED NOT NULL;
ALTER TABLE term_data CHANGE tid tid INT(10) UNSIGNED NOT NULL;
ALTER TABLE url_alias CHANGE pid pid INT(10) UNSIGNED NOT NULL;
ALTER TABLE vocabulary CHANGE vid vid INT(10) UNSIGNED NOT NULL;

ALTER TABLE watchdog CHANGE wid wid INT(5) UNSIGNED NOT NULL, 
CHANGE uid uid INT(10) UNSIGNED NOT NULL;

Updating the DB leads to the first of possibly many AUTO_INCREMENT related errors:

Fatal error: Duplicate entry '0' for key 1
query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', 'Duplicate entry \'0\' for key 1\nquery: INSERT INTO locales_source (location, source) VALUES (\'/~karthik/projects/drupal_cvs/\', \'OPML feed\') in /data2/www/drupal_cvs/includes/database.mysql.inc on line 108.', 2, '', '/~karthik/projects/drupal_cvs/', '', '192.168.0.1', 1132722094) in /data2/www/drupal_cvs/includes/database.mysql.inc on line 108

Typical example from locale.inc of code that relies on the AUTO_INCREMENT attribute:

        db_query("INSERT INTO {locales_source} (location, source) VALUES ('%s', '%s')", $comments, $english);
        $loc = db_fetch_object(db_query("SELECT lid FROM {locales_source} WHERE source = '%s'", $english));
        $lid = $loc->lid;
        db_query("INSERT INTO {locales_target} (lid, locale, translation) VALUES (%d, '%s', '%s')", $lid, $lang, $translation);

Cheers
Karthik.

Just added two patches to the locale and watchdog modules [as above]..

Thanks
Karthik.

I agree it saves a query, but I don't like these on/off levels of compliance.. IMO Drupal should either be compliant or not. A black and white rule like this will ensure that all the queries are more portable, and ease some of the work for other maintainers - pgSQL etc.

Most contribs abuse this quite regularly. Also, this locale module patch is an example in core where the ID is retrieved with a second query. Such inconsistencies will stop with a hard-and-fast rule.

I would really just like to see some consistency :)
Karthik.

I don't think it's a very good sign when senior developers on an open source project start calling contributors and/or people who voice slightly negative opinions.. "Idiots"..

-K

Since we are at loggerheads over the use of AUTO_INCREMENT, the attachment fixes all the UNSIGNED inconsistencies, while preserving the existing AUTO_INCREMENTs. I'll prepare a patch for updates.inc if needed.

And don't get me wrong, I have no problems with using AUTO_INCREMENTs. However, if it is fine to use them, then there is absolutely *no* apparent reason for using the sequences table. The whole point as far as I can tell of using the sequences table is to completely *avoid* the use of AUTO_INCREMENT. The current situation is neither here nor there.

If raw speed is the preference, then AUTO_INCREMENTs should be used everywhere. This saves you atleast three queries every time [The {sequences} LOCK, REPLACE and UNLOCK..].

This just doesn't make sense to me..
Regards,
Karthik.

I've added the UNSIGNED patch as a separate issue. Modified topic accordingly.

-K