AuthPolicyDb.java

import java.util.logging.Logger;

// mySQL access
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Array;
// import javax.sql.*; needed if using DataSource instead of DriverManager for DB-connections

import java.net.MalformedURLException;
import java.net.URI;
import java.net.URL;
import java.net.URLClassLoader;

import java.util.Enumeration;
import java.util.List;
import java.util.LinkedList;
import java.util.Set;
import java.util.HashSet;
import java.util.ArrayList;

import java.lang.ClassNotFoundException;



public class AuthPolicyDb
{
   private static final Logger LOGGER = Logger.getLogger(AuthPolicyDb.class.getName());

   private static final String DB_DRIVER = "org.postgresql.Driver";
   private static final Settings settings = Settings.getInstance();
   static public Settings.DBConn dbconn = settings.dbConn;
   static public String dbConnUrl;
   static public String dbUserName;
   static public String dbPassword;

   private Connection conn;
   private Statement  st;
   private ResultSet  res;

   AuthPolicyDb(){
      conn = null;
      st   = null;
      res  = null;
   }



   public class PubdidGroups
   {
      String pubdid;
      String[] groups;
      PubdidGroups(String pubdid, String[] groups)
      {
         this.pubdid = pubdid;
         this.groups = groups;
      }
   }


/*
   private String convertToVlkbPubdid(String obscorePubdid)
   {
      final String PUBDID_PREFIX = dbconn.obscorePublisher;

      if(obscorePubdid.startsWith(PUBDID_PREFIX))
         return obscorePubdid.substring( PUBDID_PREFIX.length() );
      else
         return obscorePubdid;
   }

   private Set<String> convertToObscorePubdids(Set<String> vlkbPubdids)
   {
      final String PUBDID_PREFIX = dbconn.obscorePublisher;

      Set<String> obscorePubdids = new HashSet<String>();

      for(String pubdid : vlkbPubdids)
      {
         String obscorePubdid =  "\'" + PUBDID_PREFIX + pubdid + "\'";
         obscorePubdids.add(obscorePubdid);
      }

      return obscorePubdids;
   }
*/

   public List<PubdidGroups> queryGroupsPrivateOnly(Set<String> uniqPubdids)
   {
      //Set<String> uniqObscorePubdids = convertToObscorePubdids(uniqPubdids);
      Set<String> uniqObscorePubdids = uniqPubdids;
      String commaSepObscorePubdids  = String.join("\',\'", uniqObscorePubdids);

      assert (commaSepObscorePubdids != null) && (!commaSepObscorePubdids.isEmpty());

      String TheQuery = "SELECT obs_publisher_did,groups FROM obscore "
         + "WHERE (policy = 'PRIV') AND (obs_publisher_did IN (\'"+commaSepObscorePubdids+"\'));";

      // FIXME use separate table holding  _only_  private data-id's
      //String TheQuery = "SELECT obs_publisher_did,groups FROM permissions "
      //   + "WHERE (obs_publisher_did IN (\'"+commaSepObscorePubdids+"\'));";

      //LOGGER.finest(TheQuery);

      List<PubdidGroups> pubdidGroups = new LinkedList<PubdidGroups>();
      try
      {
         res = doQuery(TheQuery);

         while (res.next())
         {
            //String pubdid   = convertToVlkbPubdid(res.getString("obs_publisher_did"));
            String pubdid   = res.getString("obs_publisher_did");
            Array groupsArr = res.getArray("groups");

            String[] groups   = null;
            if(groupsArr == null)
               groups = null;
            else
               groups = (String[]) groupsArr.getArray();

            PubdidGroups pg = new PubdidGroups(pubdid, groups);
            pubdidGroups.add(pg); 
         }
      }
      catch (SQLException se)
      {
         logSqlExInfo(se);
         se.printStackTrace();
      }
      catch (ClassNotFoundException e)
      {
         LOGGER.severe("DB driver "+ DB_DRIVER +" not found: " + e.getMessage());
         e.printStackTrace();
      }
      finally
      {
         closeAll();
      }

      return pubdidGroups; 
   }


