From d684cd4dbdadb11a0017556e802bdf3141336f2b Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Wed, 10 Jun 2015 20:22:04 +0200 Subject: [server] db stuff, new interface, ... --- .../src/main/java/sql/MysqlConnection.java | 74 + .../src/main/java/sql/MysqlStatement.java | 291 +++ dozentenmodulserver/src/main/java/sql/SQL.java | 1919 +------------------- .../src/main/java/sql/models/DbImage.java | 41 + 4 files changed, 454 insertions(+), 1871 deletions(-) create mode 100644 dozentenmodulserver/src/main/java/sql/MysqlConnection.java create mode 100644 dozentenmodulserver/src/main/java/sql/MysqlStatement.java create mode 100644 dozentenmodulserver/src/main/java/sql/models/DbImage.java (limited to 'dozentenmodulserver/src/main/java/sql') diff --git a/dozentenmodulserver/src/main/java/sql/MysqlConnection.java b/dozentenmodulserver/src/main/java/sql/MysqlConnection.java new file mode 100644 index 00000000..dbbddfe1 --- /dev/null +++ b/dozentenmodulserver/src/main/java/sql/MysqlConnection.java @@ -0,0 +1,74 @@ +package sql; + +import java.sql.Connection; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; + +import org.apache.log4j.Logger; + +public class MysqlConnection implements AutoCloseable { + + private static final Logger LOGGER = Logger.getLogger(MysqlConnection.class); + + private static final int CONNECTION_TIMEOUT_MS = 5 * 60 * 1000; + + private final long deadline = System.currentTimeMillis() + CONNECTION_TIMEOUT_MS; + + private final Connection rawConnection; + + private boolean hasPendingQueries = false; + + private List openStatements = new ArrayList<>(); + + MysqlConnection(Connection rawConnection) { + this.rawConnection = rawConnection; + } + + public MysqlStatement prepareStatement(String sql) throws SQLException { + hasPendingQueries = true; + MysqlStatement statement = new MysqlStatement(rawConnection, sql); + openStatements.add(statement); + return statement; + } + + public void commit() throws SQLException { + rawConnection.commit(); + hasPendingQueries = false; + } + + public void rollback() throws SQLException { + rawConnection.rollback(); + hasPendingQueries = false; + } + + boolean isValid() { + return System.currentTimeMillis() < deadline; + } + + @Override + public void close() { + if (hasPendingQueries) { + LOGGER.warn("Mysql connection had uncommited queries on .close()"); + try { + rawConnection.rollback(); + } catch (SQLException e) { + LOGGER.warn("Rolling back uncommited queries failed!", e); + } + for (MysqlStatement statement : openStatements) { + statement.close(); + } + openStatements.clear(); + } + SQL.returnConnection(this); + } + + void release() { + try { + rawConnection.close(); + } catch (SQLException e) { + // Nothing meaningful to do + } + } + +} diff --git a/dozentenmodulserver/src/main/java/sql/MysqlStatement.java b/dozentenmodulserver/src/main/java/sql/MysqlStatement.java new file mode 100644 index 00000000..efef88b0 --- /dev/null +++ b/dozentenmodulserver/src/main/java/sql/MysqlStatement.java @@ -0,0 +1,291 @@ +package sql; + +import java.io.Closeable; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.LinkedHashMap; +import java.util.List; +import java.util.Map; + +/** + * Class for creating {@link java.sql.PreparedStatement}s with + * named parameters. + * Based on + * Named Parameters for PreparedStatement + */ +public class MysqlStatement implements Closeable { + + private static final QueryCache cache = new QueryCache(); + + private final PreparsedQuery query; + + private final PreparedStatement statement; + + private final List openResultSets = new ArrayList<>(); + + MysqlStatement(Connection con, String sql) throws SQLException { + PreparsedQuery query; + synchronized (cache) { + query = cache.get(sql); + } + if (query == null) { + query = parse(sql); + synchronized (cache) { + cache.put(sql, query); + } + } + this.query = query; + this.statement = con.prepareStatement(query.sql); + } + + /** + * Returns the indexes for a parameter. + * + * @param name parameter name + * @return parameter indexes + * @throws IllegalArgumentException if the parameter does not exist + */ + private List getIndexes(String name) { + List indexes = query.indexMap.get(name); + if (indexes == null) { + throw new IllegalArgumentException("Parameter not found: " + name); + } + return indexes; + } + + /** + * Sets a parameter. + * + * @param name parameter name + * @param value parameter value + * @throws SQLException if an error occurred + * @throws IllegalArgumentException if the parameter does not exist + * @see PreparedStatement#setObject(int, java.lang.Object) + */ + public void setObject(String name, Object value) throws SQLException { + List indexes = getIndexes(name); + for (Integer index : indexes) { + statement.setObject(index, value); + } + } + + /** + * Sets a parameter. + * + * @param name parameter name + * @param value parameter value + * @throws SQLException if an error occurred + * @throws IllegalArgumentException if the parameter does not exist + * @see PreparedStatement#setString(int, java.lang.String) + */ + public void setString(String name, String value) throws SQLException { + List indexes = getIndexes(name); + for (Integer index : indexes) { + statement.setString(index, value); + } + } + + /** + * Sets a parameter. + * + * @param name parameter name + * @param value parameter value + * @throws SQLException if an error occurred + * @throws IllegalArgumentException if the parameter does not exist + * @see PreparedStatement#setInt(int, int) + */ + public void setInt(String name, int value) throws SQLException { + List indexes = getIndexes(name); + for (Integer index : indexes) { + statement.setInt(index, value); + } + } + + /** + * Sets a parameter. + * + * @param name parameter name + * @param value parameter value + * @throws SQLException if an error occurred + * @throws IllegalArgumentException if the parameter does not exist + * @see PreparedStatement#setInt(int, int) + */ + public void setLong(String name, long value) throws SQLException { + List indexes = getIndexes(name); + for (Integer index : indexes) { + statement.setLong(index, value); + } + } + + /** + * Executes the statement. + * + * @return true if the first result is a {@link ResultSet} + * @throws SQLException if an error occurred + * @see PreparedStatement#execute() + */ + public boolean execute() throws SQLException { + return statement.execute(); + } + + /** + * Executes the statement, which must be a query. + * + * @return the query results + * @throws SQLException if an error occurred + * @see PreparedStatement#executeQuery() + */ + public ResultSet executeQuery() throws SQLException { + ResultSet rs = statement.executeQuery(); + openResultSets.add(rs); + return rs; + } + + /** + * Executes the statement, which must be an SQL INSERT, UPDATE or DELETE + * statement; or an SQL statement that returns nothing, such as a DDL + * statement. + * + * @return number of rows affected + * @throws SQLException if an error occurred + * @see PreparedStatement#executeUpdate() + */ + public int executeUpdate() throws SQLException { + return statement.executeUpdate(); + } + + /** + * Closes the statement. + * + * @see Statement#close() + */ + @Override + public void close() { + for (ResultSet rs : openResultSets) { + try { + rs.close(); + } catch (SQLException e) { + // + } + } + try { + statement.close(); + } catch (SQLException e) { + // Nothing to do + } + } + + /** + * Adds the current set of parameters as a batch entry. + * + * @throws SQLException if something went wrong + */ + public void addBatch() throws SQLException { + statement.addBatch(); + } + + /** + * Executes all of the batched statements. + * + * See {@link Statement#executeBatch()} for details. + * + * @return update counts for each statement + * @throws SQLException if something went wrong + */ + public int[] executeBatch() throws SQLException { + return statement.executeBatch(); + } + + // static methods + + private static PreparsedQuery parse(String query) { + int length = query.length(); + StringBuffer parsedQuery = new StringBuffer(length); + Map> paramMap = new HashMap<>(); + boolean inSingleQuote = false; + boolean inDoubleQuote = false; + boolean hasBackslash = false; + int index = 1; + + for (int i = 0; i < length; i++) { + char c = query.charAt(i); + if (hasBackslash) { + // Last char was a backslash, so we ignore the current char + hasBackslash = false; + } else if (c == '\\') { + // This is a backslash, next char will be escaped + hasBackslash = true; + } else if (inSingleQuote) { + // End of quoted string + if (c == '\'') { + inSingleQuote = false; + } + } else if (inDoubleQuote) { + // End of quoted string + if (c == '"') { + inDoubleQuote = false; + } + } else { + // Not in string, look for named params + if (c == '\'') { + inSingleQuote = true; + } else if (c == '"') { + inDoubleQuote = true; + } else if (c == ':' && i + 1 < length && Character.isJavaIdentifierStart(query.charAt(i + 1))) { + int j = i + 2; + while (j < length && Character.isJavaIdentifierPart(query.charAt(j))) { + j++; + } + String name = query.substring(i + 1, j); + c = '?'; // replace the parameter with a question mark + i += name.length(); // skip past the end of the parameter + + List indexList = paramMap.get(name); + if (indexList == null) { + indexList = new ArrayList<>(); + paramMap.put(name, indexList); + } + indexList.add(new Integer(index)); + + index++; + } + } + parsedQuery.append(c); + } + + return new PreparsedQuery(parsedQuery.toString(), paramMap); + } + + // private helper classes + + private static class PreparsedQuery { + private final Map> indexMap; + private final String sql; + + public PreparsedQuery(String sql, Map> indexMap) { + this.sql = sql; + this.indexMap = indexMap; + } + } + + private static class QueryCache extends LinkedHashMap { + private static final long serialVersionUID = 1L; + + public QueryCache() { + super(30, (float) 0.75, true); + } + + @Override + protected boolean removeEldestEntry(Map.Entry eldest) { + return size() > 40; + } + } + +} diff --git a/dozentenmodulserver/src/main/java/sql/SQL.java b/dozentenmodulserver/src/main/java/sql/SQL.java index 925ffb57..af79b521 100644 --- a/dozentenmodulserver/src/main/java/sql/SQL.java +++ b/dozentenmodulserver/src/main/java/sql/SQL.java @@ -2,1903 +2,80 @@ package sql; import java.sql.Connection; import java.sql.DriverManager; -import java.sql.PreparedStatement; -import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.Statement; -import java.text.DateFormat; -import java.text.SimpleDateFormat; -import java.util.ArrayList; -import java.util.Date; -import java.util.HashMap; -import java.util.List; -import java.util.Map; -import java.util.UUID; +import java.util.Collections; +import java.util.Queue; +import java.util.Set; +import java.util.concurrent.ConcurrentHashMap; +import java.util.concurrent.ConcurrentLinkedQueue; import models.Configuration; import org.apache.log4j.Logger; -import org.openslx.sat.thrift.iface.Image; -import org.openslx.sat.thrift.iface.Lecture; -import org.openslx.sat.thrift.iface.Person; - -import util.Util; public class SQL { - private static final Logger log = Logger.getLogger(SQL.class); + private static final Logger LOGGER = Logger.getLogger(SQL.class); + /** + * Pool of available connections. + */ + private static final Queue pool = new ConcurrentLinkedQueue<>(); + + /** + * Set of connections currently handed out. + */ + private static final Set busyConnections = Collections.newSetFromMap(new ConcurrentHashMap()); static { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) { - log.fatal("Cannot get mysql JDBC driver!", e); + LOGGER.fatal("Cannot get mysql JDBC driver!", e); System.exit(1); } } - public Connection getConnection() { - // TODO: Connection pooling, better yet some abstraction layer for mysql, eg. like dalesbred in master-server - try { - Connection con = DriverManager.getConnection(Configuration.getDbUri(), - Configuration.getDbUsername(), Configuration.getDbPassword()); - con.setAutoCommit(false); - - return con; - } catch (SQLException e) { - - log.info("Failed to return connection to Client."); - e.printStackTrace(); - } - - return null; - } - - // no prepared statement to do here - public ResultSet getImage() { - try { - Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet rs = stm.executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;"); - con.close(); - return rs; - - } catch (SQLException e) { - - log.info("Failed to getImage."); - e.printStackTrace(); - } - - return null; - } - - public String getPathOfImage(String image_id, String version) { - try { - Connection con = getConnection(); - - String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID= " + "?" - + " and imageVersion= " + "?" + ";"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, image_id); - prest.setString(2, version); - - ResultSet rs = prest.executeQuery(); - con.commit(); - rs.first(); - String path = rs.getString("image_path"); - con.close(); - return path; - - } catch (SQLException e) { - - log.info("Failed to getPathOfImage."); - e.printStackTrace(); - } - - return null; - } - - public String setInstitution(String university) { - // TODO: WTF!? - // 1) This method is called set* and has a return value (other than boolean) - // 2) This method generates a random ID for an institution name (which is called university in the - // parameter), so every satellite server has a different ID for the same institution - // 3) If an institution is renamed, it gets a new id, and the old one remains in the db - // 4) In case it inserts a new institution with an ID, it queries the DB for the ID right - // after doing so. So it's expected to be different from what was just inserted!?? - try { - Connection con = getConnection(); - - String sql = "SELECT * FROM bwLehrpool.m_institution where name like ?;"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, university); - ResultSet ret = prest.executeQuery(); - con.commit(); - - if (ret.next() == false) { - - String id = UUID.randomUUID().toString(); - - sql = "INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`) VALUES" + "(?,?)"; - - prest = con.prepareStatement(sql); - prest.setString(1, id); - prest.setString(2, university); - prest.executeUpdate(); - - con.commit(); - - sql = "SELECT institutionID FROM bwLehrpool.m_institution WHERE name like ?;"; - - prest = con.prepareStatement(sql); - prest.setString(1, university); - - ResultSet rs = prest.executeQuery(); - con.commit(); - rs.next(); - String institution = rs.getString("institutionID"); - con.close(); - - return institution; - } else { - String institution = ret.getString("institutionID"); - con.close(); - return institution; - } - - } catch (SQLException e) { - - log.info("Failed to setInstitution."); - e.printStackTrace(); - } - return "-1"; - } - - public String setPerson(String userID, String lastname, String firstname, String mail, Date lastlogin, - String Institution) { - DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - try { - Connection con = getConnection(); - - String sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + "?" - + " and Vorname like " + "?" + ";"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, lastname); - prest.setString(2, firstname); - - ResultSet ret = prest.executeQuery(); - con.commit(); - if (ret.next() == false) { - log.info("create user statement values are: userID, loginname, lastname, firstname, mail, institution VALUES: " - + userID - + " " - + userID - + " " - + lastname - + " " - + firstname - + " " - + mail - + " " - + Institution); - - sql = "INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES(" - + "?" - + "," - + "?" - + "," - + "?" - + "," - + "?" - + "," - + "?" - + ",'" - + formatter.format(new Date()) + "'," + "?" + ");"; - - prest = con.prepareStatement(sql); - prest.setString(1, userID); - prest.setString(2, userID); - prest.setString(3, lastname); - prest.setString(4, firstname); - prest.setString(5, mail); - prest.setString(6, Institution); - - prest.executeUpdate(); - - con.commit(); - - // TODO: why... WHY!? We had the user id from the beginning! We just inserted it! - // Also if there are two users with same first and last name we might get - // the wrong id! JUST WHY!!!!!? - sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + "?" - + " and Vorname like " + "?" + ";"; - - prest = con.prepareStatement(sql); - prest.setString(1, lastname); - prest.setString(2, firstname); - - ResultSet rs = prest.executeQuery(); - con.commit(); - rs.next(); - String userid = rs.getString("userID"); - con.close(); - return userid; - } else { - ret.first(); - String userid = ret.getString("userID"); - con.close(); - return userid; - } - - } catch (SQLException e) { - - log.info("Failed to setPerson."); - e.printStackTrace(); - } - return "-1"; - } - - public boolean writeNewImageData(String pk_person, boolean license, boolean internet, String imagename, - String desc, String imagePath, long filesize, String shareMode, String pk_os, String imageId) { - - DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - - int internet_bol = 0; - int license_bol = 0; - if (internet == true) { - internet_bol = 1; - } - if (license == true) { - license_bol = 1; - } - - try { - Connection con = getConnection(); - - // create a UUID - - String sql = "INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES(" - + "?" // GUID_imageID - + ",1," // imageVersion - + "?" // image_name - + "," + "?" // image_description - + "," + "?" // image_path - + ",'" - + formatter.format(new Date()) // image_lastCall - + "','" + formatter.format(new Date()) // image_create_time - + "','" + formatter.format(new Date()) // image_update_time - + "'," + "?" // image_owner - + "," + "?" // image_change_by - + ",'" + formatter.format(new Date()) // rec_create_time - + "','" + formatter.format(new Date()) // rec_change_time - + "'," + "?" // rec_owner - + "," + "?" // rec_change_by - + "," + "?" // content_operatingSystem - + ",1" // status_isCompressed - + ",1" // status_isSecure - + ",1" // status_isOptimzed - + ",1" // status_isValid - + ",1" // status_isReady - + ",0" // status_isDeleted - + ",0," // status_isLastOfficialVersion - + "?" // cond_hasLicenseRestriction - + "," + "?" // cond_hasInternetRestriction - + "," + "?" // cond_minRAM - + "," + "?" // cond_minCPUs - + ",0" // image_isTemplate - + "," + "?" // image_filesize - + "," + "?" // shareMode - + ");"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, imageId); - prest.setString(2, imagename); - prest.setString(3, desc); - prest.setString(4, imagePath); - prest.setString(5, pk_person); - prest.setString(6, pk_person); - prest.setString(7, pk_person); - prest.setString(8, pk_person); - prest.setString(9, pk_os); - prest.setInt(10, license_bol); - prest.setInt(11, internet_bol); - prest.setLong(12, 2); - prest.setLong(13, 2); - prest.setLong(14, filesize); - prest.setString(15, shareMode); - - prest.executeUpdate(); - con.commit(); - con.close(); - } catch (SQLException e) { - log.info("Failed to insert new image into DB", e); - return false; - } - return true; - - } - - public List getImageListPermissionWrite(String userID) { - - Connection con = getConnection(); - - List list = new ArrayList(); - try { - - String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = " - + "?" - + " AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - - ResultSet res = prest.executeQuery(); - con.commit(); - while (res.next()) { - list.add(new Image(res.getString("GUID_imageID"), res.getString("imageVersion"), - res.getString("image_name"), res.getString("cond_hasLicenseRestriction"), - res.getString("name") + " " + res.getString("architecture") + " bit", - res.getString("lecture"), res.getString("image_update_time"), res.getString("user"), - res.getString("image_isTemplate"), res.getString("image_description"), - res.getString("image_filesize"))); - } - con.close(); - } catch (SQLException e) { - - e.printStackTrace(); - // TODO: Shouldn't we return null or something that gives the client a chance - // to detect the failure and notify the user? - } - return list; - } - - public List getImageListPermissionRead(String userID) { - - Connection con = getConnection(); - - List list = new ArrayList(); - try { - - String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = " - + "?" - + " AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - - ResultSet res = prest.executeQuery(); - con.commit(); - while (res.next()) { - list.add(new Image(res.getString("GUID_imageID"), res.getString("imageVersion"), - res.getString("image_name"), res.getString("cond_hasLicenseRestriction"), - res.getString("name") + " " + res.getString("architecture") + " bit", - res.getString("lecture"), res.getString("image_update_time"), res.getString("user"), - res.getString("image_isTemplate"), res.getString("image_description"), - res.getString("image_filesize"))); - } - con.close(); - } catch (SQLException e) { - - e.printStackTrace(); - } - return list; - } - - // no prepared statement to do here - public List getImageList(String userID) { - - Connection con = getConnection(); - Statement stm; - - List list = new ArrayList(); - try { - - stm = con.createStatement(); - - // ResultSet - ResultSet res = stm.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID ORDER BY vl.image_name;"); - - while (res.next()) { - list.add(new Image(res.getString("GUID_imageID"), res.getString("imageVersion"), - res.getString("image_name"), res.getString("cond_hasLicenseRestriction"), - res.getString("name") + " " + res.getString("architecture") + " bit", - res.getString("lecture"), res.getString("image_update_time"), res.getString("user"), - res.getString("image_isTemplate"), res.getString("image_description"), - res.getString("image_filesize"))); - } - con.close(); - } catch (SQLException e) { - - e.printStackTrace(); - } - return list; - } - - public List getImageListPermissionLink(String userID) { - - Connection con = getConnection(); - - List list = new ArrayList(); - try { - - String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = " - + "?" - + " AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - - ResultSet res = prest.executeQuery(); - con.commit(); - while (res.next()) { - list.add(new Image(res.getString("GUID_imageID"), res.getString("imageVersion"), - res.getString("image_name"), res.getString("cond_hasLicenseRestriction"), - res.getString("name") + " " + res.getString("architecture") + " bit", - res.getString("lecture"), res.getString("image_update_time"), res.getString("user"), - res.getString("image_isTemplate"), res.getString("image_description"), - res.getString("image_filesize"))); - } - con.close(); - } catch (SQLException e) { - - e.printStackTrace(); - } - return list; - } - - // TODO: OK this method is pretty much the tenth copy&paste version of the stuff above. Maybe refactor something here? - public List getImageListPermissionAdmin(String userID) { - Connection con = getConnection(); - - List list = new ArrayList(); - try { - - String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = " - + "?" - + " AND pmi.image_admin = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - - ResultSet res = prest.executeQuery(); - con.commit(); - while (res.next()) { - list.add(new Image(res.getString("GUID_imageID"), res.getString("imageVersion"), - res.getString("image_name"), res.getString("cond_hasLicenseRestriction"), - res.getString("name") + " " + res.getString("architecture") + " bit", - res.getString("lecture"), res.getString("image_update_time"), res.getString("user"), - res.getString("image_isTemplate"), res.getString("image_description"), - res.getString("image_filesize"))); - } - con.close(); - } catch (SQLException e) { - - e.printStackTrace(); - } - return list; - }// end getImageListPermissionAdmin - - // no prepared statement to do here - public List getImageListAllTemplates() { - Connection con = getConnection(); - Statement stm; - - List list = new ArrayList(); - try { - - stm = con.createStatement(); - - // ResultSet - ResultSet res = stm.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.image_description, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE vl.image_isTemplate=1 AND vl.content_operatingSystem=os.operatingSystemID AND vl.image_owner=u.userID ORDER BY vl.image_name;"); - - while (res.next()) { - list.add(new Image(res.getString("GUID_imageID"), res.getString("imageVersion"), - res.getString("image_name"), res.getString("cond_hasLicenseRestriction"), - res.getString("name") + " " + res.getString("architecture") + " bit", - res.getString("lecture"), res.getString("image_update_time"), res.getString("user"), - res.getString("image_isTemplate"), res.getString("image_description"), - res.getString("image_filesize"))); - } - con.close(); - } catch (SQLException e) { - - e.printStackTrace(); - } - return list; - }// end getImageListAllTemplates - - public List getLectureListPermissionRead(String userID) { - List list = new ArrayList(); - - try { - Connection con = getConnection(); - - String sql = "SELECT DISTINCT l.lectureID, l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user " - + "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u " - + "WHERE u.userID = l.admin_owner AND pml.rec_read=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.userID = ? ORDER BY l.name;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - - ResultSet res = prest.executeQuery(); - con.commit(); - while (res.next()) { - - list.add(new Lecture(res.getString("lectureID"), res.getString("name"), - res.getString("isActive"), res.getString("startTime"), res.getString("endTime"), - res.getString("lastUsed"), res.getString("description"), res.getString("image_name"), - res.getString("user"))); - + public static MysqlConnection getConnection() { + MysqlConnection con; + for (;;) { + con = pool.poll(); + if (con == null) + break; + if (!con.isValid()) { + con.release(); + continue; } - con.close(); - return list; - } catch (SQLException e) { - - log.info("Failed to getLectureListPermissionRead."); - e.printStackTrace(); - } - return list; - }// end getLectureListPermissionRead - - public List getLectureListPermissionWrite(String userID) { - List list = new ArrayList(); - - try { - Connection con = getConnection(); - - String sql = "SELECT DISTINCT l.lectureID, l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user " - + "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u " - + "WHERE pml.userID=" - + "?" - + " AND u.userID = l.admin_owner AND pml.rec_write=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID ORDER BY l.name;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - - ResultSet res = prest.executeQuery(); - con.commit(); - while (res.next()) { - - list.add(new Lecture(res.getString("lectureID"), res.getString("name"), - res.getString("isActive"), res.getString("startTime"), res.getString("endTime"), - res.getString("lastUsed"), res.getString("description"), res.getString("image_name"), - res.getString("user"))); - - } - con.close(); - return list; - } catch (SQLException e) { - - log.info("Failed to getLectureListPermissionWrite."); - e.printStackTrace(); - } - return list; - }// end getLectureListPermissionWrite - - public List getLectureListPermissionAdmin(String userID) { - List list = new ArrayList(); - - try { - Connection con = getConnection(); - - String sql = "SELECT DISTINCT l.lectureID, l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user " - + "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u " - + "WHERE u.userID = l.admin_owner AND pml.rec_admin=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.userID= ? ORDER BY l.name;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - - ResultSet res = prest.executeQuery(); - con.commit(); - while (res.next()) { - - list.add(new Lecture(res.getString("lectureID"), res.getString("name"), - res.getString("isActive"), res.getString("startTime"), res.getString("endTime"), - res.getString("lastUsed"), res.getString("description"), res.getString("image_name"), - res.getString("user"))); - - } - con.close(); - return list; - } catch (SQLException e) { - - log.info("Failed to getLectureListPermissionAdmin."); - e.printStackTrace(); + if (!busyConnections.add(con)) + throw new RuntimeException("Tried to hand out a busy connection!"); + return con; } - return list; - }// end getLectureListPermissionAdmin - - // No prepared statement to do here - public List getLectureList() { - List list = new ArrayList(); - try { - Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet res = stm.executeQuery("SELECT l.lectureID, l.name, l.isActive,l.startTime,l.endTime,l.lastUsed,l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.m_user u WHERE i.GUID_imageID=l.imageID and l.admin_owner=u.userID ORDER BY l.name;"); - - while (res.next()) { - - list.add(new Lecture(res.getString("lectureID"), res.getString("name"), - res.getString("isActive"), res.getString("startTime"), res.getString("endTime"), - res.getString("lastUsed"), res.getString("description"), res.getString("image_name"), - res.getString("user"))); - - } - con.close(); - return list; - } catch (SQLException e) { - - log.info("Failed to getLectureList."); - e.printStackTrace(); + // No pooled connection + if (busyConnections.size() > 20) { + LOGGER.warn("Too many open MySQL connections. Possible connection leak!"); + return null; } - return null; - } - - // No prepared statement to do here - public List getAllOS() { - - // if(authenticated()) - // { - List list = new ArrayList<>(); try { - Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet rs = stm.executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem ORDER BY name, architecture;"); - while (rs.next()) { - list.add(rs.getString("name") + " " + rs.getString("architecture") + " bit"); - } - return list; - + // Create fresh connection + Connection rawConnection = DriverManager.getConnection(Configuration.getDbUri(), + Configuration.getDbUsername(), Configuration.getDbPassword()); + // By convention in our program we don't want auto commit + rawConnection.setAutoCommit(false); + // Wrap into our proxy + con = new MysqlConnection(rawConnection); + // Keep track of busy mysql connection + if (!busyConnections.add(con)) + throw new RuntimeException("Tried to hand out a busy connection!"); + return con; } catch (SQLException e) { - - log.info("Failed to getAllOS."); - e.printStackTrace(); + LOGGER.info("Failed to connect to local mysql server", e); } - return null; - - // } - // return null; } - public Map getPersonData(String Vorname, String Nachname) { - Map map = new HashMap(); - try { - Connection con = getConnection(); - - String sql = "SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like " - + "?" + " and u.Vorname like " + "?" + " and u.institution=i.institutionID;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, Vorname); - prest.setString(2, Nachname); - - ResultSet rs = prest.executeQuery(); - con.commit(); - while (rs.next()) { - - map.put("mail", rs.getString("mail")); - map.put("Nachname", rs.getString("Nachname")); - map.put("Vorname", rs.getString("Vorname")); - map.put("Hochschule", rs.getString("name")); - - } - con.close(); - return map; - } catch (SQLException e) { - - log.info("Failed to getPersonData."); - e.printStackTrace(); - } - return null; - }// end getPersonData - - public Map getPersonData(String userID) { - Map map = new HashMap(); - try { - Connection con = getConnection(); - - String sql = "SELECT userID, loginName, Nachname, Vorname, mail, m_institution.name as institution FROM bwLehrpool.m_user, m_institution WHERE userID=? AND m_user.institution=m_institution.institutionID;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - - ResultSet rs = prest.executeQuery(); - con.commit(); - while (rs.next()) { - map.put("userID", rs.getString("userID")); - map.put("loginName", rs.getString("loginName")); - map.put("Nachname", rs.getString("Nachname")); - map.put("Vorname", rs.getString("Vorname")); - map.put("mail", rs.getString("mail")); - map.put("institution", rs.getString("institution")); - - } - con.close(); - return map; - } catch (SQLException e) { - log.info("Failed to getPersonData with userID='" + userID + "'."); - e.printStackTrace(); - } - return null; - }// end getPersonData - - public Map getItemOwner(String itemID) { - - Map map = new HashMap(); - Connection con = getConnection(); - PreparedStatement prest; - String userID; - - try { - // find out if the itemID belongs to an image - String sql = "SELECT image_owner FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID=?;"; - prest = con.prepareStatement(sql); - prest.setString(1, itemID); - - ResultSet rs = prest.executeQuery(); - while (rs.next()) { - map.put("userID", rs.getString("image_owner")); - } - - // if map is empty, then itemID does not belong to an image -> must - // be a lecture - // TODO: Errr what? Why? Trial and error until we find the right table? - if (map.isEmpty() == true) { - // check if itemID belongs to a lecture - sql = "SELECT admin_owner FROM bwLehrpool.m_VLData_lecture WHERE lectureID=?;"; - prest = con.prepareStatement(sql); - prest.setString(1, itemID); - - rs = prest.executeQuery(); - while (rs.next()) { - map.put("userID", rs.getString("admin_owner")); - } - } - - // if map is still empty, then the itemID could not be found - if (map.isEmpty() == true) { - log.error("Failed to getItemOwner for item '" + itemID + "'."); - } else { - - // now that we have the userID for this item, get the user - // information and put it in the map - userID = map.get("userID"); // extract the userID - map = getPersonData(userID); // get all the rest of his - // information - - // return this map with all the infos - return map; - - } - - } catch (SQLException e) { - e.printStackTrace(); - } - - // something went horribly wrong... - return null; - }// end getItemOwner - - public int setLectureData(String pk_person, String pk_image, int imageversion, String name, String desc, - String shortdesc, String start, String end, boolean isactive, String lectureID) { - - DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - int active_bol = 0; - - if (isactive == true) { - active_bol = 1; - } - - try { - Connection con = getConnection(); - - String sql = "INSERT INTO `bwLehrpool`.`m_VLData_lecture`(`lectureID`,`name`,`isActive`,`startTime`,`endTime`,`lastUsed`,`shortDescription`,`description`,`imageID`,`imageVersion`,`admin_createTime`,`admin_changeTime`,`admin_owner`,`admin_change_by`)VALUES(" - + "?" - + "," - + "?" - + "," - + "?" - + "," - + "?" - + "," - + "?" - + ",'" - + formatter.format(new Date()) - + "'," - + "?" - + "," - + "?" - + "," - + "?" - + "," - + "?" - + ",'" - + formatter.format(new Date()) - + "','" - + formatter.format(new Date()) - + "'," - + "?" - + "," - + "?" + ");"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, lectureID); - prest.setString(2, name); - prest.setInt(3, active_bol); - prest.setString(4, start); - prest.setString(5, end); - prest.setString(6, shortdesc); - prest.setString(7, desc); - prest.setString(8, pk_image); - prest.setInt(9, imageversion); - prest.setString(10, pk_person); - prest.setString(11, pk_person); - - prest.executeUpdate(); - con.commit(); - - con.close(); - } catch (SQLException e) { - - log.info("Failed to setLectureData."); - e.printStackTrace(); - } - return 0; - - } - - public Map getImageIDandVersion(String id) { - Map map = new HashMap(); - try { - Connection con = getConnection(); - - String sql = "SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = ?"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, id); - - ResultSet image = prest.executeQuery(); - con.commit(); - while (image.next()) { - map.put("GUID", image.getString("GUID_imageID")); - map.put("version", String.valueOf(image.getString("imageVersion"))); - - } - con.close(); - return map; - } catch (SQLException e) { - - log.info("Failed to getImageIDandVersion."); - e.printStackTrace(); - } - return null; - } - - public Map getImageData(String id, String version) { - Map map = new HashMap(); - try { - Connection con = getConnection(); - - String sql = "SELECT u.mail, i.GUID_imageID, i.imageVersion, i.image_name,i.image_description,i.image_update_time,i.cond_hasInternetRestriction,i.cond_hasLicenseRestriction, i.cond_minCPUs,i.cond_minRAM, i.image_syncMode,i.image_filesize,i.image_path, i.image_isTemplate,os.GuestOS, concat(u.Nachname,' ',u.Vorname) as Owner FROM bwLehrpool.m_VLData_imageInfo i,bwLehrpool.m_user u,bwLehrpool.m_operatingSystem os where i.image_owner=u.userID and i.content_operatingSystem=os.operatingSystemID and GUID_imageID = ? and imageVersion = ? ;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, id); - prest.setString(2, version); - - ResultSet data = prest.executeQuery(); - con.commit(); - while (data.next()) { - // TODO: This is just a horrible idea. Make a typo anywhere for the key, it still compiles but doesn't work. - // Somebody was too lazy to create a proper struct/class here.... - map.put("name", data.getString("image_name")); - map.put("internet", data.getString("cond_hasInternetRestriction")); - map.put("license", data.getString("cond_hasLicenseRestriction")); - map.put("cpu", data.getString("cond_minCPUs")); - map.put("ram", data.getString("cond_minRAM")); - map.put("shareMode", data.getString("image_syncMode")); - map.put("filesize", data.getString("image_filesize")); - map.put("path", data.getString("image_path")); - map.put("template", data.getString("image_isTemplate")); - map.put("os", data.getString("GuestOS")); - map.put("owner", data.getString("Owner")); - map.put("lastupdate", data.getString("image_update_time")); - map.put("desc", data.getString("image_description")); - map.put("id", data.getString("GUID_imageID")); - map.put("version", data.getString("imageVersion")); - map.put("mail", data.getString("mail")); - - } - con.close(); - return map; - } catch (SQLException e) { - - log.info("Failed to getImageData."); - e.printStackTrace(); - } - return null; - } - - public boolean updateImageData(String newName, String desc, boolean license, boolean internet, - String imageId, String shareMode, String ospk) { - try { - Connection con = getConnection(); - DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - - int internet_bol = 0; - int license_bol = 0; - - if (internet == true) { - internet_bol = 1; - } - - if (license == true) { - license_bol = 1; - } - - String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET " + "imageVersion = " + "?" + ", " - + "image_name = " + "?" + ",image_description = " + "?" - + " ,image_update_time = '" - + formatter.format(new Date()) - + "' ,rec_change_time = '" - + formatter.format(new Date()) - + "' ,cond_hasLicenseRestriction = " - + "?" - + " ,cond_hasInternetRestriction = " - + "?" - + " ,cond_minRAM = " - + "?" - + " ,cond_minCPUs = " - + "?" - + " ,image_isTemplate = " - + "0 " - + ",content_operatingSystem = " - + "?" - + ",image_syncMode = " - + "?" - + " WHERE GUID_imageID = " + "?" + " AND imageVersion = " + "?" + ";"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setInt(1, 1); - prest.setString(2, newName); - prest.setString(3, desc); - prest.setInt(4, license_bol); - prest.setInt(5, internet_bol); - prest.setLong(6, 2); - prest.setLong(7, 2); - prest.setString(8, ospk); - prest.setString(9, shareMode); - prest.setString(10, imageId); - prest.setString(11, "1"); - - prest.executeUpdate(); - - con.commit(); // needed, though executeUpdate()! - - // con.commit(); - con.close(); - return true; - } catch (SQLException e) { - - log.info("Failed to UpdateImageData."); - e.printStackTrace(); - } - return false; - } - - public boolean deleteImage(String id, String version) { - - try { - Connection con = getConnection(); - - // delete the image record - String sql = "DELETE FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = ? AND imageVersion = ?;"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, id); - prest.setString(2, version); - prest.executeUpdate(); - - // delete all permission records for this image - sql = "DELETE FROM bwLehrpool.pm_VLData_image WHERE GUID_imageID = ?"; - prest = con.prepareStatement(sql); - prest.setString(1, id); - prest.executeUpdate(); - - con.commit(); - con.close(); - return true; - - } catch (SQLException e) { - - log.info("Failed to deleteImage and permissions."); - e.printStackTrace(); - } - - return false; - } - - public int updateLectureData(String pk_image, String imageversion, String name, String newName, - String desc, String shortdesc, String start, String end, boolean isactive, String id) { - - DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - int active_bol = 0; - - if (isactive == true) { - active_bol = 1; - } - try { - Connection con = getConnection(); - - String sql = "UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = " + "?" + ",isActive = " + "?" - + ",startTime = " + "?" + ",endTime = " + "?" + ",description = " + "?" + ",imageID = " - + "?" + ",imageVersion = " + "?" + ",admin_changeTime = '" + formatter.format(new Date()) - + "'WHERE `lectureID` = ?;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, newName); - prest.setInt(2, active_bol); - prest.setString(3, start); - prest.setString(4, end); - prest.setString(5, desc); - prest.setString(6, pk_image); - prest.setString(7, imageversion); - prest.setString(8, id); - - prest.executeUpdate(); - con.commit(); - con.close(); - // log.info("Succeeded to updateLectureData."); - - } catch (SQLException e) { - - log.info("Failed to updateLectureData."); - e.printStackTrace(); - } - - return 0; - - } - - public boolean connectedToLecture(String id, String version) { - - try { - Connection con = getConnection(); - - String sql = "SELECT lectureID FROM " - + "bwLehrpool.m_VLData_lecture WHERE imageID = ? AND imageVersion = ?;"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, id); - prest.setString(2, version); - - ResultSet rs = prest.executeQuery(); - con.commit(); - boolean ret = rs.first(); - con.close(); - return ret; - - } catch (SQLException e) { - - log.info("Failed to execute method connectedToLecture."); - e.printStackTrace(); - } - - return false; - - } - - public boolean deleteLecture(String id) { - - try { - Connection con = getConnection(); - - // delete the lecture record - String sql = "DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = ?;"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, id); - - prest.executeUpdate(); - - // delete all permission records for this lecture - sql = "DELETE FROM bwLehrpool.pm_VLData_lecture WHERE lectureID = ?;"; - prest = con.prepareStatement(sql); - prest.setString(1, id); - - prest.executeUpdate(); - - con.commit(); - con.close(); - return true; - } catch (SQLException e) { - - log.info("Failed to deleteLecture and permissions."); - e.printStackTrace(); - } - - return false; - } - - public String getFile(String imageid, String imageversion) { - String path = null; - Connection con = null; - - try { - con = getConnection(); - - String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = " + "?" - + " AND imageVersion = " + "?" + ";"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, imageid); - prest.setString(2, imageversion); - - ResultSet rs = prest.executeQuery(); - con.commit(); - - if (rs.next()) - path = rs.getString("image_path"); - - return path; - - } catch (SQLException e) { - log.info("Failed to getFile.", e); - } finally { - Util.safeClose(con); - } - - return null; - } - - public Map getDeleteXMLData(String id) { - Map map = new HashMap(); - try { - Connection con = getConnection(); - - String sql = "SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID = ?;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, id); - - ResultSet rs = prest.executeQuery(); - con.commit(); - rs.next(); - - map.put("date", - rs.getString("admin_createTime").replace(" ", "").replace("-", "").replace(":", "")); - map.put("name", rs.getString("name")); - - con.close(); - return map; - - } catch (SQLException e) { - - log.info("Failed to getDeleteXMLData."); - e.printStackTrace(); - } - - return null; - } - - public int UpdateImagePath(String name) { - try { - Connection con = getConnection(); - - String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET image_path = " + "?" // prod/+name - + " WHERE image_path = " + "?" // temp/+name - + ";"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, "prod/" + name); - prest.setString(2, "temp/" + name); - - prest.executeUpdate(); - - con.commit(); - con.close(); - // log.info("Succesfully updated image path"); - return 0; - } catch (SQLException e) { - - log.info("Failed to UpdateImagePath."); - e.printStackTrace(); - } - return -1; - } - - public List getAllUniversities() { - List list = new ArrayList<>(); - try { - Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet rs = stm.executeQuery("SELECT name FROM bwLehrpool.m_institution;"); - while (rs.next()) { - list.add(rs.getString("name")); - } - return list; - - } catch (SQLException e) { - - log.info("Failed to getAllUniversities."); - e.printStackTrace(); - } - - return null; - - } - - public String getOSpk(String os, String architecture) { - try { - Connection con = getConnection(); - // log.info("Getting PrimaryKey for OS: " + os + " architecture: " + - // architecture); - - String sql = "SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like ? and architecture like ?;"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, os); - prest.setString(2, architecture); - - ResultSet rs = prest.executeQuery(); - con.commit(); - rs.first(); - String pkos = rs.getString("operatingSystemID"); - // log.info(" - Key is " + pkos); - con.close(); - return pkos; - - } catch (SQLException e) { - - log.info("Failed to getOSpk."); - e.printStackTrace(); - } - - return null; - } - - public String getOS(String pk) { - try { - Connection con = getConnection(); - // log.info(new Date() + - // " - OS: "+os+" architecture: "+architecture); - String sql = "SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = ?;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, pk); - - ResultSet rs = prest.executeQuery(); - con.commit(); - rs.first(); - String os = rs.getString("name") + " " + rs.getString("architecture"); - con.close(); - return os; - - } catch (SQLException e) { - - log.info("Failed to getOS."); - e.printStackTrace(); - } - - return null; - } - - public Map getLectureData(String id) { - Map map = new HashMap(); - try { - Connection con = getConnection(); - - String sql = "select l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_path, l.imageVersion, concat(u.Nachname,' ',u.Vorname) as owner from m_VLData_lecture l, m_user u, m_VLData_imageInfo i where l.admin_owner=u.userID and l.imageID=i.GUID_imageID and lectureID= ?"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, id); - - ResultSet data = prest.executeQuery(); - con.commit(); - while (data.next()) { - map.put("name", data.getString("name")); - map.put("isActive", data.getString("isActive")); - map.put("startTime", data.getString("startTime")); - map.put("endTime", data.getString("endTime")); - map.put("lastUsed", data.getString("lastUsed")); - map.put("desc", data.getString("description")); - map.put("imagepath", data.getString("image_path")); - map.put("imageversion", data.getString("imageVersion")); - map.put("owner", data.getString("Owner")); - - } - con.close(); - return map; - } catch (SQLException e) { - - log.info("Failed to getLectureData."); - e.printStackTrace(); - } - return null; - } - - public boolean checkUser(String username) { - - boolean ret = false; - try { - Connection con = getConnection(); - - String sql = "Select * from m_user where loginName like ?"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, username); - - ResultSet rs = prest.executeQuery(); - con.commit(); - if (rs.next()) { - ret = true; - log.info("Checking user: " + rs.getString("loginName") + " exists."); - } else { - ret = false; - log.info("Checking user: " + rs.getString("loginName") + " does not exist."); - } - con.close(); - - } catch (SQLException e) { - - e.printStackTrace(); - } - return ret; - } - - public int getRoleID(String role) { - - try { - Connection con = getConnection(); - - String sql = "SELECT roleID FROM bwLehrpool.m_role where name like ?;"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, role); - - ResultSet rs = prest.executeQuery(); - con.commit(); - rs.first(); - - return rs.getInt("roleID"); - - } catch (SQLException e) { - - log.info("Failed to getRoleID."); - e.printStackTrace(); - } - return -1; - } - - public String getLectureID(String name) { - String id = null; - try { - Connection con = getConnection(); - - String sql = "SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like ?;"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, name); - - ResultSet lecture = prest.executeQuery(); - con.commit(); - while (lecture.next()) { - id = lecture.getString("lectureID"); - - } - con.close(); - return id; - } catch (SQLException e) { - - log.info("Failed to getLectureID."); - e.printStackTrace(); - } - return id; - } - - // get all users from satellite -- temp, tb replaced by allUsers bwLehrpool - // does -NOT- return the user himself - // TODO prepared statement - public List getAllOtherSatelliteUsers(List userID) { - Connection con = getConnection(); - Statement stm = null; - ResultSet res = null; - - List list = new ArrayList(); - - try { - stm = con.createStatement(); - } catch (SQLException e) { - - e.printStackTrace(); - } - String query = "SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID NOT IN ('" - + userID.get(0) + ""; - - for (int i = 1; i < userID.size(); i++) { - query = query + "','" + userID.get(i); - } - query = query + "') ORDER BY Nachname ASC;"; - - try { - res = stm.executeQuery(query); - } catch (SQLException e1) { - - e1.printStackTrace(); - } - - try { - while (res.next()) { - // fill the list with users - permissions are all false because - // the image is new - list.add(new Person(res.getString("userID"), res.getString("Nachname"), - res.getString("Vorname"), res.getString("mail"), false, false, false, false, false, - false, false)); - } - } catch (SQLException e) { - - e.printStackTrace(); - } - return list; - }// end getAllOtherSatelliteUsers() - - // get the permissions of an image for all other registered users -EXCEPT- - // the logged on user - public List getPermissionForUserAndImage(String userID, String imageID) { - - Connection con = getConnection(); - ResultSet res = null; - List list = new ArrayList(); - - try { - - String sql = "SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, u.mail, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!= " - + "?" - + " AND pmi.GUID_imageID= " - + "?" - + " AND pmi.userID=u.userID ORDER BY u.Nachname ASC;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - prest.setString(2, imageID); - - res = prest.executeQuery(); - con.commit(); - - } catch (SQLException e) { - - e.printStackTrace(); - } - try { - boolean image_read, image_write, link_allowed, image_admin; - - while (res.next()) { - // reset old values and set the correct new values - image_read = false; - image_write = false; - link_allowed = false; - image_admin = false; - - // TODO: Are those really non-numeric columns? If yes, keep in mind parseInt can throw an exception... - if (Integer.parseInt(res.getString("image_read")) != 0) { - image_read = true; - } - if (Integer.parseInt(res.getString("image_write")) != 0) { - image_write = true; - } - if (Integer.parseInt(res.getString("link_allowed")) != 0) { - link_allowed = true; - } - if (Integer.parseInt(res.getString("image_admin")) != 0) { - image_admin = true; - } - - // fill the list with users - permissions are all false because - // the image is new - list.add(new Person(res.getString("userID"), res.getString("Nachname"), - res.getString("Vorname"), res.getString("mail"), image_read, image_write, - link_allowed, image_admin, false, // lecture_read - false, // lecture_write - false) // lecture_admin - ); - // System.out.println(res.getString("Nachname")+" "+Boolean.parseBoolean(res.getString("image_read"))+" "+Boolean.parseBoolean(res.getString("image_write"))+" "+Boolean.parseBoolean(res.getString("image_admin"))); - } - } catch (SQLException e) { - - e.printStackTrace(); - } - return list; - }// end getPermissionFOrUserAndImage() - - // get the permissions of a lecture for all other registered users -EXCEPT- - // the logged on user - public List getPermissionForUserAndLecture(String userID, String lectureID) { - - Connection con = getConnection(); - // Statement stm = null; - ResultSet res = null; - List list = new ArrayList(); - - try { - - String sql = "SELECT DISTINCT pml.lectureID, pml.userID, u.userID, u.Nachname, u.Vorname, u.mail, pml.rec_read, pml.rec_write, pml.rec_admin " - + "FROM bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u WHERE pml.userID!= ? AND pml.lectureID= ? AND pml.userID=u.userID ORDER BY u.Nachname ASC;"; - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - prest.setString(2, lectureID); - - res = prest.executeQuery(); - con.commit(); - - } catch (SQLException e) { - - e.printStackTrace(); - } - try { - boolean lecture_read, lecture_write, lecture_admin; - - while (res.next()) { - // reset old values and set the correct new values - lecture_read = false; - lecture_write = false; - lecture_admin = false; - - if (Integer.parseInt(res.getString("rec_read")) != 0) { - lecture_read = true; - } - if (Integer.parseInt(res.getString("rec_write")) != 0) { - lecture_write = true; - } - if (Integer.parseInt(res.getString("rec_admin")) != 0) { - lecture_admin = true; - } - - // fill the list with users - permissions are all false because - // the image is new - list.add(new Person(res.getString("userID"), res.getString("Nachname"), - res.getString("Vorname"), res.getString("mail"), false, // image read - false, // image write - false, // image link - false, // image admin - lecture_read, // lecture_read - lecture_write, // lecture_write - lecture_admin) // lecture_admin - ); - // System.out.println(res.getString("Nachname")+" "+Boolean.parseBoolean(res.getString("image_read"))+" "+Boolean.parseBoolean(res.getString("image_write"))+" "+Boolean.parseBoolean(res.getString("image_admin"))); - } - } catch (SQLException e) { - - e.printStackTrace(); - } - return list; - }// end getPermissionForUserAndLecture - - public int setImageRights(String pk_person, String pk_image, int role, int read, int write, - int linkallowed, int admin) { - - try { - Connection con = getConnection(); - // Statement stm = con.createStatement(); - - String uid = UUID.randomUUID().toString(); - - String sql = "INSERT INTO bwLehrpool.pm_VLData_image(GUID,GUID_imageID,roleID,userID,image_read,image_write,image_admin,link_allowed)VALUES" - + "(?,?,?,?,?,?,?,?)"; - - PreparedStatement prest = con.prepareStatement(sql); - - prest.setString(1, uid); - prest.setString(2, pk_image); - prest.setInt(3, role); - prest.setString(4, pk_person); - prest.setInt(5, read); - prest.setInt(6, write); - prest.setInt(7, admin); - prest.setInt(8, linkallowed); - - prest.executeUpdate(); - - con.commit(); - con.close(); - - } catch (SQLException e) { - - log.info("Failed to setImageRights."); - e.printStackTrace(); - } - return 0; - }// end setImageRights - - public boolean writeAdditionalImageRights(String imageID, String userID, boolean isRead, boolean isWrite, - boolean isLinkAllowed, boolean isAdmin) { - boolean success = true; - - String uid = UUID.randomUUID().toString(); - - Connection con = getConnection(); - - try { - - String sql = "INSERT INTO pm_VLData_image (" + "GUID, " + "GUID_imageID, " + "roleID, " - + "userID, " + "image_read, " + "image_write, " + "link_allowed," + "Image_admin)" - + "VALUES" + "(?,?,?,?,?,?,?,?)"; - - PreparedStatement prest = con.prepareStatement(sql); - - prest.setString(1, uid); - prest.setString(2, imageID); - prest.setInt(3, 1); - prest.setString(4, userID); - prest.setBoolean(5, isRead); - prest.setBoolean(6, isWrite); - prest.setBoolean(7, isLinkAllowed); - prest.setBoolean(8, isAdmin); - - prest.executeUpdate(); - - con.commit(); - con.close(); - - // log.info("Written additional image rights."); - - } catch (SQLException e) { - - e.printStackTrace(); - log.info("Failed to write additional image rights."); - } - return success; - }// end writeAdditionalImageRights - - public int setLectureRights(String pk_person, String pk_lecture, int role, int read, int write, int admin) { - - try { - Connection con = getConnection(); - // Statement stm = con.createStatement(); - - String uid = UUID.randomUUID().toString(); - String sql = "INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`userID`,`rec_read`,`rec_write`,`rec_admin`)VALUES" - + "(?,?,?,?,?,?)"; - - PreparedStatement prest = con.prepareStatement(sql); - - prest.setString(1, uid); - prest.setString(2, pk_lecture); - prest.setString(3, pk_person); - prest.setInt(4, read); - prest.setInt(5, write); - prest.setInt(6, admin); - - prest.executeUpdate(); - - con.commit(); - log.info("Written lecture rights for lecture '" + pk_lecture + "'."); - con.close(); - } catch (SQLException e) { - - log.info("Failed to setLectureRights."); - e.printStackTrace(); - } - return 0; - } // end setLectureRights - - public boolean writeAdditionalLectureRights(String lectureID, String userID, boolean isRead, - boolean isWrite, boolean isAdmin) { - boolean success = true; - - String uid = UUID.randomUUID().toString(); - Connection con = getConnection(); - - try { - - String sql = "INSERT INTO pm_VLData_lecture (GUID, lectureID, userID, rec_read, rec_write, rec_admin) VALUES" - + ("(?,?,?,?,?,?)"); - - PreparedStatement prest = con.prepareStatement(sql); - - prest.setString(1, uid); - prest.setString(2, lectureID); - prest.setString(3, userID); - prest.setBoolean(4, isRead); - prest.setBoolean(5, isWrite); - prest.setBoolean(6, isAdmin); - - // log.info("userdata: "+uid+" "+lectureID + " " + userID + " " + - // isRead + isWrite + isAdmin); - - prest.executeUpdate(); - - con.commit(); - con.close(); - // log.info("Written additional lecture rights for '"+ userID + - // "'."); - } catch (SQLException e) { - - e.printStackTrace(); - log.info("Failed to write additional lecture rights."); - } - return success; - }// end writeAdditionalLectureRights - - public void deleteAllAdditionalImagePermissions(String imageID, String userID) { - Connection con = getConnection(); - - try { - - String sql = "DELETE FROM pm_VLData_image WHERE GUID_imageID = ? AND userID != ?;"; - PreparedStatement prest = con.prepareStatement(sql); - - prest.setString(1, imageID); - prest.setString(2, userID); - - prest.executeUpdate(); - - } catch (SQLException e) { - - e.printStackTrace(); - } - - try { - - con.commit(); - - } catch (SQLException e) { - - e.printStackTrace(); - } - }// end deleteAllAdditionalImagePermissions - - public void deleteAllAdditionalLecturePermissions(String lectureID, String userID) { - Connection con = getConnection(); - - try { - - String sql = "DELETE FROM pm_VLData_lecture WHERE lectureID = ? AND userID != ?;"; - PreparedStatement prest = con.prepareStatement(sql); - - prest.setString(1, lectureID); - prest.setString(2, userID); - - prest.executeUpdate(); - } catch (SQLException e) { - e.printStackTrace(); - } - - try { - - con.commit(); - - } catch (SQLException e) { - - e.printStackTrace(); - } - }// end deleteAllAdditionalImagePermissions - - public List getAdditionalImageContacts(String imageID) { - - List list = new ArrayList<>(); - - Connection con = getConnection(); - try { - - String sql = "select distinct u.Nachname, u.Vorname, u.mail from bwLehrpool.pm_VLData_image img, bwLehrpool.m_user u where GUID_imageID = ? and image_write = '1' and img.userID = u.userID;"; - PreparedStatement prest = con.prepareStatement(sql); - - prest.setString(1, imageID); - - ResultSet rs = prest.executeQuery(); - con.commit(); - - while (rs.next()) { - list.add(rs.getString("Nachname") + "," + rs.getString("Vorname") + " "); - } - - } catch (SQLException e) { - - e.printStackTrace(); - } - - return list; - - } - - public String getOsNameForGuestOs(String guestOS) { - - Connection con = getConnection(); - ResultSet rs = null; - String sql = "Select name, architecture from bwLehrpool.m_operatingSystem where guestOS = ?"; - - try { - - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, guestOS); - rs = prest.executeQuery(); - con.commit(); - rs.next(); - - return rs.getString("name") + " " + rs.getString("architecture") + " bit"; - - } catch (SQLException e) { - - e.printStackTrace(); - } - - return null; - - } - - public String createRandomUUID() { - return UUID.randomUUID().toString(); - } - - public boolean userIsImageAdmin(String userID, String imageID) { - Connection con = getConnection(); - ResultSet rs = null; - String sql = "SELECT image_admin FROM bwLehrpool.pm_VLData_image WHERE userID= ? AND GUID_imageID=?"; - - log.info("stmt user is image admin: " - + "SELECT image_admin FROM bwLehrpool.pm_VLData_image WHERE userID= '" + userID - + "' AND GUID_imageID='" + imageID + "'"); - - try { - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - prest.setString(2, imageID); - rs = prest.executeQuery(); - con.commit(); - rs.next(); - - return rs.getBoolean("image_admin"); - - } catch (SQLException e) { - - e.printStackTrace(); - } - return false; - } - - public boolean userIsLectureAdmin(String userID, String lectureID) { - Connection con = getConnection(); - ResultSet rs = null; - String sql = "SELECT rec_admin FROM bwLehrpool.pm_VLData_lecture WHERE userID= ? AND lectureID=?"; - - try { - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, userID); - prest.setString(2, lectureID); - rs = prest.executeQuery(); - con.commit(); - rs.next(); - - return rs.getBoolean("rec_admin"); - - } catch (SQLException e) { - - e.printStackTrace(); - } - return false; - } - - public String getInstitutionByID(String institutionID, String errorParam) { - - Connection con = getConnection(); - ResultSet rs = null; - String sql = "SELECT name FROM bwLehrpool.m_institution WHERE institutionID = ?"; - - log.info("intitution ID is: " + institutionID); - - PreparedStatement prest; - try { - - prest = con.prepareStatement(sql); - prest.setString(1, institutionID); - - rs = prest.executeQuery(); - // con.commit(); - rs.next(); - return rs.getString("name"); - - } catch (SQLException e) { - // TODO Auto-generated catch block - e.printStackTrace(); - } - - return null; - + static void returnConnection(MysqlConnection connection) { + if (!busyConnections.remove(connection)) + throw new RuntimeException("Tried to return a mysql connection to the pool that was not taken!"); + pool.add(connection); } }// end class diff --git a/dozentenmodulserver/src/main/java/sql/models/DbImage.java b/dozentenmodulserver/src/main/java/sql/models/DbImage.java new file mode 100644 index 00000000..fe59dac8 --- /dev/null +++ b/dozentenmodulserver/src/main/java/sql/models/DbImage.java @@ -0,0 +1,41 @@ +package sql.models; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; + +import org.apache.log4j.Logger; +import org.openslx.bwlp.thrift.iface.ImageSummaryRead; +import org.openslx.bwlp.thrift.iface.UserInfo; + +import sql.MysqlConnection; +import sql.MysqlStatement; +import sql.SQL; + +public class DbImage { + + private static final Logger LOGGER = Logger.getLogger(DbImage.class); + + public static List getAllVisible(UserInfo user, List tagSearch) { + try (MysqlConnection connection = SQL.getConnection()) { + MysqlStatement stmt = connection.prepareStatement("SELECT" + + " i.imagebaseid, i.currentversionid, i.latestversionid, i.displayname," + + " i.osid, i.virtid, i.createtime, i.updatetime, i.ownerid, i.uploaderid," + + " i.sharemode, i.istemplate, i.canlinkdefault, i.candownloaddefault," + + " i.caneditdefault, i.canadmindefault," + + " cur.filesize, cur.isenabled, cur.isrestricted, cur.isvalid," + " lat.isprocessed" + + " FROM imagebase i" + + " LEFT JOIN imageversion cur ON (cur.imageversionid = i.currentversionid)" + + " LEFT JOIN imageversion lat ON (lat.imageversionid = i.latestversionid)"); + ResultSet rs = stmt.executeQuery(); + while (rs.next()) { + ImageSummaryRead entry = new ImageSummaryRead(); + } + return null; + } catch (SQLException e) { + LOGGER.error("Query failed in DbImage.getAllVisible()", e); + return null; + } + } + +} -- cgit v1.2.3-55-g7522