import java.util.*;
import java.sql.*;


public class PubChemLocal
{
  private static Connection con;
  private static ResultSet  rs;
  private static Statement  stmt;

  public PubChemLocal() {}

// ----------------------------------------------------------

  public void open()
  {
    try
    {
      Configuration config = new Configuration();

      // --- load the postgresql jdbc driver
      Class.forName(config.pc_driver());

      String url = config.pc_url();

      Properties props = new Properties();
      props.setProperty("user", config.pc_username());
      props.setProperty("password",config.pc_password());
      props.setProperty("ssl","true");

      con = DriverManager.getConnection(url, props);
      stmt = con.createStatement();
    }
    catch (SQLException ex)        {this.explain(ex);}
    catch (java.lang.Exception ex) {ex.printStackTrace();}
  }

// ----------------------------------------------------------

  public void close()
  {
    try
    {
      con.close();
      stmt.close();
    }
    catch (SQLException ex) {this.explain(ex);}
  }

// ----------------------------------------------------------

  public List<String> sid2syn(int sid)
  {
    List<String>  synonyms = new ArrayList<String>();
    List<Integer> cids     = new ArrayList<Integer>();
    int           cid;
    String        query;
    boolean       more;

    try
    {
      this.open();

      // --- map SID to CID
      query = "SELECT cid FROM cid_sid WHERE sid = " + sid + ";";
      rs    = stmt.executeQuery(query);
      more  = rs.next();
      while (more)
      {
        cids.add(rs.getInt(1));
        more = rs.next();
      }

      for (Iterator<Integer> i = cids.iterator(); i.hasNext(); )
      {
        cid = i.next();

        // --- map CID to its synonyms
        query = "SELECT syn FROM cid_synonym WHERE cid = " + cid + ";";
        rs    = stmt.executeQuery(query);
        more  = rs.next();
        while (more)
        {
          synonyms.add(rs.getString(1));
          more = rs.next();
        }
      }

      this.close();
    }
    catch (SQLException ex)        {this.explain(ex);}
    catch (java.lang.Exception ex) {ex.printStackTrace();}

    return synonyms;
  }

// ----------------------------------------------------------

  public void explain(SQLException ex)
  {
    System.out.println ("\n*** SQLException caught ***\n");

    while (ex != null) 
    {
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("Message:  " + ex.getMessage());
      System.out.println("Vendor:   " + ex.getErrorCode());
      System.out.println("");

      ex = ex.getNextException();
    }
  }
}