How are node and node_revision tables related?

Question

I've noticed that the following does not work to unpublish nodes:

db_update('node')
  ->fields(array('status' => 0))
  ->condition('nid', $array_of_nids, 'IN')
  ->execute();

But it does if you follow up with the same update on the node_revision table. (But perhaps I should only be updating the status of the current revision (vid)?)

I found this confusing because the nodes in question do not use revisioning yet it seems that the revision table holds more authoritative data than the main node table (so if so, what's the point of node table?)

Can anyone explain how node and node_revision are related and therefore how to do a bulk update such as this 'properly'? (I'd prefer to avoid a costly loop of node_load, node_save if poss.) I'd like to understand the Drupal Way here.

(I have read node revisions which covers D6, but that did not give me the understanding I was after.)

Answer 1

The node table holds the basic information about the node, the nid is the primary key on this table and contains the vid field which is a foreign key to the node_revision table. The node_revision table holds each revision of the node, the vid is the primary key on this table and contains the nid field which is a foreign key to node table.

You may think that this information is identical, but it's not. The node table only contains one record relating to a node and also stores the current vid whereas the node_revision table contains the revision data. Just because you're not using revisions it doesn't mean that this process isn't being used.

Also keep in mind that all field tables have 2 versions, field_data_[field_name] and field_revision_[field_name], the data tables contain the current revision and the revisions table contain the revision data.

To get an understanding of how practical revisioning works try looking at Revisioning, a more complex and powerful solution can be achieved using Workbench

Answer 2

Node revision handles the edits in the node time to time so if you want to unpublish any node just try unpublishing the node by node_load();

<?php
$node = node_load($nid);
$node->status=0;
node_save($node);
?>

It will update the status in all the tables automatically.

If you don't want to use default methods.

Its the best way to update any node, hoewever if you want to update any node data using query then you need to update it in both the tables(node and node_revision)

Also revision is to keep backup of old data and create a new edited copy. Drupal will pick the data from the latest revision( not the older one) because there can be multiple revision created with each edit. If it is disabled there will be only one revision entry same as the node table

It can be seen in the top right bar in the node edit area when any of its revision is created.

Revisions can be controlled by admin/structure/types/manage/[content type name] against Publishing options tab to make it enabled/disabled by default

And later it node/edit area it has a separate tab to enable/disable it