Site:Developer stuff/Analysis of edits

# use these commands to clean up temporary tables created in this code.
drop table test.commentary_edits;
drop table test.del1
drop table test.all_scripture_edits
drop table test.scripture_edits_page_summary
drop table test.scripture_edits_all_page_summary
drop table test.del2
drop table test.scripture_edits_set_100_summary

#get a list from old of all the edits.
Create table test.commentary_edits
select a.order_id, b.old_namespace as namespace, b.old_title as title, b.old_id, b.old_user_text as user_text, b.old_timestamp as timestamp, b.old_minor_edit as minor_edit
from test.commentary_pages_ordered a, feastupontheword.old b
where a.cur_title=b.old_title and b.old_namespace in (0,1)


#kick out original content on commentary pages.
#first find the first entry for commentary pages
#we don't do the same for talk pages since they are originally blank.
create table test.del1
select a.order_id, a.namespace, a.title, min(case when namespace=0 then old_id else 0 end) as min_old_id
from test.commentary_edits a
group by a.order_id, a.namespace, a.title


#make table without original commentary content
create table test.all_scripture_edits
select a.*, NULL as cur_id
from test.commentary_edits a, test.del1 b
where a.old_id<>b.min_old_id and a.namespace=b.namespace and a.title=b.title

#add cur content for commentary pages.
insert into test.all_scripture_edits
select a.order_id, b.cur_namespace as namespace, b.cur_title as title, NULL as old_id, b.cur_user_text as user_text, b.cur_timestamp as timestamp, b.cur_minor_edit as minor_edit, b.cur_id
from test.del1 a, feastupontheword.cur b
where a.namespace=0 and b.cur_namespace=0 and a.title=b.cur_title

#add cur content for talk pages
insert into test.all_scripture_edits
select a.order_id, b.cur_namespace as namespace, b.cur_title as title, NULL as old_id, b.cur_user_text as user_text, b.cur_timestamp as timestamp, b.cur_minor_edit as minor_edit, b.cur_id
from test.commentary_pages_ordered a, feastupontheword.cur b
where b.cur_namespace=1 and a.cur_title=b.cur_title

create table test.scripture_edits_page_summary
select order_id, 
	title,
	count(*) as total_edits, 
	sum(case when namespace=0 then 1 else 0 end) as commentary_edits, 
	sum(case when namespace=1 then 1 else 0 end) as talk_edits,
	sum(case when minor_edit=1 then 1 else 0 end) as minor_edits
from test.all_scripture_edits
group by order_id, title

#get all the zero pages for a complete list.
create table test.scripture_edits_all_page_summary
select b.order_id, coalesce(c.total_edits,0) as total_edits,
coalesce(c.commentary_edits,0) as commentary_edits,
coalesce(c.talk_edits,0) as talk_edits,
coalesce(c.minor_edits,0) as minor_edits
from feastupontheword.cur a inner join test.commentary_pages_ordered b
	on a.cur_id=b.cur_id
left outer join
	test.scripture_edits_page_summary c
on b.order_id=c.order_id

#further summarize by 100 page increments
#	* The last set of 100 will actually only have 95 in it.
create table test.del2
select floor(order_id/100) as set_100, min(order_id) as min_order_id,
count(*) as pages, sum(total_edits) as total_edits,
sum(commentary_edits) as commentary_edits, sum(talk_edits) as talk_edits, 
sum(minor_edits) as minor_edits
from test.scripture_edits_all_page_summary
group by floor(order_id/100)

#get count
Select count(*) from test.del2
#84

#add title of first page for each set of 100
create table test.scripture_edits_set_100_summary
select a.*, b.cur_title as start_title
from test.del2 a, test.commentary_pages_ordered b
where a.min_order_id=b.order_id

EXPORT

# use these commands to clean up temporary tables created in this code.
drop table test.commentary_edits;
drop table test.del1
drop table test.all_scripture_edits
drop table test.scripture_edits_page_summary
drop table test.scripture_edits_all_page_summary
drop table test.del2
drop table test.scripture_edits_set_100_summary