package org.openslx.bwlp.sat.database.mappers;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.UUID;
import org.apache.log4j.Logger;
import org.openslx.bwlp.sat.database.Database;
import org.openslx.bwlp.sat.database.MysqlConnection;
import org.openslx.bwlp.sat.database.MysqlStatement;
import org.openslx.bwlp.sat.permissions.User;
import org.openslx.bwlp.sat.util.Json;
import org.openslx.bwlp.thrift.iface.LectureRead;
import org.openslx.bwlp.thrift.iface.LectureSummary;
import org.openslx.bwlp.thrift.iface.LectureWrite;
import org.openslx.bwlp.thrift.iface.NetRule;
import org.openslx.bwlp.thrift.iface.TNotFoundException;
import org.openslx.bwlp.thrift.iface.UserInfo;
import com.google.gson.JsonParseException;
public class DbLecture {
private static final Logger LOGGER = Logger.getLogger(DbLecture.class);
public static String create(UserInfo user, LectureWrite lecture) throws SQLException {
if (lecture.lectureName.length() > 100) {
lecture.lectureName = lecture.lectureName.substring(0, 100);
}
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("INSERT INTO lecture"
+ " (lectureid, displayname, description, imageversionid, autoupdate,"
+ " isenabled, starttime, endtime, createtime, updatetime,"
+ " ownerid, updaterid, runscript, nics, netrules, isexam,"
+ " hasinternetaccess, caneditdefault, canadmindefault)"
+ " VALUES "
+ " (:lectureid, :displayname, :description, :imageversionid, :autoupdate,"
+ " :isenabled, :starttime, :endtime, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(),"
+ " :userid, :userid, NULL, NULL, NULL, :isexam, :hasinternetaccess, :canedit, :canadmin)");
String lectureId = UUID.randomUUID().toString();
stmt.setString("lectureid", lectureId);
stmt.setString("displayname", lecture.lectureName);
stmt.setString("description", lecture.description);
stmt.setString("imageversionid", lecture.imageVersionId);
stmt.setBoolean("autoupdate", lecture.autoUpdate);
stmt.setBoolean("isenabled", lecture.isEnabled);
stmt.setLong("starttime", lecture.startTime);
stmt.setLong("endtime", lecture.endTime);
stmt.setString("userid", user.userId);
stmt.setBoolean("isexam", lecture.isExam);
stmt.setBoolean("hasinternetaccess", lecture.hasInternetAccess);
stmt.setBoolean("canedit", lecture.defaultPermissions.edit);
stmt.setBoolean("canadmin", lecture.defaultPermissions.admin);
stmt.executeUpdate();
update(connection, user, lectureId, lecture);
connection.commit();
return lectureId;
} catch (SQLException e) {
LOGGER.error("Query failed in DbLecture.create()", e);
throw e;
}
}
public static void setOwner(UserInfo user, String lectureId, String newOwnerId) throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("UPDATE lecture"
+ " SET ownerid = :ownerid, updaterid = :userid, updatetime = UNIX_TIMESTAMP()"
+ " WHERE lectureid = :lectureid");
stmt.setString("ownerid", newOwnerId);
stmt.setString("userid", user.userId);
stmt.setString("lectureid", lectureId);
stmt.executeUpdate();
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in DbLecture.setOwner()", e);
throw e;
}
}
public static void update(UserInfo user, String lectureId, LectureWrite lecture) throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
update(connection, user, lectureId, lecture);
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in DbLecture.update()", e);
throw e;
}
}
private static void update(MysqlConnection connection, UserInfo user, String lectureId,
LectureWrite lecture) throws SQLException {
if (lecture.lectureName.length() > 100) {
lecture.lectureName = lecture.lectureName.substring(0, 100);
}
String nicsJson = null;
if (lecture.nics != null && !lecture.nics.isEmpty()) {
for (;;) {
nicsJson = Json.serialize(lecture.nics);
if (nicsJson.length() < 200)
break;
lecture.nics.remove(0);
}
}
String netruleJson = Json.serialize(lecture.networkExceptions);
MysqlStatement stmt = connection.prepareStatement("UPDATE lecture SET "
+ " displayname = :displayname, description = :description, imageversionid = :imageversionid,"
+ " autoupdate = :autoupdate, isenabled = :isenabled, starttime = :starttime,"
+ " endtime = :endtime, updatetime = UNIX_TIMESTAMP(),"
+ " updaterid = :updaterid, runscript = :runscript, nics = :nics,"
+ " netrules = :netrules, isexam = :isexam, hasinternetaccess = :hasinternetaccess,"
+ " caneditdefault = :caneditdefault, canadmindefault = :canadmindefault"
+ " WHERE lectureid = :lectureid");
stmt.setString("lectureid", lectureId);
stmt.setString("displayname", lecture.lectureName);
stmt.setString("description", lecture.description);
stmt.setString("imageversionid", lecture.imageVersionId);
stmt.setBoolean("autoupdate", lecture.autoUpdate);
stmt.setBoolean("isenabled", lecture.isEnabled);
stmt.setLong("starttime", lecture.startTime);
stmt.setLong("endtime", lecture.endTime);
stmt.setString("updaterid", user.userId);
stmt.setString("runscript", lecture.runscript);
stmt.setString("nics", nicsJson);
stmt.setString("netrules", netruleJson);
stmt.setBoolean("isexam", lecture.isExam);
stmt.setBoolean("hasinternetaccess", lecture.hasInternetAccess);
stmt.setBoolean("caneditdefault", lecture.defaultPermissions.edit);
stmt.setBoolean("canadmindefault", lecture.defaultPermissions.admin);
stmt.executeUpdate();
}
public static LectureSummary getLectureSummary(UserInfo user, String lectureId) throws SQLException,
TNotFoundException {
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("SELECT"
+ " l.lectureid, l.displayname AS lecturename, l.imageversionid, l.isenabled,"
+ " l.starttime, l.endtime, l.lastused, l.usecount, l.ownerid, l.updaterid,"
+ " l.isexam, l.hasinternetaccess, l.caneditdefault, l.canadmindefault,"
+ " perm.canedit, perm.canadmin FROM lecture l"
+ " LEFT JOIN lecturepermission perm ON (perm.lectureid = l.lectureid AND perm.userid = :userid)"
+ " WHERE lectureid = :lectureid");
stmt.setString("lectureid", lectureId);
stmt.setString("userid", user.userId);
ResultSet rs = stmt.executeQuery();
if (!rs.next())
throw new TNotFoundException();
LectureSummary lecture = new LectureSummary();
lecture.setLectureId(rs.getString("lectureid"));
lecture.setLectureName(rs.getString("lecturename"));
lecture.setImageVersionId(rs.getString("imageversionid"));
lecture.setIsEnabled(rs.getBoolean("isenabled"));
lecture.setStartTime(rs.getLong("starttime"));
lecture.setEndTime(rs.getLong("endtime"));
lecture.setLastUsed(rs.getLong("lastused"));
lecture.setUseCount(rs.getInt("usecount"));
lecture.setOwnerId(rs.getString("ownerid"));
lecture.setUpdaterId(rs.getString("updaterid"));
lecture.setIsExam(rs.getBoolean("isexam"));
lecture.setHasInternetAccess(rs.getBoolean("hasinternetaccess"));
lecture.setDefaultPermissions(DbLecturePermissions.fromResultSetDefault(rs));
lecture.setUserPermissions(DbLecturePermissions.fromResultSetUser(rs));
return lecture;
} catch (SQLException e) {
LOGGER.error("Query failed in DbLecture.getLectureSummary()", e);
throw e;
}
}
public static List<LectureSummary> getAll(UserInfo user, int page) throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("SELECT"
+ " l.lectureid, l.displayname AS lecturename, l.imageversionid, i.imagebaseid,"
+ " l.isenabled, l.starttime, l.endtime, l.lastused, l.usecount, l.ownerid, l.updaterid,"
+ " l.isexam, l.hasinternetaccess, l.caneditdefault, l.canadmindefault,"
+ " i.isvalid AS imgvalid, p.canedit, p.canadmin"
+ " FROM lecture l "
+ " INNER JOIN imageversion i USING (imageversionid)"
+ " LEFT JOIN lecturepermission p ON (p.lectureid = l.lectureid AND p.userid = :userid)"
+ (User.isStudent(user) ? " WHERE i.isrestricted = 0" : ""));
if (user == null) {
stmt.setString("userid", "-");
} else {
stmt.setString("userid", user.userId);
}
ResultSet rs = stmt.executeQuery();
List<LectureSummary> list = new ArrayList<>(100);
while (rs.next()) {
LectureSummary lecture = new LectureSummary();
lecture.setLectureId(rs.getString("lectureid"));
lecture.setLectureName(rs.getString("lecturename"));
lecture.setImageVersionId(rs.getString("imageversionid"));
lecture.setImageBaseId(rs.getString("imagebaseid"));
lecture.setIsEnabled(rs.getBoolean("isenabled"));
lecture.setStartTime(rs.getLong("starttime"));
lecture.setEndTime(rs.getLong("endtime"));
lecture.setLastUsed(rs.getLong("lastused"));
lecture.setUseCount(rs.getInt("usecount"));
lecture.setOwnerId(rs.getString("ownerid"));
lecture.setUpdaterId(rs.getString("updaterid"));
lecture.setIsExam(rs.getBoolean("isexam"));
lecture.setHasInternetAccess(rs.getBoolean("hasinternetaccess"));
lecture.setDefaultPermissions(DbLecturePermissions.fromResultSetDefault(rs));
lecture.setUserPermissions(DbLecturePermissions.fromResultSetUser(rs));
lecture.setIsImageVersionUsable(rs.getBoolean("imgvalid"));
list.add(lecture);
}
return list;
} catch (SQLException e) {
LOGGER.error("Query failed in DbLecture.getAll()", e);
throw e;
}
}
public static LectureRead getLectureDetails(UserInfo user, String lectureId) throws SQLException,
TNotFoundException {
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("SELECT"
+ " l.lectureid, l.displayname AS lecturename, l.description, l.imageversionid, l.autoupdate,"
+ " l.isenabled, l.starttime, l.endtime, l.lastused, l.usecount, l.createtime, l.updatetime,"
+ " l.ownerid, l.updaterid, l.runscript, l.nics, l.netrules, l.isexam, l.hasinternetaccess,"
+ " l.caneditdefault, l.canadmindefault, p.canedit, p.canadmin"
+ " FROM lecture l "
+ " LEFT JOIN lecturepermission p ON (l.lectureid = p.lectureid AND p.userid = :userid)"
+ " WHERE l.lectureid = :lectureid LIMIT 1");
stmt.setString("userid", user.userId);
stmt.setString("lectureid", lectureId);
ResultSet rs = stmt.executeQuery();
if (!rs.next())
throw new TNotFoundException();
LectureRead lecture = new LectureRead();
lecture.setLectureId(rs.getString("lectureid"));
lecture.setLectureName(rs.getString("lecturename"));
lecture.setDescription(rs.getString("description"));
lecture.setAutoUpdate(rs.getBoolean("autoupdate"));
lecture.setIsEnabled(rs.getBoolean("isenabled"));
lecture.setStartTime(rs.getLong("starttime"));
lecture.setEndTime(rs.getLong("endtime"));
lecture.setLastUsed(rs.getLong("lastused"));
lecture.setUseCount(rs.getInt("usecount"));
lecture.setCreateTime(rs.getLong("createtime"));
lecture.setUpdateTime(rs.getLong("updatetime"));
lecture.setOwnerId(rs.getString("ownerid"));
lecture.setUpdaterId(rs.getString("updaterid"));
lecture.setRunscript(rs.getString("runscript"));
String netrules = rs.getString("netrules");
if (netrules != null) {
try {
lecture.setNetworkExceptions(Arrays.asList(Json.deserializeThrift(netrules,
NetRule[].class)));
} catch (JsonParseException e) {
LOGGER.warn("Could not deserialize netrules for lecture " + lectureId, e);
}
}
lecture.setImage(DbImage.getImageSummary(connection, user,
DbImage.getBaseIdForVersionId(connection, rs.getString("imageversionid"))));
lecture.setAllowedUsers(getAllowedUsers(connection, lectureId));
return lecture;
} catch (SQLException e) {
LOGGER.error("Query failed in DbLecture.getLectureDetails()", e);
throw e;
}
}
public static List<String> getAllowedUsers(MysqlConnection connection, String lectureId)
throws SQLException {
MysqlStatement stmt = connection.prepareStatement("SELECT" + " userlogin FROM lectureuser"
+ " WHERE lectureid = :lectureid");
stmt.setString("lectureid", lectureId);
ResultSet rs = stmt.executeQuery();
List<String> list = new ArrayList<>();
while (rs.next()) {
list.add(rs.getString("userlogin"));
}
return list;
}
public static boolean delete(String lectureId) throws TNotFoundException, SQLException {
int affected;
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("DELETE FROM lecture WHERE lectureid = :lectureid");
stmt.setString("lectureid", lectureId);
affected = stmt.executeUpdate();
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in DbLecture.delete()", e);
throw e;
}
return affected == 1;
}
}
