From 7658a51f286bad750eea3629b0fac4780c429afe Mon Sep 17 00:00:00 2001 From: Nino Breuer Date: Tue, 18 Nov 2014 15:31:20 +0100 Subject: • fixed bug, where there could only be one image and one lecture with the same name --- dozentenmodulserver/src/main/java/sql/SQL.java | 43 +++++++++++++++----------- 1 file changed, 25 insertions(+), 18 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 10afee96..19148516 100644 --- a/dozentenmodulserver/src/main/java/sql/SQL.java +++ b/dozentenmodulserver/src/main/java/sql/SQL.java @@ -395,7 +395,7 @@ public class SQL { 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 pk_os, String uid) { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); @@ -413,7 +413,7 @@ public class SQL { //Statement stm = con.createStatement(); // create a UUID - String uid = UUID.randomUUID().toString(); + //String uid = UUID.randomUUID().toString(); @@ -539,7 +539,7 @@ public class SQL { */ 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;"; + + " 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); @@ -593,7 +593,7 @@ public class SQL { 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;"; + + " 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); @@ -642,7 +642,7 @@ public class SQL { // 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;"); + .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 @@ -699,7 +699,7 @@ public class SQL { 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;"; + + " 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); @@ -753,7 +753,7 @@ public class SQL { 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;"; + + " 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); @@ -802,7 +802,7 @@ public class SQL { // 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;"); + .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()) @@ -850,7 +850,7 @@ public class SQL { 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 i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.rec_read=true AND u.userID=pml.userID AND u.userID = ? ;"; + + "WHERE i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.rec_read=true AND u.userID=pml.userID AND u.userID = ? ORDER BY l.name;"; PreparedStatement prest = con.prepareStatement(sql); prest.setString(1, userID); @@ -900,7 +900,7 @@ public class SQL { + "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=pml.userID AND pml.rec_write=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID;"; + + " AND u.userID=pml.userID 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); @@ -949,7 +949,7 @@ public class SQL { 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 i.GUID_imageID=l.imageID AND pml.rec_admin=true AND l.lectureID=pml.lectureID AND u.userID=pml.userID AND u.userID= ? ;"; + + "WHERE i.GUID_imageID=l.imageID AND pml.rec_admin=true AND l.lectureID=pml.lectureID AND u.userID=pml.userID AND u.userID= ? ORDER BY l.name;"; PreparedStatement prest = con.prepareStatement(sql); prest.setString(1, userID); @@ -989,7 +989,7 @@ public class SQL { 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;"); + .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()) @@ -1095,7 +1095,7 @@ public class SQL { public int setLectureData(String pk_person, String pk_image, int imageversion, String name, String desc, String shortdesc, - String start, String end, boolean isactive) + String start, String end, boolean isactive, String lectureID) { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); @@ -1111,7 +1111,8 @@ public class SQL { Connection con = getConnection(); //Statement stm = con.createStatement(); - String uid = UUID.randomUUID().toString(); + //String uid = UUID.randomUUID().toString(); + /* stm.executeUpdate("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('" + uid @@ -1173,7 +1174,7 @@ public class SQL { + "?" + ");"; PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, uid); + prest.setString(1, lectureID); prest.setString(2, name); prest.setInt(3, active_bol); prest.setString(4, start); @@ -1202,7 +1203,7 @@ public class SQL { - public Map getImageIDandVersion(String name) { + public Map getImageIDandVersion(String id) { Map map = new HashMap(); try { Connection con = getConnection(); @@ -1213,10 +1214,10 @@ public class SQL { + name + "';"); */ - String sql = "SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like ?"; + String sql = "SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = ?"; PreparedStatement prest = con.prepareStatement(sql); - prest.setString(1, name); + prest.setString(1, id); ResultSet image = prest.executeQuery(); con.commit(); @@ -2541,6 +2542,12 @@ public class SQL { return null; } + + + public String createRandomUUID() + { + return UUID.randomUUID().toString(); + } }// end class -- cgit v1.2.3-55-g7522