   private void closeAll()
   {
         if(res  != null ) try { res.close(); } catch(Exception e) {LOGGER.severe("DB ResultSet::close() failed");}
         if(st   != null ) try { st.close();  } catch(Exception e) {LOGGER.severe("DB Statement::close() failed");}
         if(conn != null ) try { conn.close();} catch(Exception e) {LOGGER.severe("DB Connection::close() failed");} 
  }

   private void logSqlExInfo(SQLException se){

      /* dbconn.print_class_vars(); */

      LOGGER.severe("SQLState : " + se.getSQLState());
      LOGGER.severe("ErrorCode: " + se.getErrorCode());
      LOGGER.severe("Message  : " + se.getMessage());
      Throwable t = se.getCause();
      while(t != null) {
         LOGGER.severe("Cause: " + t);
         t = t.getCause();
      }
   }



   private ResultSet doQuery(String TheQuery)
      throws SQLException, ClassNotFoundException 
   {

      /* https://docs.oracle.com/javase/tutorial/jdbc/basics/connecting.html :
         Any JDBC 4.0 drivers that are found in your class path are automatically loaded.
         (However, you must manually load any drivers prior to JDBC 4.0 with the method
         Class.forName.)
         */
      // try {
//      Class.forName(DB_DRIVER);
      /* OR
         DriverManager.registerDriver(new org.postgresql.Driver());
         */

      //LOGGER.finest(getRegisteredDriverList());

      // FIXME seems DriverManager expects jdbc:postgresql driver scheme, it does not support postgresql:// scheme
      // additionally:
      // jdbc:postgresql:// scheme does not support username:password in the URL. 
      // So:
      // receive postgresql:// scheme with user:password and convert to jdbc:postgresql://
      // by extracting userName and password from the URL-string and prepending 'jdbc:'
      // 

      /*         LOGGER.finest("DBMS URL: " + dbConnUrl);
                 URI dbConnUri = new URI(dbConnUrl);

                 String userInfoString = dbConnUri.getUserInfo(); 

                 if(userInfoString == null) throw new AssertionError("DBMS URL must contain user:password but it is: " + dbConnUrl);

                 String[] userInfo = userInfoString.split(":"); 

                 if(userInfo.length < 2) throw new AssertionError("DBMS URL must contain user:password but it is: " + dbConnUrl);

                 String userName = userInfo[0];
                 String password = userInfo[1];

                 String dbConnJdbcUrl = "jdbc:" + dbConnUrl.replace(userInfoString + "@", "");
   //              */       LOGGER.finest("DBMS URL: " + dbConnUrl);
   //   LOGGER.finest("DBMS userName: " + dbUserName);
   //   LOGGER.finest("DBMS password: " + dbPassword);

   //   conn = DriverManager.getConnection(dbConnUrl, dbUserName, dbPassword);

    //  st = conn.createStatement();

      // } catch (Exception e){ e.printStackTrace();}

      // new
      LOGGER.finer("Connecting to: " + dbconn.uri() + " with optional user/pwd: " + dbconn.userName() + " / " + dbconn.password() );

            Connection conn = DriverManager.getConnection(dbconn.uri(), dbconn.userName(), dbconn.password());
            Statement  st   = conn.createStatement();
            ResultSet  res  = st.executeQuery(TheQuery);
         return res;
      // new end
      // return st.executeQuery(TheQuery);
   }


   private String getClasspathString() {
      StringBuffer classpath = new StringBuffer("getClasspathString:\r\n");
      ClassLoader applicationClassLoader = this.getClass().getClassLoader();
      if (applicationClassLoader == null) {
         applicationClassLoader = ClassLoader.getSystemClassLoader();
      }
      URL[] urls = ((URLClassLoader)applicationClassLoader).getURLs();
      for(int i=0; i < urls.length; i++) {
         classpath.append(urls[i].getFile()).append("\r\n");
      }

      return classpath.toString();
   }


   private String getRegisteredDriverList()
   {
      StringBuffer drvList = new StringBuffer("getRegisteredDriverList:\r\n");
      for (Enumeration e = DriverManager.getDrivers();
            e.hasMoreElements(); )
      {
         Driver d = (Driver) e.nextElement();
         String driverClass = d.getClass().getName();
         drvList.append(driverClass).append("\r\n");	
      }
      return drvList.toString();
   }


}