Add "state" and "last_state" to node_revisions table

@RoboPhred:

From what I've seen, there are plenty of Drupal modules out there that try to do similar things and conflict with one another.

In this case, yes, you could have multiple modules that all try to deal with node state management, but I don't see that as a problem. Any given site would only need to use one of them. More than likely, you'll end up with one module that gains a lot of traction and dominates the field. Other modules will then tend to work with this base module to extend its features, rather than do everything themselves. Once all the issues have been worked out in Contrib and the cream rises to the top, so to speak, we can take aspects of the best implementation and build it into core for D8. That will provide an even more robust and better integrated foundation for Contrib to work with. Many Core features have started out this way.

Drupal Core, as it stands, makes workflow management extremely difficult to implement. My hope is that this small change to Core will give Contrib developers the tools they need to take things to the next level.

The proposed solution says nothing about whether workflows are associated with content types, entire sites, or individual nodes. It also says nothing about how many states can be defined for a given node's revisions, or what any of those states might mean, functionally speaking. All it does is provide the most basic mechanism possible for Drupal to associate states with individual revisions and then it gets out of Contrib's way, letting module developers take care of the rest.

I don't want to get too off topic here, but to address your specific concerns, the idea of multiple overlapping workflows seems highly unlikely to me. In fact it sounds down right dangerous. What is more likely is a workflow which forks and has multiple possible paths for a node to travel down on it's journey from creation through publication and beyond. That, however, is still possible to implement with a single 'state' field in {node_revisions}.

If in the future someone figures out how to field revisions and that is determined to be a better solution, the this implementation can always be updated and expanded. In the mean time I would hate to delay this long awaited capability for yet another Drupal release, when the proposed solution would satisfy the needs of 99% of use cases.

@dbabbage:

Based on the default behaviors I outlined in the task above, a revision's 'state' is set back to zero anytime another revision of the same node is set to the same state. This will likely be a common occurrence in any workflow. For instance, you may go through many draft revisions of a node, but you'll only have one draft at a time. The 'last_state' column is there so you can see all the revisions that used to be drafts, or used to be published, or used to be [insert state designation here].

In the name of clarity, take a look at the following example, reprinted from #282122: D7UX: "Save draft" and "Publish" buttons on node forms. In it we assume there are two defined states state=1 indicates a "Draft" and state=2 indicates the revision is "Published". Users are also given the option to "Unpublish" a node, which basically just sets all revisions to state=0.

First I create a new node and press [Save as Draft]...

{node_revisions}:
nid | vid | title | state | last_state
1   | 1   | Foo   | 1     | 1  

{node}:
nid | vid | title | published
1   | 1   | Foo   | 0  <-- This reflects my draft revision.

Now I edit my node and [Save as Published]...

{node_revisions}:
nid | vid | title | state | last_state
1   | 1   | Foo   | 0     | 1  <-- This looses its state, but the last_state is retained.
1   | 2   | Foo   | 2     | 2  <-- A new revision is added in the published state.

{node}:
nid | vid | title | published
1   | 2   | Foo   | 1  <-- This updates to indicate the node is published.

Now I edit my node and [Save as Draft]...

{node_revisions}:
nid | vid | title | state | last_state
1   | 1   | Foo   | 0     | 1  
1   | 2   | Foo   | 2     | 2  <-- The published revision is unchanged.
1   | 3   | Bar   | 1     | 1  <-- A new draft revision is added.

{node}:
nid | vid | title | published
1   | 2   | Foo   | 1  <-- This still reflects the published revision.

I make more changes and again [Save as Draft]...

{node_revisions}:
nid | vid | title | state | last_state
1   | 1   | Foo   | 0     | 1  
1   | 2   | Foo   | 2     | 2  <-- The published revision is unchanged.
1   | 3   | Bar   | 0     | 1  <-- This looses its state, but the last_state is retained.
1   | 4   | Baz   | 1     | 1  <-- A new draft revision is added.

{node}:
nid | vid | title | published
1   | 2   | Foo   | 1  <-- This still reflects the published revision.

I edit my node and [Save as Published]...

{node_revisions}:
nid | vid | title | state | last_state
1   | 1   | Foo   | 0     | 1  
1   | 2   | Foo   | 0     | 2  <-- This looses its state, but the last_state is retained.
1   | 3   | Bar   | 0     | 1  
1   | 4   | Baz   | 0     | 1  <-- This looses its state, but the last_state is retained.
1   | 5   | Baz   | 2     | 2  <-- A new revision is added in the published state.

{node}:
nid | vid | title | published
1   | 5   | Baz   | 1  <-- This changes to reflect the new published revision.

Now I decide to [Unpublish]...

