All Downloads are FREE. Search and download functionalities are using the official Maven repository.

csip.SQLSessionStore Maven / Gradle / Ivy

Go to download

The Cloud Services Integration Platform is a SoA implementation to offer a Model-as-a-Service framework, Application Programming Interface, deployment infrastructure, and service implementations for environmental modeling.

There is a newer version: 2.6.30
Show newest version
/*
 * $Id: SQLSessionStore.java 27bed8415b8b 2019-01-31 od $
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API and application suite.
 *
 * 2012-2019, Olaf David and others, OMSLab, Colorado State University.
 *
 * OMSLab licenses this file to you under the MIT license.
 * See the LICENSE file in the project root for more information.
 */
package csip;

import csip.utils.Binaries;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;

/**
 *
 */
class SQLSessionStore implements SessionStore {

  static final String jdbc_session_id = "jdbc_session";
  SessionLogger l = new SessionLogger(null, "SQLSessionLog", "");
  DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
  boolean isMSSQL = false;


  SQLSessionStore(String url) {
    Map m = new HashMap<>();
    m.put("defaultAutoCommit", "true");
    Binaries.addToJDBCPool(jdbc_session_id, url, m);
    createTableIfNeeded();
  }


  @Override
  public synchronized void setSession(String suid, ModelSession session) throws Exception {
    try (final Connection c = Binaries.getConnection(jdbc_session_id, l)) {
      try (final Statement st = c.createStatement()) {
        String[] a = session.getAttachments();
        String att = String.join(",", a);
        String sql = null;
        if (hasSession(suid)) {
          sql = "UPDATE csip_sessions SET tst='" + session.getTstamp()
              + "', exp='" + session.getExpDate()
              + "', srv='" + session.getService()
              + "', sta='" + session.getStatus()
              + "', nip='" + session.getNodeIP()
              + "', rip='" + session.getReqIP()
              + "', cpu=" + (session.getCputime().isEmpty() ? -1 : Integer.parseInt(session.getCputime()))
              + ",  pro='" + (session.getProgress() == null ? "" : session.getProgress())
              + "', att='" + att + "';";
        } else {
          sql = "INSERT INTO csip_sessions(suid,tst,exp,srv,sta,nip,rip,cpu,pro,att) VALUES('"
              + suid + "','"
              + session.getTstamp() + "','"
              + session.getExpDate() + "','"
              + session.getService() + "','"
              + session.getStatus() + "','"
              + session.getNodeIP() + "','"
              + session.getReqIP() + "','"
              + (session.getCputime().isEmpty() ? -1 : Integer.parseInt(session.getCputime())) + "','"
              + (session.getProgress() == null ? "" : session.getProgress()) + "','"
              + att + "');";
        }
        l.info("setSession() " + sql);
        st.executeUpdate(sql);
      }
    } catch (SQLException | ServiceException ex) {
      l.log(Level.SEVERE, null, ex);
      Config.LOG.log(Level.SEVERE, null, ex);
    }
  }


  @Override
  public synchronized ModelSession getSession(String suid) throws Exception {
    try (final Connection c = Binaries.getConnection(jdbc_session_id, l)) {
      try (final Statement st = c.createStatement()) {
        String sql = "SELECT tst,exp,srv,sta,nip,rip,cpu,pro,att FROM csip_sessions WHERE suid='" + suid + "';";
        l.info("getSession() " + sql);
        try (ResultSet rs = st.executeQuery(sql)) {
          rs.next();

          ModelSession s = new ModelSession();
          s.setTstamp(rs.getString(1));
          s.setExpDate(rs.getString(2));
          s.setService(rs.getString(3));
          s.setStatus(rs.getString(4));
          s.setNodeIP(rs.getString(5));
          s.setReqIP(rs.getString(6));
          int cpu = rs.getInt(7);
          s.setCputime(cpu == -1 ? "" : Integer.toString(cpu));
          String pro = rs.getString(8);
          s.setProgress(pro.equals("") ? null : pro);
          String att = rs.getString(9);
          s.setAttachments(att.isEmpty() ? ModelSession.NO_ATTACHMENTS : att.split(","));
          return s;
        }
      }
    } catch (SQLException | ServiceException ex) {
      l.log(Level.SEVERE, null, ex);
      Config.LOG.log(Level.SEVERE, null, ex);
    }
    return null;
  }


  @Override
  public synchronized boolean hasSession(String suid) throws Exception {
    try (final Connection c = Binaries.getConnection(jdbc_session_id, l)) {
      try (final Statement st = c.createStatement()) {
        String sql = "SELECT suid FROM csip_sessions WHERE suid = '" + suid + "';";
        l.info("hasSession() " + sql);
        try (ResultSet rs = st.executeQuery(sql)) {
          return rs.next();
        }
      }
    } catch (SQLException | ServiceException ex) {
      l.log(Level.SEVERE, null, ex);
      Config.LOG.log(Level.SEVERE, null, ex);
    }
    return false;
  }


