Site:Developer stuff/Analysis of edits

From Feast upon the Word (http://feastupontheword.org). Copyright, Feast upon the Word.
< Site:Developer stuff
Revision as of 17:03, 26 November 2005 by Matthewfaulconer (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
  1. 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_talk_edits drop table test.cur_scriptures_talk 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

  1. Get the initial page content for each page
  2. * I don't want to count the original commentary page content as an edit

create table test.del1 select a.cur_id, min(old_id) as min_old_id from test.commentary_edits a group by a.cur_id

  1. Get a list of page edits out of old on commentary pages.
  2. * 65257 and 73651 is the range of commentary pages for the scriptures.
  3. * some pages (e.g. BOM title page, Article of Faith pages, Official declaration pages)
  4. are excluded. I'm not bothering with them for now.
  5. * old doesn't contain the most recent version. It only contains the history.

Create table test.commentary_edits select a.cur_id, a.cur_namespace, a.cur_title, b.old_id, b.old_user_text, b.old_timestamp,

old_minor_edit from feastupontheword.cur a, feastupontheword.old b where a.cur_namespace=b.old_namespace and a.cur_title=b.old_title and a.cur_id between 65257 and 73651

  1. get count

Select count(*) from test.commentary_edits

  1. 2503
  1. Get the initial page content for each page
  2. * I don't want to count the original commentary page content as an edit

create table test.del1 select a.cur_id, min(old_id) as min_old_id from test.commentary_edits a group by a.cur_id

  1. get count

Select count(*) from test.del1

  1. 797
  1. Get the commentary page edits from old excluding the original content

create table test.all_scripture_edits select a.* from test.commentary_edits a, test.del1 b where a.old_id<>b.min_old_id and a.cur_id=b.cur_id

  1. get count

Select count(*) from test.all_scripture_edits

  1. 1706
  1. add cur version of pages into format used in old table for a complete list of changes to
  2. the commentary pages
  3. * Watchout for overlapping id's.
  4. I'm using cur_id as substitute for old_id.
  5. Currently no problem due to different range of values

insert into test.all_scripture_edits select b.cur_id, b.cur_namespace, b.cur_title, b.cur_id as old_id, b.cur_user_text as old_user_text, b.cur_timestamp as old_timestamp, b.cur_minor_edit as old_minor_edit from test.del1 a, feastupontheword.cur b where a.cur_id=b.cur_id

  1. get new count

Select count(*) from test.all_scripture_edits

  1. 2503
  1. get a list of talk pages that exist for commentary pages.

create table test.cur_scriptures_talk select a.cur_id as commentary_cur_id, b.* from feastupontheword.cur a, feastupontheword.cur b where a.cur_id between 65257 and 73651 and b.cur_namespace=1 and a.cur_title=b.cur_title

  1. get count

Select count(*) from test.cur_scriptures_talk

  1. 217
  1. get a list of changes on those talk pages from old

create table test.scripture_talk_edits select a.commentary_cur_id, a.cur_id, a.cur_namespace, a.cur_title, b.old_id, b.old_user_text,

b.old_timestamp, old_minor_edit from test.cur_scriptures_talk a, feastupontheword.old b where a.cur_namespace=b.old_namespace and a.cur_title=b.old_title

  1. get count

Select count(*) from test.scripture_talk_edits

  1. 407
  1. add cur versions to that list

insert into test.scripture_talk_edits select a.commentary_cur_id, a.cur_id, a.cur_namespace, a.cur_title, a.cur_id as old_id,

a.cur_user_text as old_user_text, a.cur_timestamp as old_timestamp, a.cur_minor_edit as old_minor_edit from test.cur_scriptures_talk a

  1. inserted 217
  1. get new count

Select count(*) from test.scripture_talk_edits

  1. 624
  1. add talk edits to commentary edits to get one complete list
  2. * switching using the commentary_cur_id as the cur_id rather than the talk pages' cur id
  3. to allow all the edits for a single scripture (commentary or talk) to show up together.
  4. * dropping talk cur_id. Ugly, but it isn't really needed now anyway.

insert into test.all_scripture_edits select a.commentary_cur_id as cur_id, a.cur_namespace, a.cur_title, a.old_id, a.old_user_text, a.old_timestamp, a.old_minor_edit from test.scripture_talk_edits a

  1. get new count

Select count(*) from test.all_scripture_edits

  1. 3127
  1. create a summary page that shows, by page, how many edits (of different types) there have been

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

  1. get count

Select count(*) from test.scripture_edits_page_summary

  1. 821
  1. get all the zero pages for a complete list.

create table test.scripture_edits_all_page_summary select a.cur_id, coalesce(b.total_edits,0) as total_edits, coalesce(b.commentary_edits,0) as commentary_edits, coalesce(b.talk_edits,0) as talk_edits, coalesce(b.minor_edits,0) as minor_edits from feastupontheword.cur a left outer join test.scripture_edits_page_summary b on a.cur_id=b.cur_id where a.cur_id between 65257 and 73651

  1. get count

Select count(*) from test.scripture_edits_all_page_summary

  1. 8395
  1. further summarize by 100 page increments
  2. * The last set of 100 will actually only have 95 in it.

create table test.del2 select floor((cur_id-65257)/100) as set_100, min(cur_id) as min_cur_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((cur_id-65257)/100)

  1. get count

Select count(*) from test.del2

  1. 84
  1. 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, feastupontheword.cur b where a.min_cur_id=b.cur_id

  1. get count

Select count(*) from test.scripture_edits_set_100_summary

  1. 84
  1. 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_talk_edits drop table test.cur_scriptures_talk 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

Retrieved from "http://feastupontheword.org/index.php?title=Site:Developer_stuff/Analysis_of_edits&oldid=3830"