User:Revansx/GePhi/How to export all Page links as a CSV for import into GePhi

Tailor and Test the basic SQL

This code assumes a wiki database named "wiki_demo". Tailor this for your wiki database and paste it into the mysql cli on your server and make sure it runs without error and produces the expected output (a table of page links with both source and destination info)

( SELECT 
       A1.pl_from,
       A1.pl_from_namespace,
       A1.pl_namespace,
       A1.pl_title,
       B1.page_title,
       B1.page_namespace
  FROM `wiki_demo`.pagelinks AS A1
  JOIN `wiki_demo`.page AS B1 ON (A1.pl_from_namespace = B1.page_namespace AND A1.pl_from = B1.page_id)
);

Test the SQL code that produces formatted output

Make whatever changes you made to the SQL above to the nested SQL select below and you should get similar results from the mysql cli but just formatted like we want for GePhi import

( SELECT CONCAT('"',page_namespace,   ':',page_title,'"') AS Subject,
         CONCAT('"',pl_namespace,':',pl_title,  '"') AS Object
  FROM ( SELECT 
             A1.pl_from,
             A1.pl_from_namespace,
             A1.pl_namespace,
             A1.pl_title,
             B1.page_title,
             B1.page_namespace
         FROM `wiki_epics`.pagelinks AS A1
         JOIN `wiki_epics`.page AS B1 ON (A1.pl_from_namespace = B1.page_namespace AND A1.pl_from = B1.page_id)
       )a
);

Tailor and test the file Shell script that gets the page link data from your wiki and makes a CSV file ready for import into GePhi

#!/bin/sh

zResult=`mysql -e "
( SELECT CONCAT('\"',page_namespace, ':',page_title,'\"') AS Subject,
         CONCAT('\"',pl_namespace,   ':',pl_title,  '\"') AS Object
  FROM ( SELECT 
             A1.pl_from,
             A1.pl_from_namespace,
             A1.pl_namespace,
             A1.pl_title,
             B1.page_title,
             B1.page_namespace
         FROM \\\`wiki_sbu-ptc\\\`.pagelinks AS A1
         JOIN \\\`wiki_sbu-ptc\\\`.page AS B1 ON (A1.pl_from_namespace = B1.page_namespace AND A1.pl_from = B1.page_id)
       )a
); "`

# Pipe the result of the sql query to a tab-separated ".dat" file
echo "$zResult" > LinkListfromWikiDB.dat

# Convert the native sql "tab" delimited file to a "," delimited CSV file
sed 's/\t/,/g' LinkListfromWikiDB.dat > LinkListfromWikiDB.csv

# Remove the headers in line 1 from the file 
echo -e "$(sed '1d' LinkListfromWikiDB.csv)\n" > LinkListfromWikiDB.csv

# Copy the GePhi-ready file to the place on your wiki server where the text file from step 1 is stored
cp LinkListfromWikiDB.csv /opt/data-meza/uploads/pbs-kms-dev/a/ab/LinkListfromWikiDB.txt

Example output

"0:Main_Page","0:Page_A"
"0:Page_C","0:Page_A"
"0:Page_A","0:Page_B"
"0:Page_B","0:Page_C"
"0:Main_Page","106:Meeting"
"10:Meeting_Minutes_Block","0:Meeting_Minutes"
"10:FlowMention","2:Example"
"10:Meeting_Minutes_Block","10:Button"
"10:Meeting","10:Documentation"
"10:File_link","10:Documentation"
"10:Archive_for_converted_wikitext_talk_page","11:Archive_for_converted_wikitext_talk_page"
"10:Meeting_minutes","106:Meeting_Minutes"
"10:Topic_from_meeting","106:Meeting_Minutes"
"14:ExampleClass","106:ExampleClass"
"14:Meeting","106:Meeting"
"14:Meeting_Minutes","106:Meeting_Minutes"
"102:Foaf:knows","8:Smw_import_foaf"
"102:Foaf:name","8:Smw_import_foaf"
"102:Foaf:homepage","8:Smw_import_foaf"
"102:Owl:differentFrom","8:Smw_import_owl"
"102:Call_in_number","10:Property"
"102:Call_in_password","10:Property"
"102:Standard_time","10:Property"
"102:Meeting_type","10:Property"
"102:Notes_taken_by","10:Property"
"102:From_page","10:Property"
"102:Has_date","10:Property"
"102:Index","10:Property"
"102:Related_article","10:Property"
"102:Synopsis","10:Property"
"102:Building","10:Property"
"102:Overview","10:Property"
"102:Room","10:Property"
"106:Meeting","10:Button"
"106:Meeting_Minutes","10:Button"
"106:Meeting","10:Form"
"106:Meeting_Minutes","10:Form"