From 6c69c0971a81028c9a3f046b75e1eedb37f451f9 Mon Sep 17 00:00:00 2001 From: Michael Wilson Date: Wed, 29 Oct 2014 13:11:43 +0100 Subject: Ausgaben etwas verkürzt --- dozentenmodulserver/src/main/java/sql/SQL.java | 132 ++++++++++++++++--------- 1 file changed, 83 insertions(+), 49 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 a7ac4a8c..e1a1c1da 100644 --- a/dozentenmodulserver/src/main/java/sql/SQL.java +++ b/dozentenmodulserver/src/main/java/sql/SQL.java @@ -46,7 +46,7 @@ public class SQL { return con; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to return connection to Client."); + log.info("Failed to return connection to Client."); e.printStackTrace(); } return null; @@ -68,12 +68,12 @@ public class SQL { + "temp/');"); con.commit(); con.close(); - log.info(new Date() + " - created FTPUser " + user + " : " + pass + log.info("Created FTPUser " + user + " : " + pass + "."); return ret; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to writeFTPUser."); + log.info("Failed to writeFTPUser."); e.printStackTrace(); } return -1; @@ -90,11 +90,11 @@ public class SQL { + user + "';"); con.commit(); con.close(); - log.info(new Date() + " - FTPUser " + user + " deleted."); + log.info("FTPUser " + user + " deleted."); return ret; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to DeleteUser."); + log.info("Failed to DeleteUser."); e.printStackTrace(); } return -1; @@ -111,7 +111,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getImage."); + log.info("Failed to getImage."); e.printStackTrace(); } @@ -135,7 +135,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getPathOfImage."); + log.info("Failed to getPathOfImage."); e.printStackTrace(); } @@ -173,7 +173,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to setInstitution."); + log.info("Failed to setInstitution."); e.printStackTrace(); } return "-1"; @@ -231,7 +231,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to setPerson."); + log.info("Failed to setPerson."); e.printStackTrace(); } return "-1"; @@ -260,6 +260,7 @@ public class SQL { // create a UUID String uid = UUID.randomUUID().toString(); + /* log.info(" - Query is: " + "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('" + uid // GUID_imageID @@ -294,6 +295,7 @@ public class SQL { + ",'" + filesize // image_filesize + "','" + shareMode // shareMode + "');"); + */ stm.executeUpdate("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('" + uid // GUID_imageID @@ -332,7 +334,7 @@ public class SQL { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to setImageData."); + log.info("Failed to setImageData."); e.printStackTrace(); } return true; @@ -388,7 +390,6 @@ public class SQL { stm = con.createStatement(); // ResultSet - // res=stm.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, 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 as pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;"); ResultSet res = stm .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, 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 pmi.userID='" + userID @@ -426,7 +427,6 @@ public class SQL { stm = con.createStatement(); // ResultSet - // res=stm.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, 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 as pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;"); ResultSet res = stm .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, 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 pmi.userID='" + userID @@ -455,8 +455,39 @@ public class SQL { public List getImageListPermissionAdmin(String userID){ - //fill me - return null; + 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, 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 pmi.userID='" + + userID + + "' AND u.userID = pmi.userID AND pmi.image_admin = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;"); + + 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"))); + } + con.close(); + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return list; } @@ -485,7 +516,7 @@ public class SQL { return list; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getLectureListPermissionRead."); + log.info("Failed to getLectureListPermissionRead."); e.printStackTrace(); } return list; @@ -516,7 +547,7 @@ public class SQL { return list; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getLectureListPermissionWrite."); + log.info("Failed to getLectureListPermissionWrite."); e.printStackTrace(); } return list; @@ -546,7 +577,7 @@ public class SQL { return list; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getLectureListPermissionAdmin."); + log.info("Failed to getLectureListPermissionAdmin."); e.printStackTrace(); } return list; @@ -572,7 +603,7 @@ public class SQL { return list; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getLectureList."); + log.info("Failed to getLectureList."); e.printStackTrace(); } return null; @@ -593,7 +624,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getAllOS."); + log.info("Failed to getAllOS."); e.printStackTrace(); } @@ -624,7 +655,7 @@ public class SQL { return map; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getPersonData."); + log.info("Failed to getPersonData."); e.printStackTrace(); } return null; @@ -677,7 +708,7 @@ public class SQL { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to setLectureData."); + log.info("Failed to setLectureData."); e.printStackTrace(); } return 0; @@ -702,7 +733,7 @@ public class SQL { return map; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getImageIDandVersion."); + log.info("Failed to getImageIDandVersion."); e.printStackTrace(); } return null; @@ -740,7 +771,7 @@ public class SQL { return map; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getImageData."); + log.info("Failed to getImageData."); e.printStackTrace(); } return null; @@ -764,6 +795,7 @@ public class SQL { if (license == true) { license_bol = 1; } + /* log.info(new Date() + " - 'image_name` = '" + newName + "\n length of name=" + newName.length()); @@ -787,6 +819,7 @@ public class SQL { + ",`image_syncMode` = '" + shareMode + "' WHERE GUID_imageID = '" + id + "' AND imageVersion = " + version + ";"); + */ stm.executeUpdate("UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = " + newVersion @@ -827,7 +860,7 @@ public class SQL { return 0; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to UpdateImageData."); + log.info("Failed to UpdateImageData."); e.printStackTrace(); } return -1; @@ -852,7 +885,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to deleteImage and permissions."); + log.info("Failed to deleteImage and permissions."); e.printStackTrace(); } @@ -891,11 +924,11 @@ public class SQL { + "'WHERE `lectureID` = '" + id + "';"); con.commit(); con.close(); - log.info(new Date() + " - Succeeded to updateLectureData."); + log.info("Succeeded to updateLectureData."); } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to updateLectureData."); + log.info("Failed to updateLectureData."); e.printStackTrace(); } @@ -918,8 +951,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() - + " - Failed to execute method connectedToLecture."); + log.info("Failed to execute method connectedToLecture."); e.printStackTrace(); } @@ -944,7 +976,7 @@ public class SQL { return true; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to deleteLecture and permissions."); + log.info("Failed to deleteLecture and permissions."); e.printStackTrace(); } @@ -970,7 +1002,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getFile."); + log.info("Failed to getFile."); e.printStackTrace(); } @@ -996,7 +1028,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getDeleteXMLData."); + log.info("Failed to getDeleteXMLData."); e.printStackTrace(); } @@ -1009,11 +1041,12 @@ public class SQL { Statement stm = con.createStatement(); String image_path = "prod/" + name; + /* log.info(new Date() + " - Updating ImagePath with: UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '" + image_path + "' WHERE `image_path` = '" + "temp/" + name + "';"); - + */ stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '" + image_path + "' WHERE `image_path` = '" @@ -1022,10 +1055,11 @@ public class SQL { + "';"); con.commit(); con.close(); + log.info("Succesfully updated image path"); return 0; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to UpdateImagePath."); + log.info("Failed to UpdateImagePath."); e.printStackTrace(); } return -1; @@ -1045,7 +1079,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getAllUniversities."); + log.info("Failed to getAllUniversities."); e.printStackTrace(); } @@ -1057,8 +1091,7 @@ public class SQL { try { Connection con = getConnection(); Statement stm = con.createStatement(); - log.info(new Date() + " - Getting PrimaryKey for OS: " + os - + " architecture: " + architecture); + //log.info(new Date() + " - Getting PrimaryKey for OS: " + os + " architecture: " + architecture); ResultSet rs = stm .executeQuery("SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like '" + os @@ -1067,13 +1100,13 @@ public class SQL { + "';"); rs.first(); String pkos = rs.getString("operatingSystemID"); - log.info(new Date() + " - Key is " + pkos); + //log.info(" - Key is " + pkos); con.close(); return pkos; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getOSpk."); + log.info("Failed to getOSpk."); e.printStackTrace(); } @@ -1097,7 +1130,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getOS."); + log.info("Failed to getOS."); e.printStackTrace(); } @@ -1128,7 +1161,7 @@ public class SQL { return map; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getLectureData."); + log.info("Failed to getLectureData."); e.printStackTrace(); } return null; @@ -1145,12 +1178,12 @@ public class SQL { + username + "'"); if (rs.next()) { ret = true; - log.info(new Date() + " - User " + rs.getString("loginName") - + " exist."); + log.info("Checking user: " + rs.getString("loginName") + + " exists."); } else { ret = false; - log.info(new Date() + " - User " + rs.getString("loginName") - + " not exist."); + log.info("Checking user: " + rs.getString("loginName") + + " does not exist."); } con.close(); @@ -1177,7 +1210,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getRoleID."); + log.info("Failed to getRoleID."); e.printStackTrace(); } return -1; @@ -1199,7 +1232,7 @@ public class SQL { return id; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to LectureID."); + log.info("Failed to LectureID."); e.printStackTrace(); } return id; @@ -1474,7 +1507,7 @@ public class SQL { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to setImageRights."); + log.info("Failed to setImageRights."); e.printStackTrace(); } return 0; @@ -1492,6 +1525,7 @@ public class SQL { try { stm = con.createStatement(); + /* log.info("INSERT INTO pm_VLData_image (" + "GUID, " + "GUID_imageID, " + "roleID, " + "userID, " + "image_read, " + "image_write, " + "Image_admin, " @@ -1499,7 +1533,7 @@ public class SQL { + imageID + "'," + "1," + "'" + userID + "'," + isRead + "," + isWrite + "," + isAdmin + "," + isLinkAllowed + "" + ");"); - + */ stm.executeUpdate("INSERT INTO pm_VLData_image (" + "GUID, " + "GUID_imageID, " + "roleID, " + "userID, " + "image_read, " + "image_write, " + "Image_admin, " -- cgit v1.2.3-55-g7522