SQLSessionStore.java [src/csip] Revision: Date:
/*
* $Id$
*
* This file is part of the Cloud Services Integration Platform (CSIP),
* a Model-as-a-Service framework, API and application suite.
*
* 2012-2022, 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.api.server.ServiceException;
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", "", "INFO");
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
boolean isMSSQL = false;
SQLSessionStore(String url) {
Map<String, String> 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<String> keys(int skip, int limit, String sortby, boolean sortAsc) {
if (sortby == null) {
sortby = "tst";
sortAsc = false;
}
Set<String> 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");
}
}