#!/usr/bin/perl
 
###################################################################################
#
#	Parse out the Wikipedia page title, the DBpedia Infobox Relation and the subject from the infobox.nt file.
#
#	Inserts data into a MySQL database tables with the structures:
#	
#	infoboxes
#
#	+-----------+--------------+------+-----+---------+----------------+
#	| Field     | Type         | Null | Key | Default | Extra          |
#	+-----------+--------------+------+-----+---------+----------------+
#	| id        | int(11)      | NO   | PRI | NULL    | auto_increment | 
#	| predicate | varchar(255) | YES  |     | NULL    |                | 
#	| subject   | varchar(255) | YES  |     | NULL    |                | 
#	| page      | varchar(255) | YES  |     | NULL    |                | 
#	| type      | int(1)       | YES  |     | NULL    |                | 
#	+-----------+--------------+------+-----+---------+----------------+
#
#	predicateFrequency
#
#	+-----------+--------------+------+-----+---------+-------+
#	| Field     | Type         | Null | Key | Default | Extra |
#	+-----------+--------------+------+-----+---------+-------+
#	| predicate | varchar(255) | NO   | PRI |         |       | 
#	| frequency | int(6)       | YES  |     | NULL    |       | 
#	+-----------+--------------+------+-----+---------+-------+
#
#	
#	To output to a CSV file, uncomment lines beginning with "#@#"
#	
#	DBpedia infobox.nt downloaded from: http://blog.georgikobilarov.com/2008/10/dbpedia-rethinking-wikipedia-infobox-extraction/
#
#	Script written by Michael Robinson - faceleg - http://pagesofinterest.net/
#
#	Contact: mike@pagesofinterest.net
#
####################################################################################
 
use strict;
use warnings;
use DBI;
use DBD::mysql;
 
####################################################################################
#
#	Unicode: 		http://search.cpan.org/~gaas/Unicode-String-2.09/String.pm
#	Unicode::Escape:	http://search.cpan.org/~itwarrior/Unicode-Escape-0.0.2/lib/Unicode/Escape.pm
#
####################################################################################
use Unicode::Escape qw(escape unescape);
 
#Database details
my $username = '';
my $pass = '';
my $db = 'wikipedia';
my $querystring;
 
#connect to database
my $dbh = DBI->connect( "dbi:mysql:$db", $username, $pass, { 'PrintError' => 1, 'RaiseError' => 1 } );
 
my $lost = 0;	# amount of entries that are "lost" due to crazy formatting.
 
#File & directory declarations
my $dir = "/path/to/":
my $DBpedia = "infobox.nt";
#@#my $CSV = "cleanedRDF.csv";
 
###################################################################################
#
#	Decode URL encodings	
#
#	thanks to: http://glennf.com/writing/hexadecimal.url.encoding.html
#
###################################################################################
sub URLDecode {
	my $theURL = $_[0];
	$theURL =~ tr/+/ /;
	$theURL =~ s/%([a-fA-F0-9]{2,2})/chr(hex($1))/eg;
	$theURL =~ s/<!–(.|\n)*–>//g;
	return $theURL;
}
 
###################################################################################
#
#	If a string begins with 0 or more white spaces and ", and ends with " and 0 or more white spaces, remove the white spaces and ".	
#
###################################################################################
sub deQuote {
	my $deQuoted = $_[0];
	if($deQuoted =~ /^\s*".*"\s*$/){
		$deQuoted =~ /\s*"(.*)"\s*$/;
		return $1;
	}
	else{ return $deQuoted; }
}
###################################################################################
#
#	Inserts data into MySQL table
#
###################################################################################
sub doInsert {
	if($3 && $3 !~ /^\s*$/ && $3 ne ""){	#make sure the subject isn't empty... in theory - doesn't seem to work for radio station: frequency...?!
		#insert triple into infoboxes table
		$querystring = "insert into infoboxes (page,predicate,subject) values(";
		$querystring.= $dbh->quote(URLDecode($1)).",".$dbh->quote(deQuote(URLDecode($2))).",".$dbh->quote(deQuote(unescape(URLDecode($3)))).")";
		$dbh->do($querystring);
 
		#insert or update data in predicateFrequency table
		my $predicate = $dbh->quote(deQuote(URLDecode($2)));
 
		$querystring = "INSERT INTO predicateFrequency(predicate,frequency) VALUES($predicate,0) ON duplicate KEY UPDATE frequency=frequency+1";
		$dbh->do($querystring);
	}		
}
# go through dbpedia infobox relations
open (IB, $dir.$DBpedia);
#@#open (CSV,'<'.$dir.$DBpedia);
 
while(<IB>) {
 
	chomp;	#offer a byte sized sacrifice to the great compiler
 
	###################################################################################	
	# 
	#	Wikipedia page title should not be dequoted!
	#	
	#	Split into elements 
	# 	$1 = Wikipedia Page
	# 	$2 = Predicate
	# 	$3 = Object
	#
	###################################################################################
	#
	#
	#	standard triple, no extra screwing about required.
	#
	###################################################################################
   	if($_ =~ /<http:\/\/dbpedia\.org\/resource\/(.+)>\s<http:\/\/dbpedia.org\/ontology\/.*#(.+)>\s<http:\/\/dbpedia.org\/resource\/(.*)>.*/){ 
		#print URLDecode($1).'	'.URLDecode($2).'	'.unescape(URLDecode($3));
		#@#print CSV '"'.URLDecode($1).'","'.URLDecode($2).'","'.URLDecode($3).'"';
		doInsert($1,$2,$3);
	}	
	###################################################################################
	#
	#	standard triple with date, remove trailing declaration.
	#
	###################################################################################	
	elsif($_ =~ /<http:\/\/dbpedia\.org\/resource\/(.+)>\s<http:\/\/dbpedia.org\/ontology\/.*#(.+)>(.+)\^\^<http:\/\/www.w3.org\/2001\/XMLSchema#date>\s\./){ 
		#print URLDecode($1).'	'.URLDecode($2).'	'.unescape(URLDecode($3));
		#@#print CSV '"'.URLDecode($1).'","'.URLDecode($2).'","'.URLDecode($3).'"';
		doInsert($1,$2,$3);
	}
	###################################################################################
	#
	#	"Differently" presented data
	#
	###################################################################################
	elsif($_ =~ /<http:\/\/dbpedia\.org\/resource\/(.+)>\s<http:\/\/dbpedia.org\/ontology\/.*#(.+)>(.+)\s\./){ 	
		#print URLDecode($1).'	'.URLDecode($2).'	'.unescape(URLDecode($3));
		#@#print CSV '"'.URLDecode($1).'","'.URLDecode($2).'","'.URLDecode($3).'"';
		doInsert($1,$2,$3);
	}
	else{
		$lost++;
	}
	#@#print CSV "\n"; 
}
 
close(IB);
#@#close(CSV); 
$dbh->disconnect;
 
#let the world know how many we dropped
print $lost;