maintenance/archives/patch-pagelinks.sql

   1 --
   2 -- Create the new pagelinks table to merge links and brokenlinks data,
   3 -- and populate it.
   4 --
   5 -- Unlike the old links and brokenlinks, these records will not need to be
   6 -- altered when target pages are created, deleted, or renamed. This should
   7 -- reduce the amount of severe database frustration that happens when widely-
   8 -- linked pages are altered.
   9 --
  10 -- Fixups for brokenlinks to pages in namespaces need to be run after this;
  11 -- this is done by updaters.inc if run through the regular update scripts.
  12 --
  13 -- 2005-05-26
  14 --
  15
  16 --
  17 -- Track page-to-page hyperlinks within the wiki.
  18 --
  19 CREATE TABLE /*$wgDBprefix*/pagelinks (
  20   -- Key to the page_id of the page containing the link.
  21   pl_from int unsigned NOT NULL default '0',
  22
  23   -- Key to page_namespace/page_title of the target page.
  24   -- The target page may or may not exist, and due to renames
  25   -- and deletions may refer to different page records as time
  26   -- goes by.
  27   pl_namespace int NOT NULL default '0',
  28   pl_title varchar(255) binary NOT NULL default '',
  29
  30   UNIQUE KEY pl_from(pl_from,pl_namespace,pl_title),
  31   KEY (pl_namespace,pl_title)
  32
  33 ) /*$wgDBTableOptions*/;
  34
  35
  36 -- Import existing-page links
  37 INSERT
  38   INTO /*$wgDBprefix*/pagelinks (pl_from,pl_namespace,pl_title)
  39   SELECT l_from,page_namespace,page_title
  40     FROM /*$wgDBprefix*/links, /*$wgDBprefix*/page
  41     WHERE l_to=page_id;
  42
  43 -- import brokenlinks
  44 -- NOTE: We'll have to fix up individual entries that aren't in main NS
  45 INSERT INTO /*$wgDBprefix*/pagelinks (pl_from,pl_namespace,pl_title)
  46   SELECT bl_from, 0, bl_to
  47   FROM /*$wgDBprefix*/brokenlinks;
  48
  49 -- For each namespace do something like:
  50 --
  51 -- UPDATE /*$wgDBprefix*/pagelinks
  52 --   SET pl_namespace=$ns,
  53 --       pl_title=TRIM(LEADING '$prefix:' FROM pl_title)
  54 -- WHERE pl_namespace=0
  55 --   AND pl_title LIKE '$likeprefix:%'";
  56 --