ויקיפדיה:מאמרים שנערכו מעט/יצירת הרשימה

רשימת הפרמטרים ליצירת הרשימה הולכת ומתארכת, וביחד איתה גם מורכבות השאילתה, וההכנות הנדרשות כדי להפעיל אותה. לכן החלטתי לרכז כאן את המידע הנחוץ לטובת יצירת הרשימה - בעיקר כדי שיהיה לי יותר קל למצא את הדברים בפעם הבאה שהרשימה תדרוש עדכון - אבל גם לטובת אחרים שירצו ליצור את הרשימה בעצמם.

• הורדת בסיס הנתונים
• פרטים אודות מבנה בסיס הנתונים

הטבלאות הדרושות הן -

• page
• templatelinks
• categorylinks
• revisions

מכיוון שטבלת revisions לא זמינה בפורמט sql נצטרך לחלץ אותה מתוך ה-xml באמצעות Xml2sql.

עכשיו נסיר את כל העריכות שבוצעו על ידי בוטים (רשימת משתמשים עם דגל בוט). באמצעות פקודה כזו -

 delete from revision where rev_user_text in ( .....);

(אנקדוטה - נכון ל5/6/2007 בוצעו בויקיפדיה 407,006 עריכות על ידי משתמשים בעלי דגל בוט!)

ניצור טבלה נוספת שתרכז את מספר העריכות שבוצעו בכל ערך -

   create table num_of_edits( p_id int not null, PRIMARY KEY(p_id), num int);
 insert into num_of_edits(p_id,num) select rev_page, count(*) from revision group by rev_page;

ניצור טבלה נוספת שתרכז את מספר השחזורים שבוצעו בכל ערך -

   create table rollbacks( p_id int not null, PRIMARY KEY(p_id), num int);
insert into rollbacks(p_id,num) select rev_page, count(*) from revision where (rev_comment REGEXP "^שחזור") or (rev_comment REGEXP "^שוחזר") or (rev_comment REGEXP "^ביטול גרס")  group by rev_page;5

נוסיף עמודה חדשה לטבלת page ונכניס אליה את מספר הערכיכות פחות מספר השחזורים בכל ערך.

ALTER TABLE page ADD num_of_edits INT;
update page, num_of_edits  set num_of_edits=num where page_id=p_id;
 update page, rollbacks  set num_of_edits=(num_of_edits-(num*2)) where page_id=p_id;

לצורך ריכוז מספר העורכים נמחק את כל הגרסאות שבהן בוצע שחזור

delete from revision where (rev_comment REGEXP "^שחזור") or (rev_comment REGEXP "^שוחזר") or (rev_comment REGEXP "^ביטול גרס");

ניצור טבלה חדשה שתרכז את מספר העורכים השונים של כל ערך (אנונימיים שונים נספרים כעורך יחיד) -

   create table num_of_editors( p_id int not null, PRIMARY KEY(p_id), num int);
 insert into num_of_editors(p_id,num) select rev_page, count(DISTINCT rev_user) from revision group by rev_page;

נוסיף עמודה חדשה לטבלת page ונכניס אליה את מספר העורכים עבור כל ערך-

ALTER TABLE page ADD num_of_editors INT;
update page, num_of_editors  set num_of_editors=num where page_id=p_id;

בטבלה נוספת נרכז את זמן העריכה הראשונה והאחרונה של כל ערך, ובנוסף את המשתמש הראשון שערך אותו -

create table edits(p_id int not null, PRIMARY KEY(p_id), first VARCHAR(16), last VARCHAR(16), first_by int);
insert into edits(p_id,first,last) select rev_page, min(rev_timestamp), max(rev_timestamp) from revision group by rev_page;
update revision, edits set first_by=rev_user where rev_page=p_id and rev_timestamp=first;

נוסיף את המידע לתוך טבלת page -

ALTER TABLE page ADD page_last_edit INT;
ALTER TABLE page ADD page_first_by INT;
update page, edits set page_last_edit=last, page_first_by=first_by where page_id=p_id;

ואחרי שבילינו יום וחצי בהמרות והכנות למיניהן הגיע הזמן לשאילתה האמיתית (והאימתנית) -

select page_title from page where
page_namespace=0 and
num_of_edits<7 and
num_of_editors<4 and
page_is_redirect=0 and
page_id not in 
(select DISTINCT tl_from from templatelinks where 
tl_title="לשכתוב" or
tl_title="עריכה" or
tl_title="נ" or
tl_title="פירושונים" or
tl_title="בבדיקה" or
tl_title="פשט" or
tl_title="איחוד" or
tl_title="מחלוקת"
) and
page_id not in (select DISTINCT cl_from from categorylinks where cl_to="ימות השנה")
and not (page_title<2008 and page_title>0) and 
page_last_edit<"20070505000000" and
page_first_by<>4116
order by page_title;

• לזכור לשנות ידנית כל פעם שמעדכנים בסיס נתונים!'
• הוסרו רק ערכי שנים בין 0 ל2007 - אם היו לכולן קטגוריה או תבנית קבועה זה היה קל יותר...
• כדי לקבל את רשימת הערכים של דרור יש להחליף "שונה מ" בשווה בשורה לפני האחרונה.