diff options
Diffstat (limited to 'Dozentenmodulserver/src/main/java/sql')
| -rw-r--r-- | Dozentenmodulserver/src/main/java/sql/SQL.java | 279 |
1 files changed, 258 insertions, 21 deletions
diff --git a/Dozentenmodulserver/src/main/java/sql/SQL.java b/Dozentenmodulserver/src/main/java/sql/SQL.java index ec768a40..9a115f09 100644 --- a/Dozentenmodulserver/src/main/java/sql/SQL.java +++ b/Dozentenmodulserver/src/main/java/sql/SQL.java @@ -3,8 +3,10 @@ package sql; import java.sql.*;
import java.text.DateFormat;
+import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
+import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
@@ -230,8 +232,8 @@ public class SQL { }
public boolean setImageData(String pk_person, boolean license,
- boolean internet, long cpu, long ram, String imagename,
- String imagePath, boolean isTemplate, long filesize, String shareMode) {
+ boolean internet, long cpu, long ram, String imagename,String desc,
+ String imagePath, boolean isTemplate, long filesize, String shareMode, String pk_os) {
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
@@ -249,10 +251,11 @@ public class SQL { Statement stm = con.createStatement();
String uid = UUID.randomUUID().toString();
- stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`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('"
+ 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
+ "',1,'" // imageVersion
+ imagename // image_name
+ + "','" +desc //image_description
+ "','" + imagePath // image_path
+ "','" + formatter.format(new Date()) // image_lastCall
+ "','" + formatter.format(new Date()) // image_create_time
@@ -263,8 +266,8 @@ public class SQL { + "','" + formatter.format(new Date()) // rec_change_time
+ "','" + pk_person // rec_owner
+ "','" + pk_person // rec_change_by
- + "',0" // content_operatingSystem
- + ",1" // status_isCompressed
+ + "','" + pk_os // content_operatingSystem
+ + "',1" // status_isCompressed
+ ",1" // status_isSecure
+ ",1" // status_isOptimzed
+ ",1" // status_isValid
@@ -296,22 +299,23 @@ public class SQL { Statement stm;
List<Image> list = new ArrayList<Image>();
try {
+
stm = con.createStatement();
ResultSet res=stm
- .executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name, '' as lecture,vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user FROM 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, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user 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;");
+ .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.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;");
+
-
-
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("lecture"),
+ .getString("name")+" "+res.getString("architecture")+" bit", res.getString("lecture"),
res.getString("image_update_time"), res
- .getString("user")));
+ .getString("user"), res.getString("image_isTemplate")));
}
@@ -319,7 +323,7 @@ public class SQL { } catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
- }
+ }
return list;
@@ -358,9 +362,9 @@ public class SQL { Connection con=getConnection();
Statement stm = con.createStatement();
ResultSet rs=stm
- .executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;");
+ .executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem;");
while (rs.next()) {
- list.add(rs.getString("name"));
+ list.add(rs.getString("name")+" "+rs.getString("architecture")+" bit");
}
return list;
@@ -487,8 +491,7 @@ public class SQL { Connection con=getConnection();
Statement stm = con.createStatement();
ResultSet data=stm
- .executeQuery("SELECT image_name,cond_hasInternetRestriction,cond_hasLicenseRestriction, cond_minCPUs, cond_minRAM, image_syncMode,image_filesize,image_path, image_isTemplate FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '"
- + id + "' and imageVersion = '" + version + "' ;");
+ .executeQuery("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 = '" + id + "' and imageVersion = '" + version + "' ;");
while (data.next()) {
map.put("name", data.getString("image_name"));
map.put("internet",
@@ -500,6 +503,14 @@ public class SQL { 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;
@@ -511,9 +522,9 @@ public class SQL { return null;
}
- public int UpdateImageData(String name, String newName,String image_path,
+ 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 version, boolean isTemplate, long filesize, String shareMode,String ospk) {
try {
Connection con=getConnection();
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
@@ -534,6 +545,8 @@ public class SQL { + newVersion
+ "',`image_name` = '"
+ newName
+ + "',`image_description` = '"
+ + desc
+ "',`image_path` = '"
+ image_path
+ "',`image_update_time` = '"
@@ -550,6 +563,8 @@ public class SQL { + cpu
+ "',`image_isTemplate` = '"
+ isTemplate
+ + "',`content_operatingSystem` = '"
+ + ospk
+ "',`image_filesize` = '"
+ filesize
+ "',`image_syncMode` = '"
@@ -591,7 +606,7 @@ public class SQL { }
public int updateLectureData(String pk_image,
- int imageversion, String name, String newName, String desc,
+ String imageversion, String name, String newName, String desc,
String shortdesc, String start, String end, boolean isactive,
String id) {
@@ -644,8 +659,9 @@ public class SQL { ResultSet rs = stm.executeQuery("SELECT lectureID FROM "
+ "bwLehrpool.m_VLData_lecture WHERE imageID = '" + id
+ "' AND imageVersion = '" + version + "';");
+ boolean ret=rs.first();
con.close();
- return rs.first();
+ return ret;
} catch (SQLException e) {
// TODO Auto-generated catch block
@@ -746,7 +762,7 @@ public class SQL { return 0;
} catch (SQLException e) {
// TODO Auto-generated catch block
- log.info(new Date() + " - Failed to UpdateImageData.");
+ log.info(new Date() + " - Failed to UpdateImagePath.");
e.printStackTrace();
}
return -1;
@@ -773,4 +789,225 @@ public class SQL { return null;
}
+
+ public String getOSpk(String os,
+ String architecture) {
+ try {
+ Connection con=getConnection();
+ Statement stm = con.createStatement();
+ 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
+ + "' and architecture like '"
+ + architecture
+ + "';");
+ rs.first();
+ String pkos=rs.getString("operatingSystemID");
+ log.info(new Date() + " - Key is "+pkos);
+ con.close();
+ return pkos;
+
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ log.info(new Date() + " - Failed to getOSpk.");
+ e.printStackTrace();
+ }
+
+ return null;
+ }
+
+ public String getOS(String pk) {
+ try {
+ Connection con=getConnection();
+ Statement stm = con.createStatement();
+ //log.info(new Date() + " - OS: "+os+" architecture: "+architecture);
+ ResultSet rs=stm
+ .executeQuery("SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = '"
+ + pk
+ + "';");
+ rs.first();
+ String os=rs.getString("name")+" "+rs.getString("architecture");
+ con.close();
+ return os;
+
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ log.info(new Date() + " - Failed to getOS.");
+ e.printStackTrace();
+ }
+
+ return null;
+ }
+
+ public Map<String,String> getLectureData(String id) {
+ Map<String, String> map = new HashMap<String, String>();
+ try {
+ Connection con=getConnection();
+ Statement stm = con.createStatement();
+ ResultSet data=stm
+ .executeQuery("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= '" + id + "' ;");
+ 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) {
+ // TODO Auto-generated catch block
+ log.info(new Date() + " - Failed to getLectureData.");
+ e.printStackTrace();
+ }
+ return null;
+ }
+
+ public boolean checkUser(String username) {
+ Statement stm;
+ boolean ret=false;
+ try {
+ Connection con=getConnection();
+ stm = con.createStatement();
+ ResultSet rs=stm.executeQuery("Select * from m_user where loginName like '"+username+"'");
+ if(rs.next())
+ {
+ ret=true;
+ log.info(new Date() + " - User " + rs.getString("loginName") + " exist.");
+ }
+ else{
+ ret=false;
+ log.info(new Date() + " - User " + rs.getString("loginName") + " not exist.");
+ }
+ con.close();
+
+
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+
+ e.printStackTrace();
+ }
+ return ret;
+ }
+
+ public int setImageRights(String pk_person, String pk_image,int role ,int read, int write, int changePermission, int admin,int linkallowed) {
+
+
+
+ try {
+ Connection con=getConnection();
+ Statement stm = con.createStatement();
+
+ String uid = UUID.randomUUID().toString();
+ stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_image`(`GUID`,`GUID_imageID`,`roleID`,`userID`,`image_read`,`image_write`,`image_admin`,`image_changePermission`,`link_allowed`)VALUES('"
+ + uid
+ + "','"
+ + pk_image
+ + "','"
+ + role
+ + "','"
+ + pk_person
+ + "','"
+ + read
+ + "','"
+ + write
+ + "','"
+ + admin
+ + "','"
+ + changePermission
+ + "','"
+ + linkallowed+ "');");
+ con.commit();
+ con.close();
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ log.info(new Date() + " - Failed to setImageRights.");
+ e.printStackTrace();
+ }
+ return 0;
+
+ }
+ public int getRoleID(String role) {
+
+
+ try {
+ Connection con=getConnection();
+ Statement stm = con.createStatement();
+
+ ResultSet rs=stm
+ .executeQuery("SELECT roleID FROM bwLehrpool.m_role where name like '"+ role+ "';");
+ rs.first();
+
+ return rs.getInt("roleID");
+
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ log.info(new Date() + " - Failed to getRoleID.");
+ e.printStackTrace();
+ }
+ return -1;
+ }
+ public int setLectureRights(String pk_person, String pk_lecture,int role ,int read, int write, int changePermission, int admin) {
+
+
+
+ try {
+ Connection con=getConnection();
+ Statement stm = con.createStatement();
+
+ String uid = UUID.randomUUID().toString();
+ stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`roleID`,`userID`,`rec_read`,`rec_write`,`rec_admin`,`rec_changePermission`)VALUES('"
+ + uid
+ + "','"
+ + pk_lecture
+ + "','"
+ + role
+ + "','"
+ + pk_person
+ + "','"
+ + read
+ + "','"
+ + write
+ + "','"
+ + admin
+ + "','"
+ + changePermission+ "');");
+ con.commit();
+ con.close();
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ log.info(new Date() + " - Failed to setLectureRights.");
+ e.printStackTrace();
+ }
+ return 0;
+
+ }
+ public String getLectureID(String name) {
+ String id=null;
+ try {
+ Connection con=getConnection();
+ Statement stm = con.createStatement();
+ ResultSet lecture= stm
+ .executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like '"
+ + name + "';");
+ while (lecture.next()) {
+ id=lecture.getString("lectureID");
+
+ }
+ con.close();
+ return id;
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ log.info(new Date() + " - Failed to LectureID.");
+ e.printStackTrace();
+ }
+ return id;
+ }
}
|