package sql;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;
import org.apache.log4j.Logger;
import server.BinaryListener;
public class SQL {
private static Logger log = Logger.getLogger(BinaryListener.class);
public Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (InstantiationException | IllegalAccessException
| ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
Connection con = DriverManager
.getConnection("jdbc:mysql://127.0.0.1/bwLehrpool?user=root&password=slx-ng-open");
con.setAutoCommit(false);
log.info(new Date() + " - Connection returned to Client.");
return con;
} catch (SQLException e) {
// TODO Auto-generated catch block
log.info(new Date() + " - Could not return connection to Client.");
e.printStackTrace();
}
return null;
}
public int writeFTPUser(Connection con, String user, String pass) {
Statement stm;
try {
stm = con.createStatement();
int ret = stm
.executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('"
+ user
+ "',SHA1('"
+ pass
+ "'),'10001','12345','/srv/openslx/nfs/temp');");
con.commit();
log.info(new Date() + " - created FTPUser " + user + " : " + pass
+ ".");
return ret;
} catch (SQLException e) {
// TODO Auto-generated catch block
log.info(new Date() + " - FTPUser not created.");
e.printStackTrace();
}
return -1;
}
public int DeleteUser(Connection con, String user) {
Statement stm;
try {
stm = con.createStatement();
int ret = stm
.executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '"
+ user + "';");
con.commit();
log.info(new Date() + " - FTPUser " + user + " deleted.");
return ret;
} catch (SQLException e) {
// TODO Auto-generated catch block
log.info(new Date() + " - Could not delete FTPUser " + user + ".");
e.printStackTrace();
}
return -1;
}
public ResultSet getImage(Connection con) {
try {
Statement stm = con.createStatement();
return stm
.executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public ResultSet getPathOfImage(Connection con, String image_id,
String version) {
try {
Statement stm = con.createStatement();
return stm
.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='"
+ image_id
+ "' and imageVersion='"
+ version
+ "';");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public int setInstitution(Connection con, String university) {
try {
Statement stm = con.createStatement();
ResultSet ret = stm
.executeQuery("SELECT * FROM bwLehrpool.m_institution where name like'"
+ university + "';");
if (ret.next() == false) {
Random rand = new Random();
int id = rand.nextInt();
stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`)VALUES('"
+ id + "','" + university + "');");
con.commit();
ResultSet rs = stm
.executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '"
+ university + "';");
rs.next();
return rs.getInt("institutionID");
} else {
return ret.getInt("institutionID");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return -1;
}
public int setPerson(Connection con, String login, String lastname,
String firstname, String mail, Date lastlogin, int Institution) {
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
try {
Statement stm = con.createStatement();
ResultSet ret = stm
.executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '"
+ lastname
+ "' and Vorname like '"
+ firstname
+ "';");
if (ret.next() == false) {
Random rand = new Random();
int id = rand.nextInt();
stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('"
+ id
+ "','"
+login
+"','"
+ lastname
+ "','"
+ firstname
+ "','"
+ mail
+ "','"
+ formatter.format(new Date())
+ "','"
+ Institution
+ "');");
con.commit();
ResultSet rs = stm
.executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '"
+ lastname
+ "' and Vorname like '"
+ firstname
+ "';");
rs.next();
return rs.getInt("userID");
} else {
return ret.getInt("userID");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return -1;
}
public boolean setImageData(Connection con, int pk_person, boolean license,
boolean internet, long cpu, long ram, String imagename,
String imagePath) {
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 {
Statement stm = con.createStatement();
Random random = new Random();
int uid = random.nextInt();
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`)VALUES('"
+ uid // GUID_imageID
+ "',1,'" // imageVersion
+ imagename // image_name
+ "','" + imagePath // image_path
+ "','" + formatter.format(new Date()) // image_lastCall
+ "','" + formatter.format(new Date()) // image_create_time
+ "','" + formatter.format(new Date()) // image_update_time
+ "','" + pk_person // image_owner
+ "','" + pk_person // image_change_by
+ "','" + formatter.format(new Date()) // rec_create_time
+ "','" + formatter.format(new Date()) // rec_change_time
+ "','" + pk_person // rec_owner
+ "','" + pk_person // rec_change_by
+ "',0" // content_operatingSystem
+ ",1" // status_isCompressed
+ ",1" // status_isSecure
+ ",1" // status_isOptimzed
+ ",1" // status_isValid
+ ",1" // status_isReady
+ ",0" // status_isDeleted
+ ",0,'" // status_isLastOfficialVersion
+ license_bol // cond_hasLicenseRestriction
+ "','" + internet_bol // cond_hasInternetRestriction
+ "','" + ram // cond_minRAM
+ "','" + cpu // cond_minCPUs
+ "');");
con.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return true;
}
public ResultSet getImageList(Connection con) {
try {
Statement stm = con.createStatement();
return 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;");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public ResultSet getLectureList(Connection con) {
try {
Statement stm = con.createStatement();
return 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;");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public ResultSet getAllOS(Connection con) {
try {
Statement stm = con.createStatement();
return stm
.executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public ResultSet getPersonData(Connection con, String Vorname,
String Nachname) {
try {
Statement stm = con.createStatement();
return stm
.executeQuery("SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like '"
+ Nachname
+ "' and u.Vorname like '"
+ Vorname
+ "' and u.institution=i.institutionID;");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public int setLectureData(Connection con, int pk_person, int pk_image,
int imageversion, String name, String desc, String shortdesc,
String start, String end, boolean isactive) {
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
int active_bol = 0;
if (isactive == true) {
active_bol = 1;
}
try {
Statement stm = con.createStatement();
Random random = new Random();
int uid = random.nextInt();
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
+ "','"
+ name
+ "','"
+ active_bol
+ "','"
+ start
+ "','"
+ end
+ "','"
+ formatter.format(new Date())
+ "','"
+ shortdesc
+ "','"
+ desc
+ "','"
+ pk_image
+ "','"
+ imageversion
+ "','"
+ formatter.format(new Date())
+ "','"
+ formatter.format(new Date())
+ "','"
+ pk_person
+ "','"
+ pk_person + "');");
con.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
public ResultSet getImageIDandVersion(Connection con, String name) {
try {
Statement stm = con.createStatement();
return stm
.executeQuery("SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like '"
+ name + "';");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public ResultSet getImageData(Connection con, String id, String version) {
try {
Statement stm = con.createStatement();
System.out.println("Test");
return stm
.executeQuery("SELECT image_name,cond_hasInternetRestriction,cond_hasLicenseRestriction, cond_minCPUs, cond_minRAM FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '"
+ id + "' and imageVersion = '" + version + "' ;");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public int UpdateImageData(Connection con, String name, String newName,
boolean license, boolean internet, long cpu, long ram, String id,
String version) {
try {
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;
if (internet == true) {
internet_bol = 1;
}
if (license == true) {
license_bol = 1;
}
System.out.println(internet_bol);
System.out.println(license_bol);
stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `imageVersion` = '"
+ newVersion
+ "',`image_name` = '"
+ newName
+ "',`image_update_time` = '"
+ formatter.format(new Date())
+ "',`rec_change_time` = '"
+ formatter.format(new Date())
+ "',`cond_hasLicenseRestriction` = '"
+ license_bol
+ "',`cond_hasInternetRestriction` = '"
+ internet_bol
+ "',`cond_minRAM` = '"
+ ram
+ "',`cond_minCPUs` = '"
+ cpu
+ "' WHERE `GUID_imageID` = '"
+ id
+ "' AND `imageVersion` = '" + version + "';");
con.commit();
return 0;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return -1;
}
/**
*
* @param id
* @param version
* @return
*/
public boolean deleteImage(Connection con, String id, String version) {
try {
Statement stm = con.createStatement();
stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '"
+ id + "' " + "AND imageVersion = '" + version + "';");
con.commit();
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
public int updateLectureData(Connection con, int pk_image,
int 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 {
Statement stm = con.createStatement();
stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = '"
+ newName
+ "',`isActive` = '"
+ active_bol
+ "',`startTime` = '"
+ start
+ "',`endTime` = '"
+ end
+ "',`description` = '"
+ desc
+ "',`imageID` = '"
+ pk_image
+ "',`imageVersion` = '"
+ imageversion
+ "',`admin_changeTime` = '"
+ formatter.format(new Date())
+ "'WHERE `lectureID` = '" + id + "';");
con.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
public boolean connectedToLecture(Connection con, String id, String version) {
try {
Statement stm = con.createStatement();
ResultSet rs = stm.executeQuery("SELECT lectureID FROM "
+ "bwLehrpool.m_VLData_lecture WHERE imageID = '" + id
+ "' AND imageVersion = '" + version + "';");
return rs.first();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
public boolean deleteLecture(Connection con, String id) {
try {
Statement stm = con.createStatement();
stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = '"+ id + "';");
con.commit();
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
public String getFile(Connection con, String imageid, String imageversion) {
try {
Statement stm = con.createStatement();
ResultSet rs=stm.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = '" + imageid
+ "' AND imageVersion = '" + imageversion + "';");
rs.next();
return rs.getString("image_path");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public ResultSet getDeleteXMLData(Connection con, String id)
{
try {
Statement stm = con.createStatement();
ResultSet rs=stm.executeQuery("SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID='"+id+"';");
return rs;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}
