package org.openslx.bwlp.sat.database.mappers;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
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.database.Paginator;
import org.openslx.bwlp.sat.database.models.LocalImageVersion;
import org.openslx.bwlp.sat.permissions.User;
import org.openslx.bwlp.thrift.iface.ImageBaseWrite;
import org.openslx.bwlp.thrift.iface.ImageDetailsRead;
import org.openslx.bwlp.thrift.iface.ImagePermissions;
import org.openslx.bwlp.thrift.iface.ImageSummaryRead;
import org.openslx.bwlp.thrift.iface.ImageVersionDetails;
import org.openslx.bwlp.thrift.iface.ImageVersionWrite;
import org.openslx.bwlp.thrift.iface.ShareMode;
import org.openslx.bwlp.thrift.iface.TNotFoundException;
import org.openslx.bwlp.thrift.iface.UserInfo;
import org.openslx.filetransfer.util.ChunkList;
public class DbImage {
private static final Logger LOGGER = Logger.getLogger(DbImage.class);
/**
* Get list of all images visible to the given user, optionally filtered by
* the given list of tags.
*
* @param user Instance of {@link UserInfo} representing the user in
* question
* @param tagSearch list of tags an image must have to be included in the
* list.
* @param page page to return
* @return {@link List} of {@link ImageSummaryRead}
* @throws SQLException
*/
public static List<ImageSummaryRead> getAllVisible(UserInfo user, List<String> tagSearch, int page)
throws SQLException {
// TODO: Implement tag search functionality
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("SELECT"
+ " i.imagebaseid, i.latestversionid, i.displayname,"
+ " i.osid, i.virtid, i.createtime, i.updatetime, i.ownerid,"
+ " i.sharemode, i.istemplate, i.canlinkdefault, i.candownloaddefault,"
+ " i.caneditdefault, i.canadmindefault,"
+ " lat.expiretime, lat.filesize, lat.isrestricted, lat.isvalid,"
+ " lat.uploaderid, lat.isprocessed,"
+ " perm.canlink, perm.candownload, perm.canedit, perm.canadmin"
+ " FROM imagebase i"
+ " LEFT JOIN imageversion lat ON (lat.imageversionid = i.latestversionid)"
+ " LEFT JOIN imagepermission perm ON (i.imagebaseid = perm.imagebaseid AND perm.userid = :userid)"
+ Paginator.limitStatement(page));
stmt.setString("userid", user.userId);
ResultSet rs = stmt.executeQuery();
List<ImageSummaryRead> list = new ArrayList<>(100);
while (rs.next()) {
list.add(resultSetToSummary(user, rs));
}
return list;
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.getAllVisible()", e);
throw e;
}
}
public static ImageDetailsRead getImageDetails(UserInfo user, String imageBaseId)
throws TNotFoundException, SQLException {
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("SELECT i.imagebaseid, i.latestversionid,"
+ " i.displayname, i.description, i.osid, i.virtid, i.createtime, i.updatetime, i.ownerid, i.updaterid,"
+ " i.sharemode, i.istemplate,"
+ " i.canlinkdefault, i.candownloaddefault, i.caneditdefault, i.canadmindefault,"
+ " perm.canlink, perm.candownload, perm.canedit, perm.canadmin"
+ " FROM imagebase i"
+ " LEFT JOIN imagepermission perm ON (i.imagebaseid = perm.imagebaseid AND perm.userid = :userid)"
+ " WHERE i.imagebaseid = :imagebaseid");
stmt.setString("userid", user.userId);
stmt.setString("imagebaseid", imageBaseId);
ResultSet rs = stmt.executeQuery();
if (!rs.next())
throw new TNotFoundException();
// Exists:
List<String> tags = DbSoftwareTag.getImageTags(connection, imageBaseId);
List<ImageVersionDetails> versions = getImageVersions(connection, imageBaseId);
ImagePermissions defaultPermissions = DbImagePermissions.fromResultSetDefault(rs);
ImageDetailsRead image = new ImageDetailsRead(rs.getString("imagebaseid"),
rs.getString("latestversionid"), versions, rs.getString("displayname"),
rs.getString("description"), tags, rs.getInt("osid"), rs.getString("virtid"),
rs.getLong("createtime"), rs.getLong("updatetime"), rs.getString("ownerid"),
rs.getString("updaterid"), toShareMode(rs.getString("sharemode")),
rs.getByte("istemplate") != 0, defaultPermissions);
User.setCombinedUserPermissions(image, user);
return image;
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.getImageDetails()", e);
throw e;
}
}
public static LocalImageVersion getLocalImageData(String imageVersionId) throws TNotFoundException,
SQLException {
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("SELECT imageversionid, imagebaseid, filepath, filesize"
+ " FROM imageversion WHERE imageversionid = :versionid LIMIT 1");
stmt.setString("versionid", imageVersionId);
ResultSet rs = stmt.executeQuery();
if (!rs.next())
throw new TNotFoundException();
return new LocalImageVersion(rs.getString("imageversionid"), rs.getString("imagebaseid"),
rs.getString("filepath"), rs.getLong("filesize"));
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.getLocalImageData()", e);
throw e;
}
}
/**
* Private helper to create an {@link ImageSummaryRead} instance from a
* {@link ResultSet}
*
* @param rs
* @return
* @throws SQLException
*/
private static ImageSummaryRead resultSetToSummary(UserInfo user, ResultSet rs) throws SQLException {
ImagePermissions defaultPermissions = DbImagePermissions.fromResultSetDefault(rs);
ImageSummaryRead entry = new ImageSummaryRead(rs.getString("imagebaseid"),
rs.getString("latestversionid"), rs.getString("displayname"), rs.getInt("osid"),
rs.getString("virtid"), rs.getLong("createtime"), rs.getLong("updatetime"),
rs.getLong("expiretime"), rs.getString("ownerid"), rs.getString("uploaderid"),
toShareMode(rs.getString("sharemode")), rs.getLong("filesize"),
rs.getByte("isrestricted") != 0, rs.getByte("isvalid") != 0, rs.getByte("isprocessed") != 0,
rs.getByte("istemplate") != 0, defaultPermissions);
entry.userPermissions = DbImagePermissions.fromResultSetUser(rs);
User.setCombinedUserPermissions(entry, user);
return entry;
}
/**
* Get summary about an image by its base id.
*
* @param user
* @param imageBaseId
* @return
* @throws SQLException
* @throws TNotFoundException
*/
public static ImageSummaryRead getImageSummary(UserInfo user, String imageBaseId) throws SQLException,
TNotFoundException {
try (MysqlConnection connection = Database.getConnection()) {
return getImageSummary(connection, user, imageBaseId);
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.getImageSummary()", e);
throw e;
}
}
protected static ImageSummaryRead getImageSummary(MysqlConnection connection, UserInfo user,
String imageBaseId) throws SQLException, TNotFoundException {
MysqlStatement stmt = connection.prepareStatement("SELECT"
+ " i.imagebaseid, i.latestversionid, i.displayname,"
+ " i.osid, i.virtid, i.createtime, i.updatetime, i.ownerid,"
+ " i.sharemode, i.istemplate, i.canlinkdefault, i.candownloaddefault,"
+ " i.caneditdefault, i.canadmindefault,"
+ " lat.expiretime, lat.filesize, lat.isrestricted, lat.isvalid,"
+ " lat.uploaderid, lat.isprocessed,"
+ " perm.canlink, perm.candownload, perm.canedit, perm.canadmin"
+ " FROM imagebase i"
+ " LEFT JOIN imageversion lat ON (lat.imageversionid = i.latestversionid)"
+ " LEFT JOIN imagepermission perm ON (i.imagebaseid = perm.imagebaseid AND perm.userid = :userid)"
+ " WHERE i.imagebaseid = :imagebaseid");
stmt.setString("userid", user.userId);
stmt.setString("imagebaseid", imageBaseId);
ResultSet rs = stmt.executeQuery();
if (!rs.next()) {
throw new TNotFoundException();
}
return resultSetToSummary(user, rs);
}
private static List<ImageVersionDetails> getImageVersions(MysqlConnection connection, String imageBaseId)
throws SQLException {
List<ImageVersionDetails> versionList = new ArrayList<>();
MysqlStatement stmt = connection.prepareStatement("SELECT"
+ " imageversionid, createtime, expiretime, filesize, uploaderid,"
+ " isrestricted, isvalid, isprocessed" + " FROM imageversion"
+ " WHERE imagebaseid = :imagebaseid");
stmt.setString("imagebaseid", imageBaseId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
String imageVersionId = rs.getString("imageversionid");
versionList.add(new ImageVersionDetails(imageVersionId, rs.getLong("createtime"),
rs.getLong("expiretime"), rs.getLong("filesize"), rs.getString("uploaderid"),
rs.getByte("isrestricted") != 0, rs.getByte("isvalid") != 0,
rs.getByte("isprocessed") != 0, DbSoftwareTag.getImageVersionSoftwareList(connection,
imageVersionId)));
}
stmt.close();
return versionList;
}
/**
* Create new row in the imagebase table.
*
* @param user the user the image will belong to
* @param imageName name of the image to be created
* @return UUID of the newly created image
* @throws SQLException
*/
public static String createImage(UserInfo user, String imageName) throws SQLException {
if (imageName.length() > 100) {
imageName = imageName.substring(0, 100);
}
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("INSERT INTO imagebase"
+ " (imagebaseid, displayname, createtime, updatetime, ownerid, updaterid, sharemode,"
+ " istemplate, canlinkdefault, candownloaddefault, caneditdefault, canadmindefault)"
+ " VALUES"
+ " (:baseid, :name, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), :userid, :userid, 'LOCAL',"
+ " 0, 0, 0, 0, 0)");
String imageUuid = UUID.randomUUID().toString();
stmt.setString("baseid", imageUuid);
stmt.setString("name", imageName);
stmt.setString("userid", user.userId);
stmt.executeUpdate();
connection.commit();
return imageUuid;
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.createImage()", e);
throw e;
}
}
public static void updateImageMetadata(UserInfo user, String imageBaseId, ImageBaseWrite image)
throws SQLException {
if (image.imageName.length() > 100) {
image.imageName = image.imageName.substring(0, 100);
}
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("UPDATE imagebase"
+ " SET displayname = :imagename, description = :description,"
+ " osid = :osid, virtid = :virtid, istemplate = :istemplate, canlinkdefault = :canlink,"
+ " candownloaddefault = :candownload, caneditdefault = :canedit,"
+ " updaterid = :updaterid, updatetime = UNIX_TIMESTAMP(),"
+ " canadmindefault = :canadmin" + " WHERE imagebaseid = :baseid");
stmt.setString("baseid", imageBaseId);
stmt.setString("imagename", image.imageName);
stmt.setString("description", image.description);
stmt.setInt("osid", image.osId);
stmt.setString("virtid", image.virtId);
stmt.setBoolean("istemplate", image.isTemplate);
stmt.setBoolean("canlink", image.defaultPermissions.link);
stmt.setBoolean("candownload", image.defaultPermissions.download);
stmt.setBoolean("canedit", image.defaultPermissions.edit);
stmt.setBoolean("canadmin", image.defaultPermissions.admin);
stmt.setString("updaterid", user.userId);
stmt.executeUpdate();
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.updateImageMetadata()", e);
throw e;
}
}
public static void setImageOwner(String imageBaseId, String newOwnerId) throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("UPDATE imagebase"
+ " SET ownerid = :ownerid WHERE imagebaseid = :baseid");
stmt.setString("ownerid", newOwnerId);
stmt.setString("baseid", imageBaseId);
stmt.executeUpdate();
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.setImageOwner()", e);
throw e;
}
}
/**
* Get the UUID of the image base belonging to the given image version UUID.
* Returns <code>null</code> if the UUID does not exist.
*
* @param imageVersionId
* @return
* @throws SQLException
* @throws TNotFoundException
*/
public static String getBaseIdForVersionId(String imageVersionId) throws SQLException, TNotFoundException {
try (MysqlConnection connection = Database.getConnection()) {
return getBaseIdForVersionId(connection, imageVersionId);
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.getBaseIdForVersionId()", e);
throw e;
}
}
/**
* Get the UUID of the image base belonging to the given image version UUID.
*
* @param imageVersionId
* @return
* @throws SQLException
* @throws TNotFoundException version id is unknown
*/
protected static String getBaseIdForVersionId(MysqlConnection connection, String imageVersionId)
throws SQLException, TNotFoundException {
MysqlStatement stmt = connection.prepareStatement("SELECT imagebaseid FROM imageversion"
+ " WHERE imageversionid = :imageversionid LIMIT 1");
stmt.setString("imageversionid", imageVersionId);
ResultSet rs = stmt.executeQuery();
if (!rs.next())
throw new TNotFoundException();
return rs.getString("imagebaseid");
}
private static ShareMode toShareMode(String string) {
return ShareMode.valueOf(string);
}
/**
* Update meta data of a specific image version.
*
* @param user user doing the edit
* @param imageVersionId UUID of image version
* @param image meta data to set
* @throws SQLException
* @throws TNotFoundException
*/
public static void updateImageVersion(UserInfo user, String imageVersionId, ImageVersionWrite image)
throws SQLException, TNotFoundException {
try (MysqlConnection connection = Database.getConnection()) {
String baseId = getBaseIdForVersionId(connection, imageVersionId);
if (baseId == null)
throw new TNotFoundException();
// First update version table
MysqlStatement stmtVersion = connection.prepareStatement("UPDATE imageversion v SET"
+ " v.isrestricted = :isrestricted" + " WHERE v.imageversionid = :versionid");
stmtVersion.setString("versionid", imageVersionId);
stmtVersion.setBoolean("isrestricted", image.isRestricted);
stmtVersion.executeUpdate();
// Then base table
MysqlStatement stmtBase = connection.prepareStatement("UPDATE imagebase b SET"
+ " b.updaterid = :userid, b.updatetime = UNIX_TIMESTAMP()"
+ " WHERE b.imagebaseid = :baseid");
stmtBase.setString("userid", user.userId);
stmtBase.setString("baseid", baseId);
stmtBase.executeUpdate();
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.updateImageVersion()", e);
throw e;
}
}
/**
* Mark given image for deletion. The image is marked for deletion by
* setting the expire timestamp to the current date, and by setting the
* image disabled and invalid. Next time the cleanup task runs, the image
* will be deleted.
*
* @param imageVersionId UUID of image version to delete
* @throws SQLException
*/
public static void markForDeletion(String imageVersionId) throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("UPDATE imageversion SET"
+ " expiretime = UNIX_TIMESTAMP() - 1, isvalid = 0"
+ " WHERE imageversionid = :versionid");
stmt.setString("versionid", imageVersionId);
stmt.executeUpdate();
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.markForDeletion()", e);
throw e;
}
}
public static void setShareMode(String imageBaseId, ImageBaseWrite newData) throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
MysqlStatement stmt = connection.prepareStatement("UPDATE imagebase"
+ " SET sharemode = :sharemode WHERE imagebaseid = :baseid LIMIT 1");
stmt.setString("baseid", imageBaseId);
stmt.setString("sharemode", newData.shareMode.toString());
stmt.executeUpdate();
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.setShareMode()", e);
throw e;
}
}
public static void createImageVersion(String imageBaseId, String imageVersionId, UserInfo owner,
long fileSize, String filePath, ImageVersionWrite versionSettings, ChunkList chunks,
byte[] machineDescription) throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
final long nowSecs = System.currentTimeMillis() / 1000;
MysqlStatement stmt = connection.prepareStatement("INSERT INTO imageversion"
+ " (imageversionid, imagebaseid, createtime, expiretime, filesize, filepath, uploaderid,"
+ " isrestricted, isvalid, isprocessed, mastersha1, virtualizerconfig)"
+ " VALUES "
+ " (:imageversionid, :imagebaseid, :createtime, :expiretime, :filesize, :filepath,"
+ " :uploaderid, :isrestricted, :isvalid, :isprocessed, :mastersha1, :virtualizerconfig)");
stmt.setString("imageversionid", imageVersionId);
stmt.setString("imagebaseid", imageBaseId);
stmt.setLong("createtime", nowSecs);
stmt.setLong("expiretime", nowSecs + 86400 * 365); // TODO: Config!
stmt.setLong("filesize", fileSize);
stmt.setString("filepath", filePath);
stmt.setString("uploaderid", owner.userId);
stmt.setBoolean("isrestricted", versionSettings == null ? false : versionSettings.isRestricted);
stmt.setBoolean("isvalid", true); // TODO
stmt.setBoolean("isprocessed", false);
stmt.setBinary("mastersha1", null); // TODO
stmt.setBinary("virtualizerconfig", machineDescription);
stmt.executeUpdate();
// TODO: Write chunk hashes to DB
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in DbImage.createImageVersion()", e);
throw e;
}
}
}