  @Override
  public void removeSession(String suid) {
    try (final Connection c = Binaries.getConnection(jdbc_session_id, l)) {
      try (final Statement st = c.createStatement()) {
        String sql = "DELETE FROM csip_sessions WHERE suid='" + suid + "';";
        l.info("removeSession() " + sql);
        st.executeUpdate(sql);
      }
    } catch (SQLException | ServiceException ex) {
      l.log(Level.SEVERE, null, ex);
      Config.LOG.log(Level.SEVERE, null, ex);
    }
  }


  @Override
  public void close() throws Exception {
    // should shut down at the end.
  }


  @Override
  public synchronized Set keys(int skip, int limit, String sortby, boolean sortAsc) {
    if (sortby == null) {
      sortby = "tst";
      sortAsc = false;
    }
    Set keys = new LinkedHashSet<>();
    try (final Connection c = Binaries.getConnection(jdbc_session_id, l)) {
      try (final Statement st = c.createStatement()) {
        String sql = isMSSQL ? "SELECT suid FROM csip_sessions ORDER BY " + sortby
            + " OFFSET " + skip + " ROWS FETCH NEXT " + limit
            + " ROWS ONLY;" : "SELECT suid FROM csip_sessions ORDER BY " + sortby + " LIMIT " + limit + " OFFSET " + skip + ";";
        l.info("keys() " + sql);
        try (ResultSet rs = st.executeQuery(sql)) {
          while (rs.next()) {
            keys.add(rs.getString(1));
          }
        }
      }
    } catch (SQLException | ServiceException ex) {
      l.log(Level.SEVERE, null, ex);
      Config.LOG.log(Level.SEVERE, null, ex);
    }
    return keys;
  }


  @Override
  public synchronized long getCount() {
    try (final Connection c = Binaries.getConnection(jdbc_session_id, l)) {
      try (final Statement st = c.createStatement()) {
        String sql = "SELECT COUNT(*) FROM csip_sessions;";
        l.info("getCount() " + sql);
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        return rs.getLong(1);
      }
    } catch (SQLException | ServiceException ex) {
      l.log(Level.SEVERE, null, ex);
      Config.LOG.log(Level.SEVERE, null, ex);
    }
    return -1;
  }


  @Override
  public void ping() throws Exception {
  }


  private void createTableIfNeeded() {
    try (final Connection c = Binaries.getConnection(jdbc_session_id, l)) {
      String driver = c.getMetaData().getDriverName();
      l.info("Driver name: " + driver);
      isMSSQL = driver.contains("Microsoft");
      try (final Statement st = c.createStatement()) {
        // mssql
        String sql = isMSSQL
            ? "if not exists (select * from sysobjects where name='csip_sessions' and xtype='U')"
            + " BEGIN CREATE TABLE csip_sessions ("
            + "  suid VARCHAR(64) primary key,"
            + "  tst  VARCHAR(64),"
            + "  exp  VARCHAR(64),"
            + "  srv  VARCHAR(64),"
            + "  sta  VARCHAR(64),"
            + "  nip  VARCHAR(64),"
            + "  rip  VARCHAR(64),"
            + "  cpu  INTEGER,"
            + "  pro  VARCHAR(64),"
            + "  att  VARCHAR(64)"
            + ") END;"
            : "CREATE TABLE IF NOT EXISTS csip_sessions ("
            + "  suid VARCHAR(64) primary key,"
            + "  tst  VARCHAR(64),"
            + "  exp  VARCHAR(64),"
            + "  srv  VARCHAR(64),"
            + "  sta  VARCHAR(64),"
            + "  nip  VARCHAR(64),"
            + "  rip  VARCHAR(64),"
            + "  cpu  INTEGER,"
            + "  pro  VARCHAR(64),"
            + "  att  VARCHAR(64)" + ");";
        l.info("createTable() " + sql);
        st.execute(sql);
        l.info("created Table. ");
      }
    } catch (SQLException | ServiceException ex) {
      l.severe("ERROR: connecting to session store, check connection string or database setup. need r/w SQL access.");
      l.log(Level.SEVERE, null, ex);
      Config.LOG.log(Level.SEVERE, null, ex);
    }
  }


  @Override
  public long countSessionsByState(String state) {
    try (final Connection c = Binaries.getConnection(jdbc_session_id, l)) {
      try (final Statement st = c.createStatement()) {
        String sql = "SELECT COUNT(*) FROM csip_sessions WHERE sta='" + state + "';";
        l.info("getCount() " + sql);
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        return rs.getLong(1);
      }
    } catch (SQLException | ServiceException ex) {
      l.log(Level.SEVERE, null, ex);
      Config.LOG.log(Level.SEVERE, null, ex);
    }
    return -1;
  }


  @Override
  public void registerResources(boolean register) {
    Config.LOG.warning("Not implemented");
  }

}




© 2015 - 2024 Weber Informatics LLC | Privacy Policy