{node_revisions}:
nid | vid | title | state | last_state
1   | 1   | Foo   | 0     | 1  
1   | 2   | Foo   | 0     | 2  
1   | 3   | Bar   | 0     | 1  
1   | 4   | Baz   | 0     | 1  
1   | 5   | Baz   | 0     | 2  <-- This looses its state, but the last_state is retained.

{node}:
nid | vid | title | published
1   | 5   | Baz   | 0  <-- This still reflects the most recent revision, but the published flag changes.

I make some edits and [Save Chages]...

{node_revisions}:
nid | vid | title | state | last_state
1   | 1   | Foo   | 0     | 1  
1   | 2   | Foo   | 0     | 2  
1   | 3   | Bar   | 0     | 1  
1   | 4   | Baz   | 0     | 1  
1   | 5   | Baz   | 0     | 2  
1   | 6   | Foo   | 0     | 0  <-- A new "stateless" revision is added.

{node}:
nid | vid | title | published
1   | 6   | Foo   | 0  <-- This changes to reflect the most recent revision.

Now I again [Save as Draft]...

{node_revisions}:
nid | vid | title | state | last_state
1   | 1   | Foo   | 0     | 1  
1   | 2   | Foo   | 0     | 2  
1   | 3   | Bar   | 0     | 1  
1   | 4   | Baz   | 0     | 1  
1   | 5   | Baz   | 0     | 2  
1   | 6   | Foo   | 0     | 0  
1   | 7   | Foo   | 1     | 1  <-- A new draft revision is added.

{node}:
nid | vid | title | published
1   | 7   | Foo   | 0  <-- This changes to reflect the current Draft.

Now I go back to vid:2, edit it and [Save as Draft]...

{node_revisions}:
nid | vid | title | state | last_state
1   | 1   | Foo   | 0     | 1  
1   | 2   | Foo   | 0     | 2  
1   | 3   | Bar   | 0     | 1  
1   | 4   | Baz   | 0     | 1  
1   | 5   | Baz   | 0     | 2  
1   | 6   | Foo   | 0     | 0  
1   | 7   | Foo   | 0     | 1  <-- This looses its state, but the last_state is retained.
1   | 8   | Foo   | 1     | 1  <-- A new draft revision is added.

{node}:
nid | vid | title | published
1   | 8   | Foo   | 0  <-- This changes to reflect the current Draft.

See how 'state' always tracks the current state of things, while 'last_state' maintains a complete history of where you've been? So for instance, you could create a view of all previously published revisions of a node. Or you could build a module that took all the drafts created since the last published revision and allowed an editor to merge them, reconciling any differences. These two columns, working in tandem, will open many doors of possibility to contrib developers and site admins.

I want to emphasize though that we are not actually proposing to implement all these features here. The goal for D7 is just to get the fields in the database. The features using these fields can be developed in contrib later.

The 'last_state' field was never intended to track a full state history for every revision. That would be a much bigger project, likely involving separate DB tables. Personally I would question its usefulness, but that is a different conversation. In any case, I see no reason why the inability to track a complete state history should in any way delay the addition of state data to {node_revisions}. In fact, if the 'state' and 'last_state' fields are added, I fully expect that someone could develop a module that did more complete state history tracking through Contrib. Once again though, this is all dependent on this basic foundation being added to Core for developers to hook into.

Though it doesn't necessarily provide a complete state history, I don't want to minimize the importance of the 'last_state' field. It can be an important tool for sorting old revisions and quantifying their relative importance. Take the last database snapshot I show in my example in #9 above. You can see how one might tend to collect many revisions over time, so I might want to perform some automated cleanup now and then. For instance, I could have a script that looks for the most recently published revision (in this case, vid 5) and deletes any earlier revisions that were never published (vid 1, 3 and 4). Or I could have a module that takes all the draft revisions created since my last published revision (vid 7 and 8) and does a diff comparison of the content. Having the 'last_state' field opens up a huge number of possibilities.

What about a node with a single revision that goes through workflow state changes (e.g. some kind of content approval process) without new revisions being created?

The 'state' and 'last_state' fields for a single revision would change through time, just as I outlined above for a node with multiple revisions. That's important for consistency. You won't get quite as much functionality out of a single revision implementation as you would out of a multi-revision implementation, but nor should you expect to. That being said, the 'state' field would still be useful for implementing a basic multi-state workflow. Meanwhile the 'last_state' field would let you know, for instance, whether the unpublished node you're looking at had ever been published, or if it never made it past the draft state.

Anyone willing to give this a shot? I'm not above begging.

I am closing this issue and returning discussion of it to #218755: Support revisions in different states. For one thing, I now believe we only need a single state field in node_revision to accomplish this. I encourage everyone to go to the other issue and help develop a solution.