Converting ShortStats to PostgreSQL

Shaun Inmans rather spiffy shortstats has always looked nice to me but until lately I coudln't run it as I don't use MySQL. So I hacked it and with Shaun's permission here is a guide how.

configuration.php

One small change ass $SI_db['con'] = 0; to the database section to provide a connection resource for all your PostgreSQL actions.

_install.php

1. Change the checking line to use $SI_db['con'] and pg_query so the code reads
   if (@ pg_query($SI_db['con'],"SELECT * FROM $SI_tables[stats]") && @ pg_query($SI_db['con'],"SELECT * FROM $SI_tables[searchterms]"))


2. Change the two queries that add the database to the following
   

   
   $query = "CREATE TABLE ".$SI_tables['stats']." (
   	  id SERIAL,
   	  remote_ip varchar(15) NOT NULL,
   	  country varchar(50) NOT NULL,
   	  domain varchar(255) NOT NULL,
   	  referer varchar(255) NOT NULL,
   	  resource varchar(255) NOT NULL,
   	  user_agent varchar(255) NOT NULL,
   	  platform varchar(50) NOT NULL,
   	  browser varchar(50) NOT NULL,
   	  version varchar(15) NOT NULL,
   	  dt int4 NOT NULL default '0',
   	  PRIMARY KEY (\"id\")
   	  )";
   $query_search = "CREATE TABLE $SI_tables[searchterms] (
   	  id SERIAL,
   	  searchterms varchar(255) NOT NULL,
   	  count int4 NOT NULL default '0',
   	  PRIMARY KEY  (id)
   	  )";
   

   
   


3. 
   Take the query execution out of the if statment at the end as so:
   
$r1 = @ pg_query($SI_db['con'],$query);
$r2 = @ pg_query($SI_db['con'],$query_search);

if ($r1 && $r2)

   

inc.stats.php

One small change, the query at the end should be pg_query($SI_db['con'],$query); using our database connection and PostgreSQL.

functions.php

Again three changes here

1. The connection function SI_pconnect() needs a PostreSQL connection string like
   $SI_db['con'] =@ pg_connect("host=".$SI_db['server']." user=".$SI_db['username']." password=".$SI_db['password']." dbname=".$SI_db['database']); and you can replace the logic after with if ($SI_db['con'] = 0) die($horribly);


2. Every single function needs to be scoured for mysql_query($query); calls and have them replaced with pg_query($SI_db['con'],$query); as well as adding global $SI_db; at the start of every single function.


3. last but not least a to get referers working we need a small change to the SQL in SI_getDomains()
   

   
   SELECT 
       domain, 
       referer, 
       COUNT(*) AS total,
       MAX(dt) as last_access
     FROM 
       $SI_tables[stats]
     WHERE 
       domain !='".
       SI_trimReferer($_SERVER['SERVER_NAME'])."' 
     AND 
       domain!='' 
     GROUP BY 
       domain,referer
     ORDER BY 
       total DESC, 
       last_access DESC
   

   
   

And then your done

Follow the README provided and everything should work fine. If not feel free to email me and if I have time I'll offer help or even send a copy of my working abeit old version of shortstats