Wikipedia Diskussion:Archiv/Datenbankabfragen/IP-Diskussionen

Abfrage

Zunächst erzeugen wir eine Liste derjenigen Benutzerdiskussionsseiten, die keine zugehörige Benutzerseite haben. (Wählt man statt der Namensräume 2 und 3 die Namensräume 0 und 1, erhält man alle Artikeldiskussionen ohne Artikel, ich glaube, irgendwo eine Abfrage zu diesem Thema schon gesehen zu haben.)

 --alle Benutzerseiten
 drop table if exists ns2;
 create table ns2(
  ns2_id int(8) unsigned not null,
  ns2_title varchar(255) binary not null,
  primary key (ns2_id),
  key title (ns2_title)
 );
 insert into ns2
 select cur_id, cur_title
 from cur
 where cur_namespace = 2;
 
 --alle Benutzerdiskussionsseiten
 drop table if exists ns3;
 create table ns3(
  ns3_id int(8) unsigned not null,
  ns3_title varchar(255) binary not null,
  primary key (ns3_id),
  key title (ns3_title)
 );
 insert into ns3
 select cur_id, cur_title
 from cur
 where cur_namespace = 3;
 
 --alle Benutzerdiskussionsseiten ohne zugehörige Benutzerseite,
 --eine direkte Abfrage wäre auch möglich, so wie hier ist sie aber deutlich schneller
 drop table if exists diskussionswaise;
 create table diskussionswaise(
  w_id int(8) unsigned not null,
  primary key(w_id)
 );
 insert into diskussionswaise
 select ns3_id
 from ns3 left join ns2 on (ns3_title = ns2_title)
 where ns2_title is null;

Nun kommt die eigentliche Abfrage: Bestimme alle IP-Diskussionsseiten. Hierfür wird die obige Abfrage noch nicht benötigt.

--alle IP-Diskussionsseiten
 drop table if exists ipdiskussion;
 create table ipdiskussion(
  cur_id int(8) unsigned not null,
  cur_title varchar(255) binary not null,
  cur_date varchar(8) binary not null,
  primary key (cur_id)
 );
 insert into ipdiskussion
 select cur_id, cur_title, substring(cur_timestamp,1,8)
 from cur
 where cur_namespace = 3
 and cur_title regexp '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$';

Zuletzt lassen wir uns endlich die Liste ausgeben, mit der Zusatzinformation über das Vorhandensein der Benutzerseiten.

select concat(
  '*[[Benutzer Diskussion:',
  cur_title,
  '|]], ',
  cur_date,
  if (w_id IS NOT NULL,
   '',
   concat(' - [[Benutzer:',cur_title,'|(B)]]')
  )
 )
 from ipdiskussion
 left join diskussionswaise on (cur_id = w_id)
 order by cur_date asc, cur_title asc
 limit 100;

Wenn man die Information über die Benutzerseiten weglässt, kommt man gänzlich mit folgender Abfrage aus:

select concat(
  '*[[Benutzer Diskussion:',
  cur_title,
  '|]], ',
  substring(cur_timestamp,1,8)
 )
 from cur
 where cur_namespace = 3
 and cur_title regexp '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$';
 order by cur_timestamp asc, cur_title asc
 limit 100;

Kommentare

Hallo Filzstift, hier hast du die gewünschte Liste, mit der erzeugenden Abfrage. Mir fallen folgende Veränderungsmöglichkeiten ein: Mehr als 100 Einträge, Angabe der Textgröße... Was hältst du davon (gewünscht, unnötig)? --SirJective 23:14, 29. Okt 2004 (CEST)

Ich habe mir jetzt die Mühe gemacht und die DB lokal installiert, so dass ich dich nicht mehr um Updates betteln muss und einfach die SQL-Abfragen gleich selbst machen kann. --Filzstift ○ 07:41, 15. Dez 2004 (CET)