From 1bc83891c68ee269727e81a13cc70da698bcc7a7 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Tue, 2 Jun 2015 19:53:31 +0200 Subject: [server] Compiling again, still lots of stubs --- dozentenmodulserver/src/main/java/sql/SQL.java | 265 ++++++++++--------------- 1 file changed, 102 insertions(+), 163 deletions(-) (limited to 'dozentenmodulserver/src/main/java/sql') diff --git a/dozentenmodulserver/src/main/java/sql/SQL.java b/dozentenmodulserver/src/main/java/sql/SQL.java index 890e6e39..925ffb57 100644 --- a/dozentenmodulserver/src/main/java/sql/SQL.java +++ b/dozentenmodulserver/src/main/java/sql/SQL.java @@ -22,23 +22,26 @@ 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); - public Connection getConnection() { - // TODO: Connection pooling, better yet some abstraction layer for mysql, eg. like dalesbred in master-server + static { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) { - - e.printStackTrace(); + log.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("jdbc:mysql://" - + Configuration.config.getSql_connection() + "?user=" - + Configuration.config.getSql_user() + "&password=" + Configuration.config.getSql_pass() - + ""); + Connection con = DriverManager.getConnection(Configuration.getDbUri(), + Configuration.getDbUsername(), Configuration.getDbPassword()); con.setAutoCommit(false); return con; @@ -51,50 +54,6 @@ public class SQL { return null; } - public int writeFTPUser(String user, String pass) { - Statement stm; - try { - Connection con = getConnection(); - stm = con.createStatement(); - - int ret = stm - .executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('" - + user + "',SHA1('" + pass + "'),'10001','12345','" - + Configuration.config.getAbsolute_path() + "temp/');"); - con.commit(); - con.close(); - log.info("Created FTPUser " + user + " : " + pass + "."); - return ret; - } catch (SQLException e) { - - log.info("Failed to writeFTPUser."); - e.printStackTrace(); - } - return -1; - } - - public int DeleteUser(String user) { - try { - Connection con = getConnection(); - - String sql = "DELETE FROM `bwLehrpool`.`FtpUsers` where User like ?;"; - PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, user); - - int ret = prest.executeUpdate(); - - con.commit(); - con.close(); - log.info("FTPUser " + user + " deleted."); - return ret; - } catch (SQLException e) { - - log.info("Failed to DeleteUser " + user + "."); - e.printStackTrace(); - } - return -1; - } - // no prepared statement to do here public ResultSet getImage() { try { @@ -281,16 +240,11 @@ public class SQL { return "-1"; } - public boolean setImageData(String pk_person, boolean license, boolean internet, long cpu, long ram, - String imagename, String desc, String imagePath, long filesize, String shareMode, String pk_os, - String uid) { + 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"); - log.info("userID came as pk_person to SQL as: " + pk_person); - log.info("pk_os is: " + pk_os); - log.info("uid is: " + uid); - int internet_bol = 0; int license_bol = 0; if (internet == true) { @@ -339,7 +293,7 @@ public class SQL { + ");"; PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, uid); + prest.setString(1, imageId); prest.setString(2, imagename); prest.setString(3, desc); prest.setString(4, imagePath); @@ -350,20 +304,17 @@ public class SQL { prest.setString(9, pk_os); prest.setInt(10, license_bol); prest.setInt(11, internet_bol); - prest.setLong(12, ram); - prest.setLong(13, cpu); + prest.setLong(12, 2); + prest.setLong(13, 2); prest.setLong(14, filesize); prest.setString(15, shareMode); prest.executeUpdate(); con.commit(); - // con.commit(); con.close(); } catch (SQLException e) { - - log.info("Failed to setImageData."); - e.printStackTrace(); - // TODO: Yeah great - it failed, we log it, but we still return true.... + log.info("Failed to insert new image into DB", e); + return false; } return true; @@ -386,12 +337,12 @@ public class SQL { 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"))); + 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) { @@ -420,12 +371,12 @@ public class SQL { 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"))); + 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) { @@ -447,16 +398,15 @@ public class SQL { 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;"); + 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"))); + 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) { @@ -483,12 +433,12 @@ public class SQL { 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"))); + 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) { @@ -515,12 +465,12 @@ public class SQL { 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"))); + 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) { @@ -541,16 +491,15 @@ public class SQL { 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;"); + 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"))); + 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) { @@ -577,9 +526,9 @@ public class SQL { 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"), + 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"))); } @@ -612,9 +561,9 @@ public class SQL { 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"), + 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"))); } @@ -645,9 +594,9 @@ public class SQL { 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"), + 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"))); } @@ -667,14 +616,13 @@ public class SQL { 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;"); + 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"), + 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"))); } @@ -697,8 +645,7 @@ public class SQL { try { Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet rs = stm - .executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem ORDER BY name, architecture;"); + 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"); } @@ -974,14 +921,11 @@ public class SQL { return null; } - public int UpdateImageData(String name, String newName, String desc, String image_path, boolean license, - boolean internet, long cpu, long ram, String id, String version, boolean isTemplate, - long filesize, String shareMode, String ospk) { + 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"); - // Statement stm = con.createStatement(); - int newVersion = Integer.parseInt(version) + 1; int internet_bol = 0; int license_bol = 0; @@ -994,8 +938,8 @@ public class SQL { license_bol = 1; } - String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = " + "?" + ",image_name = " - + "?" + ",image_description = " + "?" + " ,image_path = " + "?" + String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET " + "imageVersion = " + "?" + ", " + + "image_name = " + "?" + ",image_description = " + "?" + " ,image_update_time = '" + formatter.format(new Date()) + "' ,rec_change_time = '" @@ -1009,32 +953,25 @@ public class SQL { + " ,cond_minCPUs = " + "?" + " ,image_isTemplate = " - // + isTemplate + "0 " + ",content_operatingSystem = " + "?" - + ",image_filesize = " - + "?" + ",image_syncMode = " + "?" - + " WHERE GUID_imageID = " - + "?" - + " AND imageVersion = " + "?" + ";"; + + " WHERE GUID_imageID = " + "?" + " AND imageVersion = " + "?" + ";"; PreparedStatement prest = con.prepareStatement(sql); - prest.setInt(1, newVersion); + prest.setInt(1, 1); prest.setString(2, newName); prest.setString(3, desc); - prest.setString(4, image_path); - prest.setInt(5, license_bol); - prest.setInt(6, internet_bol); - prest.setLong(7, ram); - prest.setLong(8, cpu); - prest.setString(9, ospk); - prest.setLong(10, filesize); - prest.setString(11, shareMode); - prest.setString(12, id); - prest.setString(13, version); + 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(); @@ -1042,13 +979,13 @@ public class SQL { // con.commit(); con.close(); - return 0; + return true; } catch (SQLException e) { log.info("Failed to UpdateImageData."); e.printStackTrace(); } - return -1; + return false; } public boolean deleteImage(String id, String version) { @@ -1183,9 +1120,11 @@ public class SQL { } public String getFile(String imageid, String imageversion) { + String path = null; + Connection con = null; try { - Connection con = getConnection(); + con = getConnection(); String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = " + "?" + " AND imageVersion = " + "?" + ";"; @@ -1197,15 +1136,15 @@ public class SQL { ResultSet rs = prest.executeQuery(); con.commit(); - rs.next(); - String path = rs.getString("image_path"); - con.close(); + if (rs.next()) + path = rs.getString("image_path"); + return path; } catch (SQLException e) { - - log.info("Failed to getFile."); - e.printStackTrace(); + log.info("Failed to getFile.", e); + } finally { + Util.safeClose(con); } return null; @@ -1486,8 +1425,8 @@ public class SQL { 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, + 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) { @@ -1550,9 +1489,9 @@ public class SQL { // 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 + 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 ); @@ -1611,8 +1550,8 @@ public class SQL { // 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 + 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 -- cgit v1.2.3-55-g7522