#!/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;

$result = mysql_query("SELECT * FROM idioms WHERE zh_idiom!=''") or die(mysql_error());
	$result_copy = mysql_query("SELECT * FROM idioms WHERE zh_idiom!=''") or die(mysql_error());
 
	$prev =  mysql_result($result_copy, mysql_num_rows($result_copy)-1 ,'zh_idiom'	);	
 	$next =  mysql_result($result_copy, 1 ,'zh_idiom');
 
 
 	$count = 0;
 
 	while($row = mysql_fetch_array($result)) {  //make one page for each idiom
 
	$file_name = $row['zh_idiom'] . ".php";	
 
    $file = fopen($file_name,"w");
 
	$update_count = "<?php ".'$'."dbhost = 'localhost';
				 ".'$'."dbuser = 'pagesofi_student';
				 ".'$'."dbpass = '".'$'."a|Qt;jXg+Nf';
				 ".'$'."dbname = 'pagesofi_idioms';
 
				 ".'$'."conn = mysql_connect(".'$'."dbhost, ".'$'."dbuser, ".'$'."dbpass) or die('Error connecting to mysql');
 
				mysql_select_db('pagesofi_idioms');
				mysql_query(\"UPDATE idioms SET times_accessed=times_accessed+1 WHERE zh_idiom='".$row['zh_idiom']."'\") or die(mysql_error());
 
				".'$'."result = mysql_query(\"SELECT * FROM idioms WHERE zh_idiom='".$row['zh_idiom']."'\");
 
				".'$'."count = mysql_fetch_array(".'$'."result);
				?>";
 
	$top =  "<META http-equiv=Content-Type content='text/html; charset=UTF-8'><head><link rel='stylesheet' type='text/css' media='screen' href='idioms_individual.css' /></head>
        ".$update_count ."
 
        <title>".$row['zh_idiom']."</title>
        <html><body><p></p>
 
<p>".$row['zh_idiom']."</p><br>".$row['pinyin']."<br><br>". "<p1>".stripslashes($row['en_translation'])."</p1><br><br><p5>" . $row['example'] . "</p5><br/><br/><p2>Keywords: " .$row['key_word']."<br/>Viewed <?php echo " .'$'. "count['times_accessed'] ?> times. <br/>Entered by ".$row['creator']." on ".$row['date_created']."</p2><br/><div class='js-kit-rating' view='score' title='".$row['zh_idiom']."' path='".$row['zh_idiom']."' permalink='http://pagesofinterest.net/idiom/search_results.php?zhongwen=".$row['zh_idiom']."'></div>
<div id='nextPrev'><a id='prev' href='".$prev.".php'>Previous</a><a id='next' href='".$next.".php'>Next</a></div>
<script src='http://js-kit.com/ratings.js'></script>
</body>
</html>";
 
fwrite($file, $top);