sql_scripts/First_and_Last_Published.sql at master · srclark/sql_scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
drop temporary table if exists moderated_nodes, first_last_published, current_published_rev, unmoderated_nodes, unmoderated_published_dates;

/* Node types that use workbench moderation */
create temporary table if not exists moderated_nodes (select distinctn.typefrom workbench_moderation_node_history as nh
left join node as n onnh.nid=n.nid
wheren.typeis not null);

/* First and last published date */
create temporary table if not exists first_last_published (selectnh.nid, MIN(nh.stamp) as first, MAX(nh.stamp) as last from workbench_moderation_node_history as nh
where state='published'
andnh.nid>0
group bynh.nid);

/* Current and Published dates */
create temporary table is not exists current_published_rev (select distinctnh.nid, nh.stamp, nh.currentas current_rev, nh.publishedas published_rev from workbench_moderation_node_history as nh
wherenh.current=1ORnh.publishe=1);

/* Unmoderated node types */
create temporary table if not exists unmoderated_nodes (selectnt.typefrom node_type as nt
left join moderated_nodes as mn onmn.type=nt.type
wheremn.type is null);

/* Find first and last published date of unmoderated content */
create temporary table if not exists unmoderated_published_dates (selectnr.nid, min(nr.timestamp) as first, max(nr.timestamp) as last from node_revision as nr
left join node as n onn.nid=nr.nid
left join unmoderated_nodes as un onun.type=n.type
whereun.typeis not null
andnr.status=1
group bynr.nid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
drop temporary table if exists moderated_nodes, first_last_published, current_published_rev, unmoderated_nodes, unmoderated_published_dates;

/* Node types that use workbench moderation */
create temporary table if not exists moderated_nodes (select distinctn.typefrom workbench_moderation_node_history as nh
left join node as n onnh.nid=n.nid
wheren.typeis not null);

/* First and last published date */
create temporary table if not exists first_last_published (selectnh.nid, MIN(nh.stamp) as first, MAX(nh.stamp) as last from workbench_moderation_node_history as nh
where state='published'
andnh.nid>0
group bynh.nid);

/* Current and Published dates */
create temporary table is not exists current_published_rev (select distinctnh.nid, nh.stamp, nh.currentas current_rev, nh.publishedas published_rev from workbench_moderation_node_history as nh
wherenh.current=1ORnh.publishe=1);

/* Unmoderated node types */
create temporary table if not exists unmoderated_nodes (selectnt.typefrom node_type as nt
left join moderated_nodes as mn onmn.type=nt.type
wheremn.type is null);

/* Find first and last published date of unmoderated content */
create temporary table if not exists unmoderated_published_dates (selectnr.nid, min(nr.timestamp) as first, max(nr.timestamp) as last from node_revision as nr
left join node as n onn.nid=nr.nid
left join unmoderated_nodes as un onun.type=n.type
whereun.typeis not null
andnr.status=1
group bynr.nid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
drop temporary table if exists moderated_nodes, first_last_published, current_published_rev, unmoderated_nodes, unmoderated_published_dates;

/* Node types that use workbench moderation */
create temporary table if not exists moderated_nodes (select distinctn.typefrom workbench_moderation_node_history as nh
left join node as n onnh.nid=n.nid
wheren.typeis not null);

/* First and last published date */
create temporary table if not exists first_last_published (selectnh.nid, MIN(nh.stamp) as first, MAX(nh.stamp) as last from workbench_moderation_node_history as nh
where state='published'
andnh.nid>0
group bynh.nid);

/* Current and Published dates */
create temporary table is not exists current_published_rev (select distinctnh.nid, nh.stamp, nh.currentas current_rev, nh.publishedas published_rev from workbench_moderation_node_history as nh
wherenh.current=1ORnh.publishe=1);

/* Unmoderated node types */
create temporary table if not exists unmoderated_nodes (selectnt.typefrom node_type as nt
left join moderated_nodes as mn onmn.type=nt.type
wheremn.type is null);

/* Find first and last published date of unmoderated content */
create temporary table if not exists unmoderated_published_dates (selectnr.nid, min(nr.timestamp) as first, max(nr.timestamp) as last from node_revision as nr
left join node as n onn.nid=nr.nid
left join unmoderated_nodes as un onun.type=n.type
whereun.typeis not null
andnr.status=1
group bynr.nid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
drop temporary table if exists moderated_nodes, first_last_published, current_published_rev, unmoderated_nodes, unmoderated_published_dates;

/* Node types that use workbench moderation */
create temporary table if not exists moderated_nodes (select distinctn.typefrom workbench_moderation_node_history as nh
left join node as n onnh.nid=n.nid
wheren.typeis not null);

/* First and last published date */
create temporary table if not exists first_last_published (selectnh.nid, MIN(nh.stamp) as first, MAX(nh.stamp) as last from workbench_moderation_node_history as nh
where state='published'
andnh.nid>0
group bynh.nid);

/* Current and Published dates */
create temporary table is not exists current_published_rev (select distinctnh.nid, nh.stamp, nh.currentas current_rev, nh.publishedas published_rev from workbench_moderation_node_history as nh
wherenh.current=1ORnh.publishe=1);

/* Unmoderated node types */
create temporary table if not exists unmoderated_nodes (selectnt.typefrom node_type as nt
left join moderated_nodes as mn onmn.type=nt.type
wheremn.type is null);

/* Find first and last published date of unmoderated content */
create temporary table if not exists unmoderated_published_dates (selectnr.nid, min(nr.timestamp) as first, max(nr.timestamp) as last from node_revision as nr
left join node as n onn.nid=nr.nid
left join unmoderated_nodes as un onun.type=n.type
whereun.typeis not null
andnr.status=1
group bynr.nid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
drop temporary table if exists moderated_nodes, first_last_published, current_published_rev, unmoderated_nodes, unmoderated_published_dates;

/* Node types that use workbench moderation */
create temporary table if not exists moderated_nodes (select distinctn.typefrom workbench_moderation_node_history as nh
left join node as n onnh.nid=n.nid
wheren.typeis not null);

/* First and last published date */
create temporary table if not exists first_last_published (selectnh.nid, MIN(nh.stamp) as first, MAX(nh.stamp) as last from workbench_moderation_node_history as nh
where state='published'
andnh.nid>0
group bynh.nid);

/* Current and Published dates */
create temporary table is not exists current_published_rev (select distinctnh.nid, nh.stamp, nh.currentas current_rev, nh.publishedas published_rev from workbench_moderation_node_history as nh
wherenh.current=1ORnh.publishe=1);

/* Unmoderated node types */
create temporary table if not exists unmoderated_nodes (selectnt.typefrom node_type as nt
left join moderated_nodes as mn onmn.type=nt.type
wheremn.type is null);

/* Find first and last published date of unmoderated content */
create temporary table if not exists unmoderated_published_dates (selectnr.nid, min(nr.timestamp) as first, max(nr.timestamp) as last from node_revision as nr
left join node as n onn.nid=nr.nid
left join unmoderated_nodes as un onun.type=n.type
whereun.typeis not null
andnr.status=1
group bynr.nid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
drop temporary table if exists moderated_nodes, first_last_published, current_published_rev, unmoderated_nodes, unmoderated_published_dates;

/* Node types that use workbench moderation */
create temporary table if not exists moderated_nodes (select distinctn.typefrom workbench_moderation_node_history as nh
left join node as n onnh.nid=n.nid
wheren.typeis not null);

/* First and last published date */
create temporary table if not exists first_last_published (selectnh.nid, MIN(nh.stamp) as first, MAX(nh.stamp) as last from workbench_moderation_node_history as nh
where state='published'
andnh.nid>0
group bynh.nid);

/* Current and Published dates */
create temporary table is not exists current_published_rev (select distinctnh.nid, nh.stamp, nh.currentas current_rev, nh.publishedas published_rev from workbench_moderation_node_history as nh
wherenh.current=1ORnh.publishe=1);

/* Unmoderated node types */
create temporary table if not exists unmoderated_nodes (selectnt.typefrom node_type as nt
left join moderated_nodes as mn onmn.type=nt.type
wheremn.type is null);

/* Find first and last published date of unmoderated content */
create temporary table if not exists unmoderated_published_dates (selectnr.nid, min(nr.timestamp) as first, max(nr.timestamp) as last from node_revision as nr
left join node as n onn.nid=nr.nid
left join unmoderated_nodes as un onun.type=n.type
whereun.typeis not null
andnr.status=1
group bynr.nid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
drop temporary table if exists moderated_nodes, first_last_published, current_published_rev, unmoderated_nodes, unmoderated_published_dates;

/* Node types that use workbench moderation */
create temporary table if not exists moderated_nodes (select distinctn.typefrom workbench_moderation_node_history as nh
left join node as n onnh.nid=n.nid
wheren.typeis not null);

/* First and last published date */
create temporary table if not exists first_last_published (selectnh.nid, MIN(nh.stamp) as first, MAX(nh.stamp) as last from workbench_moderation_node_history as nh
where state='published'
andnh.nid>0
group bynh.nid);

/* Current and Published dates */
create temporary table is not exists current_published_rev (select distinctnh.nid, nh.stamp, nh.currentas current_rev, nh.publishedas published_rev from workbench_moderation_node_history as nh
wherenh.current=1ORnh.publishe=1);

/* Unmoderated node types */
create temporary table if not exists unmoderated_nodes (selectnt.typefrom node_type as nt
left join moderated_nodes as mn onmn.type=nt.type
wheremn.type is null);

/* Find first and last published date of unmoderated content */
create temporary table if not exists unmoderated_published_dates (selectnr.nid, min(nr.timestamp) as first, max(nr.timestamp) as last from node_revision as nr
left join node as n onn.nid=nr.nid
left join unmoderated_nodes as un onun.type=n.type
whereun.typeis not null
andnr.status=1
group bynr.nid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
drop temporary table if exists moderated_nodes, first_last_published, current_published_rev, unmoderated_nodes, unmoderated_published_dates;

/* Node types that use workbench moderation */
create temporary table if not exists moderated_nodes (select distinctn.typefrom workbench_moderation_node_history as nh
left join node as n onnh.nid=n.nid
wheren.typeis not null);

/* First and last published date */
create temporary table if not exists first_last_published (selectnh.nid, MIN(nh.stamp) as first, MAX(nh.stamp) as last from workbench_moderation_node_history as nh
where state='published'
andnh.nid>0
group bynh.nid);

/* Current and Published dates */
create temporary table is not exists current_published_rev (select distinctnh.nid, nh.stamp, nh.currentas current_rev, nh.publishedas published_rev from workbench_moderation_node_history as nh
wherenh.current=1ORnh.publishe=1);

/* Unmoderated node types */
create temporary table if not exists unmoderated_nodes (selectnt.typefrom node_type as nt
left join moderated_nodes as mn onmn.type=nt.type
wheremn.type is null);

/* Find first and last published date of unmoderated content */
create temporary table if not exists unmoderated_published_dates (selectnr.nid, min(nr.timestamp) as first, max(nr.timestamp) as last from node_revision as nr
left join node as n onn.nid=nr.nid
left join unmoderated_nodes as un onun.type=n.type
whereun.typeis not null
andnr.status=1
group bynr.nid);