labia

And people wonder why I wanted a database backend for CGI::Wiki. Earle was wondering about finding orphaned OpenGuides pages - pages that aren't linked to, aren't in any categories or locales, and aren't redirects. So I thought for a minute or two and wrote some (admittedly ugly and can probably be improved) SQL:

  select distinct node.name
    from node left join internal_links on node.name=internal_links.link_to
    where internal_links.link_from is null
      and node.name not in
        (select distinct node
           from metadata
           where node in
             (select distinct node.name
                from node left join internal_links on node.name=internal_links.link_to
                where internal_links.link_from is null
             )
             and metadata_type in ('category', 'locale')
         )
      and node.text not like '%REDIRECT%';

and that does the job.