Site:Developer stuff/Pages with content

This sql gets all of the pages with content in Main and Main_talk.

#make a list of commentary pages with something other than the default text on them.

create table test.commentary_content as 
select  b.order_id, a.cur_id, a.cur_namespace, a.cur_title 
from feastupontheword.cur a, test.commentary_pages_ordered b
where a.cur_id=b.cur_id and a.cur_text not REGEXP '== Questions ==\n....Click the edit link above and to the right to add questions..\n\n\n== Lexical notes ==\n....Click the edit link above and to the right to add lexical notes..\n\n\n== Exegesis ==\n..Click the edit link above and to the right to add exegesis..\n\n\n== Related links ==\n....Click the edit link above and to the right to add related links..\n\n\n-';

#make a list of talk pages that are non-blank which correspond to commentary pages

create table test.talk_content as 
select b.order_id, a.cur_id, a.cur_namespace, a.cur_title 
from feastupontheword.cur a, test.commentary_pages_ordered b
where a.cur_namespace=1 and a.cur_title=b.cur_title;

#do a full outer join manually
create table test.t1 as
select a.order_id, a.cur_title as Commentary_Title, b.cur_title as Talk_Title
from test.commentary_content a left outer join test.talk_content b
on a.order_id=b.order_id
where b.order_id is null;

create table test.t2 as
select a.order_id, a.cur_title as Commentary_Title, b.cur_title as Talk_Title
from test.commentary_content a inner join test.talk_content b
on a.order_id=b.order_id;


create table test.t3 as
select b.order_id, a.cur_title as Commentary_Title, b.cur_title as Talk_Title
from test.commentary_content a right outer join test.talk_content b
on a.order_id=b.order_id
where a.order_id is null;

insert into test.t3 select * from test.t1;
insert into test.t3 select * from test.t2;


Export t3 to excel.

# cleanup temp tables.
drop table test.t1;
drop table test.t2;
drop table test.t3;
drop table test.commentary_content;
drop table test.talk_content;

Note: I exported using "CSV for Excel" and got nulls in column 1 when the page was empty but blanks for column 2 when the page was empty.

Then in Excel--

1) cell c2 contains a line return (Alt-Enter in Excel 2000) (In Excel 2003, I don't know of any easy way to solve the problem. I did this: In C2 write:

=crlf()

Also add a vba module to the excel file and put the following function in it:

Public Function crlf() As String
    crlf = vbCrLf
End Function

2) data starts in A6

3) Sort by order_id.

4) Formula for cell D6:

=IF(B6="NULL","|              || ","| [["&B6&"]] || ")

5) Formula for cell E6:

=IF(ISBLANK(C6),"           "&$C$2&"|-"&$C$2," [[Talk:"&C6&"]] "&$C$2&"|-"&$C$2)

6) Copy and paste D6 & E6 down the column.

Then in notepad--

1) Copy and paste the results of columns D & E from Excel into notepad.

Not sure why but when copying and pasting from excel, a set of quotes is entered around everything coming from the second column.

2) Once you have pasted into notepad search and replace the quote with nothing.

3) Then search and replace the underscore character with a space.

4) Also you will need to put

{|

before 5) and

|}

after the text has generated in order to complete the table.

Finally go to Site:Pages with content and update it.