package sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;
public class SQL {
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);
return con;
} catch (SQLException e) {
// TODO Auto-generated catch block
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();
return ret;
} catch (SQLException e) {
// TODO Auto-generated catch block
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();
return ret;
} catch (SQLException e) {
// TODO Auto-generated catch block
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_name)
{
try {
Statement stm=con.createStatement();
return stm.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where image_name like '"+image_name+"';");
} 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+"','Platzhalter','"+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 int setImageData(Connection con, int pk_person, boolean license, boolean internet, int cpu, int ram, String imagename)
{
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
int internet_bol=0;
int license_bol=0;
if(internet==true)
{
internet_bol=1;
}
else 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+"',1,'"+imagename+"','/srv/openslx/nfs/temp/','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+pk_person+"','"+pk_person+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+pk_person+"','"+pk_person+"',1,1,1,1,1,1,0,0,'"+license_bol+"','"+internet_bol+"','"+ram+"','"+cpu+"');");
con.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ram;
}
public ResultSet getImageListWithoutLectures(Connection con){
try {
Statement stm=con.createStatement();
return stm.executeQuery("SELECT distinct vl.image_name, vl.cond_hasLicenseRestriction, os.name, vl.image_update_time, '', Concat(u.Nachname,' ',u.Vorname) 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.lectureID!=vl.GUID_imageID and vl.image_owner=u.userID;");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public ResultSet getImageListWithLectures(Connection con){
try {
Statement stm=con.createStatement();
return stm.executeQuery("SELECT vl.image_name, vl.cond_hasLicenseRestriction, os.name, vl.image_update_time, lect.name, Concat(u.Nachname,' ',u.Vorname) 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 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;
}
}
