Normalize and extend schema [#1006766]

We need to normalize and extend the DB schema.
We need a flag for each action if the action was actually done, because we need to keep a history of schedule dates.
A normalized schema would allow us to support more actions (beyond publishing and unpublishing).

----

Problems: Currently I have no idea how a normalized table would be joined with the node table for views support:
- nid: int
- action: varchar
- time: int
- done: boolean

If you have a node scheduled for publishing and for unpublishing (or whatever actions we might want to support):

42;"publish";123456;false
42;"unpublish";234567;false

How do you join it with the node table to have both values available in views?

Comments

Comment #1

imrook CreditAttribution: imrook commented 11 January 2011 at 17:46

Well, you could do something like this:

select p.nid, p.time as publish, u.time as unpublish from scheduler p left join scheduler u on p.nid=u.nid where p.action = 'publish' and u.action = 'unpublish';

But this is obviously brittle. I'm not quite sure what use case you're trying to satisfy here. If you explain more what you're trying to accomplish, I can give better feedback.

Comment #2

Eric-Alexander Schaefer CreditAttribution: Eric-Alexander Schaefer commented 11 January 2011 at 19:38

I don't really know actually.

I want the scheduler table normalized so I can add new actions without adding columns. And I also want the timestamps and the state of the actions (the "done"-field) be visible for views.
As I understand it I need to provide the data to views as additional fields to the node data. Right now the scheduler table looks like this:

- nid: int
- publish_on: int -- UTC timestamp
- unpublish_on: int -- UTC timestamp

The table gets joined with the node table via this definition in the _views_data() hook:

  $tables['scheduler']['table']['join']['node'] = array(
    'left_field' => 'nid',
    'field' => 'nid',
  );

The result set gets two new columns (publish_on and unpublish_on) which are now visible to views. In this case there is at most one record per node in the scheduler table.
I guess the actual query looks something like this (simplified): SELECT * FROM node INNER JOIN scheduler ON (node.nid=scheduler.nid)
If the scheduler table gets normalized there could be an arbitrary number of rows per node. How do I make them visible to views?

Playing a bit got me this:

SELECT node.nid AS nid, 
       node.title AS title,
       s1.time AS publish_time,
       s1.done AS publish_done,
       s2.time AS unpublish_time,
       s2.done AS unpublish_done 
       s3.time AS promote_time,
       s3.done AS promote_done 
       s4.time AS demote_time,
       s4.done AS demote_done 
FROM node 
LEFT OUTER JOIN scheduler s1 ON (node.nid=s1.nid AND s1.action='publish')
LEFT OUTER JOIN scheduler s2 ON (node.nid=s2.nid AND s2.action='unpublish')
LEFT OUTER JOIN scheduler s3 ON (node.nid=s3.nid AND s3.action='promote')
LEFT OUTER JOIN scheduler s4 ON (node.nid=s4.nid AND s4.action='demote')

This results in - nid;title;publish_time;publish_done;unpublish_time;unpublish_done;promote_time;promote_done;demote_time;demote_done. Exactly what we need.

If we add a new action we just add another join. Is that a good solution or is that somewhat ugly on query performance?
I wonder if this kind of query can be defined for views. Otherwise we could try to manipulate the query with a custom field handler.

Comment #3

imrook CreditAttribution: imrook commented 14 January 2011 at 20:59

I think this is not a good way to go about views integration as you're going to be causing a bunch of headaches for yourself. Views has problems with multiple node relationships even in version 2. For example, create a view that shows taxonomy terms and tag a node with multiple terms. You'll find that the node shows up once for each term. This is exactly what you would get with a normalized scheduler table -- one entry for each scheduled action. You should look at adding additional field plugins or display styles to do fancier stuff like aggregate publish/unpublish into a single row. Or write your own UI for schedule administration and leave views to do the simple day-to-day tasks. That's just my 2 cents.

Comment #4

rickmanelius CreditAttribution: rickmanelius commented 16 April 2013 at 02:41

Hi Eric. Would this still be considered major? Is this still something you wish to pursue?

Comment #5

jonathan1055 CreditAttribution: jonathan1055 commented 10 September 2013 at 12:09

Instead of hard-coding publish_on and unpublish_on we could have a column for the actions to be taken against the node at the specified time. This could use the Drupal actions api (which we are already using in _scheduler_publish and _scheduler_unpublish). Scheduler would supply the core actions, but we would also implement a hook function allowing other modules to supply their actions. These would be picked up and displayed in the content type admin form alongside the core actions. Each could be separately enabled per content type as now. The enabled actions would then be presented to the user in the node edit form. See #2083277: integration with hidden nodes for a real example where this would be used.

Scheduler would then have the power to expand the actions which could be performed without having to continually duplicate code.

I'm sure we can solve the problem of efficiently making one table/query for integration with views. But I agree we do need to know how to do that, as it is a vital part of schedulers functionality.

Jonathan

Comment #6

k_zoltan CreditAttribution: k_zoltan commented 28 November 2014 at 13:17

Issue summary:

View changes

Here is a good module that has already all the actions available for D7
https://www.drupal.org/project/scheduled_actions

Screencast video
https://www.youtube.com/watch?v=LeqU4Qyh3l4

Comment #7

jonathan1055 CreditAttribution: jonathan1055 commented 7 January 2015 at 18:07

Version:	master	» 8.x-1.x-dev
Related issues:		+#1982932: Drupal 8 Port of the Scheduler Module

Setting the version to 8.x so we can discuss this as part of the D8 conversion which is starting now.

Comment #8

jonathan1055 CreditAttribution: jonathan1055 as a volunteer commented 26 May 2016 at 13:41

Assigned:	Eric-Alexander Schaefer	» Unassigned
Priority:	Major	» Normal

Setting this back from 'Major' to 'Normal'. Yes it will have a major impact but it is not our highest priority right now.

Currently we just want to get the main features of Scheduler 7.x ported to 8.x and get a stable release. We have a 1.0-alpha1 release and here is the #2662476: Progress towards 8.x release of Scheduler