package us.kbase.networks.adaptor.ppi.local;

import java.sql.*;
import java.io.*;
import java.util.*;
    
/**
  Class to access local PPI SQL database.
  
  <pre>
  Version 1.0, 10/3/12 - adapted from gov.lbl.scop.LocalSQL
  </pre>

  @version 1.0, 10/3/12
  @author JMC
  */
public class PPI {
    /**
       Connection to the db
    */
    private static Connection con = null;

    /**
       statement to access the db
    */
    private static Statement stmt = null;

    /**
       database URLs for RO access, separated by ;
    */
    private static String roURLs = null;

    /**
       database URLs for RW access, separated by ;
    */
    private static String rwURLs = null;

    /**
       get local property, or null if not defined.
       These are stored in ppi.properties, which is a local
       file not under version control.
    */
    public static String getProperty(String key) {
	Properties prop = new Properties();
	try {
	    PPI x = new PPI();
	    Class myClass = x.getClass();
	    prop.load(myClass.getResourceAsStream("ppi.properties"));
	}
	catch (IOException e) {
	}
	catch (SecurityException e) {
	}
	String value = prop.getProperty(key, null);
	return value;
    }

    /**
       connect to db using a particular URL or set of URLs separated by ;
    */
    final public static void connect(String urls) {
	if ((con==null) || (stmt==null)) {
	    try {
		Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		String[] url = urls.split(";");
		for (int i=0; i<url.length; i++) {
		    try {
			con = DriverManager.getConnection(url[i]);
		    }
		    catch (Exception e2) {
			con = null;
		    }
		    if (con != null) i=url.length;
		}
		stmt = con.createStatement();
	    }
	    catch (Exception e) {
		System.err.println(e.getMessage());
	    }
	}
    }
    
    /**
       connect to db with default access level
    */
    final public static void connect() {
	if (roURLs==null)
	    roURLs = getProperty("db.ro_urls");
	if (roURLs==null)
	    roURLs = "jdbc:mysql://localhost/ppi?user=anonymous";
	connect(roURLs);
    }

    /**
       connect to db with rw access
    */
    final public static void connectRW() {
	if (rwURLs==null)
	    rwURLs = getProperty("db.rw_urls");
	if (rwURLs==null)
	    rwURLs = "jdbc:mysql://localhost/ppi?user=anonymous";
	connect(rwURLs);
    }
    
    /**
       make another Statement, or null if an error occurs.
    */
    final public static Statement createStatement() {
	if (con==null) connect();
	try {
	    return con.createStatement();
	}
	catch (Exception e) {
	    System.err.println(e.getMessage());
	}
	return null;
    }

    /**
       make another Statement that returns results one
       row at a time, or null if an error occurs.
    */
    final public static Statement createStatementOneRow() {
	if (con==null) connect();
	try {
	    Statement stmt = con.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
						 java.sql.ResultSet.CONCUR_READ_ONLY);
	    stmt.setFetchSize(Integer.MIN_VALUE);
	    return stmt;
	}
	catch (Exception e) {
	    System.err.println(e.getMessage());
	}
	return null;
    }
    
    /**
       make a PreparedStatement, or null if an error occurs.
    */
    final public static PreparedStatement prepareStatement(String s) {
	if (con==null) connect();
	try {
	    return con.prepareStatement(s);
	}
	catch (Exception e) {
	    System.err.println(e.getMessage());
	}
	return null;
    }

    /**
       make a PreparedStatement w/ optional autogenerated keys, or
       null if an error occurs.
    */
    final public static PreparedStatement prepareStatement(String s,
							   int autoGeneratedKeys) {
	if (con==null) connect();
	try {
	    return con.prepareStatement(s, autoGeneratedKeys);
	}
	catch (Exception e) {
	    System.err.println(e.getMessage());
	}
	return null;
    }
    
    /**
       turn on/off manual committing, for transactions
    */
    final public static void setAutoCommit(boolean b) throws Exception {
	if (con==null)
	    return;
	con.setAutoCommit(b);
    }

    /**
       commit updates
    */
    final public static void commit() throws Exception {
	if (con==null)
	    return;
	con.commit();
    }

    /**
       roll back updates
    */
    final public static void rollback() throws Exception {
	if (con==null)
	    return;
	con.rollback();
    }
}

@@ -96,20 +96,47 @@
 util.*;%0A
-    
+%0Aimport com.mchange.v2.c3p0.*;%0A
 %0A/**%0A  C
@@ -185,16 +185,62 @@
   %3Cpre%3E%0A
+  Version 2.0, 11/27/12 - updated to use c3p0%0A
   Versio
@@ -312,25 +312,26 @@
 version 
-1
+2
 .0, 1
-0/3
+1/27
 /12%0A  @a
@@ -399,32 +399,32 @@
 o the db%0A    */%0A
+
     private stat
@@ -454,100 +454,8 @@
 l;%0A%0A
-    /**%0A       statement to access the db%0A    */%0A    private static Statement stmt = null;%0A%0A
     
@@ -1325,17 +1325,16 @@
 ) %7B%0A%09if 
-(
 (con==nu
@@ -1341,53 +1341,102 @@
 ll) 
-%7C%7C (stmt==null)) %7B%0A%09    try %7B%0A%09%09Class.forName
+%7B%0A%09    try %7B%0A%09%09ComboPooledDataSource cpds = new ComboPooledDataSource();%0A%09%09cpds.setDriverClass
 (%22or
@@ -1462,23 +1462,10 @@
 er%22)
-.newInstance()
 ;
+%0A
 %0A%09%09S
@@ -1552,26 +1552,45 @@
 %09%09%09c
-on = DriverManager
+pds.setJdbcUrl(url%5Bi%5D);%0A%09%09%09con = cpds
 .get
@@ -1600,22 +1600,16 @@
 nection(
-url%5Bi%5D
 );%0A%09%09   
@@ -1708,40 +1708,8 @@
 %09%09%7D%0A
-%09%09stmt = con.createStatement();%0A
 %09   

Use c3p0 to create connections